mysql学习

ssh登录mysql

mysql -u [用户名] -p
mysql -u root -p

1、DDL操作之数据库操作
--查看所有数据库
show databases;
--创建数据库
create database mydb1;
create database if not exists mydb1;
--选择使用哪一个数据库
use mydb1;
--删除数据库
drop database mydb1;
drop database if exists mydb1;
--修改数据库编码
alter database mydb1 character set utf8;

一、DDL 数据库操作

1、数据库操作

-- 查看所有数据库
show databases;
-- 创建数据库
create database if not exists mydb1 charset=utf8;
create database [if not exists] mydb1 [charset=utf8];
-- 使用数据库
use mydb1;
-- 查看当前数据库的所有表名称
show tables;
-- 查看指定某个表的创建语句
show create table 表名;
-- 查看表结构
desc 表名;
-- 删除数据库
drop database [if exists] mydb1;
-- 修改数据库编码
alter database mydb1 character set utf8;

2、表结构操作

a. 新建表

create table if not exists student(
  sid int,  -- 字段名1 类型[(宽度)] [约束条件] [comment '字段说明']
  name varchar(20),
  birth date,
  score double
);  -- )[表的一些设置];

1）数值类型

create table if not exists student(
  sid int unsigned, -- unsigned 表示无符号
  sid tinyint unsigned, -- unsigned 表示无符号
);

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT 或 INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL		依赖于 M 和 D 的值	依赖于 M 和 D 的值	小数值

decimal(5,2) --> 123.45

2）字符串类型

类型	大小	用途
CHAR	0 - 255 bytes	定长字符串
VARCHAR	0 - 65535 bytes	变长字符串
TINYBLOB	0 - 255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0 - 255 bytes	短文本字符串
BLOB	0 - 65535 bytes	二进制形式的长文本数据
TEXT	0 - 65535 bytes	长文本数据
MEDIUMBLOB	0 - 16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0 - 16 777 215 bytes	中等长度文本数据
LONGBLOB	0 - 4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0 - 4 294 967 295 bytes	极大文本数据

3）日期类型

类型	大小（bytes）	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038 年 1 月 19 日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

b. 修改表：添加列

alter table 表名 add 列名 类型(长度) [约束];
alter table student add dept varchar(20);

c. 修改列名和类型

alter table 表名 change 旧列名 新列名 类型(长度) 约束;
alter table student change dept department varchar(20);
alter table student change department department varchar(30);

d. 修改表：删除列

alter table 表名 drop 列名;
alter table student drop department;

e. 修改表名

rename table 表名 to 新表名;
rename table student to stu;

f. 删除表

drop table 表名;

二、DML 数据表操作

1、数据的插入

• 法1

-- 格式：
insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...); //向表中在指定列插入值，一一对应

-- 案例：
insert into student (sid, name, gender, age, birth, address, score)
values(1001, "张三", "男", 18, "2001-12-23", "北京", 85.5);

insert into student (sid, name, gender, age, birth, address, score)
values
(1002, "李四", "男", 18, "2001-12-23", "北京", 85.5),
(1003, "小五", "男", 18, "2001-12-23", "北京", 85.5);

insert into student (sid) values(1004);

• 法2

-- 格式：
insert into 表 values (值1,值2,值3...); //向表中插入所有列

-- 案例：
insert into student values(1006, "张六", "男", 18, "2001-12-23", "北京", 85.5);

insert into student values
(1005, "李四", "男", 18, "2001-12-23", "北京", 85.5),
(1006, "小五", "男", 18, "2001-12-23", "北京", 85.5);

2、数据修改

-- 格式：
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

-- 案例：
-- 1、将所有学生的地址修改为重庆
update student set address = "重庆";

-- 2、将id为1004的学生的地址修改为北京
update student set address = "北京" where sid = 1004;
update student set address = "上海" where sid > 1004;

-- 3、将id为1005的学生的地址修改为北京，成绩修改为100
update student set address="上海",score=100 where sid=1005;
-- 把成绩再加上100
update student set address="上海",score=score + 100 where sid=1005;

3、数据删除

-- 格式：
delete from 表名 [where 条件];
truncate table 表名 或者 truncate 表名

-- 案例：
-- 1、删除sid为1004的学生数据
delete from student where sid = 1004;

-- 2、删除表所有数据
delete from student;

-- 3、清空表数据
truncate table student;
truncate student;  -- table可省略
-- 注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

三、约束

-- 主键约束(primary key) PK
每个表只允许一个主键，可以多列联合成为一个主键，【非空 + 唯一】
-- 自增长约束(auto_increment)
针对于[主键约束]的附加功能
-- 非空约束(not null)
-- 唯一性约束(unique)
-- 默认约束(default)
-- 零填充约束(zerofill)
-- 外键约束(foreign key) FK

1、主键约束(primary key)

• 单列主键

-- 创建单列主键有两种方法：
-- 方法一
create table emp1(
  eid int primary key, -- <字段名> <数据类型> primary key
  name varchar(20),
  deptid int,
  salary double
);

-- 方法二
create table emp2(
eid int,
name varchar(20),
deptid int,
salary double,
constraint pk1 primary key(id)  -- [constraint <约束名>] primary key [字段名]
); 
  -- [constraint pk1] 可以省略

• 多列主键（联合主键）

create table emp3(
  name varchar(20),
  deptid int,
  salary double,
  constraint pk2 primary key(name,deptid)  -- name 和 deptid 联合作为主键
);
  -- [constraint pk2] 可以省略
  -- 联合主键的每一列，不能为空null

• 修改表结构添加主键

create table emp4(
  eid int,
  name varchar(20),
  salary double
);
-- 添加主键
alter table emp4 add primary key (eid);

create table emp5(
  eid int,
  name varchar(20),
  deptID int,
  salary double
);
-- 添加主键
alter table emp5 add primary key (eid, deptID);

• 删除主键约束

-- 删除单列主键
alter table emp1 drop primary key;
-- 删除联合主键
alter table emp5 drop primary key;

2、自增长约束(auto_increment)

• 通过添加auto_increment属性实现主键自增长，由数据库系统根据定义自动赋值
• 这个是主键的专有约束

create table t_user1(
    id int primary key auto_increment,
    name varchar(20)
);
-- 默认情况，auto_increment初始值为1，每新增一条记录，字段值自动加1
-- 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
-- auto_increment约束的字段必须具备 NOT NULL 属性。
-- auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。
-- auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

insert into t_user1 values(null,'张三');
insert into t_user1(name) values('张三');

insert into t_user1 values(5,'张三');  -- 指定id，在不冲突情况下可写入

• 指定自增字段初始值(auto_increment=100)

-- 方式1，创建表时指定
create table t_user2 (
	id int primary key auto_increment,
	name varchar(20)
)auto_increment=100;

-- 方式2，创建表之后指定
create table t_user3 (
	id int primary key auto_increment,
	name varchar(20)
);
alter table t_user3 auto_increment=100;

• delete和truncate在删除后自增列的变化

-- delete数据之后自动增长的值被保留，继续添加数据会从上一个断点开始
delete from t_user2;
-- truncate数据之后自动增长从1开始(就算指定了自增字段初始值，也会从1开始，等于重新建表)
truncate t_user2;

3、非空约束(not null)

• 添加非空约束

方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型not null;

-- 方式1，创建表时指定
create table t_user6 (
    id int,
    name varchar(20) not null,
    address varchar(20) not null
);

-- 方式2，创建表之后指定
create table t_user7 (
    id int,
    name varchar(20), -- 指定非空约束
    address varchar(20) -- 指定非空约束
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;

• 删除非空约束

-- alter table 表名 modify 字段类型
alter table t_user7 modify name varchar(20);
alter table t_user7 modify address varchar(20);

4、唯一约束(unique)

• 添加唯一约束

-- 方式1，创建表时指定
方式1：<字段名> <数据类型> unique

create table t_user8 (
    id int,
    name varchar(20),
    phone_number varchar(20) unique -- 指定唯一约束
);

-- 方式2，创建表后指定
方式2： alter table 表名 add constraint 约束名 unique(列);

create table t_user9 (
    id int,
    name varchar(20),
    phone_number varchar(20) -- 指定唯一约束
);
alter table t_user9 add constraint unique_ph unique(phone_number);  -- 指定<唯一约束名>：unique_ph
alter table t_user9 add unique(phone_number);  -- 不指定<唯一约束名>

• 注意

-- 插入数据
insert into t_user8 values(1001,'张三',138);
insert into t_user8 values(1002,'张三',139);
insert into t_user8 values(1003,'张三',NULL);
insert into t_user8 values(1004,'张三',NULL);  -- 在mysql中，NULL和任何值都不相同，甚至和自己都不相同，所以不报【字段重复】的错误

• 删除唯一约束

-- alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_ph;
-- 如果没指定<唯一约束名>，则写入<字段名>
alter table t_user9 drop index phone_number;

5、默认约束(default)

• 添加默认约束

-- 方法1，创建表时指定
create table t_user10 (
	id int,
	name varchar(20),
    address varchar(20) default '北京'
);
-- 插入数据
insert into t_user10(id,name) values(1001,'张三');
insert into t_user10(id,name,address) values(1001,'张三','上海');
insert into t_user10 values(1001,'张三', null);
-- 表中数据
1001	张三	北京
1001	张三	上海
1001	张三	null

-- 方法2，创建表后指定
create table t_user11 (
	id int,
	name varchar(20),
	address varchar(20)
);
alter table t_user11 modify address varchar(20) default '深圳';
-- 插入数据
insert into t_user11(id,name) values(1001,'张三');
insert into t_user11(id,name,address) values(1001,'张三','上海');
insert into t_user11 values(1001,'张三', null);
-- 表中数据
1001	张三	深圳
1001	张三	上海
1001	张三	null

• 删除默认约束(default null)

alter table t_user11 modify address varchar(20) default null;

6、零填充约束(zerofill)

1、插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
2、zerofill默认为int(10)
3、当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128_{+127，无符号为0}256。

• 添加零填充约束

create table t_user12 (
    id int zerofill , -- 零填充约束，默认为int(10)，会自动加unsigned（无符号）属性
    name varchar(20)
);

insert into t_user12 values(123, 'asd');
insert into t_user12 values(-11, 'asd');  -- 会报错，超出正整数范围

-- 表数据
0000000123	asd  -- id长度为10

• 删除零填充约束

alter table t_user12 modify id int;  -- 变回默认int 普通类型
alter table t_user12 modify id int(6) ZEROFILL;  -- 或修改成6位长度

注意：这个填充只是显示上的效果，实际存储中没有前导 0。

作用和意义：统一格式显示、避免字符串处理、排序兼容性。

ZEROFILL 从 MySQL 8.0 开始被标记为不推荐使用（Deprecated），将来可能被移除。

四、DQL 数据查询

-- 语法
select
[all|distinct]
<目标列的表达式1> [别名],
<目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名>
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

-- 简化
select *| 列名 from 表 where 条件

1、简单查询

• 1.查询所有的商品

SELECT * FROM product;

• 2.查询商品名和商品价格

SELECT pname,price FROM product;

• 3.别名查询，使用的关键字是as（as可以省略的）

-- 3.1表别名:
SELECT * FROM product as p;
SELECT * FROM product p;
	-- 意义：当多表查询时起简化作用
    SELECT product.id,product.id FROM product, user;
    SELECT p.id,u.id FROM product p, user u;
-- 3.2列别名：
SELECT pname as '商品名',price as '商品价格' from product; -- 表数据显示as的别名。

• 4.去掉重复值

SELECT DISTINCT price FROM product;
SELECT DISTINCT * FROM product; -- 表中所有列都重复，会去重。

• 5.查询结果是表达式（运算查询）

-- 将所有商品的价格+10元进行显示
SELECT pname,price+10 as new_price FROM product;

2、运算符

通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

-- MySQL支持4种运算符
-- 1、算术运算符
-- 2、比较运算符
-- 3、逻辑运算符
-- 4、位运算符

• 算术运算符

-- 【+】【-】【*】
-- 【/】除，返回商
-- 【%】求余，返回余数

• 比较运算符

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于，两个操作码均为 NULL 时，其所得值为 1；而当一个操作码为 NULL 时，其所得值为 0
<> 或 !=	不等于
IS NULL 或 ISNULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是 IN 列表中的任意一个值
NOT IN	判断一个值不是 IN 列表中的任意一个值
LIKE	通配符匹配【只适合简单查询，速度快】【%，_】
REGEXP	正则表达式匹配【复杂查询，速度较慢】

• 逻辑运算符

逻辑运算符	说明
NOT 或者！	逻辑非
AND 或者 &&	逻辑与
OR 或者 ||	逻辑或
XOR	逻辑异或

• 位运算符（了解即可）

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

位运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

3、运算符操作

（1）算数运算符

select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;
-- 将每件商品的价格加10
select name,price + 10 as new_price from product;
-- 将所有商品的价格上调10%
select pname,price * 1.1 as new_price from product;

（2）条件查询

-- 查询商品名称为“海尔洗衣机”的商品所有信息：
SELECT * FROM product WHERE pname='海尔洗衣机';
-- 查询价格为800商品
SELECT * FROM product WHERE price=800;
-- 查询价格不是800的所有商品
SELECT * FROM product WHERE price!=800;
SELECT * FROM product WHERE price<>800;
SELECT * FROM product WHERE NOT(price=800);
-- 查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price>=60;
-- 查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
SELECT * FROM product WHERE price >= 200 AND price <= 1000;
SELECT * FROM product WHERE price >= 200 && price <= 1000;
-- 查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price IN(200,800);
SELECT * FROM product WHERE price=200 OR price=800;
SELECT * FROM product WHERE price=200 || price=800;
-- 查询含有‘裤'字的所有商品
SELECT * FROM product WHERE pname LIKE '%裤%'; -- %用来匹配任意字符
-- 查询以'海'开头的所有商品
SELECT * FROM product WHERE pname LIKE '海%';
-- 查询第二个字为'蔻'的所有商品
SELECT * FROM product WHERE pname LIKE '_蔻%'; -- 下划线匹配单个字符
-- 查询category_id为null的商品
SELECT * FROM product WHERE category_id IS NULL;
SELECT * FROM product WHERE category_id = NULL; -- 错误示范：这个不能查询出数据
-- 查询category_id不为null分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;

关键字	用途	示例	是否通用
NOT	对任意布尔条件取反	NOT (age > 18)	✅
IS NOT	判断值是否不为某种状态（主要是 NULL/布尔值）	age IS NOT NULL	❌（只能用在特定场景）

• 求最大值、最小值函数

-- 使用least求最小值
SELECT LEAST(10,5,20);
SELECT LEAST(10,5,20) as small_number;
5
SELECT LEAST(10,NULL,20) as small_number;
NULL  -- 有个值为null，则不会进行比较，直接返回null

-- 使用greatest求最大值
SELECT GREATEST(10,20,30)
30
SELECT GREATEST(10,NULL,30)
NULL  -- 有个值为null，则不会进行比较，直接返回null

（3）位运算符（了解）

select 3&5; -- 位与
1  -- 结果
0011
0101
--------
0001

select 3|5; -- 位或
7  -- 结果
0011
0101
--------
0111

select 3^5; -- 位异或
6  -- 结果
0011
0101
--------
0110

select 3<<1; -- 位左移
6  -- 结果
0011  >> 0110

select 3>>1; -- 位右移
1  -- 结果
0011  >> 0001

select ~3; -- 位取反
18446744073709551612  -- 结果
00000000000011 ~ 11111111111100

4、排序查询(order by [asc | desc] )

select
字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……

-- 上面查询，优先排[字段名1]，[字段名1]相同情况下再排[字段名2]

1.asc升序(默认)，desc降序
2.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
3.order by子句，放在查询语句的最后面。LIMIT子句除外

-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc, category_id desc;
-- 3.只显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;

5、聚合查询

（1）聚合查询操作

聚合函数	作用
count()	统计指定列不为 NULL 的记录行数；
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0

-- 1 查询商品的总条数
select count(*) from product;
select count(pid) from product;
-- 2 查询价格大于200商品的总条数
SELECT count(*) FROM product WHERE price > 200;
-- 3 查询分类为'c001'的所有商品的价格总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
-- 4 查询商品的最大价格
SELECT MAX(price) FROM product;
-- 5 查询商品的最小价格
SELECT MIN(price) FROM product;
-- 6 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';

（2）NULL值的处理

• 1、count函数，对null值的处理
  如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

• 2、sum和avg函数，对null值的处理
  这两个函数忽略null值的存在，就好像该条记录不存在一样。

• 3、max和min函数，对null值的处理
  max和min两个函数同样忽略null值的存在。

6、分组查询(group by)

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

-- 1 统计各个分类商品的个数
select category_id,count(*) from product group by category_id;
-- 如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：

（1）分组之后的条件筛选(having)

• 分组之后对统计结果进行筛选的话必须使用having，不能使用where
• where子句用来筛选 FROM 子句中指定的操作所产生的行
• group by 子句用来分组 WHERE 子句的输出。
• having 子句用来从分组的结果中筛选行

select 字段1,字段2… 
from 表名 
where <条件表达式>
group by 分组字段 
having 分组筛选条件;

-- 2.统计各个分类商品的个数,且只显示个数大于4的信息，并且按个数升序排序
select category_id,count(*) 
from product 
group by category_id 
having count(*) > 4
order by count(*);
-- SQL执行顺序: from -> group by -> count(pid) -> select -> having -> order by
--                        分组         统计       筛选列     表筛选      表排序

7、分页查询

-- 方式1-显示前n条
select 字段1，字段2... from 表明 limit n
-- 方式2-分页显示
select 字段1，字段2... from 表明 limit m,n
m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
n: 整数，表示查询多少条数据

-- 查询product表的前5条记录
select * from product limit 5;
-- 从第4条开始显示，显示5条
select * from product limit 3,5;

select * from product limit 0,60;        -- 第1页，显示60条数据
select * from product limit 60,60;       -- 第2页
select * from product limit 120,60;      -- 第3页
select * from product limit (n-1)*60,60; -- 第n页

8、INSERT INTO SELECT语句

（1）将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。

-- 要求目标表Table2必须存在。
-- (field1,field2,…)和(value1,value2,…)字段要一致，类型也要一致。

insert into Table2(field1,field2,…) select value1,value2,… from Table1
-- 或者：
-- Table2和Table1的列必须要一致。
insert into Table2 select * from Table1

-- 例子：把聚合查询的表导入到新建的表
CREATE TABLE product3(
  category_id VARCHAR(20),
  product_count DOUBLE
);

INSERT INTO product3 SELECT category_id,count(*) FROM product GROUP BY category_id;

（2）将一张表的数据导入到另一张表中，方法二：SELECT INTO。

-- 要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
select vale1, value2 into Table2 from Table1

9、DQL 正则表达式

1、介绍

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

2、格式

模式	描述
^	匹配输入字符串的开始位置。
$	匹配输入字符串的结束位置。
.	匹配除 "\n" 之外的任何单个字符。
[...]	字符集合。匹配所包含的任意一个字符。例如，[abc] 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如，[^abc] 可以匹配 "plain" 中的 'p'。即：除了abc都可以匹配。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f) ood' 则匹配 "zood" 或 "food"。

模式	描述
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于 {0,}。
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
	n 是一个非负整数。匹配确定的 n 次。例如，'o {2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
	m 和 n 均为非负整数，其中 n <= m。最少匹配 n 次且最多匹配 m 次。

3、使用方法

-- ^ 在字符串开始处进行匹配（匹配开头是否为a）
SELECT 'abc' REGEXP '^a';  -- 1 ，即为真值，匹配成功
select * from product where pname REGEXP '^海';  -- 匹配首字“海”：“海尔冰箱”

-- $ 在字符串末尾开始匹配（匹配结尾是否为a）
SELECT 'abc' REGEXP 'a$';  -- 0
SELECT 'abc' REGEXP 'c$';  -- 1
select * from product where pname REGEXP '水$';  -- 匹配：“香奈儿神仙水”

-- . 匹配任意单个字符，可以匹配除了换行符之外的任意字符
SELECT 'abc' REGEXP '.a';  -- 0 , 【a前面没字符，false】
SELECT 'abc' REGEXP '.b';  -- 1
SELECT 'abc' REGEXP '.c';  -- 1
SELECT 'abc' REGEXP 'a.';  -- 1
SELECT 'abc' REGEXP 'a...';  -- 0 , 【a后面没有3个字符，false】

-- [...] 匹配括号内的任意单个字符
SELECT 'abc' REGEXP '[xyz]';  -- 0 , 【没有单个字符能匹配x|y|z】
SELECT 'abc' REGEXP '[xaz]'; -- 1 , 【a匹配上括号内的字符】

-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT 'a' REGEXP '[^abc]';  -- 0 , 【a匹配不上括号取反】
SELECT 'x' REGEXP '[^abc]';  -- 1
SELECT 'abc' REGEXP '[^a]';  -- 1 , 【b、c都能匹配上】

-- a* (0,无穷) 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.ta*b';  -- 1 , 【a可以出现0次或多次】
SELECT 'stab' REGEXP '.(ta)*b';  -- 1 , 【ta可以出现0次或多次】
SELECT 'stb' REGEXP '.ta*b';  -- 1 , 【a可以出现0次或多次】
SELECT '' REGEXP 'a*';  -- 1 , 【a可以出现0次或多次】

-- a+ (1,无穷) 匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';  -- 1 , 【a可以出现1次或多次】
SELECT 'stb' REGEXP '.ta+b';  -- 0 , 【a可以出现1次或多次】

-- a? (0,1) 匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';  -- 1 , 【a可以出现0次或1次】
SELECT 'stab' REGEXP '.ta?b';  -- 1 , 【a可以出现0次或1次】
SELECT 'staab' REGEXP '.ta?b';  -- 0 , 【a可以出现0次或1次】

-- a1|a2 匹配a1或者a2，
SELECT 'a' REGEXP 'a|b';  -- 1 ，【匹配a或b】
SELECT 'b' REGEXP 'a|b';  -- 1 ，【匹配a或b】
SELECT 'b' REGEXP '^(a|b)';  -- 1 ，【匹配开头a或b】
SELECT 'a' REGEXP '^(a|b)';  -- 1 ，【匹配开头a或b】
SELECT 'c' REGEXP '^(a|b)';  -- 0 ，【匹配开头a或b】

-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c';  -- 1 ，【匹配4个u】
SELECT 'auuuuc' REGEXP 'au{3}c';  -- 0 ，【匹配3个u，c排在第4个没匹配上】

-- a{m,} 匹配m个或者更多个a
SELECT 'auuuuc' REGEXP 'au{3,}c';  -- 1 ，【匹配3到无穷个u】

-- a{m,n} 匹配m到n个a,包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c';  -- 1 ，【匹配3~5个u】
SELECT 'auuuuc' REGEXP 'au{4,5}c';  -- 1 ，【匹配4~5个u】
SELECT 'auuuuc' REGEXP 'au{5,10}c';  -- 0 ，【匹配5~10个u】

-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y';  -- 1 ，【匹配1个(abab)】
SELECT 'xababy' REGEXP 'x(ab)*y';  -- 1 ，【匹配0~无穷个(ab)】
SELECT 'xababy' REGEXP 'x(ab){1,2}y';  -- 1 ，【匹配1~2个(ab)】

五、多表操作

1、多表关系

a. 一对一关系

-- 没意义，直接合并列就行

b. 一对多关系

-- 在多的地方建立外键，指向一的一方的主键

deptno	name
1001	研发部
1002	销售部
1003	财务部

eid	name	age	dept id
1	乔峰	20	1001
2	段誉	21	1001
3	虚竹	23	1001
4	阿紫	18	1002
5	扫地僧	35	1002

c. 多对多关系

-- 每个人都可以选多门课，每门课都可以被多人选
-- 原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

学生表（主表）

sid	name	age
1	小龙女	18
2	阿紫	19
3	周芷若	20

科目表（主表）

cid	cname
1	语文
2	数学
3	英语

中间表（从表），两个外键分别指向2张表的主键

（可以在这个表新增一些功能列，例如：分数）

sid（外键列）	cid（外键列）	score（普通列）
1	1	75
1	2	83
2	1	67
2	3	80
3	2	90
3	3	88

2、外键约束（一对多）

a. 创建外键约束（创建表）

# 首选创建一个主表
create table dept(
  deptno varchar(20) primary key, -- 部门号
  name varchar(20) -- 部门名
);

# 然后创建一个从表，并设置上外键约束
CREATE TABLE emp(
  eid VARCHAR(20) PRIMARY KEY,
  ename VARCHAR(20),
  age int,
  dept_id VARCHAR(20),
  [CONSTRAINT emp_fk] FOREIGN KEY(dept_id) REFERENCES dept(deptno) -- 外键约束
);

[CONSTRAINT emp_fk] # 给外键命名（不可重复），可忽略，系统自动命名

b. 创建外键约束（修改表）

alter table <数据表名> add constraint <外键名> foreign key(<从表列名>) references
<主表名> (<列名>);
-- 添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

# 重新创建一个从表
CREATE TABLE emp2(
  eid VARCHAR(20) PRIMARY KEY,
  ename VARCHAR(20),
  age int,
  dept_id VARCHAR(20)
);

# 在从表上创建一个外键约束
ALTER TABLE emp2 add [constraint emp2_fk] foreign key(dept_id) references dept(deptno)

[CONSTRAINT emp_fk] # 给外键命名（不可重复），可忽略，系统自动命名

c. 数据插入

-- 1、添加主表数据
-- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');

-- 2、添加从表数据
-- 注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003');
insert into emp values('8','天山童姥',60, '1005'); -- 不可以

d. 数据删除

-- 3、删除数据，注意：
-- 1：主表的数据被从表依赖时，不能删除，否则可以删除
delete from dept where deptno = '1001'; -- 不可以删除
delete from dept where deptno = '1004'; -- 可以删除，从表没依赖主表的这行数据

-- 2: 从表的数据可以随便删除
delete from emp where eid = '7'; -- 可以删除，从表上数据可任意删除
delete from emp; -- 可以删除，从表上数据可任意删除

e.删除外键约束

-- 当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。
alter table <表名> drop foreign key <外键约束名>;

alter table emp2 drop foreign key emp2_fk;

-- 查外键约束名，可以右键表《设计表》或者右键数据库《你逆向数据库到模型》查看

3、外键约束（多对多）

-- 学生表和课程表(多对多)
-- 1 创建学生表student(左侧主表)
create table student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);

-- 2 创建课程表course(右侧主表)
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);

-- 3 创建中间表student_course/score(从表)
create table score(
sid int,
cid int,
score double
);

-- 4 建立外键约束(2次)
ALTER TABLE score add foreign key(sid) references student(sid);
ALTER TABLE score add foreign key(cid) references course(cid);-- 已忽略给外键命名

-- 5 给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');

-- 6 给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');

-- 7 给中间表添加数据
insert into score values(1,1,78),(1,2,88),(2,1,75),(2,3,60),(3,2,53),(3,3,93);
insert into score values(4,3,100);  -- 不可添加，学生表（主表）没有sid=4的学生

-- 删除数据，注意：
-- 1、中间从表数据可以随意删除、修改。
-- 2、两边主表受从表依赖的数据不能删除、修改。

六、多表联合查询

• 注意，外键约束对多表查询并无影响。

1、交叉连接查询

-- 简单解释：两张表相乘
select * from 表1,表2,表3….;
• 交叉连接查询返回被连接的两个表所有数据行的"笛卡尔积"。
• 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。
• 假如A表有m行数据，B表有n行数据，则返回"m*n行数据"。
• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选。
-- 这个只是过渡表，因为外键列已经合并在一张表了，后面可以通过外键列匹配来筛选有效数据。

2、内连接查询

-- 求多张表的交集
隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
显示内连接（SQL99标准）：select * from A join B on 条件; -- 或者（SQL99标准）
"inner 单词可省略不写"

-- 查询每个部门的所属员工（通过外键约束匹配，筛选掉无效的数据）
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id; -- 或者

-- 查询每个部门的员工数,并升序排序
select dept3.name,count(*) 
from dept3, emp3 
where dept3.deptno = emp3.dept_id 
group by dept3.name 
order by count(*);

-- 查询人数大于等于3的部门，并按照人数降序排序
select dept3.name,count(*) 
from dept3, emp3 
where dept3.deptno = emp3.dept_id 
group by dept3.name 
having count(*) >= 3 
order by count(*) desc;

3、外连接查询

-- 外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。
"注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。"

-- 左外连接：left outer join
select * from A left outer join B on 条件;
-- 右外连接：right outer join
select * from A right outer join B on 条件;
-- 满外连接: full outer join （左外连接 ∪ 右外连接）
select * from A full outer join B on 条件;
"满外连接：（mysql不支持，oracle支持），只能用union并集来合并左外、右外连接"
"outer 单词可省略不写"

-- 查询哪些部门有员工，哪些部门没有员工
select * from dept3 left join emp3 on dept3.deptno = emp3.dept_id;
-- 查询哪些员工有对应的部门，哪些员工没有
select * from dept3 right join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left join emp3 on dept3.deptno = emp3.dept_id
union
select * from dept3 right join emp3 on dept3.deptno = emp3.dept_id;
"【union】合并后去重，【union all】合并不去重"
-- 如果有多张表要外连接，可以这么写
select * from A
  left join B on 条件1
  left join C on 条件2
  left join D on 条件3;

select * from A
  right join B on 条件1
  right join C on 条件2
  right join D on 条件3;

4、子查询

• 子查询：即，select嵌套查询

a. 单行单列

`单行单列：返回一个单值`
-- 题目：查询年研发部和销售部的员工信息，包含员工号、员工名字
-- 1、查询最大年龄:
select max(age) from emp3;
-- 2、用最大年龄来筛选列表:
select * from emp3 where age = (select max(age) from emp3);

b. 单行多列

"单行多列子查询 在实际工作中比较少见"
`返回一行数据中多个列的内容`
-- 部门表 dept(id, dept_name, manager_id)
-- 员工表 emp(id, name, email)
-- 查出销售部门的经理的姓名和邮箱
SELECT *
FROM emp
WHERE (emp.name, emp.email) = (
    SELECT emp.name, emp.email
    FROM emp,dept 
    WHERE emp.id = dept.manager_id
    AND dept.dept_name = 'Sales'
);

c. 多行单列

`返回多行记录之中同一列的内容，相当于给出了一个操作范围；`
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
-- 1、查询研发部、销售部的id:
SELECT deptno FROM dept3 where name in ('研发部', '销售部')
-- 2、用部门id筛选出对应员工:
select * from emp3 where dept_id in (SELECT deptno FROM dept3 where name in ('研发部', '销售部'));

-- 其他方法（内连接查询）：【推荐使用关联查询，效率更高】
select * from dept3 a,emp3 b where a.deptno=b.dept_id and a.name in ('研发部', '销售部');

d. 多行多列

`查询返回的结果是一张临时表`
select *
from 
	(select*from dept3 where name='研发部') t1,
	(select*from emp3 where age< 30) t2 -- 派生表(子查询作为FROM来源)必须有别名
where t1.deptno=t2.dept_id;

-- 其他方法（内连接查询）：【推荐使用关联查询，效率更高】
select * from emp3,dept3 where dept_id=deptno and (age < 30 and name = '研发部');  -- 没有括号都可以

5、子查询关键字

a. all 关键字

select …from …where c > all(查询语句)
-- 等价于：
select ...from ... where c > result1 and c > result2 and c > result3
"ALL可以与=、>、>=、<、<=、<>结合是来使用"

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(SELECT age FROM emp3 WHERE dept_id='1003');
-- 查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all(select deptno from dept3);

b. any 关键字（或some）

select …from …where c > any(查询语句)
-- 等价于：
select ...from ... where c > result1 or c > result2 or c > result3
"ALL可以与=、>、>=、<、<=、<>结合是来使用"

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');

c. in 关键字

select …from …where c in(查询语句)
-- 等价于：
select ...from ... where c = result1 or c = result2 or c = result3
"[not in], 取反"

-- 查询研发部和销售部的id
SELECT deptno FROM dept3 where name in('研发部', '销售部');

d. exists 关键字

select …from …where exists(查询语句)
• 该子查询如果“有数据结果”(至少返回一行数据),则该 EXISTS() 的结果为“true”，外层查询执行
• 该子查询如果“没有数据结果”(没有任何数据返回),则该 EXISTS()的结果为“false”，外层查询不执行
• EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立
• 注意: EXISTS关键字，比 IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用 EXISTS关键字

-- 测试（外层每行查询，exists有结果，则执行）
select * from emp3 where exists(select 1);

-- 查询公司是否有大于60岁的员工，有则输出
select * from emp3 a where exists(select * from emp3 where a.age > 60);

-- 查询有所属部门的员工信息（排除没有部门的员工）
select * from emp3 a where exists(select * from dept3 b where a.dept_id=b.deptno);
select * from emp3 where dept_id in(select deptno from dept3); -- 或者in等价
"exists比in效率更高"

6、自关联表（创建、查询）

-- 一张表自己和自己关联，一张表当成多张表来用。
"注意自关联时表必须给表起别名"
select 字段列表 from 表1 a , 表1 b where 条件;
-- 或者
select 字段列表 from 表1 a [left|right] join 表1 b on 条件;

-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,  -- 外键列（manager_id表示上级领导，要填已经存在的领导id）
foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);

-- 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);  -- manager_id体现了谁是谁的上级

-- 1.查询每个三国人物及他的上级信息，如: 关羽对应的上级是刘备，制作一个对应表
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
select * from t_sanguo a join t_sanguo b on a.manager_id = b.eid;

-- 只显示人名列
select a.ename,b.ename from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

-- 2、董事长“刘协”虚空上级的数据行保留
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;

-- 3、查询所有人物、上级、上上级，制作对应表：
select
	a.ename,b.ename,c.ename
from
	t_sanguo a
    left join t_sanguo b on a.manager_id = b.eid
    left join t_sanguo c on b.manager_id = c.eid;

七、函数

1、聚合函数

在MySQL中，聚合函数主要由: count,sum,min,max,avg,这些聚合函数我们之前都学过，不再重复。这里我们学习另外一个函数:group_concat()，该函数用户实现行的合并。

group_concat()函数，首先根据 group by 指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。
-- 可以理解为[group by]分组后，每组的数据列，合并成一行显示，并用[分隔符]分隔

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

（1）使用 distinct 可以排除重复值；
（2）如果需要对结果中的值进行排序，可以使用 order by 子句；
（3） separator 是一个字符串值，默认为[,]。

-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp;
-- 指定分隔符合并
select department,group_concat(emp_name separator ';') from emp group by department;
-- 指定排序方式和分隔符
select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;

2、数学函数

-- 返回最大值
select max(price) as largestprice from products;
-- 返回最小值
select min(price) as minprice from products;

-- 绝对值
select abs(-10); -- 10
select abs(money) from emp;
-- 向上取整
select ceil(1.1); -- 2
select ceil(1.0); -- 1
-- 向下取整
select floor(1.9); -- 1
select floor(1.0); -- 1
-- 取列表最大值
select greatest(1,2,3) -- 3
select greatest("Google", "Runoob", "Apple"); -- Runoob（第一个字母R最大）
-- 取列表最小值
select least(1,2,3) -- 1
select least("Google", "Runoob", "Apple"); -- Apple（第一个字母A最小）

-- 取模
select mod(5,2); -- 1（余数）
-- 圆周率
select pi(); -- 3.141593
-- 取x的y次方
select power(2,3); -- 8
select pow(2,3); -- 8

-- 取随机数
select rand(); -- 0~1的浮点数
select floor(rand() * 100); -- 0~100的整数
-- 取小数的四舍五入取整
select round(3.5415); -- 4
-- 取小数的四舍五入，保留3为小数
select round(3.5415 , 3); -- 3.542
-- 将小数直接截取
select truncate(3.5415 , 3); -- 3.541

3、字符串函数

-- 1、获取字符串个数
select char_length('hello'); -- 5  等价于 character_length('hello')
select char_length('你好吗'); -- 3
-- length取长度，返回的单位是字节
select length('hello'); -- 5
select length('你好吗'); -- 9

-- 2、字符串合并
select concat(s1,s2...sn);
select concat('hello','world'); -- helloworld
select concat(c1,c2) from emp; -- 将两列合并
-- 字符串合并，指定分隔符
select concat_ws(x, s1,s2...sn);
select concat_ws('-','hello','world'); -- hello-world

-- 3、返回字符串c在列表值中的位置
select field(s,s1,s2...);
select field("c", "a", "b", "c", "d", "e"); -- 3
              s    s1   s2   s3   s4   s5

-- 4、去除字符串左边空格
select ltrim('  aaa'); -- aaa
select rtrim('aaa  '); -- aaa（去除字符串右边空格）
select trim('   aaa  '); -- aaa（去除字符串两端空格）
-- 5、字符串截取
select mid('helloworld',2,3) -- ell（从第二个字符开始截取，截取长度为3）
-- 6、获取字符串A在字符串中出现的位置
select position('abc' in 'habcelloabcworld'); -- 2
-- 7、字符串替换
select replace('aaahelloaaaworld','aaa','bbb'); -- bbbhellobbbworld
-- 8、字符串翻转
select reverse('abc') -- cba
-- 9、返回字符串的后几个字符
select right('hello',3); -- llo（返回后3个字符）
select left('hello',3); -- hel（返回前3个字符）
-- 10、字符串比较
select strcmp(s1,s2); -- （如果 s1>s2 返回 1，如果 s1<s2 返回 -1，相等返回 0）
select strcmp('hello','world'); -- -1（先从第一个字符比较，h比w小，判断-1）
-- 11、字符串截取
select substr('hello',2,3); -- ell（从第二个字符开始截取，截取长度为3）
select substring('hello',2,3); -- ell（等价于substr）
-- 12、小写转大写（推荐使用upper）
select upper('HelloWorld'); -- HELLOWORLD
select ucase('HelloWorld'); -- HELLOWORLD
-- 13、大写转小写（推荐使用lower）
select lower('HelloWorld'); -- helloworld
select lcase('HelloWorld'); -- helloworld

4、日期函数

更详细请看PDF

-- 1、获取时间戳（毫秒值）
select unix_timestamp(); -- 1759567303
-- 2、将一个日期字符串转为毫秒值
select unix_timestamp('2021-12-21 08:08:08'); -- 1640074088
-- 3、将时间戳毫秒值转为指定格式的日期
select from_unixtime(1640045288,'%Y-%m-%d %H:%i:%s'); -- 2021-12-21 00:08:08
-- 4、获取当前的年月日
select current_date(); -- 2025-10-04
select curdate(); -- 2025-10-04
-- 5、获取当前的时分秒
select current_time(); -- 08:42:16
select curtime(); -- 08:42:16
-- 6、获取年月日 和 时分秒
select current_timestamp(); -- 2025-10-04 08:43:22
-- 7、从日期字符串中获取年月日
select date('2025-10-04 08:43:22'); -- 2025-10-04
-- 8、获取日期之间的差值
select datediff('2025-10-04','2025-10-01'); -- 3
select datediff(curdate(),'2025-10-01'); -- 3（获取当前日期是2025-10-04）
-- 9、获取时间的差值（秒级）
select timediff('12:45:55','08:43:22'); -- 04:02:33
-- 10、日期格式化
select date_format('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s'); -- 2021-01-01 01:01:01
-- 11、将字符串转为日期
select str_to_date('2021-1-1','%Y-%m-%d'); -- 2021-01-01
-- 12、将日期进行减法（日期向前跳转）
select date_sub('2021-10-01',interval 2 day); -- 2021-09-29
-- 13、将日期进行加法（日期向后跳转）
select date_add('2021-10-01',interval 2 month); -- 2021-12-01
'可用单位：year、month、week、day、hour、minute、second、microsecond 等等'
-- 14、从日期中获取年月日，时分秒
select extract(hour from '2025-10-04 08:43:22'); -- 8
select extract(second from '2025-10-04 08:43:22'); -- 22
'可用单位：year、month、week、day、hour、minute、second、microsecond 等等'
-- 15、获取指定日期的该月最后一天
select last_day('2021-08-13'); -- 2021-08-31
-- 16、获取指定年份和天数的日期（第几年的第几天，对应日期）
select makedate('2021',53); -- 2021-02-22
-- 17、从日期获取年月日，时分秒
select hour('2025-10-04 08:43:22'); -- 8
select year('2025-10-04 08:43:22'); -- 2025
select quarter('2025-10-04 08:43:22'); -- 4（第4季度）
-- 18、根据日期获取信息
select monthname('2025-10-04 08:43:22'); -- October
select dayname('2025-10-04 08:43:22'); -- Saturday
select dayofmonth('2025-10-04 08:43:22'); -- 4（这个月的第几天）
select dayofweek('2025-10-04 08:43:22'); -- 7（1星期日，2星期一，以此类推）
select dayofyear('2025-10-04 08:43:22'); -- 277（这年的第几天）

select week('2025-10-04 08:43:22'); -- 39（这年的第几周）
select weekday('2025-10-04 08:43:22'); -- 5（0星期一，1星期二，以此类推）
select yearweek('2025-10-04 08:43:22'); -- 202539（2025年第39周）

select now(); -- 2025-10-09 02:17:51（获取当前日期时间）

5、控制流函数

a. if 逻辑判断

-- if逻辑判断
-- if
select if(expr,v1,v2); -- （如果expr成立，返回v1，否则返回v2）
select if(5>3,"大于","小于"); -- 大于
select name,if(score >= 85,'优秀','及格') from student; -- 实例
-- ifnull
IFNULL(v1,v2); -- （如果v1的值不为 NULL，则返回v1，否则返回v2）
select ifnull('aaa','bbb'); -- aaa 
select ifnull(null,'bbb'); -- bbb 
select name,ifnull(money,0) from emp; -- 判断员工奖金为空，补充0
-- isnull
select isnull(5); -- 0
select isnull(null); -- 1
-- nullif
select nullif(expr1, expr2); -- （相同返回null，不同返回expr1）
select nullif(12,12) -- null
select nullif(12,25) -- 12

b. case when 语句

case expression
  when condition1 then result1
  when condition2 then result2
  ...
  when conditionn then resultn
  else result
end

-- 示例
select 
  case 5
    when 1 then '你好'
    when 2 then 'hello'
    when 5 then '正确'
    else
      '其它'
  end as info

-- | info |
-- | ---- |
-- | 正确 |

-- 示例，case可以不写具体值，变成if判断语句
select 
  case 
    when 1>0 then '你好'
    when 2<1 then 'hello'
    when 5>2 then '正确'
    else
      '其它'
  end as info

-- | info |
-- | ---- |
-- | 你好 |

• 实例

use mydb4;
-- 创建订单表
create table orders(
oid int primary key, -- 订单id
price double, -- 订单价格
payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);

insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);

SELECT * FROM orders;

-- 方式1
select
  * ,
  case
    when payType=1 then '微信支付'
    when payType=2 then '支付宝支付'
    when payType=3 then '银行卡支付'
    else '其他支付方式'
  end as payTypeStr
from orders;

-- 方式2
select
  * ,
  case payType
    when 1 then '微信支付'
    when 2 then '支付宝支付'
    when 3 then '银行卡支付'
    else '其他支付方式'
  end as payTypeStr
from orders;

"oid"	"price"	"payType"	"payTypeStr"
"1"		"1200"		"1"		"微信支付"
"2"		"1000"		"2"		"支付宝支付"
"3"		"200"		"3"		"银行卡支付"
"4"		"3000"		"1"		"微信支付"
"5"		"1500"		"2"		"支付宝支付"

6、窗口函数（mysql 8.0）

window_function(expr) OVER (
    PARTITION BY ...
    ORDER BY ...
    frame_clause)

其中，'window_function' 是窗口函数的名称；'expr' 是参数，有些函数不需要参数；
-- OVER子句包含三个选项：
-- 1、分区（PARTITION BY）
PARTITION BY，用于将数据行拆分成多个分区（组），它的作用类似于 GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
-- 2、排序（ORDER BY）
ORDER BY，用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
-- 3、以及窗口大小（frame_clause）。
frame_clause，用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

-- 实例
use mydb4;
create table employee(
  dname varchar(20), -- 部门名
  eid varchar(20),
  ename varchar(20),
  hiredate date, -- 入职日期
  salary double -- 薪资
);

insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

a. 序号函数

-- 以dname部门作为分组，将每个部门员工工资进行排名
select 
  dname,
  ename,
  salary,
  row_number() over(partition by dname order by salary desc) as rn1,
  rank() over(partition by dname order by salary desc) as rn2,
  dense_rank() over(partition by dname order by salary desc) as rn3
from employee;

-- ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。
-- 以上是3个模式的排名方法

"dname"	"ename"		"salary"	"rn1"	"rn2"	"rn3"
"研发部"	"张飞"	"7000"		"1"		"1"		"1"
"研发部"	"赵云"	"7000"		"2"		"1"		"1"
"研发部"	"关羽"	"5000"		"3"		"3"		"2"
"研发部"	"马超"	"4000"		"4"		"4"		"3"
"研发部"	"黄忠"	"4000"		"5"		"4"		"3"
"研发部"	"刘备"	"3000"		"6"		"6"		"4"
"销售部"	"徐晃"	"9000"		"1"		"1"		"1"
"销售部"	"张辽"	"6000"		"2"		"2"		"2"
"销售部"	"曹洪"	"6000"		"3"		"2"		"2"
"销售部"	"典韦"	"5000"		"4"		"4"		"3"
"销售部"	"许褚"	"3000"		"5"		"5"		"4"
"销售部"	"曹操"	"2000"		"6"		"6"		"5"

-- 求出每个部门薪资排在前三名的员工，分组求TOPN
select * from (
  select 
    dname,
    ename,
    salary,
    dense_rank() over(partition by dname order by salary desc) as rn3
  from employee
) as t1 
where t1.rn3 <=3;

-- 对所有员工薪资进行全局排序（不分组），即去掉over内的partition by dname 
select 
  dname,
  ename,
  salary,
  dense_rank() over(order by salary desc) as rn3
from employee;

b. 开窗聚合函数

在窗口中每条记录动态地应用聚合函数【SUM()、AVG()、MAX()、MIN()、COUNT()】，可以动态计算在指定的窗口内的各种聚合函数值。

1）sum 累加

-- 按部门分组，员工入职日期进行排序，部门内员工的薪资做累加
select 
  dname,
  ename,
  salary,
  sum(salary) over(partition by dname order by hiredate) as rn1
from employee;

"dname"	"ename"	"salary"	"rn1"
"研发部"	"刘备"	"3000"	"3000"
"研发部"	"关羽"	"5000"	"8000"
"研发部"	"张飞"	"7000"	"15000"
"研发部"	"赵云"	"7000"	"22000"
"研发部"	"马超"	"4000"	"26000"
"研发部"	"黄忠"	"4000"	"30000"
"销售部"	"曹操"	"2000"	"2000"
"销售部"	"许褚"	"3000"	"5000"
"销售部"	"典韦"	"5000"	"10000"
"销售部"	"张辽"	"6000"	"16000"
"销售部"	"徐晃"	"9000"	"25000"
"销售部"	"曹洪"	"6000"	"31000"

-- 如果没有order by排序语句，默认把分组内的所有数据进行sum操作
select 
  dname,
  ename,
  salary,
  sum(salary) over(partition by dname) as rn1
from employee;

"dname"	"ename"	"salary"	"rn1"
"研发部"	"刘备"	"3000"	"30000"
"研发部"	"关羽"	"5000"	"30000"
"研发部"	"张飞"	"7000"	"30000"
"研发部"	"赵云"	"7000"	"30000"
"研发部"	"马超"	"4000"	"30000"
"研发部"	"黄忠"	"4000"	"30000"
"销售部"	"曹操"	"2000"	"31000"
"销售部"	"许褚"	"3000"	"31000"
"销售部"	"典韦"	"5000"	"31000"
"销售部"	"张辽"	"6000"	"31000"
"销售部"	"徐晃"	"9000"	"31000"
"销售部"	"曹洪"	"6000"	"31000"

2）rows between 区间统计

-- 区间统计
-- 1、从开头加到当前行（rows between unbounded preceding and current row）
select
  dname,
  ename,
  salary,
  sum(salary) over(partition by dname order by hiredate 
  rows between unbounded preceding and current row) as c1
from employee;

-- 2、前3行加到当前行（rows between 3 preceding and current row）
select
  dname,
  ename,
  salary,
  sum(salary) over(partition by dname order by hiredate
  rows between 3 preceding and current row) as c1
from employee;

"dname"	"ename"	"salary"	"c1"
"研发部"	"刘备"	"3000"	"3000"
"研发部"	"关羽"	"5000"	"8000"
"研发部"	"张飞"	"7000"	"15000"
"研发部"	"赵云"	"7000"	"22000"
"研发部"	"马超"	"4000"	"23000"
"研发部"	"黄忠"	"4000"	"22000"
"销售部"	"曹操"	"2000"	"2000"
"销售部"	"许褚"	"3000"	"5000"
"销售部"	"典韦"	"5000"	"10000"
"销售部"	"张辽"	"6000"	"16000"
"销售部"	"徐晃"	"9000"	"23000"
"销售部"	"曹洪"	"6000"	"26000"

-- 3、前3行加到当前后1行（rows between 3 preceding and 1 following）
select
  dname,
  ename,
  salary,
  sum(salary) over(partition by dname order by hiredate
  rows between 3 preceding and 1 following) as c1
from employee;

-- 4、当前行加到最后（rows between current row and unbounded following）
select
  dname,
  ename,
  salary,
  sum(salary) over(partition by dname order by hiredate
  rows between current row and unbounded following) as c1
from employee;

c. 分布函数

1）cume_dist

用途：'分组内小于、等于当前rank值的行数' 除以 '分组内总行数'
应用场景：查询小于等于当前薪资（salary）的比例

-- 列出员工薪资水平在全公司/部门内的比例
select
  dname,
  ename,
  salary,
  cume_dist() over(order by salary) as rn1,  -- 没有分组
  cume_dist() over(partition by dname order by salary) as rn2  -- 按部门分组
from employee;

-- 刘备3000元，≤3000元的有3人，总人数为12人，所以 3 / 12 = 0.25
-- 刘备3000元，组内≤3000元的有1人，组内人数为6人，所以 1 / 6 = 0.166667

"dname"	"ename"		"salary"	"rn1"					"rn2"
"研发部"	"刘备"	"3000"	"0.25              "	"0.16666666666666666"
"研发部"	"马超"	"4000"	"0.4166666666666667"	"0.5"
"研发部"	"黄忠"	"4000"	"0.4166666666666667"	"0.5"
"研发部"	"关羽"	"5000"	"0.5833333333333334"	"0.6666666666666666"
"研发部"	"张飞"	"7000"	"0.9166666666666666"	"1"
"研发部"	"赵云"	"7000"	"0.9166666666666666"	"1"
"销售部"	"曹操"	"2000"	"0.08333333333333333"	"0.16666666666666666"
"销售部"	"许褚"	"3000"	"0.25              "	"0.3333333333333333"
"销售部"	"典韦"	"5000"	"0.5833333333333334"	"0.5"
"销售部"	"张辽"	"6000"	"0.75              "	"0.8333333333333334"
"销售部"	"曹洪"	"6000"	"0.75              "	"0.8333333333333334"
"销售部"	"徐晃"	"9000"	"1                 "		"1"

2）percent_rank

用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
应用场景：'不常用'

select
  dname,
  ename,
  salary,
  rank() over(partition by dname order by salary desc) as rn,
  percent_rank() over(partition by dname order by salary desc) as rn2
from employee;

-- 张飞：薪资配名第1，组内人数6人，(1-1)/(6-1)=0
-- 关羽：薪资配名第3，组内人数6人，(3-1)/(6-1)=0.4

"dname"	"ename"	"salary"	"rn"	"rn2"
"研发部"	"张飞"	"7000"	"1"		"0"
"研发部"	"赵云"	"7000"	"1"		"0"
"研发部"	"关羽"	"5000"	"3"		"0.4"
"研发部"	"马超"	"4000"	"4"		"0.6"
"研发部"	"黄忠"	"4000"	"4"		"0.6"
"研发部"	"刘备"	"3000"	"6"		"1"
"销售部"	"徐晃"	"9000"	"1"		"0"
"销售部"	"张辽"	"6000"	"2"		"0.2"
"销售部"	"曹洪"	"6000"	"2"		"0.2"
"销售部"	"典韦"	"5000"	"4"		"0.6"
"销售部"	"许褚"	"3000"	"5"		"0.8"
"销售部"	"曹操"	"2000"	"6"		"1"

d. 前后函数

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
应用场景：查询前1名同学的成绩和当前同学成绩的差值

1）lag(expr,n) 滞后n行

-- lag(expr,n,默认值)
select
  dname,
  ename,
  hiredate,
  lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time, -- 入职日期前1行，默认值为'2000-01-01'
  lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time
  -- 入职日期前2行，默认值为null
from employee;
--                       空值填2000-01-01     空值填null
'dname'	'ename'	'hiredate'	'last_1_time'	'last_2_time'
研发部		刘备	2021-11-1	2000-01-01		null
研发部		关羽	2021-11-2	2021-11-01		null
研发部		张飞	2021-11-3	2021-11-02		2021-11-1
研发部		赵云	2021-11-4	2021-11-03		2021-11-2
研发部		马超	2021-11-5	2021-11-04		2021-11-3
研发部		黄忠	2021-11-6	2021-11-05		2021-11-4
销售部		曹操	2021-11-1	2000-01-01		null
销售部		许褚	2021-11-2	2021-11-01		null
销售部		典韦	2021-11-3	2021-11-02		2021-11-1
销售部		张辽	2021-11-4	2021-11-03		2021-11-2
销售部		徐晃	2021-11-5	2021-11-04		2021-11-3
销售部		曹洪	2021-11-6	2021-11-05		2021-11-4

' ename '	'hiredate'	'last_1_time'	'last_2_time'
   关羽   	2021-11-2	2021-11-01		null
-- 在hiredate的排序下，关羽的上一个员工的入职日期为2021-11-01，上两个没有员工，为空。

2）lead(expr,n) 领先n行

-- lead(expr,n,默认值)
select
  dname,
  ename,
  hiredate,
  lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time, -- 入职日期后1行，默认值为'2000-01-01'
  lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time
    -- 入职日期后2行，默认值为null
from employee;

'dname'	'ename'	'hiredate'	'last_1_time'	'last_2_time'
研发部		刘备	2021-11-1	2021-11-02		2021-11-3
研发部		关羽	2021-11-2	2021-11-03		2021-11-4
研发部		张飞	2021-11-3	2021-11-04		2021-11-5
研发部		赵云	2021-11-4	2021-11-05		2021-11-6
研发部		马超	2021-11-5	2021-11-06		null
研发部		黄忠	2021-11-6	2000-01-01		null
销售部		曹操	2021-11-1	2021-11-02		2021-11-3
销售部		许褚	2021-11-2	2021-11-03		2021-11-4
销售部		典韦	2021-11-3	2021-11-04		2021-11-5
销售部		张辽	2021-11-4	2021-11-05		2021-11-6
销售部		徐晃	2021-11-5	2021-11-06		null
销售部		曹洪	2021-11-6	2000-01-01		null

'dname'	'ename'	'hiredate'	'last_1_time'	'last_2_time'
研发部		马超	2021-11-5	2021-11-06		null
-- 在hiredate的排序下，关羽的下一个员工的入职日期为2021-11-06，下两个没有员工，为空。

3）first_value(expr) 头行

4）last_value(expr) 尾行

用途：返回第一个【FIRST_VALUE(expr)】或最后一个【LAST_VALUE(expr)】expr的值
应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

-- first_value(expr) -- 头函数
-- last_value(expr) -- 尾函数
-- 按照入职日期排序，查询第1个入职和最后1个入职员工的薪资
select
  dname,
  ename,
  hiredate,
  salary,
  first_value(salary) over(partition by dname order by hiredate) as first_salary, -- 查询第1个入职员工的薪资
  last_value(salary) over(partition by dname order by hiredate) as last_salary
   -- 查询最后1个入职员工的薪资
from employee;

'dname'	'ename'	'hiredate'	'salary'	'first_salary'	'last_salary'
研发部		刘备	2021-11-1	3000		3000		3000
研发部		关羽	2021-11-2	5000		3000		5000
研发部		张飞	2021-11-3	7000		3000		7000
研发部		赵云	2021-11-4	7000		3000		7000
研发部		马超	2021-11-5	4000		3000		4000
研发部		黄忠	2021-11-6	4000		3000		4000
销售部		曹操	2021-11-1	2000		2000		2000
销售部		许褚	2021-11-2	3000		2000		3000
销售部		典韦	2021-11-3	5000		2000		5000
销售部		张辽	2021-11-4	6000		2000		6000
销售部		徐晃	2021-11-5	9000		2000		9000
销售部		曹洪	2021-11-6	6000		2000		6000

-- 若删除【order by hiredate】，last_salary全部显示组内最后一个员工的薪资

5）nth_value(expr,n) 第n行

-- nth_value(expr,n)、ntile(n)
用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

-- 查询每个部门薪资排在第二和第三的员工信息
select
  dname,
  ename,
  hiredate,
  salary,
  nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
  nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee;

'dname'	'ename'	'hiredate'	'salary'	'second_score'	'third_score'
研发部		刘备	2021-11-1	3000		null		null
研发部		关羽	2021-11-2	5000		5000		null
研发部		张飞	2021-11-3	7000		5000		7000
研发部		赵云	2021-11-4	7000		5000		7000
研发部		马超	2021-11-5	4000		5000		7000
研发部		黄忠	2021-11-6	4000		5000		7000
销售部		曹操	2021-11-1	2000		null		null
销售部		许褚	2021-11-2	3000		3000		null
销售部		典韦	2021-11-3	5000		3000		5000
销售部		张辽	2021-11-4	6000		3000		5000
销售部		徐晃	2021-11-5	9000		3000		5000
销售部		曹洪	2021-11-6	6000		3000		5000

-- 列表中，【关羽】第二名，【张飞】第三名
-- 出现null，是因为还没遍历到第二行或第三行

5）ntile(n) 平均分n组

用途：将分区中的有序数据分为n个等级，记录等级数
应用场景：将每个部门员工按照入职日期分成4组

-- 将每个部门员工按照入职日期平均分成4组
select
  dname,
  ename,
  hiredate,
  salary,
  ntile(4) over(partition by dname order by hiredate) as second_score
from employee;

'dname'	'ename'	'hiredate'	'salary'	'second_score'
研发部		刘备	2021-11-1	3000		1
研发部		关羽	2021-11-2	5000		1
研发部		张飞	2021-11-3	7000		2
研发部		赵云	2021-11-4	7000		2
研发部		马超	2021-11-5	4000		3
研发部		黄忠	2021-11-6	4000		4
销售部		曹操	2021-11-1	2000		1
销售部		许褚	2021-11-2	3000		1
销售部		典韦	2021-11-3	5000		2
销售部		张辽	2021-11-4	6000		2
销售部		徐晃	2021-11-5	9000		3
销售部		曹洪	2021-11-6	6000		4

-- 遇到不均分，优先把余数分给前面的组

八、视图

视图（view）是一个虚拟表，数据库中只存放了视图的定义，而并没有存放视图中的数据。

【安全特性】如：社会保险基金表，可以用视图只显示姓名

0、语法

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
-- 参数说明：
（0）[or replace]：可选，若视图已存在，执行覆盖。
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement：表示⼀个完整的查询语句，将查询记录导⼊视图中。
（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。

1、视图的创建

-- 创建一个数据库
create database mydb6_view;
-- 导入数据表dept、emp
...
-- 创建视图
create view view1_emp 
as 
select ename,job from emp;

-- 查看表和视图
show tables;
show full tables; -- 显示表格类型列[Table_type]

'Tables_in_mydb6_view'	'Table_type'
dept                	BASE TABLE
emp                 	BASE TABLE
view1_emp           	VIEW

-- 使用视图
select * from view1_emp;

2、视图的修改

alter view 视图名 as select语句
create or replace view 视图名 as select语句 -- 或者用 create or replace 覆盖

-- 示例
alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where
a.deptno = b.deptno;

3、视图的更新（不推荐）

某些视图是可更新的。也就是说，可以在 UPDATE,DELETE,INSERT等语句中使用它们，以更新基表的内容。
-- 对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。
-- 如果视图包含下述结构中的任何一种，那么它就是不可更新的：
• 聚合函数 SUM(), MIN(), MAX(), COUNT()等
• DISTINCT
• GROUP BY
• HAVING
• UNION（并集去重） 或 UNION ALL （并集不去重）
• 位于选择列表中的子查询
• JOIN
• FROM 子句中的不可更新视图
• WHERE 子句中的子查询，引用 FROM 子句中的表。
• 仅引用文字值（在该情况下，没有要更新的基本表）

• 示例

-- -------创建视图-------
create or replace view view1_emp
as
select ename,job from emp;
-- -------更新视图-------
update view1_emp set ename = '周瑜' where ename = '鲁肃'; -- 可以修改
insert into view1_emp values('孙权','文员'); -- 不可以插入（基表的其它列不能为空值）

-- -------视图包含聚合函数不可更新-------
create or replace view view2_emp
as
select count(*) cnt from emp;

insert into view2_emp values(100); -- 不可以插入
update view2_emp set cnt = 100; -- 不可以更新

-- 总结：统计后的视图是不能更新进基表的，无意义。
"视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。"

4、视图的重命名、删除

-- 1、重命名
-- rename table 视图名 to 新视图名;
rename table view1_emp to my_view1

-- 2、删除视图
-- drop view 视图名[,视图名…];
drop view if exists view_student;

九、存储过程

0、介绍

-- 什么是存储过程（可理解为自定义函数）
• MySQL 5.0 版本开始支持存储过程。
• 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
• 存储过就是数据库 SQL 语言层面的代码封装与重用。

-- 有哪些特性
• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
• 函数的普遍特性：模块化，封装，代码复用；
• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

1、创建存储过程

-- 创建存储过程
delimiter $$自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名数据类形...)
begin
sql语句
[return;] -- 【少用】可以配合if then判断语句，用于提前结束存储过程
end $$自定义的结束符合
delimiter ; -- 将结束符号恢复到;

-- 查看当前有哪些存储过程
show procedure status where db = database(); -- database()自动输出当前所在数据库
show create procedure proc17_while_leave;
-- 删除存储过程
drop procedure [if exists] 储存名;

-- 示例
delimiter $$  -- 自定义结束符号为$$
create procedure proc01()
begin
  select empno,ename from emp;
end $$ -- 从$$到end $$，是存储过程的开始和结束
delimiter ;  -- 将结束符号从[$$]恢复到[;]

-- 调用存储过程
call proc01();

2、变量定义

a. 局部变量

-- 用户自定义，在begin/end块中有效
语法： declare var_name type [default var_value];
        声明    变量名   类型     声明默认值
举例： declare nickname varchar(32);

-- 示例1
delimiter $$
create procedure proc02()
begin
  declare var_name01 varchar(20) default ‘aaa’; -- 定义局部变量
  set var_name01 = ‘zhangsan’; -- 为局部变量赋值
  select var_name01; -- 查询该变量
end $$
delimiter ;

-- 调用存储过程
call proc02();

-- 示例2：还可以使用 SELECT..INTO 语句为变量赋值
delimiter $$
create procedure proc03()
begin
  declare new_name varchar(20) ;
  select ename into new_name from emp where empno=1001;
  select new_name;
end $$
delimiter ;

'注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列'
-- 调用存储过程
call proc03();

b. 用户变量

-- 用户自定义，当前会话（连接）有效。类比java的成员变量
语法： @var_name 
不需要提前声明，使用即声明

delimiter $$
create procedure proc04()
begin
  set @var_name01 = 'ZS';
end $$
delimiter ;

-- 调用存储过程
call proc04(); -- 执行【自定义用户变量】程序
select @var_name01 ; -- 外面也可以看到结果

c. 系统变量

• 系统变量又分为'全局变量'与'会话变量'。
• '全局变量'在MYSQL启动的时候由服务器自动将它们初始化为'默认值'，这些默认值可以通过更改my.ini这个文件来更改。
• '会话变量'在每次建立一个新的连接的时候，由MYSQL来初始化。'MYSQL会将当前所有全局变量的值复制一份，来做为会话变量。'
• 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
• 全局变量与会话变量的区别：对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
• 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

1）全局变量

-- 由系统提供，在整个数据库有效。
语法：
@@global.var_name

-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment; -- 查看自增长字段初始值
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000; -- 所有变量修改后重启都会失效

# 要想让它重启后仍然生效，需要写入配置文件。
# 例如编辑 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf：
[mysqld]
sort_buffer_size = 40000
# 然后重启mysql服务
sudo systemctl restart mysql

2）会话变量

-- 由系统提供，当前会话（连接）有效
语法：
@@session.var_name

-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000;

'重新连接mysql后，全局变量会重新覆盖会话变量'

3、参数传递

1）in 传入参数

in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in var01 int)
begin
  select * from emp where empno = var01;
end $$
delimiter ;

-- 调用存储过程，并传入参数
call dec_param01('1001');

-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2))
begin
  select * 
  from dept a, emp b 
  where a.dname = dname and b.sal > sal;
end $$
delimiter;

-- 调用存储过程，并传入参数
call dec_param0x('学工部',20000);

2）out 传出参数

-- ---------传出参数：out（类似return）------------------------
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int, out out_ename varchar(50) )
begin
  select ename into out_ename 
  from emp 
  where emp.empno = empno;
end $$
delimiter ;

-- 调用存储过程，并传入参数，传出参数
call proc08(1001, @o_ename); -- 给用户变量@o_ename赋值
select @o_ename;

-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal
decimal(7,2))
begin
  select ename,sal into out_ename,out_sal -- 分别传参
  from emp 
  where emp.empno = empno;
end $$
delimiter ;

-- 调用存储过程，并传入参数，传出参数
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

3）inout 传入传出

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以
修改变量的值（即使函数执行完）

-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(
    inout inout_ename varchar(50),
    inout inout_sal int
)
begin
  select concat(deptno,"_",inout_ename) into inout_ename -- 拼接：部门号_员工
  from emp 
  where ename = inout_ename;
  set inout_sal = inout_sal * 12; -- 求出年薪
end $$
delimiter ;

-- 设置用户变量
set @inout_ename = '关羽';
set @inout_sal = 3000;
-- 调用存储过程，并传入参数，函数自动修改并传出参数
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename; -- 30_关羽（关羽在id为30的部门）
select @inout_sal; -- 36000

4、控制流程

a. 分支语句 if

if [真] then [执行语句]
-- 语法
if search_condition_1 then statement_list_1
  [elseif search_condition_2 then statement_list_2] ...
  [else statement_list_n]
end if

-- 输入学生的成绩，来判断成绩的级别：
/*
score < 60 :不及格
score >= 60 , score <80 :及格
score >= 80 , score < 90 :良好
score >= 90 , score <= 100 :优秀
score > 100 :成绩错误
*/
-- 构建存储过程
delimiter $$
create procedure proc_12_if(in score int)
begin
  if score < 60 then
    select '不及格';
  elseif score < 80 then
    select '及格' ;
  elseif score >= 80 and score < 90 then
    select '良好';
  elseif score >= 90 and score <= 100 then
    select '优秀';
  else
    select '成绩错误';
  end if;
end $$
delimiter ;

-- 调用存储过程
call proc_12_if(120); -- 成绩错误

-- 输入员工的名字，判断工资的情况。
-- 构建存储过程
delimiter $$
create procedure proc12_if(in in_ename varchar(50))
begin
  declare result varchar(20);
  declare var_sal decimal(7,2);
  select sal into var_sal from emp where ename = in_ename;
  if var_sal < 10000 then
    set result = '试用薪资';
  elseif var_sal < 30000 then
    set result = '转正薪资';
  else
    set result = '元老薪资';
  end if;
  select result;
end$$
delimiter ;

-- 调用存储过程
call proc12_if('庞统');

b. 分支语句 case

-- 语法一（类比java的switch）：匹配
case case_value
	when when_value then statement_list
	[when when_value then statement_list] ...
	[else statement_list]
end case
-- 语法二：判断bool值
case
	when search_condition then statement_list
	[when search_condition then statement_list] ...
	[else statement_list]
end case

-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begin
  case pay_type
  when 1 then select '微信支付' ;
  when 2 then select '支付宝支付' ;
  when 3 then select '银行卡支付';
  else select '其他方式支付';
  end case ;
end $$
delimiter ;

-- 调用存储过程
call proc14_case(2); -- 支付宝支付
call proc14_case(4); -- 其他方式支付

-- 语法二
delimiter $$
create procedure proc_15_case(in score int)
begin
  case
  when score < 60 then select '不及格';
  when score < 80 then select '及格' ;
  when score >= 80 and score < 90 then select '良好';
  when score >= 90 and score <= 100 then select '优秀';
  else select '成绩错误';
  end case;
end $$
delimiter ;

-- 调用存储过程
call proc_15_case(88); -- 良好

c. 循环语句

leave -- 类似于 break，跳出，结束当前所在的循环
iterate -- 类似于 continue，继续，跳出本次循环，继续下一次

1) while 前判断循环

【标签:】while 循环条件 do -- 若循环条件为：true执行循环，false结束循环
循环体;
end while【标签】;  -- 【标签】标签名可以省略，多嵌套且用leave，iterate的时候才写

-- 示例
-- 创建测试表
create table user (
    uid int primary key auto_increment,
    username varchar(50),
    password varchar(50)
);

• 循环插入信息

• while

-- 创建存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
    declare i int default 1;
    while i<=insertcount do -- 若true执行循环，false停止循环
        insert into user(username,password) values(concat('user',i),'123456'); -- 拼接用户名：user1
        set i=i+1;
    end while;
end $$
delimiter ;

-- 调用存储过程
call proc16_while(10);
SELECT * FROM user; -- 有10条数据显示

• while + leave 结束循环

-- 创建存储过程-while + leave跳出，结束当前循环
truncate table user; -- 先清空数据表

delimiter $$
create procedure proc17_while_leave(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do -- 若true执行循环，false停止循环
        insert into user(username,password) values(concat('user',i),'123456'); -- 拼接用户名：user1
        if i=5 then 
        	leave label; -- 跳出标签label循环，如果没有嵌套循环，直接leave就可以了
        end if;
        set i=i+1;
    end while label;
end $$
delimiter ;

call proc17_while_leave(10);
SELECT * FROM user; -- 有5条数据显示

• while + iterate 跳过本层

-- 创建存储过程-while + iterate跳过本次循环，进入下一次循环
truncate table user; -- 先清空数据表

delimiter $$
create procedure proc18_while_iterate(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do -- 若true执行循环，false停止循环
        set i=i+1;
        if i=5 then 
        	iterate label; -- 跳出本次循环，直接进行下一次循环
        end if;
        insert into user(username,password) values(concat('user',i),'123456'); -- 拼接用户名：user1
    end while label;
end $$
delimiter ;

call proc18_while_iterate(10);
SELECT * FROM user; -- 有9条数据显示到user11，缺失user1、user5的用户名

2) repeat 后判断循环

[标签:]repeat
    循环体;
    until 条件表达式  -- 若【条件表达式】为true，跳出循环
end repeat [标签];  -- 【标签】标签名可以省略，多嵌套且用leave，iterate的时候才写

-- 示例
delimiter $$
create procedure proc19_repeat(in insertcount int)
begin
    declare i int default 1;
    repeat
        insert into user(username,password) values(concat('user',i),'123456'); -- 拼接用户名：user1
        set i=i+1;
        until i=insertcount -- 若true，结束循环
        -- 【这里不能再加表达式，until是repeat循环的最后执行语句】
    end repeat;
end $$
delimiter ;

-- 调用存储过程
call proc19_repeat(10);
SELECT * FROM user; -- 有9条数据显示

3) loop 灵活判断（推荐）

[标签:] loop
    循环体;
    if 条件表达式 then
    	leave [标签];  -- 必须使用leave结束循环，不加等于无限循环
    end if;
end loop [标签];  -- 【标签】标签名可以省略，多嵌套且用leave，iterate的时候才写

-- 示例：存储过程-loop
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
  declare i int default 1;
  label:loop
    insert into user(username, password) values(concat('user',i),'123456');
    set i = i + 1;
    if i > insertCount then
      leave label; -- 必须使用leave停止循环（或者循环中出现报错，才会停止）
    end if;
  end loop label;
end $$
delimiter ;

-- 调用存储过程
call proc19_loop(10);
SELECT * FROM user; -- 有10条数据显示

5、游标 cursor

游标(cursor)是用来存储查询结果集的数据类型, 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、 OPEN、 FETCH 和 CLOSE。
格式：
-- 声明语法
declare cursor_name cursor for select_statement -- for 后面是select查询
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

• 示例

-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资，将查询结果添加游标
use mysql7_procedure;
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
    -- 定义局部变量
    declare var_empno varchar(50);
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    -- 声明游标
    declare my_cursor cursor for
        select empno , ename, sal
        from dept a ,emp b
        where a.deptno = b.deptno and a.dname = in_dname;
    -- 打开游标
    open my_cursor;
        -- 通过游标获取每1行数据，若有5行数据，会显示5行结果框，每个结果框只有1行数据
        label:loop
            fetch my_cursor into var_empno, var_ename, var_sal;
            select var_empno, var_ename, var_sal;
        end loop label; -- loop循环中，由于第6行没数据，报错异常退出
    -- 关闭游标
    close my_cursor;
end $$
delimiter ;

-- 调用存储过程
call proc20_cursor('销售部');

6、异常处理-handler 句柄

-- 声明一个异常处理行为
declare handler_action handler
    for condition_value [, condition_value] ... -- 【异常类型】
    statement  -- 【异常处理措施】

handler_action: {
    continue
    | exit
    | undo -- 【不考虑】
}

condition_value: {
    mysql_error_code -- 【MySQL专用的错误码】
    | SQLSTATE [VALUE] sqlstate_value -- 【SQL 标准定义的 SQLSTATE 状态码】
    | condition_name
    | sqlwarning
    | not found
    | sqlexception

use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(in in_dname varchar(50))
begin
    -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    -- 定义标记值
    declare flag int default 1;
    -- 声明游标
    declare my_cursor cursor for
    select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;
    -- 定义句柄：定义异常处理方式
    /*
    	文档：https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
    	1：异常处理完后程序该怎么执行
    		continue ：继续执行剩余代码
    		exit     ： 直接终止程序
    		undo     ：【代码已不支持使用】
    	2：触发条件
    		条件码：1329
    		条件名：sqlwarning | not found | sqlexception
    	3：异常出发后执行什么代码
    		设置flag的值 -> 0
    */
    -- 定义句柄：当数据未发现、或者出现条件码1329时，将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0; -- 条件名
    declare continue handler for 1329 set flag = 0; -- 或者：条件码
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 上面代码遍历到最后一行时没有数据，触发1329异常，并触发上面定义的句柄
        -- 判断标志位，如果flag的值为1，则执行，否则不执行
        if flag = 1 then
        	select var_empno, var_ename,var_sal;
        else
        	leave label; -- 离开loop循环
        end if;
    end loop label;
    -- 关闭游标
    close my_cursor;
end $$;
delimiter ;

-- 调用存储过程
call proc21_cursor_handler('销售部');

十、存储函数【少用】

0、介绍及语法

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

-- 存储函数与存储过程的区别
1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
2.存储函数只能有输入参数，而且不能带 in, 而存储过程可以有多个 in,out,inout 参数。
3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用 insert,update,delete,create 等语句；
4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
5.存储过程可以调用存储函数。但函数不能调用存储过程。
6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

-- 创建存储函数
create function func_name ([param_name type[,...]])
returns type
[characteristic ...]
begin
	routine_body
end;

-- 参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。

-- 查看当前有哪些存储函数
show function status where db = database(); -- database()自动输出当前所在数据库
show create function proc17_while_leave;
-- 删除存储过程
drop function [if exists] 储存名;

1、创建存储函数

a. 没有输入参数

use mydb9_function;
-- 允许创建函数权限信任【mysql8.0开始，需修改权限才可以创建函数】
set global log_bin_trust_function_creators=TRUE;

-- 创建存储函数-没有输入参数
delimiter $$
create function myfunc1_emp() returns int
begin
    declare cnt int default 0;
    select count(*) into cnt from emp;
    return cnt;
end $$
delimiter ;

-- 调用存储函数
select myfunc1_emp();

b. 有输入参数

-- 创建存储过程-有输入参数
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
    declare out_name varchar(50);
    select ename into out_name from emp where empno = in_empno;
    return out_name;
end $$
delimiter ;

-- 调用存储函数
select myfunc2_emp(1008);

十一、触发器【少用】

0、介绍

• 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。
• 在MySQL中，只有执行 insert,delete,update 操作时才能触发触发器的执行。
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
• 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

-- 触发器的特性
• 1、什么条件会触发：I、D、U (insert,delete,update)
• 2、什么时候触发：在增删改前或者后
• 3、触发频率：针对每一行执行
• 4、触发器定义在表上，附着在表上

1、语法

a. 创建触发器

-- 1、创建只有一个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;

-- 2、创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
	执行语句列表
end;

• 示例

-- 数据准备
create database if not exists mydb10_trigger;
use mydb10_trigger;
-- 用户表
create table user(
  uid int primary key ,
  username varchar(50) not null,
  password varchar(50) not null
);
-- 用户信息操作日志表
create table user_logs(
  id int primary key auto_increment,
  time timestamp,
  log_text varchar(255)
);

-- 需求1：当user表添加一行数据，则会自动在user_log添加日志记录

-- 如果触发器存在，则先删除
drop trigger if exists trigger_test1;

-- 创建触发器trigger_test1
create trigger trigger_test1 after insert
on user for each row  -- 触发时机：当添加user表数据时触发
insert into user_logs values(NULL,now(), '有新用户注册');

-- 添加数据，触发器自动执行并添加日志代码
insert into user values(1,'张三','123456');
insert into user values(2,'李四','123456'),(3,'王五','123456');
-- 若一次添加多个数据，触发器也会执行多次

-- 需求2：当user表数据被修改时，则会自动在user_log添加日志记录

-- 创建触发器trigger_test2
delimiter $$
create trigger trigger_test2 before update
on user for each row  -- 触发时机：当添加user表数据时触发
begin
	insert into user_logs values(NULL,now(), '有用户信息被修改');
end $$
delimiter ;

-- 修改数据，让触发器自动执行
update user set password = '888888' where uid =1;

b. 查看触发器

show triggers;

c. 删除触发器

-- drop trigger [if exists] trigger_name
drop trigger [if exists] trigger_test1;

2、NEW、OLD引用

触发器类型	触发器类型 NEW 和 OLD 的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

-- 使用方法：
　　NEW.columnName （columnName为相应数据表某一列名）
　　OLD.columnName （columnName为相应数据表某一列名）

• 示例

-- 创建触发器--insert
create trigger trigger_test3 after insert
on user for each row  -- 触发时机：当添加user表数据时触发
insert into user_logs values(
    NULL,
    now(), 
    concat('有新用户添加，信息为：',NEW.uid,NEW.username,NEW.password)
);

-- 添加数据，触发器自动执行并添加日志代码
insert into user values(4,'赵六','123456');

-- 创建触发器--update
create trigger trigger_test4 after update
on user for each row  -- 触发时机：当添加user表数据时触发
insert into user_logs values(
    NULL,
    now(), 
    concat_ws(',','有用户修改，旧信息为：',OLD.uid,OLD.username,OLD.password)
);

-- 添加数据，触发器自动执行并添加日志代码
update user set password = '999999' where uid = 3 or uid = 4;

3、注意事项

• 1.MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发。
• 2.尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。
• 3. 触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

十二、索引

索引类似一本书的目录，比如要查找’student’这个单词，可以先找到s开头的页然后向后查找，这个就类似索引。

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有
- Hash	索引
- B+Tree索引

0、索引的查删

a. 查看索引

-- 1、查看数据库所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’;
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';

-- 2、查看表中所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%’;
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5'
and a.table_name like '%student%';

-- 3、查看表中所有索引
-- show index from 表名;
show index from student;

b. 删除索引

drop index 索引名 on 表名
-- 或
alter table 表名 drop index 索引名

1、单列索引

-- 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;

a. 普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入"重复值"和"空值"，纯粹为了查询数据更快一点。

create database mydb5;
use mydb5;
-- 方式1-创建表的时候直接指定
create table student(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date,
    phone_num varchar(20),
    score double,
    index index_name(name) -- 给name列创建索引
);
-- 方式2-直接创建
-- create index indexname on tablename(columnname);
create index index_gender on student(gender);

-- 方式3-修改表结构(添加索引)
-- alter table tablename add index indexname(columnname)
alter table student add index index_age(age);

b. 唯一索引

索引列的值"必须唯一"，但"允许有空值"。如果是组合索引，则列值的组合必须唯一。

-- 方式1-创建表的时候直接指定
create table student2(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date,
    phone_num varchar(20),
    score double,
    unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 方式2-直接创建
-- create unique index 索引名 on 表名(列名)
create unique index index_card_id on student2(card_id);
-- 方式3-修改表结构(添加索引)
-- alter table 表名 add unique [索引名] (列名)
alter table student2 add unique index_phone_num(phone_num)

c. 主键索引

每张表一般都会有自己的主键，当我们在创建表时，"MySQL会自动在主键列上建立一个索引"，这就是主键索引。主键是具有"唯一"性并且"不允许为NULL"，所以他是一种特殊的唯一索引。

2、组合索引

组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段。
复合索引的使用符合最左原则。

-- 创建索引的基本语法
create index indexname on table_name(column1(length),column2(length));

-- 组合索引
use mydb5;
-- 创建-- 普通索引
create index index_phone_name on student(phone_num,name);
-- 操作-删除索引
drop index index_phone_name on student;
-- 创建-- 唯一索引
create unique index index_phone_name on student(phone_num,name);

• 注意（使用要符合最左原则）

select * from student where name = '张三';
select * from student where phone_num = '15100046637';
select * from student where phone_num = '15100046637' and name = '张三';
select * from student where name = '张三' and phone_num = '15100046637';
/*
三条sql只有2、3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段才能够进行匹配。
而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。
*/

3、全文索引

a. 介绍

• 全文索引的关键字是 fulltext
• 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的 where 语句的参数匹配。
-- 用 "like + %" 就可以实现模糊匹配了，为什么还要全文索引？
"like + %"在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。
全文索引在大量的数据面前，能比"like + %"快N倍，速度不是一个数量级，但是全文索引可能存在精度问题。

• 版本支持

-- 全文索引的版本、存储引擎、数据类型的支持情况：
• MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
• MySQL 5.6 及以后的版本，"MyISAM" 和 "InnoDB" 存储引擎均支持全文索引;
• 只有字段的数据类型为 "char、varchar、text" 及其系列才可以建全文索引；
-- 测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

-- 在数据量较大时候，建立全文索引两种方式：
1、现将数据放入一个没有全局索引的表中，然后再用 create index 创建 fulltext 索引。
2、先为一张表建立 fulltext ，然后再将数据写入。
"1比2的速度快很多"

• 词语长度限制

-- MySQL 中的全文索引，有两个变量：㊀最小搜索长度、㊁最大搜索长度。
若："词语长度" < 最小搜索长度 or 最大搜索长度 < "词语长度"
则："词语长度"不会被索引。

这两个变量可以使用以下命令查看:
show variables like '%ft%';

#	参数名称	默认值	最小值	最大值	作用
1	ft_min_word_len	4	1	3600	MyISAM 引擎表全文索引包含的最小词长度
2	ft_query_expansion_limit	20	0	1000	MyISAM 引擎表使用 with query expansion 进行全文搜索的最大匹配数
3	innodb_ft_min_token_size	3	0	16	InnoDB 引擎表全文索引包含的最小词长度
4	innodb_ft_max_token_size	84	10	84	InnoDB 引擎表全文索引包含的最大词长度

b. 创建

use mydb5;
-- 创建表的时候添加全文索引
create table t_article (
    id int primary key auto_increment ,
    title varchar(255),
    content varchar(1000),
    writing_date date -- ,
    -- fulltext (content) -- 创建全文检索【不建议在创建表时添加，效率低】
);

-- 导入数据
insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02');
insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');

-- 创建全文索引
-- 方式1：修改表结构添加全文索引
alter table t_article add fulltext index_content(content)
-- 方式2：直接添加全文索引
create fulltext index index_content on t_article(content);

c. 使用

• 全文索引有自己的语法格式，使用 match 和 against 关键字：
match (col1,col2,...) against(expr [search_modifier])

-- 示例
select * from t_article where match(content) against('yo'); -- 没有结果
-- 单词数需要大于等于3
select * from t_article where match(content) against('you'); -- 有结果
-- 使用传统like匹配检索
select * from t_article where like '%you%'; -- 效率极低

4、空间索引

• MYSQL中的空间数据类型有4种，分别是：GEOMETRY、POINT、LINESTRING、POLYGON。
• MYSQL使用 SPATIAL 关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。
• 创建空间索引的列，必须将其声明为 NOT NULL。

类型	含义	说明
Geometry	空间数据	任何一种空间类型
Point	点	坐标值
LineString	线	有一系列点连接而成
Polygon	多边形	由多条线组成

create table shop_info (
    id int primary key auto_increment comment 'id',
    shop_name varchar(64) not null comment '门店名称',
    geom_point geometry not null comment '经纬度', -- 创建《空间数据》类型
    spatial key geom_index(geom_point) -- spatial key 自定义索引名(字段名)
);

5、索引原理

【具体看数据结构】

• hash算法
• 二叉树
• B+Tree

十三、储存引擎

-- 查询当前数据库支持的存储引擎：
show engines;
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;
-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table student;
-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

十四、事务

1、概念

• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。"MySQL的核心就是存储引擎。"
• 用户可以根据不同的需求为数据表选择不同的存储引擎.
• 可以使用 SHOW ENGINES 命令 可以查看Mysql的所有执行引擎我们 可以到 默认的执行引
擎是"innoDB 支持事务"，行级锁定和外键。
-- 命令
SHOW ENGINES;
-- 显示
'Engine'		'Support'	  'Transactions'	'XA'	'Savepoints'
ndbcluster			NO	
MEMORY				YES				NO			NO			NO
InnoDB				DEFAULT			YES			YES			YES
PERFORMANCE_SCHEMA	 YES			NO			NO			NO
MyISAM				YES				NO			NO			NO
FEDERATED			NO	
ndbinfo				NO	
MRG_MYISAM			YES				NO			NO			NO
BLACKHOLE			YES				NO			NO			NO
CSV					YES				NO			NO			NO
ARCHIVE				YES				NO			NO			NO

• 在MySQL中的事务"Transaction"是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才
支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。

-- 在银行转账时，必须保证转账绝对安全，这时需要事务参与:
update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;
-- 这种情况需开启事务，将命令打包在一起。

2、操作

-- 1、开启事务：Start Transaction
• 任何一条DML语句(insert、update、delete)执行，标志事务的开启
• 命令： begin 或 start Transaction
-- 2、提交事务：Commit Transaction
• 成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
• 命令： commit
-- 3、回滚事务：Rollback Transaction
• 失败的结束，将所有的DML语句操作历史记录全部清空
• 命令： rollback

• 之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。
• 在MySQL中直接用 SET 来改变 MySQL 的自动提交模式:

set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交

-- 示例：
create database if not exists mydb12_transcation;
use mydb12_transcation;
-- 创建账户表
create table account(
    id int primary key, -- 账户id
    name varchar(20), -- 账户名
    money double -- 金额
);
-- 插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);

-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;
set autocommit = 0; -- 【全局】SET GLOBAL autocommit = 0;

-- 模拟账户转账
-- 开启事务
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 如果转账中的任何一条出现问题，则回滚事务（在commit之前操作）
rollback;
-- 提交事务（更新后的数据从内存写进硬盘）
commit;

3、隔离级别

-- 查看隔离级别
show variables like '%isolation%';
-- 设置隔离级别（临时会话：session）
set session transaction isolation level 级别字符串

-- 设置read uncommitted
set session transaction isolation level read uncommitted;
这种隔离级别会引起脏读，A事务读取到B事务没有提交的数据。
-- 设置read committed
set session transaction isolation level read committed;
这种隔离级别会引起不可重复读，A事务在没有提交事务之前，可看到数据不一致。
-- 设置repeatable read
set session transaction isolation level repeatable read;
这种隔离级别会引起幻读，A事务在提交之前和提交之后看到的数据不一致。
-- 设置serializable
set session transaction isolation level serializable;
这种隔离级别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作。

十五、锁机制

0、介绍

-- 从对数据操作的粒度分 ：
1） 表锁：操作时，会锁定整个表。
2） 行锁：操作时，会锁定当前操作行。
-- 从对数据操作的类型分：
1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

"存储引擎"	"表级锁"  	"行级锁"
MyISAM  	支持  	不支持
InnoDB  	支持  	支持
MEMORY  	支持  	不支持
BDB     	支持  	不支持

锁类型	特点
表级锁	偏向 MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁	偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

1、MyISAM 表锁

MyISAM 存储引擎只支持表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

加读锁： lock table table_name read;
加写锁： lock table table_name write;

2、InnoDB 行锁

对于 UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；

-- 行锁
共享锁（s）：select * from table_name where ... lock in share mode
排他锁（x) ：select * from table_name where ... for update

十六、日志

1、错误日志

该日志是默认开启的 ， 默认存放目录为 mysql 的数据目录, 默认的日志文件名为hostname.err（hostname是主机名）。
-- 查看日志位置指令 ：
show variables like 'log_error%';

cd /var/lib/mysql/ -- linux日志存放目录

2、二进制日志（增删改）

• 二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。
• 二进制日志，MySQl8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。
Windows系统："my.ini" Linux系统: "my.cnf"【cd /var/lib/mysql/】
# 在my.ini配置开启binlog日志，日志的文件前缀为 mysqlbin
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format=STATEMENT
# 生成的文件名如:mysqlbin.000001,mysqlbin.000002

-- 日志格式
• STATEMENT
该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。
• ROW
该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。
比如，执行SQL语句：update tb_book set status='1', 如果是STATEMENT日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。
• MIXED
混合了 STATEMENT 和 ROW 两种格式。

-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';
-- 查看binlog日志的格式
show variables like 'binlog_format';
-- 查看所有日志
show binlog events;
-- 查看最新的日志
show master status;
-- 查询指定的binlog日志
show binlog events in 'binlog.000026';
select * from mydb1.emp2; -- 查询语句不会被记录
update mydb1.emp2 set salary = 8000; -- 增删改会记录
-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 156 limit 2;
-- 从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 666 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master

3、查询日志（增删改查）

• 查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。
• 默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置 ：

# 该选项用来开启查询日志，可选值：0或者1 
general_log=1 -- 0 代表关闭， 1 代表开启
# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=file_name

-- 查看MySQL是否开启了查询日志
show variables like 'general_log%';
-- 开启查询日志
set global general_log=1; -- 重启后会失效
-- 永久开启：在 MySQL 配置文件（通常是 /etc/my.cnf 或/etc/mysql/mysql.conf.d/mysqld.cnf）中添加以下内容：
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

select * from mydb1.emp2;
update mydb1.emp2 set salary = 9000;

4、慢查询日志（查超时）

慢查询日志记录了：
当 "执行时间 > long_query_time" 并且 "扫描记录数 > min_examined_row_limit" 的所有SQL语句的日志。
# 该参数用来控制慢查询日志是否开启，可取值：1和0
slow_query_log=1 -- 1代表开启，0代表关闭
# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
# 该选项用来配置查询的时间限制，超过这个时间将认为值慢查询
long_query_time=10 -- 默认为10秒，最小为0，精度可以到微秒。

-- 查看MySQL是否开启了慢查询日志
show variables like 'slow_query_log%';
-- 开启慢查询日志
set global slow_query_log=1; -- 重启后会失效
-- 测试
select sleep(10);
-- 查看慢日志文件
cd /var/lib/mysql/hostname-slow.log -- linux日志存放目录

十七、优化

1、查看SQL执行次数

通过查看状态信息可以查看对当前数据库的主要操作类型。
-- 下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______'; -- 查看当前会话统计结果
show global status like 'Com_______'; -- 查看自数据库上次启动至今统计结果
show status like 'Innodb_rows_%‘'; -- 查看针对Innodb引擎的统计结果

"Variable_name"	"Value"
Com_binlog		0
Com_commit		0
Com_delete		5		-- 删除语句执行5次
Com_import		0
Com_insert		181		-- 插入语句执行181次
Com_repair		0
Com_revoke		0
Com_select		1631	-- 查询语句执行1631次
Com_signal		0
Com_update		4
Com_xa_end		0

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

2、定位低效率执行SQL

-- 可以通过以下两种方式定位执行效率较低的 SQL 语句。
• "慢查询日志": 通过慢查询日志定位那些执行效率较低的 SQL 语句。
• "show processlist"：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log=1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like 'long_query_time%';
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time=4;

show processlist; -- 查看sql正在执行的进程

1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据

-- 测试
select sleep(50);
-- 从另一个终端查看进程
show processlist;

3、Explain 分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

-- Explain分析执行计划
explain select * from user where uid = 1; -- 显示如下信息

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	(Null)	const	PRIMARY	PRIMARY	4	const	1	100.00	(Null)

字段	含义
id	select 查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为 (system ---> const -----> eq_ref ------> ref -------> ref_or_null ---> index_merge ---> index_subquery -----> range ------> index ------> all )
possible_keys	查询时，表示可能使用的索引
key	查询时，表示实际使用的索引
key_len	索引字段的长度（占字节）
rows	扫描行的数量
extra	执行情况的说明和描述

a. 执行顺序

㊀ 同id，从上到下

explain select * from user u, user_role ur, role r where u.uid = ur.uid
and ur.rid = r.rid ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	(Null)	ALL	PRIMARY	(Null)	(Null)	(Null)	2	100.00	(Null)
1	SIMPLE	ur	(Null)	ref	uid,rid	uid	5	mydb13_c	1	100.00	(Null)
1	SIMPLE	r	(Null)	ALL	PRIMARY	(Null)	(Null)	(Null)	2	50.00	Using...

㊁ id越大先执行

explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '张飞'))

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	role	(Null)	const	PRIMARY	PRIMARY	4	const	1	100.00	(Null)
2	SUBQUERY	user_role	(Null)	ref	uid	uid	5	const	1	100.00	Using where
3	SUBQUERY	user	(Null)	ALL	(Null)	(Null)	(Null)	(Null)	2	50.00	Using where

b. select 类型

select_type	含义
SIMPLE	简单的 select 查询，查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DERIVED	在 FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL 会递归执行这些子查询，把结果放在临时表中
UNION	若第二个 SELECT 出现在 UNION 之后，则标记为 UNION； 若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为： DERIVED
UNION RESULT	从 UNION 表获取结果的 SELECT

-- SIMPLE：没有子查询和union
explain select * from user;
explain select * from user u,user_role ur where u.uid = ur.uid;
-- PRIMARY：主查询，也就是子查询中的最外层查询
explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '张飞'));
-- SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询
explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '张飞'));
-- DERIVED：在from中包含子查询，被标记为衍生表（临时表）
explain select * from (select * from user limit 2) as t;
-- UNION
-- UNION RESULT
explain select * from user where uid = 1 union select * from user where uid = 3;

c. type 访问类型【重点】

type	含义
NULL	MySQL 不访问任何表，索引，直接返回结果
system	系统表，少量数据，从内存读取，往往不需要进行磁盘 IO；如果是 5.7 及以上版本的话就不是 system 了，而是 all，即使只有一条记录
const	命中主键 (primary key) 或者唯一 (unique) 索引；被连接的部分是一个常量 (const) 值；
eq_ref	对于前表的每一行，后表只有一行被扫描。（1）join 查询；（2）命中主键 (primary key) 或者非空唯一 (unique not null) 索引；（3）等值连接；
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。对于前表的每一行 (row)，后表可能有多于一行的数据被扫描。
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between，<,>, in 等操作。
index	需要扫描索引上的全部数据。
all	全表扫描，此时 id 上无索引

结果值从最好到最坏以此是："system > const > eq_ref > ref > range > index > ALL"

-- NULL
explain select now();
explain select rand();
-- const
explain select * from user where uid = 2; -- 唯一索引
-- eq_ref
explain select * from user1 a, user2 b where a.id = b.id; -- 左表：eq_ref
# join查询时，左表的关联列需为唯一索引，右表的关联列要一对一关系（若一对多，a表变回all访问类型）
-- ref
explain select * from user1 a, user2 b where a.id = b.id; -- 左表：ref
# join查询时，左表的关联列需为普通索引，右表的关联列要一对一关系（若一对多，a表变回all访问类型）
-- range
explain select*from user2 where id >2; -- 范围查询
-- index
explain select id from user2; -- index，扫描索引列全部数据
explain select *from user2; -- all，扫描全部数据

d. key_len

-- 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好 。

列类型	KEY_LEN	备注
id int	key_len = 4+1 = 5	允许 NULL，加 1 byte
id int not null	key_len = 4	不允许 NULL
user char(30) utf8	key_len = 30*3+1	允许 NULL
user varchar(30) not null utf8	key_len = 30*3+2	动态列类型，加 2-bytes
user varchar(30) utf8	key_len = 30*3+2+1	动态列类型，加 2-bytes；允许 NULL，再加 1-byte
detail text(10) utf8	key_len = 30*3+2+1	TEXT 列截取部分，被视为动态列类型，加 2-bytes；且允许 NULL

e. extra

-- 其他的额外的执行计划信息，在该列展示 。

extra	含义
using filesort	说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”，效率低。
using temporary	需要建立临时表 (temporary table) 来暂存中间结果，常见于 order by 和 group by；效率低【group by的列没有索引】
using index	SQL 所需要返回的所有列数据均在一棵索引树上，避免访问表的数据行，效率不错。【group by的列有索引】

"Extra":
  "using index"：使用覆盖索引的时候就会出现。
  "using where"：在查找使用索引的情况下，需要回表去查询所需的数据。
  "using index condition"：查找使用了索引，但是需要回表查询数据。
  "using index ; using where"：查找使用了索引，数据都在索引列中能找到，所以不需要回表查询数据。

4、show profile 分析SQL

-- 通过 have_profiling 参数，能够看到当前MySQL是否支持profile：
select @@have_profiling;
set profiling=1; -- 开启profiling 开关；

-- 然后执行你要的SQL
-- 最后执行show profile分析SQL，就可以看到每个SQL执行语句运行的时间
show profiles; -- 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

Query_ID	Duration	Query
1	0.00012525	select @@profiling
2	0.00010175	use mydb5
3	0.00037	explain select * from u;

-- 查看单个SQL执行过程中每个线程的状态和消耗的时间：
show profile for query query_id;
show profile for query 3;

-- MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间
show profile [all、cpu、block io 、context switch、page faults] for query query_id;
show profile cpu for query 3;

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的 cpu
CPU_system	系统占有的 cpu

5、trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划

-- 打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on;
SET optimizer_trace_max_mem_size=1000000;
-- 执行SQL语句 ：
select * from student where uid < 2;
-- 最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：
select * from information_schema.optimizer_trace \G; -- 要在ssh终端执行此命令

6、索引优化

-- 数据准备
create table `tb_seller` (
  `sellerid` varchar (100),
  `name` varchar (100),
  `nickname` varchar (50),
  `password` varchar (60),
  `status` varchar (1),
  `address` varchar (100),
  `createtime` datetime,
  primary key(`sellerid`)
);

-- 创建组合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

a. 全值匹配（效率最高）

-- 避免索引失效应用‐全值匹配
explain select * from tb_seller where name='小米科技' and status='1' and
address='北京市'; -- ref 该情况下，索引生效，执行效率高。

b. 最左前缀（效率次高）

-- 最左前缀法则
-- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain select * from tb_seller where name='小米科技'; -- 303
explain select * from tb_seller where name='小米科技' and status='1'; -- 309
explain select * from tb_seller where status='1' and name='小米科技'; -- 309

-- 违法最左前缀法则，索引失效：
explain select * from tb_seller where status='1'; -- null
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技' and address='北京市'; -- 303

c. 其他匹配原则

-- 1、范围查询右边的列，不能使用索引。
-- 根据前面的两个字段name、status查询是走索引的，但是最后的address没有用到索引。
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市'; -- 309

-- 2、不要在索引列上进行运算操作，索引将失效。
explain select * from tb_seller where substring(name,3,2) = '科技'; -- null

-- 3、字符串不加单引号，造成索引失效。
explain select * from tb_seller where name='小米科技' and status = 1; -- 303

-- 4、尽量使用覆盖索引，【避免select * 】
-- 效率低，需要从原表及磁盘上读取数据 
explain select * from tb_seller where name='小米科技' and address='北京市';
-- 效率高，从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技' and address='北京市';
explain select name,status,address from tb_seller where name='小米科技' and address='北京市';
-- 效率低，如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市';

-- 5、使用【or】、【%开头的Like】导致索引失效
-- 用or分割开的条件，那么涉及的索引都不会被用到。【name有索引，createtime无索引】
explain select * from tb_seller where name='程序员' or createtime = '2088-01-01 12:00:00'; -- null
explain select * from tb_seller where name='程序员' or address = '西安市';# null
explain select * from tb_seller where name='程序员' or status = '1'; -- null
-- 以%开头的Like模糊查询，索引失效。
explain select * from tb_seller where name like '科技%'; -- 用索引
explain select * from tb_seller where name like '%科技'; -- 不用索引
explain select * from tb_seller where name like '%科技%';-- 不用索引
-- 弥补上面的不足,【不用*】，只select索引列
explain select name from tb_seller where name like '%科技%'; -- index

• MySQL评估使用索引，时有时无

-- 1、如果MySQL评估使用索引比全表更慢，则不使用索引。
-- 这种情况是由数据本身的特点来决定的【“北京市”重复比较多】
create index index_address on tb_seller(address);
explain select * from tb_seller where address = '北京市'; -- 没有使用索引
explain select * from tb_seller where address = '西安市'; -- 使用索引

-- 2、is NULL，is NOT NULL 有时有效，有时索引失效。
create index index_address on tb_seller(nickname); -- 创建普通索引，此列空值较少
explain select * from tb_seller where nickname is NULL; -- ref
explain select * from tb_seller where nickname is not NULL; -- range

-- 3、in 走索引， not in 索引失效
-- 普通索引
explain select * from tb_seller where nickname in("阿里","百度"); # 索引
explain select * from tb_seller where nickname not in("阿里","百度"); # 无索引
-- 主键索引
explain select * from tb_seller where sellerid in("阿里","百度"); # 有索引
explain select * from tb_seller where sellerid not in("阿里","百度"); # 有索引

-- 4、 【单列索引】和【复合索引】，尽量使用【复合索引】
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
/*	等价于创建了三种索引：
	name
	name + status
	name + status + address
*/
如果一张表有多个单列索引，即使 where 中都使用了这些索引列，则只有一个最优的索引生效

7、SQL优化

a. 大批量插入数据

-- 准备一张表
create table `tb_user` (
  `id` int(11) not null auto_increment,
  `username` varchar(45) not null,
  `password` varchar(96) not null,
  `name` varchar(45) not null,
  `birthday` datetime default null,
  `sex` char(1) default null,
  `email` varchar(45) default null,
  `phone` varchar(45) default null,
  `qq` varchar(32) default null,
  `status` varchar(32) not null comment '用户状态',
  `create_time` datetime not null,
  `update_time` datetime default null,
  primary key (`id`),
  unique key `unique_user_username` (`username`)
);

1）主键顺序插入

-- 1）结论：将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。

-- 1、首先，检查一个全局系统变量'local_infile'的状态，是否开启文件导入功能。
show global variables like 'local_infile';
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
-- 3、加载数据
/*
脚本文件介绍 :
sql1.log ----> 主键有序
sql2.log ----> 主键无序
*/
-- 说明：文件中字段间分隔符为【,】，每行用的【/n】隔离。
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n'; -- 22 s

load data local infile 'D:\\sql_data\\sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n'; -- 81 s

2）关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，
在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
-- 删除表格内所有数据
truncate table tb_user;
-- 导入数据
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n'; -- 21 s
-- 恢复唯一性校验
SET UNIQUE_CHECKS=1;

b. 优化 insert

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的"缩减客户端与数据库之间的连接、关闭等消耗"。使得效率比分开执行的单个insert语句快。

-- 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
-- 优化后的方案为 ：
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

-- 在事务中进行数据插入。【优化提交次数】
begin;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

-- 数据有序插入
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
-- 优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

c. 优化 order by

-- 两种排序方式
1、第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
2、第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

-- 示例准备
create table `emp` (
    `id` int(11) not null auto_increment,
    `name` varchar(100) not null,
    `age` int(3) not null,
    `salary` int(11) default null,
    primary key (`id`)
);
create index idx_emp_age_salary on emp(age,salary);

-- 1、避免【SELECT * 】
EXPLAIN SELECT * from emp order by age; -- Using filesort
EXPLAIN SELECT * from emp order by age,salary; -- Using filesort
-- 2、避免SELECT非索引列
EXPLAIN SELECT id from emp order by age; -- Using index
EXPLAIN SELECT id,age from emp order by age; -- Using index
EXPLAIN SELECT id,age,salary,name from emp order by age; -- Using filesort

-- 3、order by后边的多个排序字段要求尽量【排序方式相同】
EXPLAIN SELECT id,age from emp order by age,salary;
-- Using index
EXPLAIN SELECT id,age from emp order by age asc,salary desc;
-- Using index; Using filesort【降级】
EXPLAIN SELECT id,age from emp order by age desc,salary desc;
-- Backward index scan; Using index
 
-- 4、order by后边的多个排序字段顺序尽量和【组合索引】字段顺序一致
EXPLAIN SELECT id,age from emp order by salary,age;
-- Using index; Using filesort【降级】

d. 优化 filesort

-- 对于Filesort，MySQL有两种排序算法：
1） "两次扫描算法"：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区"sort_buffer"中排序，如果"sort_buffer"不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2）"一次扫描算法"：一次性取出满足条件的所有字段，然后在排序区 "sort_buffer" 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
-- 简单来说：【法1】吃IO省内存，【法2】吃内存省IO
MySQL 会比较"系统变量max_length_for_sort_data的大小"和"Query语句取出的字段总大小"，来判定是否那种排序算法，
-- 如果【max_length_for_sort_data】更大，那么使用【一次扫描算法】，否则使用第一种。

可以适当提高 "sort_buffer_size" 和 "max_length_for_sort_data" 系统变量，来增大排序区的大小，提高排序的效率。

show variables like 'max_length_for_sort_data'; -- 4096
show variables like 'sort_buffer_size'; -- 262144
-- 把值调大
set @@max_length_for_sort_data = 8192;
set @@sort_buffer_size = 524288;
-- 建议去"my.cnf"添加上来实现永久配置【cd /var/lib/mysql/】

f. 优化 子查询

1、"高效率"：多表查询 > 子查询 -- 【尽量避免子查询】
-- 对比
explain select * from user where uid in (select uid from user_role); -- index
explain select * from user u,user_role ur where u.uid = ur.uid; -- ref

连接(Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

g. 优化 limit查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 900000,10 ，此时需要MySQL排序前900010 记录，仅仅返回 900000 ‐ 900010 的记录，其他记录丢弃，查询排序的代价非常大 。
-- 1、优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
-- 2、优化思路二
该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。

-- 1、优化思路一
select count(*) from tb user;

select * from tb user limit 0,10;

select * from tb user limit 900000,10: -- 0.684 s # ALL

-- 利用索引取值，避免limit排序，再利用多表查询，索引与索引间匹配
select * from tb user a,(select id from tb user order by id limit 988888,10)b where a.id = b.id; -- 0.486 s # eq_ref

-- 2、优化思路二
select * from tb user where id > 900000 limit 10; -- 0 s

-- 自增长索引的优势，先where筛选，再用limit

十八、JDBC 操作 (Java API)

1、概述

### Java 数据库连接架构树
Java App
└── JDBC API（Java 数据库连接标准接口）
    └── JDBC Driver Manager（驱动管理核心）
        ├── Mysql JDBC 驱动（适配 MySQL 5.x+/8.x）
        ├── Sql Server JDBC 驱动（适配 SQL Server 2012+）
        └── Oracle JDBC 驱动（适配 Oracle 11g+/12c+）

一、JDBC 核心类和接口

• DriverManager：用于注册数据库驱动。
• Connection：表示与数据库建立的连接。
• Statement/PreparedStatement：用于操作数据库 SQL 语句的对象。
• ResultSet：存储数据库查询结果的结果集（虚拟表）。

二、JDBC 执行流程（共 5 步）

1. 注册驱动：通过DriverManager.registerDriver(驱动)在 Java 程序中注册数据库驱动。
2. 建立连接：通过DriverManager.getConnection(ip,端口,数据库,用户名,密码)建立与数据库服务器的连接。
3. 执行 SQL 指令：通过Statement/PreparedStatement的executeUpdate()（执行增删改）或executeQuery()（执行查询）方法，将 SQL 指令发送给数据库服务器执行。
4. 处理结果：数据库服务器返回结果，增删改操作返回影响行数rows，查询操作返回ResultSet结果集。
5. 释放资源：通过close()方法释放数据库连接等资源。

2、操作

-- 数据库准备
create database mydb16_jdbc;
use mydb16_jdbc;
create table student(
  sid int PRIMARY key auto_increment,
  sname varchar(20),
  age int
 );
insert into student values(NuLL,'宋江',30),(NULL,'武松',28),(NULL,'林冲',26);

a. 结果集

// 创建java项目
// 1、主目录内新建lib文件夹，放入mysql-connector-java-8.0.25.jar驱动包
// 2、右键驱动包，菜单点击添加到库
// 3、src目录创建package，再创建一个java类
// java类实例：
package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo01 {
    public static void main(String[] args) throws SQLException {
        //1:注册驱动（新版本不需要注册了，直接忽略此行代码）
        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        //2:获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.123.226:3306/mydb16_jdbc", "root", "hxc850996480");
        //3:执行SQL语句
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from student");
        //获取表的列数
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        
        // ResultSet 是一个游标，当第一次 while (resultSet.next()) 循环结束后，游标已经到达结果集的末尾，第二个 while 循环无法再次读取数据。
        
        //4:处理结果 (通用方式 - 遍历列)
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                String columnValue = resultSet.getString(i);
                System.out.print(columnValue + "\t");
            }
            System.out.println();
        }
        // 4:处理结果 (原始方式 - 指定列)
        while (resultSet.next()) {
            int id = resultSet.getInt("sid");
            String name = resultSet.getString("sname");
            int age = resultSet.getInt("age");
            System.out.println(id + "---" + name + "---" + age);
        }
        //5:释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

// 结果
1---宋江---30
2---武松---28
3---林冲---26

b. 增删改

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo02 {
    public static void main(String[] args) throws SQLException {
        //1:注册驱动（新版本不需要注册了，直接忽略此行代码）
        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        //2:获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.123.226:3306/mydb16_jdbc", "root", "hxc850996480");
        //3:执行SQL语句
        Statement statement = connection.createStatement();
        // 增加数据
        int rows = statement.executeUpdate("insert into student values (NULL, '赵六', 24)"); // 返回增加的行数
        System.out.println("增加的行数：" + rows);
        // 修改数据
        int updateRows = statement.executeUpdate("update student set sname = '王五' where sid = 4"); // 返回修改的行数
        System.out.println("修改的行数：" + updateRows);
        // 删除数据
        int deleteRows = statement.executeUpdate("delete from student where sid = 4"); // 返回删除的行数
        System.out.println("删除的行数：" + deleteRows);

        //5:释放资源
        statement.close();
        connection.close();
    }
}

c. SQL 注入问题

-- 数据表准备
create table user(
  sid int PRIMARY key auto_increment,
  username varchar(20),
  password varchar(20)
 );
insert into user values(NuLL,'123',123456);

package cn.itcast.jdbc;

import java.sql.*;
import java.util.Scanner;

public class JdbcDemo03 {
    public static void main(String[] args) throws SQLException {
        // 用户输入用户名和密码，判断是否登录成功
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = scanner.nextLine();
        System.out.print("请输入密码：");
        String password = scanner.nextLine();


        //1:注册驱动
        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        //2:获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.123.226:3306/mydb16_jdbc", "root", "hxc850996480");
        //3:执行SQL语句
        Statement statement = connection.createStatement();
        String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
        System.out.println(sql); // 输出拼接好的 SQL 语句，便于调试
        ResultSet resultSet = statement.executeQuery(sql); // 用于执行查询语句
        
        // 如果结果集中有记录，说明用户名和密码正确
        if (resultSet.next()) {
            System.out.println("登录成功！");
        } else {
            System.out.println("登录失败！");
        }

        //5:释放资源
        statement.close();
        connection.close();
    }
}

# cmd启动java实例
请输入用户名：123
请输入密码：123456
select * from user where username = '123' and password = '123456'
登录成功！
# cmd启动java实例 -- 演示sql注入问题
请输入用户名：456456
请输入密码：aaa 'or' 1=1
select * from user where username = '456456' and password = 'aaa 'or' 1=1'
登录成功！
# 分析--------------------------------
username = '456456' and password = 'aaa ' # 无论真假
' 1=1' # 一直为真
or会导致每行数据都为真，直接输出数据

d. 解决 SQL 注入

package cn.itcast.jdbc;

import java.sql.*;
import java.util.Scanner;

public class JdbcDemo04 {
    public static void main(String[] args) throws SQLException {
        // 用户输入用户名和密码，判断是否登录成功
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = scanner.nextLine();
        System.out.print("请输入密码：");
        String password = scanner.nextLine();

        //1:注册驱动
        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        //2:获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.123.226:3306/mydb16_jdbc", "root", "hxc850996480");
        //3:执行SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where username = ? and password = ?");
        preparedStatement.setObject(1, username);
        preparedStatement.setObject(2, password);
        System.out.println(preparedStatement); // 输出预编译的 SQL 语句，便于调试
        ResultSet resultSet = preparedStatement.executeQuery(); // 用于执行查询语句

        // 如果结果集中有记录，说明用户名和密码正确
        if (resultSet.next()) {
            System.out.println("登录成功！");
        } else {
            System.out.println("登录失败！");
        }

        //5:释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

# cmd启动java实例
请输入用户名：456465
请输入密码：aaa 'or' 1=1
com.mysql.cj.jdbc.ClientPreparedStatement: select * from user where username = '456465' and password = 'aaa ''or'' 1=1'
登录失败！
# 分析--------------------------------
# 相当于把【'】转义成【\'】

十九、pymysql 操作 (python API)

1、查询

import pymysql
conn = pymysql.connect(
    host='192.168.123.226',
    port=3306,
    user='root',
    password='hxc850996480',
    database='mydb16_jdbc',
    charset='utf8')
# 获取游标
cursor = conn.cursor()

# 执行sql语句 返回值就是sql语句在执行过程中影响的行数
row_count = cursor.execute("select * from student;")
print("sql语句执行影响的行数%d" % row_count)

# 取出第一行数据
first_row = cursor.fetchone()
print("第一行数据:", first_row)

# 获取查询结果
results = cursor.fetchall()
for row in results:
    print(row)

# 关闭游标和连接
cursor.close()
conn.close()

sql语句执行影响的行数6
第一行数据: (1, '宋江', 30)
(2, '武松', 28)
(3, '林冲', 26)
(4, '王五', 24)
(5, '赵六', 24)

2、增删改

import pymysql

conn = pymysql.connect(
    host='192.168.123.226',
    port=3306,
    user='root',
    password='hxc850996480',
    database='mydb16_jdbc',
    charset='utf8')
# 获取游标
cursor = conn.cursor()

# 插入数据
sql = "insert into student values(%s,%s,%s);"
data = (None, '李四', 22)
cursor.execute(sql, data)  # sql和data之间以","隔开
# 修改数据
sql = "update student set sname=%s where sid=%s"
data = ('李逵', 4)
cursor.execute(sql, data)
# 删除数据
sql = "delete from student where sid=%s"
data = (4)
cursor.execute(sql, data)

conn.commit()  # 提交，不然无法保存插入或者修改的数据(这个一定要加上)

# 关闭游标和连接
cursor.close()
conn.close()