江南大学物联网工程学院数据库课程实验一作业1实验报告

--创建并使用该数据库
CREATE DATABASE test_one;
USE test_one;
--创建学生表 s
CREATE TABLE s(
snum VARCHAR(3) PRIMARY KEY, --将学生学号设置为主键
sname VARCHAR(5) NOT NULL, --非空约束（下同）
age INT NOT NULL,
sex VARCHAR(3) NOT NULL
);
--向学生表 s 添加测试字段
INSERT INTO s
-- VALUES('001','张三',20,'男');
-- VALUES('002','李四',19,'男');
-- VALUES('003','杨兰',19,'女');
-- VALUES ('004','韩梅',18,'女');
-- VALUES ('005','王丹',21,'女');
-- VALUES ('006','李强',21,'男');
VALUES ('007','江杰',19,'男');
--创建课程表
CREATE TABLE c(
cnum VARCHAR(4) PRIMARY KEY, -- 将课程课号设置为主键
cname VARCHAR(15) NOT NULL, -- 非空约束（下同）
credits INT NOT NULL,
teacher VARCHAR(5) NOT NULL
);
--向课程表 c 添加测试字段
INSERT INTO c
-- VALUES('C1','多媒体',3,'陈扬')；
-- VALUES('C2','数据库',4,'王帆');
VALUES('C3','Java',3,'徐可');
--创建选课表 sc
CREATE TABLE sc(
snum VARCHAR(3),
cnum VARCHAR(4),
score INT,
--设置选课表的外键，使得该表中的 snum 和 cnum 受约束
CONSTRAINT snum_fkey FOREIGN KEY (snum) REFERENCES s(snum),
CONSTRAINT cnum_fkey FOREIGN KEY (cnum) REFERENCES c(cnum),
CONSTRAINT unique_sc UNIQUE (snum,cnum) --唯一性约束组
);
--向选课表 sc 添加测试字段
INSERT INTO sc
-- VALUES('002','C1',92);
-- VALUES('002','C2',97);
-- VALUES('003','C1',99);
-- VALUES('003','C2',88);
-- VALUES('004','C2',90);
-- VALUES('005','C2',90);
-- VALUES('006','C2',86);
-- VALUES('007','C1',86);
VALUES('003','C3',82);
--检索年龄介于 18~20 之间的女学生的学号和姓名
SELECT snum, sname FROM s
WHERE age BETWEEN 18 AND 20 AND sex = '女';
--检索选修了课程号为'C2'，分数在 90 或 90 以上的学生姓名
SELECT s.sname FROM s 
JOIN sc --JOIN-ON 语句为内连接，该语法是 sql99 语法
ON s.snum = sc.snum --连接条件接在 ON 后面
WHERE sc.cnum = 'C2' AND score >= 90;
--检索李强同学所修课程的课程名称
SELECT c.cname FROM s 
JOIN sc --同上说明，为内连接 sql99 语法
ON s.snum = sc.snum --依据两表中的 snum 关联 s 和 sc 两表
JOIN c --再内连接 c 表
ON sc.cnum = c.cnum --依据两表中的 cnum 关联 sc 和 c 两表
WHERE s.sname = '李强';
--检索选修了数据库而没有选修多媒体的学生学号
SELECT s.snum FROM s
WHERE s.snum IN ( --子查询获取选修了数据库的学生学号
SELECT sc.snum FROM sc
WHERE sc.cnum = (--子查询获取数据库对应的课程课号
SELECT c.cnum FROM c
WHERE c.cname = '数据库'
)
)AND s.snum NOT IN (--子查询获取选修了多媒体的学生学号
SELECT sc.snum FROM sc
WHERE sc.cnum = (--子查询获取多媒体对应的课程课号
SELECT c.cnum FROM c
WHERE c.cname = '多媒体'
)
);
--检索没有选修数据库的学生名单
SELECT * FROM s 
WHERE s.snum NOT IN (--子查询获取选修了数据库的学生学号
SELECT sc.snum FROM sc
WHERE sc.cnum IN (--子查询获取数据库对应的课程课号
SELECT c.cnum FROM c
WHERE c.cname = '数据库'
)
);
--检索至少选修了两门课程的学生名单
SELECT * FROM s 
WHERE s.snum IN (--子查询获取至少选修了两门课程的学生学号
SELECT s1.snum
FROM sc AS s1,sc AS s2 --物理上一表，逻辑上两虚拟表
WHERE s1.snum = s2.snum --学号相同
AND s1.cnum <> s2.cnum --并且课号不同
);
--检索选修的课程包含李强同学所修课程的学生名单
SELECT * FROM s AS s1
WHERE s1.snum IN (
--子查询获取选修的课程包含李强同学所修课程的学生学号
SELECT sc1.snum FROM sc AS sc1
WHERE sc1.cnum IN (
--子查询获取李强同学所修课程的课程课号
SELECT sc2.cnum FROM sc AS sc2
WHERE sc2.snum = (--子查询获取李强同学的学号
SELECT s2.snum FROM s AS s2
WHERE s2.sname = '李强'
)
)
)AND s1.sname <> '李强'; -- 排除李强自己
--检索既选修了课程号为 C1 的课程又选修了课程号为 C2 的课程的学生学号
SELECT c1.snum 
--子查询获取两张虚拟表，其一选了 C1、其二选了 C2 的学生学号
FROM ( SELECT snum FROM sc WHERE sc.cnum = 'C1') AS c1,
(SELECT snum FROM sc WHERE sc.cnum = 'C2') AS c2
WHERE c1.snum = c2.snum; -- WHERE 条件查询，获得两张虚拟表的交集