数据库高级教程
目录
一、数据库的类型
关系性数据库
==============================================================
MySQL, oracle, sqlite, db2, sql server
1.数据与数据之间可以有关联和限制的
2.关系型数据库通常都是表结构,也就意味着你在用关系型数据库的时候，第一步就是确定表结构
非关系性数据库
redis,mongodb(文档型数据库 非常接近关系型的非关系型数据),memcache
通常都是以k,v键值的形式 存储数据 类似字典
数据库编码
一般用utf8
utf8mb4：可以存表情
二、MySQL
============================
启动前准备:
1.bin目录配置环境变量path中
2.管理员身份运行cmd，将bin目录下mysqld服务端制作成系统服务（自动开启，24小时不间断服务)
制作命令： mysqld --install
服务端
mysqld
客户端
mysql
1.启动
1.启动mysqld 服务端
1.切换到bin目录下
2.执行mysqld
ps:做前期MySQL配置的时候 终端建议你用管理员身份运行
2.启动 mysql 客户端
客户端登陆
mysql -h 127.0.0.1 -P 3306 -uroot -p
可以简写
mysql -uroot -p
如果不输入用户名和密码 默认是访客模式登陆 所能用到的功能很少
客户端退出登陆
exit;
quit;
2.修改密码
没有密码的情况下
set password for root@localhost =password('123'); 这个好用，亲身实验，老师讲的不成功
有密码的情况下
mysqladmin -uroot -p123 password 123456；
当命令输入错误的时候 可以用\c取消前面的命令 cancel
密码忘记，破解密码
思路：密码验证就是一个装饰器，那么启动的时候跳过密码装饰器，密码就破解了
注意停止：先查PID号，再杀死进程
1.先停止服务端，重启服务端，跳过用户名密码验证功能
命令：mysqld --skip-grant-tables; 启动服务端，跳过授权表
2.修改管理员用户对应的密码（而不是全部用户的密码，选择需要修改的用户）
命令：uptade mysql.user set password = password(123) where user = 'root' and host='localhost';
host='localhost' 表示 本地的服务器
3.重新正常启动服务端（拥有密码验证的功能）
4.客户端mysql正常登录到服务端（刚刚修改的密码）
3.配置文件
\s：查看 mysql服务端简单配置
通常情况下配置文件的后缀都是ini结尾
mysql自带的配置文件不要修改
但是你可以新建一个配置文件 my.ini
mysql服务端在启动就会自动加载你的my.ini配置文件内的配置
修改完配置文件之后需要先将服务端停止 重新启动 才能生效
修改了配置文件一定要重启服务端
<pre class="mysql">```
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
# 这个是用python软件连接的客户端
[client]
default-character-set=utf8
# mysql软件的客户端
[mysql]
# 可写，可不写这样可以不需要用户名与密码直接登录mysql
# user='root'
# password=123
# 设置默认的字符编码
default-character-set=utf84.设置严格模式
严格模式：比如char(4)，4个长度，但是存了5个进去，就会报错
如果不是严格模式，mysql会自动的截取最大长度来保存。
使用数据库原则：能尽量的让数据库少干活。因为量一大，数据库一旦崩溃，所有都完蛋
<pre class="mysql">```
show variables like "%mode%"; 模糊查找一下
set session 临时有效 只在你当前操作的窗口有效
set global 全局有效 终生有效
set global sql_mode = 'STRICT_TRANS_TABLES';
设置完之后 你只需要重新退出客户端再次进入即可
"""
show variables like "%mode%";
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,only_full_group_by"; # 修改模式
# 重新链接客户端三.数据库的增删改查
1.库 类似于文件夹
<pre class="mysql">```
增
create database 库名; 添加库
create database 库名 charset utf8; 添加库定义编码
查
show databases; 查所有
show create database 库名; 查单个
改
alter database 库名 charset='gbk'; 修改编码
删
drop database 库名; 删库2.表 类似于文件
=================================================================================================================================================================================================================================================================================================================================================================================================================================================================
创建表的完整语法：
create table 表名(
字段名1 字段类型[(宽度) 约束条件],
字段名2 字段类型[(宽度) 约束条件],
字段名3 字段类型[(宽度) 约束条件],
字段名4 字段类型[(宽度) 约束条件]
);
在创建表的时候 需要先指定库
指定库: use 库名
查看当前所在的库: select database()
清空表：
truncate table 表名; # 清空表
truncate 1.会重建表结构，清除主键 2.truncate 不能触发任何Delete触发
delete from 表名 仅仅是删除数据 不会重置主键
truncate table 表名 初始化表 会重置主键
<pre class="mysql">```
增
create table 表名(id int,name char);
create table 表名(id int,name char(4)default 'jeff'); # 给name属性添加默认值，用户不填的情况用默认
查
show tables; 查看某个库下面的所有的表
show create table 表名; # 查单个表
desc 表名; <==> describe 表名; # 查单个表
改
alter table 表名 modify 字段名 char(32);
删
drop table 表名;3.记录
先创建一个库或者指定一个已经存在的库
切换到该库下 创建表
然后再操作记录
create database 库名; # 创建库
create table 表名(id int,name char(32),password int); # 添加表
<pre class="mysql">```
增
insert into 表名 values(1,'jason',123); 插入单条数据
insert into 表名 values(1,'jason',123),(2,'egon',123),(3,'tank',123); 插入多条数据
insert into 表名(name,id) values ('jeff',1) # 可以指定字段插入
查
select * from 表名; 查询所有的字段信息
select name from 表名; 查询指定字段信息
select id,name from 表名 where id=1 or name='tank'; 带有筛选条件的字段信息
改
update 表名 set name='kevin' where id=1; 修改数据的一个字段信息
update 表名 set name='jason',password=666 where id=1; 修改数据的多个字段
删
delete from 表名 where id =1; 指定删符合条件的数据
delete from 表名; 将表中的数据全部删除
清空表：
truncate table 表名; # 清空表
truncate 1.会重建表结构，清除主键 2.truncate 不能触发任何Delete触发器4.查杀进程
查看所有的数据库
show databases;
查看某个进程
tasklist |findstr 名称
杀死进程
taskkill /F /PID 进程号5.修改表名、增加字段、删除字段、修改字段
1.修改表名
alter table 表名 rename 新表名;
2.增加字段
alter table 表名 add 字段名 数据类型 [完整性约束条件…],addD 字段名 数据类型 [完整性约束条件…];
3.移动字段次序
alter table 表名 add 字段名 数据类型 [完整性约束条件…] first; # 直接移到最前面
alter table 表名add 字段名 数据类型 [完整性约束条件…] after 字段名; # 寻找插哪个字段的后面
4.删除字段
alter table 表名 drop 字段名;
5.修改字段
modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
alter table 表名 modify 字段名 数据类型 [完整性约束条件…];
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…];
6.复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引）
<pre class="mysql">```
create table new_service select * from service;只复制表结构
<pre class="mysql">```
select * from service where 1=2; //条件为假，查不到任何记录
create table new1_service select * from service where 1=2;
create table t4 like employees;四、存储引擎
===========================
不同的数据应该有不同的处理机制
mysql存储引擎
Innodb: 5.5之后默认的存储引擎 查询速度较myisam慢 但是更安全，支持事务，支持行锁，支持外键 行锁：同一时间只能一个用户操作这行数据 2两个文件
myisam: mysql老版本用的存储引擎 5.5之前 3个文件
memory: 内存引擎(数据全部存在内存中)，断电或者服务端重启之后所有数据都没有了 1个文件
blackhole: 无论存什么 都立马消失(黑洞) 1个文件
研究一下每个存储引擎存取数据的特点
show engines;
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;
五、数据类型
1.常用数据类型
================================================================
只有Int整型不需要指定宽度 因为默认的宽度 足够显示对应的数据
<pre class="python">```
#1. 数字：
整型：tinyinit int bigint
小数：
float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
0.000001230123123123
存成：0.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存
unsigned 没有符号的整型，正整数
age int(3) unsigned not null default 28
#2. 字符串：
char（10）：简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
#3. 时间类型：
最常用：datetime
#4. 枚举类型与集合类型
枚举(enum) 限制某个字段能够存储的数据内容
集合(set) 限制某个字段能够存储的数据内容
2.数值类型
1.整数类型
tinyint smallint mediumint int bigint
int 不用设置宽度，因为默认值够大
2.浮点型
float(255,30) 总共255位 小数部分占30位
double(255,30) 总共255位 小数部分占30位
decimal(65,30) 总共65位 小数部分占30位
精确度：float<double<decimal
create table t12(id FLOAT(255,30));
create table t13(id DOUBLE(255,30));
create table t14(id DECIMAL(65,30));
insert into t12 values(1.111111111111111111111111111111);
insert into t13 values(1.111111111111111111111111111111);
insert into t14 values(1.111111111111111111111111111111);
六、日期类型
===========================
date time datetime timestamp year
date: 年月日
time:几时几分几秒
datetime:年月日时分秒
timestamp:时间戳
year:年份
七、字符串类型
============================
char,varchar
1.char(N) 定长
用于保存固定长度的字符串，不足的用空格补全，N的范围0~255
特点：好存好取，浪费空间
2.varchar(N) 变长
用于保存变长字符类型，N的范围0~65535
特点：不好存不好取，节约空间
八、枚举类型与集合类型
================================
字段的值只能在给定的范围中选择，比如单选框、多选框
enum 单选 只能在给定的范围选一个值，如性别选择
set 多选 在给定的范围内可以选择一个或者多个值（爱好1，爱好2，爱好3）
示例：单选 enum
<pre class="mysql">```
create table gyy (
name varchar(40),
size enum('x-small', 'small', 'medium', 'large', 'x-large')
); # 在给定的范围内选择
insert into gyy (name, size) values ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
实例： 集合 set
<pre class="mysql">```
CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); # 在给定的范围内选择
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
九、约束
1.not null
==================================================================
not null 不能为空
2.default 默认值
给某个 字段设置默认值（当用户填写的时候使用用户的，不填写使用默认的）
<pre class="mysql">```
create table biao1(id int,name char(4) default'jeff');# name选项不填写默认jeff
3.unique 唯一
单列唯一 限制某一个字段事唯一的
id int unique
联合唯一 (在语句的最后 用括号的形式 表示哪几个字段组合的结果是唯一的)
使用方法：
<pre class="mysql">```
create table server(
id int,
ip char(16),
port int,
unique(ip,port)
)
4.primary key主键
主键：一般设置在id或者编号,具有唯一且默认递增，与auto\_increment配合使用
<pre class="mysql">```
create table t21(id int primary key auto_increment,name varchar(16));
primary key 也是innidb引擎查询的索引
限制效果跟 not null + unique 非空且唯一 组合效果一至
使用方法：
<pre class="mysql">```
create table biao(id int primary key);
<pre class="mysql">```
innodb引擎在创建表的时候 必须要有一个主键
当你没有指定主键的时候
1.会将非空切唯一的字段自动升级成主键
2.当你的表中没有任何的约束条件 innodb会采用自己的内部默认的一个主键字段
该主键字段你在查询时候是无法使用的
查询数据的速度就会很慢
类似于一页一页的翻书
联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键!!!
!!!!!!!!!!!!!!!!!ps:innodb引擎中一张表有且只有一个主键!!!!!!!!!!!!!!!!!!!!!!!
联合主键： ip+端口为主键
create table biao(
ip char(16),
port int,
primary key(ip,port)
);
delete from 表名 仅仅是删除数据 不会重置主键
truncate table 表名 初始化表 会重置主键
5.primary key主键自动递增
<pre class="mysql">```
create table t66(id int auto_increment,name char(16),primary key(id)); # 创建表
# 第一种方法，不选择id主键字段
insert into t66(name) values('jeff');
# 第二种方法，用0占位
insert into t66 values(0,'jeff');
6.auto\_increment 递增
auto\_increment 自动递增，只能加载到key字段上 key：主键，唯一，外键
<pre class="mysql">```
主键字段应该具备自动递增的特点
每次添加数据 不需要用户手动输入
auto_increment 自动递增
create table t21(id int primary key auto_increment,name varchar(16));
delete from 表名; 将表中的数据全部删除。没有清空主键
清空表：
truncate table 表名; # 清空表
truncate 1.会重建表结构，清除主键 2.truncate 不能触发任何Delete触发器
十、外键
判断外键关系
==========================================================
外键的关键字：
foreign key(自己id) references 别的表名(别的表名的字段) 只是用来建表关系的
<pre class="mysql">```
foreign key(author_id) references author(id)
表示：当前表的author_id与author表的id字段关联
外键约束：
1.在创建表的时候必须先创建被关联的表
2.插入数据的时候 也必须先插入被关联表的数据
1.外键——表关系判断
判断表关系最简单的语法：
图书馆与出版社
一本书可不可以有多个出版社 不可以！！！
一个出版社可不可以出版多本书 可以！！！
一对多的关系
图书与作者
一本书可不可以有多个作者 可以！！！
一个作者可不可以写多本书 可以！！！
多对多的关系
作者与作者详情
一个作者可不可以有多个详情 不可以！！！
一个详情可不可以有多个作者 不可以！！！
两者要么一对一的关系
两者要么没关系
2.外键——连级更新
foreign key(dep\_id) references dep(id) 绑定关系
on update cascade 连级更新
on delete cascade 连级删除
<pre class="mysql">```
create table emp(
id int primary key auto_increment, # 设置主键、自动递增
emp_name varchar(64),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 连级更新
on delete cascade # 连级删除
);
3.外键——一对多
语法：
1.先建被关联的表
<pre class="mysql">```
create table press(
id int primary key auto_increment, # 给id设置主键和自动增长
p_name varchar(10)
);
2.建关联的表：
<pre class="mysql">```
create table library(
id int primary key auto_increment, # 给id设置主键和自动增长
l_name varchar(10),
l_price int,
press_id int，
foreign key(press_id)references press(id) # 关联的字段
on update cascade # 连级更新
on delete cascade # 连级删除
);
3.先插入被关联的表数据：
<pre class="mysql">```
insert into press(address) values('清华出版社'),('上海出版社'),('北京出版社');
4.插入关联数据表：
<pre class="mysql">```
insert into library(l_name,l_prince,press_id) values('天下无贼',128,1),
('亲爱的可',666,1),
('西游记',99,2),
('骆驼祥子',168,3),
('情深深雨蒙蒙',98,3);
4.外键——多对多
一本书可以拥有多个作者，一个作者可以写多本书
建表思路：因为是多对多的关系，比如先建图书表，那么图书表的作者writer字段必须是writer表的writer\_id字段中的，但是writer表还没有建立，没有。同样的道理先建writer作者表，那么图书表还没有建立。所以关系建立在第三张表中，这两个表先建立，不设外键
语法：
1.先建两个普通的表，不需要设置外键
<pre class="mysql">```
# 图书表
create table library(
id int primary key auto_increment,
name char(5),
price int,
);
# 作者表
create table writer(
id int primary key auto_increment,
name char(10),
age int
);
2.建立关系表
<pre class="mysql">```
create table library2writer(
id int prinmary key auto_increment,
library_id int,
foreign key(library_id)references library(id)
on update cascade
on delete cascade,
writer_id int,
foreign key(writer_id)references writer(id)
on update cascade
on delete cascade
);
3.插入数据
<pre class="mysql">```
# 图书表
insert into library(name,prince) values('天下无贼',99),('西游记','66'),('骆驼祥子',128);
# 作者表
insert into writer(name,age) values('jeff',18),('gyy',20),('cheary',3);
# 关系表
insert into library2writer(library_id,writer_id)values(1,1),(1,2),(2,1),(2,2),(3,3);
5.外键——一对一
语法：
<pre class="mysql">```
# 建立作者详细信息表
create table xiangqin(
id int primary key auto_increment,
prize char(30),
event varchar(100)
);
# 建立作者表
create table writer1(
id int primary key auto_increment,
name char(10),
age int
xiangqin_id int unique, # 唯一
foreign key (xiangqin_id)references xiangqin(id) # 绑定关系
on update cascade
on delete cascade
);
6.关系总结
通常将关系字段 称之为 外键字段
一对多的外键字段 建在多的一方
多对多 建在第三张表了
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率较高的一方
7.练习题
练习:
<pre class="python">```
# 班级表
cid caption
# 学生表
sid sname gender class_id
# 老师表
tid tname
# 课程表
cid cname teacher_id
# 成绩表
sid student_id course_id number
十一、基本查询语法及方法
=================================
准备的表：
<pre class="mysql">```
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),
('sean','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
from
where
group by
having
distinct
order by
limit
连表
inner join
left join
right join
union
1.书写顺序：
执行顺序：
from # 确定是那张表
where # 条件，筛选数据
select # 筛选之后拿出某些字段
2.where
1.查询id大于等于3小于等于6的数据
<pre class="mysql">```
select * from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 两者语句完全等价 between ： 之间
2.查询薪资是20000或者18000或者17000的数据
salary in (20000,18000,17000);
<pre class="mysql">```
select id,name from emp where salary = 20000 or salary = 18000 or salary = 17000;
select id,name from emp where salary in (20000,18000,17000);
3.查询员工姓名中包含o字母的员工姓名和薪资
<pre class="mysql">```
模糊匹配 like
%:匹配多个任意字符
_:匹配一个任意字符
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
char\_length :计算字段的长度
<pre class="mysql">```
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
5.查询id小于3或者大于6的数据
<pre class="mysql">```
select * from emp where id < 3 or id > 6;
select * from emp where id not between 3 and 6;
6.查询薪资不在20000，18000，17000范围的数据
<pre class="mysql">```
select id,name from emp where salary not in (20000,18000,17000);
7.查询岗位描述为空的员工名与岗位名 针对null判断的时候只能用is 不能用=
<pre class="mysql">```
select name,post from emp where post_comment = Null;
select name,post from emp where post_comment is Null;
3.group by 分组
MySQL中分组之后 只能拿到分组的字段信息 无法直接获取其他字段信息，但是你可以通过其他方法(聚合函数)简介的获取
聚合函数 max min avg sum count
<pre class="mysql">```
# 数据分组应用场景：每个部门的平均薪资，男女比例等
# 1.按部门分组
select * from emp group by post; # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post; # 验证
"""
show variables like "%mode%";
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,only_full_group_by"; # 修改模式
# 重新链接客户端
select * from emp group by post; # 报错
select id,name,sex from emp group by post; # 报错
select post from emp group by post; # 获取部门信息
# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;
# 每个部门的最低工资
select post,min(salary) from emp group by post;
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和
select post,sum(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;
# 3.查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
select post,group_concat(name,": ",salary) from emp group by post;
select post,group_concat(salary) from emp group by post;
concat_ws(':',name,age,salary) # 每个字段以冒号分隔
# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
select name as 姓名,salary as 薪资 from emp;
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;
# 补充as语法 即可以给字段起别名也可以给表起
select emp.id,emp.name from emp as t1; # 报错 因为表名已经被你改成了t1
select t1.id,t1.name from emp as t1;
# 查询四则运算
# 查询每个人的年薪
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp; # as可以省略
练习题：
<pre class="mysql">```
# 刚开始查询表，一定要按照最基本的步骤，先确定是哪张表，再确定查这张表也没有限制条件，再确定是否需要分类，最后再确定需要什么字段对应的信息
1. 查询部门名以及部门包含的所有员工名字
2. 查询部门名以及各部门内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询部门名以及各部门的平均薪资
5. 查询部门名以及各部门的最高薪资
6. 查询部门名以及各部门的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
"""
参考答案：
select post,group_concat(name) from emp group by post;
select post,count(id) from emp group by post;
select sex,count(id) from employee group by sex;
select post,avg(salary) from emp group by post;
select post,max(salary) from employee group by post;
select post,min(salary) from employee group by post;
select sex,avg(salary) from employee group by sex;
"""
# 关键字where group by同时出现的情况下，group by必须在where之后
# where先对整张表进行一次筛选，如何group by再对筛选过后的表进行分组
# 如何验证where是在group by之前执行而不是之后 利用聚合函数 因为聚合函数只能在分组之后才能使用
select id,name,age from emp where max(salary) > 3000; # 报错！
select max(salary) from emp;
# 正常运行，不分组意味着每一个人都是一组，等运行到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件
# 语法顺序
select
from
where
group by
# 再识执行顺序
from
where
group by
select
8、统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age > 30 group by post;
# 对where过滤出来的虚拟表进行一个分组
# 还不明白可以分步执行查看结构
select * from emp where age>30;
# 基于上面的虚拟表进行分组
select * from emp where age>=30 group by post;
4.having
截止目前已经学习的语法
<pre class="mysql">```
select 查询字段1,查询字段2,... from 表名
where 过滤条件
group by分组依据
# 语法这么写，但是执行顺序却不一样
from
where
group by
select
having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！
<pre class="mysql">```
1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
# 如果不信你可以将having取掉，查看结果，对比即可验证having用法！
#强调：having必须在group by后面使用
select * from emp having avg(salary) > 10000; # 报错
5.distinct 去重复
<pre class="mysql">```
# 对有重复的展示数据进行去重操作
select distinct post from emp;
6.order by 排序
<pre class="mysql">```
select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排
select * from emp order by age desc; #降序排
#先按照age降序排，在年龄相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc;
# 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age > 30
group by post
having avg(salary) > 1000
order by avg(salary)
;
7.limit 限制显示几条
limit 5； 表示限制的个数
limit 5,5; 第一个参数，表示起始位置，第二个参数表示个数
<pre class="mysql">```
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
8.正则
<pre class="mysql">```
select * from emp where name regexp '^j.*(n|y)$';
9.like
%: 模糊匹配
\_ ： 匹配一个字符
char\_length(name) = 4， 计算字符长度
<pre class="mysql">```
select * from teacher where tname like'李%' # 以李开头的所有
select * from teacher where tname like'%李' # 以李结尾的所有
select * from teacher where tname like'%李%' # 匹配包含李的所有
多表查询
查询分为两大类：
1.联表查询 把两个表拼在一起查
2.子查询 将一张表的查询结果作为另外一个sql语句的查询条件
拿到一个表的相关数据，到另一个表中查
表创建
<pre class="mysql">```
#建表
create table dep(
id int,
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',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;
# 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理
联表查询 inner join
<pre class="mysql">```
select * from emp,dep; # 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据
# 查询员工及所在部门的信息
select * from emp,dep where emp.dep_id = dep.id;
# 查询部门为技术部的员工及部门信息
select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术';
# 将两张表关联到一起的操作，有专门对应的方法
# 1、内连接：只取两张表有对应关系的记录 inner join
select * from emp inner join dep on emp.dep_id = dep.id;
select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技术";
# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录 left join
select * from emp left join dep on emp.dep_id = dep.id;
# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录 reght join
select * from emp right join dep on emp.dep_id = dep.id;
# 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录 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;
子查询
as 后面是取别名
<pre class="mysql">```
# 就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用
# 1.查询部门是技术或者人力资源的员工信息
"""
先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
"""
select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");
# 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;
"""
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询
"""
select * from emp inner join dep on emp.dep_id = dep.id;
exist(了解)
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
select * from emp
where exists
(select id from dep where id > 203);
十二、数据库高级功能
1.视图
============================================================
语法：create view 新建的视图名 as 连表
新建的视图名：表1\_表2 （一般根据命名规范）
注意：
1.视图只有表结构,视图中的数据还是来源于原来的表
2.不要改动视图表中的数据
3.一般情况下不会频繁的使用视图来写业务逻辑
1.什么是视图？
一个查询语句的结果是一张虚拟表,将这种虚拟表保存下来，它就变成了一个视图
2.为什么要使用视图？
当平凡需要使用到多张表的连表结果，你就可以事先 生成号视图之后直接调用即可，避免反复写连表操作的sql语句。
3.如何使用？
<pre class="mysql">```
# 1.这是一张连表
select * from teacher inner join course on teacher.tid = course.teacher_id;
# 2.把连表另外生成一张表，就叫视图
create view teacher_course as 跟上面的连表
4.开发过程中会不会频繁的使用视图？
不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能
2.触发器
1.什么是触发器？
触发器就是当达到某种条件自动触发
当你在对数据进行增删改的情况下会自动触发触发器，执行代码
2.触发器分为六种情况
之前：before 之后：after
增加前、增加后 before insert 、 after insert
删除前、删除后 before delete 、after delete
修改前、修改后 before update 、after updata
3.触发期命名规范
见名知意：
固定语法结构：
<pre class="mysql">```
create trigger 触发器的名字 after/before insert/update/delete on 表名 for each row
begin
sql语句
end
可以修改MySQL默认的结束符(;)
delimiter $$ # 只对当前窗口有效
案例：
<pre class="mysql">```
# 针对插入
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
# 案例
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代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
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;
3.事务(面试)
事务包含一大堆sql语句，这些sql语句要么同时成功，要么一个也别想成功
事务的作用：保证了数据操作的安全系
案例：用交行的银行卡操作建行的ATM机给工行的账户转账1000万
当交行的银行卡钱扣除1000万，建行的ATM机告诉工行给转账账号加1000万。在过程中，如果建行的ATM机突然故障，失去了消息，那么钱扣除了却没有到账。
1.事务的四大特新ACID（面试）
A.原子性 C.一致性 I：隔离性 D：持久性
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 （总量不变）
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
2.如何开启事务？
start transaction
<pre class="python">```
1.# 修改数据之前先开启事务操作
start transaction;
2.# 修改操作
sql语句
3.# 回滚到上一个状态,未保存。数据在内存中
rollback;
4.# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit; # 相当于保存，数据刷到硬盘
3.事务回滚
rollback 在没有永久性更改之前，都可以回滚。就跟没有保存之前的撤回一样。
4.永久性更改
commit 就更保存数据一样
如何用？
<pre class="mysql">```
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
# 那如何检测异常？
4.存储过程（自定义函数）
存储过程就类似于python中的自定义函数
内部封装了操作数据库的sql语句,后续想要实现相应的操作 只需要调用存储过程即可
三种开发模型
第一种
<pre class="python">```
"""
应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
"""
第二种
<pre class="python">```
"""
应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
"""
第三种
<pre class="python">```
"""
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过
"""
创建存储过程：
<pre class="mysql">```
delimiter $$ # 修改mysql的结束';'这个符号，因为下面会用到分号
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from teacher where tid > m and tid < n;
set res=0;
end $$
delimiter ; # 在八mysql结束符号改回来。
如何使用存储过程
<pre class="mysql">```
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
# 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 ;
5.函数（内置函数）
注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！
参考博客：<http://www.cnblogs.com/linhaifeng/articles/7495918.html#\_label2
<pre class="mysql">```
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-%d'),count(id) from blog group by date_format(sub_time,'%Y-%m-%d');
6.流程控制
<pre class="mysql">```
# 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 ;
<pre class="mysql">```
# 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 ;
7.索引与满查询优化
知识回顾:数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。
- primary key 聚集索引（主键）
- unique key 辅助索引（主键之外）
- index key
注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询
索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
索引的影响:
- 在表中有大量数据的前提下，创建索引速度会很慢
- 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
b+树
https://www.icode9.com/i/i/?n=17&i=blog/1036857/201709/1036857-20170912011123500-158121126.png
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段
聚集索引(primary key)**
聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。
myisam在建表的时候对应到硬盘有几个文件(三个)？
innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。
特点:叶子结点放的一条条完整的记录
辅助索引(unique,index)
辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
select name from user where name='jason';
上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select age from user where name='jason';
上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
测试索引
准备
<pre class="mysql">```
#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();
<pre class="mysql ">```
# 表没有任何索引的情况下
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字段一剑封喉
联合索引
<pre class="mysql">```
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语句，然后针对性的进行优化！