mysql平均数查询

简易执行过程

准备SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for class-- ----------------------------ｄｒｏｐ TABLE IF EXISTS `class`;CREATE TABLE `class` (`cid` int(11) NOT NULL AUTO_INCREMENT,`caption` varchar(32) NOT NULL,PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of class-- ----------------------------ｉｎｓｅｒｔ INTO `class` VALUES (‘1‘, ‘1908班‘);ｉｎｓｅｒｔ INTO `class` VALUES (‘2‘, ‘2004班‘);ｉｎｓｅｒｔ INTO `class` VALUES (‘3‘, ‘2101班‘);ｉｎｓｅｒｔ INTO `class` VALUES (‘4‘, ‘2109班‘);-- ------------------------------ Table structure for course-- ----------------------------ｄｒｏｐ TABLE IF EXISTS `course`;CREATE TABLE `course` (`cid` int(11) NOT NULL AUTO_INCREMENT,`cname` varchar(32) NOT NULL,`teacher_id` int(11) NOT NULL,PRIMARY KEY (`cid`),KEY `fk_course_teacher` (`teacher_id`),CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;QT开发交流+赀料君羊：714620761-- ------------------------------ Records of course-- ----------------------------ｉｎｓｅｒｔ INTO `course` VALUES (‘1‘, ‘c++‘, ‘1‘);ｉｎｓｅｒｔ INTO `course` VALUES (‘2‘, ‘golang‘, ‘2‘);ｉｎｓｅｒｔ INTO `course` VALUES (‘3‘, ‘rust‘, ‘4‘);ｉｎｓｅｒｔ INTO `course` VALUES (‘4‘, ‘java‘, ‘2‘);ｉｎｓｅｒｔ INTO `course` VALUES (‘5‘, ‘python‘, ‘1‘);-- ------------------------------ Table structure for score-- ----------------------------ｄｒｏｐ TABLE IF EXISTS `score`;CREATE TABLE `score` (`sid` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`course_id` int(11) NOT NULL,`num` int(11) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_score_student` (`student_id`),KEY `fk_score_course` (`course_id`),CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;-- ------------------------------ Records of score-- ----------------------------ｉｎｓｅｒｔ INTO `score` VALUES (‘1‘, ‘1‘, ‘1‘, ‘10‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘2‘, ‘1‘, ‘2‘, ‘59‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘3‘, ‘4‘, ‘5‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘4‘, ‘5‘, ‘4‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘5‘, ‘2‘, ‘2‘, ‘66‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘6‘, ‘2‘, ‘1‘, ‘58‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘8‘, ‘2‘, ‘3‘, ‘68‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘9‘, ‘2‘, ‘4‘, ‘99‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘10‘, ‘3‘, ‘1‘, ‘77‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘11‘, ‘3‘, ‘2‘, ‘66‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘12‘, ‘3‘, ‘3‘, ‘87‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘13‘, ‘3‘, ‘4‘, ‘99‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘14‘, ‘4‘, ‘1‘, ‘79‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘15‘, ‘4‘, ‘2‘, ‘11‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘16‘, ‘4‘, ‘3‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘17‘, ‘4‘, ‘4‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘18‘, ‘5‘, ‘1‘, ‘79‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘19‘, ‘5‘, ‘2‘, ‘11‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘20‘, ‘5‘, ‘3‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘21‘, ‘5‘, ‘5‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘22‘, ‘6‘, ‘1‘, ‘9‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘23‘, ‘6‘, ‘2‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘24‘, ‘6‘, ‘3‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘25‘, ‘6‘, ‘4‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘26‘, ‘7‘, ‘1‘, ‘9‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘27‘, ‘7‘, ‘2‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘28‘, ‘7‘, ‘3‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘29‘, ‘7‘, ‘4‘, ‘88‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘30‘, ‘8‘, ‘1‘, ‘49‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘31‘, ‘8‘, ‘2‘, ‘100‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘32‘, ‘8‘, ‘3‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘33‘, ‘8‘, ‘4‘, ‘88‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘34‘, ‘9‘, ‘1‘, ‘91‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘35‘, ‘9‘, ‘2‘, ‘88‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘36‘, ‘9‘, ‘5‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘37‘, ‘9‘, ‘4‘, ‘22‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘38‘, ‘10‘, ‘1‘, ‘90‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘39‘, ‘10‘, ‘2‘, ‘77‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘40‘, ‘10‘, ‘3‘, ‘43‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘41‘, ‘10‘, ‘4‘, ‘87‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘42‘, ‘11‘, ‘1‘, ‘90‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘43‘, ‘11‘, ‘2‘, ‘77‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘44‘, ‘11‘, ‘5‘, ‘43‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘45‘, ‘11‘, ‘4‘, ‘87‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘46‘, ‘12‘, ‘1‘, ‘90‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘47‘, ‘12‘, ‘2‘, ‘77‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘48‘, ‘12‘, ‘3‘, ‘43‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘49‘, ‘12‘, ‘5‘, ‘87‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘50‘, ‘13‘, ‘3‘, ‘87‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘51‘, ‘14‘, ‘2‘, ‘33‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘52‘, ‘15‘, ‘3‘, ‘22‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘53‘, ‘15‘, ‘5‘, ‘11‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘54‘, ‘13‘, ‘1‘, ‘99‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘55‘, ‘13‘, ‘2‘, ‘99‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘56‘, ‘13‘, ‘4‘, ‘67‘);ｉｎｓｅｒｔ INTO `score` VALUES (‘57‘, ‘13‘, ‘5‘, ‘87‘);-- ------------------------------ Table structure for student-- ----------------------------ｄｒｏｐ TABLE IF EXISTS `student`;CREATE TABLE `student` (`sid` int(11) NOT NULL AUTO_INCREMENT,`gender` ｃｈａｒ(1) NOT NULL,`class_id` int(11) NOT NULL,`sname` varchar(32) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------ｉｎｓｅｒｔ INTO `student` VALUES (‘1‘, ‘男‘, ‘1‘, ‘罗谊‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘2‘, ‘女‘, ‘1‘, ‘戴巧‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘3‘, ‘男‘, ‘1‘, ‘叶黎‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘4‘, ‘男‘, ‘3‘, ‘邵柴‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘5‘, ‘女‘, ‘1‘, ‘韩琪‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘6‘, ‘男‘, ‘3‘, ‘尹伸‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘7‘, ‘女‘, ‘2‘, ‘孙燕‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘8‘, ‘男‘, ‘2‘, ‘廖宽‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘9‘, ‘男‘, ‘2‘, ‘孙行‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘10‘, ‘女‘, ‘2‘, ‘宋贤‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘11‘, ‘男‘, ‘2‘, ‘谭国兴‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘12‘, ‘女‘, ‘3‘, ‘于怡瑶‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘13‘, ‘男‘, ‘4‘, ‘文乐逸‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘14‘, ‘男‘, ‘4‘, ‘邹乐和‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘15‘, ‘女‘, ‘5‘, ‘邓洋洋‘);ｉｎｓｅｒｔ INTO `student` VALUES (‘16‘, ‘男‘, ‘5‘, ‘秦永福‘);-- ------------------------------ Table structure for teacher-- ----------------------------ｄｒｏｐ TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`tid` int(11) NOT NULL AUTO_INCREMENT,`tname` varchar(32) NOT NULL,PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of teacher-- ----------------------------ｉｎｓｅｒｔ INTO `teacher` VALUES (‘1‘, ‘王天一老师‘);ｉｎｓｅｒｔ INTO `teacher` VALUES (‘2‘, ‘谢小二老师‘);ｉｎｓｅｒｔ INTO `teacher` VALUES (‘3‘, ‘廖阿三老师‘);ｉｎｓｅｒｔ INTO `teacher` VALUES (‘4‘, ‘吴启四老师‘);ｉｎｓｅｒｔ INTO `teacher` VALUES (‘5‘, ‘谢飞五老师‘);基础查询-- 全部查询ｓｅｌｅｃｔ * FROM student;-- 只查询部分字段ｓｅｌｅｃｔ `sname`, `class_id` FROM student;-- 别名 列名 不要用关键字ｓｅｌｅｃｔ `sname` AS ‘姓名‘ , `class_id` AS ‘班级ID‘ FROM student;-- 把查询出来的结果的重复记录去掉ｓｅｌｅｃｔ distinct `class_id` FROM student;条件查询-- 查询姓名为 邓洋洋 的学生信息ｓｅｌｅｃｔ * FROM `student` WHERE `name` = ‘邓洋洋‘;-- 查询性别为 男，并且班级为 2 的学生信息ｓｅｌｅｃｔ * FROM `student` WHERE `gender`="男" AND `class_id`=2;范围查询-- 查询班级id 1 到 3 的学生的信息ｓｅｌｅｃｔ * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;判空查询# is null 判断会造成索引失效ｓｅｌｅｃｔ * FROM `student` WHERE `class_id` IS NOT NULL; #判断不为空ｓｅｌｅｃｔ * FROM `student` WHERE `class_id` IS NULL; #判断为空ｓｅｌｅｃｔ * FROM `student` WHERE `gender` ‘‘; #判断不为空字符串ｓｅｌｅｃｔ * FROM `student` WHERE `gender` = ‘‘; #判断为空字符串模糊查询-- 使用 like关键字，"%"代表任意数量的字符，”_”代表占位符-- 查询名字为 m 开头的学生的信息ｓｅｌｅｃｔ * FROM `teacher` WHERE `tname` LIKE ‘谢%‘;-- 查询姓名里第二个字为 小 的学生的信息ｓｅｌｅｃｔ * FROM `teacher` WHERE `tname` LIKE ‘_小%‘;QT开发交流+赀料君羊：714620761分页查询-- 分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用-- 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0。-- 查询第二条到第三条内容ｓｅｌｅｃｔ * FROM `student` LIMIT 1,2;查询后排序-- 关键字：order by field, asc:升序, desc:降序ｓｅｌｅｃｔ * FROM `score` ORDER BY `num` ASC;-- 按照多个字段排序ｓｅｌｅｃｔ * FROM `score` ORDER BY `course_id` DESC, `num` DESC;聚合查询

聚合函数

描述

sum()

计算某列的总和

avg()

计算某列的平均值

max()

计算某列的最大值

min()

计算某列的最小值

count()

计算某列的行数

ｓｅｌｅｃｔ sum(`num`) FROM `score`;ｓｅｌｅｃｔ avg(`num`) FROM `score`;ｓｅｌｅｃｔ max(`num`) FROM `score`;ｓｅｌｅｃｔ min(`num`) FROM `score`;ｓｅｌｅｃｔ count(`num`) FROM `score`;分组查询-- 分组加group_concatSELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY `gender`;-- 可以把查询出来的结果根据某个条件来分组显示ｓｅｌｅｃｔ `gender` FROM `student` GROUP BY `gender`;-- 分组加聚合ｓｅｌｅｃｔ `gender`, count(*) as num FROM `student` GROUP BY `gender`;-- 分组加条件ｓｅｌｅｃｔ `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;联表查询

INNER JOIN

只取两张表有对应关系的记录

ｓｅｌｅｃｔ cidFROM `course` INNER JOIN `teacher` ON course.teacher_id = teacher.tid;

LEFT JOIN

在内连接的基础上保留左表没有对应关系的记录

ｓｅｌｅｃｔ course.cidFROM `course` LEFT JOIN `teacher` ON course.teacher_id = teacher.tid;

RIGHT JOIN

在内连接的基础上保留右表没有对应关系的记录

ｓｅｌｅｃｔ course.cidFROM `course` RIGHT JOIN `teacher` ON course.teacher_id = teacher.tid;子查询/合并查询单行子查询ｓｅｌｅｃｔ * from course where teacher_id = (ｓｅｌｅｃｔ tid from teacher where tname = ‘谢小二老师‘)多行子查询

多行子查询即返回多行记录的子查询

IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。

EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。

ALL 关键字：表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。s

在 FROM 子句中使用子查询：子查询出现在from子句中，这种情况下将子查询当做一个临时表使用。

ｓｅｌｅｃｔ *from studentwhere class_id in (ｓｅｌｅｃｔ cidfrom coursewhere teacher_id = 2);ｓｅｌｅｃｔ *from studentwhere exists(ｓｅｌｅｃｔ cid from course where cid = 5);正则表达式

选项

说明（自动加匹配二 字）

例子

匹配值示例

^

文本开始字符

‘^b‘匹配以字母b开头的字 符串

book, big, banana, bike

‘匹配以 st结尾的字符串

test, resist, persist

.

任何单个字符

‘b.t‘匹配任何b和t之间有一 个字符

bit, bat, but, bite

*

0个或多个在它前面的 字符

‘f*n‘匹配字符n前面有任意 n个字符f

fn, fan, faan, abcn

+

前面的字符一次或多 次

‘ba+‘匹配以b开头后面紧跟 至少一个a

ba, bay, bare, battle

包含指定字符串的文 本

‘fa‘

fan, afa, faad

[字符集合]

字符集合中的任一个 字符

‘[xz]‘匹配x或者z

d raizyz,ye,xzterabra, x-

[^]

不在括号中的任何字 符

‘[^abc]‘匹配任何不包含a、 b或c的字符串

desk, fox, f8ke

字符串

前面的字符串至少n次

b{2}匹配2个或更多的b

bbb, bbbb, bbbbbb

字符串

前面的字符串至少n 次，至多m次

b{2,4}匹配最少2个，最多4 个b

bb, bbb, bbbb

ｓｅｌｅｃｔ * FROM `teacher` WHERE `tname` REGEXP ‘^谢‘;视图定义

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。

基表：用来创建视图的表叫做基表；

通过视图，可以展现基表的部分数据；

视图数据来自定义视图的查询中使用的表，使用视图动态生成；

优点

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

语法CREATE VIEW AS QT开发交流+赀料君羊：714620761案例-- 创建视图-- 查询“c++”课程比“golang”课程成绩高的所有学生的学号；CREATE VIEW view_test1 ASSELECT A.student_idFROM (ｓｅｌｅｃｔ student_id, numFROM scoreWHERE course_id = 1) AS A -- 12 LEFT JOIN (ｓｅｌｅｃｔ student_id,numFROM scoreWHERE course_id = 2) AS B -- 11ON A.student_id = B.student_idWHERE A.num >IF(isnull(B.num), 0, B.num);作用可复用，减少重复语句书写；类似程序中函数的作用；重构利器

假如因为某种需求，需要将user拆房表usera和表userb；如果应用程序使用sql语句：ｓｅｌｅｃｔ * from user就会提示该表不存在；若此时创建视图create view user as ｓｅｌｅｃｔ a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;，则只需要更改数据库结构，而不需要更改应用程序；

视图在oracle 物化视图 mysql ｓｅｌｅｃｔ * from

逻辑更清晰，屏蔽查询细节，关注数据返回；权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作；流程控制IFIF condition THEN...ELSEIF condition THEN...ELSE...END IFCASE-- 相当于switch语句CASE valueWHEN value THEN ...WHEN value THEN ...ELSE ...END CASEWHILEWHILE condition DO...END WHILE;LEAVE-- 相当于breakLEAVE label;示例-- LEAVE语句退出循环或程序块，只能和BEGIN ... END，LOOP，REPEAT，WHILE语句配合使用-- 创建存储过程ｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE example_leave(OUT sum INT)BEGINDECLARE i INT DEFAULT 1;ｄｅｃｌａｒｅ s INT DEFAULT 0;while_label:WHILE i 100 THEN-- 退出LOOP循环LEAVE loop_label;END IF;END LOOP;SET sum = s;END//ｄｅｌｉｍｉｔｅｒ ;-- 调用存储过程CALL example_loop(@sum);ｓｅｌｅｃｔ @sum;REPEAT-- 相当于 do .. while(condition)REPEAT...UNTIL conditionEND REPEAT示例ｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE example_repeat(OUT sum INT)BEGINDECLARE i INT DEFAULT 1;ｄｅｃｌａｒｅ s INT DEFAULT 0;REPEATSET s = s + i;SET i = i + 1;UNTIL i > 100END REPEAT;SET sum = s;END//ｄｅｌｉｍｉｔｅｒ ;-- 调用存储过程CALL example_repeat(@sum);ｓｅｌｅｃｔ @sum;触发器定义

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行DML操作（ ｉｎｓｅｒｔ ， ｄｅｌｅｔｅ ， ｕｐｄａｔｅ ）时就会激活它执行。

4要素

监视对象： table

监视事件： ｉｎｓｅｒｔ 、 ｕｐｄａｔｅ 、 ｄｅｌｅｔｅ

触发时间： before ， after

触发事件： ｉｎｓｅｒｔ 、 ｕｐｄａｔｅ 、 ｄｅｌｅｔｅ

语法CREATE TRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROW[trigger_order]trigger_body -- 此处写执行语句-- trigger_body: 可以一个语句，也可以是多个语句；多个语句写在 BEGIN ... END 间-- trigger_time: { BEFORE | AFTER }-- trigger_event: { ｉｎｓｅｒｔ | ｕｐｄａｔｅ | ｄｅｌｅｔｅ }-- trigger_order: { FOLLOWS | PRECEDES } other_trigger_name准备CREATE TABLE `work`(`id`INT PRIMARY KEY auto_increment,`address` VARCHAR(32)) DEFAULT charset = utf8ENGINE = INNODB;CREATE TABLE `time`(`id`INT PRIMARY KEY auto_increment,`time` DATETIME) DEFAULT charset = utf8ENGINE = INNODB;CREATE TRIGGER trig_test1AFTER INSERTON `work`FOR EACH ROWINSERT INTO `time`VALUES (NULL, NOW());NEW 和 OLD

在 ｉｎｓｅｒｔ 型触发器中， NEW 用来表示将要（ BEFORE ）或已经（ AFTER ）插入的新数据；

在 ｄｅｌｅｔｅ 型触发器中， OLD 用来表示将要或已经被删除的原数据；

在 ｕｐｄａｔｅ 型触发器中， OLD 用来表示将要或已经被修改的原数据， NEW 用来表示将要或已经修改为的新数据；

NEW.columnName （columnName为相应数据表某一列名） OLD.columnName （columnName为相应数据表某一列名）

案例

在下订单的时候，对应的商品的库存量要相应的减少，即买几个商品就减少多少个库存量。

准备CREATE TABLE `goods`(`id`INT PRIMARY KEY auto_increment,`name` VARCHAR(32),`num`SMALLINT DEFAULT 0);CREATE TABLE `order`(`id`INT PRIMARY KEY auto_increment,`goods_id` INT,`quantity` SMALLINT COMMENT ‘下单数量‘);ｉｎｓｅｒｔ INTO goodsVALUES (NULL, ‘C++‘, 40);ｉｎｓｅｒｔ INTO goodsVALUES (NULL, ‘C‘, 63);ｉｎｓｅｒｔ INTO goodSVALUES (NULL, ‘mysql‘, 87);ｉｎｓｅｒｔ INTO `order`VALUES (NULL, 1, 3);ｉｎｓｅｒｔ INTO `order`VALUES (NULL, 2, 4);需求1

客户修改订单购买的数量，在原来购买数量的基础上减少2个；

-- ｄｅｌｉｍｉｔｅｒ-- ｄｅｌｉｍｉｔｅｒ是mysql分隔符，在mysql客户端中分隔符默认是分号 ;。如果一次输入的语句较多，并且语句中间有分号，这时需要重新指定一个特殊的分隔符。通常指定 $ 或 ||ｄｅｌｉｍｉｔｅｒ //CREATE TRIGGER trig_order_1AFTER INSERTON `order`FOR EACH ROWBEGINUPDATE goods SET num = num - 2 WHERE id = 1;END//ｄｅｌｉｍｉｔｅｒ ;需求2

客户修改订单购买的数量，商品表的库存数量自动改变

ｄｅｌｉｍｉｔｅｒ //CREATE TRIGGER trig_order_2BEFORE UPDATEON `order`FOR EACH ROWBEGINUPDATE goodsSET num=num + old.quantity - new.quantityWHERE id =new.goods_id;END//ｄｅｌｉｍｉｔｅｒ ;-- 测试ｕｐｄａｔｅ `order`SET quantity = quantity + 2WHERE id = 1;存储过程定义

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

特点能完成较复杂的判断和运算 有限的编程可编程行强，灵活SQL编程的代码可重复使用执行的速度相对快一些减少网络之间的数据传输，节省开销语法CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。

IN ：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设置默认值

OUT ：该值可在存储过程内部被改变，并可返回

INOUT ：调用时指定，并且可被改变和返回

过程体的开始与结束使用 BEGIN 与 END 进行标识。

案例ｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE proc_test1()BEGINSELECT current_time();ｓｅｌｅｃｔ current_date();END//ｄｅｌｉｍｉｔｅｒ ;call proc_test1();INｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE proc_in_param(IN p_in INT)BEGINSELECT p_in;SET p_in = 2;ｓｅｌｅｃｔ p_in;END;//ｄｅｌｉｍｉｔｅｒ ;-- 调用SET @p_in = 1;CALL proc_in_param(@p_in);-- p_in虽然在存储过程中被修改，但并不影响@p_id的值ｓｅｌｅｃｔ @p_in;-- 1OUTｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE proc_out_param(OUT p_out int)BEGINSELECT p_out;SET p_out = 2;ｓｅｌｅｃｔ p_out;END;//ｄｅｌｉｍｉｔｅｒ ;-- 调用SET @p_out = 1;CALL proc_out_param(@p_out);ｓｅｌｅｃｔ @p_out; -- 2INOUTｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE proc_inout_param(INOUT p_inout int)BEGINSELECT p_inout;SET p_inout = 2;ｓｅｌｅｃｔ p_inout;END;//ｄｅｌｉｍｉｔｅｒ ;#调用SET @p_inout = 1;CALL proc_inout_param(@p_inout);ｓｅｌｅｃｔ @p_inout; -- 2游标

游标是针对行操作的，对从数据库中 ｓｅｌｅｃｔ 查询得到的结果集的每一行可以进行分开的独立的相同或者不相同的操作。

对于取出多行数据集，需要针对每行操作；可以使用游标；游标常用于存储过程、函数、触发器、事件；

游标相当于迭代器

定义游标ｄｅｃｌａｒｅ cursor_name CURSOR FOR select_statement;打开游标OPEN cursor_name;取游标数据FETCH cursor_name INTO var_name[,var_name,......]关闭游标CLOSE curso_name;释放DEALLOCATE cursor_name;设置游标结束标志ｄｅｃｌａｒｅ done INT DEFAULT 0;ｄｅｃｌａｒｅ CONTINUE HANDLER FOR NOT FOUNDSET done = 1; -- done 为标记为案例CREATE PROCEDURE proc_while(IN age_in INT,OUT total_out INT)BEGIN-- 创建 用于接收游标值的变量ｄｅｃｌａｒｅ p_id,p_age,p_total INT;ｄｅｃｌａｒｅ p_sex TINYINT;-- 注意:接收游标值为中文时,需要给变量 指定字符集utf8DECLARE p_name VARCHAR(32) CHARACTER SET utf8; -- 游标结束的标志ｄｅｃｌａｒｅ done INT DEFAULT 0; -- 声明游标ｄｅｃｌａｒｅ cur_teacher CURSOR FOR ｓｅｌｅｃｔ teacher_id,teacher_name,teacher_sex,teacher_ageFROM teacherWHERE teacher_age > age_in; -- 指定游标循环结束时的返回值ｄｅｃｌａｒｅ CONTINUE HANDLER FOR NOT foundSET done = 1; -- 打开游标OPEN cur_teacher; -- 初始化 变量SET p_total = 0; -- while 循环WHILE done != 1DOFETCH cur_teacher INTO p_id,p_name,p_sex,p_age;IF done != 1 THENSET p_total = p_total + 1 ;ENDIF;END WHILE; -- 关闭游标CLOSE cur_teacher; -- 将累计的结果复制给输出参数SET total_out = p_total;END//ｄｅｌｉｍｉｔｅｒ ;-- 调用SET @p_age = 20;CALL proc_while(@p_age, @total);ｓｅｌｅｃｔ @total;