-------------------------------------------Review First---------------------------------------- ------------------数据库、表、索引、视图基本操作---------------------------- ------------------创建数据库------------------- create database Rev_1 on primary ( name='Rev_1', filename='D:\Rev_1.mdf', size=5MB, maxsize=unlimited, filegrowth=10% ) log on ( name='Rev_log', filename='D:\Rev_1.l.ldf', size=3mb, maxsize=unlimited, filegrowth=10% )--命令已成功完成。 -----------------查看数据库 ------------------- sp_helpdb master --master 6.50 MB sa 1 04 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 90 -----------------修改数据库------------------- --用modify修改数据库时，每次只能修改一个属性 alter database Rev_1 alter database name='Rev_2'--无法用modify命令和以上命令修改数据库名字 sp_renamedb 'Rev_1','Rev_2' --数据库 名称 'Rev_2' 已设置。 alter database Rev_1 modify file ( name ='Rev_1', size=3Mb--MODIFY FILE 失败。指定的大小小于当前大小。 ) alter database Rev_1 --modify file --add file (...)--增加数据文件 --add log file(...)--增加日志文件 --remove file file_name --不能删除主数据文件，只能用该命令删除辅助数据文件 --remove filegroup fg --文件组'fg' 已删除。 --add filegroup fg filegroup_name ( name ='Rev_1', size=6Mb )--还可以用修改其他属性 alter database Rev_1 -------------------删除数据库------------------- drop database Rev_1 --无法删除数据库 "Rev_1"，因为该数据库当前正在使用。 -------------------创建表------------------- create table Rev_t1 ( rid int primary key identity(1,1) not null, rname varchar(20) null constraint rdft default '张三' , rage int null constraint rck check (rage>=18 and rage <=100), rphone varchar(30) null constraint rck_1 check (rphone like '1%') ) -------------------为表增加记录------------------- insert into Rev_t1 (rage,rphone) values(18,'123123') -------------------查看表记录------------------- select * from Rev_t1 select rname from Rev_t1 -------------------删除表记录------------------- delete from Rev_t1 where --rid=? --rname=? --rage=? -------------------查看表结构------------------- sp_help Rev_t1 --查看表属性 -------------------修改表结构：增加列------------------- alter table Rev_t1 add --column 注意：增加列时不需要column关键字 rdate smalldatetime null constraint dft_rdate default getdate() -------------------修改表结构：删除列------------------- --删除列的默认值约束 alter table Rev_t1 drop constraint dft_rdate,rdft --删除列的check约束 alter table Rev_t1 drop constraint rck,rck_1 -------------------修改表结构：修改列------------------- alter table Rev_t1 alter column rname varchar(40) null -------------------修改表结构：删除表------------------- drop table Rev_t1 ------------------- 索引，视图------------------- ----------------索引---------------- --创建索引-- create index in_Rev_t1 on Rev_t1(rid,rname,rage,rphone,rdate) --创建唯一聚集索引-- create unique clustered index in_Rev_t2 on in_Rev_t1(rid,rname) --查看索引-- sp_helpindex in_Rev_t1 --使用索引-- select rname from Rev_t1 with (index(in_Rev_t1)) --注意：使用Index查询时，不是直接查询index，而是从表中查找数据， --用with附带index以及字段。 --管理索引 --重命名 sp_rename 'in_Rev_t1','in_Rev_t2'--不能通过该命令重命名索引 sp_rename 'Rev_t1.in_Rev_t1','in_Rev_t2','index'--原索引名前必须加表名 --重建索引 alter index in_Rev_t1 on Rev_t1 rebuild --删除索引 drop index in_Rev_t1 on Rev_t1 ----------------视图---------------- --创建视图-- create view vw_Rev_t1 with encryption,schemabinding --加密，绑定 as select rid,rname,rage,rphone,rdate from dbo.Rev_t1 --select rid,rname,rage,rphone,rdate from Rev_t1 无法将 视图'vw_Rev_t1' 绑定到架构，因为名称 'Rev_t1' 对于架构绑定无效。名称必须由两部分构成，并且对象不能引用自身。 --select * from Rev_t1 在绑定到架构的对象中不允许使用语法 '*'。 --查看视图属性 sp_help 'vw_Rev_t1' sp_helptext 'vw_Rev_t1' --修改视图-- --重命名 sp_rename 'vw_Rev_t1','vw_Rev_t2' --警告: 更改对象名的任一部分都可能会破坏脚本和存储过程。 --修改视图属性 alter view vw_Rev_t1 as select * from dbo.Rev_t1 --使用视图-- select * from vw_Rev_t1 --使用视图查询和使用索引查询是不一样的 --删除视图-- drop view vw_Rev_t1 ----------------函数、自定义类型、声明局部变量、多表连接查询---------------- --T-SQL中提供了4中类型的函数：1.行集函数2.聚合函数3.标量函数4.排名函数 --------函数-------- ----聚合函数---- ----Rollup cube---- select username from userinfo group by username with rollup select userage from userinfo group by userage with cube --AVG-SUM-- --创建xs表，并对其操作-- create table xs ( 学号 char(6) not null, 姓名 char(8) not null, 专业名 char (10) not null, 性别 bit not null, 出生时间 smalldatetime not null, 总学分 tinyint not null, 备注 text null, 入学日期 datetime constraint dateflt default getdate() ) alter table xs modify datetime constraint datedflt default getdate() alter table xs modify [datetime ] column default getdate() alter table xs drop constraint dateflt alter table xs add addDate smalldatetime null constraint addDateDflt default getdate() with values insert into xs ( 学号 , 姓名 , 性别 , 总学分 , 备注)values(10,'aa','true',100,'a') ------------------创建xs_kc表------------------- create table xs_kc ( 学号 char(6) null, 姓名 char(8) null, 课程 char(10) null ) select * from xs_temp select avg(distinct 总学分) as avg, sum=sum(all 总学分) from xs_temp --MIN - MAX-- select max(distinct 总学分) as 无重复最大值,全部最小值=min(all 总学分) from xs_temp select * from xs select 平均分=avg(总学分),总分=sum(总学分) from xs select min=min(distinct 总学分),max = max(distinct 总学分) from xs update xs set 总学分=100 where 姓名='wer' or 姓名='sadf' --COUNT-- select 所有列和=count(all *),无重复列和=count(distinct *) from xs_temp--'*' 附近有语法错误。 select 所有列和=count(all),无重复列和=count(distinct) from xs_temp--')' 附近有语法错误。 select 所有列和=count(all 总学分),无重复列和=count(distinct 总学分) from xs_temp ----字符串函数---- select str(123.456,10,5)--123.45600 select str(123.456,3,0)--123 select str(123.456,3,2) --123 --中间的参数控制总长度，第三个参数控制小数位数 --LEFT-RIGHT-- select left('123456',5)--12345 select right('123456',5)--23456 select left('just do it',6) select right ('live now',5) --LEN-- select len('123456') select len ('just do it and live now') select len(select 姓名 from xs_temp where 姓名='sadf')--提示语法错误 --REVERSE-- select reverse('123456')--654321 select reverse ('just do it and live now') --LOWER - UPPER-- select lower('LOWER') select upper('upper') select lower('JUST') select upper('just') --LTRIM RTRIM-- select ltrim(' ab cde ') select rtrim(' ab cde ') select ltrim('just ') select rtrim(' just ') --SQL中没有trim内置函数，也就是没有直接去掉所有空格的函数 --substring-- select substring('just do it ',4,3) --charindex-- select charindex=charindex('i','just do it') --as 'charindex' --nchar-- select nchar('43') --ascii-- select ascii('4') --replace-- select replace('just_do_it_and_ ',' ','live_now') ----数学函数---- --ABS-- select abs(-123456)--取绝对值 --PI-- select PI() --返回π的常量值 --FLOOR-- select floor(123.456)--取不大于123.456的最大整数 --SIN COS TAN COT-- select SIN(PI()) select COS(PI()) select TAN(PI()) select COT(PI()) --SQUARE POWER-- select square(3)--求平方 select power(4,2)--求4的2次方 ----转换函数---- --CAST-- select cast('123.456' as float) select cast (出生时间 as varchar(30)) as birsday from xs --CONVERT-- select convert(varchar(30),123.456) select convert(varchar(30),出生时间) as birsday from xs select convert(varchar(20),getdate(),23) select convert(varchar(20),getdate(),24) ----日期和时间函数---- --GETDATE-- select getdate()--返回当前毫秒级时间 --DATEADD DATEDIFF-- select dateadd(day,10,getdate())--当前日期加10天 select datediff(month,'2005-7-30',getdate())--2005年8月9号距几天多少月(忽略零散的天数计算) --------创建用户类型-------- --创建用户自定义类型-- create type mytype from varchar(7) --删除用户自定义类型-- drop type mytype --通过系统存储过程创建用户自定义类型-- exec sp_addtype mytype2, 'varchar(8)', 'null' --删除用户自定义类型-- drop type mytype2 --通过系统存储过程删除用户自定义类型-- sp_droptype 'mytype2' --------声明局部变量-------- declare @str varchar(8) ,@int1 int set @str='welcome' select @int1=7 print 'str:'+@str print 'int1:'+str(@int1) select * from xs declare @name varchar(20) set @name='%s%' select * from xs where 姓名 like @name select 姓名='user',学号=1,性别,专业名 from xs declare @mynumber int set @mynumber = 7-1+7/4*7 select @mynumber --------join查询-------- --创建临时表-- select * into xs_temp from xs --内连接-- select * from xs inner join xs_temp on xs.学号=xs_temp.学号 --外连接-- select * from xs a full outer join xs_temp on xs.学号=xs_temp.学号 ----------------创建、管理登录名和用户---------------- ----登录---- --创建登录-- create login s_a with password = '1234' create login [ntc02\ZhouYuan] from windows sp_addlogin s_aaa,'123' --修改登录-- --修改登录名和密码-- alter login s_a with name=s_aa ,password='321' --使登录是否可用-- alter login s_aa disable alter login s_aa enable --查看登录信息-- sp_helplogins s_aa --赋予登录名权限-- sp_addsrvrolemember s_a,'sysadmin' --删除登录-- drop login s_a sp_droplogin s_aa ----用户---- --创建用户-- create user s_a --必须在创建login的前提下创建user create user s_a for login s_a sp_adduser s_aaa --修改用户-- alter user s_a with NAME=s_aa --删除用户-- drop user s_aa sp_dropuser s_aa --赋予用户角色或权限-- sp_addsrvrolemember s_a,'sysadmin' ----------------创建、管理Default、rule、check约束---------------- --实体完整性 --实体完整性将行定义为特定表的唯一实体。实体完整性通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性强制表的标识符列或主键的完整性。 --域完整性 --域完整性指特定列的项的有效性。您可以强制域完整性限制类型（通过使用数据类型）、限制格式（通过使用 CHECK 约束和规则）或限制可能值的范围（通过使用 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则）。 --引用完整性 --引用完整性通过 FOREIGN KEY 和 CHECK 约束 --用户定义完整性 --用户定义完整性使您可以定义不属于其他任何完整性类别的特定业务规则。 ----Default---- --创建表时创建默认值约束-- create table dfttest ( id smallint, proccessid int default @@spid,--@@spid 输出当前用户进程ID insertdate datetime default getdate(), innum smallint default 30*4, inname char(3), flag char(5) default 'hello' ) --单独创建默认值约束-- create default dftname as 'dft' --删除默认值约束-- drop default dftname --为dfttest表的inname列绑定默认值约束-- sp_bindefault dftname,'dfttest.inname' --为dfttest表的inname列解除绑定约束-- sp_unbindefault 'dfttest.inname' --测试约束-- insert into dfttest ( id ) values(1) select * from dfttest ----Rule---- --创建规则-- create rule ru_name as @name like 'dft' --规则名：ru_name 该规则用于约束绑定该规则的列的每一个字段都必须和'dft'相同 --查看规则信息-- sp_help ru_name --创建该规则的文本信息-- sp_helptext ru_name --绑定该规则到表dfttest的列inname上-- sp_bindrule ru_name,'dfttest.inname' --解除表dfttest列inname的规则绑定-- sp_unbindrule 'dfttest.inname' --删除规则-- drop rule ru_name --truncate table truncate table dfttest --删除表中所有行，速度比delete快 ----check---- --创建表时创建check约束-- create table check_test ( id smallint identity(1,1)check (id>0 and id<10), inname char(3), flag char(5) default 'hello', constraint check_name check (inname like '[a][b][c]') ) --测试以上约束-- drop table check_test insert into check_test (inname) values ('abc') --表创建后通过修改表创建check约束-- alter table check_test add constraint check_name check(inname like 'abc%') --通过修改表删除该表的指定的check约束-- alter table check_test drop constraint check_name --使用with nocheck可以在创建该约束时不验证该表现有的记录-- alter table check_test with nocheck add constraint check_name1 check(inname like 'cba') --通过删除该约束在系统中的名字删除该约束-- alter table check_test drop constraint CK__check_test__id__740F363E select * from check_test ----unique约束---- --unique 唯一约束主要用于约束被约束列唯一 --通过修改表的add constraint方法创建唯一约束-- alter table check_test with nocheck add constraint unique_name unique(inname asc); --通过alter drop删除该表的unique约束-- alter table check_test drop constraint unique_name ----除了以上约束之外，还有主键约束，外键约束，可借李伟的SQL2005参考查询 ----------------------------存储过程、自定义函数、RAISERROR------------------------------- -----------------存储过程----------------- --接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 --包含用于在数据库中执行操作（包括调用其他过程）的编程语句。 --向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。 --create table Article-- create table Article ( NickName varchar(30) not null, Title varchar(30) not null, article varchar(max) not null, date smalldatetime null ) --create table Review-- create table Review ( Floor tinyint identity(1,1) not null, NickName varchar(30) not null, Title varchar(30) not null, Reviewor varchar(30) not null, Review varchar(200) not null, date smalldatetime null ) --create table Members-- create table Members ( NickName varchar(30) not null, Password varchar(30) not null, [Name] varchar(30) not null, Age tinyint null, [e-mail] varchar(30) null, Address varchar(100) null ) --create table Administrator-- create table Administrator ( Nickname varchar(30) not null, Password varchar(30) not null, Super bit null ) --创建查看所有表的存储过程up_selectArt_Rew-- create procedure up_selectArt_Rew as select * from article ,review ,members ,administrator --执行存储过程up_selectArt_Rew-- up_selectArt_Rew --创建向Review表中插入记录的存储过程up_insertRew-- create procedure up_insertRew ( @nickname varchar(20), @title varchar(50), @reviewor varchar(20), @review varchar(300) ) as insert into dbo.review ( nickname,title,reviewor,review ) values ( @nickname,@title,@reviewor,@review ) --使用up_insertRew存储过程-- exec up_insertRew @nickname='t',@title='t',@reviewor='t',@review='t' go --删除up_insertRew存储过程-- drop proc up_insertRew --显示该数据库中的所有表-- sys.sp_tables --创建带输出参数output的存储过程select_name-- create procedure select_name ( @article varchar(100) output, @title varchar(40), @name varchar(10) ) as select article from article where title=@title and nickname=@name --使用存储过程select_name-- declare @art varchar(100) exec select_name @art output,@title='first',@name='zhou' print @art --删除存储过程select_name-- drop proc select_name --设置自动执行的存储过程-- create procedure auto_print as print 'first auto print' sp_procoption 'auto_print' ,'startup','true' auto_print --修改存储过程auto_print-- alter procedure auto_print as print 'altered auto print' auto_print --加密存储过程auto_print-- alter procedure auto_print with encryption as print 'altered and encryption auto print' sp_helptext auto_print --对象 'auto_print' 的文本已加密。 --重新编译存储过程auto_print-- alter procedure auto_print with recompile as print 'altered,encryption and recompiled auto print' --删除存储过程auto_print-- if object_id ('auto_print','P') is not null drop procedure auto_print ------------------自定义函数------------------- --与编程语言中的函数类似，Microsoft SQL Server 2005 用户定义函数是接受参数、 --执行操作（例如复杂计算）并将操作结果以值的形式返回的例程。 --返回值可以是单个标量值或结果集。 --标量函数-- --创建自定义函数fc，该函数有一个varchar类型的参数，一个int类型的返回值， --该函数主要用于返回xs_temp表中的以专业名计数的总数，没有的话就返回0 create function fc(@v varchar(10)) returns int --注意返回的是int类型，而不是一个参数 as begin declare @vsum int; select @vsum=count(*) from xs_temp --注意并不是@vsum=select count(*) from xs_temp where 专业名=@v; if (@vsum is null) set @vsum=0 --注意 设置一个参数的值时，要用set return @vsum end --使用自定义函数fc-- declare @f varchar(10) set @f='fe' select dbo.fc(@f) from xs_temp --注意 使用select 使用函数 而不是用exec --内嵌表值函数-- --创建自定义函数f_table，该函数有一个Int类型的参数，一个table类型的返回值 --该函数用于返回xs_temp表中的所有数据 create function f_table(@f int) returns table --注意：在内嵌表值函数里，这里是returns 不是return as return(select * from xs_temp) --删除自定义函数f_table-- drop function f_table select function f_table() --注意:此处自定义函数的使用方法 不是select function f_table() select * from dbo.f_table(3) --多语句表值型函数-- alter function f_table (@f int) returns int table as begin declare @count return (select @count=count(*) from xs_temp) end --该函数为找到解决办法，不能执行 ------------------------RAISERROR实例---------------------- --通过创建存储过程p1测试RAISERROR-- create procedure p1(@stime varchar(30),@etime varchar(30),@vday varchar(30) out) with encryption--加密 as if @stime>@etime --print '开始时间大于结束时间'--注意：有raiserror的地方，只能有它自己一条语句，它比较特殊 raiserror('开始时间大于结束时间',18,2) else select @vday=datediff(day,convert(datetime,@stime,120),convert(datetime,@etime,120)) exec p1 @stime='20090101',--注意 如果此处使用了 '@name = value' 形式之后， @etime='20100101',--所有后续的参数就必须以 '@name = value' 的形式传递。 @vday1 output print @vday1 declare @vday1 varchar(30) exec p1 '20090101', '20100101', @vday1 output print @vday1 --create function-- create function f1(@stime varchar(30),@etime varchar(30)) returns varchar as begin declare @vday varchar(30); if @stime>@etime begin select @vday= @etime--print '开始时间大于结束时间' --raiserror('开始时间大于结束时间',18,2) end else begin select @vday= @stime --(datediff(day,convert(datetime,@stime,120),convert(datetime,@etime,120))) end return @vday end --消息 443，级别 16，状态 14，过程 f1，第 6 行 --在函数内的 'PRINT' 中对带副作用的或依赖于时间的运算符的使用无效。 --消息 443，级别 16，状态 14，过程 f1，第 9 行 --在函数内的 'RAISERROR' 中对带副作用的或依赖于时间的运算符的使用无效。 --测试-- declare @vaday varchar(30),@stime varchar(30),@etime varchar(30) set @stime='20090101' set @etime='20100202' select @vaday=datediff(day,convert(datetime,@stime,120),convert(datetime,@etime,120)) print @vaday ----------------------------DDL、DML触发器、游标------------------------------- -------------DDL、DML触发器--------------- --DDL（Data Definition Language，数据定义语言）： --用于定义数据的结构，比如 创建、修改或者删除数据库对象.主要是以 CREATE、ALTER 和 DROP 开头的语句。 --创建触发器tr_1-- create trigger tr_1 on database --注意：此处不能写成 all server 指定的事件类型对指定的目标对象无效。 for create_table,alter_table,drop_table--注意：此处是create_table 不是create table as --注意：这儿要使用 as ，而不是什么都不写 print '触发禁用表操作' rollback; create table t_2 ( id int ) drop table t_1 --事务在触发器中结束。批处理已中止。 --删除触发器tr_1-- drop trigger tr_1 on all server--无法对 触发器'tr_1' 执行 删除，因为它不存在，或者您没有所需的权限。 drop trigger tr_1 on database--命令已成功完成。 --在服务器上创建触发器tr_1-- create trigger tr_1 on all server for create_database,drop_database ,alter_database--注意：for 写在as 之前 as print '触发器禁止数据库操作' rollback create database db_1 --事务在触发器中结束。批处理已中止。 --在服务器上删除tr_1-- drop trigger tr_1 on all server select * from test --创建禁止创建，删除，修改登陆的触发器以及相关操作-- create trigger tr_1 on database --注意：指定的事件类型对指定的目标对象无效。 不能用database，而要用all server for create_login,drop_login,alter_login as print '触发器禁止登陆操作' rollback create trigger tr_1 on all server for create_login,drop_login,alter_login as print '触发器禁止登陆操作' rollback transaction create login lo_1 with password='1' --事务在触发器中结束。批处理已中止。 drop trigger tr_1 on all server --修改触发器-- alter trigger tr_1 on all server --注意：修改触发器时，不能修改这一部分 for create_database,alter_database,drop_database as print 'Trigger ban such work' rollback drop trigger tr_1 on all server --禁用和解除禁用触发器-- disable trigger tr_1 on all server enable trigger tr_1 on all server ---------------------------DML 触发器-------------------------------- ------------------for,after触发器--------------------- select * from test -------for触发器----------- create trigger tr_1 on test after insert as insert into test(tname,tage) values('ta',12) drop trigger tr_1 insert into test values('ta',13) -------update触发器----------- create trigger tr_1 on test for update as if update(tname)--注意:update(这里面写的是列名) begin --注意：如果 if内有多条语句，那么就需要用begin end print 'test table can not change' rollback transaction end update test set tname='china' where tage=12--事务在触发器中结束。批处理已中止。 drop trigger tr_1 --在视图上创建触发器-- create view v_t as select * from test create trigger tr_1 on v_t for insert -- 不能用for 或者 after 在视图上创建触发器 as if update(tname) begin insert into test values('china',14) end --------instead of触发器--------- create trigger tr_1 on v_t instead of insert as if update(tname) begin insert into test values('china',14) end --命令已成功完成。 insert into v_t values(1,'japan',15) select * from v_t select * from test --结果： --12 china 14 --13 china 14 -- --12 china 14 --13 china 14 drop trigger tr_1 ----------------------cursor-游标------------------- -------游标操作------- declare c_1 cursor for --此处用for 而不是as select * from test --打开游标-- open c_1 --获取数据-- fetch next from c_1 --关闭游标-- close c_1 --释放游标-- deallocate c_1 ----------游标使用实例------------- select * from test --声明变量 declare @ti int ,@tn varchar(10),@ta int --声明游标 declare c_1 cursor for select * from test --打开游标 open c_1 --获取数据 fetch next from c_1 into @ti,@tn,@ta --输出表test的下一行数据的后两列数据 print @tn print @ta --判断下一行数据是否存在 -- 0 FETCH 语句成功。 -- -1 FETCH 语句失败或行不在结果集中。 -- -2 提取的行不存在。 while @@fetch_status = 0 begin if @tn='SQL' update test set tname='china' ,tage=15 fetch next from c_1 into @ti,@tn,@ta print @tn print @ta end if @@fetch_status <>0 print '执行完毕' close c_1 deallocate c_1 --------创建存储过程并在其中使用游标的实例-------- create procedure p_1 as declare c_1 cursor for select * from test open c_1 fetch next from c_1 while @@fetch_status =0 begin update test set tname='japan',tage=154 fetch next from c_1 end if @@fetch_status <>0 print 'Done' close c_1 deallocate c_1 p_1