SQLServer数据库操作总结（sql语法的使用）

浏览： ℃

字体：大中小

发布时间：2013-12-23 12:22:19

来源：

前言

数据库学完了，但是脑子里还是没有一个系统的数据库操作概念。借着考试复习的这个机会，总结一下数据库的常用操作。内容参考上课的课件进行了整理，整篇文章会很长。

1.数据库创建管理

1.1 创建数据库

CREATE DATABASE DBON 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 DBADD 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 DBMODIFY FILE(	NAME = ,	SIZE = ,)

1.2.4 删除数据文件和日志文件

语法：

alter database 数据库名称

remove file 数据文件或日志文件的逻辑文件名

操作：删除数据库db1中的数据文件data4和日志文件log2

alter database db1    remove data4alter 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 db1remove filegroup g3

1.2.8 修改数据库名称

语法：

alter database 数据库名

modify name = 新数据库名

操作：将数据库db1的名字修改为gl

alter database db1modify 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 studentadd constraint pk_studentprimary 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   scadd  constraint  ck_sccheck   (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_xhalter  table student drop  constraint   uq_xsqk_dhalter  table student drop  constraint   ck_xsqk _xbalter  table student drop  constraint   ck_xsqk _xhalter  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  studentadd   address  char(10)   constraint  df_xsqk_jg   default ‘shanghai’ ,      email  varchar(30),        number  int  identitygo

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 scalter 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 scset grade = grade + 10where ( 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 goDrop  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 sudentwhere dept in ('信息管理系','通信工程','计算机系')

等价于

select  name, sex from sudentwhere 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 intselect @credit=max(credit) from courseselect 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 SCON 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   性别=1go

使用视图

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_StudentGrade1AS SELECT Sname, Cname, Grade    FROM Student s INNER JOIN SC    ON s.Sno = SC.Sno  INNER JOIN Course cON 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        where   xsqk.学号=xs_kc.学号  and  xsqk.学号= @xhgo

执行：

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，返回指定教师(作为输入参数)所授课程的课程号(作为输出参数)。

create    procedure   p_kh    @teacher  char(8) ,    @kch   char(3)    output    as       select     @kch =  课程号   from   kc   where    授课教师= @teachergo

执行：

declare    @teacher  varchar(8),  @kch  char(3)set     @teacher='赵怡'exec   p_kh   @teacher,  @kch  outputprint    @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    intexec   @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   updateas     if   update(姓名)            begin                  rollback   transaction         -- 撤消修改操作                  raiserror(‘不能修改学生姓名！’ , 16 ,1)              endgo

执行下面这条语句后会触发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   deleteas      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哈哈~

>更多相关文章

首页推荐

东方联盟网培训质量如何?靠谱吗?

　　佛山市东联科技有限公司一直秉承“一切以用户价值为依归

相关文章

24小时热门资讯

24小时回复排行

热门推荐

最新资讯

网络安全

操作系统

黑客防御

站长知识