数据库基础__SQL_Server
选择题：
数据库系统DBS：包括DB和DBMS
数据库系统DBS的组成：数据库，硬件支撑环境，软件系统，人员。
软件系统包括：操作系统，数据库管理系统，高级语言及编译系统
，应用开发工具软件，数据库应用软件。
人员包括：使用人员，软件管理人员，软件开发人员。
DBMS数据库管理系统：
DBMS主要功能：数据定义，数据操纵，数据组织存储与管理
数据库的运行管理，，数据库的保护，数据库的维护，通信。
数据库体系结构：
三级模式结构：内模式，模式（概念模式），外模式。
[数据物理存储细节和硬件环境，数据的整体逻辑结构和特征的描述，具体的应用程序及程序设计语言]
一个数据库可以有一个内模式，一个概念模式，多个外模式。
两级映射：外模式/模式映射，模式/内模式映射。[实现3个抽象级别的联系与转换]
两级数据独立性：逻辑数据独立性（外模式/模式映射）,物理数据独立性（模式/内模式映射）
数据库应用系统体系结构：
单用户结构，集中式结构，客户机/服务器结构，浏览器/服务器结构等类型。
数据模型：
定义：是现实世界数据特征的抽象，用来描述数据的一组概念和定义，是能表示实体类型及实体间联系的模型。
数据模型分类：现实世界---概念数据模型[E-R图]---逻辑数据模型。
数据模型组成：数据结构，数据操作，数据的约束条件。
数据模型：层次数据模型，网状数据模型，关系数据模型，面型对象数据模型
关系数据库---关系数据模型：
关系数据结构：关系[一个表]，属性，元组，候选键，主键，外键，域，分量，
关系模式[关系名（属性名，属性名）]。
关系操作：[集合操作]查询(选择，投影，联接，除，并，交，差)和更新(增删改)操作。
关系完整性约束：域完整性约束，实体完整性约束[主键不能为空]，参照完整性约束[外键]，用户自定义完整性约束。
关系三级模式：
关系代数：并交差，笛卡尔积[两个关系的所有元组的集合]
特殊的关系运算：
选择where[元组行]，$<选择条件>（<关系名>）
投影select[属性列]，#(属性名)（<关系名>）
联接[等值联接，自然联接//外联接：左外联接，右外联接，全外联接]，
自然联接有消除冗余数据。
除[T=R/S,T的属性由属于R但不属于S的属性组成；
元组由属于S且R都满足的元组组成]。
关系数据库规范化理论:
函数依赖：X函数确定Y，Y函数依赖于X。
非平凡函数依赖、平凡函数依赖
完全函数依赖，部分函数依赖
传递函数依赖
关系范式：
1NF---消除非主属性对主键的部分函数依赖—
2NF---消除非主属性对主键的传递函数依赖——
3NF---消除主属性对主键的部分和传递函数依赖—
BCNF---消除非平凡且非函数依赖的多值依赖——4NF。
关系分解原则：
无损联接，保持依赖。
E-R图：
实体：矩形框。实体和属性用直线相连。
属性：椭圆框，多值属性用双椭圆框，派生属性用虚椭圆框，复合属性。主键属性带下划线。
联系：棱形框，实体与实体联系,线上标上联系类型,联系的某个参与者是全部则用双线相连。
联系类型：1对n，n对1和n对m。实体间联系，实体内部联系。
属性和实体不能有联系。
联系也可以有属性。
弱实体用双线框表示，与弱实体的联系用双线棱形框表示。（如教师的子女依赖与教师存在，则子女为弱实体）
IsA超类子类。
E-R模型转换成关系模型：
复合属性：直接列出。
弱实体：本身属性+所依赖的实体的主键
IsA：本身属性+超类主键
多值属性：本身属性+对应实体的主键
联系：本身属性+相连的实体的主键
合并：******
T-SQL语言：
数据定义语言DDL： create，alter，drop。
database数据库。create database <数据库名>
table表。create table <表名>（列名 数据类型， 列名 数据类型， constraint约束）
index索引：create [unique][clustered] index <索引名> on <表名> (列名 [排序]，列名 [排序])。
索引的优缺点。
聚簇索引：一个基本表最多建立一个聚簇索引，对于经常更新的列则不宜建立聚簇索引。
索引排序：ASC升序，DESC降序。
当排序列含空值时
- ASC：排序列为空值的元组最后显示
- DESC：排序列为空值的元组最先显示
视图view：create view <视图名> as <子查询>
视图是在基本表中派生出来的，依赖于基本表，不能独立存在。视图是一个逻辑表，虚表，不包含数据。
例：王平只能检索计算机系学生的信息
先建立计算机系学生的视图CS_Student
CREATE VIEW CS_Student
AS
SELECT
FROM Student
WHERE Sdept='CS'；
在视图上进一步定义存取权限
GRANT SELECT
ON CS_Student
TO 王平 ；
存储过程proc：存储sql语句于数据库中，方便执行，而不是存放在内存中。
表完整性约束：
主键：constraint <约束名> primary key（列名，列名）。
外键：Constraint <约束名> foreign key (列名) references key <表名>(列名)。
唯一性：Constraint <约束名> unique (列名，列名…)。
CHECK：constraint <约束名> check (约束表达式)。
NULL或NOT NULL。
数据操纵语言DML：insert update delete select
insert into <表名> values (值)。
update <表名> set 列名=表达式 where 条件。
delete from <表名> where 条件。
select [all/distinct] from <表名> [别名] where 条件
[group by 列名 having 条件]分组
[order by 列名 ASC|DESC]排序。
ALL全部，distinct去除重复部分。
数据控制语言DCL：
权限控制：grant，revoke，deny
事务控制：begin transaction，commit，rollback
GRANT SELECT ON TABLE Student TO U1
GRANT ALL PRIVILEGES
ON TABLE Student, Course
TO U2, U3
GRANT UPDATE( Sname ), REFERENCES( sno )
ON TABLE Student
TO U4
WITH GRANT OPTION
把用户U4修改学生学号的权限收回
REVOKE UPDATE(Sno)
ON TABLE STUDENT
FROM U4
把用户U5对SC表的INSERT权限收回
REVOKE INSERT
ON TABLE SC
FROM U5
CASCADE
嵌入式SQL
游标cursor：允许应用程序每次处理结果集中的一行数据。
declare，open，fetch，close。
变量:
局部变量：DECLARE @变量名 数据类型 。
变量赋值：SELECT/SET @变量名=值
SELECT @s=price FROM titles WHERE title_id=‘bu395’
查看变量的值：SELECT @变量名。
全局变量：@@变量名 @@error，@@rowcount
类型转换：convert(类型符，表达式[，style])、style只用于日期类型到字符串的转换。
|
style取值 |
输出格式 |
|
2 |
yy.mm.dd |
|
3 |
dd/mm/yy |
|
4 |
dd.mm.yy |
|
5 |
dd-mm-yy |
|
102 |
yyyy.mm.dd |
|
103 |
dd/mm/yyyy |
|
104 |
dd.mm.yyyy |
|
105 |
dd-mm-yyyy |
日期函数：
SELECT getdate()
datename(日期元素,日期表达式)：以字符串形式返回日期元素指定的日期的名字。
datepart(日期元素,日期表达式)：以数字形式返回日期元素指定的日期的名字。
日期元素：yy,qq,mm,dd,hh,
Select datename(mm,pubdate) from titiles where title_id=‘Bu1032’
返回June，如用datepart则返回 6。
dateadd(日期元素，数值，日期表达式)：将数值转换成日期元素指定的部分加到日期表达式上返回。
datediff(日期元素，较早日期表达式，较晚日期表达式)：两个日期相减后，按日期元素指定部分转化后返回。
数学函数：
|
示 例 |
结 果 |
|
abs(-100) |
100 |
|
ceiling(99.2) |
100 |
|
floor(99.2) |
99 |
|
round(66.2387,2) |
66.24 |
|
exp(0) |
1 |
|
rand(23) |
|
|
log(1) |
0 |
|
pi() |
3.141 592 65... |
|
power(2,10) |
1 024 |
|
sqrt(4) |
2 |
|
sin(pi()) |
0 |
触发器：
Deleted表与Inserted表：触发器是在表上建立的，我们将触发器所依附的表称为触发器表。当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，在执行这些动作之前系统首先自动建立两个表，Deleted表与Inserted表。这两个表临时驻留在内存当中，其结构与触发器表完全相同，一旦触发器执行完毕，两个表也随之消失。
Trigger触发器类型：INSTEAD OF触发器，AFTER触发器
用户自定义函数：
数据库设计：
数据库设计基本步骤：6
需求分析：数据流图，数据字典（自顶向下的方法）
概念结构设计：E-R图，模块结构图
逻辑结构设计：E-R模型à关系模型
物理结构设计，
数据库实施，
数据库运行和维护阶段。
数据库保护：
参照完整性违规的处理策略：
级联方式cascades，
受限方式restrict，
置空方式set null。
数据库安全性控制手段ppt
用户标识与鉴别
存取控制
视图机制
审计
密码存储
数据库并发控制：
事务transaction、commit、rollback
事务特性：原子性，一致性，隔离性，持续性。
事务的调度：串行调度，并发调度。
事务串行调度的结果总是正确的，并发调度效率高但不一定是正确的。
一个并发调度，当它是可串行化的调度时，它才是正确的调度。
事务并发执行带来的问题：丢失修改，读取脏数据，不可重复读。
封锁技术：
排它锁（写锁，X锁）
共享锁（读锁，S锁）
若已加读锁S，则不能再加写锁X
若已加写锁X，则不能再加锁，直至释放该锁。
封锁协议：
一级封锁协议：防止丢失修改
二级封锁协议：防止丢失修改，读取脏数据
三级封锁协议：防止丢失修改，读取脏数据，不可重复读。
活锁：采用先来先服务可防止活锁的发生
死锁：死锁检测（事务依赖图有没有回路）
数据库恢复：
数据库系统的故障种类：事务故障，系统故障，介质故障。
数据库恢复原理：建立冗余数据（副本和日志）；
数据转储（静态/动态，海量/增量），日志文件，检查点方法
SQL代码
CREATE TABLE Teacher( TNo char(8) NOT NULL, TName nchar(4) NOT NULL, Sex nchar(1) NULL, Prof nchar(5) NULL, Mobile char(11) NULL, DepID Int NOT NULL, CONSTRAINT TKey PRIMARY KEY(TNo), constraint T2D foreign key(DepID) references Department(DepID), constraint TSex check(Sex='男' or Sex='女'), constraint TProf check(prof in ('助教','讲师','正教','副教')), constraint TMobile check(Mobile like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) Go insert into Teacher(TNo,TName,Sex,Prof,Mobile,DepID) values(804,'李诚','男','副教',13712884353,1) insert into Teacher values('856','张旭','男','讲师','13578453321',4) insert into Teacher values('825','王萍','女','助教',null ,1) insert into Teacher values('831','刘冰','女','助教','13686751234',4) insert into Teacher values('822','周刚','男','正教','13512484756',3) insert into Teacher values('827','张旺才','男','副教',NULL,3) insert into Teacher values('854','沈长生','男','正教',NULL,2) insert into Teacher values('836','刘明','女','副教','13813536608',4) --select * from Teacher Go --查询选修了所有课程的学生的学号，姓名，年龄 select sno,sname,datediff(yy,birthdate,getdate()) as age from student where not exists (select * from course where not exists (select * from sc where student.sno=sc.sno and course.cno=sc.cno)) --查询没有选修李诚老师所授课程的学生的学号、姓名、--【年龄】 select sno,sname,datediff(yy,birthdate,getdate()) as age from student s where not exists (select * from course c,teacher t,sc where sc.sno=s.sno and c.cno=sc.cno and c.tno=t.tno and tname='李诚') --2、数据类型转换函数 --从Teacher表中查询教师的电话号码中的最后位数字能被整除的教师信息 select * from Teacher where convert(int, right(mobile,8)) %9 = 0 --convert(A,B):将B类型转换成A类型 --right(mobile,8):返回mobile字符串从最右端数到第位中间的字符串 Go --查询学生的学号、姓名、出生日期。要求将出生日期转化为整数后显示 select SNo,SName,convert(char(8),BirthDate,112) from Student go --3、日期函数 --把当前日期转换为样式101 select getdate() select convert(char(10),getdate(),101) --分别获取数据库当前时间的年、月、日、小时 select datepart(yy,GETDATE()) as y, datepart(mm,GETDATE()) as m, datepart(dd,GETDATE()) as d, datepart(hh,GETDATE()) as h go --5、字符串函数【!】 --求在字符串‘They call me the hunter'中从个字符开始，"the"出现的位置 select charindex('the','They call me the hunter',5) go --返回Teacher表中的所有老师的姓氏（不重复） select distinct substring(Tname,1,1) from Teacher go --用ZZZ 替换abcdefghi 中的字符串cde select replace('abcdefghi','cde','ZZZ') go --组合字符串‘hello‘和‘join‘，中间加入一空格 select 'hello'+' '+'join' go
--1、触发器
/*
`触发器只能在表上建立，且一张表最多建立个触发器
`操作时被触发而执行，不能由用户直接调用。
`触发器是在表上建立的，我们将触发器所依附的表称为触发器表。
当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，
在执行这些动作之前系统首先自动建立两个表，Deleted表与Inserted表。
这两个表临时驻留在内存当中，其结构与触发器表完全相同，
一旦触发器执行完毕，两个表也随之消失。
`UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，
因此要用到Deleted与Inserted两个表
======================================SQL语句： create database JiaoXue go use JiaoXue go CREATE TABLE Teacher( TNo char(8) NOT NULL, TName nchar(4) NOT NULL, Sex nchar(1) NULL, Prof nchar(5) NULL, Mobile char(11) NULL, DepID Int NOT NULL, CONSTRAINT TKey PRIMARY KEY(TNo), constraint T2D foreign key(DepID) references Department(DepID), constraint TSex check(Sex='男' or Sex='女'), constraint TProf check(prof in ('助教','讲师','正教','副教')), constraint TMobile check(Mobile like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) go insert into Teacher(TNo,TName,Sex,Prof,Mobile,DepID) values(804,'李诚','男','副教',13712884353,1) insert into Teacher values('856','张旭','男','讲师','13578453321',4) insert into Teacher values('825','王萍','女','助教',null ,1) go select * from Teacher go delete from Teacher go
