SQL SERVER

库

Transact-SQL语言

DCL数据控制语言（进行安全性管理）

GRANT 授予权限

REVOKE 收回权限(不影响该用户从其他角色作为成员继承许可权限)

DENY 收回权限(禁止该用户从其他角色作为成员继承许可权限)

DDL数据定义语言（执行数据库任务）

CREATE 创建数据库或数据库对象

ALTER 修改——

DROP 删除——

DML数据操作语言（操作数据库中各对象）

select 从表或试图中检索数据

insert ——插入数据

update ——修改

delete ——删除

数据库

包含三个基本文件

（1）基本数据文件：有且只有一个 .mdf

（2）辅助数据文件：自由选择，0,1,2 .ndf

（3）日志文件：由于恢复数据库所需要的事务日志信息，至少一个。 .ldf

创建数据库

附一个源码

create database DB2
on primary
(
name='f1',
filename='F:\sqlserve\sqlwj\f1.mdf',
size=3mb,
maxsize=unlimited,
filegrowth=3%
),
(
name='f2',
filename='F:\sqlserve\sqlwj\f2.ndf',
size=10mb,
maxsize=500mb,
filegrowth=6mb
)
log on
(
name='f3',
filename='F:\sqlserve\sqlwj\f3.ldf',
size=1mb,
maxsize=unlimited,
filegrowth=2%
)

修改数据库名字

1
2
3

--修改数据库的名字
alter database DB2
modify name=DB1

添加辅助文件

--在DB1中添加一个辅助文件日志文件
alter database DB1
add file
(
name='f2a',
filename='F:\sqlserver\sqlwj\f2a.ndf',
size=3mb,
maxsize=unlimited,
filegrowth=2mb
)

添加日志文件

add log file

删除数据库

drop database 数据库名

表

设计表

创建需要确定下列特征：

要包含的数据类型；

表中的列数，每一列中数据的类型和长度；

哪些列允许空值；

是否要使用以及何处使用约束；

哪些是主键，哪些为外键。

数据类型

Bigint 大整型 8字节

int 常用的整型 4字节

smallint 小整型 2字节

tinyint 微整型 1字节

Bit 位类型其取值只有0/1

decimal[(p[,s])]和numeric[(p[,s])] p总位数，s小数位 p默认为18，s默认为0

Money存储的货币值由八个字节，前四个代表整数，后四代表小数

日期和时间

Datetime 长度8字节

1753.1.1-9999.12.31

Smalldatetime 四字节

1900.1.1-2079.12.31

字符串

Char(n) n单位为字节，表示输入字符串的最大值，若超过则不存，小于则添加空格

varchar(n) 变动长度

Unicode

Nchar

Nvarchar

二进制字符串

Binary：binary(n)存储图像等数据

varbinary(n)

创建表

切换数据库名字 use 数据库名字

--创建课程表（课程编号，课程名称，课程教师，上课时间）
create table 课程表
(
课程编号 varchar(15) not null,
课程名称 varchar(20) not null,
任课教师 char(10) not null,
上课时间 varchar(30) 
)

修改表

添加列：

1 2	alter table 课程表 add 上课教室 varchar(20) not null

修改数据类型

1 2	alter table 课程表 alter column 任课教师 varchar(10) not null

删除列

1 2	alter table 课程表 drop column 任课教师

修改列名

1	exec sp_rename'表名.列名',‘新列名'

修改表名

1	exec sp_rename'原表名',‘新表名'

约束

主键约束PRIMARY KEY

唯一确定表中每一按条记录的表示符

外键约束FOREIGN KEY

用于建立和加强两个表数据之间的连接

唯一约束UNIQUE

指定一个列或多个列的值具有唯一性，可为空

检查约束CHECK

限制输入值

默认约束DEFAULT

插入操作中没有提供输入值时系统会自动添加指定值

创建约束

1 2	alter table 表名 add constraint 约束名约束类型具体的约束说明

1
2
3

-对表中的课程编号添加主键约束
alter table 课程表 
add constraint PK_课程编号 primary key (课程编号)

1
2
3

--对课程名称添加一个唯一约束unique
alter table 课程表
add constraint UN_课程名称 unique(课程名称)

1
2
3

--给上课教室添加默认约束
alter table 课程表
add constraint DF_教室 default '正兴' for 上课教室

1
2
3

--给上课教室添加检查约束
alter table 课程表
add constraint CK_教室 check (上课教室='正兴' or 上课教室='诚意' or 上课教室='致知')

外界约束前提，列中的数据类型必须保持一致。引用的列必须为主键约束或者唯一约束。两个表的列名尽量一致。

删除约束

alter table 表名

drop constraint 约束名

若被引用要先删除外界约束再删除主键约束

在定义时约束

T-SQL语言

基本概念

标识符：数据库对象的名称

常规标识符与分隔标识符

常规标识符规则

第一个字符必须是英文大小写字母；_;@;#

后续字符除了第一字符，还有十进制数字，美元符号($)

批处理：一次处理多条语句

常量和变量

局部变量用DECLARE语句声明，作用范围仅在程序内部，局部变量的名称自己定义，以@开头。

声明语法

1	DECLARE @name datatype

对局部变量赋值

1 2	SET @NAME=expression SELECT @name=expression[,..n]

SET只能对一个变量赋值

SELECT可以对多个赋值

PRINT输出

--声明两个变量
declare @name varchar(10),@age int

--赋值
set @name='张三'
set @age=18

--select
select @name='李四',@age=19

print @name
print @age

全局变量是事先定义好的，不允许创建修改，以@@开头

运算符

算数运算符

赋值运算符=

位运算符：两个整数类型表达式

比较运算符：除text,ntext.image数据类型外都可以用

逻辑运算符：

AND如果两个表达式都为true，则结果为true

BETWEEN如果操作数在某个范围，则结果为true

NOT

一元运算符

流控制语句

BEGIN…END用于将多个语句组合为一个逻辑块

IF…ELSE

WHILE

BREAK

CONTINUE

CASE

case 
when ...then...
when ...then...
else

WAITFOR延迟语句可以将它之后的语句在一个指定的时间间隔后执行

1	Waitfor delay 'time'\|time'time'

waitfor delay '00:00:03'
print '傻瓜你好'

waitfor time '17:34:59'
print '傻瓜你好'

GOTO

return无条件退出语句

数据库操作实例

查询数据

SELECT

1
2
3

--查询教师
select 任课教师,上课时间 --列名
from 课程表 --表名

select *为查询所有列

DISTINCT

删除重复行

1
2
3

--去重复
select distinct 任课教师
from 课程表

distinct后只能跟一个列

TOP

用于规定要返回的查询结果的数目

--返回前3行所有数据
select top 3 *
from 课程表

--返回前三行的任课教师和时间
select top 3 任课教师,上课时间
from 课程表

去掉重复数据后，后面的数据往前补

1 2	select distinct top 3 任课教师 from 课程表

别名

1
2
3

--别名查询，方式一
select 任课教师 '教师', 上课时间 '时间'
from 课程表

1
2
3

方式二
select '别名'=列名
from 表名

1
2
3

方式三
select 列名 as '别名'
from 表名

计算列

1 2	seclect '原始学号'=学号,'调整学号'=学号-10 from 表名

选择查询

基本语法

1
2
3

SELECT LIST
FROM TABLE
WHERE CONDITIONS

比较搜索条件

<>不等于

!=不等于
！>不大于
!<不小于

--查询任课教师为孙大烈的课程名称
select 课程名称
from 课程表
where 任课教师='孙大烈' 后可接逻辑运算符

范围搜索条件

包括范围

排他范围

select 课程编号
from 课程表
where 课程编号 (not) between 3 and 5

列表搜索条件

IN关键字使用户可以与列表中的任意值匹配。

1
2
3

select 课程编号
from 课程表
where 课程编号 in ('3','4')

搜索条件中的字符匹配符

LIKE搜索匹配指定模式字符串

通配符

% 替代0/多个字符
_ 替代一个字符
[] 代表指定范围内的单个字符，[]中可以是单个字符([acef])，也可以为范围([a-f])
[^] 代表不在范围内的单个字符[^]中可以是单个字符([^acef])，也可以为范围([^a-f])

查询出姓王的老师
select 任课教师
from 课程表
where 任课教师 like '王%'

涉及空置的查询

NULL在数据库中表示不确定的值

判断为空

1	列名 is (not) null

聚合函数

对一组值执行计算，并返回单个值。

SUM([distinct]<列名>)
AVG([distinct]<列名>)
MAX([distinct]<列名>)
MIN([distinct]<列名>)
COUNT (*)统计元组个数
COUNT ([distinct]<列名>)统计本列列值个数
除count外，其他函数均计算NULL

1	select 聚合函数 from 表名

1 2	select sum (grade) as '总成绩' from stu

数据分组

group by根据一个或多个列对结果进行分组

select 年级,SUM(人数) as '总人数'，
COUNIT(班级编号) as '班级总量'
from 班级信息
group by 年纪

having通常与group by子句一起使用。

后跟查询条件

having子句可以包含聚合函数，where不可以

select 学号,SUM(成绩) as '总成绩'
from 成绩表
group by 学号
having SUM(成绩)<100

order by

用于对指定结果进行排序，默认升序

降序排序可以用DESC关键字

查看成绩表
select *
from 成绩表
order by 成绩

1
2
3

select *
from 成绩表
order by 成绩 desc

表连接

从多个相关表中查询数据

内部连接

select list from 表名1，表名2
where 表1.列=表2.列
或
select list from 表1 [inner] join 表2
on 表1.列=表2.列

查询学生的学号，姓名，性别，以及所在的班级名称和年级

1
2
3

select 学生信息.姓名,学生信息.学号,学生信息.性别,班级信息.班级名称,班级信息.年级
from 学生信息,班级信息
where 学生信息.班级编号=班级信息.班级编号

select 学生信息.姓名,学号,性别,//偷懒写法
班级信息.班级名称,年级
from 学生信息.inner join 班级信息
on 学生信息.班级编号=班级信息.班级编号

内部连接只有共同匹配到的结果才会有输出

外部连接

外部连接会返回from子句中提到的至少一个表的所有行。

分为左外部连接、右外部链接和全外部连接

左外部连接对连接条件中左边的表不加限制，其余同理。

先写为左表，后写为右表。

left outer join，若左表的某行在右表没有找到匹配的行，则结果集中的右表的相对应的位置为null

right outer join

full outer join

子查询

用来表示where子句的条件

子查询用圆括号括起来

嵌套子查询与相关子查询

嵌套子查询

一个子查询可以包含另一个查询

--查询计算机系学生选修了哪些课程
select *
from sc
where sno in
(select sno
from student
where sdept='计算机系')

where子句后条件要什么，子查询就查什么

select sno,grade
from sc
where cno='c02'
and grade >
(select AVG(grade)
from sc
where cno='c02'
)

在查询基础上创建新表

使用select …into语句可以在查询的基础上创建新表。

语法为

1
2
3

select list
into新表名
from表名

添加数据

使用Insert和values插入

1 2	insert[into] 表名[列名] values(...)

使用insert和select插入行

使用select子句将一个表或多个表的值添加到另一个表中。

修改数据

update修改表中数据语法形式

1 2	update 表名 set 列名=表达式 where//加条件

update 成绩表
set 学号='3'
where 成绩='95'
and 课程编号 between '1002' and '1003'

如果条件不统一，更改的值也不一样，则分开来写。

1
2
3

update 成绩表
set 成绩='89'
where 课程编号='1001'

删除数据

1 2	delete from 表名 where 选择条件

(删除行)

1
2
3

删除固定行
delete top (20) percent from 表名//删除前20%
delete top (20) percent from 表名//删除前20行

视图

视图是基于某个查询结果的虚表。

标准视图、索引视图、分区视图。

视图的优点

着重于特定数据

简化数据操作

自定义数据

导出和导入数据

跨服务器组合分区数据

创建视图

1
2
3

create view [数据库名 ] 视图名
AS
select_statement

1
2
3

create view View_班级信息
as
select *from 班级信息

修改视图名字

sp_rename修改视图名字

1	exec sp_rename 'view_班级信息','view_班级信息2'

对于表的操作，视图同样可以使用。

管理视图

1 2	inbseret into view_学生信息 values('7','13','aa','nv','beijing')

视图信息更改，原表信息也会更改。

alter既能修改数据内容，修改数据结构。

删除视图

1	drop view 视图名1,视图名2

有条件删除

1 2	delete from 视图名 where 条件

索引

对数据库表中一个或多个列的值进行排序的结构。用来定位。

作用

加快数据检索；保持数据一致性；实现表与表之间参照完整性。

选择创建索引的数据列

定义有主键和外键的列；在指定范围中快速或频繁查询的列；连接中频繁使用的列；需要按排序顺序快速或频繁检索的列

类型

聚集索引：

索引的顺序决定了表中行的存储顺序，因此每个表中只能有一个聚集索引。

非聚集索引：

索引中的逻辑顺序并不等同与表中行的物理顺序。

事务

事务是作为单个逻辑单元执行的一系列操作。不可分割。

四大特性：

原子性；一致性；隔离性；永久性。

1
2
3

begin transaction
rollback transaction//恢复
commit transaction//提交

存储

系统存储过程

exec执行

用户自定义存储过程

参数有输入和输出参数

create proc[edure] 存储过程名
@参数一 数据类型=默认值 output
AS
SQL语句
GO

参数可以设置默认值

库