MySQL查询练习一
数据准备:
学生表student
学号:姓名:性别:出生日期:所在班级;create table student (
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(10)
);
教师表teacher
教师编号,教师名字,教师性别,出生日期,职称,所在部门。create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
课程表course
课程号,课程名称,教师编号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
学号,课程号,成绩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','王尼玛','男','1974-06-03','95031');
insert into student values('108','张全蛋','男','1974-06-03','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-105','92');
insert into score values('103','3-245','86');
insert into score values('103','6-166','85');
insert into score values('105','3-105','88');
insert into score values('105','3-245','75');
insert into score values('105','6-166','79');
insert into score values('109','3-105','76');
insert into score values('109','3-245','68');
insert into score values('109','6-166','81');
1、查询student表中的所有记录;mysql> 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 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
2、查询student表中的所有记录的sname,ssex,class列;mysql> select sname, ssex, class from student;
+-----------+------+-------+
| sname | ssex | class |
+-----------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 王尼玛 | 男 | 95031 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
+-----------+------+-------+
9 rows in set (0.00 sec)
3、查询教师所有的单位即不重复的depart列;
关键字distinct排除重复。mysql> select distinct depart from teacher;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
2 rows in set (0.01 sec)
4、查询score表中成绩在60到80之间的所有记录;
查询区间:
方式一:between...and...mysql> 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 |
+-----+-------+--------+
4 rows in set (0.00 sec)
方式二:直接使用运算符比较;mysql> select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)
5、查询score表中成绩为85,86或88的记录;
表示或着关系的查询;in关键字。mysql> select * from score where degree in(85,86,88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
3 rows in set (0.00 sec)
6、查询student表中“95031”班或者性别为女的记录;
关键字or,可以是不同的字段mysql> 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 |
| 107 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
7 rows in set (0.00 sec)
7、以class降序查询student表的所有记录;
排序的关键字:order by...
降序:desc
升序:asc默认可以不写mysql> select * from student order by class desc;
+-----+-----------+------+---------------------+-------+
| 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 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
8、以cno升序、degree降序查询score表中的所有记录;
以逗号隔开。mysql> 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 rows in set (0.00 sec)
9、查询’95031‘班的所有人数;
统计:countmysql> 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 | 王尼玛 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
10、查询score表中的最高分的学生学号和课程号;
子查询或者排序;
复合语句。mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
步骤:
①、找到最高分select max(degree) from score;
②、找最高分的sno和cnomysql> select sno,cno from score where degree=(select max(degree) from score);
③、排序的做法:
存在一定缺陷,比如成绩最高有多个分相同。mysql> select sno,cno,degree from score order by degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
9 rows in set (0.00 sec)
limit第一个数字表示从第几条开始,第二个数字表示查多少条。mysql> select sno,cno,degree from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
+-----+-------+--------+
1 row in set (0.00 sec)
11、查询每门课的平均成绩;
avg()
查询一门的平均成绩;mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
| 85.3333 |
+-------------+
1 row in set (0.00 sec)
一个语句中查询多门课程的平均成绩;
...group by...分组mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)
12、查询score表中至少有两名学生选修的并以3开头的课程的平均成绩
分组条件与模糊查询mysql> 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 |
+-------+-------------+----------+
2 rows in set (0.00 sec)
分组条件:group by...having...
模糊查询:like
13、查询分数大于70小于80的sno列
范围查询的两种方式
方式一:mysql> 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 |
+-----+--------+
7 rows in set (0.00 s
方式二:between...and...mysql> select sno,degree from score where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+
7 rows in set (0.00 sec)
14、查询所有学生的cno,sname和degree
多表查询mysql> 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 |
+-----------+-------+--------+
9 rows in set (0.00 sec)
15、查询所有学生的sno,cname和degree列mysql> select sno,cname,degree from course,score where course.cno=score.cno;
+-----+-----------------+--------+
| sno | cname | degree |
+-----+-----------------+--------+
| 103 | 计算机导论 | 92 |
| 103 | 操作系统 | 86 |
| 103 | 数字电路 | 85 |
| 105 | 计算机导论 | 88 |
| 105 | 操作系统 | 75 |
| 105 | 数字电路 | 79 |
| 109 | 计算机导论 | 76 |
| 109 | 操作系统 | 68 |
| 109 | 数字电路 | 81 |
+-----+-----------------+--------+
9 rows in set (0.05 sec)
16、所有学生的sname,cname,degree列
三表关联查询,通过共同字段的相等关系来联系在一起。mysql> 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班学生每门课的平均分
子查询加分组求平均分mysql> 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 |
+-------+-------------+
3 rows in set (0.08 sec)
18、查询选修3-105课程的成绩高于109号同学3-105成绩的所有同学的记录
子查询步骤:
① select degree from score where sno='109'and cno ='3-105';
② select * from score where cno = '3-105' and degree > (select degree from score where sno='109'and cno ='3-105');
mysql> 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 |
+-----+-------+--------+
2 rows in set (0.00 sec)
19查询成绩高于学号109、课程号为3-105的成绩的所有记录mysql> SELECT * from score
where degree > (SELECT degree FROM score where sno = '109' and cno = '3-105' );
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
6 rows in set (0.00 sec)
20、查询和学号为108、101的同学同年出生的所有学生的sno、sname、和sbirthday列
year()+in关键字的子查询mysql> SELECT sno,sname, sbirthday from student
-> where YEAR(sbirthday) in (SELECT year(sbirthday) from student where sno in( 101,108));
+-----+-----------+---------------------+
| sno | sname | sbirthday |
+-----+-----------+---------------------+
| 101 | 曾华 | 1977-09-01 00:00:00 |
| 106 | 陆君 | 1974-06-03 00:00:00 |
| 107 | 王尼玛 | 1974-06-03 00:00:00 |
| 108 | 张全蛋 | 1974-06-03 00:00:00 |
| 109 | 赵铁柱 | 1974-06-03 00:00:00 |
+-----+-----------+---------------------+
5 rows in set (0.00 sec)
21、查询张旭教师任课的学生成绩
多层嵌套子查询步骤:
① SELECT tno FROM teacher WHERE tname='张旭';
② SELECT cno from course where tno =(SELECT tno FROM teacher WHERE tname='张旭');
③ SELECT * FROM score
WHERE cno =(SELECT cno from course where tno =(SELECT tno FROM teacher WHERE tname='张旭'));
mysql> SELECT * FROM score WHERE cno =(SELECT cno from course where tno =(SELECT tno FROM teacher WHERE tname='张旭'));
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
3 rows in set (0.00 sec)
22、查询选修某课程的同学人数多于5人的教师姓名
多表查询+子查询步骤:
① SELECT cno from score GROUP BY cno HAVING count(*)>5;
② select tno from course where cno=(SELECT cno from score GROUP BY cno HAVING count(*)>5);
③ SELECT tname from teacher
WHERE tno = (select tno from course where cno=(SELECT cno from score GROUP BY cno HAVING count(*)>5));
mysql> SELECT tname from teacher WHERE tno = (select tno from course where cno=(SELECT cno from score GROUP BY cno HAVING count(*)>5));
+--------+
| tname |
+--------+
| 王萍 |
+--------+
1 row in set (0.00 sec)
23、查询95033班95031班全体学生的记录
in表示或者关系SELECT * from student WHERE class IN(95033,95031);
24、查询存在成绩有85分以上成绩的课程cnoselect cno from score where degree>85;
mysql> SELECT cno,degree FROM score WHERE degree>85;
+-------+--------+
| cno | degree |
+-------+--------+
| 3-105 | 90 |
| 3-105 | 91 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 88 |
| 3-105 | 88 |
+-------+--------+
6 rows in set (0.00 sec)