MySQL基础查漏补缺

1. char 与 varchar ?

char 定义的是定长的字符串类型，比如电话号码、性别等字段，如果插入的数据长度小于定长就会以unicode编码补缺‘\u0000’（我们看着是空格），大于定长就会插入失败而报错；

而varchar定义的是不定长的字符串类型。

两者定义的长度都是编码字符长度，比如：如果定义一个varchar(1)去存储性别（假如数据为：男，女），这样很不合理，因为汉字占多个字节（不同的编码占用字节可能不一样），导致插入的数据长度会溢出。

2. unique ？

unique表示该字段的值不能重复，如下面的建表语句表示id、name、age都不能为空，但是qq可以为空；其中id和qq不能重复，即这两个字段的数据不能存在相同的值：

create table students (
    stu_id char(6) not null unique,
    stu_name varchar(20) not null,
    stu_age int not null,
    stu_qq varchar(11) unique
    );

3.

desc <tableName>;        ##表示查看表的结构

## 修改表名
alter table <tableName> rename to <newTableName>;

## 数据表也是有字符集的，默认字符集和数据库⼀致
alter table <tableName> character set utf8;

## 添加列（字段）
alter table <tableName> add <columnName> varchar(200);

## 修改列（字段）的列表和类型
alter table <tableName> change <oldColumnName> <newCloumnName> <type>;

## 只修改列（字段）类型
alter table <tableName> modify <columnName> <newType>;

## 删除列（字段）
alter table stus drop <columnName>;

4. 数值类型有哪些，分别占用多少字节？

数值类型

类型                   内存空间大小      范围                                                           说明

tinyint                 1byte（8bit）     有符号 -128~127，⽆符号 0~255              特小型整数

smallint              2byte（16bit）   有符号 -2^15~2^15，⽆符号 0~2^16 - 1    小型整数

mediumint          3byte（24bit）   有符号 -2^23~2^23，⽆符号 0~2^24 - 1    中型整数

int/integer           4byte（32bit）   有符号 -2^31~2^31，⽆符号 0~2^32 - 1    整数

bigint                  8byte（64bit）   有符号 -2^63~2^63，⽆符号 0~2^64 - 1    大型整数

float                   4byte                   有符号 -2^31~2^31，⽆符号 0~2^32 - 1    单精度

double               8byte                   有符号 -2^63~2^63，⽆符号 0~2^64 - 1    双进度

decimal             视情况而定          自定义                                         decimal(10,2)表示数值⼀共有10位⼩数位有2位（最大精度为32）

关于decimal的存储大小查看以下博客：MySQL数据类型（decimal的存储大小）

 5. mysql能存储图片、音频或视频吗？

 能！ 其中字符串类型的tinyblob、blob、mediumblob和longblob均可存储不同字符长度的⼆进制字符串（图片、视频、音频等都能转换为二进制类型）。但是一般不会用，大多数是数据直接存在服务器上，将存储数据的地址放到数据库中...

6. char、varchar 与 text?

 char是定长字符串，不够定长就补‘\u0000’（看着是空格），而text是文本类型，不能指定字符长度不够就不补，而varchar可以指定字符长度。一般是使用char、varchar，当varchar不够时用longtext。

更多请查看以下博客：char和varchar，text的区别

 7. 常见的字段约束？

⾮空约束（not null）：限制此列的值必须提供，不能为null

唯⼀约束（unique）：在表中的多条数据，此列的值不能重复

主键约束（primary key）：⾮空+唯⼀，能够唯⼀标识数据表中的⼀条数据

外键约束（foreign key）：建⽴不同表之间的关联关系

 其中主键就是数据表中记录的唯⼀标识，在⼀张表中只能有⼀个主键（主键可以是⼀个 列，也可以是多个列的组合），以下是两种创建数据表添加约束:

create table books(
 book_isbn char(4) primary key,
 book_name varchar(10) not null,
 book_author varchar(6)
);

create table books(
 book_isbn char(4),
 book_name varchar(10) not null,
 book_author varchar(6),
 primary key(book_isbn)
);

下面是删除表中的主键约束：

 alter table books drop primary key;

创建表后添加主键约束：

## 创建表时没有添加主键约束
create table books(
 book_isbn char(4),
 book_name varchar(10) not null,
 book_author varchar(6)
);

## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;

定义自动增长主键：

create table types(
 type_id int primary key auto_increment,
 type_name varchar(20) not null,
 type_remark varchar(100)
);

定义联合主键（在实际企业项⽬的数据库设计中，联合主键使⽤频率并不⾼；当⼀个张数据表中没有 明确的字段可以作为主键时，我们可以额外添加⼀个ID字段作为主键。）：

##正确示范
create table grades(
 stu_num char(8),
 course_id int,
 score int,
 primary key(stu_num,course_id)
); 

##注意这样定义是不对的
create table grades(
 stu_num char(8) primary key(,
 course_id int primary key(,
 score int
); 

8、 DDL（Database Manipulation Language）数据操纵语言？

 1、插入数据（insert操作）:

##语法
insert into <tableName>(columnName,columnName....) 
values (value1,value2....);

##示例

## 向数据表中指定的列添加数据（不允许为空的列必须提供数据）
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel)
values ('20210101','张三','男',21,'13030303300');

## 数据表名后的字段名列表顺序可以不与表中⼀致，但是values中值的顺序必须与表名后字段名顺序对应
insert into stus(stu_num,stu_name,stu_age,stu_tel,stu_gender)
values ('20210103','王五',20,'13030303302','⼥');
## 当要向表中的所有列添加数据时，数据表名后⾯的字段列表可以省略，但是values中的值的顺序要与数据表定义的字段保持⼀致;
insert into stus values('20210105','孙琦','男',21,'13030303304','666666');

## 不过在项⽬开发中，即使要向所有列添加数据,也建议将列名的列表显式写出来(增强SQL的稳定性)
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq)
values('20210105','孙琦','男',21,'13030303304','666666');

项目开发中不推荐省略字段列表，若临时添加一个新字段，则省略字段列表的sql语句将会出错。

2、删除数据（delete操作）

##语法

delete from <tableName> [where conditions];


##示例

## 删除学号为20210102的学⽣信息
delete from stus where stu_num='20210102';

## 删除年龄⼤于20岁的学⽣信息(如果满⾜where⼦句的记录有多条，则删除多条记录)
delete from stus where stu_age>20;

## 如果删除语句没有where⼦句，则表示删除当前数据表中的所有记录(敏感操作)
delete from stus;

 3、修改数据（update操作）

## 语法
update <tableName> set columnName=value [where conditions]


## 示例

## 将学号为20210105的学⽣姓名修改为“孙七”（只修改⼀列）
update stus set stu_name='孙七' where stu_num='20210105';

## 将学号为20210103的学⽣ 性别修改为“男”,同时将QQ修改为 777777（修改多列）
update stus set stu_gender='男',stu_qq='777777' where
stu_num='20210103';

## 如果update语句没有where⼦句，则表示修改当前表中所有⾏（记录）
update stus set stu_name='Tom';

9、like ？

在like关键字后的reg表达式中 % 表示任意多个字符 【 %o% 包含字⺟o】 _ 表示任意⼀个字符 【 _o% 第⼆个字⺟为o】

# 查询学⽣姓名包含字⺟o的学⽣信息
select * from stus where stu_name like '%o%';

# 查询学⽣姓名第⼀个字为`张`的学⽣信息
select * from stus where stu_name like '张%';

# 查询学⽣姓名最后⼀个字⺟为o的学⽣信息
select * from stus where stu_name like '%o';

# 查询学⽣姓名中第⼆个字⺟为o的学⽣信息
select * from stus where stu_name like '_o%';

10、distinct（消除重复行） ？

select stu_age from stus;
+---------+
| stu_age |
+---------+
| 21 |
| 18 |
| 20 |
| 21 |
| 21 |
| 17 |
+---------+
select distinct stu_age from stus;
+---------+
| stu_age |
+---------+
| 21 |
| 18 |
| 20 |
| 17 |
+---------

11、 order by ? 

order by columnName 表示将查询结果按照指定的列排序

asc 按照指定的列升序（默认）

desc 按照指定的列降序

# 单字段排序

select * from stus where stu_age>15 order by stu_gender desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210101 | omg | 男 | 21 | 13030303300 | NULL |
| 20210103 | Tom | 男 | 20 | 13030303302 | 777777 |
| 20210105 | Polly | 男 | 21 | 13030303304 | 666666 |
| 20210106 | Theo | 男 | 17 | 13232323322 | NULL |
| 20210102 | 韩梅梅 | ⼥ | 18 | 13131313311 | 999999 |
| 20210104 | Lucy | ⼥ | 21 | 13131323334 | NULL |
+----------+-----------+------------+---------+-------------+--------+


# 多字段排序 ： 先满⾜第⼀个排序规则，当第⼀个排序的列的值相同时再按照第⼆个列的规则
排序

select * from stus where stu_age>15 order by stu_gender asc,stu_age
desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210104 | Lucy | ⼥ | 21 | 13131323334 | NULL |
| 20210102 | 韩梅梅 | ⼥ | 18 | 13131313311 | 999999 |
| 20210101 | omg | 男 | 21 | 13030303300 | NULL |
| 20210105 | Polly | 男 | 21 | 13030303304 | 666666 |
| 20210103 | Tom | 男 | 20 | 13030303302 | 777777 |
| 20210106 | Theo | 男 | 17 | 13232323322 | NULL |
+----------+-----------+------------+---------+-------------+--------+

12、聚合函数：avg(),sum(),min(),max(),count()等。

# 计算所有学⽣年龄的平均值
select avg(stu_age) from stus;
+--------------+
| avg(stu_age) |
+--------------+
| 19.0000 |
+--------------+

# 计算所有性别为男的学⽣的年龄的平均值
select avg(stu_age) from stus where stu_gender='男';
+--------------+
| avg(stu_age) |
+--------------+
| 18.8000 |
+--------------+

13、 日期函数：当我们向⽇期类型的列添加数据时，可以通过字符串类型赋值（字符串的格式必须为 yyyy-MM-dd hh:mm:ss） 如果我们想要获取当前系统时间添加到⽇期类型的列，可以使⽤ now() 或者 sysdate()

# 通过字符串类型 给⽇期类型的列赋值
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20200108','张⼩三','⼥',20,'13434343344','123111','2021-09-01
09:00:00');

# 通过now()获取当前时间
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210109','张⼩四','⼥',20,'13434343355','1233333',now());

# 通过sysdate()获取当前时间
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210110','李雷','男',16,'13434343366','123333344',sysdate());

# 通过now和sysdate获取当前系统时间

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-09-10 16:22:19 |
+---------------------+

mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-09-10 16:22:26 |
+---------------------+

14、 字符串函数：

concat(colnum1,colunm2,...) 拼接多列

upper(column) 将字段的值转换成⼤写

lower(column) 将指定列的值转换成⼩写

substring(column,start,len) 从指定列中截取部分显示 start从1开始

# concat(colnum1,colunm2,...) 拼接多列

select concat(stu_name,'-',stu_gender) from stus;
+---------------------------------+
| concat(stu_name,'-',stu_gender) |
+---------------------------------+
| 韩梅梅-⼥ |
| Tom-男 |
| Lucy-⼥ |
| 林涛-男 |
+---------------------------------+


# upper(column) 将字段的值转换成⼤写

mysql> select upper(stu_name) from stus;
+-----------------+
| upper(stu_name) |
+-----------------+
| 韩梅梅 |
| TOM |
| LUCY |
| POLLY |
| THEO |
| 林涛 |
+-----------------+


# lower(column) 将指定列的值转换成⼩写

mysql> select lower(stu_name) from stus;
+-----------------+
| lower(stu_name) |
+-----------------+
| 韩梅梅 |
| tom |
| lucy |
| polly |
| theo |
+-----------------+


# substring(column,start,len) 从指定列中截取部分显示 start从1开始

mysql> select stu_name,substring(stu_tel,8,4) from stus;
+-----------+------------------------+
| stu_name | substring(stu_tel,8,4) |
+-----------+------------------------+
| 韩梅梅 | 3311 |
| Tom | 3302 |

| Lucy | 3334 |
+-----------+------------------------+

 15、group by （分组）?（重点）

 分组就是将数据表中的记录按照指定的类进⾏分组

①select 后使⽤ * 显示对查询的结果进⾏分组之后，显示每组的第⼀条记录（这种显示通 常是⽆意义的）

②select 后通常显示分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等) 语句

③执⾏顺序： 先根据where条件从数据库查询记录 ，然后group by对查询记录进⾏分组，然后执⾏having对分组后的数据进⾏筛选，最后order by对结果排序。

having是对group by后的条件筛选，所以没有group by是不会有having的

#语法

select 分组字段/聚合函数 from 表名 
[where 条件] 
group by 分组列名 
[having 条件]
[order by 排序字段]

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再分别统计每组学⽣的个数
select stu_gender,count(stu_num) from stus group by stu_gender;
+------------+----------------+
| stu_gender | count(stu_num) |
+------------+----------------+
| ⼥ | 4 |
| 男 | 5 |
+------------+----------------+

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再计算每组的平均年龄
select stu_gender,avg(stu_age) from stus group by stu_gender;
+------------+--------------+
| stu_gender | avg(stu_age) |
+------------+--------------+
| ⼥ | 19.7500 |
| 男 | 18.2000 |
+------------+--------------+

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再分别统计每组学⽣的个数
select stu_gender,count(stu_num) from stus group by stu_gender;
+------------+----------------+
| stu_gender | count(stu_num) |
+------------+----------------+
| ⼥ | 4 |
| 男 | 5 |
+------------+----------------+

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再计算每组的平均年龄
select stu_gender,avg(stu_age) from stus group by stu_gender;
+------------+--------------+
| stu_gender | avg(stu_age) |
+------------+--------------+
| ⼥ | 19.7500 |
| 男 | 18.2000 |
+------------+--------------+

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再分别统计每组学⽣的个数
select stu_gender,count(stu_num) from stus group by stu_gender;
+------------+----------------+
| stu_gender | count(stu_num) |
+------------+----------------+
| ⼥ | 4 |
| 男 | 5 |
+------------+----------------+

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再计算每组的平均年龄
select stu_gender,avg(stu_age) from stus group by stu_gender;
+------------+--------------+
| stu_gender | avg(stu_age) |
+------------+--------------+
| ⼥ | 19.7500 |
| 男 | 18.2000 |
+------------+--------------+

16、分页查询的通用公式（limit）？

# 如果在⼀张数据表中：
# pageNum表示查询的⻚码（第几页？）
# pageSize表示每⻚显示的条数（每页显示几条数据？）
# 通⽤分⻚语句如下：
select * from <tableName> [where ...] limit (pageNum - 1) * pageSize , pageSize;

 17、联表中的 ①一对多和多对一 以及 ②多对多 ？

①⽅案：在多的⼀端添加外键 ，与⼀的⼀端主键进⾏关联

②方案：额外创建⼀张关系表来维护多对多关联，即在关系表中定义两个外键，分别与两个数据表的主键进⾏关联

 18、外键约束 ？

外键的使用对于减少数据库冗余性，以及保证数据完整性和一致性有很大作用。

CASCADE 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录

NO ACTION 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。

RESTRICT 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。

SET NULL 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

另外注意，如果两张表之间存在外键关系，则MySQL不能直接删除表(Drop Table)，而应该先删除外键，之后才可以删除。

所以一般默认就好（RESTRICT）

外键的删除时和更新时：外键更新时和删除时的四种选择

#案例：学生表与班级表


#先创建班级表

create table classes(
 class_id int primary key auto_increment,
 class_name varchar(40) not null unique,
 class_remark varchar(200)
);

#创建学⽣表（在学⽣表中添加外键与班级表的主键进⾏关联）

# 【⽅式⼀】在创建表的时候，定义cid字段，并添加外键约束
# 由于cid 列 要与classes表的class_id进⾏关联，因此cid字段类型和⻓度要与class_id⼀致
create table students(
 stu_num char(8) primary key,
 stu_name varchar(20) not null,
 stu_gender char(2) not null,
 stu_age int not null,
 cid int,
constraint FK_STUDENTS_CLASSES foreign key(cid) references
classes(class_id)
);



#【⽅式⼆】先创建表，再添加外键约束

create table students(
 stu_num char(8) primary key,
 stu_name varchar(20) not null,
 stu_gender char(2) not null,
 stu_age int not null,
 cid int
);

# 在创建表之后，为cid添加外键约束
alter table students add constraint FK_STUDENTS_CLASSES foreign
key(cid) references classes(class_id);

# 删除外键约束
alter table students drop foreign key FK_STUDENTS_CLASSES;

因为外键约束的限制，不能对父表（其主键id被引用为外键的表）删除或更新，如果一定要修改将如何解决？

假如：学生表（子表）中的外键是班级表的主键（父表），想要修改班级表的id。

①先将引⽤班级id的学⽣表中的cid修改为 NULL ，然后再修改班级表中对应记录的 classid，最后再将  学⽣表中设置为NULL记录的那一条cid  重新修改为班级表中修改的那个新id。

②设置级联修改和级联删除，也就是设置外键更新时和删除时的四种选择。当选择为CASCADE时，删除和更新父表将不会报错，而是直接在子表中对应的那一条直接被删除或被修改，具体sql语句如下：

# 删除原有的外键
alter table students drop foreign key FK_STUDENTS_CLASSES;

# 重新添加外键，并设置级联修改和级联删除
alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;


#测试

# 班级信息
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
| 5 | Java2104 | ... |
+----------+------------+--------------+

# 学⽣信息
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 5 |
| 20210102 | 李四 | 男 | 18 | 5 |
| 20210103 | 王五 | 男 | 18 | 5 |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+

# 直接修改Java2104的class_id,关联Java2104这个班级的学⽣记录的cid也会同步修改
update classes set class_id=1 where class_name='Java2104';

# 班级信息
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
| 1 | Java2104 | ... |
+----------+------------+--------------+

# 学⽣信息
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 1 |
| 20210102 | 李四 | 男 | 18 | 1 |
| 20210103 | 王五 | 男 | 18 | 1 |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+

# 删除class_id=1的班级信息，学⽣表引⽤此班级信息的记录也会被同步删除
delete from classes where class_id=1;

#班级信息
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
+----------+------------+--------------+

#学生信息
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+

 19、join（连接查询）？

关于内外全连接的文章：点我查看

①内连接 

#语法
select ... from tableName1 inner join tableName2 ON 匹配条件 [where 条件];

#笛卡尔积:select ... from tableName1 inner join tableName2 ON 匹配条件 [where 条件];

#使用where筛选内连接的数据:
#使⽤where设置过滤条件：先⽣成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from students INNER JOIN classes where students.cid = classes.class_id;

#使⽤ON设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀条结果记录
select * from students INNER JOIN classes ON students.cid = classes.class_id;

#然后从内连接后的数据再进行筛选
select * from students INNER JOIN classes ON students.cid = classes.class_id [ where 条件];

#结果：只获取两种表中匹配条件成⽴的数据，任何⼀张表在另⼀种表如果没有找到对应匹配则不会出现在查询结果中（例如：⼩红和⼩明没有对应的班级信息，Java2106 和 Python2106没有对应的学⽣）。

②外连接（以下为示例）

# 语法
select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];

#左连接：显示左表中的所有数据，如果在有右表中存在与左表记录满⾜匹配条件的数据，则
进⾏匹配；如果右表中不存在匹配数据，则显示为Null

#右连接：显示右表中的所有记录，如果在有左表中存在与右表记录满⾜匹配条件的数据，则
进⾏匹配；如果左表中不存在匹配数据，则显示为Null

 ③子查询/嵌套查询

单行单列：

#查询班级名称为'Java2104'班级中的学⽣信息 (只知道班级名称，⽽不知道班级ID)
select * from students where cid = 
(select class_id from classes where class_name='Java2105');

多行单列： 如果⼦查询返回的结果是多个值（单列多⾏），条件使⽤ IN / NOT IN

#查询所有Java班级中的学⽣信息

#传统方式

# a.查询所有Java班的班级编号
select class_id from classes where class_name LIKE 'Java%';
+--------------+
| class_id |
+--------------+
| 1 |
| 2 |
| 3 |
+--------------+
# b.查询这些班级编号中的学⽣信息(union 将多个查询语句的结果整合在⼀起)
select * from students where cid=1
UNION
select * from students where cid=2
UNION
select * from students where cid=3;

#子查询
select * from students where cid IN 
(select class_id from classes whereclass_name LIKE 'Java%');

20、存储过程

①sql指令执行过程图：

②什么时候用到存储过程？

 1. 如果我们需要重复多次执⾏相同的SQL，SQL执⾏都需要通过连接（网络通信）传递到MySQL，并且需要经过编译和执⾏的步骤；

2. 如果我们需要连续执⾏多个SQL指令，并且第⼆个SQL指令需要使⽤第⼀个SQL指令执⾏的结果作为参数；

3.mysql的存储过程还有必要用吗？

③存储过程的介绍：将能够完成特定功能的SQL指令进⾏封装（SQL指令集），编译之后存储在数据库服务 器上，并且为之取⼀个名字，客户端可以通过名字直接调⽤这个SQL指令集，获取执⾏ 结果。以下为存储过程图：

 ④存储过程的优缺点：

存储过程优点：

1. SQL指令⽆需客户端编写，通过⽹络传送，可以节省⽹络开销，同时避免SQL指令在⽹络 传输过程中被恶意篡改保证安全性；

2. 存储过程经过编译创建并保存在数据库中的，执⾏过程⽆需重复的进⾏编译操作，对SQL 指令的执⾏过程进⾏了性能提升；

3. 存储过程中多个SQL指令之间存在逻辑关系，⽀持流程控制语句（分⽀、循环），可以实 现更为复杂的业务;

存储过程的缺点：

1. 存储过程是根据不同的数据库进⾏编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程；

2. 存储过程受限于数据库产品，如果需要⾼性能的优化会成为⼀个问题；

3. 在互联⽹项⽬中，如果需要数据库的⾼并发访问，使⽤存储过程会增加数据库的连接执⾏时间（因为我们将复杂的业务交给了数据库进⾏处理）（高并发尽量不使用存储过程）

⑤存储过程的使用：存储过程与存储函数

⑥案例：使⽤存储过程完成借书操作

业务分析：哪个学⽣借哪本书，借了多少本？ 所以操作时： 保存借书记录，修改图书库存条件： 判断学⽣是否存在？ 判断图书是否存在、库存是否充⾜？

数据准备：

-- 创建数据库
create database db_test3;

-- 使⽤数据库
use db_test3;

-- 创建图书信息表：
create table books(
 book_id int primary key auto_increment,
 book_name varchar(50) not null,
 book_author varchar(20) not null,
 book_price decimal(10,2) not null,
 book_stock int not null,
 book_desc varchar(200)
);

-- 添加图书信息
insert into
books(book_name,book_author,book_price,book_stock,book_desc)
values('Java程序设计','亮亮',38.80,12,'亮亮⽼师带你学Java');
insert into
books(book_name,book_author,book_price,book_stock,book_desc)
values('Java王者之路','威哥',44.40,9,'千锋威哥，Java王者领路⼈');

-- 创建学⽣信息表
create table students(
 stu_num char(4) primary key,
 stu_name varchar(20) not null,
 stu_gender char(2) not null,
 stu_age int not null
);

-- 添加学⽣信息
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1001','张三','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1002','李四','⼥',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1003','王五','男',20);

-- 借书记录表：
create table records(
 rid int primary key auto_increment,
 snum char(4) not null,
 bid int not null,
 borrow_num int not null,
 is_return int not null, -- 0表示为归还 1 表示已经归还
 borrow_date date not null,
 constraint FK_RECORDS_STUDENTS foreign key(snum) references
students(stu_num),
 constraint FK_RECORDS_BOOKS foreign key(bid) REFERENCES
books(book_id)
);

创建存储过程实现借书业务：

-- 实现借书业务：
-- 参数1： a 输⼊参数 学号
-- 参数2： b 输⼊参数 图书编号
-- 参数3： m 输⼊参数 借书的数量
-- 参数4： state 输出参数 借书的状态（1 借书成功，2 学号不存在，3 图书不存在，4 库存不⾜）
create procedure proc_borrow_book(IN a char(4),IN b int, IN m int,OUT state int)
begin
 declare stu_count int default 0;
 declare book_count int default 0;
 declare stock int default 0;
 -- 判断学号是否存在：根据参数 a 到学⽣信息表查询是否有stu_num=a的记录
 select count(stu_num) INTO stu_count from students where stu_num=a;
 if stu_count>0 then
 -- 学号存在
 -- 判断图书ID是否存在：根据参数b 查询图书记录总数
 select count(book_id) INTO book_count from books where book_id=b;
 if book_count >0 then
 -- 图书存在
 -- 判断图书库存是否充⾜：查询当前图书库存，然后和参数m进⾏⽐较
 select book_stock INTO stock from books where book_id=b;
 if stock >= m then
 -- 执⾏借书
 -- 操作1：在借书记录表中添加记录
insert into records(snum,bid,borrow_num,is_return,borrow_date) values(a,b,m,0,sysdate());
 -- 操作2：修改图书库存
 update books set book_stock=stock-m where book_id=b;
 -- 借书成功
 set state=1;
 else
 -- 库存不⾜
 set state=4;
 end if; 
 else
 -- 图书不存在
 set state = 3;
 end if;
 else
 -- 不存在
 set state = 2;
 end if;
end;

-- 调⽤存储过程借书
set @state=0;
call proc_borrow_book('1001',1,2,@state);
select @state from dual;

⑦游标：问题：如果我们要创建⼀个存储 过程，需要返回查询语句查询到的多条数据，该如何实 现呢？游标可以解决该问题：游标可以⽤来依次取出查询结果集中的每⼀条数据——逐条读取查询结果集中的记录。

使用步骤：

1.声明游标

#语法
DECLARE cursor_nanme CURSOR FOR [查询的结果集];

#示例
declare mycursor cursor for select book_name,book_author,book_price from books;

2.打开游标

open mycursor;

3.使用游标

FETCH mycursor INTO bname,bauthor,bprice;

4.关闭游标

CLOSE mycursor;

 5.完整案例

-- 游标使⽤案例
create procedure proc_test2(OUT result varchar(200))
begin
 declare bname varchar(20);
 declare bauthor varchar(20);
 declare bprice decimal(10,2);
 declare num int;
 declare i int;
 declare str varchar(50);
 -- 此查询语句执⾏之后返回的是⼀个结果集（多条记录），使⽤游标可以来遍历查询结果集
declare mycursor cursor for select book_name,book_author,book_price
from books;
 select count(1) INTO num from books;
 -- 打开游标
 open mycursor;
 -- 使⽤游标要结合循环语句
 set i=0;
 while i<num do
 -- 使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）
 FETCH mycursor INTO bname,bauthor,bprice;
 set i=i+1;
 -- set str=concat_ws('~',bname,bauthor,bprice);
 select concat_ws('~',bname,bauthor,bprice) INTO str;
 set result = concat_ws(',',result,str);
 end while;
 -- 关闭游标
 close mycursor;
end;
-- 案例测试
set @r = '';
call proc_test2(@r);
select @r from dual;

21、触发器

想对增删改等操作添加日志记录，比如我在某个时间进行了某个DML操作，将这个事件记录下来，触发器就是实现这个功能。

触发器，就是⼀种特殊的存储过程。触发器和存储过程⼀样是⼀个能够完成特定功能、存储 在数据库服务器上的SQL⽚段，但是触发器⽆需调⽤，当对数据表中的数据执⾏DML操作时 ⾃动触发这个SQL⽚段的执⾏，⽆需⼿动调⽤。 在MySQL,只有执⾏insert\delete\update操作才能触发触发器的执⾏。

#语法
create trigger tri_name
<before|after> -- 定义触发时机
<insert|delete|update> -- 定义DML类型
ON <table_name>
for each row -- 声明为⾏级触发器（只要操作⼀条记录就触发触发器执⾏⼀
次）
sql_statement -- 触发器操作

示例：1.先创建一个日志表

create table logs(
	log_id int primary key auto_increment,
	log_time datetime not null,
	log_text text not null
);

2.测试

#创建触发器
create trigger stu_trigger 
after insert on students for each row
insert into logs (log_time,log_text) values (now(),concat('添加了- students',NEW.stu_num));

#查看触发器
show triggers;

#测试触发器
insert into students (stu_num,stu_name,stu_gender,stu_age) values ('1005','小狗1号','女',20),('1008','小狗2号','男',21);

#删除触发器
drop trigger stu_trigger;

 3.上个示例中在创建触发器时出现的关键字NEW ?

触发器⽤于监听对数据表中数据的insert、delete、update操作，在触发器中通常处理⼀些DML的关联操作；我们可以使⽤ NEW 和 OLD 关键字在触发器中获取触发这个触发器的DML操作的数据。

NEW : 在触发器中⽤于获取insert操作添加的数据、update操作修改后的记录。(添加、修改的哪个数据？)

OLD：在触发器中⽤于获取delete操作删除前的数据、update操作修改前的数据。(删除的哪个数据？)

 insert操作中：NEW表示添加的新记录

create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添
加',NEW.stu_num,'学⽣信息'));

update操作中：NEW 表示修改后的数据

-- 创建触发器 : 在监听update操作的触发器中，可以使⽤NEW获取修改后的数据
create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('修改学⽣信息
为：',NEW.stu_num,NEW.stu_name));

delete操作中：OLD表示删除的记录

create trigger tri_test3
after delete on students for each row
insert into stulogs(time,log_text) values(now(), concat('删
除',OLD.stu_num,'学⽣信息'));

update操作中：OLD表示修改前的记录

create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('将学⽣姓名从
【',OLD.stu_name,'】修改为【',NEW.stu_name,'】'));

4.触发器使用总结

优点

触发器是⾃动执⾏的，当对触发器相关的表执⾏响应的DML操作时⽴即执⾏；

触发器可以实现表中的数据的级联操作（关联操作），有利于保证数据的完整性；

触发器可以对DML操作的数据进⾏更为复杂的合法性校验；

缺点

使⽤触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难； 

⼤量使⽤触发器容易导致代码结构杂乱，增加了程序的复杂性；

当触发器操作的数据量⽐较⼤时，执⾏效率会⼤⼤降低；

使用建议

在互联⽹项⽬中，应避免使用触发器；

对于并发量不⼤的项⽬可以选择使⽤存储过程，但是在互联⽹引⽤中不提倡使⽤存储过程。（原因：存储过程时将实现业务的逻辑交给数据库处理，⼀则增加了数据库的负载， ⼆则不利于数据库的迁移）

22.视图 

视图概念：就是由数据库中⼀张表或者多张表根据特定的条件查询出得数据构造成得虚拟表

视图作用：

安全性：如果我们直接将数据表授权给⽤户操作，那么⽤户可以CRUD数据表中所有数 据，加⼊我们想要对数据表中的部分数据进⾏保护，可以将公开的数据⽣成视图，授权 ⽤户访问视图；⽤户通过查询视图可以获取数据表中公开的数据，从⽽达到将数据表中 的部分数据对⽤户隐藏。

简单性：如果我们需要查询的数据来源于多张数据表，可以使⽤多表连接查询来实现； 我们通过视图将这些连表查询的结果对⽤户开放，⽤户则可以直接通过查询视图获取多 表数据，操作更便捷。

简单的创建视图：

#语法
create view <view_name>
AS
select_statement

#示例1
-- 创建视图实例1：将学⽣表中性别为男的学⽣⽣成⼀个视图
create view view_test1
AS
select * from students where stu_gender='男';
-- 查询视图
select * from view_test1;

#示例2
-- 创建视图示例2：查询学⽣借书的信息（学⽣名、图书名、借书数量）
create view view_test2
AS
select s.stu_name,b.book_name,borrow_num
from books b inner join records r inner join students s
on b.book_id=r.bid and r.snum=s.stu_num;
-- 查询视图
select * from view_test2;

-- 查询视图结构
desc view_test2;

-- ⽅式1
create OR REPLACE view view_test1
AS
select * from students where stu_gender='⼥';
-- ⽅式2
alter view view_test1
AS
select * from students where stu_gender='男';

-- 删除视图
drop view view_test1;

-- 删除数据表时会同时删除数据表中的数据，删除视图时不会影响原数据表中的数据

23.索引

概念：

索引，就是⽤来提⾼数据表中数据的查询效率的。

索引，就是将数据表中某⼀列/某⼏列的值取出来构造成便于查找的结构进⾏存储，⽣成数据表的⽬录当我们进⾏数据查询的时候，则先在⽬录中进⾏查找得到对应的数据的地址，然后再到 数据表中根据地址快速的获取数据记录，避免全表扫描。

分类：

MySQL中的索引，根据创建索引的列的不同，可以分为：

主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有⼀个主键

唯⼀索引：在数据表中的唯⼀列创建的索引(unique)，此列的所有值只能出现⼀次，可以为NULL

普通索引：在普通字段上创建的索引，没有唯⼀性的限制

组合索引：两个及以上字段联合起来创建的索引

说明 : 1. 在创建数据表时，将字段声明为主键（添加主键约束），会⾃动在主键字段创建主键索引；2. 在创建数据表时，将字段声明为唯⼀键（添加唯⼀约束），会⾃动在唯⼀字段创建唯⼀索引；

优点：

索引⼤⼤降低了数据库服务器在执⾏查询操作时扫描的数据，提⾼查询效率

索引可以避免服务器排序、将随机IO编程顺序IO

缺点：

索引是根据数据表列的创建的，当数据表中数据发⽣DML操作时，索引⻚需要更新；

索引⽂件也会占⽤磁盘空间；

注意事项：

数据表中数据不多时，全表扫⾯可能更快，不要使⽤索引；

数据量⼤但是DML操作很频繁时，不建议使⽤索引；

不要在数据重复读⾼的列上创建索引（如：性别）；

创建索引之后，要注意查询SQL语句的编写，避免索引失效。

索引的使用：

#创建索引

-- 创建唯⼀索引: 创建唯⼀索引的列的值不能重复
-- create unique index <index_name> on 表名(列名);
create unique index index_test1 on tb_testindex(tid);

-- 创建普通索引: 不要求创建索引的列的值的唯⼀性
-- create index <index_name> on 表名(列名);
create index index_test2 on tb_testindex(name);

-- 创建组合索引
-- create index <index_name> on 表名(列名1,列名2...);
create index index_test3 on tb_testindex(tid,name);
-- 注意联合索引在使用时，如果条件只调用了联合索引中的其中一个也会触发索引

-- MySQL 5.6 版本新增的索引，可以通过此索引进⾏全⽂检索操作，因为MySQL全⽂检索
-- 不⽀持中⽂，因此这个全⽂索引不被开发者关注，在应⽤开发中通常是通过搜索引擎
-- （数据库中间件如：ES）实现全⽂检索
-- 了解即可
create fulltext index <index_name> on 表名(字段名);

-- 在 命令⾏窗⼝中可以查看查询语句的查询规划：
explain select * from tb_testindex where tid=250000\G;

-- 查看创建索引结构
show create table tb_testindex\G;

-- 查询数据表的索引
show indexes from tb_testindex;
#或
show keys from tb_testindex;

-- 删除索引：索引是建⽴在表的字段上的，不同的表中可能会出现相同名称的索引
-- 因此删除索引时需要指定表名
drop index index_test3 on tb_testindex;

24.事务

事务特性

原⼦性（Atomicity）：⼀个事务中的多个DML操作，要么同时执⾏成功，要么同时执⾏失败

⼀致性（Consistency）：事务执⾏之前和事务执⾏之后，数据库中的数据是⼀致的，完整性 和⼀致性不能被破坏

隔离性（Isolation）：数据库允许多个事务同时执⾏（张三借Java书的同时允许李四借Java 书），多个必⾏的事务之间不能相互影响

持久性（Durability）：事务完整之后，对数据库的操作是永久的

事务管理

在开始事务第⼀个操作之前，执⾏ start transaction 开启事务，依次执⾏事务中的每个DML操作，如果在执⾏的过程中的任何位置出现异常，则执⾏ rollback 回滚事务；如果事务中所有的DML操作都执⾏成功，则在最后执⾏ commit 提交事务；

#示例

-- 借书业务

-- 【开启事务】（关闭⾃动提交---⼿动提交）
start transaction;

-- 操作1：在借书记录表中添加记录
insert into records(snum,bid,borrow_num,is_return,borrow_date)
values('1007',4,2,0,sysdate());

-- 【事务回滚】（清除连接缓存中的操作,撤销当前事务已经执⾏的操作）
-- rollback;

-- 操作2：修改图书库存
update books set book_stock=book_stock-2 where book_id=4;

-- 【提交事务】（将连接缓存中的操作写⼊数据⽂件）
commit;

事务隔离级别

①读未提交（read uncommitted） -- 脏读

脏读：⼀个事务读取到了另⼀个事务中未提交的数据

示例：T2可以读取T1执⾏但未提交的数据；可能会导致出现脏读

②读已提交（read committed） -- 不可重复度（虚读）

不可重复度（虚读）: 在同⼀个事务中，两次查询操作读取到数据不⼀致

示例：T2进⾏第⼀次查询之后在第⼆次查询之前，T1修改并提交了数据，T2进⾏第⼆次 查询时读取到的数据和第⼀次查询读取到数据不⼀致。

③可重复读（repeatable read） -- 幻读

T2执⾏第⼀次查询之后，在事务结束之前其他事务不能修改对应的数据；避免了不可重复读 (虚读)，但可能会导致幻读。

示例：T2对数据表中的数据进⾏修改然后查询，在查询之前T1向数据表中新增了⼀条数 据，就导致T2以为修改了所有数据，但却查询出了与修改不⼀致的数据（T1事务新增的 数据）

④串行化

同时只允许⼀个事务对数据表进⾏操作；避免了脏读、虚读、幻读问题

 设置数据库事务隔离界别

我们可以通过设置数据库默认的事务隔离级别来控制事务之间的隔离性；

也可以通过客户端与数据库连接设置来设置事务间的隔离性（在应⽤程序中设置-- Spring）；

MySQL数据库默认的隔离级别为 可重复读；

#查看MySQL数据库默认的隔离级别

-- 在MySQL8.0.3 之前
select @@tx_isolation;
-- 在MySQL8.0.3 之后
select @@transaction_isolation;

#设置MySQL默认隔离级别
set session transaction isolation level <read committed>;

数据库设计....等后续内容：千锋涛哥Mysql教学

点我mysql免费 0 积分完整笔记pdf下载