200719-0722 SQL复习宝典

周末是综艺扎堆的日子（给不想学习找了个借口
代码运行环境：Mac端MySQLWorkbench 8.0
笔记基础：尚硅谷视频+两本书

这周🧾

最开始学的是：尚硅谷-MySQL数据库-初学者零基础入门到精通
挺好的，太长时间没用SQL，准备在刷提前先整理一下整体框架
用的材料是：《SQL基础教程第二版》和《SQL必知必会第四版》
来过一遍大概知识点💪
两本书我都看了，小白推荐的顺序是 b站视频 + 基础教程 > 必知必会
基础教程的第九章《通过应用程序连接数据库》没看，不会Java
然后刷一下猴子的案例or牛客网

P.S. 终端SQL导入Excel数据
[SQL连接Power BI]

---

一、理论知识

（一）数据库VS数据库软件VS数据库语言⭐️

• DB:数据库database存储数据的仓库，保存了一系列有组织的数据

  数据库的特点：

  • 将数据放到表中，表再放到库中
  • 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
  • 表具有一些特性，这些特性定义了数据在表中如何存储。
  • 表由列组成，也称为字段。所有表都是由一个或多个列组成的。
  • 表中的数据是按行存储的。

• DBMS:数据库管理系统(Database Management System)，又称为数据库软件或数据库产品。DB是通过DBMS创建和操作的容器。优点是实现多个用户同时安全简单地操作大量数据。

  • 基于共享文件系统的DBMS（Access）
  • 基于客户机–服务器的DBMS（MySQL，Oracle，SqlServer）
  • 一般安装的是服务端（用来接受其他程序发出的请求，并对该请求进行相应处理的程序）

• SQL:结构化查询语言(Structure Query Language)专门用于数据库通信的语言

（二）数据库和表的关系

（三）SQL语句及其种类

根据对RDBMS(关系数据库管理系统)赋予的指令种类的不同，SQL语句可以分为以下三类：

• DQL数据库查询语言
• DDL(Data Definition Language 数据定义语言)用来创建或者删除存储数据用的数据库以及数据库中的表等对象。包含的指令：CREATE/DROP/ALTER
• DML(Data Manipulation Language 数据操纵语言)用来查询或者变更表中的记录。包含的指令：SELECT/INSERT/UPDATE/DELETE
• DCL(Data Control Language 数据控制语言)用来确认或者取消对数据库中的数据进行的变更。包含的指令：COMMIT确认变更/ROLLBACK取消变更/GRANT赋予用户操作权限/REVOKE取消用户操作权限

（四）SQL书写规则

1. 不区分大小写，但建议关键字大写，表名首字母大写，列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要，可以进行缩进或换行
4. 字符串和日期常数需要使用单引号（’）括起来。
5. 注释
   单行注释： #注释文字 或者 --(空格)注释文字
   多行注释：/*注释文字*/

---

二、 比较熟悉的知识

（一）库&表的操作

1. 创建表

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

CREATE DATABASE IF NOT EXISTS <数据库名称>; #减少异常的发生

CREATE TABLE <表名> (<列名1> <数据类型> <该列所需约束>，
 <列名2> <数据类型> <该列所需约束>，
 <列名3> <数据类型> <该列所需约束>，
 <列名4> <数据类型> <该列所需约束>，
 .
 .
 .
 <该表的约束1>， <该表的约束2>，……);

补充：列级约束 VS 表级约束

			支持类型				是否可以起约束名
列级约束		除了外键					不可以
表级约束		除了非空和默认		 可以，但是对主键无效

# 添加约束的案例<列级约束>
语法：直接在字段名和类型后面追加 约束类型即可
只支持 默认、非空、主键、唯一，外键约束没有效果
列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

（1）命名的规则
我们只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称。名称必须以半角英文字母作为开头。

（2）数字类型

int 整型

double双精度/float单精度：浮点型 double(a,b)；a = 整数+小数不能超过这个位数，b = 小数点后最多多少位数

decimal浮点型，在表示钱方面使用该类型，因为不会出现精度缺失的问题<精确度最高>decimal>double>float

blob:二进制类型 字节类型jpg mp3 avi
text：字符串类型，表示存储较长文本

date：日期类型，格式为yyyy-MM-dd
time: 时间类型，格式为hh:mm:ss	
datetime			保存范围:1900-1-1～xxxx年		   所占字节: 8
timestamp:时间戳类型，日期+时间 yyyyMMdd hhmmss 
					保存范围:1970-1-1～2038-12-31	   所占字节:  4
					
char(n)：固定长度字符串类型		
		n的解释：最大的字符个数，可省略，默认1	
		特点：不管实际存储，开辟的空间都是n个字符	
		效率：高
varchar(n)：可变长度字符串类型		
		n的解释：最大的字符个数，必选					
		特点：根据实际存储决定开辟的空间					 
		效率：低

CHAR和VARCHAR的区别：字符串以定长字符串的形式存储在被指定为 CHAR 型的列中。所谓定长字符串，就是当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足。但可变长字符串VARCHAR不同，即使字符数未达到最大长度，也不会用半角空格补足。

（3）常用约束

说明：用于限制表中字段的数据，从而进一步保证数据表的数据是一致准确的
NOT NULL 非空：用于限制该字段为必填项
DEFAULT 默认：用于限制该字段当没有插入值时，直接显示默认值
PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空；
			   	 一个表只能有1个主键，但可以是多个字段的组合主键
			   	 主键列中的值不允许修改或更新；
			   	 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

UNIQUE 唯一：限制不能重复
CHECK 检查：用于限制该字段值必须满足指定条件 **CHECK(age BETWEEN 1 AND 100)**。一般用于：
	- 检查最小或最大值。
	- 指定范围。
	- 只允许特定的值。

FOREIGN KEY 外键：用于限制两个表的关系，要求外键列的值必须来自主表的关联列
	**外键添加格式**
	-CONSTRAINT 外键约束的名字
	-FOREIGN KEY (dept_id) references dept(did)
	-意思是设置当前表中的dept_id字段和dept表中的did字段关联，dept表中的did字段就是当前表中的dept_id的外键约束，
	-这个外键约束的名字叫做fk_id，一般潜规则外键约束的名字开头是fk_
                     
	**特点**
    1. 要求在从表设置外键关系，从表的字段值引用了
    2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
    3. 主表的关联列必须是一个key（一般是主键或唯一）
    4. 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

-- <列级约束>
CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY, 		#添加了主键约束
    stuname varchar(20) UNIQUE, #添加了唯一约束
    stugender char(1) DEFAULT '男', #默认约束
    email varchar(20) NOT NULL,	#非空约束
    age INT CHECK (age BETWEEN 0 AND 100), #检查约束，mysql不支持，没效果
    # ****看看外键怎么添加的！是在下面****
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)
    # 起名规律fk(foreign key)_stuinfo(当前表名)_major（主表名）
    );

# 添加约束的案例<表级约束>
/*语法：在各个字段的最下面
【constraint 】约束类型(字段名)；
除了非空、默认，其他的都支持*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname varchar(20),
    gender char,
    seat int,
    age int,
    majorid int,
    
    CONSTRAINT ok PRIMARY KEY(id), #主键
    CONSTRAINT uq UNIQUE(SEAT),	   #唯一键
    CONSTRAINT ck CHECK(gender = '男'), #检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
    );

# 或者先建表，后加约束
ALTER TABLE Vendors 
ADD CONSTRAINT PRIMARY KEY (vend_id);

ALTER TABLE Orders 
ADD CONSTRAINT 
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

ALTER TABLE Orders 
ADD CONSTRAINT CHECK (gender LIKE '[MF]')# M/F中的一个

# ⚠️外键的删除问题
    # 删除问题：可以通过以下两种方式来删除主表的记录
    DELETE FROM major WHERE id = 3; #不可以因为删除要先删除从表，这是主表
    # （1）级联删除
    ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; #删除原来的外键
    #传统方式添加外键 ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;#添加级联删除的外键
    DELETE FROM major WHERE id = 3; #两个表的三号专业都删了
    # (2) 级联置空
	ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; #删除原来的外键
	ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;#添加外键
    DELETE FROM major WHERE id = 2; 

【补充】 设置自增长列 AUTO_INCREMENT

• 自增长列要求必须设置在一个键上，比如主键或者唯一键
• 自增长列要求数据类型为数值型
• 一个表至多有一个自增长列
• 不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
  如果要更改起始值：手动插入值；
  如果要更改步长，更改系统变量 SET auto_increment_increment = 值

-- （1）创建表时设置自增长列
CREATE TABLE 表(字段名 字段类型 约束 auto_increment)
-- （2）修改表时设置自增长列
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束 auto_increment;
-- （3）删除表时设置自增长列
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束；

CREATE TABLE gradeinfo(
	gradeID INT PRIMARY KEY AUTO_INCREMENT,
    gradeName VARCHAR(20));

SELECT * FROM gradeinfo;
INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'二年级');
INSERT INTO gradeinfo(gradename) VALUES('一年级'),('二年级');

2. 修改表

ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型 【字段约束】;

修改表名：ALTER TABLE 表名 RENAME TO 新表名；
添加新字段：ALTER TABLE 表名 ADD COLUMN 新字段名 字段类型 【约束】;
修改字段类型或约束：ALTER TABLE 表名 MODIFY COLUMN 字段名 新字段类型 【新字段约束】;
删除约束：ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;
修改字段名： ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 新字段类型 【新字段约束】; 
删除字段：ALTER TABLE 表名 DROP COLUMN 字段名;

3. 删除表/库

DROP TABLE IF EXISTS <表名>;
DROP DATABASE IF EXISTS <数据库名>;

4. 复制表

（1）仅仅复制表的结构

CREATE TABLE new_table LIKE old_table;
-- 或者
CREATE TABLE new_table 
AS
SELECT *
FROM old_table
WHERE 1=2; #不成立

（2）复制表的结构+数据

CREATE TABLE new_table AS  SELECT * FROM old_table;

5. 数据的插入

# 方式一(经典插入)
-- 插入单行
INSERT INTO 表名(列名,...) VALUES(值1,...);
-- 插入多行
INSERT INTO 表名(列名,...) VALUES(值1,...),(值1,...),(值1,...)...;

# 方式二
INSERT INTO 表名
SET 列名 = 值，列名 = 值...

（1）方式对比

• 方式一支持插入多行，但是方式二不支持
• 方式一支持子查询（结果作为value），方式二不支持

INSERT INTO beauty(id,name,phone)
SELECT id,boyname,'123'
FROM boys WHERE id<3;

（2）注意点

• 列的顺序可以调换，只要保证列名和值对应。
• 可以省略所有列名，默认所有列，而且列的顺序和表中列的顺序一致。
• 省略INSERT语句中的某列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）
• 默认字段/NULL值如何插入？
  • 方式一：字段名写上，values对应的值改为defualt/NULL
  • 方式二：字段名和值都不写

6. 数据的删除

-- 方式一：delete语句
DELETE FROM 表名 WHERE 筛选条件;

-- 方式二：truncate语句 只能删除所有数据
truncate table 表名;

• 注意不要丢掉FROM！
• DELETE 语句从表中删除行，甚至是删除表中所有行。而不是表/列。但是，DELETE不删除表本身。要删除指定的列，请使用 UPDATE 语句。

7. 数据的更新

-- 修改单表的记录 
语法							执行顺序
UPDATE 表名					 1
SET 列 = 新值,列 = 新值 ...    3
WHERE 筛选条件;			     2

修改多表的记录【补充】

sql92语法：
UPDATE 表1 别名，表2 别名
SET 列 = 值，...
WHERE 连接条件
AND 筛选条件

sql99语法：
UPDATE 表1 别名
inner|left|right join 表2 别名
ON 连接条件
SET 列 = 值,...
WHERE 筛选条件;

（二）视图

1. 含义

虚拟表，和普通表一样使用，它的数据来自于表，通过执行时动态生成；表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。

2. 应用场景

多个地方用到同样的查询结果，查询结果使用的sql语句较为复杂，不如直接封装起来

3. 表VS视图

		创建语法的关键字		是否实际占用物理空间	 使用
视图		CREATE VIEW			 只保存sql逻辑		一般用于查询，一般不能增删改
表		CREATE TABLE		 保存了数据			增删改查

4. 创建视图

CREATE VIEW 视图名（视图列名1，视图列名2...） AS 查询语句;

• 也可以不写视图列名，则默认和查询语句的列名一致
• AS 必须写
• 应该避免在视图的基础上创建视图，因为多重视图会降低 SQL 的性能
• 定义视图时不要使用ORDER BY子句

# 查询各部门的平均工资级别
CREATE VIEW myv
AS
SELECT AVG(salary) ag
FROM employees
GROUP BY department_id;

SELECT myv.*,g.grade_level
FROM myv
JOIN job_grades g
ON myv.ag BETWEEN g.lowest_sal AND g.highest_sal;

5. 更新视图

标准 SQL 中有这样的规定：如果定义视图的 SELECT 语句能够满足某些条件，那么这个视图就可以被更新。下面就给大家列举一些比较具有代表性的条件。

• SELECT 子句中未使用 DISTINCT
• FROM 子句中只有一张表
• 未使用 GROUP BY 子句
• 未使用 HAVING 子句

反之，视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的

• 包含以下关键字的sql语句：分组函数,distinct,group by,having, union,union all
• 常量视图
• select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

INSERT、UPDATE 和 DELETE 语句当然也可以像操作表时那样正常执行，但是对于原表来说却需要设置各种各样的约束（主键和 NOT NULL 等），需要特别注意。

# 1. 插入
INSERT INTO myv1 VALUES('sj','男');
SELECT * FROM Student; #也插入到了原表

# 2. 修改
UPDATE myv1 SET last_name = 'super' WHERE last_name = 'sj';

# 3.删除
DELETE FROM myv1 WHERE last_name = 'super';

6. 删除视图

DROP VIEW 视图名1，...;

7. 修改&查看视图（补充）

-- 方式一
CREATE OR REPLACE VIEW 视图名
AS
查询语句;

-- 方式二
ALTER VIEW 视图名
AS
查询语句; 

DESC 视图名;
SHOW CREATE VIEW 视图名;

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

（三）子查询

• 用自联结而不用子查询
  自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。

1. 概念

当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询，外面的select语句称主查询或外查询
【子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多】(外面的语句可以是insert update delete select等，一般select较多)

2. 分类

• 按子查询出现的位置进行分类

  • select后面： 子查询的结果为单行单列（标量子查询）
  • from后面：子查询的结果可以为多行多列（表子查询）
    FROM后面：将子查询结果充当一张表，必须起别名alias
  • where或having后面 * 要求子查询的结果必须为单列（列子查询=单行子查询+多行子查询）
  • exists后面：子查询的结果必须为单列（相关子查询）
    exists（完整的查询语句）用于查询是否存在 结果： 1/0

• 按结果集的行列

  • 标量子查询(单行字查询) 结果集为1行1列 *
  • 列子查询（多行子查询） 结果集1列多行 *
  • 行子查询：结果集为多列
  • 表子查询：结果集为多行多列

3. 特点

• 子查询放在条件中，要求必须放在条件的右侧
• 子查询一般放在小括号中
• 子查询的执行优先于主查询，主查询使用到了子查询的结果
• 单行子查询 结果集为单行单列 一般搭配单行操作符: > < = >= <= <>
  多行子查询 结果集为多行单列 一般搭配多行操作符： any/some in all

4. 关联子查询

在细分的组内进行比较时，需要使用关联子查询。
e.g. 选取出各商品种类中高于该商品种类的平均销售单价的商品

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);

• 这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。
• 结合条件一定要写在子查询中
  

5. 多行子查询

• IN / NOT IN 等于列表中的任意一个
• ANY / SOME 和子查询返回的某一个值比较
• ALL 和子查询返回的所有值比较

6. 例题

 # 返回location_id是1400或1700的部门中的所有员工姓名
 SELECT last_name
 FROM employees
 WHERE department_id = ANY(
	SELECT department_id
	FROM departments
	WHERE location_id IN (1400,1700));

# 返回其他工种中比job_id为'IT_PROG'工种所有工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG');

    
# 查询员工编号最小并且工资最高的员工信息<行子查询>
SELECT *
FROM employees
WHERE employee_id = (
	SELECT min(employee_id)
	FROM employees)
AND salary = (
	SELECT max(salary)
    FROM employees
    );
# -------------------------
SELECT *
FROM employees
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
    FROM employees);


# 查询没有女朋友的男神信息
SELECT boys.*
FROM boys
WHERE NOT EXISTS (
	SELECT boyfriend_id
    FROM beauty
    WHERE beauty.boyfriend_id = boys.id);

（四）函数

1. 数学函数

# ***round(对象数值，保留小数的位数)四舍五入*** 不管是正负数，先对绝对值四舍五入，然后再把符号加上
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2); #指定位数

# ***ceil向上取整，返回>=该参数的最小整数***
SELECT CEIL(1.02);
SELECT CEIL(-1.02);

# ***floor向下取整，返回<=该参数的最大整数***
SELECT floor(1.02);

# ***truncate小数点后指定位数截断***
SELECT TRUNCATE(1.699,1);

# ***mod（被除数，除数）取余*** 和%是一样的效果
# 原理：MOD(a,b) == a - a/b*b
SELECT MOD(-10,-3); #结果是-1 被除数如果是正，结果为正，反之为负
SELECT MOD(10,-3);

# ***rand获取随机数，返回0-1之间的小数***
# ***abs绝对值***

2. 字符串函数

# ***length 获取参数值的字节个数***
SHOW VARIABLES LIKE '%char%'; #目前客户端使用的字符集utf8 一个英文一个字节 一个汉字三个字节；JBK 一个英文一个字节 一个汉字两个字节
SELECT LENGTH('张三丰zhang'); #14

# ***char_length 获取参数值的字符个数***
SELECT CHAR_LENGTH('张三丰zhang');#8

# ***concat 拼接字符串*** 注意null值ifnull
#【补充】isnull函数 判断某字段或表达式是否为null，是返回1，否则0

# ***upper/lower***
# 将姓变大写，名变小写，然后拼接在一起
SELECT CONCAT(lower(last_name),upper(first_name)) 姓名 FROM employees;

# ***substr,substring*** 注意 索引从1开始
SELECT SUBSTR("请你好好加油找工作",7) output;   #截取从指定索引处后面所有的字符：找工作
SELECT SUBSTR("请你好好加油找工作",8,2) output; #截取从指定索引处指定字符长度的字符：工作

# ***instr*** 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR("请你好好加油找工作",'加油') output; #5

# ***trim*** 去掉前后指定的字段，默认是去空格
# ***ltrim/rtrim*** 去掉左边/右边指定的字段，默认是去空格
SELECT trim('   heyhey   ') as out_put; #删除了前后的空格
SELECT trim('a' FROM 'aaaaaahaeayaaaaa') out_put; #中间的去不了

# ***lpad*** 用指定的字符实现左填充达到指定长度;如果长度小于最初的字符，则截断
SELECT LPAD('加油',6,'!') out_put;
# ***rpad*** 右填充

# ***replace(对象字符串，替换前的字符串，替换后的字符串)替换*** 不管出现多少次都会被替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') out_put; 

# [***STRCMP 比较两个字符大小***](https://baike.baidu.com/item/strcmp/5495571?fr=aladdin)

# ***LEFT/RIGHT  截取子串***
SELECT LEFT('找工作',1); #找
SELECT RIGHT('找工作',1); #作

【补充】SOUNDEX（） 返回字符串的SOUNDEX值

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL 概念，但多数 DBMS 都提供对 SOUNDEX的支持。

e.g. 使用 SOUNDEX()函数进行搜索，它匹配所有发音类似于Michael Green 的联系名：

SELECT cust_name, cust_contact 
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

因为 Michael Green 和Michelle Green 发音相似，所以它们的 SOUNDEX 值匹配，因此 WHERE子句正确地过滤出了所需的数据。

3. 日期函数

# now返回当前系统日期+时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

# curdate返回当前系统日期，不包含时间
SELECT CURDATE();
SELECT CURRENT_DATE;

# curtime返回当前时间
SELECT CURTIME();
SELECT CURRENT_TIME;

# EXTRACT(日期元素 FROM 日期)——截取日期元素
SELECT CURRENT_TIMESTAMP,
 EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
 EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
 EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
 EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
 EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
 EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

#可以获取指定的部分-年，月，日，时间，小时，分钟，秒
# ***monthname以英文形式返回月***
SELECT YEAR(NOW()) 年;
SELECT MONTH(now()) 月; #数字展示的月份
SELECT MONTHNAME(now()) 月; #英文展示的月份

# ***str_to_date将日期格式的字符转换成指定格式的日期***
/*格式符
%Y 四位的年份
%y 两位的年份
%m 月份（01,02,...12)
%c 月份（1,2...12）
%d 日(01,02...)
%H 小时(24h)
%h 小时(12h)
%i 分钟(00,01...59)
%s 秒(00,01...59)
*/

SELECT str_to_date('9-13-1999','%m-%d-%Y');

# ***datediff返回两个日期相差的天数***
-- 查询员工表的最大入职时间和最小入职时间的相差天数
SELECT max(s_birth)-min(s_birth) difference FROM Student; #错误 有专门的函数
SELECT DATEDIFF(max(s_birth),min(s_birth)) difference FROM Student;

# ***date_format将日期转换成字符***
SELECT DATE_FORMAT(now(),'%y年%c月%d日');
# 查询有奖金的员工名和入职日期(xx月xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE NOT(ISNULL(commission_pct));

4. 转换函数

# cast（转换前的值 AS 想要转换的数据类型） 类型转化
-- （1） 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

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


# COALESCE(数据1，数据2，数据3……)——将NULL转换为其他值
# 该函数会返回可变参数中左侧开始第1个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。
SELECT 
	COALESCE(NULL, 1) AS col_1,
 	COALESCE(NULL, 'test', NULL) AS col_2,
 	COALESCE(NULL, NULL, '2009-11-01') AS col_3;

5. 谓词

谓词是需要满足特定条件的函数，该条件就是返回值是真值。对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）。这也是谓词和函数的最大区别。

• LIKE+通配符
  通配符的缺点：通配符搜索一般比其他搜索要耗费更长的处理时间。
  • %任意多个字符，包含0个字符（通配符%看起来像是可以匹配任何东西，但有个例外，这就是 NULL。子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。）
  • _任意单个字符

#将员工的姓按首字母排序，并写出姓名的长度
SELECT *, length(last_name) len
FROM employees
ORDER BY last_name; #错误 这样的话首字母一样 会比较第二个字母 依此类推

SELECT *, LENGTH(last_name) len, SUBSTR(last_name,1,1) 首字母
FROM employees
ORDER BY 首字母;

#查询员工姓中第二个字符为_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_%'; #转义字符
#或者指定其他字符为转义字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$'; #指定转义字符用ESCAPE

【补充】
方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。（只有微软的 Access 和 SQL Server 支持集合）

e.g. 找出所有名字以 J 或 M 起头的联系人

FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;

[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。

e.g. 匹配以J 和 M 之外的任意字符起头的任意联系人名

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[^JM]%' 
ORDER BY cust_contact;

Microsoft Access需要用!而不是^来否定一个集合，因此，使用的是[!JM]而不是[^JM]。

• BETWEEN
  #查询员工编号在100到120之间的员工信息

# between and 提高简洁度，***包含临界值***，不要调换顺序
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;

• IS NULLL、IS NOT NULL 判断是否为NULL
  为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL

SELECT *
FROM TABLE
WHERE Col IS (NOT) NULL;

• IN / NOT IN
  在使用IN 和 NOT IN 时是无法选取出 NULL 数据的，NULL 终究还是需要使用IS NULL 和 IS NOT NULL 来进行判断。
• EXIST / NOT EXIST
  • 通常指定关联子查询作为EXIST的参数。
  • 作为EXIST参数的子查询中经常会使用SELECT *。

① EXIST 的使用方法与之前的都不相同
② 语法理解起来比较困难
③ 实际上即使不使用 EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替

EXIST 是只有 1 个参数的谓词。EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

e.g. 使用 EXIST 选取出“大阪店（000C）在售商品（product_id）的销售单（sale_price）”。

SELECT product_name, sale_price
FROM Product AS P 
WHERE EXISTS (SELECT *
 	FROM ShopProduct AS SP 
	WHERE SP.shop_id = '000C'
	AND SP.product_id = P.product_id);

上面这样的子查询就是唯一的参数。确切地说，由于通过条件“SP.product_id = P.product_id”将 Product 表和 ShopProduct表进行了联接，因此作为参数的是关联子查询。EXIST 通常都会使用关联子查询作为参数。

可能大家会觉得子查询中的 SELECT * 稍微有些不同，就像我们之前学到的那样，由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（Product）表和商店商品（ShopProduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。

6. CASE表达式

• ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显示地写出 ELSE 子句。
• CASE表达式中的END不能省略。
  -== CASE表达式可以进行行列互换==

-- 简单 CASE 表达式
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
... ... 
else 要显示的值n或语句n;
end
# 如果是显示的值 不需要结尾分号；语句需要
/*
简单 CASE 表达式在将想要求值的表达式（这里是列）书写过一次之后，就无需在之后的WHEN 子句中重复书写“product_type”了。虽然看上去简化了书写，但是想要在 WHEN子句中指定不同列时，简单 CASE 表达式就无能为力了。
*/
-- 搜索 CASE 表达式
CASE
WHEN 条件1（列 = 值） THEN 要显示的值1或语句1
WHEN 条件2（列 = 值） THEN 要显示的值1或语句2
...
ELSE 要显示的值n或语句n
END

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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;

7. IF函数

# ***if函数*** if(条件表达式，表达式1，表达式2):如果条件表达式成立，返回表达式1否则2
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金') summary
FROM employees;

8. 补充自定义函数

# （1）创建语法
CREATE FUNCTION 函数名(参数列表 = 参数名 + 参数类型) RETURNS 返回类型
BEGIN	  #函数体仅有一句话，则可以省略begin end
	函数体 # 肯定会有return语句，如果没有也不报错/return语句没有放在函数体的最后也不报错，但都不建议
END	#使用delimiter语句设置结束标记

# （2）调用语法
SELECT 函数名（参数列表）

（五）集合运算

1. 表的加减法（行数变化）

• UNION （ALL）
  当查询结果来自多张表，但多张表之间没有关联，这时候往往使用联合查询，也称union查询。
  • 多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
  • union自动去重，union all可以支持显示重复项
  • 可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

SELECT 查询列表
FROM 表1
WHERE 筛选条件
UNION
SELECT 查询列表
FROM 表2
WHERE 筛选条件
...

• MySQL并不支持的INTERSECT /EXCEPT （ALL）

2. 联结（列数变化）

• (INNER) JOIN
  内联结只能选取出同时存在于两张表中的数据。

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件 #<可以是等值连接，也可以是非等值连接>
WHERE 筛选条件;

# 查询员工的工资级别
SELECT grade_level,salary
FROM employees e
JOIN job_grades g
ON salary BETWEEN lowest_sal AND highest_sal;#非等值

• OUTER JOIN
  对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。

1.外连接的查询结果为主表中的所有记录
	如果表中有和它匹配的，则显示匹配的值
    如果表中没有和它匹配的，则显示null
    外连接查询结果 = 内连接结果+ *主表中有而从表没有的记录*
2.左外连接，left join左边的是主表
  右外连接，right join右边的主表
  全连接 full join
3.左外和右外交换两个表的顺序，可以实现同样的效果

• CROSS JOIN
  在实际业务中并不会使用。

# ***交叉连接*** 就是笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo; #4*12 = 48
# 等同于
SELECT count(*) FROM beauty; #输出12行
SELECT count(*) FROM boys;   #输出4行
SELECT name,boyName FROM beauty,boys; #最终输出12*4=48行
# 发生原因：当查询多个表示，没有添加有效的连接条件，导致多个表所有行实现完全连接

（六）零碎的点

查询的结果是一个虚拟的表格，临时性

1. disticnt

• 不能部分使用 DISTINCT：DISTINCT 关键字作用于跟在其后所有的列的组合，不仅仅是跟在其后的那一列。
• 在使用 DISTINCT 时，NULL 也被视为一类数据。
• DISTINCT 关键字只能用在第一个列名之前。

2. limit分页查询

• 应用场景
  当页面上的数据，一页显示不全，则需要分页显示。
  分页查询的sql命令请求数据库服务器->服务器响应查询到的多条数据->前台页面。

🌟🌟🌟🌟🌟🌟执行顺序🌟🌟🌟🌟🌟

语法：							执行顺序
SELECT 查询列表						7
FROM 表1 别名						1
JOIN 表2 别名						2
ON 连接条件							3
WHERE 筛选条件						4		
GROUP BY 分组						5
HAVING 分组后筛选						6
ORDER BY 排序列表					8
LIMIT 起始条目索引，显示的条目数		9

特点：
1.起始条目索引如果不写，默认从0开始显示
2.limit后面支持两个参数（1）参数1 显示的起始条目索引 （2）参数2 条目数

公式：
假如要显示的页数是page，每页显示的条目数为size

SELECT *
FROM employees
LIMIT (page-1)*size,size;

# e.g.
page(size = 10)
1				limit 0,10
2				limit 10,10
3				limit 20,10
...

• LIMIT 4 OFFSET 3 = LIMIT 3,4 指示 MySQL 等 DBMS 返回从第 3 行(包括)起的 4 行数据。第一个数字是指从哪儿开始，第二个数字是检索的行数。
• 第 0 行 : 第一个被检索的行是第 0 行，而不是第 1 行。因此，LIMIT 1 OFFSET 1 会检索第 2 行，而不是第 1 行。

3. 运算符

• 所有包含 NULL 的计算，结果肯定是 NULL。

+的作用
java中的+号：运算符+连接符
mysql中的+号：运算符 连接需要用 concat()

select 100+90;    #两个操作数都为数值型，则做加法运算
select '123'+90;  #只要其中一方为字符型，先试图将字符型转换成字符型，成功，则加法运算
select 'kiki'+90; #若失败，则字符型转换成0
select null+90;   #只要一方为null，不管是什么运算，则一定返回null，所以连接数值列的时候一定要用ifnull函数

• 小于某个日期就是在该日期之前的意思。
• 一定要让不等号在左，等号在右。
• 对字符串使用不等号时的注意事项:在对字符串类型的数据进行大小比较时，使用的是和数字比较不同的规则。典型的规则就是按照字典顺序进行比较，也就是像姓名那样，按照条目在字典中出现的顺序来进行排序。该规则最重要的一点就是，以相同字符开头的单词比不同字符开头的单词更相近。

CREATE TABLE Chars(chr CHAR(3) NOT NULL PRIMARY KEY);

START TRANSACTION; 
INSERT INTO Chars VALUES ('1');
INSERT INTO Chars VALUES ('2');
INSERT INTO Chars VALUES ('3');
INSERT INTO Chars VALUES ('10');
INSERT INTO Chars VALUES ('11');
INSERT INTO Chars VALUES ('222');
COMMIT;

SELECT chr FROM Chars WHERE chr > '2';
/* 输出结果是 ‘222’ 和 ‘3’ 
Chars 表 chr 列中的数据按照字典顺序进行排序的结果如下所示。
1
10
11
2
222
3
'10' 和 '11' 同样都是以 '1' 开头的字符串，首先判定为比 '2' 小。 */

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

#查询部门编号不是在90到110之间，或者工资高于1w5的员工信息
SELECT *
FROM employees
WHERE 
	NOT(department_id >= 90 AND department_id <= 110) 
	OR 
	salary > 15000;

• AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。
• 使用逻辑运算符时也需要特别对待 NULL（这时真值是除真假之外的第三种值——不确定UNKNOWN）
  

4. 起别名

若有特殊符号，记得用双引号引起来

SELECT salary AS "out put" FROM employees;

5. 聚集函数

• AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个 AVG()函数。
• COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
• 除了COUNT(*)，聚合函数会将NULL排除在外。
• SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。用于文本数据时，MAX/MIN返回按该列排序后的最后一行/第一行。

6. 分组函数

• 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
• GROUP BY 子句中是不能使用别名的（原因：SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时，SELECT 子句中定义的别名，DBMS 还并不知道）
• 使用聚合函数时，SELECT/HAVING 子句中只能存在以下三种元素。
  • 常数
  • 聚合函数
  • GROUP BY子句中指定的列名（也就是聚合键）
• GROUP BY子句结果的显示是无序的。

7. 排序函数

• order by 可按列编号排序，但不推荐！

SELECT prod_id, prod_price, prod_name 
FROM Products 
ORDER BY 2, 3;

ORDER BY 2，3 表示先按 prod_price，再按 prod_name 进行排序。

• 如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。
• 使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示。
• 在ORDER BY子句中可以使用SELECT子句中定义的别名。
• 在ORDER BY子句中可以使用SELECT子句中未使用的列。

---

三、 比较陌生的知识点

（一）事务

1. 概念

• 用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。
• 一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败。
• 使用事务开始语句和事务结束语句，将一系列 DML 语句（INSERT/
  UPDATE/DELETE 语句）括起来，就实现了一个事务处理。

2. ACID特性

• 原子性Atomicity：事务中所有操作是不可再分割的原子单位，要么执行要么都不执行。

• 一致性Consisyency：事务执行后，数据库状态与其他业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的；一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。

• 隔离性Isolation：是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。

• 持久性Durability：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

3. 分类

• 隐式事务：没有明显的开启和结束标记，提交操作是自动进行的（比如dml语句的insert,update,delete语句本身就是一条事务）

insert into stuinfo values(1,'john','男') 
# 如果其中某一个字段出现问题，其他的值也无法插入

• 显式事务：具有明显的开启和结束标记（一般由多条sql语句组成）
  步骤：取消隐式事务自动开启的功能
  （1）开启事务
  （2）编写事务需要的sql语句（1条或多条） # 一般insert\update\delete
  （3）结束事务

# （1）取消事务自动开启
SET autocommit = 0;
# （2）开启事务
START TRANSACTION; #可以省略
# （3）编写事务的sql语句
UPDATE account SET balance = balance - 50 WHERE id = 1;

# 设置回滚点
/*
使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。
要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
*/
savepoint 回滚点名;

UPDATE account SET balance = balance + 50 WHERE id = 2;

# （4）结束事务
# 提交
COMMIT; #代码运行，正常提交
# 回滚
ROLLBACK; #异常，整个单元回滚，所有收到影响的数据将返回到事务开始以前的状态
# 回滚到指定的地方
ROLLBACK TO 回滚点名;  
# 第一条DML语句执行了，然而第二条没有

4. 并发事务（补充）

# 并发事务：多个事务 同时操作 同一个数据库的相同数据时
# ***数据库的隔离级别***
/* 对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题
	- 脏读：对于两个事务T1，T2，T1读取了T2更新 *但还没有被提交* 的字段，之后若T2回滚，则T1读取的内容就是临时且无效的
    - 不可重复读：对于两个事务T1，T2，T1读取了一个字段，然后T2 *更新* 了该字段，之后T1再次读取同一个字段，值就不同了
    - 幻读：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中 *插入* 了一些新的行，之后，如果T1再次读取同一个表，就会多出几行
    
    数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题
    
    隔离级别								描述
	read uncommitted(读未提交数据)		允许事务读取未被其他事务提交的变更。
	read committed(读已提交数据)			只允许事务读取被其他事务提交的变更。
	repeatable read(可重复读)			 确保事务可以从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新
										（其他事物的更新会等待本事务的完成）
	serializable(串行化)	（本事务执行时，其他事务增删改查全部等待）确保事务可以从一个表中读取相同的行，在这个事务的持续的期间，
						 禁止其他事务对表的执行插入，更新和删除操作，所有并发问题都可以避免，但性能十分低下

	mysql中默认 第三个隔离级别；oracle中默认 第二个隔离级别
    
    *总结* 隔离能否解决问题				脏读			不可重复读		幻读
    read uncommitted(读未提交数据)		否				否			 否
    read committed(读已提交数据)			是				否			 否		
    repeatable read(可重复读)			是				是			 否
    serializable(串行化)					是				是			 是 */

（二）SQL高级处理

1. 窗口函数

• 语法

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

• 分类
  窗口函数大体可以分为以下两种。
  ① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）

  SELECT product_id, product_name, sale_price,
  SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
   FROM Product;
  

  

  ② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

  ●RANK函数
  计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
  例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
  ●DENSE_RANK函数
  同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
  例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
  ●ROW_NUMBER函数
  赋予唯一的连续位次。
  例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

• 注意点

  • 原则上窗口函数只能在SELECT子句中使用。
  • 由于专用窗口函数无需参数，因此通常括号中都是空的。
  • OVER（）后面[]中的内容可以省略
  • PARTITION BY用于分组，但并不具备汇总的能力
  • OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。

---

• 计算移动平均
  (1) 往前移动平均 ROWS X PRECEDING
  窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

# 指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
	AVG (sale_price) OVER (ORDER BY product_id 
						   ROWS 2 PRECEDING) AS moving_avg
FROM Product;

这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。
● 自身（当前记录）
● 之前 1行的记录
● 之前 2行的记录

(2) 往后移动平均 ROWS X FOLLOWING

# 指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
	AVG (sale_price) OVER (ORDER BY product_id 
						   ROWS 2 FOLLOWING) AS moving_avg
FROM Product;

(3) 前后移动平均 ROWS BETWEEN X PRECEDING AND Y FOLLOWING

# 将当前记录的前后行作为汇总对象
SELECT product_id, product_name, sale_price,
 AVG (sale_price) OVER (ORDER BY product_id
 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
 FROM Product;

2. GROUPING运算符

只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用GROUPING运算符。

• ROLLUP——同时得出合计和小计

-- MySQL写法特别
SELECT s_id, c_id, SUM(s_score) AS sum_score
 FROM Score
 GROUP BY s_id, c_id WITH ROLLUP;

使用GROUPING函数来判断NULL
该函数在其参数列的值为超级分组（合计/小计自动产生的）记录所产生的 NULL 时返回 1，其他情况返回 0（e.g. 本身数据缺失，分组产生的NULL）。

 SELECT GROUPING(s_id) AS s_id, 
 GROUPING(c_id) AS c_id, SUM(s_score) AS s_score
 FROM Score
 GROUP BY s_id,c_id WITH ROLLUP;

这样就能分辨超级分组记录中的 NULL 和原始数据本身的 NULL 了。使用 GROUPING 函数还能在超级分组记录的键值中插入字符串。也就是说，当 GROUPING 函数的返回值为 1 时，指定“合计”或者“小计”等字符串，其他情况返回通常的列的值。

select 
	case when grouping(s_id) = 1 then '合计' else s_id end as new_s_id, 
    case when grouping(c_id) = 1 then '合计' else c_id end as new_c_id, 
    sum(s_score) sum_score
from Score
group by s_id,c_id with rollup;

• CUBE——用数据来搭积木(MySQL不能使用)
  CUBE 的语法和 ROLLUP 相同，只需要将 ROLLUP 替换为 CUBE 就可以了。
  所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。

SELECT CASE WHEN GROUPING(product_type) = 1 
 THEN '商品种类 合计'
 ELSE product_type END AS product_type,
 CASE WHEN GROUPING(regist_date) = 1 
 THEN '登记日期 合计'
 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
 #这是为了满足CASE 表达式所有分支的返回值必须一致的条件。如果不这样的话，那么各个分支会分别返回日期类型和字符串类型的值，执行时就会发生语法错误。
 SUM(sale_price) AS sum_price
 FROM Product
 GROUP BY CUBE(product_type, regist_date);

① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (regist_date) ←添加的组合
④ GROUP BY (product_type, regist_date)

• GROUPING SETS——取得期望的积木

运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录。

例如，之前的 CUBE 的结果就是根据聚合键的所有可能的组合计算而来的。如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用 2 个聚合键的记录”时，可以使用 GROUPING SETS。

SELECT CASE WHEN GROUPING(product_type) = 1 
 THEN '商品种类 合计'
 ELSE product_type END AS product_type,
 CASE WHEN GROUPING(regist_date) = 1 
 THEN '登记日期 合计'
 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
 SUM(sale_price) AS sum_price
 FROM Product
 GROUP BY GROUPING SETS (product_type, regist_date);

（三）游标（cursor）

SQL 检索操作返回一组称为结果集（SQL 查询所检索出的结果）的行，这组返回的行都是与 SQL 语句相匹配的行（零行或多行）。

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

不同的 DBMS 支持不同的游标选项和特性。常见的一些选项和特性如下。

• 能够标记游标为只读，使数据能读取，但不能更新和删除。
• 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。
• 能标记某些列为可编辑的，某些列为不可编辑的。
• 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。
• 指示 DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。

使用游标涉及几个明确的步骤。

• 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
• 一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的 DBMS）。

e.g 们创建一个游标来检索没有电子邮件地址的所有顾客，作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址。

DECLARE CustCursor CURSOR 
FOR 
SELECT * FROM Customers 
WHERE cust_email IS NULL

使用 OPEN CURSOR 语句打开游标

OPEN CURSOR CustCursor

在处理 OPEN CURSOR 语句时，执行查询，存储检索出的数据以供浏览和滚动。
现在可以用 FETCH 语句访问游标数据了。FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）

（四）索引

索引用来排序数据以加快搜索和排序操作的速度。索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。

在执行这些操作时，DBMS 必须动态地更新索引。

• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
• 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

CREATE INDEX prod_name_ind 
ON Products (prod_name);

索引必须唯一命名。这里的索引名 prod_name_ind 在关键字 CREATE INDEX 之后定义。ON 用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

（五）存储过程

1. 变量

• 系统变量：变量由系统提供，不是用户定义，属于服务器层面
  • 全局变量
  • 会话变量

#（1）查看所有的系统变量
show global | [session] variables;

#（2）查看满足条件的部分系统变量
show global | [session] variables like '%char%';

#（3）查看指定的某个系统变量的值
select @@global | [session].系统变量名;

#e.g.
select @@global.autocommit;
#（4）为某个系统变量赋值
# 方式一
set global | [session] 系统变量名 = 值;
# 方式二
set @@global | [session].系统变量名 = 值;

• 自定义变量：变量是用户自定义的，不是系统
  使用步骤：声明、赋值、使用（查看、比较、运算等）
  • 用户变量：作用域针对于当前会话（连接）有效，等同于会话变量的作用域，可放
    会话中的任何地方
  • 局部变量：作用域仅仅在定义它的begin end中有效，且应用在begin end中的第一句话

-- 用户变量
# 赋值的操作符 = 或者 :=
#（1） 声明并初始化
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

#（2）赋值（更新用户变量的值）
# 方式一：通过SET或SELECT 《代码同第一步声明》

# 方式二：通过SELECT INTO
SELECT 字段 INTO 变量名 FROM 表；

SELECT count(*) INTO @count FROM employees;

# (3)使用（查看用户变量的值） 
SELECT @用户变量名;


-- 局部变量
#（1）声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

#（2）赋值
# 方式一：通过SET或SELECT 
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT @局部变量名 := 值; # 这里又有 @
# 方式二：通过SELECT INTO
SELECT 字段 INTO 局部变量名 FROM 表；

# (3)使用（查看用户变量的值） 
SELECT 局部变量名;

e.g. 用户变量

SET @m = 1;
SET @n = 2;
SET @sum = @m + @n;
SELECT @sum;

e.g. 局部变量

# 执行报错 因为没有放在begin end中
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE sum INT; 
SET sum = m + n;
SELECT sum;

2. 创建存储过程

存储过程就是为以后使用而保存的一条或多条 SQL 语句，往往是增删改。

#（1）创建语法
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
	存储过程体（一组合法的SQL语句）
END

#（2）调用语法
CALL 存储过程名(实参列表);

• 参数列表包含三部分：参数模式 参数名 参数类型
  e.g. IN stuname VARCHAR(20)
  • IN 该参数可以作为输入，也就是该参数需要调用方传入值
  • OUT 该参数可以作为输出，也就是该参数可以作为返回值
  • INOUT 既可以输入又可以输出
• 如果存储过程体仅仅只有一句话，BEGIN END可以省略
• 存储过程体中的每条SQL语句的结尾要求必须加分号。存储过程的结尾可以使用DELIMITER 重新设置。 语法：DELIMITERE 结束标记

e.g.

#（1）空参列表
# 插入到admin表中2条记录，实际操作都是插入上万条
# 在终端中调用
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,password) VALUES('A','000'),('B','000');
END $

# 调用
CALL myp1()$

#（2）创建带in模式参数的存储过程
# 案例：创建存储过程实现：根据女生名，查询对应的男生信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boy.bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

# 调用
call myp2() $ #参数可以写一个常量值，只要类型能对应的上


# 案例2:创建存储过程实现，用户是否登陆成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; #声明并初始化
	
	select count(*) into result #赋值
	from admin a
	where a.username = username
	and a.password = password;
	
	select if(count(*)>0,"成功","失败"); #使用
END $

# 调用
call myp3('zzz','zzzzzz')$

#(3)创建带out模式的存储过程
# 案例1:根据女生名，返回对应的男生名 （单个返回值）

CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b on bo.id = b.boyfriend_id
	WHERE b.name = beautyName
END $

# 调用
SET @bname $
CALL myp4('小昭',@bname) $
SELECT @bname $

# 案例2:根据女生名，返回对应的男生名和魅力值（多个返回值）
#⚠️格式！！！！！！！！！
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20), OUT usercp INT)
BEGIN
	SELECT bo.boyName, bo.userCP INTO boyName,usercp
	FROM boys bo
	INNER JOIN beauty b on bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

# 调用
CALL myp6('小昭',@bName,@usercp) $ #可以不用先声明

SELECT @bName,@usercp $

#（4）创建带inout模式参数的存储过程
# 案例1:传入a,b两个值，最终a,b翻倍
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a*2;
	SET b = b*2;
END $

set @m = 10 $
set @n = 20 $
call myp6(@m,@n) $
select @m,@n $

3. 删除存储过程

DROP PROCEDURE 存储过程名; #一次只能删除一个

4. 查看存储过程

SHOW CREATE PROCEDURE 存储过程名;

（六）触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。

下面是触发器的一些常见用途。

• 保证数据一致。例如，在 INSERT 或 UPDATE 操作中将所有州名转换为大写。
• 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
• 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
• 计算计算列的值或更新时间戳。

这是本例子的 SQL Server 版本。
输入▼

CREATE TRIGGER customer_state 
ON Customers 
FOR INSERT, UPDATE 
AS 
UPDATE Customers 
SET cust_state = Upper(cust_state) 
WHERE Customers.cust_id = inserted.cust_id; 

这是本例子的 Oracle 和 PostgreSQL 的版本：
输入▼

CREATE TRIGGER customer_state 
AFTER INSERT OR UPDATE 
FOR EACH ROW 
BEGIN 
UPDATE Customers 
SET cust_state = Upper(cust_state) 
WHERE Customers.cust_id = :OLD.cust_id 
END;

---

附：MySQL常见命令

1.查看当前所有的数据库

show databases;

2.打开指定的库

use 库名;

3.查看当前库的所有表

show tables;

4.查看其他库的所有表

show table from 库名;

5.创建表

create table 表名(
列名 列类型,
列名 列类型,
...
)

6.查看表结构

desc 表名;

7.查看服务器的版本

select version();