考试考SQL Server，看这一篇就够了

写在开头

该文章适用于要参加SQL Server考试并，或者想对SQL Server相关代码有个大概了解的人阅读。推荐使用目录阅读。
例子已经更新，但是还是需要自己跟着格式动手敲敲代码，这样映象更深刻。

必读

为了记忆更翻遍，所以我将代码格式抽象成了统一格式，所以一定要先阅读这部分内容，才能看懂下文格式。

1. 使用<>包裹的内容表示在实际操作中需要根据题目要求变动的
2. 使用[]包裹的内容表示可有可无，其中[,...n]表示前面的内容支持多个
3. ()就是格式中的一部分
4. 使用|分割的内容，表示或，列如：[add | drop]表示add和drop选一个

数据库

创建数据库

格式

create database <数据库名>
on 
(<filespec>)[, ...n]
log on
(<filespec>)[, ...n]

<filespec> = {name,filename,size,maxsize,filegrowth}

例子

-- 创建一个student123数据库（123为学号后三位），该数据库的主数据文件逻辑名称为Student1，物理文件名为Student1.mdf，初始大小为10MB，最大尺寸无限制，增量为10%；数据库的日志文件逻辑名称为Student1_log，物理文件名为Student1.ldf，初始大小为4MB，最大尺寸为18MB，增量为1MB。

create database student106 on
(
    name = Student1,
    filename = 'H:\sqlserver_data\les2\Student1.mdf',
    size = 10MB,
    maxsize = unlimited,
    filegrowth = 10%
)
log on
(
    name = Student1_log,
    filename = 'H:\sqlserver_data\les2\Student1.ldf',
    size = 4MB,
    maxsize = 18MB,
    filegrowth = 1MB
)

修改数据库

格式

alter database <database_name>
 {add|remove|modify} {file|logfile} <filespec>

例子

--对student123数据库进行扩容，添加一个5MB的数据文件（student2.ndf）
alter database student106
add file
(
    name = student2,
    filename = 'H:\sqlserver_data\les2\student2.ndf',
    size = 5MB
);

-- 和一个5MB的事务日志文件（student2_log.ldf）
。
alter database student106
add log file
(
	name = student2_log,
	filename = 'H:\sqlserver_data\les2\student2_log.ldf',
	size = 5MB
);

切换数据库

use <databse_name>

查询数据库信息

格式

• 记忆技巧：相关的数据库操作命令都会带上db后缀

sp_renamedb <old_name>,<new_name>
sp_helpdb <database_name>

例子

-- 修改名为student的数据库的新名字为db_stu
sp_renamedb 'student','db_stu'

-- 查看数据库具体信息
sp_helpdb sales

表

创建表

格式

create table <tb_name>
(<column>[, ...n]
[primary key (<col_name>[,...n])] #用于联合主键
[foreign key (<col_name>) references <tb_name>(<col_name>)]
)

<column> = (
	name type [not null
		| primary key
		| foreign key references <tb_name>(<col_name>)
	]
) # 只能声明单个主键

例子

1. 创建表，要求如下

列名	数据类型	宽度	为空性	备注
LecturerID	int			主键
LecturerName	varchar	50
Gender	char	2	√

create table lecturer(
	LecturerID int not null,
	LecturerName varchar(50) not null,
	Gender char(2) null,
	primary key(LecturerID)
)
-- 或则
create table lecturer(
	LecturerID int not null primary key,
	LecturerName varchar(50) not null,
	Gender char(2) null,
)

修改表

格式

alter table <tb_name>
| add <column>
| alter column <col_name>
| drop column <col_name>

例子

-- 1. 利用Sql语句添加薪水和学院代码两列：“Salary”列，数据类型为money，允许为空；“SchoolID”列，数据类型为int，不允许为空
alter table lecturer
add 
	Salary money null,
	SchoolID int not null

修改列名

格式

• 列名是table的一部分，所以前面要加上表名才知道是哪个表的列，跟他同样道理的还有index

sp_rename '<tb_name>.<col_name>' '<new_name>'

例子

sp_rename 'lecturer.LecturerID' 'lecID'

删除表

drop table <tb_name>

约束

注意

1. 所有<col_name>和<logical_content>都要用(包裹

约束操作

添加约束

格式

alter table <tb_name>
add constraint <cs_name> <cs_type> [<logical_content>]

约束类型

主键约束

<cs_content> = primary key(<col_name>[,...n])

外键约束

<cs_content> = foreign key (<col_name>) references <tb_name>.<col_name>

唯一约束

<cs_content> = unique(<col_name>)

检查约束

<cs_content> = check (<logical_content>)

例子

-- 若SchoolID是School表中主键，请通过Sql语句给Lecturer表添加上相应外键约束(约束名fk_schoolid)
alter table lecture
add constarint fk_schoolid foreign key (SchoolID) references School(SchoolID)

删除约束

alter table <tb_name>
drop constraint <cs_name>

查询

简单查询

格式

select [top n] [all|distinct]
	from <tb_name>
	where ..
	group by ..
	having ..
	order by ..

例子

-- 检索student表中前3位女同学学号、姓名和所在系，列名依次显示为“学号”、“姓名”和“系”
select top 3 sno '学号',sname '姓名',sdept '系' from student;

合集查询

1. union
2. intersect
3. except

连接查询

1. inner join = join
2. left join
3. right join
4. full join

格式

select <col_names> from <tb_name> [type] join <tb_name> on <condition_content>

例子

-- 利用多表连接， 检索年龄小于23的同学学名以及选课总分，并按总分上升排序。显示列名为"学名"和“总分”。
select s.sname '学名',sum(grade) '总分' from student s
	right join sc on s.sno = sc.sno
	where Sage<23
	group by s.sname
	order by sum(grade) asc;

嵌套查询

格式

select <> from <> 
	where <表达式> in (
		select <> from <> where <>
	)

例子

-- 1. 利用嵌套查询，查询高于平均年龄的同学的选课信息（学号，课程号，成绩），列名按原样显示。
select sno,cno,Grade from sc 
	where sno in (select sno from student 
		where Sage > (select avg(sage) from student)
);

编程

变量

declare @<name> <type>
set @<name> = <value>
select @<name> = <value>

逻辑判断

if <condition>
	<code>
else
	<code>

打印

print {<string> | <@variable>}

常用函数

• convert(tyoe,variable)：将指定的variable的值转换为type类型的值

循环

while <boolean_expression>
	<code>

case结构

筛选value

case <@variable>
when <value> then <value>
....
end

筛选true or false

case
when <boolean_value> then <value>
...
end

补充

• 当<code>中出现多行代码时，需要添加<begin>和<end>

游标

• 游标的执行流程有五步，分为声明，打开，读取，关闭，释放。

声明游标

• 如何记忆理解游标的声明格式：你可以理解游标就是一个变量，这个变量的类型是cursor。
• 游标创建用的是for，而创建视图，触发器，存储过程用的是as

declear <cursor_name> cursor
{forward_only | scroll} {dynamic | static}
for
	<select_statement>

打开游标

open <cursor_name>

读取游标

• @@fetch_states：判断上一条fetch的获取状态
  • 0：成功
  • -1：失败，超出结果集
  • -2：不存在
  • -9：未执行提取操作

fetch {next | prior | last | first}
from <cursor_name>
into <@variable>[,...n]

关闭游标

close <cursor_name>

释放游标

deallocate <cursor_name>

例子

-- 使用游标，逐行打印每个学生的学号和姓名。
-- 输出格式为：'员工号：XXXXX,姓名：XXX'
declare mcur cursor 

for select sno,sname from student;

open mcur

declare @sno char(5)
declare @sname varchar(20)

fetch next from mcur into @sno,@sname
while @@FETCH_STATUS=0
begin
	print '员工号：'+@sno+',姓名：'+@sname
	fetch next from mcur into @sno,@sname
end

close mcur;
deallocate mcur;

视图

创建视图

creaet view <view_name>(<column>[,...n])
[with encryption]
as 
	<select_statement>

修改视图

• 修改视图就是直接把创建视图中的create改为alter，其他都一样

删除视图

drop <view_name>

查询视图信息

• 查看视图信息：sp_depends <view_name>
• 查看视图源码：sp_helptext <view_name>
• 修改视图名：sp_rename <old_name>,<new_name>

索引

• 索引是在表中的，所以你在操作的索引的时候都要带上表的某个列上的，这就是为什么在删除表的时候要用on <table_name>(<column>)的原因

创建索引

格式

• index前面的都是索引类型
  • unique是唯一索引
  • clustered和nonclustered是聚集索引和非聚集索引。

create [unique] [clustered | nonclustered] index <index_name>
	on <table_name>(<column>[,...n])

例子

--4. 使用SQL语句，为student表的sname列创建非聚集索引，名为sname_ind
create clustered index sname_ind on student(sname);

删除索引

drop index <index_name> on <table_name>

操作索引

格式

--修改索引名：
sp_rename '<table_name>.<index_name>','<new_name>'
--查看指定表中所有索引
sp_helpindex <table_name>

例子

--5. 查看student表中的所有索引，
sp_helpindex student
-- 将刚才创建的索引重命名为new_sname_ind
sp_rename 'student.sname_ind','new_sname_ind';

存储过程

创建过程

格式

• 参数也是局部变量，所以开头要带@
• output用于声明变量用于输出值

create proc[edure]
	[<@parameter_name> <type> [ = <default_value> ] [output] ][,...n]
	as
		<select_statement>

例子

-- 创建一个存储过程(p_student)，实现根据学生所属系，列出员工的姓名和年龄（列名原样显示）
create proc p_student
@dept varchar(20)
as
	select sname,sage from student where sdept = @dept;

-- 创建一个存储过程（p_sc），实现根据学号，输出该学生选课总数。
create proc p_sc
@sno char(5),
@c int output
as
	select @c = count(*) from sc where sno = @sno group by sno; 

#错题

执行过程

格式

• 当变量用作输出时，需要声明output

[exec] <procedure_name> [<@parameter_name> [output]][,...n]

例子

-- 利用存储过程片p_student，查询数学系同学们的姓名和年龄。
exec p_student '数学系'
或者
p_student '数学系'

-- 执行带输出参数的存储过程：
-- 通过查询学号为95001的选课总数，执行和输出验证存储过程。
declare @count int;
exec p_sc '95001',@count output;
print @count;

修改过程

• 将创建过程中的creaet改成alter即可。

删除过程

drop proc[edure] <procedure_name>

系统过程

• sp_depends <name>：查看目标内部信息（涉及表，涉及列等）
• sp_helptext <name>：查看目标源码
• sp_help <name>：查看目标相关外部（所有者，创建时间）

触发器

• 分类
  • DML全称为data manipulation language triggers
  • DDL全称为data definition language triggers，
• 区别
  • DML中的m主要是表操作，例如insert,delete,update等
  • DDL中的d主要是数据库中的定义(操作)，列如create table,drop view,alter table等

创建触发器

DML(T)触发器

• DML触发器也是作用域表或视图的，所以要用on标记依赖对象
• rollback进行数据回滚，避免数据被修改（让本次修改数据失效）。
• 触发器类型
  • for：执行操作之前（在2022中的官方文档中已经被废弃）
  • after：执行操作之后，需要配合rollback进行数据回滚才能避免数据修改。
  • instead of：代替触发器

create trigger <trigger_name>
	on <table_name>
	{for | after | instead of} {<manipulation>[,]}
as
	<select_statements>

<manipulation> = [isnert,delete,update]

DDL(T)触发器

create trigger <trigger_name>
	on {all server | database}
	[with encrption]
	{for | after} {DDL events} [,...n]
as 
	<sql_statement>

修改触发器

• creaet改alter同view,procedure。

删除触发器

drop trigger <trigger_name>

查看触发器信息

• sp_depends：查看触发器相关内部信息（涉及表，涉及列等）
• sp_helptext：查看触发器源码
• sp_help：查看触发器相关外部（所有者，创建时间）

数据库安全性管理

登录名

• login的理解
  • login是连接SQL Server用的，没有操作数据库和表的权限，每个login只能拥有一个user。你可以理解为他是一把打开整个超大仓库的智能锁的一个密码。每个数据库就是大仓库里的小仓库，数据表就是小仓库里的每个小房间

创建登录名

格式

creaet login <login_name> 
	[with <option_list>[,...n] [must_change]]
	[from <sources>]

<sources> = 
	windows
<option_list> = 
	password = 'password',
	check_expiration= {on|off}

例子

create login yufeng
create login yufeng with password = '123456'
create login [hostname/yufeng] from windwos
create login yufeng  with password = '123456' must_change,check_expiration

- 1.在数据库所在服务器上创建sql_server登录名abc123，密码为123456；
create login abc123 with password = '123456';
- 将登录名abc123映射到sales数据库上。  
use sales
create user abc123 from login abc123;

修改登录名

• create改alter，dddd

删除登录名

drop login <login_name>

用户

• user的理解
  • user依赖于login的，拥有对SQL Server中具体的数据库和表操作的权限（需要使用grant进行赋予，就是老板赋予你仓库管理员对某个仓库的管理权限，数据库管理员就是那个老板）。你可以理解为他是有大仓库智能锁密码的小仓库管理人员（每个密码只有一个仓库管理人员知道），可以同时或只用于一个小仓库(数据库)或小仓库中的小房间(表)，同时带有grant option头衔的管家还可以为自己添加员工。

创建用户

格式

create user <user_name>   
	[{ for | from } LOGIN login_name]  
    [ with <limited_options_list> [ ,...n ] ]  

<limited_options_list> =  
      default_schema = <schema_name>

例子

-- 在登录名abc123下创建一个用户abc1
create user abc1 from login abc123

修改用户

• 无法修改，只能删除

删除用户

drop login <user_name>

权限

• to和from的区别
  • to是出现在grant和deny意思是授权和拒绝某个用户，这个权限或者拒绝是作用于用户的所以是to。反观from就是权限已经在用户那，我们需要收回，就是用from从他那里收回。

授权

格式

• with grant option：赋予可以将自身有的能力授权他人的权限。
• ALL 该选项并不授予全部可能的权限。 授予 ALL 等同于授予下列权限：BACKUP DATABASE、BACKUP LOG、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和 CREATE VIEW。 #记忆
• 数据表和数据库有关的权限授予格式区别在于：数据库相关的权限使用了use指令代替了作用域

--授权跟表|视图|存储过程有关的权限
grant <grant_name>
	on <{table|view|proc}_name> 
	to <user>[,...n]
	[with grant option]

<grant_name> = 
	{insert | update | delete }

--授权跟数据库操作有关的权限（创建表，创建视图等）
use <database_name> -- 指令在哪个的数据库执行
grant <option>
	to <user>[,...n]
	[with grant option]

<option> = 
	{ all | BACKUP DATABASE | BACKUP LOG | CREATE DEFAULT | CREATE FUNCTION | CREATE PROCEDURE | CREATE RULE | CREATE TABLE | CREATE VIEW}

例子

-- 授予用户abc123创建表的权限；
grant create table to abc123;
-- 授予用户abc123对Sell_Order表的选择权，更新权，并允许转授。
grant select,update on sell_order to abc123 with grant option;

收权

格式

• cascade：英文意思连续传递，顾名思义就是取消该用户下包括所有授权包括子授权。

revoke <grant_name>
	on <{table|view|proc}_name> 
	form <user>[,...n]
	[cascade]

例子

--4.收回用户abc123对Sell_Order表的选择权。
revoke select on sell_order from abc123 cascade;

拒绝

deny <grant_name>
	on <{table|view|proc}_name> 
	to <user>[,...n]

例子

--3.拒绝用户abc123对Purchase_Order选择权。
deny select on purchase_order to abc123;

数据库日常维护

• 数据库的维护流程包括：创建备份设备，备份数据库，恢复数据共三步。

备份设备

• 理解
  • 备份设备是备份数据存放的地方，所以主要用于声明的内容有物理地址和逻辑地址

创建备份设备

• 物理文件名必须遵从操作系统文 件名规则或网络设备的通用命名约定，并且必须包含完整路径

sp_addumpdevice <device_type>, <logical_name>, <physical_name> 
	
<device_type>：备份设备的类型: 
	Disk：备份文件建立在磁盘上。 
	Type：备份文件建立磁带设备上，考试一般用不到。 
<logical_name>：备份设备逻辑名 
<physical_name>：备份设备物理文件名。

备份数据库

backup database <database_name>
	to { <backup_device> | {disk | type} = <physical_name> }
	[with <options>[,..n]]

<option> = 
	DIFFERENTIAL：进行差异备份；
	INIT：本次备份数据库将重写备份设备； 
	NOINIT：本次备份数据库将追加到备份设备上

备份日志文件

backup log <database_name>
	to { <backup_device> | {disk | type} = <physical_name> }
	[with <options>[,..n]]

<option> = 
	NO_LOG 和TRUNCATE_ONLY：备份日志后截断不活动的日志。 
	NO_TRUNCATE：备份日志后不截断不活动日志。
	INIT：本次备份数据库将重写备份设备； 
	NOINIT：本次备份数据库将追加到备份设备上

查看备份设备信息

restore headeronly from <backup_device>

恢复数据库

• file参数
  • 是用于恢复备份文件中的第file次备份的数据，要先恢复完整备份才能恢复差异备份，并且第一次备份需要使用norecovery参数声明回复还没结束。

restore databse <database_name>
	from { <backup_device> | {disk | type} = <physical_name> }
	[with <option>[,...n]]

<option> = 
	FILE = 文件号：标识要还原的备份； 
	NORECOVERY：表明对数据库的恢复操作还没有完成，可以继续恢复后续的备份。 
	RECOVERY：默认选项，表明对数据库的恢复操作已经完成，恢复后的数据库是可用的。

关系规范化与数据库设计

范式

个人理解，便于记忆：

1. 1NF：数据表中每一行数据不能重复，利用主键可以完成第一范式的要求，因为主键不允许重复
2. 2NF：不能有联合主键，就是主键只能绑定一个列
3. 3NF：非主键列，不能相互依赖，不能产生关系，相互是独立的。

ER图

格式

1. 实体用方形
2. 关系用菱形
3. 属性用椭圆
4. 主键要下划线

例子

最后

学习一门编程语言的时候带点英文思维会事半功倍，毕竟大部分编程语言逻辑是基于英语开发的，一起进步！