多表查询

一、连接查询

1.交叉连接： cross join；查询的结果是两个表的笛卡儿积

语法格式：select * from 表1 cross join 表2          ||         select * from 表1 ，表2     （cross join 可以省略）

2.内连接: inner join  （inner可以省略）

语法格式：

  显示内连接：select * from 表1 【inner】join 表2  on 关联条件

  隐式内连接： select * from 表1 ，表2 where 关联条件

3.外连接： outer join    （outer可以省略）

 左外连接： select * from  表1 left 【outer】 join 表2  on 关联条件，  结果以左表为准

 右外连接： select * from 表 1 rigth 【outer】 join 表2  on 关联条件，结果以右表为准

二、子查询：一个查询语句条件需要依赖另一个查询语句的结果，没有固定语法

-- 创建班级表
CREATE TABLE `classes` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `cnum` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
);
-- 插入班级信息
insert  into `classes`(`cid`,`cname`,`cnum`) values (1,'01班',60),(2,'02班',70),(3,'03班',90),(4,'04班',80);

-- 创建课程表
CREATE TABLE `cource` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`cid`)
);
-- 插入课程信息
INSERT  INTO `cource`(`cid`,`cname`) VALUES (1,'java'),(2,'php'),(3,'c++');

-- 创建学生表
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `sex` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `FK_studen001` (`cid`),
  CONSTRAINT `FK_studen001` FOREIGN KEY (`cid`) REFERENCES `classes` (`cid`)
);
-- 插入学生信息
INSERT  INTO `student`(`sid`,`sname`,`sex`,`birthday`,`cid`) VALUES (1,'张三','男',NULL,1),(2,'李四','男','1983-03-22',3),(3,'王五','女','1986-07-19',2),(4,'赵六','女','1993-05-29',3),(5,'田七','女','1985-04-23',1),(6,'老八','男','1982-01-16',2),(7,'鬼九','男','1992-10-26',4),(8,'丑鬼','男','1996-09-23',4),(9,'十娘','女','1987-10-26',NULL);

-- 创建学生选课表
CREATE TABLE `stu_cour` (
  `scid` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `score` double DEFAULT NULL,
  PRIMARY KEY (`scid`),
  KEY `FK_studencource_002` (`cid`),
  KEY `FK_studencource_001` (`sid`),
  CONSTRAINT `FK_studencource_001` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`),
  CONSTRAINT `FK_studencource_002` FOREIGN KEY (`cid`) REFERENCES `cource` (`cid`)
);

-- 插入学生选课信息
INSERT  INTO `stu_cour`(`scid`,`sid`,`cid`,`score`) VALUES 
(1,1,2,92),(2,1,3,89),(3,2,3,83),(4,2,1,74),(5,2,2,76),(6,3,2,68),(7,3,1,46),(8,4,1,52),(10,5,1,82),(11,5,2,93),(12,5,3,99),
(13,6,3,36),(14,6,1,69),(15,7,1,92),(16,7,2,85),(17,8,2,87),(18,8,3,92),(19,8,1,56),(21,9,2,73),(22,9,3,83);

1.学生表和班级表交叉查询

显示两表的笛卡儿积

2.查询所有学生的信息包括及他的班级信息

查出的数据是量表的交集

3.查询所有学生及其所在班级的信息，包含没有班级的学生的学生信息

4.查询所有学生及其班级信息，包含没有学生的班级信息

5.查询班级有学生早于91年出生的班级信息

首先要查出有91年之前出生的学生的班级

然后再进行嵌套，查询当前这些班级的信息

6.any子查询，>(符合大于any数组里面最小值的所有数据)，<any （小于any里面最大值的所有数据）

7.all子查询，>all 大于最大值，<all小于最小值

SELECT * FROM classes WHERE cid>ALL(SELECT DISTINCT cid FROM student WHERE birthday>'1991-1-1')

三、多表查询练习

1.统计学生表在个班级中的所占人数

SELECT c.cname,COUNT(s.sid) FROM student s,classes c WHERE s.cid=c.cid GROUP BY s.cid

2.查询所有学生及其对应的所有课程的平均成绩

用子查询：

查询学生的平均成绩

3.查询学生的姓名和学生的选课总数，显示选课超过两门的学生姓名

 4.查询平均成绩大于80分的总人数，子查询的结果必须取别名

SELECT COUNT(*) 平均成绩超过80的总人数 FROM(SELECT AVG(score) FROM  stu_cour  GROUP BY sid HAVING AVG(score)>80)t

5.查询平均成绩大于2班任何一个同学的平均成绩的信息（姓名和平均成绩）

先查询2班学生的平均成绩

SELECT s.sname,AVG(st.score),s.sid ,s.cid FROM student s,stu_cour st WHERE s.sid=st.sid GROUP BY s.sid HAVING s.cid=2

再查询所有学生的平均成绩

SELECT   s.sname  ,AVG(st.score),s.sid  FROM  student s,stu_cour st WHERE s.sid=st.sid GROUP BY s.sid

再用子查询，查复符合要求的数据

SELECT ss.sname,ss.avgscore FROM (SELECT s.sname,AVG(st.score) avgscore,s.sid FROM student s,stu_cour st WHERE s.sid=st.sid GROUP BY s.sid)ss 
WHERE ss.avgscore >ALL(SELECT av.score FROM(SELECT s.sname,AVG(st.score) score,s.sid ,s.cid FROM student s,stu_cour st WHERE s.sid=st.sid GROUP BY s.sid HAVING s.cid=2)av)