项目开发中常用到的SQL语句

项目开发中常用到的sql语句

1、循环示例

循环示例代码：

declare @i int
declare @name varchar(10)
declare @password varchar(10)
set @i = 1000

while @i < 1200
begin
set @i =@i +1
set @name = right('0000' + cast(@i as varchar(10)),4) 
set @password = @name
select @name

insert into dbo.locomotiveteminalbase
(li_id,t_id,lt_idnumber,lt_mim,lt_fuwqip,lt_fuwqdkh,lt_createdby)
values('d82575c0-2d21-4c47-a406-7771d7d2c80a','fb5d9a7b-9cd6-4a55-9e90-881706eaf2b9',
@name,@password,'192.168.1.187','2000','9015c234-e185-4e15-96c6-f53426dd6690')

end

2、缓存依赖中用到的sql语句

代码示例：

--查看状态
  select databasepropertyex('soft_locomotiverm_db','isbrokerenabled')
  
  --启用broker
alter database soft_locomotiverm_db set new_broker with rollback immediate
alter database soft_locomotiverm_db set enable_broker

--添加用户
--创建一个新的登录
exec sp_addlogin 'test','123456'
--赋予新用户sa(system administrator)权限,添加服务器角色
exec sp_addsrvrolemember 'test','sysadmin'   
--授予对自己数据库的所有权限?        
exec sp_addrolemember n'db_owner', n'test'  

--赋予权限
grant subscribe query notifications to test 


--检查错误?
    use [master]
    go
    alter database soft_locomotiverm_db set  single_user with rollback immediate
    go
    alter database soft_locomotiverm_db set  single_user 
    go
    alter database soft_locomotiverm_db set enable_broker 
    go
    alter database soft_locomotiverm_db set  multi_user 
    go 

use soft_locomotiverm_db
go


--新增用户
exec sp_addlogin 'test'            --添?加登录
exec sp_grantdbaccess n'test'            --使其成为当前数据库的合法用户
exec sp_addrolemember n'db_owner', n'test'      --授予对自己数据库的所有权限


exec sp_revokedbaccess n'test'            --移除对数据库的访问权限
exec sp_droplogin n'test'            --删除登录