|
|
数据库学完了，但是脑子里还是没有一个系统的数据库操作 概念。借着考试复习的这个机会，总结一下数据库的常用操作。内容参考上课的课件进行了整理，整篇文章会很长。
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 log2
1.2.5 增加文件组语法：alter database 数据库名add filegroup 文件组名操作：在数据库db1中增加一个g2文件组
alter database db1 add filegroup g2
1.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 = gl
1.3 删除数据库语法：Drop database 数据库名 [，……n]
操作：删除数据库DB1,DB2,DB3
DROP DATABASE DB1,DB2,DB3
2.架构与基本表
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.Test
2.3 删除架构
DROP SCHEMA T2
2.4 创建基本表2.4.1 表约束
类型： 主键（PRIMARY KEY）约束 惟一（UNIQUE）约束 外键（FOREIGN KEY）约束 检查（CHECK）约束 说明：非空和默认值也可看成是约束。
创建表约束的方法：新建表时，在单列后创建约束或者在所有列之后，再创建约束；如果表已存在，只能通过修改表，添加约束。语法：create table 表名 (字段名 类型[(长度)] [,……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）
操作2：在学生与课程表sc中,添加名ck_sc j的检查约束，该约束限制“成绩”在0到100之间
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' ) 操作2：修改已有的表
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 go
2.5.2 修改列
语法：alter table 表名alter column 列名 新类型[(长度[,小数位数])]
操作1：将sc表的grade列的数据类型修改为numeric(4,1)
alter table sc alter column grade numeric(4,1)
操作2：将sc表的grade列的数据类型修改为int
alter table sc alter column grade int
注意：1.不能修改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 逻辑表达式 ]
操作：删除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 ,course
2.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 逻辑表达式 ]
操作：将sc表中的成绩不及格的记录，插入到nopass表中,或者完全写出一一对应的列名。参考语法说明
insert into nopass select * from sc where grade<60
注意：1.查询的值与列名按顺序对应，要求值类型与列数据类型一致。2.对语句中无值对应的列名赋NULL。3.如果没有指明列名表，则新插入记录的值的顺序必须与表中列的顺序一致，且每一列均有值（可为空）
3.数据库查询3.1 基本查询语句结构
语法：
SELECT <目标列名序列> -- 需要哪些列 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 ]
3.2 单表查询3.2.1 未做处理的查询3.2.1.1 查询部分信息操作：查询student表中的全体学生姓名和年龄
select name,age from student
3.2.1.2 查询全部信息操作：查询全体学生的信息
select * from student
3.2.2 指定列别名语法：[ 列名 | 表达式 ] [ AS ] 列别名
操作：在Student表中查询学生的姓名，和年龄（年龄由计算得出），将年龄列命名为age
SELECT name,year(getdate()) - year(Birthdate) AS age FROM Student
3.2.3 去掉重复行语法：在要求不重复的数据列前使用distinct关键字
操作：
SELECT DISTINCT Sno FROM SC
3.2.4 where 指定查询条件
常用查询条件如下表
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 < 60
3.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 <=90
操作2：查询考试成绩不在80～90之间的学生学号、课程号和成绩
SELECT Sno, Cno, Grade FROM SC WHERE Grade NOT BETWEEN 80 AND 90 等价与下面这句话
SELECT Sno, Cno, Grade FROM SC WHERE Grade < 80 AND Grade > 90
3.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 '张%'
操作2：查询姓“张”、姓“李”和姓“刘”的学生的详细信息
select * from student where name like '[张刘李]%'
操作3：查询名字的第2个字为“小”或“大”的学生的姓名和学号
select * from student where name like '_[大小]%'
操作4：查询所有不姓“张”的学生姓名
select name from student where name not like '张%'
操作5：在Student表中查询学号的倒数第三为位不是1、2、3的学生信息
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 NULL
注意：空值不是一个确定的值，所以不可以用等于或不等于来比较或衡量
3.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
注意：OR的优先级小于AND，要改变运算的顺序可以通过加括号的方式实现
SELECT Sno, Cno, Grade FROM SC WHERE (Cno = 'C001' OR Cno = 'C002') AND Grade BETWEEN 80 AND 90
3.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
操作2：统计每个学生的选课门数和平均成绩。
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 Dept
3.2.14 HAVING 限制分组结果
语法：HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。
操作1： 查询选课门数超过3门的学生的学号和选课门数。
SELECT Sno, Count(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) > 3
处理过程：先执行GROUP BY子句对SC表数据按Sno进行分组，然后再用统计函数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.Sno 操作2：去除重复列。如果不指定查询结果的列名（如操作1）则直接将两个表拼接在一起，学号列会重复。为了避免重复，需要制定列名。
SELECT Student.Sno, Sname, Sex, Sage, Dept, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno 操作3：指定列别名 ，参照前面说过的格式：<源表名> [ AS ] <表别名>
SELECT 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语句中指定的条件
操作1:创建一个 名为“v1”的视图，用于查询计算机网络专业男生的学号、姓名、出生日期，并将视图的列名分别改为：学生学号、男生姓名、生日。
create view v1 (学生学号,男生姓名, 生日) as select 学号, 姓名, 出生日期 from xsqk where 专业名=‘计算机网络’ and 性别=1 go 使用视图
select * from v1
5.3 修改视图语法：
alter view 视图 [ (列名表) ] [ 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_StudentGrade1
6.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 <span style="font-family:Verdana;"> </span>where xsqk.学号=xs_kc.学号 and xsqk.学号= @xh go 执行：
exec p_xsqk ‘020102‘ <span style="font-family:Verdana;"> </span>--（1）按位置传递参数 exec p_xsqk @xh=‘020103‘ --（2）通过参数名传递参数 注意：因输入参数没有默认值，所以不能用“exec p_xsqk”
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 0 执行：
declare @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表! ’ go 执行下面这条语句后会触发insert触发器
insert 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触发器
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哈哈~
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2013-12-24 09:04:46
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡