MySQL——视图/触发器/事务/存储过程/函数/流程控制

#两张有关系的表
mysql> select * from course;
&＃43;-----&＃43;--------&＃43;------------&＃43;
| cid | cname | teacher_id |
&＃43;-----&＃43;--------&＃43;------------&＃43;
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
&＃43;-----&＃43;--------&＃43;------------&＃43;
rows in set (0.00 sec)

mysql> select * from teacher;
&＃43;-----&＃43;-----------------&＃43;
| tid | tname |
&＃43;-----&＃43;-----------------&＃43;
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 刘海燕老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
&＃43;-----&＃43;-----------------&＃43;
rows in set (0.00 sec)

#查询李平老师教授的课程名
mysql> select cname from course where teacher_id &＃61; (select tid from teacher where tname&＃61;&＃39;李平老师&＃39;);
&＃43;--------&＃43;
| cname |
&＃43;--------&＃43;
| 物理 |
| 美术 |
&＃43;--------&＃43;
rows in set (0.00 sec)

#子查询出临时表&＃xff0c;作为teacher_id等判断依据
select tid from teacher where tname&＃61;&＃39;李平老师&＃39;

临时表应用举例

#语法&＃xff1a;CREATE VIEW 视图名称 AS SQL语句
create view teacher_view as select tid from teacher where tname&＃61;&＃39;李平老师&＃39;;

#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id &＃61; (select tid from teacher_view);
&＃43;--------&＃43;
| cname |
&＃43;--------&＃43;
| 物理 |
| 美术 |
&＃43;--------&＃43;
rows in set (0.00 sec)

#&＃xff01;&＃xff01;&＃xff01;注意注意注意&＃xff1a;
#1. 使用视图以后就无需每次都重写子查询的sql&＃xff0c;但是这么效率并不高&＃xff0c;还不如我们写子查询的效率高

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

#修改视图&＃xff0c;原始表也跟着改
mysql> select * from course;
&＃43;-----&＃43;--------&＃43;------------&＃43;
| cid | cname | teacher_id |
&＃43;-----&＃43;--------&＃43;------------&＃43;
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
&＃43;-----&＃43;--------&＃43;------------&＃43;
rows in set (0.00 sec)

mysql> create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)

mysql> select * from course_view;
&＃43;-----&＃43;--------&＃43;------------&＃43;
| cid | cname | teacher_id |
&＃43;-----&＃43;--------&＃43;------------&＃43;
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
&＃43;-----&＃43;--------&＃43;------------&＃43;
rows in set (0.00 sec)

mysql> update course_view set cname&＃61;&＃39;xxx&＃39;; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> insert into course_view values(5,&＃39;yyy&＃39;,2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)

mysql> select * from course; #发现原始表的记录也跟着修改了
&＃43;-----&＃43;-------&＃43;------------&＃43;
| cid | cname | teacher_id |
&＃43;-----&＃43;-------&＃43;------------&＃43;
| 1 | xxx | 1 |
| 2 | xxx | 2 |
| 3 | xxx | 3 |
| 4 | xxx | 2 |
| 5 | yyy | 2 |
&＃43;-----&＃43;-------&＃43;------------&＃43;
rows in set (0.00 sec)

语法&＃xff1a;ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from teacher_view;
&＃43;-----&＃43;-------&＃43;------------&＃43;
| cid | cname | teacher_id |
&＃43;-----&＃43;-------&＃43;------------&＃43;
| 4 | xxx | 2 |
| 5 | yyy | 2 |
&＃43;-----&＃43;-------&＃43;------------&＃43;
rows in set (0.00 sec)

语法&＃xff1a;DROP VIEW 视图名称DROP VIEW teacher_view

# 插入前
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

#准备表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum (&＃39;yes&＃39;, &＃39;no&＃39;) #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 &＃61; &＃39;no&＃39; THEN #等值判断只有一个等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
END IF ; #必须加分号
END//
delimiter ;

#往表cmd中插入记录&＃xff0c;触发触发器&＃xff0c;根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
(&＃39;egon&＃39;,&＃39;0755&＃39;,&＃39;ls -l /etc&＃39;,NOW(),&＃39;yes&＃39;),
(&＃39;egon&＃39;,&＃39;0755&＃39;,&＃39;cat /etc/passwd&＃39;,NOW(),&＃39;no&＃39;),
(&＃39;egon&＃39;,&＃39;0755&＃39;,&＃39;useradd xxx&＃39;,NOW(),&＃39;no&＃39;),
(&＃39;egon&＃39;,&＃39;0755&＃39;,&＃39;ps aux&＃39;,NOW(),&＃39;yes&＃39;);

#查询错误日志&＃xff0c;发现有两条
mysql> select * from errlog;
&＃43;----&＃43;-----------------&＃43;---------------------&＃43;
| id | err_cmd | err_time |
&＃43;----&＃43;-----------------&＃43;---------------------&＃43;
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
&＃43;----&＃43;-----------------&＃43;---------------------&＃43;
rows in set (0.00 sec)

插入后触发触发器

drop trigger tri_after_insert_cmd;

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
(&＃39;wsb&＃39;,1000),
(&＃39;egon&＃39;,1000),
(&＃39;ysb&＃39;,1000);

#原子操作
start transaction;
update user set balance&＃61;900 where name&＃61;&＃39;wsb&＃39;; #买支付100元
update user set balance&＃61;1010 where name&＃61;&＃39;egon&＃39;; #中介拿走10元
update user set balance&＃61;1090 where name&＃61;&＃39;ysb&＃39;; #卖家拿到90元
commit;

#出现异常&＃xff0c;回滚到初始状态
start transaction;
update user set balance&＃61;900 where name&＃61;&＃39;wsb&＃39;; #买支付100元
update user set balance&＃61;1010 where name&＃61;&＃39;egon&＃39;; #中介拿走10元
uppdate user set balance&＃61;1090 where name&＃61;&＃39;ysb&＃39;; #卖家拿到90元,出现异常没有拿到
rollback;
commit;
mysql> select * from user;
&＃43;----&＃43;------&＃43;---------&＃43;
| id | name | balance |
&＃43;----&＃43;------&＃43;---------&＃43;
| 1 | wsb | 1000 |
| 2 | egon | 1000 |
| 3 | ysb | 1000 |
&＃43;----&＃43;------&＃43;---------&＃43;
rows in set (0.00 sec)

#1. 用于替代程序写的SQL语句&＃xff0c;实现程序与sql解耦#2. 基于网络传输&＃xff0c;传别名的数据量小&＃xff0c;而直接传sql数据量大

#1. 程序员扩展功能不方便

#方式一&＃xff1a;MySQL&＃xff1a;存储过程程序&＃xff1a;调用存储过程#方式二&＃xff1a;MySQL&＃xff1a;程序&＃xff1a;纯SQL语句#方式三&＃xff1a;MySQL:程序&＃xff1a;类和对象&＃xff0c;即ORM&＃xff08;本质还是纯SQL语句&＃xff09;

delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

#在mysql中调用
call p1()

#在python中基于pymysql调用
cursor.callproc(&＃39;p1&＃39;) 
print(cursor.fetchall())

对于存储过程&＃xff0c;可以接收参数&＃xff0c;其参数有三类&＃xff1a;#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值

delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN

select * from blog where id > n1;
END //
delimiter ;

#在mysql中调用
call p2(3,2)

#在python中基于pymysql调用
cursor.callproc(&＃39;p2&＃39;,(3,2))
print(cursor.fetchall())

in:传入参数

delimiter //
create procedure p3(
in n1 int,
out res int
)
BEGIN
select * from blog where id > n1;
set res &＃61; 1;
END //
delimiter ;

#在mysql中调用
set &＃64;res&＃61;0; #0代表假&＃xff08;执行失败&＃xff09;&＃xff0c;1代表真&＃xff08;执行成功&＃xff09;
call p3(3,&＃64;res);
select &＃64;res;

#在python中基于pymysql调用
cursor.callproc(&＃39;p3&＃39;,(3,0)) #0相当于set &＃64;res&＃61;0
print(cursor.fetchall()) #查询select的查询结果

cursor.execute(&＃39;select &＃64;_p3_0,&＃64;_p3_1;&＃39;) #&＃64;p3_0代表第一个参数&＃xff0c;&＃64;p3_1代表第二个参数&＃xff0c;即返回值
print(cursor.fetchall())

out&＃xff1a;返回值

delimiter //
create procedure p4(
inout n1 int
)
BEGIN
select * from blog where id > n1;
set n1 &＃61; 1;
END //
delimiter ;

#在mysql中调用
set &＃64;x&＃61;3;
call p4(&＃64;x);
select &＃64;x;

#在python中基于pymysql调用
cursor.callproc(&＃39;p4&＃39;,(3,))
print(cursor.fetchall()) #查询select的查询结果

cursor.execute(&＃39;select &＃64;_p4_0;&＃39;) 
print(cursor.fetchall())

inout:既可以传入又可以返回

#介绍
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status &＃61; 1;
rollback;
}

开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束

set status &＃61; 2;


END //
delimiter ;

#实现
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN 
DECLARE exit handler for sqlexception 
BEGIN 
-- ERROR 
set p_return_code &＃61; 1; 
rollback; 
END;

DECLARE exit handler for sqlwarning 
BEGIN 
-- WARNING 
set p_return_code &＃61; 2; 
rollback; 
END;

START TRANSACTION; 
DELETE from tb1; #执行失败
insert into blog(name,sub_time) values(&＃39;yyy&＃39;,now());
COMMIT;

-- SUCCESS 
set p_return_code &＃61; 0; #0代表执行成功

END //
delimiter ;

#在mysql中调用存储过程
set &＃64;res&＃61;123;
call p5(&＃64;res);
select &＃64;res;

#在python中基于pymysql调用存储过程
cursor.callproc(&＃39;p5&＃39;,(123,))
print(cursor.fetchall()) #查询select的查询结果

cursor.execute(&＃39;select &＃64;_p5_0;&＃39;)
print(cursor.fetchall())

事务

-- 无参数
call proc_name()

-- 有参数&＃xff0c;全in
call proc_name(1,2)

-- 有参数&＃xff0c;有in&＃xff0c;out&＃xff0c;inout
set &＃64;t1&＃61;0;
set &＃64;t2&＃61;3;
call proc_name(1,2,&＃64;t1,&＃64;t2)

执行存储过程

在MySQL中执行存储过程

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn &＃61; pymysql.connect(host&＃61;&＃39;127.0.0.1&＃39;, port&＃61;3306, user&＃61;&＃39;root&＃39;, passwd&＃61;&＃39;123&＃39;, db&＃61;&＃39;t1&＃39;)
cursor &＃61; conn.cursor(cursor&＃61;pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc(&＃39;p1&＃39;, args&＃61;(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select &＃64;_p1_0,&＃64;_p1_1,&＃64;_p1_2,&＃64;_p1_3")
result &＃61; cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

print(result)

在python中基于pymysql执行存储过程

drop procedure proc_name;

一、数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值

RAND()
返回&＃xff10;到&＃xff11;内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

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

三、字符串函数

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

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

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

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

LOWER(str)
变小写

UPPER(str)
变大写

REVERSE(str)
返回字符串 str &＃xff0c;顺序和字符顺序相反。

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

mysql> SELECT SUBSTRING(&＃39;Quadratically&＃39;,5);
-> &＃39;ratically&＃39;

mysql> SELECT SUBSTRING(&＃39;foobarbar&＃39; FROM 4);
-> &＃39;barbar&＃39;

mysql> SELECT SUBSTRING(&＃39;Quadratically&＃39;,5,6);
-> &＃39;ratica&＃39;

mysql> SELECT SUBSTRING(&＃39;Sakila&＃39;, -3);
-> &＃39;ila&＃39;

mysql> SELECT SUBSTRING(&＃39;Sakila&＃39;, -5, 3);
-> &＃39;aki&＃39;

mysql> SELECT SUBSTRING(&＃39;Sakila&＃39; FROM -4 FOR 2);
-> &＃39;ki&＃39;

四、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名&＃xff0c;如&＃xff1a;SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式&＃xff0c;格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名&＃xff0c;如&＃xff1a;SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)&＃xff0c;如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值

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

五、加密函数
MD5() 
计算字符串str的MD5校验和
PASSWORD(str) 
返回字符串str的加密版本&＃xff0c;这个加密过程是不可逆转的&＃xff0c;和UNIX密码加密过程使用不同的算法。

六、控制流函数 
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真&＃xff0c;则返回resultN&＃xff0c;否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 
如果test和valN相等&＃xff0c;则返回resultN&＃xff0c;否则返回default

IF(test,t,f) 
如果test是真&＃xff0c;返回t&＃xff1b;否则返回f

IFNULL(arg1,arg2) 
如果arg1不是空&＃xff0c;返回arg1&＃xff0c;否则返回arg2

NULLIF(arg1,arg2) 
如果arg1&＃61;arg2返回NULL&＃xff1b;否则返回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&＃61;0;

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

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO &＃96;course&＃96; VALUES (&＃39;1&＃39;, &＃39;python&＃39;, &＃39;1&＃39;);
INSERT INTO &＃96;course&＃96; VALUES (&＃39;2&＃39;, &＃39;java&＃39;, &＃39;2&＃39;);
INSERT INTO &＃96;course&＃96; VALUES (&＃39;3&＃39;, &＃39;linux&＃39;, &＃39;3&＃39;);
INSERT INTO &＃96;course&＃96; VALUES (&＃39;4&＃39;, &＃39;web&＃39;, &＃39;2&＃39;);

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

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

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

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

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

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;1&＃39;, &＃39;大王&＃39;);
INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;2&＃39;, &＃39;alex&＃39;);
INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;3&＃39;, &＃39;egon&＃39;);
INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;4&＃39;, &＃39;peiqi&＃39;);

#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 &＃39;[100-85]&＃39;,
sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as &＃39;[85-70]&＃39;,
sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as &＃39;[70-60]&＃39;,
sum(CASE WHEN num <60 THEN 1 ELSE 0 END) as &＃39;[ <60]&＃39;
from score,course where score.c_id&＃61;course.c_id GROUP BY score.c_id;

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

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

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

#3. 提取sub_time字段的值&＃xff0c;按照格式后的结果即"年月"来分组
SELECT DATE_FORMAT(sub_time,&＃39;%Y-%m&＃39;),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,&＃39;%Y-%m&＃39;);

#结果
&＃43;-------------------------------&＃43;----------&＃43;
| DATE_FORMAT(sub_time,&＃39;%Y-%m&＃39;) | COUNT(1) |
&＃43;-------------------------------&＃43;----------&＃43;
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |
&＃43;-------------------------------&＃43;----------&＃43;
rows in set (0.00 sec)

需要掌握函数&＃xff1a;date_format

#&＃xff01;&＃xff01;&＃xff01;注意&＃xff01;&＃xff01;&＃xff01;
#函数中不要写sql语句&＃xff08;否则会报错&＃xff09;&＃xff0c;函数仅仅只是一个功能&＃xff0c;是一个在sql中被应用的功能
#若要想在begin...end...中写sql&＃xff0c;请用存储过程

delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num &＃61; i1 &＃43; i2;
return(num);
END //
delimiter ;

delimiter //
create function f5(
i int
)
returns int
begin
declare res int default 0;
if i &＃61; 10 then
set res&＃61;100;
elseif i &＃61; 20 then
set res&＃61;200;
elseif i &＃61; 30 then
set res&＃61;300;
else
set res&＃61;400;
end if;
return res;
end //
delimiter ;

drop function func_name;

# 获取返回值
select UPPER(&＃39;egon&＃39;) into &＃64;res;
SELECT &＃64;res;

# 在查询中使用
select f1(11,nid) ,name from tb2;

delimiter //
CREATE PROCEDURE proc_if ()
BEGIN

declare i int default 0;
if i &＃61; 1 THEN
SELECT 1;
ELSEIF i &＃61; 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;

END //
delimiter ;

if条件语句

delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

DECLARE num INT ;
SET num &＃61; 0 ;
WHILE num <10 DO
SELECT
num ;
SET num &＃61; num &＃43; 1 ;
END WHILE ;

END //
delimiter ;

while循环

delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN

DECLARE i INT ;
SET i &＃61; 0 ;
repeat
select i;
set i &＃61; i &＃43; 1;
until i >&＃61; 5
end repeat;

END //
delimiter ;

repeat循环

BEGIN

declare i int default 0;
loop_label: loop

set i&＃61;i&＃43;1;
if i<8 then
iterate loop_label;
end if;
if i>&＃61;10 then
leave loop_label;
end if;
select i;
end loop loop_label;

END

loop