mysql练习-数据查询之连接查询

连接查询

若在一个查询请求中，涉及到多个表中的数据，则需要进行连接查询。 按连接方式分： 内连接 （默认连接类型）INNER JOIN、外连接、自连接、交叉连接

• 内连接

1.等值连接—连接运算符为“=”
例：查询每个学生的基本信息及其选课情况。

SELECT * FROM student INNER JOIN sc
ON stuent.sno=sc.sno;
或：
SELECT * FROM student, sc
WHERE student.sno=sc.sno;

2.自然连接–将等值连接中重复的属性列去掉
例：查询每个学生的基本信息及其选课情况。

SELECT student.sno, sname, ssex, sage, sdept, cno, grade
FROM student INNER JOIN sc
ON student.sno=sc.sno;

3.自连接：一个表进行自连接
需要给表起别名以示区别；由于所有属性名都是同名属性，因此必须使用别名前缀。
例：查询各门课程的先修课情况

SELECT FIRST.cno,FIRST.cname,
			SECOND.cname AS '先修课'
FROM course FIRST,course SECOND
WHERE FIRST.cpno=SECOND.cno;

对于多表连接，若连接方式为内连接，则多采用将连接条件写在where子句中的形式，更加简洁。
如：
SELECT sname,cname,grade
FROM student，sc，course 
WHERE student.sno=sc.sno AND
sc.cno=course.cno AND 
sdept=‘计算机系’

对于多表连接，若多个表中均有同名列，则必须在列名指定输出列来自与哪个表。
如：
SELECT student.sno,sname,
		   cname,grade
FROM student,sc,course 
WHERE student.sno=sc.sno AND
sc.cno=course.cno AND 
sdept=‘计算机系’

• 外连接

外连接与内连接的区别：
内连接操作只输出满足连接条件的元组。
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

1.左外连接：LEFT [OUTER] JOIN

例：查询学生的选课情况，要求输出学号，姓名，课程代号，成绩。(包括学生未选课的信息)
SELECT student.sno,sname,cno,grade
FROM student LEFT JOIN sc
ON student.sno=sc.sno 
ORDER BY student.sno;

2.右外连接：RIGHT [OUTER] JOIN

例： 查询教师的工资情况
SELECT gc.*,js.xm
FROM gz RIGHT  JOIN js
ON gz.gh=js.gh
ORDER BY xm;

3.完全连接：FULL [OUTER] JOIN

例： 查询教师的工资情况
SELECT gz.*, js.*
FROM gz  FULL JOIN js
ON gz.gh=js.gh;
#完全连接返回两表中的所有行，其中匹配记录仅显示一次；非匹配行的数据不可用的列显示NULL值。

• 交叉连接 cross join

一个表中的每行与另一个表中的每行连接； 结果集中行的数量是第一个表中行的数量与第二个表中行的数量的乘积。

例：
SELECT titles.*,publishers.*      
FROM titles CROSS JOIN publishers;

下面开始做题。

• 1
  SQL查询：统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分，按课程号排序。
  学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：
  1、student（学生表）：
  SNO学号CHAR(7)
  SNAME姓名CHAR(10)
  SSEX性别CHAR(2)
  SAGE年龄SMALLINT
  SDEPT所在系 VARCHAR(20)
  2、course（课程表）
  CNO课程号CHAR(10)
  CNAME课程名VARCHAR(20)
  CCREDIT学分SMALLINT
  SEMSTER学期SMALLINT
  PERIOD学时SMALLINT
  3、sc（选课表）
  SNO 学号CHAR(7)
  CNO 课程号CHAR(10)
  GRADE 成绩 SMALLINT

SELECT course.cno,cname,COUNT(grade),MAX(grade),Min(grade),ROUND(AVG(grade))
FROM course,sc WHERE course.cno=sc.cno
group by course.cno
ORDER BY course.cno;

• 2
  查询学生有效成绩信息
  有课程表，学生表，成绩表如下，请完成查询，查询学生成绩单，要求输出有有效成绩的学号，姓名，课程名，成绩，按学号升序课程名降序排列。
  

select student.sno,sname,cname,grade
from student,sc,course
where grade is not null and student.sno=sc.sno and course.cno=sc.cno
order by sno asc,cname desc;

• 3
  查询选修“高等数学”课程的成绩不为空的学生学号、姓名和成绩。
  有课程表，学生表，成绩表如下，请完成查询。
  表结构如第二题。

select student.sno,sname,grade
from student,sc,course
where (grade is not null) and cname='高等数学'
and student.sno=sc.sno and course.cno=sc.cno
;

• 4
  输出学生成绩单，要求 输出学号，姓名，平均分，选修门数，按平均分降序排序。
  表结构如第二题。

select student.sno,sname,avg(grade),count(*)
from student,sc
where student.sno=sc.sno
group by student.sno
order by avg(grade) desc;

• 5
  SQL查询：查询输出平均成绩在2-5名的学生，输出学号、姓名和平均成绩（取整），平均成绩降序。【不用考虑空值】
  表结构如第一题。

SELECT sc.sno,sname,ROUND(AVG(grade))
FROM sc,student,course
WHERE student.sno=sc.sno and course.cno=sc.cno
group by sc.sno
ORDER BY avg(grade)desc
limit 1,4;

• 6
  查询姓名中含有“海”的学生的选课信息，要求输出学号、姓名、课程名、成绩。
  

select student.sno,sname,course.cname,grade from student,sc,course
where sname like '%海%' and student.sno=sc.sno and course.cno=sc.cno;

• 7
  SQL查询：查询每个学生高于他自己选修平均分的那门课程的成绩，输出学号，课程号，课程成绩，他所有课程的平均分，并按学号升序排列
  表结构如第一题。

select sc.sno,cno,grade,avggrade
from sc,
(select sno,round(AVG(grade))as avggrade
from sc
group by sno)AS AVG_sc
where sc.sno=AVG_sc.sno and sc.grade>AVG_sc.avggrade
order by sc.sno;

• 8
  查询学生成绩单，要求输出学号，姓名，平均分，选修门数，按平均分降序排序。
  
  
  

select student.sno,sname,avg(grade),count(*)
from student,sc
where student.sno=sc.sno 
group by student.sno
order by avg(grade) desc;

• 9
  查询平均成绩大于70分的学生的学号、姓名、平均成绩，按平均成绩降序排序。
  表结构如第二题。

select student.sno,sname,avg(grade)
from student,sc
where student.sno=sc.sno 
group by sc.sno
having avg(grade)>70
order by avg(grade)desc;

• 10
  查询每门课的先修课，输出课程号、课程名和先修课程名。
  表结构如第二题。

select a.cno,a.cname,b.cname
from course a,course b
where a.cpno=b.cno;

• 11
  输出网络工程专业学生的学号，姓名和籍贯，并按学号降序输出。
  #查询输出网络工程专业学生的学号，姓名和籍贯，并按学号降序输出。
  表结构如下：
  1、zy（专业表）：
  zydh 专业代号 CHAR(4)
  zymc 专业名称 VARCHAR(20)
  xz 学制 SMALLINT
  2、student（学生表）：
  sno 学号 CHAR(9)
  sname 姓名 CHAR(10)
  ssex 性别 CHAR(2)
  sage 年龄 SMALLINT
  jg 籍贯 VARCHAR(50)
  zydh 专业代号 CHAR(4)

select sno,sname,jg
from student,zy
where zy.zydh=student.zydh and zymc='网络工程'
order by sno desc;

• 12
  查询95级学生(学号前两位)的选课情况，输出学号、姓名、课程号、成绩（包括未选课的学生信息）。
  表结构如第二题。

select s.sno,s.sname,cno,grade 
from student s 
left join sc on (s.sno=sc.sno)
where  s.sno like '95%';

• 13
  统计各专业的学生人数，要求输出系名，专业名，总人数，按系名排序。
  表结构如下：
  1、student（学生表）：
  sno 学号 CHAR(9)
  sname 姓名 CHAR(10)
  ssex 性别 CHAR(2)
  sage 年龄 SMALLINT
  jg 籍贯 VARCHAR(50)
  zydh 专业代号 CHAR(4)
  xdh 系代号 CHAR(2)
  2、zy（专业表）
  zydh 专业代号 CHAR(4)
  zymc 专业名称 VARCHAR(20)
  xz 学制 SMALLINT
  3、xb（系表）
  xdh 系代号 CHAR(2)
  xmc 系名称 VARCHAR(20)

select xmc,zymc,count(*)
from student,zy,xb
where student.zydh=zy.zydh and student.xdh=xb.xdh
group by zy.zydh
order by xmc;

• 14
  查询计算机系平均成绩高于70分的成绩信息。
  有课程表，学生表，成绩表如下，查询计算机系平均成绩高于70分的学号，姓名、平均成绩，并按平均成绩降序排列。
  表结构如第二题。

select student.sno,sname,avg(grade) 
from student,sc
where sdept='计算机系' and student.sno=sc.sno 
group by student.sno
having avg(grade)>70
order by avg(grade) desc ;

• 15
  SQL查询：查询选修了课程的学生的学号和姓名，按学号排序。
  表结构如第一题。

select sno,sname 
from student
where sno in( select sc.sno from sc
             group by sc.sno
             having count(*) is not null)
order by sno;

• 16
  统计各门课程的重修人数（包括grade为NULL），要求输出课程代号，课程名及重修人数。
  表结构如第六题。

select course.cno,cname,count(*) 
from sc,course 
where (grade<60 or grade is null) 
and course.cno=sc.cno
group by cno;

• 17
  查询选修未通过2门（包括2门）以上的学生的信息，输出学号、姓名、选修未通过门数，按门数降序排序，若门数相同，按学号升序排序。
  表结构如第六题。

SELECT student.sno,sname,COUNT(*)
FROM sc,student
WHERE grade<60 and student.sno=sc.sno
GROUP BY sno
HAVING COUNT(*)>=2
ORDER BY COUNT(*) DESC, sno asc;

• 18
  查询计算机系平均成绩前三名的学号，姓名、平均成绩.
  表结构如第二题。

select student.sno,sname,avg(grade)
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
and sdept='计算机系'
group by student.sno
order by avg(grade) desc limit 3;

• 19
  查询输出“高等数学”课程成绩前三名（不考虑成绩有重复值的情况）的学生的学号，姓名，课程名，系名，成绩。
  表结构如第六题。

select student.sno,sname,cname,sdept,grade from student,sc,course
where cname='高等数学' and student.sno=sc.sno and course.cno=sc.cno
order by grade desc limit 3;

• 20
  查询计算机系学生的成绩，输出学号，姓名，课程名，成绩，按学号排序（升序）。
  表结构如第一题。

select student.sno,sname,course.cname,grade from student,sc,course
where sdept='计算机系' and student.sno=sc.sno and course.cno=sc.cno
order by sno;