CASE WHEN
意义: If(a==b) a=c;
用法:
1, CASE 字段 WHEN 字段结果1 THEN 字段显示结果1 WHEN 字段结果2 THEN 字段显示结果2 END
2, CASE WHEN 字段1=字段结果1 THEN 字段显示结果1 WHEN 字段2=字段结果2 THEN 字段显示结果2 END
2.替换空值
select a.trx_id 事务id ,a.trx_mysql_thread_id 事务线程id,a.trx_query 事务sql from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id;
复制代码
2.查看一个字段的在那些表中
select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id;
复制代码
SqlServer：
select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id into outfile '/tmp/kill.txt';
复制代码
3. 查询所有表及其字段和备注
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
复制代码
4.sql数据自我复制
declare @spid int Set @spid = 57
declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)
exec(@sql)
复制代码
5.删除重复数据，保留最小id的那一条
SELECT DISTINCT TABLE_NAME FROM information_schema.`COLUMNS` WHERE COLUMN_NAME='ip' AND TABLE_SCHEMA='guard_scan' AND TABLE_NAME NOT LIKE 'vm%';
复制代码
6.SQL查询相隔天数语句
--今天
select table_name from user_tab_columns where COLUMN_NAME='字段'
复制代码
--昨天
SELECT t.table_name,
t.colUMN_NAME,
t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
t1.COMMENTS
FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.table_name = t1.table_name
AND t.column_name = t1.column_name;
复制代码
--本周
insert into test(name,age,gender)
select name,age,gender from test
复制代码
--本月
delete from table_name as ta where ta.唯一键 <> ( select max(tb.唯一键) from table_name as tb where ta.判断重复的列 = tb.判断重复的列);
复制代码
--上一个月
select * from 表名 where to_days(时间字段名) = to_days(now());
复制代码
--本年
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) = 1
复制代码
--上一月
SELECT * FROM 表名 WHERE YEARWEEK( date_format( 时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ;
复制代码
--查询本季度数据
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' )
复制代码
--查询上季度数据
SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m') =1
复制代码
--查询本年数据
SELECT * FROM 表名 WHERE YEAR( 时间字段名 ) = YEAR( NOW( ) )
复制代码
--查询上年数据
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
复制代码
--查询当前这周的数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
复制代码
--查询上周的数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
复制代码
--查询当前月份的数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
复制代码
--查询距离当前现在6个月的数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
复制代码
--查询上个月的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
复制代码
7.mysql经纬度圆周计算
单位为米
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
复制代码
8.mysql的ip地址段查询判断
ip查询前三段
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
复制代码
示例:
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
复制代码
9.mysql的地址段大小查询判断
使用INET_ATON函数进行转换
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
复制代码
其他
MySQL
1.查询所有数据库
SELECT st_distance_sphere(POINT('114.43107891381024', '30.52764363752110'), POINT('114.42638694658900', '30.54681469735225')) AS distcance
复制代码
2.查询指定数据库中所有表名
SELECT SUBSTRING_INDEX(ip,'.',3) FROM t_ip
复制代码
3.查询指定表中的所有字段名
SELECT SUBSTRING_INDEX(tia.ip_addr,'.',3),ip_addr FROM t_ip_all tia
WHERE SUBSTRING_INDEX(tia.ip_addr,'.',3) = '192.168.21'
复制代码
4.查询指定表中的所有字段名和字段类型
SELECT
*
FROM
表名
WHERE
INET_ATON(ip) between INET_ATON("192.168.21.0")
AND INET_ATON("192.168.1.255")
复制代码
SQLServer
1.查询所有数据库
show databases;
复制代码
2.查询当前数据库中所有表名
select table_name from information_schema.tables where table_schema='database_name' and table_type='base table';
select name from syscolumns where id=Object_Id('table_name');
复制代码
4.查询指定表中的所有字段名和字段类型
select sc.name,st.name from syscolumns sc,systypes st where sc.xtype=st.xtype and sc.id in(select id from sysobjects where xtype='U' and name='table_name');
