SQL Server数据定义——模式与基本表操作
1.模式的定义与删除-- 建立模式
CREATE SCHEMA 模式名 AUTHORIZATION 用户名
-- 删除模式（前提删除该模式下的所有内容，如删除基本表drop table test.tab1）
drop schema test
12342.基本表的定义、删除与修改
-- 基本表的定义
create table 表名(
属性名 类型 约束
);
-- 如建立学生表Student
create table Student(
Sno char(9) primary key, -- 列级完整性约束条件，Sno为主码
Sname varchar(10), -- 表中的Sname不可有重复值
Ssex char(2),
Sage smallint,
Sdept char(20)
)
-- 基本表的修改
alter table 表名 要更改的内容
-- 如向Student表中增加“入学时间”列，其类型为日期
alter table Student add Sentrance date;
-- 删除基本表
drop table 表名
123456789101112131415161718二、查询Query
查询涉及的知识点很多，关键是嵌套查询和子查询部分，是难点也是重点。囿于篇幅，不方便把知识点一一罗列，这里放上之前的链接，结合起来复习。就不多写了，自己慢慢消化！！
SQL Server——索引 基于单表的数据插入与简单查询【1】
2021-4-3课程——SQL Server查询【2】
2021-4-5课程——SQL Server查询【3】
2021-4-6课程——SQL Server查询【4】
2021-4-12课程——SQL Server查询【5】 数据更新 空值处理 视图
记住CC标准，是评估信息产品安全性的主要标准。
二、数据库安全性控制1.计算机系统的安全模式
存取控制机制主要包括定义用户权限和合法权限检查两部分。定义用户权限和合法权限检查机制一起组成了数据库管理系统的存取控制子系统。C2级的数据库管理系统支持自主存取控制，B1级的支持强制存取控制。
3.自主存取控制（DAC）：通过 SQL 的GRANT语句和REVOKE语句实现- 用户对不同的数据对象有不同的存取权限
- 不同的用户对同一对象也有不同的权限
- 用户还可将其拥有的存取权限转授给其他用户
（1）用户权限有两个要素组成：数据库对象和操作类型。在数据库系统中，定义存取权限称为授权（authorization）。
（2）授权：授予与收回
GRANT语句向用户授予权限
REVOKE语句收回已经授予用户的权限
GRANT
在授予（grant）时，如果指定了with grant option子句，说明被授予权限的用户还可以把这种权限再授予其他的用户。
PS：
①对属性列的授权时必须明确指出相应属性列名
②若授予所有用户使用public
③标准SQL在指定基本表时要写table，而T-SQL不写！！
REVOKE
格式和注意点基本与GRANT操作相同，只是把grant换成revoke
数据库角色：被命名的一组与数据库操作相关的权限。角色是权限的集合
-- 角色的创建
CREATE ROLE <角色名>
-- 给角色授权和收回权限同用户，不再赘述
1234.强制存取控制（MAC）
- 每一个数据对象被标以一定的密级
- 每一个用户也被授予某一个级别的许可证
- 对于任意一个对象，具有合法许可证的用户才可以存取
在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体两大类。
主体是系统中的活动实体
◼数据库管理系统所管理的实际用户
客体是系统中的被动实体
◼文件、基本表、索引、视图
❖两类常用连接运算
◼ 等值连接（equijoin）
⚫
❖敏感度标记（Label）
◼ 对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记（Label）敏感度标记分成若干级别：
⚫绝密（Top Secret，TS）
⚫机密（Secret，S）
⚫可信（Confidential，C）
⚫公开（Public，P）
◼TS>=S>=C>=P
◼主体的敏感度标记称为许可证级别（Clearance Level）
◼客体的敏感度标记称为密级（Classification Level）
❖强制存取控制规则
◼仅当主体的许可证级别大于或等于客体的密级时，该主体才能读相应的客体
◼仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体
为不同的用户定义不同的机制，把数据对象限制在一定范围内。视图机制间接地实现支持存取谓词的用户权限定义
四、数据加密数据加密主要包括存储加密和传输加密
第5章 数据库完整性一、三大完整性1.实体完整性以主码作为唯一性标识，竹马中的属性即主属性不能取空值。用PRIMARY KEY定义
插入或更新操作时，DBMS按照实体完整性规则自动进行检查。
（1）检查主码值是否唯一，如果不唯一则拒绝插入或修改
（2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改
参照和被参照关系。主码和外码。用FOREIGN KEY短语定义哪些列为外码。用REFERENCES短语指明这些外码参照哪些表的主码
❖CREATE TABLE时定义属性上的约束条件
◼列值非空（NOT NULL）
◼列值唯一（UNIQUE）
◼检查列值是否满足一个条件表达式（CHECK）
❖在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
二、触发器（trigger） 存储过程❖触发器类型
◼行级触发器（FOR EACH ROW）
◼语句级触发器（FOR EACH STATEMENT）
❖AFTER/BEFORE是触发的时机
◼AFTER表示在触发事件的操作执行之后激活触发器
◼BEFORE表示在触发事件的操作执行之前激活触发器
❖触发器的执行
是由触发事件激活的，并由数据库服务器自动执行！
❖删除触发器
-- 标准SQL
DROP TRIGGER<触发器名> ON <表名>
-- T-SQL
drop trigger 触发器名;
-- 或者这样写，下面这种写法更好，类似删表，如果存在才删除
if(object_id('触发器名') is not null)
drop trigger 触发器名;
1234567
❖存储过程
由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。
◼标准SQL创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,…])
AS <过程化SQL块>；
◼T-SQL典型例子，用以说明建立过程
if (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
drop procedure Proc_TRANSFER
go
create procedure Proc_TRANSFER
@inAccount int,@outAccount int,@amount float
/*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
as
begin transaction TRANS
declare /*定义变量*/
@totalDepositOut float,
@totalDepositIn float,
@inAccountnum int;
/*检查转出账户的余额 */
select @totalDepositOut = total from Account where accountnum = @outAccount;
/*如果转出账户不存在或账户中没有存款*/
if @totalDepositOut is null
begin
print '转出账户不存在或账户中没有存款'
rollback transaction TRANS; /*回滚事务*/
return;
end;
/*如果账户存款不足*/
if @totalDepositOut < @amount
begin
print '账户存款不足'
rollback transaction TRANS; /*回滚事务*/
return;
end
/*检查转入账户的状态 */
select @inAccountnum = accountnum from Account where accountnum = @inAccount;
/*如果转入账户不存在*/
if @inAccountnum is null
begin
print '转入账户不存在'
rollback transaction TRANS; /*回滚事务*/
return;
end;
/*如果条件都没有异常，开始转账。*/
begin
update Account set total = total - @amount where accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
update Account set total = total @amount where accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
print '转账完成，请取走银行卡'
commit transaction TRANS; /* 提交转账事务 */
return;
end
123456789101112131415161718192021222324252627282930313233343536373839404142434445
◼执行存储过程：T-SQL是用exec，标准SQL是call或者perform
◼修改存储过程：只需将创建时的create改成alter
◼删除存储过程：
drop procedure 过程名;
❖函数
函数和存储过程的异同同：都是持久性存储模块
异：函数必须指定返回的类型
下面是标准SQL函数的相关内容
1.函数的定义语句格式2.函数的执行语句格式CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;
3.修改函数CALL/SELECT 函数名 ([参数1,参数2,…]);
重命名
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
重新编译
ALTER FUNCTION 过程名 COMPILE;
下面是T-SQL函数的相关内容，拿个练习的例子说
create function AvgAge(@Sex varchar(10)='男') --在这里是给参数默认值，即可以不传参数调用这个函数
returns decimal
as
begin
declare @AvgAge decimal(10,2)
select @AvgAge=AVG(Convert(decimal(10,2),Sage)) from Student where Ssex=@Sex
return @AvgAge
end;
-- 测试：select [函数的所有者].函数名 as 字段别名
select dbo.AvgAge(default) as AvgAge;
select dbo.AvgAge('男');
select dbo.AvgAge('女');
12345678910111213六、收尾写的过程中发现自己忘的真的不是一星半点，而是so much！基础篇第1~5章的内容不少，需要记忆和练习的东西也很多，尤其是第3章，难点是第5章和第8章的存储过程，属于基础篇自然有它的道理，基础打的牢才能走的远，在学习进阶等高级部分时也会更容易上手！“不积跬步无以至千里，不积小流无以成江海”。Just Do It !
