大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
(1)创建一个数据库 sql_exercise01
(2)创建4个数据表:
①学生表(Student)
②课程表(Course)
③成绩表(Score)
④教师信息表(Teacher)
设计如下针对四张表完成如下信息:
表结构:
#学生表
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Sno | varchar(20) | NO | PRI | NULL | |
| Sname | varchar(20) | NO | | NULL | |
| Ssex | varchar(20) | NO | | NULL | |
| Sbirthday | datetime | YES | | NULL | |
| Class | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#教师表
mysql> desc teacher;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Tno | varchar(20) | NO | PRI | NULL | |
| Tname | varchar(20) | NO | | NULL | |
| Tsex | varchar(20) | NO | | NULL | |
| Tbirthday | datetime | YES | | NULL | |
| Prof | varchar(20) | YES | | NULL | |
| Depart | varchar(20) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> #课程表
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno | varchar(20) | NO | PRI | NULL | |
| Cname | varchar(20) | NO | | NULL | |
| Tno | varchar(20) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> #成绩表
mysql> desc score;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| Sno | varchar(20) | NO | MUL | NULL | |
| Cno | varchar(20) | NO | MUL | NULL | |
| Degree | decimal(10,0) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
表数据
mysql> #学生表
mysql> select *from student;
+-----+--------+------+---------------------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+--------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)
mysql> #课程表
mysql> select *from course;
+-------+-----------------+-----+
| Cno | Cname | Tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)
mysql> #成绩表
mysql> select *from score;
+-----+-------+--------+
| Sno | Cno | Degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-105 | 64 |
| 105 | 3-105 | 91 |
| 109 | 3-105 | 78 |
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec)
mysql> #教师表
mysql> select *from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| Tno | Tname | Tsex | Tbirthday | Prof | Depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 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 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)
根据上述要求完成MySQL语句书写
#创建数据库sql_exercise01
CREATE DATABASE sql_exercise01;
#使用数据库sql_exercise02
USE sql_exercise01;
#学生表
CREATE TABLE student(
`Sno` VARCHAR(20) NOT NULL COMMENT '学号',
`Sname` VARCHAR(20) NOT NULL COMMENT '姓名',
`Ssex` VARCHAR(20) NOT NULL COMMENT '性别',
`Sbirthday` DATETIME COMMENT '出生日期',
`Class` VARCHAR(20) COMMENT '班级',
PRIMARY KEY(`Sno`)
)
#教师表
CREATE TABLE teacher(
`Tno` VARCHAR(20) NOT NULL COMMENT '教师编号',
`Tname` VARCHAR(20) NOT NULL COMMENT '姓名',
`Tsex` VARCHAR(20) NOT NULL COMMENT '性别',
`Tbirthday` DATETIME COMMENT '出生日期',
`Prof` VARCHAR(20) COMMENT '职称',
`Depart` VARCHAR(20) NOT NULL COMMENT '科系',
PRIMARY KEY(`Tno`)
)
#课程表
CREATE TABLE course(
`Cno` VARCHAR(20) NOT NULL COMMENT '课程编号',
`Cname` VARCHAR(20) NOT NULL COMMENT '课程名称',
`Tno` VARCHAR(20) NOT NULL COMMENT '授课教师编号',
PRIMARY KEY(`Cno`)
)
#将其中Tno字段里的key设为MUL
ALTER TABLE course ADD INDEX(Tno);
#成绩表
CREATE TABLE score(
`Sno` VARCHAR(20) NOT NULL COMMENT '学生学号',
`Cno` VARCHAR(20) NOT NULL COMMENT '课程编号',
`Degree` DECIMAL(10,0) COMMENT '成绩'
)
#将其中Sno,Cno字段里的key设为MUL
ALTER TABLE score ADD INDEX(Sno);
ALTER TABLE score ADD INDEX(Cno);
#向学生student表添加数据
#使用insert语句向user表格中添加数据
#语法:insert into 表名( 字段1, 字段2, 字段3, … )values('值1', '值2', '值3',…);
INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(101,'李军','男','1976-02-20','95033');
INSERT INTO student VALUES(103,'陆君','男','1974-06-03','95031');
INSERT INTO student VALUES(105,'匡明','男','1975-10-02','95031');
INSERT INTO student VALUES(107,'王丽','女','1976-01-23','95033');
INSERT INTO student VALUES(108,'曾华','男','1977-09-01','95033');
INSERT INTO student VALUES(109,'王芳','女','1975-02-20','95031');
#向教师信息teacher表添加数据
INSERT INTO teacher
VALUES
(804,'李诚','男','1958-12-02','副教授','计算机系'),
(856,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'),
(831,'刘冰','女','1977-08-14','助教','电子工程系');
#向课程course表添加数据
INSERT INTO course
VALUES
('3-105','计算机导论',825),
('3-245','操作系统',804),
('6-166','数字电路',856),
('9-888','高等数学',831);
#向成绩score表添加数据
INSERT INTO score
VALUES
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-166',79),
(108,'6-166',81);
题目:
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.查询每门课的平均成绩
12.查询score表中至少有5名学生选修的并以3开头的课程的平均分数
13.查询分数大于70,小于90的sno列
14.查询所有学生的sname、cno和degree列
15.查询所有学生的sno、cname和degree列
16.查询所有学生的sname、cname和degree列
17.查询“95033”班学生的平均分
18.假设使用如下命令建立了一个grade表
19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
20.查询score中选学多门课程的同学中分数为非最高分成绩的记录
21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
22.查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列
23.查询“张旭“教师任课的学生成绩
24.查询选修某课程的同学人数多于5人的教师姓名
25.查询95033班和95031班全体学生的记录
26.查询存在有85分以上成绩的课程cno
27.查询出“计算机系“教师所教课程的成绩表
28.查询“计算机系”与“电子工程系“不同职称的教师的tname和prof
29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序
30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree
31.查询所有教师和同学的name、sex和birthday
32.查询所有“女”教师和“女”同学的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.查询所有选修“计算机导论”课程的“男”同学的成绩表
1.查询Student表中的所有记录的Sname , Ssex 和 Class列
SELECT Sname,Ssex,Class FROM Student;
-->最终结果
mysql> SELECT Sname,Ssex,Class FROM Student;
+-------+------+-------+
| Sname | Ssex | Class |
+-------+------+-------+
| 李军 | 男 | 95033 |
| 陆君 | 男 | 95031 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 曾华 | 男 | 95033 |
| 王芳 | 女 | 95031 |
+-------+------+-------+
2.查询教师所有的单位即不重复的Depart列
SELECT DISTINCT Depart FROM teacher;
-->最终结果:
mysql> SELECT DISTINCT Depart FROM teacher;
+------------+
| Depart |
+------------+
| 计算机系 |
| 电子工程系 |
+------------+
3.查询Student表的所有记录
SELECT * FROM Student;
-->最终结果:
mysql> SELECT * FROM Student;
+-----+-------+------+---------------------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
4. 查询Score表中成绩在60到80之间的所有记录
方法1:
SELECT * FROM Score WHERE Degree BETWEEN 60 AND 80;
方法2:
SELECT * FROM Score WHERE Degree>=60 AND Degree<=80;
-->最终结果:
mysql> SELECT * FROM Score WHERE Degree BETWEEN 60 AND 80;
+-----+-------+--------+
| Sno | Cno | Degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 109 | 3-105 | 76 |
| 101 | 3-105 | 64 |
| 108 | 3-105 | 78 |
| 107 | 6-166 | 79 |
+-----+-------+--------+
5.查询Score表中成绩为85 86 或 88 的记录
#方式1
SELECT *
FROM score
WHERE degree=85 OR degree=86 OR degree=88;
-->最终结果
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 |
+-----+-------+--------+
#方式2
SELECT *
FROM score
WHERE degree IN ('85','86','88');
-->最终结果
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.查询student表中“95031”班或性别为“女”的同学记录
SELECT *
FROM student
WHERE `class`='95031' OR `ssex`='女';
-->最终结果
mysql> SELECT *
-> FROM student
-> WHERE `class`='95031' OR `ssex`='女';
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
7.以class降序查询student表的所有记录
SELECT *
FROM student ORDER BY class DESC;
-->最终结果
mysql> SELECT *
-> FROM student ORDER BY class DESC;
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)
8.以cno升序、degree降序查询score表的所有记录
SELECT *
FROM score ORDER BY cno ASC,degree DESC;
-->最终结果
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 |
| 101 | 6-166 | 85.0 |
| 108 | 6-166 | 81.0 |
| 107 | 6-166 | 79.0 |
+-----+-------+--------+
12 rows in set (0.00 sec)
9.查询“95031”班的学生人数
SELECT COUNT(*) AS '95031班的学生人数'
FROM Student WHERE Class='95031';
-->最终结果
mysql> SELECT COUNT(*) AS '95031班的学生人数'
-> FROM Student WHERE Class='95031';
+-------------------+
| 95031班的学生人数 |
+-------------------+
| 3 |
+-------------------+
1 row in set (0.00 sec)
10.查询score表中的最高分的学生学号和课程号。(子查询或者排序)
#方式1
SELECT sno,cno
FROM score
WHERE `degree`=(SELECT MAX(degree) FROM 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)
#方式2:
SELECT sno,cno FROM score ORDER BY degree DESC LIMIT 0,1
-->最终结果
mysql> SELECT sno,cno FROM score ORDER BY degree DESC LIMIT 0,1;
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
#方式3
SELECT sno,cno
FROM student,course
WHERE (sno,cno) = (SELECT sno,cno FROM score WHERE degree = (SELECT MAX(degree) FROM score));
-->最终结果
mysql> SELECT sno,cno
-> FROM student,course
-> WHERE (sno,cno) = (SELECT sno,cno FROM score WHERE degree = (SELECT MAX(degree) FROM score));
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
11.查询每门课的平均成绩
SELECT cno,AVG(degree) AS '平均分'
FROM score GROUP BY cno;
-->最终结果
mysql> SELECT cno,AVG(degree) AS '平均分'
-> FROM score GROUP BY cno;
+-------+----------+
| cno | 平均分 |
+-------+----------+
| 3-105 | 81.50000 |
| 3-245 | 76.33333 |
| 6-166 | 81.66667 |
+-------+----------+
3 rows in set (0.00 sec)
12.查询score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT AVG(degree)
FROM score
WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(cno)>=5;
-->最终结果
mysql> SELECT AVG(degree)
-> FROM score
-> WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(cno)>=5;
+-------------+
| AVG(degree) |
+-------------+
| 81.50000 |
+-------------+
1 row in set (0.00 sec)
13.查询分数大于70,小于90的sno列
SELECT sno
FROM score
WHERE degree>70 AND degree>90
-->最终结果
mysql> SELECT sno
-> FROM score
-> WHERE degree>70 AND degree>90;
+-----+
| sno |
+-----+
| 103 |
| 107 |
+-----+
2 rows in set (0.00 sec)
14.查询所有学生的sname、cno和degree列
#方式1
SELECT sname,cno,degree
FROM student,score
WHERE student.sno=score.sno;
-->最终结果
mysql> SELECT sname,cno,degree
-> FROM student,score
-> WHERE student.sno=score.sno;
+-------+-------+--------+
| sname | cno | degree |
+-------+-------+--------+
| 李军 | 3-105 | 64.0 |
| 李军 | 6-166 | 85.0 |
| 陆君 | 3-245 | 86.0 |
| 陆君 | 3-105 | 92.0 |
| 匡明 | 3-245 | 75.0 |
| 匡明 | 3-105 | 88.0 |
| 王丽 | 3-105 | 91.0 |
| 王丽 | 6-166 | 79.0 |
| 曾华 | 3-105 | 78.0 |
| 曾华 | 6-166 | 81.0 |
| 王芳 | 3-245 | 68.0 |
| 王芳 | 3-105 | 76.0 |
+-------+-------+--------+
12 rows in set (0.00 sec)
#方式2
SELECT sname,cno,degree
FROM student
JOIN score ON student.sno=score.sno;
-->最终结果
mysql> SELECT sname,cno,degree
-> FROM student
-> JOIN score ON student.sno=score.sno;
+-------+-------+--------+
| sname | cno | degree |
+-------+-------+--------+
| 李军 | 3-105 | 64.0 |
| 李军 | 6-166 | 85.0 |
| 陆君 | 3-245 | 86.0 |
| 陆君 | 3-105 | 92.0 |
| 匡明 | 3-245 | 75.0 |
| 匡明 | 3-105 | 88.0 |
| 王丽 | 3-105 | 91.0 |
| 王丽 | 6-166 | 79.0 |
| 曾华 | 3-105 | 78.0 |
| 曾华 | 6-166 | 81.0 |
| 王芳 | 3-245 | 68.0 |
| 王芳 | 3-105 | 76.0 |
+-------+-------+--------+
12 rows in set (0.00 sec)
15.查询所有学生的sno、cname和degree列
#方式1
SELECT sno,cname,degree
FROM score,course
WHERE score.cno=course.cno;
-->最终结果
mysql> SELECT sno,cname,degree
-> FROM score,course
-> WHERE score.cno=course.cno;
+-----+------------+--------+
| sno | cname | degree |
+-----+------------+--------+
| 103 | 计算机导论 | 92.0 |
| 105 | 计算机导论 | 88.0 |
| 109 | 计算机导论 | 76.0 |
| 101 | 计算机导论 | 64.0 |
| 107 | 计算机导论 | 91.0 |
| 108 | 计算机导论 | 78.0 |
| 103 | 操作系统 | 86.0 |
| 105 | 操作系统 | 75.0 |
| 109 | 操作系统 | 68.0 |
| 101 | 数字电路 | 85.0 |
| 107 | 数字电路 | 79.0 |
| 108 | 数字电路 | 81.0 |
+-----+------------+--------+
12 rows in set (0.10 sec)
#方式2
SELECT sno,cname,degree
FROM score
JOIN course ON course.cno=score.cno;
-->最终结果
mysql> SELECT sno,cname,degree
-> FROM score
-> JOIN course ON course.cno=score.cno;
+-----+------------+--------+
| sno | cname | degree |
+-----+------------+--------+
| 103 | 计算机导论 | 92.0 |
| 105 | 计算机导论 | 88.0 |
| 109 | 计算机导论 | 76.0 |
| 101 | 计算机导论 | 64.0 |
| 107 | 计算机导论 | 91.0 |
| 108 | 计算机导论 | 78.0 |
| 103 | 操作系统 | 86.0 |
| 105 | 操作系统 | 75.0 |
| 109 | 操作系统 | 68.0 |
| 101 | 数字电路 | 85.0 |
| 107 | 数字电路 | 79.0 |
| 108 | 数字电路 | 81.0 |
+-----+------------+--------+
12 rows in set (0.00 sec)
16.查询所有学生的sname、cname和degree列
#方式1
SELECT sname,cname,degree
FROM student,score,course
WHERE student.sno=score.sno AND score.cno=course.cno;
-->最终结果
mysql> SELECT sname,cname,degree
-> FROM student,score,course
-> WHERE student.sno=score.sno AND score.cno=course.cno;
+-------+------------+--------+
| sname | cname | degree |
+-------+------------+--------+
| 李军 | 计算机导论 | 64.0 |
| 李军 | 数字电路 | 85.0 |
| 陆君 | 操作系统 | 86.0 |
| 陆君 | 计算机导论 | 92.0 |
| 匡明 | 操作系统 | 75.0 |
| 匡明 | 计算机导论 | 88.0 |
| 王丽 | 计算机导论 | 91.0 |
| 王丽 | 数字电路 | 79.0 |
| 曾华 | 计算机导论 | 78.0 |
| 曾华 | 数字电路 | 81.0 |
| 王芳 | 操作系统 | 68.0 |
| 王芳 | 计算机导论 | 76.0 |
+-------+------------+--------+
12 rows in set (0.00 sec)
#方式2
SELECT student.sname,cname,degree
FROM student
JOIN score ON student.sno=score.sno
JOIN course ON course.cno=score.cno;
-->最终结果
mysql> SELECT student.sname,cname,degree
-> FROM student
-> JOIN score ON student.sno=score.sno
-> JOIN course ON course.cno=score.cno;
+-------+------------+--------+
| sname | cname | degree |
+-------+------------+--------+
| 李军 | 计算机导论 | 64.0 |
| 李军 | 数字电路 | 85.0 |
| 陆君 | 操作系统 | 86.0 |
| 陆君 | 计算机导论 | 92.0 |
| 匡明 | 操作系统 | 75.0 |
| 匡明 | 计算机导论 | 88.0 |
| 王丽 | 计算机导论 | 91.0 |
| 王丽 | 数字电路 | 79.0 |
| 曾华 | 计算机导论 | 78.0 |
| 曾华 | 数字电路 | 81.0 |
| 王芳 | 操作系统 | 68.0 |
| 王芳 | 计算机导论 | 76.0 |
+-------+------------+--------+
12 rows in set (0.00 sec)
17.查询“95033”班学生的平均分
#方式1
SELECT AVG(degree) AS '95033班学生的平均分'
FROM score
WHERE sno IN(SELECT sno FROM student WHERE class='95033');
-->最终结果
mysql> SELECT AVG(degree) AS '95033班学生的平均分'
-> FROM score
-> WHERE sno IN(SELECT sno FROM student WHERE class='95033');
+---------------------+
| 95033班学生的平均分 |
+---------------------+
| 79.66667 |
+---------------------+
1 row in set (0.00 sec)
#方式2
SELECT AVG(degree) AS '95033班学生的平均分'
FROM score,student
WHERE student.sno=score.sno AND class='95033';
-->最终结果
mysql> SELECT AVG(degree) AS '95033班学生的平均分'
-> FROM score,student
-> WHERE student.sno=score.sno AND class='95033';
+---------------------+
| 95033班学生的平均分 |
+---------------------+
| 79.66667 |
+---------------------+
1 row in set (0.00 sec)
18.假设使用如下命令建立了一个grade表( 然后查询所有同学的sno、cno和rank列)
CREATE TABLE IF NOT EXISTS `grade` ( `low` INT (3) COMMENT '底限', `upp` INT (3) COMMENT '上限', `rank` CHAR(1) COMMENT '等级' ) ; #向grade表中添加数据 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'); --->创建表结果为: mysql> select * from grade; +------+------+------+ | low | upp | rank | +------+------+------+ | 90 | 100 | A | | 80 | 89 | B | | 70 | 79 | C | | 60 | 69 | D | | 0 | 59 | E | +------+------+------+ 5 rows in set (0.00 sec)
#方式1
SELECT sno,cno,`rank`
FROM score,grade
WHERE degree BETWEEN low AND upp;
-->最终结果
mysql> SELECT sno,cno,`rank`
-> FROM score,grade
-> WHERE degree BETWEEN low AND upp;
+-----+-------+------+
| sno | cno | rank |
+-----+-------+------+
| 103 | 3-245 | B |
| 105 | 3-245 | C |
| 109 | 3-245 | D |
| 103 | 3-105 | A |
| 105 | 3-105 | B |
| 109 | 3-105 | C |
| 101 | 3-105 | D |
| 107 | 3-105 | A |
| 108 | 3-105 | C |
| 101 | 6-166 | B |
| 107 | 6-166 | C |
| 108 | 6-166 | B |
+-----+-------+------+
12 rows in set (0.00 sec)
#方式2
SELECT sno,cno,`rank`
FROM grade
JOIN score ON score.degree BETWEEN low AND upp;
-->最终结果
mysql> SELECT sno,cno,`rank`
-> FROM grade
-> JOIN score ON score.degree BETWEEN low AND upp;
+-----+-------+------+
| sno | cno | rank |
+-----+-------+------+
| 103 | 3-245 | B |
| 105 | 3-245 | C |
| 109 | 3-245 | D |
| 103 | 3-105 | A |
| 105 | 3-105 | B |
| 109 | 3-105 | C |
| 101 | 3-105 | D |
| 107 | 3-105 | A |
| 108 | 3-105 | C |
| 101 | 6-166 | B |
| 107 | 6-166 | C |
| 108 | 6-166 | B |
+-----+-------+------+
12 rows in set (0.00 sec)
19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
#方式1:
#第一步:查询'109'号同学成绩的最大值-->76.0
SELECT MAX(degree) FROM score WHERE sno=109;
#第二步:查询选修“3-105”课程的成绩大于76.0的同学的sno
SELECT sno FROM score WHERE `cno`='3-105' AND degree>76.0
#第三步:合并写:查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT *
FROM student
WHERE sno IN(SELECT sno FROM score WHERE `cno`='3-105' AND degree>(SELECT MAX(degree) FROM score WHERE sno=109))
-->最终结果
mysql> SELECT *
-> FROM student
-> WHERE sno IN(SELECT sno FROM score WHERE `cno`='3-105' AND degree>(SELECT MAX(degree) FROM score WHERE sno=109));
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
+-----+-------+------+---------------------+-------+
4 rows in set (0.00 sec)
#方式2
SELECT *
FROM student,score
WHERE score.cno='3-105'
AND student.sno=score.sno
AND score.degree>(SELECT degree FROM score WHERE cno='3-105' AND sno='109');
-->最终结果
mysql> SELECT *
-> FROM student,score
-> WHERE score.cno='3-105'
-> AND student.sno=score.sno
-> AND score.degree>(SELECT degree FROM score WHERE cno='3-105' AND sno='109');
+-----+-------+------+---------------------+-------+-----+-------+--------+
| sno | sname | ssex | sbirthday | class | sno | cno | degree |
+-----+-------+------+---------------------+-------+-----+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92.0 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88.0 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91.0 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 | 78.0 |
+-----+-------+------+---------------------+-------+-----+-------+--------+
4 rows in set (0.00 sec)
20.查询score中选学多门课程的同学中分数为非最高分成绩的记录
SELECT *
FROM score a
WHERE degree <(SELECT MAX(degree) FROM score b WHERE a.cno=b.cno)
AND sno IN(SELECT sno FROM score GROUP BY sno HAVING COUNT(*)>1);
-->最终结果
mysql> SELECT *
-> FROM score a
-> WHERE degree <(SELECT MAX(degree) FROM score b WHERE a.cno=b.cno)
-> AND sno IN(SELECT sno FROM score GROUP BY sno HAVING COUNT(*)>1);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75.0 |
| 109 | 3-245 | 68.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 |
| 107 | 6-166 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
9 rows in set (0.00 sec)
21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
#第一步:查询学号为109、课程号为3-105同学的成绩-->76.0
SELECT degree FROM score WHERE sno=109 AND cno='3-105';
#第二步:查询成绩高于76.0的同学的所有记录
SELECT * FROM student,score WHERE student.sno=score.sno AND score.degree>76.0;
#合并写:
SELECT *
FROM student,score
WHERE student.sno=score.sno
AND score.degree>(SELECT degree FROM score WHERE sno=109 AND cno='3-105');
-->最终结果
mysql> SELECT *
-> FROM student,score
-> WHERE student.sno=score.sno
-> AND score.degree>(SELECT degree FROM score WHERE sno=109 AND cno='3-105');
+-----+-------+------+---------------------+-------+-----+-------+--------+
| sno | sname | ssex | sbirthday | class | sno | cno | degree |
+-----+-------+------+---------------------+-------+-----+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 | 86.0 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92.0 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88.0 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91.0 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 | 78.0 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 | 85.0 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 6-166 | 79.0 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 | 81.0 |
+-----+-------+------+---------------------+-------+-----+-------+--------+
8 rows in set (0.00 sec)
22.查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列
SELECT sno,sname,sbirthday
FROM student
WHERE YEAR(student.sbirthday)=(SELECT YEAR(sbirthday) FROM student WHERE sno='107');
-->最终结果
mysql> SELECT sno,sname,sbirthday
-> FROM student
-> WHERE YEAR(student.sbirthday)=(SELECT YEAR(sbirthday) FROM student WHERE sno='107');
+-----+-------+---------------------+
| sno | sname | sbirthday |
+-----+-------+---------------------+
| 101 | 李军 | 1976-02-20 00:00:00 |
| 107 | 王丽 | 1976-01-23 00:00:00 |
+-----+-------+---------------------+
2 rows in set (0.00 sec)
23.查询“张旭“教师任课的学生成绩
#方式1
SELECT sno,degree
FROM teacher,course,score
WHERE teacher.tno=course.tno
AND course.cno=score.cno AND tname='张旭';
-->最终结果
mysql> SELECT sno,degree
-> FROM teacher,course,score
-> WHERE teacher.tno=course.tno
-> AND course.cno=score.cno AND tname='张旭';
+-----+--------+
| sno | degree |
+-----+--------+
| 101 | 85.0 |
| 107 | 79.0 |
| 108 | 81.0 |
+-----+--------+
3 rows in set (0.00 sec)
#方式2
#第一步:查询“张旭“教师对应的教师编号tno
SELECT tno FROM teacher WHERE tname='张旭';
#第二步:合并写
SELECT sno,degree
FROM course,score
WHERE course.tno=(SELECT tno FROM teacher WHERE tname='张旭') AND course.cno=score.cno;
-->最终结果
mysql> SELECT sno,degree
-> FROM course,score
-> WHERE course.tno=(SELECT tno FROM teacher WHERE tname='张旭') AND course.cno=score.cno;
+-----+--------+
| sno | degree |
+-----+--------+
| 101 | 85.0 |
| 107 | 79.0 |
| 108 | 81.0 |
+-----+--------+
3 rows in set (0.00 sec)
#方式3
SELECT sno,degree
FROM score
WHERE cno IN (SELECT cno FROM course WHERE tno IN (SELECT tno FROM teacher WHERE tname='张旭'));
-->最终结果
mysql> SELECT sno,degree
-> FROM score
-> WHERE cno IN (SELECT cno FROM course WHERE tno IN (SELECT tno FROM teacher WHERE tname='张旭'));
+-----+--------+
| sno | degree |
+-----+--------+
| 101 | 85.0 |
| 107 | 79.0 |
| 108 | 81.0 |
+-----+--------+
3 rows in set (0.00 sec)
24.查询选修某课程的同学人数多于5人的教师姓名
#方式1
#查询人数大于5人的选修课程cno
SELECT cno FROM score GROUP BY score.cno HAVING COUNT(cno)>5;
#第二步:合并写
SELECT tname
FROM course,teacher
WHERE course.cno=(SELECT cno FROM score
GROUP BY score.cno HAVING COUNT(cno)>5) AND course.tno=teacher.tno
-->最终结果
mysql> SELECT tname
-> FROM course,teacher
-> WHERE course.cno=(SELECT cno FROM score
-> GROUP BY score.cno HAVING COUNT(cno)>5) AND course.tno=teacher.tno;
+-------+
| tname |
+-------+
| 王萍 |
+-------+
1 row in set (0.00 sec)
#方式2
SELECT tname
FROM teacher,course,score
WHERE teacher.tno=course.tno AND course.cno=score.cno
GROUP BY score.cno HAVING COUNT(score.cno)>5;
-->最终结果
mysql> SELECT tname
-> FROM teacher,course,score
-> WHERE teacher.tno=course.tno AND course.cno=score.cno
-> GROUP BY score.cno HAVING COUNT(score.cno)>5;
+-------+
| tname |
+-------+
| 王萍 |
+-------+
1 row in set (0.00 sec)
#方式3
SELECT tname
FROM teacher
WHERE tno IN (SELECT tno
FROM course WHERE cno IN (SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5) )
-->最终结果
mysql> SELECT tname
-> FROM teacher
-> WHERE tno IN (SELECT tno
-> FROM course WHERE cno IN (SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5) );
+-------+
| tname |
+-------+
| 王萍 |
+-------+
1 row in set (0.00 sec)
25.查询95033班和95031班全体学生的记录
#方式1
SELECT * FROM student WHERE class IN('95033','95031');
-->最终结果
mysql> SELECT * FROM student WHERE class IN('95033','95031');
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)
#方式2
SELECT * FROM student WHERE class='95033' OR class='95031';
--->最终结果
mysql> SELECT * FROM student WHERE class='95033' OR class='95031';
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)
26.查询存在有85分以上成绩的课程cno
SELECT DISTINCT cno FROM score WHERE degree>85
-->最终结果
mysql> SELECT DISTINCT cno FROM score WHERE degree>85;
+-------+
| cno |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)
27.查询出“计算机系“教师所教课程的成绩表
#方式1
SELECT score.*
FROM teacher,course,score
WHERE teacher.depart='计算机系' AND teacher.tno=course.tno AND course.cno=score.cno;
-->最终结果
mysql> SELECT score.*
-> FROM teacher,course,score
-> WHERE teacher.depart='计算机系' AND teacher.tno=course.tno AND course.cno=score.cno;
+-----+-------+--------+
| 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 |
+-----+-------+--------+
9 rows in set (0.00 sec)
#方式2
#第一步:查询'计算机系'对应的教师编号tno-->804 825
SELECT tno FROM teacher WHERE depart='计算机系';
#第二步:查询教师编号对应的课程编号cno-->3-245 3-105
SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系');
#第三步:查询出“计算机系“教师所教课程的成绩表
SELECT *
FROM score
WHERE cno IN
(SELECT cno FROM course WHERE tno IN
(SELECT tno FROM teacher WHERE depart='计算机系'));
-->最终结果
mysql> SELECT *
-> FROM score
-> WHERE cno IN
-> (SELECT cno FROM course WHERE tno IN
-> (SELECT tno FROM teacher WHERE depart='计算机系'));
+-----+-------+--------+
| 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 |
+-----+-------+--------+
9 rows in set (0.00 sec)
28.查询“计算机系”与“电子工程系“不同职称的教师的tname和prof
#方式1
SELECT tname,prof
FROM teacher a
WHERE prof NOT IN
(SELECT prof FROM teacher b WHERE a.depart!=b.depart)
-->最终结果
mysql> SELECT tname,prof
-> FROM teacher a
-> WHERE prof NOT IN
-> (SELECT prof FROM teacher b WHERE a.depart!=b.depart);
+-------+--------+
| tname | prof |
+-------+--------+
| 李诚 | 副教授 |
| 张旭 | 讲师 |
+-------+--------+
2 rows in set (0.00 sec)
#方式2
SELECT any_value(Tname) Tname,Prof
FROM teacher
WHERE Depart='计算机系' OR Depart='电子工程系'
GROUP BY Prof
HAVING COUNT(1) = 1;
-->最终结果
mysql> SELECT any_value(Tname) Tname,Prof
-> FROM teacher
-> WHERE Depart='计算机系' OR Depart='电子工程系'
-> GROUP BY Prof
-> HAVING COUNT(1) = 1;
+-------+--------+
| Tname | Prof |
+-------+--------+
| 李诚 | 副教授 |
| 张旭 | 讲师 |
+-------+--------+
2 rows in set (0.00 sec)
29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序
#方式1
#第一步:查询选修编号'3-245'的同学成绩最小值-->68.0
SELECT MIN(degree) FROM score WHERE cno='3-245';
#第二步:查询选修编号'3-105'同学的成绩大于选修编号为“3-245”的同学的成绩
SELECT degree FROM score WHERE cno='3-105' AND degree>(SELECT MIN(degree) FROM score WHERE cno='3-245');
#第三步:查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序
SELECT sno,cno,degree
FROM score
WHERE cno='3-105' AND degree>(SELECT MIN(degree)
FROM score WHERE cno='3-245') ORDER BY degree DESC;
-->最终结果
mysql> SELECT sno,cno,degree
-> FROM score
-> WHERE cno='3-105' AND degree>(SELECT MIN(degree)
-> FROM score WHERE cno='3-245') ORDER BY 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 |
+-----+-------+--------+
5 rows in set (0.00 sec)
#方式2
SELECT DISTINCT score.sno,score.cno,degree
FROM student, course,score
WHERE score.Cno = '3-105' AND student.Sno = score.Sno AND
( Degree > (SELECT MIN(Degree) FROM score WHERE Cno='3-245'))
ORDER BY Degree DESC;
-->最终结果
mysql> SELECT DISTINCT score.sno,score.cno,degree
-> FROM student, course,score
-> WHERE score.Cno = '3-105' AND student.Sno = score.Sno AND
-> ( Degree > (SELECT MIN(Degree) FROM score WHERE Cno='3-245'))
-> ORDER BY 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 |
+-----+-------+--------+
5 rows in set (0.00 sec)
#方式3
SELECT *
FROM score
WHERE cno='3-105' AND degree >ANY(SELECT degree FROM score WHERE cno='3-245')
ORDER BY Degree DESC;
-->最终结果
mysql> 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.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.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree
/*
解析:意思就是,一个同学同时选了3-105和3-245的课程,查询出这个
同学选修3-105课程的成绩大于他选修3-245课程的成绩的cno、sno和degree的记录
*/
SELECT *
FROM score a
WHERE a.cno = '3-105' AND a.degree > ( SELECT b.degree FROM score b WHERE cno = '3-245' AND a.sno = b.sno )
-->最终结果
mysql> SELECT *
-> FROM score a
-> WHERE a.cno = '3-105' AND a.degree > ( SELECT b.degree FROM score b WHERE cno = '3-245' AND a.sno = b.sno );
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 109 | 3-105 | 76.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)
31.查询所有教师和同学的name、sex和birthday
SELECT DISTINCT sname NAME,ssex sex,sbirthday birthday
FROM student
UNION
SELECT tname NAME,tsex sex,tbirthday birthday
FROM teacher;
-->最终结果
mysql> SELECT DISTINCT sname NAME,ssex sex,sbirthday birthday
-> FROM student
-> UNION
-> SELECT tname NAME,tsex sex,tbirthday birthday
-> FROM teacher;
+------+-----+---------------------+
| NAME | sex | birthday |
+------+-----+---------------------+
| 李军 | 男 | 1976-02-20 00:00:00 |
| 陆君 | 男 | 1974-06-03 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 王芳 | 女 | 1975-02-20 00:00:00 |
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
+------+-----+---------------------+
10 rows in set (0.00 sec)
32.查询所有“女”教师和“女”同学的name、sex和birthday
SELECT DISTINCT sname NAME,ssex sex,sbirthday birthday
FROM student WHERE ssex='女'
UNION
SELECT tname NAME,tsex sex,tbirthday birthday
FROM teacher WHERE tsex='女'
-->最终结果
mysql> SELECT DISTINCT sname NAME,ssex sex,sbirthday birthday
-> FROM student WHERE ssex='女'
-> UNION
-> SELECT tname NAME,tsex sex,tbirthday birthday
-> FROM teacher WHERE tsex='女';
+------+-----+---------------------+
| NAME | sex | birthday |
+------+-----+---------------------+
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-20 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
+------+-----+---------------------+
4 rows in set (0.00 sec)
33.查询成绩比该课程平均成绩低的同学的成绩表
#方式1
SELECT *
FROM score a
WHERE degree < (SELECT AVG(degree) FROM score b WHERE b.cno=a.cno);
-->最终结果
mysql> SELECT *
-> FROM score a
-> WHERE degree < (SELECT AVG(degree) FROM score b WHERE b.cno=a.cno);
+-----+-------+--------+
| 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-166 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
7 rows in set (0.00 sec)
#方式2
SELECT Sno,score.Cno Cno ,score.Degree FROM score JOIN
(SELECT Cno , AVG(Degree) Degree FROM score GROUP BY Cno) AS s2
ON score.Cno = s2.Cno AND score.Degree <s2.Degree;
-->最终结果
mysql> SELECT Sno,score.Cno Cno ,score.Degree FROM score JOIN
-> (SELECT Cno , AVG(Degree) Degree FROM score GROUP BY Cno) AS s2
-> ON score.Cno = s2.Cno AND score.Degree <s2.Degree;
+-----+-------+--------+
| 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-166 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
7 rows in set (0.00 sec)
#方式3
SELECT sno,cno,degree
FROM score a
WHERE a.degree<(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno)
-->最终结果
mysql> SELECT sno,cno,degree
-> FROM score a
-> WHERE a.degree<(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno);
+-----+-------+--------+
| 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-166 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
7 rows in set (0.00 sec)
34.查询所有任课教师的tname和depart
#方式1:
SELECT tname,depart
FROM teacher,course
WHERE teacher.tno = course.tno
AND course.cno IN (SELECT cno FROM score);
-->最终结果
mysql> SELECT tname,depart
-> FROM teacher,course
-> WHERE teacher.tno = course.tno
-> AND course.cno IN (SELECT cno FROM score);
+-------+------------+
| tname | depart |
+-------+------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 张旭 | 电子工程系 |
+-------+------------+
3 rows in set (0.00 sec)
#方式2
SELECT tname,depart
FROM teacher
WHERE tname IN (SELECT DISTINCT tname FROM teacher,course,score
WHERE teacher.tno=course.tno AND course.cno=score.cno);
-->最终结果
mysql> SELECT tname,depart
-> FROM teacher
-> WHERE tname IN (SELECT DISTINCT tname FROM teacher,course,score
-> WHERE teacher.tno=course.tno AND course.cno=score.cno);
+-------+------------+
| tname | depart |
+-------+------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 张旭 | 电子工程系 |
+-------+------------+
3 rows in set (0.00 sec)
#方式3
SELECT tname,depart
FROM teacher WHERE tno IN
(SELECT tno FROM course WHERE cno IN (SELECT DISTINCT cno FROM score))
-->最终结果
mysql> SELECT tname,depart
-> FROM teacher WHERE tno IN
-> (SELECT tno FROM course WHERE cno IN (SELECT DISTINCT cno FROM score));
+-------+------------+
| tname | depart |
+-------+------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 张旭 | 电子工程系 |
+-------+------------+
3 rows in set (0.00 sec)
35.查询所有未讲课的教师的tname和depart
#方式1
SELECT tname,depart
FROM teacher,course
WHERE teacher.tno=course.tno
AND course.cno NOT IN(SELECT cno FROM score);
-->最终结果
mysql> SELECT tname,depart
-> FROM teacher,course
-> WHERE teacher.tno=course.tno
-> AND course.cno NOT IN(SELECT cno FROM score);
+-------+------------+
| tname | depart |
+-------+------------+
| 刘冰 | 电子工程系 |
+-------+------------+
1 row in set (0.00 sec)
#方式2
SELECT tname,depart
FROM teacher WHERE tname
NOT IN (SELECT DISTINCT tname FROM teacher,course,score
WHERE teacher.tno=course.tno AND course.cno=score.cno)
-->最终结果
mysql> SELECT tname,depart
-> FROM teacher WHERE tname
-> NOT IN (SELECT DISTINCT tname FROM teacher,course,score
-> WHERE teacher.tno=course.tno AND course.cno=score.cno);
+-------+------------+
| tname | depart |
+-------+------------+
| 刘冰 | 电子工程系 |
+-------+------------+
1 row in set (0.00 sec)
36.查询至少有2名男生的班号
#方式1
SELECT class
FROM student
GROUP BY class HAVING COUNT(ssex)>=2;
-->最终结果
mysql> SELECT class
-> FROM student
-> GROUP BY class HAVING COUNT(ssex)>=2;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)
#方式2
SELECT class
FROM student
WHERE ssex='男' GROUP BY class HAVING COUNT(*)>1;
-->最终结果
mysql> SELECT class
-> FROM student
-> WHERE ssex='男' GROUP BY class HAVING COUNT(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)
37.查询student表中不姓“王”的同学记录
SELECT * FROM student WHERE sname NOT LIKE '王%'
-->最终结果
mysql> SELECT * FROM student WHERE sname NOT LIKE '王%';
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
+-----+-------+------+---------------------+-------+
4 rows in set (0.00 sec)
38.查询student表中每个学生的姓名和年龄
SELECT sname,YEAR(NOW())-YEAR(sbirthday) AS age FROM student;
-->最终结果
mysql> SELECT sname,YEAR(NOW())-YEAR(sbirthday) AS age FROM student;
+-------+------+
| sname | age |
+-------+------+
| 李军 | 46 |
| 陆君 | 48 |
| 匡明 | 47 |
| 王丽 | 46 |
| 曾华 | 45 |
| 王芳 | 47 |
+-------+------+
6 rows in set (0.00 sec)
39.查询student表中最大和最小的sbirthday日期值
SELECT MAX(sbirthday) ,MIN(sbirthday) FROM student
-->最终结果
mysql> SELECT MAX(sbirthday) ,MIN(sbirthday) FROM student;
+---------------------+---------------------+
| MAX(sbirthday) | MIN(sbirthday) |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
40.以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT *
FROM student
ORDER BY class DESC,sbirthday DESC;
-->最终结果
mysql> SELECT *
-> FROM student
-> ORDER BY class DESC,sbirthday DESC;
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set (0.00 sec)
41.查询“男”教师及其所上的课程
SELECT tname,cname
FROM teacher,course
WHERE tsex='男' AND teacher.tno=course.tno
-->最终结果
mysql> SELECT tname,cname
-> FROM teacher,course
-> WHERE tsex='男' AND teacher.tno=course.tno;
+-------+----------+
| tname | cname |
+-------+----------+
| 李诚 | 操作系统 |
| 张旭 | 数字电路 |
+-------+----------+
2 rows in set (0.00 sec)
42.查询最高分同学的sno、cno和degree列
#方法一:
SELECT sno,cno,degree
FROM score
WHERE degree=(SELECT MAX(degree) FROM score);
-->最终结果
mysql> SELECT sno,cno,degree
-> FROM score
-> WHERE degree=(SELECT MAX(degree) FROM score);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
+-----+-------+--------+
1 row in set (0.00 sec)
#方法二:
SELECT sno,cno,degree
FROM score
HAVING degree = (SELECT MAX(degree) FROM score);
-->最终结果
mysql> SELECT sno,cno,degree
-> FROM score
-> HAVING degree = (SELECT MAX(degree) FROM score);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92.0 |
+-----+-------+--------+
1 row in set (0.00 sec)
43.查询和“李军”同性别的所有同学的sname
SELECT sname
FROM student
WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') AND sname NOT IN ('李军');
-->最终结果
mysql> SELECT sname
-> FROM student
-> WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') AND sname NOT IN ('李军');
+-------+
| sname |
+-------+
| 陆君 |
| 匡明 |
| 曾华 |
+-------+
3 rows in set (0.00 sec)
44.查询和“李军”同性别并同班的同学sname
#方式1
SELECT sname
FROM student
WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')
AND class=(SELECT class FROM student WHERE sname='李军')
AND sname NOT IN ('李军')
-->最终结果
mysql> SELECT sname
-> FROM student
-> WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')
-> AND class=(SELECT class FROM student WHERE sname='李军')
-> AND sname NOT IN ('李军') ;
+-------+
| sname |
+-------+
| 曾华 |
+-------+
1 row in set (0.00 sec)
#方法二:
SELECT sname
FROM student
WHERE (ssex,class)
IN(SELECT ssex,class FROM student WHERE sname='李军')
AND sname NOT IN ('李军');
-->最终结果
mysql> SELECT sname
-> FROM student
-> WHERE (ssex,class)
-> IN(SELECT ssex,class FROM student WHERE sname='李军')
-> AND sname NOT IN ('李军');
+-------+
| sname |
+-------+
| 曾华 |
+-------+
1 row in set (0.00 sec)
45.查询所有选修“计算机导论”课程的“男”同学的成绩表
#方式1
SELECT score.*
FROM score,student,course
WHERE score.sno=student.sno AND score.cno=course.cno
AND course.cname='计算机导论' AND student.ssex='男';
-->最终结果
mysql> SELECT score.*
-> FROM score,student,course
-> WHERE score.sno=student.sno AND score.cno=course.cno
-> AND course.cname='计算机导论' AND student.ssex='男';
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 64.0 |
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 108 | 3-105 | 78.0 |
+-----+-------+--------+
4 rows in set (0.00 sec)
#方式2
SELECT *
FROM score
WHERE sno IN (SELECT sno FROM student WHERE ssex='男')
AND cno IN (SELECT cno FROM course WHERE cname='计算机导论');
-->最终结果
mysql> SELECT *
-> FROM score
-> WHERE sno IN (SELECT sno FROM student WHERE ssex='男')
-> AND cno IN (SELECT cno FROM course WHERE cname='计算机导论');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 64.0 |
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 108 | 3-105 | 78.0 |
+-----+-------+--------+
4 rows in set (0.00 sec)
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/194112.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...