SQL Server教程从基础到应用学习笔记

Copyright vivi_and_qiao

• 在一台计算机上可以安装有一个或者多个的SQL Server(不同版本或者是同一版本的)，其中的
  一个称为一个数据库实例。
• 当基本表发生变化的时候，从视图中查询出来的数据也随之变化
• 表中的记录通常按照其输入的顺序顺序存放,这种顺序称为记录的物理顺序
• 主键的约束当前表的主键字段的唯一性，外键约束当前表与其他表的关系
• 架构的作用是将数据库中的所有对象分成不同的集合，每个集合就是一个架构，系统默认架构
  是dbo
• 如果修改之后创建的所有新数据库都将继承这些修改建议备份model数据库
• tempdb是一个全局资源，可供连接到SOL Server实例的所有用户使用
• 当数据库内容超过初始大小存放不下时，会按照增长方式增加文件大小但是不会超过总的大小
• 数据库存放在数据文件和日志文件中，日志文件记录操作数据库的全过程
• 数据库成功创建之后，数据文件名和日志文件名都不能修改了
• 可以删除用户定义的文件组，但不能删除主文件组，删除用户定义的文件组之后该文家组中所
  有的文件将被错删除
• 文件扩展名 - mdf主数据文件， - ndf辅助数据文件， - ldf日志文件filename选项中“d:\SQL Server\2012”
  必须存在否则会产生错误即数据库创建失败
• 创建数据库，用primary和filegroup
  create database pxscj1/每次执行成功应该只有一次/
  on
  primary
  (
  name=’pxscj_data’,
  filename=’D:\Program Files\Microsoft SQL Server\pxcj_data - mdf’,
  filegrowth=5%,
  size=3mb,/主文件的初始大小至少5mb才能容纳的了model数据库/
  maxsize=20mb
  ),
  filegroup group1
  (
  NAME=’group1’,/所有的字符串都应该是用一对单引号括起来/
  filename=’D:\Program Files\Microsoft SQL Server\MSAS11 - LIWEI1\group1 - ndf’,
  size=5mb,/指定路径必须存在/
  Maxsize=20mb,
  filegrowth=10%
  )
  log oncc
  (
  name=’pxcj1_log’,
  filename=’D:\Program Files\Microsoft SQL Server\MSAS11 - LIWEI1\pxcj_log - ldf’,
  size=1mb,
  filegrowth=10%,
  maxsize=3mb
  )
• 精度指数值数据中存储的十进制数据的总位数，长度指存储数据用的总字节数
• 精确数值型demical|numeric(p[,s])其中p为精度，s为小数位数，s的默认值为0，其小数位数
  必须小于精度
• 使用TRUANCATE TABLE语句将删除指定表中的所有行，但表结构及其列，约束，索引等保持不变
  ，而新行标识所用的计数值重置为该列的初始值，如果是要保留标识计数值，要使用delete语句
• 对于由外键约束引用的表，不能使用TRUANCATE TABLE语句删除数据，而应该使用不带where子句
  的DELETE子句，其也不能用于参与索引视图的表
• 当自定义的列标题中含有空格的时候，必须使用引号将标题括号括起来，不允许在WHERE子句中使用
  列别名，这是因为执行where代码的时候可能尚未确定列值
• 要替换查询结果中的数据，需要使用查询语句CASE表达式
• %可用于除money，smallmoney以外的数字类型
• TOP选项限制其返回的行数，若带PERCENT则表示返回结果集的前百分之多少行
  distinct只选择结果集中的一个重复行,all将保留结果集当中的所有行
• 可以指定结果集当中最大的行数，top 5，或者top 20percent格式
• 如果一个select语句中有一个GROUP BY子句,则这个聚合函数对所有列起作用，如果没有，则
  SELECT语句只产生一行作为结果
• 使用带通配符的LIKE,%代表倒数，_下划线代表单个字符，[]指定范围，[^]指定不属于范围
• ALL指定表达式要与子查询结果集中的每个值进行比较，只有表达式与每个值都满足比较关系的时候
  ，才返回TRUE,否则返回FALSE
• SOME和ANY表示表达式只要与子查询结果集中的某个值满足比较关系，、就返回true，否则返回FALSE
• EXISTS子查询用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE
  否则返回FALSE
  use JWGL
  GO
• 一个登录名在本数据库中只能创建一个数据库用户
• 
  SELECT distinct 学号=Sno+1,Sex AS 性别,Sdept as 课程,Age=
  case when Age<18 then ‘未成年’
  when Age>18 then ‘已成年’
  else’年龄出错或未录入’
  end
  from Student
  select avg(all Sno) as 学号平均值,count(*) as 行数
  from Student
• YEAR函数用来取出日期数据类型的年份
• 更查询结果中的列标题也可以使用“列别名=表达式”的形式，当自定义的标题中含有空格时，必须使用双引号括起来语句
• 替换查询结果可以用case语句
• 如果from子句指定了表别名，则这条select语句的所有子句都要使用表别名来代替原表名
• 以jion指定的的链接：inner表示内链接，outer表示外链接。on表示用于指定连接条件， apply运算符：实现查询操作的外部表达式返回的每个行调用表值函数 cross jion表示交叉连接，cross APPLY返回外部表中的通过表值函数生成结果集的行，outer apply 既返回生成结果集的行，也返回不生成结果集的行
• use JWGL
  GO
  SELECT *
  FROM Student INNER JoiN SC
  ON Student - Sno=SC - Sno/*执行结果将产生两个表的所有字段，不去除重复的列
• 内链接是系统默认的，可以省略inner关键字，使用内连接仍可使用where子句指定的条件
• 视图是一个从一个或多个表（或视图导出的表）视图是一个虚表，视图所对应的数据不进行实际存储
  数据库中只存储视图的定义，在对视图的数据进行操作的时候，系统根据视图的定义去操作与视图相关联的基本表
• 一个对表进行操作的T - SQL语句通常都可以产生或处理一组记录，但是许多应用程序，尤其是以T - SQL嵌入的语言，通常不能把整个结果集作为一个单元进行处理，这些应用程序需要一种机制来保证每次处理集中的一行或几行，游标（cursor）就提供了这种机制
• local说明所声明的游标是局部游标，其作用域为创建它的批处理，存储过程或触发器，该游标名仅在这个作用域内有效，批处理，存储过程或触发器或存储过程output参数中，该游可以由局部游标变量引用，当其终止时，该游标自动释放但output参数将游标传递回来
  declare CUR cursor LOCAL 则游标仍可引用
  scroll dynamic
  for
  select Sno,Sname,Sex,Age,Sdept
  from Student
  WHERE Sno between 1 and 20 for update of Sdept
  open CUR
  FETCH LAST FROM CUR
  FETCH NEXT FROM CUR
  声明游标declare cursor其有两个版本 SQL - 92语法以及T - SQL扩展
  关键字static指定为静态游标，指定系统将创建供所定义的游标使用数据的临时副本，对游标的所有请求都从tempdb的该临时表中得到应答
  因此对该游标进行提取操作的时候返回的数据中不反应对进本表的修改，并且该游标不允许修改
  declare cur CURSOR
  DYNAMIC
  FOR SELECT Sno,Sex,Sname,Age
  from Student where Sno between 10 and 20
  FOR UPDATE OF Sdept
  open cur
  fetch first from cur
  go
  fetch next from cur
  go
  fetch prior from cur
  go
  fetch relative 2 from cur
  go
  select ‘执行情况’=@@FETCH_STATUS
  fetch relative - 4 from cur
  select @@FETCH_STATUS as’执行情况’

Sno Sex Sname Age
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
10 女 安瑞 19

(1 行受影响)

Sno Sex Sname Age
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
12 男 vivi 20

(1 行受影响)

Sno Sex Sname Age
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
10 女 安瑞 19

(1 行受影响)

Sno Sex Sname Age
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
13 男 vivi 20

(1 行受影响)

执行情况
- - - - - - - - - - -
0

(1 行受影响)

Sno Sex Sname Age
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

(0 行受影响)

执行情况
- - - - - - - - - - -
- 1
fetch语句的执行状态保存在全局变量@@fetch_status中，0表示上个fetch执行成功， - 1表示所要读取的行不在结果集中， - 2表示被提取的行不存在（已被删除）
- 数据控制语言：GRANT 授予授权可把语句许可的权限授予其他用户和角色，REVOKE收回权限但不影响该用户或角色从其他角色从其他角色最为成员继承许可权限，deny收回权限并禁止从其他角色继承许可权限功能与revoke相似，不同之处：处收回权限外，还禁止从其他角色继承许可权限
- unicode字符串常量前面有一个N标识符，N前缀必须为大写字母
- unicode数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储
- 用户自定义表数据类型也由用户自行定义，可以作为参数提供给语句存储过程和函数
- 全局变量由系统提供且预先声明，在名称前加两个@来区别局部变量select @@ERROR,@@CONNECTIONS显示执行的上一个T - SQL语句的错误号，
以及自距上一次启动以来链接或者是试图连接的次数
- exists用于检测一个子查询的结果是否不为空，若是则运算结果为真，否则为假。

use JWGL
GO
select (Age+Sno)as 学好及姓名
from Student
+来实现两个字符串的链接运算
- 等待语句:指定触发语句块，存储过程或事务执行的时刻或者是需等待的时间间隔
- try catch 构造可对严重程度高于10但不关闭数据库连接的所有执行错误进行缓存
- T - SQL提供3种系统内置函数，行集函数聚合函数和标量函数
- 如果一个表没有创建索引，则数据行不按任何特定的顺序存储，这种结构称之为堆集
- 支持在表中任何列（包括计算列上）定义索引，按索引组织的方式可将索引分为聚集索引和非聚集索引两种类型
- 索引可以是唯一的，这意味着不会有两行记录相同的索引键值，这样的索引称之为唯一索引，可以多行共享同一键值
- 对于两个相关联的表（主表和从表，附表与子表）插入和删除数据时，通过参照完整性来保证他们之间数据的一致性
- 定义表之间的参照关系：先定义主表的主键（或唯一键），再对从表定义外键约束
- 删除表间的参照关系，只需删除从表的外键约束即可
- 存储过程是数据库对象之一，存储过程可以理解成数据库的子程序，在客户端和服务器端可以直接调用它
- 使用存储过程的优点：存储过程在服务器端运行，执行速度快
存储过程执行一次后就驻留在高速缓冲存储器上，在以后的操作中，只需从高速缓冲存储器上调用已经编译好的二进制代码执行，提高系统性能
使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数据库信息访问的权限，确保数据库系统的安全
存储过程可以在SQL Server启动时自动执行
- 在SQL Server中创建存储过程，必须拥有CREATE ROUTINE权限
- _sp前缀是由SQL Server指定系统存储过程
- 用户定义的存储过程只能在当前数据库中创建，存储过程名在sysobjects系统表中，而语句文本存储在syscomments中
- SQL Server启动时可以自动执行一个或者多个存储过程，这些存储过程必须由系统管理员在master数据库中创建，并在sysadmin固定服务器角色下作为后台过程执行，这些过程不能有任何参数
- 触发器中使用的特殊表：执行触发器时系统创建了两个特殊的临时表inserted表和deleted表
- 在表或者视图上，每个insert，update，delete语句最多可以定义一个instance of触发器，另外instance of触发器不可以用于使用了with
check option选项的可更新视图，如果触发器表存在约束，则在instance of触发器执行之后和after触发器执行之前检查这些约束，如果违反了约束则回滚instance of 触发器操作，且不执行after触发器
- 当修改定义了update触发器表记录时，表中原纪录移到delete表中，修改过的记录插入到inserted表中，由于都是临时表，他们在触发器执行的时候创建，在执行后消失，所以只能在触发器的语句中使用select语句使用这两个表
- 不能对临时表和系统表创建DML触发器
- TRUNCATE TABLE语句虽然能够删除表中的记录但是不会触发delete触发器
- 触发器内可以指定任意的set语句，所选择的set选项在触发器执行期间有效，并在触发器执行完后恢复到原来的设置
- create trigger SC_insetr
on SC AFTER INSERT
AS
BEGIN
DECLARE @SNO int,@sde char(20)
select @SNO=Sno,@sde=Grade from inserted
update Student set Sdept=@sde+Sdept where Sno=@SNO
PRINT ‘向SC表中添加记录时触发SC_insert成功，并将对应学生的学分修改成功’
END

use JWGL
GO
CREATE TRIGGER Student_update1
on Student after update
as
begin
declare @oldSno int,@newSno int
select @oldSno=Sno from deleted
select @newSno=Sno from inserted
update SC SET Sno=@newSno where Sno=@oldSno
print ‘当修改Student表时，触发Student_updatec触发器并将SC表中的学号进行对应修改（更新）’
end

use JWGL
GO
CREATE TRIGGER Student_delete
on Student after delete
as
begin
declare @Sno int
select @Sno=Sno from deleted
delete from SC WHERE Sno=@Sno
end

use JWGL
go
if exists(select name from sysobjects where name=’Student_delete’)
drop TRIGGER Studentr_delete
use JWGL
GO
CREATE TRIGGER Student_delete
on Student after delete,update
as
begin
declare @Sno int
if(update(Sno))
update SC SET Sno=(select Sno from inserted) where(Sno=(select Sno from deleted))
else /update()函数返回一个布尔值，指示是否对表或视图的指定列进行了insert或update操作/
select @Sno=Sno from deleted
delete from SC WHERE Sno=@Sno
end

• 与after触发器不同的是，instance of触发器触发时只执行触发器内部的语句而不执行激活该触发器的SQL语句一个表或视图中只能有一个
  instance of触发器
• instance of触发器的主要作用是使不可更新视图支持更新，如果视图中的数据来自于多个基表，则必须使用instance of触发器支持引用表中的数据的插入，更新和删除操作

use JWGL
insert into Student_view values(20,’乔丹林’,’女’,20,’15591558033’,’CS’,320,15,’大学计算机基础’,99);
执行结果
消息 4405，级别 16，状态 1，第 2 行
视图或函数 ‘Student_view’ 不可更新，因为修改会影响多个基表。
用instance触发器：
use JWGL
go
create trigger Student_view_insert
on Student_view instead of insert
as
begin
declare @Sno int,@Sname varchar(10),@Sex char(2),@Age tinyint,@Phoenumber char(12),
@Sdept varchar(20),@Sumgrade int,@Cno int,@Cname varchar(20),@Grade tinyint
select @Sno=Sno,@Sname=Sname,@Sex=Sex,@Age=Age,@Phoenumber=Phoenumber,@Sdept=Sdept,
@Sumgrade=Sumgrade,@Cno=Cno,@Cname=Cname,@Grade=Grade from inserted
insert into Student VALUES(@Sno,@Sname,@Sex,@Age,@Phoenumber,@Sdept,@Sumgrade);
insert into SC VALUES(@Sno,@Cno,@Grade);
end
再执行use JWGL
GO
insert into Student_view values(20,’乔丹林’,’女’,20,’15591558033’,’CS’,320,15,’大学计算机基础’,99);语句后成功，成功在SC和Student插入记录
- DDL触发器ALL SERVER关键字指将当前DDL触发器作用域应用到当前服务器，DATABASE是指将当前DDL触发器的作用域应用于当前数据库
- 时间类型：执行之后将导致触发DDL触发器的T - SQL语句事件的名称，当ON关键字后面指定DATABASE选项时使用该名称
use JWGL
GO
CREATE TRIGGER SAFETY ON database
after DROP_TABLE
AS
PRINT’禁止删除该操作’
rollback transaction

• 该语句用于回滚之前所做的修改，将数据库恢复到原来的状态

• create TRIGGER safety_server on ALL SERVER
  AFTER DROP_DATABASE
  AS
  PRINT ‘删除数据库时触发safety_server触发器，组织删除数据库’
  rollback transaction

• 被设置成with ENCRYPTION的DML触发器是不能被修改的

• 要使用备份设备的逻辑名进行备份，就必须创建命名的备份设备，否则只能使用物理名访问备份设备，可以使用逻辑名访问的备份设备则称为命名的备份设备，只能使用物理名访问的备份设备则称为临时备份设备
• 如果要使用备份设备的逻辑名来引用备份数据库，就必须在使用它之前创建命名备份设备，当希望创建的设备能够重新使用或者设置系统自动备份数据库时，就要使用永久备份设备
• use JWGL
  go
  SP_ADDUMPDEVICE @DEVTYPE=’DISK’,/执行系统存储过程sp_addumpdevice创建命名备份设备，也可以将数据定向到命名管道，当不再需要时使用系统存储过程sp_dropdevice/
  @LOGICALNAME=’数据库JWGL备份文件’,
  @physicalname=’d:\sqlserver2012\备份\JWGL备份文件 - bak’
• 如果不准备重用备份设备，就可以使用临时备份设备
• BACKUP DATABASE 命令语法格式：TO子句：表示伴随的备份设备组是一个非镜像媒体集，或者镜像媒体集中的镜像之一
  <备份设备>指定备份操作时要使用的逻辑或者物理备份设备，最多可以指定64个备份设备