几个简单的SQL代码

2019-11-21 by fffasttime·0评论

几道例题，包含了SQL的常见用法。

数据库SPJ包含4个表：
供应商表S（代码Sno，名称Sname，状态Status，所在城市City），
零件表P（代码Pno，名称Pname，颜色Color，重量Weight），
工程项目表J（代码Jno，名称Jname，所在城市City），
供应情况表Spj（供应商代码Sno，零件代码Pno，项目代码Jno，数量Qty）。

 
-- 查询所有供应商的名称和所在城市 
SELECT Sname, City
FROM S;	
 
-- 找出重量大于14的零件的名称,颜色,重量
SELECT Pname, Color, Weight
FROM P
WHERE Weight>14;
 
-- 找出工程项目“J2”使用的各种零件号码及其数量总和（取一个别名）
-- 一个工程可能用到同一个零件多处，需要group一下
SELECT Pno, SUM(Qty) van
FROM Spj
WHERE Jno='J2'
GROUP BY Pno
 
-- 找出使用“天津”供应商供应零件的工程号码
SELECT Jno
FROM S, Spj
WHERE S.Sno=Spj.Sno AND S.City='天津'	
 
-- 找出没有使用“天津”供应商供应“红”色零件的工程号码
-- 这个要注意，工程号码满足“天津”和“红”两个条件就要被排除
SELECT Jno
FROM J
WHERE Jno NOT IN 
(SELECT Jno FROM S, P, Spj 
    WHERE S.Sno=Spj.Sno AND P.Pno=Spj.Pno AND S.City='天津' AND P.Color='红');
 
-- 求至少用了供应商“S1”所供应的全部零件的工程号
-- not exists是子查询结果为空则TRUE
-- 这里使用差集判断，若(S1供应集合-工程使用集合)不空，则还有没用的，不满足条件
SELECT DISTINCT Jno
FROM Spj AS sa
WHERE NOT EXISTS ((SELECT Pno FROM Spj AS sb WHERE sb.Sno='S1') EXCEPT (SELECT Pno FROM Spj AS sb WHERE sb.Pno=sa.Pno));

数据库XJGL包含3个表：
学生表Student(学号Sno，姓名Sname，性别Ssex，年龄Sage，系别Sdept),
课程表Course(课程号Cno，课程名称Cname，先修课Cpno，学分Ccredit),
成绩表Sc(学号Sno,课程号Cno，成绩Grade)

 
-- 查询年龄不大于19的“女”生的学号和姓名
SELECT Sno, Sname
FROM Student
WHERE Sage<=19 AND Ssex='女'
 
-- 查询“CS”系年龄大于本系平均年龄的学生的学号、姓名和年龄
SELECT sno, sname, sage
FROM student AS s1
WHERE s1.sdept='cs' AND sage>(SELECT avg(sage) FROM student AS s2 WHERE s2.sdept='cs')	
 
-- 将一个新学生元组（学号：200215228；姓名：陈冬；性别：男；年龄：18岁;所在系：IS）插入到Student表中
INSERT INTO student VALUES (200215228, '陈冬', '男', 18, 'IS')	
 
-- 删除学号为“200215122”的学生的选课记录
DELETE FROM sc WHERE sno=200215122
 
-- 将所有学生的年龄增加一岁
UPDATE student SET sage=sage+1	
 
-- 查询至少选修了学号为“200215121”的学生所选全部课程的学生学号
-- 使用差集判断
-- 还有一种三层嵌套的写法，比较麻烦
SELECT sno
FROM student AS s1
WHERE NOT EXISTS ((SELECT cno FROM sc WHERE sno=200215121) EXCEPT 
(SELECT cno FROM sc,student AS s2 WHERE s1.sno=s2.sno AND sc.sno=s1.sno))

发表评论取消回复