本文主要是介绍重学SQL 基础,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
SQL 基础
SQL 执行流程:
sql语句->缓存查询(有)->输出结果
sql语句->缓存查询(无)->解释器->优化器->执行器->加入缓存查询中->输出结果
mysql8之后取消了缓存
SQL 查看执行时间:
-- 查看profiling是否打开,0是没打开,1是打开
SELECT @@profiling;-- 设置profiling打开
SET profiling=1;-- 查看一条数据
SELECT * FROM mysql.db;-- 查看上一条查询的详细执行时间
SHOW profile;-- 查询所有的查询执行,如果要看详情需要配合SHOW profile for query 1(这是profiles中的query ID)
SHOW profiles;-- 想看profiles中某一条查询的执行时间,使用这个命令
SHOW profile for query 1
查看执行计划:
使用EXPLAIN,一般可以看执行sql语句用了什么索引
EXPLAIN SELECT * FROM t1
SQL 关于编码:
关于utf-8:
- utf8_general_ci:排序规则对大小写不敏感
- utf8_bin:排序规则大小写敏感
SQL 关于行格式:
- fixed(静态表):每条记录所占用的字节一样
- 优点读取快,缺点浪费额外一部分空间
- dynamic(动态表,默认):每条记录所占用的字节是动态的
- 优点节省空间,缺点增加读取的时间开销
SQL 关于索引/约束:
索引:
- UNIQUE INDEX:唯一索引
- NORMAL INDEX:普通索引
唯一索引和普通索引的区别就是唯一约束
索引方法:
- BTREE:B-Tree是最常见的索引类型,所有值(被索引的列)都是排过序的,每个叶节点到跟节点距离相等
约束:
- 主键约束:唯一索引加非空约束
- 外键约束:一般不建议设置外键
- 唯一约束:唯一索引
- 非空约束:不能为空
表设计原则:
表设计原则:三少
- 数据表个数越少越好
- 数据表字段个数越少越好
- 数据表联合主键越少越好
基础
常量:
-- constant 为常量的别名,当然可以不用别名,那就字段内容就是字段名
SELECT '这是一个常量' as constant, name FROM heros;
DISTINCT 去重:
- DISTINCT 是对后面所有列的组合进行去重
ORDER BY 排序:
- ORDER BY 后面可以有一个或多个列名,如果是多个列名进行排序,会按照后面第一个列先进行排序,当第一列的值相同的时候,再按照第二列进行排序,以此类推
LIMIT 返回结果数量:
- LIMIT 返回结果的的数量
SELECT 执行顺序:
FROM(包括 JOIN ) > WHERE > GROUP BY(包括聚合函数) > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT
多表联查的步骤:
- FROM:数据库会先产生一个虚拟表vt1-1,如果有联表会根据ON条件筛选得到vt1-2,如果还有其它外联表,就会继续拓展在原有基础新增外部行,得到vt1-3,以此类推,最终得到完整数据虚拟表vt1
- WHERE:根据筛选条件,筛选vt1,得到虚拟表vt2
- GROUP BY:根据分组,如果有聚合函数就执行聚合函数,得到虚拟表vt3
- HAVING:根据筛选得到虚拟表vt4
- SELECT,DISTINCT:其实是在一个大步骤里,根据SELECT筛选得到vt5-1,再根据DISTINCT去重得到完整的vt5
- ORDER BY:排序得到vt6
- LIMIT:筛选数据得到最终的返回数据虚拟表vt7
可以看出来,所有的步骤都是在上一步骤的虚拟表上进行操作得到新的虚拟表,从第一步也就能看到如果外联很多表,那我们将得到一个特别特别大的虚拟表,这是很消耗资源的
效率:COUNT(*) = COUNT(1) > COUNT(字段)
优先级:
( ) > AND > OR
LIKE:
- %在开头会对全表进行扫描,比如:%三
- %在最后如果字段有索引就不会全表扫描,比如:张%
需要用LIKE的字段最好加索引
SQL函数
算术函数:
- ABS( ):绝对值
-- 运行结果为 2
SELECT ABS(-2)
- MOD( ):取余
-- 运行结果 2
SELECT MOD(101,3)
- ROUND( ):四舍五入为指定的小数位数,需要有两个参数,分别为字段名称、小数位数
-- 运行结果 37.3
SELECT ROUND(37.25,1)
字符串函数:
-- 运行结果为 abc123
SELECT CONCAT('abc', 123)-- 运行结果为 6
SELECT LENGTH('你好')-- 运行结果为 2
SELECT CHAR_LENGTH('你好')-- 运行结果为 abc
SELECT LOWER('ABC')-- 运行结果 ABC
SELECT UPPER('abc')-- 运行结果为 f123d
SELECT REPLACE('fabcd', 'abc', 123)-- 运行结果为 fab
SELECT SUBSTRING('fabcd', 1,3)
日期函数:
-- 运行结果为 2021-07-06
SELECT CURRENT_DATE()-- 运行结果为 14:39:05
SELECT CURRENT_TIME()-- 结果 2021-07-06 14:39:38
SELECT CURRENT_TIMESTAMP()-- 结果 2021
SELECT EXTRACT(YEAR FROM '2021-07-06')-- 结果 2021-07-06
SELECT DATE('2021-07-06 12:00:05')
这里需要注意的是,DATE 日期格式必须是 yyyy-mm-dd 的形式。如果要进行日期比较,就要使用 DATE 函数,不要直接使用日期与字符串进行比较
转换函数:
-- 运行结果会报错
SELECT CAST(123.123 AS INT)-- 运行结果为 123.12
SELECT CAST(123.123 AS DECIMAL(8,2))-- 运行结果为 1
SELECT COALESCE(null,1,2)
CAST 函数在转换数据类型的时候,不会四舍五入,如果原数值有小数,那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型,在 MySQL中,可以用DECIMAL(a,b)来指定,其中 a 代表整数部分和小数部分加起来最大的位数,b 代表小数位数,比如DECIMAL(8,2)代表的是精度为 8 位(整数加小数位数最多为 8 位),小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12
聚合函数:
COUNT(*) 获取所有行
COUNT(字段) 获取不为null的行
注意:mysql的sql_mode默认是only_full_group_by,所以在不使用group by 并且select后面出现聚集函数的话,那所有的字段全是聚合函数,否则就会报错
-- 这是报错的语句,因为player_name字段没有使用聚合函数
-- mysql的sql_mode默认是only_full_group_by的时候,在不使用group by 并且select后面出现聚集函数的话,那么所有被select的都应该是聚集函数,否则就会报错
SELECT player_name, max(height) FROM player
HAVING 和 WHERE 的区别:
- WHERE 是分组之前的筛选,不能使用聚合函数
- HAVING 是分组之后的筛选,可以使用聚合函数
因为sql执行顺序的缘故,聚合函数是在WHERE以后才会执行,所以WHERE无法使用聚合函数
子查询:
非关联子查询:
- 非关联子查询:子查询只会执行一次,结果作为主查询的条件
比如我们查找身高最高的球员时:先子查询出最高的身高是多少,然后再通过这个身高值找到最高的球员
-- 需要先子查询出最高的身高是多少,然后再通过这个身高值找到最高的球员
SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)
关联子查询:
- 关联子查询:子查询执行多次,以一种循环的方式执行,因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次
以下面sql为例,每次查询球队超过平均身高的球员都会去执行一次子查询,拿到球队的平均身高
获取每个球队超过平均身高的球员:先将player表复制成表a和表b,然后让他们通过球队ID关联起来,先通过子查询查出每个队的平均身高,再对比这个平均值获取超过平均身高的球员
-- 先将player表复制成表a和表b,然后让他们通过球队ID关联起来,先通过子查询查出每个队的平均身高,再对比这个平均值获取超过平均身高的球员
SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)
EXISTS 子查询:
- EXISTS:子查询用来判断条件是否满足,满足的话为 True,不满足为 False
player:球员表
player_score:球员比赛记录表(没记录说明没比赛过)
下面是查出所有比赛过的球员:
-- 通过球员ID关联两张表,然后子查询查出所有球员,主查询使用EXISTS可以理解为拿主查询中的球员去子查询中查找,如果存在就返回
SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
NOT EXISTS 子查询:
- NOT EXISTS 不存在的意思
player:球员表
player_score:球员比赛记录表(没记录说明没比赛过)
下面是查出所有没有比赛过的球员(只需将EXISTS换成NOT EXISTS):
-- NOT EXISTS 就会去找比赛表中没有的球员
SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
集合比较子查询:
其实上面EXISTS 和 NOT EXISTS 的例子可以使用IN 和 NOT IN
所有比赛的球员,使用IN:
SELECT player_id, team_id, player_name FROM player WHERE player_id IN (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
所有没有比赛的球员,使用NOT IN:
SELECT player_id, team_id, player_name FROM player WHERE player_id NOT IN (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
什么时候用IN,什么时候用EXIST?
简化:
-- IN 子查询
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)-- EXIST 子查询
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
前提条件字段cc添加了索引,如果A表比B表大,IN 效率更高,因为这时候是B表对cc字段使用索引;如果A表比B表小,EXIST 效率更高,因为这时候使用A表对cc字段的索引
主表大用IN,主表小用EXIST
还有一个说法,如果子查询中会有null值,IN/NOT IN会出现错误结果,使用EXIST/NOT EXIST就不会
ANY:就是和集合的最小值比较
SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
ALL:就是和集合最大值比较
SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)
将子查询作为计算字段:
将子查询当作select字段:
SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team
连接:
交叉连接:
- 交叉连接:就是笛卡尔乘积,就是两张表的所有组合,采用的是 CROSS JOIN,交叉连接可以连接没有关联的表
player表37条数据,team表3条数据,使用CROSS JOIN以后的结果是37*3=111条数据
-- 两表交叉连接
SELECT * FROM player CROSS JOIN team
-- 多表交叉连接:SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3
自然连接:
- 自然连接:自动查询两张连接表中所有相同的字段,然后进行等值连接,使用NATURAL JOIN
player表37条数据,team表3条数据,使用NATURAL JOIN以后的结果是37条数据,以最多数据的表为主
NATURAL JOIN team 相当于 JOIN ON 将两张表中所有相同的字段相等
所以返回数据量要么等于主表数量要么小于主表数量
两张表数据匹配的才会显示,不匹配的不会显示
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team
实际上NATURAL JOIN 替代了 ON player.team_id = team.team_id,当然如果还有相同的字段,就是相当于 ON a.id=b.id AND a.name=b.name AND a.age=b.age
ON 连接:
- ON 连接:用来指定我们想要的连接条件
比如上面的例子还能写为:
SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id
这里指定了连接条件是ON player.team_id = team.team_id,相当于是用 ON 进行了 team_id 字段的等值连接
-
等价连接就是自然连接,就是多张表查询的时候条件是相等的
-
非等价连接就是多张表查询时条件不是相等的
非等价连接:
SELECT p.player_name, p.height, h.height_level FROM player as p JOIN height_grades as h ON height BETWEEN h.height_lowest AND h.height_highest
个人对等价/非等价连接的理解就是可以看作两张表的笛卡尔积,但是加了条件限制,筛选了一部分数据
ON 和 WHERE 的区别:ON是表关联,WHERE是数据筛选
USING 连接:
- USING 指定数据表里的同名字段进行等值连接
player表和team表都有team_id字段,USING(team_id) 相当于 player.team_id = team.team_id
SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)
外连接:
三种形式:
- 左外连接:LEFT JOIN 或 LEFT OUTER JOIN
左边是主表,加上右边表中匹配的行,如果右边表没有匹配的数据剩余字段用null填充
SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id
- 右外连接:RIGHT JOIN 或 RIGHT OUTER JOIN
右边是主表,加上左边表中匹配的行,如果左表中没有匹配的字段用null填充
SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id
- 全外连接:FULL JOIN 或 FULL OUTER JOIN(全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据,mysql不支持全外连接)
-
内连接:将多个表之间满足连接条件的数据行查询出来。它包括了等值连接、非等值连接和自连接。
-
外连接:会返回一个表中的所有记录,以及另一个表中匹配的行。它包括了左外连接、右外连接和全连接。
-
交叉连接:也称为笛卡尔积,返回左表中每一行与右表中每一行的组合。在 SQL99 中使用的 CROSS JOIN。
内连接和外连接的区别:
-
内连接只显示匹配的字段,不会显示不匹配的数据,返回数量一般小于等于数量最多的表
-
外连接如果是左外连接,返回数量以左表数量一样,右表不匹配的字段以null填充
对于连接需要注意:
-
控制连接表的数量:联表越多性能越低
-
在连接时不要忘记 WHERE :多表连接的目的不是为了做笛卡尔积,而是筛选符合条件的数据行,因此在多表连接的时候不要忘记了 WHERE 语句,这样可以过滤掉不必要的数据行返回
-
使用自连接而不是子查询:在许多 DBMS 的处理过程中,对于自连接的处理速度要比子查询快得多。可以这样理解:子查询实际上是通过未知表进行查询后的条件判断,而自连接是通过已知的自身数据表进行条件判断,因此在大部分 DBMS 中都对自连接处理进行了优化。
视图:
-
视图:其实是对一些特定需求(比如只给销售看,某些字段不需要展示)或者复杂SQL查询的封装(比如一百多行的查询SQL),一个视图其实是SELECT语句的集合,执行时会提前编译好,可以反复使用
-
创建视图:CREATE VIEW
-- CREATE VIEW 视图名 AS 后面是普通SQL查询
CREATE VIEW view_name AS SELECT column1, column2 FROM table WHERE condition
- 修改视图:ALTER VIEW
- 删除视图:DROP VIEW
Navicat 中直接新建视图,加入SQL语句,预览结果以后保存,添加视图名,修改视图就是修改那条SQL再保存,删除就是直接删除
存储过程:
- 存储过程:思想很简单,就是 SQL 语句的封装。一旦存储过程被创建出来,使用它就像使用函数一样简单,我们直接通过调用存储过程名即可
定义存储过程:
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN需要执行的语句
END
如果使用Navicat之类的工具编写存储过程的时候,不需要加 DELIMITER,但是直接终端编写的时候,因为mysql的结束符是;,但是存储过程就像编程语言一样会由多条语句组成,所以会有很多;,这时候就要改变结束符,使用 DELIMITER
-- 设置结束符为 //
DELIMITER //
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN需要执行的语句
-- 这里结束
END //
-- 重新设置结束符为;
DELIMITER ;
没再看了,感觉除了能使用参数之外和视图差不多,之前的公司因为其它部门经常需要拉数据,有些数据就是月份不一样,会写几个存储过程,参数是起始时间和结束时间
Navicat里创建的时候只需要写BEGIN和END之间的内容就可以了,BEGIN和END不需要写,参数的模式用空白就可以了
事务处理
- START TRANSACTION 或者 BEGIN,作用是显式开启一个事务。
- COMMIT:提交事务。当提交事务后,对数据库的修改是永久性的。
- ROLLBACK 或者 ROLLBACK TO [SAVEPOINT],意为回滚事务。意思是撤销正在进行的所有没有提交的修改,或者将事务回滚到某个保存点。
- SAVEPOINT:在事务中创建保存点,方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
- RELEASE SAVEPOINT:删除某个保存点。
- SET TRANSACTION,设置事务的隔离级别。
mysql默认自动提交
两个事务的执行结果:
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
-- BEGIN 开启一个事务
BEGIN;
-- 插入数据
INSERT INTO test SELECT '关羽';
-- COMMIT 提交数据
COMMIT;
-- BEGIN 再开启一个事务
BEGIN;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
-- ROLLBACK 回滚
ROLLBACK;
-- 这里只能查询出关羽,因为第一条事务没有错误提交,第二个事务因为name有唯一约束,所以报错,回滚,所以整个事务都没有提交成功
SELECT * FROM test;
在同一个事务下的结果:
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
-- BEGIN 开启一个事务
BEGIN;
-- 插入数据
INSERT INTO test SELECT '关羽';
-- COMMIT 提交数据
COMMIT;
-- 因为mysql是默认自动提交的
INSERT INTO test SELECT '张飞';
-- 自动提交相当于这里有一个COMMIT
INSERT INTO test SELECT '张飞';
-- 自动提交相当于这里也有一个COMMIT
-- ROLLBACK 回滚
ROLLBACK;
-- 这里只能查询出一条关羽和一条张飞,这里所有操作在一个事务里,每次插入后都会提交,但是最后一次提交因为违反唯一约束,报错,然后回滚,但是上面的两条已经提交,所以有两条数据
SELECT * FROM test;
当然在两个事务下还有一种方式可以实现上面一种事物的效果,就是使用SAVEPOINT保存点:
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
-- BEGIN 开启一个事务
BEGIN;
-- 插入数据
INSERT INTO test SELECT '关羽';
-- COMMIT 提交数据
COMMIT;
-- BEGIN 再开启一个事务
BEGIN;
INSERT INTO test SELECT '张飞';
-- 添加一个保存点
SAVEPOINT 1;
INSERT INTO test SELECT '张飞';
-- 添加另一个保存点
SAVEPOINT 2;
-- ROLLBACK 回滚到保存点1
ROLLBACK TO 1;
-- 这里只能查询出关羽,因为第一条事务没有错误提交,第二个事务因为name有唯一约束,所以报错,回滚,但是回滚到保存点1,因为自动提交,所以回滚的位置一直插入了一条张飞
SELECT * FROM test;
事务隔离:
- 脏读:读到了其他事务还没有提交的数据。
- 不可重复读:对某数据进行读取,发现两次读取的结果不同,也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。
- 幻读:事务 A 根据条件查询得到了 N 条数据,但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据,这样当事务 A 再次进行查询的时候发现会有 N+M 条数据,产生了幻读。
这篇关于重学SQL 基础的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!