SqlServer语法总结

dongkailong

已于 2022-10-26 17:52:29 修改

阅读量1.2k

点赞数 2

文章标签：数据库 sql sqlserver

于 2022-10-26 17:41:18 首次发布

本文链接：https://blog.csdn.net/dongkailong/article/details/127537218

版权

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文件组

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 创建架构

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])

操作：

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

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

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分

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的数据记录

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表。

Drop table sc

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表中,或者完全写出一一对应的列名。参考语法说明

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的学生的学号、课程号和成绩

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）排序；当用多个列排序时，这些列在该子句中出现的顺序决定了对结果集进行排序的方式

操作：查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列

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)

应该改为：

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子句的分组。统计每个系的女生人数。

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门的学生的学号和选课门数。

1 2	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，逐一查找满足连接条件的元组，

找到后就将表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 ] --用于加密视图的定义，用户只能查看不能修改。

select查询语句

[ with check option ] --强制所有通过是同修改的数据，都要满足select语句中指定的条件

操作1:创建一个名为“v1”的视图，用于查询计算机网络专业男生的学号、姓名、出生日期，并将视图的列名分别改为：学生学号、男生姓名、生日。

create view v1 (学生学号,男生姓名, 生日)

select 学号, 姓名, 出生日期 from xsqk

where 专业名=‘计算机网络’ and 性别=1

使用视图

1	select * from v1

5.3 修改视图

语法：

alter view 视图

[ (列名表) ]

[ with encryption ]

select查询语句

[ with check option ]

操作：在“v1”的视图中增加两列：专业名和所在系。

alter view v1

(学生学号,男生姓名,生日,专业,系)

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’的记录。

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 不带参数的存储过程

操作：查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。

CREATE PROCEDURE p_StudentGrade1

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，查询指定学号(作为输入参数)的学生姓名、课程号、成绩。

create procedure p_xsqk @xh char(6)

as select 姓名，课程号，成绩 from xsqk , xs_kc

where xsqk.学号=xs_kc.学号 and xsqk.学号= @xh

执行：

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

create procedure p_kh

@teacher char(8) , @kch char(3) output

select @kch = 课程号 from kc where 授课教师= @teacher

执行：

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)

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表! ’

执行下面这条语句后会触发insert触发器

1	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

if update(姓名)

begin

rollback transaction -- 撤消修改操作

raiserror(‘不能修改学生姓名！’ , 16 ,1)

end

执行下面这条语句后会触发update触发器

1	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

rollback transaction

print ‘不能删除xsqk表中的信息!’

执行下面这条语句会触发delete触发器

1	delete xsqk where 学号= '020101'

6.2.2 更新触发器

语法：

alter trigger 触发器名

6.2.3 删除触发器

语法：

drop trigger 触发器名[,…n]

on {database | all server}

dongkailong

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
SqlServer语法总结

SqlServer语法总结
复制链接

扫一扫