MySQL高级查询
多表查询
当前的应用系统在存储数据的时候为了⽅便对数据进⾏管理，都不会将所有的数据存放在⼀个数据库表中，比如：员⼯的个⼈资料和部门信息是分开存放在不同的表中的，但是员⼯个⼈信息中会标记出其所在的部门。此时在进⾏员⼯信息查询时，会涉及到多个表；
连接查询：同时涉及多个表的查询
连接条件或连接谓词：用来连接两个表的条件
⼀般格式：
[表名1.]<列名1> <比较运算符> [表名2.]<列名2>
[表名1.]<列名1> BETWEEN [表名2.]<列名2> AND [表名2.]<列名3>
• 连接条件中的各连接字段类型必须是可比的，但名字不必相同。
常⻅连接查询
- 内连接
- 外连接
- 复合条件连接
内连接—等值连接
例：查询每个员⼯所在的部门
内连接的写法：
第⼀种：使用连接符 =
select * from emp,dept where Dept_id = dept.id;
第⼆种：使用 join .. on..
select * from emp join dept on Dept_id = dept.id;
第三种：使用 inner join .. on..
select * from emp inner join dept on Dept_id = dept.id;
⾮等值连接查询
员⼯信息表enginfo
⼯资等级表salgrade
select e.ename as '姓名',s.id as '等级' from enginfo e,salgrade s
where e.sal BETWEEN s.local and s.maxline;
⾃身连接
- 自身连接：⼀个表与其自⼰进⾏连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性，因此必须使用别名前缀
例如：查询每门课程的先修课名称
SELECT Qir.cno,Qir.cname, Qir.cpno,sec.cname
FROM course Qir, course sec
WHERE Qir.cpno = sec.cno
外连接
- 外联接可以是左向外联接、右向外联接
- 在 FROM⼦句中指定外联接时，可以由下列⼏组关键字中的⼀组指定：
LEFT JOIN或LEFT OUTER JOIN
RIGHT JOIN 或 RIGHT OUTER JOIN
左连接
- 左向外联接的结果集包括 LEFT OUTER⼦句中指定的左表的所有⾏，⽽不仅仅是联接列所匹配的⾏。
- 如果左表的某⾏在右表中没有匹配⾏，则在相关联的结果集⾏中右表的所有选择列表列均为空值。
例：
SELECT s.sno, sname, sex, age, dept, cno, grade
FROM student s LEFT OUT JOIN sc ON s.sno=sc.sno
右连接
右向外联接是左向外联接的反向联接。将返回右表的所有⾏。如果右表的某⾏在左表中没有匹配⾏，则将为左表返回空值。
外键
什么是外键：
是另⼀表的主键, 外键可以有重复的, 可以是空值，用来和其他表建立联系用的。
主外键关系
在上图上涉及的两个表中学⽣信息表 (stuInfo)为主表，学⽣成绩表(score)为从表；那么主从表之间有什么关系呢？
1、当主表中没有对应的记录时，不能将记录添加到⼦表
学⽣成绩表中不能出现学⽣信息表中没有的学号；
2、不能更改主表中的值⽽导致⼦表中的记录孤立
学⽣信息表中的id变化了，学⽣成绩表中的id也要随着
发⽣改变；
3、⼦表存在与主表对应的记录，不能从主表中删除该⾏
不能把部门表中的数据删除
4、删除主表前，先删⼦表
先删除成绩表、后删除学⽣信息表；
外键的使⽤
外键的使用需要满⾜下列的条件：
1、两张表必须都是InnoDB表，并且它们没有临时表。
2、建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
3、建立外键关系的对应列必须建立了索引。
创建外键的两种⽅式：
⽅式⼀：在创建表的时候进⾏添加。
⽅式⼆：表已经创建好了，继续修改表的结构来添加外键。
在创建表的时候添加外键：
create table stuInfo(
Scode int primary key,-- 学⽣的学号
Sname char(10),-- 学⽣的姓名
Saddress varchar(50),-- 学⽣的住址
Sgrade int,-- 学⽣所在班级
Semail varchar(50),-- 学⽣的邮箱地址
Sbrith date
)DEFAULT CHARSET='utf8';
create table score(
studentID int,
coureseID int,
score int,
scoreID int primary key,
foreign key(studentID) references stuInfo(Scode) -- 添加外键
)DEFAULT charset='utf8';
删除外键：
语法：alter table 表名称 drop foreign key 外键名称;
例：alter table score drop foreign key score_ibQk_1;
注意：如果没有在建表的时候标明外键名称，可以通过：
show create table 表名 进⾏查看外键名称；
建表以后添加外键：
语法：alter table 表名称 add foreign key (列名称) references
关联表名称（列名称）;
例：alter table stuInfo add foreign key (scode) references
score(studentID);
⼦查询
- ⼦查询允许把⼀个查询嵌套在另⼀个查询当中。
- ⼦查询可以包含普通select可以包括的任何⼦句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之⼀：select、insert、update、delete、set或者do。
⼦查询的分类
- 标量⼦查询：
返回单⼀值的标量，最简单的形式。
2.列⼦查询：
返回的结果集是 N ⾏⼀列。 - ⾏⼦查询：
返回的结果集是⼀⾏ N 列。 - 表⼦查询：
返回的结果集是 N ⾏ N 列
可以使用的操作符：= > < >= <= <> ANY IN
SOME ALL EXISTS
标量⼦查询
是指⼦查询返回的是单⼀值的标量，如⼀个数字或⼀个字符串，也是⼦查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对⼦查询的标量结果进⾏比较，通常⼦查询的位置在比较式的右侧
SELECT * FROM article WHERE uid = (SELECT uid FROM user
WHERE status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid =t.uid)
列⼦查询
指⼦查询返回的结果集是 N ⾏⼀列，该结果通常来自对表的某个字段查询返回。
可以使用 IN、ANY和 ALL 操作符
SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)
⾏⼦查询
指⼦查询返回的结果集是⼀⾏ N 列，该⼦查询的结果通常是对表的某⾏数据进⾏查询⽽返回的结果集。
SELECT * FROM article WHERE (title,content,uid) =
(SELECT title, content,uid FROM blog WHERE bid=2)
表⼦查询
指⼦查询返回的结果集是 N ⾏ N 列的⼀个表数据。
SELECT * FROM article WHERE (title, content, uid)
IN (SELECT title, content, uid FROM blog)
EXIST谓词
EXISTS是⼀个非常⽜叉的谓词，它允许数据库⾼效地检查指定查询是否产⽣某些⾏。
select * from t1 where city=‘beijing' and exists
(select * from t2 where t1.cid=t2.cid);
派⽣表
在⼦查询返回的值中，也可能返回⼀个表，如果将⼦查询返回的虚拟表再次作为FROM⼦句的输⼊时，这就⼦查询的虚拟表就成为了⼀个派⽣表。
FROM (subquery expression) AS derived_table_alias
派⽣表使⽤
派⽣表⼀般与外连接，分组统计⼀起使用
SELECT t1.name,t2.sex,a.city,a.age FROM t1,
(SELECT city, MAX(age) FROM t2 GROUP BY city) a
WHERE t1.age=t2.age;
联合查询
使用UNION或UNION ALL关键字
相同结果被筛选掉了
SELECT cname,sex FROM users
UNION
SELECT name,sex FROM teacher;
有ALL，保留相同项
SELECT cname,sex FROM users
UNION ALL
SELECT name,sex FROM teacher;
