4.数据库之视图,触发器,事件
视图
#创建表 create table course( cid int primary key auto_increment, cname char(20), teacher_id int ); #插入数据 mysql> insert into course values(1,'生物',1),(2,'物理',2),(3,'体育',3); #显示数据 mysql> select * from course; +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | +-----+-------+------------+ 3 rows in set (0.08 sec) #创建视图 create view course_view as select * from course; mysql> select * from course_view; +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | +-----+-------+------------+ 3 rows in set (0.10 sec) #修改数据 mysql> update course_view set cname = "物理2" where cid = 2; mysql> select * from course; +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 1 | 生物 | 1 | | 2 | 物理2 | 2 | | 3 | 体育 | 3 | +-----+-------+------------+ 3 rows in set (0.14 sec) #插入数据 mysql> insert into course values(4,'语文',4),(5,"数学",5); #修改视图 mysql> alter view course_view as select * from course where cid > 3; mysql> select * from course_view; +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 4 | 语文 | 4 | | 5 | 数学 | 5 | +-----+-------+------------+ 2 rows in set (0.16 sec) #删除视图 drop view course_view;
触发器
#创建表 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 // create trigger tri_after_inser_cmd_log after insert on cmd_log for each row begin if new.is_success = 'no' then insert into err_log(cname,stime) values(new.cmd_name,new.sub_time); end if; #记录加分号,加分号代表结束 end // delimiter ; #还原初始状态 #写入记录,触发触发器,根据if条件决定是否插入错误日志 insert into cmd( user, priv, cmd, sub_time, success ) values ('chao','0755','ls -l /etc',NOW(),'yes'), ('chao','0755','cat /etc/passwd',NOW(),'no'), ('chao','0755','useradd xxx',NOW(),'no'), ('chao','0755','ps aux',NOW(),'yes'); mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2019-01-22 19:49:39 | | 2 | useradd xxx | 2019-01-22 19:49:39 | +----+-----------------+---------------------+ 2 rows in set (0.19 sec) #删除触发器 drop trigger tir_after_insert_cmd;
事务
事务的特点:原子性,一致性,隔离性,持久性.
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values('wsb',1000), ('qian',1000), ('ysb',1000); #原子性 start transaction; update user set balance = 900 where name = 'wsb';#支付100元 update user set balance = 1010 where name = 'qian';#中介拿走10元 update user set balance = 1090 where name = 'ysb';#卖家拿到90元 commit; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 900 | | 2 | qian | 1010 | | 3 | ysb | 1090 | +----+------+---------+ 3 rows in set (0.10 sec) #出现异常,滚回到初始状态 start transaction; update user set balance = 900 where name = 'wsb';#支付100元 update user set balance = 1010 where name = 'qian';#中介拿走10元 update user1 set balance = 1090 where name = 'ysb';#卖家拿到90元 rollback; commit; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | qian | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.11 sec) #通过存储过程来捕捉异常 delimiter // create procedure p1() begin DECLARE exit handler for sqlexception begin rollback; end; start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='qian'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 commit; end // delimiter ; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | qian | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.11 sec)
存储过程
存储过程的优点:
1.用于替代程序写的SQL语句,实现程序与sql解耦
2.基于网络传输,传别名的数据量小,而直接传sql数据量大
存储过程的缺点:
1.程序员扩展功能不方便
创建简单的存储过程(无参)
create table t1( id int primary key auto_increment, name char(20), dep_id int ); delimiter // create procedure x1() begin select * from t1; insert into t1(name,dep_id) values("qian",1); end // delimiter ; call x1(); #调用
创建简单的存储过程(有参)
in 仅用于传入参数用
delimiter // create procedure x2( in m int , #从外部传入的值 in n int ) begin insert into t1(name,dep_id) values('alex',2), ('wusir',3); select * from t1 where id between m and n; end // delimiter ; call x2(2,4);
out 仅用于返回值
delimiter // create procedure x3( in m int, in n int, out res int ) begin select * from t1 where id between m and n; set res = 1; #如果不设值 res返回null end // delimiter ; set @res = 1111; call x3(4,7,@res); select @res;
inout 即可以传入又可以当返回值
delimiter // create procedure x4( inout m int ) begin select * from t1 where id > m; set m = 1; end // delimiter ; set @m = 2; call x4(@m); select @m;
import pymysql conn = pymysql.connect( host = 'localhost', user = 'root', password='root', database = 'alex', charset = 'utf8' ) cursor = conn.cursor(pymysql.cursors.DictCursor) #以字典的形式输出 # rows = cursor.callproc('x1') #1.调用存储过程的方法 ,没参数时 # rows = cursor.callproc('x2',args=(2,6)) #有参数时 rows = cursor.callproc('x3', args=(2,6,9)) #@_x3_0=2,@_x3_1=6 ,@_x3_2=9 #有参数时 conn.commit() #执行 print(cursor.fetchall()) cursor.execute('select @_x3_0,@_x3_1,@_x3_2') print(cursor.fetchall()) cursor.close() conn.close()
删除存储过程
drop procedure x1;
关于查看存储过程,函数,视图,触发器的语法:
-- 查询数据库中的存储过程和函数 select name from mysql.proc where db = 'xx' and type = 'procedure' //存储过程 select name from mysql.proc where db = 'xx' and type = 'function' //函数 show procedure status; show function status; -- 查看存储过程或函数的创建代码 show create procedure proc_name; show create function func_name; -- 查看视图 show triggers[from db_name] [like expr] select * from triggers T where trigger_name = 'mytrigger' \G
流程控制
delimiter // create function f1( i int ) returns int begin declare res int default 0; if i = 10 then set res = 100; elseif i = 20 then set res = 200; elseif i = 30 then set res = 300; else set res = 400; end if; return res; end // delimiter ;
数据库内容回顾 数据库的安装(重点) 修改root用户的密码(重点) 修改字符集编码(重点) 数据类型: 数值类型:整型 浮点型 字符串:(重点) char(定长) : 插入数据或查询数据都很快,因为char在磁盘上插入数据的时候的存储空间是固定的,简单粗暴,直接就是定长空间,那么就不需要考虑数据的长度,所以我们在进行数据查询的时候,速度也快,因为在读取数据的是也不需要考虑数据长度,简单粗暴就按照定长的空间来取数据,也导致一下空间的浪费. varchar(不定长):插入和查询速度都相对较慢,因为它在内容存储数据的时候,是按照数据的长度来进行存储的,那么每次存储数据都需要计算一下数据的长度,按照长度来开辟存储空间,那么在数据的存储空间前面还要开辟1-2个字节空间长度来存储数据的长度,也就是说格式大概是长度+内容,也导致了在我们读取数据的时候,首先要先读取数据的长度,然后根据长度在读取后面的内容,导致速度较慢.但是多数情况下可以节省存储空间 日期类型 : Year Date Time Datetime(重点) timestamp 枚举类型(enum(‘1’,’2’),单选) 集合类型:set(‘1’,’2’,’3’) 多选 完整性约束: Not null : 不为空 字段1设置了not null Insert into t1(字段2,字段3...) values(字段2的值,....) Default : 默认值 default 2 Unique 唯一,不允许重复 Primary key :主键, 唯一非空 not nul + unique 必须有一个主键字段,且只能有一个 1. 自动查看你所有的字段里面是否有not nul + unique,如果有默认就将这个字段设置为主键字段 2. 自动给你设置一个看不到的字段作为主键. Auto_increment 自增,一般加在主键后面,从1开始每次+1,可以设置步长,可以设置起始值 Foreign key : 外键,建立表之间关系用的 一对多 : T1(id) id 1 一对多 t2 T1里面的一条数据,可以对应t2表里面的多条数据 T2表里面加一个字段,t1_id,1,1,1,1 建立外键关系的时候,t1表的id字段是被关联的字段:不为空 且唯一 Create table t2( -- 字段名 数据类型(宽度) 约束条件, 字段名和数据类型是必须的 Id int primary key auto_increment, Name char(10) not null, Sex enum(‘男’,’女’) default “男” not null, Id_card char(18) not null unique, T1_id int not null, Constraint fk_t2_t1 Foreign key(t1_id) references t1(id) ) 一对一 Create table t2( -- 字段名 数据类型(宽度) 约束条件, 字段名和数据类型是必须的 Id int primary key auto_increment, Name char(10) not null, Sex enum(‘男’,’女’) default “男” not null, Id_card char(18) not null unique, T1_id int not null unique, Constraint fk_t2_t1 Foreign key(t1_id) references t1(id) ) T1 id 1 t2 t1_id 1 多对多 建立第三章表 T3 T3 id t1_id t2_id 库的操作 增加:create database 库名 删:drop database 库名 改:alter database 库名 charset utf8; 查:show databases; Show create database 库名 \G; \c 表的操作 增:create table 表名( -- 字段名 数据类型(宽度) 约束条件, 字段名和数据类型是必须的, -- 字段名 数据类型(宽度) 约束条件, 字段名和数据类型是必须的 ) 删:drop table 表名; 改(表字段的修改,表结构的修改): Alter table 表名 rename 新表名 Alter table 表名 modify 字段名 数据类型 完整性约束 Change 旧字段名 新的字段名 数据类型 完整性约束 Alter table 表名 add 字段名 数据类型 完整性约束 first; After 字段名 Alter table 表名 add foreign key(c_id) references class(id); 查:show tables; 行记录的操作 增:insert into 表名 values() 删:delete from 表名 where condition 1=1 id=10 改:update 表名 set name=’baobao’ where id = 10; 单表查询:select * from t1 where 1=1 id=1; Select distinct 字段... from 库名.表名 Where condition Group by Having Order by limit 多表查询: 笛卡尔积:将两表的所有记录全部对应一遍 Select * from emp,dep where emp.dep_id = dep.id; 这样获得了一个包含量表所有字段的对应关系数据的一张虚拟表 连表操作: Inner join left join right join union 子查询:将子查询的结果作为另外一个查询 语句的筛选条件 今日内容: 如果我们在id字段上加了主键索引,只有select * from t1 where id=100; Where后面的条件中没有用到id,那么久没有用到主键索引,select * from t1 where name=xxx; 所以mysql里面又提供了辅助索引 Select name,age from t1 where name=’xxx’; 索引: 添加索引: 添加主键索引: 创建的时候添加: 添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加. 聚集索引的添加方式 创建的是添加 Create table t1( Id int primary key, ) Create table t1( Id int, Primary key(id) ) 表创建完了之后添加 Alter table 表名 add primary key(id) 删除主键索引: Alter table 表名 drop primary key; 唯一索引: Create table t1( Id int unique, ) Create table t1( Id int, Unique key uni_name (id) ) 表创建好之后添加唯一索引: alter table s1 add unique key u_name(id); 删除: Alter table s1 drop index u_name; 普通索引: 创建: Create table t1( Id int, Index index_name(id) ) Alter table s1 add index index_name(id); Create index index_name on s1(id); 删除: Alter table s1 drop index u_name; DROP INDEX 索引名 ON 表名字; Sql优化神器explain 数据备份 创建用户,指定权限 视图\触发器\存储过程\函数\事务 Id primary key: 数据库集群 数据库读写分离 主从复制 高可用