视图,触发器,事务,存储过程,函数,索引

一.视图(了解)

什么是视图?

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

为什么要用视图?

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

如何操作

固定语法:
create view 表名 as 虚拟表的查询sql语句

创建视图

临时表应用举例

#建表  2张有关系的表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

应用:

# 拼表操作
select * from emp inner join dep on emp.dep_id=dep.id;
'''创建视图'''
'''语法: create view 视图名称 as sql 语句'''
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id=dep.id;
# 更新记录
update emp2dep set name='EGON' where id=1;

select * from emp2dep;
# 查询结果:
+----+-----------+--------+------+--------+--------------+
| id | name      | sex    | age  | dep_id | dep_name     |
+----+-----------+--------+------+--------+--------------+
|  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 | 技术         |
+----+-----------+--------+------+--------+--------------+

select * from emp;
# 查询结果
+----+------------+--------+------+--------+
| 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 |
|  7 | lili       | female |   48 |   NULL |
+----+------------+--------+------+--------+

'''修改视图'''
# 语法:alter view 视图名称 as sql语句
alter view emp2dep as 查询语句;
'''删除视图'''
# 语法:drop view 视图名称
drop view emp2dep;

'''
注意:
1 创建视图在硬盘上只会有表结构,没有表数据(数据还是来自于之前的表)
2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表

视图的使用频率:
不高
当你创建了很多视图之后 会造成表的不好维护
'''

二.触发器(了解)

在满足对表数据进行增,删,改的情况下,自动触发的功能
使用触发器可以帮助我们实现监控,日志…
触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后

基本语法结构

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

案例

# 增 => insert
# 插入前
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
	sql 语句;
end
# 插入后
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
	sql 语句;
end

# 删除=> delete
# 删除前
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
	sql语句;
end
# 删除后
create trigger tri_after_delete_t1 after delete on t1 for each row
begin
	sql语句;
end

# 修改=>update

# 例子
insert into tt1 values(1,'egon','male');

delimiter //
create trigger tri_before_insert_tt1 before insert on tt1 for each row
begin
	insert into tt2 values(NEW.name);
end //
delimiter;

insert into tt1 values(2,'tom','female');

注意:NEW表示即将插入的数据行,OLD表示即将删除的数据行

触发器无法由用户直接调用,而知由于对表的[增/删/改]操作被动引发的

小练习

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

insert into cmd(user,priv,cmd,sub_time,success) values
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');

drop trigger tri_after_insert_cmd;
	

三.事务(*****)

1.什么是事务?

数据库事务是指作为单个逻辑工作单元执行的一系列操作(SQL语句),这些操作要么全部执行,要么全部不执行

2.为什么需要事务?

经典的银行转账行为,A账户给B账户10元,数据库操作需要两步,第一步A账户减10元,第二步B账户加10元,如果没有事务并且在两步中间发生异常,就会导致A的账户少了10元,但B的账户没有变化,如果不能保证这两步操作统一,银行的转账业务也没法进行展开了.

事务管理是每个数据库(Oracle,mysql,db等)都必须实现的

3.事务特性(4种):

原子性:强调事务的不可分割.
一致性:事务的执行的前后数据的完整性保持一致
隔离性:一个事务执行的过程中,不应该受到其他事务的干扰
持久性:事务一旦结束,数据就持久到数据库

4.事务运行模式(3种)

自动提交事务:默认事务管理模式,如果一个语句成功地完成,则提交该语句,如果遇到错误,则回滚该语句
显式事务:以begin transaction 显式开始.以commit或rollback显式结束
隐性事务:当连接以此模式进行操作时,sql将在提交或回滚当前事务后自动启动新事物.无需描述事务的开始,只需提交或回滚每个事务,它生成连续的事务链.

5.如何使用事务

事务相关的关键字
1 开启事务
start transaction;
2 回滚(回到事务执行之前的状态)
rollback;
3 确认(确认之后就无法回滚了)
commit;

create table user1(
id int primary key auto_increment,
name varchar(10),
balance int
);

insert into user1(name,balance) values
('liuhongwei',1000),
('宝哥',1000),
('力哥',1000);

# 原子操作
start transaction;
update user1 set balance=800 where id=1; 
update user1 set balance=1100 where id=2;
update user1 set balance=1100 where id=3;

# 回滚
rollback;

# 真正提交到数据库内
commit;

总结:

事务用于将某些操作的多个sql作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库完整性.

四.存储过程(了解)

存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点:

用于替代程序写的sql语句,实现程序与sql解耦
基于网络传输,传别名的数据量小,而直接传sql数据量大

使用存储过程的缺点:

程序员扩展功能不方便

程序与数据库结合使用的三种方式

#方式一:
MySQL : 存储过程
程序: 调用存储过程

#方式二:
MySQL :
程序: 纯SQL语句

#方式三:
MySQL :
程序:类和对象,即ORM(本质还是纯SQL语句)

第一种基本不用,一般都是第三种,出现效率问题再动手写sql

基本使用

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

创建无参存储过程

delimiter $$
create procedure p1()
begin
	select * from emp;
end $$

delimiter ;
call p1();

创建有参存储过程

对于存储过程,可以接收参数,其参数有三类:

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当做返回值

delimiter $$
create procedure p2(
in n int,
out res int
)
begin 
	select * from emp where id > n;
	set res=1;
end $$

delimiter ;

在mysql里如何调用存储过程

set @x=111;

call p2(3,@x);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
|  7 | lili       | female |   48 |   NULL |
+----+------------+--------+------+--------+

select @x;
+------+
| @x   |
+------+
|    1 |
+------+

在pymysql里如何调用存储过程

import pymysql
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123',db='db4',charset='utf8mb4')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc('p2',(3,0))  # @_p2_0=3,@_p2_1=0
'''
set @_p2_0=3
set @_p2_1=0

call p2(@_p2_0,@_p2_1);

'''
print(cursor.fetchall())
cursor.execute('select @_p2_1;')
print(cursor.fetchall())

cursor.execute("select @_p2_0;")
print(cursor.fetchall())

cursor.close()
conn.close()

五.函数

函数跟存储过程是有区别的,存储过程是自定义函数,函数就类似于是内置函数

mysql中提供了许多内置函数,例如:

一、数学函数
    ROUND(x,y)
        返回参数x的四舍五入的有y位小数的值
        
    RAND()
        返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

二、聚合函数(常用于GROUP BY从句的SELECT查询中)
    AVG(col)返回指定列的平均值
    COUNT(col)返回指定列中非NULL值的个数
    MIN(col)返回指定列的最小值
    MAX(col)返回指定列的最大值
    SUM(col)返回指定列的所有值之和
    GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果    
    
三、字符串函数

    CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

    CONV(N,from_base,to_base)
        进制转换
        例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写
   
    REVERSE(str)
        返回字符串 str ，顺序和字符顺序相反。
        
    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'
            
四、日期和时间函数
    CURDATE()或CURRENT_DATE() 返回当前的日期
    CURTIME()或CURRENT_TIME() 返回当前的时间
    DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
    DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
    DAYOFYEAR(date)   返回date是一年的第几天(1~366)
    DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
    FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
    HOUR(time)   返回time的小时值(0~23)
    MINUTE(time)   返回time的分钟值(0~59)
    MONTH(date)   返回date的月份值(1~12)
    MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
    NOW()    返回当前的日期和时间
    QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
    WEEK(date)   返回日期date为一年中第几周(0~53)
    YEAR(date)   返回日期date的年份(1000~9999)
    
    重点:
    DATE_FORMAT(date,format) 根据format字符串格式化date值

       mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
       mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
       mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
        ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
       mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
        ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
       mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
       mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'
        
五、加密函数
    MD5()    
        计算字符串str的MD5校验和
    PASSWORD(str)   
        返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
        
六、控制流函数            
    CASE WHEN[test1] THEN [result1]...ELSE [default] END
        如果testN是真，则返回resultN，否则返回default
    CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
        如果test和valN相等，则返回resultN，否则返回default

    IF(test,t,f)   
        如果test是真，返回t；否则返回f

    IFNULL(arg1,arg2) 
        如果arg1不是空，返回arg1，否则返回arg2

    NULLIF(arg1,arg2) 
        如果arg1=arg2返回NULL；否则返回arg1        
        
七、控制流函数小练习
#7.1、准备表
/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50720
Source Host           : localhost:3306
Source Database       : student

Target Server Type    : MYSQL
Target Server Version : 50720
File Encoding         : 65001

Date: 2018-01-02 12:05:30
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(255) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(10) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `num` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');

#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select  score.c_id,
          course.c_name, 
      sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
      sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
      sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
      sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;

需要掌握的函数date_format

# 1.基本使用
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'


#2 准备表和记录
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

# 提取sub_time字段的值,按照格式后的结果即'年月'来分组
select date_format(sub_time,"%Y-%m") as t,count(id) from blog group by t;

# 结果
+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03                       |        2 |
| 2016-07                       |        4 |
| 2017-03                       |        3 |
+-------------------------------+----------+

六.索引原理

1.为何要用索引?

创建索引的目的就是为了优化查询速度
但是一张表一旦创建了索引,会降低写速度

2.什么是索引

索引是mysql数据库的一种数据结构,在mysql里称之为key

primary key
unique key
index key

注意foreign key不是用来加速查询用的,不在我们的研究范围内

上面的三种key,前面两种除了可以增加查询速度之外各自还具有约束条件,而最后一种index key没有任何的约束条件,知识用来帮助你快速查询数据

本质

通过不断的缩小想要的数据范围筛选出最终的结果,同时将随机事件(一页一页的翻)变成顺序事件(先找目录,找数据)
也就是说有了索引机制,我们可以总是用一种固定的方式查找数据

一张表中可以有多个索引(目录)
索引虽然能够帮助你加快查询速度但是也有缺点

1 当表中有大量数据存在的前提下 创建索引速度会很慢
2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
注意:索引不要随意的创建

b+树

只有叶子节点存放的是真实的数据,其他节点存放的是虚拟数据,仅仅是用来指路的,树的层级越高,查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
…
一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
占的空间少,一个磁盘块能够存储的数据多
那么就降低了树的高度,从而减少查询次数