SQL和Java这种还不同, 如果是Java相同的版本, 语法都一样. 但不同的数据库可能支持的具体SQL语句不尽相同, 这就是通常所说数据库使用的是SQL方言的来历.

然后这两天把各种书捋了一遍, 细细思考, 发现顺序还真不能错, 必须得先会基础理论, 再扩展到PgSQL的具体实践上. 所以SQL还是得先学通用知识和理论, 再看具体的. 包括再深入的比如数据类型, 也是这样, 从通用到具体.

既然也是一门语言, 也会有关键字, 常量等等要素, 开始吧!

1. SQL基础语法
2. 基础数据类型
3. DDL - 关系操作
4. 查询 - 基础
5. AS 重命名
6. LIKE 字符串匹配
7. ORDER BY 排序

SQL基础语法

从Python, C, Java这么一路走过来, 最近这一年没有折腾新的语言. 现在年尾了折腾一下SQL.

SQL一条命令由一个分号结尾. 可以一次写多条语句, 顺序执行. 和其他语言一样, 也由各个TOKEN组成.

每个TOKEN可以是一个关键字, 一个标识符, 字面量或者特殊字符. TOKEN一般用空白(不是空格)来区分, 所以可以写的比较随意, 可以换行. 仅仅有些特殊地方需要严格书写.

学语言肯定要先知道关键字. 本来想找找SQL 2011的标准看看, 然后发现2016版有更新, 跟过去一看, 下载还收费.

所以这一块就暂时参考PostgreSQL中文技术站提供的10.1版本手册, 这里不赘述了, 我简单看了一下列表, 除了常用的之外, 剩下的也基本不会遇到了.

标识符用来表示关系, 属性, 以及其他数据库中的对象的名字, 所以也被简称为名字. 有一种特殊的标识符, 就是如果数据库对象的名称和关键字相同, 则要给标识符加上双引号.

关键字和标识符对大小写不敏感.

常量有两种, 一个是纯数字, 一个是字符串.

PgSQL中还支持标识符用Unicode码点, 以及字符串转义和位串.

操作符有这些: + - * / < > = ~ ! @ # % ^ & | ` ?. 具体用法遇到再说.

我翻了一下官网, 官网SQL的逻辑与数据库系统概论还不太相同, 后者更清晰一些, 先按后者的逻辑来.

基础数据类型

DDL就是用来定义关系的语句, 核心就是操作表. 由于关系意味着定义一批属性和对应的取值, 因此先看看数据类型.

SQL标准有如下, 这里一并把对应的PgSQL的也看了:

即使名称不同, 也可以使用SQL标准的名称: bigint、bit、bit varying、boolean、char、character varying、character、varchar、date、double precision、integer、interval、numeric、decimal、real、smallint、time（有时区或无时区）、timestamp（有时区或无时区）、xml.

这些SQL标准名称是全部数据库都支持的. 此外PgSQL还支持一些特殊的数据类型, 等用到再看, inet, json, jsonb等数据类型确实有意思. 还有text用于存储长字符串.

每种类型都可以取一个空值, 表示这个值不存在, 或者缺失, 或者暂时未知. 一般尽量不要用空值.(这里我是这么理解的, 如果允许一个空值的出现, 则这个关系和没有这个属性的关系, 其实一样了, 因为这个值不存在, 这本身就是一种打破了关系的存在.

定长的字符串如果存入短于定长的字符, 会补齐空白. 变长则不会. 如果比较两个长度不同的char属性, 会自动在短的后边补空白.

而varchar的比较取决于实现, 所以如果不是特定情况, 比如UUID之类的存放, 而是存普通的字符串, 推荐使用varchar. SQL规定nvarchar可以用来存放Unicode数据, 现实是很多数据库直接都可以指定编码, 这样普通的字符串也可以存放Unicode数据.

DDL - 关系操作

DDL既然是描述关系, 其本质就是操作表, 自然包含增删改表, 创建一个表不是一个空白的, 而是包含各个属性, 所以要先知道上边的数据类型才能定义属性.

用CREATE TABLE来定义(创建)关系, 格式为 CREATE TABLE 表名 (属性名1 数据类型1, 属性名2 数据类型2...., 完整性约束...)

属性名和数据类型好懂, 关键是后边的完整性约束, 有如下几种:

1. primary key(属性名1, 属性名2....), 表示其中的属性(共同)是主键. 主键默认非空(not null)而且唯一(不能重复)
2. foreign key(属性名) references B, 表示外键, 这个前边已经知道了,属性名是当前关系的属性名, 而B是被参照关系的名称.
3. not null, 这个要紧跟在属性名和数据类型之后, 表示该属性不能为空.

删除关系使用 DROP TABLE tablename. 如果不删除关系, 仅仅删除一个关系内的全部元组, 可以使用 DELETE FROM tablename.

修改关系则需要使用ALTER TABLE, 这个语句有两个写法, 一个表示给关系添加属性: ALTER TABLE tablename ADD A, D; 其中A是属性名称, D是数据类型.

另一个写法表示删除一个属性, 如果存在元组, 元组的这个值也会被删除: ALTER TABLE tablename DROP A; A是属性的名称, 这个就相当于从表里删除一列.

对关系的增删改的基础操作就是这些, 等看到后边章节可以再来补, 尤其是完整性约束的内容.

查询 - 基础

从这里开始就是DML的内容了, 也就对元组=具体数据对象的增删改查了. 核心就是查. 而删除一般是不允许普通用户操作的.

查询的基础语句是 SELECT FROM WHERE三个子句构成, 三个子句的顺序一定是 SELECT FROM WHERE, 不能够任意更改.

最基础的从单个关系中查询的语句是 SELECT A.. FROM B, 其中A是属性名称, B是关系名称, 这个查询的结果是由A..组成的一个新关系.

在默认情况下, 即使A..中有重复的内容, 也会被挑选出来. 如果想要去掉重复, 需要在SELECT之后加上distinct语句.

实验一下:

SELECT dept_name FROM test.instructor;
SELECT distinct dept_name FROM test.instructor;

很明显可以看到后者去重. 还可以对属性使用运算符, 以获得运算后的结果. 比如:

SELECT name, salary * 1.5 FROM test.instructor;

这个出来的结果就是名称与1.5倍的原来salary取值的元组组成的新关系. 实际上还有一些函数也可以作用于属性上, 返回来的结果就是函数执行后的结果. 一会就可以用上聚合函数了.

还记得查询的结果也是一个关系吗. FROM子句后边就是一个关系, 所以FROM后边跟一个表名, 也可以跟一个查询.

WHERE子句则表示条件, 用特定谓词表示某个条件, 只会选中符合条件的结果. 其中可以使用 and or not 和< <= = > >= <> 逻辑运算符.

比如我们想知道, 给所有的讲师涨完20%的工资之后, 有几个人大于100000元:

SELECT name FROM test.instructor WHERE test.instructor.salary * 1.2 >100000;

上边就是查询语句的最简单用法, 也就是从一个表内查询. 不过这也是基础.

如果要从多个表内查询, 就要明确的指出哪个表的属性名, 除非这个属性名在所有被选的表中是唯一的.

比如我们从两个表中选出一些列, 那就会得到笛卡尔积, 比如:

SELECT test.instructor.dept_name, section.building FROM test.instructor, test.section;

结果有180行, instructor表有12行, section表有15行, 可见结果就是一个笛卡尔积.

SELECT test.course.title, test.department.building
FROM test.department,
     test.course
WHERE test.course.dept_name = test.department.dept_name;

这个查询语句如果不加上后边的WHERE, 结果依然是一个笛卡尔积 13*7 = 91个, 注意这两个表有外键关联, 但在从多表查询的时候, 不加WHERE语句, 依然是笛卡尔积. 如果用SELECT * 来执行不带WHERE的语句, 可以看的更清晰, 有WHERE的就是从其中取走了department.dept_name与course.dept_name相等的行.

当然, 由于有外键关系, 使用WHERE语句的查询是有意义的, 表示可以查到课程与系所在楼的关系, 而笛卡尔积中不属于这个关系以外的关系并不是常见的关系.

这里很显然, 如果将FROM子句改成 FROM test.department natural join test.course 也可以得到想要的结果. 不过如果使用SELECT *, 就可以发现natural join 与使用WHERE查询的细微区别(多表查询有两个dept_name列, 而natural join 只有一列dept_name).

所以多表查询的关键就是, FROM之后以逗号分隔的关系, 会组成笛卡尔积, 组成之后, 再根据WHERE条件从中选取符合条件的结果. 回头看看上一篇文章里关系型数据库的理论, 会发现数据库确实完美的符合理论.

AS 重命名

重命名非常有用, 在出现子查询的时候, SQL更是要强制给子查询命名. 我们刚才计算工资, 如果执行如下查询:

SELECT name, salary*1.2 FROM test.instructor;

得到的新关系中1.2倍工资的属性, 没有名称. 如果将这个关系再当做一个关系, 就无法查询这个1.2倍工资的属性, 这个时候就可以给新的属性赋予一个名称:

SELECT name, salary * 1.2 as new_salary FROM test.instructor;

这个时候所得到的关系第二列就有了名称, 将其作为子查询的时候, 也可以查到, 比如:

SELECT foo.new_salary
FROM (SELECT name, salary * 1.2 as new_salary
      FROM test.instructor) as foo;

SQL规定, 子查询必须被as一个名称. 由于我们指定了子查询的新列是new_salary, 又指定了子查询关系的名称是foo, 所以就可以用foo.new_salary来查到这一列薪水了.

AS 语句只能出现在SELECT和FROM中, 起到命名的作用, WHERE语句中无法使用AS, 只能使用前边语句中由AS新命名的名称, 例如:

SELECT (foo.new_salary)
FROM (SELECT name, salary * 1.2 as new_salary
      FROM test.instructor) as foo
WHERE foo.new_salary > 90000;

重命名还有一个重要的作用, 就是自己与自己进行比较. 比如我们符合这样条件的老师: 不属于Biology系和Physics系的, 工资比至少一个Biology系的老师工资要高的老师.

这个先不用想SQL, 就想如何做, 其实很简单, 只要求instructor和其自身的笛卡尔积, 然后应用条件就可以, 问题是如何使用SQL语句呢? 两个关系虽然都是instructor, 但实际上还是两个关系做笛卡尔积, 一般这种情况都会采用命名, 有了两个命名, 实际上就可以明确的指定笛卡尔积中一半来自一个关系的列, 另一半来自另外一个关系的同名列了.

SELECT T1.name, T1.salary, T1.dept_name
FROM test.instructor as T1,
     test.instructor as T2
WHERE T1.salary > T2.salary and T2.dept_name='Biology' and T1.dept_name<>'Physics' and T1.dept_name<>'Biology';

如果不用命名, 这种查询就没办法写出来.

字符串

SQL用单引号表示字符串, 如果字符串中有单引号, 就连续写两个单引号表示一个单引号. 其他字符都不转义.

字符串还可以使用多个函数, 比如 || 表示连接字符串, 提取子串, 计算长度等, 这个以后要仔细看看PgSQL的函数.

最关键的字符串操作是 like 操作符, 其后跟一个字符串pattern, 一说pattern现在都明白了, 就是个用来匹配的模式串. pattern中用 % 代表0-任意长的子串, _ 代表单个字符. 所以可以使用这个模式来查找出匹配的字符串. 比如:

SELECT name FROM test.student WHERE name like 'S%';

会从学生中寻找出以S开头的名称. 在like 模式串中使用 escape 来定义转义字符, 这样就可以使用%和_, 如下:

SELECT name FROM test.student WHERE name like 'S\%' escape '\';

这个模式就是查S%, 当然这个例子查不出任何内容.

ORDER BY 排序

ORDER BY一般放在最后, 其后跟一个或者多个指定的属性, 默认按照属性的升序排序, 如果相同顺序, 再按照下一个指定的属性排序. 可以用desc加在属性后表示降序, 用asc来表示升序.

典型的用法如下:

SELECT * FROM test.instructor ORDER BY dept_name asc, salary desc;

即将全部intructor关系中的元组按照系名称升序, 再按照工资降序排列. 这样系名称相同的元组, 就会再按照工资降序排列.

WHERE 子句中的 BETWEEN

这个BETWEEN子句用在WHERE中表示取值范围, 包含两端的取值. 可以用其来简化一些逻辑判断:

SELECT * FROM test.instructor WHERE salary >= 80000 and salary <= 100000;

选出工资在80000和100000之间的讲师, 可以改写成:

SELECT * FROM test.instructor WHERE salary BETWEEN 80000 and 100000;

其实可以认为就是一颗语法糖.

可以说到目前为止, 从单个或者多个表中进行简单查询, 根据条件查询, 将查询结果进行排序都会了. 这已经足够应付很多时候的查询了. 不过SQL肯定不只如此了.