一、视图
1.什么是视图
一个查询语句的结果是一张虚拟表,将这种虚拟表保存下来 它就变成了一个视图
2.为什么要用视图
当频繁需要用到多张表的连表结果,你就可以事先生成好视图 之后直接调用即可,避免了反复写连表操作的sql语句
3.如何使用视图
create view teacher_course as select * from teacher INNER JOIN courseon teacher.tid = course.teacher_id;
注意：
1.视图只有表结构,视图中的数据是来源于原来的表
2.不要改动视图表中的数据
3.一般情况下不会频繁的使用视图来写业务逻辑
二、触发器
1.什么是触发器
触发器是达到某个条件自动触发，
当对数据进行增、删、改的情况下会自动触发触发器的运行
2.为何要使用触发器
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行 就会触发触发器的执行，即自动运行另外一段sql代码
3.固定语法结构
create trigger 触发器的名字 after/before insert/update/delete on 表名 for each rowbeginsql语句end如下： t1为表名触发器名：tri_befor_t1在触发器之前或者之后进行插入或更改或删除t1create trigger tri_before(after)_t1(insert/update/delete) after/before insert/update/delete on t1 for each rowbeginsql语句end
delimiter $$ # 只对当前窗口有效 create trigger tri_after_insert_user after insert on user for each row begininsert into user(name,password) values('jason','123'); end $$ delimiter ;
# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN... END# 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN... END# 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN... END# 删除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN... END# 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN... END# 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN... END
delimiter $$ create trigger tri_after_insert_cmd after insert on cmd for each row beginif NEW.success = 'no' then insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);end if;end $$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 );delimiter $$ # 将mysql默认的结束符由;换成$$ create trigger tri_after_insert_cmd after insert on cmd for each row beginif NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象 insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);end if; end $$ delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志 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');# 查询errlog表记录 select * from errlog; # 删除触发器 drop trigger tri_after_insert_cmd;
三、事务
1.什么是事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功 要么一个都别想成功，称之为事务的原子性
2.事务的作用
保证了对数据操作的数据安全性
事务具有的4个特性
原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。 一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 持久性（durability）：持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
3.如何使用
主要操作：
如何开启事务start transaction
事务回滚rollback
永久性更改commit 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);# 修改数据之前先开启事务操作 start transaction;# 修改操作 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元# 回滚到上一个状态 rollback;# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘 commit; """开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""# 站在python代码的角度，应该实现的伪代码逻辑， 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; else:commit;
四、存储过程
1.什么是存储过程
存储过程类似于python中的自定义函数，内部封装了操作数据库的sql语句，
后续先要实现相应的操作，只需要调用存储过程即可
#1. 用于替代程序写的SQL语句，实现程序与sql解耦#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
使用存储过程的缺点
#1. 程序员扩展功能不方便
2.三种开发模式
第一种
""" 应用程序：只需要开发应用程序的逻辑 mysql:编写好存储过程，以供应用程序调用 优点：开发效率，执行效率都高 缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差 """
""" 应用程序：除了开发应用程序的逻辑，还需要编写原生sql 优点：比方式1，扩展性高(非技术性的) 缺点： 1、开发效率，执行效率都不如方式1 2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题 """
""" 应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM 优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处 缺点：执行效率连方式2都比不过 """
语法结果：delimiter $$create procedure p1()beginselect * from user;end $$delimiter ;#使用callp1(); 调用即可带参数的：delimiter $$create procedure p1(in m int, # 不能被返回出去in n int,out res int, # 可以被返回inout xxx int, # 既可以进又可以出 )beginselect * from user;end $$delimiter ;
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！# 1、直接在mysql中调用 set @res=10 # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10 call p1(2,4,10); # 报错 call p1(2,4,@res); # 查看结果 select @res; # 执行成功，@res变量值发生了变化# 2、在python程序中调用 import pymysqlconn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',password = '123',database = 'day38',charset = 'utf8',autocommit = True ) cursor = conn.cursor(pymysql.cursors.DictCursor) # call p1() mysql中调用 # callproc() pymysql中调用 cursor.callproc('p1',(1,5,10)) # 内部自动用变量名存储了对应的值 print(cursor.fetchall()) """ @_p1_0=1 @_p1_1=5 @_p1_2=10@_存储过程名_索引值 """cursor.execute('select @_p1_0') print(cursor.fetchall()) cursor.execute('select @_p1_1') print(cursor.fetchall()) cursor.execute('select @_p1_2') print(cursor.fetchall())# 3、存储过程与事务使用举例(了解) delimiter // create PROCEDURE p5(OUT p_return_code tinyint ) BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset 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;-- SUCCESSset p_return_code = 0; #0代表执行成功 END // delimiter ;
一、数学函数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时间戳tsHOUR(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，否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回defaultIF(test,t,f) 如果test是真，返回t；否则返回fIFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
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');select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
#！！！注意！！！ #函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能 #若要想在begin...end...中写sql，请用存储过程
delimiter // create function f1(i1 int,i2 int) returns int BEGINdeclare num int;set num = i1 + i2;return(num); END // delimiter ;
delimiter // create function f5(i int ) returns int begindeclare res int default 0;if i = 10 thenset res=100;elseif i = 20 thenset res=200;elseif i = 30 thenset res=300;elseset res=400;end if;return res; end // delimiter ;
2.删除函数
drop function func_name;
# 获取返回值 select UPPER('egon') into @res; SELECT @res;# 在查询中使用 select f1(11,nid) ,name from tb2;
delimiter // CREATE PROCEDURE proc_if () BEGINdeclare i int default 0;if i = 1 THENSELECT 1;ELSEIF i = 2 THENSELECT 2;ELSESELECT 7;END IF;END // delimiter ;
2.循环语句
delimiter // CREATE PROCEDURE proc_while () BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END // delimiter ;
delimiter // CREATE PROCEDURE proc_repeat () BEGINDECLARE i INT ;SET i = 0 ;repeatselect i;set i = i + 1;until i >= 5end repeat;END // delimiter ;
BEGINdeclare i int default 0;loop_label: loopset i=i+1;if i<8 theniterate loop_label;end if;if i>=10 thenleave loop_label;end if;select i;end loop loop_label;END