Sql Server总结

 主键

主键就是数据行的唯一标识。不会重复的列，才能当主键。一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键。主键有两种选用策略：业务主键和逻辑主键。

业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；

逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键（自增主键）。

主键注意的地方：

1.主键的作用：唯一标识表中的一条记录。

2.选择多列同时作为一个主键→组合主键（复合主键）.(一般不建议采用)

3.尽量选择单列作为主键：

   1>必须唯一（不能有重复），

2>该列不能为空值。

3>比较稳定的列（不经常更新的，最好是建好以后再也不更新。）

4>选择主键列的时候，推荐使用“逻辑主键”(例如：自动编号(bigInt)、guid（数据类型：uniqueidentifier）等。)，不推荐“业务主键”（选择有实际意义的列作为主键（例如：身份证号，工号、学号等。））

自增主键注意事项

自动编号只会向前增长，不会倒退。如果插入数据出错，自动编号也会增长，下次插入时，编号会“跳过”。自动编号列是不能人为修改的。（一般情况）。这也间接说明了，sql语句操作本身就是存储过程，只是sqlServer帮我们自动提交和回滚。

自增主键再设计时将自增功能关闭，那么就可以人为的设置主键大小，下次再插入数据时，自增主键会在你设置的大小上进行累加。打开和关闭自增主键的自增功能代码：set IDentity_insertTblTacheron|off。

若想主键恢复到开始时（零），那么需要用删除数据：truncate table t1将数据全部清空，此时自增主键将变为1。

外键

作用：消除数据冗余。

当主键更新时，外键也会随之而更新。

条件：选用的外键必须是主键

注意：当创建了主外键关系后，如果在外键表中有任何记录引用了主键表中的某条记录，则在主键表中不能删除该记录。同时主键表也不能删除。

新建一张表：员工信息表

约束

用来控制数据的类型，大小等。

主键、外键、约束的用法：

数据库的设计中关系中建立。

数据库的设计中列的属性。

自己写sql语句关键字确定。

数据类型

二进制数据类型

Image存储非子符和文本的数据可用来存储图像

二进制数据占用的空间都比较大，一般不使用。

文本数据类型：字符数据包括任意字母、符号或数字字符的组合

Char   最大8000

固定长度的非 Unicode 字符数据。固定长度的字符串相对于可变长度的字符串来说效率要高一些，在数据长度固定的情况下优先选用固定长度，省去了计算长度的过程，提高效率。例如：Char(10),10表示10个字符。能存储10个ASCII字符和5个Unicode字符。

Nchar最大4000

固定长度的Unicode 数据。

Varchar最大8000

可变长度非Unicode 数据。

Nvarchar最大4000

可变长度Unicode 数据。

Ps:代N的一般都是Unicode编码格式，带var是可变长度。

Text和varchar(max)

存储长文本信息(指针,2G)varchar(max)，大字符串类型可以保存非常多的字符，但是对于这种类型的数据DBMS经常将它们保存到单独的空间中，这就导致了数据的保存和加载速度比较慢，因此除非必要，否则不要使用。Ntext和nvarchar(max)可变长度。

日期和时间

Datetime日期和时间，日期和时间在单引号内输入

数字数据

该数据仅包含数字，包括正数、负数以及分数。Int、smallint整数;Float、real数字;

货币数据类型

Money用于十进制货币值,money 和smallmoney数据类型精确到它们所代表的货币单位的万分之一。

Bit数据类型

Bit存储布尔数据类型，表示是/否的数据。Bit类型在管理器上只能用true和false，在写程序代码的时候只能用1和0。

 数据类型的意义：

1>提高效率。（减少空间，提高访问效率，‘1234567’如果用字符串存储占7个字节，如果用整型则占4个字节。）；

2>能对数据进行正确的计算1+1,如果是整型则为2,如果为字符串则为11。

不同的数据类型占用空间不尽相同，删除数据后又有的空间还被占用并没有释放。此时可收缩数据库，来回收没有用的数据空间。

Sql语句：

SQL 全名是结构化查询语言（Structured Query Language），是关系数据库管理系统的标准语言。SQL语句是和DBMS“交谈”专用的语句，不同DBMS都认SQL语法；SQL语句中字符串用单引号、单等号（在数据库中两个'表示一个‘也就是 ''转义‘）；SQL语句是大小写不敏感的，不敏感指的是SQL关键字，字符串值还是大小写敏感的；

SQL主要分DDL（数据定义语言,建表、建库等语句。）、DML（数据操作语言）和DCL （数据库控制语言）。Create Table、Drop Table、Alter Table等属于DDL，Select、Insert、Update、Delete等属于DML， GRANT 授权、REVOKE 取消授权属于DCL 。

创建数据库例子：

--drop database MySchool

create database MySchool

on(--括号一定是圆括号

name='MySchool_data',--数据库名称

filename='d:\MySchool_data.mdf',--物理文件名

size=5mb,--初始大小

maxsize=10mb,--最大大小

filegrowth=15% --主文件增长率)

log on(

name='MySchool_log',--日志文件名

filename='d:\MySchool_log.ldf',--日志物理文件名

maxsize=4mb,--最大大小

size=2mb,

filegrowth=1mb)

创建表的例子：

--查询当前实例中的已经存在的数据库

select*fromsys.databases//--CTRL + R ;切换消息窗口的显示。

--删除TestDb数据库

DROPDATABASE TESTDB;

--将当前的代码执行环境切换到TestDb数据库中

useTestDb

--创建TblStudent表

createtableTblStudent

(

         tSIdintidentity(1,1)primarykey,

         tSNamevarchar(50),

         tSGenderchar(2)notnull,

         tSAddressvarchar(300),

         tSPhonevarchar(100),

         tSAgeint,

         tSBirthdaydatetime,

         tSCardIdvarchar(18)null,

         tSClassIdintnotnull

)

Go

数据库插入

向表中插入一行（该行的每一列都有数据）。下面有多条语句的例子。

• • insert into 表(列名，列名) values(值1，值2)
  • insert语句可以省略表名后的列名，但是不推荐。
    • Insert into 表values(值1，值2)

插入数据时，只向某些列插入数据：如果插入的行中有些字段的值不确定，那么Insert的时候不指定那些列即可。

• • Insert into 表(列1)  values(值1)

注意：

主键不能有重复值;自动编号列不需要手动插入。【SET IDENTITY_INSERT 表名ON 】;插入数据时的单引号问题;

Insert into 表（列）select 列1，列2 union   Insert into 表（列）select 列1，列2 from 表

Select 列  into 新表名 from 旧表

N前缀：N’字符串’，在服务器上执行的代码中（例如在存储过程和触发器中）显示的Unicode 字符串常量必须以大写字母N 为前缀。即使所引用的列已定义为Unicode 类型，也应如此。如果不使用N 前缀，字符串将转换为数据库的默认代码页。这可能导致不识别某些字符。

数据删除

删除表中全部数据：DELETE  FROM  Student。Delete只是删除数据，表还在，和Drop Table不同。

Delete 也可以带where子句来删除一部分数据：DELETE FROM Student WHERE sAge> 20

truncate table student 的作用与delete from student一样，都是删除student表中的全部数据，区别在于：

• • 1.truncate语句非常高效。由于truncate操作采用按最小方式来记录日志，所以效率非常高。对于数百万条数据使用truncate删除只要几秒钟，而使用delete则可能耗费几小时。
  • 通过truncate语句删除表中的数据的时候，只能一次性都清空，不能根据条件来删除，而delete可以根据条件来删除。
  • 2.truncate语句会把表中的自动编号重置为默认值。
  • 3.truncate语句不触发delete触发器。

约束-保证数据完整性

数据库约束是为了保证数据的完整性(正确性)而实现的一套机制

1. 1. 非空约束
   2. 主键约束(PK) primary key constraint 唯一且不为空
   3. 唯一约束(UQ)unique constraint 唯一，允许为空，但只能出现一次
   4. 默认约束(DF)default constraint 默认值
   5. 检查约束(CK)check constraint 范围以及格式限制
   6. 外键约束(FK)foreign key constraint 表关系
   7. 增加外键约束时，设置级联更新、级联删除：

语法：

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

--手动删除一列(删除EmpAddress列)

alter table Employees dropcolumn EmpAddress

--手动增加一列(增加一列EmpAddrvarchar(1000))

alter table Employees add EmpAddr11 varchar(500),AA int,bb varchar(10)

--手动修改一下EmpEmail的数据类型(varchar(200))

altertable Employees altercolumn EmpAddr varchar(1000)

--为EmpId增加一个主键约束

altertable Employees addconstraintPK_EmpIdprimarykey(EmpId)

--非空约束,为EmpName增加一个非空约束

altertable Employees altercolumnEmpNamevarchar(50)notnull

--为EmpName增加一个唯一约束

altertable Employees addconstraintUQ_EmpNameunique(EmpName)

--为性别增加一个默认约束，默认为'男'

altertable Employees addconstraintDF_EmpGenderdefault('男')forEmpGender

--为年龄增加一个检查约束：年龄必须在-120岁之间，含岁与岁。

altertable Employees addconstraintCK_EmpAgecheck(EmpAge>=0 andEmpAge<=120)

--创建一个部门表，然后为Employee表增加一个DepId列。

altertable Employees addDeptIdint

--增加外键约束

altertable Employees addconstraint FK_DeptId_DepId foreignkey(DeptId) references Department(DepId)

--添加主键约束

alter table Score add constraint  PK_Score primary key(sId)

--添加唯一约束

alter table student add constraint UQ_student unique(sNo)

--添加默认约束

alter table student add constraint DF_student default('男') for sSex

--添加检查约束

alter table student add constraint CK_student check(sAge>=18 and sAge<=100)

--添加外键约束（主键表Class 外键表student）

alter table student add constraint FK_student  foreign key(sClassId) references Class(cId) --外键student表中的sClassId来 references引用主键表中的cid

--级联删除

--on delete cascade on update cascade

--删除约束

alter table student drop constraint FK_student

--用一条语句为表增加多个约束。

alter table Employees add

constraint PK_Employees_EmpId primary key(EmpId),--增加主键约束

constraint UQ_Employees_EmpName unique(EmpName),--增加唯一约束

constraint DF_Employees_EmpGender default('女') for EmpGender,--默认约束

constraintCK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120),

constraintFK_Employees_Department_DepId foreign key(DepId) references Department(DepId)

alter table Employees drop constraint

CK_Employees_EmpAge,

FK_Employees_DepId

CREATE TABLE 语句和ALTER TABLE 语句的REFERENCES 子句支持ON DELETE 子句和ON UPDATE 子句。还可以使用“外键关系”对话框定义级联操作：

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

--删除原有的外键约束

alter table Employees drop constraint FK_Employees_Department_DepId

--设置主外键关系的级联删除

alter table Employees add constraint FK_Employees_Department_DepId foreign key(DepId) references Department(DepId)

on delete CASCADE --设置级联删除--on update cascade --设置级联更新

注意：如果timestamp列是外键或被引用键的一部分，则不能指定 CASCADE。

关键字

Top 获取前几条数据，top一般都与order by连用

Distinct 去除重复数据

DISTINCT是对查询出的整个结果集进行数据重复处理的，而不是针对某一个列。

聚合函数

SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量:记录的条数。）

聚合函数对null值不计算。如果一行的数据都是null，count(*)包含对空值行、重复行的统计。

补充：

COUNT(*) 返回组中的项数。包括NULL 值和重复项。

COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。

COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。

对于大于2^31-1 的返回值，COUNT 生成一个错误。这时应使用COUNT_BIG。

带条件查询

Select …from…where … 。

Between…and …在之间。 建议：优先使用between … and …，而不是“列>=值1 and 列<=值2 ”，between … and …已做过优化处理，效率高。

带条件的查询-模糊查询(都是针对字符串操作的)

例如：Select  * from student where sName like ‘%亮%’

_    、%   、[]   、^

通配符%多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

通配符_ 单字符匹配，它匹配单个出现的字符

[] 只匹配一个字符并且这个字符必须是[]范围内的[0-9]  [a-z]

not与like一起使用：not like ….

要通配_、%、[、^这些字符怎么办？[_]、[%]、[ [ ]、^(不需要放到中括号里，因为^只有放到中括号中才认为是通配符)

使用通配符时应着重考虑的另一个问题是对性能的影响。如果表达式以通配符开头，就不能使用索引（就如同给定了姓名"%mith" 而非"Smith" 时，将无法知道应从电话簿的哪一页开始查找）。表达式中间或结尾处的通配符不妨碍索引的使用（就如同在电话簿中一样，如果姓名为"Samuel%"，则不论Samuels 和Samuelson 是否都在电话簿上，都知道该从何处开始查找。）

空值处理

数据库中，一个列如果没有指定值，那么值就为null，数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。SQL中使用is null、is not null来进行空值判断，可以用IsNull来对null数据进行转换。

数据排序

ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。Order by 语句一般要放到所有语句的后面，就是先让其他语句进行筛选，全部筛选完成后，最后排序一下。

表中数据是集合，集合是没有顺序的。Order by 返回的数据是有顺序的，故此我们把order by 以后返回的数据集合叫“游标”。

数据分组

在使用select查询的时候，有时需要对数据进行分组汇总，这时就需要用到group by语句。select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息。//分组一般都和聚合函数连用。

GROUP BY子句必须放到WHERE语句的之后 ,Group By与Order By都是对筛选后的数据进行处理，而Where是用来筛选数据的。没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的（聚合函数中除外）。

Having语句

对表中的数据分组后，会得到一个分组后的结果集，如何对该结果集在进行筛选？having

注意Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。

Having 是Group By的条件对分组后的数据进行筛选（与Where类似，都是筛选，只不过having是用来筛选分组后的组的。）

在Where中不能使用聚合函数，必须使用Having，Having要位于Group By之后。

Having的使用几乎是与where一样的，也可以用in。

Sql语句的执行顺序：

5>…Select 5-1>选择列,5-2>distinct,5-3>top

1>…From表

2>…Where 条件

 3>…Group by列

4>…Having筛选条件

 6>…Order by列

类型转换函数

CAST ( expression AS data_type)

CONVERT ( data_type, expression,[style])

CAST(RIGHT(sNo,3) ASINTEGER) as后三位的整数形式,

类型转换只是转换查询出来的数据类型，数据库中真实的数据类型并没有转换。

联合结果集union（集合运算符）

集合运算符是对两个集合操作的，两个集合必须具有相同的列数，列具有相同的数据类型（至少能隐式转换的），最终输出的集合的列名由第一个集合的列名来确定。（可以用来连接多个结果）

联合(union)与连接(join)不一样。（join说的是left join等）

基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。

联合：将多个结果集合并成一个结果集。union(去除重复，相当于默认应用了distinct)、union all

切记：在使用union连接数据的时候，必须保证对应的列的数据类型是一致的，如果数据类型不一致，则报错。若数据类型不一致时，此时可以使用类型转换：cast() convert()。

一次插入多条数据

把现有表的数据插入到新表（表不能存在）,例如，为表建备份。

select * into newStudent from student（newStudent表在select查询的同时自动建立。）--通过这种方式复制，只能复制表中的数据，以及列的名字和数据类型。对于约束，不会复制过来。

Select * into newTbl from oldTbl where 1<>1,这样做可以只复制表结构，但效率并不高。建议：select top 0 * into newTbl from oldTbl

如果表已经存在的话：insert into backupStudent select * from students(backupStudent表必须提前建好)

插入新数据时比较特殊的用法：

insert into Score(studentId,english,math)

select 1,80,100 union

select 1,80,100 union

select 3,50,59 union all

select 4,66,89 union

select 5,59,100

此处如果用union all同样会去除重复数据。

字符串函数：

LEN() ：计算字符串长度（字符的个数。）

datalength();//计算字符串所占用的字节数，不属于字符串函数。

LOWER() 、UPPER () ：转小写、大写

LTRIM()：字符串左侧的空格去掉

RTRIM () ：字符串右侧的空格去掉

LTRIM(RTRIM('         bb        '))

LEFT()、RIGHT()  截取取字符串SELECT LEFT('abcdefg',2)

SUBSTRING(string,start_position,length)，索引从1开始。参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。SELECT  SUBSTRING('abcdef111',2,3)

日期函数：

GETDATE()：取得当前日期时间

DATEADD (datepart , number, date )，计算增加以后的日期。参数date为待计算的日期；参数number为增量；DATEADD(DAY, 3,date)为计算日期date的3天后的日期，而DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期。

DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。datepart为计量单位，可取值参考DateAdd。例如，统计不同入学年数的学生个数：select DateDiff(year,sInDate,getdate()),count(*) from student Group by DateDiff(year,sInDate,getdate())

DATEPART (datepart,date)：返回一个日期的特定部分。也可以用Month()、year()、day()来代替。

Datepart可选值

取值     别名         说明

year      yy,yyyy     年份

quarter         qq,q 季度

month  mm,m      月份

dayofyear    dy,y  当年度的第几天

day       dd,d 日

week    wk,ww     当年度的第几周

weekday      dw,w        星期几

hour     hh    小时

minute mi,n 分

second ss,s  秒

millisecond  ms    毫秒

Case函数用法

(1)

等值判断，相当于switch case

CASE expression

WHEN value1 THEN returnvalue1

WHEN value2 THEN returnvalue2

WHEN value3 THEN returnvalue3

ELSE defaultreturnvalue

END

在Expression中可以添加判断条件。例如某列>1等。

(2)

CASE

WHEN condition1 THEN returnvalue1

WHEN condition 2 THEN returnvalue2

WHEN condition 3 THEN returnvalue3

ELSE defaultreturnvalue

END

相当于if…else if…else….（可以进行区间判断）

索引

全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。

索引：相当于字典中的目录；加快查询速度；执行增删改的时候降低了速度；

索引分类：聚集索引、非聚集索引、是否为唯一索引。

聚集索引

相当于字典中拼音目录

拼音目录的顺序和数据的顺序是一致的

一个表中只能有一个聚集索引。

索引的排序顺序与表中数据的物理存储位置是一致的，一般新建主键列后回自动生成一个聚集索引

非聚集索引(逻辑上的排序。)

一个表中可以有多个非聚集索引。

相当于字典中笔画目录

笔画目录的顺序和数据是无关的

建索引的目的是为了加快查询速度。索引之所以能加快查询速度是因为索引对数据进行了排序。建索引应该建在某个列上，就是说要对某个列排序。这是，如果用用户执行一条查询语句，where条件中包含了建索引的那列，那么这时，采用用到索引，否则，不会使用索引。Name=数据（用索引），name like ‘%aa%’(不用索引)

创建索引的方式，在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。但是，即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。--删除索引drop index T8.IX_T8_tage

=======非聚集索引=============

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson(SalesQuota, SalesYTD); GO

====创建唯一非聚集索引=============

CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name); GO

=======创建聚集索引=================

CREATE TABLE t1 (a int, b int, c AS a/b); CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c); INSERT INTO t1 VALUES (1, 0);

子查询

把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用)。就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。说白了就是：就是把一个查询的结果作为另一个查询的数据源。SELECT * FROM (SELECT * FROM student where sAge<30) as t

只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。

select * from student where sClassId = (select cId from class where cName='高二二班')

子查询返回的值不止一个。当子查询跟随在=、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。子查询基本分类：独立子查询（子查询可以独立运行）、相关子查询（子查询中引用了父查询中的结果）。

常用的判断：In、exists、not in、not exists

一些标示符的用法：

Col1 in (1,2,3) 等价于col1=1 or col1=2 or col1=3

Col1 >= any(1,2,3) 等价于col1 >=1 or col1>=2 or col1>=3

Col1>= all(1,2,3)等价于col1>=1 and col1>=2 and col1>=3

连接两个表的查询：

select * from TblStudent  where  exists

(  --子查询的结果，要依赖于当前父查询中当前行的tsClassid的结果。

select * from TblClass

where tClassName='计算机软件班' andtClassId=TblStudent.tsClassId)

分页

以前分页可以使用Top语句来进行。SQLServer2005后增加了Row_Number函数简化实现。

Over()就是传说中的”开窗函数”，本身聚合函数只会计算一次，开窗以后就可以为每条记录都计算一次聚合了。Over子句可以为每一行计算表达式而不是只为一行，并且over可以单独定义窗口中的排序方式，而不影响最终结果集。

例如：select *,row_number() over(order by id asc) as hanghao from callrecords order by id desc。

两种分页比较：

select*from(selectrow_number()over(orderbycustomeridasc)asrnumber,*from customers)astblwheretbl.rnumberbetween 15 and 20

select top 5 * from student where sId not in (select top (5*(2-1)) sId from student order by sIddesc)order by sIddesc

sql中的over函数和row_numbert()函数配合使用，可生成行号。over里的order只能查查询里的原始数据进行操作，不会对计算出的新值或新字段起作用。

表连接Join

联接条件可在FROM或WHERE子句中指定，建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选联接条件所选的行。

联接可分为以下几类：

1、内联接（典型的联接运算，使用像 =  或<>之类的比较运算符）。包括相等联接和自然联接。

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：

1）LEFT  JOIN或LEFT OUTER JOIN

左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

3、自连接

select ts.tId,ts.tName as 子分类,td.tName as 父分类from Category as ts inner join (select tId,tName from Category) as td  on ts.tParentId=td.tId

临时表

局部临时表

create table #tbName(列信息);--表名前缀#

只在当前会话中有效，不能跨连接访问。作用域范围类似C#：如果直接在连接会话中创建的，则当前连接断开后删除，如果是在存储过程中创建的则当前存储过程执行完毕后删除

全局临时表

create table ##tbName(列信息);--表名前缀##

多个会话可共享全局临时表。当创建全局临时表的会话断开，并且没有用户正在访问(事务正在进行中)全局临时表时删除

表变量：declare @varT1 table(col1 int,col2 char(2));//存储更小量的数据，比临时表有更多的限制。

注意：临时数据都存储在tempdb,当服务重新启动的时候，会重建tempdb。

临时表的应用：

在对大数据量的表做复杂子查询的时候为了提高执行效率，降低内存消耗可以使用临时表。

Ps：数据库中的临时表有很多。例如上面nsert into class(cName,cDescription) output inserted.classId values(‘高三一班’,‘描述’)，就是利用的Inserted临时表。

数据表中的数据中的数据过百万，如何优化查询？

大数据时可以考虑使用临时表，临时表在内存中的，速度比较快。可以使用临时表。还可以分化在多个表中，以便于数据的优化。

视图概述

视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上。视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录。相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）。视图的目的是方便查询，所以一般情况下不能对视图进行增删改。

优点：

筛选表中的行\降低数据库的复杂程度

防止未经许可的用户访问敏感数据

普通视图：并不存储数据（虚拟表），访问的是真实表中的数据。

使用视图注意事项：

1.视图中的查询不能使用order by ，除非指定了top语句。

视图被认为是一个虚拟表，表是一个集合，是不能有顺序的。而order by 则返回的是一个有顺序的，是一个游标。但可以在视图中使用select top percent + order by 。所有查询的列，必须有列名,且列名必须唯一。create view vw_name as 后不能跟begin end。

例如：create view vw_tab as  select top 100 percent * from tab order by col1 desc

以上语句执行完毕以后查询select * from vw_tab也不会排序，与select * from vw_tab order by col1 desc不一样。top已经取得了上次order by 的结果前几个结果，并且top输出的结果没有再次排序，所以无法保证输出的结果是desc排序后的结果。视图中不允许使用order by，尽量不要用。

索引视图：在视图上创建唯一聚集索引。数据会保存在数据库中而不是引用表中的数据。

例如：

createview vw_ix_T1WITHSCHEMABINDINGAs  selectautoid,uname,usrIdfrom dbo.T1   createuniqueclusteredindex ix_vw_t1 on vw_ix_T1(autoId)

局部变量

(1).先声明再赋值：

声明局部变量

    DECLARE @变量名数据类型。例如:DECLARE @name varchar(20)

PS:声明变量时，可以直接赋值。此时赋的值称为做默认值。

赋值

SET @变量名 =值      --set用于普通的赋值

SELECT @变量名 = 值  --用于从表中查询数据并赋值

输出变量的值

SELECT 以表格的方式输出,可以同时输出多个变量。

PRINT 以文本的方式输出，一次只能输出一个变量的值。

变量的种类

局部变量：

局部变量必须以标记@作为前缀，如@Age int。

局部变量：先声明，再赋值。

全局变量（系统变量）：

全局变量必须以标记@@作为前缀，如@@version。

全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。

强调：

1.局部变量标志：一个@；

2.全局变量标志：两个@。

3.我们可以定义局部变量，但不能定义全局系统变量，但我们能读取全局系统变量的值。

全局变量

@@ERROR                                最后一个T-SQL错误的错误号

@@IDENTITY                           最后一次插入的标识值

@@LANGUAGE                        当前使用的语言的名称

@@MAX_CONNECTIONS      可以创建的同时连接的最大数目

@@ROWCOUNT                      受上一个SQL语句影响的行数

@@SERVERNAME                   本地服务器的名称

@@TRANSCOUNT                   当前连接打开的事务数

@@VERSION                            SQL Server的版本信息

@@error变量，在每次执行完SQL语句后，都会为@@error变量赋值，如果上次执行的SQL语句有错，则将@@errro赋值为一个不为0的值，否则（执行没错），则将@@error赋值为0。

IF ELSE

IF(条件表达式)  BEGIN --相当于C#里的{  语句1  ……  END --相当于C#里的}

ELSE BEGIN语句1 ……  END

While循环

WHILE(条件表达式)  BEGIN--相当于C#里的{ 语句   ……    continue   BREAK  END --相当于C#里的}

事务

事务：同生共死

指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行。这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行。

语法步骤：

开始事务：BEGIN TRANSACTION

事务提交：COMMIT TRANSACTION

事务回滚：ROLLBACK TRANSACTION

判断某条语句执行是否出错：全局变量@@ERROR；@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计：SET @errorSum=@errorSum+@@error

数据库中使用事务的例子：

begin transaction

declare @error int

set @error = 0

update bank set balance=balance-1000 where cid='0001'

set @error = @error + @@error

update bank set balance=balance + 1000 where cid='0002'

set @error = @error + @@error

if @error != 0

rollback transaction

else

commit transaction

go

存储过程

存储过程---就像数据库中运行方法(函数)，和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用。

优点：

执行速度更快–在数据库中保存的存储过程语句都是编译过的

允许模块化程序设计–类似方法的复用

提高系统安全性–防止SQL注入

减少网络流通量–只要传输存储过程的名称

系统存储过程

由系统定义，存放在master数据库中，名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。可以参考：http://www.knowsky.com/534162.html

系统存储过程

sp_databases                            列出服务器上的所有数据库。

sp_helpdb                                  报告有关指定数据库或所有数据库的信息

sp_renamedb                            更改数据库的名称

sp_tables                                            返回当前环境下可查询的对象的列表

sp_columns                               回某个表列的信息

sp_help                                       查看某个表的所有信息

sp_helpconstraint                    查看某个表的约束

sp_helpindex                             查看某个表的索引

sp_stored_procedures            列出当前环境中的所有存储过程。

sp_password                             添加或修改登录帐户的密码。

sp_helptext                                        显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

创建存储过程（自定义存储过程）

定义存储过程的语法

CREATE  PROC[EDURE]  存储过程名

@参数1  数据类型 = 默认值 OUTPUT,

@参数n  数据类型 = 默认值 OUTPUT

AS

SQL语句

参数说明：参数可选；参数分为输入参数、输出参数；输入参数允许有默认值

EXEC  过程名  [参数]

存储过程中使用输出参数

输出参数关键字：OUTPUT。存储过程的参数可以是默认值（在创建时赋值）。传参时也可以颠倒参数的传入顺序，但需要指定变量的名称。

执行存储过程

用ExecuteNonQuery可以；用ExecuteSalar还可以；用ExecuteReader照样可以（记得用IsDBNull判断空值）。

数据库中的默认事务

默认情况下执行的sql语句都是隐式事务。可用set Implicit_Transactions on|off 打开或关闭隐式事务

参数的作用范围

参数一般用在where语句后面，或者赋值时，不能把表名、列名等用参数来代替。

触发器

触发器的作用：自动化操作，减少了手动操作以及出错的几率。

触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。【在SQL内部把触发器看做是存储过程但是不能传递参数】

一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。

触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

那究竟何为触发器？

在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。

常见的触发器

DML触发器：Insert、delete、update（不支持select）；after触发器(for)、instead of触发器（不支持before触发器）；

DDL触发器：Create table、create database、alter、drop…。

触发器触发时会存在inserted和deleted两个临时表，但执行完毕这些会被立即删除。

inserted表与deleted表

inserted表与deleted表是干什么的？

inserted表包含新数据：insert、update触发器会用到；deleted表包含旧数据：delete、update触发器会用到。

 deleted表

deleted表存放由于执行delete或update语句而要从表中删除的所有行。在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted 表，这两个表不会有共同的行。

inserted表

inserted表存放由于执行insert或update语句而要向表中插入的所有行。在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中， inserted表的内容是激活触发器的表中新行的拷贝。

说明：update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。

不能对视图定义 AFTER 触发器。

After触发器：

在语句执行完毕之后触发

按语句触发，而不是所影响的行数，无论所影响为多少行，只触发一次。

只能建立在常规表上，不能建立在视图和临时表上。(*)

可以递归触发，最高可达32级。

instead of触发器

用来替换原本的操作

不会递归触发

可以在约束被检查之前触发

可以建在表和视图上（*）

语法：

CREATE TRIGGER triggerName ON 表名

after(for)（for与after都表示after触发器）  |  instead of

 UPDATE|INSERT|DELETE（insert,update,delete）

AS

begin

…

end

触发器例子：

插入

CREATE TRIGGER tr_updateStudent ON score

for INSERT

AS

Begin

declare @sidint,@scoreidint

select @sid = studentId,@ scoreid=sid from inserted

if exists(select * from student where sid=@sid)

print ‘插入成功’

else

delete from score where sid = @scoreId

End

Insert into score (studentId,english) values(100,100)

删除：

CREATE TRIGGER tr_deleteStudent ON student

for delete

AS

begin

insert into backupStudent select * from deleted

End

Delete from student where sId=1

当然触发器也可以变成多种操作（for 后面用逗号隔开即可）。sql Server中的触发器是表级触发器，无论删除多少行或者插入多少行，只触发一次。

触发器使用建议：

尽量避免在触发器中执行耗时操作，因为触发器会与SQL语句认为在同一个事务中。（事务不结束，就无法释放锁。）

避免在触发器中做复杂操作，影响触发器性能的因素比较多（如：产品版本、所使用架构等等），要想编写高效的触发器考虑因素比较多（编写触发器容易，编写复杂的高性能触发器难！）。触发器编写时注意对多行触发时的处理。（一般不建议使用游标，性能问题！）。

游标

SQL语句是把结果集作为一个整体（整个作为一个单元）来操作的，但有时候我们需要针对结果集中的每条记录（或某部分记录）进行特殊操作，这时就需要用到游标。

Update更新语句将某列更新为指定的值，但是如果每行的该列要更新的值都不一样呢？可以用游标和sql语句。当然尽量用sql语句。

游标是过程化思维，而SQL本身是集合化思维。比如：把所有人（或某些人）的年龄都加1岁。（是对一个集合做的整体的操作“都加1岁”，基于集合的思维，集合中每个元素的操作都一样）针对表中的每一行数据内容调用某个存储过程，或者为每个用户的手机发一条短信。（这个是针对表中每条记录都要做的事情，类似于集合遍历，并且有可能对每条数据执行的操作都不一样）。

多数情况下使用查询的性能要比使用游标高效，但并不是任何情况下使用查询性能都高于使用游标。

游标为什么性能低下？

1.读取次数本身变多了；

2.没有应用比较好的查询优化，数据库会多sql语句执行多种优化，并选择最优的一种，而对于游标则没有更多的选择；

定义游标基本语法

1.delcare 游标名 cursor [local | global][游标选项] for 查询语句

2.open 游标名

3.fetch [next] from 游标名 into @v1,@v2…

4.判断@@fetch_status状态，继续fetch

5.close 游标名–关闭游标

6.deallocate 游标名–删除游标中数据库中保存的数据结构（彻底释放资源）

游标的例子：

将每个老师的工资更新为原来的工资+奖金

定义两个变量，用来存储id于奖金

declare @tid int

declare @reward money

1.建立一个基于奖金的游标：

declare cur_reward cursor fast_forward for select ttid,reward

2.打开游标

open cur_reward

--通过游标读取数据    into给声明的变量赋值

fetch next from cur_reward into @tid,@reward

while @@fetch_status=0

begin

--更新工资

update TblTeacher set ttsalary=ttsalary+@reward where ttid=@tid

fetch next from cur_reward into @tid,@reward

end

--3关闭游标

close cur_reward

--4释放资源

deallocate cur_reward

Sql语句更新：

--将每个人的工资在原来的基础上减去根据奖金表中的金额（update更新一张表中的数据，而连接的是两张表）

update TblTeacher set ttsalary=ttsalary-(select reward from TbTeacherSalary where TblTeacherSalary.ttid=TblTeacher.ttid)

sql语句的另外一种写法

update TblTeacher set tTSalary=tTSalary+b.reward from TblTeachersalary b where TblTeacher.ttid=b.ttid（注意这里的有from和它的更新语句）。

动态sql语句

在sqlServer动态sql语句避免攻击问题：用动态sql非常灵活，不可避免的存在注入攻击，只能减小危害，不能避免。由于在SqlServer端，所以处理的方法也多种多样，常见的是：

替换关键字；设置权限，只能查询；限制字符长度；

危害语句如：select * from sysobjects;exec sp_databases;

可以将变量带进动态sql语句，只是需要将变量的类型转换成字符串进行拼接。

动态sql中的输出变量问题：

只看最后一句：exec sp_executesql @sql,N'@Cnt int output',@HasInsert output；可参考：http://www.cnblogs.com/RascallySnake/archive/2010/05/20/1739839.html和http://www.cnblogs.com/RascallySnake/archive/2010/05/20/1739839.html

可以借用临时表：create table #rdCount (rsCount int)

set @sql='declare @count int;selcet @count=count(*) from TblStudent ;insert into #rdCount values(@count);'

exec(@sql)

select * from #rdCount

set statistics time on –SQL语句执行时间。