sql语句练习题

.sql文件和题目明天上传。

[参考阅读]

练习题

原作者使用的是Mysql5.0 我使用的版本是Mysql5.7 修改了部分不再能运行的答案 并把sql语句返回结果也摘录下来了。

表结构

也可以选择直接运行navicat导出的.sql文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华'
,'男' ,1977-09-01,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明'
,'男' ,1975-10-02,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽'
,'女' ,1976-01-23,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军'
,'男' ,1976-02-20,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳'
,'女' ,1975-02-10,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君'
,'男' ,1974-06-03,95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

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

题目

  1. 查询Student表中的所有记录的Sname、Ssex和Class列。
  2. 查询教师所有的单位即不重复的Depart列。
  3. 查询Student表的所有记录。
  4. 查询Score表中成绩在60到80之间的所有记录。
  5. 查询Score表中成绩为85,86或88的记录。
  6. 查询Student表中“95031”班或性别为“女”的同学记录。
  7. 以Class降序查询Student表的所有记录。
  8. 以Cno升序、Degree降序查询Score表的所有记录。
  9. 查询“95031”班的学生人数。
  10. 查询Score表中的最高分的学生学号和课程号。
  11. 查询‘3-105’号课程的平均分。
  12. 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
  13. 查询最低分大于70,最高分小于90的Sno列。
  14. 查询所有学生的Sname、Cno和Degree列。
  15. 查询所有学生的Sno、Cname和Degree列。
  16. 查询所有学生的Sname、Cname和Degree列。
  17. 查询“95033”班所选课程的平均分。
  18. 假设使用如下命令建立了一个grade表,现查询所有同学的Sno、Cno和rank列。
1
2
3
4
5
6
create table grade(low   number(3,0),upp   number(3),rank   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’);
  1. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
  2. 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
  3. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
  4. 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
  5. 查询“张旭“教师任课的学生成绩。
  6. 查询选修某课程的同学人数多于5人的教师姓名。
  7. 查询95033班和95031班全体学生的记录。
  8. 查询存在有85分以上成绩的课程Cno.
  9. 查询出“计算机系“教师所教课程的成绩表。
  10. 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
  11. 查询选修编号为“3-105“课程且成绩至少高于一门选修编号为“3-245”课程成绩的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
  12. 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
  13. 查询所有教师和同学的name、sex和birthday.
  14. 查询所有“女”教师和“女”同学的name、sex和birthday.
    []33、查询成绩比该课程平均成绩低的同学的成绩表。
    []34、查询所有任课教师的Tname和Depart.
    []35 查询所有未讲课的教师的Tname和Depart.
    []36、查询至少有2名男生的班号。
    []37、查询Student表中不姓“王”的同学记录。
    []38、查询Student表中每个学生的姓名和年龄。
    []39、查询Student表中最大和最小的Sbirthday日期值。
    []40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
    []41、查询“男”教师及其所上的课程。
    []42、查询最高分同学的Sno、Cno和Degree列。
    []43、查询和“李军”同性别的所有同学的Sname.
    []44、查询和“李军”同性别并同班的同学Sname.
    []45、查询所有选修“计算机导论”课程的“男”同学的成绩表

sql_practice遇到的问题

mysql启动

1
2
3
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
mysql -u root -p

编码问题

1
Incorrect string value: '\xE6\x9B\xBE\xE5\x8D\x8E'

解决方法:

1
alter table student convert to character set utf8

datetime时间格式问题

1
ERROR 1292 (22007): Incorrect datetime value: '1987' for column 'sbirthday' at row 1

解决方法:

1
2
3
mysql> insert into student (sno,sname,ssex,sbirthday,class) values (108,'曾华','男',1997-09-01,95033);
替换为
mysql> insert into student (sno,sname,ssex,sbirthday,class) values (108,'曾华','男',19970901,95033);

修改表的列

1
alter table teacher modify tbirthday date not null;

参考答案

1

1
mysql> select sname, ssex, class from student;

2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select * from teacher;
+-----+--------+------+------------+-----------+-----------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 |
+-----+--------+------+------------+-----------+-----------------+
4 rows in set (0.00 sec)

mysql> select depart from teacher;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
| 计算机系 |
| 电子工程系 |
+-----------------+
4 rows in set (0.00 sec)

mysql> select distinct depart from teacher;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
2 rows in set (0.01 sec)

4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select degree where between 60 and 80 from score;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where between 60 and 80 from score' at line 1
mysql> select * from score
-> where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75.0 |
| 109 | 3-245 | 68.0 |
| 109 | 3-105 | 76.0 |
| 101 | 3-105 | 64.0 |
| 108 | 3-105 | 78.0 |
| 107 | 6-106 | 79.0 |
+-----+-------+--------+
6 rows in set (0.01 sec)

5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from score
-> where degree = 85 or degree=86 or degree=88;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86.0 |
| 105 | 3-105 | 88.0 |
| 101 | 6-166 | 85.0 |
+-----+-------+--------+
3 rows in set (0.01 sec)

mysql> select * from score where degree in (85,86,88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86.0 |
| 105 | 3-105 | 88.0 |
| 101 | 6-166 | 85.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

6

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from student
-> where class=95031 or ssex='女';
+-----+--------+------+------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+------------+-------+
| 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 |
+-----+--------+------+------------+-------+
4 rows in set (0.00 sec)

7

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from student order by class desc;
+-----+--------+------+------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+------------+-------+
| 108 | 曾华 | 男 | 1997-09-01 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 |
+-----+--------+------+------------+-------+
6 rows in set (0.00 sec)

8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql> select * from score
-> order by cno and degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86.0 |
| 105 | 3-245 | 75.0 |
| 109 | 3-245 | 68.0 |
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 109 | 3-105 | 76.0 |
| 101 | 3-105 | 64.0 |
| 107 | 3-105 | 91.0 |
| 108 | 3-105 | 78.0 |
| 101 | 6-166 | 85.0 |
| 107 | 6-106 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
12 rows in set, 12 warnings (0.00 sec)

mysql> select * from score order by cno asc, degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
| 107 | 3-105 | 91.0 |
| 105 | 3-105 | 88.0 |
| 108 | 3-105 | 78.0 |
| 109 | 3-105 | 76.0 |
| 101 | 3-105 | 64.0 |
| 103 | 3-245 | 86.0 |
| 105 | 3-245 | 75.0 |
| 109 | 3-245 | 68.0 |
| 107 | 6-106 | 79.0 |
| 101 | 6-166 | 85.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
12 rows in set (0.00 sec)

9

1
2
3
4
5
6
7
mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.01 sec)

10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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)

mysql> select sno,cno from score
-> order by degree desc
-> limit 1;
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

11

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select sum(degree)/count(*) from score
-> where cno = '3-105';

+----------------------+
| sum(degree)/count(*) |
+----------------------+
| 81.50000 |
+----------------------+
1 row in set (0.00 sec)

mysql> select avg(degree) from score where cno = '3-105';
+-------------+
| avg(degree) |
+-------------+
| 81.50000 |
+-------------+
1 row in set (0.00 sec)

12 like having

1
2
3
4
5
6
7
8
9
10
mysql> select avg(degree) from score
-> where cno like '3%'
-> group by cno
-> having count(sno) >= 5;
+-------------+
| avg(degree) |
+-------------+
| 81.50000 |
+-------------+
1 row in set (0.00 sec)

13 group by having

1
2
3
4
5
6
7
8
9
10
mysql> select sno from score
-> group by sno
-> having min(degree) > 70 and max(degree) < 90;
+-----+
| sno |
+-----+
| 105 |
| 108 |
+-----+
2 rows in set (0.00 sec)

14 join on

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select a.sname, b.cno, b.degree
-> from
-> student as a
-> join
-> score as b
-> on a.sno = b.sno
-> order by sname;
+--------+-------+--------+
| sname | cno | degree |
+--------+-------+--------+
| 匡明 | 3-245 | 75.0 |
| 匡明 | 3-105 | 88.0 |
| 曾华 | 3-105 | 78.0 |
| 曾华 | 6-166 | 81.0 |
| 李军 | 6-166 | 85.0 |
| 李军 | 3-245 | 77.0 |
| 李军 | 3-105 | 64.0 |
| 王丽 | 3-105 | 91.0 |
| 王丽 | 6-106 | 79.0 |
| 王芳 | 3-245 | 68.0 |
| 王芳 | 3-105 | 76.0 |
| 陆君 | 3-245 | 86.0 |
| 陆君 | 3-105 | 92.0 |
+--------+-------+--------+
13 rows in set (0.01 sec)

15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select sc.sno, sc.degree, co.cname
-> from
-> score as sc
-> join
-> course as co
-> on sc.cno = co.cno;
+-----+--------+-----------------+
| sno | degree | cname |
+-----+--------+-----------------+
| 103 | 86.0 | 操作系统 |
| 105 | 75.0 | 操作系统 |
| 109 | 68.0 | 操作系统 |
| 103 | 92.0 | 计算机导论 |
| 105 | 88.0 | 计算机导论 |
| 109 | 76.0 | 计算机导论 |
| 101 | 64.0 | 计算机导论 |
| 107 | 91.0 | 计算机导论 |
| 108 | 78.0 | 计算机导论 |
| 101 | 85.0 | 数字电路 |
| 108 | 81.0 | 数字电路 |
| 101 | 77.0 | 操作系统 |
+-----+--------+-----------------+
12 rows in set (0.00 sec)

16

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select stu.sname, co.cname, sc.degree
-> from
-> student stu
-> join (course co, score sc)
-> on stu.sno = sc.sno and sc.cno = co.cno;

+--------+-----------------+--------+
| sname | cname | degree |
+--------+-----------------+--------+
| 陆君 | 操作系统 | 86.0 |
| 匡明 | 操作系统 | 75.0 |
| 王芳 | 操作系统 | 68.0 |
| 陆君 | 计算机导论 | 92.0 |
| 匡明 | 计算机导论 | 88.0 |
| 王芳 | 计算机导论 | 76.0 |
| 李军 | 计算机导论 | 64.0 |
| 王丽 | 计算机导论 | 91.0 |
| 曾华 | 计算机导论 | 78.0 |
| 李军 | 数字电路 | 85.0 |
| 曾华 | 数字电路 | 81.0 |
| 李军 | 操作系统 | 77.0 |
+--------+-----------------+--------+
12 rows in set (0.00 sec)

17

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
//先看一下95033班的分数情况
mysql> select stu.sname, co.cname, sc.degree
-> from
-> student stu
-> join
-> (score sc, course co)
-> on stu.sno=sc.sno and co.cno=sc.cno
-> where stu.class = '95033'
-> order by co.cname;
+--------+-----------------+--------+
| sname | cname | degree |
+--------+-----------------+--------+
| 李军 | 操作系统 | 77.0 |
| 曾华 | 数字电路 | 81.0 |
| 李军 | 数字电路 | 85.0 |
| 李军 | 计算机导论 | 64.0 |
| 王丽 | 计算机导论 | 91.0 |
| 曾华 | 计算机导论 | 78.0 |
+--------+-----------------+--------+
6 rows in set (0.00 sec)

//再统计95033班上各门课的平均分
mysql> select co.cname, avg(sc.degree)
-> from
-> student stu
-> join (score sc, course co)
-> on stu.sno=sc.sno and co.cno=sc.cno
-> where stu.class = '95033'
-> group by cname;
+-----------------+----------------+
| cname | avg(sc.degree) |
+-----------------+----------------+
| 操作系统 | 77.00000 |
| 数字电路 | 83.00000 |
| 计算机导论 | 77.66667 |
+-----------------+----------------+
3 rows in set (0.00 sec)

18

1
2
3
4
5
mysql> insert into grade values(90,100,'A');
mysql> insert into grade values(80,89,'B');
mysql> insert into grade values(70,79,'C');
mysql> insert into grade values(60,69,'D');
mysql> insert into grade values(0,59,'E');

19

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select a.* from score a
-> where a.cno = '3-105' and a.degree >
-> all(
-> select degree from score b
-> where b.sno = '109' and b.cno = '3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 107 | 3-105 | 91.0 |
| 108 | 3-105 | 78.0 |
+-----+-------+--------+
4 rows in set (0.01 sec)

20

1
2
3
4
5
6
mysql> select * from score s
-> where degree < (select max(degree) from score)
-> group by sno
-> having count(sno) > 1
-> order by degree;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_practice.s.cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[解决方法]

1
2
3
4
5
6
7
8
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from score s 
-> where degree < (select max(degree) from score)
-> group by sno
-> having count(sno) > 1
-> order by degree;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 64.0 |
| 109 | 3-245 | 68.0 |
| 105 | 3-245 | 75.0 |
| 108 | 3-105 | 78.0 |
| 107 | 3-105 | 91.0 |
+-----+-------+--------+
5 rows in set (0.00 sec)

21

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from score
-> where degree > (select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86.0 |
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 107 | 3-105 | 91.0 |
| 108 | 3-105 | 78.0 |
| 101 | 6-166 | 85.0 |
| 107 | 6-106 | 79.0 |
| 108 | 6-166 | 81.0 |
| 101 | 3-245 | 77.0 |
+-----+-------+--------+
9 rows in set (0.00 sec)

22

1
2
3
4
5
6
7
8
9
10
mysql> select sno, sname, sbirthday from student
-> where year(sbirthday) = (
-> select year(sbirthday) from student
-> where sno = '108');
+-----+--------+------------+
| sno | sname | sbirthday |
+-----+--------+------------+
| 108 | 曾华 | 1997-09-01 |
+-----+--------+------------+
1 row in set (0.00 sec)

23

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select sno, degree from score sc
-> join
-> (course co, teacher te)
-> on co.tno = te.tno and sc.cno = co.cno
-> where te.tname = '张旭';
+-----+--------+
| sno | degree |
+-----+--------+
| 101 | 85.0 |
| 108 | 81.0 |
+-----+--------+
2 rows in set (0.00 sec)

24

1
2
3
4
5
6
7
8
9
10
11
mysql> select te.tname from teacher te
-> join (score sc, course co)
-> on te.tno = co.tno and sc.cno = co.cno
-> group by co.cno
-> having cout(co.cno) > 5;
+--------+
| tname |
+--------+
| 王萍 |
+--------+
1 row in set (0.00 sec)

25

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from score sc
-> join student st on sc.sno = st.sno
-> where stu.class = '95033' or stu.class = '95031';
+-----+-------+--------+-----+--------+------+------------+-------+
| sno | cno | degree | sno | sname | ssex | sbirthday | class |
+-----+-------+--------+-----+--------+------+------------+-------+
| 103 | 3-245 | 86.0 | 103 | 陆君 | 男 | 1974-06-03 | 95031 |
| 105 | 3-245 | 75.0 | 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 109 | 3-245 | 68.0 | 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 103 | 3-105 | 92.0 | 103 | 陆君 | 男 | 1974-06-03 | 95031 |
| 105 | 3-105 | 88.0 | 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 109 | 3-105 | 76.0 | 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 101 | 3-105 | 64.0 | 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 107 | 3-105 | 91.0 | 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 108 | 3-105 | 78.0 | 108 | 曾华 | 男 | 1997-09-01 | 95033 |
| 101 | 6-166 | 85.0 | 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 107 | 6-106 | 79.0 | 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 108 | 6-166 | 81.0 | 108 | 曾华 | 男 | 1997-09-01 | 95033 |
| 101 | 3-245 | 77.0 | 101 | 李军 | 男 | 1976-02-20 | 95033 |
+-----+-------+--------+-----+--------+------+------------+-------+
13 rows in set (0.00 sec)

26

1
2
3
4
5
6
7
8
9
10
mysql> select cno from score
-> group by cno
-> having max(degree) > 85;
+-------+
| cno |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)

27

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select sc.sno, co.cname, sc.degree, te.tname from
-> score sc join (course co, teacher te)
-> on sc.cno = co.cno and te.tno = co.tno
-> where te.depart = '计算机系';
+-----+-----------------+--------+--------+
| sno | cname | degree | tname |
+-----+-----------------+--------+--------+
| 103 | 操作系统 | 86.0 | 李诚 |
| 105 | 操作系统 | 75.0 | 李诚 |
| 109 | 操作系统 | 68.0 | 李诚 |
| 103 | 计算机导论 | 92.0 | 王萍 |
| 105 | 计算机导论 | 88.0 | 王萍 |
| 109 | 计算机导论 | 76.0 | 王萍 |
| 101 | 计算机导论 | 64.0 | 王萍 |
| 107 | 计算机导论 | 91.0 | 王萍 |
| 108 | 计算机导论 | 78.0 | 王萍 |
| 101 | 操作系统 | 77.0 | 李诚 |
+-----+-----------------+--------+--------+
10 rows in set (0.00 sec)

28

1
2
3
4
5
6
7
8
9
10
mysql> select tname, prof from teacher
-> where depart = '计算机系' and prof not in (
-> select prof from teacher
-> where depart = '电子工程系');
+--------+-----------+
| tname | prof |
+--------+-----------+
| 李诚 | 副教授 |
+--------+-----------+
1 row in set (0.00 sec)

29 any ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from score 
-> where degree > any(
-> select degree from score
-> where cno = '3-245')
-> and cno = '3-105'
-> order by degree
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
| 107 | 3-105 | 91.0 |
| 105 | 3-105 | 88.0 |
| 108 | 3-105 | 78.0 |
| 109 | 3-105 | 76.0 |
+-----+-------+--------+
5 rows in set (0.00 sec)

30 all ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from score 
-> where degree > all(
-> select degree from score
-> where cno = '3-245')
-> and cno = '3-105'
-> order by degree
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
| 107 | 3-105 | 91.0 |
| 105 | 3-105 | 88.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

面试统计订单问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/* 创建表 */
mysql> CREATE TABLE success_killed(
`seckill_id` BIGINT NOT NULL COMMENT '秒杀商品ID',
`user_phone` BIGINT NOT NULL COMMENT '用户手机号',
`state` TINYINT NOT NULL DEFAULT -1 COMMENT '状态标识:-1:无效 0:成功 1:已付款 2:已发货',
`create_time` TIMESTAMP NOT NULL COMMENT '创建时间',
PRIMARY KEY(seckill_id,user_phone),/*联合主键*/
KEY idx_create_time(create_time)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='秒杀成功明细表'

mysql> select count(*) from success_killed group by date_format(create_time, '%Y-%m');

/* 或者 */
mysql> select count(*) from success_killed group by year(create_time), month(create_time);
+----------+
| count(*) |
+----------+
| 4 |
| 4 |
| 2 |
| 5 |
| 4 |
| 4 |
| 4 |
| 2 |
| 5 |
| 9 |
+----------+
10 rows in set (0.00 sec)
Donate here.