[DB] SQL 复习2

-----------------
----  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