SQL Server 数据库实验课第三周——模式、基本表定义与删除

一.新建用户

新建数据库stu，在数据库stu中stu->安全性->用户->右击新建用户

新建用户:wang，选择所对应的登录类型

新建用户成功

二.定义模式

SQL语句

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

1.为用户WANG定义一个学生-课程模式S-T

CREATE SCHEMA "S-T" AUTHORIZATION WANG

2.不指定<模式名>，所以<模式名>隐含为用户名WANG

CREATE SCHEMA AUTHORIZATION WANG

3.创建模式时定义基本表
SQL-Server中，默认用户为dbo，所以默认<模式名>为dbo
目前，在CREATE SCHEMA中可以接受CREATE TABLE,CREATE VIEW和GRANT子句。也就是说用户可以在创建模式的同时在在这个模式定义中进一步创建基本表、视图，定义授权

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];

如

CREATE SCHEMA TEST AUTHORIZATION WANG
CREATE TABLE TAB1(
	COL1 SMALLINT,
	COL2 INT,
	COL3 CHAR(20),
	COL4 NUMERIC(10,3),
	COL5 DECIMAL(5,2)
);

若两条语句分开执行，则表的模式默认dbo

二、删除模式

DROP SCHEMA <模式名><CASCADE|RESTRICT>

CASCAED（级联）：删除模式时同时把该模式中所有数据库对象全部删除
RESTRICT（限制）：如果该模式已经定义了下属数据库对象，则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行

DROP SCHEMA TEST CASCADE;

该语句删除了模式TEST，同时模式中的表TAB1也被删除
但SQL Server中输入报错，因为SQL Server没有RESTRICT和CASCADE选项

SQL Server中，可先删除表TEST.TAB1,再删除TEST模式

DROP TABLE TEST.TAB1;
DROP SCHEMA TEST;

三、基本表的定义、删除，与修改

使用SQL语句，新建查询
注意操作的范围

1.定义基本表

格式

CREATE TABLE <表名>
(
	<列名> <数据类型>[ <列级完整性约束条件> ]
	[,<列名> <数据类型>[ <列级完整性约束条件>] ] 
	...
	[,<表级完整性约束条件> ] 
);

建表时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背完整性约束条件。如果完成性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级，也可以定义在表级
（1）新建学生表

CREATE TABLE Student
(
	Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/  
	Sname CHAR(20) UNIQUE, /* Sname取唯一值*/
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)

);

（2）新建课程表

CREATE TABLE Course
(
	Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40) NOT NULL, /*列级完整性约束条件，不可取空*/
	Cpno CHAR(4),
	Ccredit SMALLINT,
	FOREIGN KEY(Cpno) REFERENCES Course(Cno)
	/*表级完整性约束条件，Cpno是外码，被参照表是Course,被参照列是Cno*/
);

（3）新建学生选课表SC

CREATE TABLE SC
(
	Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno), /* 主码由两个属性构成，必须作为表级完整性进行定义*/
	FOREIGN KEY(Sno) REFERENCES Student(Sno),
	/* 表级完整性约束条件，Sno是外码，被参照表是Student */ 
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
	/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
);

2.数据类型

SQL 数据类型

3.模式与表

三种方法定义基本表所属模式
1.创建表时在表中明显的给出模式名

CREATE TABLE "S-T".ss(
	Sno CHAR(10)
);

2.创建模式时定义基本表（上文已提到）
3.设置所属模式，这样在创建表名时不必给出模式名
当用户创建基本表（或数据库其他对象）时若没有指定模式，系统根据搜索路径来确定该对象所属模式。
搜索路径包含一组模式列表，关系数据库会使用模式列表中南第一个存在的模式作为数据库对象的模式名
显示当前搜索路径语句

SHOW search_path

4.修改基本表

格式

ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;

语句	含义
ADD 子句	用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN 子句	用于删除表中的列·。1.如果指定了CASCADE短语，则自动删除引用了该列的其他对象。2.如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT 子句	用于删除指定的完整性约束条件
ALTER COLUMN 子句	用于修改原有的列定义，包括修改列名和数据类型

（1）.向Student表增加“入学时间”列S_entrance，其数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE

（2）将年龄的数据类型由SMALLINT改为INT

ALTER TABLE Student ALTER COLUMN Sage INT

（3）增加课程名称必须取唯一值的约束条件

ALTER TABLE Course ADD UNIQUE(Cname)

5.删除基本表

格式

DROP TABLE <表名>［RESTRICT| CASCADE］;

RESTRICT： 该表删除是有限制条件的， 欲删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数等。
CASCADE：在删除基本表的同时，相关的依赖对象一起删除
1.删除stu表

DROP stu CASCADE

2.若表上建有视图（或者是有其他依赖该表的对象，即与它相关的东西），使用RESTRICT时表不能删除；使用CASCADE时可以删除表，视图也自动删除。
先创建一个基于Student的视图 IS_Student：

CREATE VIEW IS_Student
	AS
	SELECT Sno,Sname,Sage
	FROM Student 
	WHERE Sdept='IS';

查询视图

SELECT *FROM IS_STUDENT;

然后用RESTRICT删除：

DROP TABLE Student RESTRICT; 

DBMS提示信息：

--ERROR: cannot drop table Student because other objects depend on it

如果选择CASCADE，可删除该表，视图自动被删除

DROP TABLE Student CASCADE;

DBMS提示信息：

--NOTICE: drop cascades to view IS_Student

然后，用查询语句检测视图是否存在：

SELECT * FROM IS_Student;

提示信息：

--ERROR: relation " IS_Student " does not exist

删除成功啦！
但 SQL Server中不支持CASCADE/RESTRICT关键字，所以删除只需输入：

DROP TABLE Student;

提示信息：

sql server外键约束的增加及删除

因为SQL Server不能使用 DROP TABLE删除被外键约束引用的表
所以先找出外键约束名字

exec sp_helpconstraint 'Student'

删除外键约束

alter table Student drop constraint PK__Student__CA1FE4647199F2C8

再删除表Student

DROP TABLE Student

查看是否删除

SELECT *FROM IS_Student

四、遇到问题

显示当前搜索路径语句

SHOW search_path

但是输进去一直报错
百度了一下，SQL Server中没有搜索路径的概念
PostgreSQL中有search_path的设置