MySql语法记录

示例表结构以及数据

1、LEFT JOIN：左连接，从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行

   例1：查询出user_info表中所有列，以及user_info_new表中对应用户的联系电话（如果有的话）。

   SELECT ui.*,uin.user_tel FROM user_info ui LEFT JOIN user_info_new uin ON ui.user_id = uin.user_id

    例2：查询出user_info表中所有列，以及user_info_new表中对应用户的电话号码不为空的数据。

SELECT ui.*,uin.user_tel FROM user_info ui LEFT JOIN user_info_new uin ON ui.user_id = uin.user_id WHERE uin.user_tel is not null or uin.user_tel !=''

例2不使用LEFT JOIN的写法：

SELECT ui.*,uin.user_tel FROM user_info ui,user_info_new uin  WHERE ui.user_id = uin.user_id and uin.user_tel is not null or uin.user_tel !=''

2、RIGHT JOIN：右连接，从右表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行

   例1：查询出user_info_new表中所有列，以及user_info表中对应用户的信息（如果有的话）。

SELECT * FROM user_info ui RIGHT JOIN user_info_new uin ON ui.user_id = uin.user_id

3、INNER JOIN：内连接，根据on后的条件关联两张表数据，只有两张表中匹配到数据才会有记录

    SELECT * FROM user_info ui INNER JOIN user_info_new uin ON ui.user_id = uin.user_id

4、UNION：用于合并两个或多个 SELECT 语句的结果集（不允许重复）

SELECT user_name FROM user_info ui UNION

SELECT user_name FROM user_info user_info_new

5、UNION ALL：用于合并两个或多个 SELECT 语句的结果集（允许重复）

SELECT user_name FROM user_info ui UNION ALL

SELECT user_name FROM user_info user_info_new

6、将多个查询结果拼接成一行显示

SELECT * FROM ( SELECT count( 1 ) AS 男性人数 FROM user_info WHERE sex = 1 ) a

LEFT JOIN ( SELECT count( 1 ) AS 女性人数 FROM user_info WHERE sex = 0 ) b ON 1 = 1

LEFT JOIN ( SELECT count( 1 ) AS 联系电话不为空 FROM user_info_new WHERE user_tel IS NOT NULL OR user_tel != '' ) c ON 1 =1

7、统计最近一周数据时，生成最近一周的日期

SELECT
        date_sub( curdate(), INTERVAL 1 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 2 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 3 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 4 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 5 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 6 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 7 DAY ) AS click_date 

8、统计最近一周的数据，结果为空就填充0

SELECT
    b.click_date,
    IFNULL( c.数1, 0 ) AS 数1,
    IFNULL( d.`数2`, 0 ) AS 数2,
    IFNULL( e.`数3`, 0 ) AS 数3 
FROM
    (
    SELECT
        date_sub( curdate(), INTERVAL 1 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 2 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 3 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 4 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 5 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 6 DAY ) AS click_date UNION ALL
    SELECT
        date_sub( curdate(), INTERVAL 7 DAY ) AS click_date 
    ) b
    LEFT JOIN (
    SELECT
        DATE_FORMAT( create_time, '%Y-%m-%d' ) AS cDate,
        COUNT(*) AS 数1 
    FROM
        `表1` 
    WHERE
        create_time >= '2021-11-17 00:00:00' 
        AND create_time < '2021-11-24 00:00:00' 
        AND is_delete = 0 
    GROUP BY
        DAY ( create_time ) 
    ORDER BY
        create_time ASC 
    ) c ON b.click_date = c.cDate
    LEFT JOIN (
    SELECT
        DATE_FORMAT( insert_time, '%Y-%m-%d' ) AS dDate,
        COUNT(*) AS 数2 
    FROM
        `表2` 
    WHERE
        insert_time >= '2021-11-17 00:00:00' 
        AND insert_time < '2021-11-24 00:00:00' 
        AND type = 1 
        AND is_delete = 0 
    GROUP BY
        DAY ( insert_time ) 
    ORDER BY
        insert_time ASC 
    ) d ON b.click_date = d.dDate
    LEFT JOIN (
    SELECT
        DATE_FORMAT( create_time, '%Y-%m-%d' ) AS eDate,
        COUNT(*) AS 数3 
    FROM
        `表3` 
    WHERE
        create_time >= '2021-11-17 00:00:00' 
        AND create_time < '2021-11-24 00:00:00' 
        AND is_delete = 0 
    GROUP BY
        DAY ( create_time ) 
    ORDER BY
        create_time ASC 
    ) e ON b.click_date = e.eDate 
ORDER BY
    b.click_date ASC

 9、计算两个时间相差多少分钟，并保留3位小数

SELECT round((UNIX_TIMESTAMP('2021-11-24 16:10:11')-UNIX_TIMESTAMP('2021-11-23 00:09:12'))/60,3)

10、用查询结果集更新表（INNER JOIN）

UPDATE user_info as u INNER JOIN
(
select b.user_id,b.user_name from user_info_new b
) as r ON u.user_id = r.user_id SET u.user_name = r.user_name

11、case when 统计符合条件数据

select count(1) as 总数,count(case when state in(4,5) then 1 else null end) as 已处理数 from 表1 where is_delete=0