mysql连表条件查询_mysql带条件查询，联表查询

---恢复内容开始---

1,用于设定所select出来的数据是否允许出现重复行(完全相同的数据行)

all：允许出现——默认不写就是All(允许的)。

distinct：不允许出现——就是所谓的“消除重复行”

2，where:条件

3，group by：分组依据 后面加表的字段名，通常只进行一个字段的分组

mysql表查询语法形式：select [all | distinct] 字段名或表达式 from 表名 [where] [group by] [having] [order by] [limit];

练习题:共有下面四张表    学生表：student  教师表：teacher  课程表：course 成绩表：score

    

 

1，查询Score表中至少有5名学生选修的并以3开头的课程的平均分数

--操作表score，以cno分组并且cno是以3开头，取出各个cno的总数，及与cno对应的degree的平均值

select count(cno),avg(degree) from score where cnolike '3%' group by cno;

--从上面的虚拟表中找到cno总数大于5的

select * from

(select count(cno) a,avg(degree) b from score where cno like '3%' group by cno) c

where a>5;

2,查询所有学生的Sno、Cname和Degree列

--找到student的sno

select sno from student;

--找到score 的sno,degree,cno

select sno,degree,cno from score;

--找到course的cno

select cno,cname from course;

--组成新表cno sno degree

select a.cno,b.sno,b.sname,a.degree from (select sno,degree,cno from score) a  join (select sno,sname from student) b on a.sno=b.sno;

--组成有cname cno sn degree sname的表

select d.cname,e.cno,e.sno,e.degree,e.sname

from

(select a.cno,b.sno,b.sname,a.degree from (select sno,degree,cno from score) a  join (select sno,sname from student) b on a.sno=b.sno) as e

join

(select cname,cno from course) as d

on d.cno=e.cno;

3，查询“95033”班学生的平均分

--从student取sno class,条件是class是95033的

select sno,class from student where class='95033';

--取出score中sno degree

select sno,degree from score;

--将上面两张表组成一张,取degree的平均值

select avg(degree) from

(select sno,class from student where class='95033') a

join

(select sno,degree from score) b

on a.sno=b.sno;

4,查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

--将109号的成绩取出

select degree from score where sno='109' and cno='3-105';

-- 得出最终表

select * from score

where

degree>(select degree from score where sno='109' and cno='3-105')

and cno='3-105';

5,查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列

--找到student中sno为108 的Sbirthday

select year(sbirthday) from student where sno='108';

-- 得出最终表

select sno,sname,sbirthday from student

where

year(sbirthday)=(select year(sbirthday) from student where sno='108');

6.查询“张旭“教师任课的学生成绩(姓名)

--找到teacher中tname为张旭的tno

select tno from teacher where tname='张旭';

--找到course中tno和teacher中tname为张旭的tno相同的cno

select cno from course where tno=(select tno from teacher where tname='张旭');

--找到score中cno为6-166的sno cno degree

select sno,cno,degree from score

where

cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));

--找到student表中与上表sno相同的sname与上表组成新表

select a.sname,b.sno,b.cno,b.degree from

(select sno,sname from student) a

join

(select sno sno,cno,degree from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'))) b

on a.sno=b.sno;

7,查询考计算机导论的学生成绩

--找到course中cname为计算机导论的cno

select cno from course where cname='计算机导论';

--找到score中cno与上表中相同时的sno degree

select sno,degree from score where cno=(select cno from course where cname='计算机导论');

-- 得出最终表

select b.sname,a.sno,a.degree from

(select sno,degree from score where

cno=(select cno from course where cname='计算机导论')) a join (select sname,sno from student) b

on a.sno=b.sno;

8,查询李诚老师教的课程名称

select tno from teacher where tname='李诚';

select cname,cno from course where tno=(select tno from teacher  where tname='李诚');

9,查询选修某课程的同学人数多于5人的教师姓名

--score以cno分组，统计cno的数量

select count(cno) a,cno from score group by cno;

--找到上表中cno数量大于5的的cno

select cno from (select count(cno) a,cno from score group by cno) b where a>5;

--找到与上表cno一致的表course表中的tno

select tno from course where cno=(select cno from (select count(cno) a,cno from score group by cno) b where a>5);

--与上表tno一致的表teacher中的tname

select tname from teacher where tno=(select tno from course where cno=(select cno from (select count(cno) a,cno from score group by cno) b where a>5));

10,查询95033班和95031班全体学生的记录

--找到表score中的sno和cno 和degree

select sno,cno,degree from score;

--将上表和student组合

select a.sno,a.sname,a.ssex,a.sbirthday,a.class,b.cno,b.degree from

(select * from student) a join (select cno,degree,sno from score) b

on a.sno=b.sno;

11,查询存在有85分以上成绩的课程Cno

select cno,degree from score where degree>85;

12,查询出“计算机系“教师所教课程的成绩表

--找到teacher中的tno tname

select tno,tname from teacher where dapart='计算机系';

--找到course中tno和上表相同的cno

select a.cno,b.tno,b.tname from (select tno,cno from course) a join (select tno,tname from teacher where dapart='计算机系') b on a.tno=b.tno;

--找到score中cno与上表相同的degree

select c.sno,c.cno,c.degree,d.tname from (select sno,cno,degree from score) c join (select a.cno,b.tno,b.tname from (select tno,cno from course) a join (select tno,tname from teacher where dapart='计算机系') b on a.tno=b.tno) d on c.cno=d.cno;

13,查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学    的Cno、Sno和Degree,并按Degree从高到低次序排序

--score中找到cno=3-245的degree,取出最小值

select min(degree) from score where cno='3-245';

--score中找到cno=3-105的cno sno degree，并且degree大于上表的degree

select cno,sno,degree from

score where cno='3-105'

and degree>(select min(degree) from score where cno='3-245')

order by degree desc;

14,查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的    Cno、Sno和Degree.

--找到score中cno=3-245中的degree的最大值

select max(degree) from score where cno='3-245';

--找到score中cno=3-105的cno sno degree

select cno,sno,degree from

score where

degree>(select max(degree) from score where cno='3-245') order by degree asc;

15,查询所有教师和同学的name、sex和birthday

select tname,tsex,tbirthday from teacher

union

select sname,ssex,sbirthday from student;

16,查询所有“女”教师和“女”同学的name、sex和birthday

select tname,tsex,tbirthday from teacher where tsex='女'

union

select sname,ssex,sbirthday from student where ssex='女';

17,查询成绩比该课程平均成绩低的同学的成绩表

select avg(degree) from score where cno='3-105';

select avg(degree) from score where cno='3-245';

select avg(degree) from score where cno='6-166';

select * from score where cno='3-105' and degree

union

select * from score where cno='3-245' and degree

union

select * from score where cno='6-166' and degree

18,查询所有任课教师的Tname和Depart

select a.tname,a.dapart from

(select tno,tname,dapart from teacher) a join

(select tno from course) b

on a.tno=b.tno;

19,查询所有未讲课的教师的Tname和Depart

select a.tname,a.dapart from

(select tno,tname,dapart from teacher) a join

(select tno from course) b

on a.tno=b.tno;

20,查询至少有2名男生的班号

select count(ssex),class from student where ssex='男' group by class;

select b.class from (select count(ssex) a,class from student where ssex='男' group by class) b where a>=2;

21,查询Student表中不姓“王”的同学记录

select * from student where sname not like '王%';

22,查询Student表中每个学生的姓名和年龄

select  sname,year(now())-year(sbirthday) from student;

23,查询Student表中最大和最小的Sbirthday日期值

select min(day(sbirthday)),max(day(sbirthday)) from student;

24,以班号和年龄从大到小的顺序查询Student表中的全部记录

select * from student order by class desc,date(sbirthday) asc;

25,查询“男”教师及其所上的课程

select tno from teacher where tsex='男';

select a.tno,b.cname,a.tname from (select tno,tname from teacher where tsex='男') a join (select * from course) b on a.tno=b.tno;