SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统 DBMS（Database Management System）充分利用SQL语言以提高计算机应用系统的工作质量与效率。

DBMS种类

主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型。

  • 层次数据库（Hierarchical Database，HDB）
  • 关系数据库（Relational Database，RDB）
    • Oracle Database：甲骨文公司的RDBMS
    • SQL Server：微软公司的RDBMS
    • DB2：IBM公司的RDBMS
    • PostgreSQL：开源的RDBMS
    • MySQL：开源的RDBMS
  • 面向对象数据库（Object Oriented Database，OODB）
  • XML数据库（XML Database，XMLDB）
  • 键值存储系统（Key-Value Store，KVS）

SQL语句及其种类

SQL 用关键字、表名、列名等组合而成的一条语句（SQL 语句）来描述操作的内容。

  • DDL（Data Definition Language，数据定义语言）

    用来创建或者删除存储数据用的数据库以及数据库中的表等对象，DDL 包含以下几种指令:

    • CREATE ： 创建数据库和表等对象
    • DROP ： 删除数据库和表等对象
    • ALTER ： 修改数据库和表等对象的结构

  • DML（Data Manipulation Language，数据操纵语言）

    用来查询或者变更表中的记录，DML 包含以下几种指令：

    • SELECT ：查询表中的数据`
    • INSERT ：向表中插入新数据`
    • UPDATE ：更新表中的数据`
    • DELETE ：删除表中的数据

  • DCL（Data Control Language，数据控制语言）
    用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定，DCL 包含以下几种指令：

    • COMMIT ： 确认对数据库中的数据进行的变更
    • ROLLBACK ： 取消对数据库中的数据进行的变更
    • GRANT ： 赋予用户操作权限
    • REVOKE ： 取消用户的操作权限

实际使用的 SQL 语句当中有 90% 属于 DML。

另外，有的地方将SELECT FROM WHERE归为DQL（Date Query Language，数据查询语言）

SQL的基本书写规则

  • SQL语句要以分号;结尾

  • SQL语句不区分大小写

    为了理解起来更加容易，保持规范，简易使用某种特定规则，如：

    • 关键字大写
    • 表名的首字母大写
    • 其余（列名等）小写

  • 常数的书写方式是固定的

    • SQL 语句中含有字符串的时候，需要像 ' abc ' 这样，使用单引号'将字符串括起来
    • 日期的格式有很多种（'30 Aug 2019'或者'19/08/30'等）

  • 单词需要用半角空格或者换行来分隔

  • 注释书写方式：

    • 1行注释：书写在--之后，只能写在同一行
    • 多行注释：书写在/*和*/之间，可以跨多行

创建示例

数据库的创建（ CREATE DATABASE 语句）

在创建表之前，一定要先创建用来存储表的数据库

CREATE DATABASE <数据库名称>;

示例，创建数据库 shop：

1
CREATE DATABASE shop;

表的创建（ CREATE TABLE 语句）

创建好数据库之后，接下来可以在其中创建表

CREATE TABLE < 表名 >
（ < 列名 1> < 数据类型 > < 该列所需约束 > ，
​ < 列名 2> < 数据类型 > < 该列所需约束 > ，
​ < 列名 3> < 数据类型 > < 该列所需约束 > ，
​ < 列名 4> < 数据类型 > < 该列所需约束 > ，
​ .
​ .
​ .
​ < 该表的约束 1> ， < 该表的约束 2> ，……）；

示例，创建 Product 表：

1
2
3
4
5
6
7
8
CREATE TABLE Product
(product_id 	 CHAR(4) 	   NOT NULL,
 product_name	 VARCHAR(100)  NOT NULL,
 product_type	 VARCHAR(32)   NOT NULL,
 sale_price		 INTEGER       ,
 purchase_price  INTEGER       ,
 regist_date 	 DATE          ,
 PRIMARY KEY (product_id));

数据类型

数据类型表示数据的种类，包括数字型、字符型和日期型等

  • INTEGER

    用来指定存储整数的列的数据类型（数字型），不能存储小数

  • CHAR

    CHAR 是 CHARACTER（字符）的缩写，是用来指定存储字符串的列的数据类型（字符型）

    可以像 CHAR(10) 或者 CHAR(200) 这样，在括号中指定该列可以存储的字符串的长度（最大长度）

  • VARCHAR

    同 CHAR 类型一样， 可通过括号内的数字来指定字符串的长度

    但该类型的列是以可变长字符串的形式来保存字符串的

  • DATE

    用来指定存储日期（年月日）的列的数据类型（日期型）

约束设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能，Product 表中设置了两种约束：

  • NOT NULL 约束

    NULL 是代表空白（无记录）的关键字A。在NULL 之前加上了表示否定的 NOT，就是给该列设置了不能输入空白，也就是必须输入数据的约束。

  • 主键约束

    如果把product _ id 列指定为主键，就可以通过该列取出特定的商品数据了。

表的删除（ DROP TABLE 语句）

DROP TABLE < 表名 > ；

示例，删除 Product 表：

1
DROP TABLE Product;

注：删除了的表是无法恢复的。在执行 DROP TABLE 语句之前请务必仔细确认。

表定义的更新（ ALTER TABLE 语句）

有时表创建后发现少了几列，这时无需把表删除再重新创建，只需使用变更表。ALTER 在英语中就是“改变”的意思。

  • 添加列的 ALTER TABLE 语句

    ALTER TABLE < 表名 > ADD COLUMN < 列的定义 > ；

    示例，添加一列可以存储100位的可变长字符串的 product_name_pinyin 列：

    1
    ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

  • 删除列的 ALTER TABLE 语句

    ALTER TABLE < 表名 > DROP COLUMN < 列名 > ；

    示例，删除 product_name_pinyin 列：

    1
    ALTER TABLE Product DROP COLUMN product_name_pinyin;

向 Product 表中插入数据

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN TRANSACTION; 

INSERT INTO Product VALUES ('0001', 'T恤衫',    '衣服',     1000, 500,   '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器',   '办公用品',  500, 320,    '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤',  '衣服',     4000, 2800,   NULL       );
INSERT INTO Product VALUES ('0004', '菜刀',     '厨房用具',  3000, 2800,  '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅',   '厨房用具',  6800, 5000,  '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子',     '厨房用具',  500, NULL,   '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板',    '厨房用具', 880, 790,     '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔',    '办公用品', 100, NULL,    '2009-11-11');

COMMIT;

注：在MySQL中运行，需要把BEGIN改写成START。

查询示例

从表中选取数据时需要使用 SELECT 语句，也就是只从表中选出（SELECT）必要数据的意思。SELECT 语句是 SQL 语句中使用最多的最基本的 SQL 语句。

列的查询

SELECT < 列名 > ，……
　 FROM < 表名 > ；

该 SELECT 语句包含了SELECT和 FROM 两个子句（clause） 。子句是 SQL 语句的组成要素，是以 SELECT 或者 FROM 等作为起始的短语。
SELECT 子句中列举了希望从表中查询出的列的名称，而FROM子句则指定了选取出数据的表的名称。

示例，从 Product 表中输出3列：

1
2
SELECT product_id, product_name, purchase_price
FROM Product;

结果：

1
2
3
4
5
6
7
8
9
10
product_id | product_name | purchase_price
-----------+--------------+---------------
0001 	   |  T恤衫        |           500
0002  	   |  打孔器       |           320
0003	   |  运动T恤      |          2800
0004       |  菜刀         |          2800
0005	   |  高压锅       |          5000
0006 	   |  叉子         |
0007  	   |  擦菜板       |           790
0008   	   |  圆珠笔       |

若要查询出表中所有的列，可以使用代表所有列的星号*。

SELECT 　 *
FROM < 表名 >;

为列设定别名

SQL 语句可以使用 AS 关键字为列设定别名。

示例：

1
2
3
4
SELECT product_id     AS id,
       product_name   AS name,
       purchase_price AS price
  FROM Product;

结果：

1
2
3
4
5
6
7
8
9
10
id    | name    | price
------+---------+-------
0001  |  T恤衫   |   500
0002  |  打孔器  |   320
0003  |  运动T恤 |  2800
0004  |  菜刀    |  2800
0005  |  高压锅  |  5000
0006  |  叉子    |
0007  |  擦菜板  |   790
0008  |  圆珠笔  |

别名也可以使用中文，使用中文时需要用双引号"括起来，如：

1
2
3
4
SELECT product_id     AS " 商品编号 ",
       product_name   AS " 商品名称 ",
       purchase_price AS " 进货单价 "
  FROM Product;

常数的查询

SELECT 子句中不仅可以书写列名，还可以书写常数，包括字符串常数 ，数字常数 ，日期常数。

1
2
3
SELECT ' 商品 ' AS string, 38 AS number, '2009-02-24' AS date,
       product_id, product_name
  FROM Product;

结果：

1
2
3
4
5
6
7
8
9
10
string   | number    | date         | product_id  | product_name
---------+-----------+--------------+-------------+--------------
商品      | 38        | 2009-02-24  | 0001         | T恤衫
商品      | 38        | 2009-02-24  | 0002         | 打孔器
商品      | 38        | 2009-02-24  | 0003         | 运动T恤
商品      | 38        | 2009-02-24  | 0004         | 菜刀
商品      | 38        | 2009-02-24  | 0005         | 高压锅
商品      | 38        | 2009-02-24  | 0006         | 叉子
商品      | 38        | 2009-02-24  | 0007         | 擦菜板
商品      | 38        | 2009-02-24  | 0008         | 圆珠笔

从结果中删除重复行

想知道 Product 表中保存了哪些商品种类（ product _ type ）时，可以通过在 SELECT 子句中使用DISTINCT 来实现。

示例：

1
2
SELECT DISTINCT product_type
  FROM Product;

结果：

1
2
3
4
5
6
product_type
---------------
厨房用具
衣服
办公用品
法

注：在使用 DISTINCT 时， NULL 也被视为一类数据。 NULL 存在于多行中时 ， 也会被合并为一条 NULL 数据。

DISTINCT 也可以在多列之前使用。此时会将多个列的数据进行组合，将重复的数据合并为一条。

示例：

1
2
SELECT DISTINCT product_type, regist_date
  FROM Product;

结果：

1
2
3
4
5
6
7
8
9
product_type  | regist_date
--------------+------------
衣服          | 2009-09-20
办公用品       | 2009-09-11
办公用品       | 2009-11-11
衣服          |
厨房用具       | 2009-09-20
厨房用具       | 2009-01-15
厨房用具       | 2008-04-28

根据 WHERE 语句来选择记录

SELECT 语句通过WHERE 子句来指定查询数据的条件。在 WHERE子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件。执行含有这些条件的 SELECT 语句，就可以查询出只符合该条件的记录了。

SELECT < 列名 >, ……
FROM < 表名 >
WHERE < 条件表达式 >;

示例，用来选取 product_type 列为 ‘ 衣服 ‘ 的记录：

1
2
3
SELECT product_name, product_type
  FROM Product
 WHERE product_type = ' 衣服 ';

结果：

1
2
3
4
product_name   | product_type
---------------+--------------
T恤衫           | 衣服
运动T恤         | 衣服

注：上面代码语句为了确认选取出的数据是否正确，通过SELECT 子句把作为查询条件的 product _ type 列也选取出来了，如果只想知道商品名称的话，可以只选取出 product _ name 列。

SQL 中子句的书写顺序是固定的，不能随意更改。 WHERE 子句必须紧跟在 FROM 子句之后，书写顺序发生改变的话会造成执行错误，如：

1
2
3
SELECT product_name, product_type
 WHERE product_type = ' 衣服 '
  FROM Product;

结果：

1
2
3
ERROR:  "FROM" 或者其前后有语法错误
第3行: FROM Product;
       ^

算数运算符

SQL 语句中可以使用计算表达式。

1
2
3
SELECT product_name, sale_price,
       sale_price * 2 AS "sale_price_x2"
  FROM Product;

结果：

1
2
3
4
5
6
7
8
9
10
product_name   | sale_price  | sale_price_x2
---------------+-------------+----------------
T恤衫           | 1000        | 2000
打孔器          | 500         | 1000
运动T恤         | 4000        | 8000
菜刀            | 3000        | 6000
高压锅          | 6800        | 13600
叉子            | 500         | 1000
擦菜板          | 880         | 1760
圆珠笔          | 100         | 200

注：

  • SQL 语句中可以使用的四则运算的主要运算符+，-，*，/。
  • 所有包含 NULL 的计算，结果都是 NULL，如：5+NULL，1*NULL，NULL/0等。

比较运算符

1
2
3
SELECT product_name, product_type
  FROM Product
 WHERE sale_price <> 500;

结果：

1
2
3
4
5
6
7
8
product_name  | product_type
---------------+--------------
T恤衫          | 衣服
运动T恤        | 衣服
菜刀           | 厨房用具
高压锅         | 厨房用具
擦菜板         | 厨房用具
圆珠笔         | 办公用品

SQL 中比较运算符有：等于：=，不等于：<>，大于：>，大于等于：>=，小于：<，小于等于：<=。

注：

  • 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

  • 不能对NULL使用比较运算符，需使用专门用来判断是否为 NULL 的IS NULL运算符，如：

    1
    2
    3
    SELECT product_name, purchase_price
      FROM Product
     WHERE purchase_price IS NULL;

    结果：

    1
    2
    3
    4
    product_name   | purchase_price
    ---------------+---------------
    叉子            |
    圆珠笔          |

    反之，希望选取不是 NULL 的记录时，需要使用IS NOT NULL 运算符

逻辑运算符

NOT运算符

NOT 不能单独使用，必须和其他查询条件组合起来使用，如：

1
2
3
SELECT product_name, product_type, sale_price
  FROM Product
 WHERE NOT sale_price >= 1000;

实际上第3句和WHERE sale_price < 1000;是等效的。

AND 运算符和 OR 运算符

示例：

1
2
3
4
SELECT product_name, purchase_price
  FROM Product
 WHERE product_type = ' 厨房用具 '
   AND sale_price >= 3000;

用法和各种编程语言如C语言中的逻辑运算符的功能类似。另外也可以使用括号，明确优先级顺序：

1
2
3
4
5
SELECT product_name, product_type, regist_date
  FROM Product
 WHERE product_type = ' 办公用品 '
   AND (  regist_date = '2009-09-11'
       OR regist_date = '2009-09-20');

结果：

1
2
3
4
product_name   | product_type | regist_date
---------------+--------------+------------
打孔器          | 办公用品      | 2009-09-11

真值

上面三个运算符 NOT 、 AND 和 OR 称为逻辑运算符，这里所说的逻辑就是对真值进行操作的意思。真值就是值为真（ TRUE ）或假（ FALSE ）其中之一的值。

另外，上面介绍了查询 NULL 时不能使用比较运算符，其结果既不是真也不是假，SQL为此定义了第三种值——不确定（UNKNOWN），因而存在特殊的三值逻辑运算。

P Q P AND Q P OR Q
真 真 真 真
真 假 假 真
真 不确定 不确定 真
假 真 假 真
假 假 假 假
假 不确定 假 不确定
不确定 真 不确定 真
不确定 假 假 不确定
不确定 不确定 不确定 不确定

注：原本只有 4 行的真值表，如果要考虑 NULL 的话就会像表 2-6 那样增加为3×3=9 行，看起来也变得更加繁琐，考虑 NULL 时的条件判断也会变得异常复杂，这与我们希望的结果大相径庭。因此，数据库领域的有识之士们达成了“尽量不使用 NULL ”的共识。
这就是为什么在创建 Product 表时要给某些列设置 NOT NULL 约束（禁止录入 NULL ）的缘故。

整理摘录自：SQL基础教程 第2版