一, 视图
1, 什么是视图
虚拟表: 在硬盘中没有的, 通过查询在内存中拼接的表
视图: 通过查询得到一张虚拟表, 保存下来, 下次可直接使用
2, 为什么要用视图
如果要频繁使用一张虚拟表, 可以不用重复查询
3, 如何用视图
create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;
4, 删除视图
drop view teacher2course;
5, 强调
1
, 在硬盘中, 视图只有表结构文件
(.frm)
, 没有表数据文件 (.idb); 其在后台对应的是一条 sql 语句
2, 视图通常是用于查询, 尽量不要修改视图中的数据
二, 触发器
1, 触发器: 在满足对某张表数据的增, 删, 改的情况下, 自动触发的功能称之为触发器
2, 为何要用触发器?
触发器专门针对我们对某一张表数据增 insert, 删 delete, 改 update 的行为, 这类行为一旦执行就会触发触发器的执行, 即自动运行另外一段 sql 代码
3, 创建触发器语法
# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row #after
begin #begin end
在 sql 中用于标志同一执行级别的代码; 相当于 python 中的缩进
sql 代码...
end
create trigger tri_before_insert_t2 before insert on 表名 for each row #before
begin
sql 代码...
- end
- # 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql 代码...
end
create trigger tri_before_delete_t2 before delete on 表名 for each row
begin
sql 代码...
- end
- # 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql 代码...
end
create trigger tri_before_update_t2 before update on 表名 for each row
begin
sql 代码...
- end
- # 删除触发器
- drop trigger tri_after_insert_cmd;
4, 案例
- 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 $$ #修改 sql 中的默认分隔符, 从; 改成 $$
- create trigger tri_after_insert_cmd after insert on cmd for each row #触发器名称要尽可能详细如例
- begin
- if NEW.success = 'no' then # NEW 为 mysql 对新插入记录的封装名; 如果不用 mysql 的触发器, 可以在自己的应用程序中加 if 判断
- insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
- end if; # mysql 中固定用法
- end $$ # $$ 表一行语句终结
- delimiter ; # 将 sql 中的默认分隔符改回;
三, 事务 (重要)
1, 什么是事务
开启一个事务可以包含一些 sql 语句, 这些 sql 语句要么同时成功; 要么一个都别想成功, 称之为事务的原子性
2, 事务的作用: 转账等, 屏蔽因网络传输部分失效而带来的影响
3, 如何用 (以下为运行逻辑, 非代码)
- create table user(
- id int primary key auto_increment,
- name char(32),
- balance int
- );
- insert into user(name,balance)
- values
- ('wsb',1000),
- ('egon',1000),
- ('ysb',1000);
- try:
- update user set balance=900 where name='wsb'; #买支付 100 元
- update user set balance=1010 where name='egon'; #中介拿走 10 元
- update user set balance=1090 where name='ysb'; #卖家拿到 90 元
except 异常:
- rollback; #如果异常, 回滚到前一个状态, 即 balance 都为 1000
- else:
- commit; #如果无异常, 修改数据库 (硬盘上的数据)
四, 存储过程 (重要)
#
在 mysql 中函数是不能单独使用的, 必须放在 sql 语句中使用; 但存储过程是可以单独使用的
1, 存储过程: 存储过程包含了一系列可执行的 sql 语句, 存储过程存放于 MySQL 中, 通过调用它的名字可以执行其内部的一堆 sql
2, 三种开发模型
a,(如果不考虑非技术因素, 各方面效率最高的选择; 但考虑到实际情况, 一般不被选择)
应用程序: 只需要开发应用程序的逻辑
mysql: 编写好存储过程, 以供应用程序调用
优点: 开发效率, 执行效率都高
缺点: 考虑到人为因素, 跨部门沟通等问题, 会导致扩展性差
b,(与 c 的思路一致, 会被使用)
应用程序: 除了开发应用程序的逻辑, 还需要编写原生 sql
mysql:
优点: 比方式
1, 扩展性高 (非技术性的)
缺点:
1, 开发效率, 执行效率都不如方式 1
2
, 编写原生 sql 太过于复杂, 而且需要考虑到 sql 语句的优化问题
c,(考虑到非技术因素, 最常见的选择)
应用程序: 开发应用程序的逻辑, 不需要编写原生 sql, 基于别人编写好的框架来处理数据, ORM(object relationship matching)
mysql:
优点: 不用再编写纯生
sql
, 这意味着开发效率比方式 2 高, 同时兼容方式 2 扩展性高的好处
缺点: 执行效率连方式 2 都比不过
3, 创建存储过程
- delimiter $$
- create procedure p1( #创建存储过程 p1
- in m int, #mysql
中参数必须先规定类型和用途 (
- in
- ,out,inout)
- in n int,
- out res int #res 用于接收返回值
- )
- begin
- select tname from teacher where tid> m and tid <n;
- set res=0;
- end $$
- delimiter ;
4, 如何用存储过程
a, 直接在 mysql 中调用
- set @res=10 #mysql 中变量的定义要用 @abc 的形式
- call p1(2,4,@res);
- select @res; #查看结果
b, 在 python 程序中调用
- import pymysql
- conn=pymysql.connect(
- host='127.0.0.1',
- port=3306,
- user='root',
- password='123',
- charset='utf8',
- database='db42'
- )
- cursor=conn.cursor(pymysql.cursors.DictCursor)
- cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10 #pymysql 帮助对传入变量进行以上变形
- print(cursor.fetchall())
- cursor.execute('select @_p1_2;') #查看返回值, 确认执行结果
- print(cursor.fetchone())
- cursor.close()
- conn.close()
- 5
, 事务的使用 (事务 + 存储过程)
- delimiter //
- create PROCEDURE p5(
- OUT p_return_code tinyint
- )
- BEGIN
- DECLARE exit handler for sqlexception #如果出现错误, 执行
- BEGIN
- -- ERROR
- set p_return_code = 1;
- rollback;
- END;
- DECLARE exit handler for sqlwarning #如果出现警告, 执行
- BEGIN
- -- WARNING
- set p_return_code = 2;
- rollback;
- END;
- START TRANSACTION; #事务的应用
- update user set balance=900 where id =1;
- update user123 set balance=1010 where id = 2;
- update user set balance=1090 where id =3;
- COMMIT;
- -- SUCCESS
- set p_return_code = 0; #0 代表执行成功
- END //
- delimiter ;
- # 在 python 中调用存储过程
- import pymysql
- conn=pymysql.connect(
- host='127.0.0.1',
- port=3306,
- user='root',
- password='123',
- charset='utf8',
- database='db44'
- )
- cursor=conn.cursor(pymysql.cursors.DictCursor)
- cursor.callproc('p6',(100,)) #@_p5_0 = 100
- cursor.execute('select @_p6_0')
- print(cursor.fetchone())
- cursor.close()
- conn.close()
五, 函数
1
, 强调: mysql 内置的函数只能在 sql 语句中使用
mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
2, 补充
a,select * from s1 \G #表字段太多 (字段行显示不全) 时, 用 \ G 将表竖着显示出来: row1 哪些字段: 对应的内容, row2...
b, 视图, 触发器, 事务, 存储过程, 函数, 流程控制皆是在库下面建立
六, 流程控制
- (
- if
- ,while,case)
- #case
- select
- case
- when name = 'egon' then
- name
- when name = 'alex' then
- concat(name,'_BIGSB')
- else
- concat(name,'_SB')
- end
- from emp;
七, 索引
1, 为什么要用索引
对于一个应用来说, 对数据库的读写比例基本上是 10:1, 即读多写少
而且对于写来说极少出现性能问题, 大多数性能问题都是慢查询
提到加速查, 就必须用到索引
2, 什么是索引
索引就相当于书的目录, 是
mysql
中一种专门的数据结构, 称为
- key
- (
- primary key
- ,unique,index key)
索引的本质原理就是通过不断地缩小查询范围, 来降低 io 次数从而提升查询性能
强调: 一旦为表创建了索引, 以后的查询都会先查索引, 再根据索引定位的结果去找数据 (同一种方式)
3, 索引的影响 (先有数据, 后有索引; 索引, 亦占硬盘空间)
a, 在表中有大量数据的前提下, 创建索引速度会很慢
b, 在索引创建完毕后, 对表的查询性能会大幅度提升, 但是写性能会降低
4,B + 树: 只有叶子节点才存放真实数据, 其他的 (根节点, 枝节点) 都是虚拟数据
a
, 一次 IO 走一个 block 块
b
, 浅蓝
-block
块;
深蓝
-
数据项; 黄 - 指针
c, 为降低 IO 次数 - 降低树的高度 - 数据项越小越好 (block 块大小一定的情况下, 数据项越小, 存放的数据量最多)- 采用占空间少的做索引 (比如 id)
5, 索引的最左匹配特性 (从左到右一次匹配)
6
, 聚集索引 (primary key): 组织数据时就按照此索引组织
特点: 叶子节点存放的一整条数据
7
, 辅助索引 (unique,index)
特点: 如果是按照这个字段创建的索引, 那么叶子节点存放的是:{名字: 名字所在那条记录的主键的值}
innodb 的索引存放在表数据里 (.idb)
a, 覆盖索引: 只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select name from user where name='egon';
b, 回表操作
select age from user where name='egon';
8, 补充: 权限管理
a, 授权
- grant all on *.* to 'egon' @'192.168.12.%' identified by '123'; #all 指的是除了 grant 以外的全部权限
- flush privileges;
- exist
b, 删除权限
revoke select on db1.* from 'egon'@'%';
9, 其他:
a, 对区别度低的数据不要建索引, 且索引不是越多越好
b, 注意范围问题, 或者说条件不明确的问题 (条件中出现这些符号或关键字:>,>=,<,<=,!= ,between...and...,like)
c
,mysql 中对 a and b and c.. 的形式, 会先从有索引或区分度高的数据入手开始查询, 以减少查询范围
d
,mysql 中对 a or b or c.. 的形式, 会从左至右依次查询
e, 索引列不要参与计算, 如果非要运算 a\12=3 ===> a=3\12
f, 联合索引的原则:
区别度高, 范围小的放左边, 区别度低, 范围大的放右边
(
email, name, gender, id
): 可以搜
- (email)
- ,
- (email,name, gender)
等 (含 email), 不能搜 (id) 等
来源: http://www.bubuko.com/infodetail-2601338.html