Postgresql SQL入门

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 102，35+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；