SQL Server 数据库基本操作入门篇【2】

本文主要介绍运用SQL语句对模式和基本表的操作，欢迎阅读~

本文构成比较简单

• 一、用SQL语句新建数据库
• 二、模式（SCHEMA）的定义与删除
• • 1. 定义模式
  • 2. 删除模式
• 三、基本表的定义、删除与修改
• • 1. 创建基本表
  • 2. 修改基本表
  • 3. 删除基本表

一、用SQL语句新建数据库

用SQL语句新建一个名为STUDENT的数据库：

CREATE DATABASE STUDENT

• 1

在SSMS中点击【新建查询】，输入该语句，【执行】后然后到左侧的【数据库】右键刷新一下，即可看到新建的STUDENT数据库

之前第一篇中讲解了直接用对话窗口新建数据库（可视化操作），那样创建操作比较直观简单，那为什么要用SQL语句来写呢？

• 因为SQL能够嵌入到高级语言（例如C++，Java，Python）程序中，当程序需要自动新建数据库时就得使用SQL语句啦

---

二、模式（SCHEMA）的定义与删除

（注：SQL Server 中文版里SCHEMA翻译为：架构 ）

首先检查数据库中是否存在WANG这个用户。如果没有，需先创建该用户。
可以用SQL语句创建用户（我会在以后的文章中介绍）
这里我就先介绍使用DBMS直接用对话窗口新建用户：
① 【STUDENT】—>【安全性】—>【用户】，右键【新建用户】

② 在弹出的窗口中按如下顺序操作，填好【用户名】和【登录名】之后点击【确定】即可完成STUDENT数据库中WANG用户的创建：

1. 定义模式

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

CREATE SCHEMA "S-T" AUTHORIZATION WANG;

• 1

然后打开之间新建的【STUDENT】数据库—>【安全性】—>【架构】就能看到刚刚为用户WANG新建的模式【S-T】

—— —— —— —— —— —— —— —— —— —— —— —— —— —— —— ——

CREATE SCHEMA AUTHORIZATION WANG;

• 1

该语句没有指定<模式名>，<模式名>隐含为 <用户名>
（SQL Server 中，默认用户为 dbo，在没有创建模式的情况下，默认的模式名为dbo，所以表名为dbo.* 的格式 ）

· 在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。格式如下：

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

• 1
• 2

比如：咱们为用户WANG创建一个模式TEST，并且在其中定义一个表TAB1：

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

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

如下图所示：

TEST模式建立成功后可以继续在TEST模式下建立表TAB2：

CREATE TABLE TEST.TAB2
( 
	COL1 SMALLINT                                                                                    
);

• 1
• 2
• 3
• 4

2. 删除模式

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

• 1

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

这里注意：

DROP SCHEMA WANG CASCADE;

• 1

删除模式WANG，同时该模式中定义的表TAB1也被删除
但是，当你输入这句语句之后它会给你报错：

· 因为SQL Server使用的是T-SQL，而该语句为标准SQL，SQL Server不支持在 DROP SCHEMA使用CASCADE，可变通后实现该功能，实现方法参考：【Transact-SQL】DROP SCHEMA TEST CASCADE ：关键字 ‘CASCADE’ 附近有语法错误

---

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

1. 创建基本表

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

• 1
• 2
• 3
• 4
• 5
• 6
• 7

（ps：[ ]的内容是可写可不写的，例如上面的第一行中 [ <列级完整性约束条件> ] 为可写可不写，第二行及之后的都有 [ ]，即可以只有一列，也可以有多列）

<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

• 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级

—— —— —— —— —— —— —— —— —— —— —— —— —— —— —— ——
来康康栗子：
· 建立一个“学生”表 Student

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

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

创建好后如下图所示，Sno前有一个小钥匙图标表示它是主码PK

ps：需在当前数据库的当前模式下执行（例如我这里是在STUDENT数据库下，需如下如所示红色区域选择STUDENT，模式默认为dbo）

· 建立一个“课程”表 Course

CREATE TABLE Course
(
	Cno	CHAR(4) PRIMARY KEY,
	Cname	CHAR(40),            
	Cpno	CHAR(4),               	                      
	Ccredit	SMALLINT,
	FOREIGN KEY	(Cpno) REFERENCES  Course(Cno) 
); 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

（ps：Cpno是外码，被参照表是Course，被参照列是Cno。因为这里的 FOREIGN KEY完整性约束条件涉及到该表的两个属性列Cpno和 Cno，所以必须定义在表级上）

· 建立一个学生选课表 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*/
); 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

2. 修改基本表

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

• 1
• 2
• 3
• 4
• 5
• 6

（[ ]同上面所讲为可选项）
<表名> 是要修改的基本表
ADD 子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN 子句用于删除表中的列
· 如果指定了CASCADE短语，则自动删除引用了该列的其他对象
· 如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT 子句用于删除指定的完整性约束条件
ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数据类型
—— —— —— —— —— —— —— —— —— —— —— —— —— —— —— ——
来看例子：

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

ALTER TABLE Student ADD S_entrance DATE;

• 1

（ps：不管基本表中原来是否已有数据，新增加的列一律为空值 NULL ）

· 将年龄的数据类型由字符型（这里原来的数据类型是SMALLINT）改为整数

ALTER TABLE Student ALTER COLUMN Sage INT;

• 1

（ps：这里只需声明要改成什么类型即可，不用知道或写出原来的类型）

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

ALTER TABLE Course ADD UNIQUE(Cname); 

• 1

3. 删除基本表

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

• 1

RESTRICT： 删除表是有限制的
· 欲删除的基本表不能被其他表的约束所引用
· 如果存在依赖该表的对象，则此表不能被删除
· （即需要把与它相关的东西都删除了才能删它）
CASCADE： 删除该表没有限制（级联）
· 在删除基本表的同时，相关的依赖对象一起删除
—— —— —— —— —— —— —— —— —— —— —— —— —— —— —— ——

又到了看例子的环节：
（大家一定要考虑清楚了再删除吖，这没了可就真的没了，当然表简单的话再新建就行，不过毕竟浪费时间，大家可以把平时写的代码保存起来，一来不怕误删了，而来下次用到直接一顿Ctrl，稍微改改就搞定~）

• 删除Student表

DROP TABLE  Student  CASCADE;

• 1

（基本表定义被删除，数据被删除；表上建立的索引、视图、触发器等一般也将被删除 ）

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

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

• 1
• 2
• 3
• 4
• 5

然后尝试使用RESTRICT删除：

DROP TABLE Student RESTRICT; 

• 1

DBMS提示信息：
--ERROR: cannot drop table Student because other objects depend on it

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

DROP TABLE Student CASCADE;

• 1

DBMS提示信息：
--NOTICE: drop cascades to view IS_Student
然后，用查询语句检测视图是否存在：

SELECT * FROM IS_Student;

• 1

提示信息：
--ERROR: relation " IS_Student " does not exist
删除成功啦！
<- - -以上是标准SQL语句及其执行结果- - ->

注意！ SQL Server中不支持CASCADE/RESTRICT关键字，所以删除只需输入：

DROP TABLE Student;

• 1

此时DBMS提示信息：

这是因为SQL Server不能使用 DROP TABLE 删除被 FOREIGN KEY 约束引用的表，所以需要先删除FK约束，参考：SQL删除外键约束

敲小黑板！！

• 以上操作一定要切换至对应的数据库来进行，比如我这里就是在STUDENT数据库下进行的
  
  （这里很重要，因为如果是没切换，还是默认的系统数据库master的话，执行以上操作DBMS会报错！）
  
• SQL Server中不支持DROP删除模式或基本表时使用CASCADE/RESTRICT关键字，且SQL Server不能使用 DROP TABLE 删除被 FOREIGN KEY 约束引用的表。 必须先删除引用 FOREIGN KEY 约束或引用表，如何删除引用FK约束可参考：SQL删除外键约束
• 这里的的基本表除了Student，Course和SC也都是是有FK的，所以需要先删除FK约束才能删除表
  
  
• 因为SQL Server的T-SQL与标准SQL不尽相同，所以遇到标准SQL执行报错的时候（自己没写错的情况下），可以去查阅Microsoft的SQL文档：DROP TABLE (Transact-SQL)，当然也可自行百度或Google

---

嘿嘿，这次的分享就这么多啦，索引以及数据更新等知识和相关操作会写在下一篇，感谢阅读~????
咱们下期 见~

• 下一篇：SQL Server 数据库基本操作入门篇【3】
• 相关推荐：【吐血整理】数据库重点知识总结【2】