-----------------
---- 2011-06-25-10:47 ---
------------------
--3.3 查询
--3.3.4 嵌套查询
-- 带有 IN 谓词
SELECT Sname, Sno
FROM Student
WHERE Sno IN (
SELECT Sno
FROM SC
WHERE Cno = 'c02'
)
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN (
SELECT Sdept
FROM Student
WHERE Sname = '张三'
)
--带有比较运算符的，一个学生只能在一个系里学习
/*
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept =
SELECT Sdept
FROM Student
WHERE Sname = '张三' ？？
*/
--用自身连接完成该查询
SELECT S1.Sno, S1.Sname, S1.Sdept
FROM Student S1, Student S2
WHERE S1.Sdept = S2.Sdept AND S2.Sname = '张三'
--查询选修了课程名为 “JAVA程序设计” 的学生学号和姓名
SELECT Sno, Sname
FROM Student
WHERE Sno IN (
SELECT Sno
FROM SC
WHERE Cno IN (
SELECT Cno
FROM Course
WHERE Cname = 'JAVA程序设计'
)
)
SELECT S1.Sno, S1.Sname
FROM Student S1, Course C1, SC
WHERE S1.Sno = SC.Sno AND SC.Cno = C1.Cno AND C1.Cname = 'JAVA程序设计'
--带有ANY或ALL谓词的子查询
SELECT Sage, Sno, Sname
FROM Student
WHERE Sage < ANY (
SELECT Sage
FROM Student
WHERE Sdept = 'CS'
)
SELECT Sage, Sname, Sno, Sdept
FROM Student
WHERE Sage < (
SELECT MAX(Sage)
FROM Student
WHERE Sdept = 'MA'
)
--AND Sdept <> 'MA'
SELECT Sname, Sage, Sdept
FROM Student
WHERE Sage < ALL(
SELECT Sage
FROM Student
WHERE Sdept= 'MA'
)
--AND Sdept <> 'MA'
SELECT Sname,Sage
FROM Student
WHERE Sage < (
SELECT MIN(Sage)
FROM Student
WHERE Sdept= 'MA'
)
--AND Sdept <> 'MA'
--3.带有EXISTS谓词的子查询
SELECT Sname
FROM Student
WHERE EXISTS (
SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno = 'c02'
)
SELECT Sno, Sname, Sdept
FROM Student S1
WHERE EXISTS (
SELECT *
FROM Student S2
WHERE S1.Sdept = S2.Sdept AND S2.Sname = '张三'
)
SELECT *
FROM Student
WHERE NOT EXISTS (
SELECT *
FROM Course
WHERE NOT EXISTS (
SELECT *
FROM SC
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno
)
AND EXISTS (
SELECT *
FROM SC
WHERE SC.Sno = '10002' AND SC.Cno = Course.Cno
)
)
-- 3.3.5 集合查询
--标准SQL直接支持的集合操作种类 -- 并操作(UNION)
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 24
--等价于
SELECT *
FROM Student
WHERE Sage <= 24 OR Sdept = 'CS'
SELECT Sno
FROM SC
WHERE Cno = 'c02'
UNION
SELECT Sno
FROM SC
WHERE Cno = 'c01' --这是并的操作
SELECT DISTINCT Sno
FROM SC
WHERE Cno = 'c01' OR Cno = 'c02'
CREATE TABLE Teacher
(
Tno varchar(5) PRIMARY KEY,
Tname varchar(20) NOT NULL,
Tsex varchar(2) DEFAULT '男',
Tage INT DEFAULT '0' CHECK(Tage >= 0 AND Tage <= 100),
Tdept varchar(50),
);
GO
-- 向Teacher表插入数据
INSERT INTO Teacher(Tno, Tname, Tsex, Tage, Tdept) VALUES('10011', '张老师', '男', 19, 'CS');
INSERT INTO Teacher(Tno, Tname, Tsex, Tage, Tdept) VALUES('10012', '李老师', '男', 21, 'MA');
INSERT INTO Teacher(Tno, Tname, Tsex, Tage, Tdept) VALUES('10013', '王老师', '女', 25, 'CS');
INSERT INTO Teacher(Tno, Tname, Tsex, Tage, Tdept) VALUES('10014', '孙老师', '男', 21, 'IS');
INSERT INTO Teacher(Tno, Tname, Tsex, Tage, Tdept) VALUES('10016', '赵七七', '男', 20, 'IS');
GO
--DROP TABLE Teacher
SELECT Sname
FROM Student
UNION
SELECT Tname
FROM Teacher
--(2), 实现 交 操作
SELECT *
FROM Student
WHERE Sage <= 24 AND Sdept = 'CS'
SELECT Sno
FROM SC
WHERE Cno = 'c01' AND Sno IN (
SELECT Sno
FROM SC
WHERE Cno = 'c02'
)
--(3), 实现 差 操作
SELECT *
FROM Student
WHERE Sdept = 'CS' AND Sage > 19
SELECT Sname
FROM Student
WHERE Sname NOT IN (
SELECT Tname
FROM Teacher
)
--(4), 对集合操作结果的排序
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19
ORDER BY 3 DESC
-- 3.4, 数据更新
INSERT
INTO Student(Sno, Sname)
VALUES('10009', '高尚')
INSERT
INTO Course(Cno, Cname, Ccredit)
VALUES('c09', 'TCPIP', 6)
--建表
CREATE TABLE JST (
Sdept CHAR(15),
JVage INT
)
--DROP TABLE JST
--插入数据
INSERT
INTO JST(Sdept, JVage)
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept
--3.4 数据更新
UPDATE Student
SET Sage = 25
WHERE Sno = 10002
UPDATE Student
SET Sage = Sage + 1
UPDATE Student
SET Sage = Sage + 1
WHERE Sdept = 'CS'
UPDATE SC
SET Grade = 0
WHERE 'CS' = (
SELECT Sdept
FROM Student
WHERE SC.Sno = Student.Sno
)
DELETE
FROM Student
WHERE Sno = '10009'
DELETE
FROM SC
WHERE Cno = 'c02'
DELETE
FROM SC
WHERE 'CS' = (
SELECT Sdept
FROM Student
WHERE SC.Sno = Student.Sno
)
-- 3.5 视图
-- 3.5.1 定义视图
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS'
DROP VIEW IS_Student
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION
/*
WITH CHECK OPTION
透过视图进行增删改操作时，不得破坏视
图定义中的谓词条件
（即子查询中的条件表达式）
*/
DROP VIEW IS_S1
CREATE VIEW IS_S1(Sno, Sname, Grade)
AS
SELECT Student.Sno, Sname, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Sdept = 'IS' AND Cno = 'c03'
--基于视图的视图
CREATE VIEW IS_S2
AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade >= 90
CREATE VIEW BT_S(Sno, Sname, Sbirth)
AS
SELECT Sno, Sname, 2000-Sage
FROM Student
CREATE VIEW S_G(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
DROP VIEW S_G
DELETE
FROM SC
WHERE Sno = ANY(
SELECT Sno
FROM Student
WHERE Sdept = 'CS'
)
--3.5.2 查询视图
SELECT Sno, Sage
FROM IS_Student
WHERE Sage < 30
SELECT SC.Sno, Sname
FROM IS_Student, SC
WHERE IS_Student.Sno =SC.Sno AND
SC.Cno= 'c01'
SELECT *
FROM S_G
WHERE Gavg > 70
CREATE VIEW S_G(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)--AS ASD
FROM SC
GROUP BY Sno -- 聚合不能出现在 WHERE 中
--HAVING AVG(Grade) > 70
-- 3.5.3 更新视图
UPDATE IS_Student
SET Sname = '高蕾'
WHERE Sno = '10004'
UPDATE Student
SET Sname = '尚尚'
WHERE Sno = '10004'
WHERE Sno = '10004'
INSERT
INTO IS_Student
VALUES('10014', '亮', 21)
--更新视图的限制
-- 一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义
-- 地转换成对相应基本表的更新(对两类方法均如此)
-- 视图 S_GG 为不可更新视图
CREATE VIEW S_GG(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
CREATE VIEW GOOD_SG
AS
SELECT Sno, Cno, Grade
FROM SC
WHERE Grade > (
SELECT AVG(Grade)
FROM SC
)
-- 3.5.4 视图的作用
--四个, 1, 简化用户操作
-- 小结
-- 第三章, 关系数据库标准语言SQL
/*
SQL的特点
1. 综合统一
2. 非过程化
3. 面向集合的操作方式
4. 同一种语法结构提供两种使用方式
5. 语言简捷，易学易用
*/
-- 交互式 SQL