SQL小技巧(LTS)

摘要:总结一些常用的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 <> ''
评论