mysql表相关操作

一存储引擎

存储引擎	特点
myisam	MySQL5.5版本之前默认的存储引擎，速度要比Innodb更快但是我们更加注重的是数据的安全
memory	内存引擎(数据全部存放在内存中) 断电数据丢失
blackhole	无论存什么，都立刻消失(黑洞)
Innodb	MySQL5.5版本及之后默认的存储引擎，存储数据更加的安全

# 查看所有的存储引擎
show engines;

# 不同的存储引擎在存储表的时候 异同点
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;

# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

二创建表

#创建表语法
create table 表名(
	字段名1 类型(宽度) 约束条件,
    字段名2 类型(宽度) 约束条件,
    字段名3 类型(宽度) 约束条件
)

# 注意
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
	约束条件写的话 也支持写多个
    字段名1 类型(宽度) 约束条件1 约束条件2...,
	create table t5(id);  报错
3 最后一行不能有逗号
	create table t6(
        id int,
        name char,
    );   报错
    
#mysql 严格模式的不同
一般情况下指的是对存储数据的限制
create table t7(name char);  默认宽度是1
insert into t7 values('jason');
insert into t7 values(null);  关键字NULL
针对不同的版本会出现不同的效果
   5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符，那么我会自动帮你截取
   5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超，一旦超出范围立刻报错 Data too long for ....
"""严格模式到底开不开呢？"""
MySQL5.7之后的版本默认都是开启严格模式的

三基本数据类型

3.1 int类型

分类：TINYINT SMALLINT MEDUIMINT INT BIGINT
作用：存储年龄、等级、id、号码等等

# 针对整型 括号内的宽度是指定数值的位数
create table t12(id int(8));
insert into t12 values(123456789);

"""
特例:只有整型括号里面的数字不是表示限制位数
id int(8)
	如果数字没有超出8位 那么默认用空格填充至8位
	如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围)
"""

create table t13(id int(8) unsigned zerofill);
# 用0填充至8位，unsingned无符号，zerofille非空(null)

# 总结:
针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了

3.2 浮点型

分类：FLOAT、DOUBLE、DECIMAL
作用：身高、体重、薪资

# 存储限制
float(255,30)  # 总共255位 小数部分占30位
double(255,30)  # 总共255位 小数部分占30位
decimal(65,30)  # 总共65位 小数部分占30位

# 精确度验证
create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));

insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);

float < double < decimal
# 要结合实际应用场景 三者都能使用

3.3 字符类型

分类：char varchar

create table t18(name char(4));
create table t19(name varchar(4));
  
insert into t18 values('a');
insert into t19 values('a');
  
# 介绍一个小方法 char_length统计字段长度
select char_length(name) from t18;
select char_length(name) from t19;
"""
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的
但是在显示的时候MySQL会自动将多余的空格剔除
"""

# 再次修改sql_mode 让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

#char 和varchar的区别
char
  缺点:浪费空间
  优点:存取都很简单
  	直接按照固定的字符存取数据即可
  	jason egon alex wusir tank 
  	存按照五个字符存 取也直接按照五个字符取
  		
varchar
  优点:节省空间
  缺点:存取较为麻烦
  	1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 
  		
  	存的时候需要制作报头
  	取的时候也需要先读取报头 之后才能读取真实数据
  		
 以前基本上都是用的char 其实现在用varchar的也挺多

3.4 时间类型

分类：
	date:年月日 2020-5-4
	datetime:年月日时分秒  2020-5-4 11:11:11
	time:时分秒11:11:11
	Year:2020
	
create table student(
  id int,
  name varchar(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);
insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');

3.5 枚举与集合类型

分类：  枚举(enum)--> 多选一    集合(set)--> 多选多

create table user(
  id int,
    name char(16),
    gender enum('male','female','others')
);
insert into user values(1,'jason','male');  正常
insert into user values(2,'egon','xxxxooo');  报错
# 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 


create table teacher(
  id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','DBJ','hecha')
);
insert into teacher values(1,'jason','male','read');  正常
insert into teacher values(2,'egon','female','DBJ,hecha');  正常
insert into teacher values(3,'tank','others','生蚝'); 报错
# 集合可以只写一个  但是不能写没有列举的

四创建表(extend)

4.1 unique唯一

# 单列唯一
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和port
单个都可以重复 但是加载一起必须是唯一的
"""
create table t4(
	id int,
    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',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080);  报错

4.2 主键

"""
1.单单从约束效果上来看primary key等价于not null + unique
非空且唯一！！！
"""
create table t5(id int primary key);
insert into t5 values(null);  报错
insert into t5 values(1),(1);  报错
insert 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 t7(
    ip char(16),
    port int,
    primary key(ip,port)
);

"""
也意味着 以后我们在创建表的时候id字段一定要加primary key
"""

4.3 auto_increment自增

# 当编号特别多的时候 人为的去维护太麻烦
create table t8(
	id int primary key auto_increment,
    name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');

# 注意auto_increment通常都是加在主键上的 不能给普通字段加
create table t9(
	id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

4.4 删除表

delete from t1  #删除表中数据后 主键的自增不会停止

truncate t1  # 清空表数据并且重置主键

五表关系

分类：一对多、多对多关系、一对一关系、没有关系

5.1 一对多

'''
员工表与部门表为例
	先站在员工表
		思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
			不能！！！
			(不能直接得出结论 一定要两张表都考虑完全)
	再站在部门表
		思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
			能！！！
	得出结论
		员工表与部门表示单向的一对多
		所以表关系就是一对多
'''

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','others') 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教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

5.2 多对多

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  # 同步删除
);
"""
按照上述的方式创建 一个都别想成功！！！
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系 不能在两张原有的表中创建外键
需要你单独再开设一张 专门用来存储两张表数据之间的关系
"""
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  # 同步删除
);

5.3 一对一

"""
id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二  
	用户表
		用户表
			id name age
		用户详情表
			id addr phone hobby email........
	
	站在用户表
		一个用户能否对应多个用户详情   不能！！！
	站在详情表
		一个详情能否属于多个用户      不能！！！
	结论:单向的一对多都不成立 那么这个时候两者之间的表关系
		就是一对一
		或者没有关系(好判断)

客户表和学生表
	在你们报名之前你们是客户端
	报名之后是学生(期间有一些客户不会报名)
"""

一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
create table authordetail(
	id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
)

六修改表

#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 旧表;  不能复制主键 外键 ...

create table new_dep2 select * from dep where id>3;

八查询表

#准备表用来测试
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), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','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);


# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK

8.1 where

# 作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name 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);

# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	like
		%  匹配任意多个字符
		_  匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

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

# 5.查询id小于3或者id大于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;

8.2 group by

# 分组实际应用场景  分组应用场景非常的多
	男女比例
    部门平均薪资
    部门秃头率
    国家之间数据统计

# 1	按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 还不再是组内的单个数据
	上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
	如果设置了严格模式 那么上述命令会直接报错 
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';

设置严格模式之后  分组 默认只能拿到分组的依据
select post from emp group by post;  
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)


"""
什么时候需要分组啊？？？
	关键字 
		每个 平均 最高 最低 
	
	聚合函数
		max
		min
		sum
		count
		avg
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# 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;

# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;

# 查询每个人的年薪  12薪
select name,salary*12 from emp;



#注意：
# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
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;
    
    select post,avg(salary) from emp where age>30 group by post;

8.3 having

"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
		where age>30 
    	group by post
        having avg(salary) > 10000
        ;

8.4 distinct去重

"""
一定要注意 必须是完全一样的数据才可以去重！！！
一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
ORM  对象关系映射   让不懂SQL语句的人也能够非常牛逼的操作数据库
表								类
一条条的数据						对象
字段对应的值						对象的属性

你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
"""
select distinct id,age from emp;
select distinct age from emp;

8.5 order by排序

select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
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>10 
    	group by post
        having avg(salary) > 1000
        order by avg(salary) desc
        ;

8.6 limit

select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3;  # 只展示三条数据

select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数

8.7 正则

select * from emp where name regexp '^j.*(n|y)$';

8.8 exists关键字

只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists 
    (select id from dep where id>3);
        
        
select * from emp where exists 
    (select id from dep where id>300);

8.9 多表查询

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   左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL

# right join  右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL

# 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;

8.10 子查询

"""
子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
	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 = '人力资源');

9 视图

 """

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

 """
    
1、为什么要用视图？

如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图 后续直接操作。

2、如何操作视图？
  # 固定语法
  create view 表名 as 虚拟表的查询sql语句
  
  # 具体操作
  create view teacher2course as
  select * from teacher INNER JOIN course
  on teacher.tid = course.teacher_id
  ;

注意： 
	"""
  	1 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表)
  	2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表
  	"""
    
3、视图到底使用频率高不高呢？
  """
  不高
  当你创建了很多视图之后 会造成表的不好维护 
  """

10 触发器

在满足对表数据进行增、删、改的情况下，自动触发的功能

使用触发器可以帮助我们实现监控、日志...

触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后

#基本语法结构
create trigger 触发器的名字  before/after insert/update/delete on 表名
for each row
begin
	sql语句
end

示例：插入前触发
create trigger tri_before_insert_t1  before insert on t1
for each row
begin
	sql语句
end


ps:修改MySQL默认的语句结束符，只作用于当前窗口
	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代表执行失败
);

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 ;

# 朝cmd表插入数据
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

# 删除触发器
drop trigger tri_after_insert_cmd;

11 事务

1、什么是事务？
  """
  开启一个事务可以包含多条sql语句 这些sql语句要么同时成功
  要么一个都别想成功 称之为事务的原子性
  """
  
2、事务的作用？
  """
  保证了对数据操作的安全性
  """
  eg:还钱的例子
      egon用银行卡给我的支付宝转账1000
      	1 将egon银行卡账户的数据减1000块
          2 将jason支付宝账户的数据加1000块
      
     	你在操作多条数据的时候可能会出现某几条操作不成功的情况 
     	
3、事务的四大特性
  """
  ACID
  A:原子性
  	一个事务是一个不可分割的单位，事务中包含的诸多操作
  	要么同时成功要么同时失败
  C:一致性
  	事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态
  	一致性跟原子性是密切相关的
  I:隔离性
  	一个事务的执行不能被其他事务干扰
  	（即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的）
  D:持久性
  	也叫"永久性"
  	一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的
  	接下来的其他操作或者故障不应该对其有任何的影响
  """
  
4、如何使用事务
  # 事务相关的关键字
  # 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,balance) 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';
  
  """
  总结
  	当你想让多条sql语句保持一致性 要么同时成功要么同时失败 
  	你就应该考虑使用事务
  """

12 存储过程

存储过程就类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行

#基本使用
create procedure 存储过程的名字(形参1,形参2,...)
begin
  sql代码
end
# 调用
call 存储过程的名字();

示例：
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 res=666;  # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;

# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;

使用示例：

在pymysql模块中如何调用存储过程呢？

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())

13 流程控制

# 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 ;

posted @ 2022-04-15 21:09 彬彬l 阅读(9) 评论(0) 编辑收藏举报

刷新页面返回顶部

mysql表相关操作

一 存储引擎

二 创建表

三 基本数据类型

3.1 int类型

3.2 浮点型

3.3 字符类型

3.4 时间类型

3.5 枚举与集合类型

四 创建表(extend)

4.1 unique唯一

4.2 主键

4.3 auto_increment自增

4.4 删除表

五 表关系

5.1 一对多

5.2 多对多

5.3 一对一

六 修改表

七 复制表

八 查询表

8.1 where

8.2 group by

8.3 having

8.4 distinct去重

8.5 order by排序

8.6 limit

8.7 正则

8.8 exists关键字

8.9 多表查询

8.10 子查询

9 视图

10 触发器

11 事务

12 存储过程

13 流程控制

一存储引擎

二创建表

三基本数据类型

四创建表(extend)

五表关系

六修改表

七复制表

八查询表