数据库基础SQL知识面试题二
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.选课系统SQL语法练习
course数据库中有以下四张表：
•students表（学生表）：
sid整型自增主键，sname字符串64位，gender字符串12位，dept_id整型并外键到dept表的id字段。 •dept表（系表）：
id整型自增主键，dept_name字符串64位。 •course表（课程表）：
id整型自增字段主键，course_name字符串64位，teacher_id整型外键到teacher表的id字段。 •teacher表（老师表）：
id整型自增字段主键，name字符串64位，dept_id整型外键到dept表的id字段。 •score表（选课成绩表）：
sid整型自增主键，course_id整型，score整型。 各表的预置数据如下所示：
• students表:
(,’Jason Yin’,,),(,’Andy’,,),(,’Bob’,,),(,’Ruth’,,),(,’Mike’,,),(,’John’, ,),(,’Cindy’,,),(,’Susan’,,) • dept表:
(,’Education’),(,’Computer Science’),(,’Mathematics’) • course表:
(,‘math’,),(,’english’,),(,’chinese’,),(,’history’,),(,’biology’,) • teacher表:
(,’Zhang san’,),(,’Li si’,),(,’Wang wu’,),(,’Liu liu’,),(,’Ding qi’,) • score表:
(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(, ,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,)
1>.请根据上述的信息创建出相应的表和插入预置数据
mysql> CREATE DATABASE course CHARACTER SET = utf8;
Query OK, row affected, warning (0.00 sec) mysql>
mysql> USE course;
Database changed
mysql>
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| course |
+------------+
row in set (0.00 sec) mysql>
mysql> CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR());
Query OK, rows affected (0.01 sec) mysql>
mysql> CREATE TABLE students(
-> sid INT PRIMARY KEY AUTO_INCREMENT,
-> sname VARCHAR(),
-> gender VARCHAR(),
-> dept_id INT NOT NULL,
-> CONSTRAINT student_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
-> );
Query OK, rows affected (0.02 sec) mysql>
mysql>
mysql> CREATE TABLE teacher(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(),
-> dept_id INT NOT NULL,
-> CONSTRAINT teacher_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
-> );
Query OK, rows affected (0.01 sec) mysql>
mysql> CREATE TABLE course(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> course_name VARCHAR(),
-> teacher_id INT,
-> CONSTRAINT course_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id)
-> );
Query OK, rows affected (0.01 sec) mysql>
mysql> CREATE TABLE score(sid INT,course_id INT,score INT,PRIMARY KEY(sid,course_id));
Query OK, rows affected (0.01 sec) mysql>
创建数据库与对应的表结构
mysql> INSERT INTO dept VALUES(,'Education'),(,'Computer Science'),(,'Mathematics');
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: mysql>
mysql> INSERT INTO teacher VALUES(,'Zhang san',),(,'Li si',),(,'Wang wu',),(,'Liu liu',),(,'Ding qi',);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: mysql>
mysql> INSERT INTO students VALUES(,'Jason Yin',,),(,'Andy',,),(,'Bob',,),(,'Ruth',,),(,'Mike',,),(,'John',,),(,'Cindy',,),(,'Susan',,);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: mysql>
mysql> INSERT INTO course VALUES(,'math',),(,'english',),(,'chinese',),(,'history',),(,'biology',);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: mysql>
mysql> INSERT INTO score VALUES(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,);
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: mysql>
mysql>
插入预置数据
2>.查看所有英语成绩超过数学成绩的学生的学号和姓名
mysql> SELECT aa.sid,aa.sname FROM (SELECT a1.sid,sname,score FROM students a1 INNER JOIN score b1 ON a1.sid=b1.sid WHERE course_id=) aa INNER JOIN (SELECT a2.sid,sname, score FROM students a2 INNER JOIN score b2 ON a2.sid=b2.sid WHERE course_id=) bb ON aa.sid=bb.sid WHERE aa.score>bb.score;
+-----+-----------+
| sid | sname |
+-----+-----------+
| | Jason Yin |
| | Bob |
| | Cindy |
+-----+-----------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT aa.sid,aa.sname FROM (SELECT a1.sid,sname,score FROM students a1 INNER JOIN score b1 ON a1.sid=b1.sid WHERE course_id=2) aa INNER JOIN (SELECT a2.sid,sname, score FROM students a2 INNER JOIN score b2 ON a2.sid=b2.sid WHERE course_id=1) bb ON aa.sid=bb.sid WHERE aa.score>bb.score;
3>.查看平均成绩大于等于60的所有学生的姓名和平均成绩
mysql> SELECT a.sid,a.sname,AVG(b.score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname HAVING AVG(b.score)>=;
+-----+-----------+--------------+
| sid | sname | AVG(b.score) |
+-----+-----------+--------------+
| | Jason Yin | 76.0000 |
| | Andy | 78.0000 |
| | Bob | 79.8000 |
| | Ruth | 72.5000 |
| | Mike | 82.3333 |
| | John | 73.0000 |
| | Cindy | 64.2000 |
| | Susan | 94.0000 |
+-----+-----------+--------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT a.sid,a.sname,AVG(b.score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname HAVING AVG(b.score)>=60;
4>.查询所有同学的学号，姓名，选课数和总成绩
mysql> SELECT a.sid,a.sname,COUNT(*),SUM(score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname;
+-----+-----------+----------+------------+
| sid | sname | COUNT(*) | SUM(score) |
+-----+-----------+----------+------------+
| | Jason Yin | | |
| | Andy | | |
| | Bob | | |
| | Ruth | | |
| | Mike | | |
| | John | | |
| | Cindy | | |
| | Susan | | |
+-----+-----------+----------+------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT a.sid,a.sname,COUNT(*),SUM(score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname;
5>.查询姓zhang的老师的个数
mysql> SELECT count(*) FROM teacher WHERE name LIKE 'zhang%';
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) mysql>
mysql>
mysql> SELECT count(*) FROM teacher WHERE name LIKE 'zhang%';
6>.查询没学过zhangsan老师课程的学生的学号和姓名
mysql> SELECT sid,sname FROM students WHERE Sid NOT IN (SELECT b.sid FROM course a INNER JOIN score b ON a.id=b.course_id INNER JOIN teacher c ON a.teacher_id=c.id WHERE c.name='Zhang san');
+-----+-----------+
| sid | sname |
+-----+-----------+
| | Jason Yin |
+-----+-----------+
row in set (0.00 sec) mysql>
mysql> SELECT sid,sname FROM students WHERE Sid NOT IN (SELECT b.sid FROM course a INNER JOIN score b ON a.id=b.course_id INNER JOIN teacher c ON a.teacher_id=c.id WHERE c.name='Zhang san');
7>.查询既学过英语也学过语文的学生的学号和姓名
mysql> SELECT a.sid,sname,count(*) FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id AND c.course_name in ('english', 'chinese') GROUP BY Sid,sname HAVING COUNT(*)>=;
+-----+-----------+----------+
| sid | sname | count(*) |
+-----+-----------+----------+
| | Jason Yin | |
| | Andy | |
| | Bob | |
| | Cindy | |
+-----+-----------+----------+
rows in set (0.00 sec)
mysql>
mysql> SELECT a.sid,sname,count(*) FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id AND c.course_name in ('english', 'chinese') GROUP BY Sid,sname HAVING COUNT(*)>=2;
8>.查询有学生的单科成绩小于60的姓名和课程名称
mysql>
mysql> SELECT a.sname,c.course_name FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id WHERE b.score<;
+-----------+-------------+
| sname | course_name |
+-----------+-------------+
| Jason Yin | biology |
| Bob | math |
| John | english |
| Cindy | math |
+-----------+-------------+
rows in set (0.01 sec) mysql>
mysql>
mysql> SELECT a.sname,c.course_name FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id WHERE b.score<60;
9>.按平均成绩从高到低显示所有学生的姓名和语文，数学，英语 三科成绩
mysql> SELECT a.sid,AVG(score) score_avg,SUM(CASE WHEN b.course_name='chinese' THEN a.score ELSE END) a1,SUM(CASE WHEN b.course_name='math' THEN a.score ELSE END) a2,SUM(CASE WHEN b.course_name='English' THEN a.score ELSE END) a3 FROM score a INNER JOIN course b ON a.course_id=b.id GROUP BY a.sid;
+-----+-----------+------+------+------+
| sid | score_avg | a1 | a2 | a3 |
+-----+-----------+------+------+------+
| | 76.0000 | | | |
| | 78.0000 | | | |
| | 79.8000 | | | |
| | 72.5000 | | | |
| | 82.3333 | | | |
| | 73.0000 | | | |
| | 64.2000 | | | |
| | 94.0000 | | | |
+-----+-----------+------+------+------+
rows in set (0.00 sec) mysql>
mysql> SELECT a.sid,AVG(score) score_avg,SUM(CASE WHEN b.course_name='chinese' THEN a.score ELSE 0 END) a1,SUM(CASE WHEN b.course_name='math' THEN a.score ELSE 0 END) a2,SUM(CASE WHEN b.course_name='English' THEN a.score ELSE 0 END) a3 FROM score a INNER JOIN course b ON a.course_id=b.id GROUP BY a.sid;
10>.查询各科成绩中的最高分和最低分
mysql> SELECT course_id,MIN(score),MAX(score) FROM score GROUP BY course_id;
+-----------+------------+------------+
| course_id | MIN(score) | MAX(score) |
+-----------+------------+------------+
| | | |
| | | |
| | | |
| | | |
| | | |
+-----------+------------+------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT course_id,MIN(score),MAX(score) FROM score GROUP BY course_id;
11>.计算各科平均成绩和及格率百分比
mysql> SELECT course_id,AVG(score),SUM(CASE WHEN score>= THEN ELSE END)/COUNT(*)* FROM score GROUP BY course_id;
+-----------+------------+---------------------------------------------------------+
| course_id | AVG(score) | SUM(CASE WHEN score>= THEN ELSE END)/COUNT(*)* |
+-----------+------------+---------------------------------------------------------+
| | 63.5000 | 50.0000 |
| | 77.2000 | 80.0000 |
| | 77.5000 | 100.0000 |
| | 76.6000 | 80.0000 |
| | 82.0000 | 100.0000 |
+-----------+------------+---------------------------------------------------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT course_id,AVG(score),SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*)*100 FROM score GROUP BY course_id;
12>.查询不同老师所教不同课程平均分从高到低
mysql> SELECT c.name,b.course_name,AVG(score) FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN teacher c ON b.teacher_id=c.id GROUP BY c.name,b.course_name ORDER BY AVG(score) DESC;
+-----------+-------------+------------+
| name | course_name | AVG(score) |
+-----------+-------------+------------+
| Zhang san | history | 82.0000 |
| Liu liu | chinese | 77.5000 |
| Li si | english | 77.2000 |
| Ding qi | biology | 76.6000 |
| Wang wu | math | 63.5000 |
+-----------+-------------+------------+
rows in set (0.00 sec) mysql>
mysql> SELECT c.name,b.course_name,AVG(score) FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN teacher c ON b.teacher_id=c.id GROUP BY c.name,b.course_name ORDER BY AVG(score) DESC;
13>.查询英语和数学课程成绩排名第5到第10位的学生姓名和成绩
mysql> SELECT c.sname,a.score FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN students c ON a.sid=c.sid WHERE b.course_name='English' ORDER BY a.score LIMIT ,;
+-------+-------+
| sname | score |
+-------+-------+
| Bob | |
+-------+-------+
row in set (0.00 sec) mysql>
mysql>
mysql> SELECT c.sname,a.score FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN students c ON a.sid=c.sid WHERE b.course_name='English' ORDER BY a.score LIMIT 4,6;
14>.统计按照各科成绩，分段统计每个课程在90分以上、80-90的、60~80、低于60分的人数
mysql> SELECT b.course_name,SUM(CASE WHEN score>= THEN ELSE END),SUM(CASE WHEN score< AND score>= THEN ELSE END),SUM(CASE WHEN score< AND score>= THEN ELSE END),SUM(CASE WHEN score< THEN ELSE END) FROM score a INNER JOIN course b ON a.course_id=b.id GROUP BY b.course_name;
+-------------+--------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+-------------------------------------------+
| course_name | SUM(CASE WHEN score>= THEN ELSE END) | SUM(CASE WHEN score< AND score>= THEN ELSE END) | SUM(CASE WHEN score< AND score>= THEN ELSE END) | SUM(CASE WHEN score< THEN ELSE END) |
+-------------+--------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+-------------------------------------------+
| math | | | | |
| english | | | | |
| chinese | | | | |
| biology | | | | |
| history | | | | |
+-------------+--------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+-------------------------------------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT b.course_name,SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END),SUM(CASE WHEN score=80 THEN 1 ELSE 0 END),SUM(CASE WHEN score=60 THEN 1 ELSE 0 END),SUM(CASE WHEN score
15>.查看每门课程被选修的学生数
mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;
+-----------+----------+
| course_id | COUNT(*) |
+-----------+----------+
| | |
| | |
| | |
| | |
| | |
+-----------+----------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;
16>.查看只学习了一门课程的学生的姓名和学号
mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;
+-----------+----------+
| course_id | COUNT(*) |
+-----------+----------+
| | |
| | |
| | |
| | |
| | |
+-----------+----------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;
17>.查询名字相同的学生名单和个数
mysql> SELECT sname,count(*) FROM students GROUP BY sname HAVING COUNT(*)>=;
Empty set (0.00 sec) mysql>
mysql> SELECT sname,count(*) FROM students GROUP BY sname HAVING COUNT(*)>=2;
18>.85年之后出生的学生人数
mysql> alter table students add brithday datetime;
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: mysql> update students set brithday='1983-01-01' where sid<;
Query OK, rows affected (0.01 sec)
Rows matched: Changed: Warnings: mysql> update students set brithday='1986-01-01' where sid>=;
Query OK, rows affected (0.01 sec)
Rows matched: Changed: Warnings: mysql>
添加字段并插入测试数据
mysql> SELECT * FROM students WHERE brithday>='1985-01-01';
+-----+-------+--------+---------+---------------------+
| sid | sname | gender | dept_id | brithday |
+-----+-------+--------+---------+---------------------+
| | Mike | | | -- :: |
| | John | | | -- :: |
| | Cindy | | | -- :: |
| | Susan | | | -- :: |
+-----+-------+--------+---------+---------------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT * FROM students WHERE brithday>='1985-01-01';
19>.查询每门课程的平均成绩，按升序排序，如果平均成绩相同按ID降序排序
mysql> SELECT course_id,AVG(score) FROM score GROUP BY course_id ORDER BY AVG(score),course_id DESC;
+-----------+------------+
| course_id | AVG(score) |
+-----------+------------+
| | 63.5000 |
| | 76.6000 |
| | 77.2000 |
| | 77.5000 |
| | 82.0000 |
+-----------+------------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT course_id,AVG(score) FROM score GROUP BY course_id ORDER BY AVG(score),course_id DESC;
20>.查询有不及格学生的课程和不及格学生个数
mysql> SELECT course_id,COUNT(*) FROM score WHERE score< GROUP BY course_id;
+-----------+----------+
| course_id | COUNT(*) |
+-----------+----------+
| | |
| | |
| | |
+-----------+----------+
rows in set (0.00 sec) mysql>
mysql>
mysql> SELECT course_id,COUNT(*) FROM score WHERE score
21>.将所有学生姓名中前后的空格去掉
mysql> UPDATE students SET sname=LTRIM(RTRIM(sname));
Query OK, rows affected (0.01 sec)
Rows matched: Changed: Warnings: mysql>
mysql>
mysql> UPDATE students SET sname=LTRIM(RTRIM(sname));
22>.将所有学生的考试成绩展示为课程名:成绩样式
mysql>
mysql> SELECT a.sid,CONCAT(b.course_name,':',a.score)FROM score a INNER JOIN course b ON a.course_id=b.id;
+-----+-----------------------------------+
| sid | CONCAT(b.course_name,':',a.score) |
+-----+-----------------------------------+
| | math: |
| | english: |
| | chinese: |
| | biology: |
| | english: |
| | chinese: |
| | history: |
| | biology: |
| | math: |
| | english: |
| | chinese: |
| | history: |
| | biology: |
| | chinese: |
| | history: |
| | math: |
| | chinese: |
| | history: |
| | english: |
| | history: |
| | math: |
| | english: |
| | chinese: |
| | history: |
| | biology: |
| | history: |
| | biology: |
+-----+-----------------------------------+
rows in set (0.00 sec) mysql>
mysql> SELECT a.sid,CONCAT(b.course_name,':',a.score)FROM score a INNER JOIN course b ON a.course_id=b.id;
23>.将所有老师的名字拆分成姓和名两个字段显示
mysql> SELECT name, SUBSTRING(name,,LOCATE(' ',name)- ),SUBSTRING(name,LOCATE(' ',name)+,) FROM teacher;
+-----------+---------------------------------------+---------------------------------------+
| name | SUBSTRING(name,,LOCATE(' ',name)- ) | SUBSTRING(name,LOCATE(' ',name)+,) |
+-----------+---------------------------------------+---------------------------------------+
| Zhang san | Zhang | san |
| Li si | Li | si |
| Wang wu | Wang | wu |
| Liu liu | Liu | liu |
| Ding qi | Ding | qi |
+-----------+---------------------------------------+---------------------------------------+
rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT name, SUBSTRING(name,1,LOCATE(' ',name)- 1),SUBSTRING(name,LOCATE(' ',name)+1,50) FROM teacher;
24>.把所有学生的生日格式转换成年月日的格式，并计算每个学生年龄
mysql> SELECT sname,brithday,DATE_FORMAT(brithday, '%Y%m%d'),YEAR(NOW())- YEAR(brithday),FLOOR(DATEDIFF(NOW(),brithday)/) FROM students;
+-----------+---------------------+---------------------------------+-----------------------------+-------------------------------------+
| sname | brithday | DATE_FORMAT(brithday, '%Y%m%d') | YEAR(NOW())- YEAR(brithday) | FLOOR(DATEDIFF(NOW(),brithday)/) |
+-----------+---------------------+---------------------------------+-----------------------------+-------------------------------------+
| Jason Yin | -- :: | | | |
| Andy | -- :: | | | |
| Bob | -- :: | | | |
| Ruth | -- :: | | | |
| Mike | -- :: | | | |
| John | -- :: | | | |
| Cindy | -- :: | | | |
| Susan | -- :: | | | |
+-----------+---------------------+---------------------------------+-----------------------------+-------------------------------------+
rows in set (0.00 sec) mysql>
mysql>
mysql>
mysql> SELECT sname,brithday,DATE_FORMAT(brithday, '%Y%m%d'),YEAR(NOW())- YEAR(brithday),FLOOR(DATEDIFF(NOW(),brithday)/365) FROM students;