作业7
大约 18 分钟
作业7
1.基本sql查询语句
基本sql查询语句
CREATE TABLE student (
id INT, -- 编号
name VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO
student (id,name,age,sex,address,math,english)
VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
-- 查询 math 分数大于 80 分的学生
-- 查询 english 分数小于或等于 80 分的学生
-- 查询 age 等于 20 岁的学生
-- 查询 age 不等于 20 岁的学生
-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
-- 查询 id 是 1 或 3 或 5 的学生
-- 查询 english 成绩大于等于 75，且小于等于 90 的学生
-- 查询姓马的学生
-- 查询姓名中包含'德'字的学生
2.经典sql查询语句(含答案)
前言
SQL语句 👇
-- 经典SQL练习题
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', '助教', '电子工程系');
- 查询student表中的所有记录的sname,ssex和class列。
SELECT sname, ssex, class
FROM student;
- 查询教师所有的单位即不重复的depart列。
SELECT DISTINCT depart
FROM teacher;
- 查询student表的所有记录。
SELECT *
FROM student;
- 查询score表中成绩在60到80之间的所有记录。
SELECT *
FROM score
WHERE degree BETWEEN 60 AND 80;
- 查询score表中成绩为85，86或88的记录。
SELECT *
FROM score
WHERE degree IN (85, 86, 88);
- 查询student表中“95031”班或性别为“女”的同学记录。
SELECT *
FROM student
WHERE class = '95031' OR ssex = '女';
- 以class降序查询student表的所有记录。
SELECT *
FROM student
ORDER BY class DESC;
- 以cno升序、degree降序查询score表的所有记录。
SELECT *
FROM score
ORDER BY cno ASC, degree DESC;
- 查询”95031”班的学生人数。
SELECT COUNT(*)
FROM student
WHERE class = '95031';
========以上是单表题目
- 查询Score表中的最高分的学生学号和课程号。
SELECT sno, cno
FROM score
WHERE degree = (SELECT MAX(degree) FROM score);
- 查询“3-105”号课程的平均分。
SELECT AVG(degree)
FROM score
WHERE cno = '3-105';
- 查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno, AVG(degree)
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(DISTINCT sno) >= 5;
- 查询最低分大于70，最高分小于90的Sno列。
SELECT sno
FROM score
WHERE degree > 70 AND degree < 90;
- 查询所有学生的sname、cno和degree列。
SELECT sname, cno, degree
FROM score
JOIN student ON score.sno = student.sno;
- 查询所有学生的sno、cname和degree列。
SELECT sno, cname, degree
FROM score
JOIN course ON score.cno = course.cno;
- 查询所有学生的sname、cname和degree列。
SELECT sname, cname, degree
FROM score
JOIN student ON score.sno = student.sno
JOIN course ON score.cno = course.cno;
- 查询“95033″班所选课程的平均分。
SELECT AVG(degree)
FROM score
JOIN student ON score.sno = student.sno
WHERE student.class = '95033';
- 假设使用如下命令建立了一个grade表：
create table grade(
low decimal(3,0), -- 最低值
upp int(3), -- 最高值
rankk varchar(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');
- 查询选修“3-105″课程的成绩高于“109″号同学成绩的所有同学的记录。
-- 从学生表（student）中选择需要的信息，包括学号（sno）、姓名（sname）、性别（ssex）、生日（sbirthday）、班级（class）。
-- 使用INNER JOIN将学生表（student）、成绩表（score）、课程表（course）连接起来，通过学生学号（sno）、课程编号（cno）之间的关联。
-- 使用WHERE条件来筛选出选修了“3-105”课程（'3-105'）的学生，并且他们的成绩（degree）高于“109”号同学在同一门课程的成绩。
-- 最终，我们得到的结果是选修“3-105”课程的成绩高于“109”号同学的所有同学的记录。
SELECT s1.sno, s1.sname, s1.ssex, s1.sbirthday, s1.class,c1.cname,sc1.degree
FROM student s1
INNER JOIN score sc1 ON s1.sno = sc1.sno
INNER JOIN course c1 ON sc1.cno = c1.cno
WHERE c1.cno = '3-105'
AND sc1.degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');
- 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
-- 1. 从score表中选择需要的信息，包括学号（sno）和课程编号（cno）。
-- 2. 使用WHERE条件来筛选满足以下两个条件的记录：
-- 2.1学号在score表中出现的次数大于1，即选修了一门以上课程（使用子查询来统计学号出现的次数）。
-- 2.2该记录的分数（degree）不等于（<>）分数最高的记录。
-- 3. 在第二个子查询中，获取每个学号（sno）的最高分数，然后将学号和最高分数与原始表中的记录进行比较，排除了最高分数的记录。
-- 4. 最终，我们得到的结果是选修了一门以上课程的同学中分数不是最高分的记录
SELECT sno, cno
FROM score
WHERE sno IN (SELECT sno FROM score GROUP BY sno HAVING COUNT(*) > 1)
AND (sno, degree) NOT IN (
SELECT sno, MAX(degree)
FROM score
GROUP BY sno
);
- 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
-- 选择所有列
SELECT *
-- 从score表中
FROM score
-- 使用WHERE条件筛选满足以下条件的记录：
-- 1. 分数（degree）高于（>）学号为“109”、课程号为“3-105”的成绩
WHERE degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');
- 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
-- 22
select sno, sname, sbirthday
from student
where year(sbirthday) = (select year(sbirthday) from student where sno = '108'); -- 获取年份year()
- 查询“张旭“教师任课的学生姓名（sname）、课程名称（cname）和成绩（degree）。
-- 23
select *
from score
where cno in (select cno
from teacher, course
where teacher.tno = course.tno and tname = '张旭');
-- 方法2
-- 选择学生姓名（sname）、课程名称（cname）和成绩（degree）
SELECT s.sname, c.cname, sc.degree
-- 从student表（s）、course表（c）和score表（sc）中
FROM student s
JOIN score sc ON s.sno = sc.sno
JOIN course c ON c.cno = sc.cno
-- 使用WHERE条件筛选满足以下条件的记录：
-- 1. 课程的教师编号（tno）等于“张旭”的教师编号
WHERE c.tno = (SELECT tno FROM teacher WHERE tname = '张旭');
- 查询选修某课程的同学人数多于5人的教师姓名。
-- 方式1：
--
-- 使用了子查询，内部查询了与课程表和成绩表相关的数据，然后在外部查询中使用IN子句进行筛选。
-- 子查询中包括了联接操作和GROUP BY，这会增加查询的复杂性和计算成本。
-- 子查询可能需要执行多次，因为在WHERE子句中使用IN时，内部查询可能需要为每个外部查询的行执行一次。
-- 方式1：
select tname
from teacher
where teacher.tno in (
select tno
from course, score
where course.cno = score.cno
group by score.cno
having count(*) > 5);
-- 方式2：
-- 使用了JOIN操作，将教师表、课程表和成绩表联接在一起，然后通过ON条件将它们关联。
-- 在GROUP BY子句中对成绩表的课程编号进行分组。
-- 结果更直观且易于理解，因为所有相关的表和条件都在一起，无需嵌套子查询。
--
select tname
from teacher join (course, score)
on teacher.tno = course.tno and course.cno = score.cno
group by score.cno
having count(*) > 5;
-- 综上所述，方式2更具可读性和效率，因为它将所有操作集中在一个查询中，减少了查询的复杂性，提高了查询性能。
-- 方式1虽然也能实现相同的结果，但更容易导致性能问题，并且在代码维护和理解上更具挑战性。
- 查询95O33班和95031班全体学生的记录。
-- 25
select *
from student
where class = '95033' or class = '95031';
- 查询存在有85分以上成绩的课程Cno.
-- 26
select distinct cno
from score
where degree > 85;
-- 方法2
select cno
from score
group by cno
having max(degree) > 85;
-- 方法1：
-- 使用DISTINCT关键字来筛选不重复的课程编号，这需要在内存中进行去重操作，可能会增加计算成本。
-- 通过WHERE子句过滤出成绩高于85分的记录。
--
-- 方法2：
-- 使用GROUP BY对课程编号进行分组，然后使用HAVING子句来筛选满足条件的组。
-- 在HAVING子句中，使用max(degree)来找到每门课程的最高分，并将其与85进行比较。
-- 方法2更好的原因：
-- 方法2在一个查询中完成了所有操作，不需要额外的去重步骤，减少了内存和计算的开销。
-- 方法2更符合SQL的标准结构，将聚合操作和筛选操作组合在一起，更容易理解和维护。
- 查询出“计算机系“教师所教课程的成绩表。
-- 查询出“计算机系“教师所教课程的成绩表。
-- 方式1
select *
from score
where score.cno in (
select cno
from course
where course.tno in( select teacher.tno from teacher where depart = '计算机系'));
-- 方法2
select *
from score join (course, teacher)
on score.cno = course.cno and course.tno = teacher.tno
where depart = '计算机系';
-- 方法3，
select *
from score
where score.cno in(
select cno from course join teacher on course.tno = teacher.tno where depart = '计算机系');
-- 方法1使用了子查询来获取计算机系教师教授的课程编号，然后将这些课程编号与成绩表进行比较。这需要执行两次子查询。性能方面，子查询的效率取决于数据库优化器的优化能力，通常情况下，性能较差。
-- 方法2使用了连接（JOIN）来将成绩表、课程表和教师表联接在一起，并通过WHERE子句来筛选“计算机系”的教师。这种方法通常更容易理解，而且只需要一次连接。性能较好。
-- 方法3与方法1类似，使用了子查询和内连接。然而，它执行了更多的连接操作，将课程表和教师表连接在一起，然后将结果与成绩表进行比较。在多连接的情况下，性能可能会迅速下降，因为需要更多的内存和计算资源。
-- 总的来说，方法2通常是性能最好且易于理解的方法，因为它只执行一次连接操作，并在WHERE子句中筛选所需的数据。方法1和方法3可能在性能上稍有差异，但通常来说，方法1使用子查询的性能较差，方法3更好些。然而，性能还取决于数据库优化器和数据库表的索引结构等因素。在实际应用中，可以使用性能分析工具来测试不同方法的性能表现。
- 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT Tname, Prof
FROM teacher
WHERE depart = '计算机系' AND Prof NOT IN (
SELECT Prof
FROM teacher
WHERE depart = '电子工程系'
)
UNION
SELECT Tname, Prof
FROM teacher
WHERE depart = '电子工程系' AND Prof NOT IN (
SELECT Prof
FROM teacher
WHERE depart = '计算机系'
);
--UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
--请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT语句中的列的顺序必须相同
- 查询选修编号为”3-105“课程且成绩至少高于选修编号为”3-245”的同学的Cno、Sno和Degree，并按Degree从高到低次序排序。
select *
from score sc
where cno = '3-105' and degree > (select degree from score sc2 where cno = '3-245' and sc.sno = sc2.sno)
order by degree desc;
- 查询选修编号为“3-105”且成绩高于选修编号为“3-245″课程的同学的Cno、Sno和Degree。
-- 30
select *
from score sc
where cno = '3-105' and degree > (select degree from score sc2 where cno = '3-245' and sc.sno = sc2.sno);
- 查询所有教师和同学的name、sex和birthday。
-- 31
select sname name, ssex sex, sbirthday birthday
from student
union -- 用于合并两个或多个 SELECT 语句的结果集
select tname name, tsex sex, tbirthday birthday
from teacher;
- 查询所有“女”教师和“女”同学的name、sex和birthday。
-- 32
select sname name, ssex sex, sbirthday birthday
from student
where ssex = '女'
union -- 同上
select tname name, tsex sex, tbirthday birthday
from teacher
where tsex = '女';
- 查询成绩比该课程平均成绩低的同学的成绩表。
-- 33
select *
from score sc
where degree < (select avg(degree) from score sc2 where sc.cno = sc2.cno);
- 查询所有任课教师的Tname和Depart。
--方法1
select tname, depart
from teacher, course
where teacher.tno = course.tno;
-- 方法2
select tname, depart
from teacher join course
on teacher.tno = course.tno;
-- 方法3
select tname, depart
from teacher
where tno in(select tno from course);
- 查询所有未讲课的教师的Tname和Depart。
-- 35
select tname, depart
from teacher
where teacher.tno not in (select tno from course); -- not in 方法效率最差
select tname, depart -- 方法2
from teacher left join course
using(tno) -- using 必须等值连接
where isnull(course.tno); -- 判断tno是否为空，为空返回1，否则返回0
select tname, depart -- 方法3，同方法2效率差不多
from teacher
where not exists ( -- exists 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False
select * from course
where teacher.tno = course.tno
);
- 查询至少有2名男生的班号。
-- 36 -- where → group by → having
select class, count(*)
from student
where ssex = '男'
group by class
having count(ssex) > 1;
- 查询Student表中不姓””王”的同学记录。
-- 37
select *
from student
where sname not like '王%';
- 查询Student表中每个学生的姓名和年龄。
-- 38
select sname, year(now()) - year(SBIRTHDAY) age -- now() 表示现在的时间
from student;
- 查询Student表中年龄最大和最小学生的Sbirthday日期值。
-- 39 DATE_FORMAT(日期,'%m-%d') 月-日，该函数用于以不同的格式显示日期/时间数据。
select sname, sbirthday max
from student
where sbirthday = (select max(sbirthday) from student)
union
select sname, sbirthday min
from student
where sbirthday = (select min(sbirthday) from student);
- 以班号和年龄从大到小的顺序查询Student表中的全部记录。
-- 40
select *
from student
order by class desc, date(SBIRTHDAY);
- 查询“男”教师及其所上的课程。
-- 41
select tname, cname
from teacher, course
where tsex = '男' and teacher.tno = course.tno;
-- 方法2
select tname, cname
from teacher join course
on teacher.tno = course.tno
where tsex = '男';
- 查询最高分同学的Sno、Cno和Degree列。
-- 42
select sno, cno, degree
from score
where degree = (select max(degree) from score);
- 查询和“李军”同性别的所有同学的Sname。
-- 43
select sname
from student
where ssex = (select ssex from student where sname = '李军');
- 查询和“李军”同性别并同班的同学Sname。
-- 44
select sname
from student
where ssex = (select ssex from student where sname = '李军')
and class = (select class from student where sname = '李军');
- 查询所有选修“计算机导论”课程的“男”同学的成绩表。
-- 方法1
select *
from student, score, course
where student.ssex = '男' and score.sno = student.sno
and score.cno = course.cno and cname = '计算机导论';
-- 方法2
select *
from score join (student, course)
using (sno, cno)
where ssex = '男' and cname = '计算机导论';
3.数据库多表设计题当天必须完成
数据库多表设计题
根据下列需求，完成表关系设计，无需物理外键和数据
题目: 餐厅菜单管理
设计数据库模型来管理餐厅的菜单。数据库中应包括以下表和关系：
- 餐厅表（Restaurants）：包含餐厅的信息，如名称、地址等。
- 菜单表（Menus）：每个餐厅可以有多个菜单。
- 菜品表（Dishes）：每个菜单包含多个菜品。
- 订单表（Orders）：记录顾客下的订单，与餐厅和菜品建立关系。
题目:顾客订餐管理
设计数据库模型来管理顾客的订餐信息。数据库中应包括以下表和关系：
- 顾客表（Customers）：包含顾客的信息，如姓名、联系方式等。
- 订单表（Orders）：记录顾客下的订单。
- 菜品表（Dishes）：包含可供选择的菜品。
- 菜品订单关系表（DishOrderRelations）：记录每个订单中包含的菜品。
题目: 学校课程管理
设计数据库模型来管理学校的课程和学生。数据库中应包括以下表和关系：
- 学生表（Students）：包含学生的信息，如姓名、学号等。
- 课程表（Courses）：包含学校提供的课程信息。
- 选课表（Enrollments）：学生可以选择多门课程，该表记录了学生的选课信息。
题目: 物流运单管理
设计数据库模型来管理物流公司的运单和车辆。数据库中应包括以下表和关系：
- 运单表（Waybills）：包含每个运单的信息，如货物、起始地、目的地等。
- 车辆表（Vehicles）：包含公司的所有车辆信息。
- 驾驶员表（Drivers）：包含驾驶员的信息。
- 运单分配表（WaybillAssignments）：记录每个运单分配给哪辆车和哪名驾驶员。
4.数据库多表设计题当天必须完成
小说APP
原则上多少张表取决于你自己的理解，言之有理即可，没有标准答案
现在要为一个阅读 app 设计部分后台数据库表，功能原型
1️⃣ (1) 我的书架 - 图1
- 只需考虑书架这个页面可见部分（浏览历史、圈子和下面的书城、分类、福 利，我的都不用考虑）
- 整本书有个阅读进度（最近一次阅读的章节）
2️⃣ (2) 书籍详情 - 图2
只需要考虑详情和目录两部分（笔记不考虑）
- 目录中列出了书的所有章节
- 金庸和梁羽生的两本书都说有多少多少册，这个不考虑，权且认为每本书 分成多个章节即可 注意：每一章节都有各自的阅读进度（显示的是读过段落和本章总段落的百分比）
3️⃣ 3)阅读 - 图3
可以看到每一章节分成若干段落，根据段落确定阅读进度 
4️⃣ (4) 想法 - 图4
- 以段落为单位可以进行评论（称为想法），点击每个段落最后的按钮后进入此段落的想法
- 想法可以有多条，每个想法均可以被点赞（不必考虑转发）
