1 子查询

1：用select返回的值当做查询条件
SELECT * FROM book 
WHERE class = (SELECT class FROM book WHERE NAME = 'Java从入门到精通');

多行比较操作符 IN, ALL, ANY
其中 ALL和 ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。

2：in 关键字 查询员工表(employee)数据，保证查出来的员工的部门是存在的。
  (根据部门表department中返回的数据，来查询当前表)
 SELECT * FROM employee
 WHERE d_id IN (SELECT d_id FROM department);

3: EXISTS关键字，子查询语句不返回查询的记录，而是返回一个真假值。
   列出来哪些有员工的部门信息。(d 和 e 为别名的一种形式)
SELECT * FROM department d
WHERE EXISTS (SELECT * FROM employee e WHERE d.d_id = e.d_id);

4: 子查询在 HAVING子句中  （having 多行过虑）
   查询列出最低价格高于传记类图书的最低价格的图书信息。
SELECT NAME ,MIN(price) '最低价格' , class FROM book GROUP BY class
HAVING MIN(price) >= (SELECT MIN(price) FROM book WHERE class = '传记类');

5: 子查询在 FROM子句中
   FROM 子句用来指定要查询的表
   如果要在一个子查询的结果中继续查询，则子查询出现在 FROM 子句中，
   这个子查询也称作行内视图或者匿名视图。
   
   查询出价格比本类图书平均价格要高的图书信
SELECT * FROM book b,
(SELECT class, AVG(price) avg_price FROM book GROUP BY class) xx
WHERE b.class = xx.class AND b.price > xx.avg_price;

6: 子查询在 SELECT 部分
   把子查询放在SELECT 子句部分,可以认为是外连接的另一种表现形式，使用更灵活。
SELECT e.name, e.age,
(SELECT d.d_id FROM department d WHERE d.d_id = e.d_id)
departmentID FROM employee e;

分页查询

1: LIMIT 不指定初始位置时，记录从第一条记录开始显示。
SELECT * FROM book LIMIT 2;

2: LIMIT 可以指定从哪条记录开始显示,并且可以指定显示多少条记录。
SELECT * FROM book LIMIT 1, 2;

3：实现分页
SELECT * FROM book LIMIT pageSize * (page - 1), pageSize;
pageSisz : 要分的页的大小 5 ，  page：第几页 1

如第一页：
SELECT * FROM book LIMIT 5*（1-1）, 5; // 示意不能执行
SELECT * FROM book LIMIT 0, 5; // 第一页执行
如第二页：
SELECT * FROM book LIMIT 5*（2-1）, 5; // 示意不能执行
SELECT * FROM book LIMIT 5, 5; // 第二页执行

4：分页优化:
SELECT * FROM book WHERE id >= (SELECT id FROM book LIMIT
pageSize*(page - 1), 1) LIMIT pageSize;

SELECT * FROM (SELECT * FROM book ORDER BY id) b
WHERE id >= (SELECT id FROM (SELECT * FROM book ORDER BY id) b LIMIT 3,1) LIMIT 3;

返回id作为limit的起始位置，以page作为一个变量

计算 sum， count， avg，等合计函数时排除null值

SELECT class, AVG(IFNULL(price, 0)) '平均价格' FROM book GROUP BY class;

CASE 函数在分组查询中的应用

1：技术类和传记类是我的最爱favorite，其他书没感觉normal。计算出我喜欢的书。
SELECT
(CASE class WHEN '技术类' THEN 'favorite'
WHEN '传记类' THEN 'favorite'
ELSE 'normal' END ) Favorite,
COUNT(1) COUNT
FROM book
GROUP BY
(CASE class WHEN '技术类' THEN 'favorite'
WHEN '传记类' THEN 'favorite'
ELSE 'normal' END );

2 关联查询

1：连接查询 通常建立在存在相互关系的父子表之间两表都有d_id属性
SELECT e.`name`, d.`d_name` 
FROM employee e, department d 
WHERE e.`d_id` = d.`d_id` ;

2：笛卡尔积 指做关联操作的每个表的第一行者和其它表的每一行做组合
SELECT COUNT(1) FROM department;//4行
SELECT COUNT(1) FROM employee; //4行
SELECT department.`d_name`,employee.`name` FROM department, employee;//关联之后16行


3：等值连接 有主外键关联关系的表间建立，
   并将连接条件设定为有关系的列，使用等号 “=” 连接相关的表。

查询学生姓名、性别、年龄，以及班主任信息
SELECT s.name, s.sex, s.age, t.name
FROM student s, teacher t
WHERE s.teacherNO = t.id;
-----------------------------------------------------------------------------
//创建一个teacher表，其中id为主键。
CREATE TABLE teacher (
  id INT PRIMARY KEY,
  NAME VARCHAR (20) NOT NULL,
  sex VARCHAR (2),
  age INT
);
INSERT INTO teacher VALUE(1,'李老师','男',28);
INSERT INTO teacher VALUE(2,'刘老师','女',22);
INSERT INTO teacher VALUE(3,'胡老师','男',34);
INSERT INTO teacher VALUE(4,'王老师','女',20);

//创建一个student表，其中id为主键，teacherNo为外键与teacher表的id键关联
CREATE TABLE student (
  id INT AUTO_INCREMENT PRIMARY KEY, // 自增 主键
  NAME VARCHAR (20) NOT NULL,
  sex VARCHAR (2),
  age INT,
  teacherNo INT,
  CONSTRAINT teacherNo_fk FOREIGN KEY (teacherNo) REFERENCES teacher (id)
);
INSERT INTO student(NAME, sex, age, teacherNo) VALUES('张三', '男', 19, 1);
INSERT INTO student(NAME, sex, age, teacherNo) VALUES('李四', '女', 18, 2);
INSERT INTO student(NAME, sex, age, teacherNo) VALUES('王五', '男', 19, 2);
INSERT INTO student(NAME, sex, age, teacherNo) VALUES('赵六', '女', 30, 3);

SELECT s.`name` AS '学生',t.`NAME` AS '老师'
FROM  student s ,teacher t
WHERE s.`teacherNo` = t.`id`;
-----------------------------------------------------------------------------
4：内连接，即返回所有满足连接条件的记录
SELECT s.name, t.name 
FROM student s JOIN teacher t
ON(s.`teacherNo` = t.id);

5：外连接，分为左连接和右连接
   返回主表的所有记录，而连接的表，只能查询出匹配的记录，如果没有匹配上的记录，为null

   左连接，
SELECT  NAME, employee.`d_id`, d_name
FROM employee LEFT JOIN department
ON employee.`d_id` = department.`d_id`;

   右连接，
SELECT NAME, employee.`d_id`, d_name
FROM employee RIGHT JOIN department
ON employee.`d_id` = department.`d_id`;

3 视图

视图(VIEW)也被称作虚表,视图本身并不包含任何数据，它只包含映射到基表的一个查询语句， 
当基表数据发生变化， 视图数据也随之变化。

基表分类：

简单视图：SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数
复杂视图：SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句
连接视图：SELECT语句是基于多个表的

视图的作用:

1: 如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，
此后查询此视图即可，简化复杂查询

2: 视图本质上就是一条 SELECT 语句，所以当访问视图时，
只能访问到所对应的 SELECT 语句中涉及到的列，对基表中的其它列起到安全和保密的作用，
限制数据访问。

给用户授权创建视图

1: 给root用户授权db01下面的所有表，都有create VIEW 的权限
GRANT CREATE VIEW ON db01.* TO 'yxh';

2：查看权限
SHOW GRANTS FOR yxh;

创建视图

CREATE [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名 [(属性清单)]
AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION];

ALGORITHM表示视图选择的算法。
属性清单是可选参数，指定了视图中各个属性的名词，
默认情况下与SELECT 语句中查询的属性相同。
SELECT语句参数是一个完整的查询语句，
表示从某个表中查出某些满足条件的记录，将这些记录导入视图中。

1：创建简单视图
CREATE VIEW book_view (书名, 作者, 出版编号)
AS SELECT NAME, author, ISBN FROM book;

2：查看视图
   查看视图必须要有 SHOW VIEW 的权限。授权类似于 CREATE VIEW 授权。
DESC book_view; //查看字段信息
SHOW TABLE STATUS LIKE 'book_view';
SHOW CREATE VIEW book_view; //查看创造信息

3：查询视图内容
SELECT * FROM book_view;

对视图进行 INSERT 操作

1：简单视图能够执行 DML 操作，下列情况除外：在基表中定义了非空列， 
   但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列对视图不可见，
   这时无法对视图执行INSERT 操作。
  
INSERT INTO book_view
VALUE (11,'三国志','施耐庵','公元200年');

// 对视图进行修改操作同时修改基表内容
DELETE FROM book_view WHERE 号码 = 10;
   
2：如果视图定义中包含了函数、 表达式、 分组语句、DISTINCT关键字或ROWNUM伪列，
   不允许执行DML 操作

3：创建具有 CHECK OPTION约束的视图
   WITH CHECK OPTION 短语表示，通过视图所做的修改，必须在视图的可见范围内。
   假设 INSERT，新增的记录在视图仍可查看
   假设 UPDATE，修改后的结果必须能通过视图查看到
   假设 DELETE，只能删除现有视图里能查到的记录。

//创建一个只有技术类图书的视图
CREATE OR REPLACE VIEW book_view1
AS SELECT id, NAME, author, price, class
FROM book WHERE class='技术类' WITH CHECK OPTION;

//插入 '异类' 的图书，发现无法通过。必须在视图的可见范围内
INSERT INTO book_view1
VALUE (15,'无题','傻叉','99.9','异类'); // 改成技术类才行 

4: 创建复杂视图（多表关联）

CREATE VIEW new_list AS 
SELECT 
  d.d_name AS '部门',
  AVG(e.salary) AS '平均薪水',
  SUM(e.salary) AS '薪水总和',
  MAX(e.salary) AS '最高薪水',
  MIN(e.salary) AS '最低薪水'
FROM
  employee e JOIN department d ON e.d_id = d.d_id
 GROUP BY d.d_name;

//查询
SELECT * FROM new_list;
  
//复杂视图不允许 DML操作。

删除视图

视图虽然是存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，
所以对视图的删除不会导致基表数据的丢失，不会影响基表数据。

DROP VIEW book_view1;