杨记

碎片化学习令人焦虑,系统化学习使人进步

0%

MySQL基础篇

菜鸟教程学习链接:https://www.runoob.com/MySQL/MySQL-tutorial.html

C语言中文网:数据库入门 (biancheng.net)

数据库和表的创建、修改、删除,数据的导入。

数据查询:多表查询,分组查询等

mysql的常用内置函数

约束:实体完整性约束和参照完整性约束

用户的添加、查看、删除、信息修改;并发控制,事务,隔离

MySQL安装

windows

启动和停止MySQL服务(cmd.exe管理员运行)

1
2
net start mysql
net stop mysql

登录MySQL数据库

1
mysql -u root –p

输入密码即可出现欢迎界面

Linux

1) 准备一台Linux服务器

云服务器或者虚拟机都可以;Linux的版本为 CentOS7;

2) 下载Linux版MySQL安装包

https://downloads.mysql.com/archives/community/

image-20211031230239760

3) 上传MySQL安装包

image-20211031231930205

4) 创建目录,并解压

1
2
mkdir mysql
tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C mysql

5) 安装mysql的安装包

1
2
3
4
5
6
7
8
9
cd mysql
rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm
yum install openssl-devel
rpm -ivh mysql-community-devel-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm

6) 启动MySQL服务

1
2
3
systemctl start mysqld
systemctl restart mysqld
systemctl stop mysqld

7) 查询自动生成的root用户密码

1
grep 'temporary password' /var/log/mysqld.log

命令行执行指令 :

1
mysql -u root -p

然后输入上述查询到的自动生成的密码, 完成登录 .

8) 修改root用户密码

登录到MySQL之后,需要将自动生成的不便记忆的密码修改了,修改成自己熟悉的便于记忆的密码。

1
ALTER  USER  'root'@'localhost'  IDENTIFIED BY '1234';

执行上述的SQL会报错,原因是因为设置的密码太简单,密码复杂度不够。我们可以设置密码的复杂度为简单类型,密码长度为4。

1
2
set global validate_password.policy = 0;
set global validate_password.length = 4;

降低密码的校验规则之后,再次执行上述修改密码的指令。

9) 创建用户

默认的root用户只能当前节点localhost访问,是无法远程访问的,我们还需要创建一个root账户,用户远程访问

1
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

10) 并给root用户分配权限

1
grant all on *.* to 'root'@'%';

11) 重新连接MySQL

1
mysql -u root -p

然后输入密码

12) 通过DataGrip远程连接MySQL

MySQL基本命令

在描述命令格式时,用[ ]表示可选项

MySQL命令不区分大小写,为了读者阅读方便,在描述命令格式和命令实例时,命令关键字用大写表示,其他用小写表示。但在实际对MySQL操作时为了避免大小写频繁切换,一般都用小写。

命令关键字可以只写前面4个字符。DESCRIBE user;DESC user; 效果是一样的。

在MySQL中,服务器处理语句的时候是以分号为结束标志的。使用DELIMITER命令可以将MySQL语句的结束标志修改为其他符号。

1
2
/*以\\结束*/
DELTMITER \\

1)查看MySQL系统的已有的数据库

1
SHOW DATABASES;

2)进入某个数据库,切换数据库

1
USE database_name;

3)查看当前数据库:

1
SELECT database();

4)显示当前数据库中所有表的名称

1
2
3
SHOW tables;
/*显示指定数据库的所有表名称*/
SHOW tables from database_name;

5)显示创建基本表的语句:(即创建这个基本表时所用的语句)

1
2
3
4
5
SHOW create table table_name;

/*此方法用处很多*/
show create database db_name;
show create procedure 数据库.触发器名;

6)显示表中所有列名

1
2
3
4
/*在选择进入了某个数据库之后,可以不指定数据库名称,否则按后两行格式来写*/
SHOW columns from table_name;
SHOW columns from table_name from database_name;
SHOW columns from database_name.table_name;

7)显示一个用户的权限

1
2
3
show grants for user_name;
/*例*/
show grants for root@localhost;

8)显示一些系统特定资源的信息,例如,正在运行的线程数量。

1
SHOW status;

9)显示系统变量的名称和值

1
2
3
4
SHOW GLOBAL VARIABLES; 	# 查看 MySQL 中所有的全局变量信息
SHOW SESSION variables; # 查看与当前会话相关的所有会话变量以及全局变量, SESSION 可忽略
/*可以用like缩小范围或精确变量*/
show variables like 'xxx';

10)DESCRIBE语句用于显示表中各列的信息,语法格式:

1
{DESCRIBE | DESC} 表名 [列名 | wild];

也可以使用explain table_name\G来显示表信息

11)查看所有数据库

1
SHOW DATABASES;	# 列出当前用户可查看的所有数据库

image-20221023114516713

上面的表都是安装 MySQL 时系统自动创建

  • information_schema:主要存储了系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
  • mysql:MySQL 的核心数据库,类似于 SQL Server 中的 master 表,主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
  • performance_schema:主要用于收集数据库服务器性能参数。
  • sys:MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图,数据都来自于 performation_schema,主要是让开发者和使用者更方便地查看性能问题。

建库建表

创建数据库

1
2
3
4
5
6
7
8
9
CREATE DATABASE [IF NOT EXISTS] 数据库名 [选项...];
/*选项如下*/
[DEFAULT] CHARACTER SET 字符集
[DEFAULT] COLLATE 校对规则名

/*例*/
CREATE DATABASE DBST
CHARACTER SET gbk
COLLATE gbk_chinese_ci;

修改数据库

1
2
3
4
ALTER DATABASE [数据库名] 选项... ;
选项:
[DEFAULT] CHARACTER SET 字符集名
[DEFAULT] COLLATE 校对规则名

删除数据库

1
DROP DATABASE [IF EXISTS] database_name;

注:除列名区分大小写,数据库名、表名等,以及createdelete等既可大写也可小写

创建表

全新创建

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 [IF NOT EXISTS] table_name
[([列定义]...|[表索引定义])]
[表选项][select语句];
/*
说明:
IF NOT EXISTS:在创建表前加上一个判断,只有该表目前尚不存在时才执行CREATE TABLE操作
列定义格式:名称 类型 属性
表索引项定义:主要定义表的索引、主键、外键等
select语句:用于在一个已有表的基础上创建表
*/

/*简单例子*/
CREATE TABLE student
(
studentkey char(6) not null primary key,
name char(8) not null,
major char(10) null,
gender tinyint(1) not null,
birth date not null,
totalcredit tinyint(1) null,
comment varchar(100) null
);

/*例*/
CREATE TABLE `employee` (
`employeeID` char(6) NOT NULL,
`name` char(10) NOT NULL,
`education` char(4) NOT NULL,
`birth` date NOT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`workYear` tinyint(1) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`phone` char(12) DEFAULT NULL,
`departmentID` char(3) DEFAULT NULL,
PRIMARY KEY (`employeeID`),
KEY `on_employee` (`name`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

复制现成的表

1
2
3
4
5
6
7
CREATE TABLE [IF NOT EXISTS] table_name
[() LIKE 已有表名 [ ] | [AS (表达式)];

/*例1*/
CREATE TABLE student_copy1 LIKE student;
/*例2*/
CREATE TABLE student_copy2 AS (SELECT * FROM student);

修改表

ALTER TABLE用于更改原有表的结构。例如,可以增加(删减)列、创建(取消)索引、更改原有列的类型、重新命名列或表,还可以更改表的评注和表的类型。语法格式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ALTER [IGNORE] TABLE table_name 选项...;
选项:
ADD [COLUMN] 列定义 [FIRST | AFTER 列名] /*添加列*/
ALTER [COLUMN] 列名 {SET DEFAULT iteral | DROP DEFAULT} /*修改默认值*/
CHANGE [COLUMN] 原列名 新列名 [FIRST | AFTER 列名] /*列名重定义*/
MODIFY [COLUMN] 列定义 [FIRST | AFTER 列名] /*修改列数据类型*/
DROP [COLUMN] 列名 /*删除列*/
RENAME [TO] 新表名 /*重命名该表*/
ORDER BY 列名 /*排序*/
CHARACTER SET 字符集名
COLLATE 校队规则名

/*例*/
ALTER TABLE student
ADD scholarship tinyint(1) null,
DROP COLUMN name;

直接修改表名:
1
RENAME TABLE 旧表名 TO 新表名 ...;

删除表

1
DROP TABLE [IF EXISTS] 表名1[,表名2,...]
1
TRUNCATE TABLE 表名;	# 删除表并重新创建

文件导入数据

注意:后面向表中导入本地文件所存储的数据时,文件需完整路径名,且文件路径名不能有中文和空格,路径用/不能用\。谨慎避坑。

同时导入数据可能遇到

The MySQL server is running with the --secure-file-priv option so it cannot execute

需要如下修改my.ini文件
在mysql安装文件夹下的my.ini文件中修改或添加以下内容,之后重启mysql

1
2
[mysqld]
secure_file_priv=""

secure-file-priv的值有三种情况:
1
2
3
secure_file_prive=null ––限制mysqld 不允许导入导出
secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下
secure_file_priv='' – --不对mysqld 的导入 导出做限制

详情见:https://blog.csdn.net/weixin_44595372/article/details/88723191

1
2
3
4
5
6
/*想在table中导入csv文件中的data*/
LOAD DATA INFILE 'D:/XXX/XXX/XXX.csv' INTO TABLE table_name
CHARACTER SET gbk
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
[(col_name,col_name,.....)];

插入记录

插入一个全新记录到表里面,使用INSERT命令,语法格式如下:

1
2
3
4
5
6
7
8
9
10
11
12
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] 
[IGNORE] [INTO] 表名 [(列名,...)]
VALUES ({expr | DEFAULT},...),
| SET 列名={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE 列名=expr, ... ];

/*例:注意 除数字外,字符串需要用''合起来*/
INSERT INTO student
VALUES('081101','王林','计算机',1,'1994-02-10',50,null);

insert into student(studentkey,name)
values('00001','张三'),('0002','李四');

已有表中选择记录到当前表中,语法格式如下:
1
2
3
4
5
6
7
8
9
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] 
表名 [(列名,...)]
SELECT语句
[ ON DUPLICATE KEY UPDATE 列名=expr, ... ];
/*注:若有primary key冲突,可以用replace命令*/

/*例*/
INSERT INTO user1
SELECT * FROM user;

修改记录

修改基本表中的记录,使用UPDATE命令,语法格式如下:

1
2
3
4
5
6
7
8
9
UPDATE [LOW_PRIORITY] [IGNORE] 表名
SET 列名1=expr1 [, 列名2=expr2 ...]
[WHERE 条件]
[ORDER BY ...]
[LIMIT row_count];

/*例*/
UPDATE student
SET totalcredit=totalcreadit+10;

删除记录

删除基本表中的记录,使用DELETE命令,语法格式如下:

1
2
3
4
5
6
7
8
9
10
11
12
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM 表名
[WHERE 条件]
[ORDER BY ...]
[LIMIT row_count];
/*注:*/
/*清除所有记录,下面两语句任选其一*/
DELETE FROM 表名;
TRUNCATE TABLE 表名;

/*例*/
delete from student
where name='张三';
1
2
/*删除表table*/
DROP TABLE table_name;
1
2
3
4
5
6
7
8
9
/*向表中插入数据*/
INSERT INTO TABLE_NAME
[(col_name,col_name,.....)]
VALUES(xx,'xx',.....)
/*从其他表中插入数据到另一个表中*/
INSERT INTO TABLE_NAME
[(col_name,col_name,.....)]
SELECT [*|col_name,col_name,...]
FROM table_nam

DQL

单表查询、分组统计查询和连接查询,视图的创建,删除等

  • 基本查询
  • 条件查询(WHERE)
  • 聚合查询(count、max、min、avg、sum)
  • 分组查询(GROUP BY)
  • 排序查询(ORDER BY)
  • 分页查询(LIMIT)

SELECT语句

1
2
3
4
5
6
7
8
9
10
SELECT [ALL | DISTINCT | DISTINCTROW ][HIGH_PRIORITY]...
列名表达式 …
[FROM table_reference ... ] /*FROM子句*/
[WHERE 条件] /*WHERE子句*/
[GROUP BY {列名| 表达式 | position} [ASC | DESC], ... [WITH ROLLUP]]
/*GROUP BY子句*/
[HAVING 条件] /*HAVING 子句*/
[ORDER BY {列名 | 表达式 | position} [ASC | DESC] , ...]
/*ORDER BY子句*/
[LIMIT {[offset,] row_count|row_count OFFSET offset}]; /*LIMIT子句*/

聚合函数

通常与GROUP BY子句一起使用:

函 数 名 说 明
COUNT 求组中项数,返回int类型整数
MAX 求最大值
MIN 求最小值
SUM 返回表达式中所有值的和
AVG 求组中值的平均值
STD或STDDEV 返回给定表达式中所有值的标准差
VARIANCE 返回给定表达式中所有值的方差
GROUP_CONCAT 返回由属于一组的列值连接组合而成的结果
BIT_AND 逻辑或
BIT_OR 逻辑与
BIT_XOR 逻辑异或

查询示例

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
41
42
43
44
/*下面都是例子例*/
SELECT name, major, totalcredit FROM student;

/*聚合函数,查询学生总人数*/
SELECT count(*) AS `total student number` FROM student;

/*聚合函数,查询选修101课程的学生的最高分和最低分*/
SELECT max(result), min(result) FROM score WHERE coursekey='101';

/*聚合函数,查询选修101课程的学生的平均成绩*/
SELECT avg(result) FROM score WHERE coursekey='101';

/*全连接,查找DBST中所有学生选过的课程名和课程号*/
SELECT DISTINCT course.name, score.coursekey
FROM course, score
WHERE course.coursekey=score.coursekey;

/*JOIN连接,查找选修了206课程且成绩在80分以上的学生姓名及成绩*/
SELECT name, result
FROM student
JOIN score
ON student.studentkey=score.studentkey
WHERE coursekey='206' AND result>=80;

/*模式匹配,查找所有姓“王”的学生学号、姓名*/
SELECT studentkey, name FROM student WHERE name LIKE '王%';

/*GROUP BY子句,求DBST中各专业的学生数*/
SELECT major, count(*) AS num_student
FROM student
GROUP BY major;

/*HAVING子句,查找平均成绩在85分以上的学生的学号和平均成绩*/
SELECT studentkey, avg(result) AS avg_result FROM score
GROUP BY studentkey HAVING avg(result)>=85;

/*ORDER BY子句,将通信工程专业的学生按出生日期排序*/
SELECT studentkey, name, major, birth
FROM student
WHERE major='通信工程' ORDER BY birth;

/*LIMIT子句,查找student表中从第4位学生开始的5名学生信息*/
SELECT studentkey, name, major, gender, birth, totalcredit
FROM student ORDER BY studentkey LIMIT 3,5;

SQL查询语法要点

通配符 * 可以表示所有字段

可以使用+ - * / 数学表达式,列与列之间的运算

AS 别名

select id as 'ID' from student;

ORDER BY 排序

1
2
3
4
默认升序ASC
降序 DESC
select * from employee order by age desc; # 按年龄降序
select * from employee order by age asc, entrydate desc; # 按年龄升序排序,年龄相同再按入职时间降序排序

DISTINCT 去重

需放在第一个字段名之前,否则报错
作用于后面跟着的所有字段      

WHERE + 条件

功能 比较运算符
大于 >
小于 <
等于 =
不等于 != 或 <>
大于等于 >=
小于等于 <=
某个范围(含左右边界) BETWEEN … AND …
in列表中的值,多选一 IN(…)
模糊匹配(_单个字符, %任意各字符) LIKE 占位符
是NULL/不是NULL IS NULL 或 IS NOT NULL
功能 逻辑运算符
并且(多个条件同时成立) AND 或 &&
或者 OR 或 \ \
非,不是 NOT 或 !
1
2
3
4
5
6
7
8
9
10
11
WHERE age>12 AND (gender='男' OR gender='女')

WHERE birthday BETWEEN '2001-01-01' AND '2021-12-31'

WHERE province IN('四川省','重庆市')

WHERE phone_number = NULL; /*是错的*/
WHERE phone_number IS NULL

WHERE text LIKE '%<img'
WHERE text LIKE '_ary'

五大聚类函数

COUNT()

  • COUNT(*)统计所有行数,包括列中是NULL的行和重复的行
  • COUNT(X字段)统计X列中不为NULL的行数
  • COUNT(DISTINCT X)统计X列中不为NULL的行数,并去重

MAX()

  • eg: SELECT account_id,MAX(total_price) AS max_price
  • MAX()找最大的数值或日期值
  • MAX()函数忽略列值为NULL的行
  • 在MySQL中,可以用于文本列字母排序的最高值

MIN()和MAX()类似

SUM()

  • 返回指定列值的和
  • 必须指定列名如:SUM(total_price)
  • 忽略列值为NULL的行
  • eg: SELECT SUM(total_price)/50000 AS ROI

AVG()

  • 计算数值列的平均值
  • 忽略列值为NULL的行
  • eg: SELECT AVG(total_price) AS avg_price

GROUP BY 分组

1
2
3
4
5
# 根据性别分组,统计男性员工 和 女性员工的数量
select gender, count(*) from employee group by gender;

# 查询年龄小于45的员工,根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count from employee where age < 45 group by workaddress having count(*) >= 3;

分组之后,查询的字段一般为聚合函数和分组字段,查询其它字段无任何意义

HAVING和WHERE

where和having的区别:

执行时机不同:

  • where是分组之前进行过滤,过滤行
  • having是对分组之后的结果过滤,过滤组

判断条件不同:where不能对聚合函数进行判断,而having可以

子查询

子查询就是将一个查询(子查询)的结果作为另一个查询(主查询)的数据来源或判断条件的查询

1
2
3
4
5
6
7
8
/*例*/
SELECT *
FROM account_2c
WHERE id NOT IN(
SELECT account_id
FROM address
WHERE city='成都市'
);

ANY:子查询结果至少存在一个满足
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*例*/
SELECT name
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = '设计');
/*等价于*/
SELECT name
FROM employees
WHERE salary > (
SELECT MIN(salary)
FROM employees
WHERE department = '设计');

ALL:子查询结果所有均要满足
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*例*/
SELECT department
FROM employees
WHERE salary > ALL(
SELECT salary
FROM employees
WHERE department = '法务');
/*等价于*/
SELECT department
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE department = '法务');

内联结(连接)JOIN ON

INNER JOIN 内联结(INNER可省略)

ON 设定联结键的条件

1
2
3
4
5
6
/*例:将商品名与品牌表的数据联结,并筛选出所有的商品编号、商品名、品牌名*/
SELECT c.id,sku_name,brand_name
FROM commodity AS c
INNER JOIN brand AS b
ON c.brand_id=b.id;
/*多表查询列名冲突时,表名限定列名*/

外联结(连接)

左外联结:LEFT JOIN

右外联结:RIGHT JOIN

多表连接查询

limit

1
SELECT ... FROM ... LIMIT 起始索引,查询记录数;
  • 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
  • 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL是LIMIT
  • 如果查询的是第一页数据,起始索引可以省略
1
2
select .. .. limit 4;   /*取最后结果的前四行*/
select .. .. limit 5,4; /*最后结果的去掉前5行,再取4行*/

SQL书写顺序

SQL书写顺序

SQL执行顺序

SQL执行顺序

使用AS关键字时,执行顺序这一点会有体现,尤其注意!

注:字符串加单引号’’,字段名即列名在特殊情况下用 `` (英文键盘下Tab上面的键)包裹

注:多个表连接时的格式:

1
2
3
4
5
6
7
select xxx
from table_name
join table1_name
on 条件
join table2_name
on 条件
....

多表关系

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

  • 一对多(多对一)
  • 多对多
  • 一对一

一对多

  • 案例: 部门 与 员工的关系
  • 关系: 一个部门对应多个员工,一个员工对应一个部门
  • 实现: 在多的一方建立外键,指向一的一方的主键

多对多

  • 案例: 学生 与 课程的关系
  • 关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
  • 实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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
30
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
-- 准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';

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 dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
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, '会计',4800, '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);

分类

多表查询就是指从多张表中查询数据。执行多表查询,就只需要使用逗号分隔多张表即可,

如:select * from emp , dept;,查询结果中包含了大量的结果集,总共102条记录,而这其实就是员工表emp所有的记录(17) 与 部门表dept所有记录(6) 的所有组合情况,这种现象称之为笛卡尔积。

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

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

我们可以给多表查询加上连接查询的条件即可。select * from emp , dept where emp.dept_id = dept.id;

分类

  1. 连接查询
    1. 内连接:相当于查询A、B交集部分数据
    2. 外连接:
      1. 左外连接:查询左表所有数据,以及两张表交集部分数据
      2. 右外连接:查询右表所有数据,以及两张表交集部分数据
    3. 自连接:当前表与自身的连接查询,自连接必须使用表别名
  2. 子查询

内连接

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

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

查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现):

1
2
3
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现):

1
2
3
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

外连接

外连接分为两种,分别是:左外连接 和 右外连接。

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

查询emp表的所有数据, 和对应的部门信息:

1
2
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

查询dept表的所有数据, 和对应的员工信息(右外连接):

1
2
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

注意事项:

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

自连接

自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。

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

而对于自连接查询,可以是内连接查询,也可以是外连接查询。

1
2
3
4
-- 查询员工 及其 所属领导的名字:
select a.name, b.name from emp a, emp b where a.managerid = b.id;
-- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

注意事项:

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

联合查询

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

1
2
3
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
  • 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
1
2
3
4
-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来:
select * from emp where salary < 5000
union
select * from emp where age > 50;

注意:

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

子查询

SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。

1
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
-- 查询 "销售部" 的所有员工信息
-- a.查询 "销售部" 部门ID
-- b.根据 "销售部" 部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 查询 方东百 入职之后的员工信息
-- 1. 查询 方东白 的入职日期
-- 2. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
列子查询

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

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

操作符 描述
IN 在指定的集合范围之内,多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中,有任意一个满足即可
SOME 与ANY等同,使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询 "销售部" 和 "市场部" 的所有员工信息
-- a. 查询 "销售部" 和 "市场部" 的部门ID
-- b. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

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

-- 查询比研发部其中任意一人工资高的员工信息
-- a. 查询研发部所有人工资
-- b. 比研发部其中任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
行子查询

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

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

1
2
3
4
5
-- 查询与 "张无忌" 的薪资及直属领导相同的员工信息
-- a. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息;
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
表子查询

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

常用的操作符:IN

1
2
3
4
5
6
7
8
9
-- 查询与 "鹿杖客","宋远桥" 的职位和薪资相同的员工信息
-- a. 查询 "鹿杖客","宋远桥" 的职位和薪资
-- b. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客' or name = '宋远桥' );

-- 查询入职日期是'2006-01-01'之后的员工信息,及其部门信息
-- a. 入职日期是 '2006-01-01' 之后的员工信息
-- b. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

多表查询案例

在多表查询的数据基础上,添加下面的表,共:emp员工表、dept部门表、salgrade薪资等级表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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);
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
-- 查询员工的姓名、年龄、职位、部门信息 (隐式内连接)
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;

-- 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

-- 查询拥有员工的部门ID、部门名称
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;

-- 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;

-- 查询所有员工的工资等级
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;

-- 查询 "研发部" 所有员工的信息及 工资等级
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部';

-- 查询 "研发部" 员工的平均工资
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

-- 查询工资比 "灭绝" 高的员工信息。
select * from emp where salary > ( select salary from emp where name = '灭绝' );

-- 查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );

-- 查询低于本部门平均工资的员工信息
select *, avgs from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id ) avgs;

-- 查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name, ( se lect count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;

-- 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

DCL

管理用户

创建用户

使用CREATE USER命令创建用户并设置密码,语法格式如下:

1
2
3
4
5
6
CREATE USER 用户 [IDENTIFIED BY'password'];
/*用户='用户名@登录主机名'*/ /*初始用户一般是root@localhost*/

/*例*/
create user 'ALEX'@'LOCALHOST' identified by '123456';
create user 'yanglinqi'@'%' identified by '123456'; # 允许任意主机登录

查看用户

1
2
/*使用以下命令查看所有用户*/
select * from mysql.user;

其中 Host 代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

删除用户

使用DROP USER命令删除用户,语法格式如下:

注:只有用户当前数据库全局权限的用户才能使用DROP命令

1
2
3
4
5
DROP USER 用户; 
/*用户形式 用户名@主机 例:yanglinqi@localhost 用户是区分大小写的*/

/*例*/
drop user yanglinqi@localhost;

修改用户信息

修改用户密码:

使用SET PASSWORD命令修改某用户登录密码,语法格式如下:

1
2
3
4
5
6
7
/*8.0版本之前*/
SET PASSWORD FOR 用户=PASSWORD('新密码');
/*8.0版本及之后*/
SET PASSWORD FOR 用户='新密码'

/*例*/
set password for ALEX@LOCALHOST='123';

也可以使用以下命令修改密码

1
2
ALTER USER 'ALEX'@'localhost' IDENTIFIED BY '123';
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

还可以使用update

1
2
UPDATE user SET password=PASSWORD('123456') WHERE user='root';
FLUSH PRIVILEGES;

修改用户名:

1
2
/*相当于修改mysql.user这张存有用户信息的表,将ALEX修改为alex*/
update mysql.user set user='alex' where user='ALEX';

注意事项:

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

2、主机名可以使用 % 通配。

3、这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用。

权限控制

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

权限 说明
ALL, ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表

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

授予权限

可授予用户列权限、表权限、数据库权限、用户权限等,语法格式如下:

1
2
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'
[IDENTIFIED BY '密码'][WITH grant option];

注:若指定密码,则原密码将会被覆盖,如果权限授予一个不存在的用户,则MySQL会自动创建这个用户,但必须为该用户指定密码。

多个权限之间,使用逗号分隔;授权时, 数据库名和表名可以使用 * 进行通配,代表所有;如果要授予所有权限,则用ALL或者ALL PRIVILEGES表示。

WITH GRANT OPTION:若加上了WITH GRANT OPTION子句,获得某种权限的用户还可以把这种权限再授予其他的用户。

1
2
3
4
5
6
7
8
9
10
11
/*例*/
/*说明:student是dbst数据库的一张表,需要先使用use dbst;选择该数据库,当然数据库和表都要先创建*/

/*这条命令给予ALEX表student的权限*/
grant select on student to ALEX@LOCALHOST;

grant update(name) on student to ALEX@LOCALHOST;
/*这条命令给予ALEX更新studnet表中name列的权限*/

GRANT ALL ON DBST.* TO ALEX@LOCALHOST;
GRANT ALL ON *.* TO ALEX@LOCALHOST; # 所有数据库所有表的所有权限

查看权限

1
2
3
4
5
show grants for '用户名'@'主机名';

/*例*/
show grants for root@localhost;
show grants for ALEX@LOCALHOST;

回收权限

使用REVOKE命令可以回收授予的权限,语法格式如下:

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

/*例*/
revoke select ON student from ALEX@LOCALHOST;

权限一览表

语 句 用 户 权 限 数据库权限 表 权 限 列 权 限
SELECT Yes Yes Yes No
INSERT Yes Yes Yes No
DELETE Yes Yes Yes Yes
UPDATE Yes Yes Yes Yes
REFERENCES Yes Yes Yes Yes
CREATE Yes Yes Yes No
ALTER Yes Yes Yes No
DROP Yes Yes Yes No
INDEX Yes Yes Yes Yes
CREATE TEMPORARY TABLES Yes Yes No No
CREATE VIEW Yes Yes No No
SHOW VIEW Yes Yes No No
CREATE ROUTINE Yes Yes No No
ALTER ROUTINE Yes Yes No No
EXECUTE ROUTINE Yes Yes No No
LOCK TABLES Yes Yes No No
CREATE USER Yes No No No
SHOW DATABASES Yes No No No
FILE Yes No No No
PROCESS Yes No No No
RELOAD Yes No No No
REPLICATION CLIENT Yes No No No
REPLICATION SLAVE Yes No No No
SHUTDOWN Yes No No No
SUPER Yes No No No
USAGE Yes No No No

函数

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

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

字符串函数

MySQL中内置了很多字符串函数,常用的几个如下:

函数 功能
CONCAT(S1,S2,…Sn) 字符串拼接,将S1,S2,… Sn拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,n,pad) 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
RPAD(str,n,pad) 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串
CAST(expr as type) 将expr转化为type类型

使用示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- concat : 字符串拼接
select concat('hello',' mysql');
-- lower : 全部转小写
select lower('Hello');
-- upper : 全部转大写
select upper('Hello');
-- lpad : 左填充
select lpad('01',5,'-');
-- rpad : 右填充
select rpad('01',5,'-');
-- trim : 去除空格
select trim(' Hello MySQL ');
-- substring : 截取子字符串
select substring('Hello MySQL', 1, 5);

数值函数

常见数值函数如下:

函数 功能
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模(相除取余)
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数x的四舍五入的值,保留y位小数
1
2
3
4
5
6
7
8
9
10
-- ceil:向上取整
select ceil(1.1);
-- floor:向下取整
select floor(1.9);
-- mod:取模
select mod(7,4);
-- rand:获取随机数
select rand();
-- round:四舍五入
select round(2.344, 2);

通过数据库的函数,生成一个六位数的随机验证码

1
select lpad(round(rand()*1000000, 0), 6, '0');

日期函数

常见的日期函数如下:

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2) 返回起始时间date1 和 结束时间date2之间的天数
SEC_TO_TIME(sec) 将秒值转换为时间格式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- curdate
select curdate();
-- curtime
select curtime();
-- now()
select now();
-- year, month, day
select year(now());
select month(now());
select day(now());
-- date_add
select date_add(now(), INTERVAL 70 DAY); # 向后推70
select date_add(now(), INTERVAL -1 YEAR); # 向前推1
-- datediff
select datediff('2021-12-01', '2021-10-01'); # 第一个日期减去第二个日期
-- sec_to_time
select sec_to_time('54925'); # 15:15:25

查询所有员工的入职天数,并根据入职天数倒序排序:

1
select name, datediff(curdate(), entrydate) as 'entrydates' from employee order by entrydates desc;

流程函数

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

函数 功能
IF(value , t , f) 如果value为true,则返回t,否则返回f
IFNULL(value1 , value2) 如果value1不为空,返回value1,否则返回value2
CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果val1为true,返回res1,… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果expr的值等于val1,返回res1,… 否则返回default默认值
1
2
3
4
5
6
7
8
-- if
select if(true, 'ok', 'error');
select if(false, 'ok', 'error');
-- ifnull
select ifnull('0k','default');
select ifnull(null, 'default');
-- case when then else end
select name, (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from employee;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 统计班级各个学员的成绩,展示规则如下:
-- >= 85 优秀
-- >= 60 及格
-- 其它 不及格
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95 ), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

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

约束

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

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

分类:

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY
默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT
检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY

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

建表示例

案例需求: 根据需求,完成表结构的创建。需求如下:

字段名 字段含义 字段类型 约束条件 约束关键字
id ID唯一标识 int 主键,并且自动增长 PRIMARY KEY, AUTO_INCREMENT
name 姓名 varchar(10) 不为空,并且唯一 NOT NULL , UNIQUE
age 年龄 int 大于0,并且小于等于120 CHECK
status 状态 char(1) 如果没有指定该值, 默认为1 DEFAULT
gender 性别 char(1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 建表
create table user(
id int primary key auto_increment comment '主键',
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 '性别'
) comment '用户表';
-- 插入数据
insert into user(name,age,status,gender) values ('Tom1', 19, '1', '男'),('Tom2', 25, '0', '男');
insert into user(name,age,status,gender) values ('Tom1', 19, '1', '男');
insert into user(name,age,status,gender) values (null, 19, '1', '男');
insert into user(name,age,status,gender) values ('Tom2', 19, '1', '男');
insert into user(name,age,status,gender) values ('Tom4', 50, '1', '男');
insert into user(name,age,status,gender) values ('Tom5', -1, '1', '男');
insert into user(name,age,gender) values ('Tom8', 120, '男');

外键约束

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

示例:

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

注意:目前上述两张表,只是在逻辑上存在这样一层关系;在数据库层面,并未建立外键关联,所以是无法保证数据的一致性和完整性的

设置/删除外键

添加外键的方法

1
2
3
4
5
6
7
8
9
-- 创建表的时候设置
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

-- 为已创建的表设置外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);

删除外键

1
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

上图示例的数据:

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
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
-- 为emp表的dept_id字段添加外键约束,关联dept表的主键id
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

添加了外键约束之后,我们再到dept表(父表)删除id为1的记录,然后看一下会发生什么现象。 此时

将会报错,不能删除或更新父表记录,因为存在外键约束。

删除/更新行为

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

行为 说明
NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为
RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为
CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则 也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表 中该外键值为null(这就要求该外键允许取null)。
SET DEFAULT 父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持)

具体语法:

1
ALTER TABLE 表名  ADD CONSTRAINT 外键名称  FOREIGN KEY (外键字段)	REFERENCES 主表名 (主表字段名)	ON UPDATE CASCADE ON DELETE CASCADE;

实体完整性约束

定义主键约束

  • 列级实体完整性

    列定义时加上关键字 PRIMARY KEY

  • 表级实体完整性

    表定义时,在语句最后加上PRIMARY KEY (col_name,…)

定义替代键约束

  • 替代键是没有被选作主键的候选键,用UNIQUE来定义

注:CONSTRAINT可以用来指定表完整性约束的名字

1
CONSTRAINT key_name PRIMARY KEY(key)

参照完整性约束

创建表时定义参照完整性

列级参照完整性(不会生效,仅用于备注)

  • 列定义时加上关键字 REFERENCES ref_table_name(ref_key)

表级参照完整性

  • 表定义时,在语句最后加上

    CONSTRAINT ref_key_name FOREIGN KEY (ref_key) REFERENCES ref_table_name(ref_key)

参照动作

指定这个参照动作应用哪一条语句:这里有两条相关的语句,即UPDATEDELETE语句;

指定采取哪个动作:可能采取的动作是RESTRICT、CASCADE、SET NULL、NO ACTION和SET DEFAULT

不同参照动作含义:

RESTRICT:当要删除或更新父表中被参照列上在外键中出现的值时,拒绝对父表的删除或更新操作

CASCADE:从父表删除或更新行时自动删除或更新子表中匹配的行

SET NULL:当从父表删除或更新行时,设置子表中与之对应的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是合法的

NO ACTION:NO ACTION意味着不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新父表中主要键值的企图不被允许,和RESTRICT一样

SET DEFAULT:作用和SET NULL一样,只不过SET DEFAULT是指定子表中的外键列为默认值

设置外键的时候需要注意以下几点

  1. 外键是用于两个表的数据之间建立连接,可以是一列或者多列,即一个表可以有一个或多个外键。
  2. 这个表里面设置的外键必须是另外一个表的主键!
  3. 外键可以不是这个表的主键,但必须和另外一个表的主键相对应(字段的类型和值必须一样)。
  4. 带有主键的那张表称为父表,含外键的是子表,必须先删除外键约束才能删除父表。

并发控制

事务概念

事务由作为一个单独单元的一个或多个SQL语句组成。这个单元中的SQL语句是相互依赖的,整个单元是不可分割的。如果单元中的一个语句不能完成,整个单元就会回滚(撤销操作),所有影响到的数据将返回到事务开始以前的状态。(银行交易、网上购物等)

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

就比如:张三给李四转账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 = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

2)测试异常情况

1
2
3
4
5
6
7
-- 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 = '李四';

我们把数据都恢复到2000, 然后再次一次性执行上述的SQL语句(出错了…. 这句话不符合SQL语法,执行就会报错),检查最终的数据情况, 发现数据在操作前后不一致了。

控制事务

方法一

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

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

方法二

1
2
3
4
5
6
-- 开启事务
START TRANSACTION 或 BEGIN ;
-- 提交事务
COMMIT;
-- 回滚事务
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;

四大特性ACID

原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。

一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。

隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。

持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。

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

并发控制问题

丢失修改:两个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,因此最后的更新将重写由其他事务所做的更新,导致数据丢失

脏读:一个事务正在访问数据,而其他事务正在更新该数据,但尚未提交,此时会发生脏读问题,即第一个事务所读取的数据是“脏”的

不可重复读:在一个事务内多次读同一数据,在该事务还没有结束时,另外的事务也访问该数据并对其做修改,从而导致第一个事务两次读取的数据不一样

幻读:当一个事务对某行执行了插入或删除操作时,由于该行在另外的事务读取的范围内,从而导致两次读取多出了一行或者消失了一行

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

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

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

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

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

事务隔离级别

为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。四级隔离级别:定义用户之间隔离和交互的程度

  • 读未提交(read uncommitted):可以读到其他事务还没有提交的数据,仅避免丢失修改,会导致大量数据变化,一级封锁协议

  • 读已提交(read committed):可以看到其他事务添加的新纪录,而且其他事务对现存记录做出的修改一旦被提交,也可以看到,避免丢失修改和脏读,二级封锁协议

  • 可重复读(repeatable read):当前在执行的事务的变化无法被看到,同一事务中执行select数次结果都相同,避免丢失修改、脏读和不可重复读,增强的二级封锁协议

  • 可串行化(serializable):用户之间一个接一个执行事务,避免所有并发控制问题,最大限度的隔离,三级封锁协议

注:MySQL默认为repeatable read隔离级别

隔离级别 丢失修改 脏读 不可重复读 幻读
读未提交 X
读已提交 X X
可重复读 X X X
可串行化 X X X X
1
2
3
4
-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

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

在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

事务控制语句

1
2
3
4
5
6
7
8
9
10
11
12
13
BEGINSTART TRANSACTION 显式地开启一个事务;

COMMIT 也可以使用 COMMIT WORK,不过二者是等价的。COMMIT 会提交事务,并使已对数据库进行的所有修改成为永久性的;

ROLLBACK 也可以使用 ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;

SAVEPOINT identifier,SAVEPOINT 允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT

RELEASE SAVEPOINT identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;

ROLLBACK TO identifier 把事务回滚到标记点;

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE

MYSQL 事务处理主要有两种方法:

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

补充

注意:用户的”localhost”,是指该用户只能在本地登录,不能在另外一台机器上远程登录。如果想远程登录的话,将”localhost”改为”%”,表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

flush privileges; 刷新系统权限表

查看帮助

在 MySQL 中,查看帮助的命令是 HELP,语法格式如下:

1
HELP 查询内容	# 查询内容为要查询的关键字
  • 查询内容中不区分大小写。
  • 查询内容中可以包含通配符“%”和“_”,效果与 LIKE 运算符执行的模式匹配操作含义相同。例如,HELP 'rep%' 用来返回以 rep 开头的主题列表。
  • 查询内容可以使单引号引起来,也可以不使用单引号,为避免歧义,最好使用单引号引起来。

示例:

1)查询帮助文档目录列表

image-20221023112153973

2)查看具体内容

HELP 'Data Types;'查看所支持的数据类型

image-20221023112331911

进一步查看某一数据类型,如 INT 类型,可以使用 HELP INT;命令,运行结果如下:

image-20221023112430667

另外,还可以查询某命令,例如使用 HELP CREATE TABLE 命令查询创建数据表的语法,运行结果如下所示:

image-20221023112552579

拓展

MySQL 提供了 4 张数据表来保存服务端的帮助信息,即使用 HELP 语法查看的帮助信息。执行语句就是从这些表中获取数据并返回给客户端的,MySQL 提供的 4 张数据表如下:

  • help_category:关于帮助主题类别的信息
  • help_keyword:与帮助主题相关的关键字信息
  • help_relation:帮助关键字信息和主题信息之间的映射
  • help_topic:帮助主题的详细内容

查看系统变量

1
2
SHOW GLOBAL VARIABLES;	# 查看 MySQL 中所有的全局变量信息
SHOW SESSION VARIABLES; # 查看与当前会话相关的所有会话变量以及全局变量,SESSION 关键字可以省略

MySQL 中的系统变量以两个“@”开头。

  • @@global 仅仅用于标记全局变量;
  • @@session 仅仅用于标记会话变量;
  • @@ 首先标记会话变量,如果会话变量不存在,则标记全局变量。

MySQL 中有一些系统变量仅仅是全局变量,例如 innodb_data_file_path,可以使用以下 3 种方法查看:

1
2
3
SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path';
SHOW SESSION VARIABLES LIKE 'innodb_data_file_path';
SHOW VARIABLES LIKE 'innodb_data_file_path';

MySQL 中有一些系统变量仅仅是会话变量,例如 MySQL 连接 ID 会话变量 pseudo_thread_id,可以使用以下 2 种方法查看。

1
2
SHOW SESSION VARIABLES LIKE 'pseudo_thread_id';
SHOW VARIABLES LIKE 'pseudo_thread_id';

MySQL 中有一些系统变量既是全局变量,又是会话变量,例如系统变量 character_set_client 既是全局变量,又是会话变量。

1
2
SHOW SESSION VARIABLES LIKE 'character_set_client';
SHOW VARIABLES LIKE 'character_set_client';

此时查看全局变量的方法如下:

1
SHOW GLOBAL VARIABLES LIKE 'character_set_client';

设置系统变量

可以通过以下方法设置系统变量:

  1. 修改 MySQL 源代码,然后对 MySQL 源代码重新编译(该方法适用于 MySQL 高级用户,这里不做阐述)。
  2. 在 MySQL 配置文件(mysql.ini 或 mysql.cnf)中修改 MySQL 系统变量的值(需要重启 MySQL 服务才会生效)。
  3. 在 MySQL 服务运行期间,使用 SET 命令重新设置系统变量的值。

服务器启动时,会将所有的全局变量赋予默认值。这些默认值可以在选项文件中或在命令行中对执行的选项进行更改。

更改全局变量,必须具有 SUPER 权限。设置全局变量的值的方法如下:

1
2
3
SET @@global.innodb_file_per_table=default;
SET @@global.innodb_file_per_table=ON;
SET global innodb_file_per_table=ON;

需要注意的是,更改全局变量只影响更改后连接客户端的相应会话变量,而不会影响目前已经连接的客户端的会话变量(即使客户端执行 SET GLOBAL 语句也不影响)。也就是说,对于修改全局变量之前连接的客户端只有在客户端重新连接后,才会影响到客户端。

客户端连接时,当前全局变量的值会对客户端的会话变量进行相应初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。设置会话变量的值的方法如下:

1
2
3
4
SET @@session.pseudo_thread_id=5;
SET session pseudo_thread_id=5;
SET @@pseudo_thread_id=5;
SET pseudo_thread_id = 5;

如果没有指定修改全局变量还是会话变量,服务器会当作会话变量来处理。比如:

1
SET @@sort_buffer_size = 50000; # 没有指定是 GLOBAL 还是 SESSION,服务器会当做 SESSION 处理

使用 SET 设置全局变量或会话变量成功后,如果 MySQL 服务重启,数据库的配置就又会重新初始化。一切按照配置文件进行初始化,全局变量和会话变量的配置都会失效。

MySQL 中还有一些特殊的全局变量,如 log_bin、tmpdir、version、datadir,在 MySQL 服务实例运行期间它们的值不能动态修改,也就是不能使用 SET 命令进行重新设置,这种变量称为静态变量。数据库管理员可以使用前面提到的修改源代码或更改配置文件来重新设置静态变量的值。

MySQL注释

mysql的注释有4 种 :

1
2
3
4
# 开头到行尾的都为注释,只能注释一行
-- 开头到行尾都为注释 , 只能注释一行
/* 可以注释多行,但是一定要闭合,不然出错 */
/*! 数字 代码 */

/*! 数字 代码 */" 是有特殊含义的

例如: /!40101 SET NAMES gbk /; 代表的是 如果mysql 版本大于4.0.1.01 ,就执行后面的 set names gbk 这句代码。这就有一个好处,这样就可以把mysql 特有的功能用这种注释,给注释掉,对于非mysql数据库可以正常运行,不需要修改,而对于mysql 数据库,就可以利用这些特性。

MySQL密码规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like 'validate_password%';  # 查看密码规则
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | | # 规则文件保存路径
| validate_password.length | 8 | # 密码长度
| validate_password.mixed_case_count | 1 | # 整个密码中至少要包含大/小写字母的总个数
| validate_password.number_count | 1 | # 整个密码中至少要包含阿拉伯数字的个数
| validate_password.policy | MEDIUM | # 密码的验证强度等级
| validate_password.special_char_count | 1 | # 密码中特殊字符至少的个数
+--------------------------------------+--------+
7 rows in set (0.00 sec)

其中验证等级:

  • 0/LOW:只验证长度;
  • 1/MEDIUM:验证长度、数字、大小写、特殊字符;
  • 2/STRONG:验证长度、数字、大小写、特殊字符、字典文件;

欢迎关注我的其它发布渠道