Postgresql SQL入门

绝地灬酷狼 2024-02-18 12:15 84阅读 0赞

#### 文章目录 ####

*   *  1.1 SQL语句语法简介
     *   *  1.1.1 语句的分类
         *  1.1.2 词法结构
     *  1.2 DDL语句
     *   *  1.2.1 建表语句
         *  1.2.2 删除表语句
     *  1.3 DML语句
     *   *  1.3.1 插入语句
         *  1.3.2 更新语句
         *  1.3.3 删除语句
     *  1.4 查询语句
     *   *  1.4.1 单表查询语句
         *  1.4.2 过滤条件的查询
         *  1.4.3 排序
         *  1.4.4 分组查询
         *  1.4.5 多表关联查询
         *  1.4.6 子查询
     *  1.5 其他SQL语句
     *   *  1.5.1 INSERT...SELECT语句
         *  1.5.2 UNION 语句
         *  1.5.3 TRUNCATE TABLE 语句

SQL是结构化查询语言（Structured Query Language）的简称，它是关系型数据库的最重要的操作语言，并且它的影响已经超出了数据库领域，比如，在Hadoop中的 Hive就是一个SQL接口。  
以下将介绍一些通用的、最基础的SQL语法知识。这些使用语法不仅适用于PostgreSQL数据库，也适用于其他关系型数据库，如MySQL、Oracle。

### 1.1 SQL语句语法简介 ###

#### 1.1.1 语句的分类 ####

SQL命令一般分为DQL、DML、DDL三类。  
•DQL：数据查询语句，基本就是SELECT查询命令，用于数据查询。  
•DML：Data Manipulation Language，即数据操纵语言，主要用于插入、更新、 删除数据，所以也分为INSERT、UPDATE、DELETE三种语句。  
•DDL： Data Definition Language， 即数据定义语言，简单来说，是用于创建、删除、修改表、索引等数据库对象的语言。

#### 1.1.2 词法结构 ####

每次执行的SQL可以由多条SQL命令组成。多条SQL命令之间由分号（；）分隔。  
SQL命令由一系列记号组成，这些记号可以由关键字、标识符、双引号包围的标识符、常量和单引号包围的文本常量、特殊的字符等组成。SQL命令中可以有注释，这些注释在PostgreSQL中等同于空白。  
举例来说，下面的命令从SQL语法上来说是合法的：  
SELECT \* FROM OSDBA\_TABLEO1；  
UPDATE OSDBA\_TABLE SET COL1 = 614；  
INSERT INTO OSDBA\_TABLE VALUES （232， ‘hello osdba“）；  
该SQL由3条命令组成。在SQL中，多行命令也可以写在一行中，当然也可以写在多行中，单条命令也可以占用多行。  
SQL命令并未像计算机语言一样严格地明确标识哪些是命令、哪些是操作数或参数。 SQL语法可以帮助用户比较直观地理解其意思。比如，查询一个表的数据的命令就是由 “SELECT+要查询的各列+FROM表”这样的语法组成的。后面会详细叙述SQL的用法。

### 1.2 DDL语句 ###

#### 1.2.1 建表语句 ####

表是关系型数据库中最基本的对象，数据库中的表与实际生活中的二维表格很相似， 表中有很多列也有很多行，每一列有一个名称，不同的列有不同的数据类型，比如，列可能是数字、文本字符串，也可能是日期类型。建表语句的简单语法如下：  
CREATE TABLE table\_name（  
COl01 *namme data\_type，  
col02\_namme data\_type，  
col03\_namme data\_type，  
col04\_namme data\_type， 5；  
其中“CREATE”“TABLE”为关键字，是不变的，从字面上意思也很好理解，表示创建表，“table\_name”表示表名， “col01\_name”“col02\_name”“col03\_name”“col04* name”分别表示列名。“data\_type”表示数据类型，不同的数据库系统有不同的数据类型名称，即使是同一种整数类型，在不同的数据库系统中类型名称也有所不同。变长的字符串在大多数数据库中都可以使用varchar类型， 比如PostgreSQL、MySQL和Oracle 数据库等。整型数据在PostgreSQL和MySQL中都可以使用int类型。日期类型的类型名称一般为“date”。例如，要创建一张分数表“score” ，包括“学生名称（student\_name）”“语文成绩（chinese\_score）”“数学成绩（math\_score）”“考试日期（test\_date）”4列， 创建该表的SQL语句如下：  
CREATE TABLE score（  
student \_name varchar（40），  
chinese\_score int，  
math\_score int，  
test\_date date ）；  
如果已安装了数据库，现在就可以使用psq|工具连接到  
PostgreSQL数据库了，执行上面的建表语句，命令如下：  
osdba=\# CREATE TABLE score（  
osdba（\# student\_name varchar（40），  
osdba（\# chinese\_score int，  
osdba（\# math\_score Int，  
osdba（\# test\_date date osdba（\#）；  
CREATE TABLE  
osdba=\#  
在psq|中，可使用“\\d”显示数据库中有哪些表，示例如下：  
osdba=\# \\d  
使用“\\d score”可以显示这张表的定义情况：  
osdba=\#\\d score

建表时可以指定表的主键，主键是表中行的唯一标识，这个唯一标识是不能重复的。  
在创建表的语句中，可以在列定义后面用“primary key”主键来指定该列为主键，如下面的学生表所示：  
CREATE TABLE student（no int primary key， student\_name varchar（40）， age int）；  
在该表中，“学号（no）”为主键，则在列定义后面加上“primary key”。在psal中演示如下：  
osdba=\# CREATE TABLE student（no int primary key， student\_name varchar（40）， age int）；  
NOTICE： CREATE TABLE / PRIMARY KEY will create implicit index “student\_pkey“ for table “‘student“  
CREATE TABLE

> “NOTICE：CREATE TABLE/PRIMARY KEY will create implicit index “‘student\_pkey“ for table “‘student“”，这句提醒表示系统自动为主键创建了一个隐含的索引“student\_pkey”。

#### 1.2.2 删除表语句 ####

删除表的语法比较简单，语法格式如下：  
DROP TABLE table\_name；  
其中“table\_name”表示要删除的表的表名。假设要删除前面建的表“student” 〞，则可以使用下面的SQL语句：  
DROP TABLE student；

### 1.3 DML语句 ###

#### 1.3.1 插入语句 ####

使用下面的语句可以向前面建的学生表 （student）中插入数据：  
NSERT INTO student VALUES（1，‘张三，14）；  
由此可以看出，INSERT语句的语法为：以“INSERT INTO”关键字为首，后面跟表名，然后再跟“VALUES”关键字，最后是由小括号括起来的以逗号分隔的各列数据，数据的顺序与定义表时表列的顺序相同。当然，也可以在表名后指定要插入的数据列的顺序，SQL语句如下：  
NSERT INTO student（no， age， student\_name） VALUES（2， 13， ‘李四“；  
在插入数据时，也可以不为某些列插入数据，此时这些列的数据会被置空，SQL语句如下：  
INSERT INTO student（no， student\_name） VALUES（2， ‘王二）；  
如果在psql中执行了下面的语句，就可以使用SELECT语句查询数据了：  
SELECT \* FROM student；

> 注意：在插入数据时，未提供的列数据会被置为NULL。

#### 1.3.2 更新语句 ####

假设要把student表中所有学生的年龄（age）更新为“15”，则更新语句如下：  
UPDATE student SET age = 15；  
从上面的语句可以看出，更新语句以“UPDATE”关键字开始，后面跟表名，然后是 “SET”关键字，表示要设置的数据，再后面就是要设置的数据表达式“age=15”，设置数据的表达式也很简单，格式是“列名=数据”。  
实际执行的效果如下：  
osdba=\# UPDATE student SET age = 15；  
在更新数据时，还可以指定过滤表达式“WHERE”，从而指定更新哪条或哪些数据， 比如，要将学号（no）为“3”的学生的年龄更新为14岁，则使用如下语句：  
UPDATE student SET age =14 WHERE no = 3；  
在SET子句中，还可以同时更新多个列的值，如下所示：  
UPDATE student SET age =13， student\_name=“王明充‘WHERE nO =3；

#### 1.3.3 删除语句 ####

删除学号（no）力“3”的学生的记录的语句如下：  
DELETE FROM student WHERE nO = 3；  
由此可见，删除语句比较简单，以“DELETE FROM”开始，后面跟表名，然后再加一个“WHERE”子句用于指定要删除的记录。  
当然也可以没有“WHERE”子句，这表明要删除整个表的数据。删除表student表中所有数据的语句如下：  
DELETE FROM student；

### 1.4 查询语句 ###

#### 1.4.1 单表查询语句 ####

查询student表中所有数据的语句如下：  
select no， student\_name， age from student；  
由此可见，“SELECT”是关键字，表示查询，后面跟多个列名，各列之间使用逗号分隔；其后的“FROM”是关键字，后面跟表名。列可以是表的列名，也可以是一个表达式：  
select age+5 from student；  
表达式中可以包括表的列，也可以与表列无关：  
select no， 3+5 from student；  
当表达式与表列无关时，在PostgreSQL和MySQL中可以不使用“FROM表名”，这样一来就可以作为计算器使用了：  
osdba=\# select 55+88；  
osdba=\# select 10*2，3*5+2；  
如果想查询表中所有列的数据，则可以使用“\*”代表所有列，如下所示：  
select \* from student；

#### 1.4.2 过滤条件的查询 ####

SELECT语句后面可以指定WHERE子句，用于指定要查询哪条或哪些记录，比如， 要查询学号为3的学生记录，其SQL语句如下：  
osdba=\# SELECT \* FROM student where no=3；  
WHERE子句中也可以使用大于、小于表达式，比如，想查询年龄大于或等于15岁的学生记录，查询语句如下：  
osdba=\# SELECT \* FROM student where age >= 15；

#### 1.4.3 排序 ####

使用排序子句可以对查询结果进行排序，排序子句是在SELECT语句后面加上 “ORDER BY”子句，比如，想将查询结果按年龄排序，则查询语句如下：  
osdba=\# SELECT \* FROM student ORDER BY age；  
排序子句“ORDER BY”应该在WHERE子句之后，如果顺序错了，执行时会报错：  
osdba=\# SELECT \* FROM student ORDER BY age WHERE age >= 15；

> ERROR： syntax error at or near “WHERE“  
> LINE 1： SELECT \* FROM student ORDER BY age WHERE age >=15；

把“ORDER BY”子句放到“WHERE”子句后面就不会报错了：  
osdba=\# SELECT \* FROM student WHERE age >=15 ORDER BY age；  
还可以按多个列对查询结果进行排序，比如，根据“age”和“‘student\_name”两个列  
osdba=\# SELECT \* FROM student ORDER BY age，student\_name；

#### 1.4.4 分组查询 ####

如果需要统计不同年龄的学生人数，可以使用分组查询，分组查询子句的关键字为“GROUP BY”，用法如下：  
osdba=\# SELECT age， count（\*） FROM student GROUP BY age；  
从上面的查询语句中可以看出，使用“GROUP BY”语句时需要使用聚合函数，常用的聚合函数有“count”“sum”等。

#### 1.4.5 多表关联查询 ####

多表关联查询也称表join。假设有一张班级表“class” ”，建表语句如下：  
CREATE TABLE class（no int primary key， class\_name varchar（40））；  
表中的“no”表示班级编号，“class\_name”表示班级名称。  
现插入一些测试数据：  
osdba=\# INSERT INTO class VALUES（1，初二（1）班“)；  
osdba=\# INSERT INTO class VALUES（2，初二（2）班“)；  
osdba=\# INSERT INTO class VALUES（3，“初二（3）班“)；  
osdba=\# INSERT INTO class VALUES（4， 初二（4）班）；  
osdba=\# SELECT \* FROM class；  
还有一张学生表“student” ，建表语句如下：  
CREATE TABLE student（no int primary key， student\_name varchar（40）， age int， class\_no int）  
同样插入一些测试数据：  
osdba=\# INSERT INTO student VALUES（1， “张三，14， 1）；  
osdba=\# INSERT INTO student VALUES（2，‘吴二，15，1)；  
osdba=\# INSERT INTO student VALUES（3, '李四,13,2）;  
osdba=\# INSERT INTO student VALUES（4, '吴三,15,2);  
osdba=\# INSERT INTO student VALUES（5, '王二,15, 3）;  
osdba=\# INSERT INTO student VALUES（6, '李三,14,3）;  
osdba=\# INSERT INTO student VALUES（7, 吴二,15,4）;  
osdba=\# INSERT INTO student VALUES（8, "张四,14,4）;  
osdba=\# SELECT \* FROM student;  
假设想查询每个学生的名字与班级名称的关系,那么就需要关联查询两张表:  
SELECT student\_name, class\_name FROM student, class WHERE student.class\_no = class.no;  
查询结果如下:  
osdba=\# SELECT student\_name, class\_name FROM student, class WHERE student.class\_no = Class.no;  
表关联查询就是在WHERE子句中加上需要关联的条件（两张表关联）：  
WHERE student。class\_no =class。no；  
由于两张表中有些列的名称相同，如在表“student”中“no”表示学生号，而在表 “class”中表示班级号，所以在关键条件中要明确使用“表名”加“列名”来唯一定位某一列。如果输入的表名比较长，可以给表起个别名，SQL语句如下：  
SELECT student\_name， class\_name FROM student a， Class b WHERE a。class\_no = D。no；  
在上面的语句中，表“student”的别名为“a”，表“class”的别名为“b”，此时条件表达式中“b。no”就代表表“class”中的“no”列。  
还可以在关联查询的WHERE子句中加上其他过滤条件，如下所示：  
osdba=\# SELECT student\_name， class\_name FROM student a， Class D WHERE a。class\_no = b。no AND a。age > 14；

#### 1.4.6 子查询 ####

当一个查询是另一个查询的条件时，称之为子查询。主要有4种语法的子查询：  
•带有谓词IN的子查询：expression ［NOT］IN （sqlstatement）。  
•带有EXISTS谓词的子查询：［NOT］ EXISTS （sqIstatement）。  
•带有比较运算符的子查询：comparison（>，<，=，！=）（sqlstatement）。  
•带有ANY（SOME）或ALL谓词的子查询：comparison ［ANY ALLSOME］（sqlstatement）。  
我们还用前面例子中班级表“class”和学生表“studtent中的数据。  
下面用带有谓词IN的子查询来查询“初二（1）班”的学生记录：  
osdba=\#SELECT \* FROM student WHERE class\_no in （select no FROM class where class\_name =‘初二（1）班“；  
上面的查询也可以用带EXISTS谓词的子查询来实现：  
osdba=\# SELECT \* FROM student s WHERE EXISTS （SELECT 1 FROM class C WHERE s。class\_no=c.no AND c.class\_name =‘初二（1）班）；  
此查询还可以用带有比较符（这里用的是“=”）的子查询来实现：  
osdba=\# SELECT \* FROM student WHERE class\_no = （SELECT no FROM class C WHERE class\_nam e=‘初二（1）班“；  
此查询还可以用带有ANY（SOME）或ALL谓词的子查询来实现：  
osdba=\# SELECT \* FROM student WHERE class\_no = any（SELECT no FROM class C WHERE Class\_n ame =‘初- 二（1）班“）；  
但如果我们要查询两个班级的学生记录，不能使用带有等于“\_”比较符的子查询：  
osdba=\# SELECT \* FROM student WHERE no = （SELECT no FROM class C WHERE class\_name in（ 初二（1）班，初二（2）班》）；

> ERROR： more than one row returned oy a subquery used as an expression

上面的查询报错说子查询不能返回多行。这种不能返回多行的子查询也称标量子查询，标量子查询不仅能嵌套在WHERE子句中，也可以嵌套在SELECT的列表中，如我们要查询每个班级学生的最大年龄，则可以用如下SQL语句：  
osdba=\# SELECT no， class\_name， （SELECT max（age） as max\_age FROM student s WHERE s.no= c.n o as max\_age FROM class C；  
查询两个班级的学生记录时用带有ANY（SOME）谓词的子查询就没有问题了，示例如下：osdba=\# SELECT \* FROM student WHERE class\_no = any（SELECT no FROM class C WHERE class\_n ame in（初二（1）班，‘初二（2）班“；

### 1.5 其他SQL语句 ###

#### 1.5.1 INSERT…SELECT语句 ####

使用INSERT。SELECT语句可以把一张表中的数据插入另一张表中，该语句属于DML语句。  
假设创建了一张学生表的备份表“student\_bak”，建表语句如下：  
CREATE TABLE student\_bak（no int primary key， student\_name varchar（40）， age int， class\_no int）；  
可以使用下面的语句把数据备份到备份表中：  
INSERT INTO student\_bak SELECT \* FROM student；  
演示如下：  
osdba=\# INSERT INTO student\_bak SELECT \* FROM student；

#### 1.5.2 UNION 语句 ####

使用UNION语句可以把从两张表中查询出来的数据合在一个结果集下，示例如下：

SELECT \* FROM student WHERE no =1 UNION SELECT \* FROM student\_bak where no = 2；

上面的语法比较简单，把两个SQL语句用“UNION”关键字连接起来就可以了。

查询结果如下：  
osdba=\# SELECT \* FROM student WHERE nO = 1 UNION SELECT \* FROM student\_bak where no = 2；  
需要特别注意的是，UNION语句可以把结果集中相同的两条记录合并成一条：  
osdba=\# SELECT \* FROM student WHERE nO = 1 UNION SELECT \* FROM student\_bak where no = 1；  
如果不想合并结果集中的相同记录，可以使用UNION ALL语句，示例如下：  
osdba=\# SELECT \* FROM student WHERE no = 1 UNION ALL SELECT \* FROM student\_bak where no

#### 1.5.3 TRUNCATE TABLE 语句 ####

TRUNCATE TABLE语句的用途是清空表内容。不带WHERE条件子句的DELETE语句也表示清空表内容，从执行结果来看，两者实现了相同的功能，但两者实现的原理是不一样的。TRUNCATE TABLE语句是DDL语句，即数据定义语句，相当于用重新定义一个新表的方法把原表的内容直接丢弃了，所以TRUNCATE TABLE执行起来会很快；  
而DELETE语句是DML语句，我们可以认为DELETE语句是把数据一条一条地删除，所以DELETE语句删除多行数据时执行起来比较慢。  
如果想把student\_bak表中的数据清除，可以使用如下命令：  
TRUNCATE TABLE student\_bak；