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

SQL必知必会