SQL 必知必会

第1章 了解SQL

定义

• 结构化查询语言(Structured Query Language)

主键

• 定义

  • 一行或一组列，其值能够唯一标识表中每一行

• 要求

  • 任意两行不能有相同的主键值
  • 每一行都必须有一个主键值
  • 主键列中的值不能更改或更新
  • 主键值不能重用（一旦删除，不能够再赋予给以后的新行）

第2章 检索数据：SELECT

基本语法

• SELECT Col1,Col2, FROM table

选择全部列

• 星号 *

选择不同行

• DISTINCT

  • 作用于所有行，不仅仅是跟在其后的一行：即只要两行不是所有列完全相同，则会被检索出来。

限制查询结果行数

• SQL SERVER: TOP 5

  • SELECT TOP 5 student_name FROM Students

• MYSQL :LIMIT 5 OFFESET 3

  • SELECT prod_name FROM Products LIMIT 5 OFFSET 5
  • 可以简写为LIMIT 3,5
  • 索引序号从0开始

第3章 排序检索数据：ORDER BY

语句位置

• SELECT 语句的最后一条

指定排序列的方式

• 按列位置（序号）排序

  • '位置'指的是SELECT语句后的列排序顺序，而不是原表中的顺序

  • 序号从1开始

  • 缺点

    • 对SELECT 清单修改时，如果没有同时修改ORDER BY的序号，容易出错。

• 指定准确列名

  • SELECT .....
    ORDER BY col1,col2

指定顺序

• DESC/ASC

  • 位置

    • 对应列名后

  • 默认为ASC，可省略

第4章 过滤数据：WHERE

基本操作符

• 不等于：<>/!=

  • MSSQL和MYSQL两者都可使用
  • 可以用于字符串匹配检查

• 不大于，不小于：!>， !<

• BETWEEN

  • 支持字符串，日期/时间，数值的比较

• NULL值的比较检测

  • IS NULL/IS NOT NULL

    • 专门用于NULL值得匹配

  • <=>

    • 当且仅当两个值都为NULL时才返回NULL

  • 不能使用=/<>来对NULL值进行比较或检验

  • MYSQL的NULL值处理(RUNOOB)

第5章 高级数据过滤

AND

OR

• 注意：AND优先级高于OR。多使用括号确立优先级，即使知晓优先级。

IN

• 语法

  • col in (a,b,c) 等价于col1 =a or col1 = b or col =c

• 优点

  • 比对应的OR语句更快
  • 更直观
  • 配合其他select查询进行嵌套

NOT

• 作用

  • 否认其后的条件

• 位置

  • 位于其他判断关键词前：NOT IN

第6章 通配符：LIKE

%

• 作用

  • 任意字符出现任意次数

    • 任意字数：可以匹配0个字符

    • 一个使用场景：字段后填补的空格的解决

      • 在匹配字符串后再加一个%，来匹配空格

• 语法

  • SELECT *
    FROM Customers
    WHERE cust_name LIKE 'F%'

• 不匹配NULL

  • '%'不会匹配为NULL的行

• ACCESS 使用的是*

下划线_

• 作用

  • 仅匹配单个字符，且不可匹配0个字符

字符集匹配

• [ ]

  • 作用

    • 匹配字符集

  • 使用

    • SQL SERVER

      • SELECT *
        FROM Customers
        WHERE cust_name LIKE '[FV]%';

  • 语法

    • SELECT *
      FROM Customers
      WHERE cust_name RLIKE '^[FV]';

• 正则表达式RLIKE(FOR MYSQL)

  • MYSQL不支持使用[]匹配字符集

  • MySQL 中使用 REGEXP 或 NOT REGEXP (或 RLIKE 和 NOT RLIKE) 和正则表达式来实现字符串匹配。

  • 语法

    • SELECT *
      FROM course
      WHERE Tno REGEXP '^[张王].*'

  • RUNOOB链接

^

• 作用

  • 对[ ]进行否定：即不在方扩内的字母才能够匹配

• 使用

  • [^JMK]

注意点

• 使用技巧

  • ① 不要过度使用通配符
  • ② 尽量不要在搜索模式的开始使用通配符
  • ③ 特别注意通配符位置的正确使用
  • ④默认不区分大小写

第7章 创建计算字段

位置

• 紧贴在SELECT后

别名（ALIAS）

• 作用

  • 一个字段或值的替换名
  • 为拼接等字符操作创建的字段提供名称

• 使用

  • AS

    • MYSQL，MSSQL都可以省略 AS

拼接字段（concatenate)

• SQL SERVER

  • +（使用与Python相似）

• Oracle，SQLite等

  • ||

• MySQL，MariaDB

  • Concat()语句

    • SELECT CONCAT(id, name, work_date)
      FROM employee_tbl;

算术计算

• +-*/
• ()改变计算顺序

第8章 使用函数处理数据

DBMS差异：函数在各DBMS的通用度较低，只有少数函数通用。

• 可移植

  • 代码可在不同系统上运行
  • 众多程序员建议只使用可移植的函数

• 建议

  • 使用函数时，尽量进行注释，以帮助其他DBMS系统的使用者理解。

使用函数

• 字符处理

  • TRIM/RTRIM/LTRIM

    • 去除空格

  • UPPER/LOWER

    • 转化为大/小写

  • LEFT/RIGHT

    • 返回字符左/右指定长度的字符

    • 使用

      • LEFT('ZENG'',2)
        返回： ‘ZE'

  • SOUNDEX

    • 匹配发音类似的项

    • 使用

      • SOUNDEX(colname) = SOUNDEX('charact')

• 日期和时间处理

  • 可移植性最差
  • ONENOTE有网页裁剪

• 数值处理函数

  • ABS

    • 绝对值

  • EXP

    • 自然底数的指数函数

  • SQRT

    • 平方根

  • PI

    • 圆周率

  • COS/SIN/TAN

    • 三角函数

  • MYSQL数值函数(RUNOOB)

第9章 汇总数据

汇集数据

• COUNT

  • 计行数

    • count(*)

      • 返回总行数

    • count(col_name)

      • 返回col_name中非空行数

• MAX

• MIN

• AVG

• SUM

• MIN,MAX,AVG,SUM的括号内都能进行列之间的数值计算

聚集函数内部的可用参数

• DISTINCT

  • 作用

    • 聚集不同的值

  • 用法

    • AGG_FUNC(DISTINCT col_name)

• TOP/TOP PERCENT

  • 仅支持sqlserver

组合聚集函数

• 一个SELECT语句可以包含多个聚集函数

• 举例

  • SELECT COUNT(*) AS num_itesm,
    MIN(prod_price) AS min_price

第10章 分组数据

创建分组:GROUP BY

• 位置

  • WHERE后，ORDER前

• 注意点

  • ONLY_FULL_GROUP_BY参数

    • 参数为打开状态时

      • 除聚集语句（COUNT,SUM,MAX等），所有SELECT中出现的列名，都必须在GROUP BY中出现。

过滤分组:HAVING

• 与WHERE的关系

  • 基本语法与WHERE一致，但位置位于GROUP BY后
  • WHERE 过滤行，而HAVING过滤组

• 使用

  • 一定是配合GROUP BY使用，一定是对分组进行过滤

  • 可以与WHERE共同使用（两者对行和分组进行过滤，不互斥）

  • 举例

    • SELECT col1,FUC(col2)
      FROM table1
      WHERE col3>0
      GROUP BY col1
      HAVING FUNC(col2) >0
    • SELECT order_num,SUM(quantity*item_price) as total_price
      FROM OrderItems
      GROUP BY order_num
      HAVING total_price>1000

第11章 使用子查询

定义

• 嵌套在其他查询中的查询

作用

• 利用子查询进行过滤

  • 使用

    • SELECT col1
      FROM table1
      WHERE col2 = (SELECT col2
      FROM table2
      WHERE con1)

  • 注意

    • 子查询的SELECT只能用来返回单列，不能返回多个列

• 将子查询做为一个字段

  • 使用

    • SELECT cust_name,
      cust_state,
      (SELECT COUNT(*)
      FROM Orders
      WHERE Orders.cust_id=Customers.cust_id) AS orders
      FROM Customers
      ORDER BY cust_name

  • 注意

    • WHERE条件处要使用完全限定名
    • 等价联结语句

第12章 连接表

联结表

• 内联结

  • 使用

    • 内联结(inner join)

      • SELECT vend_name,prod_name,prod_price
        FROM Vendors INNER JOIN Products
        ON Vendors.prod_id = Products.prod_id

        • 解释

          • SELECT 的列后的各列名，来自两个以上的不同表
            FROM 各名来自的表名
            WHERE语句指示匹配列（注意使用带表名的完全限定名）

            • 缺少WHERE/ON的联结条件语句，将返回m*n行的笛卡尔积表。m,n为两表各自的行数。

    • 等值语句

      • SELECT vend_name,prod_name,prod_price
        FROM Vendors,Products
        WHERE Vendors.prod_id = Products.prod_id

    • 联结多个表

      • 可以通过多个联结连接多个表

  • 可用于替代子查询

    • 子查询

      • SELECT cust_name,cust_contact
        FROM Customers
        WHERE Customers.cust_id in(
        SELECT cust_id
        FROM Orders
        WHERE order_num in(
        SELECT order_num
        FROM OrderItems
        WHERE prod_id='RGAN01'
        )
        )

    • 等价内联结语句

      • SELECT cust_name,cust_contact
        FROM Customers
        WHERE Customers.cust_id in(
        SELECT cust_id
        FROM Orders
        WHERE order_num in(
        SELECT order_num
        FROM OrderItems
        WHERE prod_id='RGAN01'
        )
        )

  • 使用建议及注意

    • 根据SELECT 需求的列选择最少的表格及联结
    • 表格尽量少，使用的联结尽量少。 以节省运算力
    • 需满足引用完整性

第13章 高级联结

表别名

• 在FROM语句中定义

• SQL SERVER中，FROM语句最先运行，因此表别名可在SELECT语句中的任意阶段被使用。

• 作用

  • 缩短SQL语句
  • 可以在一条语句中使用同一表多次，且避免歧义

使用不同类型的联结

• 自联结

  • 需要自己控制返回的列，来避免列重复

  • 使用场景

    • 替代需要自身子查询的场景

      • SELECT *
        FROM OrderItems oi1,OrderItems oi2
        WHERE oi1.order_item=oi2.order_item AND
        oi2.prod_id='BR01'

• 自然联结

  • SELECT *
    FROM Customers NATURAL JOIN Orders

• 自然连接和内联结的关系和区别

  • 自然联结是特殊的内联结，自然连接自动寻找相同的列名作为匹配项
  • 内连接自己指定联结列（ON/WHERE指定），SELECT 后为*。
  • 两者返回的列数相同。
  • 但自然联结不包含两表用作匹配的列不相同的行，而inner join包含不同的列。
  • INNER JOIN 配合USING(col list) 可以达到与natural join 相同的结果
  • 小总结：NATURAL JOIN=SELECT * FROM TABLE1,TABLE2 WHERE table1.同名columns=table2.同名columns
  • 详见diigo标注部分

• 外联结

  • 与内联结区别

    • 包含了那些在相关表中没有关联的行

  • 使用

    • LEFT OUTER JOIN

    • RIGHT OUTER JOIN

    • FULL OUTER JOIN

      • MSSQL 支持
      • MYSQL,ACCESS,SQLite等不支持

第14章 组合查询

UNION

• 作用

  • 组合多个查询结果
  • 每个组合查询都有等效的单个使用复WHERE的语句

• 使用

  • SELECT col1,col2,FUNC(col3)
    ...
    UNION
    SELECT col1,col2,FUNC(col3)

• 使用注意点

  • ①包含两个及两个以上的SELECT语句
  • ②每个查询都具有相同的列名或别名，表达式或聚集函数（不要求顺序一致）
  • ③列类型必须可兼容（不一定相同，但需要可相互转换）
  • ④查询多个表时，若表名不匹配可以使用别名，别名一致即可，不要求原列名一致。
  • ⑤使用ORDER BY，只需在最后一个SELECT语句中指定排序方式即可作用在整个查询结果上

UNION ALL

• UNION ALL保留各个SELECT语句中相同的行（UNION和等效的WHRER语句都默认去除）

EXCEPT/MINUS

• 检索只在第一个SELECT检索的表中存在而不在第二个SELECT查询结果中的行

• 举例

  • SELECT cust_id,cust_name,cust_contact
    FROM Customers
    WHERE cust_name='FUn4All'
    EXCEPT
    SELECT cust_id,cust_name,cust_contact
    FROM Customers
    WHERE cust_contact='Jim Jones'

• 注意

  • ①仅部分DBMS支持
  • ②MSSQL使用EXCEPT

INTERCEPT

• 检索在两个表格中都存在的行（交集∩）

• 注意

  • ①部分DBMS支持
  • ②MSSQL支持
  • ③可以使用FULL OUTER JOIN 代替

第15章 插入数据

INSERT

• 插入完整的行

  • 实例

    • INSERT INTO table1(col1_name,col2_name,..,coln_name)
      VALUES(col1_value,col2_value,...,coln_value);

      • 注意

        • 两个tuple的顺序互相一致即可，不需要与原表的列顺序一致
        • table1后的表名tuple可以省略。但不建议，因为省略该tuple后VALUES的列tuple顺序必须与table1的实际列顺序一致。

• 插入部分行

  • 允许条件

    • 被省略的列允许NULL或者设置了默认值

  • 实例

    • 与插入完整行相似，只是两个tuple可以不完整。
    • INSERT INTO后的tuple不可省略

• 注意

  • 一个INSERT INTO 语句只能插入一行内容

INSERT INTO ...SELECT

• 实例

  • INSERT INTO table1(col1_name,col2_name,..,coln_name)
    SELECT col1_value,col2_value,...,coln_value
    FROM table2
    WHERE...

    • 注意

      • SELECT语句后的列名序列不需要使用括号
      • 两个列名序列的名称不要求一致
      • 两个列名序列的位置必须正确

• 注意

  • INSERT INTO...SELECT是特殊的INSERT INTO 语句。可以根据SELECT返回结果的行数插入数据。即可以一次性插入多行。

SELECT INTO

• 作用

  • 将SELECT选出的表插入一个新创立的table1中

• 实例

  • SELECT col_name_tuple
    INTO table1
    FROM table2,table3
    WHERE...
    GROUP BY...
    ORDER BY...

    • 注意

      • 所有SELECT语句中可用的子语句和方法都可以用
      • table1是在该语句中新创立的表

• 使用场景

  • 测试新的SQL语句前，创立一个新的表格用做测试

• MYSQL不支持SELECT INTO

  • MYSQL 如何创建一个表的复制？：

    • show create table orders + insert into ... SELECT

第16课 更新和删除数据

更新数据

• UPDATE SET

  • 实例

    • UPDATE table1
      SET col_name1 = col_value1,
      col_name2 = col_value2
      WHERE cons

• UPDATE SELECT

  • 基本格式

    • UPDATE A a INNER JOIN B b ON b.key_id = a.key_id
      SET a.val = 2
      WHERE b.satisfy = 1

  • LEFT JOIN/RIGHT JOIN 配合GOURP BY 来修改表格

    • UPDATE course c
      RIGHT JOIN (
      SELECT Cno,AVG(score) avg_score
      FROM sc
      GROUP BY Cno
      HAVING avg_score>=75
      ) sc
      ON c.Cno=sc.Cno
      SET c.Tno="厉害老师";

SELECT *
FROM course;

• UPDATE FROM

删除数据

• 删除某表中的部分数据

  • DELETE FROM

    • DELETE FROM table
      WHERE cons

• 删除全部数据

  • DELETE FROM table1

  • TRUNCATE table1

    • 比DELETE更快

使用更新和删除注意点

• 建议UPDATE或删除前使用SELECT进行测试，保证进行正确的操作（对WHERE语句）
• 务必考虑并实施引用完整性的数据库
• 部分DBMS有强制使用WHERE的选项，尽量使用

第17章 创建和操纵表

创建表

• 实例

  • CREATE TABLE table_name
    (col1_name char(10) NOT NULL,
    col2_name DECIMAL(8,2) NULL,
    ...
    coln_name (data_type) (NULL/NOT NULL)
    )

• 注意点

  • 大部分DBMS NULL/NOT NULL一项默认NULL，因此NULL可省略

  • 设定默认值：对应列的NULL/NOT NULL后添加DEFAULT default_value（常使用日期函数做为默认值）

    • 日期函数

      • MSSQL

        • GETDATE()

      • MYSQL

        • CURRENT_DATE()

  • 注意一个字段的名称，类型，是否为空之间是不需要空格的。

• 建议

  • 尽量给定DEFAULT值，而定义为NULL

更新表

• 注意点

  • 尽量在创立表时，就考虑好未来需求，减少表的修改。
  • 大多数表支持新增列或者更改无内容填充的列的操作
  • 大多数表不支持 删除或更改表中的列，重新命名表中的列，对已有内容填充的表的修改

• 示例

  • 新增列

    • ALTER TABLE table1
      ADD col_name data_type NULL/NOTNULL
    • 大多数DBMS支持

  • 删除列

    • ALTER TABLE table1
      DROP col_name
    • DBMS 支持通用度不高

  • 修改表

    • ALTER CHANGE
    • ALTER MODIFY

  • 修改表名

    • ALTER TABLE testalter_tbl RENAME TO alter_tbl;

  • 复杂表更改的一般流程

    • 按照新需求创建一个新的表格
    • 复制原表仍需要的数据内容到新表（UPDATE SELECT)
    • 检查插入数据后的新表
    • 更改原表表名或直接删除
    • 将新表更改为原表表名
    • 根据需要，重新建立触发器，储存过程，索引和外键

  • 注意点

    • ALTER的使用需尽量小心，更改前做好备份

  • RUNNOOB关于ALTER TABLE

删除表

• DROP table1

  • 遵循引用完整性的等关系规则

• MYSQL

  • DROP TABLE table_name

重命名表格

• sp_rename 储存过程

  • MSSQL

• RENAME

  • MSSQL,SQLite外的大多数DBMS

第18课 使用视图

视图作用

• 重用SQL语句，简化操作
• 权限管理，只允许查看视图内容，而非整个表
• 展示与原表不同的数据格式和内容

注意

• 每次使用视图，都会重新运行一次查询。因此复杂视图会影响性能

限制规则

• 命名唯一

• 可进行权限管理

• 可以嵌套（但嵌套会提高复杂度，占用更多运算力）

• 众多DBMS进制在视图的定义时的查询语句中使用ORDER BY

  • SQL SERVER不允许。但是如果SELECT语句后添加TOP 关键字，则可以使用。

• 部分DBMS将视图设置为只读，即不可更新视图内容并写入底层原表

• 部分DBMS允许创建一种视图，视图限制原表内容修改会导致视图行数减少的操作

• 参照具体的DBMS

创建视图

• CREATE VIEW view_name AS
  SELECT 语句

使用建议

• 创建不绑定特定数据的视图（如限定某个id为具体值），而在使用视图时再进行限定。

第19课 使用储存过程

定义

• 为以后使用而保存的一条或多条SQL语句。可以视为批处理（但不只是批处理）

使用

• 计Customers中带邮件的行数

  • 创建

    • CREATE PROCEDURE MailingListCounting
      AS
      DECLARE @cnt INTEGER
      SELECT @cnt = COUNT(*)
      FROM Customers
      WHERE NOT cust_email IS NULL

  • 使用

    • DECLARE @RETURNVALUE INT
      @RETURNVALUE = MailingListCounting
      SELECT @RETURNVALUE

• 在ORDERS表单插入新订单

  • 创建方法①

    • CREATE PRODUCE NewOrder @cust_id CHAR(10)
      AS
      DECLARE @order_num INTEGER
      SELECT @order_num=MAX(order_num)
      FROM Orders
      SELECT @order_num = @order_num+1
      INSERT INTO Orders(order_num,order_date,cust_id)
      VALUES(@order_num,GETDATE(),@cust_id)

RETURN @order_num

- 创建方法②

    - CREATE PRODUCE NewOrder @cust_id CHAR(10)

AS
INSERT INTO Orders(cust_id)
VALUES(cust_id)

SELECT cust_num = @@IDENTITY

        - 方法②的日期由创建表格时的DEFAULT GETDATE()给出

order_num由标识字段(identity field)给出

注意点

• 使用频率远高于创建，创建与使用权限可分隔

第20课 管理事务处理

事务处理

• 可回退的语句

  • INSERT
  • UPDATE
  • DELETE

• 不可回退的语句

  • SELECT
  • DROP
  • CREATE

控制事务处理

• 事务（TRANSCATION)

  • BEGIN TRANSCATION
    ...
    COMMIT TRANSCATION
  • 省略号部分，要么全部执行，如果中间某句出错，则全部不执行

• 回滚(ROLLBACK)

  • 作用

    • 使数据库回到上次COMMIT或者TRANCATION的状态

• 保留点(SAVE)

  • 实例

    • SAVE TRANSACTION delete1;

ROLLBACK TRANSACTION delete1:

- 注意

    - 每个回滚点的名称必须唯一（本实例为delete1）
    - 常用回滚条件：

IF @@ERROR <>0 ROLLBACK TRANSCATION delete1;

补充

ONLY_FULL_GROUP_BY

• Mysql的WHERE 语句的联结Group by似乎不要求聚合函数外的所有列。但JOIN ON 要求有

  • SELECT c.cust_id,c.cust_name,c.cust_state,COUNT(o.order_num) nums
    FROM Customers c,Orders o
    WHERE c.cust_id=o.cust_id
    GROUP BY c.cust_id;

  • ANSWER

    • sql_mode下的ONLY_FULL_GROUP_BY默认打开

大小写区分

• 默认大小写不区分，但可以通过设置更改为敏感

注释

• 符号

  • 单行注释

    • -- comments

  • 多行注释

    • /* comments1
      comments2 */

数值型补零前后的值大小相同

• 3.49,3.490大小相同，条件筛选不用区分

MSSQL语句顺序

• 运行顺序

  • (1) FROM < left_table>

(3) < join_type> JOIN < right_table> (2) ON < join_condition>

(4) WHERE < where_condition>

(5) GROUP BY < group_by_list>

(6) WITH {cube | rollup}

(7) HAVING < having_condition>

(8) SELECT (9) DISTINCT (11) < top_specification> < select_list>

(10) ORDER BY < order_by_list>

    - FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

        - 理解：FROM 中定义的别名，可以全局引用

• 书写顺序

  • SELECT DISTINCT
    FROM
    JOIN
    ON
    WHERE
    GROUP BY
    HAVING
    ORDER
    LIMIT

• 因此别名在SELECT创建，则只能在SELECT和ORDER BY语句中使用。

NULL不是空字符串

• NULL是没有值，而“”可以理解为0长度的字符串

SQL 必知必会.png