手记系列之五 ----- SQL使用经验分享
前言本篇文章主要介绍的关于本人从刚工作到现在使用Sql一些使用方法和经验，从最基本的SQL函数使用，到一些场景的业务场景SQL编写。SQL基础函数使用
1.字段转换
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.替换空值
意义: if(a==null) a=0;
MySQL:IFNULL
用法:IFNULL(字段,0)别名
Oracle：NVL
用法:NVL(字段,0)别名
Sybase: ISNULL
用法:ISNULL(字段,0)别名
3.合计函数
GROUP BY
例:SELECT 字段1, 【如sum】函数名(字段2) FROM 表名GROUP BY 字段1
4.取某段数据
Mysql： LIMIT
用法: select* from 表 LIMIT 数字 ----取零到数字中的数据
select* from 表 LIMIT 数字1,数字2----取数字1到数字2中的数据
Oracle:rownum
用法:select* from 表 rownum1) 9. 日期格式使用
DATE_FORMAT 可以把日志格式化成想要的格式
DATE_FORMAT(date, format)
sql设置日期格式TO_DATE(字段名,YYYY-MM-DD)字段名
时间格式化:
select substr(字段名,起始位置,长度)自定义名from表名例如:
SET @rownum=0;
SELECT@rownum := @rownum +1 AS aid, h.* FROM household h;查询结果为 20181010
根据日期得到星期几DAYOFWEEK是从周日开始，所以要减一，WEEKDAY是从0开始，所有要加一
SELECT DAYOFWEEK('2021-4-22')-1,WEEKDAY('2021-4-20')+1
10.UNION和UNION All比较
SELECT user_id FROM t_userGROUP BY user_id HAVING AVG(user_age)<22;11. sql之left join、right join、inner join的区别
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
左表返回的值一定大于或等于右表的值
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
右表返回的值一定大于或等于左表的值
inner join(等值连接) 只返回两个表中联结字段相等的行
左表返回的值一定等于右表返回的值
常用SQL
1.解锁sql表
MySql
1.查看是否有锁表
delete from 表名 where 主键 in
(select 主键 from 表名 group by 删除的字段数据名 having count(1) > 1)
and 主键 not in (select min(主键) from 表名 group by 删除的字段数据名 having count(1)>1) 2.查询产生锁的具体sql
SELECT DATE_FORMAT(a.`update_time`,'%Y-%m-%d %H:%i:%S') AS updateTime,
a.`update_time` FROM t_user a 3.杀死产生锁的事物线程
根据具体的sql判断是不是死锁，具体是什么业务，是否可以进行kill。
然后根据结果 kill掉产生锁的事物线程:
SELECT DATE_FORMAT('2018-10-10 00:00:00', '%Y%m%d')批量kill ：
UNION在进行表链接后会筛选掉重复的记录UNION ALL只是简单的将两个结果合并后就返回
例：SELECT 字段 FROM 表1
UNION
SELECT 字段 FROM 表2
SELECT 字段 FROM 表1
UNION ALL
SELECT 字段 FROM 表2 SqlServer
查看被锁表：
SHOW OPEN TABLES WHERE In_use > 0;spid 锁表进程
tableName 被锁表名
解锁：
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 @spidint Set @spid= 57
declare @sql varchar(1000)set @sql='kill '+cast(@spidas 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_namefrom 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 distcance2.查询指定数据库中所有表名
SELECT SUBSTRING_INDEX(ip,'.',3) FROM t_ip 3.查询指定表中的所有字段名
SELECT SUBSTRING_INDEX(tia.ip_addr,'.',3),ip_addr FROM t_ip_all tia
WHERESUBSTRING_INDEX(tia.ip_addr,'.',3) = '192.168.21'4.查询指定表中的所有字段名和字段类型
SELECT
*
FROM
表名
WHERE
INET_ATON(ip) betweenINET_ATON("192.168.21.0")
ANDINET_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';xtype='U':表示所有用户表，xtype='S':表示所有系统表。
3.查询指定表中的所有字段名
select column_name from information_schema.columns where table_schema='database_name' and table_name='table_name';4.查询指定表中的所有字段名和字段类型
select column_name,data_type from information_schema.columns where table_schema='database_name' and table_name='table_name';Oracle
1.查询所有数据库
由于Oralce没有库名,只有表空间,所以Oracle没有提供数据库名称查询支持，只提供了表空间名称查询。
select * from sysdatabases;2.查询当前数据库中所有表名
select * from sysobjects where xtype='U';3.查询指定表中的所有字段名
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');业务场景SQL
select * from v$tablespace;--查询表空间(需要一定权限)学生排名统计
一张表t，有class(班级)、name(学生)、score(成绩)字段
查询每个班级成绩最高的学生
思路:根据分组函数 group by 和最大值 max来实现。
select * from user_tables;查找出每个班级成绩前三的学生
思路: 通过双重子查询来查找
先对学生的成绩进行排名，相同的为一列，然后在跟进这个结果得到前三成绩的学生。
select column_name from user_tab_columns where table_name = 'table_name';--表名要全大写查找出每个班级成绩第二的学生。
思路: 在条件里面发现最大的，然后去除最大的就是第二大的
select column_name, data_type from user_tab_columns where table_name = 'table_name';--表名要全大写查询每名学生的学科总分并排名
思路: 先用group by和sum得到总分排名，然后再利用order by将结果进行排名
这是在一些常见的场景中个人编写以及收集的一些SQL，从刚开始工作的时候就有记录，如有不妥或有更好的写法，欢迎指出~查询用一条SQL 语句 查询出每门课都大于80 分的学生姓名
思路一:利用group by和having 函数来查询
Select name,class,max(score) from t group by class;思路二:利用子查询来查询
SELECT *
FROM( SELECT NAME,score , class,(SELECT COUNT(*)+1 FROM t WHERE score>b.score AND class = b.class ) rank
FROM t b) e
WHERE e.rank<=3
ORDER BY class,rank ASC;根据班级进行区分
SELECT * FROM t a
WHERE 4 >(SELECT count(*)+1 FROM t WHERE class = a.class and score>a.score)
ORDER BY a.class,a.score DESC;场景二、分数相同排名相同(如果有两个第二，有第三名)
思路：需要使用一个额外的变量进行查询比较
SELECT class, MAX(score) FROM tWHERE score NOT IN (SELECT MAX(score) FROM t GROUP BY class) GROUP BY class组织部门查询
一张部门表，有id(主键)、name(名称)、parent_id(父级ID)字段
建表语句和测试数据
SELECT *,SUM(scroe)scroe FROM a GROUP BY sID ORDER BY scroe DESC;根据部门ID查询该部门下面所有的子部门
思路: 由于不清楚部门层级，这里需要使用递归查询，需要定义一个变量和FIND_IN_SET函数来实现递归查询。当查询到一个部门时，将其ID添加到一个变量中，然后继续查询其子部门，直到所有子部门都被查询到为止。
SELECT * FROM a GROUP BY scroe HAVING AVG(scroe) >80;如果是MySql8.0，可以使用WITH RECURSIVE关键字实现递归查询
SELECT * FROM a c WHERE scroe NOT IN (SELECT scroe FROM a b WHERE scroe<='80') GROUP BY sID;根据一个部门ID，查询所有的上级部门
思路: 这篇的作者讲得很详细，这里就不在赘述了.
https://www.cnblogs.com/liuxiaoji/p/15219091.html
SELECT id,NAME,score, (SELECT COUNT(*)+1 FROM t_student WHERE score>t.score) rankFROM t_student t ORDER BY rank ASC; 如果是MySql8.0，可以使用WITH RECURSIVE关键字实现递归查询
SELECT class,NAME,score,(SELECT COUNT(*)+1 FROM t WHERE score>b.`score` AND class=b.`class`)rank FROM t bORDER BY class,rank;其他
手记系列
记载个人从刚开始工作到现在各种杂谈笔记、问题汇总、经验累积的系列。
手记系列
[*]手记系列之一 ----- 关于微信公众号和小程序的开发流程
[*]手记系列之二 ----- 关于IDEA的一些使用方法经验
[*]手记系列之三 ----- 关于使用Nginx的一些使用方法和经验
[*]手记系列之四 ----- 关于使用MySql的经验
一首很带感的动漫钢琴曲~
原创不易，如果感觉不错，希望给个推荐！您的支持是我写作的最大动力！
版权声明:
作者：虚无境
博客园出处：http://www.cnblogs.com/xuwujing
CSDN出处：http://blog.csdn.net/qazwsxpcm
个人博客出处：https://xuwujing.github.io/
来源:https://www.cnblogs.com/xuwujing/p/17444266.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]