MySQL数据库基础练习一

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‘班的所有人数；

统计：count

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)

---

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和cno

mysql> 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分以上成绩的课程cno

select 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)