SQLServer常用语法总结

摘要信息：

1.查询指定数据库
2.查询数据库是否存在
3.查询指定列是否存在
4.查询表是否存在
5.分页查询
6.字段中字符串批量修改
7.字符串截取
8.生成随机数
9.创建登录名与数据库用户
10.解决无法删除数据库问题
11.查看系统用户
12.主键自动增长类型
13.查询指定表的字段类型
14.存储过程
15.规则创建与绑定
16.约束创建与删除
17.触发器

具体如下：

1.查询指定数据库

 select * From master.dbo.sysdatabases where name='db_61'

2.查询数据库是否存在

-- 方法一：判断数据库 test_db是否存在
if exists (select * from master.dbo.sysdatabases where name='dd_61')
print 'exists' else print 'not exists';

-- 方法二：判断数据库 test_db是否存在
if db_id('test_db') is not null print 'exists' else print 'not exists';

-- 查询数据库 db_61
-- 数据库不存在则输出 NULL
select db_id('db_61');

3.查询指定列是否存在

-- 查询所有列
select * from syscolumns where id=object_id('test');
select * from syscolumns where id=object_id('test') and objectproperty(id,'isusertable')=1;

-- 查询指定表的列
select * from syscolumns where id=object_id('test') and name='num';

-- 如果列不存在则添加列
if not exists (select * from syscolumns where id=object_id('student_61') and name='addr') alter table student_61 add addr varchar(20);

4.查询表是否存在

-- 查询表
select * from sysobjects where id=object_id('test');

-- 查询表指定是用户表
select * from sysobjects where id=object_id('test')and objectproperty(id,'isusertable')=1;

-- 表不存在则创建
-- 其中表名 sc1_61,列名 sno
if not exists (select * from syscolumns where id=object_id('sc1_61') and name='sno')
Create table sc1_61(
Sno Char(10),
Cno varchar(12),
Grade int default 2 ,
Primary key(sno),
Foreign key(cno) references course_61(cno)
);

-- 表存在则删除
if exists(select * from sysobjects where id=object_id('test')) 
drop table test;

-- 存在则删除后再建表
if exists(select * from sysobjects where id=object_id('test')) 
drop table sc1_61
Create table sc1_61(
Sno Char(10),
Cno varchar(12),
Grade int default 2 ,
Primary key(sno),
Foreign key(cno) references course_61(cno)
);  

5.分页查询
– 假设每页显示n条记录，当前显示的是第m页，表名是A，主键是A_id
select top n * from A where A_id not in (select top (m-1)*n A_id from A);

-- 每页2条，当前显示在第3页，查询第5、6条记录
select top 2 * from student_61 where sno not in (select top ((3-1)*2) sno from student_61 order by sno asc) order by sno asc;

6.字段中字符串批量修改

-- 声明 用户变量@i，则在SQL语句中应用 (@i)，需用括号括起来
-- 实现把字符串截短再放回去
declare @i int   
set @i=0   
while @i<10   
   begin   
   update student_61 set sno=right((select top 1 sno from student_61 where sno not in 
(select top (@i) sno from student_61)),4) from student_61
 where sno=(select top 1 sno from student_61 where sno not in (select top (@i) sno from student_61))
   set @i=@i+1   
end; 

-- 说明
-- select top 1 sno from student_61 where sno not in (select top (@i) sno from student_61)
-- i从0开始，表示每次选取一条记录，i++；到下一条记录 当 i=0；查询为第2条；i=1，查询为第第2条

7.字符串截取

select right('1234',2);		-- '34'
select left('1234',2);		--'12'
LTRIM('  123');	--左边去空格
RTRIM('123  ');	--右边去空格
SUBSTRING('string',start,len);	
--从第start个字符开始，截取len个字符

8.生成随机数

-- 方法1：数字范围：0至N-1之间，如
cast(floor(rand()*100) as int) 		--  生成0至99之间任一整数

-- 方法2：数字范围：1至N之间，如
cast(ceiling(rand() * 100) as int)	-- 就会生成1至100之间任一整数

-- 随机获取随机表中前几列数据
select top (cast(ceiling(rand()*10) as int)) * from student_61;

-- 复合语句
-- 测试表中随机插入数据
insert into sc_61(sno,cno,grade) values(( 
	select top 1 sno from student_61 
	where sno not in (select top (cast(ceiling(rand()*8) as int)) sno from student_61)),
	(select top 1 cno from course_61
	where cno not in (select top (cast(ceiling(rand()*8) as int)) cno from course_61)),
	(cast(50+ceiling(rand() * 50) as int)));

9.创建登录名与数据库用户

create database test;

-- 创建登录名 wang，指定默认数据库
-- 如果登录名的默认数据库中没有数据，会无法进入
create login wang with password='666666',default_database=test;

--为登录名 wang 创建数据库用户 wz_61
create user wz_61 for login wang with default_schema=dbo;

-- 赋予 数据库用户 wz_61 管理员权限，到这一步基本完成
exec sp_addrolemember 'db_owner','wz_61';


-- 修改及删除的部分操作语句

-- 修改密码
alter login wang with password='111111';

-- 要先删除数据库用户 wz_61;
drop user wz_61;

-- 然后再删除登录名
drop login wang;

10.解决无法删除数据库问题

-- 假设数据库rantal_db无法删除
-- msdb.dbo.sp_delete_database_backuphistory 删除数据库备份和还原历史记录信息
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'rantal_db' 
GO
USE [master]
GO
ALTER DATABASE rantal_db SET  SINGLE_USER WITH ROLLBACK IMMEDIATE -- 设置库单用户模式，和设置立即回滚
GO
USE [master]
GO
DROP DATABASE rantal_db
GO

11.查看系统用户

select name from sysusers;
select name from sysusers where name='wz_61';

12.主键自动增长类型

-- identity(m,n)
-- m表示的是初始值，n表示的是每次自动增加的值
-- 如果m和n的值都没有指定，默认为（1,1）
-- 一旦使用自动增长类型，将不能再对主键插入数据，只能由系统自动生成
num int identity(100,10);
num int identity;

13.查询指定表的字段类型

-- 查询指定表的字段属性说明
-- 1.连接syscolumns表、systypes表，选取其中几项信息输出
-- 2.syscolumns表中只含有数据类型编号
-- 3.要获取完整的名字需要从systypes表中找
-- 4.syscolumns.length得到物理内存的长度，对于nvarchar和varchar等类型在数据库中的显示是这个的一半

-- 查询指定表的字段属性
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length 
FROM syscolumns, systypes 
WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('表名');

-- 与上相似，使用左连接
select syscolumns.name, systypes.name, syscolumns.length from syscolumns   
left join systypes on syscolumns.xusertype=systypes.xusertype 
where id=(select id from sysobjects where name='表名');

-- 获取字段名
Select name FROM SysColumns Where id=Object_Id('表名')

14.存储过程

-- 带默认参数的存储过程调用
create procedure test_pro 
@sno char(10) = '1208',@cno varchar(12) 
as select * from sc_61 where sno=@sno and cno=@cno;

-- 执行存储过程
-- 第一个参数使用默认值
exec test_pro default,'002';

-- 带输出参数的存储过程
create procedure stu_avg @dept char(18),@avg_grade int output as 
select @avg_grade=avg(grade) from sc_61 join student_61 s on sc_61.sno=s.sno where s.sdept=@dept;

-- 调用存储过程
declare @x int exec stu_avg '计算机',@x output print @x;

-- 判断存储过程是否存在
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'stu_avg')) select '不存在';  

-- 查看所有存储过程( 'p'表示类型)
select * from sys.objects where type='P'
select * from sysobjects where xtype='P'
select * from sys.procedures;

-- 查看存储过程 test_procedure 的内容
exec sp_helptext 'test_procedure' 
-- 或用
select text from syscomments where id=object_id('test_procedure') 

-- 存在则删除存储过程再创建
USE db_61  
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[myProc]'))  
   DROP PROCEDURE myProc  
GO   
CREATE PROCEDURE myProc   
    @inparam int
AS  
SELECT sno,cno,grade FROM sc_61 where grade>@inparam;

-- 执行
exec myProc 90;

15.规则创建与绑定

-- 创建规则
create rule sex_rule
as @sex in ('男','女')

-- 绑定规则（参数sex_rule为规则名，test为表名，sex为字段名）
EXEC sp_bindrule 'sex_rule','test.sex'

-- 取消绑定
EXEC sp_unbindrule 'test.sex';

-- 删除规则
drop rule sex_rule;

16.约束创建与删除

-- 创建不指定约束名时
alter table sc_61 add check(grade>=0 and grade<=100);

sp_help 'sc_61';
-- 查看 结果 7 中列 constraint_name 即为约束名
-- 可以发现不指定约束名将随机生成的约束名如下：CK__sc_61__grade__662B2B3B

-- 删除约束（知道约束名才可以直接删除）
alter table sc_61 drop CK__sc_61__grade__662B2B3B;

-- 创建约束时指定约束名 grade_limit
alter table sc_61 add  constraint grade_limit check(grade>=0 and grade<=100);

-- 删除约束
alter table sc_61 drop grade_limit;

17.触发器
以下几个和触发器的函数，这是查看官网得出的解释：
UPDATE ( column )

• 返回一个布尔值，指示是否对表或视图的指定列进行了INSERT或UPDATE尝试。

UPDATE（）

• 用于Transact-SQL INSERT或UPDATE触发器主体内部的任何位置，以测试该触发器是否应执行某些操作。
• 不管INSERT或UPDATE尝试是否成功，UPDATE（）都会返回TRUE。

COLUMNS_UPDATED()

• 测试在多列上执行的UPDATE或INSERT操作。

• 返回从左到右排序的一个或多个字节。每个字节的最右位是最低有效位。最左边的字节的最右边的位表示表中的第一列，左边的下一位表示第二列，依此类推。

• 对于INSERT操作中的所有列，返回TRUE，因为这些列插入了显式值或隐式（NULL）值。 在Transact-SQL

• INSERT或UPDATE触发器内的任何位置使用。

 如果存在则删除触发器
if exists (select name from sys.objects where name='tri_grade' and type='TR')
drop trigger tri_grade;

创建触发器
create trigger tri_grade on sc_61 for update,delete as 
-- 如果更新了成绩列
if update(grade) 
begin 
select inserted.sno as 学号,deleted.grade as 原成绩,inserted.grade as 新成绩 
from deleted join inserted on deleted.sno=inserted.sno
end 
-- 如果是删除操作
else if columns_updated()=0
begin
select 被删学号=sno,被删课号=cno,被删成绩=grade from deleted
end 
else 
print '更新了其他列!';

提示：如需了解更多用法，可参考官方文档SQL Server教程