MySQL概述

数据库相关概念

关系型数据库（RDBMS）

建立在关系模型基础上，由多张相互连接的二维表组成的数据库

启动与停止

方法一：

  1. win+R
  2. 输入services.msc (mysql在下载时默认启动，默认开机紫气)
  3. 找到mysql80右键启动/停止

方法二：

1
2
3
4
5
cmd管理员身份运行
--启动
net start mysql80
--停止
net stop mysql80

客户端连接

方法一：

MySQL提供的命令行MySQL 8.0 Command Line Client - Unicode

方法二：

系统自带命令行cmd(需配置环境变量，把bin目录加到系统path)

1
2
3
4
5
6
mysql [-h 127.0.0.1][-p 3306] -u root -p
参数：
-h : MySQL服务所在的主机IP
-P : MySQL服务端口号， 默认3306
-u : MySQL数据库用户名
-p ： MySQL数据库用户名对应的密码

[ ]内为可选参数，如果需要连接远程的MySQL，需要加上这两个参数来指定远程主机IP、端口，如果

连接本地的MySQL，则无需指定这两个参数。

数据模型

SQL

SQL通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。

  2. SQL语句可以使用空格/缩进来增强语句的可读性。

  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

  4. 注释：

    单行注释：– 注释内容 或 # 注释内容

    多行注释：/* 注释内容 */

SQL分类

SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL

分类 全称 说明
DDL Data Definition 数据定义语言，用来定义数据库对象(数据库，表，字段）
DML Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改
DQL Data Query Language 数据查询语言，用来查询数据库中表的记录
DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的

DDL——数据定义

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--查询所有数据库
show databases;

--查询当前数据库
select database();

--创建数据库
create database [if not exists] 数据库名 [default charset字符集] [collate排序规则];

--删除数据库
drop database [ if exists ] 数据库名 ;

--切换数据库
use 数据库名 ;

在同一个数据库服务器中，不能创建两个名称相同的数据库或删除不存在的数据库，否则将会报错！！！

可以通过if not exists 参数来解决这个问题，数据库不存在, 则创建该数据库，如果存在，则不创建

表操作

查询创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--查询当前数据库所有表
show tables ;

--查看指定表结构
desc 表名 ;

--查询指定表的建表语句
show create table 表名 ;

--创建表结构
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

注意: […] 内为可选参数，最后一个字段后面没有逗号

数据类型

类型 大小 有符号(signed)范围 无符号(unsigned)范围 描述
TINYINT 1byte (-128,127) (0,255) 小整数值
SMALLINT 2bytes (-32768，32767) (0，65535) 大整数值
MEDIUMINT 3bytes (-8388608，8388607) (0，16777215) 大整数值
INT/INTEGER 4bytes (-2147483648，2147483647) (0，4294967295) 大整数值
BIGINT 8bytes (-2^63，2^63-1) (0，2^64-1) 极大整数值
FLOAT 4bytes (-3.402823466 E+38，3.402823466351 E+38) 0 和 (1.175494351 E-38，3.402823466 E+38) 单精度浮点数值
DOUBLE 8bytes (-1.7976931348623157E+308，1.7976931348623157 0 和(2.2250738585072014E-308，1.7976931348623157E+308) 双精度浮点数值
DECIMAL 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 小数值（精确定点数） 
1
2
3
4
5
如:
1). 年龄字段 -- 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned
2). 分数 -- 总分100分, 最多出现一位小数
score double(4,1) --表示四位数，一位小数

字符串类型

blog相对使用不多，性能不高，

char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 （超出报错）。

相对来说，char的性能会更高些。

1
2
3
4
5
6
7
如：
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)

日期时间类型

1
2
3
4
5
如:
1). 生日字段 birthday
birthday date
2). 创建时间 createtime
createtime datetime

注意取值范围，timestamp只能到2038

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

--修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

--修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

--删除字段
ALTER TABLE 表名 DROP 字段名;

--修改表名
ALTER TABLE 表名 RENAME TO 新表名;

删除

1
2
3
4
5
6
--删除表
DROP TABLE [ IF EXISTS ] 表名;
--可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。

--删除指定表, 并重新创建表
TRUNCATE TABLE 表名;  --相当于格式化

注意: 在删除表的时候，表中的全部数据也都会被删除。

DML——数据操作

​ DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

  • 添加数据（INSERT）

  • 修改数据（UPDATE）

  • 删除数据（DELETE）

添加数据

1
2
3
4
5
6
7
8
9
10
--给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

--给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);

--批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

注意事项:

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

修改数据

1
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

注意事项 :

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

删除数据

1
DELETE FROM 表名 [ WHERE 条件 ] ;

注意事项:

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

  • DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

  • 当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除，我们直接点击Execute即可。

DQL——数据查询

​ DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。查询关键字: SELECT

​ 在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站，在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作。

基本语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

拆分为以下部分：

  • 基本查询（不带任何条件）

  • 条件查询（WHERE）

  • 聚合函数（count、max、min、avg、sum）

  • 分组查询（group by）

  • 排序查询（order by）

  • 分页查询（limit）

基础查询

1
2
3
4
5
6
7
8
9
10
11
12
--查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;

--字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名; 
--即as可省略

--去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

条件查询

1
2
--语法
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
条件
常用比较运算符

**注意：**in(a,b,c)相当于a or b or c

常见逻辑运算符

聚合函数

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

常见聚合函数
1
2
--语法
SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的！

​ 对于count聚合函数，统计符合条件的总记录数，还可以通过 count(数字/字符串)的形式进行统计

查询，例如：

1
select count(1) from emp;

分组查询

1
2
--语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

where与having区别：

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

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

注意事项:

  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

  • 执行顺序: where > 聚合函数 > having 。

  • 支持多字段分组, 具体语法为 : group by columnA,columnB

排序查询

1
2
--语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式

  • ASC : 升序(默认值)

  • DESC: 降序

注意事项：

  • 如果是升序, 可以不指定排序方式ASC ;

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

分页查询

​ 分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台都需要借助于数据库的分页操作。

1
2
--语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

  • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

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

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

案例练习

1
2
3
4
5
6
7
8
9
10
1).查询年龄为20,21,22,23岁的员工信息。
select * from emp where age in (20,21,22,23);
2). 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';
3). 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
select gender,count(*) from emp where age < 60 group by gender;
4). 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name , age from emp where age <= 35 order by age asc , entrydate desc;
5). 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender = '男' and (age between 20 and 40) order by age asc , entrydate asc limit 5;

执行顺序

DCL——数据控制

​ DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

1
2
3
4
5
6
7
8
9
10
11
-- 查询用户
select * from mysql.user;

-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

-- 删除用户
DROP USER '用户名'@'主机名' ;

注意事项:

  • 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

  • 主机名可以使用 % 通配。

  • 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库

管理员）使用。

权限控制

​ MySQL中定义了很多种权限，但是常用的就以下几种

​ 上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，可以直接参考官方文档

1
2
3
4
5
6
7
8
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;

-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

  • 多个权限之间，使用逗号分隔

  • 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。

函数

​ 函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

字符串函数

right(str,len) 和 left(str,len) 可以取字符串左右

**注意：**substring字符串下标从1开始

数值函数

MOD即取余

日期函数

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

注意：ifnull 判断value1时 “” 也不为空，即

1
select ifnull("",value2) --返回value2

case 里 when / then 可以写多个

约束

概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

约束演示

1
2
3
4
5
6
7
8
--对应的建表语句
CREATE TABLE tb_user(
	id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
	name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
	age int check (age > 0 && age <= 120) COMMENT '年龄' ,
	status char(1) default '1' COMMENT '状态',
	gender char(1) COMMENT '性别'
);

​ 在为字段添加约束时，我们只需要在字段之后加上约束的关键字即可，需要关注其语法。我们执行上面的SQL把表结构创建完成，然后接下来，就可以通过一组数据进行测试，从而验证一下，约束是否可以生效。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'), ('Tom2',25,'0','男');

insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');

insert into tb_user(name,age,status,gender) values (null,19,'1','男');

insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');

insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');

insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');

insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');

insert into tb_user(name,age,gender) values ('Tom5',120,'男');

使用软件图形化页面创建表结构

外键约束

介绍

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

​ 从上图例子中看：左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。

**注意：**目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，

所以是无法保证数据的一致性和完整性的。

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
27
28
29
--准备数据
CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';
INSERT INTO dept ( id, NAME )
VALUES
	( 1, '研发部' ),
	( 2, '市场部' ),
	( 3, '财务部' ),
	( 4, '销售部' ),
	( 5, '总经办' );
CREATE TABLE emp (
	id INT auto_increment COMMENT 'ID' PRIMARY KEY,
	NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
	age INT COMMENT '年龄',
	job VARCHAR ( 20 ) COMMENT '职位',
	salary INT COMMENT '薪资',
	entrydate date COMMENT '入职时间',
	managerid INT COMMENT '直属领导ID',
	dept_id INT COMMENT '部门ID' 
) COMMENT '员工表';
INSERT INTO emp ( id, NAME, age, job, salary, entrydate, managerid, dept_id )
VALUES
	( 1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
	( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
	( 3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
	( 4, '韦一笑', 48, '开
	发', 11000, '2002-02-05', 2, 1 ),
	( 5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
	( 6, '小昭', 19, '程
	序员鼓励师', 6600, '2004-10-12', 2, 1 );
语法
1
2
3
4
5
6
7
8
9
10
11
--添加外键
CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

--删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

​ 添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

1
2
--具体语法为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

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

  • 一对多（多对一）
  • 多对多
  • 一对一

一对多

  • 案例: 学生 与 课程的关系

  • 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

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

多对多

  • 案例: 部门 与 员工的关系

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

  • 实现: 在多的一方建立外键，指向一的一方的主键

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
27
28
29
30
31
32
33
34
35
36
37
CREATE TABLE student ( 
    id INT auto_increment PRIMARY KEY COMMENT '主键ID', 
    NAME VARCHAR ( 10 ) COMMENT '姓名', 
    NO VARCHAR ( 10 ) COMMENT '学号' ) COMMENT '学生表';
INSERT INTO student
VALUES
	( NULL, '黛绮丝', '2000100101' ),
	( NULL, '谢逊', '2000100102' ),
	( NULL, '殷天正', '2000100103' ),
	( NULL, '韦一笑', '2000100104' );
	
CREATE TABLE course ( 
    id INT auto_increment PRIMARY KEY COMMENT '主键ID', 
    NAME VARCHAR ( 10 ) COMMENT '课程名称' ) COMMENT '课程表';
    
INSERT INTO course
VALUES
	( NULL, 'Java' ),
	( NULL, 'PHP' ),
	( NULL, 'MySQL' ),
	( NULL, 'Hadoop' );

CREATE TABLE student_course (
	id INT auto_increment COMMENT '主键' PRIMARY KEY,
	studentid INT NOT NULL COMMENT '学生ID',
	courseid INT NOT NULL COMMENT '课程ID',
	CONSTRAINT fk_courseid FOREIGN KEY ( courseid ) REFERENCES course ( id ),
	CONSTRAINT fk_studentid FOREIGN KEY ( studentid ) REFERENCES student ( id ) 
) COMMENT '学生课程中间表';
INSERT INTO student_course
VALUES
	( NULL, 1, 1 ),
	( NULL, 1, 2 ),
	( NULL, 1, 3 ),
	( NULL, 2, 2 ),
	( NULL, 2, 3 ),
	( NULL, 3, 4 );

一对一

  • 案例: 用户 与 用户详情的关系

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

  • 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

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
27
28
29
CREATE TABLE tb_user (
	id INT auto_increment PRIMARY KEY COMMENT '主键ID',
	NAME VARCHAR ( 10 ) COMMENT '姓名',
	age INT COMMENT '年龄',
	gender CHAR ( 1 ) COMMENT '1: 男 , 2: 女',
	phone CHAR ( 11 ) COMMENT '手机号' 
) COMMENT '用户基本信息表';
CREATE TABLE tb_user_edu (
	id INT auto_increment PRIMARY KEY COMMENT '主键ID',
	degree VARCHAR ( 20 ) COMMENT '学历',
	major VARCHAR ( 50 ) COMMENT '专业',
	primaryschool VARCHAR ( 50 ) COMMENT '小学',
	middleschool VARCHAR ( 50 ) COMMENT '中学',
	university VARCHAR ( 50 ) COMMENT '大学',
	userid INT UNIQUE COMMENT '用户ID',
	CONSTRAINT fk_userid FOREIGN KEY ( userid ) REFERENCES tb_user ( id ) 
) COMMENT '用户教育信息表';
INSERT INTO tb_user ( id, NAME, age, gender, phone )
VALUES
	( NULL, '黄渤', 45, '1', '18800001111' ),
	( NULL, '冰冰', 35, '2', '18800002222' ),
	( NULL, '码云', 55, '1', '18800008888' ),
	( NULL, '李彦宏', 50, '1', '18800009999' );
INSERT INTO tb_user_edu ( id, degree, major, primaryschool, middleschool, university, userid )
VALUES
	( NULL, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1 ),
	( NULL, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2 ),
	( NULL, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3 ),
	( NULL, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4 );

多表查询概述

数据准备

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 创建dept表，并插入数据
CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';
INSERT INTO dept ( id, NAME )
VALUES
	( 1, '研发部' ),
	( 2, '市场部' ),
	( 3, '财务部' ),
	( 4, '销售部' ),
	( 5, '总经办' ),
	( 6, '人事部' );-- 创建emp表，并插入数据
CREATE TABLE emp (
	id INT auto_increment COMMENT 'ID' PRIMARY KEY,
	NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
	age INT COMMENT '年龄',
	job VARCHAR ( 20 ) COMMENT '职位',
	salary INT COMMENT '薪资',
	entrydate date COMMENT '入职时间',
	managerid INT COMMENT '直属领导ID',
	dept_id INT COMMENT '部门ID' 
) COMMENT '员工表';-- 添加外键
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id );
INSERT INTO emp ( id, NAME, age, job, salary, entrydate, managerid, dept_id )
VALUES
	( 1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
	( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
	( 3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
	( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
	( 5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
	( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
	( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
	( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
	( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
	( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
	( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
	( 12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2 ),
	( 13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
	( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
	( 15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4 ),
	( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
	( 17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL );

概述

多表查询就是指从多张表中查询数据。

原来查询单表数据，执行的SQL形式为：select * from emp;

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept; 此时生成多表的笛卡尔积

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。

而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据

我们可以给多表查询加上连接查询的条件（即where）即可。

分类

连接查询

  • 内连接：相当于查询A、B交集部分数据

  • 外连接：

    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

内连接

内连接查询的是两张表交集部分的数据。(也就是上图绿色部分的数据)

内连接的语法分为两种: 隐式内连接、显式内连接。

1
2
3
4
--隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
--显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

表的别名:

  1. tablea as 别名1 , tableb as 别名2 ;
  2. tablea 别名1 , tableb 别名2 ;

**注意：**一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

1
2
3
4
--左外连接，相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
--右外连接，相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

注意事项：

​ 左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接

自连接

自连接查询

​ 自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。而对于自连接查询，可以是内连接查询，也可以是外连接查询。

1
2
--语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

注意事项:

​ 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

自然连接

​ 自然连接（Natural Join）是 SQL 中一种特殊的连接方式，它基于两个表中所有名称相同的列自动进行匹配连接，不需要显式指定连接条件（如ON子句），且结果中会自动去除重复的同名列。

核心特点

  1. 自动匹配条件：无需手动指定ON子句，数据库会自动寻找两个表中所有名称相同的列作为连接条件（例如，表 A 和表 B 都有id列，则自然连接会以A.id = B.id为条件）。
  2. 去重同名列：连接结果中，同名的连接列只会保留一次（避免重复显示）。
  3. 默认内连接效果：自然连接默认是 “内连接”（INNER JOIN）的逻辑，只返回两表中所有同名列值都匹配的行；若需左 / 右连接效果，可显式用NATURAL LEFT JOIN或NATURAL RIGHT JOIN。

注意事项

  1. 同名列语义必须一致：若两表有多个同名列，但语义不同（例如，employees有name（员工名），departments有name（部门名）），自然连接会错误地将这些列都作为连接条件，导致结果不符合预期。
  2. 可读性差：连接条件隐藏在 “同名列” 中，其他开发者难以快速理解关联逻辑，不如显式INNER JOIN ... ON清晰。
  3. 慎用：实际开发中，推荐用显式JOIN ... ON指定连接条件，避免因同名列语义不一致导致的错误。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

1
2
3
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

注意事项：

  • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

  • union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

  • 如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。

子查询

概述

**概念：**SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

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

1
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

分类

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

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

2.根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

标量子查询

​ 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=

列子查询

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

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

行子查询

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

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

表子查询

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

常用的操作符：IN

多表查询案例

数据环境准备

1
2
3
4
5
6
7
8
9
10
11
12
13
create table salgrade(
grade int,
losal int,
hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

事务

简介

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

​

​ 就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

​ 正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四增加1000, 转账成功 :

​ 异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

​ 为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态

注意：

​ 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

事务操作

数据准备

1
2
3
4
5
6
7
drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';
insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

未控制事务

  1. 测试正常情况
1
2
3
4
5
6
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
  1. 测试异常情况
1
2
3
4
5
6
7
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了.... --出错了.... 这句话不符合SQL语法,执行就会报错
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

控制事务一

1
2
3
4
5
6
7
--1). 查看/设置事务提交方式，1为自动，0为手动
SELECT @@autocommit ;
SET @@autocommit = 0 ;
--2). 提交事务
COMMIT;
--3). 回滚事务
ROLLBACK;

**注意：**上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

控制事务二

1
2
3
4
5
6
--1). 开启事务
START TRANSACTION 或 BEGIN ;
--2). 提交事务
COMMIT;
--3). 回滚事务
ROLLBACK;

转账案例

1
2
3
4
5
6
7
8
9
10
11
12
-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

事务四大特性（面试）

  1. 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

  2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

  3. 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作（如其他事务）影响的独立环境下运行。

  4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称ACID。

并发事务问题

1).赃读：一个事务读到另外一个事务还没有提交的数据。

比如B读取到了A未提交的数据。

2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

事务A两次读取同一条记录，但是读取到的数据却是不一样的。

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

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

#serializable 【计】可串行化的

1). 查看事务隔离级别

1
SELECT @@TRANSACTION_ISOLATION

2). 设置事务隔离级别

1
2
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
--符号'|'为或的意思

注意：事务隔离级别越高，数据越安全，但是性能越低。

mysql的默认是repeatable read，但是其他的不一定是

若设置serializable，则同时间只能允许一个事务执行，其他事务要排队。