数据库原理与应用2-7章知识点及例题
博客地址:
https://blog.csdn.net/holly_Z_P_F/article/details/85466177
第二章 创建和管理数据库
2-1
创建数据库 指定主数据文件 以及事务日志文件
//创建数据库(不指定文件)
create database student
//创建名为student2的数据库
指定逻辑文件名为student2_dat
磁盘文件名为E:\Database\student2.mdf
该文件默认为主数据文件,size=4MB maxsize为10MB 增量为每次1MB
并将自动创建一个1MB的事务日志文件student2_log.ldf
create database student2
on
(
name=student2_dat,
filename='E:Database\student2.mdf',
size=4,
maxsize=10,
filegrowth=1
)
//创建名字为student3的数据库
第一个数据文件 student3_dat 默认为主数据文件
student3_dat文件初始大小为10MB,每次增长5%,最多可以增长到500MB
指定事务日志文件逻辑名为student3_log 磁盘文件名为E:\Database\student3_log.ldf
该日志文件初始大小为5MB 增长量为每次5MB 最大为25MB
create database student3
on
(
name=student3_dat,
filename='E:\Database\student3_dat.mdf',
size=10MB,
maxsize=500MB,
filrgrowth=5%
)
log on
(
name=student3_log,
filename='E:\Database\student3_log.ldf',
size=5MB,
maxsize=25MB,
filegrowth=5
)
//这个例子,创建名为student4的数据库,定义该数据库包含的两个100MB的数据文件和两个100MB的事务日志文
件。主数据文件是列表中第一个文件,并使用primary 关键字显式指定。
事务日志文件在 log on 关键字之后指定。注意filename项中所用的文件扩展名:主数据文件使用.mdf 次数据文件
使用.ndf 事务日志文件使用.ldf
create database student4
on
primary
(name=student4_dat1,
filename='E:\Database\student4_dat1.mdf',
size=100MB,
maxsize=200,
filegrowth=20), /* 这里要有逗号 */
(name=student4_dat2,
filename='E:\Database\student4_dat2.ndf',
size=100MB,
maxsize=200,
filegrowth=20) /* 这里不需要逗号 */
log on
(name=student4_log1,
filename='E:\Database\student4_log1.ldf',
size=100MB,
maxsize=200,
filegrowth=20), /* 这里要有逗号 */
(name=student4_log2,
filename='E:\Database\student4_log2.ldf',
size=100MB,
maxsize=200,
filegrowth=20)
2-2
创建 包含多个文件组的数据库
//本例创建了名为student5的数据库 该数据库包含2个文件组
1、主文件组包含主数据文件student5_dat1,存放在E:\Database 文件增量为15%
2、student5_Group1文件组包含次数据文件student5_dat2,存放在E:\Database
create database student5
on
primary /* 默认的Primary文件组 */
(name=student5_dat1,
filename='E:\Database\student5_dat1.mdf',
size=10,
maxsize=50,
filegrowth=15%), /*别忘记逗号*/
filegroup student5_Group1 /* student5_Group1文件组 */
(name=student5_dat2,
filename='E:\Database\student5_dat2.ndf',
size=10,
maxsize=50,
filegrowth=5)
log on
(name=student5_log,
filename='E:\Database\student5_log.ldf',
size=5MB,
maxsize=25MB,
filegrowth=5MB)
2-3
修改数据库 向数据库中添加主数据文件以及次数据文件 并指定文件组
//修改数据库的名字
//将student数据库的名字改为newstudent
exec sp_renamedb 'student','naestudent'
/*修改2-2所创建的数据库 为其添加一个逻辑文件名为student2_dat2的先数据文件
该数据文件为次数据文件*/
alter database student2
add file
(
name=student2_dat2,
filename='E:\Database\student2_dat2.ndf',
size=5MB,
maxsize=100MB,
filegrowth=5MB
)
//向2-2创建的student2数据库中添加由两个文件组成的文件组
//首先,在student2中建一个文件组 student_Group1
//然后 向该文件组添加两个数据文件 student2g1f1_dat、student2g1f2_dat
//最后 将该文件组设置为默认文件组
/*添加文件组 */
alter database student2
add filegroup student2_Group1
/* 添加文件到文件组里 部分重复操作就省略不写了*/
alter database student2
add file
(name=.......... ,
filename='E:\Database\student2g1f1_dat.ndf',
size= .........
maxsize=......
),
(name=
filename='E:\Database\student2g1f2_dat.ndf',
..........
)
to filegroup student2_Group1
/*指定默认文件组*/
alter database student2
modify filegroup student2_Group1 default
2-4
向数据库中添加日志文件
alter database student2
add log file
(name=
filename='E:\Database....'
............./*与上边创建日志文件相同*/
)
2-5
删除数据库
drop database student5
drop database student5,student4,student3, /*删除多个数据库*/
第三章 创建和管理表
3-1
创建表和创建临时表
/*创建专业表 共有三列:专业名称(varchar) 成立年份(smalldatetime) 专业简介(varchar)
设置专业名称和成立年份非空 指定专业表保存在primary文件组中
*/
create table 专业
(专业名称 varchar(30) not null,
成立年份 smalldatetime not null,
专业简介 varchar(50)
) on [ primary ]
/*创建临时表*/
create table #student
(学号 varchar(8),
姓名 varchar(10),
性别 varchar(2)
)
3-2
更改和删除表
/*在学生表中添加新列‘联系电话’ 且允许为空*/
alter table 学生
add 联系电话 varchar(15) null
/*删除学生表中联系电话列*/
alter table 学生
drop column 联系电话
/*将学生表中专业名称字段改为varchar(50)数据类型 并且不允许为空*/
alter table 学生
alter column 专业名称 varchar(50) not null
/*删除表*/
drop table 专业
3-4
维护表(插入和更新)
/*使用insert插入数据 在课程表中插入一行*/
/*按照指定顺序添加行*/
insert 课程 (课程名称,课程编号,课程类别,学分)
values('大学物理','C903‘,'必修',4)
/*不指定顺序 默认按照表中顺序添加行*/
insert 课程 values('C903','大学物理','必修',4)
/*使用updat语句更新表中数据*/
update 课程 set 学分= 学分+1 where 课程类别='必修'
3-5
使用delete删除表中指定行
/*删除课程表中课程编号为C607的行*/
delete from 课程 where 课程编号 ='C607'
/*删除学生表中所有行*/
delete 学生
第四章 索引与数据完整性
4-1
首先 创建索引中有些语法需要注意一下
unique 为表或视图创建唯一索引
clustered 创建聚集索引 不指定clustered 则创建非聚集索引
asc | desc 确定特定索引列的升序或者降序 默认值是asc
4-2
创建索引
/*为“图书信息”表的图书编号列创建唯一性索引book_id_ind。*/
create unique index book_id_ind on 图书信息(图书编号)
/*为BookDb数据库的“图书信息”表的“图书编号”字段创建一个非聚集索引,命名为book_index。*/
create index book_index on 图书信息(图书编号)
/*为BookDb数据库的“借阅信息”表的“借阅证号”和“图书编号”字段创建一个复合唯一索引,命名为
book_reader_ind。*/
create unique index book_reader_ind on 借阅信息(借阅证号,图书编号)
4-3
查看、更改、删除索引
/*查看索引 查看学生表上的索引*/
exec sp_helpindex 学生
/*更改学生表中索引name_index的名称为student_name_index */
sp_rename '学生.name_index','student_name_index','index'
/*删除索引*/
drop index student_name_index on 学生
/*删除选课表中的主键约数(pk_选课)索引*/
alter table 选课
drop constraint pk_选课
4-4
创建规则
//为读者信息表的联系电话创建一个规则phone_rule,限制所输入的数据为8位或者11位0~9的数字。
create rule phone_rule
as @联系电话 like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or
@联系电话 like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
4-5
将规则绑定到列上和解除绑定
/*使用系统存储过程sp_bindrule将phone_rule规则绑定到读者信息表的“联系电话”列上。*/
exec sp_bindrule phone_rule,'读者信息.联系电话'
/*创建一个规则gd_rule,将其绑定到“读者信息”表的“性别”列上,保证输入的性别值只能是“男”或“女”。
*/
create rule gd_rule
as @性别 like '男' or @性别 like '女'
exec sp_bindrule gd_rule,'读者信息.性别'
/*解除绑定在课程表的课程类别列上的规则*/
exec sp_unbindrule '课程.课程类别'
4-6
查看规则和删除规则
/*使用sp_helptext查看gd_rule规则*/
sp_helptext gd_rule
/*删除gender_rule规则。*/
sp_unbindrule '读者信息.性别' /*先要解绑 才能删除*/
drop rule gender_rule
4-7
创建默认值和查看默认值
/* 创建日期型默认对象df_date,默认值为‘2015-9-1’。*/
create default df_date as '2015-9-1'
/*创建默认值 nationality_default*/
create default nationality_default as '汉族'
/*查看默认值 nationality_default*/
exec sp_helptext nationality_default
4-8
绑定默认值和解绑默认值以及删除默认值
/*使用系统存储过程sp_bindefault将默认对象df_date绑定在读者_备份表的“办卡时间”列上。*/
exec sp_bindefault df_date,'读者_备份.办卡时间'
/*解除默认对象df_date的绑定,并删除之。*/
exec sp_unbindefault '读者_备份.办卡时间'
drop default df_date
4-9
主键约束
/* 为BookDb数据库的“读者信息”表创建主键约束,名称形如PK_列名。*/
alter table 读者信息
add constraint PK_借阅证号 primary key(借阅证号)
/*为BookDb数据库的“借阅信息”表创建联合主键约束,名称形如PK_列名_列名。*/
alter table 借阅信息
add constraint PK_借阅证号_图书编号 primary key(借阅证号,图书编号)
4-10
外键约束
/* 为“借阅信息”表的“借阅证号”添加外键约束,名称形如FK_列名。*/
alter table 借阅信息
add constraint FK_借阅证号 foreign key(借阅证号)
references 读者信息(借阅证号)
/*删除“借阅信息”表中图书编号列的外键约束。重新创建此列外键为级联删除。*/
/*******
注意 要理解部分关键字的含义
references 指定要建立关联的表的名称
on delete{cascade|no action}
如果设置为cascade 当主键表中某行被删除时 外键表中所有相关行将被删除
如果设置为no action 当主键表中某行被删除时 SQL将报错 默认设置是no action
*******/
alter table 借阅信息
drop constraint FK_图书编号
alter table 借阅信息
add constraint FK_图书编号 foreign key(图书编号)
references 图书信息(图书编号)
on delete cascade
4-11
非空约束。
/*为读者信息表的“借阅证件类型”字段设置非空约束。*/
alter table 读者信息
alter column 借阅证件类型 varchar(10) not null