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 |
+-----+-------+-------+