SQL学习笔记（二）

1、SQL 语句及其种类：

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

用来创建或者删除存储数据用的数据库以及数据库中的表等对象

CREATE： 创建数据库和表等对象

DROP： 删除数据库和表等对象

ALTER： 修改数据库和表等对象的结构

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

用来查询或者变更表中的记录。（实际使用的SQL 语句当中有90% 属于DML）

SELECT：查询表中的数据

INSERT：向表中插入新数据

UPDATE：更新表中的数据

DELETE：删除表中的数据

（3）DCL（Data Control Language，数据控制语言）

用来确认或者取消对数据库中的数据进行的变更。还可以对RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。

COMMIT： 确认对数据库中的数据进行的变更

ROLLBACK： 取消对数据库中的数据进行的变更

GRANT： 赋予用户操作权限

REVOKE： 取消用户的操作权限

2、运算符类型

（1）算术运算符：加减乘除 +、-、*、/ （注意：所有包含NULL 的计算，结果肯定是NULL）

（2）比较运算符：=、<>、>、>=、<、<= （注意：字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆；不能对NULL使用比较运算符，可以在条件表达式中使用IS NULL、IS NOT NULL）。BETWEEN匹配的区间，包括开始值和结束值。

（3）逻辑运算符：NOT、AND、OR。（WHERE子句中可以包含很多个AND、OR连接的条件，但AND的执行顺序优先于OR，有时候为了防止歧义，需要用圆括号（）括起来分组。因为圆括号比AND、OR有更高的优先级。）

3、聚合函数

COUNT、SUM、AVG、MAX、MIN

COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

4、分组 GROUP BY

GROUP BY可以后接几个列名，在建立分组时，指定的所有列都一起计算。

GROUP BY中的每一列都必须是检索列或有效表达式（但不能是聚集函数）。如果在SELECT语句中使用了表达式，GROUP BY中需要使用相同的表达式，不能用别名。

常见错误：在SELECT子句中书写了聚合键以外的列；在GROUP BY子句中写了列的别名；在WHERE子句中使用聚合函数（只有SELECT子句、HAVING子句、ORDER BY子句中能够使用聚合函数。）错误示例：

SELECT product_name, purchase_price, COUNT(*)  -- product name是多余的列，报错

FROM Product

GROUP BY purchase_price;

SELECT product_type, COUNT(*)

FROM Product

WHERE COUNT(*) = 2 --where 子句中不应该使用聚合函数，会报错

GROUP BY product_type;

5、过滤 HAVING

HAVING子句的构成要素：常数、聚合函数、GROUP BY子句中指定的列名（即聚合键）

错误示例：

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type

HAVING product_name = '圆珠笔';  --product_name 列并不包含在GROUP BY 子句之中，因此不允许写在HAVING 子句里，报错

6、排序 ORDER BY

OERDER BY 默认是升序，如果想要降序，需要在列的名字后面加上 DESC。如果想要每个列都降序，每个列后面都要加上DESC。ORDER BY A, B 表示先按A列排序 再按B列排序。如果A列每行都是不同的，则不会按B列排序。举例： ORDER BY prod_price desc，prod_name 先按商品价格降序排列，再按商品名称升序排列。

ORDER BY必须是SELECT语句的最后一条。在ORDER BY子句中可以使用SELECT子句中定义的别名，可以使用SELECT子句中未使用的列和聚合函数。

7、视图 CREATE VIEW

创建视图（定义视图时不能使用ORDER BY子句，因为视图是无序的）

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

AS

<SELECT语句>

删除视图：DROP VIEW 视图名称

8、子查询

子查询作为内层查询会首先执行。

子查询可以多层嵌套，但是性能也会越来越差，因此尽量避免使用多层嵌套的子查询。

WHERE 子句中不能使用聚合函数，可以替换为返回单一值的子查询（标量子查询），例如：

在细分的组内进行比较时，需要使用关联子查询：

常见错误：将关联条件写在子查询之外的外层查询之中。错误原因：关联名称存在一个有效范围的限制，具体来讲，子查询内部设定的关联名称，只能在该子查询内部使用，就是“内部可以看到外部，而外部看不到内部”。

9、函数

算术函数：加减乘除、ROUND、ABS、

字符串函数：LENGTH、LOWER、UPPER、REPLACE、SUBSTRING

日期函数：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、EXTRACT

转换函数：CAST——类型转换、COALESCE——将NULL转换为其他值

SELECT CAST('2009-12-14' AS DATE) AS date_col --将字符串类型转换为日期类型

10、谓词

LIKE、BETWEEN、IS NULL、 IS NOT NULL、IN、EXIST

CASE WHEN <求值表达式> THEN <表达式>

WHEN <求值表达式> THEN <表达式>

ELSE <表达式>

END

11、集合运算

区别：UNION是以行（纵向）为单位进行操作，在固定的列下增加多行记录，而联结则是以列（横向）为单位进行的。

（1）表的加减法

UNION 等集合运算符通常都会除去重复的记录，想要包含重复项需要加上ALL。UNION联结（并集）的列数必须相同、列的类型必须相同。

选取表中公共部分——INTERSECT（交集）

记录的减法——EXCEPT（差集）

（2）表的联结

a.内联结——INNER JOIN，作用是查询出两表交叉的内容。进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。

b.外联结——LEFT OUTER JOIN、RIGHT OUTER JOIN，作用是以一个表为主表，返回另一个表中对应匹配的内容。使用LEFT 时FROM 子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。

c.三表联结：