学习SQL知识总结之基础篇

个人学习总结，内容较多。
如有错漏，请指正，谢谢！

SQL 基础知识整理

SQL语句是怎么执行的？

SQL语句在oracle中的执行过程：
1.语法检查，检查语句的语法是否存在问题。
2.语义检查，检查语句的寓意是否存在问题。
3.权限检查，检查当前用户是否有访问权限。
4.共享池

1. 若存在共享池中->优化器（软解析）
2. 若不存在共享池中->解析器->执行器（硬解析）

共享池是oracle中的术语，主要包括了库缓存以及数据字典缓冲区。
库缓存存储了SQL语句以及执行计划，数据字段缓冲区存储的是Oracle对对象的定义，比如表、视图、索引等。

主要用于判断该SQL语句是硬解析还是软解析。

SQL语句在MySQL中的执行过程：
1.查询缓存，服务端判断查询缓存中是否存在该条语句，若存在则直接返回给客户端，若不存在，则进入解析器。（查询缓存效率不高，MySQL8.0后，取消了查询缓存）
2.解析器，分析语句中的语义和语法。
3.优化器，会确定语句的执行路径，比如：判断是全表检索还是根据索引检索。
4.执行器，在执行语句之前，会先判断当前数据用户是否有权限访问，若有权限则返回执行结果。（MySQL8.0之前若设置了查询缓存，会将执行结果进行缓存）

SQL语句的语言功能划分

语言	功能
DDL（Data Definition Lanuage）	数据库定义语言，用于定义数据库、数据表、列等。可以帮助我们增加、修改、删除数据库以及数据表。
DCL（Data Control Lanuage）	数据库控制语言，用于控制用户可访问的数据。
DML（Data Manipulate Lanuage）	数据库操作语言，用于操作数据库，对数据库的数据进行增、删、改等
DQL（Data Query Lanuage）	数据库查询语言，用户查询数据库，数据表的信息。

DDL，常用语法有

--增加数据库
CREATE DATABASE data_db;
--删除数据库
DROP DATABASE data_db;
--创建表
CREATE table1(id INT identity);
--删除表
DROP TABLE table1;
--修改表
ALTER TABLE table1 ...
  --增加列 ->add column col_name type;
  --删除列 ->drop column col_name;
  --修改列名  ->rename column col_name to new_col_name;
  --修改属性的类型 ->modify column col_name type

SQL语句各个关键字查询顺序：

SELECT count(*) AS num,distinct t1.relate_id FROM table1 as t1 left join table2 as t2   USING(id) WHERE t1.times > "2020-01-01" GROUP BY id having num > 200 ORDER BY t1.relate_id desc limit 100;

form->where->group by ->having->select 查询内容->distinct->order by ->limit

常用的函数

计算函数

函数名	作用
MOD	取余，MOD(4,2) -> 4%2
AVG	求平均值 AVG(list)，需要的是数据列表，相当于是聚集函数
ROUND	四舍五入，ROUND(data,2)，data是需要四舍五入的数据，2是保留两位小数
ABS	求绝对值，ABC(-1)->1
GREATEST	取当前列，设置列名中最大的数值

GREATEST的用法为：
例如：有表数据字段为以下表格

id	mon	thues	Wed
1	2	3	4

那么下面这句伪语句的结果是什么呢？

select GREATEST(mon,thues,wed)
## 输出结果为4

---

转换函数

函数名	作用
CAST	转换函数，CAST(A AS B)，B为转换的类型，A为需要转换的数据
COALESCE	返回参数中第一个不为空的数，COALESCE(VALUE…)，可以有多个参数

这边需要注意的是，CAST中的B，不是正常的INTEGER等数据类型而是要参照以下的表格

支持的类型	具体描述
BINARY	二进制型
CHAR	字符型
DATE	日期，格式为 ‘YYYY-MM-DD’
DATETIME	日期加具体的时间，格式为 ‘YYYY-MM-DD HH:MM:SS’
TIME	时间，格式为 ‘HH:MM:SS’
DECIMAL	float 型
SIGNED	int 型
UNSIGNED	无符号int

select cast(2.3 as SIGNED) #输出结果为：2

---

字符串函数

函数名	作用
SUBSTRING	截取字符串，有三个参数，第一个参数，需要截取的数据，第二个参数，截取数据的起点，第三个参数截取数据的终点
REPLACE	替换函数，有三个参数，第一个参数，需要替换的数据，第二个参数，替换数据中需要替换的字符串，第三个参数，需要替换成什么字符串
CONCAT	拼接字符串，可以将数字与字符串进行拼接，用法为：CONCAT(“USER”,“user2”)->USERuser2
LENGTH	返回字符串的长度，汉字返回三个字节，字母和数字返回一个字节
CHAR_LENGTH	返回字符串的长度，汉字、字母、数字都是返回一个字节
LOWER	将字母转换成小写
UPPER	将字母转换成大写
GROUP_CONCAT	使用GROUP BY 语句时，可以将SELECT的字段合并在一起，用“，”分隔，这查询后，若有不一样的字段，也会合并为一行显示。
substring_index	根据字符串中的字符截取字符串，substring_index(column,‘字符’,1)
left	从左开始截取字符串,例如left(date,7),从左开始截取日期7位，获得结果：2020-11
right	从右开始截取字符串,例如right(date,7),从左开始截取日期7位，获得结果：0-11-13

GROUP_CONCAT的使用方法

select school_no,student_name  from student group by school_no,student_name;

以上sql会查询出关于school_no的多行数据

school_no	student_name
1001	张三
1001	李四
1001	王五
1002	赵六

若使用了GROUP_CONCAT后

select school_no,GROUP_CONCAT(student_name)  from student group by school_no,student_name;

结果就如以下表格：

school_no	student_name
1001	张三,李四，王五
1002	赵六

GROUP_CONCAT的用法为

GROUP_CONCAT([distinct] column [order by column desc|asc])

substring_index的用法

用上面GROUP_CONCAT查询出来的数据进行对比

select substring_index(name,',',1) from (select school_no,GROUP_CONCAT(student_name) name from student group by school_no,student_name);

已知name为张三，李四。那么substring_index语句中，第二个参数就是在字符串中查找“，”找到后，根据第三个参数进行截取，为1就截取“，”前的字符串（张三），为2就截取“，”前的两个字符串*（张三,王五）以此类推。所以该语句输出结果就为:

name
张三
王五

---

日期函数

函数名	作用
CURRENT_DATE	获取当前日期：yyyy-MM-dd
CURRENT_TIME	获取当前时间：HH:mm:ss
CURRENT_TIMESTAMP	获取当前日期时间：yyyy-MM-dd HH:mm:ss
EXTRACT	抽取日期时间的年月日时分秒，用法：EXTRACT(until from data)->until可以使用YEAR获取年，MONTH获取月等，data就是需要抽取的日期，如2020-10-01 20:48:10。
DATE	获取当前日期部分
YEAR	获取当前年
MONTH	获取当前月，由于月份是0-11月的所以若要获取正确的月份，需要+1
DAY	获取当前日
HOUR	获取当前小时
MINUTE	获取当前分钟
SECOND	获取当前秒钟
DATE_SUB	减日期
DATE_ADD	加日期
DATEDIFF	计算两个日期之间的天数

date_sub 的用法为：DATE_SUB(date,INTERVAL val expr);
date_add 的用法为：DATE_ADD(date,INTERVAL val expr);
其中val必须为数值，可为正负数，若SUB中 为负数的话，实际上就是+val，--得加嘛。同样的ADD中为负数就是-val。而expr则是你要操作的格式可为YEAR|MONTH|DAY|HOUR|MINUTE|SECOND等

主要用于查询date之前的数据或者date之后的数据，这种未来过去的数据。

其中DATE、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND
这都是一样的用法，抽取传入的时间中的年月日时分秒。
若传入的时间格式错误，或者数据错误，则返回NULL。
例如：

SELECT DATE("2020-01-01 20:20:20");-- 2020-01-01
SELECT DAY("2020-01-01 20:20:20"); -- 01
SELECT YEAR("2020-01-01 20:20:20"); -- 2020
SELECT hour("2020-01-01 20:20:20"); -- 01

DATEDIFF的用法为：

select datediff('2020-10-20','2020-10-19') ##结果为1
select datediff('2020-10-20','2020-10-21') ##结果为-1

---

聚集函数

函数名	作用
SUM	返回查询出来某个字段的数据的总和，例如费用总和->SUM(amount)
COUNT	返回集合的长度
AVG	返回集合中某个字段的平均值，例如费用平均值->AVG(amount)
MAX	返回查询集合中某个数据的最大值，例如查询离当前时间最近的值->SUM(cur_date)
MIN	返回查询集合中魔偶个数据的最小值，例如查询收入最低的人->MIN(salary)

子查询

关联子查询与非关联子查询

关联子查询就是主表与从表有关联关系的连接查询。
非关联子查询就是主表和从表没有任何关系的连接查询。

EXIST查询

EXIST就是判断主表中的某个属性是否能在从表中查询到。
NOT EXIST就是与EXIST相反

SELECT * FROM users u where EXIST(SELECT * FROM roles r WHERE r.userid = u.id)

SELECT * FROM users u where NOT EXIST(SELECT * FROM roles r WHERE r.userid = u.id)

集合比较子查询

命令	作用
IN	判断主表字段是否在从表中
ANY	需要与比较操作符一起使用，和返回集合的任何值做比较
ALL	需要与比较操作符一起使用，和返回集合的任何值做比较

这其中 IN 与EXIST的返回的结果是相同的，但是不同的场景就需要使用不放的方法。

SELECT * FROM A a WHERE a.id IN (SELECT aid from B)

SELECT * FROM A a WHERE EXIST (SELECT * FROM B WHERE aid = a.id )

从以上可以看出，B表是A的从表。若A表大于B表的时候，此时若B表的aid列建了索引，那么使用IN的效率就会更高。若A表小于B表的时候，此时若A表的id列建了索引，那么就不需要再B表中查询aid列，那么使用EXIST的效率就会更高。

若主表大于从表，则使用IN
若主表小于从表，则使用EXIST

常用的SQL标准有哪些不同的连接方式

SQL标准有很多，但是常用的标准就是SQL92与SQL99。
SQL92的连接：
1.笛卡尔积又称交叉连接。
返回连接表的所有属性。

SELECT * FROM player as p,team as t

2.等值连接将主表与从表中的等值数据进行连接。

SELECT * FROM player as p,team as t where p.id = t.player_id;

非等值连接就是不使用等号的连接方式。

SELECT * FROM player as p ,team as t where p.score < t.score; 
--或者
SELECT * FROM player as p ,height_grades as h where p.height between h.lowheight and h.highheight;

3.外连接
外连接分为左外连接、右外连接（在SQL99中存在全外连接）
左外连接：以左侧的表为主表，右侧的表为从表。返回左侧主表的所有属性的值（包括null），与右侧从表的连接属性。
右外连接：与左外连接相反。

以哪侧的表为主表，若主表连接的属性有空值，也会一起返回。

在SQL92中使用（+）表示左外连接还是右外连接

--左外连接
SELECT * FROM table1 t1 ,table2 t2 where t1.id(+) = t2.id
--右外连接
SELECT * FROM table1 t1 ,table2 t2 where t1.id = t2.id(+)

4.自连接主表与从表都为一张表，使用同一张表进行连接查询。
比如若要查询比张三年龄小的人出来
不使用自连接的方式，使用子查询的话，就需要两步来查询

--1.查询张三的年龄->20
SELECT age FROM person WHERE name = '张三';
--2.通过返回的age再查询比他小的人
SELECT * FROM person WHERE age<20;

而使用自连接的方式，只需要一步就可以查询出来，效率会比子查询更高。

SELECT * FROM person p1,person p2 WHERE p1.name = '张三' AND p2.age < p1.age;

在SQL99标准中，比SQL92标准的可读性更高，例如SQL92使用(+)来表示左外连接，还是右外连接，而SQL99中使用的是直观的LEFT JOIN 与RIGHT JOIN 来表示。

1.交叉连接其实就是SQL92的笛卡尔积连接，只不过SQL99中使用的是CROSS JOIN

SELECT * FROM player p CROSS JOIN team t where p.team_id = t.team_id;

2.自然连接其实就是SQL92的等值连接，在SQL99中使用的是NATURAL JOIN

SELECT * FROM player NATURAL JOIN team

NATURAL JOIN team就相当于where p.team_id = t.team_id;

3.ON连接可以在ON连接后面可以使用等值连接或者非等值连接

--等值连接
SELECT * FROM player p JOIN team t ON p.team_id = t.team_id;

--非等值连接
SELECT * FROM heros hero JOIN heros_growth hg ON hero.growth BETWEEN hg.lowergrowth AND hg.highgrowth;

4.USING连接可以使用两张表中相同字段的值来进行连接，使用USING比直接使用等于号效率更高一点。

SELECT * FROM player JOIN team USING(team_id);

5.外连接 SQL99中在SQL92的基础上增加了全外连接，并将92的语法调整得更可读。

SQL99中使用LEFT JOIN 表示左外连接、RIGHT JOIN 表示右外连接，FULL JOIN表示全外连接

--左外连接
SELECT p.player_name,t.team_name FROM player p LEFT JOIN team t USING(team_id)

--右外连接
SELECT t.team_name,p.player_name FROM player p RIGHT JOIN team t USING(team_id)

--全外连接
--返回两张表匹配的属性
--返回左表未匹配的属性
--返回右表未匹配的属性
SELECT * FROM player p FULL JOIN team t USING(team_id)

MySQL不支持FULL JOIN ，Oracle、DB2、SqlServer支持FULL JOIN。
若想要在MySQL中使用全外连接，则需要将左外连接与右外连接通过UNION连接起来

SELECT p.player_name,t.team_name FROM player p LEFT JOIN team t USING(team_id)
UNION 
SELECT t.team_name,p.player_name FROM player p RIGHT JOIN team t USING(team_id)

SQL视图
创建视图，将数据表的数据结构进行封装，提供一个对外接口，与较好的安全性。

CREATE VIEW view_name
AS
SELECT team_name as tname ,player_name as pname FROM player p LEFT JOIN team t USING(team_id);

那么就可直接查询view_name视图
视图中只有tname、pname两个字段。

若视图中查询表的数据有改动，查询视图中的数据也会跟着改动。

存储过程

如何创建存储过程，在存储过程中可能需要的几个步骤
1.创建存储过程，声明存储过程的名称以及参数。

CREATE PROCEDURE procudure_name(IN data_name VARCHAR,OUT result_name VARCHAR)
BEGIN
	...
END

参数中存在IN（输入的参数，不能改变）、OUT（输出的参数，可以改变）

2.声明变量
存储过程中若要使用变量就必须声明变量，否则会报错。

...
BEGIN
	DECLARE i int default 0;
	...
END

3.赋值

SET i = 1;

4.循环
有三种循环方式：

• LOOP
• REPEAT … UNTIL … END REPEAT
• WHILE … DO … END WHILE

LOOP 进行无限循环，在循环体内判断跳出循环

label: LOOP
	statement_list
	IF exit_condition THEN
		LEAVE label; --离开循环
	END IF; 
END LOOP label;

REPEAT … UNTIL … END REPEAT类似于JAVA的do{…}while(condition)先进入循环，再判断是否跳出循环

REPEAT
	statement_list
UNTIL search_condition END REPEAT;

WHILE … DO … END WHILE 类似与JAVA的while(condition){…}，先满足条件才能进入循环。

WHILE search_condition DO
	statement_list
END WHILE;

5.流程控制

• IF … THEN … ELSEIF … THEN … ELSE … END IF
  类似于JAVA中简单的IF条件判断

IF search_condition THEN
	statement_list
ELSEIF search_condition THEN
  statement_list
ELSE
	statement_list
END IF;

• CASE … WHEN … THEN … ELSE … END CASE
  类似于JAVA中的switch(){ case … }

CASE case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;

• SELECT … INTO …
  查询数据批量赋值变量
  可以直接赋值OUT的变量

CREATE DEFINER=`root`@`localhost` PROCEDURE `procudure_name`(OUT result_name VARCHAR(35))
BEGIN
	select team_name from  team where team_id = '1001' INTO result_name;
END

然后执行命令

call  procudure_name(@result_name);
select @result_name

若使用INOUT或者OUT，传入参数则必须为@result_name，result_name为存储过程参数的名称。

什么是事务？

启动事务后的所有基本单元，要么全部执行，要么全部不执行。若是事务后，有语句执行错误，那么前面的语句无论有没有成功，都将数据进行回滚到之前最新的数据，确保数据的一致性，这也是事务其中一种特性。

事务的特性是什么？
事务的特性：ACID
ACID就是事务的四个特性。其中，A表示原子性，C表示一致性，I表示隔离性，D表示持久性。

A：Atomicity，原子性就是表的基本单位是不可分割的。
C：Consistency，一致性就是将数据的一致性状态变为另一种一致性状态，也就是数据改变后，表的完整性约束不可被改变。（加锁，是保证事务的一致性的方法。）
I：Isolation，隔离性就是让一个事务执行完成之前，其他事务获取不到该事务的信息。
D：Durability，持久性就是在提交事务之前，系统宕机，还能有方式能恢复这期间的数据。用的是写前日志，在语句写入之前，就将语句保存在事务的重写日志中，事务的日志包括（回滚日志、重写日志）。

这四大特性中，隔离性是基础，一致性是约束条件，隔离性是手段，持久性是目的。

如何启动事务？

• START TRANSACTION 或者 BEGIN，开启一个显示事务。
• COMMIT 提交一个事务
• ROLLBACK 或 ROLLBACK TO [SAVEPOINT]，当执行异常后，会回滚当前事务，或回滚到当前事务的一个保存点。
• SAVEPOINT 在事务执行前，创建一个保存点。
• RELEASE SAVEPOINT 删除一个保存点
• SET TRANSACTION 设置事务的隔离级别

Oracle中的事务不自动提交，需要自己使用commit来提交事务，而MySQL中时自动提交的，若不需要自动提交就需要对autocommit进行设置，那么就需要begin启动事务，commit提交事务，rollback回滚事务。这就是隐式事务与显示事务。

set autocommit = 0;
--查询autocommit值
select @@autocommit;

关于事务，MySQL就要说一下它的存储引擎， 在MySQL5.0之前的默认存储引擎是MyISAM，这个是不支持事务的，但是执行速度快。
而在MySQL5.0之后的默认存储引擎使用的是InnoDB，该存储引擎支持事务，行级锁，以及外键。
所以，若是数据库只用来读取的话，那存储引擎就选择MyISAM。

也可以查看MySQL中有哪些存储引擎

show engines;

事务的隔离
事务的隔离性是事务的基本特性之一，主要用于并发的背景下， 保证数据的一致性。有些情况需要降低一些正确性从而提高效率，有时候的数据背景可能不允许出错，那就需要将隔离调到最高，从而牺牲执行效率。

事务并发处理可能产生的异常，这些异常与隔离级别在SQL92标准中有得到定义，这些异常就是脏读、不可重复读、幻读。

脏读：一个事务读到了其他事务还没有提交的数据。此时其他事务可能会回滚，那么读到的数据就是脏数据。

不可重复读：一个事务使用一条语句进行查询后，有其他事务对这条语句的数据进行修改，这时这个事务又将该语句重新执行了一遍，这时就会发现查询出来的数据不一致，这就是不可重复读。

幻读：一个事务使用一条语句进行查询得到集合后，有其他事务对该表进行增加或删除，这时该事务又使用同一条语句重新执行了一遍，这时，会发现查询出来的数量不一致。

事务有四大隔离级别，级别从低到高有：

1. 读不提交（READ UNCOMMITTED）：隔离级别最低，并发的问题都会存在。
2. 读已提交（READ COMMITTED）: 能解决脏读，而不可重复读与幻读都还存在。
3. 可重复读（REPEATABLE READ）：能解决脏读，不可重复读，而幻读还存在。
4. 可串行化（SERIALIZABLE）：能解决所有的并发问题。

隔离级别从低到高，也表示了执行效率也是从低到高。有时就需要降低正确性从而提高执行效率。

浮标

有些情况不需要对所有结果集的所有数据行都采用相同的处理方式，这些情况就可以使用面向过程的编程方式，浮标是一种面向过程的编程方式。

什么是游标？
游标是一种临时的数据库对象，可以充当指针的作用，指向的是存储在数据库中的数据行指针。操作游标，就相当于操作数据航。

怎么使用游标？
DBMS不同使用游标的方式可能略有不同

1.创建游标
MySQL，SQLServer，DB2，MariaDB中打开游标的方法

CREATE cursor_name CURSOR FOR select_elementlist;

Oracle，PostgreSQL中略有不同

CREATE cursor_name CURSOR IS select_elementlist;

select_elementlist是查询的结果集

CREATE cursor_name CURSOR FOR select tname from player

2.打开游标

OPEN cursor_name;

游标打开后，会将查询的结果集发送到游标工作区中。

3.提取数据

FETCH cursor_name INTO varname...;

从游标中提取出当前行通过INTO进行赋值，若由表中的列有多个，赋值的参数写多个即可。

4.关闭游标

CLOSE cursor_name;

有打开就有关闭，若关闭游标后，就不允许查询到结果集，若想要继续查询，就得打开游标。

5.释放游标

DEALLOCATE cursor_name

有DECLARE就需要有DEALLOCATE，要养成释放游标的习惯，否则游标就会一直存储在内存中，知道进程结束后才会释放。

一个比较复杂的例子：模拟英雄联盟中版本升级带动英雄攻击力升级的存储过程。
若英雄的攻击成长（attack_growth）小于5，并且攻击力成长空间小于200（attack_max - attack_start）则攻击成长提升10%。若在150到200之间，则攻击力成长提升8%。若小于150，则攻击力成长提升7%。

若攻击成长大于5小于10，则提升5%。
若大于10，则保持不变。

CREATE DEFINER=`root`@`localhost` PROCEDURE `attack_growth_pro`()
BEGIN
	#定义临时变量
	DECLARE temp_id INT;
	DECLARE temp_growth,temp_diff,temp_max,temp_start FLOAT;
	#创建游标结束参数
	DECLARE DONE INT DEFAULT false;
	#创建游标
	DECLARE growth_cursor CURSOR FOR 
	SELECT id,attack_growth,attack_max,attack_start FROM heros;
	#若游标结束则将DONE赋值为TRUE
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
	#开启游标，并将查询的集合放到游标处理区中
	OPEN growth_cursor;
	#读取游标的参数并赋值
	FETCH growth_cursor into temp_id,temp_growth,temp_max,temp_start;
	#循环，相当于do{selectment}while(contition)
	REPEAT
		IF NOT DONE THEN
			SET temp_diff = temp_max - temp_start;
			IF temp_growth < 5 THEN
				if temp_diff > 200 THEN
						SET temp_growth = temp_growth + temp_growth*0.1;
				ELSEIF temp_diff <200 and temp_diff > 150 THEN
						SET temp_growth = temp_growth + temp_growth*0.08;
				ELSEIF temp_diff < 150 THEN
						SET temp_growth = temp_growth + temp_growth*0.07;
				END IF;
			ELSEIF temp_growth > 5 and temp_growth<10 THEN
					SET temp_growth = temp_growth + temp_growth*0.05;
			END IF;
			UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
		END IF;
	-- 将游标指针指向下一行
	FETCH growth_cursor into temp_id,temp_growth,temp_max,temp_start;		
	UNTIL DONE = true END REPEAT;
END

如何求两个集合的差集
可以用NOT IN

select * from tables1 where id not in (select id from tables2 where date between date1 and date2)

但是这种方式的效率较低

还有第二种方式，使用 left join

select t1.id from tables1 t1 left join tables2 t2 on t1.id = t2.id where t2.id is null;

查询出两个表的并集，这是将表2与表1相等的值置为null，这时查出来的就是不相等的值了。