发表于 更新于 分类于 本文字数： 38k 阅读时长 ≈ 34 分钟

1 数据库分类

数据库本质就是一款cs架构软件,分为 关系型数据库和非关系型数据库

  • 关系型数据库:固定表结构并且表与表之间可以建关系
    • MySQL、PostgreSQL、sqlite，db2，oracle，access，sql server…
  • 非关系型数据库：没有固定表结构并且存储数据以kv键值对形式
    • mongodb，redis，memcache…

2 MySQL简介

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
"""
本质也是一款基于网络通信的应用程序
"""
任何基于网络通信的软件底层肯定都是socket
简单地说MySQL的架构如下：
	-服务端
		-基于socket通信
		-本地文件操作
		-解析指令(SQL语句)  

	-客户端
		-基于socket通信
		-发送指令
		-解析指令(SQL语句)

SQL语句的由来
"""
MySQL不单单支持MySQL客户端操作，也支持其他编程语言来操作。python、java、php、c++、go语法都不一样 那MySQL如何做到兼容的呢？
这里有两种解决语言障碍的思路：
		1.自己精通多国语言 轻松自如的切换
		2.采用统一的语言(SQL语句)
"""

3 安装与配置

3.1 重要概念介绍

数据库里的数据到底是是以什么格式存储的呢，我们拿它和我们电脑里的文件做一个对比：

1
2
3
4
5
6
7
8
"""
库			>>>			  文件夹
表			>>>			  文件夹里面的文件
记录	 >>>			 文件内一行行的数据

表头	>>>				  表格的第一行数据
字段	>>>					字段名+字段类型
"""

3.2 下载与安装

1
2
3
4
5
6
7
8
9
10
11
1.版本问题
	5.6  这里我们安装的是5.6版本
  5.7
  5.8
  
2.下载
	官网下载压缩包
  解压到固定地址
  简单启动
    	mysql.exe	   客户端
      mysqld.exe	 服务端

3.3 做成系统服务

步骤如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
## 本步骤是针对的windows用户
1.环境变量添加
2.系统服务制作(全部在管理员身份下操作)
	# 查看系统服务
    services.msc
	# 制作成系统服务
    mysqld --install
    # 取消系统服务
    mysqld --remove
    # 启动服务
    net start mysql
    # 停止服务
    net stop mysql

3.4 用户登录

1
2
3
4
5
6
7
8
9
10
11
mysql -u用户名 -p密码  # 管理员初次登录无需密码
# mysql游客模式		

# 忘记密码
	1.跳过授权表启动
		mysqld --skip-grant-tables
  2.无密码登录
    mysql -uroot -p
  3.修改用户密码
    update mysql.user set password=password(123) where user="root" and host="localhost";  # 修改对应用户的密码
	4.退出正常启动

3.5 配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL默认配置文件my-default.ini不能修改
拷贝并重命名my.ini

# \s查看基本信息

文件内容
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

4 基本SQL语句

库、表、记录的增、删、改、查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
##########################  库  ###############################
# 增
create database db1 charset='utf8';
# 改
alter database db1 charset='gbk';
# 查
show databases;
show create database db1;
# 删
drop database db1;


##########################   表   ###############################
user db1;  # 切换文件夹
select database();  # 查看当前所在的库
# 增
create table t1(id int,name char(4));  # 创建出来的可能是多个文件，解耦管理
# 改
alter table t1 modify name char(16);
# 查
show tables;
show create table t1;		# 查看表的详细信息
describe t1; == desc t1;  # 查看表结构
# 删
drop table t1;

##########################   记录   ###############################
# 增
insert into db1.t1 values (1,'tom');  # 单
insert into db1.t1 values (2,'tom'),(3,'tony'),(4,'jason');  # 多
# 改
update db1.t1 set name='DSB' where id > 1;
update db1.t1 set name='DSB' where id = 2 or id = 3;
# 查
select id,name from db1.t1;  # db1可不指定，默认当前库下
select * from t1;
# 删
delete from db1.t1 where id >3;
delete from db1.t1 where name='jason'; 
delete from db1.t1;  # 将t1表所有的数据都删除

5 存储引擎

存储引擎决定了一个数据库存取数据的方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# 查看所有的存储引擎
show engines;

# 需要知道的有四个
MyISAM
	  MySQL5.5及之前版本默认的存储引擎
    存取数据的速度都非常快 但是功能较少安全性较低
InnoDB
	  MySQL5.6及之后版本默认的存储引擎
    存取数据速度没有MyISAM快 但是功能更多 安全性更高
    支持：事务 行锁 外键
BlackHole
	  往里面写入的任何数据都会直接消失
Memory
	  内存存储数据
  
# 存储数据的特点
create table t1(id int) engine=InnoDB;
create table t2(id int) engine=MyISAM;
create table t3(id int) engine=BlackHole;
create table t4(id int) engine=Memory;
    
# 文件个数不一致
InnoDB
	  .frm	表结构
    .ibd	表数据和索引(类似于书的目录)
MyISAM
	  .frm	表结构
    .MYD	表数据
    .MYI	索引(类似于书的目录)
BlackHole
	  .frm	表结构
Memory
	  .frm	表结构
    
# 验证
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
"""乐观锁与悲观锁"""

6 MySQL基本数据类型

6.1 类型分类

6.1.1 整型

image-20210427153114123

1
2
3
4
5
6
7
8
9
10
11
tinyint smallint int bigint
1.不同的类型能够存储的数字范围不一样
 2.验证是否自带符号
   	create table t5(id tinyint);
     insert into t5 values(-129),(256);
       # 结论:所有的整型都是自带符号
   """
   如果不想让整型有符号
   	create table t6(id tinyint unsigned);
   	insert into t6 values(-129),(256);
   """

6.1.2 浮点型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
float(255,30)
   	总共255位小数占30位
 double(255,30)
   	总共255位小数占30位
 decimal(65,30)
   	总共65位小数占30位
   
 create table t7(id float(255,30));
 create table t8(id double(255,30));
 create table t9(id decimal(65,30));
 insert into t7 values(1.111111111111111111111111111111);
 insert into t8 values(1.111111111111111111111111111111);
 insert into t9 values(1.111111111111111111111111111111);
 
"""
精确度不一样
	float < double < decimal
	
在实际开发中 很多时候看似需要用数字存储的数据
其实我们可能都是使用的字符串存储
"""

6.1.3字符类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
char(4)
   	最大存储四个字符 超出了直接报错 不超过空格填充至四位
 varchar(4)
   	最大存储四个字符 超出了直接报错 不超过有几位存几位
   create table t10(id int,name char(4));
   create table t11(id int,name varchar(4));
   insert into t10 values(1,'jason');
   insert into t11 values(1,'jason');
   
   # 由于我们修改了配置并且没有指定sql_mode
  	"""
  	1.拷贝sql_mode到自己的配置文件
  	2.修改
  	"""

6.1.4 日期类型

1
2
3
4
5
6
7
8
9
10
11
12
13
datetime	年月日 时分秒
 time			时分秒
 date			年月日
 year			年
   create table t13(
   	id int,
       name varchar(32),
       reg_time datetime,
       study_time time,
       birth date,
       r_time year
   );
   insert into t13 values(1,'jason','2000-11-11 11:11:11','11:11:11','2000-01-21','1995');

6.1.5 枚举与集合

1
2
3
4
5
6
7
8
9
10
11
12
13
枚举		多选一
   	create table t14(
       	id int,
           name char(16),
           gender enum('male','female','others')
       );
   
   集合		多选多(多选一)
   	create table t15(
       	id int,
           name char(32),
           hobby set('read','study','music','tea')
       );

6.2 严格模式

1
2
3
4
5
6
7
8
9
10
# 查看sql_mode
show variables like '%mode%';

# 修改
set global sql_mode='strict_trans_tables';

# 退出客户端重新进入即可

# 验证是否自动填充
set global sql_mode='strict_trans_tables,pad_char_to_full_length';

6.3 字符类型对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
char(4)
	  优点:整存整取 速度快
    缺点:浪费硬盘空间
varchar(4)
	  优点:节省硬盘空间
    缺点:速度没有char快
    
char(5) varchar(5)
jasonkevintony egon jack tom
1bytes+jason1bytes+kevin1bytes+tony1bytes+egon1bytes+jack1bytes+tom 

# 到底用char还是varchar?
	各有优缺点 具体结合实际情况
    
"""
针对整型字段 括号里面的数字不是用来限制存储长度而是用来控制展示长度
ps:如果不理解 直接记结论
	定义整型字段无需自己写括号和数字
	int
	tinyint
	
	create table t12(id int(4) zerofill);
	insert into t12 values(1),(11111111);
"""

6.4 创建表的完整语法

1
2
3
4
5
6
7
8
9
10
11
create table 表名(
	字段名1 字段类型(宽度) 约束条件,
    字段名2 字段类型(宽度) 约束条件,
    字段名3 字段类型(宽度) 约束条件
)
"""
1.字段名和字段类型是必须的 宽度和约束条件是可选的
2.约束条件可以有多个
	字段名1 字段类型(宽度) 约束条件1 约束条件2 约束条件3
3.最后一个字段结尾不能加逗号
"""

7 约束条件

在字段类型的基础之上添加的额外约束

7.1 分类

unsigned 、zerofill、not null、default、unique、primary key、auto_increment

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
#1.unsgined
	无符号(正负号)
#2.zerofill
	填充
	
#3.not null
	非空
    create table t2(
    	id int,
        name varchar(32) not null
    );
    
#4.default
	默认值
    create table t3(
    	id int,
        name char(32),
        gender enum('male','female','others') default 'male'
    );
    
#5.unique
	唯一
    # 单列唯一
    create table t4(
    	id int,
        name varchar(32) unique
    );
    # 多列唯一
    create table t5(
    	id int,
        host varchar(32),
        port int,
        unique(host,port)
    );
#6.primary key
主键
"""
1.单从限制角度上来看 主键相当于是非空且唯一
id int primary key == id int not null unique
create table t6(
id int primary key
);
2.InnoDB存储引擎规定了在创建表的时候必须有且只有一个主键
1.为什么之前创建表的时候没有主键也可以呢？
当表中没有任何的约束条件字段，为了能够顺利的创建出表，InnoDB存储引擎会采用内部隐藏的字段作为主键
隐藏意味着我们无法使用到，主键其实可以帮助我们加快数据的查询速度
2.没有主键但是有非空且唯一的字段 那么会自动将该字段升级为主键
create table t7(
id int,
age int not null unique,
pwd int not null unique
);
结论:我们在创建表的时候一般都会创建一个id字段(序号\编号)
并且我们会将该字段设置为主键
id int primary key
ps:id名称可以不固定 uid cid pid 
"""

#7.auto_increment
	自增
    # 单一主键
    专门用来给key键使用(你就理解为是专门给主键用的)
    create table t8(
    	id int primary key auto_increment,
        name varchar(32)
    );
    # 联合主键
    create table t8(
    	id int,
        nid int,
        name varchar(32),
        primary key(id,nid)...
    );
"""以后的主键字段固定写法"""
	id int primary key auto_increment

7.2 主键的特征

1
2
3
4
# 1.delete from
	不会影响主键的自增
# 2.truncate
	清空表数据并且重置主键

7.3 外键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'''员工表'''
id emp_name emp_salary dep_name dep_desc
# 问题
1.表结构不够明确(忽略)
	到底是员工表还是部门表
2.浪费存储空间(忽略)
	硬盘不值钱 不够花钱买
3.表扩展性极差(不能忽略)
	
# 拆分
上述三个问题都能够解决 但是员工与部门之间没有了关联

外键字段
	用来建立表与表之间的关系的字段

7.3.1 表关系

表关系分类

  • 1.一对多
  • 2.多对多
  • 3.一对一
  • 4.没有关系

表关系判断方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#1.以员工表与部门表为例
1.先站在员工表角度
问:一名员工能否对应多个部门
答:不可以

2.再站在部门表角度
问:一个部门能否对应多名员工
答:可以

结论:两边一个可以一个不可以 那么表关系就是"一对多"
'''
针对一对多表关系
外键字段建在多的一方(建在员工表中)
'''
	
	
#2.以书籍表与作者表为例
1.先站在书籍表角度
问:一本书能否对应多名作者
答:可以
2.再站在作者表角度
问:一名作者能否对应多本书
答:可以
结论:两边都可以 那么表关系就是"多对多"

"""
针对多对多表关系
外键字段并不会建在任何一张关系表
而是单独开设一张新表专门存储
"""
	
#3.以用户表与用户详情表为例
1.先站在用户表角度
问:一名用户能否对应多个用户详情
答:不可以
2.再站在用户详情表角度
问:一个用户详情能否对应多名用户
答:不可以
结论:两边都不可以 那么要么没关系 要么"一对一"

"""
针对一对一表关系
外键字段建在任何一方都可以
但是推荐你建在查询频率较高的表中
"""

7.3.2 SQL语句建立表关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
############################一对多#########################
# 1.先创建基本字段
# 2.再考虑外键字段
create table emp(
	id int primary key auto_increment,
    emp_name varchar(32),
    emp_salary int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)    
);
"""
1.在创建表的是先创建被关联的表(没有外键字段的表)
2.在录入数据的时候先录入被关联表的数据
3.修改或者删除被关联表数据
"""


####################多对多######################
create table book(
	id int primary key auto_increment,
    title varchar(32),
    price float(8,2) 
);
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int  
);
create table book2author(
	id int primary key auto_increment,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);


######################一对一####################
create table userinfo(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    detail_id int unique,
    foreign key(detail_id) references userinfo_detail(id)
    on update cascade
    on delete cascade
);
create table userinfo_detail(
	id int primary key auto_increment,
    addr varchar(32),
    phone bigint
);

7.3.3 级联更新级联删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)    
);
create table emp(
	id int primary key auto_increment,
    emp_name varchar(32),
    emp_salary int,
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

作业

1
2
3
4
5
6
7
8
1.书籍表与出版社表
2.课程表与老师表
3.班级表与学生表
4.作者表与作者详情表
"""
书写出完整的判断过程
并且写上对应的SQL语句
"""

8 查询关键字

**首先准备一个表

1
s

8.1 select from where

1
2
3
4
5
6
7
8
9
10
11
12
13
1.select
	用来指定表的字段数据
    select * from emp;
    select id,name from emp;
    """
    在工作中一般很少使用*号 我们只是为了教学方便
    """
 
2.from
	后面跟需要查询的表名即可
    
3.where
	筛选数据

8.2 查询关键字之where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6;  

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 简写

"""
模糊查询
	关键字  
		like
	关键符号
		%:匹配任意个数的任意字符
		_:匹配单个个数的任意字符
	show variables like '%mode%';
"""
# 3.查询姓名中带有字母o的员工姓名和薪资
select name,salary from emp where name like '%o%';

# 4.查询姓名由四个字符组成的员工姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) =4;


# 5.查询id小于3或者大于6的数据
select *  from emp where id not between 3 and 6;

# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

8.3 查询关键字之group by分组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
分组
	将单个单个的个体按照指定的条件分成一个个整体

"""
分组之后默认只能直接获取到分组的依据
其他字段无法再直接获取(可以间接获取)
"""
# 严格模式
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'



# 1.每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.每个部门的人数
select post,count(id) from emp group by post;
# 5.每个部门的月工资总和
select post,sum(salary) from emp group by post;

"""
可以给字段起别名(as还可以给表起别名)
select post as '部门',sum(salary) as '总和' from emp group by post;
"""
# 查询分组之后的部门名称和每个部门下所有的员工姓名
"""
group_concat()  获取分组以外的字段数据 并且支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;

concat()  未分组之前使用的拼接功能
select concat(name,':',sex) from emp;

concat_ws()
select concat_ws(':',name,sex,salary,age) from emp;
"""

8.4 聚合函数

1
2
3
4
5
6
分组之后频繁需要使用的
	max		最大值
    min		最小值
    sum		求和
    count	计数
    avg		平均值

作业

1
2
3
4
5
6
7
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

8.5 查询关键字之having过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
功能上having与where是一模一样的
但是使用位置上有所不同
	where在分组之前使用
    having在分组之后使用
 
1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
# 1.先筛选出所有30岁以上的员工
	select * from emp where age>30;
# 2.然后再按照部门分组
	'''SQL语句的查询结构我们也可以直接看成是一张表'''
	select post,avg(salary) from emp where age>30 group by post;
# 3.分组之后做过滤操作
	select post,avg(salary) from emp 
    	where age>30 
        group by post 
        having avg(salary)>10000
        ;

8.6 查询关键字之distinct去重

1
2
3
去重有一个非常严格的前提条件 数据必须是完全一样
	如果数据带有主键那么肯定无法去重
select distinct age from emp;

8.7 查询关键字之order by排序

1
2
3
4
5
6
7
8
9
10
11
12
13
select * from emp order by salary;  # 默认是升序
select * from emp order by salary asc;  # 升序关键字 可以不写
select * from emp order by salary desc;  # 降序

# 排序也可以指定多个字段
select * from emp order by age desc,salary asc;

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
	select post,avg(salary) from emp 
    	where age>10
        group by post
        having avg(salary)>1000
        order by avg(salary);

8.8 查询关键字之limit分页

1
2
3
4
5
6
7
用来限制数据的展示条数
select * from emp limit 5;  # 前五条
select * from emp limit 5,5;  # 起始位置、条数

# 查询工资最高的人的详细信息
	# 先按照工资排序 然后限制展示条数
select * from emp order by salary desc limit 1;

8.9 查询关键字之regexp正则

1
2
3
4
5
正则表达式
	用一些特殊符号的组合去字符串中筛选出符合条件的数据
   	
select * from emp where name regexp '^j.*(n|y)$';
# '^j.*(n|y)$'  j开头 中间无所谓 n或者y结尾

9 多表查询

9.1 多表查询思想

1
2
3
4
5
6
7
1.子查询
	分步解决问题
    将一条SQL语句的查询结果用括号括起来当作另外一条SQL语句的查询条件
 
2.连表操作
	先将所有需要用到的表拼接到一起(一张表)
    然后就是转换成单表查询

9.2 前期表准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#建表
create table dep(
id int primary key auto_increment,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

9.3 子查询

1
2
3
4
5
6
7
# 查询jason所在的部门名称
	# 第一步 先获取jason所在的部门id
    select dep_id from emp where name='jason';
   	# 第二步 根据id号去部门表中筛选
    select * from dep where id = 200;
    # 完整句式
    select * from dep where id=(select dep_id from emp where name='jason');

9.4 连表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 前戏(了解)
select * from emp,dep;
# 基于上表筛选数据(了解)
	'''为了避免字段冲突 可以在字段名前面加表名明确'''
select * from emp,dep where emp.dep_id=dep.id;


########################掌握############################
inner join	内连接	拼接公共的部分
	select * from emp inner join dep on emp.dep_id=dep.id;
left join	左连接 以左表为基准展示所有数据 没有的null填充
	select * from emp left join dep on emp.dep_id=dep.id;
right join	右连接 以右表为基准展示所有数据 没有的null填充
	select * from emp right join dep on emp.dep_id=dep.id;
union	全连接
	select * from emp left join dep on emp.dep_id=dep.id
    union
    select * from emp right join dep on emp.dep_id=dep.id;

9.5 Navicat可视化软件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
可以充当很多数据库软件的客户端 封装了很多快捷方法

该软件默认也是收费的 需要破解
正版不破解免费试用14天
破解版(老版本):https://pan.baidu.com/s/1bpo5mqj

1.下载与安装
2.使用方法
	创建库 表 记录
    	注意主键
     外键字段
    逆向数据库到模型
    转储SQL文件
  	查询

9.6 多表查询SQL文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

9.7 多表查询题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
1、 查询所有的课程的名称以及对应的任课老师姓名
4、 查询平均成绩大于八十分的同学的姓名和平均成绩
7、 查询没有报李平老师课的学生姓名
8、 查询没有同时选修物理课程和体育课程的学生姓名
9、 查询挂科超过两门(包括两门)的学生姓名和班级

#####################关键字习惯都用大写###############################
# 建议:在书写SQL语句的时候一定不要想着一次性成功 写一点看一点再写一点  慢慢拼凑起来
-- 1、 查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要的表	course表 teacher表
-- 	select * from course;
-- 	select * from teacher;
# 2.连表操作 明确字段
-- SELECT
-- 	course.cname,
-- 	teacher.tname
-- FROM
-- 	course
-- 	INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、 查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先查看成绩表 
-- 	select * from score;
# 2.求所有学生的平均成绩
-- 	select score.student_id,avg(num) from score group by score.student_id;
# 3.筛选出大于80分
-- 	select score.student_id,avg(num) as 'avg_num' from score group by score.student_id having avg(num)>80
-- 	;
# 4.学生表与上述查询出来的表连接
-- SELECT
-- 	student.sname,
-- 	t1.avg_num 
-- FROM
-- 	student
-- 	INNER JOIN ( SELECT score.student_id, avg( num ) AS 'avg_num' FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;
-- 7、 查询没有报李平老师课的学生姓名
# 1.正向思路:课下可以尝试一下
# 2.反向思路:先找所有报了李平老师课程的学生 再取反
# 1.先查询李平老师教授的课程id号
-- select tid from teacher WHERE tname='李平老师';
-- select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师');
# 2.去成绩表中筛选出所有报了李平老师课程的学生id号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师')); 
# 3.去学生表中 取反获取没有报李平老师课程的学生姓名
-- SELECT
-- 	sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid NOT IN (
-- 	SELECT DISTINCT
-- 		student_id 
-- 	FROM
-- 		score 
-- 	WHERE
-- 		course_id IN (
-- 		SELECT
-- 			cid 
-- 		FROM
-- 			course 
-- 		WHERE
-- 		teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门和都不报都不要)
# 1.先查询物理 和 体育课程的id号
-- 	select cid from course where cname in ('物理','体育');
# 2.去成绩表中先筛选出所有报了课程的数据(报了一门 报了两门)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 3.按照学生id分组 统计每个学生报了的课程数目
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- 	group by student_id
-- 	having count(course_id) = 1
-- ;
# 4.去学生表中根据id获取学生姓名
-- SELECT
-- 	sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid IN (
-- 	SELECT
-- 		student_id 
-- 	FROM
-- 		score 
-- 	WHERE
-- 		course_id IN (
-- 		SELECT
-- 			cid 
-- 		FROM
-- 			course 
-- 		WHERE
-- 		cname IN ( '物理', '体育' ))
-- 	group by student_id
-- 	having count(course_id) = 1);
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先去成绩表中 筛选出分数小于60分的数据
-- select * from score where num<60;
# 2.按照学生id分组 然后统计个数
-- select student_id from score where num<60 group by student_id
-- 	having count(num) >= 2
-- ;
# 3.将班级表与学生表拼接起来
SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( num ) >= 2 );

作业布置

1
2
3
4
5
6
7
8
9
10
1.查询平均年龄在25岁以上的部门名(表为之前查询关键字表)
# 
	分别运用子查询和连表操作完成上述题目
SELECT name from dep WHERE id in (SELECT dep_id from emp GROUP BY dep_id HAVING AVG(age) > 25);
SELECT dep.name from dep INNER JOIN emp ON dep.id = emp.dep_id GROUP BY dep.name HAVING avg(emp.age) > 25;

2.整理今日内容
3.彻底消化吸收课上练习题
4.数据库练习题大礼包(能做几道是几道 内附答案)
	https://www.cnblogs.com/Dominic-Ji/p/10875493.html

10 表字段操作补充

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 1.添加表字段
alter table 表名 add 字段名 字段类型 约束条件;  # 默认尾部追加
alter table 表名 add 字段名 字段类型 约束条件 after 已经存在的字段名; 
alter table 表名 add 字段名 字段类型 约束条件 first;  # 了解 

# 2.修改字段
"""modify只能改字段数据类型完整约束，不能改字段名，但是change可以！"""
ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 数据类型 [完整性约束条件…];

# 3.删除字段
ALTER TABLE 表名 
                          DROP 字段名;

11 python操作MySQL(掌握)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
1.下载模块
	pip3 install pymysql

"""
python默认下载模块的地址是国外的网站
速度有时候会非常的慢 如果想要提升速度我们可以切换下载源
"""
（1）阿里云 http://mirrors.aliyun.com/pypi/simple/
（2）豆瓣http://pypi.douban.com/simple/
（3）清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
（4）中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/
（5）华中科技大学http://pypi.hustunique.com/
# 1.命令行切换源
	pip3 install pymysql -i 源地址
# 2.pycharm永久切换
	file
    	setting
        	interpreter
            	双击
                	manage 仓库
   
2.基本使用
import pymysql


# 创建链接对象
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1',
    database='db4_3',
    charset='utf8'
)
# 生成游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内不写参数是元祖组织不够明确
# 定义SQL语句
# sql = 'show tables'
sql = 'select * from teacher'
# 执行sql语句
affect_rows = cursor.execute(sql)
print(affect_rows)  # 执行SQL语句所影响的行数
# 获取结果
res = cursor.fetchall()
print(res)

12 SQL注入

1
2
3
4
5
6
7
8
利用特殊符号的组合绕过相应的机制

如何解决
	不要自己手动处理敏感数据
 
sql = "select * from userinfo where name=%s and password=%s"
# 执行sql语句
cursor.execute(sql,(username,password))  # 交由execute自动拼接 自动筛选

13 其他操作

1
2
3
4
5
6
7
8
9
10
11
12
针对增删改查
	查重要程度很低 无序二次确认
    增改删重要程度很高 都需要二次确认
 conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1',
    database='db5',
    charset='utf8',
    autocommit=True  # 自动二次确认
)

14 视图

14.1 致命三问

14.1.1 什么是视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可

14.1.2 为什么要用视图

如果要频繁使用一张虚拟表，可以不用重复查询

14.1.3如何用视图

1
2
3
4
5
6
7
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"""
创建好了之后 验证它的存在navicat验证 cmd终端验证
最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
delete from teacher2course where id=1;
"""

强调 1、在硬盘中，视图只有表结构文件，没有表数据文件 2、视图通常是用于查询，尽量不要修改视图中的数据

1
drop view teacher2course;

思考:开发过程中会不会去使用视图？

不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能

15 触发器

15.1 致命三问

15.1.1 什么是触发器

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

15.1.2 为何要用触发器

触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
就会触发触发器的执行，即自动运行另外一段sql代码

15.1.3 如何用触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
"""语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
	sql语句
end
"""

# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
    sql代码。。。
end 
create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
    sql代码。。。
end

# 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
    sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
    sql代码。。。
end

# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
    sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
    sql代码。。。
end

"""
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
这就会出现语法冲突 需要我们临时修改结束符号
delimiter $$
delimiter ; 
该语法只在当前窗口有效  
"""

# 案例
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代 o   	  

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;

16 事务

16.1 致命三问

16.1.1 什么是事务

开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性

16.1.2 事务的作用

保证了对数据操作的数据安全性
案例:用交行的卡操作建行ATM机给工商的账户转钱

**事务应该具有4个属性：**原子性、一致性、隔离性、持久性。这四个属性通常称为**ACID特性**。

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

16.1.3 如何用事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 先介绍事务的三个关键字 再去用表实际展示效果

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

a

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

# 站在python代码的角度，应该实现的伪代码逻辑，
try:
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

# 那如何检测异常？

17 存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql,类似于python中的自定义函数

17.1 基本使用

1
2
3
4
5
6
7
8
9
delimiter $$
create procedure p1()
begin
	select * from user;
end $$
delimiter ;

# 调用
call p1()

17.2 三种开发模型

17.2.1 第一种

1
2
3
4
5
6
"""
应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
"""

17.2.2 第二种

1
2
3
4
5
6
7
"""
应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
"""

17.2.3 第三种

1
2
3
4
5
"""
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过
"""

17.3 创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 介绍形参特点  再写具体功能

delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

17.4 如何用存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')


# 3、存储过程与事务使用举例(了解)
delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;


  DECLARE exit handler for sqlwarning
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;

  START TRANSACTION;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功


END //
delimiter ;

18 函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

参考博客：http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

19 流程控制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

20 索引

20.1 索引基础

知识回顾:数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

  • primary key
  • unique key
  • index key

注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响:

  • 在表中有大量数据的前提下，创建索引速度会很慢
  • 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

20.2 b+树

https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170912011123500-158121126.png

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项

思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

20.3 聚集索引(primary key)

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。

myisam在建表的时候对应到硬盘有几个文件(三个)？

innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。

**特点:**叶子结点放的一条条完整的记录

20.4 辅助索引(unique,index)

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})

select name from user where name=’jason’;

上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select age from user where name=’jason’;

上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

20.5 测试索引

准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢

"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

20.6 联合索引

1
2
3
4
5
6
7
8
9
10
11
12
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了

慢查询日志

设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！

20.7作业

1
2
1.基于pymysql编写用户注册登录功能
	ps:也可以将之前项目做优化