sql 语句
/*--------------------------------------SQL语法复习知识-----------------------------------------------*/
use master
go
--检查是否存在,存在删除
if exists (select * from sysdatabases where name='ReviewDatabase')
drop database databaseName
go
--创建数据库
create database ReviewDatabase--数据库名为ReviewDatabase(对象资源管理器显示的名称)
on
(
name='ReviewDatabase',--文件在电脑上显示的文件名（物理名）
filename='F:\各类小功能\我的源码\SQL Server\database\ReviewDatabase.mdf',--数据文件保存的位置注意盘符下的文件夹必须事先存在
size=5,--数据库初始大小
maxsize=100,--数据库最大多少
filegrowth=10%--自动增长百分之多少
)
log on--创建对应的日志文件
(
name='ReviewDatabase_log',
filename='F:\各类小功能\我的源码\SQL Server\database\ReviewDatabase_log.ldf',
size=4,
maxsize=50,
filegrowth=10%
)
/*-----------------------------------------------------------------------------------------------------*/
--创建表
use ReviewDatabase
go
--检查是否存在,存在删除
if exists(select * from sys.tables where name='t1')
drop table t1
go
--创建表
create table t1--不要在主表里创建外键
(
id1 int identity(1,1) not null,--从1自增1起(identity(1,1),not null可省,默认为null)
id2 int constraint fk_t1 primary key,--设置为主键(名字可省)
id3 int not null constraint id4_unique unique,--唯一
id4 uniqueidentifier not null constraint t1_newid default(newid()),--newid()随机函数,设置默认值
tel int not null constraint ch_t1_tel check(tel like '7809[0-9][0-9][1-9]'),
name varchar(10) not null,
)
if exists(select * from sys.tables where name='t2')
drop table t2
create table t2
(
id1 int,
id2 int not null constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade on delete cascade,--主外键级联更新,删除
constraint pk_t2 primary key(id1),
)
/*-----------------------------------------------------------------------------------------------------*/
--插入数据到表
insert t1(id2,id3,tel,name) values(1,1,7809001,'a')
insert t1(id2,id3,tel,name) values(2,2,7809002,'b')
insert t1(id2,id3,tel,name) values(3,3,7809003,'c')
insert t1(id2,id3,tel,name) values(4,4,7809004,'d')
insert t1(id2,id3,tel,name) values(5,5,7809005,'e')
insert t1(id2,id3,tel,name) values(6,6,7809006,'f')
insert t1(id2,id3,tel,name) values(7,7,7809007,'g')
insert t1(id2,id3,tel,name) values(8,8,7809008,'h')
insert t1(id2,id3,tel,name) values(9,9,7809009,'i')
insert t2(id1,id2) values(1,1)
insert t2(id1,id2) values(2,2)
insert t2(id1,id2) values(3,3)
insert t2(id1,id2) values(4,4)
insert t2(id1,id2) values(5,5)
insert t2(id1,id2) values(6,6)
insert t2(id1,id2) values(7,7)
insert t2(id1,id2) values(8,8)
insert t2(id1,id2) values(9,9)
insert into t1(id2,id3,tel,name) values(10,10,7809011,'j')
insert into t2(id1,id2) values(10,10)
select * into #temp from t2--将t2复制到临时表
select * from #temp--本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的,当用户从 SQL Server 实例断开连接时被删除
insert into t3 select * from #temp--把临时表中的数据插入到另一个表中
select * from t1,t2 where t1.id2=t2.id2
update t2 set id2=10 where id1=1--出错,外键冲突
update t1 set id2=10 where id1=1--正确,t2.id2也会更改
/*-----------------------------------------------------------------------------------------------------*/
alter table t2 add id3 int null--添加一列
alter table t2 add constraint pk_t2_id3 primary key(id3)--t2添加一个主键
alter table t2 drop pk_t2_id3--t2主键删除
alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2)--添加一个外键
alter table t2 drop fk_t1_t2--删除外键
alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade--级联更新
alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on delete cascade--级联更新
alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade on delete cascade--级联更新和删除
alter table t2 add constraint un_t2_id3 unique(id3)--添加唯一列,非聚集
alter table t2 drop un_t2_id3--删除唯一列,非聚集
alter table t2 add constraint un_t2_id3 unique clustered(id3)--添加唯一聚集
alter table t2 drop constraint un_t2_id3
alter table t1 add constraint de_t1 default '默认值' for name--添加默认值
alter table t1 drop constraint de_t1
alter table t2 with nocheck add constraint ch_t2 check (id3 like '[1-9]')--添加check
alter table t2 drop constraint de_t2
alter table t2 nocheck constraint ch_t2--设置check无效
alter table t2 check constraint ch_t2--设置check重新有效
/*-----------------------------------------------------------------------------------------------------*/
--索引是不可更改的,想更改必须删除重新建
create unique clustered index in_t2 on t2(id1)
drop index in_t2 on t2
/*-----------------------------------------------------------------------------------------------------*/
--常用查询
exec sp_help t1--查看约束
select * from t1 order by id1 asc --order by "asc 升,desc 降"
select distinct * from t1 --消除重复行
select * from t2 where id1='1' --选择where"=,>,<.>=,<=,<>"
insert into t2(id1,id2) values('7','7') --插入insert(t2(内可省,但values要一一对应))
delete from t2 where id1='6' --删除delete
update t1 set name='liuyi' where id1='1' --更新update
--'%'零个和多个任意的字符,'_'任意一个字符,'[]'指定的范围作保单个字符,'[^]不在指定的范围作保单个字符
select * from t2 where id2 not like '%666%' --查找"字符串比较like,not like"
select * from t2 where id1='1' or id1='2' --查找"逻辑运算符 and,or"
select * from t2 where not id1='1' --查找"逻辑运算符 not"
select * from t2 where id1 between '1' and '6' --查找"值的范围 between,not betweent"
select * from t2 where id1 in('1','3','6') --查找"值的列表in ,not in"
select * from t2 where id1 is not null --查找"未知值is null ,not is null"
select t1.id1,t1.name from t1 where exists --指定一个子查询，检测行的存在
(select t1.id2 from t1,t2 where t1.id2=t2.id2 and t2.id1 between 6 and 9)
/*-----------------------------------------------------------------------------------------------------*/
--聚合函数
select count(*) as '总数' from t1 --总数
select sum(id1) as '求和' from t1 --求和
select avg(id1) as '平均' from t1 --平均
select max(id1) as '最大' from t1 --最大
select min(id1) as '最小' from t1 --最小
select id1,name,COUNT(*) from t1 group by id1,name --group by
select id1,name,COUNT(*) from t1 group by id1,name having id1>2 --group by ,having
select id1,name,sum(id1) from t1 group by id1,name with rollup --group by ,with rollup
select id1,name,sum(id1) from t1 group by id1,name with cube --group by ,with cube
--select id,name,sum(id) grouping(id) from s1
select id1 name from t1 order by id1 compute sum(id1) --compute
select id1,name from t1 order by id1 compute sum(id1) by id --compute by
/*-----------------------------------------------------------------------------------------------------*/
--联接
select * from t1 inner join t2 on t1.id2=t2.id2--内联接1
select * from t1,t2 where t1.id2=t2.id2--内联接2
select * from t1 left join t2 on t1.id2=t2.id2--左外联接
select * from t1 right join t2 on t1.id2=t2.id2--右外联接
select * from t1 full join t2 on t1.id2=t2.id2--全外联接
select * from t1 cross join t2--交叉联接1
select * from t1,t2--交叉联接2
--合并多个结果集
select t1.id1,t1.id2 from t1
union
select t2.id1,t2.id2 from t2
/*-----------------------------------------------------------------------------------------------------*/
--事务
begin tran
begin tran
begin tran
print @@trancount--计算事务的个数 对于嵌套的transaction来讲，每次begin transaction都让它加一，每次commit tran都会让它减一
rollback tran--rollback是很特殊的，它会直接把@@TRANCOUNT设置为0
print @@trancount
begin tran
print @@trancount
--嵌套
delete t1 where id1>5--删除数据,
begin tran out1
insert t1(id2,id3,tel,name) values(6,6,7809006,'f')
insert t1(id2,id3,tel,name) values(7,7,7809007,'g')
begin tran inn1
insert t2(id1,id2) values(6,6)
insert t2(id1,id2) values(7,7)
save tran inn1SavePoint
insert t1(id2,id3,tel,name) values(8,8,7809008,'h')
insert t1(id2,id3,tel,name) values(9,9,7809009,'i')
insert t2(id1,id2) values(8,8)
insert t2(id1,id2) values(9,9)
rollback tran inn1SavePoint--8-9之间的回滚了,没有插入
commit tran inn1
commit tran out1--如果没有提交事务会出现死锁
select * from t1,t2 where t1.id2=t2.id2
/*-----------------------------------------------------------------------------------------------------*/
--触发器trigger
disable trigger tri_t1 on database --禁用触发器
enable trigger tri_t1 on database --开启触发器
use ReviewDatabase
go
--1.For
create trigger tri_t1 --修改触发器时,可以将create改为alter,再修改相应语句
on dbo.t1 --可以是视图
--with encryption --表进行加密(可省)
for insert,update,delete --阻止删除时,可以用ROLLBACK回滚
--not for replication --表明当复制处理修改与触发器相关联的表时，触发器不能被执行(可省)
as
select * from t1,t2 where t1.id2=t2.id2
go
drop trigger tri_t1 --删除触发器
--2.after 和for一样 先会检测约束,这里会报错
use ReviewDatabase
go
create trigger tri_t2
on dbo.t2
after insert,update,delete
as
begin
select * from inserted
select * from updated
select * from deleted
end
insert t2(id1,id2) values(9,9)--这里会报错
drop trigger tri_t2
--2.after of --先会执行触发器再检测约束
use ReviewDatabase
go
alter trigger tri_t2_of
on dbo.t2
after insert,update,delete
as
begin
print 'inserted'
select * from inserted
print 'deleted'
select * from deleted
end
delete t2 where id1=9
insert t2(id1,id2) values(9,9)
update t2 set id3=10 where id1=9
select * from t2 where id1=9
/*-----------------------------------------------------------------------------------------------------*/
--视图
use ReviewDatabase
go
create view vi_t2
with encryption
as
select * from t1
go
select * from vi_t2
drop view vi_t2
/*-----------------------------------------------------------------------------------------------------*/
--储存过程procedure
--常用系统存储过程有
exec sp_databases; --查看数据库
exec sp_tables;--查看表
exec sp_columns t2;--查看列
exec sp_helpIndex t1;--查看索引
exec sp_helpConstraint t2;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename t1, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb ReviewDatabase;--查询数据库信息
--表重命名
exec sp_rename 'test_sub', 'stu';
select * from test_sub;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N't1.idx_cid', N'idx_cidd', N'index';
exec sp_help 't1';
--查询所有存储过程
select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
--自定义存储过程
--1、 创建语法
/*
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_语句
*/
--2、 创建不带参数存储过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_t1'))
drop proc proc_get_t1
go
create proc proc_get_t1
as
select * from t1;
--调用、执行存储过程
exec proc_get_t1;
--3、 修改存储过程
--修改存储过程
alter proc proc_get_t1
as
select * from t1;
--4、 带参存储过程
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_t1
go
create proc proc_find_t1(@startId int, @endId int)
as
select * from t1 where id1 between @startId and @endId
go
exec proc_find_t1 2, 4; --不可以打乱顺序
exec proc_find_t1 @startId=2,@endId=4 --可以打乱顺序
--5、 带通配符参数存储过程
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findt1ByName
go
create proc proc_findt1ByName(@name varchar(10) = '%j%')
as
select * from t1 where name like @name;
go
exec proc_findt1ByName;
exec proc_findt1ByName '%a%';
--6、 带输出参数存储过程
if (object_id('proc_gett1Record', 'P') is not null)
drop proc proc_gett1Record
go
create proc proc_gett1Record(
@id1 int, --默认输入参数
@id2 int out, --输出参数
@id3 int output--输入输出参数
)
as
update t1 set id2 = @id2, id3 = @id3 where id1 = @id1;
set @id2=@id2+1
set @id3=@id3+1
go
declare @id1 int=18,@id2 int,@id3 int;
set @id2 = 18;
select @id3 = 18;
exec proc_gett1Record @id1, @id2 out, @id3 output;
select @id2, @id3;
print @id2 + ' ' + @id3;
--7、 不缓存存储过程
--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from t1;
go
exec proc_temp;
--8、 加密存储过程
--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from t1;
go
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
--9、 带游标参数存储过程
if (object_id('proc_cursor', 'P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id1, id2, id3 from t1;
open @cur;
go
--调用
declare @exec_cur cursor;
declare @id1 int,
@id2 int,
@id3 int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id1, @id2, @id3;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id1, @id2, @id3;
print @id1 + @id2 + @id3;
end
close @exec_cur;
deallocate @exec_cur;--删除游标
--10、 分页存储过程
---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
drop proc pro_page
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from t1;
select * from (
select row_number() over(order by id1) as rowId, * from t1
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
drop proc pro_page
go
create procedure pro_page(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1 --求出pageIndex前的行数求出
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id1 asc) as number from t1
) t
where t.number between @startRow and @endRow;
exec pro_page 2, 7;
--Ø Raiserror
--Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。
--语法如下：
--Raiserror({msg_id | msg_str | @local_variable}
-- {, severity, state}
-- [,argument[,…n]]
-- [with option[,…n]]
--)
--# msg_id:在sysmessages系统表中指定的用户定义错误信息
--# msg_str:用户定义的信息，信息最大长度在2047个字符。
--# severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。
--任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。
--# state：介于1至127直接的任何整数。State默认值是1。
--raiserror('is error', 16, 1);
--select * from sys.messages;
--使用sysmessages中定义的消息
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);
/*-----------------------------------------------------------------------------------------------------*/
--函数
--标量函数
use ReviewDatabase
go
if exists(select* from sysobjects where type='fn' and name='fun_t1')
drop function fun_t1
go
create function dbo.fun_t1(@id1 int)
returns int
as
begin
declare @id2 int
select @id2=id3 from t1 where id1=@id1
return @id2
end
go
select dbo.fun_t1(t2.id1+1)as id,t1.name,t1.tel from t1,t2 where t1.id2=t2.id2 and dbo.fun_t1(t2.id1+1)<>''
--内联表值函数
create function fun_t1_2(@id1 int)
returns table
as
return (select * from t1 where id1=@id1)
go
select * from dbo.fun_t1_2(1)
--多语句表值函数
create function fun_t1_3()
returns @t1 table(a int,b int)
as
begin
insert @t1
select id1,id2 from t1
return
end
go
select * from fun_t1_3()
/*-----------------------------------------------------------------------------------------------------*/
--游标
/*
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 全局或局部
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四选一
这四个关键字是游标所在数据集所反应的表内数据和游标读取出的数据的关系
STATIC意味着，当游标被建立时，将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容.
DYNAMIC是和STATIC完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变
KEYSET可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据
FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好.
READ_ONLY SCROLL_LOCKS OPTIMISTIC 三选一
READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作
SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功
OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新
*/
declare rs insensitive cursor
for
select * from t1
open global rs --打开游标
IF @@ERROR=0
BEGIN
PRINT '游标打开成功。'
PRINT '学生总数为：'+ CONVERT(VARCHAR(3),@@CURSOR_ROWS)
END
close rs --关闭游标
deallocate rs --释放游标
GO
--定义后直接赋值
declare t1_cursor cursor --如果不指定游标作用域，默认作用域为GLOBAL
for select * from t1
--先定义后赋值
declare @t1_cursor1 cursor
set @t1_cursor1=cursor for select * from t1
--LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文
declare t1_cursor3 cursor global--全局
for select * from t1
declare t1_cursor4 cursor local--局部
for select * from t1
--go结束上面的作用域
go
open t1_cursor3
close t1_cursor3
open t1_cursor4--错误,在处理结束后被隐匿释放,
--实例
--游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作
--只有支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:
declare t1_curosor5 cursor global scroll--必须指定scrool否则只支持next只进选项
for select tel from t1
open t1_curosor5--打开游标
declare @tel int
--取下一行
fetch next from t1_curosor5 into @tel
print @tel
--取最后一行
fetch last from t1_curosor5 into @tel
print @tel
--取第一行
fetch first from t1_curosor5 into @tel
print @tel
--取上一行
fetch prior from t1_curosor5 into @tel
print @tel
--取第三行
fetch absolute 3 from t1_curosor5 into @tel
print @tel
--取相对目前来说上一行
fetch relative -1 from t1_curosor5 into @tel
print @tel
close t1_curosor5
deallocate t1_curosor5
-- 对于未指定SCROLL选项的游标来说，只支持NEXT取值.
declare test_cursor6 cursor global forward_only--可省
for select id1,tel,name from t1
open test_cursor6
declare @id1 int
declare @te int
declare @name varchar(10)
fetch next from test_cursor6 into @id1,@te,@name
print @id1+@te
close test_cursor6
deallocate test_cursor6
--游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的
declare t1_cursor8 cursor scroll
for select id2,tel from t1
open t1_cursor8
declare @id2 int
declare @te2 int
fetch next from t1_cousor into @id2,@te2
while @@fetch_status=0
begin
print @id2
print @te2
fetch next from t1_cousor into @id2,@te2
end
close t1_cursor8
deallocate t1_cursor8
浙公网安备 33010602011771号