前言
数据库学完了，但是脑子里还是没有一个系统的数据库操作 概念。借着考试复习的这个机会，总结一下数据库的常用操作。内容参考上课的课件进行了整理，整篇文章会很长。
1.数据库创建管理
1.1 创建数据库
CREATE DATABASE DB
ON PRIMARY
(
NAME = DB_data,
FILENAME = 'F:\DB_data1.mdf',
SIZE = 10MB,
MAXSIZE = UNLIMTED
),
(
NAME = DB_data2,
FILENAME = 'F:\DB_data2.ndf',
SIZE = 11MB,
MAXSIZE = 20MB,
FILEGROWTH = 2MB
)
LOG ON
(
NAME = DB_log1,
FILENAME = 'F:\DB_log1.ldf',
SIZE = 1MB,
MAXSIZE = 30MB,
FILEGROWTH = 10%
)1.2 修改数据库
1.2.1 增加数据文件
语法：
alter database 数据库名称
add file 数据文件
[to file group 文件组名称]
add log file 日志文件
操作：
ALTER DATABASE DB
ADD FILE
(
NAME = ,
FILENAME = ,
SIZE = ,
FILEGROWTH =
)1.2.2 增加日志文件
ALTER DATABASE DB
ADD LOG FILE
(
NAME = ,
FILENAME = ,
SIZE = ,
FILEGROWTH = ,
MAXSIZE =
)1.2.3 修改数据文件
语法：
ALTER DATABASE 数据库名
MODIFY FILE 文件属性
操作：将数据库db1中的数据文件data2的初始大小改为10MB，最大容量为20MB，增长幅度为10%
alter database db1
modify file
(
name = data2,
size = 10,
maxsize = 20,
filegrowth = 10%
)
ALTER DATABASE DB
MODIFY FILE
(
NAME = ,
SIZE = ,
)
1.2.4 删除数据文件和日志文件
语法：
alter database 数据库名称
remove file 数据文件或日志文件的逻辑文件名
操作：删除数据库db1中的数据文件data4和日志文件log2
alter database db1
remove data4
alter database db1
remove log21.2.5 增加文件组
语法：
alter database 数据库名
add filegroup 文件组名
操作：在数据库db1中增加一个g2文件组
alter database db1
add filegroup g21.2.6 重命名文件组
语法：
alter database 数据库名
modify filegroup 文件组名
name=新文件组名
操作：将数据库db1中的文件组g2更名为g3
alter database db1
modify filegroup g2 name=g3
1.2.7 删除文件组
语法：
alter database 数据库名称
remove filegroup 文件组名
操作：删除数据库db1的文件组g3
alter database db1
remove filegroup g3
1.2.8 修改数据库名称
语法：
alter database 数据库名
modify name = 新数据库名
操作：将数据库db1的名字修改为gl
alter database db1
modify name = gl1.3 删除数据库
语法：
Drop database 数据库名 [，……n]
操作：删除数据库DB1,DB2,DB3
DROP DATABASE DB1,DB2,DB32.架构与基本表
2.1 创建架构
CREATE SCHEMA T2 AUTHORIZATION User1
CREATE TABLE Test(C1 INT PRIMATY KEY, C2 CHAR(4) )
GRANT SELECT TO User2
DENY DELETE TO User3;
2.2 修改架构
ALTER SCHEMA T1 TRANSFER T2.Test2.3 删除架构
DROP SCHEMA T2 2.4 创建基本表
2.4.1 表约束
类型：
主键（PRIMARY KEY）约束
惟一（UNIQUE）约束
外键（FOREIGN KEY）约束
检查（CHECK）约束
说明：非空和默认值也可看成是约束。
创建表约束的方法：新建表时，在单列后创建约束或者在所有列之后，再创建约束；如果表已存在，只能通过修改表，添加约束。
语法：
create table 表名
(字段名 类型[(长度)] [,……n])
(字段名 类型[(长度)] [,……n])
操作：
CREATE TABLE Student
(
SNO CHAR(7) PRIMATY KEY,
SNAME NCHAR(5) NOT NULL,
SID CHAR(18) UNIQUE,
SEX NCHAR(1) DEFAULT 'MAN',
SAGE TINYINT CHECK (SAGE >= 15 AND SAGE <= 40)
SDEPT NVARCHAR(20)
)
CREATE TABLE Course
(
cno CHAR(6) PRIMARY KEY,
cname NVARCHAR(20) NOT NULL,
credit NUMRIC(3,1) CHECK (credit > 0),
senester TINYINT
)
CREATE TABLE SC
(
sno CHAR(7) NOT NULL,
cno CHAR(9) NOT NULL,
grade TINYINT,
PRIMARY KEY (sno, cno)
FOREIGN KEY (sno) REFERENCES Student (SNO)
FOREIGN KEY (cno) REFERENCES Course (cno)
)
2.4.1.1 PRIMARY KEY
主键约束的作用：
1.不允许输入重复的值
2.不能取空值 （当主键是由多个属性组成时：某一属性上的数据可以重复，但其组合必须是惟一的；每个属性的值都不能为空。）
3.一个表上只能有一个主键。
2.4.1.2 UNIQUE
惟一性约束的作用：保证列中不会出现重复的数据。
主键约束与惟一性约束的区别：
1.一个表中只能定义一个主键约束，但可以定义多个惟一约束。
2.定义了惟一约束的列数据可以为空值，而定义了主键约束的列数据不能为空值。
2.4.1.3 FOREIGN KEY
外间约束的作用：
用于建立和强制两个表间的关联，限制外键的取值必须是主表的主键值。
2.4.1.4 CHECK
检查约束的作用：
1.用来限制列上可以接受的数据值
2.使用逻辑表达式来判断数据合法性
2.4.1.5 DEFAULT
默认约束的作用：当列值未确定且该列又不能为空时，可由系统自动为该列添加一个值
2.4.2 添加主键约束
操作：在学生情况表student中，添加“sno”的主键约束,主键约束命名为pk_student
如果表不存在
create table student
( sno char (6),
sname char (8),
ssex bit ,
sphone char(11)
constraint pk_student
primary key (sno)
)
如果表已存在
alter table student
add constraint pk_student
primary key (sno)2.4.3 创建唯一性约束
操作：在student表中，创建“sphone”字段惟一性约束,并且将约束命名为uq_sphone
alter table student
add constraint uq_sphone
unique (sphone)
2.4.4 创建外键约束
create table sc
( sno char ( 6 ) not null references student ( sno ),
cno char ( 3 ),
grade tinyint
primary key (sno,cno),
foreign key ( cno ) references course ( cno )
)2.4.5 创建检查约束
操作1：在student表中，设置名为ck_student的检查约束，该约束限制“性别”为man或woman
alter table student
add constraint ck_student
check （sex=man or sex=woman）
alter table sc
add constraint ck_sc
check (grade>=0 and grade<=100)2.4.6 设置默认约束
操作1：新建表时添加
create table student
(
name char(6) not null,
age int(10) null,
sex char(5) default 'man'
)alter table student
add constraint df_student
default 'man' for sex
2.4.7 创建约束小结
create table student
(sno char (6) not null ,
sname char (8) not null ,
ssex bit , 电话 char(11) )
alter table xsqk
add constraint pk_xsqk_xh primary key (学号),
constraint df_xsqk_xb default 1 for 性别,
constraint ck_xsqk_xb check (性别=1 or 性别=0 ),
constraint uq_xsqk_dh unique (电话)
create table student
( sno char (6) primary key (学号) check (学号 like '[0-9] [0-9] [0-9] [0-9] [0-9] [0-9]'),
sname char (8) not null ,
ssex bit default 1 check (ssex=0 or ssex=1) ,
sphone char(11) unique (sphone)
)
2.4.8 删除约束
alter table student drop constraint pk_xsqk_xh
alter table student drop constraint uq_xsqk_dh
alter table student drop constraint ck_xsqk _xb
alter table student drop constraint ck_xsqk _xh
alter table student drop constraint df_xsqk_xb
2.5 修改基本表
2.5.1 增加列
语法：
alter table 表名
add <列定义>[<列约束>][ ,……n ]
关于“标识列”：
每个表中都可以有一个标识列，其作用是由系统自动生成能标识表中每一行数据的惟一序列值。（其实可以理解为行号）
“标识列”定义格式：
identity [ (seed, increment) ]
说明：seed为初始值，increment为增长的步长。意思就是你从几开始增长，每次增长几。省略时，初始值为1，步长为1.
操作：在student表中，增加三列
alter table student
add address char(10) constraint df_xsqk_jg default ‘shanghai’ ,
email varchar(30),
number int identity
go2.5.2 修改列
语法：
alter table 表名
alter column 列名 新类型[(长度[,小数位数])]
操作1：将sc表的grade列的数据类型修改为numeric(4,1)
alter table sc
alter column grade numeric(4,1)alter table sc
alter column grade int1.不能修改text、image、ntext、gimestamp类型的列；
2.不能修改类型是varchar、nvarchar、varbinary的列的数据类型，但可增加其长度。
3.不能修改是主键、外键列的类型，但可增加其长度；
4.不能修改包含索引、有默认值、检查约束和惟一性约束列的类型，但可增加其长度。
5.不能修改用列表达式定义或被引用在列表达式中的列。
6.不能修改复制列。
2.5.3 修改表中的数据
语法：
update 表名
set {列名 = 表达式 | null | default } [ , … n ] )
[ where 逻辑表达式 ]
操作：将sc表中的课程号为101的成绩不及格的学生的成绩都加上10分
update sc
set grade = grade + 10
where ( cno = '101' and grade < 60)2.6 删除基本表
2.6.1 删除列
注意：
若列上有约束，所以应先删除该约束后，再删除该列。
语法：
alter table 表名
drop column 列名
操作：
--先删除表中的约束
Alter table student drop constraint df_xsqk_jg
--再删除表中的列
Alter table student drop column address,email,number
2.6.2 删除行
语法：
delete [from]表名
[ where 逻辑表达式 ]
[ where 逻辑表达式 ]
操作：删除student表中姓名为zhangsan的数据记录
delete from student where name = 'zhangsan'2.6.3 重命名数据表
语法：
exec sp_rename '表名'，'新表名'
操作：将student表重命名为rename_student
exec sp_rename ‘student’,'rename_student'2.6.4 删除数据表
注意：如果要删除的表T1是其他表T2的参照表，即如果T2有外键约束，参照了T1的数据项，则不能删除。需要先取消T2表中的外键约束载删除T1，或者先删除T2表再删除T1表。
语法：
drop table 表名[ ,……n ]
操作：已知sc表设置了外键约束，参照了表student和表course，现在要删除student表和course表。
Drop table sc
go
Drop table student ,course2.7 向表中插入数据
2.7.1 插入单行数据
语法：
insert [ into ] 表名 [ (字段名列表) ] values (字段值列表)
操作：已知student有以下数据项：SNO,SNAME,SAGE,SEX,SAGE,SDEPT，现向student表中插入数据
INSERT INTO Student(SNO,SNAME,SAGE,SDEPT) VALUES ('132','ZHANGSAN',23,'ASD')
INSERT INTO Student VALUES ('1234','ZHANGSAN','143','MAN',22,'YI',)注意：字符型、日期型数据要用单引号括起来。
2.7.2 插入多行数据
语法：
insert [ into ] 目的表名 [ ( 字段列表 ) ]
select [ 源表名 . ] 列名 [ , … n ] from 源表名 [ , … n ]
[ where 逻辑表达式 ]
select [ 源表名 . ] 列名 [ , … n ] from 源表名 [ , … n ]
[ where 逻辑表达式 ]
操作：将sc表中的成绩不及格的记录，插入到nopass表中,或者完全写出一一对应的列名。参考语法说明
insert into nopass
select * from sc
where grade<60
注意：
1.查询的值与列名按顺序对应，要求值类型与列数据类型一致。
2.对语句中无值对应的列名赋NULL。
3.如果没有指明列名表，则新插入记录的值的顺序必须与表中列的顺序一致，且每一列均有值（可为空）
2.对语句中无值对应的列名赋NULL。
3.如果没有指明列名表，则新插入记录的值的顺序必须与表中列的顺序一致，且每一列均有值（可为空）
3.数据库查询
3.1 基本查询语句结构
语法：
SELECT <目标列名序列> -- 需要哪些列
FROM <表名> -- 来自于哪些表
[WHERE <行选择条件>] -- 根据什么条件
[GROUP BY <分组依据列>] --分组依据
[HAVING <组选择条件>]
[ORDER BY <排序依据列>]
FROM <表名> -- 来自于哪些表
[WHERE <行选择条件>] -- 根据什么条件
[GROUP BY <分组依据列>] --分组依据
[HAVING <组选择条件>]
[ORDER BY <排序依据列>]
<目标列名序列>部分能够包含的内容有如下结构：
SELECT [ ALL | DISTINCT ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ]
<select_list>
<select_list> ::=
{
*
| { table_name | view_name | table_alias }.*
| {
[ { table_name | view_name | table_alias }. ]
{ column_name | $IDENTITY } } ]
| expression
[ [ AS ] column_alias ] }
| column_alias = expression
} [ ,...n ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ]
<select_list>
<select_list> ::=
{
*
| { table_name | view_name | table_alias }.*
| {
[ { table_name | view_name | table_alias }. ]
{ column_name | $IDENTITY } } ]
| expression
[ [ AS ] column_alias ] }
| column_alias = expression
} [ ,...n ]
3.2 单表查询
3.2.1 未做处理的查询
3.2.1.1 查询部分信息
操作：查询student表中的全体学生姓名和年龄
select name,age from student3.2.1.2 查询全部信息
操作：查询全体学生的信息
select * from student3.2.2 指定列别名
语法：
[ 列名 | 表达式 ] [ AS ] 列别名
操作：在Student表中查询学生的姓名，和年龄（年龄由计算得出），将年龄列命名为age
SELECT name,year(getdate()) - year(Birthdate) AS age FROM Student3.2.3 去掉重复行
语法：
在要求不重复的数据列前使用distinct关键字
操作：
SELECT DISTINCT Sno FROM SC3.2.4 where 指定查询条件
常用查询条件如下表
| 查询条件 | 谓词 |
|---|---|
| 比较运算符 | 比较运算符 =, >, >=, <, <=, <>（或!=） |
| 确定范围 | BETWEEN AND, NOT BETWEEN AND |
| 确定集合 | IN, NOT IN |
| 字符匹配 | LIKE, NOT LIKE |
| 空值 | IS NULL, IS NOT NULL |
| 多重条件 | AND, OR |
操作：查询不及格的学生姓名
SELECT DISTINCT Sno FROM SC WHERE Grade < 603.2.5 查询范围
语法：
BETWEEN…AND …
NOT BETWEEN…AND…
说明：BETWEEN后是范围的下限，AND后是范围的上限
操作1：查询考试成绩在80～90之间的学生学号、课程号和成绩
SELECT Sno, Cno, Grade FROM SC WHERE Grade BETWEEN 80 AND 90 SELECT Sno, Cno, Grade FROM SC WHERE Grade >=80 AND Grade <=90SELECT Sno, Cno, Grade FROM SC WHERE Grade NOT BETWEEN 80 AND 90 SELECT Sno, Cno, Grade FROM SC WHERE Grade < 80 AND Grade > 903.2.6 IN 确定集合
语法：列名 [NOT] IN (常量1, 常量2, … )
作用：用来查找属性值属于指定集合的元组
操作1：查询信息管理系、通信工程系和计算机系学生的姓名和性别
select name, sex from sudent
where dept in ('信息管理系','通信工程','计算机系')
等价于
select name, sex from sudent
where dept = '信息管理系' or dept = '通信工程系' or dept = '计算机系'
操作2：查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别
SELECT Sname, Sex FROM Student
WHERE Dept NOT IN ( '信息管理系', '通信工程系', '计算机系')SELECT Sname, Sex FROM Student
WHERE Dept!= '信息管理系' AND Dept!= '通信工程系' AND Dept!= '计算机系'3.2.7 LIKE 字符串匹配
语法：
列名 [NOT] LIKE <匹配串> [ESCAPE <转义字符>]
说明：匹配串中可包含如下通配符：
1.%（百分号）：匹配0个或多个字符。
2._（下划线）：匹配一个字符。
3.[]：匹配方括号中的任何一个字符。
4.[^]：不匹配方括号中的任何一个字符。
如果比较的字符是连续的，则可以用连字符“-”表达，例如，要匹配b、c、d、e中的任何一个字符，则可以表示为：[b-e]
(说实话这不就是简化版的正则表达式么？)
操作1：查询姓“张”的学生详细信息
select * from student where name like '张%'select * from student where name like '[张刘李]%'select * from student where name like '_[大小]%'select name from student where name not like '张%'select * from student where number like '%[^123]__'3.2.8 ESCAPE 转义字符
语法：ESCAPE 转义字符
如果要查找的字符串正好含有通配符，比如下划线或百分号，就需要用ESCAPE来说明。其中“转义字符”是任何一个有效的字符，在匹配串中也包含这个字符，表明位于该字符后面的那个字符将被视为普通字符，而不是通配符。
操作：查找字段t1中包含字符串“30%”的记录
where t1 like '%30!%%' escape '!'3.2.9 NULL 空值查询
语法： 列名 IS [NOT] NULL
空值是未确定的值或其值尚不知道。
操作：查询还没有考试的学生的学号和相应的课程号
SELECT Sno, Cno FROM SC WHERE Grade IS NULL3.2.10 AND OR 多重条件查询
语法：当需要多个查询条件时，可以在WHERE子句中使用逻辑运算符AND和OR来组成多条件查询
操作：查询C002和C003课程中考试成绩在80～90的学生的学号、课程号和成绩
SELECT Sno, Cno, Grade FROM SC
WHERE Cno IN( 'C002', 'C003')
AND Grade BETWEEN 80 AND 90
SELECT Sno, Cno, Grade FROM SC
WHERE (Cno = 'C001' OR Cno = 'C002')
AND Grade BETWEEN 80 AND 903.2.11 ORDER BY 对查询结果排序
语法：ORDER BY <列名> [ASC | DESC ] [,<列名> … ]
说明：按<列名>进行升序（ASC）或降序（DESC）排序；当用多个列排序时，这些列在该子句中出现的顺序决定了对结果集进行排序的方式
操作：查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列
SELECT * FROM Student
ORDER BY Dept ASC, Birthdate DESC
3.2.12 使用聚合函数
语法：
COUNT（*）：统计表中元组的个数。
COUNT（[DISTINCT] <列名>）：统计列值个数
SUM（<列名>）：计算列值的和值（必须是数值型列）。
AVG（<列名>）：计算列值的平均值（必须是数值型列）。
MAX（<列名>）：得到列值的最大值。
MIN（<列名>）：得到列值的最小值。
除COUNT（*）外，其他函数在计算过程中均忽略NULL值。
操作1：统计选修了课程（SC）的学生人数。
SELECT COUNT(DISTINCT Sno) FROM SC
操作2：计算学号为“0811101”的学生的考试总成绩
SELECT SUM(Grade) FROM SC WHERE Sno = '0811101'
注意！：聚合函数不能出现在WHERE子句中。
操作：查询学分最高的课程名，如下写法是错误的!
SELECT Cname FROM Course WHERE Credit = MAX(Credit)declare @credit int
select @credit=max(credit) from course
select cname from course where credit=@credit
3.2.13 GROUP BY 分组
语法：
[GROUP BY <分组条件>]
作用：细化聚合函数的作用对象
操作1：统计每门课程的选课人数，列出课程号和选课人数。对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求出每组的学生人数。
SELECT Cno, COUNT(Sno) FROM SC
GROUP BY Cno
SELECT Sno as 学号, COUNT(*) as 选课门数, AVG(Grade) as 平均成绩 FROM SC
GROUP BY Sno
1.GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的列别名。
2.带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列和统计函数，因为分组后每个组只返回一行结果。
操作3：带WHERE子句的分组。统计每个系的女生人数。
SELECT Dept, Count(*) 女生人数 FROM Student
WHERE Sex = '女'
GROUP BY Dept操作4：按多个列分组。统计每个系的男生人数和女生人数，结果按系名的升序排序。
SELECT Dept, Sex, Count(*) 人数, FROM Student
GROUP BY Dept, Sex
ORDER BY Dept3.2.14 HAVING 限制分组结果
语法：HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。
操作1： 查询选课门数超过3门的学生的学号和选课门数。
SELECT Sno, Count(*) 选课门数 FROM SC
GROUP BY Sno HAVING COUNT(*) > 3
分组的优先级大于查询，having是分组内操作。
操作2：查询选课门数大于等于4门的学生的平均成绩和选课门数
SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 选课门数
FROM SC
GROUP BY Sno
HAVING COUNT(*) >= 4
3.2.15 小结
1.在分组操作之前应用的筛选条件，比在WHERE子句中指定更有效。
2.在HAVING子句中指定的筛选条件，应该是那些必须在执行分组操作之后应用的筛选条件。
3.将所有应该在分组之前进行的筛选条件放在WHERE子句中而不是HAVING子句中。
3.3 多表查询
多表连接查询分类三种：
1.内连接(INNER JOIN)：
分为三种：等值连接、自连接、不等连接
2.外连接(OUTER JOIN)：
分为三种：左外连接、右外连接、全外连接
3.交叉连接(CROSS JOIN) ：
没有WHERE子句，它返回连接表中所有数据
行的笛卡尔积
3.3.1 内连接
语法：ANSI方式的连接格式
FROM 表1 [INNER] JOIN 表2 ON <连接条件>
连接条件语法格式：[<表名1.>][<列名1>]<比较运算符>[<表名2.>][<列名2>]
内连接执行过程：
首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。
表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， …
重复这个过程，直到表1中的全部元组都处理完毕为止。
操作1：查询学生的选课情况。学生信息在student表中，选课信息在sc表中。两张表都存放着学生的学学号sno。
SELECT * FROM Student
INNER JOIN SC
ON Student.Sno=SC.SnoSELECT Student.Sno, Sname, Sex, Sage, Dept, Cno, Grade FROM Student
JOIN SC ON Student.Sno = SC.SnoSELECT Sname, Cno, Grade
FROM Student S JOIN SC
ON S.Sno = SC.Sno
WHERE Dept = '计算机系'操作4：三张表的连接查询。查询“信息管理系”修了“计算机文化学” 的学生姓名和成绩。
SELECT Sname, Grade
FROM Student s
JOIN SC ON s.Sno = SC. Sno
JOIN Course c ON c.Cno = SC.Cno
WHERE Dept = '信息管理系'
AND Cname = '计算机文化学'
操作5：综合使用聚合函数、多表连接、分组。
有分组和行选择条件的多表连接查询。统计计算机系学生每门课程的选课人数、平均成绩、最高成绩和最低成绩。
SELECT Cno, COUNT(*) AS Total,
AVG(Grade) as AvgGrade,
MAX(Grade) as MaxGrade,
MIN(Grade) as MinGrade
FROM Student S JOIN SC ON S.Sno = SC.Sno
WHERE Dept = '计算机系'
GROUP BY Cno
4.索引
4.1 创建索引
语法：
create [ unique ] [ clustered | nonclustered ] index 索引名
on { 表名 | 视图名 } ( 列名 [ asc | desc ] [ , ...n ] )
注意：
1.一个表中只能创建1个聚集索引。（由于系统已自动在主键上创建了聚集索引，所以用户不能再创建，除非先删除已有的索引，重新创建）
2.一个表中可以创建若干个非聚集索引。
操作：在kc表中，重新创建名为“ix_kcm”的索引，使其成为惟一性的非聚集索引
create unique index ix_kcm
on kc ( 课程名 desc)
with drop_existing --删除已存在的索引，创建新的索引4.2 删索引
语法：drop index {表名 . | 视图名 . } 索引名 [ , … n ]
注意：
SQL Server系统自动建立的索引不能用drop index删除，只能用alter table语句中的drop constraint子句来解除加在该字段上的主键约束或惟一性约束，这些约束一解除，相关的索引也就被删除了。
4.3 查看索引
语法：[exec] sp_helpindex {表名 | 视图名 }
5.视图
5.1 视图介绍
5.1.1 视图的含义和作用
视图是基于某个查询结果的虚表。是用户查看和修改数据表中数据的一种方式。每个视图都有几个被定义的列和多个数据行。
5.1.2 视图与基本表
1.视图中的数据列和行来源于其所引用的基表。
2.视图所对应的数据并不实际存储在数据库中，而是仍存储在视图所引用的基表中。
3.数据库中只存储视图的定义。
5.1.3 使用视图的目的与好处
1.聚焦特定数据：使用户只能看到和操作与他们有关的数据，提高了数据的安全性。
2.简化数据操作：使用户不必写复杂的查询语句就可对数据进行操作。
3.定制用户数据：使不同水平的用户能以不同的方式看到不同的数据。
4.合并分离数据：视图可以从水平和垂直方向上分割数据，但原数据库的结构保持不变。
5.2 创建视图
语法：
create view 视图
[ (列名表) ]
[ with encryption ] --用于加密视图的定义，用户只能查看不能修改。
as
select查询语句
[ with check option ] --强制所有通过是同修改的数据，都要满足select语句中指定的条件
[ (列名表) ]
[ with encryption ] --用于加密视图的定义，用户只能查看不能修改。
as
select查询语句
[ with check option ] --强制所有通过是同修改的数据，都要满足select语句中指定的条件
操作1:创建一个 名为“v1”的视图，用于查询计算机网络专业男生的学号、姓名、出生日期，并将视图的列名分别改为：学生学号、男生姓名、生日。
create view v1 (学生学号,男生姓名, 生日)
as
select 学号, 姓名, 出生日期 from xsqk
where 专业名=‘计算机网络’ and 性别=1
goselect * from v15.3 修改视图
语法：
alter view 视图
[ (列名表) ]
[ with encryption ]
as
select查询语句
[ with check option ]
[ (列名表) ]
[ with encryption ]
as
select查询语句
[ with check option ]
操作：在“v1”的视图中增加两列：专业名和所在系。
alter view v1
(学生学号,男生姓名,生日,专业,系)
as
select 学号,姓名,出生日期,专业名,所在系
from xsqk
where 专业名=‘计算机网络’ and 性别=1
）5.4 删除视图
语法：
drop view 视图名[ ,……n ]
5.5 通过视图管理表中的数据
5.5.1 使用视图插入数据
注意：
1.可通过视图向基表中插入数据，但插入的数据实际上存放在基表中，而不是存放在视图中。
2.如果视图引用了多个表，使用insert语句插入的列必须属于同一个表。
3.若创建视图时定义了“with check option”选项，则使用视图向基表中插入数据时，必须保证插入后的数据满足定义视图的限制条件。
操作1：向“V1”视图中添加两条记录。
insert into v1
values(‘020106’,‘张三’,‘1981-04-22’,‘计算机网络’,‘计算机’)
insert into v1
values(‘020107’,‘张四’,‘1981-07-08’,‘信息安全’,‘计算机’)5.5.2 使用视图删除数据
注意：
1.要删除的数据必须包含在视图的结果集中。
2.如果视图引用了多个表时，无法用delete命令删除数据。
语法：
delete from 视图名 [ where 条件]
操作：删除“V1”视图中学号为‘020108’的记录。
delete from V1 where sno = ‘020108’6.存储过程和触发器
6.1 存储过程
存储过程实际上就是数据库里的函数6.1.2 创建并执行存储过程
创建存储过程语法：
CREATE PROC[EDURE] 存储过程名
[ { @参数名 数据类型 } [ = default ] [OUTPUT] ] [ , … n ]
AS SQL语句 [ … n ]
执行存储过程
语法：
[ EXEC [ UTE ] ] 存储过程名 [实参 [, OUTPUT] [, … n] ]
6.1.3 不带参数的存储过程
操作：查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade1
AS
SELECT Sname, Cname, Grade
FROM Student s INNER JOIN SC
ON s.Sno = SC.Sno INNER JOIN Course c
ON c.Cno = sc.Cno
WHERE Dept = '计算机系'EXEC p_StudentGrade16.1.4 使用输入参数
语法：create proc[edure] 存储过程名
@形参 数据类型 [=默认值] ，…n
as SQL语句
执行：
[execute] 存储过程名 [ @实参= ] 值 ,…n
注意：
执行存储过程时输入参数的传递方式由三种（让我想到了python ^_^）
1.按位置传递：直接给出参数的值，实参与形参一一对应
2.通过参数名传递：使用“参数名=参数值“的形式，参数可以任意顺序给出
3.如果在定义存储过程时为参数指定了默认值，则在执行存储过程时可以不为有默认值的参数提供值。
操作：创建并执行带输入参数的存储过程p_xsqk，查询指定学号(作为输入参数)的学生姓名、课程号、成绩。
create procedure p_xsqk @xh char(6)
as select 姓名，课程号，成绩 from xsqk , xs_kc
where xsqk.学号=xs_kc.学号 and xsqk.学号= @xh
go
exec p_xsqk ‘020102‘ --（1）按位置传递参数
exec p_xsqk @xh=‘020103‘ --（2）通过参数名传递参数6.1.5 使用输出参数
语法：
create proc[edure] 存储过程名
@形参 数据类型 output ，…n
as SQL语句
执行：
[execute] 存储过程名 @实参 output ,…n
说明：
1.输出实参和输出形参的名字可以相同，也可以不同。
2.使用时，要先声明输入和输出实参变量。
操作：创建1个带有输入参数和输出的存储过程p_kh，返回指定教师(作为输入参数)所授课程的课程号(作为输出参数)。
create procedure p_kh
@teacher char(8) , @kch char(3) output
as
select @kch = 课程号 from kc where 授课教师= @teacher
go
declare @teacher varchar(8), @kch char(3)
set @teacher='赵怡'
exec p_kh @teacher, @kch output
print @teacher + ‘教师所受课程的课程号为:’ + @kch
6.1.6 使用返回值
语法：
return 整型表达式
作用：用于显示存储过程的执行情况
执行：
[execute] @状态值=存储过程名
操作：创建并执行存储过程p_find，用于查找指定的学生，如果找到，则返回数字1，否则返回0。
create procedure p_find
@findname char(8)
as
if exists (select * from xsqk
where 姓名=@findname)
return 1
else
return 0declare @result int
exec @result=p_find ‘陈伟‘
if @result =1
print ‘有这个人！‘
else
print ‘ 没有这个人！6.1.7 删除存储过程
语法：drop proc[edure] 存储过程名
6.1.8 查看存储过程
语法：
sp_help 存储过程名 --显示存储过程的基本信息
sp_helptext 存储过程名 --显示存储过程的源代码
6.1.9 修改存储过程
语法：
alter proc[edure] 存储过程名
[@形参 数据类型 [=默认值] [output ]，…n ]
as SQL语句
注意：
1.修改存储过程的定义后，原存储过程的权限设置仍有效
2.如果采用先删除存储过程再重建同名存储过程的方法，那么在原来存储过程上设置的权限将会全部丢失。
6.2 触发器
触发器就是是一种表或视图执行insert、 delete、update操作时，被系统自动执行的特殊的存储过程。
6.2.1 创建触发器
语法：
create trigger 触发器名
on 表名| 视图名
for | after | instead of [ insert , update, delete ]
as SQL语句
注意：1个表上可有多个触发器。 每个触发器只能作用在一个表上。这是一个一对多的关系
6.2.1.1 创建insert触发器
操作：在xscj库的xs_kc表上创建1个名为tr_insert_cj的触发器，当向xs_kc表进行插入操作时激发该触发器，并给出提示信息“有新成绩插入到xs_kc表中!”
create trigger tr_insert_cj
on xs_kc after insert
as print ‘有新成绩信息插入到xs_kc表! ’
goinsert into xs_kc values( '020105', '101', 87, null )
6.2.1.2 创建update触发器
操作：在student表上创建名为tr_update_xsqk2的触发器，当对该表的“姓名”列修改时激发该触发器，使用户不能修改“姓名”列。
create trigger tr_update_xsqk2 on student after update
as
if update(姓名)
begin
rollback transaction -- 撤消修改操作
raiserror(‘不能修改学生姓名！’ , 16 ,1)
end
go
update student set 姓名=‘小花’ where 姓名=‘杨颖’
6.2.1.3 创建delete触发器
操作：在xscj库的xsqk表上创建1个名为tr_delete_xsqk的触发器，当要删除指定学号的行时，激发该触发器，撤消删除操作，并给出提示信息“不能删除xsqk表中的信息
create trigger tr_delete_xsqk
on xsqk
after delete
as
rollback transaction
print ‘不能删除xsqk表中的信息!’
go执行下面这条语句会触发delete触发器
delete xsqk where 学号= '020101'6.2.2 更新触发器
语法：
alter trigger 触发器名
6.2.3 删除触发器
语法：
drop trigger 触发器名[,…n]
on {database | all server}
参考资料
《数据库原理及应用》 课件
总结
不得不说，课件上有很多错误。起的名字还是拼音简写，⊙﹏⊙b汗！。改了一部分，还有部分没改。个人觉得课堂上学道的东西不会太多，还是自己多动手比较好。像我这样把课件给整理一遍，估计整个计算机系再也找不出来第二个这样的奇葩了～O(∩_∩)O哈哈~
1620
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
