SQL Server学习笔记

《SQL Server从入门到精通》学习笔记

1 数据管理技术的三个阶段：人工管理、文件系统、数据库系统。

2 数据库的组成：数据库、数据库管理系统、数据库管理员、硬件平台、软件平台5部分。

3 数据库的三级模式：内模式、模式、外模式。

4 三级模式之间的映射：外模式/模式，模式/内模式。

5 常用的数据库数据模型：层次模型、网状模型、关系模型。

6 关系数据库的规范化：

（1）1NF：原子性，字段不可分。

（2）2NF：唯一性，有主键，非主键关键字依赖于主键。

（3）3NF：去除传递依赖（非主键关键字不能相互依赖）。

7 SQL规范支持五种基本字段类型：字符型、文本型、数值型、逻辑型和日期/时间型。

8 创建用户自定义数据类型

use qxzy

exec sp_addtype ccname,'nvarchar(10)','not
null'

9 T-SQL语言的组成：数据定义语言DDL、数据控制语言DCL、数据操纵语言DML

10 变量使用

定义局部变量：

declare @songname char(10)

局部变量赋值：

select @songname='abc'

或者set @songname='abc'

还可以同时处理多个变量：

declare
@songname char(10),@aint,@bint

select @songname='abc',@a=1,@b=2;

全局变量是SQL Server系统内部事先定义好的变量，不用用户参与定义。SQL Server一共提供了30多个全局变量，全局变量的名称都是以“@@”开头的。

11 注释符有两类：“--”和“/**/”。

12 比较运算符返回的结果是布尔数据类型，它有TRUE、FLASE及UNKNOWN三种值。

13 逻辑运算符对某些条件进行测试，以获得其真实情况。 逻辑运算符和比较运算符一样，返回带有 TRUE、FALSE 或 UNKNOWN 值的 Boolean 数据类型。

14 运算符优先级

15 通配符

16 流程控制

17 创建数据库

create database Eagle on primary
(
Name = Eagle_data,
Filename = 'D:\kankan\Eagle.mdf',
Size = 5MB,
Maxsize = unlimited,
Filegrowth = 10%
)
log on
(
Name = Eagle_log,
Filename = 'D:\kankan\Eagle.ldf',
Size=3MB,
Maxsize = unlimited,
FileGrowth = 10MB
)

18 修改数据库名

use Eagle
exec sp_renamedb 'Eagle','CnEagle'

19 删除数据库

use master
drop database CnEagle

20 数据表操作create、alter、drop等（table、column、constraint)

21 数据操作select、insert、update、delete等（NOT、AND、OR）

22 视图操作

创建视图：create view view_eagle [with schemabinding] as select * from Eagle
重命名视图：exec sp_rename 'view_eagle','view_cneagle' 
重命名视图的列Name：exec sp_rename 'view_eagle.[Name]','姓名','column'
修改视图：alter view view_eagle as select name,age from Eagle
删除视图：drop view

对试图中数据的操作与对数据表中数据的操作类似，select、insert、update、delete。

22 select * into new_table from tablename创建新表并将来自查询结果的表插入新表中。

23 sql运算符

24 LIKE关键字用于确定特定字符串是否与指定模式相匹配，模式可以包含常规字符串和通配符。[NOT] LIKE

25 BETWEEN关键字

test_expression [NOT] BETWEEN begin_expression AND end_expression

指定的第一个值必须小于第二个值，查询条件是表达式1和表达式2的闭区间值

26 IS [NOT] NULL，在WHERE子句中不能使用比较运算符（=）对空置进行判断，只能使用IS [NOT] NULL对空值进行查询。

27 IN关键字：确定指定的值是否与子查询或列表中的值相匹配。

28 ALL、SOME、ANY关键字：比较标量值和单列集中的值。如>ALL表示大于条件中的每一个值。

29 EXISTS关键字：用于指定一个子查询，测试行是否存在

30 GROUP BY表示按一个或多个列的值将一组选定行组合成一个摘要行集，每一组返回一行。

31 HAVING关键字只能与SELECT语句一起使用，HAVING通常在GROUP BY子句中使用。如果不使用GROUP BY，则HAVING的行为与WHERE子句一样。

32 ORDER BY关键字：除非同时指定了TOP，否则ORDER BY子句在视图、内联函数、派生表和子查询中无效。

33 COMPUTE关键字：生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果中生成控制中断和小计。

34 DISTINCT关键字：去掉重复记录

35 TOP关键字：限制查询结果显示的行数SELECT TOP n * FROM table_name

36 UNION关键字

用于合并多个查询结果。默认情况下合并操作会去掉重复的行，如果希望返回重复的行，就必须明确地指定ALL关键字。

合并表时，两个表源中对应的每个列数据类型不一定相同，只要是数据类型兼容就可以。

当合并的两个表源列数不同时，只要向其中一个表源中添加列，就可以使两表源的列数相同，也就可以合并列了。

SELECT Sname,Sex,Sage FROM Student
UNION ALL
SELECT Cno,Cname,NULL FROM COURSE

37 嵌套查询：将一个查询块嵌套在另一个查询块的WHERE或HAVING短语的条件中的查询。

38 联接查询：在水平方向上合并两个数据集合，并产生一个新的结果集合。

联接可以分为内部联接、外部联接和交叉联接3种。

内部联接：使用比较运算符比较要联接的列中的值得联接。

SELECT fieldlist
FROM table1 JOIN table2
ON table1.column = table2.column

外部联接：将内联接中删除的一些数据保留下来，根据保留下来的行的不同，可以分为左向外部联接、右向外部联接和完整外部联接。

左向外部联接：返回左表中的所有行。

SELECT fieldlist
FROM table1 left JOIN table2
ON table1.column = table2.column

右向外部联接：返回右表中的所有行。

SELECT fieldlist
FROM table1 right JOIN table2
ON table1.column = table2.column

完整外部联接：使用FULL JOIN进行联接，返回左表和右表中的所有行，当某一行在另一个表中没有匹配行时，另一个表的选择列表列将包含空值。

交叉联接：使用CROSS JOIN关键字，没有WHERE子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

39 联接多表

SELECT fieldlist
FROM table1
join table2
join table3
ON table1.column = table2.column
ON table2.column = table3.column

40 case的使用

use Qiu
update students
set age =
case
when sex = '男' then age-1
when sex = '女' then age+1
end

注意：部分关键字的使用不能针对text、ntext和image类型

41 查看存储过程的3种方式

select * from sys.sql_modules
select object_definition(objectid)
sp_helptext 'proc_name' --同时也可用于查看触发器、索引

42 重命名存储过程

sp_rename 'proc_stu','proc_eagle' --不推荐使用，尽量删除再重建（对于触发器也一样）
--注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

43 删除存储过程

drop procedure proc_eagle

44 禁用触发器

disable trigger trigger_name on table_name | DATABASE | ALL SERVER

45 启用触发器

enable trigger trigger_name on table_name | DATABASE | ALL SERVER

46 删除触发器

drop trigger trigger_name

47 创建索引

create unique|clustered|nonclustered index index_name
on table|view (column[ASC|DESC][,...n])

48 删除索引

drop index index_name

49 显示查询语句执行信息：SHOWPLAN_ALL、SHOWPLAN_TEXT在执行或运行时设置，如果为ON，则SQL Server将返回每个语句的执行信息而不执行语句。

50 STATISTIC TO为ON 则显示有关由Transact-SQL语句生成的磁盘活动量的信息。

51 指定数据库启用全文索引

use Qiu
exec sp_fulltext_database 'enable' --对于表则是sp_fulltext_table  --删除索引用drop fulltext index

52 SQL Server事务以以下模式运行：自动提交事务、显式事务、隐式事务和批处理级事务。

53 事务的并发问题：丢失更新、未确认的相关性（脏读）、不一致的分析（不可重复读）、幻象读。

1）丢失更新：撤消一个事务时，把其它事务已提交的更新的数据覆盖了。

2）脏读：一个事务读到另一个事务未提交的更新数据。

3）幻读：一个事务执行两次查询，但第二次查询比第一次查询多出了一些数据行。

4）不可重复读：一个事务两次读同一行数据，可是这两次读到的数据不一样。

54 事务隔离级别SET TRANSACTION ISOLATION LEVEL

55 锁机制

56 分布式事务：涉及一个以上数据库的事务DISTRIBUTION TRANSACTION

57 数据库事务正确执行的四个基本要素：ACID：原子性，一致性，隔离性，永久性。

原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性：两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。

持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

58 三层模式：

1. 模式
   模式又称概念模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data
Description Language，DDL)来描述、定义的，体现、反映了数据库系统的整体观。
2．外模式
    外模式又称子模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data
Manipulation Language，DML)对这些数据记录进行处理。外模式反映了数据库的用户观。
3．内模式
    内模式又称存储模式，对应于物理级，它是数据库中全体数据的内部表示或底层描述，它描述了数据在存储介质上的存储方式及物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义，它是数据库的存储观。
    在一个数据库系统中，只有唯一的数据库，因而作为定义、描述数据库存储结构的内模式和定义、描述数据库逻辑结构的模式，也是惟一的，但建立在数据库系统之上的应用则是非常广泛、多样的，所以对应的外模式不是惟一的，也不可能是惟一的。

4．三级模式间的映射（二级映像）

    数据库的三级模式是数据库在三个级别 (层次)上的抽象，使用户能够逻辑地、抽象地处理数据而不必关心数据在计算机中的物理表示和存储。实际上，对于一个数据库系统而言，物理级数据库是客观存在的，它是进行数据库操作的基础，概念级数据库中不过是物理数据库的一种逻辑的、抽象的描述(即模式)，用户级数据库则是用户与数据库的接口，它是概念级数据库的一个子集(外模式)。
    用户应用程序根据外模式进行数据操作，

　　通过外模式/模式映射，定义和建立某个外模式与模式间的对应关系，将外模式与模式联系起来，当模式发生改变时，只要改变其映射，就可以使外模式保持不变，对应的应用程序也可保持不变；（逻辑性）

　　通过模式/内模式映射，定义建立数据的逻辑结构(模式)与存储结构(内模式)间的对应关系，当数据的存储结构发生变化时，只需改变模式/内模式映射，就能保持模式不变，因此应用程序也可以保持不变。（物理性）