16 视图_触发器_事务_存储过程_函数_流程控制

577次阅读

共计 14968 个字符，预计需要花费 38 分钟才能阅读完成。

视图就是通过查询得到一张虚拟表(并非真实存在), 然后保存下来, 下次可以直接使用
视图的本质也是一张表

如果要频繁的操作一张虚拟表, 就可以将其制作成视图后直接操作

语法 : create view [表名] as [查询表的语句]
创建两个相互关联的表

select * from emp;
select * from dep;

16 视图_触发器_事务_存储过程_函数_流程控制

创建视图

create view emp_dep_view as
    select * from
        emp inner join dep 
        on emp.dep_id=dep.did;

show tables;
select * from emp_dep_view;

16 视图_触发器_事务_存储过程_函数_流程控制

修改视图内容

update emp_dep_view set name="BigBob" where name="Bob";
select * from emp_dep_view;
select * from emp;

alter view emp_dep_view2 as select * from emp where id>4;
show tables;
select * from emp_dep_view2;

16 视图_触发器_事务_存储过程_函数_流程控制

我们发现修改了视图表, 原来的 emp 表的数据也发生了改变

删除视图

drop view emp_dep_view;
show tables;

16 视图_触发器_事务_存储过程_函数_流程控制

修改视图的内容会影响真正表的数据, 所以视图一般不修改, 只是用来查询的
视图的修改一般只争对创建视图时 as 后面定义的虚拟表的 sql 查询语句
创建视图在硬盘上只会有表结构(.frm), 没有表数据(.idb). 数据还是来自于之前的表(所以该视图, 原数据会改变)

致命的问题：视图是存放到数据库里的，如果我们程序中的 sql 过分依赖于数据库中存放的视图，那么意味着，一旦 sql 需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的 DBA 负责，你要想完成修改，必须付出大量的沟通成本 DBA 可能才会帮你完成修改，极其地不方便

对一张表进行 : 增、删、改 操作的时候, 自动触发预先编译好的 SQL 语句的执行 (没有查操作)

保证数据的完整性, 还可以起到类似于事务回滚的效果, 帮我们实现监控、日志等

增前、增后
删前、删后
改前、改后

delimiter : 改变输入的结束符，默认情况下输入结束符是分号 ";"，下面我把它改成了自定义 "%%"，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行 (只在当前窗口有效)
new : 表示即将插入的数据行
old : 表示即将删除的数据行

完整语法

delimiter %%  # 修改结束符
create  trigger [触发器名] [before/after] [insert/update/delete] on [表名]
for each row  # 表示每往上面创建的表进行指定操作之后就执行下面 "begin...end" 里面的 SQL 语句
begin
    [sql 语句]
end %%
delimiter ;   # 将结束符改回来

触发器的命名(见明知意)

🥝创建一个往 "t01" 表内插入记录之前触发 "sql" 语句的触发器
delimiter %%
create trigger tri_before_insert_t01 before insert on t01
for each row
begin
    [SQL 语句]
end %%
delimiter ;

🥝创建一个在 "t01" 表删除记录之后触发 "sql" 语句的触发器
delimiter %%
create trigger tri_after_delete_t01 after delete on t01
for each row
begin
    [sql 语句]
end %%
delimiter ;

🥝创建一个在对 "t01" 表修改数据之前就触发 "sql" 语句的触发器
delimiter %%
create trigger tri_before_update_t01 before update on t01
for each row
    [SQL 语句]
end %%
delimiter ;

模拟 cmd 命令的执行, 执行失败则添加到错误日志中

🥝准备两张表 "cmd"、"error_log"
create table cmd(
    id int primary key auto_increment,
    user varchar(16) not null,
    permi char(4) not null,
    cmd varchar(60) not null,
    sub_time datetime,
    success enum("yes","no") not null default "no"
);

create table error_log(
    id int primary key auto_increment,
    error_cmd varchar(60),
    error_time datetime
);

🥝创建触发器, 当 "cmd" 表中的 "cuccess" 字段是 "no", 那么将触发执行 "error_log" 表的插入操作
delimiter %%
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
    if new.success="no" then  # 如果即将插入的记录的 "success" 字段是 "no", 则执行下面的语句
        insert error_log(error_cmd,error_time) value(new.cmd,new.sub_time);  # 加分号
    end if;
end %%
delimiter ;

🥝开始模拟插入记录
insert cmd(user,permi,cmd,sub_time,success) value
    ("shawn","0644","ls -l /root",now(),"yes"),
    ("shawn","0644","ps -elf",now(),"yes"),
    ("shawn","0644","groupadd xing",now(),"no"),
    ("shawn","0644","cat /etc/gshadow",now(),"no");

🥝查看 "error_log"
select * from error_log;

16 视图_触发器_事务_存储过程_函数_流程控制

drop trigger tri_after_insert_cmd

删除之后, 再向 "cmd" 表中插入记录, 字段 "success" 为 "no" 数据所在的行就不会再添加到 "error_log" 中去了

16 视图_触发器_事务_存储过程_函数_流程控制

事务是由一条或多条 SQL 语句组成的逻辑执行单元, 可以比喻成一个容器, 里面放的就是一堆 SQL 语句, 这些语句要么全部执行成功, 要么一个都无法执行成功 ( 原子性)

对数据进行一系列的操作的时候, 为了防止这些操作中部分操作成功而另一些操作失败, 从而造成数据的不正确性, 这个时候我们就需要使用事务将其回滚到原来的状态

原子性(Atomicity) : 事务是一段程序的最小执行单元, 不可再分(就如同自然界的原子不可再分), 所以事务中的操作要么都成功, 要么都失败
一致性(Consistency) : 事务的执行, 必须使数据库从一个一致性状态, 变成另一个一致性状态, 一致性是通过原子性来保证的
隔离性(Lsolation) : 各个事务的执行互不干扰, 任意一个事务的内部操作对其他并发的事务, 都是隔离的; 也就是说 : 并发执行的事务之间不能看到对方的中间状态, 并发执行的事务之间不能相互影响
持续性(Durability) : 也叫 "持久性", 指事务一旦提交, 对数据的任何改变都记录到永久存储器中, 通常是物理数据库

关键字

🍎开启事务
begin;  # 或者下面的语句  
start transaction;

🍎事务回滚(回滚到之前的状态, 并关闭事务)
rollback;  # 回滚 + 关闭

🍎事务提交(将修改提交, 并关闭事务)
commit;    # 提交 + 关闭

由上面关键字可以看出, 一个事务的开始对应一个回滚或者提交, 之后就需要重新开启事务

银行余额示例

🍎先创建一个用户余额表并插入记录
create table user(
    id int primary key auto_increment,
    name varchar(16) not null,
    balance int not null
);

insert user(name,balance) value
    ("shawn",150000),
    ("song",20000),
    ("xing",520022),
    ("hai",10000);

select * from user;  # 查看一下所有记录

🍎开启事务
begin;  # 或者 start transaction;

🍎更新记录
update user set balance=100 where name="shawn";
update user set balance=100 where name="song";
update user set balance=100 where name="xing";
update user set balance=200 where name="hai";

select * from user;  # 查看一下是否修改成功

🍎事务回滚
rollback;  # 回滚之后, 该事务就关闭了
select * from user;  # 查看一下是否回滚到原来的数据

🍎再开启一个事务
begin;

🍎再次对数据进行更新
update user set balance=200 where name="shawn";
update user set balance=200 where name="song";
commit;  # 事务提交, 并关闭了该事务
select * from user;  # 查看数据的变化

rollback;  # 再次使用事务回滚将不在起作用, 因为事务已经关闭了 
select * from user;

16 视图_触发器_事务_存储过程_函数_流程控制

存储过程中包含了一系列的 SQL 语句, 就类似于 Python 中定义的函数, 通过调用存储过程名 (函数名) 来执行其内部的一堆 sql 语句(Python 代码)

优点 :
用于替代程序写的 SQL 语句, 实现程序与 SQL 语句的解耦
基于网络的传输, 只传 "存储过程名" 比传一堆的 SQL 语句的数据量小的多
缺点 :
程序的可扩展性非常低

第一种

" 应用程序 " : 程序员自己写代码开发
"mysql" : 提前写好的存储过程, 提供给程序来调用

🔰优点 : 提升了开发效率, 执行效率提升(只传输 '存储过程名')
🔰缺点 : 可扩展性查, 可能使用者的一些变动就需要修改整个存储过程

第二种

程序员在开发应用程序的过程中涉及到的数据库操作也是自己手动编写

🔰优点 : 扩展性很高
🔰缺点 : 开发效率低, 编写 SQL 语句太过繁琐, 且有些重复, 后期维护也不方便

第三种

程序员开发程序时只写程序代码, 而 sql 语句是基于别人写好的框架直接拿过来使用 (例如使用 ORM 框架)

🔰优点 : 开发效率比第一种和第二种都要高
🔰缺点 : 语句的可扩展性差, 并可能出现效率低下的问题

语法

delimiter %%  # 更换默认结束符
create procedure [存储过程名]()
begin
    [一堆 SQL 语句]
end %%
delimiter ;   # 将默认结束符改回来

简单示例

🍅先创建一个表, 在插入几条子记录
create table text(
    id int primary key auto_increment,
    name varchar(100) not null,
    sub_time datetime);
insert text(name,sub_time) value
    ("python 详解 ",now()),
    ("Java 入门 ",now()),
    (" 派大星的故事 ",now()),
    (" 小江放牛羊 ",now());

🍅创建无参存储过程, 对 "text" 表进行一些操作
delimiter %%
create procedure p01()
begin
    select * from text;
    insert text(name,sub_time) value(" 杀牛羊手法 2 ",now());
end %% 
delimiter ;

🔰在 "mysql" 中调用
call p01();

🔰在 "Python" 中的 "pymysql" 中调用
# 首先导入模块并连接数据库
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
# 得到游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p01")    # 调用存储过程
print(cursor.fetchall())  # 拿到结果并输出

cursor.close()  # 关闭游标
conn.close()    # 关闭连接

16 视图_触发器_事务_存储过程_函数_流程控制

mysql 中调用

16 视图_触发器_事务_存储过程_函数_流程控制

三种参数类型

"in" : 仅用于传入参数 (声明后面的变量为外部参数)
"out" : 仅用于返回值 (声明后面的变量是一个返回值, 需要使用 "set" 声明)
"inout" : 既可以传入时使用有可以返回值时使用 (声明后面的变量可以但参数也可以当返回值, 需要 "set" 声明)

语法

delimiter %%%  # 修改默认结束符
create procedure [存储过程名](in|out|inout [参数名] [类型],
    .....(可以多个参数),
    ......
)
begin
    [sql 语句]
end %%%
delimiter ;  # 将默认结束符改回来

in : 传入参数示例

🍅创建有参存储过程
delimiter %%%
create procedure p02(in num01 int)
begin
    select * from text where id=num01;
end %%%
delimiter ;

🍅在 "mysql" 中直接调用
call p02(4);

🍅"Python" 中使用 "pymysql" 模块调用
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc("p02",(4,))  # 参数传入的是一个元组
print(cursor.fetchall())

cursor.close()
conn.close()

16 视图_触发器_事务_存储过程_函数_流程控制

out : 返回值示例 (in+out)

🍅创建有参存储过程
delimiter %%%
create procedure p03(
    in num01 int,
    out res01 int
)
begin
    select * from text where id=num01;
    set res01=num01+1;
end %%%
delimiter ;

🍅在 "mysql" 中调用
call p03(4,@res01);  # @res01 表示定义一个全局变量来接收返回值
select @res01;       # 查看这个返回值

🍅在 "python" 中使用 "pymysql" 调用
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = ""
cursor.callproc("p03", (4, res))  # 第二个参数是用来接收返回值的
print(cursor.fetchall())

cursor.execute("select @_p03_0,@_p03_1;")  # "@_p03_0" 代表的是传入的第一个参数,"@_p03_1" 代表的是第二的参数, 也就是返回值
print(cursor.fetchall())

cursor.close()
conn.close()

16 视图_触发器_事务_存储过程_函数_流程控制

inout : 能传能返回示例

🍅创建存储过程
delimiter %%%
create procedure p04(inout num01 int)
begin
    select * from text where id>num01;
    set num01=num01+num01;
end %%%
delimiter ;

🍅在 "mysql" 中调用
set @res=2;     # 先定义一个全局变量
call p04(@res); # 然后传进去, 一方面当参数, 一方面接收返回值
select @res     # 查看这个返回值

🍅在 "Python" 使用 "pymysql" 调用
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test03",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = 2  # 定义一个变量
cursor.callproc("p04", (res,))     # 传入这个变量, 可当参数, 也可接收返回值 
print(cursor.fetchall())

cursor.execute("select @_p04_0;")  # 查看返回值
print(cursor.fetchall())

cursor.close()
conn.close()

16 视图_触发器_事务_存储过程_函数_流程控制

语法

drop procedure [存储过程名];

示例

drop procedure p01;

上面介绍的存储过程类似于 python 中的自定义的函数
而这里的函数指的是 mysql 提供的内置函数

👉🏻mysql 内置函数官方文档👈🏻

👉🏻mysql 内置函数中文文档👈🏻

数学函数

ROUND(x,y)  # 返回参数 x 的四舍五入的有 y 位小数的值
RAND()      # 返回 0 到1内的随机值, 可以通过提供一个参数 (种子) 使 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)

加密函数

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

ps : 流程控制练习

🍎准备表 : 将下面数据复制到 "sql" 后缀的文件中
/*
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');

16 视图_触发器_事务_存储过程_函数_流程控制

🍎创建一个库, 并导入该文件
create database school_test;  # 创建一个库
use school_test
source J:\MySql\mysql-5.6.48-winx64\data\sql_file\school_test.sql  # 导入该文件(需要文件路徑)
show tables;  # 查看是否导入成功
select * from student;
select * from teacher;

16 视图_触发器_事务_存储过程_函数_流程控制

🍎统计各科各分数段人数, 显示格式: 课程 ID, 课程名称,[100-85],[85-70],[70-60],[<60]
# 以各学科分组, 显示课程 ID 和课程名字, 于是需要将 course 与 score 链表, 使用流程控制 +sum()函数统计各分数段的人数
select course.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 84) then 1 else 0 end) as "[85-70]",
    sum(case when (num between 60 and 69) 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;

16 视图_触发器_事务_存储过程_函数_流程控制

语法

select date_format([date],[format])  # 将传入的时间 (date) 按照传入的格式 (format) 进行转换

基本使用示例

select date_format("2021-02-22","%W %M %Y") as time;  # 星期 月 年
select date_format("2020-01-25 11:14:23","%H:%i:%s") as time;  # 时 分 秒
select date_format("2020-01-25 11:14:23","%D %y %d %m %b %j") as time;  # 日 年 日 月 月 日
select date_format("1111-11-11 11:11:11","%H %k %I %r %T %S %w")as time;

16 视图_触发器_事务_存储过程_函数_流程控制

示例

🍅将 "text" 表中的 "sub_time" 提取出来转换成 " 时分秒 " 格式进行分组, 并统计个数
select * from text;
select date_format(sub_time,"%H:%i:%s") as time,count(id)
    from text
    group by time;

16 视图_触发器_事务_存储过程_函数_流程控制

自定义函数不同于存储过程, 函数中不能写 sql 语句(否则报错), 函数仅仅是 sql 中被应用的功能; 如果需要在 begin...end 中放置 sql 语句, 使用存储过程就行了

语法

delimiter %%%
create function [函数名]([参数 1] [类型],
    [参数 2] [类型])
returns [类型]
begin
    [一系列代码]
end %%%
delimiter ;

示例 1

🍅定义一个函数(求两个数和)
delimiter %%%           # 修改默认结束符
create function f01(
    num01 int,
    num02 int)          # 定义参数以及类型
returns int             # 设置返回值的类型为 int
begin
    declare num03 int;  # 声明变量 num03 为 int 类型
    set num03=num01+num02;
    return(num03);
end %%%
delimiter ;             # 将默认结束符修改回来

🍅调用自定义函数
select f01(2,4);  # 查询 + 调用
select f01(2,4) into @res;  # 调用并将返回值给变量 @res
select @res;  # 查看变量值

16 视图_触发器_事务_存储过程_函数_流程控制

示例二

🍅定义一个函数
delimiter %%%
create function f012(num01 int)
returns int
begin
    declare res int default 0;  # 声明变量 res 为 int 类型, 如果传入的不是 int, 默认返回 0
    if num01 = 10 then
        set res = num01*num01;
    elseif num01 = 20 then
        set res = num01*num01;
    elseif num01 = 30 then
        set res = num01*num01;
    else
        set res = num01;
    end if;
    return(res);
end %%%
delimiter ;

🍅调用
select f012(10);
select f012(20);
select f012(30);
select f012(40);
select f012("ad");

16 视图_触发器_事务_存储过程_函数_流程控制

删除函数

drop function [函数名];
drop function f012;

delimiter %%%
create procedure p01(in num int)
begin
    if num = 1 then
        select 1;
    elseif num = 2 then
        select 2;
    else select 3;
    end if;
end %%%
delimiter ;

16 视图_触发器_事务_存储过程_函数_流程控制

while 循环

delimiter %%%
create procedure p02()
begin
    declare num int;
    set num = 0;
    while num < 3 do
        select num;
        set num = num + 1;
    end while;
end %%%
delimiter ;

16 视图_触发器_事务_存储过程_函数_流程控制

repeat 循环

delimiter %%%
create procedure p03()
begin 
    declare num int;
    set num = 0;
    repeat
        select num;
        set num = num + 1;
        until num > 3  # 结束条件: 当 num>3 时结束
    end repeat;
end %%%
delimiter ;

16 视图_触发器_事务_存储过程_函数_流程控制

loop 循环

delimiter %%%
create procedure p04()
begin
    declare num int default 0;
    myloop:loop
        set num=num+1;
        if num>=3 then
            leave myloop;  # 当 num>=3 结束循环
        end if;
        select num;
    end loop myloop;
end %%%
delimiter ;

16 视图_触发器_事务_存储过程_函数_流程控制