2.10 联表查询
2.10.1 INNER JOIN
只取两张表有对应关系的记录
SELECT cid FROM `course` INNER JOIN `teacher` ON course.teacher_id = teacher.tid;
2.10.2 LEFT JOIN
在内连接的基础上保留左表没有对应关系的记录
SELECT course.cid FROM `course` LEFT JOIN `teacher` ON course.teacher_id = teacher.tid;
2.10.3 RIGHT JOIN
在内连接的基础上保留右表没有对应关系的记录
SELECT course.cid FROM `course` RIGHT JOIN `teacher` ON course.teacher_id = teacher.tid;
2.11 子查询/合并查询
2.11.1 单行子查询
SELECT * from course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '谢小二老师')
2.11.2 多行子查询
多行子查询即返回多行记录的子查询.
IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（ true ），否则，将返回一个假值（ false ）。当返回的值为 true 时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。
ALL 关键字：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。
在 FROM 子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用。
select * from student where class_id in (select cid from course where teacher_id = 2); select * from student where exists(select cid from course where cid = 5); select student_id,sname FROM (SELECT * FROM score WHERE course_id = 5 OR course_id = 2) AS A LEFT JOIN student ON A.student_id = student.sid;
三、视图
视图 view 不是表，是一种虚表，没有实体，并不实际存储数据，其内容由查询 select 定义。用来创建视图的表称为基表，通过视图，可以展现基表的部分数据。
视图的优点：
1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
CREATE VIEW 视图名 AS SELECT 语句
例如，查询 ‘数学’ 课程比 ‘语文’ 课程成绩高的所有学生的学号
CREATE VIEW view_test1 AS SELECT A.student_id FROM ( (SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = '数学') ) AS A INNER JOIN (SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = '语文') ) AS B ON A.student_id = B.student_id ) WHERE A.num > B.num; SELECT * FROM view_test1;
作用：
1）权限管理。可以隐藏表结构；视图的表结构可以开放给用户，但不会开放基表的表结构。即某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作。
2）可复用。减少重复语句书写；类似程序中函数的作用。
3）节约资源。一些列表的关联查询构成的数据包比较大，而使用 select … from view构成的数据包就小多了。
4）重构利器。假如因为某种需求，需要将 user 拆成表 usera 和表 userb；如果应用程序使用 sql 语句：select * from user那就会提示该表不存在；若此时创建视图
create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;
则只需要更改数据库结构，而不需要更改应用程序。
5）逻辑更清晰，屏蔽查询细节，关注数据返回。
四、触发器
触发器（trigger）是一种对表执行某操作后会触发执行其他命令的机制。
4.1 要素
监视对象：table
监视事件：insert、update、delete
触发时间：before ，after
触发事件：insert、update、delete
4.2 语法
-- 创建触发器 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_body END -- trigger_time: { BEFORE | AFTER } -- trigger_event: { INSERT | UPDATE | DELETE } -- trigger_order: { FOLLOWS | PRECEDES } -- trigger_body：tbl_name表更新前(OLD, 列名)，表更新后(NEW, 列名) -- 确认触发器 SHOW TRIGGERS -- 删除触发器 SHOW TRIGGER trigger_name
4.3 例子
DELIMITER 是在 MySQL 数据库中使用的一个关键字，用于指定 SQL 语句的分隔符。它的作用是告诉 MySQL 解析器在遇到指定的分隔符时，将整个语句作为一个整体进行处理，而不会将其中的分号视为语句的结束。通常指定 $$ 或 ||
准备
-- 创建触发器 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_body END -- trigger_time: { BEFORE | AFTER } -- trigger_event: { INSERT | UPDATE | DELETE } -- trigger_order: { FOLLOWS | PRECEDES } -- trigger_body：tbl_name表更新前(OLD, 列名)，表更新后(NEW, 列名) -- 确认触发器 SHOW TRIGGERS -- 删除触发器 SHOW TRIGGER trigger_name
DROP TABLE IF EXISTS `goods`; DROP TABLE IF EXISTS `order`; CREATE TABLE `goods` ( `gid` INT PRIMARY KEY auto_increment, `name` VARCHAR (32), `num` SMALLINT DEFAULT 0 ); CREATE TABLE `order` ( `id` INT PRIMARY KEY auto_increment, `gid` INT, `quantity` SMALLINT COMMENT '下单数量' ); DROP TRIGGER if EXISTS `trig_order_1`; DROP TRIGGER if EXISTS `trig_order_2`;
需求1：客户新建订单购买的数量，商品表的库存数量自动改变
delimiter // CREATE TRIGGER `trig_order_1` AFTER INSERT ON `order` FOR EACH ROW BEGIN UPDATE goods SET num = num - new.quantity WHERE gid = new.gid; END // delimiter ; -- 测试1：新建订单 INSERT INTO `order` VALUES (NULL, 1, 2); INSERT INTO `order` VALUES (NULL, 2, 2); INSERT INTO `order` VALUES (NULL, 3, 2); SELECT * FROM `order`;
需求2：客户修改订单购买的数量，商品表的库存数量自动改变
delimiter // EACH ROW BEGIN UPDATE goods SET num = num + old.quantity - new.quantity WHERE gid = new.gid; END // delimiter ; INSERT INTO `goods` VALUES (NULL, 'cat', 10); INSERT INTO `goods` VALUES (NULL, 'dog', 10); INSERT INTO `goods` VALUES (NULL, 'pig', 10); -- 测试2：修改订单 UPDATE `order` SET quantity = quantity + 2 WHERE gid = 1; SELECT * FROM `goods`;
五、权限管理
5.1 创建用户
CREATE USER username@host IDENTIFIED BY password;
host 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost ，如果想让该用户可以从任意远程主机登陆，可以使用通配符 % ;
5.2 授权
5.2.1 对表授权
GRANT privileges ON database_name.table_name TO 'username'@'host' WITH GRANT OPTION;
privileges ：用户的操作权限，如 SELECT ， INSERT ，UPDATE 等，如果要授予所的权限则使用ALL；
databasename.tablename 如果是.表示任意数据库以及任意表；
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION 选项导致后来该用户不能使用 GRANT 命令创建用户或者给其它用户授权。
如果不想这个用户有这个 grant 的权限，则不要加该 WITHGRANT OPTION 选项；
5.2.2 对视图授权
GRANT SELECT, SHOW VIEW ON database_name.view_name TO 'username'@'host';
5.2.3 刷新权限
-- 修改权限后需要刷新权限 FLUSH PRIVILEGES;
