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之间任⼀整数
update语法大全-
- 随机获取随机表中前⼏列数据
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.约束创建与删除

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。