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:


数据库集群

数据库读写分离

主从复制

高可用
复习

 

posted @ 2019-02-24 15:15  等待の喵  阅读(555)  评论(0编辑  收藏  举报