常用SQL语句

1、update select
在sql server中，我们可是使用以下update语句对表进行更新：
update a set a.x= (select y from b) ;
但是在mysql中，不能直接使用set select的结果，必须使用inner join：
update a inner join (select y from b) c set a.x = c.y

UPDATE USER u INNER JOIN (SELECT user_id, SUM(price*num) AS sum FROM table) t
SET u.ammount=u.ammount + t.sum
WHERE u.id = t.user_id

2、统计查询结果作为一个字段
SELECT id, name
    , (SELECT sum(score) FROM s_examination AS b WHERE a.id = b.user_id) AS score
FROM b_student b
 

3、INSERT INTO和EXISTS联合使用
查询一条记录，如果存在于表中，就不执行插入操作，如果不存在与表中，执行插入操作
INSERT INTO s_user(user_id, sex, username)
(SELECT '111','男','张三'
FROM DUAL WHERE NOT EXISTS (SELECT username
                     FROM s_user
                     WHERE username = '张三'))
                    
                    
                    
4、COUNT和DISTINCT联合使用
查询表中不重复的记录条数
SELECT COUNT(*) count FROM
(SELECT DISTINCT username, sex
FROM s_user) s
如果是DISTINCT一个字段
SELECT COUNT(DISTINCT username)
FROM s_user
;


5、UPDATE和CASE WHEN THEN联合使用
当我们在更新记录的时候，常常会遇到条件不同的时候更新条件不同
一个区域可能有三级代理（也就是可能没有），如果有就将该区域的收益按照5:3:2的比例分给一级、二级和三级代理
UPDATE b_statistics s
SET agent_region_sum = (CASE (SELECT t.agent_region_id FROM b_area t WHERE t.id = s.trade_id LIMIT 1)
                        WHEN IS NULL THEN 0
                        WHEN '' THEN 0
                        ELSE 0.5
                        END)*(s.income_sum)
    , agent_city_sum = (CASE (SELECT t.agent_area_id FROM b_area t WHERE t.id = s.trade_id LIMIT 1)
                        WHEN IS NULL THEN 0
                        WHEN '' THEN 0
                        ELSE 0.2
                        END)*(s.income_sum)
    , agent_distract_sum = (CASE (SELECT t.agent_district_id FROM b_area t WHERE t.id = s.trade_id LIMIT 1)
                        WHEN IS NULL THEN 0
                        WHEN '' THEN 0
                        ELSE 0.3
                        END)*(s.income_sum)
WHERE agent_status = 0


6、查询商户距离
当前经纬度为$latitude，$longitude

SELECT *
    , ROUND(6378.138*2*ASIN(SQRT(POW(SIN(('.$latitude.'*PI()/180-latitude*PI()/180)/2),2)+COS('.$latitude.'*PI()/180)*COS(latitude*PI()/180)*POW(SIN(('.$longitude.'*PI()/180-longitude*PI()/180)/2),2)))*1000) distance
FROM b_merchant
ORDER BY distance

7、更新表有效期，添加1年
UPDATE table SET date = DATE_ADD(date, INTERVAL 1 YEAR)

如果要增加1天，则后面的 1 YEAR 变成 1 DAY，1月则是 1 MONTH

8、将表中性别男改成女，女改成男
UPDATE s_user
SET sex =
    CASE sex
        WHEN '男' THEN '女'
        ELSE '男'
    END

9、字段按照首字母排序

SELECT id, `name`, `level`
    , lower(case when `shortname` REGEXP '^[a-zA-Z]' then LEFT(`shortname`, 1)
        when `shortname` REGEXP '^[0-9]' then LEFT(`shortname`, 1)
        else ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(`shortname` USING gbk),1)),16,10),
                        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
                        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,
                        0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
                        'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P',
                        'Q','R','S','T','W','X','Y','Z') end) firstChar
FROM yu_area
WHERE `level` = 1
ORDER BY firstChar ASC

10、查询老用户分享新用户在有效时间段内最新购买推广时间和id

SELECT s.id, s.app_datetime, s.telephone, s.special_plane, s.address, s.countdown_day, s.release_datetime, s.user_id
    , SUBSTRING_INDEX(GROUP_CONCAT(p.add_datetime ORDER BY p.add_datetime DESC),',',1) add_datetime
    , SUBSTRING_INDEX(GROUP_CONCAT(p.publish_id ORDER BY p.add_datetime DESC),',',1) publish_id
FROM
(SELECT app_datetime, telephone, special_plane, address, id, user_id, release_datetime
    , DATEDIFF(release_datetime, '2019-01-04') countdown_day
FROM bc_subordinate) s
LEFT JOIN
(SELECT add_datetime, id publish_id, user_id
FROM b_publish) p
ON s.user_id = p.user_id AND s.app_datetime <= p.add_datetime AND s.release_datetime >= p.add_datetime
GROUP BY id

11、交换同一张表中的两个字段的值
UPDATE b_promotion a, b_promotion b
SET a.latitude = b.longitude , a.longitude = b.latitude
WHERE a.id = b.id AND a.latitude > 106

12、自定义变量

SELECT *, @rank := @rank+1
FROM b_bank b, (SELECT @rank := 20) p

13、批量更新

UPDATE mytable
    SET myfield = CASE other_field
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

MySql中4种批量更新的方法_命中的缘分的博客-CSDN博客_mysql批量更新

14、排名

SELECT id, export_poll_num, rank FROM
(SELECT id, export_poll_num,
@curRank := IF(@prevRank = export_poll_num, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := export_poll_num
FROM be_info i, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
WHERE @curRank < 4 
ORDER BY i.export_poll_num DESC) s

在MySQL中实现Rank高级排名函数 - study_php - 博客园

15、分组取每组前几条记录

SELECT t1.id, t1.title, t1.cate_name, t1.type_id FROM
(SELECT i.id, i.title, i.type_id, i.cate_id, i.dept_status, c.`name` cate_name, c.sort
FROM be_info i, be_cate c
WHERE i.batch_id = '5ec503ee438eb' AND i.cate_id = c.id) t1
LEFT JOIN be_info t2
ON t1.cate_id = t2.cate_id AND t1.id < t2.id
-- WHERE t1.dept_status = 0
GROUP BY t1.cate_id, t1.id
HAVING COUNT(*) < 3
ORDER BY t1.type_id, t1.sort DESC

SELECT i.id, i.title, i.type_id, i.cate_id, i.dept_status, i.prize_sort, i.add_datetime, c.`name` cate_name, c.sort
FROM be_info i, be_cate c
WHERE i.batch_id = '5ec503ee438eb' AND i.cate_id = c.id
AND 3 > (SELECT COUNT(*) FROM be_info ii WHERE ii.cate_id = i.cate_id AND i.id < ii.id)

https://www.jianshu.com/p/667e13d19cc9

16、更新同一张表中满足本表一定条件的记录

UPDATE tb_data  SET audit_time=NOW() 
WHERE id =( 
        select id from (
            SELECT b.id FROM tb_area a INNER JOIN tb_data b ON a.id=b.id WHERE a.id=171  AND b.account='***'
        ) as c
       )

17、按照字段首字母排序

select * from tableName order by convert( name using gbk) DESC

18、根据数组排序

SELECT *, FIND_IN_SET(id, '98,97,93,130') char_sort
                FROM table
WHERE id IN (98,97,93,130)
ORDER BY char_sort

19、复制表结构

复制表

不带索引、主键

CREATE TABLE 新表表名
SELECT * FROM 旧表表名 
WHERE 1 = 2

带索引、主键等

CREATE TABLE 新表名 LIKE 旧表名