SQL小技巧（LTS）

Nov 26, 2018 Eric Guo    2444

摘要：总结一些常用的sql操作，方便之后查询使用

先输出某一类的信息

SELECT * FROM wp_posts ORDER BY CASE WHEN post_type ='product' THEN 0 ELSE 1 END,id desc;

MYSQL的不等于使用!=或者<>都可以 但是sqlserver不支持!= 所以最好使用<>

UNION与UNION ALL的区别

union 会去除重复的数据union不会
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

批量更新

update wp_posts set post_content = replace(post_content, 'http://ws234.com','https://ws234.com');

给字段后面增加一个自定义的字符串(拼接)

update posts set title=CONCAT(title,'.html') where id = 1;（正确示例）
update posts set title = title+'.html' where id = 1;（错误示例，使用了+会当做加法运算，最后结果title变成0）

删除字符串最后一位

UPDATE posts SET title = substring(title,1,length(title)-1);

查找字符串中存在另外的字符串（必须是a,b,c,这样用逗号分开的）

select * from `test` where FIND_IN_SET('a',`list`);

参数绑定，命名绑定

 DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
 $results = DB::select('select * from users where id = :id', ['id' => 1]);

脱敏操作

update `customer` set mobile=INSERT(mobile,4,4,'0000');

group by 组内排序使用max（time）

select count(a.id)ct,a.handler_id,u.username,t.value,max(t.value)max_time from mantis_bug_table a left join mantis_user_table u on a.handler_id=u.id left join mantis_custom_field_string_table t on a.id=t.bug_id where a.status=80 and t.field_id=2 and t.value GROUP by u.id

CONCAT可以用来拼接字符串

insert into wp_postmeta(post_id,meta_key,meta_value)  SELECT c.`id`,'custom_permalink',CONCAT(c.HtmlFile,'.html')   FROM (select a.id,b.HtmlFile from wp_posts a left join product b on a.post_title = b.title WHERE a.post_type='product')c

replace into

不重复的插入数据库，他会先尝试插入如果有了，就会将之前的删除，然后将最新的插入数据库

BETWEEN 和 NOT BETWEEN

其中between是包含边界值的，not between不包含边界值

sum进行求和的时候出现奇怪的小数

原因：
    对于浮点数的加减（sum就是加了）有效的小数位与参与运算的数中小数位最少的相同
    如 10.9 + 0.23 结果 11.13 按此原则就是 11.1
    计算后可用 round 函数截取有小数位（他会做四舍五入）
    对于需要精确计算的小数的场合（比如金融），就不能使用浮点数了
    mysql 专门提供了 DECIMAL 类型来适应这种需求，当然运算速度要稍稍慢一点 
解决：
    round(sum(t.value),1)

分组按照数量筛选

 select * from [KPI].[dbo].[Attendance_Employee]
where 工号 in (select   工号 from   [KPI].[dbo].[Attendance_Employee] group by [工号]  having count([工号]) > 1)

SQLSERVER 中使用判断

SELECT  TOP (100) PERCENT f_ConsumerName AS username, ReadDate, CASE WHEN MIN(ReadTime) > (ReadDate + ' 12:00:00') 
                   THEN DATEDIFF(mi, MIN(ReadTime), MAX(ReadTime)) ELSE DATEDIFF(mi, MIN(ReadTime), MAX(ReadTime)) 
                   - 60 END AS work_time, '480' AS standard_time, DATEDIFF(mi, MIN(ReadTime), MAX(ReadTime)) - 540 AS diff_time, 
                   MIN(ReadTime) AS PunchIn, MAX(ReadTime) AS PunchOut
FROM      dbo.oceania_all_attendance
GROUP BY f_ConsumerName, ReadDate
ORDER BY ReadDate DESC

单纯的输出一个数据，但是并没有操作表

select 'test' as 'test2'
不加引号就会报错table不存在，加引号就可以输出key为test2，值为test的数据

Having操作

SELECT *
  FROM [KPI].[dbo].[Attendance_Employee] where 姓名 in (select 姓名 from [KPI].[dbo].[Attendance_Employee] group by 姓名 having count(姓名)>1)

查找当前有谁连接了数据库

infomation_schema 下的PROCESSLIST  记录当前连接的人

left join 在where

查询的是left后的这个总的临时表的数据，所以会出现左表不连续的问题
如果想整表过滤就直接老样子使用where
如果单独想过滤右侧的，将where去掉，换成and 后加查询条件在对应的关联表后
SELECT
       T.xx,
       T1.xx,
       ...
  FROM xxx T
  LEFT JOIN xxx T1
    ON T.ID = T1.ID AND T.xx = xx AND T1.xx = xx

exists

select a.* from tb a where exists(select 1 from tb where name =a.name)
exists 后面的返回真假，exists 后面的返回真 就返回这条数据，返回假就跳过这个数据。 not exists 后面的返回真就跳过这条数据，返回假就返回这个数据。 
相当于对外层的用loop循环。每次都去判断exists后的查询的真假。符合的就返回，不符合的就跳过
exists要比in 相比好一点

导出数据库

mysqldump -uroot -pguoshixinpassword mydbname>  /usr/share/nginx/html/test.sql

查看数据库版本

select VERSION();

PHP使用原生插入报错，因为单双引号等的问题

addslashes() 使用这个函数处理一下

给sql结果拼接字符

SELECT name, CONCAT('http://www.adamantvalves.com/f/',img) as images,brief,remark FROM `ps_companyproduct` where img <> ''

sqlserver中php读出的时候float类型读取失真

convert(nvarchar(30),[Qty])    将之转换为浮点数进行操作

格式化时间戳

select a.id,a.summary,c.name,from_unixtime(b.value),MAX(from_unixtime(e.date_modified)) as endTime
from mantis_bug_table a 
LEFT JOIN mantis_category_table c on a.category_id=c.id
LEFT JOIN mantis_custom_field_string_table b ON a.id = b.bug_id
LEFT JOIN mantis_bug_history_table e ON e.bug_id = a.id
WHERE a.status = 90 and b.field_id = 8  
GROUP BY a.id


select a.id,a.summary,c.name,from_unixtime(b.value),MAX(from_unixtime(e.date_modified)) as endTime
from mantis_bug_table a 
LEFT JOIN mantis_category_table c on a.category_id=c.id
LEFT JOIN mantis_custom_field_string_table b ON a.id = b.bug_id
LEFT JOIN mantis_bug_history_table e ON e.bug_id = a.id
WHERE a.status = 90 and b.field_id = 8  
GROUP BY a.id

连接查询，出现多条数据，希望保留主表的个数，可采用子查询

 SELECT [OC_User_Seat_Info].id,[OC_User_Seat_Info].SeatNumber, [OC_User_Info].[DisplayName] as username,[OC_User_Info].[LegalName],[OC_User_Info].[Department],[OC_User_Info].[ExtNumber],
(select top 1 DisplayName from OC_CodeTable where Code = [OC_User_Info].Position) as DisplayName,
    [OC_User_Seat_Info].x,[OC_User_Seat_Info].y
  FROM [OceaniaERP].[dbo].[OC_User_Seat_Info] left join 
 [OceaniaERP].[dbo].[OC_User_Info] on [OC_User_Info].SeatNumber = [OC_User_Seat_Info].SeatNumber  and [OC_User_Info].Status = 0
 where [OC_User_Seat_Info].[Floor] = 9 
 order by [OC_User_Seat_Info].SeatNumber

SQL中递归查询

with all_children as(
select user_db.displayName,user_db.EmployeeNo from [KPI].[dbo].[v_oa_okr_employee] user_db where employeeno = 218
union all
select user_db_children.displayName,user_db_children.EmployeeNo from [KPI].[dbo].[v_oa_okr_employee] user_db_children inner join all_children c on user_db_children.DirectManagerID = c.employeeno
) select * from all_children

替换指定字符

update tab set lieming = replace(lieming,'2011','2014');

将多行数据转为一行

      select 
    RoleId = stuff((
        SELECT ',' + RoleId
        FROM OC_User_Role
        WHERE UserID = 'Eric.Guo'
        FOR XML path('')
    ), 1, 1, '')
评论