SQL 学习之 （一）基础知识

SQL是如何被执行的（Oracle）

1. 语法检查
2. 语义检查
3. 权限检查
4. 共享池检查 共享池是一块内存 保存SQL的执行计划 对SQL进行hash 运算 然后在共享池中查找有没有当前SQL的执行计划 如果有就直接到执行器执行这是软解析 如果没有就到优化器 创建解析树 生成执行计划到优化器再执行这是硬解析
5. 优化器 硬解析 创建解析树 生成执行计划
6. 执行器

SQL是如何被执行的（Mysql）

1. 解析器
2. 优化器
3. 执行器
4. Mysql 8之后去掉了缓存 因为缓存之后 只要有数据表更新 所有的缓存就要全部更新一遍 造成性能上的下降

Mysql的存储引擎是插件式的 常用的存储引擎有

1. InnoDB 支持外键约束 行级锁 支持事务
2. MyISAM 不支持事务 锁 特点就是快
3. NDB 主要用在mysql 集群

对一条SQL语句执行时间进行分析

1. select @@profiling; 查看当前是否开启profile
2. set profiling=1; 设置开启
3. select * from wucai
4. show profile for query 2; 查看第二次执行各部分花费的时间

数据库表的常见约束

1. 主键约束 主键唯一标示一条记录 不能重复 不能为null 可以是一个字段也可以是多个字段的组合
2. 外键约束 一个表的外键是另一个表的主键 建立两张表的关系
3. 唯一性约束 创建一个普通索引和约束 和 普通索引不一样 普通索引不会保证唯一性
4. NOT NULL 不能为空约束
5. DEFAULT
6. CHECK

数据表设计的原则 三多一少

1. 数据表尽量少
2. 数据表字段尽量少
3. 联合主键少
4. 主键 外键尽量多

对表常见操作

1. ALTER TABLE player ADD (player_age int(11)); 添加列
2. ALTER TABLE player RENAME COLUMN player_age to age; 修改列名
3. ALTER TABLE player DROP COLUMN age; 删除列

查询

1. SELECT NAME, hp_max FROM heros; 查询列
2. SELECT DISTINCT mp_max FROM heros;去重复行
3. SELECT DISTINCT name,mp_max FROM heros; 组合起来的不重复
4. SELECT name, hp_max,mp_max FROM heros ORDER BY mp_max ASC; 排序查询 ASC升序 DESC 降序
5. SELECT name, hp_max,mp_max FROM heros ORDER BY mp_max ASC LIMIT 10; 限制查询数量
6. 不推荐使用 SELECT * 如果确定就1条数据 最好使用 LIMIT 1
7. 执行顺序
   SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

过滤查询结果

1. 比较运算符 >、 <、 >= 、 <=、!= 、IS NULL 、BETWEEN
2. 逻辑运算符 AND OR IN NOT 执行优先级 () > AND > OR

SQL 函数

1. 算数函数 ABS 取绝对值 MOD 取余 ROUND 四舍五入指定位数的小数 例如SELECT ROUND(13.5,0) 输出 14
2. 字符串函数
   拼接函数 SELECT CONCAT(“asd”,“zxc”,“vbbn”); // asdzxcvbbn
   LENGTH SELECT LENGTH(“这是33sad”); // 11 一个汉字3个字符
   CHAR_LENGTH SELECT CHAR_LENGTH(“这是33sad”); // 7
   LOWER
   UPPER
   REPLACE 替换
   SUBSTRING 截取
3. 时间函数
   只有日期 SELECT CURRENT_DATE();
   只有时间 SELECT CURRENT_TIME();
   日期加时间 SELECT CURRENT_TIMESTAMP();
   字符串转日期 SELECT TIMESTAMP(‘2020-06-17 11:30:53’);
4. 练习
   保留两位小数 SELECT name , ROUND(attack_growth,2) FROM heros ;
   查询出最大值 SELECT MAX(hp_max) FROM heros;
   两次查询 第一次查询的美国是第二次查询的条件 SELECT name, hp_max FROM heros WHERE hp_max = (SELECT MAX(hp_max) FROM heros);
   按时间查 SELECT name, EXTRACT(YEAR FROM birthdate) FROM heros WHERE birthdate IS NOT NULL;
   日期比较 SELECT * FROM heros WHERE DATE(birthdate) > ‘2016-10-01’; 要用DATE进行转一下 更安全一点
5. 使用SQL函数 可能会因为数据库管理系统的差异 而 出现问题

聚集函数

1. GROUP BY 以那个字段进行分组 SELECT role_main, COUNT(*) FROM heros GROUP BY role_main;
2. 根据所选字段的组合进行分组 SELECT role_assist, role_main, COUNT(*) FROM heros GROUP BY role_main, role_assist;
3. 使用HAVING 对分组进行过滤 SELECT role_assist, role_main, COUNT(*) as num FROM heros GROUP BY role_main, role_assist HAVING num > 5;
4. 练习 SELECT COUNT(*) as num ,role_assist,role_main FROM heros WHERE hp_max > 6000 GROUP BY role_assist , role_main HAVING num > 5 ORDER BY num DESC;

子查询

1. 关联子查询 和 非关联子查询 通过子查询的执行次数判断 如果执行一次就是非关联子查询 如果执行多次就是关联子查询
2. 非关联子查询举例

SELECT player_name, height FROM player WHERE height = (SELECT MAX(height) FROM player);

3. 关联子查询

SELECT player_name , height, team_id FROM player WHERE height > (SELECT AVG(height) FROM player); 每执行一次外部 子查询都要执行一次

4. EXISTS 子查询

SELECT player_name, player_id, team_id FROM player 
WHERE EXISTS (SELECT player_id FROM player_score WHERE player_score.player_id = player.player_id); 
EXISTS后边 放的是条件 True 或 False

5. 将子查询作为条件

SELECT team_name, (SELECT COUNT(*) FROM player WHERE team.team_id = player.team_id) FROM team

6. 练习

SELECT player_id, player_name FROM player WHERE player_id IN (SELECT player_id FROM player_score GROUP BY player_id HAVING AVG(score) > 20)

如何使用链接

1. 笛卡尔积 也叫做交叉连接 SELECT * FROM player, team
2. 等值链接
3. 非等值链接

SELECT p.player_name, p.height, h.height_level FROM player as p, height_grades as h WHERE p.height BETWEEN h.height_lowest AND h.height_highest;

4. 左外链接

SELECT * FROM player LEFT JOIN team ON player.player_id = team.team_id  左表会出现全部的列

5. 右外链接

SELECT * FROM player RIGHT JOIN team ON player.player_id = team.team_id 右表就是主表 会展示所有的列

6. 自链接

SELECT b.player_name , b.height FROM player as a, player as b 
WHERE a.player_name = '布雷克-格里芬' AND a.height < b.height

7. 练习

SELECT t1.team_name, t2.team_name FROM team as t1 , team as t2 WHERE t1.team_id != t2.team_id

8. 练习

SELECT h.height_level, COUNT(*) FROM player as p, height_grades as h WHERE p.height BETWEEN h.height_lowest AND h.height_highest GROUP BY h.height_level

什么是视图 如何创建使用视图

1. 视图是一张虚表 相当于一张表或多张表的数据结果集 可以简化SQL查询 不会更改底层的数据
2. 创建视图

CREATE VIEW view_name AS SELECT column1, column2 FROM tableWHERE condition

3. 创建一个大于平均身高的视图

CREATE VIEW player_above_avg_height AS 
SELECT player_id, height FROM player
WHERE height > (SELECT AVG(height) FROM player)

4. 查视图

SELECT *FROM  player_above_avg_height

5. 在视图上再创建视图

CREATE VIEW player_above_above_avg_height  AS
SELECT * FROM player_above_avg_height 
WHERE height > (SELECT AVG(height) FROM player_above_avg_height) 

6. 查询

SELECT * FROM player_above_above_avg_height

7. 修改视图

ALTER VIEW player_above_avg_height AS SELECT player_id, player_name, height FROM player;

8. 练习

SELECT CONCAT(player_name,'(',t.team_name,')') as 
player_team FROM player as p JOIN team as t ON p.team_id = t.team_id;

9. 两大特点 安全性 和 简单清晰

存储过程

1. 存储过程就是对SQL语句的封装 存储方式一旦创建 就可以像 调用函数一样使用
2. 创建存储过程

CREATE PROCEDURE `add_num3`(IN n INT)
BEGIN
	DECLARE i INT;
	DECLARE sum INT;
	SET i = 1;
	SET sum = 0;
	WHILE i <= n DO
				SET sum = sum + i;
				SET i = i + 1;
	END WHILE;
	SELECT sum;
END

3. 调用存储过程

CALL add_num(100);

4. IN OUT INOUT 传入参数的区别

IN  向存储过程中传入参数的值可以修改 但不能作为返回值
OUT  把存储过程计算的结果放到该参数中 调用者可以得到返回只
INOUT 个人感觉和OUT差不多

5. OUT 举例

CREATE PROCEDURE `get_hero_scores`(
		OUT max_max_hp FLOAT,
		OUT min_max_mp FLOAT, 
		OUT avg_max_attack FLOAT, 
		s VARCHAR(255)
		)
BEGIN
		SELECT MAX(hp_max), MIN(hp_max),AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp ,avg_max_attack;
END
CALL get_hero_scores(@max_max_hp,@min_max_mp,@avg_max_attack,'战士');
SELECT @max_max_hp,@min_max_mp,@avg_max_attack;

6. 数据库就算是关闭了 存储过程也不会消失
7. 练习 创建一个存储过程 并且执行

CREATE PROCEDURE `get_sum_score1`(OUT sum_hp_max INT, OUT s_role_main VARCHAR(255))
BEGIN
	SELECT SUM(hp_max) ,role_main FROM heros WHERE role_main = '战士' INTO sum_hp_max,s_role_main;
END
CALL get_sum_score1(@sum_hp_max,@s_role_main);
SELECT @sum_hp_max , @s_role_main;

事务

1. 什么是事务 事物是执行一个事情的完整过程 要么其中都执行完毕 要么都执行失败
2. 显示事务 就是 由开发者手动指定BEGIN 和 COMMIT的操作 隐式事务 是当我们显示执行完一个事务后Mysql会默认开始一个事务 并且自动提交
3. 练习一 练习两个BEGIN 都是显式事务

CREATE TABLE test(name VARCHAR(255), PRIMARY KEY (name)) ENGINE=INNODB;
BEGIN;
INSERT INTO test (name) VALUES('关羽');
COMMIT;
BEGIN;
INSERT INT test (`name`) VALUES('张飞');
INSERT INT test (`name`) VALUES('张飞');
ROLLBACK;
SELECT * FROM test;

执行结果 之后关羽
默认情况下 mysql事务是 自动提交 但是 BEGIN开始的必须显式的使用COMMIT才会提交 所以回滚后只有一条关羽 第二次BEGIN 没有显式的提交 由于name是主键 所以 插入两条张飞失败 会滚到只有一条关羽

4. 练习二

BEGIN;
INSERT INTO test (name) VALUES('关羽');
COMMIT;
INSERT INTO test (name) VALUES('张飞');
INSERT INTO test (name) VALUES('张飞');
ROLLBACK;
SELECT * FROM test;

执行结果是 关羽和张飞两条记录
第一次插入张飞 自动提交 第二次违背了主键约束 所以插入失败 回滚的时候 就回滚到 第一次插入成功张飞的位置

5. completion_type 参数含义
   completion_type = 0 这是默认情况 就是我们执行一个COMMIT之后 需要手动调用BEGIN 来开启另外一个事务
   completion_type = 1 当我们执行一个COMMIT 之后 相当于开启一个相同隔离级别的事务
   completion_type = 2 提交后会自动的与服务器断开链接
   例如

BEGIN;
SET @@completion_type = 1;
INSERT INTO test (name) VALUES('关羽');
COMMIT;
INSERT INTO test (name) VALUES('张飞');
INSERT INTO test (name) VALUES('张飞');
ROLLBACK;
SELECT * FROM test;

执行结果 只有关羽
当 completion_type=1时 当执行完COMMIT之后 会再次开启一个相同级别的事务 所以在执行ROLLBACK之前的任务都在同一个事务中 插入两条张飞报错 所以只能回滚到第一次commit的位置

6. 事务的异常情况

   • 脏读 读到了其他事务还没有提交的内容
   • 不可重复读 第一次读和第二次读 读到的结果是不一样的
   • 幻读 是对多条数据的 第一次查询有5条 第二次查询因为另外一个事务执行了还没有提交的更新处理 导致幻读

7. 事务的隔离级别

   • 读未提交 没有任何限制
   • 读已提交 可以避免脏读
   • 可重复读 避免不可重复读(Mysql 默认)
   • 可串行化 可以避免所有