数据库语言的练习 1 - CREATE / DROP / ALTER（2020.3.4作业）

最新推荐文章于 2023-02-01 10:23:30 发布

与阿轩势力狼狈为奸

最新推荐文章于 2023-02-01 10:23:30 发布

阅读量3.5k

点赞数 5

本文链接：https://blog.csdn.net/MooM_X/article/details/104658786

版权

首先新建一个数据库SCHOOL
点击新建查询，输入指令

CREATE DATABASE SCHOOL;

在这里插入图片描述
点击“✔”分析语言是否正确，点击“！执行”执行所输入的指令。
右键数据库刷新后，可以看到已成功建立数据库SCHOOL

新建数据库用户WANG
在SCHOOL-安全性下找到“用户“，新建用户

在这里插入图片描述
用户建立成功

[例3.1] 为用户WANG定义一个学生-课程模式S-T

CREATE SCHEMA "S-T" AUTHORIZATION WANG;

在执行指令之前，一定要将当前运行的库改为SCHOOL，否则将显示找不到用户WANG
在这里插入图片描述
注意：不能在之前的指令后续写，否则将再次执行前一个指令
成功建立S-T模式

[例3.2]

CREATE SCHEMA AUTHORIZATION WANG

在没有创建模式的情况下，默认的模式名为dbo，所以表名为dbo.*
[例3.3]为用户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)
                   );

在这里插入图片描述
若去掉第一行（为指定用户），则该表为dbo.TAB1（默认属于dbo架构）

TEST模式建立成功，继续在TEST模式下建立表TAB2

CREATE TABLE TEST.TAB2(COL1 SMALLINT
					  );

刷新表可以看到建立成功
在这里插入图片描述
此时若去掉语句中的“TEST.”，则表默认属于dbo，表名为dbo.TAB2

[例3.4]

DROP SCHEMA TEST CASCADE;

删除模式TEST，同时该模式中定义的表也被删除
在这里插入图片描述
这里报错了，因为在SQL Serever里drop schema语句并不支持cascade关键字，要想成功删除架构，必须先删除这个架构下已经创建的对象

可以看到架构成功删除

[例3.5] 建立“学生”表Student。学号是主码，姓名取值唯一。

CREATE TABLE Student(Sno   CHAR(9) PRIMARY KEY, 
                     Sname CHAR(20) UNIQUE, 
                     Ssex    CHAR(2),
        			 Sage   SMALLINT,
       			     Sdept  CHAR(20)
      				);

创建表的形式：
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
涉及到一个属性列的完整性约束条件时可以用列级完整性约束条件，也可以用表级完整性约束条件；涉及多个属性列的完整性约束条件时只能用表级完整性约束条件。
在这里插入图片描述
其中PRIMARY KEY为主码，UNIQUE为取唯一值，即该表中不能有同名的学生。两者都为列的完整性约束条件
如图，有金钥匙标志的为主码

[例3.6 ] 建立一个“课程”表Course

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

在这里插入图片描述
FOREIGN KEY是外码， REFERENCES后面为被参照表（被参照列），即Cpno是外码，被参照表是Course，被参照列是Cno。这里为表级完整性约束条件。

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

在这里插入图片描述
这里主码由两个属性构成，必须作为表级完整性进行定义；后两行语言同上，Sno是外码，被参照表是Student ；Cno是外码，被参照表是Course。

以上为创立表的过程，除此之外可自行将表储存，后续可用记事本打开复制过来。也可直接将表打开执行。
在这里插入图片描述

[例3.8] 向Student表增加“入学时间”列，其数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE;

修改基本表：
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件；
DROP COLUMN子句用于删除表中的列；
如果指定了CASCADE短语，则自动删除引用了该列的其他对象。如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列；
DROP CONSTRAINT子句用于删除指定的完整性约束条件；
ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。
在这里插入图片描述
不管基本表中原来是否已有数据，新增加的列一律为空值

[例3.9] 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT;

在这里插入图片描述
[例3.10] 增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);

在这里插入图片描述
[例3.11] 删除Student表

DROP TABLE  Student  CASCADE;

删除基本表：
DROP TABLE <表名>［RESTRICT| CASCADE］;
RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除

在这里，使用SQL Server执行“ DROP TABLE Student CASCADE”却被提示有语法错误。
在这里插入图片描述
报错的原因在于SQL Server不支持在删除过程中使用CASCADE关键字

SQL Server在删除表时不区分RESTRICT或CASCADE
所以应去掉CASCADE

DROP TABLE  Student;

在这里插入图片描述
这里又出现了因外键问题报错（SC）
应该先删除外键再删除表，在网上搜索了删除外键的方法

ALTER TABLE 表名 DROP CONSTRAINT 外键名

表名是添加外键的表名，即SC
外键名与添加外键的外键名是不同的，需要通过

select name 
from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id 
where f.parent_object_id=object_id('表名')

找出表上的外键约束名字
（参考博客https://www.cnblogs.com/dangpengfei/p/8358390.html）
在这里插入图片描述
删除外键

这样就可以成功删除表，可以看到刷新后Student已经成功删除

[例3.12 ]若表上建有视图，使用RESTRICT时表不能删除;使用CASCADE时可以删除表，视图也自动删除。
创建基于Student的视图IS_Student（我把删了的Student表又捡回来了=-=，但是我已经提前存储好了，直接打开十分方便~）

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

在这里插入图片描述
标准SQL中，若表上建有视图，使用RESTRICT时表不能删除;使用CASCADE时可以删除表，视图也自动删除。
在SQL serever中，我们建立视图后删除Student表

刷新后发现视图依旧保留

用查询语句检测时会报错，显示对象名无效，视图也是无法使用的
在这里插入图片描述
当我再次重建Student表后，用查询语句检测时视图存在（这里忘记刷新表了，此时Student存在）

我的结论：在SQLserever中，删除表后视图会保留但不起作用，表恢复后视图可使用