2022关于sql sever的一些问题

use ST
--创建数据库ST
--create database ST
--创建数据库ST下的模式ST
--create schema ST
--创建表Student
--create table ST.ST.Student(
--Sno char(9)primary key,
--Sname char(20)unique,
--Ssex char(2),
--Sage smallint,
--Sdept char(20)
--);
--创建表Course
--create table ST.ST.Course(
--Cno char(4)primary key,
--Cname char(40)not null,
--Cpno char(4),
--Ccredit smallint,
--foreign key(Cpno)references ST.Course(Cno));
--创建表SC
--create table ST.ST.SC(
--Sno char(9),
--Cno char(4),
--Grade smallint,
--primary key(Sno,Cno),
--foreign key(Sno)references ST.Student(Sno),
--foreign key(Cno)references ST.Course(Cno));
--删除表SC
--drop table ST.SC
-------------------------------------------------修改表-------------------------------------------------
--向学生表增加列（入学时间）
--alter table ST.Student add S_entrance DATE
--删除学生表中入学时间列
--alter table ST.Student drop column S_entrance
--将年龄由字符型 改为 整型
--alter table .ST.Student alter column Sage int
--增加表级约束条件
--alter table .ST.Course add unique(Cname) 
--删除唯一性约束可以将列删除再创建新的列
--直接修改列
--alter table .ST.Course alter column Cname char(40)
------------------------------------------针对索引的操作--------------------------------------------------
--建立学生表中学号列的唯一索引
--CREATE UNIQUE INDEX Stusno ON .ST.Student(Sno)--默认升号
--Course表中按课程号升序建唯一索引
--CREATE UNIQUE INDEX Course ON .ST.Course(Cno)
--SC表中按学号升序和课程号降序建唯一索引
--CREATE UNIQUE INDEX SC ON .ST.SC(Sno ASC,Cno DESC)
--删除课程表中的索引
--DROP INDEX Course ON ST.Course
--CREATE UNIQUE INDEX Coucno ON .ST.Course(Cno)
--修改课程表与选课表中的索引--在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作。
--EXEC sp_rename @objname = 'SC.SC',@newname='SCno',@objtype='index'修改内存
--删除选课表中的索引，只能先删除再创建
--DROP INDEX SC ON ST.SC
--CREATE UNIQUE INDEX SCno ON .ST.SC(Sno ASC,Cno DESC)
--------------------------------------------------------数据查询-----------------------------------------------------------------------------------------
--查询全体学生的学号和姓名
--SELECT Sno,Sname FROM .ST.Student
--查询全体学生姓名、学号和所在系
--SELECT Sname,Sno,Sdept FROM .ST.Student
--查询全部列
--SELECT * FROM .ST.Student   等价于   SELECT Sno,Sname,Ssex,Sage,Sdept FROM .ST.Student
---------------------------------查询经过计算的值<目标列表达式>->算术运算、字符串常量、函数--------------------------------
--查询全体学生姓名及出生年份
--SELECT Sname,2022-Sage FROM .ST.Student
--SELECT Sname,2022-Sage BIRTHDAY,LOWER(Sdept) Sdept FROM .ST.Student
--选择表中的元组，用DISTINCT消除重复行
--SELECT DISTINCT Sno FROM .ST.Student --------ps:没有指定，默认为ALL
--查询年龄小于20岁的学生的姓名及年龄
--SELECT Sname,Sage FROM .ST.Student WHERE Sage<20;
--查询计算机系全体学生名单
--SELECT Sname FROM .ST.Student WHERE Sdept='CS'
--查询考试不及格学生学号
--SELECT DISTINCT Sno FROM .ST.SC WHERE Grade<60
--查询年龄在20~23岁之间学生的姓名、系别和年龄
--SELECT Sname,Sdept,Sage FROM .ST.Student WHERE Sage BETWEEN 20 AND 23
--查询年龄不在20~23岁之间学生的姓名、系别和年龄
--SELECT Sname,Sdept,Sage FROM .ST.Student WHERE Sage NOT BETWEEN 20 AND 23
--查询计算机系、数学系和信息系学生的姓名和性别
--SELECT Sname,Ssex FROM .ST.Student WHERE Sdept IN('CS','MA','IS')
--查询不是计算机系、数学系和信息系学生的姓名和性别
--SELECT Sname,Ssex FROM .ST.Student WHERE Sdept NOT IN('CS','MA','IS')
----------------------------------------------------------"字符串匹配"-----------------------------------------------------------
--查询学号为201215121的学生的详细情况
--SELECT * FROM .ST.Student WHERE Sno LIKE '201215121'   --不含通配符则等价于 SELECT * FROM .ST.Student WHERE Sno= '201215121'
--查询所有姓刘的学生的姓名、学号和性别
--SELECT Sname,Sno,Ssex FROM .ST.Student WHERE Sname LIKE '刘%'
--查询所有姓欧阳且全名为三个字的学生的姓名、学号和性别
--SELECT Sname,Sno,Ssex FROM .ST.Student WHERE Sname LIKE '欧阳__'--ASCLL码存储占两个字节，GBK存储占一个字节
--查询名字中第二个字为阳的学生的姓名和学号
--SELECT Sname,Sno FROM .ST.Student WHERE Sname LIKE '__阳%'
--查询所有不姓刘的学生的姓名、学号和性别
--SELECT Sname,Sno,Ssex FROM .ST.Student WHERE Sname NOT LIKE '刘%'
-------------------------------------------------''字符串转义''--------------------------------------------------------
--查询DB_Design课程的课程号和学分
--SELECT Cno,Ccredit FROM .ST.Course WHERE Cname LIKE('DB\_Design')ESCAPE '\'--ESCAPE'\'表示‘\’为普通转义字符
--查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况
--SELECT * FROM .ST.Course WHERE Cname LIKE('DB\_%i____')ESCAPE'\'
--查询缺少成绩的学生学号和课程号
--SELECT Sno,Cno FROM .ST.SC WHERE Grade IS NULL
--查询有成绩的学生学号和课程号
--SELECT Sno,Cno FROM .ST.SC WHERE Grade IS NOT NULL
------------------------------------------------多重条件查询-----------------------------------------------------------
--查询所有计算机系年龄在20岁以下的学生姓名
--SELECT Sname FROM .ST.Student WHERE Sdept='CS' AND Sage<20
--------IN谓词是多个OR运算缩写--------
--SELECT Sname,Ssex FROM .ST.Student WHERE Sdept ='CS' OR Sdept='MA'OR Sdept='IS'
--查询选修了3号课程的学生的学号及其成绩，查询结果按成绩的降序排列
--SELECT Sno,Grade FROM .ST.SC WHERE Cno='3' ORDER BY Grade DESC
--------------------------------------------------聚集函数--------------------------------------------------------------
--查询学生总人数
--SELECT COUNT(*) '总人数' FROM .ST.Student
--查询选修了课程的学生人数
--SELECT COUNT(DISTINCT Sno) '选修人数' FROM .ST.SC
--计算选修了1号课程的学生的平均成绩
--SELECT AVG(Grade) '1号课程平均成绩' FROM .ST.SC WHERE Cno='1'
--查询选修1号课程学生的最高成绩
--SELECT MAX(Grade) '1号课程最高成绩' FROM .ST.SC WHERE Cno='1'
--查询学号为201215121的学生的总学分
--SELECT SUM(Ccredit) '学生总学分' FROM .ST.SC,.ST.Course WHERE  Sno='201215121' AND SC.Cno=Course.Cno
--求各个课程号及相应的选课人数
--SELECT Cno,COUNT(Sno)'COUNT(Sno)'FROM .ST.SC GROUP BY Cno
--查询选修了三门以上课程的学生学号
--SELECT Sno FROM .ST.SC GROUP BY Sno HAVING COUNT(*)>3 
--查询平均成绩大于等于90分的学生学号和平均成绩
--SELECT Sno,AVG(Grade) '平均成绩' FROM .ST.SC GROUP BY Sno HAVING AVG(Grade)>=90
------------------------------------------------等值连接---------------------------------------------------------------   
--查询每个学生及其选修课程情况
--SELECT Student.*,SC.* FROM .ST.Student,.ST.SC WHERE Student.Sno=SC.Sno
------------------------------------------------自然连接   --------------------------------------------------------------去掉重复列
--SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM .ST.Student,.ST.SC WHERE Student.Sno=SC.Sno
--查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
--SELECT Student.Sno,Sname FROM .ST.Student,.ST.SC WHERE  Student.Sno=SC.Sno AND SC.Cno='2' AND Grade>90
------------------------------------------------ 自身连接  -------------------------------------------------------------- 
--查找先修课的先修课
--SELECT FIRST.Cno 'Cno',SECOND.Cpno 'Cppno' FROM .ST.Course FIRST,.ST.Course SECOND WHERE FIRST.Cpno=SECOND.Cno
--外连接   改写    （--等值连接   查询每个学生及其选修课程情况） --SELECT Student.*,SC.* FROM .ST.Student,.ST.SC WHERE Student.Sno=SC.Sno
--SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM .ST.Student LEFT OUTER JOIN .ST.SC ON(Student.Sno=SC.Sno)--左外连接 LEFT JOIN  --右外连接 RIGHT JOIN
--也可以使用USING来去掉结果中的重复值--FROM .ST.Student LEFT OUTER JOIN .ST.SC USING(Sno)
----------------------------------------------  多表连接 ----------------------------------------------------------------- 
--查询每个学生的学号、姓名、选修的课程名及成绩  --本查询涉及三个表
--SELECT Student.Sno,Sname,Cname,Grade FROM .ST.Student,.ST.Course,.ST.SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno

---------------------------------------------- --- 嵌套查询   --------------------------------------------------------------
----------------带有IN谓词的子查询-------------------
--查询与“刘晨”在同一个系的学生
--解1.  (用IN 构造嵌套查询)
--SELECT Sno,Sname,Sdept FROM .ST.Student WHERE Sdept IN (SELECT Sdept FROM .ST.Student WHERE Sname='刘晨')
--解2.（用自身连接）
--SELECT S1.Sno,S1.Sname,S1.Sdept FROM .ST.Student S1,.ST.Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨'
--解3.（带有比较的子运算符）
--SELECT Sno,Sname,Sdept FROM .ST.Student WHERE Sdept=(SELECT Sdept FROM .ST.Student WHERE Sname='刘晨')
--解4.（用带EXISTS的子查询替换）
--SELECT Sno,Sname,Sdept FROM .ST.Student S1 WHERE EXISTS(SELECT * FROM .ST.Student S2 WHERE Sname='刘晨' AND S1.Sdept=S2.Sdept)
--查询选修了课程名为“信息系统”的学生学号和姓名
--解1.
--SELECT Sno,Sname FROM .ST.Student WHERE Sno IN(SELECT Sno FROM .ST.SC WHERE Cno IN(SELECT Cno FROM .ST.Course WHERE Cname='信息系统'))
--解2.（连接查询实现）
--SELECT Student.Sno,Sname FROM .ST.Student,.ST.Course,.ST.SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname='信息系统'
----------------典型嵌套案例--------找出每个学生超过他选修课平均成绩的课程号
--SELECT Sno,Cno FROM .ST.SC x WHERE Grade>(SELECT AVG(Grade) FROM .ST.SC y WHERE y.Sno=x.Sno)

------------------------------------------------------------带有ANY(SOME)或ALL谓词的子查询-------------------------------------------------
--查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
--SELECT Sname,Sage FROM .ST.Student WHERE Sage< ANY(SELECT Sage FROM .ST.Student WHERE Sdept='CS')AND Sdept <>'CS'
--聚集函数实现
--SELECT Sname,Sage FROM .ST.Student WHERE Sage<(SELECT MAX(Sage) FROM .ST.Student WHERE Sdept='CS')AND Sdept<>'CS'
--查询非计算机科学系中比计算机科学系所有学生年龄小的学生姓名和年龄
--SELECT Sname,Sage FROM .ST.Student WHERE Sage< ALL(SELECT Sage FROM .ST.Student WHERE Sdept='CS')AND Sdept <>'CS'
--聚集函数实现
--SELECT Sname,Sage FROM .ST.Student WHERE Sage<(SELECT MIN(Sage) FROM .ST.Student WHERE Sdept='CS') AND Sdept<>'CS'
--带有EXISTS谓词的子查询
--查询所有选修了1号课程的学生姓名
--SELECT Sname FROM .ST.Student WHERE EXISTS(SELECT * FROM .ST.SC WHERE SC.Sno=Student.Sno AND Cno='1')
--查询没有选修了1号课程的学生姓名
--SELECT Sname FROM .ST.Student WHERE NOT EXISTS(SELECT * FROM .ST.SC WHERE SC.Sno=Student.Sno AND Cno='1')
-------------------------------------------------------------全称谓词可由存在谓词转化-----------------------------------------------------
--查询选修了全部课程的学生姓名   转换为    没有一门课是他不选修的-----------------按现实世界的顺序，先确定Student表的一个元组，在到Course表中确定第一条元组，最后到SC表中遍历所有元组
--SElECT Sname FROM .ST.Student WHERE NOT EXISTS(SELECT * FROM .ST.Course WHERE NOT EXISTS(SELECT* FROM .ST.SC WHERE Sno=Student.Sno AND Cno=Course.Cno))
--查询至少选修了学生201215122选修的全部课程的学生号码   转换为  不存在这样的课程y,学生201215122选修，而该学生没有选-------ps:学号对应学号、课程号对应课程号
--SELECT DISTINCT Sno FROM .ST.SC SCX WHERE  NOT EXISTS(SELECT *FROM .ST.SC SCY WHERE SCY.Sno='201215122'AND NOT EXISTS(SELECT * FROM .ST.SC SCZ WHERE SCX.Sno=SCZ.Sno AND SCZ.Cno=SCY.Cno))
--------------------------------------------------------------------集合查询----------------------------------------------------------------
--查询计算机科学系的学生及年龄不大于19岁的学生
--SELECT * FROM .ST.Student WHERE Sdept='CS' UNION SELECT * FROM .ST.Student WHERE Sage<19
--查询选修课程1或者选修课程2的学生
--SELECT Sno FROM .ST.SC WHERE Cno='1' UNION SELECT Sno FROM .ST.SC WHERE Cno='2'
--查询计算机科学系中的学生与年龄不大于19岁的学生的交集
--SELECT * FROM .ST.Student WHERE Sdept='CS' INTERSECT SELECT * FROM .ST.Student WHERE Sage<=19   等价于   SELECT * FROM .ST.Student WHERE Sdept='CS' AND Sage<=19
--查询既选修课程1又选修课程2的学生
--SELECT Sno FROM .ST.SC WHERE Cno='1' INTERSECT SELECT Sno FROM .ST.SC WHERE Cno='2' 
--SELECT Sno FROM .ST.SC WHERE Cno='1'AND Sno IN(SELECT Sno FROM .ST.SC WHERE Cno='2')
--查询计算机科学系的学生与年龄不大于19岁学生的交集
--SELECT * FROM .ST.Student WHERE Sdept='CS' EXCEPT SELECT * FROM .ST.Student WHERE Sage<=19    等价于    SELECT * FROM .ST.Student WHERE Sdept='CS' AND Sage>19
---------------------------------------------------------------基于派生表的查询------------------------------------------------------------
--找出每个学生超过他自己选修课程平均成绩的课程号
--SELECT Sno,Cno FROM .ST.SC ,(SELECT Sno,AVG(Grade) FROM .ST.SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)  WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>Avg_sc.avg_grade

---------------------------------------------------                      数据更新                   -----------------------------------------------------

-----------------------插入数据（元组）------------------------
--将新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：IS，年龄：18岁）插入到Student表中
--INSERT INTO .ST.Student(Sno,Sname,Ssex,Sdept,Sage)VALUES('201215128','陈冬','男','IS',18);
--将学生张成民的信息插入到Student表中
--INSERT INTO .ST.Student VALUES('201215126','张成民','男',18,'CS')--不指定时必须与建表时的顺序一致
--插入一条选课记录（'201215128','1'）ps:数据库插入时自动为列Grade赋空值---------------------（目前插入失败，原因为Course表中暂时无数据，而Cno作为该表的外码）
--INSERT INTO .ST.SC(Sno,Cno) VALUES('201215128','1')
--INSERT INTO .ST.SC VALUES('201215128','1',NULL)
-------------------------------------------------------------插入子查询结果-----------------------------------------------------

--对每一个系，求学生平均年龄，并把结果存入数据库-----先建表后插入
--CREATE TABLE .ST.Dept_age(Sdept Char(15),Avg_age SMALLINT);
--INSERT INTO .ST.Dept_age(Sdept,Avg_age)SELECT Sdept,AVG(Sage)FROM .ST.Student Group BY Sdept
---------------------------------------------------------------修改数据----------------------------------------------------------

--修改一个元组值------将学生201215121的年龄改为22岁
--UPDATE .ST.Student SET Sage=22 WHERE Sno='201215121'
--修改多个元组值---------将所有学生的年龄增加一岁
--UPDATE .ST.Student SET Sage=Sage+1
--带子查询的修改语句---------将计算机科学系全体的学生成绩置零
---UPDATE .ST.SC SET Grade=0 WHERE Sno IN(SELECT Sno FROM .ST.Student WHERE Sdept='CS')
--------------------------------------------------------------删除数据------------------------------------------------------------

--删除某一个元组值------删除学号为201215128的学生记录
--DELETE FROM .ST.Student WHERE Sno='201215128'
--删除多个元组值--------删除所有学生的选课记录
--DELETE FROM .ST.SC
--带子查询的删除语句-------删除计算机科学系所有同学的选课记录
-- DELETE FROM .ST.SC WHERE Sno IN(SELECT Sno FROM .ST.Student WHERE Sdept='CS')

------------------------------------------------------------空值的处理------------------------------------------------------------

--1.空值的产生
--向SC表中插入一个元组，学生号是‘201215126’，课程号是‘1’，成绩为空。
--INSERT INTO .ST.SC(Sno,Cno,Grade)VALUES('201215126','1',NULL)
--INSERT INTO .ST.SC(Sno,Cno)VALUES('201215126','1')
--2.空值的判断
--从Student表中找出漏填了数据的学生信息
--SELECT*FROM .ST.Student WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL
--3.空值的约束条件=>属性定义（或者域定义）中有NOT NULL约束条件的不能取空值=>码属性不能取空值
--4.空值的算术运算、比较运算和逻辑运算
--找出选修了1号课程的不及格的学生
--SELECT Sno FROM .ST.SC WHERE Grade<60 AND Cno='1'
--选出选修了1号课程的不及格学生以及缺考的学生
--SELECT Sno FROM .ST.SC WHERE Grade<60 AND Cno='1' UNION SELECT Sno FROM .ST.SC WHERE Grade IS NULL AND Cno='1'
--SELECT Sno FROM .ST.SC WHERE Cno='1' AND (Grade IS NULL OR Grade<60)

----------------------------------------------------------------------视图---------------------------------------------------------------------------

------------------------------------------------------------建立视图-----------------------------------------------------------------------

--建立信息系学生的视图
--GO 
--CREATE VIEW .ST.IS_Student 
--AS 
--SELECT Sno,Sname,Sage FROM .ST.Student WHERE Sdept='IS'
--GO
--建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系学生
--GO
--CREATE VIEW .ST.IS_Student
--AS
--SELECT Sno,Sname,Sage FROM .ST.Student WHERE Sdept='IS'
--WITH CHECK OPTION
--GO
--建立信息系选修了1号课程的学生的视图
--GO
--CREATE VIEW .ST.IS_S1(Sno,Sname,Grade)
--AS
--SELECT Student.Sno,Sname,Grade FROM .ST.Student,.ST.SC WHERE Sdept='IS' AND Student.Sno=SC.Sno AND Cno='1'
--GO
--建立在一个或多个定义好的视图上--建立信息系选修了1号课程且成绩在90分以上的学生的视图
--GO
--CREATE VIEW .ST.IS_S2
--AS
--SELECT Sno,Sname,Grade FROM .ST.IS_S1 WHERE Grade>=90
--GO

--定义一个反映学生出生年份的视图
--GO
--CREATE VIEW .ST.BT_S(Sno,Sname,Sbirth)
--AS 
--SELECT Sno,Sname,2022-Sage FROM .ST.Student
--GO
--分组视图--将学生的学号及平均成绩定义为一个视图
--GO
--CREATE VIEW .ST.S_G(Sno,Gavg)
--AS
--SELECT Sno,AVG(Grade) FROM .ST.SC GROUP BY Sno
--GO
--分组视图--将Student表中的女生定义为一个视图
--GO
--CREATE VIEW .ST.F_Student(F_sno,name,sex,age,dept)
--AS
--SELECT * FROM .ST.Student WHERE Ssex='女'
--GO
--删除视图F_Student、S_G
--DROP VIEW .ST.F_Student
--DROP VIEW S_G
--查询视图
--SELECT Sno,Sage FROM .ST.IS_Student WHERE Sage<20
--转换后语句为
--SELECT Sno,Sage FROM .ST.Student WHERE Sage<20 AND Sdept='IS'
--查询选修了1号课程的信息系学生
--SELECT IS_Student.Sno,Sname FROM .ST.IS_Student,.ST.SC WHERE Cno='1' AND IS_Student.Sno=SC.Sno
--在S_G视图上查询平均成绩在90分以上的学生学号和平均成绩
--SELECT * FROM .ST.S_G WHERE Gavg>=90
--转换后因为在WHERE子句中是不能用聚集函数作为表达式条件，所以正确转换后语句为
--SELECT Sno,AVG(Grade) Gavg FROM .ST.SC GROUP BY Sno HAVING AVG(Grade)>90 

--或者
--SELECT *FROM (SELECT Sno,AVG(Grade) FROM .ST.SC GROUP BY Sno)AS S_G(Sno,Gavg)WHERE Gavg>=90
--更新视图
--将信息系学生视图IS_Student中学号为'201215122'的学生姓名改为“刘辰”
--UPDATE .ST.IS_Student SET Sname='刘辰' WHERE Sno='201215122'
--转换后的更新语句为
--UPDATE .ST.Student SET Sname='刘辰'WHERE Sno='201215121' AND Sdept='IS'
--向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为“201215129”，姓名为“赵新”，年龄为20岁
--INSERT INTO .ST.IS_Student VALUES('201215129','赵新',20) 
--转换后
--INSERT INTO .ST.Student(Sno,Sname,Sage,Sdept) VALUES('201215129','赵新',20,'IS')
--删除信息系学生视图IS_Student中学号为‘201215129’的记录
--DELETE FROM .ST.IS_Student WHERE Sno='201215129'
--转换后
--DELETE FROM .ST.Student WHERE Sno='201215129'AND Sdept='IS'
--将视图S_G中学号为‘201215121’的学生平均成绩改为90分
--UPDATE .ST.S_G SET Gavg=90 WHERE Sno='201215121'--------------------更新失败！！！！因为视图S_G是无法转换为表完成更新的

--若是视图定义中有嵌套查询，并且内层查询的FROM 自居涉及的表也是导出该视图的基本表，则此视图不允许更新。例如，将SC表中成绩在平均成绩之上的元组定义成一个视图
--GO
--CREATE VIEW GOOD_SC
--AS
--SELECT Sno,Cno,Grade
--FROM .ST.SC
--WHERE Grade>
--           (SELECT AVG(Grade) FROM .ST.SC)
--GO
--导出视图的基本表是SC ,内层查询中涉及的表也是SC,所以视图GOOD_SC是不允许更新的

----------------------------------------数据库角色---------------------------------------------
--通过角色来实现将一组权限授予一个用户
--1.首先创建一个角色R1
--CREATE ROLE R1
--1.1删除角色R1
--drop ROLE R1

--2.然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT权限
--GRANT SELECT,UPDATE,INSERT ON .ST.Student TO R1
--2.1收回权限
--REVOKE SELECT,UPDATE,INSERT ON .ST.Student FROM Rl

--3.将这个角色授予U1，使他们拥有角色R1所包含的全部权限
--ALTER ROLE R1 ADD MEMBER U1
--EXEC sp_addrolemember 'R1','U1'
--4.一次性通过R1收回U1的权限
--exec sp_droprolemember 'R1','U1'

---------角色的权限修改
--使角色R1在原来的基础上增加了Student表的DELETE权限
--GRANT DELETE ON .ST.Student TO R1  

--使R1减少了SELECT权限
--REVOKE SELECT ON .ST.Student FROM R1

---------------------------------------------------*************第五章*********----------------------------------------------------------
----------------用户定义的完整性-------------------
--例1、
--    建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和
--CREATE TABLE .ST.TEACHER(
--    Eno NUMERIC(4)PRIMARY KEY,
--    Ename CHAR(10),
--    Job CHAR(8),
--    Sal NUMERIC(7,2),
--    Deduct NUMERIC(7,2),
--    Deptno NUMERIC(2),
--    CONSTRAINT TEACHERKey FOREIGN KEY(Deptno)
--    REFERENCES .ST.DEPT(Deptno),
--    CONSTRAINT C1 CHECK(Sal+Deduct>=3000)
--    );

----------------------5.6 断言----------------------
--插入学生信息
--INSERT INTO .ST.Student VALUES('201215121','李勇','男',20,'CS')
--INSERT INTO .ST.Student VALUES('201215122','刘晨','女',19,'CS')
--INSERT INTO .ST.Student VALUES('201215123','王敏','女',18,'MA')
--INSERT INTO .ST.Student VALUES('201215124','张立','男',19,'IS')

--将课程表插入表
--INSERT INTO .ST.Course VALUES('1','数据库','5','4')
--INSERT INTO .ST.Course(Cno,Cname,Ccredit) VALUES('2','数学','2')
--INSERT INTO .ST.Course VALUES('3','信息系统','1','4')
--INSERT INTO .ST.Course VALUES('4','操作系统','6','3')
--INSERT INTO .ST.Course VALUES('5','数据结构','7','4')
--INSERT INTO .ST.Course(Cno,Cname,Ccredit) VALUES('6','数据处理','2')
--INSERT INTO .ST.Course VALUES('7','PASCAL语言','6','4')

--赋值SC表
--INSERT INTO .ST.SC VALUES('201215121','1',92)
--INSERT INTO .ST.SC VALUES('201215121','2',85)
--INSERT INTO .ST.SC VALUES('201215121','3',88)
--INSERT INTO .ST.SC VALUES('201215122','2',90)
--INSERT INTO .ST.SC VALUES('201215122','3',80)

--------格式  CREATE ASSERTION<断言名><CHECK 字句>
--------每个断言都被赋予一个名字，<CHECK 字句>的约束条件与WHERE字句的条件表达式类似
--限制数据库课程最多60名学生选修--在SQL server中没有ASSERTION这一关键字。那么怎么才能实现涉及多个表或聚集操作的断言呢?是这样的，在SQL server中使用触发器才能实现以上的情况。
--CREATE ASSERTION ASSE_SC_DB_NUM 
--CHECK(60>=(SELECT count(*)
--FROM .ST.Course,.ST.SC WHERE SC.Cno=Course.Cno AND Course.Cname='数据库')
--);

----------------------------------触发器-----------------------------
--SQL Sever提供了两种主要机制来强制实施数据库的完整性：约束和触发器
--触发器可以实现比CHECK约束更为复杂的完整性约束
----------------概念
--触发器是数据库对象，也是一种特殊类型的存储过程
--当对表或视图进行特定操作时，触发器将被自动触发并执行，
--用来防止对表、视图及他们所包含的数据所进行的不正确、未经授权的或不一致的操作
---------------作用
--触发器的主要作用是实现主键和外键所不能保证的复杂的参照完整性，或实现约束和默认值所不能保证的复杂的数据完整性

--触发器：是一种特殊的存储过程，在用户试图更新触发器保护的数据时自动执行
--（1）总是与一个表相连
--触发器是在单个表或视图上定义，这个表成为触发器表。每个表上课以建立若干触发器
--（2）是自动激活的
--当对一个表中数据执行插入、删除、修改操作时，如果对该表上这个操作定义了触发器，则该触发器自动执行，这是不可阻挡的。
--（3）不能直接调用
--与标准的存储过程不同，触发器不能直接调用，也不能传递或接受参数。
--（4）是一个事务
--可以从触发器中二点任何位置撤销。触发器可以包括
--ROLLBACK TRANSACTION语句，激活触发器的语句可以看成隐含事务的开始。
--Delete和inserted表：
--当用户对触发器表执行更新操作时，SQL Server自行为每个触发器创建和管理这两个表。
--他们是逻辑表，存放在内存中，用户不能直接对这两个表进行修改。这两个表的结构
--与触发器表的结构相同。触发工作完成后，与触发器相关的这两个表将被删除。
--1、inserted表
--inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，
--inserted表中的行是触发器表中新行的副本
--2、deleted表
--deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，
--行从触发器表中删除，并传送到delete表，delete表和触发器表一般没有相同的行。

---------------------------------------------------1.语法格式
--CREATE TRIGGER 触发器名  
--ON 表                       --给定建立触发器的表
--[WITH ENCRYPTION]           --加密存储触发器定义
--FOR|AFTER|INSTEAD OF        --触发时间
--[INSERT][,][DELETE][,][UPDATE]
--AS
--触发器要完成的操作          --触发后的动作
-------------------------------------------------------
--AFTER:在触发操作执行完后触发
--FOR:与AFTER相同
--INSTEAD OF:代替触发动作（触发语句）进行操作，并在处理约束之前触发
--WITH ENCRYPTION:加密触发器存放在syscomments中的文本

---ps:
--·创建触发器的权限默认为表的所有者，且不能将该权限转让给其它用户。
--·不能在临时表上创建触发器，但在触发器中可引用临时表。
--·触发器允许嵌套。最大嵌套数32。
--·触发器中不允许有下列语句:
--ALTER DATABASE,CREATE DATABASE,
--DISK INIT，DISK RESIZE，DROP DATABASE,
--LOAD DATABASE等等。
---------------------------------------------------2.INSERT触发器
--插入触发器的执行过程
--·首先执行INSERT语句的插入操作。将新行插入到触发器表和inserted表中。
--·然后执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。

--例1、建立一个INSERT触发器。每当在“选课”表中插入一条记录时，检查学号在学生表中是否存在，若不存在，拒绝插入，否则允许。
--GO
--CREATE TRIGGER sc_insert 
--ON .ST.SC FOR INSERT AS
--BEGIN 
--    DECLARE @sno CHAR(9)
--    SELECT @sno FROM inserted
--    IF NOT EXISTS(SELECT * FROM .ST.Student WHERE Sno=@sno)
--        BEGIN
--            PRINT '该生不存在！'
--            ROLLBACK TRAN
--        END
--END
--GO

--例2、建立一个触发器，学号必须是数字字符
--GO
--CREATE TRIGGER s_sno_numeric
--ON .ST.Student FOR INSERT,UPDATE AS
--BEGIN
--    DECLARE @sno char(6)
--    SELECT @sno FROM inserted
--    if ISNUMERIC(@sno)=0
--        BEGIN
--            PRINT '学号必须是数字构成，撤销此插入'
--            DELETE FROM .ST.Student WHERE Sno LIKE @sno
--        END
--END

--作业（1）
--例3、建立一个触发器，一门功课的选课人数不能大于160
--GO
--CREATE TRIGGER sc_count
--ON .ST.SC FOR INSERT AS
--BEGIN
--    DECLARE @cno char(6)
--    SELECT @cno FROM inserted
--    if 160<(SELECT count(*) FROM .ST.SC WHERE Cno=@cno)
--    BEGIN
--        PRINT '选课人数已满'
--        ROLLBACK TRANSACTION
--    END
--END

---------------------------------------------------3.delete触发器
--执行过程：
--·首先执行DELETE语句的删除操作。将要删除的记录存放到deleted表中，然后删除触发器表中相应的行
--·执行触发器中语句。如果执行到ROLLBACK语句，则系统将回滚整个操作

--例1、建立一个DELETE触发器，每当在学生表中删除一条记录时，也将在选课表中删除相应的记录。

--GO
--CREATE TRIGGER S_delete
--ON .ST.Student FOR DELETE AS
--BEGIN
--    DECLARE @sno CHAR(6)
--    SELECT @sno=deleted.Sno FROM deleted
--    if EXISTS(SELECT * FROM .ST.SC
--                WHERE Sno=@sno)
--        BEGIN
--            DELETE FROM .ST.SC WHERE Sno=@sno
--            PRINT '成功删除！'
--        END
--END

---------------------------------------------------4.UPDATE触发器
--执行过程：
--·首先执行UPDATE语句。将修改前的旧行插入到deleted表中,再修改触发器表史该行信息,将修改后的新行插入到inserted表。
--·执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。
--例、建立一个UPDATE触发器，每当在选课表中修改一条记录时，也将在c_avg表中更新相应的记录
--先建表
--CREATE TABLE .ST.c_avg(课号 char(4),平均成绩 int)
--在插入数据
--INSERT INTO .ST.c_avg SELECT Cno,Avg(Grade) AS 平均成绩 FROM .ST.SC GROUP BY Cno
--最后建立触发器
--GO
--CREATE TRIGGER sc_update
--ON .ST.SC FOR UPDATE AS
--DECLARE @cno CHAR(4)
--SELECT @cno=inserted.Cno FROM inserted
--BEGIN
--    UPDATE .ST.c_avg SET 平均成绩 =(SELECT AVG(Grade) FROM .ST.SC WHERE Cno=@cno GROUP BY Cno)
--    WHERE 课号=@cno
--    PRINT '成功更新课程的平均成绩'
--END

---------------------------------------------------5.INSTEAD OF触发器
--功能:当触发器表上要执行更新操作时,该更新操作并不执行，代之运行触发器本身的SQL语句。
--INSTEAD OF触发器是sQL Server新增的功能。其优点是可以使不具有更新功能的视图支持更新。
--包含多个数据表的视图必须使用INSTEAD OF触发器来支持引用表中数据的更新操作。

--例、建立一个INSTEAD OF触发器，每当对视图进行插入操作时，就在视图触发器中用SQL语句完成对基本表插入记录

--首先为课程表设计一个视图：课程_view1
--GO
--CREATE VIEW .ST.课程_view1 AS SELECT Cno,Cname,Ccredit FROM .ST.Course
--建立该视图的触发器
--GO
--CREATE TRIGGER sc_update_view1 
--ON .ST.课程_view1 INSTEAD OF INSERT AS
--BEGIN
--    DECLARE @cno CHAR(4),@cname CHAR(30),@credit int
--    SELECT @cno=inserted.Cno,@cname=inserted.Cname,
--           @credit=inserted.Ccredit 
--    FROM inserted
--    INSERT INTO .ST.Course(Cno,Cname,Ccredit)
--    VALUES(@cno,@cname,@credit)
--END

--测试代码
--INSERT INTO 课程_view1(Cno,Cname,Ccredit)VALUES('0123','宗教与文化',34)

--作业（2）、现有两个基本表：SX(Sno,Sname,Sage)、SY(Sno,Ssex,Sdept),建立一个视图：student(Sno,Sname,Sage,Ssex,Sdept)；
--建立视图student上的insert触发器，通过视图student来完成对基本表SX和SY中数据的插入。
--首先建立SX与SY表
--CREATE TABLE .ST.SX(Sno char(9),Sname char(9),Sage int)
--CREATE TABLE .ST.SY(Sno char(9),Ssex char(2),Sdept char(20))
--建立student视图
--GO
--CREATE VIEW .ST.Student8(Sno,Sname,Ssex,Sage,Sdept) 
--AS
--SELECT SX.Sno,Sname,Ssex,Sage,Sdept FROM .ST.SX,.ST.SY WHERE SX.Sno=SY.Sno

--创建视图Student8的触发器
--GO
--CREATE TRIGGER Student8_insert
--ON .ST.Student8 INSTEAD OF INSERT
--AS
--BEGIN
--    DECLARE @Sno char(8),@Sname char(8),@Ssex char(2),@Sdept char(20),@Sage int
--    SELECT @Sno=inserted.Sno,@Sname=inserted.Sname,@Ssex=inserted.Ssex,
--            @Sdept=inserted.Sdept,@Sage=inserted.Sage FROM inserted
--    INSERT INTO .ST.SX(Sno,Sname,Sage) VALUES(@Sno,@Sname,@Sage)
--    INSERT INTO .ST.SY(Sno,Ssex,Sdept) VALUES(@Sno,@Ssex,@Sdept)
--END

-- 操作触发器的主要手段
--触发器的管理手段主要为以下两种：
--1、在图形化的集成环境【SQL Sever Management Studio】下实现操作；
--2、【查询编辑器】中运行T-SQL命令实现操作

--T-SQL创建触发器
--CREATE TRIGGER语句用以创建DML触发器
--语句的基本语法如下：
--CREATE CREATE TRIGGER [ <所有者名称>. ] <触发器名> 
--ON { <表名>︱<视图名> } 
--[ WITH ENCRYPTION ] { FOR︱AFTER︱INSTEAD OF }{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE] }[ NOT FOR REPLICATION ] 
--AS
--   [ BEGIN ]
--    T-SQL语句 [ …n ]
--   [ END ][;]

--DML触发器的类别
--DML触发器的类别                     触发时机               触发事件                             支持的对象
--AFTER-INSERT触发器                  后触发         向表中插入记录时触发                   只对数据表有效
--AFTER-UPDATE触发器                  后触发        更新表中的记录时触发
--AFTER- DELETE触发器                  后触发          删除表中的记录时触发
--INSTEAD OF-INSERT触发器             替代触发         向表或视图的基表中插入记录时触发       数据表或视图
--INSTEAD OF-UPDATE触发器             替代触发     更新表或视图的基表中的记录时触发 
--INSTEAD OF- DELETE触发器             替代触发     删除表或视图的基表中的记录时触发

--ALTER TRIGGER 语句用以修改  DML触发器
--语句的基本语法如下：
--ALTER  CREATE TRIGGER [ <所有者名称>. ] <触发器名> 
--ON { <表名>︱<视图名> } [ WITH ENCRYPTION ] { FOR︱AFTER︱INSTEAD OF }{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE] }[ NOT FOR REPLICATION ] 
--AS
--[ BEGIN ]
-- T-SQL语句 [ …n ]
--[ END ][;]

----------------T-SQL查看触发器
--查看触发器的存储过程
--系统存储过程                        语法格式                             功能
--sp_help                         sp_help <触发器名>                   显示触发器的名字、类型、创建时间等基本信息
--sp_helptext                     sp_helptext <触发器名>               显示触发器的源代码
--sp_depends                     sp_depends <触发器名>               查看指定触发器所引用的表
--sp_depends                     sp_depends <表名>                   查看指定表涉及到的所有的触发器
--sp_helptrigger                 sp_helptrigger <表名>,[<类型>]       查看指定表上的指定类型的触发器信息

--------------T-SQL启用触发器
--触发器一旦成功创建，便会自动处于启用状态。随时可以使用ENABLE TRIGGER命令显式地启用各类触发器。
--ENABLE TRIGGER语句的基本语法如下：
--ENABLE TRIGGER{ <触发器名> [ ,...n ]︱ALL } 
--ON { ALL SERVER︱DATABASE︱<对象名> }[;]

------------T-SQL禁启用触发器
--禁用触发器是指暂停触发器的作用，但并不删除该触发器，该触发器依然作为对象存在于当前数据库中，可随时再启用该触发器的功能
--DISABLE TRIGGER语句禁用触发器，其基本语法如下：
--DISABLE TRIGGER{ [ <所有者名称>. ] <触发器名> [ ,...n ]︱ALL } 
--ON { ALL SERVER︱DATABASE︱<对象名> }[;]

----------T-SQL删除触发器
--DROP TRIGGER语句用以删除一个或多个触发器。删除DML触发器的基本语法如下： 
--DROP TRIGGER { [ <所有者名称>. ] <触发器名> } [ ,...n ][;]

--DROP TRIGGER语句用以删除一个或多个触发器。删除DDL触发器的基本语法如下： 
--DROP TRIGGER <触发器名> [ ,...n ] ON { ALL SERVER︱DATABASE } [ ; ]

补充：关于授予用户权限：
--授予用户建数据库的权限**********先将用户放入到master用户中
--use master
--grant create database to 用户名
--授予用户在数据库中建立模式的权限**********先要成为需要建模式的数据库的用户
--use ST
--GO
--grant create schema to 用户名
--授予用户在上述模式下建表的权限***********需要先授予用户修改模式的权限
--use ST 
--GO
--先授予用户修改模式的权限
--grant alter on schema::(模式名) to 用户
--再授予用户建表的权限
--grant create table to 用户名