mysql查询操作教程

mysql查询

篇幅有些长，但我相信如果你能读完它，对mysql的查询操作你会有一定的了解

首先创建4个表(所有的符号都是在英文输入法下的)

学生表（student）

学号、姓名、性别、出生日期、所在班级

mysql> create table student(
    -> sno varchar(20) primary key,
    -> sname varchar(20) not null,
    -> ssex varchar(10) not null,
    -> sbirthday datetime,
    -> class varchar(20)
    -> );

教师表（teacher）

教师编号、教师名字、性别、出生日期、职称、部门

mysql> create table teacher(
    -> tno varchar(20) primary key,
    -> tname varchar() not null,
    -> tsex varchar(10) not null,
    -> tbirthday datetime,
    -> prof varchar(20) not null,
    -> dapart varchar(20) not null //应该是depart，打错了 后面才发现，懒得改了
    -> );

课程表（course）

课程号、课程名称、教师编号

mysql> create table course(
    -> cno varchar(20) primary key,
    -> cname varchar(20) not null,
    -> tno varchar(20) not null,
    -> foreign key(tno) references teacher(tno)
    -> );

成绩表（score）

学号、课程号、成绩

mysql> create table score(
    -> sno varchar(20) not null,
    -> cno varchar(20) not null,
    -> degree decimal,
    -> foreign key(sno) references student(sno),
    -> foreign key(cno) references course(cno),
    -> primary key(sno,cno)
    -> );

向表中添加数据

学生表数据

INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

教师表数据

INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

课程表数据

INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

成绩表数据

INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

接下来就是主题-------查询了

1.查询student表中所有的记录

SELECT * FROM student;

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

2.查询student表中所有记录的sname,ssex和class列

SELECT sname,ssex,class FROM student;

+--------+------+-------+
| sname  | ssex | class |
+--------+------+-------+
| 曾华   | 男   | 95033 |
| 匡明   | 男   | 95031 |
| 王丽   | 女   | 95033 |
| 李军   | 男   | 95033 |
| 王芳   | 女   | 95031 |
| 陆军   | 男   | 95031 |
| 王尼玛 | 男   | 95033 |
| 张全蛋 | 男   | 95031 |
| 赵铁柱 | 男   | 95031 |
+--------+------+-------+

3.查询教师所有的depart列

SELECT dapart FROM teacher; //这里是depart，但我创建表的时候打错了，懒得改了

+------------+
| dapart     |
+------------+
| 计算机系   |
| 计算机系   |
| 电子工程系 |
| 电子工程系 |
+------------+

若是筛检重复项，则输入：

SELECT distinct dapart FROM teacher;

+------------+
| dapart     |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+

4.查询score表中成绩在60-80之间所有的记录(degree)

select * from score where degree between 60 and 80;

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+

5.查询score表中成绩为85, 86, 或者88的记录(degree)

select * from score where degree in (85,86,88);

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
+-----+-------+--------+

6.查询student表中'95031'班或者性别为'女'的同学记录

select * from student where class = '95031' or ssex = '女';

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

7.以class降序查询student表中所有的记录

select * from student order by class desc; //升序的话把结尾改成 asc

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男   | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

8.以c_no升序.degree降序查询score表中所有的数据

select * from score order by cno asc,degree desc;

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
| 105 | 6-166 |     79 |
+-----+-------+--------+

9.查询'95031'班的学生人数

select count(*) from student where class='95031';

+----------+
| count(*) |
+----------+
|        5 |
+----------+

10.查询score表中的最高分数的学生号和课程号.

select sno,cno from score where degree=(select max(degree) from score); /////select max(degree) from score为查询最高分，接着通过最高分找到学号和课程号select sno,cno from score where degree=

+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+

11.查询每门课的平均成绩

我们先来看一下 3-105这门课的全部成绩

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

+--------+
| degree |
+--------+
|     92 |
|     88 |
|     76 |
+--------+

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

+-------+--------------+
| cno   | avg (degree) |
+-------+--------------+
| 3-105 |      85.3333 |
+-------+--------------+

如果我们要查看所有课程的平均成绩呢？

select cno,avg (degree) from score group by cno;

+-------+--------------+
| cno   | avg (degree) |
+-------+--------------+
| 3-105 |      85.3333 |
| 3-245 |      76.3333 |
| 6-166 |      81.6667 |
+-------+--------------+

12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

可以拆分来做：

1.select cno from score group by cno; 选出所有课程，

2.select cno from score group by cno having count(cno)>=2;选出至少有两名学生选修的

3.and cno like '3%';选出以3开头的

select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';

我们也可以把具体的人数加载出来：

select cno,avg(degree) ，count(*) from score group by cno having count(cno)>=2 and cno like '3%';

+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

13.查询分数大于70但是小于90的sno列

除了之前的between，也可以使用：

select sno,degree from score where degree>70 and degree<90;

+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     85 |
| 105 |     88 |
| 105 |     75 |
| 105 |     79 |
| 109 |     76 |
| 109 |     81 |
+-----+--------+

14.查询所有的学生 sname , cno, degree列(多表查询)

select sname,cno,degree from student,score where student.sno=score.sno;

+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 王丽   | 3-105 |     92 |
| 王丽   | 3-245 |     86 |
| 王丽   | 6-166 |     85 |
| 王芳   | 3-105 |     88 |
| 王芳   | 3-245 |     75 |
| 王芳   | 6-166 |     79 |
| 赵铁柱 | 3-105 |     76 |
| 赵铁柱 | 3-245 |     68 |
| 赵铁柱 | 6-166 |     81 |
+--------+-------+--------+

15.查询所有学生的sno, cname, degree列

SELECT sno, cname, degree FROM student, course,score WHERE student.sno = score.sno AND score.cno = course.cno ;

+------+------------+-----------+
| sno | cname     |    degree |
+------+------------+-----------+
| 103  | 计算机导论 |        92 |
| 103  | 操作系统   |        86 |
| 103  | 数字电路   |        85 |
| 105  | 计算机导论 |        88 |
| 105  | 操作系统   |        75 |
| 105  | 数字电路   |        79 |
| 109  | 计算机导论 |        76 |
| 109  | 操作系统   |        68 |
| 109  | 数字电路   |        81 |
+------+------------+-----------+

16.查询所有的学生 sname , cname, degree列

sname来自student,cname来自course，degree来自score，三项来自不同的表

select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

+--------+------------+--------+
| sname  | cname      | degree |
+--------+------------+--------+
| 王丽   | 计算机导论 |     92 |
| 王丽   | 操作系统   |     86 |
| 王丽   | 数字电路   |     85 |
| 王芳   | 计算机导论 |     88 |
| 王芳   | 操作系统   |     75 |
| 王芳   | 数字电路   |     79 |
| 赵铁柱 | 计算机导论 |     76 |
| 赵铁柱 | 操作系统   |     68 |
| 赵铁柱 | 数字电路   |     81 |
+--------+------------+--------+
9 rows in set (0.00 sec)

17.查询班级是'95031'班学生每门课的平均分

先找到95031班的所有学生

select * from student where class='95031';

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

select * from score where sno in (select sno from student where class='95031');

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

再算平均分

select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;

+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |

18.查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩的所有同学的记录

选出选修3-105课程且学号是109的成绩

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

+--------+
| degree |
+--------+
|     76 |
+--------+

再查询比109高的分

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

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+

19.查询所有学号为108.101的同学同年出生的所有学生的sno,sname和sbirthday

先查出同年出生的：

select * from student where sno in (101,108);

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

再把 101和108号的年份提取出来，用year()

select year(sbirthday) from student where sno in (101,108);

+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1975 |
+-----------------+

再把年份一样的其他人挑出来

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (101,108));

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

20.查询张旭教师任课的学生的成绩

首先找到张旭的tno

select tno from teacher where tname='张旭'；//856

再找到张旭上的课

select cno from course where course.tno=(select tno from teacher where tname='李旭')); //6-166

再根据课程代号，找出学生成绩

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

+--------+
| degree |
+--------+
|     85 |
|     79 |
|     81 |
+--------+

21.查询选修课程的同学人数多余 5 人的教师姓名

先看一下所有课程 select * from score;

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

可以看到我们的数据量不足，再插入点数据

INSERT INTO score VALUES('101','3-105','90');

INSERT INTO score VALUES('102','3-105','91');

INSERT INTO score VALUES('104','3-105','89');

再把人数大于5人的课选出来：

select cno from score group by cno having count(*)>5;

+-------+
| cno   |
+-------+
| 3-105 |
+-------+

再通过课程号再课程表里吧教师编号选出来

select tno from course where cno=(select cno from score group by cno having count(*)>5); //825

再通过教师编号选出教师姓名

select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5)); / /王萍

22.查询95033班和95031班全体学生的记录

因为数据里只有这两个班，所以我们要增加一些数据：

INSERT INTO student VALUES('110','张飞','男','1974-06-03','95038');

select * from student where class in('95031','95033');

23.查出所有'计算机系' 教师所教课程的成绩表

先查出计算机系的教师编号：select tno from teacher where dapart='计算机系';////804，825

tno知道了，找课程号 select cno from course where tno in (select tno from teacher where dapart='计算机系');////////因为tno不是唯一的，这里要用in

+-------+
| cno   |
+-------+
| 3-245 |
| 3-105 |
+-------+

通过cno来找成绩

select * from score where cno in (select cno from course where tno in (select tno from teacher where dapart='计算机系'));

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

24.查询'计算机系'与'电子工程系' 不同职称的教师的tname和prof

首先查看一下计算机系和电子工程系所有的老师编号：select tno from teacher where dapart in ('计算机系','电子工程系');

+-----+
| tno |
+-----+
| 804 |
| 825 |
| 831 |
| 856 |
+-----+

通过tno查看一下tname和prof: select * from teacher where tno in ( select tno from teacher where dapart in ('计算机系','电子工程系'));

+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday           | prof   | dapart     |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 825 | 王萍  | 女   | 1972-05-05 00:00:00 | 助教   | 计算机系   |
| 831 | 刘冰  | 女   | 1977-08-14 00:00:00 | 助教   | 电子工程系 |
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+

可以看到王萍和刘冰的prof重复了，我们不要他们俩，只保留李诚和张旭，李诚在计算机系，张旭在电子工程系

所以我们先把李诚挑出来：

select * from teacher where dapart='计算机系' and prof not in (select prof from teacher where dapart='电子工程系')；

+-----+-------+------+---------------------+--------+----------+
| tno | tname | tsex | tbirthday           | prof   | dapart   |
+-----+-------+------+---------------------+--------+----------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
+-----+-------+------+---------------------+--------+----------+

select * from teacher where dapart='电子工程系' and prof not in (select prof from teacher where dapart='计算机系');

+-----+-------+------+---------------------+------+------------+
| tno | tname | tsex | tbirthday           | prof | dapart     |
+-----+-------+------+---------------------+------+------------+
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+-------+------+---------------------+------+------------+

我们只需要把他们连在一起：这里用到 union

select * from teacher where dapart='计算机系' and prof not in (select prof from teacher where dapart='电子工程系') union select * from teacher where dapart='电子工程系' and prof not in (select prof from teacher where dapart='计算机系');

+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday           | prof   | dapart     |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+

25.查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的cno,sno和degree,并且按照degree从高到低排序

先把3-245 选出来：select * from score where cno='3-245'

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
+-----+-------+--------+

再把3-105选出来：select * from score where cno='3-105'；

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

至少代表 105里有高于245里的任一一个，用到any

select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245');

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

从高到低排序：在末尾加上 order by degree desc;

select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245') order by degree desc;

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 102 | 3-105 |     91 |
| 101 | 3-105 |     90 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

26.查询选修编号为"3-105"课程且成绩高于选修编号为'3-245'同学的cno,sno和degree,并且按照degree从高到低排序

同25 只是把 any 改成all 就可以了

27.查询所有教师和同学的 name ,sex, birthday

select tname,tsex,tbirthday from teacher;//显示的是老师的
select sname,ssex,sbirthday from student; ////显示的是学生的

我们之前讲过了连接两个用的union函数

select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student；

+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 李诚   | 男   | 1958-12-02 00:00:00 |
| 王萍   | 女   | 1972-05-05 00:00:00 |
| 刘冰   | 女   | 1977-08-14 00:00:00 |
| 张旭   | 男   | 1969-03-12 00:00:00 |
| 曾华   | 男   | 1977-09-01 00:00:00 |
| 匡明   | 男   | 1975-10-02 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 李军   | 男   | 1976-02-20 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
| 陆军   | 男   | 1974-06-03 00:00:00 |
| 王尼玛 | 男   | 1976-02-20 00:00:00 |
| 张全蛋 | 男   | 1975-02-10 00:00:00 |
| 赵铁柱 | 男   | 1974-06-03 00:00:00 |
| 张飞   | 男   | 1974-06-03 00:00:00 |
+--------+------+---------------------+

但是问题出来了，虽然信息排到了一起，但是都变成了tname，tsex等，所以我们要把tname、tsex、tbirthday换一个名字

select tname as name,tsex as sex,tbirthday as birthday from teacher union select sname,ssex,sbirthday from student;

+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 李诚   | 男  | 1958-12-02 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 刘冰   | 女  | 1977-08-14 00:00:00 |
| 张旭   | 男  | 1969-03-12 00:00:00 |
| 曾华   | 男  | 1977-09-01 00:00:00 |
| 匡明   | 男  | 1975-10-02 00:00:00 |
| 王丽   | 女  | 1976-01-23 00:00:00 |
| 李军   | 男  | 1976-02-20 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 陆军   | 男  | 1974-06-03 00:00:00 |
| 王尼玛 | 男  | 1976-02-20 00:00:00 |
| 张全蛋 | 男  | 1975-02-10 00:00:00 |
| 赵铁柱 | 男  | 1974-06-03 00:00:00 |
| 张飞   | 男  | 1974-06-03 00:00:00 |
+--------+-----+---------------------+

28.查询所有'女'教师和'女'学生的name,sex,birthday

很简单后面加一个where 条件

select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex='女'union select sname,ssex,sbirthday from student where ssex='女';

+------+-----+---------------------+
| name | sex | birthday            |
+------+-----+---------------------+
| 王萍 | 女  | 1972-05-05 00:00:00 |
| 刘冰 | 女  | 1977-08-14 00:00:00 |
| 王丽 | 女  | 1976-01-23 00:00:00 |
| 王芳 | 女  | 1975-02-10 00:00:00 |
+------+-----+---------------------+

29.查询成绩比该课程平均成绩低的同学的成绩表。

select cno,avg(degree) from score group by cno;

+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

select * from score;

+-----+-------+--------+                   
| sno | cno   | degree |              
+-----+-------+--------+              
| 101 | 3-105 |     90 |              
| 102 | 3-105 |     91 |              
| 103 | 3-105 |     92 |              
| 103 | 3-245 |     86 |              
| 103 | 6-166 |     85 |              // a表
| 104 | 3-105 |     89 |              
| 105 | 3-105 |     88 |              
| 105 | 3-245 |     75 |              
| 105 | 6-166 |     79 |              
| 109 | 3-105 |     76 |              
| 109 | 3-245 |     68 |              
| 109 | 6-166 |     81 |              
+-----+-------+--------+

再复制粘贴一遍这个表

+-----+-------+--------+                   
| sno | cno   | degree |              
+-----+-------+--------+              
| 101 | 3-105 |     90 |              
| 102 | 3-105 |     91 |              
| 103 | 3-105 |     92 |              
| 103 | 3-245 |     86 |              
| 103 | 6-166 |     85 |              //b表
| 104 | 3-105 |     89 |              
| 105 | 3-105 |     88 |              
| 105 | 3-245 |     75 |              
| 105 | 6-166 |     79 |              
| 109 | 3-105 |     76 |              
| 109 | 3-245 |     68 |              
| 109 | 6-166 |     81 |              
+-----+-------+--------+

select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
5 rows in set (0.00 sec)

30.查询所有任课教师的Tname和dapart

在课程表有课程才能算课程

select * from course;

+-------+------------+-----+
| cno   | cname      | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统   | 804 |
| 6-166 | 数字电路   | 856 |
| 9-888 | 高等数学   | 831 |
+-------+------------+-----+

select * from teacher where tno in (select tno from course);

+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday           | prof   | dapart     |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 825 | 王萍  | 女   | 1972-05-05 00:00:00 | 助教   | 计算机系   |
| 831 | 刘冰  | 女   | 1977-08-14 00:00:00 | 助教   | 电子工程系 |
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+

31.查询至少有两名男生的班号

select class from student where ssex='男' group by class having count(*)>1;

+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

32.查询student 表中不姓"王"的同学的记录+

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

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞   | 男   | 1974-06-03 00:00:00 | 95038 |
+-----+--------+------+---------------------+-------+

33.查询student 中每个学生的姓名和年龄

年龄=当前年份-出生年份

查看当前年份：select year(now());

+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+

出生年份：select year(sbirthday) from student;

+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1975 |
|            1976 |
|            1976 |
|            1975 |
|            1974 |
|            1976 |
|            1975 |
|            1974 |
|            1974 |
+-----------------+

select sname,year(now())-year(sbirthday) as '年龄' from student;

+--------+------+
| sname  | 年龄 |
+--------+------+
| 曾华   |   43 |
| 匡明   |   45 |
| 王丽   |   44 |
| 李军   |   44 |
| 王芳   |   45 |
| 陆军   |   46 |
| 王尼玛 |   44 |
| 张全蛋 |   45 |
| 赵铁柱 |   46 |
| 张飞   |   46 |
+--------+------+

34.查询student中最大和最小的 sbirthday的值

用到max、min函数

select max(sbirthday) as '最大'，min(sbirthday) as '最小' from student;

+---------------------+---------------------+
| 最大                | 最小                |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+

35.以班级号和年龄从大到小的顺序查询student表中的全部记录

select * from student order by class desc,sbirthday;

+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 110 | 张飞   | 男   | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男   | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

36.查询"男"教师及其所上的课

先找到男教师：select * from teacher where tsex='男'；

再通过tno找到课程：select * from course where tno in (select tno from teacher where tsex='男');

+-------+----------+-----+
| cno   | cname    | tno |
+-------+----------+-----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
+-------+----------+-----+

37.查询最高分同学的sno 、cno 和 degree;

先查最高分：select max(degree) from score;

select * from score where degree=(select max(degree) from score);

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+

38.查询和"李军"同性别的所有同学的sname

先查李军性别 select ssex from student where sname='李军'；//男

select sname ,ssex from student where ssex =（ select ssex from student where sname='李军'）；

+--------+------+
| sname  | ssex |
+--------+------+
| 曾华   | 男   |
| 匡明   | 男   |
| 李军   | 男   |
| 陆军   | 男   |
| 王尼玛 | 男   |
| 张全蛋 | 男   |
| 赵铁柱 | 男   |
| 张飞   | 男   |
+--------+------+

39.查询所有选修'计算机导论'课程的'男'同学的成绩表

select cno from course where cname='计算机导论'；//3-105

select * from student where ssex='男'；

select * from score where cno=(select cno from course where cname='计算机导论') and sno in (select sno from student where ssex='男);

+--------+------------+-----------+
| sname | cname     | degree |
+--------+------------+-----------+
| 曾华   | 计算机导论 |        90 |
| 匡明   | 计算机导论 |        91 |
| 李军   | 计算机导论 |        89 |
| 赵铁柱 | 计算机导论 |        76 |
+--------+------------+-----------+

假设使用了以下命令建立了一个grade表

CREATE TABLE grade(

low INT(3),

upp INT(3),

grade CHAR(1)

);

INSERT INTO grade VALUES(90,100,'A');

INSERT INTO grade VALUES(80,89,'B');

INSERT INTO grade VALUES(70,79,'c');

INSERT INTO grade VALUES(60,69,'D');

INSERT INTO grade VALUES(0,59,'E');

查询所有同学的sno , cno 和grade列

select sno, cno,grade from score ,grade where degree between low and upp;

+-----+-------+-------+
| sno | cno   | grade |
+-----+-------+-------+
| 101 | 3-105 | A     |
| 102 | 3-105 | A     |
| 103 | 3-105 | A     |
| 103 | 3-245 | B     |
| 103 | 6-166 | B     |
| 104 | 3-105 | B     |
| 105 | 3-105 | B     |
| 105 | 3-245 | c     |
| 105 | 6-166 | c     |
| 109 | 3-105 | c     |
| 109 | 3-245 | D     |
| 109 | 6-166 | B     |
+-----+-------+-------+

学习自编程158俱乐部b站教学视频