数据库视频7到12章知识点总结

第七章
基本连接
基本多表连接
select A.姓名,A.性别,A.出生日期,A.民族,B.班级名,A.家庭住址
from 学生信息 A,班级信息 B
where A.所属班级=B.班级编号

内连接 消除与另一个白哦的任何不匹配的行

select A.班级名,A.班级人数,B.姓名,B.联系方式
from 班级信息 A inner join辅导员信息 B
ON A.辅导员=B.辅导员编号

外连接（下面3个）

左外连接
参与连接的表中有主从之分，主表去匹配从表，符合条件就直接连接
不满足条件 显示为NULL值
select A.班级名,A.班级人数,B.姓名,B.联系方式
from 班级信息 A LEFT （OUTER可以省） join辅导员信息 B
ON A.辅导员=B.辅导员编号

右外连接
主从表关系的互换
select A.班级名,A.班级人数,B.姓名,B.联系方式
from 班级信息 A RIGHT （OUTER可以省） join辅导员信息 B
ON A.辅导员=B.辅导员编号

全连接
获取两个表中的所有信息，并且获取相匹配的行
select A.班级名,A.班级人数,B.姓名,B.联系方式
from 班级信息 A FULL （OUTER可以省） join辅导员信息 B
ON A.辅导员=B.辅导员编号

交叉连接
不带 where 子句  返回迪卡尔积
CROSS join

自连接

联合查询
 UNION　
注意：　使用的时候连接两个结果集必须在其目标列中有相同数目的表达式，且数据内容尽量保持一致

select A.成绩编号,A.分数,B.分数
from 成绩 A ,学生信息 B
where A.学生编号= B.学号
UNION
select '',SUM（分数）,'合计'
from 成绩 A ,学生信息 B
where A.学生编号= B.学号
ORDER BY 分数

使用子查询
在where 后面中写入子查询

嵌套子查询
在子查询的基础上 再加个查询

xml 查询
读取节点值

FOR XML 子句
指定模式可以将从数据库系统的表中检索出来的数据自动表示成XML格式。
 row模式 auto模式

EXISTS关键字查询
select　ａ.* from 成绩信息 a
where exists（select * from 考试安排b where b.考试编号=a.考试编号 and b.考试编号='0801'）

交查询INTERSECT 
两条语句 共同的部分

差查询EXCEPT
列固定相同

第8章
Transact—SQL
语句 分类  数据定义语言 数据操纵语言 数据控制语言

变量与常量
DECLARE @name nvarchar(50)
SET @name='李明'（赋值）
select @name='王文'（也可以赋值）

运算符
% 取模运算
比较运算符
<>不等于
！= 不等于
！<不小于
！>不大于

表达式
DECLARE @STR nvarchar(50)
select @STR='姓名：'+姓名+'性别'from 学生信息 where 学号='2011654015'

注释
单行注释-- 多行注释（/*****...******/）

if 条件语句
IF ...ELSE 
if Boolean
BEGIN
（语块）
END
else
（语块）

if...else语句嵌套

Case 语句
CASE语句示例
select 姓名,所任职位,员工职称=
case 所任职位 
when '经理' then '高级职称'
when '经理' then '中级职称'
when '经理' then '初级职称'
else '其他职称'
end
from 员工信息

While 语句 循环
declare @i int 
set @i=1
while @i<10
begin
  set @num=@num*@i
  set @i=@i+1

end
print @num

Whlie 语句中使用其他语句

WAITFOR 延迟语句
WAITFOR
{
DELAY time
|TIME time
}

waitfor delay '00:00:05'
exec sp_help

Goto 语句
跳转

TRY...CATCH 错误处理语句
BEGIN TRY
语句
END TRY
BRGIN CATCH
语句
END　CATCH
例子
begin try
declate @num int
set@num=1/0
select @num
end try
begin catch
 select ERROR_LINE() as '错误行数'
end catch

数学函数
abs取绝对值
round（@i,2）去两位小数
floor 取整
ceiling 进一取整

字符串函数
ASCII 返回字符串中最左侧字符的ASCII代码值
CHAR  ASCII转化成字符
LEN 返回字符串长度
LOWER 字符转换成小写
UPPER 字符转化成大写
LTRIM 删除空格字符
RTRIM 返回字符串尾部空客
REPLACE 替换函数
REPLICATE 复制函数
space 空格函数
STR 数字向字符转换函数
substring3个参数（具体变量，起始位置，截取字符长度）

字符串函数使用示例
CHARINDEX 返回字符串中某个指定的子串出现的开始位置
不能用于text和IMAGE 数据类型
PATINDEX 返回字符串中某个指定的子串出现的开始位置
可以使用通配符

聚合函数
AVG 平均
count 结果记录数，行数
MAX最大 
MIN最小
SUM 加

时间和日期函数
DATEADD 给指定日期加上一个时间间隔后的新的DATETIME值
DATEDIFF  跨两个指定日期的日期边界书和时间边界数
DATNAME  指定日期部分的字符串
DATEPART 日期整数部分

GETDATE 系统现在日期

DAY   日
MONTH 月
YEAR  年
yy-mm-dd

标量值函数
创建函数  右击 创建 有模板
create function  avgsum（@ID varchar(10)）
returns   int
as
begin
 declare @sum int
 select @sum=avg(分数)from 成绩信息
 where 课程编号=@id
 return@sum
end

表值函数
右击表值函数 模板
与上面类似

系统与元素函数
CONVERT() 数据类型转换函数
CURRENT USER() 返回当前用户名
ISDATE() 判断输入的是不是有效日期
ISNULL()  用一个指定替换值更换任何空值
ISNUMERIC() 判断它的输入是不是一个数值

游标概念
数据访问机制，允许访问单独的数据行。
游标结果集，定义游标的select语句返回的行的集合
游标位置 指向这个结果集中的某一行的指针

游标的声明
declare student_cursor scroll cursor 
for
select * from 学生信息
for read only

操作游标
打开游标 OPEN 名称
检索数据
FETCH
检索的哪样具体行的关键字 NEXT PRIOR FIRST LAST  from
   into  
关闭游标
close
释放游标
DEALLOCATE 

判断游标提取状态
@@FETCH_STATUS 
取值 0 成功  -1 失败   -2 提取行不存在

游标应用示例

事务
begin transaction  开始事务
declare @ nation varchar(20)
set @nation='满族'
insert into 学生信息 value('')
if @nation='汉族'
begin 
    commint transaction
end
else
rollback transaction

事务模式
自动提交模式 每条单独语句都是一个事务
显示事务 都是以begin transaction 开始，commit 或者bollback 语句结束
隐式事务  在前一个事务完成时新事务隐式启动，但每个事务仍以commit或rollback 语句显示完成
批处理级事务  应用去多个活动结果集。

begin end 语块
declare  @sex varchar(5)
begin
select * from 学生信息 where 性别=@sex 
print '成功'
end 
else
begin 
print '查询失败'
end

XACT_ABORT
 当SQL语句出现运行时错误，SQL SERVER是否自动回滚到当前事务

嵌套事务

锁
一个事务可向系统发出请求，对被操作的数据加锁，其他书屋必须等到此事务解锁之后才能访问数据。
共享(s)锁 读操作
独占(x)锁 用于写操作
更新(U)锁 

死锁
两个或者多个进程在执行过程中，因争夺资源而造成的一种互相等待的现象。
死锁条件
1.互斥2.请求与保持3.不剥夺条件4.循环等待条件

第九章
存储过程概述
是一组为了完成特点功能的SQL语句集，经编译后储存再数据库中。 存储过程包括 程序流，逻辑以及对数据库的查询。 主动调用
存储过程种类
用户定义存储过程
程序存储过程
扩展存储过程

创建存储过程
CREATE PROC

create proc proc_student 
@sex varchar（10）
as
select * from 学生信息 where 性别=@sex

使用带默认值的参数
create proc p_employee
@departmentId varchar(10),
@zhiwei varchar(20)='职员'
as
  select A.员工姓名,A.性别,A.所任职位,A.联系电话,B.部门名称
  from 员工信息 A ,部门信息 B
  ON A.所在部门编号=B.部门编号

调用
exec P_employee @departmentid='10001'

使用输出参数
create proc P_sorce
@name varchar (10)
@sorce int output
as 
    select @sorcer=avg(A.分数)
    from 成绩信息 A,学生信息 B
   where A.学生编号=B.学号 and B.姓名=@name

调用
 DECLARE @sorce1 int
 exec P_sorce @name='',@sorce=@sorce1 output
 print@sorce1

存储过程示例
create proc sorceforupdate
@id varchar(10)
@sorce int
as 
   update 成绩信息 set 分数=@sorce where 成绩编号=@id
调用
exec sorcefordate @id='3',@sorce=100

修改存储过程
ALTER PROCEDURE
右击修改

删除存储过程
DROP PROCEDURE

设计存储过程的规则
可以引用再同一个存储过程中创建的对象，只要引用时已经创建了该对象即可
可以再存储过程内引用临时表
不能包括的T－SQL语句

系统存储过程
exec sp_who sa
exec sp_helpdb 学生成绩管理系统
exec sp_monitor

临时存储过程
create procedure #get_info
as
  select * form 成绩信息 a,学生信息 b 
 where a.学生编号=b.学号 
 order by b.姓名

嵌套存储过程
最多嵌套32层

查看存储过程
exec sp_helptext get_student

第十章
触发器
在修改指定表中的数据时执行的存储过程。 被动 被事件触发

优点
自动执行，可以通过数据库中的相关表进行层叠更改，强制限制，数据完整性

种类
DDL触发器                                                    
DML触发器

创建INSERT触发器
CREATE TRIGGER

CREATE TRIGGER T_addnum
on 学生信息
for insert
as 
update 班级信息 set 班级人数=班级人数+1
 where 班级编号=(select 所在班级 from inserted)

insert into 学生信息 values('','','','','','','')

创建DELETE 触发器
CREATE TRIGGER T_DELETACHER 
ON 教师信息
for DEKETE
as
 select 姓名 as 被删除的教室姓名,性别,年龄,联系电话 FROM DELETED

创建 UPDATE触发器
CREATE TRIGGER t_UPDATE 
ON 教师信息
for update
as
if (update (姓名) or update(性别))
   begin
    print'事务不能被处理'
 end
 else
  print'数据修改成功！'

创建DDL触发器  数据库触发器
CREATE TRIGGER
on database
for DROP_table,alter_table
as
 print'事务不能被处理'
  rollback

管理触发器
修改

嵌套触发器
create trigger t_del
on 新员工信息
for delete
as 

 insert into 员工信息(,,,,)
  select ,,,,
  form deleted
create trigger t_add
 on 员工信息
 for insert
as
  
 update 部门信息 set 员工人数=员工人数+1
 where部门编号=(select 所在部门编号 form inserted)

递归触发器
属性  选项  递归触发器 true

注意事项
很复杂，必须经过以后条例的设计和全面的测试
在任何点的数据修改会出发一系列的触发器
所有触发器一起构成一个大失误
触发器最多只能递归16层

第十一章
安全性机制的5个等级
客户机，网络传输，实例级别，数据库级别，对象级别。

SQL 身份验证模式
WINDOWS 身份验证模式
混合登陆模式
右击属性 安全性 

创建Windows 登陆用户
计算机 管理 用户 

创建SQL server 登陆账号

创建数据库用户
数据库的使用映射

固定服务器角色

固定数据库角色

用户自定义数据角色

应用程序角色

权限
授予权限
GRANT 
ALL
TO 
撤销权限
REVOKE
FROM
拒绝权限
DENY
TO

管理角色

DENY　否决优先权
新建数据库角色　

第十二章
备份概述
1.完整数据库备份  备份整个数据库 花费的空间大
2.差异数据库备份  最近一次完全数据库备份
3.事务日志备份  依赖于完整备份，并不备份数据库本身
4.文件组备份
备份恢复模式
1.简单恢复模型 1.2
2.完全恢复模型 4个备份
3.大容量日志记录恢复模型

创建备份设备
数据库 服务器对象 备份设备

创建完整的备份
数据库 右击 任务 备份 

创建其他备份
先确定已经执行过完整备份然后才能创建差异备份
备份事务日志

常规恢复

时间点恢复
其他备份选项
备份压缩