sql sever笔记.doc

于亚洲SQL SEVER笔记1、数据库操作/*如果数据库存在就删除*/ if exists (select * from sysdatabases where name = 数据库名) drop database 数据库名 /*建立数据库myDt*/create database myDt on primary/*建主要数据文件*/ (name=ya,/*数据库逻辑名*/filename=d:zhou.mdf,/*磁盘上文件名*/size=1,/*分配大小M*/maxsize=3,/*最大大小*/filegrowth=10%/*每次增长大小*/)log on/*建日志文件*/(name=ya1,filename=d:zhou.ldf,size=1,maxsize=3,filegrowth=10%)/*打开数据库myDt*/use myDt/*删除数据库*/drop database yu/*给数据库更名*/exec sp_renamedb yu,yu5/*不能在脱机下运行*/*查看数据库参数*/exec sp_helpdb yu/*查看数据库选项*/exec sp_dboption yu/*在数据库中添加文件*/alter database yu add file/*添加主数据文件和次数据文件*/(name=yu2,filename=d:yu2.mdf,size=5,maxsize=8,filegrowth=2)goalter database yu add log file/*添加日志文件*/(name=yu3,filename=d:yu3.ldf,size=5,maxsize=8,filegrowth=2)/*增加数据库容量*/alter database yumodify file(name=ya,/*用name不用filename*/size=2)/*缩小数据库容量*/1、 设置数据库库为自动收缩，通过AUTO_SHRINK实现2、 收缩整个数据库的容量dbcc shrinkdatabase(yu)3、 收缩指定数据文件语法：dbcc shrinkdatabase ( database_name ,target_percent , NOTRUNCATE | TRUNCATEONLY )参数：1、 database_name是要收缩的数据库名称。2、target_percent是数据库收缩后的数据库文件中所要的剩余可用空间百分比。3、NOTRUNCATE导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。4、TRUNCATEONLY导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行。使用 TRUNCATEONLY 时，忽略 target_percentis。/*数据库创建快照*/create database yub on(name=yu3,filename=d:yu3.sdf)as SNAPSHOT of yu2、表的操作/*如果存在就删除表yu*/ if exists(select 1 from sysobjects where name=yu)drop table yu/*建立表yu*/create table yu( id int not null identity(0,1), /*非空，第一个值为1，以后自动加1*/ name varchar(8) not null, sex varchar(2) not null,score intconstraint idpr primary key (id,name)/*添加主键*/constraint sexun unique(sex)/*添加unque约束*/constraint sexck check(sex=男 or sex=女) /*添加check约束*/constraint sexde default 男 for sex/*添加default约束*/)/*添加约束*/alter table yu add constraint 约束名 primary key (id,name)/*添加主键*/alter table yu add constraint 约束名 unique(sex)/*添加unque约束*/alter table yu add constraint 约束名 check(sex=男 or sex=女) /*添加check约束*/alter table yu add constraint 约束名default 男 for sex/*添加default约束*/alter table yu1 add constraint 约束名foreign key (id) references yu2 (id)/*添加外键约束*/*删除约束*/drop constraint sexun /*删除一列*/drop table yu/*规则*/create rule 名 as sex=男 or sex=女goexec sp_bindrule 名,yu.sexgo列的操作/*增加一列*/alter table yu add paiming int/*修改一列*/alter table yu alter column paiming varchar(5)/*删除一列*/alter table yu drop column paiming数据操作/*插入单条数据*/insert into yu(name,sex,score)values(zsan,男,89)/*插入多条数据*/insert into yu(name,sex,score)select lisi,女,56 unionselect wang,男,88/*删除记录*/delete from yu where id=1/*修改记录*/update yu set score = 100 where id = 13、查询操作/*查询*/select 列名,列名,AVG(列名) from 表名Where 条件(and,or,is null,between and,in,like _ 代表1个 %代表多个)Order by 列名 ASC或者DESC/*按列排序，ASC升序，DESC降序*/Group by 列名 having 条件/*按组排序，与函数一起用如上面AVG(列名)*/*等值内连接*/Select * from student1 inner join student2On student1.id = student2.id Order by student1.id/*不等值内连接*/Select * from student1 inner join student2On student1.id student2.id Order by student1.id/*自然内连接*/Select id,name/*与等值内连接区别为，等值内连接只能用*来查*/from student1 inner join student2On student1.id student2.id Order by student1.id/*外连接*/Select * from student1 left|right|full outer join student2On student1.id = student2.id Order by student1.id4、权限控制/*权限控制,给所有有户查询表yu的权限*/grant select on yu to public/*拒绝给用户授权*/deny select on yu to public5、变量/*定义变量*/declare myBl int /*变量赋值*/set myBl = 101/*显示变量*/select myBl as 列名WAITFOR函数/*3秒后查询*/waitfor delay 00:00:3 select * from yu/*8点查询*/waitfor time 8:00 select * from yu6、视图/*创建视图*/create view yu(stud_id,name,birthday,gender,address,telcode,zipcode,mark)/*表头可有可无，以下情况必须有，当1、列名为表达式或函数时必须有2、视图由多个表连接成3、需为某列启用更合适名字。此时必须与select语名中返回的相对应*/with encryption /*可有可无，加密*/asselect * from stud_infowith check option /*可有可无，表示视图要完成的操作或内容就是select语名指定内容*/*在select中不能使用order by和distinct要排序必须在在视图定义后对视图查询时再进行*/*视图的查询、修改、删除数据使用方法*/Select * from yu /*视图的使用同表，用时相当于表*/*视图的修改数据方法同表的使用方法*/*视图的删除数据方法同表的使用方法*/*视图的插入数据使用方法*/insert into yu(stud_id,name,birthday,gender,address,telcode,zipcode,mark)values(1001,aa,2012-1-1,aaaaa,aaa,aaaa,aaaaa)/*相当于把视图和插入代码结合起来,但是必须没有被定义为非空列不包含在视图里*/*修改视图*/alter view yuwith encryptionasselect * from stud_infowith check option/*用法同创建视图*/*修改视图名称*/exec sp_rename yu,ya/*查看视图定义信息*/exec sp_depends yu /*查看视图参照对象和列*/exec sp_help yu /*查看数据库对象详细信息*/exec sp_helptext yu /*查看视图代码*/7、索引/*创建索引*/create unique clustered index yu on stud_info(stud_id asc)/*nonclustered是非聚集索引*/*升序，desc为降序*/withfillfactor=60/*填充因子，各索引的填充程度为60%*/*查看索引信息*/exec sp_helpindex stud_info/*查看索引使用的空间信息*/exec sp_spaceused/*查看索引属性*/select indexproperty(object_id(stud_info),yu,IsPadIndex)/*查看stud_info表中的yu索引的sPadIndex属性的设置*/*修改索引*/8、存储过程/*创建存储过程*/create procedure zhouasselect * from stud_info/*此处为任意sql语名*/*执行存储过程*/execute zhou/*创建带参的存储过程*/create procedure zzz(nam nvarchar(4)=默认值)with recompile/*可有可无，每次执行都重新编辑*/with encryption /*可有可无，加密*/asselect * from stud_info where name=nam/*执行带参的存储过程*/exec zzz 张源/*创建带返回值的存储过程*/create procedure zz(nam nvarchar(4),id char(10) output)asSelect id=(select stud_id from stud_info where name=nam)/*执行带返回值的存储过程*/Declare iid char(10)exec zz张源,iid outputprint iid9、 触发器10、 事务11、游标/*建立游标*/declare 游标名 cursor for 查询语句for read only for update of column_name_list/游标for后面是一个select语句，必须有from,不能包含compute、into字句/ for read only可有可无，为只读 for update游标结果可修改在之后的of column_name_list位置列出可以被修改的列的清单 declare info intset info=0/*打开游标*/open yuLOOP:/*推进游标*/fetch next from yu into info/next prior first last是游标移动方向，默认是nextif(info40)print info/*判断是否执行成功*/if(fetch_status=0) goto LOOP /fetch_status=0是最后执行的状态，0是成功，-1是fetch语句有错误，或者游标已经在结果集中的最后一行，-2表示提取的行不存在/*删除游标中的数据*/delete from table_name|view_namewhere current of cursor_name/使用删除时，游标必须定义为for update，只能删除当前行, table_name|view_name是要删除的表或试图名/*更新数据*/update