篇幅较长O(∩_∩)O,内容主要分为三个部分:准备工作、模式的定义与删除、基本表的定义、删除与修改
引入:
关系数据库系统支持三级模式结构,其模式、外模式和内模式中的基本对象有模式、基本表、视图和索引等。因此SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。一个关系数据库管理系统的实例中可以建立多个数据库,一个数据库中可以建立多个模式,一个模式下通常包括多个表、视图和索引等数据库对象。
一、准备工作
1.新建数据库
create database stutest
2.在该库下新建用户,展开stutest库—> 安全性—> 用户—> 新建(没有用户又何来为用户定义模式呢)
二、模式的定义与删除
在SQL中,模式定义语句为
CREATE SCHEMA 模式名 AUTHORIZATION 用户名
若未指定模式名,则默认是dbo
1.为用户fmmpmo定义一个学生-课程模式S-T
create schema "S-T" authorization fmmpmo
执行后发现架构下多了一个模式S-T,创建成功
2.为用户fmmpmo创建一个模式test,并在该模式下新建表tab1,刷新展开表后会发现多了一个test.tab1,而不是dbo.tab1
create schema test authorization fmmpmo;
create table tab1(
id int,
username varchar(20),
pwd varchar(20)
)
3.删除模式
注意,不能加关键字CASCADE和RESTRICT,因为本版本的SQL Server不支持。但仍要说一下:二者只能选其一,选择了CASCADE(级联),表示在杉树模式的同时把该模式中所有的数据库对象全部删除;选择了RESTRICT(限制),表示如果该模式中已经定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。只有当该模式中没有了任何下属的对象时才能执行
drop schema test
但输入该SQL语句后,发现仍然报错,内容如下:
这是因为我在当前模式下新建了一张名为tab1的表,他不允许我这样直接删除,需要把该模式下的内容都删除后才能执行删除模式的操作,直接默认是RESTRICT操作了,挺好。
drop table test.tab1
执行删除表操作后会发现该表没有了,再执行删除模式操作,发现test模式被删除
4.补充:
SQL语句可以以分号结尾,当然也可以不写,但并不是什么时候不写都是对的。当要执行的命令不止一个时,需要在命令与命令之间加上分号,否则报错。当然你也可以选中某个命令,表明只针对当前语句执行,此时不加分号也是可以的,因为已经确定了范围。还有关于模式,他必须作为命令语句的第一句,就像java中的super和this一样,否则即便你加了分号也无济于事。把第一行注释掉或者删除即可。
三、基本表的定义、删除与修改
说明:以下操作均为在stutest库下的dbo(默认)模式下的操作。
1.定义基本表
1.1 建立一个“学生”表Student
create table Student(
Sno char(9) primary key, -- 列级完整性约束条件,Sno为主码
Sname varchar(10), -- 表中的Sname不可有重复值
Ssex char(2),
Sage smallint,
Sdept char(20)
)
1.2 建立一个课程表
Cpno是外码,被参照表和参照表是同一个表,都为Course,被参照列时Cno
create table Course(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) references Course(Cno)
)
1.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.补充:char与varchar的对比
两者都用于字符串类型,并且都可以指定最大长度,但char是定长字符串,即在不不超过最大长度的前提下,字符串无论长度多少,分配的空间都是最大长度那么大,此种适用于恒定不变的数据,比如学生学号。
varchar是可变字符串,即它的空间分配大小是随着该字符串的长度而变化的,就像c++中的vector,可以实现动态扩展,前提也是不能超过最大长度。我个人习惯用varchar,几乎没用过char。
3.模式与表
每一个基本表都属于某一个模式,默认是dbo,而一个模式可以包含多个基本表,定义表时一般可以有三种方式定义它所属的模式
①在表名中明显地给出模式名
create table "S-T".Student(...); -- Student所属模式为S-T
②在创建模式语句中同时创建表
create schema test authorization fmmpmo;
create table tab1(
id int,
username varchar(20),
pwd varchar(20)
)
③设置所属的模式,这样在创建表时表名中不必给出模式名,系统根据搜索路径来确定,默认是$user,PUBLIC,即与用户名相同的模式名,当然也可以指定搜索路径
4.修改基本表
一律采用ALTER关键字开头:ADD用于增加新列,即增加新的属性;ALTER COLUMN用于修改某一列;DROP COLUMN用于删除某一列
4.1 向Student表中增加“入学时间”列,其类型为日期
alter table Student add Sentrance date;
4.2 将年龄的数据类型由字符型改为整数
alter table Student
alter column Sage int;
4.3 增加课程名必须取唯一值的约束条件
alter table Course
add unique(Cname)
5.删除基本表
同样不能写CASCADE和RESTRICT,因为不支持,还是一样的原理,RESTRICT表名删除有限制条件
drop table Student;
表上建立视图,若执行删除表操作也会有依赖
-- Student表上建立视图
create view IS_Student
as
select Sno,Sname,Sage
from Student
where Sdept = 'IS'
感想
一说SQL,脑海里经常想到删库跑路四个字,哪个程序员删库跑路并被传开后,估计他的程序生涯也快到头了,RESTRICT表明删除有限制条件,那么有没有一种方法,可以让有这些删库跑路的程序员不能得逞呢,比如某个公司的所有这种操作的最后一步是CEO,即便数据库人员可以一路删删删,改改改,到最后一步也需要经过CEO的同意,否则前边的一路猛如虎的操作也将失效,即做了一堆无效操作。。哈哈,这个想法很美好,但。。