SQL基础教程（第2版）笔记整理
花了一段时间把SQL基础教程（第2版）看完，并把笔记整理好。
数据定义语言（Data Define Language）
数据操作语言（Data Manipulation Language）
数据控制语言（Data Control Language）
表的创建
CREATE TABLE 表名
(列名 数据类型 完整性约束);
表的删除与更新
DROP TABLE 表名; — 删除表
ALTER TABLE 表名 ADD 列名 数据类型; — 增加列名
ALTER TABLE 表名 DROP列名; — 删除列名
ALTER TABLE 表名 MODIFY 旧列名 数据类型; — 修改列名
ALTER TABLE 表名 CHANGE 新列名 数据类型; — 修改列名
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT; — 删除默认
ALTER TABLE 表名 RENAME TO 新表名; — 修改表名
ALTER TABLE 表名 MODIFY 列名 数据类型 COMMENT \’\’； –添加备注
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
数据的插入
INSERT语句
INSERT INTO <表名> ( 列1 ,列2 , 列3 , …… ) VALUES ( 值1 , 值2 , 值3 , …… );
数据的删除
保留数据表，仅删除全部数据行的 DELETE 语句
DELETE FROM < 表名 >;
TRUNCATE < 表名 >;
删除部分数据行的搜索型 DELETE
DELETE FROM < 表名 > WHERE < 条件 >;
数据的更新
改变表中数据的 UPDATE 语句
UPDATE < 表名 > SET < 列名 > = < 表达式 > WHERE < 条件 >;
数据的查询
查询标准语句格式：
SELECT <列名> FROM <表名>;
查询所有列：* 代表 所有
SELECT * FROM <表名>;
为列设定别名：AS（设定汉语别名时需要必须用双引号（”）括起来）
SELECT product_id AS id, product_name AS name FROM product;
从结果中去掉重复行：DISTINCT （关键字只能用在第一个列名之前）
SELECT DISTINCT product_type AS p_type, product_name FROM product;
聚合函数中使用DISTINCT
SELECT COUNT(DISTINCT product_type) FROM Product;
常数 的查询：（ NULL 也被视为一类数据）
表达式：SELECT <常数值> AS <显示字段名> FROM product;
SELECT \’ 商品 \’ AS string, 38 AS number, \’2009-02-24\’ AS date,
product_id, product_name
FROM product;
根据 WHERE 语句来选择记录：
SELECT <列名> FROM <表名> WHERE <条件>
注释 的书写方法:
1行注释
书写在“–”之后，只能写在同一行。
多行注释
书写在“/*”和“*/”之间，可以跨多行。
算数运算符
+ – * /（加减乘除）
注意：所有包含 NULL 的计算，结果肯定是 NULL
SQL语句中也可以使用运算表达式：
SELECT product_name, sale_price, sale_price * 2 AS “sale_price_x2” FROM Product;
比较运算符（使用比较运算符时一定要注意不等号和等号的位置。）
= 相等 <> 不等于
>= 大于等于 <= 小于等于
> 大于 < 小于
字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
逻辑运算符
NOT 、 AND 、OR
多个查询条件 进行组合时，需要使用 AND 运算符或者 OR 运算符。
AND 运算符的优先级高于 OR 运算符。想要优先执行 OR 运算符时可以使用括号。
GROUP BY：（对表进行分组）
使用聚合函数和 GROUP BY 子句时需要注意以下4点。
① 只能写在 SELECT 子句之中
② GROUP BY 子句中不能使用 SELECT 子句中列的 别名
③ GROUP BY 子句的聚合结果是 无序 的
④ WHERE 子句中不能使用聚合函数（SELECT子句和HAVING子句以及ORDER BY子句可以）
当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据
GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序
FROM → WHERE → GROUP BY → SELECT
GROUP BY表达式
SELECT < 列名 1>, < 列名2>, < 列名3>, ……
FROM < 表名 >
WHERE <条件>
GROUP BY <列名 1>, < 列名2>, < 列名3>, ……;
HAVING子句 （HAVING 子句必须写在 GROUP BY 子句之后）
WHERE 子句 = 指定 行 所对应的条件
HAVING 子句 = 指定 组 所对应的条件
SELECT < 列名 1>, < 列名 2>, < 列名 3>, ……
FROM < 表名 >
GROUP BY < 列名 1>, < 列名 2>, < 列名 3>, ……
HAVING < 分组结果对应的条件 >
例子：
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
ORDER BY 子句（默认升序，DESC降序，ASC升序）（可以使用 SELECT 子句中定义别名）
SELECT < 列名 1>, < 列名 2>, < 列名 3>, ……
FROM < 表名 >
ORDER BY < 排序基准列 1>, < 排序基准列 2>, ……
子句的书写顺序
1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →
5. HAVING 子句 → 6. ORDER BY 子句
LIMIT 1,3
函数
函数大致可以分为以下几种：
● 聚合函数（用来进行数据聚合的函数）
COUNT ： 计算表中的记录数（行数）
SELECT COUNT(列名) FROM Product; （下列函数表达式相同）
SUM ： 计算表中数值列中数据的合计值
AVG ： 计算表中数值列中数据的平均值
MAX ： 求出表中任意列中数据的最大值
MIN ： 求出表中任意列中数据的最小值
● 算术函数（用来进行数值计算的函数）
ABS 绝对值函数:ABS( 数值 )
SELECT m, ABS(m) AS abs_col FROM SampleMath;
MOD 求余函数:MOD( 被除数，除数 )
ROUND 四舍五入函数:ROUND( 对象数值，保留小数的位数 )
● 字符串函数（用来进行字符串操作的函数）
|| 拼接字符串函数:字符串 1 || 字符串 2
(MYSQL中使用:CONCAT(str1, str2))
LENGTH 字符串长度函数:LENGTH(str)
MySQL 中的LENGTH 这样以字节为单位的函数进行计算时
● 日期函数（用来进行日期操作的函数）
CURRENT_DATE 当前日期函数：CURRENT_DATE
SELECT CURRENT_DATE;
CURRENT_TIME 当前时间函数：CURRENT_TIME
EXTRACT 当前日期和时间函数：EXTRACT( 日期元素 FROM 日期 )
● 转换函数（用来转换数据类型和值的函数）
LOWER小写转换函数：LOWER( 字符串 )
REPLACE 字符串的替换函数：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING 字符串的截取函数：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
UPPER 大写转换函数：UPPER( 字符串 )
CAST 类型转换函数：CAST （转换前的值 AS 想要转换的数据类型）
COALESCE 将 NULL 转换为其他值函数：COALESCE( 数据 1 ，数据 2 ，数据 3 …… )
谓词
谓词包括以下：
● LIKE （模糊匹配）
SELECT * FROM product LIKE ‘%s’; （后面匹配）s%’ （前面匹配） ‘%s%’（中间匹配）
‘_s_’ （三位且中间是s的） ‘_s’ （两位且结尾是s的） ‘s_’ （两位且开头是s的）
● BETWEEN （范围查询）
SELECT * FROM product WHERE sale_price BETWEEN 100 AND 1000;
● IS NULL 、 IS NOT NULL （判断是否为 NULL）
SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;
● IN 、 NOT IN（ OR 的简便用法）
SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000);
● EXISTS
CASE表达式
— 不写默认为NULL，尽量写上
— END不能省略
SELECT product_name,
CASE WHEN product_type = \’ 衣服 \’
THEN \’A ： \’ | | product_type
WHEN product_type = \’ 办公用品 \’
THEN \’B ： \’ | | product_type
WHEN product_type = \’ 厨房用具 \’
THEN \’C ： \’ | | product_type
ELSE NULL
END AS abc_product_type
FROM Product;
例子：
SELECT SUM(CASE WHEN product_type = \’ 衣服 \’
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = \’ 厨房用具 \’
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = \’ 办公用品 \’
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;
集合运算
表的加法—— UNION（并集）
SELECT product_id, product_name, sale_price FROM Product
UNION
SELECT product_id, product_name, sale_price FROM Product2
ORDER BY sale_price;
注意事项① —— 作为运算对象的记录的列数必须相同
注意事项② —— 作为运算对象的记录中列的类型必须一致
注意事项③ —— 可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次
选取表中公共部分—— INTERSECT（交集）
SELECT product_id, product_name, sale_price FROM Product
INTERSECT
SELECT product_id, product_name, sale_price FROM Product2
ORDER BY sale_price;
包含重复行的集合运算—— ALL 选项
SELECT product_id, product_name FROM Product
UNION ALL
SELECT product_id, product_name FROM Product2;
记录的减法—— EXCEPT
SELECT product_id, product_name FROM Product
EXCEPT
SELECT product_id, product_name FROM Product2
ORDER BY product_id;
联结
联结（ JOIN ）就是将其他表中的列添加过来，进行“添加列”的集合运算。
UNION 是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位
进行的。
将两张表进行内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price
FROM ShopProduct AS SP
JOIN Product AS P ON SP.product_id = P.product_id;
内联结要点① —— FROM 子句
第一点要注意的是，之前的 FROM 子句中只有一张表，而这次我们同时使用了 ShopProduct 和 Product 两张表。
FROM ShopProduct AS SP INNER JOIN Product AS P
使用关键字 INNER JOIN 就可以将两张表联结在一起了。 SP 和 P
分别是这两张表的别名，但别名并不是必需的。在 SELECT 子句中直接使用 ShopProduct 和 product _ id 这样的表的原名也没有关系，但由于表名太长会影响 SQL 语句的可读性，因此还是希望大家能够习惯使用别名。
内联结要点② —— ON 子句
第二点要注意的是 ON 后面的联结条件。
进行内联结时必须使用 ON 子句，并且要书写在 FROM 和 WHERE 之间。
内联结要点③ —— SELECT 子句
第三点要注意的是，在 SELECT 子句中指定的列。
使用联结时 SELECT 子句中的列需要按照“ < 表的别名 > . < 列名 > ”的格式进行书写。
外联结 —— OUTER JOIN
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;
外联结中使用 LEFT 、 RIGHT 来指定主表。使用二者所得到的结果完全相同。
交叉联结 —— CROSS JOIN
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;
事务
事务就是需要在同一个处理单元中执行的一系列更新处理的集合
事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
要点：
1.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
2.事务处理可以用来维护数据库的完整性，保证SQL语句要么全部执行，要么全部不执行。
3.事务用来管理 insert,update,delete 语句
事务控制语句：
BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
MYSQL 事务处理 主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
MYSQL事务
BEGIN;
UPDATE sc SET Grade = 81 WHERE Grade = 80;
DELETE FROM student WHERE Sname = \’陈冬\’;
INSERT INTO student VALUE(9521105, \’陈冬\’, \’男\’, 19, \’数学系\’);
UPDATE student SET Sage = 20 WHERE Sname = \’陈冬\’;
COMMIT;
复杂查询
视图（将经常使用的 SELECT 语句做成视图。）
创建视图的 CREATE VIEW 语句
CREATE VIEW 视图名称 (< 视图列名 1>, < 视图列名 2>, …… )
AS
<SELECT 语句 >
删除视图
DROP VIEW 视图名称
例子：
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
子查询
子查询作为内层查询会首先执行。
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product GROUP BY product_type ) — 先执行FROM子句中的SELECT子句
AS ProductSum;
标量子查询 就是返回 单一值 的子查询，绝对不能返回 多行 结果。
能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，只要使用 单一值 的几乎所有的地方都可以使用。
SELECT product_id, product_name, sale_price,
(SELECT AVG(sale_price) FROM Product) — 标量子查询作为列名
AS avg_price
FROM Product;
关联子查询 在细分的 组内 进行比较时，需要使用关联子查询。
SELECT product _type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type = P2.product_type — 关联点
GROUP BY product_type);