1.数据库创建管理
1.1 创建数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 日志文件
操作:
1 2 3 4 5 6 7 8 | ALTER DATABASE DB ADD FILE ( NAME = , FILENAME = , SIZE = , FILEGROWTH = ) |
1.2.2 增加日志文件
1 2 3 4 5 6 7 8 9 | ALTER DATABASE DB ADD LOG FILE ( NAME = , FILENAME = , SIZE = , FILEGROWTH = , MAXSIZE = ) |
1.2.3 修改数据文件
语法:
ALTER DATABASE 数据库名
MODIFY FILE 文件属性
操作:将数据库db1中的数据文件data2的初始大小改为10MB,最大容量为20MB,增长幅度为10%
1 2 3 4 5 6 7 8 | alter database db1 modify file ( name = data2, size = 10, maxsize = 20, filegrowth = 10% ) |
1 2 3 4 5 6 | ALTER DATABASE DB MODIFY FILE ( NAME = , SIZE = , ) |
1.2.4 删除数据文件和日志文件
语法:
alter database 数据库名称
remove file 数据文件或日志文件的逻辑文件名
操作:删除数据库db1中的数据文件data4和日志文件log2
1 2 3 4 5 | alter database db1 remove data4 alter database db1 remove log2 |
1.2.5 增加文件组
语法:
alter database 数据库名
add filegroup 文件组名
操作:在数据库db1中增加一个g2文件组
1 2 | alter database db1 add filegroup g2 |
1.2.6 重命名文件组
语法:
alter database 数据库名
modify filegroup 文件组名
name=新文件组名
操作:将数据库db1中的文件组g2更名为g3
1 2 | alter database db1 modify filegroup g2 name=g3 |
1.2.7 删除文件组
语法:
alter database 数据库名称
remove filegroup 文件组名
操作:删除数据库db1的文件组g
1 2 | alter database db1 remove filegroup g3 |
1.2.8 修改数据库名称
语法:
alter database 数据库名
modify name = 新数据库名
操作:将数据库db1的名字修改为gl
1 2 | alter database db1 modify name = gl |
1.3 删除数据库
语法: Drop database 数据库名 [,……n]
操作:删除数据库DB1,DB2,DB3
1 | DROP DATABASE DB1,DB2,DB3 |
2.架构与基本表
2.1 创建架构
1 2 3 4 | 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 修改架构
1 | ALTER SCHEMA T1 TRANSFER T2.Test |
2.3 删除架构
1 | DROP SCHEMA T2 |
2.4 创建基本表
2.4.1 表约束
类型:
主键(PRIMARY KEY)约束
惟一(UNIQUE)约束
外键(FOREIGN KEY)约束
检查(CHECK)约束
说明:非空和默认值也可看成是约束。
创建表约束的方法:新建表时,在单列后创建约束或者在所有列之后,再创建约束;如果表已存在,只能通过修改表,添加约束。
语法:
create table 表名
(字段名 类型[(长度)] [,……n])
操作:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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
如果表不存在
1 2 3 4 5 6 7 8 | create table student ( sno char (6), sname char (8), ssex bit , sphone char(11) constraint pk_student primary key (sno) ) |
如果表已存在
1 2 3 | alter table student add constraint pk_student primary key (sno) |
2.4.3 创建唯一性约束
操作:在student表中,创建“sphone”字段惟一性约束,并且将约束命名为uq_sphone
1 2 3 | alter table student add constraint uq_sphone unique (sphone) |
2.4.4 创建外键约束
1 2 3 4 5 6 7 | 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
1 2 3 | alter table student add constraint ck_student check (sex=man or sex=woman) |
操作2:在学生与课程表sc中,添加名ck_sc j的检查约束,该约束限制“成绩”在0到100之间
1 2 3 | alter table sc add constraint ck_sc check (grade>=0 and grade |
2.4.6 设置默认约束
操作1:新建表时添加
1 2 3 4 5 6 | create table student ( name char(6) not null, age int(10) null, sex char(5) default 'man' ) |
操作2:修改已有的表
1 2 3 | alter table student add constraint df_student default 'man' for sex |
2.4.7 创建约束小结
1 2 3 4 5 6 7 8 9 | 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 (电话) |
1 2 3 4 5 6 | 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 删除约束
1 2 3 4 5 | 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表中,增加三列
1 2 3 4 5 | 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)
1 2 | alter table sc alter column grade numeric(4,1) |
操作2:将sc表的grade列的数据类型修改为int
1 2 | 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分
1 2 3 | update sc set grade = grade + 10 where ( cno = '101' and grade < 60) |
2.6 删除基本表
2.6.1 删除列
注意:若列上有约束,所以应先删除该约束后,再删除该列。
语法: alter table 表名 drop column 列名
操作:
1 2 3 4 | --先删除表中的约束 Alter table student drop constraint df_xsqk_jg --再删除表中的列 Alter table student drop column address,email,number |
2.6.2 删除行
语法: delete [from]表名
[ where 逻辑表达式 ]
操作:删除student表中姓名为zhangsan的数据记录
1 | delete from student where name = 'zhangsan' |
2.6.3 重命名数据表
语法: exec sp_rename '表名','新表名'
操作:将student表重命名为rename_student
1 | 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表。
1 2 3 | Drop table sc go Drop table student ,course |
2.7 向表中插入数据
2.7.1 插入单行数据
语法: insert [ into ] 表名 [ (字段名列表) ] values (字段值列表)
操作:已知student有以下数据项:SNO,SNAME,SAGE,SEX,SAGE,SDEPT,现向student表中插入数据
1 2 | 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表中,或者完全写出一一对应的列名。参考语法说明
1 2 3 | insert into nopass select * from sc where grade |
注意: 1.查询的值与列名按顺序对应,要求值类型与列数据类型一致。
2.对语句中无值对应的列名赋NULL。
3.如果没有指明列名表,则新插入记录的值的顺序必须与表中列的顺序一致,且每一列均有值(可为空)
3.数据库查询
3.1 基本查询语句结构
语法:
SELECT -- 需要哪些列
FROM -- 来自于哪些表
[WHERE ] -- 根据什么条件
[GROUP BY ] --分组依据
[HAVING ]
[ORDER BY ]
部分能够包含的内容有如下结构:
SELECT [ ALL | DISTINCT ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ]
::=
{
*
| { 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表中的全体学生姓名和年龄
1 | select name,age from student |
3.2.1.2 查询全部信息
操作:查询全体学生的信息
1 | select * from student |
3.2.2 指定列别名
语法: [ 列名 | 表达式 ] [ AS ] 列别名
操作:在Student表中查询学生的姓名,和年龄(年龄由计算得出),将年龄列命名为age
1 | SELECT name,year(getdate()) - year(Birthdate) AS age FROM Student |
3.2.3 去掉重复行
语法:在要求不重复的数据列前使用distinct关键字
操作:
1 | 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 |
操作:查询不及格的学生姓名
1 | SELECT DISTINCT Sno FROM SC WHERE Grade < 60 |
3.2.5 查询范围
语法:
BETWEEN…AND …
NOT BETWEEN…AND…
说明:BETWEEN后是范围的下限,AND后是范围的上限
操作1:查询考试成绩在80~90之间的学生学号、课程号和成绩
1 | SELECT Sno, Cno, Grade FROM SC WHERE Grade BETWEEN 80 AND 90 |
等价与下面这句话
1 | SELECT Sno, Cno, Grade FROM SC WHERE Grade >=80 AND Grade |
操作2:查询考试成绩不在80~90之间的学生学号、课程号和成绩
1 | SELECT Sno, Cno, Grade FROM SC WHERE Grade NOT BETWEEN 80 AND 90 |
等价与下面这句话
1 | SELECT Sno, Cno, Grade FROM SC WHERE Grade < 80 AND Grade > 90 |
3.2.6 IN 确定集合
语法:列名 [NOT] IN (常量1, 常量2, … )
作用:用来查找属性值属于指定集合的元组
操作1:查询信息管理系、通信工程系和计算机系学生的姓名和性别
1 2 | select name, sex from sudent where dept in ('信息管理系','通信工程','计算机系') |
等价于
1 2 | select name, sex from sudent where dept = '信息管理系' or dept = '通信工程系' or dept = '计算机系' |
操作2:查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别
1 2 | SELECT Sname, Sex FROM Student WHERE Dept NOT IN ( '信息管理系', '通信工程系', '计算机系') |
等价于
1 2 | 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:查询姓“张”的学生详细信息
1 | select * from student where name like '张%' |
操作2:查询姓“张”、姓“李”和姓“刘”的学生的详细信息
1 | select * from student where name like '[张刘李]%' |
操作3:查询名字的第2个字为“小”或“大”的学生的姓名和学号
1 | select * from student where name like '_[大小]%' |
操作4:查询所有不姓“张”的学生姓名
1 | select name from student where name not like '张%' |
操作5:在Student表中查询学号的倒数第三为位不是1、2、3的学生信息
1 | select * from student where number like '%[^123]__' |
3.2.8 ESCAPE 转义字符
语法:ESCAPE 转义字符如果要查找的字符串正好含有通配符,比如下划线或百分号,就需要用ESCAPE来说明。其中“转义字符”是任何一个有效的字符,在匹配串中也包含这个字符,表明位于该字符后面的那个字符将被视为普通字符,而不是通配符。
操作:查找字段t1中包含字符串“30%”的记录
1 | where t1 like '%30!%%' escape '!' |
3.2.9 NULL 空值查询
语法: 列名 IS [NOT] NULL 空值是未确定的值或其值尚不知道。
操作:查询还没有考试的学生的学号和相应的课程号
1 | SELECT Sno, Cno FROM SC WHERE Grade IS NULL |
注意:空值不是一个确定的值,所以不可以用等于或不等于来比较或衡量
3.2.10 AND OR 多重条件查询
语法:当需要多个查询条件时,可以在WHERE子句中使用逻辑运算符AND和OR来组成多条件查询
操作:查询C002和C003课程中考试成绩在80~90的学生的学号、课程号和成绩
1 2 3 | SELECT Sno, Cno, Grade FROM SC WHERE Cno IN( 'C002', 'C003') AND Grade BETWEEN 80 AND 90 |
注意:OR的优先级小于AND,要改变运算的顺序可以通过加括号的方式实现
1 2 3 | 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)排序;当用多个列排序时,这些列在该子句中出现的顺序决定了对结果集进行排序的方式
操作:查询全体学生详细信息,结果按系名升序排列,同一个系的学生按出生日期降序排列
1 2 | SELECT * FROM Student ORDER BY Dept ASC, Birthdate DESC |
3.2.12 使用聚合函数
语法:
COUNT(*):统计表中元组的个数。
COUNT([DISTINCT] ):统计列值个数
SUM():计算列值的和值(必须是数值型列)。
AVG():计算列值的平均值(必须是数值型列)。
MAX():得到列值的最大值。
MIN():得到列值的最小值。
除COUNT(*)外,其他函数在计算过程中均忽略NULL值。
操作1:统计选修了课程(SC)的学生人数。
1 | SELECT COUNT(DISTINCT Sno) FROM SC |
操作2:计算学号为“0811101”的学生的考试总成绩
1 | SELECT SUM(Grade) FROM SC WHERE Sno = '0811101' |
注意!:聚合函数不能出现在WHERE子句中。
操作:查询学分最高的课程名,如下写法是错误的!
1 | SELECT Cname FROM Course WHERE Credit = MAX(Credit) |
应该改为:
1 2 3 | 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计算,求出每组的学生人数。
1 2 | SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno |
操作2:统计每个学生的选课门数和平均成绩。
1 2 | SELECT Sno as 学号, COUNT(*) as 选课门数, AVG(Grade) as 平均成绩 FROM SC GROUP BY Sno |
注意: 1.GROUP BY子句中的分组依据列必须是表中存在的列名,不能使用AS子句指派的列别名。 2.带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列和统计函数,因为分组后每个组只返回一行结果。
操作3:带WHERE子句的分组。统计每个系的女生人数。
1 2 3 | SELECT Dept, Count(*) 女生人数 FROM Student WHERE Sex = '女' GROUP BY Dept |
操作4:按多个列分组。统计每个系的男生人数和女生人数,结果按系名的升序排序。
1 2 3 | SELECT Dept, Sex, Count(*) 人数, FROM Student GROUP BY Dept, Sex ORDER BY Dept |
3.2.14 HAVING 限制分组结果
语法:HAVING用于对分组自身进行限制,它有点象WHERE子句,但它用于组而不是对单个记录。
操作1: 查询选课门数超过3门的学生的学号和选课门数。
1 2 | SELECT Sno, Count(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) > 3 |
处理过程:先执行GROUP BY子句对SC表数据按Sno进行分组,然后再用统计函数COUNT分别对每一组进行统计,最后筛选出统计结果满足大于3的组。分组的优先级大于查询,having是分组内操作。
操作2:查询选课门数大于等于4门的学生的平均成绩和选课门数
1 2 3 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,逐一查找满足连接条件的元组,
找到后就将表1中的第1个元组与该元组拼接起来,形成结果表中的一个元组。
表2全部查找完毕后,再取表1中的第2个元组,然后再从头开始扫描表2, …
重复这个过程,直到表1中的全部元组都处理完毕为止。
操作1:查询学生的选课情况。学生信息在student表中,选课信息在sc表中。两张表都存放着学生的学学号sno。
1 2 3 | 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 ]
1 2 3 4 | SELECT Sname, Cno, Grade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = '计算机系' |
注意:当为表指定了别名时,在查询语句中的其他地方,所有用到表名的地方都要使用别名,而不能再使用原表名。
操作4:三张表的连接查询。查询“信息管理系”修了“计算机文化学” 的学生姓名和成绩。
1 2 3 4 5 6 | 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”的索引,使其成为惟一性的非聚集索引
1 2 3 | 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”的视图,用于查询计算机网络专业男生的学号、姓名、出生日期,并将视图的列名分别改为:学生学号、男生姓名、生日。
1 2 3 4 5 | create view v1 (学生学号,男生姓名, 生日) as select 学号, 姓名, 出生日期 from xsqk where 专业名=‘计算机网络’ and 性别=1 go |
使用视图
1 | select * from v1 |
5.3 修改视图
语法:
alter view 视图
[ (列名表) ]
[ with encryption ]
as
select查询语句
[ with check option ]
操作:在“v1”的视图中增加两列:专业名和所在系。
1 2 3 4 5 6 7 | 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”视图中添加两条记录。
1 2 3 4 | 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’的记录。
1 | 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 不带参数的存储过程
操作:查询计算机系学生的考试情况,列出学生的姓名、课程名和考试成绩。
1 2 3 4 5 6 7 | 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 = '计算机系' |
执行:
1 | EXEC p_StudentGrade1 |
6.1.4 使用输入参数
语法:
create proc[edure] 存储过程名
@形参 数据类型 [=默认值] ,…n
as SQL语句
执行:
[execute] 存储过程名 [ @实参= ] 值 ,…n
注意:
执行存储过程时输入参数的传递方式由三种(让我想到了python ^_^)
1.按位置传递:直接给出参数的值,实参与形参一一对应
2.通过参数名传递:使用“参数名=参数值“的形式,参数可以任意顺序给出
3.如果在定义存储过程时为参数指定了默认值,则在执行存储过程时可以不为有默认值的参数提供值。
操作:创建并执行带输入参数的存储过程p_xsqk,查询指定学号(作为输入参数)的学生姓名、课程号、成绩。
1 2 3 4 | create procedure p_xsqk @xh char(6) as select 姓名,课程号,成绩 from xsqk , xs_kc where xsqk.学号=xs_kc.学号 and xsqk.学号= @xh go |
执行:
1 2 | exec p_xsqk ‘020102‘ --(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,返回指定教师(作为输入参数)所授课程的课程号(作为输出参数)。
1 2 3 4 5 | create procedure p_kh @teacher char(8) , @kch char(3) output as select @kch = 课程号 from kc where 授课教师= @teacher go |
执行:
1 2 3 4 | 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。
1 2 3 4 5 6 7 8 | create procedure p_find @findname char(8) as if exists (select * from xsqk where 姓名=@findname) return 1 else return 0 |
执行:
1 2 3 4 5 6 | 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表中!”
1 2 3 4 | create trigger tr_insert_cj on xs_kc after insert as print ‘有新成绩信息插入到xs_kc表! ’ go |
执行下面这条语句后会触发insert触发器
1 | insert into xs_kc values( '020105', '101', 87, null ) |
6.2.1.2 创建update触发器
操作:在student表上创建名为tr_update_xsqk2的触发器,当对该表的“姓名”列修改时激发该触发器,使用户不能修改“姓名”列。
1 2 3 4 5 6 7 8 | create trigger tr_update_xsqk2 on student after update as if update(姓名) begin rollback transaction -- 撤消修改操作 raiserror(‘不能修改学生姓名!’ , 16 ,1) end go |
执行下面这条语句后会触发update触发器
1 | update student set 姓名=‘小花’ where 姓名=‘杨颖’ |
6.2.1.3 创建delete触发器
操作:在xscj库的xsqk表上创建1个名为tr_delete_xsqk的触发器,当要删除指定学号的行时,激发该触发器,撤消删除操作,并给出提示信息“不能删除xsqk表中的信息
1 2 3 4 5 6 7 | create trigger tr_delete_xsqk on xsqk after delete as rollback transaction print ‘不能删除xsqk表中的信息!’ go |
执行下面这条语句会触发delete触发器
1 | delete xsqk where 学号= '020101' |
6.2.2 更新触发器
语法:
alter trigger 触发器名
6.2.3 删除触发器
语法:
drop trigger 触发器名[,…n]
on {database | all server}