SQL Server模式定义与表的增删改 3.22课程

第四周的数据库实验课，动手建立用户与模式，并将学习的表之间的参照关系使用SQL语句应用于表的建立、修改和删除中

1、模式的定义和授权

模式在SQL Server中称为架构，一个模式下通常包括了多个表、视图和索引等数据库对象。模式授权于用户，用户被授权某个模式，相当于被授权使用数据库中该模式下数据的权利

新建用户时，需要指定登录名，在浏览(… )中选择

建立架构时，可直接指定将该架构授权给哪个用户

create schema "S-T" authorization wang --为用户 wang 授权新建的架构S-T，批处理语句末尾不能分号
create table tab1(
	col1 smallint,
	col2 int,
	col3 char(20),
	col4 numeric(10,3),
	col5 decimal(5,2)
); --为该模式创建一个表tab1，S-T.tab1

如果不先授权架构，直接创建表，默认的是创建最高权限管理员架构的表，即dbo.*
也可使用SSMS可视化操作，建立架构并选择授权用户

建立架构时，架构所有者即为授权的用户

2、模式的删除

删除分为(级联)cascade删除和(限制)restrict删除，cascade在删除时同时将该模式下已定义的数据库对象一并删除，restrict删除会在该模式存在数据库对象时删除失败

SQL Server的T-SQL语言不支持使用cascade和restrict删除

-- drop schema "S-T" cascade; --不支持
-- drop schema "S-T" restrict; --不支持
drop schema "S-T"; --表示架构时需要加双引号，感觉相当于restrict删除模式

直接删除内部存在表、视图等数据库对象的架构会失败

需要在删除前清空架构内的数据库对象

drop table "S-T".tab1; -- 先删该模式下的表等数据对象
drop schema "S-T"; -- 再删除该模式

3、表的建立和修改

直接建立表，默认会在最高权限模式下建立

建立表时，可在列名后添加该属性列的完整性约束条件(主码、能否为空等)，最后添加该表的完整性约束条件(主属性、外码设置等)

 -- foreign key(a) references tab(b) --该表中a属性为外码，对应于tab表的b属性

create table Student(
	Sno varchar(20) primary key, --主码
	Sname varchar(20) unique, --值互不相同
	Ssex varchar(2),
	Sage smallint,
	Sdept varchar(20)
); --建立学生表
create table Course(
	Cno varchar(4) primary key, --主码
	Cname varchar(40) not null, --不能取空值
	Cpno varchar(4), --表示先行课程
	Ccredit smallint,
	foreign key(Cpno) references Course(Cno) --自身与自身参照，Cpno是外码(确保先行课程是存在的)
); --建立课程表
create table SC(
	Sno varchar(20),
	Cno varchar(4),
	Grade smallint,
	primary key(Sno,Cno), --主属性包含两个属性
	foreign key(Sno) references Student(Sno), --Sno是外码，被参照表为Student表，数据类型需相同
	foreign key(Cno) references Course(Cno) --Cno是外码，被参照表为Course表
	/*确保选课的学号真实存在，所选的课真实存在*/
); --建立选课表

insert into Student values(
	'20191102041',
	'王伯成',
	'男',
	21,
	'计算机科学与技术'
); --插入一个元组，注意字符串单引号

可为已建立的表添加新属性(列)，或修改已有属性的完整性约束

alter table Student add S_entrance date; --Student表添加新列
alter table Student alter column Sage int; --Student表改属性数据类型
alter table Course add unique(Cname); --Course表改Cname属性值互不相同

4、表的删除

因为参照完整性规则，在删除表时，需要考虑该表与其他表使的参照关系，直接删除带有外码的表会失败

需要先删除被参照关系的表

-- drop table SC cascade; --不支持
-- drop table SC restrict; --不支持
drop table SC;
drop table Course;
drop table Student; --删除表后，视图等相关内容不可用

删除表后，依赖该表的视图等对象仍有物理空间存在，但已不可用

SQL-Server取消了cascade和restrict的删除，是不是为了安全性？避免误操作
感觉SSMS的编辑器挺奇特，莫名其妙的红线(偏说刚建的表或什么的不存在)，但是可以执行成功