SQL 语句练习

约 2494 字大约 8 分钟

2024-08-10

SQL数据脚本

-- MySQL dump 10.13  Distrib 5.7.17, for macos10.12 (x86_64)
--
-- Host: localhost    Database: learn_sql_pdai_tech
-- ------------------------------------------------------
-- Server version	5.7.28

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `course`
--

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
  `no` varchar(5) NOT NULL,
  `name` varchar(10) NOT NULL,
  `teacher_no` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `course`
--

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES ('3-105','计算机导论','825'),('3-245','操作系统','804'),('6-166','数据电路','856'),('9-888','高等数学','100');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `score`
--

DROP TABLE IF EXISTS `score`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `score` (
  `student_no` varchar(3) NOT NULL,
  `course_no` varchar(5) NOT NULL,
  `degree` decimal(10,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `score`
--

LOCK TABLES `score` WRITE;
/*!40000 ALTER TABLE `score` DISABLE KEYS */;
INSERT INTO `score` VALUES ('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),('101','6-166',85.0),('107','6-106',79.0),('108','3-105',78.0),('108','6-166',81.0);
/*!40000 ALTER TABLE `score` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `no` varchar(3) NOT NULL,
  `name` varchar(4) NOT NULL,
  `sex` varchar(2) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `class` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES ('108','曾华','男','1977-09-01 00:00:00','95033'),('105','匡明','男','1975-10-02 00:00:00','95031'),('107','王丽','女','1976-01-23 00:00:00','95033'),('101','李军','男','1976-02-20 00:00:00','95033'),('109','王芳','女','1975-02-10 00:00:00','95031'),('103','陆君','男','1974-06-03 00:00:00','95031');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teacher`
--

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
  `no` varchar(3) NOT NULL,
  `name` varchar(4) NOT NULL,
  `sex` varchar(2) NOT NULL,
  `birthday` datetime NOT NULL,
  `prof` varchar(6) DEFAULT NULL,
  `depart` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teacher`
--

LOCK TABLES `teacher` WRITE;
/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
INSERT INTO `teacher` VALUES ('804','李诚','男','1958-12-02 00:00:00','副教授','计算机系'),('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系'),('825','王萍','女','1972-05-05 00:00:00','助教','计算机系'),('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系');
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

练习

1、查询 student 表中的所有记录的 name,sex,class 列

SELECT name,sex,class FROM student;

2、查询教师所有的单位即不重复的 depart 列

SELECT DISTINCT depart FROM teacher;

3、查询 student 表的所有记录。 select * from STUDENT;

SELECT * FROM student;

4、查询 score 表中成绩在 60 到 80 之间的所有记录

SELECT * 
FROM score 
WHERE degree > 60 and degree < 80;

5、查询 score 表中成绩为 85，86 或 88 的记录。

SELECT * 
FROM score 
WHERE degree = 85 or degree = 86 or degree = 88;

6、查询 student 表中 “95031” 班或性别为 “女” 的同学记录

SELECT * 
FROM student 
WHERE class = '95031' or sex = '女';

7、以 class 降序查询 student 表的所有记录

SELECT * 
FROM student 
ORDER BY class desc;

8、以 course_no 升序、degree 降序查询 Score 表的所有记录

SELECT * 
FROM score 
ORDER BY course_no asc, degree desc;

9、查询 “95031” 班的学生人数

SELECT count(*) 
FROM student 
WHERE class = '95031';

10、查询 score 表中的最高分的学生学号和课程号

SELECT student_no,course_no 
FROM score 
WHERE degree = (
	SELECT max(degree) FROM score
);

11、查询 ‘3-105’ 号课程的平均分

SELECT AVG(degree) 
FROM score 
WHERE course_no = '3-105';

12、查询 score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数

SELECT AVG(degree),course_no 
FROM score 
WHERE course_no like '3%'
GROUP BY course_no
HAVING count(*) >= 5;

13、查询最低分大于 70，最高分小于 90 的 student_no 列

SELECT student_no
FROM score
GROUP BY student_no
HAVING min(degree) > 70 and max(degree) < 90;

14、查询所有学生的 name、no 和 degree 列

SELECT no,name,degree 
FROM student,score 
WHERE no = student_no;

15、查询所有学生的 no,课程的 name 和 degree 列

SELECT s.no,c.name,sc.degree
FROM student as s
left join score as sc on s.no = sc.student_no
left join course as c on sc.course_no = c.no;

16、查询所有学生的 name, 课程 name 和 degree 列

SELECT s.name,c.name,sc.degree
FROM student as s
left join score as sc on s.no = sc.student_no
left join course as c on sc.course_no = c.no;

17、查询 “95033” 班所选课程的平均分

SELECT avg(degree)
FROM score as sc
where sc.student_no in (
	select no 
    FROM student 
    WHERE class = '95033'
);

18、假设使用如下命令建立了一个 grade 表:

create table grade (
  low  numeric(3, 0),
  upp  numeric(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');

现查询所有同学的 no、课程 no 和 rank 列

SELECT student_no,course_no,g.rank
FROM score s,grade g
WHERE s.degree BETWEEN g.low and g.upp;

19、查询选修 “3-105” 课程的成绩高于 “109” 号同学成绩的所有同学的记录

SELECT *
FROM score
WHERE course_no = '3-105' and degree > (
	SELECT degree 
	FROM score 
	WHERE course_no = '3-105' and student_no = '109'
);

20、查询 score 中选学一门以上课程的同学中分数为非最高分成绩的学生记录

21、查询成绩高于学号为 “109”、课程号为 “3-105” 的成绩的所有记录

SELECT *
FROM score
WHERE course_no = '3-105' and degree > (
	SELECT degree 
	FROM score 
	WHERE course_no = '3-105' and student_no = '109'
);

22、查询和学号为108的同学同年出生的所有学生的 Sno、Sname 和 Sbirthday 列

SELECT no,name,birthday
FROM student
WHERE year(birthday) = (
	SELECT year(birthday)
	from student 
	WHERE no = '108'
);

23、查询“张旭“教师任课的学生成绩

SELECT *
FROM score
WHERE course_no in (
	SELECT c.no
	FROM course c
	inner join teacher t on c.teacher_no = t.no and t.name  = '张旭'
);

24、查询选修某课程的同学人数多于 5 人的教师姓名

SELECT name
FROM teacher
WHERE no in (
	SELECT teacher_no 
	FROM course
	where no = (
		SELECT course_no 
		FROM score 
		GROUP BY course_no 
		HAVING count(course_no) > 5
	)
);

25、查询 95033 班和 95031 班全体学生的记录

SELECT *
FROM student
WHERE class in ('95033','95031');

26、查询存在有 85 分以上成绩的课程 no

SELECT DISTINCT course_no
FROM score
WHERE degree > 85;
或
SELECT course_no
FROM score
GROUP BY course_no
HAVING max(degree) > 85;

27、查询出“计算机系“教师所教课程的成绩表

SELECT *
FROM score
WHERE course_no in (
	SELECT c.no
	FROM course c
	INNER JOIN teacher t on c.teacher_no = t.no
	WHERE t.depart = '计算机系'
);

28、查询“计算机系”与“电子工程系“不同职称的教师的 name 和 prof

SELECT name
FROM teacher
WHERE depart = '计算机系' and prof not in (
	SELECT prof
	FROM teacher
	WHERE depart = '电子工程系'
);

29、查询选修编号为 “3-105“ 课程且成绩至少高于选修编号为 “3-245” 的同学的学生 no、课程 no 和 degree,并按 degree 从高到低次序排序

select
  student_no,
  course_no,
  degree
from score
where course_no = '3-105' and degree > any (
  select degree
  from score
  where course_no = '3-245'
)
order by degree desc;

30、查询选修编号为 “3-105” 且成绩高于选修编号为 “3-245” 课程的同学的 student_no、course_no 和 degree

select
  student_no,
  course_no,
  degree
from score
where course_no = '3-105' and degree > ALL (
  select degree
  from score
  where course_no = '3-245'
)
order by degree desc;

31、查询所有教师和同学的 name、sex 和 birthday

select name, sex,birthday
from teacher
union
select name,sex,birthday
from student;

32、查询所有“女”教师和“女”同学的 name、sex 和 birthday

select name, sex,birthday
from teacher
WHERE sex = '女'
union
select name,sex,birthday
from student
WHERE sex = '女';

33、查询成绩比该课程平均成绩低的同学的成绩表。

SELECT a.*
FROM score a
WHERE degree < (SELECT AVG(degree)
                FROM score b
                WHERE a.course_no = b.course_no);

34、查询所有任课教师的 name 和 depart

SELECT name,depart
FROM teacher t
WHERE EXISTS (
	SELECT teacher_no
	FROM course c
	WHERE c.teacher_no = t.no
);

35、查询所有未讲课的教师的 name 和 depart

SELECT name,depart
FROM teacher
WHERE no not in (
	SELECT teacher_no
	FROM course
);

36、查询至少有 2 名男生的班号

SELECT class
FROM student
WHERE sex = '男'
GROUP BY class
HAVING count(sex) > 1;

37、查询 student 表中不姓 “王” 的同学记录

SELECT *
FROM student
WHERE name not like '王%';

38、查询 student 表中每个学生的姓名和年龄

SELECT 
name,
year(now()) - year(birthday) as age
FROM student;

39、查询 student 表中最大和最小的 birthday 日期值

SELECT min(birthday),max(birthday)
FROM student;

40、以班号和年龄从大到小的顺序查询 student 表中的全部记录

SELECT * 
FROM student
ORDER BY class desc,year(now()) - year(birthday) desc;

41、查询“男”教师及其所上的课程。

SELECT *
FROM teacher t
INNER JOIN course c on t.no = c.teacher_no
WHERE t.sex = '男';

42、查询最高分同学的 no、课程 no 和 degree 列

SELECT student_no,course_no,degree
FROM score
WHERE degree = (
	SELECT max(degree)
	FROM score
);

43、查询和“李军”同性别的所有同学的 name

SELECT name
FROM student
WHERE sex = (
	SELECT sex
	FROM student
	WHERE name = '李军'
);

44、查询和“李军”同性别并同班的同学 name

SELECT name
FROM student
WHERE (sex,class) = (
	SELECT sex,class
	FROM student
	WHERE name = '李军'
);

45、查询所有选修“计算机导论”课程的“男”同学的成绩表

SELECT sc.*
FROM score sc
left join student s on sc.student_no = s.no
WHERE sc.course_no = (
	SELECT no 
	FROM course
	WHERE name = '计算机导论'
) and s.sex = '男';

46、使用游标方式来同时查询每位同学的名字，他所选课程及成绩

47、声明触发器指令，每当有同学转换班级时执行触发器显示当前和之前所在班级

48、删除已设置的触发器指令

DROP TRIGGER display_class_changes;