一、数据库的基本概念
数据库的英文单词:DataBase(DB)
数据库:用来存储和管理数据的仓库。
数据库的特点:
- 持久化存储数据,其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库---SQL
常见的数据库软件:
![](https://img-blog.csdnimg.cn/20200425132039832.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
-
MySQL:开源免费的数据库,小型的数据库,已经被 Oracle 收购了。MySQL6.x 版本也开始收费。后来 Sun 公司收购了MySQL,而 Sun 公司又被 Oracle 收购
-
Oracle:收费的大型数据库,Oracle 公司的产品。
-
DB2 :IBM 公司的数据库产品,收费的。常应用在银行系统中。
-
SQL Server:MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用。
-
SQLite: 嵌入式的小型数据库,应用在手机端,如:Android。
数据库管理系统,数据库,数据表的关系:
数据库管理程序
(DBMS)
可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体 User
的数据。
![](https://img-blog.csdnimg.cn/2020042513410812.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
二、Mysql数据库的安装,卸载,配置
详见附录
三、SQL(Structured Query Language)
(1)SQL简介
- SQL(Structured Query Language):结构化查询语言,其本质是操作所有关系型数据库的规则。
-
SQL是一种所有关系型数据库的查询规范,不同的数据库都支持,但是每一种数据库操作的方式存在不一样的地方,成为“方言”,比如Mysql特有的方言,Oracle特有的方言。
(2)SQL通用语法
- SQL语句可以单行或者多行书写,以分号结尾
- 可使用空格和缩进来增强语句的可读性
- Mysql数据库的SQL语句不区分大小写,关键字建议使用大写
(3)SQL的分类
-
Data Definition Language (DDL 数据定义语言) 如:建库,建表
-
Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改
-
Data Query Language(DQL 数据查询语言),如:对表中的查询操作
-
Data Control Language(DCL 数据控制语言),如:对用户权限的设置
![](https://img-blog.csdnimg.cn/20200425140254378.bmp?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(4)DDL(操作数据库、表)
(4.1)DDL操作数据库
- C(Create):创建
- R(Retrieve):查询
- U(Update):修改
- D(Delete):删除
- 使用数据库:
C(Create):创建
//语法:
//1.创建数据库
create database 数据库名称;
//2.创建数据库,判断不存在,再创建
create database if not exists 数据库名称;
//3.创建数据库,并指定字符集
create database 数据库名称 character set 字符集名称;
//示例:
//1.直接创建数据库 db1
create database db1;
//2.判断是否存在,如果不存在则创建数据库 db2
create database if not exists db2;
//3.创建数据库并指定字符集为 gbk
create database db3 default character set gbk;
R(Retrieve):查询
语法:
//1.查询所有数据库的名称
show databases;
//2.查询某个数据库的定义信息
show create database 数据库名称;
U(Update):修改
//语法:
//1.修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
//示例:
//1.将db3数据库的字符集改成utf8(注意这里是utf8,而非utf-8)
alter database db3 character set utf8;
D(Delete):删除
//语法:
//1.删除数据库
drop database 数据库名称;
//2.判断数据库存在,再进行删除
drop database 数据库名称 if exists 数据库名称;
//示例:
//1.删除db2数据库
drop database db2;
使用数据库:
//语法:
//1.查询当前正在使用的数据库名称
select database();
//2.使用数据库
use 数据库名称;
//示例:
//1.查看正在使用的数据库
select database();
//2.改变要使用的数据库
use db4;
(4.2)DDL操作数据表
C(Create):创建
常用数据类型:
-
int:整数类型,例如int age;
-
double:小数类型,例如score double(5,2);代表小数类型的score,其中5代表小数最多5位,2代表小数点后保留两位
-
date:日期类型,格式为yyyy-MM-dd,只有年月日,没有时分秒
-
time:时间类型,格式为HH-mm-ss,只有时分秒
-
datetime:日期和时间类型,格式为yyyy-MM-dd HH-mm-ss,同时可以表示日期和时间类型
-
timestamp:时间戳类型,包含年月日时分秒,如果将来不给这个字段赋值或赋值为null,则默认使用当前系统时间来自动赋值
-
char:固定长度字符串
-
varchar:可变长度字符
注意:创建表的时候,最后一个字段后不要加逗号(否则会报错)
//语法:
//1.创建一张表
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
列名3 数据类型3,
......
列名n 数据类型n
);
//2.复制一张表
create table 新表名 like 旧表名;
//示例:
//1.创建student表包含id,name,score,birthday,insert_time字段
create table student (
id int,
name varchar(20),
score double(4,1),
birthday date,
insert_time timestamp
);
//2.快速创建一个与student表结构相同的student1表
create table student1 like student;
R(Retrieve):查询
//语法:
//1.查询某个数据库中的所有表名称
show tables;
//2.查询表结构
desc 表名;
//3.查看创建表的SQL语句
show create table 表名;
//示例:
//1.查看 day21 数据库中的所有表
use day21;
show tables;
//2.查看student表的结构
desc student;
//3.查看student的创建表 SQL 语句
show create table student;
U(Update):修改
//语法:
//1.修改表名
alter table 表名 rename to 新表名;
//2.修改表的字符集
alter table 表名 character set 字符集;
//3.添加一列
alter table 表名 add 列名 数据类型;
//4.修改列名,数据类型
alter table 表名 change 旧列名 新列名 新数据类型;(修改列名以及数据类型)
alter table 表名 modify 列名 新数据类型;(修改数据类型)
//5.删除列
alter table 表名 drop 列名;
//示例:
//1.将s1的表名修改为s2
alter table s1 rename to s2;
//2.将s1的字符集修改为utf8
alter table s1 character set utf8;
//3.在s1表中添加一列remark,类型为varchar(20)
alter table s1 add remark varchar(20);
//4.将s1表中的remark列修改为gender,数据类型为char(2)
alter table s1 change remark gender char(2);
//5.删除s1表中的gender列
alter table s1 drop gender;
D(Delete):删除
//语法:
//1.直接删除表
drop table 表名;
//2.判断表是否存在,如果存在则删除表
drop table if exists 表名;
//示例:
//1.直接删除表s1表
drop table s1;
//2.判断表是否存在并删除s1表
drop table if exists s1;
(5)DML(增删改表中的记录)
(5.1)添加记录
//语法:
//1.添加一条记录
insert into 表名 (列名1,列名2,...列名n) values (值1,值2,...值n);
//示例
//1.向全部字段插入数值
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
//2.向部分字段插入数值
insert into student (name,age) values ('孙悟天', 16);
注意:
- 插入记录的时候,可以插入全部字段数据,也可以插入部分字段数据,但列名要和数值要一一对应,未添加数据的字段会使用null。
- 如果表名后不定义列名,则默认给所有列添加数值,建议不省略列名的写法
- 除了数字类型,其他类型的数值需要使用引号(单引号和双引号都可以)引起来
(5.2)删除记录
//语法:
//1.删除一条记录(有条件)
delete from 表名 [where 条件]
//2.删除全部记录
delete from 表名; //不推荐使用。有多少记录就会执行多少次删除操作。
truncate table 表名; //推荐使用,效率更高。先删除表,然后再创建一张相同的表
//示例:
//1.带条件删除,删除student表中id为1的记录
delete from student where id=1;
//2.不带条件删除,即删除全部记录
delete from student; //逐条删除记录
truncate table student; //先删除表,再创建一张相同的表
注意:
- 如果不加条件,则删除表中所有记录。
- where关键字后加入条件,则按照条件删除记录
(5.3)修改记录
//语法:
//1.修改一条记录
update 表名 set 列名1=值1,列名2=值2,...列名n=值n [where 条件]
//示例
//1.将student表中id为2的学生性别改成男
update student set gender="男" where id=2;
//2.将student表中id为3的学生,年龄改成26岁,address改成北京
update student set age=26,address="北京" where id=3;
注意:
- 如果不加条件,则修改表中所有记录,例如将表中记录的所有性别修改为女性。
- where关键字后加入条件,则按照条件修改记录
(6)DQL(查找表中的记录)
(6.1)基础查询
注意:
- 多个字段查询中,如果查询所有字段,可以使用 * 来替代所有字段
- 可以使用distinct关键字去除重复
- 查询结果参与运算的时候,可以使用函数:ifnull(表达式1,表达式2)将null值参与运算。其中表达式1代表哪个字段需要判断是否为空,表达式2代表如果该字段为null后的替换值
- 可以使用as起别名,也可以省略。起别名可以方便显示,例如计算了数学字段和英语字段的总和,起别名“总和”方便显示。
//语法:
//1.查询多个字段
select 字段1,字段2,字段3,... from 表名;
//2.查询所有字段
select * from 表名;
//3.去除重复值查询
select distinct 字段名 from 表名;
//4.查询结果参与运算
select 列名1+列名2 from 表名;
//5.查询结果参与运算,如果有null值,替换为0后参与运算
select 列名1+ifnull(列名2,0) from 表名;
//6.给查询的字段名起别名
select 列名 as 别名 from 表名;
select 列名 别名 from 表名;
//示例:
//1.查询学生表中的姓名,年龄,地址信息
select name,age,address from student;
//2.查询学生表中的所有的信息
select * from student;
//3.从学生表中,查询学生的的地址(要求地址不重复)
select distinct address from student;
//4.从学生表中,查询学生姓名,数学和英语成绩的总和
select name,math+english from student;
//5.从学生表中,查询学生姓名,数学成绩,英语成绩,数学和英语的总和(英语为null,用0替换)
select name,math,english,math+ifnull(english,0) from student;
//6.从学生表中,查询学生姓名,数学和英语成绩的总和(使用别名)
select name,math+english as 总分 from student;
select name 姓名,math+english 总分 from student;
(6.2)条件查询
如果没有查询条件,则每次查询所有的记录。实际应用中,一般要指定查询的条件,对记录进行过滤。
运算符:
-
>、<、<=、>=、=、<>
- BETWEEN...AND
-
IN(集合)
- LIKE
- IS NULL
-
and 或 &&
-
or 或 ||
-
not 或 !
注意:
- <>表示不等于,在mysql中也可以使用!=
- 在mysql中没有==,可以使用=
- between...and...表示在某个范围内
- in(集合)其中集合可以是多个值,使用逗号隔开
- is null可以用来查询某一个字段为null的值,注意不能写=null
- like表示模糊查询,其中:“_”表示单个任意字符,“%”表示任意多个字符(包含0个)
//示例:
//1.查询年龄大于等于20岁的所有学生记录
select * from student where age>=20;
//2.查询年龄等于20岁的学生记录
select * from student where age=20;
//3.查询年龄不等于20岁的学生记录
select * from student where age!=20;
select * from student where age<>20;
//4.查询年龄大于等于20岁,小于等于30岁的学生记录
select * from student where age>=20 and age<=30;
select * from student where age>=20 && age<=30;
select * from student where age between 20 and 30;
//5.查询年龄是22岁或19岁或25岁的学生记录
select * from student where age=22 or age=19 or age=25;
select * from student where age=22 || age=19 || age=25;
select * from student where age in (22,19,25);
//6.查询年龄不是22岁或19岁或25岁的学生记录
select * from student where age not in (22,19,25);
//7.查询英语成绩为null的学生记录
select * from student where english is null;
//8.查询英语成绩不为null的学生记录
select * from student where english is not null;
//9.查询姓马的所有学生记录
select * from student where name like "马%";
//10.查询名字中,第二个字是“化”的所有学生记录
select * from student where name like "_化%";
//11.查询名字是三个字的所有学生记录
select * from student where name like "___"; //注意:三个下划线
//12.查询名字中包含"德"的所有学生记录
select * from student where name like "%德%";
(6.3)排序查询
//语法:
//1.排序(ASC:升序;DESC:降序)
order by 排序字段1 排序方式1 , 排序字段2 排序方式2...
//示例:
//1.查询学生表,按照数学成绩升序排序
select * from student order by math asc;
//2.查询学生表,按照数学成绩升序排序,如果数学成绩一样,则按照英语成绩升序排序
select * from student order by math asc,english asc;
注意:如果有多个排序条件,如前边的条件值一样,才会判断第二个条件
(6.4)聚合函数
聚合函数:将一列数据作为一个整体,进行纵向的计算
- count:统计个数(一般选择非空列,主键进行计算)
- max:计算最大值
- min:计算最小值
- sum:计算总和
- avg:计算平均值
//示例:
//1.统计学生表中的学生个数
select count(name) from student;
//2.统计学生表中的英语成绩的个数(null值也一起计算)
select count(ifnull(english,0)) from student;
//3.统计数学成绩的最大值
select max(math) from student;
//4.统计英语成绩的最小值(英语成绩中有null值)
select min(ifnull(english,0)) from student;
//5.统计数学成绩的总和
select sum(math) from student;
//6.统计数学成绩的平均值
select avg(math) from student;
注意:聚合函数的计算,排除null值。解决方案:选择不包含非null的列统计;使用ifnull函数。
(6.5)分组查询
GROUP BY
将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。
分组的目的就是为了统计,
一般分组会跟聚合函数一起使用
。
//语法:
//1.分组
group by 分组字段
//示例:
//1.将学生表按照性别分组,分别查询男、女同学的数学平均分
select sex,avg(math) from student group by sex;
//2.将学生表按照性别分组,分别查询男、女同学的人数,数学平均分
select sex,count(id),avg(math) from student group by sex;
//3.将学生表按照性别分组,分别查询男、女同学的数学平均分。要求:分数低于70分的人,不参与分组
select sex,avg(math) from student where math>70 group by sex;
//4.将学生表按照性别分组,分别查询男、女同学的数学平均分,人数。要求:分数低于70分的人,不参与分组,且分组的人数要大于2个人
select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2;
select sex,avg(math),count(id) 人数 from student where math>70 group by sex having 人数>2; //使用别名
注意:
- 分组之后查询的字段应该是分组字段或者聚合函数,例如,按照性别分组之后,可以查看各分组的性别,各分组的聚合函数(最大值,最小值,个数,总和,平均值)
where和having的区别:
(1)where在分组之前限定,如果不满足条件,则不参与分组;having在分组之后限定,如果不满足条件,则不会被查询出来
(2)where后不可以跟聚合函数,having可以进行聚合函数的判断
![](https://img-blog.csdnimg.cn/20200425235734766.png)
分析:
(1)查询年龄大于25岁的人,按性别分组,统计每组的人数
![](https://img-blog.csdnimg.cn/20200425235506452.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(2)查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于 2 的数据
![](https://img-blog.csdnimg.cn/20200425235615755.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
示例:
//将学生表按照性别分组,分别查询男、女同学的数学平均分,人数。要求:分数低于70分的人,不参与分组,且分组的人数要大于2个人
select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2;
select sex,avg(math),count(id) 人数 from student where math>70 group by sex having 人数>2; //使用别名
(6.6)分页查询
LIMIT
是限制的意思,所以
LIMIT
的作用就是限制查询记录的条数。
![](https://img-blog.csdnimg.cn/20200425235911606.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
//语法:
//1.分页
limit 开始的索引,每页查询的条数;
//示例:
//1.查询学生表,每页显示3条记录,查看第一页
select * from student limit 0,3;
//2.1.查询学生表,每页显示3条记录,查看第二页
select * from student limit 3,3;
注意:
- 开始的索引=(当前的页码-1)*每页显示的条数
- limit分页操作是一个"方言",limit语法只能用在mysql数据库
使用场景:
![](https://img-blog.csdnimg.cn/20200426000034738.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(7)DCL(管理用户,授权)
(7.1)管理用户
//1.查询用户(mysql数据库的用户都在mysql数据库的user表中,不过密码经过了加密)
use mysql;
select * from user;
//2.创建用户
create user "用户名"@"主机名" identified by "密码";
create user "zhangsan"@"localhost" identified by "123";
create user "lisi"@"%" identified by "111"; //通配符%表示可以在任意主机使用该用户登录数据库
//3.删除用户
drop user "用户名"@"主机名";
drop user "zhangsan"@"localhost";
//4.修改用户密码
update user set password=password("新密码") where user="用户名";
set password for "用户名"@"localhost" =password("新密码");
//5.mysql中忘记了root用户的密码
第一步:cmd--->net stop mysql(需要使用管理员权限运行cmd,停止mysql服务)
第二步:使用无验证方式启动mysql服务:mysqld --skip-grant-tables
第三步: use mysql;
update user set password=password("新密码") where user="root";
第四步:关闭两个窗口
第五步:打开任务管理器,手动结束mysqld.exe的进程
第六步:启动mysql服务
第七部:使用新密码登陆
(7.2)授权
//1.查询权限
show grants for "用户名"@"主机名";
show grants for "lisi"@"localhost"
show grants for "root"@"%";
//2.授予权限
grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";
grant select on libiary.user to "lisi"@"localhost";
grant select,update,delete on libiary.user to "lisi"@"localhost";
grant all on *.* to "lisi"@"localhost"; //给lisi授予所有权限,在任意数据库的任意表上
//3.撤销权限
revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";
revoke delete on libiary.user from "用户名"@"主机名";
四、约束
概念:对表中的数据进行限定,保证数据的正确性,有效性,完整性。
分类:
- 非空约束:not null
- 唯一约束:unique
- 主键约束:primary key
- 外键约束:foreign key
(1)非空约束(not null)
非空约束:某一列总不能有null
//示例:
//1.在创建表时添加非空约束
create table stu(
id int,
name varchar(20) not null
);
//2.创建表后,添加非空约束
alter table stu modify name varchar(20) not null;
//删除非空约束
alter table stu modify name varchar(20);
(2)唯一约束(unique)
唯一约束:某一列不能有重复值
//示例:
//1.在创建表时添加唯一约束
create table stu(
id int,
phone varchar(20) unique
);
//2.创建表后,添加唯一约束
alter table stu modify phone varchar(20) unique;
//删除唯一约束
alter table stu drop index phone;
注意:mysql中,唯一约束限定的列可以有多个null值。
(3)主键约束(primary key)
主键约束:用来唯一标识数据库中的每一条记录。(非空且唯一,一张表只能有一个字段作为主键)
//示例:
//1.在创建表时添加主键约束
create table stu(
id int primary key,
phone varchar(20)
);
//2.创建表后,添加主键约束
alter table stu add primary key(id);
//删除主键约束
alter table stu drop primary key;
自动增长:如果某一列是数值类型的,使用auto_increment可以来完成值得自动增长
//示例:
//1.在创建表时添加主键约束,并且设置主键自增长
create table stu(
id int primary key auto_increment,
phone varchar(20)
);
//2.创建表后,添加自增长
alter table stu modify id int auto_increment;
//删除自增长
alter table stu modify id int;
注意:主键约束一般和自动增长一起使用。即:在每次插入新记录时,数据库自动生成主键字段的值。
(4)外键约束(foreign key)
(4.1)背景:
- 1.创建单表,如员工表会出现数据冗余(研发部在广州,销售部在深圳,在员工表中表示两者会产生重复数据),而且修改的时候过于麻烦(修改研发部的地址,需要修改多条记录)。
![](https://img-blog.csdnimg.cn/20200426123145344.png)
- 2.为了解决上述问题,可以将员工表拆分成两张表(员工表和部门表),部门表中存储部门名字和部门地址,员工表中存储员工信息,以及员工所属部门。当查看员工部门信息的时候,可以通过员工所属部门id,到部门表中查找具体信息。但是这种方式会出现以下问题:如果删除部门表中的某条记录,员工表没有及时更新,如删除研发部,则在员工表中属于研发部的员工,其部门还是属于研发部。或者在员工表中可以将员工所属部门改变为不存在的部门。
![](https://img-blog.csdnimg.cn/2020042612340173.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
- 3.为了解决上述问题,可以将部门表和员工表关联起来,在添加和删除记录的时候,保证数据的正确性。
![](https://img-blog.csdnimg.cn/20200426123933689.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(4.2)外键约束:使主表和从表产生关系,从而保证数据的正确性。
//语法:
//1.创建表时添加外键
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
...
外键列 数据类型,
constraint 外键名称 foreign key (外键字段名) references 主表名(主表字段名)
);
//2.创建表之后,添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名(主表字段名);
//3.删除外键约束
alter table 表名 drop foreign key 外键名称;
//示例:
//1.在创建表时添加外键约束
//创建主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_address varchar(20)
);
//创建从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
constraint emp_dept_fk foreign key (dep_id) reference department(id)
);
//2.创建表之后,添加外键
alter table employee add constraint emp_dept_fk foreign key(dep_id) references department(id);
//删除外键约束
alter table employee drop foreign key emp_dept_fk;
(4.3)级联操作
级联操作:在修改和删除主表的主键时,同时更新或删除副表的外键值,成为级联操作。
//语法:
//1.添加级联操作
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名(主表字段名) on update cascade on delete cascade;
分类:
- 级联更新:on update cascade
- 级联删除:on delete cascade
注意:级联更新和级联删除可以同时使用,也可以分开使用。
五、数据库的设计
(1)多表之间的关系
- 一对一:如人和身份证的关系,一个人只能有一个身份证,一个身份证只能对应一个人。
- 一对多(多对一):如部门和员工的关系,一个部门有多个员工,一个员工只能对应一个部门。
- 多对多:如学生和课程的关系,一个学生可以选择多门课程,一门课程可以被很多学生选择。
(2)一对一关系的实现
一对一建表原则:可以在任一方添加唯一外键指向另一方的主键。
一对一关系在实际开发中应用不多,因为一对一可以创建成一张表。
(3)一对多关系的实现
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
![](https://img-blog.csdnimg.cn/20200426151258315.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(4)多对多关系的实现
多对多关系建表原则: 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的
主键。
(5)案例分析
背景:用户在途牛网选择旅游线路的时候,首先选择旅游线路的分类,比如:国内游,国外游,周边游等等。选择了旅游线路的分类以后可以选择旅游线路,比如:选择了国外游后可以选择法国游,美国游,瑞士游等等。
分析:上述背景中涉及三个实体类,分别是用户实体,旅游线路分类实体,旅游线路实体。首先分析旅游线路分类和旅游线路的关系:一个旅游分类对应多个旅游线路,而一个旅游线路对应一个旅游分类,所以它们的关系是一对多的关系。然后分析旅游线路和用户的关系:一个用户可以选择多条旅游线路,一条旅游线路可以对应多个用户,所以它们的关系是多对多的关系。最后分析旅游线路分类和用户的关系:用户最终选择的是旅游线路,而跟旅游线路的分类没有直接关系。
表的设计:
(1)旅游线路分类和旅游线路(一对多)
一的一方(旅游线路分类)为主表,多的一方(旅游线路)为副表,在副表中添加外键,指向主表的主键。
//创建旅游分类表
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
)
//创建旅游线路表
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int,
foreign key (cid) references tab_category(cid)
)
(1)用户和旅游线路(多对多)
用户和旅游线路为多对多的关系,需要增加一张中间表来维护它们之间的关系。
//创建用户表
create table tab_user (
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1) default '男',
telephone varchar(11),
email varchar(100)
)
//创建用户和旅游线路的中间表
create table tab_favorite (
rid int,
date datetime,
uid int,
primary key(rid,uid), -- 创建复合主键
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
)
(5)范式
范式:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
通俗理解:第一范式是扫地,第二范式是扫地+擦桌子,第三范式是扫地+擦桌子+擦玻璃。要遵循第三范式,必须遵循前边的范式。
分类:
- 第一范式(1NF)
- 第二范式(2NF):在1NF的基础上。
- 第三范式(3NF):在2NF的基础上。
(5.1)1NF
概念:数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据项。即表中的某个列有多个值
时,必须拆分为不同的列。
简而言之,第一范式每一列不可再拆分,称为原子性。
例如:商品这一项数据又划分为名称和数量两个数据项,故不符合第一范式。
![](https://img-blog.csdnimg.cn/20200426162157526.png)
(5.2)2NF
概念:在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。简而言之,第二范式就是在第一范式的基础上,所有列完全依赖于主键列。
第二范式的特点:一张表只描述一件事情;表中的每一列都完全依赖于主键。
几个概念:
- 函数依赖:如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A。
- 完全函数依赖:如果A是一个属性组,则B属性值的确定需要依赖于A属性组中的所有属性值。
- 部分函数依赖:如果A是一个属性组,则B属性值的确定只需要以来于A属性组中的某一些值即可。
- 传递函数依赖:如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递依赖于A。
例如:
![](https://img-blog.csdnimg.cn/20200426162710516.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(5.3)3NF
概念:在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其他的列间接依赖于主键。简而言之,第三范式就是在第二范式的基础上,所有非主列不得传递依赖于主键。
例如:
![](https://img-blog.csdnimg.cn/20200426163041761.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(5.4)总结
![](https://img-blog.csdnimg.cn/20200426163124586.png)
(6)数据库的备份和还原
导出结果:数据库中的所有表和数据都会导出成
SQL
语句
使用命令行:
//语法:
//1.命令行备份方式
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
//2.命令行还原方式
(1)登陆数据库
(2)创建数据库
(3)使用数据库
(4)执行文件-----source 文件路径
//示例:
//1.备份 day21 数据库中的数据到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sql
//2.还原 day21 数据库中的数据,注意:还原的时候需要先登录 MySQL,并选中对应的数据库。
use day21;
source d:/day21.sql;
使用图形化方式:
//操作
1) 选中数据库,右键 ”备份/导出”
2) 指定导出路径,保存成.sql 文件即可。
![](https://img-blog.csdnimg.cn/20200427155258447.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
![](https://img-blog.csdnimg.cn/2020042715531162.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
//操作
1) 删除数据库
2) 数据库列表区域右键“执行 SQL 脚本”, 指定要执行的 SQL 文件,执行即可
![](https://img-blog.csdnimg.cn/20200427155417658.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
![](https://img-blog.csdnimg.cn/20200427155429541.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
六、多表查询
背景:如有一张员工表,一张部门表,所要查询的数据记录涉及到多张表
// 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
// 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键) )
// 插入部门信息
insert into dept (name) values ('开发部'),('市场部'),('财务部');
// 插入员工信息
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);
笛卡尔积:
- 有两个集合A,B,取这两个集合的所有组成情况构成笛卡尔积。
- 由于笛卡尔积包含所有的数据组合,所以需要过滤掉无用的数据(可以使用内连接查询,外连接查询,子查询)
![](https://img-blog.csdnimg.cn/20200427162041878.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
分类:
(1)内连接查询
分析:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。
![](https://img-blog.csdn.net/20171209135846780?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcGxnMTc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
(1.1)隐式内连接:使用where条件过滤掉无用的数据
示例:
//1.查询所有员工信息和对应的部门信息
select * from emp,dept where emp.dept_id=dept.id;
//2.查询员工表的名称,性别,部门表的名称
select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id=dept.id;
//3.查询员工表的名称,性别,部门表的名称(使用别名)
select
t1.name,t1.gender,t2.name
from
emp t1,detp t2
where
t1.dept_id=t2.id;
(1.2)显式内连接:使用inner join...on语句,可以省略inner
//语法:
select 字段列表 from 表名1 inner join 表名2 on 条件;
//示例:
//1.查询员工表信息和对应的部门信息(确定表连接条件)
select * from emp inner join dept on emp.dept_id=dept.id;
select * from emp join dept on emp.dept_id=dept.id; //inner可以省略
//2.查询唐僧的信息以及对应的部门信息(确定表连接条件,确定查询条件)
select * from emp inner join dept on emp.dept_id=dept.id where emp.name="唐僧";
//3.查询唐僧的员工id,姓名,性别,工资,所在部门名称(确定表连接条件,确定查询条件,确定查询的字段)
select emp.id,emp.name,emp.gender,emp.salary,dept.name from emp inner join dept on emp.dept_id=dept.id where emp.name="唐僧";
内连接查询步骤:
- 确定查询哪些表
- 确定表连接的条件
- 确定查询的条件
- 确定查询的字段
(2)外连接查询
(2.1)左外连接:
- 使用left outer join...on,可以省略outer
- 查询的是左表所有数据以及其交集部分
分析:
左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
![](https://img-blog.csdn.net/20171209142610819?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcGxnMTc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
//语法:
select 字段列表 from 表1 left outer join 表2 on 条件
//示例:
//1.在部门表中增加一个销售部(目前没有员工属于销售部)
insert into dept (name) values ('销售部');
//2.使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;
![](https://img-blog.csdnimg.cn/20200427170908339.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(2.2)右外连接
- 使用right outer join...on,可以省略outer
- 查询的是右表所有数据以及其交集部分
分析:
![](https://img-blog.csdn.net/20171209144056668?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcGxnMTc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
//语法:
select 字段列表 from 表1 right outer join 表2 on 条件
//示例:
//1.在员工表中增加一个员工(目前沙僧没有所属部门)
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
//2.使用右外连接查询
select * from dept right join emp on dept.id = emp.dept_id;
![](https://img-blog.csdnimg.cn/20200427171058714.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
(2.3)总结:
left join (左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
right join (右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join (等值连接或者叫内连接):只返回两个表中连接字段相等的行。
(3)子查询
背景:在员工表查询工资最高的员工信息,需要分两个步骤:首先查询工资最高的员工信息,然后查询最高工资的员工信息。
//1.查询最高的工资是多少(查询结果为9000)
select max(salary) from emp;
//2.查询员工信息,并且工资等于9000
select * from emp where salary=9000;
//3.使用子查询,一条sql语句就可以完成这个操作
select * from emp where salary=(select max(salary) from emp);
子查询的概念:
- 一个查询结果做为另一个查询的条件
- 有查询的嵌套,内部的查询成为子查询
- 子查询要使用括号
子查询的不同情况:
- 子查询的结果是单行单列的
- 子查询的结果是多行单列的
- 子查询的结果是多行多列的
(1)子查询的结果是单行单列的
子查询结果是单行单列,父查询使用运算符(>,<,<>,=等)做判断。
![](https://img-blog.csdnimg.cn/20200427175045863.png)
//示例:
//查询员工工资小于平均工资的人
select * from emp where salary<(select avg(salary) from emp);
//查询工资最高的员工是谁
select name from emp where salary=(select max(salary) from emp);
(2)子查询的结果是多行单列的
子查询结果是单列多行,结果集类似于一个数组,父查询使用in运算符(集合的判断)
![](https://img-blog.csdnimg.cn/20200427175400888.png)
//示例:
//查询财务部和市场部所有的员工信息
select * from emp where dep_id in (select id from dept where name in ("财务部","市场部"));
//查询工资大于5000的员工,来自哪些部门的名字
select name from dept where id in (select dept_id from emp where salary > 5000);
(3)子查询的结果是多行多列的
子查询结果是多行多列,其结果可以作为一张虚拟表,即在from后面作为表
注意:子查询作为表需要起别名,否则这张表没有名称无法访问表中的字段。
![](https://img-blog.csdnimg.cn/20200427214118115.png)
//查询2011年以后入职的员工信息,包括部门信息
//1.采用子查询
select * from dept d,(select * from emp where join_date>="2011-1-1") e where d.id=e.dept_id;
//2.采用内连接
select * from emp inner join dept on emp.dept_id=dept.id where join_date>="2011-1-1";
select * from emp t1,dept t2 where t1.dept_id=t2.id and t1.join_date>"2011-1-1";
总结:
- 子查询结果是单行单列,则在where后面作为条件,使用逻辑运算符判断
- 子查询结果是多行单列,则在where后面作为条件,使用in关键字(in指在集合中)
- 子查询结果是多行多列,则在from后面作为虚拟表进行二次查询
(4)多表查询练习
表结构:
![](https://img-blog.csdnimg.cn/20200427233055256.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
创建表:
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表,职务名称,职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司,接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
操作:
//1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
//分析:员工编号,员工姓名,工资需要查询emp表,职务名称,职务描述需要查询job表
//查询条件:t1.job_id=t2.id
SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description
FROM
emp t1,job t2
WHERE
t1.job_id=t2.id;
//2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
//分析:员工编号,员工姓名,工资需要查询emp表,职务名称,职务描述需要查询job表,部门名称,部门位置需要查询dept表
//查询条件:t1.job_id=t2.id AND t1.dept_id=t3.id
SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc
FROM
emp t1,job t2,dept t3
WHERE
t1.job_id=t2.id AND t1.dept_id=t3.id;
//3.--查询员工姓名,工资,工资等级
//分析:员工姓名,工资需要查询emp表,工资等级需要查询salarygrade表
//条件:t1.salary BETWEEN t2.losalary AND t2.hisalary
SELECT
t1.ename,t1.salary,t2.grade
FROM
emp t1,salarygrade t2
WHERE
t1.salary
BETWEEN
t2.losalary
AND
t2.hisalary;
//4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
//分析:员工姓名,工资需要查询emp表,职务名称,职务描述需要查询job表,部门名称,部门位置需要查询dept表,工资等级需要查询salarygrade表
//条件:t1.job_id=t2.id AND t1.dept_id=t3.id AND t1.salary BETWEEN t4.losalary AND t4.hisalary
SELECT
t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc,t4.grade
FROM
emp t1,job t2,dept t3,salarygrade t4
WHERE
t1.job_id=t2.id AND t1.dept_id=t3.id AND t1.salary BETWEEN t4.losalary AND t4.hisalary;
//5.查询出部门编号、部门名称、部门位置、部门人数
//分析:部门编号,部门名称,部门位置需要查询dept表,部门人数需要查询emp表(统计dept_id字段数)
//步骤1:使用分组查询,按照t2.dept_id完成分组,查询count(id),并起别名total;
//步骤2:使用子查询将上一步的查询结果和dept表进行关联查询
SELECT
t1.id,t1.dname,t1.loc,t2.total
FROM
dept t1,(SELECT dept_id,COUNT(id) total FROM emp GROUP BY emp.dept_id ) t2
WHERE
t1.id=t2.dept_id;
//6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
//分析:员工姓名需要查询emp表,直接上级的姓名也需要查询emp表(emp表的id和mgr是自关联的)
//分析:没有领导的员工也需要查询,即查询左表的所有数据和交集数据,使用左外连接
SELECT
t1.ename,t2.ename
FROM
emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgr=t2.id;
七、事务
(1)事务的基本介绍
如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。比如在转账业务中,一个用户扣钱,一个用户加钱,如果其中有一条SQL语句出现异常,这条SQL就可能执行失败。而事务的执行是一个整体,所有的SQL语句都必须执行成功,如果其中有1条SQL语句出现异常,则所有的SQL语句都要回滚,整个业务执行失败。
原理:
事务开启之后,所有的操作都会临时保存到事务日志中,事务日志只有在得到commit命令之后才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)。
- 客户端连接数据库服务器,创建连接时创建此用户临时日志文件
- 开启事务之后,所有的操作都会先写入到临时日志文件中
- 所有的查询操作从表中查询,但会经过日志文件加工后才返回
- 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件
![](https://img-blog.csdnimg.cn/20200428225737936.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
操作流程:
事务提交的两种方式:
- 第一种方式(自动提交),mysql数据库中事务默认自动提交,一条DML语句会自动提交一次事务。
- 第二种方式(手动提交),需要先开启事务,再提交(orale数据库默认手动提交)
修改事务的默认提交方式:
- 查看事务的默认提交方式:select @@autocommit;-----1代表自动提交,0代表手动提交
- 修改事务的提交方式:set @@autocommit=0;
手动提交事务使用流程:
![](https://img-blog.csdnimg.cn/20200428225452431.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
注意:
开启事务以后,就需要手动提交,否则数据不是持久化的(关闭窗口,数据还原)
比如开启事务以后,对数据库表进行修改,如果没有commit提交,则修改的结果在关闭窗口以后还原。
//语法:
//1.开启事务
start transaction;
//2.提交事务
commit;
//3.回滚事务
rollback;
//示例:张三给李四转账500元
create table account(
id int primary key auto_increment,
name varchar(20),
balance double
);
insert into account (name,balance) values ("zhangsan",1000);
insert into account (name,balance) values ("lisi",1000);
start transaction; //开启事务
update account set balance=500 where name="zhangsan";
update account set balance=1500 where name="lisi";
rollback; //回滚
commit; //提交
(2)事务的四大特征(重要)
- 原子性(Atomicity):事务是不可分割的最小操作单元,事务中的SQL语句要么同时成功,要么同时失败。
- 一致性(Consistency):事务操作前后,数据总量不变。
- 隔离性(Isolation):事务与事务之间不应该相互影响,执行时保持隔离的状态。
- 持久性(Durability):当事务提交或回滚后,数据库会持久化的保存数据。
(3)事务的隔离级别(了解)
概念:
事务在操作时的理想状态是所有的事务之间保持隔离,互不影响。因为并发操作,多个用户同时访问同一个数据,可能引发并发访问的问题,通过设置不同的隔离级别可以解决这些问题。
存在的问题:
- 脏读:一个事务,读取到另一个事务没有提交的数据。
- 不可重复读(虚读):一个事务中两次读取到的数据不一样。
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
![](https://img-blog.csdnimg.cn/20200428222347143.png)
隔离级别:(隔离级别越高,性能越差,安全性越高)
- read uncommitted:读未提交,产生的问题:脏读,不可重复读,幻读。
- read committed:读已提交,产生的问题:不可重复读,幻读。(Oracle默认)
- repeatdable read:可重复读,产生的问题:幻读。(Mysql默认)
- serializable:串行化,类似于加锁的操作,可以解决所有的问题。
![](https://img-blog.csdnimg.cn/20200428223349790.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
隔离级别操作:
//数据库查询隔离级别
select @@tx_isolation;
//数据库设置隔离级别
set global transaction isolation level 隔离级别字符串;
八、索引
1、什么是索引
- MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。即索引的本质是数据结构。
- 索引的目的在于提高查询效率,类比字典。如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往上找到y字母,再找剩下的sql即可。如果没有索引,那么我们可能需要从a到z,逐一进行查找。所以,索引的本质是“排好序的快速查找数据结构”。(排序+查找的两大功能)
- 除数据本身之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下面就是一种索引方式示例:
![](https://img-blog.csdnimg.cn/20200810101157211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
- 左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。为了加快col2的查找,可以维护一个右边所示的二叉查找树,每个结点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应的数据,从而快速的检索出符合条件的记录。
- 一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。
- 我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉)结构组织的索引。其中聚合索引,次要索引,复合索引,前缀索引,唯一索引默认都是使用B+树,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引等。
2.索引的优势
- 类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
3.索引的劣势
- 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,索引索引列也是要占用空间的。
- 虽然索引大大提高了查询速度,同时却降低更新表的速度,如对表进行insert,update,delete等。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询。
4.MySQL索引分类
- 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引。
- 唯一索引:索引列的值必须唯一,但允许空值。
- 复合索引:即一个索引包含多个列。
基本语法:
//创建
create [unique] index indexName on mytable(columnname(length));
alter mytable add [unique] index [indexname] on (columnname(length));
//删除
drop index [indexname] on mytable;
//查看
show index from mytable;
//使用alter命令(有四种方式来添加数据表的索引)
alter table tbl_name add primary key(column_list); //该语句添加一个主键,这意味着索引值必须是唯一的,且不能为空
alter table tbl_name add unique index_name(column_list); //这条语句创建索引的值必须是唯一的(除了null外,null可能会出现多次)
alter table tbl_name add index_name(colunm_list); //添加普通索引,索引值可出现多次
alter table tbl_name add fulltext index_name(column_list); //该语句指定了索引为fulltext,用于全文检索
5.MySQL索引结构
检索原理:
初始化介绍:一棵B+树,浅蓝色的块我们称之为一个磁盘块,我们看到每个磁盘块包含几个数据项(深蓝色所以)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1,P2,P3。其中P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子结点,即3,5,9,10,13,15,28,29,36,60,75,79,90,99。非叶子结点不存储真实的数据,只存储指引搜索方向的数据项,如17,35并不真实存在于数据表中!
查找过程:如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比于磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找,找到29,结束查询,总计三次IO。
真实的情况是3层的B+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提升将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
![](https://img-blog.csdnimg.cn/20200810105302261.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjcxNzQx,size_16,color_FFFFFF,t_70)
- Hash索引
- full-text索引
- R-Tree树索引
6.哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引(比如银行系统的银行账号,电信系统的手机号,微信系统的微信号)
- 查询中与其他表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录,还会更新索引
- where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询汇总统计或者分组的字段
7.哪些情况不要创建索引
- 表记录太少
- 经常增删改的表(提高了查询速度,同时却会降低更新表的速度,如对表进行insert,update,delete。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件)
- 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果(例如为性别的列建立索引就没有必要)。假如一个表有10万行记录,有一个字段A只有true和false两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。