首页 > 代码库 > Sql Server总结

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. 非空约束
    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_T1WITHSCHEMABINDING As  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语句执行时间。