@[TOC](目录)
## 数据库基本操作
```
创建用户：
create user 'khw'@'192.168.1.1' identified by '123123'
create user 'khw'@'%' identified by '123123';
# %号匹配所有
授权：
创建权限
grant select,insert,update on db1.* to 'khw'@'%'
grant all privileges on mysql.* to 'khw'@'%'
privileges:(表示拥有所有权限)
db1.*:(表示这个数据库里面的所有表)
to 'khw'@'%':表示把这个权限给khw这个用户
查看：
use mysql;
select user,host from user #查看user这个表里的user,host两列
设置密码：
mysqladmin -uroot -p 原密码 password 新密码
该命令在终端输入即可，无须进入客户端
破解密码：
可以将mysql获取用户名和密码校验的功能看出一个装饰器
装饰在了客户端请求访问的功能上，将装饰器溢出就可以不校验用户名密码了
1、先关闭当前mysql服务端
#命令行的方式启动(让mysql跳过用户密码验证功能)
mysql --skip-grant-tables;
2、直接以无密码的方式连接
mysql -uroot -p;
3、修改当前用户的密码
update mysql.user set password=password(123456) where user='root' and host='localhost';
"""真正存储用户表的密码字段，肯定是密文"""
4、立刻将修改数据刷到硬盘
flush privileges;
```
## 库的增删改
```
增:
create database db2 default charset=utf8; 创建数据库
查:
show databases; 查看数据库
show create database db1; 查看单个
删:
drop database db2; 删除数据库
改:
alter database db2 charset='utf-8'; 修改
```
## 表的增删改
```
查看当前所在库的名字
select database();
进入数据库：
use db2;
增：
create table t1(id int,name char(10)) default charset=utf8;
# 创建表第一列名称为id int类型,第二列名称为name char类型，最多存10个字符
查：
show tables; # 查看当前库下面的所有表名
show create table t1; # 查看单一表
describe t1; # 支持简写 desc t1;
改：
alter table t1 modify name char(16); # 将char的最大字符改为16
删：
drop table t1;
```
## 操作表中的内容
```
查：
select * from t1;
select name from t1; # 查name这一列的数据
增：
insert into t1 values(1,'jason');
insert into t1 values(1,'jason'),(2,'egon'),(3,'tank');
改：
update t1 set age=18 # set表示设置，将age这一列都改为18
update t1 set age=18 where age=17 # 将age这一列里17改为18，where为限制条件
删
delete from t1 where id>6 # 将t1这张表里id大于6的都删掉
delete from t1 where name='json'
将表所有的数据清空：
delete from t1;
```
## 创建表的完整语法
```
# 语法
create table 表名(
字段名1 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件,
)
# 注意
1、在同一张表中字段名不能重复
2、宽度和约束条件是可选的，而字段名和类型是必选的
3、约束条件可以支持写多个
create table t1(
字段名1 类型(宽度) 约束条件1 约束条件2 ....
)
4、最后一行不能有逗号
create table t1(
id int,
name char, # 报错
);
"""补充"""
# 宽度
一半情况下指的是对存储数据的限制
create table t1(name char); 默认宽度是1
insert into t1 values('jason') # 应为宽度唯一，所以这次插入只能插进j
insert into t1 values(null); # 关键字NULL，插入关键字null
# 约束条件 null ， not null
create table t1(id int, name char not null) #不能插入null
# 宽度和约束条件的关系
宽度是来限制数据的存储
约束条件是在宽度的基础之上增加额外的约束
```
## 修改表
```
# MySQL 对大小写是不敏感的
"""
1、修改表名
alter table 表名 rename 新表明;
2、增加字段
alter table 表明 add 字段名 字段类型(宽度) 约束条件; 默认是在尾部
alter table 表明 add 字段名 字段类型(宽度) 约束条件 first; 将字段添加到表的最前面
alter table 表明 add 字段名 字段类型(宽度) 约束条件 after 字段名; 更在谁的后面
3、删除字段
alter table 表名 drop 字段名;
4、修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名(宽度) 约束条件;
"""
```
## 复制表(了解)
```
# sql语句的结果其实也是一张虚拟表
"""
create table 新表名 select * from 旧表; 不能复制主键 外键
"""
```
## Mysql主要存储引擎
```
日常生活中文件格式有很多，并且针对不同的文件格式有对应不同存储方式和处理机制
针对不同的数据应该有对应不同的处理机制来存储
存储引擎就是不同的处理机制
innodb:
是Mysql5.5版本之后默认的存储引擎
存储数据更加的安全
# 文件个数两个，frm 表结构，ibd 表数据
myisam：
是Mysql5.5版本之前默认存储引擎
速度要比innodb更快 但我们更加注重的是数据的安全
# 文件个数三个，frm 表结构，myd 表数据， Myl 索引
memory：
内存引擎，数据放在内存中，断电丢失
# 文件个数一个，frm 表结构，数据都在内存追踪
blackhole：
无论存什么，都立刻消失
# 文件个数一个，frm 表结构
"""
# 查看所有的存储引擎
show engines;
"""
```
## 基本数据类型
```
整形
1、分类：
tinyint smallint meduimint int gifint
2、作用：
存储年龄、等级、id、号码等等
3、详细介绍： 图片链接
"""
# 针对整型 括号内的宽度有啥用
只有整型括号里的数字不是表示限制位数
id int(8)
如果数字没有超出8魏，默认用空格填充至8位
如果数字超出了8位，有几位存几位（但还是要遵守最大范围）
总结：
针对整型字段 括号内无须指定宽度，应为他的默认宽度足够存常见数据了
"""
浮点型
1、分类
FLOAT、DOUBLE、DECIMAL
2、作用
身高、体重、薪资
3、存储限制
float(255,30) # 总共255位，小数部分30位
double(255,30) # 总共255位，小数部分30位
decimal(65,30) # 总共65位，小数部分30位
4、精确度
float < double < decimal
事件类型
1、分类
date 年月日
datetime 年月日 时分秒
time 时分秒
year 年份
枚举与集合类型
1、分类
枚举(enum) 多选一
集合(set) 多选多
2、作用：
"""
create table user(
id int,
name char(16),
gender enum('male','female')
);
insert into user values(1,'jason','male'); 正常
insert into user values(2,'egon','xo'); 报错
# 枚举字段 后期在存储数据的时候只能从枚举里面选择一个存储
"""
"""
create table user(
id int,
name char(16),
gender enum('male','female')
hobby set('read','dbj','hecha')
);
insert into teacher values(1,'jason','read'); 正常
insert into teacher values(2,'egon','female','dbj,hecha'); 正常
insert into teacher values(3,'tank','others','生蚝'); 报错
# 结合可以只写一个或多个，但是不能写没有列举的
"""
```
#### 严格模式
```
# 如何查看严格模式
show variabels like "%mode"
模糊匹配/查询
关键字 like
%：匹配任意多个字符
-：匹配任意单个字符
# 修改严格模式
set session 只在当前窗口有效
set global 全局有效
set global sql_mode = "STRICT_TRANS_TABLES";
修改完之后重新进入服务端即可
```
## 约束条件
### default 默认值
```
default 默认值
# 补充知识点 插入数据的时候可以指定字段
create table t1(
id int,
name char(16)
);
insert into t1(name,id) values('jason',1);
create table t2(
id int,
name char(16),
gender enum('male','female','others') default 'male'
);
# default 表示不填默认位male
insert into t2(id,name) values(1,'jason');
insert into t2 values(2,'egon','female');
```
### unique唯一
```
unique唯一
# 单列唯一,一般位于id这一列
create table t3(
id int unique,
name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');
insert into t3 values(1,'jason'),(2,'egon')
# 联合唯一，例如ip和端口，单个可以重复，但是加载一起必须是唯一的
create table t4(
id int unique,
ip char(16)
port int,
unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8080); #这条报错
```
### primary key主键
```
primary key主键
# 1、从约束效果上看 primary key 等价于not null+unique，非空且唯一
create table t5(id int primary key);
intsert into t5 values(null); 报错
intsert into t5 values(1),(1); 报错
intsert into t5 values(1),(2);
# 2、他除了有约束效果之外，他还是innodb存储引擎组织数据的依据，innodb存储引擎在创建表的时候必须要有primary key，应为他类似与书的目录 能够帮助提示查询效率 并且也是建表的依据
"""
1、一张表中有且只有一个主键 如果没有设置主键，会从上往下搜索，知道遇到一个非空且唯一的字段 将他自动升级为主键
create table t6(
id int ,
name char(16),
age int not null unique,
addr char(32) not null unique
);
2、如果表中没有主键也没有其他非空唯一字段 南无innodb会采用自己内部提供的一个隐藏字段为主键，隐藏意味着你无法使用，无法提升查询速度
3、一张表中通常由一个主键字段，并且通常将id/uid/sid字段作为主键
单个字段主键
create table t5(
id int primary key,
name char(16)
);
联合主键(多个字段联合起来作为表的主键，本质还是一个主键)
create table t4(
id int unique,
ip char(16)
port int,
primary key(ip,port)
);
"""
所以我们在创建表的时候id字段一定要加primary key
```
### auto_increment自增
```
# 当编号太多，人为维护麻烦，可以使用此主键
create table t8(
id int primary key auto_increment, #id字段可以自增，并且非空且唯一
name char(16)
)
insert into t8(name) values('json'),('egon'),('kevin');
总结：
以后再创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
补充：
delete from 再删除表中数据的时候，主键的自增不会停止
truncate t1 清空表数据并重置主键
```
## 表与表之间建关系
### 外键
```
外键是用来帮助我们建立表与表之间的关系
foreign key
```
### 一对多关系
```
"""
员工表与部门表为例
一个员工能否对应多个部门
不能
一个部门能否对应多个员工
能
得出结论
员工表与部门表 是单向的一对多
所以表关系就是一对多
"""
foreign key
1、一对多表关系，外键字段写在多的一方
2、再创建表的时候，一定要先建被关联表
3、再录入数据的时候，也必须先录入被关联表
# 部门表
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
# 员工表
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','oyhers') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
)
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人')，values('外交部','人多外交');
insert into emp(name,dep_id) values('jason',2),values('egon',1);
# 级联操作：
# 1、修改dep表里面的id字段
update dep set id=200 where id=2； 不行
# 2、删除dep表里面的数据
delete from dep; 不行
# 3、删除教学部门对应的员工数据，之后再删除部门
操作繁琐
# 4、真正做到数据之间的关系
更新就同步跟新，级联更新
删除就同步删除，级联删除
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','oyhers') default 'male',
dep_id int,
foreign key(dep_id) references dep(id),
on update cascade, # 同步更新
on delete cascade # 同步删除
)
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人')，values('外交部','人多外交');
insert into emp(name,dep_id) values('jason',2),values('egon',1);
```
### 多对多关系
```
"""
图书表和作者表为例
一本书可以对应多个作者
可以
一个作者对应多本书
可以
所以推出为多对多关系
两张表都有外键字段
"""
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id),
on update cascade, # 同步更新
on delete cascade # 同步删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id),
on update cascade, # 同步更新
on delete cascade # 同步删除
);
"""
foreign key
1、一对多表关系，外键字段写在多的一方
2、再创建表的时候，一定要先建被关联表
3、再录入数据的时候，也必须先录入被关联表
按照上述的方式创建一个都别想成功
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系，不能在两张表原有的表中创建外键
需专门建立一张表，来存放外键
"""
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
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,
book_id int,
foreign key(author_id) references author(id),
on update cascade, # 同步更新
on delete cascade, # 同步删除
foreign key(book_id) references book(id),
on update cascade, # 同步更新
on delete cascade # 同步删除
);
```
### 一对一
```
"""
id name age addr phone hobby .....
如果一个表的字段特别多，每次查询又不是所有字段都能用得到
将表一分为二
用户表
id name age
用户详情表
id addr phone bobby email...
站在用户表
一个用户能否对应多个用户详情 不能
站在详情表
一个详情能否属于多个用户 不能
结论：这种就属于一对一或者没有关系
"""
一对一外键字段建在任意一方都可以，但建议建在查询频率较高的表中
create table authordetail(
id int primary key auth_increment,
phone int,
addr varchar()
);
create table author(
id int primary key auth_increment,
name varchar(32)
age int,
authordetail_id int unique,
foreign key(authordetail_id) referemces aithordetail(id),
on update cascade, # 同步更新
on delete cascade # 同步删除
);
```
### 总结
```
"""
表关系建立需要用到 foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三方存储
一对一
建在任意一方都可以 但是推荐建在查询频率较高的表中
判断表之间关系的方式
换位思考！
员工与部门
图书与作者
作者与作者详情
"""
```
## 查询表
### 前期表准备
```
"""
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',78,'20150302','张江第一帅形象代言',7300,22,421,1), #以下是教学部
('top','male',28,'20150302','teacher',100000,33,421,1),
('owem','male',38,'20050302','teacher',8300,13,421,1),
('top','male',48,'20150302','teacher',9500,63,421,1),
('哈哈','male',58,'20150302','sale',6500,53,421,1), #以下是销售部
('呵呵','male',68,'20150302','sale',5500,63,421,1),
('憎龙','male',78,'20150302','sale',4500,73,421,1), #以下是运营部
('程咬铁','male',88,'20150302','sale',3500,93,421,1);
"""
```
### 几个重要关键字的执行顺序
```
# 书写顺序
select id,name from emp where id > 3;
#执行顺序
from
where
select
"""
可以按照书写顺序的方式写sql
select * 先用*占位
之后补全后面的sql语句
最后将*号替换成想要的字段
"""
```
### where筛选条件
```
# 作用：是对整体数据的一个筛选操作
1、查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,nam,age from emp where id betwenn 3 and 6; 两者等价
2、查询薪资是20000或者18000或者17000的数据
slect * from emp where salary=2000 or salary=18000 or salary=17000;
select * from emp where salary in (2000,18000,17000);
3、查询员工姓名中包含字母o的员工姓名和薪资
"""
模糊查询
like
%匹配任意多个字符
_匹配任意单个字符
"""
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或者大于5的
select * from emp where id not between 3 and 6
6、查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not int (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;
```
### group by 分组
```
场景：
男女比例
部门平均薪资
部门秃头率
国家之间数据统计
分组语法：
select * from emp group by post;
聚合函数：
max
avg
sum
count
ps: 分组之后，最小可操作单位是组，而不是单个数据，上述命令在非严格模式下可以执行，返回的是分组之后每个组的第一条数据 ，但这个不符合分组规范，分组之后不应该考虑单个数据，而应该以组为操作单位(分组之后 没办法直接获取组内的单个数据)
严格模式： set global sql_mode = 'strict_trans_tables,only_full_group_by';
1、获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
ps: as可以给字段起别名
2、获取每个部门的最低薪资
select post,min(salary) from emp group by post;
3、获取每个部门的最高薪资
select post,avg(salary) from emp group by post;
4、获取每个部门的薪资综合
select post,sum(salary) from emp group by post;
5、获取每个部门的人数
select post,count(id) from emp group by post; # 常用
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; # null不行
6、查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat 不单单可以支持你获取分组之后的其他字段，还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat 不分组的时候使用
select concat('NAME:',name),concat('SAL:',salary) from emp;
7、查询每个人的年薪 12薪
select name,salary*12 from emp;
补充： as语法不仅能给字段起别名，还可以给表零时起别名
select emp.id,emp.id from emp ;
select emp.id,emp.id from emp as t1; 报错
select t1.id,t1.id from emp as t1;
```
### 分组注意事项
```
1、关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
2、where 筛选条件不能使用筛选条件
3、聚合函数只能在分组之后使用
select id,name,age from emp where max(salary) > 3000; 报错
select max(salary) from emp; # 不分组默认整体就是一组
# 题目： 统计各部门年龄在30岁以上的员工平均薪资
1、先求所有年龄大于30岁的员工
select * from emp where age>30;
2、在对结果进行分组
select * from emp where age>30 group by post;
3、最终结果
select post,avg(salary) from emp where age>30 group by post;
```
### having 分组之后的筛选
```
"""
having的语法和where是一致的，只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
聚合函数：max，avg，sum，count
"""
# 题目： 统计各部门年龄在30岁以上的员工工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) >10000
;
```
### distinct去重
```
"""
一定注意 必须是完全一样的数据才可以去重
一定不要将主键忽视，有主键存在的情况下是不能去重，因为主键都是不同的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
"""
# 这一行有主键，无法去重
select distinct id,age from emp;
# 这一行无主键，可以去重
select distinct age from emp;
```
### order by 排序
```
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary dasc;
"""
order by 默认是升序 asc 该asc可以省略不写
也可以修改为降序 desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排 如果碰到age相同,则按照salary升序排，后面还可以再跟
# 题目： 统计各部门年龄再10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资降序排序
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) >10000
order by avg(salary) desc
;
```
### limit限制展示条数
```
select * from emp;
"""当数据量多的时候容易造成卡死现象"""
select * from emp limit 3 # 只展示三条数据
select * from emp limit 0,5; #从0的位置往后取5条
select * from emp limit 5,5; #从5的位置往后取5条
1、第一个参数是起始位置
2、第二个参数是条数
```
### 正则
```
select * from emp where name regexp '^j.*(n|y)$';
j开头，n或者y结尾，*代表所有
```
### 多表查询
```
select * from dep,emp; # 结果 笛卡尔积
select * from emp,dep where emp.dep_id = dep.id;
"""
Mysql正对拼表操作专门开设了对应的方法
inner join 内连接
left join 左连接
right join 右连接
union 全连接
"""
# 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;
```
### 子查询
```
"""
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二部
...
将一个查询语句的结果当作另一个查询语句的条件使用
"""
# 题目：查询部门是技术或者人力资源的员工信息
1、获取部门的id号
2、再去员工表里面筛选出对应的员工
select id from dep where name='技术' or name='人力资源';
select name from emp where dep_id in (200,201);
select * from emp where dep_id in (select id from dep where name='技术' or name='人力资源');
```
### 总结
```
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把他作为一张虚拟表跟其他表关联
# 查询平均年龄再25岁以上的部门名称
"""只要是多表查询就有两种思路 连表，子查询"""
# 连表操作
1、先拿到部门和员工表 拼接之后的结果
2、分析语义 得出需要进行分组
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;
"""涉及到多表操作的时候，一定要加上表的前缀"""
# 子查询
select name from dep where id in
(select dep_id from emp group by dep_id
having avg(age)>25);
# 关键字 exist(了解)
只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists
(select id from dep where id>3);
```
## Navicat软件
```
1、MySQ是不区分大小写的
验证码忽略大小写
内部统一转大写或者小写比较即可
upper
lower
2、MySQL建议所有的关键字大写
3、MySQL中的注释有两种
--
#
4、再navicat中如何快速注释和解注释
ctrl + ？ 加注释
ctrl + ？ 基于以上操作再来一次解注释
ctrl + shift + ？ 老版本解开注释
```
## pymysql模块
```
"""
支持python代码操作数据库MySQL
"""
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user='root',
password='Huawei12#$',
database='goods_management',
) # 连接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象(就是用来执行命令的)
sql = 'select * from client;'
res = cursor.execute(sql)
print(res) # execute 返回当前sql语句影响的行数
print(cursor.fetchone()) # 只拿一条
print(cursor.fetchall()) # 拿所有
print(cursor.fetchmany(2)) # 指定拿2条
# 读取数据类似于文件光标的移动
cursor.scroll(1, 'relative') # 相对于光标所在位置继续往后移动一位
cursor.scroll(1, 'absolute') # 相对于数据的开头往后移动一位
```
## sql注入问题
```
"""
利用语法的特性，书写一些特点的语句实现固定的语法
利用MySQL的注释语法
"""
select * from passwd where name='asd' or 1=1 -- asdasd' and password=''
日常生活中很多软件在注册的时候都不含有特殊符号
因为容易构造出特定的语句入侵数据库 不安全
# 敏感的数据不要自己做拼接，交给execute方法即可
rows = cursor.execute(sql,(username,password))
例子：
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user='root',
password='Huawei12#$',
database='goods_management',
) # 连接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象(就是用来执行命令的)
username = input('>>>:')
password = input('>>>:')
sql = "select * from passwd where name=%s and password=%s"
# 不要手动拼接数据 先%s占位，之后将需要凭借的数据交给execute方法即可
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面的元组里面的数据替换
print(sql)
if rows:
print('登陆成功')
print(cursor.fetchall())
else:
print('登录失败')
```
## pymysql模块增删改查数据操作
```
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user='root',
password='Huawei12#$',
database='goods_management',
# autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象(就是用来执行命令的)
# 增
sql = 'insert into passwd(name,password) values(%s,%s)'
# rows = cursor.execute(sql,('admin',123))
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)]) #以下增加2条
print(rows)
conn.commit() # 确认
# 修改
sql = 'update passwd set name="hwnb" where id=2'
rows = cursor.execute(sql,)
print(rows)
conn.commit()
# 删
sql = 'delete from passwd where id=2'
rows = cursor.execute(sql,)
print(rows)
conn.commit()
# 查
sql = 'select * from passwd'
cursor.execute(sql)
print(cursor.fetchall())
"""
增删改查中
增 删 改 他们的操作涉及到数据的修改
需要二次确认
# conn.commit() # 确认
如果再上面加上autocommit=True后续则不需要确认
"""
1、增删改需要二次确认才能操作
2、批量增
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)])
```
## 视图(了解)
```
1、什么是视图
视图就是哦通过查询得到一张虚拟表，保存下来，下次直接使用
视图也是表
2、为什么要用
频繁操作一张虚拟表，这样就可以做成视图，后续直接操作
3、怎么用
固定语法
create view 视图名 as 虚拟表的查询sql语句
4、使用频率
不高
当创建很多视图之后，会造成表的不好维护
注意：
1、视图再硬盘上只有表结构，没有数据（数据还是来自于之前的表）
2、视图只用来查询 里面的数据不要修改，可能会影响到真正的表
```
## 触发器(了解)
```
是什么：
再满足对表数据进行增、删、改的情况下，自动触发的功能
有啥用：
可以帮助我们实现监控、日志...
语法结构：
触发器可以再六种情况自动触发
增前，增后，删前，删后，改前，改后
create trigger 触发器的名字 before/after insert/update on 表名
for each row
begin
sql语句
end
ps:修改MySQL默认的语句结束符,只作用于当前窗口
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') # no代表执行失败
);
create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
"""
当cmd表中的记录succes字段是no那么触发触发器的执行取errlog表中插入数据
NEW指代的是一条条数据对象
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if NEW.success = 'no' then
insert into errlog(err_cmd,err_time)
values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter;
# 删除触发器
drop trigger tri_after_insert_cmd
```
## 事务(需要掌握)
```
是什么：
开启一个事务可以包含多条sql语句 这些sql语句要么同时成功
要么一个都别想成功
作用：
保证了对数据操作的安全性
事务的四大特性：
ACID
A:原子性
一个事务是不可分割的单位，事务中包含这诸多操作
要么同时成功要摸同时失败
C:一致性
事务必须是使数据库从一个一致性状态变到另一个一致性状态
一致性跟原子性密切相关
I:隔离性
一个事务的执行不能为其他事务干扰
D:持久性
也叫"永久性"
一个事务一旦提交成功执行成功 那么他是将数据刷到硬盘中的，不会丢数据
如何使用：
1、开启事务
start transaction;
2、回滚(回到事务发生之前的状态)
rollback;
3、确认(确认之后就无法回滚)
commit;
"""模拟转账功能"""
create table user(
id int primary key auto_increment,
name char(16),
balance int
);
insert into user(name,blance) values
('jason',1000),
('egon',1000),
('tank',1000);
#1、先开启事务
start transaction;
#2、多条sql语句
update user set balance=900 where name = 'jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';
#3、回滚
rollback;
#4、确认
commit;
总结：
当你想让sql语句保持一致性的时候可以使用事务
```
## 存储过程(了解)
```
是什么
类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句，存储过程放于MySQL服务端中，可以直接通过存储过程触发内部sql语句执行
怎么用
固定语法
create procedure 存储过程的名字(形参1,形参2,....)
begin
sql代码
end
ps:修改MySQL默认的语句结束符,只作用于当前窗口
delimiter $$ 将默认的结束符号由;改为$$
# 调用
call 存储过程的名字
三种开发模型
1、第一种
应用程序：程序员写代码开发
MySQL：提前编写好存储过程，供应用程序调用
优点：提升开发效率
缺点：考虑到认为因素，跨部门沟通问题，后续的存储过程扩展性差
2、第二种
应用程序:程序员写代码开发之外，涉及到数据库操作也自己写
优点：扩展性高
缺点：开发效率低，编写sql语句太过繁琐，而且后续还要考虑sql优化问题
3、第三种
应用程序：只写程序代码，不写sql语句，基于别人写好的操作MySQL的python框架直接操作即可 ORM框架
优点：开发效率比上面两种情况都高
缺点：语句的扩展性差，可能会出现效率低下的问题
4、总结
第一种基本不用，一般都是第三种
"""存储过程具体演示"""
delimiter $$
create procedure p1(
in m int, # 只进不出 m不返回出去
in n int,
out res int, #该形参可以返回出去
)
begin
select tname from teacher where tid>m and tid<n;
set tes=0; #将res变量修改，用来标识当前存储代码执行
end $$
delimiter ;
# 正对形参res 不能直接传数据，应该传一个变量
# 定义变量
set @res = 10;
# 查看变量
select @res;
# 调用
call p1(1,5,@res)
```
## 在pymysql中调用存储过程
```
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user='root',
password='Huawei12#$',
database='goods_management',
# autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象(就是用来执行命令的)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
@_p1_0 = 1
@_p1_1 = 5
@_p1_2 = 10
"""
print(cursor.fetchall())
```
## 内置函数(了解)
```python
跟存储过程的区别：存储过程是自定义函数，函数类似于内置函数
```
## 流程控制(了解)
```
# 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 < 0 do
select
num;
set num = num + 1;
end while;
```
## 索引理论(了解)
```
是什么
数据都是存在硬盘上，我们需要通过索引来找到数据
类似于书的目录
优点：可以通过查询速度降低io操作
索引在MySQL中也叫做'键',是存储引擎用于快速查找记录的一种数据结构
1、primary key
2、unique key
3、index key
ps：foreign key 不是加速查询用的，不在研究范围内
上面三种key，前面两种除了增加查询速度之外各自还有约束条件
最后一种index key没有任何的约束条件，只是用来帮助快速查询速度
本质
通过不断缩小想要的数据范围，筛选出最终的结果，同时将随机事件变成顺序事件
也就是有了索引顺序，我们总可以通过一种固定的方式找到数据
一张表中可以有多个索引
缺点：
1、当表中有大量数据存在的前提下，创建索引速度慢
2、在索引创建完毕之后，写的性能会降低(当插入数据的时候索引需要拆掉重新创)
3、索引不要随意的创建
聚集索引 primary key
聚集索引指的就是主键
innodb 只有两个文件 直接将主键存放在了idb表中
Myisam 三个文件，单独将索引存放在一个文件
辅助索引 unique index
查询数据的时候不可能一直使用到主键，也可能会用到name,password等其他字段
这个时候无法用聚集索引,这个时候可以设置辅助索引也是个b+树
覆盖索引
在辅助索引的叶子节点上就已经拿到了需要的数据
```
