--查询外键
select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
--查找BAK表缺少的列
select name from sys.columns
where object_id = object_id(N'dwCaseDrAdvices')--修改表名
and name not in
(select name from sys.columns
where object_id = object_id(N'dwCaseDrAdvices_BAK'))--修改BAK表名
--查找两个表列类型不一致的列
select a.name from
(select * from sys.columns
where object_id = object_id(N'dwCaseDrAdvices')) a --修改表名
join (select * from sys.columns
where object_id = object_id(N'dwCaseDrAdvices_BAK')) b--修改BAK表名
on a.name = b.name
where a.user_type_id != b.user_type_id
or a.max_length != b.max_length
or a.system_type_id != b.system_type_id
--修改表中的列类型
alter table dwCaseDrAdvices--需要修改的表名
alter column DradviceName --需要修改的列名
int --需要修改成的类型
--根据条件查出结果插入到另一张表中
select * into #mytable from CIS2014..HRCaseHisRecord
where iDiagnoseId = 50124 and iCaseHisId = 385765
insert into doctorcis2013..HRCaseHisRecord
select * From #mytable
--RowNumber
select orderid,custid,orderdate,
ROW_NUMBER() over(partition by custid
order by orderdate) as rownum
from Sales.Orders
/*
orderid custid orderdate rownum
10643 1 2007-08-25 00:00:00.000 1
10692 1 2007-10-03 00:00:00.000 2
10702 1 2007-10-13 00:00:00.000 3
10835 1 2008-01-15 00:00:00.000 4
10952 1 2008-03-16 00:00:00.000 5
11011 1 2008-04-09 00:00:00.000 6
10308 2 2006-09-18 00:00:00.000 1
10625 2 2007-08-08 00:00:00.000 2
10759 2 2007-11-28 00:00:00.000 3
10926 2 2008-03-04 00:00:00.000 4
*/
--查看被锁表:
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
--spid 锁表进程
--tableName 被锁表名
--解锁:
declare @spid int
Set @spid = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
--判断一个表是否存在
--存储过程 P
if(OBJECT_ID('table_name','U')) is not null
begin
end
else
begin
end
--判断列是否存在
if COL_LENGTH('pubStyleList','nShortCutKey')) is null
begin
alter table pubStyleList
add nShortCutKey nvarchar(10)
end
/*增加约束*/
示例:
--添加主键约束
alter table stuInfo
add constraint PK_stuNo primary key(stuNo)
--添加唯一键约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)
--添加默认约束
alter table stuInfo
add constraint DF_stuAddress default('地址不详') for stuAddress
--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
--添加外键约束
alter table stuInfo
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
--取最近整数
select CEILING(1.08)
/*删除约束*/
alter table 表名 drop constraint 约束名
--当 IDENTITY_INSERT 设置为 OFF 时,不能为表中的标识列插入显式值
--允许将显式值插入表的标识列中 ON-允许 OFF-不允许
set identity_insert OrderList ON--打开
insert into OrderList(id,ordername,createdate)
values(4520,'set',getdate())
set identity_insert OrderList OFF--关闭
--SQL 链接到Oracle服务器语句
/*查询操作*/
SELECT * FROM OPENQUERY(Province, 'select * from t_base_duty')
/*删除操作*/
DELETE FROM OPENQUERY(Province, 'select * from t_base_duty')
/*插入操作*/
INSERT INTO OPENQUERY (province,'SELECT * FROM t_base_role') (role_id,role_name,bureautype_id,is_system,b_use,field_order,b_del,identity_id) VALUES (10,'科比',7,1,0,1,0,2)
/*更新操作*/
UPDATE OPENQUERY (province,'SELECT * FROM t_base_role') SET role_name='科比' WHERE role_id=10
--创建连接服务器
exec sp_addlinkedserver 'CisZy','','SQLOLEDB','111.111.111.200'
exec sp_addlinkedsrvlogin 'CisZy','false',null,'SA',''
--重新生秘钥
--在本地服务器上执行:tempdb库中
ALTER SERVICE MASTER KEY FORCE REGENERATE
--比较两张表的结构
select a.name as '列名',c.name as '数据类型',a.max_length as '长度',d.name as '数据类型',b.max_length as '长度' from
(select * from sys.columns
where object_id = object_id(N'HROPSList')) a --修改表名
join (select * from sys.columns
where object_id = object_id(N'HROPSList_bak')) b--修改BAK表名
on a.name = b.name
join sys.types c on a.system_type_id =c.system_type_id
join sys.types d on a.system_type_id =d.system_type_id
where a.user_type_id != b.user_type_id
or a.max_length != b.max_length
or a.system_type_id != b.system_type_id
--清除表
truncate table hradvicerunzy
--OPENDATASOURCE
--开启配置
EXECUTE sp_configure 'show advanced options',1
RECONFIGURE
EXECUTE sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
SELECT top 10 *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=globe01;User ID=xw_cai;Password=123'
).[order].dbo.ordermaster
--查询外键所在表
select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
where fk.name = 'FK_HRDIAGLI_REFERENCE_HRFIRSTP1'
--一行数据拆分成两行数据
--cOPSName: 右腋下淋巴清扫术+子宫下段剖宫产
declare @SplitChar varchar(2)
set @SplitChar = '+'
select top 5000 ID=Identity(int,1,1) into #Num
from syscolumns a,syscolumns b
Select
a.iOPSList,COl2=substring(a.cOPSName,b.ID,charindex(@SplitChar,a.cOPSName+@SplitChar,b.ID)-b.ID)
from
HROPSList a,#Num b
where
a.iOPSList = 74222
and charindex(@SplitChar,@SplitChar+a.cOPSName,b.ID)=b.ID
drop table #Num
--SQL生成脚本
SELECT 'INSERT INTO pubDictStaff(No_Staff,cStaffCode,cName) VALUES
('+cast(No_Staff as varchar(10))+',''' +CONVERT(varchar(19), cStaffCode, 121) + ''',
''' + cName + ''')' FROM pubDictStaff
--替换函数返回结果Li2
select REPLACE('LiChao','Chao','2')
--查询数据库中所有表的行数
select b.name as tablename ,
c.row_count as datacount
from sys.indexes a ,
sys.objects b ,
sys.dm_db_partition_stats c
where a.[object_id] = b.[object_id]
AND b.[object_id] = c.[object_id]
AND a.index_id = c.index_id
AND a.index_id < 2
AND b.is_ms_shipped = 0
and c.row_count >0
--查询没有分配班级的学生信息
select * from student where not exists (
select * from classes where id = student.cid
);
--查询主键
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=
'pubDictStaff'
--Sql Server数据库用SQL语句查询方法如下:
select name from sysobjects where xtype='TR' --所有触发器
select name from sysobjects where xtype='P' --所有存储过程
select name from sysobjects where xtype='V' --所有视图
select name from sysobjects where xtype='U' --所有表
--查询表初特定列外所有的列
declare @col nvarchar(max)
set @col=''
select @col=@col+','+name from syscolumns where id=object_id('HRDiagnose')
and name<>'iNurseLevel' order by colid
--禁用:
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
--恢复:
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
--禁用某个表上的所有触发器
ALTER TABLE 你的表 DISABLE TRIGGER all
--启用某个表上的所有触发器
ALTER TABLE 你的表 enable TRIGGER all
--高效去重
WITH TEST AS
(
SELECT ROW_NUMBER()
OVER(PARTITION BY Column1,Column2,Column3 ORDER BY ID )
AS NUM,* FROM TableName
)
DELETE FROM TEST
WHERE NUM != 1
--删除重复语句
SELECT * FROM frmZHProductResult
where id<>(select max(id) from frmZHProductResult d
where frmZHProductResult.zhproductid =d.zhproductid )
AND zhproductid in (select zhproductid from frmZHProductResult b
GROUP BY ZhproductId HAVING COUNT(ZhproductId)>1)
--比较两张表中新增数据
select * From czszyyhis..DictStaff a
where not exists
(select * from DictStaff b
where a.No_Staff = b.No_Staff)
--重命名表明
EXEC sp_rename 'DictStaff','DictStaff_Table'
--多行数据合并成一行
select No_Dept,
[EnableString]=stuff((select ','+ CAST(iAdviceItemId as varchar(10)) from pubDeptAdviceItem
where No_Dept=a.No_Dept for xml path('')),1,1,'')
from pubDeptAdviceItem a group by No_Dept
--查看小数点长度大于3
select
iRetail,* From DictDrugact
where iRetail*100>floor(100*iRetail)
--查看表是否存在标识列
if exists(select * from syscolumns where id=object_id(N'DictStaff') and status=0x80)
print '存在'
else
print '不存在'
--分页取数据
SELECT ROW_NUMBER() OVER (ORDER BY No_Staff) AS RowNumber,* into #temp
FROM DictStaff with(nolock)
select top 100 * from #temp where RowNumber > 10 * ({0} - 1)
drop table #temp
--查询包含连接服务器的视图、存储过程
select distinct b.name
from dbo.syscomments a, dbo.sysobjects b
where a.id=b.id and b.xtype in ('p','v') and a.text like '%text%'
order by name
--查询表的行数
SELECT a.name, b.rows as '行数'
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC
自己积累的一些SQL语句
最新推荐文章于 2019-11-06 23:04:33 发布