sql常用记录
sqlserver
在已有值的列上自动增加
获取列最大的值
declare @Field int
select @Field = ISNULL(Max(Field),0) from SupCsBill
根据序号增加
select @Field+(ROW_NUMBER() OVER (ORDER BY a.Uid ASC)) from SupCsBill
1:自己添加一个区别字段,判断区别字段进行删除
delete from (select ROW_NUMBER() over(partition by 代码 order by 时间 desc) as rows a,代码,时间 from 表) where a > 2
2:查询出表中多于1的数据,查询出区别,根据区别进行删除
select 区别字段 from 表 a right join (select 代码,MIN(时间) 最小时间 from 表 group by sddm having COUNT(代码)>1) b on a.代码= b.代码 and a.时间= b.时间 order by a.代码
3:sql查询指定表外键约束
引用地址:https://www.cnblogs.com/sishahu/p/5806930.html
select
a.name as 约束名,
object_name(b.parent_object_id) as 外键表,
d.name as 外键列,
object_name(b.referenced_object_id) as 主健表,
c.name as 主键列
from sys.foreign_keys A
inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id
inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id
where object_name(B.referenced_object_id)='xxx';
4:根据值查询表与列
引用地址:https://blog.csdn.net/zengcong2013/article/details/55264202
Declare @value VARCHAR(1024)
--要查询的值
set @value = 'Medical'
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR FOR
SELECT o.name, c.name FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT * FROM #t
DROP TABLE #t
5:查询数据库特定字段,修改字段类型
declare @index int,@name varchar(100),@sql varchar(100)
set @index=1
while @index <=50 --查询出50张表有特定字段
begin
select @name=TABLE_NAME from (
SELECT COLUMN_NAME,TABLE_NAME,ROW_NUMBER() OVER (ORDER BY a.COLUMN_NAME ASC) AS seq FROM INFORMATION_SCHEMA.columns a WHERE COLUMN_NAME='特定字段' --查询出有多少表有特定字段
) b
where b.seq=@index
set @sql=' alter table '+@name+' alter column 特定字段 float null '
--select @sql
exec(@sql)
set @index=@index+1
end
6:查找一个外键被引用的所有表
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 oMain.name LIKE ‘% [主表名称]%’ ;
7.根据排序获取最小的唯一值
UPDATE A_REDIS_DETAIL SET A_SYS_ID = A.SYS_ID FROM
(SELECT A.*,B.SYS_ID SYS_ID_B FROM
(SELECT A.*,B.SYS_ID FROM
(SELECT A_CODE,MIN(SEQ_NO) SEQ_NO FROM A_REDIS WHERE BATCH_SYS_ID = @BATCH_SYS_ID GROUP BY A_CODE) A
INNER JOIN A_REDIS B ON A.A_CODE = B.A_CODE AND A.SEQ_NO = B.SEQ_NO) A
INNER JOIN A_REDIS B ON A.A_CODE = B.A_CODE) A
WHERE A_REDIS_DETAIL.A_SYS_ID = A.SYS_ID_B
8.多行数据合并为一个字符串
SELECT STUFF((SELECT '、' + A+'-'+B FROM TAB WHERE SYS_ID = T.SYS_ID FOR XML PATH('')),1,1,'') FROM TAB2 T
ORACLE
执行update,delete的时候还要点击提交语句才算是执行了
select * from all_tables WHERE owner='STM' ;或者
SELECT * FROM DBA_TABLES WHERE OWNER='STM';
表和列
DBA_TABLES、ALL_TABLES和USER_TABLES显示了有关数据库表的一般信息。
DBA_TAB_COLUMNS、ALL_TAB_COLUMNS和USER_TAB_COLUMNS显示了每个数据库表的列的信息。
注意:DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息,包括表。
最新文章
- nefu1109 游戏争霸赛(状压dp)
- [译文]通过ID, TagName, ClassName, Name, CSS selector 得到element
- 基于Session的国际化实现
- 传输层(3)-缓冲区大小及限制、TCP输出
- 解决oracle11g 空表不能exp导出的问题
- javaScript hook
- 黑马程序员-IO(二)
- oracle 11g RAC ocfs2
- Quartz学习——SSMM(Spring+SpringMVC+Mybatis+Mysql)和Quartz集成详解(四)
- let and const
- Kernel数据结构移植(list和rbtree)
- powerDesigner创建类及数据模型
- 「SDOI2008」Sandy 的卡片
- str和unicode类
- ACM题目————STL练习之求次数
- electron 的窗口设置最大化 最小化
- 小程序获取access_token
- April Fools Day Contest 2016 E. Out of Controls
- WinCE数据通讯之Web Service篇
- Unknown character set: &#39;utf8mb4&#39;