sql sever笔记.docx

sql sever笔记.docx

《sql sever笔记.docx》由会员分享，可在线阅读，更多相关《sql sever笔记.docx（14页珍藏版）》请在冰点文库上搜索。

sqlsever笔记

1、数据库操作

/*如果数据库存在就删除*/

ifexists（select*fromsysdatabaseswherename='数据库名'）dropdatabase数据库名

/*建立数据库myDt*/

createdatabasemyDt

onprimary/*建主要数据文件*/

（

name=ya,/*数据库逻辑名*/

filename='d:

\zhou.mdf',/*磁盘上文件名*/

size=1,/*分配大小M*/

maxsize=3,/*最大大小*/

filegrowth=10%/*每次增长大小*/

）

logon/*建日志文件*/

（

name=ya1,

filename='d:

\zhou.ldf',

size=1,

maxsize=3,

filegrowth=10%

）

/*打开数据库myDt*/

usemyDt

/*删除数据库*/

dropdatabaseyu

/*给数据库更名*/

execsp_renamedb'yu','yu5'/*不能在脱机下运行*/

/*查看数据库参数*/

execsp_helpdb'yu'

/*查看数据库选项*/

execsp_dboption'yu'

/*在数据库中添加文件*/

alterdatabaseyu

addfile/*添加主数据文件和次数据文件*/

（

name=yu2,

filename='d:

\yu2.mdf',

size=5,

maxsize=8,

filegrowth=2

）

go

alterdatabaseyu

addlogfile/*添加日志文件*/

（

name=yu3,

filename='d:

\yu3.ldf',

size=5,

maxsize=8,

filegrowth=2

）

/*增加数据库容量*/

alterdatabaseyu

modifyfile

（

name=ya,/*用name不用filename*/

size=2

）

/*缩小数据库容量*/

1、设置数据库库为自动收缩，通过AUTO_SHRINK实现

2、收缩整个数据库的容量

dbccshrinkdatabase（'yu'）

3、收缩指定数据文件

语法：

dbccshrinkdatabase

（database_name[,target_percent]

[,{NOTRUNCATE|TRUNCATEONLY}]

）

参数：

1、database_name是要收缩的数据库名称。

2、target_percent是数据库收缩后的数据库文件中所要的剩余可用空间百分比。

3、NOTRUNCATE导致在数据库文件中保留所释放的文件空间。

如果未指定，将所释放的文件空间释放给操作系统。

4、TRUNCATEONLY导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。

不试图重新定位未分配页的行。

使用TRUNCATEONLY时，忽略target_percentis。

/*数据库创建快照*/

createdatabaseyubon

（

name=yu3,

filename='d:

\yu3.sdf'

）

asSNAPSHOTofyu

2、表的操作

/*如果存在就删除表yu*/

ifexists（select1fromsysobjectswherename='yu'）droptableyu

/*建立表yu*/

createtableyu（

idintnotnullidentity（0,1）,/*非空，第一个值为1，以后自动加1*/

namevarchar（8）notnull,

sexvarchar

（2）notnull,

scoreint

constraintidprprimarykey（id,name）/*添加主键*/

constraintsexununique（sex）/*添加unque约束*/

constraintsexckcheck（sex='男'orsex='女'）/*添加check约束*/

constraintsexdedefault'男'forsex/*添加default约束*/

）

/*添加约束*/

altertableyuaddconstraint约束名primarykey（id,name）/*添加主键*/

altertableyuaddconstraint约束名unique（sex）/*添加unque约束*/

altertableyuaddconstraint约束名check（sex='男'orsex='女'）/*添加check约束*/

altertableyuaddconstraint约束名default'男'forsex/*添加default约束*/

altertableyu1addconstraint约束名foreignkey（id）referencesyu2（id）/*添加外键约束*/

/*删除约束*/

dropconstraintsexun

/*删除一列*/

droptableyu

/*规则*/

createrule名as@sex='男'or@sex='女'

go

execsp_bindrule名,'yu.sex'

go

列的操作

/*增加一列*/

altertableyuaddpaimingint

/*修改一列*/

altertableyualtercolumnpaimingvarchar（5）

/*删除一列*/

altertableyudropcolumnpaiming

数据操作

/*插入单条数据*/

insertintoyu（name,sex,score）values（'zsan','男',89）

/*插入多条数据*/

insertintoyu（name,sex,score）

select'lisi','女',56union

select'wang','男',88

/*删除记录*/

deletefromyuwhereid=1

/*修改记录*/

updateyusetscore=100whereid=1

3、查询操作

/*查询*/

select列名,列名,AVG（列名）from表名

Where条件（and,or,isnull,betweenand,in,like_代表1个%代表多个）

Orderby列名ASC或者DESC/*按列排序，ASC升序，DESC降序*/

Groupby列名having条件/*按组排序，与函数一起用如上面AVG（列名）*/

/*等值内连接*/

Select*

fromstudent1innerjoinstudent2

Onstudent1.id=student2.id

Orderbystudent1.id

/*不等值内连接*/

Select*

fromstudent1innerjoinstudent2

Onstudent1.id>student2.id

Orderbystudent1.id

/*自然内连接*/

Selectid,name/*与等值内连接区别为，等值内连接只能用*来查*/

fromstudent1innerjoinstudent2

Onstudent1.id>student2.id

Orderbystudent1.id

/*外连接*/

Select*

fromstudent1left|right|fullouterjoinstudent2

Onstudent1.id=student2.id

Orderbystudent1.id

4、权限控制

/*权限控制,给所有有户查询表yu的权限*/

grantselectonyutopublic

/*拒绝给用户授权*/

denyselectonyutopublic

5、变量

/*定义变量*/

declare@myBlint

/*变量赋值*/

set@myBl=101

/*显示变量*/

select@myBlas'列名'

WAITFOR函数

/*3秒后查询*/

waitfordelay'00:

00:

3'select*fromyu

/*8点查询*/

waitfortime'8:

00'select*fromyu

6、视图

/*创建视图*/

createviewyu（stud_id,name,birthday,gender,address,telcode,zipcode,mark）

/*表头可有可无，以下情况必须有，当1、列名为表达式或函数时必须有2、视图由多个表连接成3、需为某列启用更合适名字。

此时必须与select语名中返回的相对应*/

withencryption/*可有可无，加密*/

as

select*fromstud_info

withcheckoption/*可有可无，表示视图要完成的操作或内容就是select语名指定内容*/

/*在select中不能使用orderby和distinct要排序必须在在视图定义后对视图查询时再进行*/

/*视图的查询、修改、删除数据使用方法*/

Select*fromyu/*视图的使用同表，用时相当于表*/

/*视图的修改数据方法同表的使用方法*/

/*视图的删除数据方法同表的使用方法*/

/*视图的插入数据使用方法*/

insertintoyu（stud_id,name,birthday,gender,address,telcode,zipcode,mark）values

（'1001','aa','2012-1-1','aaaaa','aaa','aaaa','aaaaa'）

/*相当于把视图和插入代码结合起来,但是必须没有被定义为非空列不包含在视图里*/

/*修改视图*/

alterviewyu

withencryption

as

select*fromstud_info

withcheckoption

/*用法同创建视图*/

/*修改视图名称*/

execsp_rename'yu','ya'

/*查看视图定义信息*/

execsp_dependsyu/*查看视图参照对象和列*/

execsp_helpyu/*查看数据库对象详细信息*/

execsp_helptextyu/*查看视图代码*/

7、索引

/*创建索引*/

createuniqueclusteredindexyuonstud_info（stud_idasc）

/*nonclustered是非聚集索引*/

/*升序，desc为降序*/

with

fillfactor=60/*填充因子，各索引的填充程度为60%*/

/*查看索引信息*/

execsp_helpindexstud_info

/*查看索引使用的空间信息*/

execsp_spaceused

/*查看索引属性*/

selectindexproperty（object_id（'stud_info'）,'yu','IsPadIndex'）

/*查看stud_info表中的yu索引的sPadIndex属性的设置*/

/*修改索引*/

8、存储过程

/*创建存储过程*/

createproc[edure]zhou

as

select*fromstud_info/*此处为任意sql语名*/

/*执行存储过程*/

exec[ute]zhou

/*创建带参的存储过程*/

createprocedurezzz（@namnvarchar（4）='默认值'）

withrecompile/*可有可无，每次执行都重新编辑*/

withencryption/*可有可无，加密*/

as

select*fromstud_infowherename=@nam

/*执行带参的存储过程*/

execzzz'张源'

/*创建带返回值的存储过程*/

createprocedurezz（@namnvarchar（4）,@idchar（10）output）

as

Select@id=（selectstud_idfromstud_infowherename=@nam）

/*执行带返回值的存储过程*/

Declare@iidchar（10）

execzz'张源',@iidoutput

print@iid

9、触发器

10、事务

11、游标

/*建立游标*/

declare游标名cursorfor查询语句

forreadonlyforupdateofcolumn_name_list

//游标for后面是一个select语句，必须有from,不能包含compute、into字句

//forreadonly可有可无，为只读forupdate游标结果可修改在之后的ofcolumn_name_list位置列出可以被修改的列的清单

declare@infoint

set@info=0

/*打开游标*/

openyu

LOOP:

/*推进游标*/

fetchnextfromyuinto@info

//nextpriorfirstlast是游标移动方向，默认是next

if（@info>40）print@info

/*判断是否执行成功*/

if（@@fetch_status=0）gotoLOOP

//@@fetch_status=0是最后执行的状态，0是成功，-1是fetch语句有错误，或者游标已经在结果集中的最后一行，-2表示提取的行不存在

/*删除游标中的数据*/

deletefromtable_name|view_name

wherecurrentofcursor_name

//使用删除时，游标必须定义为forupdate，只能删除当前行,table_name|view_name是要删除的表或试图名

/*更新数据*/

updatetable_name|view_name

settable_name.id=””

wherecurrentofcursor_name

/*关闭游标*/

closeyu

/*释放游标*/

deallocateyu

/*内外连代码*/

createdatabaseyu

useyu

createtableya（

idint,

namevarchar（10）

）

createtablezhou（

idint,

namevarchar（10）

）

insertintoya（id,name）

select11,'zsan'union

select12,'lisi'

insertintozhou（id,name）

select11,'zsan'union

select13,'wang'

select*fromya

select*fromzhou

select*

fromyainnerjoinzhou

onya.id

select*

fromyainnerjoinzhou

onya.id=zhou.id

selectya.id,ya.name

fromyainnerjoinzhou

onya.id=zhou.id

select*

fromyaleftouterjoinzhou

onya.id=zhou.id

select*

fromyafullouterjoinzhou

onya.id=zhou.id