sqlserver语句学习笔记.docx

sqlserver语句学习笔记.docx

《sqlserver语句学习笔记.docx》由会员分享，可在线阅读，更多相关《sqlserver语句学习笔记.docx（143页珍藏版）》请在冰豆网上搜索。

sqlserver语句学习笔记

目录

--准备----------------3

--基本sql语句---------------4

--any\all----------5

--临时表-------------------6

--constraint（约束）-----------------7

--rule（规则）---------------------7

--rollup同cube的区别-----------------------8

--inner\left\right\full\cross连接-------11

--自连接-------------------14

--casewhen----------------14

--union-----------------------16

--innerjoin----------------17

--exists-----------------------17

--except和intersect---------------------18

--位运算符------------------18

--like\通配符---------20

--if...begin...end-----------------------20

--while循环\break-----------------------20

--waitfor------------------22

--goto语句-----------------------22

--trycatch块-----------------------22

--几个函数------------23

--行转列函数pivot（）----------23

--列转行函数unpivot（）-----27

--row_number（）函数\partitionby子句----29

--rand（）函数\newid（）函数---------29

--几个字符串函数---------------------31

--几个日期函数-----------------33

--自定义函数-----------------------34

--变量-----------------35

--事务-------------------36

--游标--------------------37

--存储过程------------39

--视图view-----42

--触发器---------------------42

--dml触发器-----------42

--ddl触发器---------------45

--加密------------------------46

--权限操作-----------------------46

--查看电子邮件服务--------------------46

--同义词------------------------46

--2008四个新增排序函数row_number\rank\dense_rank\ntile----------------------47

--一道作业----------------48

end50

--触发器学习笔记--------------------51

1.触发器基础51

1.1触发器简介51

1.2DML触发器的分类52

1.3DML触发器的工作原理52

1.3.1After触发器的工作原理53

1.3.2InsteadOf触发器的工作原理53

1.4设计DML触发器的注意事项及技巧53

1.4.1设计触发器的限制53

1.4.2如何在触发器取得字段修改前和修改后的数据54

1.4.3其他注意事项54

1.5设计After触发器54

1.5.2测试触发器功能57

1.5.3建立触发器的SQL语句58

1.6设置After触发器的激活顺序59

1.7触发器的嵌套61

1.8触发器的递归63

1.9设计InsteadOf触发器63

1.9.1InsteadOf触发器的使用范围64

1.9.2设计简单的InsteadOf触发器64

1.10查看DML触发器64

1.10.2用系统存储过程查看触发器65

1.10.2.1sp_help：

65

1.10.2.2sp_helptext：

66

1.11修改DML触发器66

1.12删除DML触发器67

1.13禁用与启用DML触发器67

1.142005新增功能：

DDL触发器67

1.152005新增功能：

设计DDL触发器67

1.15.1建立DDL触发器的语句67

1.162005新增功能：

查看与修改DDL触发器71

1.17触发器的应用技巧72

1.17.1如何知道触发器修改了多少条记录72

1.17.2如何知道插入记录的自动编号是多少73

1.17.3如何知道某个字段是否被修改73

1.17.4如何返回错误信息74

1.18小结75

--搜索指定数据在哪个对象中存在-----75

--行列转换的一个例子------79

--游标详解------------79

------eend83

返回目录83

注意:

在向sqlserver2008拷贝代码时间注意空格\缩进,可能会造成语法错误,退格重打就好了.

--准备----------------

数据库students:

表student_info:

表score_info:

表class_info:

表teacher_info:

返回目录

--基本sql语句---------------

usedb1

createtablescore_info

（

scr_idvarchar

（2）primarykey,

scr_namvarchar（30）,

scr_valueint,

stu_idvarchar（20）

）

--在数据库db1中创建表score_info

select*intossfromstudent_infowhere1=2

--拷贝表student_info的字段到新建表ss

selectstu_folk,count（stu_folk）

fromstudent_infogroupbystu_folk

--统计不同民族的同学人数

selectstu_folkas民族,count（stu_folk）as人数

fromstudent_infogroupbystu_folk

--统计不同民族的同学人数,并为获取集的每一列定义名称

selectstu_sex,count（stu_sex）as人数

fromstudent_infogroupbystu_sex

--按性别统计人数

selectstu_sex,SUM（stu_age）/count（stu_sex）as平均

fromstudent_infogroupbystu_sex

--按性别统计平均年龄

select*fromstudent_infoorderby5

--orderby第五列

--any\all----------

select*fromstudent_info

wherestu_age

--年龄小于任何一个即满足（小于最大的就满足）,等效如下

select*fromstudent_info

wherestu_age

select*fromstudent_info

wherestu_age

--年龄小于每一个才满足（小于最小的才满足）,等效如下

select*fromstudent_info

wherestu_age

sp_helpdb

--显示系统数据库信息

alterdatabasetestDBmodifyname=test_db

--更改数据库testDB的名称为test_db

alterdatabasetest_dbmodifyname=test01

--更改数据库test_db的名称为test01

selectDATABASEPROPERTYEX（'db01','version'）

-------------------------

selectDATABASEPROPERTYEX（'db01','Recovery'）

-------------------------

createdatabaseNorthwind_snap

On（name=Northwind,filename=

'F:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\Northwind000.mdf'）

assnapshotofNorthwind

-------------------------

格式:

createdatabasedatabase_snapshot_name

on

（name=logic_snapshot_name,

filename='os_file_name.mdf'）

assnapshotofsource_database_name

-------------------------

createdatabaseschool_new_s

on

（name=school,---数据库=>属性=>文件=>逻辑名称

filename=

'F:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\school_n_s.mdf'）

assnapshotofschool_new

消息5014，级别16，状态3，第1行

数据库'school_new'中不存在文件'school_new'。

-------------------------

createdatabasetest01_s

on

（name=testDB,

filename=

'F:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\test01_n_s.mdf'）

assnapshotoftest01

-------------------------

restoredatabasetest01

fromdatabase_snapshot='test01_s'

-------------------------

注意:

char（10）和varchar（10）的区别

char（10）和nchar（10）的区别

返回目录

--临时表-------------------

--局部表

createtable#temp0

（

idint,

namenvarchar（10）

）

--创建一个（局部）临时表

insert#temp0values（11,'aa'）

--向临时表插入值

select*from#temp0

--访问临时表

--全局表

Sqlquery1:

createtable##temp00

（

idintnotnull,

namenvarchar（20）,

sexint

）

--创建一个全局临时表

insert##temp00values（22,'aaa',1）

--向全局临时表中插入数据

Sqlquery2:

select*from##temp00

--新建另一个查询,访问全局临时表

-----------------------

createtablestu1

（

idintidentity（12,2）,

namenvarchar（20）,

ageintdefault18

）

--创建一个表

altertablestudent_info

add身高intnull

--向表student_info添加一个新的列身高,数据类型为型int,可空

altertablestudent_info

altercolumn身高float

--在表student_info中修改列’身高’的数据类型为float型

altertablestudent_info

dropcolumn身高

--从表student_info中删除’身高’列

返回目录

--constraint（约束）-----------------

返回目录

--rule（规则）---------------------

createrulebetween_0_100

as

@xbetween0and100

--创建一个规则between_0_100,要求:

between0and100

--规则类似于约束,但:

一个列可以有多个约束,一个约束只能应用到一个列;

--一个规则可以应用到多个列,一个列只能有一个规则.

sp_bindrulebetween_0_100,'stu1.score'

--在stu1表的score'列上应用规则between_0_100

sp_bindrulebetween_0_100,'stu1.age'

--在'stu1表的age列上应用规则between_0_100

sp_unbindrule'stu1.age'

--在某一列上取消规则

sp_unbindrule'stu1.score'

--在某一列上取消规则

droprulebetween_0_100

--删除某个规则

select

sc.stu_id,

sc.stu_name

from

score_infoass

innerjoin

student_infoassc

on

sc.stu_id=s.stu_id

--上下两句效果相同

select

sc.stu_id,

sc.stu_name

from

score_infoass,student_infoassc

wheresc.stu_id=s.stu_id

-----------------------

select12*11

--132

select2*PI（）

--6.28318530717959

返回目录

--rollup同cube的区别-----------------------

--rollup:

生成包含小计和合计的报表

-------class_info表----------

selectc_depart,c_name,SUM（c_count）

fromclass_info

groupbyc_depart,c_namewithrollup

--按专业统计人数

selectc_collage,c_depart,COUNT（c_depart）

fromclass_infogroupbyc_collage,c_departwithrollup

--统计专业班级数,及每个系的班级数

select*fromA

selectid,name,sum（stock）

fromA

groupbyid,namewithrollup

--按id进行合计

selectid,name,sum（stock）

fromA

groupbyid,namewithcube

--按groupby后的每一项进出合计

selecttop3withties*fromscore_infoorderbyyuwendesc

--按yuwen成绩降序排列选出前三行,如果有多个同学并列第三,则全部列出（withties的作用）

declare@iint

set@i=20

selecttop（@i）percent*fromstudent_info

--列出前20%的同学（向上取整:

如若20%为3.1个人,则列出四个）

declare@iint

set@i=20

selecttop（@i+20）percent*fromstudent_info

--40%

declare@iint

set@i=20

selecttop（@i+20）percentwithties*fromstudent_infoorderbystu_age

--列出末位的平局

updatetop

（2）student_infosetstu_home='河南郑州'

---更新表student_info最上面两行

selectstu_name,stu_sex,stu_agefromstudent_infoorderbystu_sex

computeavg（stu_age）bystu_sex

--将显示四个表

--

select*fromstudent_infowherestu_age%2=1

--年龄除2的余等于1

select*fromstudent_infowherestu_age%3>=1

--年龄除3的余大于等于1（不等于0）

select*fromstudent_infowherestu_age%3!

=0

--年龄除3的余不等于0

select*fromstudent_infowherestu_age%3<>0

--年龄除3的余不等于0

altertablestudent_info

dropcolumnstu_class

--在一个表中删除一个列

返回目录

--inner\left\right\full\cross连接-------

--innerjoin时,两表数据均按条件筛选;

--leftjoin时,左侧的表数据全选;rightjoin时,右侧的表数据全选

--fulljoin\crossjoin时,两表数据均全选

--innerjoin----------------

selects.stu_name,c.c_name

fromstudent_infoassinnerjoinclass_infoasc

ons.stu_class=c.c_id

--按条件进出内连接,在两个表中各选若干列组成一个新的数据集（表）

selects.stu_id,s.stu_name,s.stu_class,t.t_name,t.t_class_id

fromstudent_infosinnerjointeacher_infot

ons.stu_class=t.t_class_id

--as关键字可以省略

selects.stu_name,c.c_name,s.stu_sex

fromstudent_infoassinnerjoinclass_infoasc

ons.stu_class=c.c_idands.stu_sex='女'

--把s.stu_sex='女'作为连接条件

--下句中把s.stu_sex='女'作为限定条件,效果相同

selects.stu_name,c.c_name,s.stu_sex

fromstudent_infoassinnerjoinclass_infoasc

ons.stu_class=c.c_id

wheres.stu_sex='女'

-----------------------

selects.stu_name,c.c_name,s.stu_sex

fromstudent_infoassjoinclass_infoasc

ons.stu_class=c.c_id

wheres.stu_sex='女'

--innerjoin时,inner可以省略

selects.stu_name,c.c_name,s.stu_sex

fromstudent_infoassjoinclass_infoasc

ons.stu_class=c.c_id

wheres.stu_sex='女'

--leftjoin-----------------

selects.stu_id,s.stu_name,s.stu_class,t.t_name,t.t_class_id

fromstudent_infosleftouterjointeacher_infot

ons.stu_class=t.t_class_id

--leftjoin-----------------------

selects.stu_id,s.stu_name,s.stu_sex,s.stu_class,t.t_name,t.t_class_id

fromstudent_infosleftouterjointeacher_infot

ons.stu_class=t.t_class_idands.stu_sex='男'

注意上句同下面一句存在严重区别!

!

!

区别:

在上句中,ands.stu_sex='男'这个条件等于没有,因为leftouterjoin已经决定了选择s表中的所有值;

而在下一句中,wheres.stu_sex='男'这个条件,是对连接后的集合进行条件限定,

selects.stu_id,s.stu_name,s.stu_sex,s.stu_class,t.t_name,t.t_class_id

fromstudent_infosleftouterjointeacher_infot

ons.stu_class=t.t_class_id

wheres.stu_sex='男'

--以左表为主（全部显示）,把右表符合条件的数据同左表匹配起来,

---rightjoin--------------------

selects.stu_id,s.stu_name,s.st