当前位置：Gxlcms > 数据库问题 > mysql七：视图、触发器、事务、存储过程、函数

mysql七：视图、触发器、事务、存储过程、函数

时间：2021-07-01 10:21:17 帮助过：15人阅读

两张有关系的表 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | | 4 | 美术 | 2 | +-----+--------+------------+ 4 rows in set (0.00 sec) mysql> select * from teacher; +-----+-----------------+ | tid | tname | +-----+-----------------+ | 1 | 张磊老师 | | 2 | 李平老师 | | 3 | 刘海燕老师 | | 4 | 朱云海老师 | | 5 | 李杰老师 | +-----+-----------------+ 5 rows in set (0.00 sec) #查询李平老师教授的课程名 mysql> select cname from course where teacher_id = (select tid from teacher where tname=‘李平老师‘); +--------+ | cname | +--------+ | 物理 | | 美术 | +--------+ 2 rows in set (0.00 sec) #子查询出临时表，作为teacher_id等判断依据 select tid from teacher where tname=‘李平老师‘ 临时表应用举例

一 创建视图

技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">语法：CREATE VIEW 视图名称 AS  SQL语句</span>
  2. create view teacher_view as select tid <span style="color: #0000ff">from</span> teacher where tname=<span style="color: #800000">‘</span><span style="color: #800000">李平老师</span><span style="color: #800000">‘</span><span style="color: #000000">;
  3. </span><span style="color: #008000">#</span><span style="color: #008000">于是查询李平老师教授的课程名的sql可以改写为</span>
  4. mysql> select cname <span style="color: #0000ff">from</span> course where teacher_id = (select tid <span style="color: #0000ff">from</span><span style="color: #000000"> teacher_view);
  5. </span>+--------+
  6. | cname  |
  7. +--------+
  8. | 物理   |
  9. | 美术   |
  10. +--------+
  11. 2 rows <span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  12. </span><span style="color: #008000">#</span><span style="color: #008000">！！！注意注意注意：</span><span style="color: #008000">
  13. #</span><span style="color: #008000">1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高</span>
  14. <span style="color: #008000">#</span><span style="color: #008000">2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便</span>
View Code

二 使用视图

技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">修改视图，原始表也跟着改</span>
  2. mysql> select * <span style="color: #0000ff">from</span><span style="color: #000000"> course;
  3. </span>+-----+--------+------------+
  4. | cid | cname  | teacher_id |
  5. +-----+--------+------------+
  6. |   1 | 生物   |          1 |
  7. |   2 | 物理   |          2 |
  8. |   3 | 体育   |          3 |
  9. |   4 | 美术   |          2 |
  10. +-----+--------+------------+
  11. 4 rows <span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  12. mysql</span>> create view course_view as select * <span style="color: #0000ff">from</span> course; <span style="color: #008000">#</span><span style="color: #008000">创建表course的视图</span>
  13. Query OK, 0 rows affected (0.52<span style="color: #000000"> sec)
  14. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> course_view;
  15. </span>+-----+--------+------------+
  16. | cid | cname  | teacher_id |
  17. +-----+--------+------------+
  18. |   1 | 生物   |          1 |
  19. |   2 | 物理   |          2 |
  20. |   3 | 体育   |          3 |
  21. |   4 | 美术   |          2 |
  22. +-----+--------+------------+
  23. 4 rows <span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  24.  
  25. mysql</span>> update course_view set cname=<span style="color: #800000">‘</span><span style="color: #800000">xxx</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">更新视图中的数据</span>
  26. Query OK, 4 rows affected (0.04<span style="color: #000000"> sec)
  27. Rows matched: </span>4  Changed: 4<span style="color: #000000">  Warnings: 0
  28. mysql</span>> insert into course_view values(5,<span style="color: #800000">‘</span><span style="color: #800000">yyy</span><span style="color: #800000">‘</span>,2); <span style="color: #008000">#</span><span style="color: #008000">往视图中插入数据</span>
  29. Query OK, 1 row affected (0.03<span style="color: #000000"> sec)
  30. mysql</span>> select * <span style="color: #0000ff">from</span> course; <span style="color: #008000">#</span><span style="color: #008000">发现原始表的记录也跟着修改了</span>
  31. +-----+-------+------------+
  32. | cid | cname | teacher_id |
  33. +-----+-------+------------+
  34. |   1 | xxx   |          1 |
  35. |   2 | xxx   |          2 |
  36. |   3 | xxx   |          3 |
  37. |   4 | xxx   |          2 |
  38. |   5 | yyy   |          2 |
  39. +-----+-------+------------+
  40. 5 rows <span style="color: #0000ff">in</span> set (0.00 sec)
View Code

我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的，如下图

技术分享图片

三 修改视图

技术分享图片 
  1. <span style="color: #000000">语法：ALTER VIEW 视图名称 AS SQL语句
  2. mysql</span>> alter view teacher_view as select * <span style="color: #0000ff">from</span> course where cid>3<span style="color: #000000">;
  3. Query OK, 0 rows affected (</span>0.04<span style="color: #000000"> sec)
  4. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> teacher_view;
  5. </span>+-----+-------+------------+
  6. | cid | cname | teacher_id |
  7. +-----+-------+------------+
  8. |   4 | xxx   |          2 |
  9. |   5 | yyy   |          2 |
  10. +-----+-------+------------+
  11. 2 rows <span style="color: #0000ff">in</span> set (0.00 sec)
View Code

四 删除视图

技术分享图片 
  1. <span style="color: #000000">语法：DROP VIEW 视图名称
  2. DROP VIEW teacher_view</span>
View Code

二 触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

一 创建触发器

技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000"> 插入前</span>
  2. <span style="color: #000000">CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
  3. BEGIN
  4.     ...
  5. END
  6. </span><span style="color: #008000">#</span><span style="color: #008000"> 插入后</span>
  7. <span style="color: #000000">CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
  8. BEGIN
  9.     ...
  10. END
  11. </span><span style="color: #008000">#</span><span style="color: #008000"> 删除前</span>
  12. <span style="color: #000000">CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
  13. BEGIN
  14.     ...
  15. END
  16. </span><span style="color: #008000">#</span><span style="color: #008000"> 删除后</span>
  17. <span style="color: #000000">CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
  18. BEGIN
  19.     ...
  20. END
  21. </span><span style="color: #008000">#</span><span style="color: #008000"> 更新前</span>
  22. <span style="color: #000000">CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
  23. BEGIN
  24.     ...
  25. END
  26. </span><span style="color: #008000">#</span><span style="color: #008000"> 更新后</span>
  27. <span style="color: #000000">CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
  28. BEGIN
  29.     ...
  30. END</span>
View Code 技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">准备表</span>
  2. <span style="color: #000000">CREATE TABLE cmd (
  3.     id INT PRIMARY KEY auto_increment,
  4.     USER CHAR (</span>32<span style="color: #000000">),
  5.     priv CHAR (</span>10<span style="color: #000000">),
  6.     cmd CHAR (</span>64<span style="color: #000000">),
  7.     sub_time datetime, </span><span style="color: #008000">#</span><span style="color: #008000">提交时间</span>
  8.     success enum (<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span>) <span style="color: #008000">#</span><span style="color: #008000">0代表执行失败</span>
  9. <span style="color: #000000">);
  10. CREATE TABLE errlog (
  11.     id INT PRIMARY KEY auto_increment,
  12.     err_cmd CHAR (</span>64<span style="color: #000000">),
  13.     err_time datetime
  14. );
  15. </span><span style="color: #008000">#</span><span style="color: #008000">创建触发器</span>
  16. delimiter //<span style="color: #000000">
  17. CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
  18. BEGIN
  19.     IF NEW.success </span>= <span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span> THEN <span style="color: #008000">#</span><span style="color: #008000">等值判断只有一个等号</span>
  20.             INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; <span style="color: #008000">#</span><span style="color: #008000">必须加分号</span>
  21.       END IF ; <span style="color: #008000">#</span><span style="color: #008000">必须加分号</span>
  22. END//<span style="color: #000000">
  23. delimiter ;
  24. </span><span style="color: #008000">#</span><span style="color: #008000">往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志</span>
  25. <span style="color: #000000">INSERT INTO cmd (
  26.     USER,
  27.     priv,
  28.     cmd,
  29.     sub_time,
  30.     success
  31. )
  32. VALUES
  33.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">ls -l /etc</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span><span style="color: #000000">),
  34.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">cat /etc/passwd</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span><span style="color: #000000">),
  35.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">useradd xxx</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span><span style="color: #000000">),
  36.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">ps aux</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span><span style="color: #000000">);
  37. </span><span style="color: #008000">#</span><span style="color: #008000">查询错误日志，发现有两条</span>
  38. mysql> select * <span style="color: #0000ff">from</span><span style="color: #000000"> errlog;
  39. </span>+----+-----------------+---------------------+
  40. | id | err_cmd         | err_time            |
  41. +----+-----------------+---------------------+
  42. |  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
  43. |  2 | useradd xxx     | 2017-09-14 22:18:48 |
  44. +----+-----------------+---------------------+
  45. 2 rows <span style="color: #0000ff">in</span> set (0.00 sec)
插入后触发触发器

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

二 使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

三 删除触发器

技术分享图片 
  1. drop trigger tri_after_insert_cmd;
View Code

三 事务

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

技术分享图片 
  1. <span style="color: #000000">create table user(
  2. id int primary key auto_increment,
  3. name char(</span>32<span style="color: #000000">),
  4. balance int
  5. );
  6. insert into user(name,balance)
  7. values
  8. (</span><span style="color: #800000">‘</span><span style="color: #800000">wsb</span><span style="color: #800000">‘</span>,1000<span style="color: #000000">),
  9. (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,1000<span style="color: #000000">),
  10. (</span><span style="color: #800000">‘</span><span style="color: #800000">ysb</span><span style="color: #800000">‘</span>,1000<span style="color: #000000">);
  11. </span><span style="color: #008000">#</span><span style="color: #008000">原子操作</span>
  12. <span style="color: #000000">start transaction;
  13. update user set balance</span>=900 where name=<span style="color: #800000">‘</span><span style="color: #800000">wsb</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">买支付100元</span>
  14. update user set balance=1010 where name=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">中介拿走10元</span>
  15. update user set balance=1090 where name=<span style="color: #800000">‘</span><span style="color: #800000">ysb</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">卖家拿到90元</span>
  16. <span style="color: #000000">commit;
  17. </span><span style="color: #008000">#</span><span style="color: #008000">出现异常，回滚到初始状态</span>
  18. <span style="color: #000000">start transaction;
  19. update user set balance</span>=900 where name=<span style="color: #800000">‘</span><span style="color: #800000">wsb</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">买支付100元</span>
  20. update user set balance=1010 where name=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">中介拿走10元</span>
  21. uppdate user set balance=1090 where name=<span style="color: #800000">‘</span><span style="color: #800000">ysb</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">卖家拿到90元,出现异常没有拿到</span>
  22. <span style="color: #000000">rollback;
  23. commit;
  24. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> user;
  25. </span>+----+------+---------+
  26. | id | name | balance |
  27. +----+------+---------+
  28. |  1 | wsb  |    1000 |
  29. |  2 | egon |    1000 |
  30. |  3 | ysb  |    1000 |
  31. +----+------+---------+
  32. 3 rows <span style="color: #0000ff">in</span> set (0.00 sec)
View Code

四 存储过程

一 介绍

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

使用存储过程的优点：

  1. <span style="color: #008000">#</span><span style="color: #008000">1. 用于替代程序写的SQL语句，实现程序与sql解耦</span>
  2. <span style="color: #008000">#</span><span style="color: #008000">2. 基于网络传输，传别名的数据量小，而直接传sql数据量大</span>

使用存储过程的缺点：

  1. <span style="color: #008000">#1</span><span style="color: #008000">. 程序员扩展功能不方便</span>

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

技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">方式一：</span>
  2. <span style="color: #000000">    MySQL：存储过程
  3.     程序：调用存储过程
  4. </span><span style="color: #008000">#</span><span style="color: #008000">方式二：</span>
  5. <span style="color: #000000">    MySQL：
  6.     程序：纯SQL语句
  7. </span><span style="color: #008000">#</span><span style="color: #008000">方式三：</span>
  8. <span style="color: #000000">    MySQL:
  9.     程序：类和对象，即ORM（本质还是纯SQL语句）</span>
技术分享图片

二 创建简单存储过程（无参）

技术分享图片 
  1. delimiter //<span style="color: #000000">
  2. create procedure p1()
  3. BEGIN
  4.     select </span>* <span style="color: #0000ff">from</span><span style="color: #000000"> blog;
  5.     INSERT into blog(name,sub_time) values(</span><span style="color: #800000">"</span><span style="color: #800000">xxx</span><span style="color: #800000">"</span><span style="color: #000000">,now());
  6. END </span>//<span style="color: #000000">
  7. delimiter ;
  8. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
  9. <span style="color: #000000">call p1() 
  10. </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用</span>
  11. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p1</span><span style="color: #800000">‘</span><span style="color: #000000">) 
  12. </span><span style="color: #0000ff">print</span>(cursor.fetchall())
View Code

三 创建存储过程（有参）

  1. <span style="color: #000000">对于存储过程，可以接收参数，其参数有三类：
  2. </span><span style="color: #008000">#</span><span style="color: #008000">in          仅用于传入参数用</span><span style="color: #008000">
  3. #</span><span style="color: #008000">out        仅用于返回值用</span><span style="color: #008000">
  4. #</span><span style="color: #008000">inout     既可以传入又可以当作返回值</span>
技术分享图片 
  1. delimiter //<span style="color: #000000">
  2. create procedure p2(
  3.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> n1 int,
  4.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> n2 int
  5. )
  6. BEGIN
  7.     
  8.     select </span>* <span style="color: #0000ff">from</span> blog where id ><span style="color: #000000"> n1;
  9. END </span>//<span style="color: #000000">
  10. delimiter ;
  11. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
  12. call p2(3,2<span style="color: #000000">)
  13. </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用</span>
  14. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p2</span><span style="color: #800000">‘</span>,(3,2<span style="color: #000000">))
  15. </span><span style="color: #0000ff">print</span>(cursor.fetchall())
in:传入参数 技术分享图片 
  1. delimiter //<span style="color: #000000">
  2. create procedure p3(
  3.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> n1 int,
  4.     out res int
  5. )
  6. BEGIN
  7.     select </span>* <span style="color: #0000ff">from</span> blog where id ><span style="color: #000000"> n1;
  8.     set res </span>= 1<span style="color: #000000">;
  9. END </span>//<span style="color: #000000">
  10. delimiter ;
  11. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
  12. set @res=0; <span style="color: #008000">#</span><span style="color: #008000">0代表假（执行失败），1代表真（执行成功）</span>
  13. call p3(3<span style="color: #000000">,@res);
  14. select @res;
  15. </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用</span>
  16. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p3</span><span style="color: #800000">‘</span>,(3,0)) <span style="color: #008000">#</span><span style="color: #008000">0相当于set @res=0</span>
  17. <span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">查询select的查询结果</span>
  18. <span style="color: #000000">
  19. cursor.execute(</span><span style="color: #800000">‘</span><span style="color: #800000">select @_p3_0,@_p3_1;</span><span style="color: #800000">‘</span>) <span style="color: #008000">#</span><span style="color: #008000">@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值</span>
  20. <span style="color: #0000ff">print</span>(cursor.fetchall())
out：返回值 技术分享图片 
  1. delimiter //<span style="color: #000000">
  2. create procedure p4(
  3.     inout n1 int
  4. )
  5. BEGIN
  6.     select </span>* <span style="color: #0000ff">from</span> blog where id ><span style="color: #000000"> n1;
  7.     set n1 </span>= 1<span style="color: #000000">;
  8. END </span>//<span style="color: #000000">
  9. delimiter ;
  10. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
  11. set @x=3<span style="color: #000000">;
  12. call p4(@x);
  13. select @x;
  14. </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用</span>
  15. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p4</span><span style="color: #800000">‘</span>,(3<span style="color: #000000">,))
  16. </span><span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">查询select的查询结果</span>
  17. <span style="color: #000000">
  18. cursor.execute(</span><span style="color: #800000">‘</span><span style="color: #800000">select @_p4_0;</span><span style="color: #800000">‘</span><span style="color: #000000">) 
  19. </span><span style="color: #0000ff">print</span>(cursor.fetchall())
inout:既可以传入又可以返回 技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">介绍</span>
  2. delimiter //<span style="color: #000000">
  3.             create procedure p4(
  4.                 out status int
  5.             )
  6.             BEGIN
  7.                 </span>1<span style="color: #000000">. 声明如果出现异常则执行{
  8.                     set status </span>= 1<span style="color: #000000">;
  9.                     rollback;
  10.                 }
  11.                    
  12.                 开始事务
  13.                     </span>--<span style="color: #000000"> 由秦兵账户减去100
  14.                     </span>--<span style="color: #000000"> 方少伟账户加90
  15.                     </span>--<span style="color: #000000"> 张根账户加10
  16.                     commit;
  17.                 结束
  18.                 
  19.                 set status </span>= 2<span style="color: #000000">;
  20.                 
  21.                 
  22.             END </span>//<span style="color: #000000">
  23.             delimiter ;
  24. </span><span style="color: #008000">#</span><span style="color: #008000">实现</span>
  25. delimiter //<span style="color: #000000">
  26. create PROCEDURE p5(
  27.     OUT p_return_code tinyint
  28. )
  29. BEGIN 
  30.     DECLARE exit handler </span><span style="color: #0000ff">for</span><span style="color: #000000"> sqlexception 
  31.     BEGIN 
  32.         </span>--<span style="color: #000000"> ERROR 
  33.         set p_return_code </span>= 1<span style="color: #000000">; 
  34.         rollback; 
  35.     END; 
  36.     DECLARE exit handler </span><span style="color: #0000ff">for</span><span style="color: #000000"> sqlwarning 
  37.     BEGIN 
  38.         </span>--<span style="color: #000000"> WARNING 
  39.         set p_return_code </span>= 2<span style="color: #000000">; 
  40.         rollback; 
  41.     END; 
  42.     START TRANSACTION; 
  43.         DELETE </span><span style="color: #0000ff">from</span> tb1; <span style="color: #008000">#</span><span style="color: #008000">执行失败</span>
  44.         insert into blog(name,sub_time) values(<span style="color: #800000">‘</span><span style="color: #800000">yyy</span><span style="color: #800000">‘</span><span style="color: #000000">,now());
  45.     COMMIT; 
  46.     </span>--<span style="color: #000000"> SUCCESS 
  47.     set p_return_code </span>= 0; <span style="color: #008000">#</span><span style="color: #008000">0代表执行成功</span>
  48. <span style="color: #000000">
  49. END </span>//<span style="color: #000000">
  50. delimiter ;
  51. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用存储过程</span>
  52. set @res=123<span style="color: #000000">;
  53. call p5(@res);
  54. select @res;
  55. </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用存储过程</span>
  56. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p5</span><span style="color: #800000">‘</span>,(123<span style="color: #000000">,))
  57. </span><span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">查询select的查询结果</span>
  58. <span style="color: #000000">
  59. cursor.execute(</span><span style="color: #800000">‘</span><span style="color: #800000">select @_p5_0;</span><span style="color: #800000">‘</span><span style="color: #000000">)
  60. </span><span style="color: #0000ff">print</span>(cursor.fetchall())
事务

四 执行存储过程

技术分享图片 
  1. --<span style="color: #000000"> 无参数
  2. call proc_name()
  3. </span>--<span style="color: #000000"> 有参数，全in
  4. call proc_name(</span>1,2<span style="color: #000000">)
  5. </span>--<span style="color: #000000"> 有参数，有in，out，inout
  6. set @t1</span>=<span style="color: #000000">0;
  7. set @t2</span>=3<span style="color: #000000">;
  8. call proc_name(</span>1,2<span style="color: #000000">,@t1,@t2)
  9. 执行存储过程</span>
在MySQL中执行存储过程 技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span><span style="color: #008000">
  2. #</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff">import</span><span style="color: #000000"> pymysql
  4. conn </span>= pymysql.connect(host=<span style="color: #800000">‘</span><span style="color: #800000">127.0.0.1</span><span style="color: #800000">‘</span>, port=3306, user=<span style="color: #800000">‘</span><span style="color: #800000">root</span><span style="color: #800000">‘</span>, passwd=<span style="color: #800000">‘</span><span style="color: #800000">123</span><span style="color: #800000">‘</span>, db=<span style="color: #800000">‘</span><span style="color: #800000">t1</span><span style="color: #800000">‘</span><span style="color: #000000">)
  5. cursor </span>= conn.cursor(cursor=<span style="color: #000000">pymysql.cursors.DictCursor)
  6. </span><span style="color: #008000">#</span><span style="color: #008000"> 执行存储过程</span>
  7. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p1</span><span style="color: #800000">‘</span>, args=(1, 22, 3, 4<span style="color: #000000">))
  8. </span><span style="color: #008000">#</span><span style="color: #008000"> 获取执行完存储的参数</span>
  9. cursor.execute(<span style="color: #800000">"</span><span style="color: #800000">select @_p1_0,@_p1_1,@_p1_2,@_p1_3</span><span style="color: #800000">"</span><span style="color: #000000">)
  10. result </span>=<span style="color: #000000"> cursor.fetchall()
  11. conn.commit()
  12. cursor.close()
  13. conn.close()
  14. </span><span style="color: #0000ff">print</span>(result)
在python中基于pymysql执行存储过程

五 删除存储过程

技术分享图片 
  1. drop procedure proc_name;
View Code

五 函数

MySQL中提供了许多内置函数，例如：

技术分享图片 
  1. <span style="color: #000000">一、数学函数
  2.     ROUND(x,y)
  3.         返回参数x的四舍五入的有y位小数的值
  4.         
  5.     RAND()
  6.         返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
  7. 二、聚合函数(常用于GROUP BY从句的SELECT查询中)
  8.     AVG(col)返回指定列的平均值
  9.     COUNT(col)返回指定列中非NULL值的个数
  10.     MIN(col)返回指定列的最小值
  11.     MAX(col)返回指定列的最大值
  12.     SUM(col)返回指定列的所有值之和
  13.     GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果    
  14.     
  15. 三、字符串函数
  16.     CHAR_LENGTH(str)
  17.         返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
  18.     CONCAT(str1,str2,...)
  19.         字符串拼接
  20.         如有任何一个参数为NULL ，则返回值为 NULL。
  21.     CONCAT_WS(separator,str1,str2,...)
  22.         字符串拼接（自定义连接符）
  23.         CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
  24.     CONV(N,from_base,to_base)
  25.         进制转换
  26.         例如：
  27.             SELECT CONV(</span><span style="color: #800000">‘</span><span style="color: #800000">a</span><span style="color: #800000">‘</span>,16,2<span style="color: #000000">); 表示将 a 由16进制转换为2进制字符串表示
  28.     FORMAT(X,D)
  29.         将数字X 的格式写为</span><span style="color: #800000">‘</span><span style="color: #800000">#,###,###.##</span><span style="color: #800000">‘</span><span style="color: #000000">,以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
  30.         例如：
  31.             SELECT FORMAT(</span>12332.1,4); 结果为： <span style="color: #800000">‘</span><span style="color: #800000">12,332.1000</span><span style="color: #800000">‘</span><span style="color: #000000">
  32.     INSERT(str,pos,len,newstr)
  33.         在str的指定位置插入字符串
  34.             pos：要替换位置其实位置
  35.             len：替换的长度
  36.             newstr：新字符串
  37.         特别的：
  38.             如果pos超过原字符串长度，则返回原字符串
  39.             如果len超过原字符串长度，则由新字符串完全替换
  40.     INSTR(str,substr)
  41.         返回字符串 str 中子字符串的第一个出现位置。
  42.     LEFT(str,len)
  43.         返回字符串str 从开始的len位置的子序列字符。
  44.     LOWER(str)
  45.         变小写
  46.     UPPER(str)
  47.         变大写
  48.    
  49.     REVERSE(str)
  50.         返回字符串 str ，顺序和字符顺序相反。
  51.         
  52.     SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
  53.         不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
  54.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Quadratically</span><span style="color: #800000">‘</span>,5<span style="color: #000000">);
  55.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ratically</span><span style="color: #800000">‘</span><span style="color: #000000">
  56.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">foobarbar</span><span style="color: #800000">‘</span> FROM 4<span style="color: #000000">);
  57.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barbar</span><span style="color: #800000">‘</span><span style="color: #000000">
  58.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Quadratically</span><span style="color: #800000">‘</span>,5,6<span style="color: #000000">);
  59.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ratica</span><span style="color: #800000">‘</span><span style="color: #000000">
  60.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span>, -3<span style="color: #000000">);
  61.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ila</span><span style="color: #800000">‘</span><span style="color: #000000">
  62.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span>, -5, 3<span style="color: #000000">);
  63.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">aki</span><span style="color: #800000">‘</span><span style="color: #000000">
  64.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span> FROM -4 FOR 2<span style="color: #000000">);
  65.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ki</span><span style="color: #800000">‘</span><span style="color: #000000">
  66.             
  67. 四、日期和时间函数
  68.     CURDATE()或CURRENT_DATE() 返回当前的日期
  69.     CURTIME()或CURRENT_TIME() 返回当前的时间
  70.     DAYOFWEEK(date)   返回date所代表的一星期中的第几天(</span>1~7<span style="color: #000000">)
  71.     DAYOFMONTH(date)  返回date是一个月的第几天(</span>1~31<span style="color: #000000">)
  72.     DAYOFYEAR(date)   返回date是一年的第几天(</span>1~366<span style="color: #000000">)
  73.     DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
  74.     FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
  75.     HOUR(time)   返回time的小时值(0</span>~23<span style="color: #000000">)
  76.     MINUTE(time)   返回time的分钟值(0</span>~59<span style="color: #000000">)
  77.     MONTH(date)   返回date的月份值(</span>1~12<span style="color: #000000">)
  78.     MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
  79.     NOW()    返回当前的日期和时间
  80.     QUARTER(date)   返回date在一年中的季度(</span>1~4<span style="color: #000000">)，如SELECT QUARTER(CURRENT_DATE);
  81.     WEEK(date)   返回日期date为一年中第几周(0</span>~53<span style="color: #000000">)
  82.     YEAR(date)   返回日期date的年份(</span>1000~9999<span style="color: #000000">)
  83.     
  84.     重点:
  85.     DATE_FORMAT(date,format) 根据format字符串格式化date值
  86.        mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2009-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%W %M %Y</span><span style="color: #800000">‘</span><span style="color: #000000">);
  87.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">Sunday October 2009</span><span style="color: #800000">‘</span><span style="color: #000000">
  88.        mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2007-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%H:%i:%s</span><span style="color: #800000">‘</span><span style="color: #000000">);
  89.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">
  90.        mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1900-10-04 22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">,
  91.         </span>->                 <span style="color: #800000">‘</span><span style="color: #800000">%D %y %a %d %m %b %j</span><span style="color: #800000">‘</span><span style="color: #000000">);
  92.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">4th 00 Thu 04 10 Oct 277</span><span style="color: #800000">‘</span><span style="color: #000000">
  93.        mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1997-10-04 22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">,
  94.         </span>->                 <span style="color: #800000">‘</span><span style="color: #800000">%H %k %I %r %T %S %w</span><span style="color: #800000">‘</span><span style="color: #000000">);
  95.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">22 22 10 10:23:00 PM 22:23:00 00 6</span><span style="color: #800000">‘</span><span style="color: #000000">
  96.        mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1999-01-01</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%X %V</span><span style="color: #800000">‘</span><span style="color: #000000">);
  97.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">1998 52</span><span style="color: #800000">‘</span><span style="color: #000000">
  98.        mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2006-06-00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%d</span><span style="color: #800000">‘</span><span style="color: #000000">);
  99.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">00</span><span style="color: #800000">‘</span><span style="color: #000000">
  100.         
  101. 五、加密函数
  102.     MD5()    
  103.         计算字符串str的MD5校验和
  104.     PASSWORD(str)   
  105.         返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
  106.         
  107. 六、控制流函数            
  108.     CASE WHEN[test1] THEN [result1]...ELSE [default] END
  109.         如果testN是真，则返回resultN，否则返回default
  110.     CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
  111.         如果test和valN相等，则返回resultN，否则返回default
  112.     IF(test,t,f)   
  113.         如果test是真，返回t；否则返回f
  114.     IFNULL(arg1,arg2) 
  115.         如果arg1不是空，返回arg1，否则返回arg2
  116.     NULLIF(arg1,arg2) 
  117.         如果arg1</span>=<span style="color: #000000">arg2返回NULL；否则返回arg1        
  118.         
  119. 七、控制流函数小练习
  120. </span><span style="color: #008000">#</span><span style="color: #008000">7.1、准备表</span>
  121. /*<span style="color: #000000">
  122. Navicat MySQL Data Transfer
  123. Source Server         : localhost_3306
  124. Source Server Version : </span>50720<span style="color: #000000">
  125. Source Host           : localhost:</span>3306<span style="color: #000000">
  126. Source Database       : student
  127. Target Server Type    : MYSQL
  128. Target Server Version : </span>50720<span style="color: #000000">
  129. File Encoding         : </span>65001<span style="color: #000000">
  130. Date: </span>2018-01-02 12:05:30
  131. */<span style="color: #000000">
  132. SET FOREIGN_KEY_CHECKS</span>=<span style="color: #000000">0;
  133. </span>-- ----------------------------
  134. -- Table structure <span style="color: #0000ff">for</span><span style="color: #000000"> course
  135. </span>-- ----------------------------<span style="color: #000000">
  136. DROP TABLE IF EXISTS `course`;
  137. CREATE TABLE `course` (
  138.   `c_id` int(</span>11<span style="color: #000000">) NOT NULL,
  139.   `c_name` varchar(</span>255<span style="color: #000000">) DEFAULT NULL,
  140.   `t_id` int(</span>11<span style="color: #000000">) DEFAULT NULL,
  141.   PRIMARY KEY (`c_id`),
  142.   KEY `t_id` (`t_id`)
  143. ) ENGINE</span>=InnoDB DEFAULT CHARSET=<span style="color: #000000">utf8;
  144. </span>-- ----------------------------
  145. --<span style="color: #000000"> Records of course
  146. </span>-- ----------------------------<span style="color: #000000">
  147. INSERT INTO `course` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">python</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span><span style="color: #000000">);
  148. INSERT INTO `course` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">java</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span><span style="color: #000000">);
  149. INSERT INTO `course` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">linux</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span><span style="color: #000000">);
  150. INSERT INTO `course` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">web</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span><span style="color: #000000">);
  151. </span>-- ----------------------------
  152. -- Table structure <span style="color: #0000ff">for</span><span style="color: #000000"> score
  153. </span>-- ----------------------------<span style="color: #000000">
  154. DROP TABLE IF EXISTS `score`;
  155. CREATE TABLE `score` (
  156.   `id` int(</span>11<span style="color: #000000">) NOT NULL AUTO_INCREMENT,
  157.   `s_id` int(</span>10<span style="color: #000000">) DEFAULT NULL,
  158.   `c_id` int(</span>11<span style="color: #000000">) DEFAULT NULL,
  159.   `num` double DEFAULT NULL,
  160.   PRIMARY KEY (`id`)
  161. ) ENGINE</span>=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=<span style="color: #000000">utf8;
  162. </span>-- ----------------------------
  163. --<span style="color: #000000"> Records of score
  164. </span>-- ----------------------------<span style="color: #000000">
  165. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">79</span><span style="color: #800000">‘</span><span style="color: #000000">);
  166. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">78</span><span style="color: #800000">‘</span><span style="color: #000000">);
  167. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">35</span><span style="color: #800000">‘</span><span style="color: #000000">);
  168. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">32</span><span style="color: #800000">‘</span><span style="color: #000000">);
  169. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">5</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">66</span><span style="color: #800000">‘</span><span style="color: #000000">);
  170. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">6</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">77</span><span style="color: #800000">‘</span><span style="color: #000000">);
  171. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">7</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">68</span><span style="color: #800000">‘</span><span style="color: #000000">);
  172. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">8</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">5</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">66</span><span style="color: #800000">‘</span><span style="color: #000000">);
  173. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">9</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">69</span><span style="color: #800000">‘</span><span style="color: #000000">);
  174. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">10</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">75</span><span style="color: #800000">‘</span><span style="color: #000000">);
  175. INSERT INTO `score` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">11</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">5</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">66.7</span><span style="color: #800000">‘</span><span style="color: #000000">);
  176. </span>-- ----------------------------
  177. -- Table structure <span style="color: #0000ff">for</span><span style="color: #000000"> student
  178. </span>-- ----------------------------<span style="color: #000000">
  179. DROP TABLE IF EXISTS `student`;
  180. CREATE TABLE `student` (
  181.   `s_id` varchar(</span>20<span style="color: #000000">) NOT NULL,
  182.   `s_name` varchar(</span>255<span style="color: #000000">) DEFAULT NULL,
  183.   `s_age` int(</span>10<span style="color: #000000">) DEFAULT NULL,
  184.   `s_sex` char(</span>1<span style="color: #000000">) DEFAULT NULL,
  185.   PRIMARY KEY (`s_id`)
  186. ) ENGINE</span>=InnoDB DEFAULT CHARSET=<span style="color: #000000">utf8;
  187. </span>-- ----------------------------
  188. --<span style="color: #000000"> Records of student
  189. </span>-- ----------------------------<span style="color: #000000">
  190. INSERT INTO `student` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">鲁班</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">12</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">男</span><span style="color: #800000">‘</span><span style="color: #000000">);
  191. INSERT INTO `student` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">貂蝉</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">20</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">女</span><span style="color: #800000">‘</span><span style="color: #000000">);
  192. INSERT INTO `student` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">刘备</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">35</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">男</span><span style="color: #800000">‘</span><span style="color: #000000">);
  193. INSERT INTO `student` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">关羽</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">34</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">男</span><span style="color: #800000">‘</span><span style="color: #000000">);
  194. INSERT INTO `student` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">5</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">张飞</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">33</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">女</span><span style="color: #800000">‘</span><span style="color: #000000">);
  195. </span>-- ----------------------------
  196. -- Table structure <span style="color: #0000ff">for</span><span style="color: #000000"> teacher
  197. </span>-- ----------------------------<span style="color: #000000">
  198. DROP TABLE IF EXISTS `teacher`;
  199. CREATE TABLE `teacher` (
  200.   `t_id` int(</span>10<span style="color: #000000">) NOT NULL,
  201.   `t_name` varchar(</span>50<span style="color: #000000">) DEFAULT NULL,
  202.   PRIMARY KEY (`t_id`)
  203. ) ENGINE</span>=InnoDB DEFAULT CHARSET=<span style="color: #000000">utf8;
  204. </span>-- ----------------------------
  205. --<span style="color: #000000"> Records of teacher
  206. </span>-- ----------------------------<span style="color: #000000">
  207. INSERT INTO `teacher` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">1</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">大王</span><span style="color: #800000">‘</span><span style="color: #000000">);
  208. INSERT INTO `teacher` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span><span style="color: #000000">);
  209. INSERT INTO `teacher` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">);
  210. INSERT INTO `teacher` VALUES (</span><span style="color: #800000">‘</span><span style="color: #800000">4</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">peiqi</span><span style="color: #800000">‘</span><span style="color: #000000">);
  211. </span><span style="color: #008000">#</span><span style="color: #008000">7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]</span>
  212. <span style="color: #000000">
  213. select  score.c_id,
  214.           course.c_name, 
  215.       sum(CASE WHEN num BETWEEN </span>85 <span style="color: #0000ff">and</span> 100 THEN 1 ELSE 0 END) as <span style="color: #800000">‘</span><span style="color: #800000">[100-85]</span><span style="color: #800000">‘</span><span style="color: #000000">,
  216.       sum(CASE WHEN num BETWEEN </span>70 <span style="color: #0000ff">and</span> 85 THEN 1 ELSE 0 END) as <span style="color: #800000">‘</span><span style="color: #800000">[85-70]</span><span style="color: #800000">‘</span><span style="color: #000000">,
  217.       sum(CASE WHEN num BETWEEN </span>60 <span style="color: #0000ff">and</span> 70 THEN 1 ELSE 0 END) as <span style="color: #800000">‘</span><span style="color: #800000">[70-60]</span><span style="color: #800000">‘</span><span style="color: #000000">,
  218.       sum(CASE WHEN num </span>< 60 THEN 1 ELSE 0 END) as <span style="color: #800000">‘</span><span style="color: #800000">[ <60]</span><span style="color: #800000">‘</span>
  219. <span style="color: #0000ff">from</span> score,course where score.c_id=course.c_id GROUP BY score.c_id;
View Code 技术分享图片 
  1. <span style="color: #008000">#</span><span style="color: #008000">1 基本使用</span>
  2. mysql> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2009-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%W %M %Y</span><span style="color: #800000">‘</span><span style="color: #000000">);
  3.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">Sunday October 2009</span><span style="color: #800000">‘</span><span style="color: #000000">
  4. mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2007-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000" <="" div="">
  5.                   
  6.  
  7.                     <div class="">
  8.                         <ul class="m-news-opt fix">
  9.                             <li class="opt-item">
  10.                                 <a href="/sql_question-410944.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">mysql三-1:存储引擎</p></a>
  11.                             </li>
  12.                             <li class="opt-item ta-r">
  13.                                  <a href="/sql_question-410946.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">Zedboard(二)使用Vivado+SDK开发嵌入式应用程序——实例一</p></a>
  14.                             </li>
  15.                         </ul>
  16.                     </div>
  17.                     
  18.                     
  19.                     
  20.                     
  21.                 </span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！