---相关网站
1 https://www.cnblogs.com/linhaifeng/articles/7495918.html
2 https://www.luffycity.com/home
3 https://book.apeland.cn/details/12/
关系型数据库
把复杂的数据逻辑归纳为二元结构（二维表格数据），表格与表格之间可以相互关联
便于查询
1 常见的关系型数据库
1 mysql ,小巧，免费，功能强大，目前市场上大多互联网公司都在使用
2 Microsoft SQL Server 微软数据库，只能在windows平台使用
3 oracle,oracle公司出品 速度快 稳定 贵 维护成本高
4 DB2.IBM提供的数据库 运行在UNIX上，生活中少见
01 数据库相关概念
1、数据库服务器：运行数据库管理软件的计算机
2、数据库管理软件：mysql，oracle，db2，slqserver
3、库：文件夹
4、表:文件
5、记录：事物一系列典型的特征：egon,male,18,oldgirl
6、数据：描述事物特征的符号
02 初识SQL语句
前期准备：
mysql服务端的启动与终止 net start（stop） mysql ；
切换用户： mysql -uroot -p
修改密码： mysqladmin -uroot -p password ‘123‘
查看当前使用用户 ： select user（）；
查看当前所在文件夹：select database（）；
SQL语句：
操作文件夹（库）
增
create database db1 charset utf8;
查
show create database db1;（显示编码格式）
show databases;
改
alter database db1 charset gbk;（修改编码格式）
删
drop database db1;
操作文件（表）
切换文件夹：use db1;
查看当前所在文件夹：select database();
增
create table t1(id int,name char);
查
show create table t1;
show tables;
desc t1; （显示表结构）
改
alter table t1 modify name char(6);
alter table t1 change name NAME char(7);
删
drop table t1;
操作文件内容（记录）
增
insert t1(id,name) values(1,‘egon1‘),(2,‘egon2‘),(3,‘egon3‘);
查
select id,name from db1.t1;
select * from db1.t1;
改
update db1.t1 set name=‘SB‘;
update db1.t1 set name=‘ALEX‘ where id=2;
删
delete from t1;
delete from t1 where id=2;
03 存储引擎介绍
1、什么是存储引擎？
存储引擎就是表的类型
2、查看MySQL支持的存储引擎
show engines;
3、指定表类型/存储引擎
create table t1(id int)engine=innodb;
create table t2(id int)engine=memory;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=myisam;
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
04 日期类型
create table student(
id int,
name char(6),
born_year year,
birth_date date,
class_time time,
reg_time datetime
);
insert into student values
(1,‘egon‘,now(),now(),now(),now());
insert into student values
(2,‘alex‘,"1997","1997-12-12","12:12:12","2017-12-12 12:12:12");
05 字符类型
char:定长
varchar：变长
#宽度指的是字符的个数
create table t13(name char(5));
create table t14(name varchar(5));
insert into t13 values(‘李杰 ‘); #‘李杰 ‘
insert into t14 values(‘李杰 ‘); #‘李杰 ‘
select char_length(name) from t13; #5
select char_length(name) from t14; #3
select name from t13 where name=‘李杰‘;
select name from t13 where name like ‘李杰‘;
name char(5)
egon |alex |wxx |
name varchar(5)
1bytes+egon|1bytes+alex|1bytes+wxx|
4+egon|4+alex|3+wxx|
06 枚举类型和集合类型
create table consumer(
id int,
name char(16),
sex enum(‘male‘,‘female‘,‘other‘),
level enum(‘vip1‘,‘vip2‘,‘vip3‘), # 一个
hobbies set(‘play‘,‘music‘,‘read‘,‘run‘) # 多个
);
insert into consumer values
(1,‘egon‘,‘male‘,‘vip2‘,‘music,read‘);
insert into consumer values
(1,‘egon‘,‘xxxxx‘,‘vip2‘,‘music,read‘); # 没有为空
07约束条件 not null 与 default
create table t15(
id int(11) unsigned zerofill
);
create table t16(
id int,
name char(6),
sex enum(‘male‘,‘female‘) not null default ‘male‘
);
insert into t16(id,name) values(1,‘egon‘);
08 约束条件 unique key
unique key
单列唯一
#方式一
create table department(
id int unique,
name char(10) unique
);
#方式二：
create table department(
id int,
name char(10)，
unique(id),
unique(name)
);
insert into department values
(1,‘IT‘),
(2,‘Sale‘);
联合唯一
create table services(
id int,
ip char(15),
port int,
unique(id),
unique(ip,port)
);
insert into services values
(1,‘192.168.11.10‘,80),
(2,‘192.168.11.10‘,81),
(3,‘192.168.11.13‘,80);
insert into services values
(4,‘192.168.11.10‘,80);
09 primary key
primary key
约束：not null unique
存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须有一个主键
# 单列主键
create table t17(
id int primary key,
name char(16)
);
insert into t17 values
(1,‘egon‘),
(2,‘alex‘);
insert into t17 values
(2,‘wxx‘);
insert into t17(name) values
(‘wxx‘);
create table t18(
id int not null unique,
name char(16)
);
# 复合主键
create table t19(
ip char(15),
port int,
primary key(ip,port)
);
insert into t19 values
(‘1.1.1.1‘,80),
(‘1.1.1.1‘,81);
10 约束条件之auto_increment
auto_increment
create table t20(
id int primary key auto_increment,
name char(16)
);
insert into t20(name) values
(‘egon‘),
(‘alex‘),
(‘wxx‘);
insert into t20(id,name) values
(7,‘yuanhao‘);
insert into t20(name) values
(‘egon1‘),
(‘egon2‘),
(‘egon3‘);
#了解
show variables like ‘auto_inc%‘;
#步长：
auto_increment_increment默认为1
#起始偏移量
auto_increment_offset默认1
#设置步长
set session auto_increment_increment=5;
set global auto_increment_increment=5;
#设置起始偏移量
set global auto_increment_offset=3;
强调：起始偏移量<=步长
create table t21(
id int primary key auto_increment,
name char(16)
);
insert into t21(name) values
(‘egon‘),
(‘alex‘),
(‘wxx‘),
(‘yxx‘);
清空表：
delete from t20;
delete from t20 where id = 3;
insert into t20(name) values
(‘xxx‘);
truncate t20; #应该用它来清空表
11 约束条件之foreign key
foreign key:建立表之间的关系
#1、建立表关系：
#先建被关联的表,并且保证被关联的字段唯一
create table dep(
id int primary key,
name char(16),
comment char(50)
);
#再建立关联的表
create table emp(
id int primary key,
name char(10),
sex enum(‘male‘,‘female‘),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade #同步删除
on update cascade #同步更新
);
#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");
#再往关联表插入记录
insert into emp values
(1,‘egon‘,‘male‘,1);
insert into emp values
(2,‘alex‘,‘male‘,1),
(3,‘wupeiqi‘,‘female‘,2),
(4,‘yuanhao‘,‘male‘,3),
(5,‘jinximn‘,‘male‘,2);
delete from emp where dep_id=1;
delete from dep where id=1;
delete from dep where id=3;
12 表之间的关系
foreign key:建立表之间的关系
#1、建立表关系：
#先建被关联的表,并且保证被关联的字段唯一
create table dep(
id int primary key,
name char(16),
comment char(50)
);
#再建立关联的表
create table emp(
id int primary key,
name char(10),
sex enum(‘male‘,‘female‘),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade #同步删除
on update cascade #同步更新
);
#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");
#再往关联表插入记录
insert into emp values
(1,‘egon‘,‘male‘,1);
insert into emp values
(2,‘alex‘,‘male‘,1),
(3,‘wupeiqi‘,‘female‘,2),
(4,‘yuanhao‘,‘male‘,3),
(5,‘jinximn‘,‘male‘,2);
delete from emp where dep_id=1;
delete from dep where id=1;
delete from dep where id=3;
12 表之间的关系
两张表之间的关系：
1、多对一
出版社 书（foreign key(press_id) references press(id)）
2、多对多
作者 书
egon:
九阳神功
九阴真经
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(2,1),
(2,6);
3、一对一
customer表 student表
13 单表查询
三 简单查询
复制代码
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
#创建表
create table employee(
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), # 15为数字个数，2为小数个数
office int, #一个部门一个屋子
depart_id int
);
#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum(‘male‘,‘female‘) | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
#插入记录insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部
(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),
(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),
(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),
(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),
(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),
(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),
(‘成龙‘,‘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
复制代码
复制代码
#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
SELECT * FROM employee;
SELECT name,salary FROM employee;
#避免重复DISTINCT
SELECT DISTINCT post FROM employee; （distinct 不同种类的）
#通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
#定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT(‘姓名: ‘,name,‘ 年薪: ‘, salary*12) AS Annual_salary
FROM employee;
CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS(‘:‘,name,salary*12) AS Annual_salary
FROM employee;
结合CASE语句：
SELECT
(
CASE
WHEN NAME = ‘egon‘ THEN
NAME
WHEN NAME = ‘alex‘ THEN
CONCAT(name,‘_BIGSB‘)
ELSE
concat(NAME, ‘SB‘)
END
) as new_name
FROM
emp;
复制代码
小练习：
1 查出所有员工的名字，薪资,格式为
<名字:egon> <薪资:3000>
2 查出所有的岗位（去掉重复）
3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
单表查询
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段（优先级在 distinct 之后）
limit n;
#where
select id,name,age from employee where id > 7;
# and
select name,post,salary from employee where post=‘teacher‘ and salary > 8000;
# between.. and..
select name,salary from employee where salary >= 20000 and salary <= 30000;
select name,salary from employee where salary between 20000 and 30000;
# not between and
select name,salary from employee where salary < 20000 or salary > 30000;
select name,salary from employee where salary not between 20000 and 30000;
# or 或者 in
select * from employee where age = 73 or age = 81 or age = 28;
select * from employee where age in (73,81,28);
# is (is not)
select * from employee where post_comment is Null;
select * from employee where post_comment is not Null;
# like 模糊匹配
select * from employee where name like "jin%";（% 任意字符）
select * from employee where name like "jin___";（___3个任意字符）
#group by
mysql> set global sql_mode="ONLY_FULL_GROUP_BY";
分组之后，只能取分组的字段，以及每个组聚合结果
select post from employee group by post;
#聚合函数
max
min
avg
sum
count
#每个职位有多少个员工
select post,count(id) as emp_count from employee group by post;
select post,max(salary) as emp_count from employee group by post;
select post,min(salary) as emp_count from employee group by post;
select post,avg(salary) as emp_count from employee group by post;
select post,sum(age) as emp_count from employee group by post;
#没有group by则默认整体算作一组
select max(salary) from employee;
#group_concat
select post,group_concat(name) from employee group by post;
#having （过滤，分组之后）
select post,group_concat(name),count(id) from employee group by post;
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
select post,avg(salary) from employee group by post having avg(salary) > 10000;
#order by 排序
select * from employee order by age asc; #升序
select * from employee order by age desc; #降序
select * from employee order by age asc,id desc; #先按照age升序排，如果age相同则按照id降序排
select distinct post,count(id) as emp_count from employee
where salary > 1000
group by post
having count(id) > 1
order by emp_count desc
;
#limit （限制条数）
select * from employee limit 3;
select * from employee order by salary desc limit 1;
select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;
#总结：
一 SELECT语句关键字的定义顺序
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
（select distinct 字段1,字段2,字段3 from 库.表
from
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;显示条数）
执行顺序：
二 关键字的执行优先级(重点)
重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
详细见：http://www.cnblogs.com/linhaifeng/articles/7372774.html
#正则表达式
select * from employee where name like ‘jin%‘;
select * from employee where name regexp ‘^jin‘; （^以jin开头）
select * from employee where name regexp ‘^jin.*(g|n)$‘;（^以jin开头，$以（g或n结尾））
14 连表操作
# 交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department;
内连接：只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id ;
左连接:在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id ;
右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id ;
全外连接：在内连接的基础上保留左右两表没有对应关系的记录
select * from employee full join department on employee.dep_id = department.id ;
（不支持full_join）
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id ;
-练习题
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;
#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
13、查询全部学生都选修了的课程号和课程名
17、查询平均成绩大于85的学生姓名和平均成绩
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
--- 子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
1 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name=‘技术‘);
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
2 带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)
17 权限管理
权限管理
select * from mysql.user\G # 查询相关用户 授权信息
1、创建账号
# 本地账号
create user ‘egon1‘@‘localhost‘ identified by ‘123‘; # mysql -uegon1 -p123
# 远程帐号
create user ‘egon2‘@‘192.168.31.10‘ identified by ‘123‘; # mysql -uegon2 -p123 -h 服务端ip
create user ‘egon3‘@‘192.168.31.%‘ identified by ‘123‘; # mysql -uegon3 -p123 -h 服务端ip
create user ‘egon3‘@‘%‘ identified by ‘123‘; # mysql -uegon3 -p123 -h 服务端ip
2、授权
user:*.*
db:db1.*
tables_priv:db1.t1
columns_priv:id,name
grant all on *.* to ‘egon1‘@‘localhost‘; # 授权
grant select on *.* to ‘egon1‘@‘localhost‘;
revoke select on *.* from ‘egon1‘@‘localhost‘; 取消，废除
grant select on db1.* to ‘egon1‘@‘localhost‘;
revoke select on db1.* from ‘egon1‘@‘localhost‘;
3 平常使用(创建+授权)
grant all on *.* to ‘root‘@‘%‘ identified by ‘123‘
flush privileges; (刷新)
grant select on db1.t2 to ‘egon1‘@‘localhost‘;
revoke select on db1.t2 from ‘egon1‘@‘localhost‘;
grant select(id,name),update(age) on db1.t2 to ‘egon1‘@‘localhost‘;
18 Navicat工具的使用
用于自己开发和调试
一 IDE工具介绍
生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具
下载链接：https://pan.baidu.com/s/1bpo5mqj
掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表
#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键
19 pymtsql模块
# 安装
pip install pymysql
# --链接，执行sql，关闭（游标）
user=input(‘用户名: ‘).strip()
pwd=input(‘密码: ‘).strip()
#链接
conn=pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘123‘,database=‘egon‘,charset=‘utf8‘)
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#执行sql语句
sql=‘select * from userinfo where name="%s" and password="%s"‘ %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res)
cursor.close()
conn.close()
if res:
print(‘登录成功‘)
else:
print(‘登录失败‘)
18 视图
一 视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，
用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
一 创建视图
#语法：CREATE VIEW 视图名称 AS SQL语句
create view teacher_view as select tid from teacher where tname=‘李平老师‘;
#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view)
二 触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
19 存储过程
一 介绍
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点：
#1. 用于替代程序写的SQL语句，实现程序与sql解耦
#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
使用存储过程的缺点：
#1. 程序员扩展功能不方便
#1、无参存储过程
delimiter //
create procedure p1()
BEGIN
select * from db7.teacher;
END //
delimiter ;
# MySQL中调用
call p1();
# Python中调用
cursor.callproc(‘p1‘)
#2、有参存储过程
delimiter //
create procedure p2(in n1 int,in n2 int,out res int)
BEGIN
select * from db7.teacher where tid > n1 and tid < n2;
set res = 1;
END //
delimiter ;
# MySQL中调用
set @x=0
call p2(2,4,@x);
select @x;
# Python中调用
cursor.callproc(‘p2‘,(2,4,0))# @_p2_0=2,@_p2_1=4,@_p2_2=0
cursor.execute(‘select @_p3_2‘)
cursor.fetchone()
应用程序与数据库结合使用
方式一：
Python:调用存储过程
MySQL:编写存储过程
方式二：
Python：编写纯生SQL
MySQL：
方式三：
Python：ORM->纯生SQL
MySQL：
# 存储过程的执行
#1、增删改
import pymysql
# 建立链接
conn=pymysql.connect(
host=‘192.168.10.15‘,
port=3306,
user=‘root‘,
password=‘123‘,
db=‘db7‘,
charset=‘utf8‘
)
# 拿游标
cursor=conn.cursor()
# 执行sql
# cursor.callproc(‘p1‘)
# print(cursor.fetchall())
cursor.callproc(‘p2‘,(2,4,0))
# print(cursor.fetchall())
cursor.execute(‘select @_p2_2‘)
print(cursor.fetchone())
# 关闭
cursor.close()
conn.close()
mysql 知识点