基础篇

SQL通用语法

  • SQL语句可以单行或多行书写，使用回车可换行继续书写最后以分号结尾。
  • SQL语句可以使用空格/缩进来增加语句的可读性。
  • MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  • 注释：
  • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
  • 多行注释：/*注释内容*/

SQL分类

1
2
3
4
DDL: 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML: 数据操作语言，用来对数据库表中的数据进行增删改
DQL: 数据查询语言，用来查询数据库中表的记录
DCL: 数据控制语言，用来创建数据库的用户、控制数据库的访问权限

SQL数据类型

见这篇blog->MySQL字段类型最全解析_kun_行者的博客-CSDN博客

SQL运算符

SQL学习之运算符_sql运算符有哪些_heart-szu的博客-CSDN博客

SQL七大约束

约束条件用法参考这篇blog【MySQL基础】MySQL表的七大约束_mysql 表级约束_小杨MiManchi的博客-CSDN博客

DDL(数据库操作和表操作)

数据库操作

查询

查看所有数据库：

1
SHOW DATABASES;

查询当前数据库：

1
SELECT DATABASE();

创建

1
2
3
4
5
6
7
8
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 数据库名 [DEFAULT (CHARSET)|(CHARACTER SET) 字符集] [COLLATE 排序规则];
//DEFAULT这个单词在写代码的时候可以加也可以不加,最好加上

例：
CREATE DATABASE IF NOT ETISTS teaching
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

删除

1
DROP DATABASE [IF EXISTS] 数据库名;

使用

1
USE 数据库名;

查看

1
2
SHOW CREATE DATABASE 数据库名
//结果会显示出创建命令和各参数设置(字符集，校对规则)

修改

1
2
ALTER DATABASE|SCHEMA [IF NOT EXISTS] 数据库名 [DEFAULT (CHARSET)|(CHARACTER SET) 字符集] [COLLATE 排序规则];
//注意：用户必须有数据库的修改权限才能使用该命令修改数据库

表操作

查询

查询当前数据库所有表

1
SHOW TABLES;

查询表结构

1
DESC 表明;

查询指定表的建表语句

1
2
SHOW CREATE TABLE 表名;
//会显示出详细注释，引擎，字符集，校对规则

创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE 表名(
    字段1 字段1类型 [约束条件] [COMMENT '字段1注释'],
    字段2 字段2类型 [约束条件] [COMMENT '字段2注释'],
    字段3 字段3类型 [约束条件] [COMMENT '字段3注释'],
    ...
    字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];


例子:
create table tb_user(
    id int comment '编号',
    name varchar(50) comment '姓名'
    ) comment '用户表';
  • 表名和字段只能用反引号或不用符号，不能用单引号
  • 最后一个字段结尾不要有逗号
  • comment后的字段注释或表注释必须使用引号，而且是单引号而不是反引号

修改

添加字段

1
2
3
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
//alter: 修改
//add: 添加

修改指定字段数据类型

1
2
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
//modify: 修改

修改字段数据名和字段类型

1
2
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
//change: 修改

删除字段

1
ALTER TABLE 表名 DROP 字段名;

修改表名

1
ALTER TABLE 表名 RENAME TO 新表名;

删除

删除表

1
2
DROP TABLE [IF EXISTS] 表名;
//数据全部删除

删除指定表，并重新创建该表

1
2
TRUNCATE TABLE 表名;
//数据全部删除，结构不变

DML(数据操作)

添加

  • 给指定字段添加一条数据
1
INSERT|REPLACE INTO 表名(字段名1，字段名2，字段名3,..) VALUES(值1，值2，值3，..);
  • 给全部字段添加一条数据
1
INSERT|REPLACE INTO 表名 VALUES(值1，值2，...);
  • 批量添加多条数据
1
2
3
4
5
6
INSERT|REPLACE INTO 表名(字段名1，字段名2，字段名3,..) VALUES(值1，值2，值3，..),(值1，值2，值3，..),(值1，值2，值3，..);
//指定字段添加多份数据

INSERT INTO 表名 VALUES(值1，值2，值3，..),(值1，值2，值3，..),(值1，值2，值3，..);
//所有字段添加多份数据

注意：

  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
  • 字符串和日期型(date型)数据应该包含在引号中
  • 插入的数据大小应该在字段的规定范围内

修改数据

修改

1
2
3
4
5
UPDATE 表名 SET 字段名1=值1, 字段名2=值2,...[WHERE 条件]；

例：
update test set name = 'kqd' where id=1;
//将id为1的字段name数据修改为kqd

注意：

  • 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除

1
2
3
4
5
6
7
DELETE FROM 表名 [WHERE 条件]

例：
//删除gender为女的员工
delete from employee where gender = '女';
//删除所有员工
delete from employee;

注意：

  • DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
  • DELETE语句不能删除某一个字段的值(但可以使用UPDATE)。

DQL(查询)

基础查询

1.查询多个字段

1
2
3
4
SELECT 字段1，字段2... FROM 表名；
SELECT * FROM 表名 ; 

*代表查询所有的字段

2.设置别名

1
2
3
4
5
6
7
8
9
10
11
SELECT 字段1 [AS 别名1]，字段2 [AS 别名2]... FROM 表名；
//AS 可省略

-- 对字段起别名 
select 字段1 as '别名',...
为字段1取别名

-- 对表取别名
select a.name
from student as a
为student表取别名a

3.去除重复记录

1
SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1
SELECT 字段列表 FROM 表名 WHERE 条件列表;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
条件:
运算符
1.算数
+, -, *, /
/********************/
2.判断
>, <, >=, <=, !=, <>(不等于) 
IS NULL|IS NOT NULL(判断是否为NULL)
/********************/
3.逻辑 
AND(&&), OR(||),NOT||!
/********************/
4.范围 
BETWEEN 最小值 AND 最大值,
IN(值1，值2，...)  //IN之后的列表的值为范围
5.模糊匹配 
LIKE '_||%||'
'_' 表示代表一个字符
'%' 表示0各或多个字符
'[]' 表示某一范围的字符,例如[0-9],0~9之间的字符
'[^]' 表示不再某一范围的字符
/********************/


聚合函数

介绍:

将一列数据作为一个整体，进行纵向计算

1
2
3
4
5
6
聚合函数：
count() 统计元组数量（行数）
max() 最大值
min() 最小值
avg() 平均值
sum() 求和

语法:

1
SELECT 聚合函数(字段列表),聚合函数,... FROM 表名;
  • 所有的NULL值不参与所有聚合函数运算，对某个字段中有NULL在使用count时将不会计算数据条数。
  • count(*)统计的是表中所有的行数,count(字段)是统计该字段非NULL的行数,一般使用前者较多

分组查询

语法：

1
SELECT 字段列表 FROM 表名 [WHERE 条件] GEOUP BY 分组字段名 [HAVING 分组后过滤条件]

where和having的区别:

执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果的过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

  • 执行顺序：where > 聚合函数 > having.
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询

语法:

1
2
3
4
5
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段二 排序方式

排序方式:
ASC: 升序（默认值）
DESC: 降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

语法：

1
2
3
SELECT 字段列表 FROM 表名 LIMIT 起始索引(偏移量)，查询记录数

-- 一般LIMIT加在所有代码的最后

注意：

  • 起始索引从0开始，起始索引 = (查询页码-1) * 每页显示记录数，起始索引也叫偏移量，表示从起始索引所在行数的下一行开始查询

  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

  • 如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10。

DQL执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
执行顺序:了解了执行顺序后我们就可以知道哪些部分可以使用别名来访问数据，哪些不能使用
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.ORDER BY
7.LIMIT

例如：
select name,age from emp where age>15 order by age ASC;

若取别名 select e.name,e.age from emp as e age where e.age>15 order by age ASC;
//执行成功，先执行from为emp取别名e，再执行where,select

若取别名select e.name AS ename, e.age AS eage from emp AS e age where eage>15 order by age ASC;
//错误，select语句在where之后执行，在执行select之前没有eage这个别名

DCL(管理)

用户管理

查询用户

1
2
USE mysql;
SELECT * FROM user;

创建用户

1
2
3
4
5
6
7
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码' 


//设置任意主机访问该数据库
CREATE USER '用户名'@'#' identified by '密码'
//设置只能在本机访问数据库
CREATE USER '用户名'@'localhost' identified by '密码'

修改用户密码

1
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'

删除用户

1
DROP USER '用户名'@'主机名';

注意：

  • 主机名可以使用%通配
  • 这类SQL开发人员操作的比较少，主要是DBA （数据库管理员)使用

权限控制

查询权限

1
SHOW GRANTS FOR '用户名'@'主机名'

授予权限

1
2
3
4
5
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'

ALL/all 代表所有的权限
数据库.* 代表所有的表
*.* 代表所有数据库的所有表

撤销权限

1
2
3
4
5
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

ALL/all 代表所有的权限
* 代表所有的表
*.* 代表所有数据库的所有表

注意：

  • 多个权限之间，使用逗号分隔
  • 授权时，数据库名和表名可以使用*进行通配，代表所有,

函数

MySQL 十大常用字符串函数_hzbooks的博客-CSDN博客

字符串函数

常用的函数:

CONCAT

1
2
CONCAT(s1,s2,..sn)
将字符串拼接，S1,S2,...Sn拼接成一个字符串，返回该字符串

LOWER

1
2
LOWER(str) 
将字符串str全部转换为小写,返回该函数

UPPER

1
2
UPPER(str)
将字符串str全部转换为大写,返回该函数

LPAD

1
2
3
4
5
LPAD(str,n,pad)
左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

lpad('01',5,'-')
代表用'-'填充左边的得到'---01',一共五个字符，下面的rpad同理

RPAD

1
2
RPAD(str,n,pad)
右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

TRIM

1
2
TRIM(str)
去掉字符串头部和尾部的空格(不除去中间)

SUBSTRING

1
2
SUBSTRING(str,start,len)
返回从字符串str从start位置起的len个长度的字符串

数值函数

CEIL

1
2
CEIL()
向上取整

FLOOR

1
2
FLOOR()
向下取整

MOD

1
2
MOD(x,y)
返回x/y的模

RAND

1
2
RAND()
返回0~1内的随机数

ROUND

1
2
ROUND(x,y)
求参数x的四舍五入的值，保留y位小数

日期函数

CURDATE

1
2
curdate()
返回当前日期,年-月-日

CURTIME

1
2
curtime()
返回当前时间

NOW

1
2
now()
返回当前日期和时间

YEAR

1
2
year(date)
获取指定date的年份

MONTH

1
2
month(date)
获取指定date的月份

DAY

1
2
day(date)
获取指定date的日期

DATE_ADD

1
2
3
4
5
6
7
8
DATE_ADD(date,INTERVAL expr type)
返回一个日期/时间值加上一个时间间隔expr后的时间值

expr 指定数字
type 指定类型:年/月/日(DAY/MONTH/YEAR)
例：
DATE_ADD(now(),INTERVAL 70 day)-- 70天之后的时间
DATE_ADD(now(),INTERVAL 70 month)-- 70个月之后的时间

DATEDIFF

1
2
3
4
5
DATEDIFF(date1,date2)

-- 返回起始时间date1和结束时间date2
之间的天数
-- 一般用于求入职，入学时间等

流程函数

IF

1
2
3
IF(value,t,f)
如果value为true，则返回t，否则返回f
-- 类似三目运算符

IFNULL

1
2
3
4
5
6
7
8
9
10
IFNULL(value1,value2)
如果value1不为null，返回value1，否则返回value2

-- 用于聚合函数的筛选
-- 例： 求成绩大于60分才有学分，求同学的总学分

select sno as '学号',sn as '姓名',sum(IF(score>=60,credit,NULL)) from s;

-- 这里的if会判断，如果score>=60分就会返回credit，即sum(credit)计算学分和,如果不满足则会返回NULL，就不会计算该值

CASE WHEN

1
2
3
4
5
6
7
8
9
10
CASE [expr] WHEN [val1] THEN [res1] ...ELSE[default] END
如果expr(表达式/字段)的值等于val1，返回res1，...否则返回default默认值
-- 一般用于给出具体的条件的值

例：查询emp表的员工姓名和工作地址，如果工作地址在北京/上海，则标明一线城市，其他则为二线城市

select 
	name,
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CASE WHEN [val 1] THEN [RES1] ... ELSE [default] END
如果val1为true，返回res1，...否则返回default默认值
-- 一般用于范围

例如：统计成绩，85分以上为优秀，60分以上为及格，60分以下为不及格

select
	id,
	name,
	(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end ) as '数学',
	(case when english>=85 then '优秀' when math>=60 then '及格' else '不及格' end ) as '英语',
	(case when chinese>=85 then '优秀' when math>=60 then '及格' else '不及格' end ) as '语文',
from score;

其中math，english，chinese 三个字段使用了流程函数,使select出来的值进行了指定

多表关系

项目开发中，在进行数据库表结构设计师，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所有各个表结构之间也存在着各种联系，基本分为三种:

  • 一对多(多对一)

    案例：部门与 员工的关系

    关系：一个部门对应多个员工，一个员工对应多个部门

​ 实现：在多的一方建立外键，指向一的一方主键

  • 多对多

​ 案例：学生与课程的关系

​ 关系：一个学生可以选修多么课程，一门课程也可以供多个学生选择

​ 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  • 一对一

    案例：用户与用户详情的关系

    关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中,以提升操作效率

    实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的UNIQUE

多表查询

概述

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

1
2
3
4
select * from emp,dept where emp.dept_id = dept.id

-- 后面这个where就可以保证出来的两个id对应
连接条件为: emp.dept_id = dept.id

内连接

内连接将两张表连接起来，内连接查询的是两张表的交集的部分

隐式内连接

1
2
SELECT 字段列表 FROM 表1，表2 WHERE 条件 ...;
-- 不使用内连接实现方式

显示内连接

1
2
3
4
5
-- 写法一
SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 连接条件 INNER JOIN 表3 ON 连接条件...;

-- 写法二
SELECT 字段列表 FROM 表1 INNER JOIN 表2 INNER JOIN 表3 ON 连接条件 and 连接条件 ...;

外连接

左外连接

1
SELECT 字段列表 FROM 表1 LEFT (OUTER) JOIN 表2 ON 条件 LEFT (OUTER) JOIN 表3 ON 连接条件...;

注：相当于查询左表(表1)的所有数据包含表1和表2交集部分的数据

右外连接

1
SELECT 字段列表 FROM 表1 RIGHT (OUTER) JOIN 表2 ON 条件 RIGHT (OUTER) JOIN 表3 ON 连接条件...;

注：相当于查询右表(表2)的所有数据包含表1和表2交集部分的数据

注意:

  • 左外连接可以和右外连接互相转换，只需要交换字段位置
  • 外连接和内连接的区别:内连接只会展示两张表的交集部分当连接的部分中没有原表中的某条数据，即为NULL时，就不会显示出来，而外连接可以将某一张表的所有数据和交集部分显示出来，即可以显示想要知道的NULL

自连接

1
2
3
SELECT 字段列表 FROM 表A AS 别名A JOIN 表A AS 别名B ON 条件 ...;

一定要将该表取两个别名，看成两张表

自连接查询，可以是内连接查询，也可以是外连接查询

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集

1
2
3
4
5
6
7
8
9
10
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

例子：
select * from emp where salary < 5000
union all
select * from emp where age > 50 

有ALL可能会导致有重复，但去掉ALL只用union则会去除相同的

注意:

  • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重
  • or只能单表查询，而union可以多表查询

子查询

  • 概念：SQL语句中嵌套SELECT语句，成为嵌套查询，又称子查询
1
2
3
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是：INSERT/UPDATE/DELETE/SELECT的任何一个。

  • 根据子查询结果不同，分为：

    • 标量子查询（子查询结果为单个值）
    • 列子查询（子查询结果为一列）
    • 行子查询（子查询结果为一行）
    • 表子查询 （子查询结果为多行多列）

  • 根据子查询位置,分为：WHERE之后、FROM之后、SELECT之后。

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等), 是最简单的形式，这种子查询称为标量子查询

常用的操作符：= ，<> ，>， >=， <， <=

1
2
3
4
5
6
7
8
9
10
11
-- 例如:
-- 查询'销售部'的所有员工信息(id和名称在两张表)
-- 我们分为两步
-- 1.查询'销售部'部门ID
select id from dept where name = '销售部';

-- 2.根据销售部的部门ID，查询员工信息
select * from emp where dept_id = 4;

-- 3.使用标量子查询合并之后就是
select * from emp where dept_id = (select id from dept where name = '销售部');

列子查询

子查询返回的结果是一列(可以是多行), 这种子查询成为列子查询。

常用的操作符： IN 、NOT IN 、ANY 、SOME 、ALL、EXISTS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
IN ：在指定的集合范围之内  ，多选一
NOT IN ： 不在指定的集合范围之内
ANY ：子查询返回列表中，有任意一个满足即可
SOME ： 与ANY等同，使用SOME的地方都可以使用ANY
ALL : 子查询返回列表的所有值都必须满足
EXISTS: 也可进行相关子查询，但不返回任何实际数据，只得到真或假


例：
-- 列子查询
-- 1.查询'销售部'和'市场部'的所有员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

-- 2.查询比财务部所有人工资都高的员工信息
-- a. 查询所有财务部人员工资
select salary from where dept_id = (select id from dept where name = '财务部')

-- b. 比财务部所有人工资都高的员工信息
select * from emp where salary > ALL(select  salary from where dept_id = (select id from dept where name = '财务部')salary )

-- 3.查询比研发部任意一人工资高的员工该信息
-- a. 查询研发部所有人工资
select salary from where dept_id = (select id from dept where name = '研发部')

-- b. 比研发部其中任意一人工资高的员工信息
select * from emp where salary > ANY (select salary from where dept_id = (select id from dept where name = '研发部'))
1
2
3
4
5
6
-- exist 表示存在的量词，带有exists的子查询不返回任何实际数据，它只得到逻辑值"真"或"假"。当子查询的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOT EXISTS与此相反。
-- 查询学生的学号和姓名，查询条件是学生没有选修课程号为"c1"的课程（使用exist）
select sno,sn from s where not exists(select * from sc where sno = s.sno AND cno = 'C1')

-- 上述语句的执行过程是，对于父查询中的每一位学生，在子查询中查询是否选修了课程号为C1的课程，如果没有选课记录，说明子查询的结果集合为空，则父查询中WHERE子句中的not exists的返回逻辑值"真",从而该学生的信息会被被选取到父查询的结果集合中,集合不为空，则not exists的返回逻辑值为"假"，则该学生信息丢弃。对父查询表s中的每一位学生重复上述过程，即可完成查询任务

行子查询

子查询返回的结果是一行（可以是多列），这种子查询成为行子查询

常用的操作符：= ，<> ，IN、NOT IN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 行子查询
-- 1.查询与'张无忌'的薪资及直属领导相同的员工信息
-- a. 查询张无忌的薪资及直属领导
select salary,managerid from emp where name = '张无忌';

-- b. 查询与'张无忌'的薪资及直属领导相同的员工信息
select * from emp where salary = 12500 and managerid = 1;


-- 组合作为条件
select * from emp where (salary,managerid) = (12500,1);
-- 表示salary =12500，managerid = 1 


-- 行子查询
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');
-- 刚好是两条数据两两对应，等号的组合写法

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用操作符: IN

1
2
3
4
5
6
7
8
-- 表子查询
-- 1. 查询与 'lzk' ,'syq' 的职位和薪资相同的员工信息
-- a. 查询 lzk，syq的职位和薪资
select job,salary from emp where name = 'lzk' or name = 'lyq';

-- b.查询与lzk，syq的职位和薪资相同的员工信息
-- 组合写法
select from emp where (job,salary) in (select job,salary from emp where name = 'lzk' or name = 'lyq')

事务简介

事务（transaction）是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务操作

方式一

  • 查看/设置事务提交方式
1
2
3
4
5
6
7
SELECT @@autocommit;
-- 查看事务的自动提交方式：1为自动，0为手动

SET @@autocommit = 0;
-- 设置系统变量的提交方式：1为自动，0为手动

-- 这种方法可作用于全局
  • 提交事务
1
2
COMMIT;
-- 在设置了手动提交后，任意的sql语句，只有在sql语句之后使用了commit才会提交修改数据库，否则不会修改
  • 回滚事务
1
2
3
rollback;
-- 遇到异常时可以回滚已经执行的sql语句
-- 提交之后不能回滚

方式二

  • 开启事务
1
2
3
4
START TRANSACTION 或 BEGIN;
-- 这句话执行后代表开启事务，代表我们要手动控制事务，只要没有commit数据库就不会改变、

-- 这种方法可作用于某些语句,执行完成后事务自动关闭
  • 提交事务
1
2
COMMIT;
-- commit事务后代表事务完成了
  • 回滚事务
1
rollback;

事务的四大特性(ACID)

  • 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态(合理加减)。
  • 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变使永久的。

并发事务问题

  • 丢失更新：两个事务读入同一数据，并发执行修改操作时，一个事务将另一个事务的修改覆盖，导致这些修改好像丢失了一样

  • 读取脏数据(脏读)：一个事务读到另外一个事务还没有提交的数据(未commit)

  • **不可重复读：**在一个事务中先后读取同一条数据，但两次读取的数据不同，称为不可重复读。

  • **幻象读(幻读)：**一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了"幻影"

1
2
3
4
5
6
-- (查询)不可重复读：现在分别开启A,B两个事务，A第一次读取数据，在A第二次读取前，B事务修改了该数据，导致该事务再次读取数据时数据与第一次读取不同(A事务始终没有结束)
-- 如果解决了不可重复读，在一个事务中，两次读取同一个数据，数据相同，但在该事务结束后，再读取，数据就会更新了


-- (增删)幻读: 在解决了不可重复读之后才会出现，A事务查询数据时，没有对应的数据行，A准备插入，但是在A插入前，B插入了数据,A就会得到数据已经存在，但是之后第二次查询，又发现没有该数据行。
-- 因为设置了可重复读（解决不可重复读），所以才会满足在同一个事务中两次读取到的数据应该是相同的，但是实际数据确实存在

事务隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 四种事务隔离级别，从上到下性能降低，安全升高
-- 通常要综合性能和安全选择隔离级别

					脏读      不可重复读     幻读
Read uncommitted:  会出现       会出现      会出现
Read committed:     不会			会 		 会
Repeatable Read:	不会			不会		 会
Serializable:		不会			不会		不会


-- Mysql默认：Repeatable Read 可重复读
-- Oracle默认: Read committed

-- Read uncommitted：未提交读
-- Read committed：提交读
-- Repeatable Read：可重复读
-- Serializable：序列化(串行化)，代表在进行并发事务操作时，同一时间只能执行一个事务，只有在该事务提交之后，才能执行其他事务，其他事务在这之前会被阻塞,避免所有的并发问题

1
2
3
4
5
6
7
8
-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

-- 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED| READ COMMITTED| REPEATABLE READ| SERIALIZABLE]

-- SESSION：会话级别,代表针对当前客户端窗口有效,某一个console控制台
-- GLOBAL：代表所有客户端有效
  • 注意事务隔离级别越高，数据越安全，但是性能越低

进阶篇

索引

索引(index) 是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优点：

  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

  • 索引列需要占用空间
  • 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

索引结构

MySQL的索引是在储存引擎层实现的，不同的储存引擎有不同的结构，主要包含以下几种：

  • B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
  • Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
  • R-tree：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • Full-text：是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

  • 二叉树

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量的情况下，层级较深，检索速度慢。

红黑树：大数据量的情况下，层级较深，检索速度慢。

  • B-Tree(多路平衡查找树)

以一颗最大度数为5(5个子节点)的b-tree为例(每个节点最多储存四个key，5个指针)，指针数 = key + 1

1
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250  为例

B-Tree 动态变化的过程可以参考网站：

B-Tree Visualization (usfca.edu)

Data Structure Visualization (usfca.edu)

索引分类

  • 主键索引：针对于表中主键创建的索引，建表时默认自动创建，只能有一个，关键字是PRIMARY。
  • 唯一索引：避免同一个表中某数据列中的值重复，可以有多个，关键字UNIQUE。
  • 常规索引：快速定位特定的数据，可以有多个。
  • 全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个，关键字FULLTEXT。
  • 空间索引： 使用了SPATIAL关键字的索引，适用于GEOMETRY ,POINT,POLYGON等空间类型数据的列

在InnoDB储存引擎中，根据索引的存储形式，又可以分为以下两种：

  • 聚集索引(Clustered Index)：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据(记录)，必须有，而且只有一个
  • 二级索引(Secondary Index)：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个

聚集索引的选取规则：

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
  • 如果表中没有主键，或没有适合的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询：先根据字段对应的二级索引去查对应的主键值，在根据主键对应的聚集索引去查行数据。

索引语法

创建索引

1
2
3
4
5
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name(字段名[(索引字符长度) [ASC|DESC]],...);

-- 一个索引可关联多个字段

-- 不加可选项UNIQUE或FULLTEXT代表默认创建常规索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 以name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);

--  以name字段为姓名字段，该字段的值可能会重复，且索引针对name的前3个字符以降序排序,为该字段创建索引。
create index idx_user_name on tb_user(name(3) desc);
-- phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
create UNIQUE index idx_user_phone on tb_user(phone);

-- 为profession、age、status创建联合索引
create index idx_user_pro_age_status on tb_user(profession、age、status);
联合索引中字段索引是有讲究的

-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
  • 可以在建表时创建索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table 表名(
	字段1 数据类型 列约束 comment '注释',
    字段2 数据类型 列约束 comment '注释'，
    ...
    [表约束]，
    [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name(字段名[(索引字符长度) [ASC|DESC]],...),
    ...
     [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name(字段名[(索引字符长度) [ASC|DESC]],...),
)

-- 例:
create table classroom(
	cid int auto_increment,
    crno varchar(13) not null,
    cbn varchar(10) not null,
    primary key(cid),
    unique index cn_cb_index(crno,cbn)
);
1
2
3
1.在表约束后，可以使用index关键字为表创建索引，与create index用法相同。
2.create table语句可以一次附带多个索引，不同索引间使用逗号分隔
3.create table创建索引时无需提供表名，而是使用create index语句创建索引时要指明表名

查看索引

1
SHOW INDEX FROM table_name;
1
2
-- 查看tb_user表的索引
show index from tb_user;

修改索引

Mysql 索引（三）—— 不同索引的创建方式（主键索引、普通索引、唯一键索引）-CSDN博客

删除索引

1
2
DROP INDEX index_name ON table_name;

1
2
-- 例：删除email的索引
drop index idx_user_email on tb_user;

视图

  • 介绍

视图(view)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图语法

  • 创建
1
2
3
4
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

-- OR REPLACE '或替代' , 可选项
--
  • 查询
1
2
3
4
5
-- 查看创建视图的语句
SHOW CREATE VIEW 视图名称；

-- 查看视图数据(把视图当作表来查，可以像操作表一样来操作视图)
SELECT * FROM 视图名称 WHERE...;
1
2
3
4
-- 例
show create view stu_v_1;
select * from stu_v_1；
select * from stu_v_1 where id < 3;
  • 修改视图
1
2
3
4
5
-- 方式一：关键在or replace上，修改必须加
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]

-- 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
  • 删除视图
1
DROP VIEW [IF EXISTS] 视图名称，... ;

视图的检查选项

当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded 和 local，默认值为cascaded(级联)。

注意：

  • 只有添加了with check option的视图才会在插入时进行where条件的检查，没有检查时直接会添加成功到表

  • 插入的数据是存放到基表中的，视图中不存放数据

CASCADED(级联)

cascade 会去递归检查当前视图以及创建时依赖的视图，并且看是否加了检查选项，如果其依赖的视图中没有加入检查选项，则会将cascaded向下传递，使其向下的视图都加上检查选项使其都能检查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建一个视图
create or replace view stu_1 as select id,name from student where id <=20  

select * from stu_1;

insert into stu_1 values(6,'TOM')
-- 成功添加到student中
insert into stu_1 values(30,'TOM')
-- 添加成功到student中，用select搜索该视图中找不到该数据

-- 添加cascaded选项后
create or replace view stu_1 as select id,name from student where id <=20 with cascaded check option;

insert into stu_1 values(30,'TOM')
-- 添加失败，因为id>20，故会阻止该数据的添加
1
2
3
4
5
6
7
8
9
10
11
12
create or replace view stu_1 as select id,name from student where id <=20  
-- 由于stu_2使用cascaded检查选项，则stu_1也会继承到该检查选项
create or replace view stu_2 as select id,name from student where id >=10 with cascaded check option

create or replace view stu_3 as select id,name from student where id <=15

insert into stu_3 values(11,'tom');
-- 插入成功
insert into stu_3 values(17,'tom');
-- 插入成功，stu_3没有检查选项，不会检查
insert into stu_3 values(28,'tom');
-- 插入失败，stu_3没有检查选项，直接到依赖视图stu_2中，stu_2中有检查选项并且是cascaded，满足id>=10,递归进入stu_1中，由于stu_1继承到了cascaded, 会进行判断,不满足id<=20，阻止该数据插入.

LOCAL

local 会去递归检查当前视图以及创建时依赖的视图，并且看是否加了检查选项，如果有，则进行条件的检查看是否满足条件，如果没有则不进行检查继续递归

1
2
3
4
5
6
7
8
9
10
11
-- 创建视图1,没有依赖视图，没有检查选项
create or replace view stu_1 as select id,name from  student where id<=15;
-- 创建视图2，依赖于视图1,没有检查选项
create or replace view stu_2 as select id,name from stu_1 where id >= 10 ;
-- 创建视图3，依赖于视图2,设置local检查选项
create or replace view stu_3 as select id,name from stu_2 where id < 20 with local check option;

insert into stu_3 values(13,'TOM');
-- 由于stu_3设置了检查选项,该语句会先判断id<20(如果没设置就不会判断而是会直接进入stu_2中),然后递归到stu_2发现stu_2没有添加检查选项，则不进行判断id>=10，如果添加了，则会判断id>=10，再依次递归到stu_1，直到没有依赖其他视图建立的视图为止

-- 如果任意一次条件判断不满足则会插入失败