欢迎光临
个人知识库,ERP、IT知识分享和应用

常用sql :插入、删除、批量更新、判重、新增列、数据库连接数

----------------
批量插入跨数据库

insert into ejpms.dbo.role (Name,Info,[Level])  select RoleName,RoleNote,[Level] from ttrj.dbo.T_role
------
删除表

 truncate table ejpms.dbo.role
-------- 
批量更新

update table1 set name=table2.name from table1 inner join table2 on table1.id=table2.id
-----------
bit类型取反

update table1 set ***=~ ***
--------------
判断是否为null

select * from table where remark is null
---------------
两个select 合并


select DISTINCT Code  from   dbo.C_Purview  
    
UNION

select DISTINCT  Code from  dbo.OA_StaffPersonalPurview 

union去掉重复的、union all 不去掉重复的 、distinct 返回唯一不同的值

-------------
数据库表新增列

alter table A add 列名 nvarchar(50)
alter table tableName drop column columnName   
--加默认值
alter table table1 add col1 int default 0
-----------------
查询时的Case When
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

-------------------------------------
查询重复列的数据
select * from ttrj.dbo.T_CommissionCard   where a.id in
(select id from ttrj.dbo.T_CommissionCard  group by id having count(*)>1  )

SQL查询的基本原理:两种情况介绍。
第一、单表查询:根据WHERE条件过滤表中的记录,形成中间表(这个中间表对用户是不可见的);然后根据SELECT的选择列选择相应的列进行返回最终结果。

第二、两表连接查询:对两表求积(笛卡尔积)并用ON条件和连接连接类型进行过滤形成中间表;然后根据WHERE条件过滤中间表的记录,并根据SELECT指定的列返回查询结果。

第三、多表连接查询:先对第一个和第二个表按照两表连接做查询,然后用查询结果和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一个中间的结果表,然后根据WHERE条件过滤中间表的记录,并根据SELECT指定的列返回查询结果。


ON后面的条件(ON条件)和WHERE条件的区别:
ON条件:是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件:在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出,将WHERE条件移入ON后面是不恰当的。推荐的做法是:
ON只进行连接操作,WHERE只过滤中间表的记录。


------------------------------------
查看数据库连接数
SELECT [dec].client_net_address,
       [des].[program_name],
       [des].[host_name],
       Count([dec].session_id) AS connection_count
FROM   sys.dm_exec_sessions AS [des]
       INNER JOIN sys.dm_exec_connections AS [dec]
               ON [des].session_id = [dec].session_id
GROUP  BY [dec].client_net_address,
          [des].[program_name],
          [des].[host_name]
ORDER  BY [des].[program_name],
          [dec].[client_net_address];
--数据库回滚
--判断是否有某一列
--开启修改自增长列--插入数据--当前时间加三个月(可类推日、月、年)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--向商户版账号表中插入默认数据
BEGIN tran
USE Eplus365
IF not exists (SELECT null from Eplus365.dbo.commercialtenant_account where name ='system')
begin
SET IDENTITY_INSERT Eplus365.dbo.commercialtenant_account ON  --关闭
insert INTO Eplus365.dbo.commercialtenant_account (id,commercialtenantid,name,mobile,password,passwordsalt,status,gmt_create,gmt_modified,creator,creatorid,roleid,type)
select 0,0,'system','','','',1,getdate(),getdate(),'system',0,0,0
SET IDENTITY_INSERT Eplus365.dbo.commercialtenant_account OFF --开启
 end
if(@@ERROR<>0)
Rollback TRAN
ELSE
COMMIT TRAN
--向商户表中添加有效期字段
--默认增加三个月有效期
BEGIN tran
USE Eplus365
IF NOT EXISTS (SELECT FROM syscolumns INNER JOIN sysobjects ON sysobjects.id = syscolumns.id
        WHERE syscolumns.name = 'startdate' AND sysobjects.name = 'commercialtenant')
ALTER TABLE commercialtenant ADD startdate datetime NULL default getdate()
alter table commercialtenant add enddate datetime null default getdate()
 UPDATE [Eplus365].[dbo].[commercialtenant] SET startdate=getdate(),enddate=convert(datetime,convert(char(20),dateadd(month,3,getdate()),102))
if(@@ERROR<>0)
Rollback TRAN
ELSE
COMMIT TRAN

数据库中存储过程、视图、表数量

SELECT count(*) 存储过程个数
FROM sys.sysobjects
WHERE (type = ‘P’)
GO

SELECT count(*) 视图个数
FROM sys.sysobjects
WHERE (type = ‘V’)
GO

SELECT count(*) 表个数
FROM sys.sysobjects
WHERE (type = ‘U’)
GO


查询所有存储过程中有没有某一内容

select a.* from 
(select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b
where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V','AF')
 ) a
where a.definition like '%vi_RoomDetailRentCases%'
未经允许不得转载:Blog.XiaoMing.Xyz » 常用sql :插入、删除、批量更新、判重、新增列、数据库连接数

登录

找回密码

注册