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

时间:2024-10-05 21:06:33

本文主要介绍运用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	SMALLINTPRIMARY 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执行报错的时候(自己没写错的情况下),可以去查阅MicrosoftSQL文档:DROP TABLE (Transact-SQL),当然也可自行百度或Google

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

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