sqlserver提高篇
一．Microsoft SQL Server 系统的体系结构
1.Microsoft SQL Server2008由4个主要的部分组成，即4个服务：
数据库引擎、分析服务、报表服务、集成服务。如下图：
2.Microsoft SQL Server2008提供了两种类型的数据库:系统数据库和用户数据库。
系统数据库包括：master、model、msdb、Resource和tempdb数据库；
用户实例数据库包括：AdventureWorks、AdventureWorksDW、AdventureWorksDW2008、AdventureWorksLT、AdventureWorksLT2008等数据库。
Master数据库是最重要的系统数据库，它记录了服务器配置信息、登录帐户信息、数据库文件信息、SQL Server初始化信息等。
Model 他是于SQLServerAgent服务有关的数据库。该系统记录了有关作业、警报、操作员、调度信息，这些信息可以用作自动化系统操作。
Tempdb 是一个临时数据库，用于存储查询过程中的中间数据和结果。
3.OLTP和OLAP
OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果.
4.数据库对象类型主要包括了：
数据库关系图、表、视图、同义词、可编程性、Service Broker、存储和安全性等。
5.SQL Server Profilter（照相）
它主要用于从服务器中捕获Sqlserver2008事件的工具。
6.数据库引擎优化顾问：
这个工具可以帮助用户分析工作负荷、提出创建高效率索引的建议等功能。他就是一个优化大师，尽可能的提高系统效率。
7.数据库管理员DBA
职责：保证系统正常高效的运行。
二．管理安全性
- 管理登录名
包括：创建登录名、设置密码策略、查看登录名信息及修改和删除登录名等。
登录策略：windows身份验证和sqlserver身份验证登录。
比如：create login lin from windows go//创建windows登录名
create login lin with password=‘sa’//创建sqlserver登录名
- 管理数据库用户
数据库用户是数据库级的主体，是登录名在数据库中的映射，实在数据库中执行操作和活动的行动者。
在某个A数据库中创建对应sa登录名的用户lin：
use A
create user lin from login sa [with default_schema=架构名] go
3.管理架构
架构是形成单个命名空间的数据库实体的集合。
如果一个用户没有制定架构名，则默认为dbo架构。
1)创建架构的sql: create schema 架构名
2)创建某个架构属于某个用户：create schema 架构名 authorization 用户名
3)创建某个架构属于某个用户同时创建表: create schema 架构名 authorization 用户名 create table 表名 (………)
4.数据库角色(权限)
数据库角色是数据库级别的主体，也是数据库用户的集合。
1）创建简单的角色：create role 角色名
2）创建带所有者的角色：create role 角色名 authorization 用户名
3）在角色中添加成员：sp_addrolemember(‘lin’,…)
5.管理权限（放权和释权）
授权：grand语句
释权：revoke语句
拒绝：deny语句 通过继承他人权限则无法收回权限，便可以用deny拒绝。
1) 将创建数据库的权限授予某个lin用户：grant create table to lin [with grant option 表示可以将此权限转给他人]
2) 将lin在某张表的查询权限回收：revoke select on student from lin go
6. Microsoft SQL Server2008内置的加密机制
对称加密和非对称加密两种。密钥分为公钥和私钥。
对称加密：
三.管理数据库
1.数据库文件和文件组的特征：
数据库文件分为：主数据库.mdf、次数据库.ndf、日志文件.ldf;
1)主数据库.mdf包含数据库的启动信息，并指向数据库中的其他文件。用
户数据和对象可存储在此文件中，也可以存储在次要数据文件中。
每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf。
2) 次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。
3)日志文件
文件组：是文件的集合。
注意事项：①一个文件或者文件组只能用于一个数据库，不能用于多个数②一个文件只能是某一个文件组的成员，不能是多个文件组的成员③数据库的数据信息和日志信息不能放在同一个文件或文件组中，应该分开④日志文件永远不能是任何文件组的一部分。
- 管理单位：管理的最小物理单位是以页为单位的，每一个页的大小为8KB.extend 64kb。
- 定义数据库： create database 数据库名
修改数据库： alter database数据库名
- 扩大数据库方法：
1）通过增加新的文件来扩大数据库。
例如：alter database 数据库名 add file ( name=‘‘，filename=’’,size=’’,filegrowth=’’)
2）通过扩大数据库文件的大小来扩大数据库
例如：alter database 数据库名 modify file(name=’’, size=’新值’)
6.三种主要收缩数据库方法：
1）设置数据库为自动收缩；auto_shrink
2)收缩整个数据库的大小: dbcc shrinkdatabase
3)收缩制定的数据文件: dbcc shrinkfile
7.管理数据库快照:
概念: 数据库某一瞬时的状态.
1)创建数据库快照的语法: create database 快照名称 on(name=’’,filename=’’, as snapshot of 源数据库名称)
2)删除快照: drop database 快照名称
8.RAID技术—独立磁盘冗余阵列：
RAID是一个磁盘系统，可以将多个磁盘驱动器合成一个磁盘阵列，以提供高性能、高可靠性及低成本。
1) 容错等级: RAID0（数据并行）、 RAID1(设备镜像)、 RAID5(奇偶信息并行存储).
四．T-SQL语言
1.T-SQL语言的4个特点: 一体化（定义、操纵、控制、事务管理语言与一体）、两种使用方式(交互式和嵌入式到高级语言中)、非过程化语言（告诉干什么就行）、类似人的语言，容易理解和掌握。
2.T-SQL语言的5个特点：数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素。
3.重点说一下附加语言元素：
1）标识符格式规则: 规则一，第一个字符①Unicode标准定义的字母a-z、A-Z②下划线_、符号@、数字符号#； 以一个符号@开头的标识符表示局部变量，以两个符号@@开头表示内置的某些函数。以#开头表示临时表或临时存储过程，##开头标识符表示全局临时对象。
规则二：①跟第一字符规则一样，多了数字。
规则三：不能是保留字；
规则四：不允许嵌入空格和其他特殊字符。
分割标识符：“”、[]
4.变量和常量：
变量以@开头 定义变量如: declare @name varchar
用set为变量设置值
对于常量需要使用’’来使用。
- 控制流语言:
Begin…end 、break、goto、continue、if…else、 while、return、waitfor（悬挂起批处理、存储过程、事务的执行）。
- 错误的捕捉语言:
Try…catchj构造和@@ERROR函数、ERROR_NUMBER(返回错误号)。
- 数据类型：
- 聚合函数和内置函数:
SUM 返回选取结果集所有值的和
MAX 返回选取结果集中所有值的最大值
MIN 返回选取结果集中所有值的最小值
AVG 返回选取结果集中所有值的平均值
COUNT 返回选取结果集中行的数目
内置函数:
Convert Convert(varchar(10),123)结果返回“123” 装换数据类型
DataLength DataLength('12中国')结果返回6 返回任何数据类型的字节数。“中国”包含4个字节，“12”包含两个字节，所以返回6.
日期和时间函数:
-时间函数
select getDate() as currentTime;
select GETUTCDATE() AS UTCtime;
select DAY('2015-04-30 01:01:01');
SELECT MONTH('2015-04-30')AS monthValue;
SELECT YEAR('2015-04-30'),YEAR('1997-07-01');
SELECT DATENAME(year,'2015-04-30 01:01:01') AS yearValue,
DATENAME(quarter,'2015-04-30 01:01:01') AS quaterValue,
DATENAME(dayofyear,'2015-04-30 01:01:01') AS dayofyearValue,
DATENAME(day,'2015-04-30 01:01:01') AS dayValue,
DATENAME(week,'2015-04-30 01:01:01') AS weekValue,
DATENAME(weekday,'2015-04-30 01:01:01') AS weekdayValue,
DATENAME(hour,'2015-04-30 01:01:01') AS hourValue,
DATENAME(minute,'2015-04-30 01:01:01') AS minuteValue,
DATENAME(second,'2015-04-30 01:01:01') AS secondValue;
SELECT DATEPART(year,'2015-04-30 01:01:01'),
DATEPART(month,'2015-04-30 01:01:01'),
DATEPART(dayofyear,'2015-04-30 01:01:01');
SELECT DATEADD(year,1,'2015-04-30 01:01:01') AS yearAdd,
DATEADD(month ,2, '2015-04-30 01:01:01') AS weekdayAdd,
DATEADD(hour,2,'2015-04-30 01:01:01') AS hourAdd;
排名函数:
- --创建测试数据表
- DECLARE @table TABLE (姓名 VARCHAR(4),成绩 INT)
- insert into @table
- SELECT '张三',129 UNION ALL
- SELECT '李四',137 UNION ALL
- SELECT '王二',137 UNION ALL
- SELECT '小明',126 UNION ALL
- SELECT '小六',126 UNION ALL
- SELECT '小白',125 UNION ALL
- SELECT '小黑',124 UNION ALL
- SELECT '西大',123 UNION ALL
- SELECT '小才',120 UNION ALL
- SELECT '师兄',120 UNION ALL
- SELECT '唐朝',99 UNION ALL
- SELECT '尼玛',99
- --查看一下
- --SELECT * FROM @table
- SELECT 姓名,成绩,
- ROW_NUMBER() OVER(ORDER BY 成绩 DESC) AS [ROW_NUMBER],
- RANK() OVER(ORDER BY 成绩 DESC) AS [RANK],
- DENSE_RANK() OVER(ORDER BY 成绩 DESC) AS [DENSE_RANK],
- NTILE(6) OVER(ORDER BY 成绩 DESC) AS [NTILE]
- FROM @table
五．数据库表
1.表的类型: 普通表、以分区表、临时表、系统表。
2.创建表：create table 表名
3.计算列：使用persisted关键字，指定由该列保存计算得到的数据，因此该列是一个物理列，当该列所依赖的其他列中的数据发生变化时该列中的数据也会发生变化。
4.创建临时表: #本地临时表只能由创建着使用 、 ##全局临时表，其生存期间可以为所有的用户使用。
例如：create table ##student(…)
5.增加列和删除列
Alert table 表名 add/drop column_name 数据类型
6.修改该列的数据类型
Alter table table_name alter COLUMN column_name new_type_name数据类型
7.创建和修改标识符列：
identity(seed, increment)
8.ROWGUIDCOL列
Uname unqiueidentityfier default NEWID() 这就一个ROWGUIDCOL列。
10.创建分区表步骤:
1).创建分区函数，指定如何分区。
2）创建分区方案，指定分区函数的分区在文件祖上的位置。
3）创建使用分区表方案的表。
例如：
创建分区函数： create partition function saleAmountPF(INT)
As rang left for values(10,100,1000,10000)
创建分区方案: create partition schema saleAmountPS
As partition saleAmountPF
TO(saleFG1,saleFG2,…)
创建分区表: create table salePT(
serolId int, saleAmount int, saleDate datetime, salePerson varchar(32) on saleAmountPF(saleamount)
)
六．操纵数据
1.使用INSERT语句插入数据
Insert into 表名（字段列…） values(值…)
如果是unicode数据，应该在字符数据的引号前使用N字符。
字符数据需要使用 引号 引起来，数字数据可以直接插入。
提供空值：NULL、default。
使用default values向表中提供一行全部是默认值。
使用bulk insert语句按照用户指定的格式批量的插入数据。
2.使用update语句更新数据
Update 表名 set column_name=expression,….
Where 查询条件
3.使用delete语句删除数据
Delete from table_name where 删除条件
delete操作只是删除表中的数据，并没有破坏表的结构。被删除的数据会被记录在日志中。
Truncate table语句删除表中的数据，系统会立即释放表中的数据和索引所占的空间，并且不把这种数据的变化记录在日中。
4.改变列标题 = 和 as
5.order by进行排序：可以使用多个列进行排序，如果第一个列的值相同，那么就按照第二个列排序，每一个列都可以指定升序或降序。
Top：返回指定数量的数据行。用在分页。
6.like进行模糊匹配 % _ [] [^]
7.Compute字句： 当使用聚合函数得到的结果集全是聚合值，没有明细值，解决方法就是是哟哦那个compute字句。
两种形式：一种带by字句；一种不带by字句。
例如： select … from 表名 order by 排序字段 compute sum(字段)
select … from 表名 order by 排序字段 compute sum(字段) by 字段
8.分组技术:group by子句
分为无条件的分组：
Select 字段…from 表名 where 条件 group by 列名
有条件的分组:
Select 字段…from 表名 where 条件 group by 列名 having 分组筛选条件
ROLLUP得到各组的单项组合和CUBE关键字得到各组之间的任意组合。
9.连接技术：交叉连接即笛卡儿积 cross join、内连接inner join 用on指定连接条件按、左外连接left outer join、有外连接right outer join、完全外连接full outer join
10.子查询技术：where、group by、having
七．索引
1.索引的类型和特点
建立索引的优点：
1）.大大加快数据的检索速度;
2）.创建唯一性索引,保证数据库表中每一行数据的唯一性;
3）.加速表和表之间的连接;
4）.在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间.
索引类型：
根据数据库的功能,可以在数据库设计器中创建四种索引：唯一索引、非唯一索引、主键索引和聚集索引.尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一约束.
唯一索引：
唯 一索引是不允许其中任何两行具有相同索引值的索引.当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存.数据库还可能防止 添加将在表中创建重复键值的新数据.例如,如果在 employee 表中职员的姓 (lname) 上创建了唯一索引,则任何两个员工都不能同姓.
非唯一索引：
非唯一索引是相对唯一索引,允许其中任何两行具有相同索引值的索引.当现有数据中存在重复的键值时,数据库是允许将新创建的索引与表一起保存.这时数据库不能防止添加将在表中创建重复键值的新数据.
主键索引：
数据库表经常有一列或列组合,其值唯一标识表中的每一行.该列称为表的主键.在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型.该索引要求主键中的每个值都唯一.当在查询中使用主键索引时,它还允许对数据的快速访问.
聚集索引（也叫聚簇索引）：
在聚集索引中,表中行的物理顺序与键值的逻辑（索引）顺序相同.一个表只能包含一个聚集索引.如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配.与非聚集索引相比,聚集索引通常提供更快的数据访问速度.
2.创建索引：create index语句
Create [unique唯一] [clustered聚集 | nonclustered非聚集] index index_name on table_or_view_name (列名)[include (列…)包含性]
例如: create unique clustered index index_xxx on tabl_xxx(…)
3.维护索引：dbccshow_statistics命令和图形化工具。查看索引碎片信息:sys.dm_db_index_physical_stats系统函数和使用图形化工具。
