MySQL使用学习记录

---

• 2017.8.18 find_in_set函数
  find_in_set函数
  

  FIND_IN_SET(str,strlist)

假如字符串str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。
一个字符串列表就是一个由一些被‘,’符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则 FIND_IN_SET() 函数被优化，使用比特计算。
如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。 基本用法：

select FIND_IN_SET('2','1，2');返回2 select FIND_IN_SET('6','1'); 返回0 

关联查询：用(x,y,z)存储同表或另一张表中的字段值，如多个id（以逗号隔开），关联查询多条记录。

SELECT    *FROM    zz,    classify cWHERE    FIND_IN_SET(        c.id,        (            SELECT                zzid            FROM                zz            WHERE                zz.id = 1        )    )

结果：

---

• 2017.8.21 truncate table清空表并重置自增id
  使用truncate 表名清空某张表并重置该表的自增id，但无法操作有外键约束的表

---

• 2017.9.2记录 MySQL删除一个数据库中的所有表
  
  1. 用图形工具多选删除。
  2. 先用select语句获取所有表的删除语句，然后全选复制执行。有外键的需要先暂时关闭外键约束检查。

SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='数据库名';

set foreign_key_checks=0;     //关闭外键检查set foreign_key_checks=1;  //打开外键检查//执行第一条select的复制结果drop table account;drop table address;drop table admin;drop table admin_authority;………

---

• 2017.11.1 Date相关，按天、周、月、年统计

知识关键词：DATE_FORMAT select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;  select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;  select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months; DATE_FORMAT(date,format) 

根据format字符串格式化date值。下列修饰符可以被用在format字符串中： %M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位   www.2cto.com  %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩写的月份名字(Jan……Dec) %j 一年中的天数(001……366) %H 小时(00……23) %k 小时(0……23) %h 小时(01……12) %I 小时(01……12) %l 小时(1……12) %i 分钟, 数字(00……59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一个星期中的天数(0=Sunday ……6=Saturday ） %U 星期(0……52), 这里星期天是星期的第一天 %u 星期(0……52), 这里星期一是星期的第一天 %% 一个文字“%”。

示例

当天SELECT    *FROM    ordersWHERE    TO_DAYS(now()) = TO_DAYS(pay_datetime)SELECT    *FROM    ordersWHERE    date(now()) = date(pay_datetime)一周SELECT    *FROM    `orders`WHERE    date(        date('2017-10-13 18:31:24') - WEEKDAY(            date('2017-10-13 18:31:24')        )    ) <= pay_datetimeAND pay_datetime <= DATE_ADD(    date(        date('2017-10-13 18:31:24') - WEEKDAY(            date('2017-10-13 18:31:24')        )    ),    INTERVAL 6 DAY);一周 (yearweek(date,first)),first参数取1即可SELECT    *FROM    ordersWHERE    yearweek('2017-11-01', 1) = yearweek(pay_datetime, 1);SELECT    *FROM    `orders`WHERE    date(pay_datetime) > last_day('2017-10-02');当月第一天SELECT    date(        concat(DATE_FORMAT('2017-10-02', '%Y-%m'),'-','1')    );一月SELECT    *FROM    ordersWHERE    DATE_FORMAT(pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');一月SELECT    *FROM    `orders`WHERE    date(        concat(            YEAR ('2017-10-13 18:31:24'),            '-',            MONTH (curdate()),            '-',            '1'        )    ) < pay_datetimeAND pay_datetime < LAST_DAY('2017-10-13 18:31:24');当年SELECT    *FROM    ordersWHERE    YEAR (now()) = YEAR (pay_datetime)

---

• 2017.11.21 statistics report

//每月销售报总表     SELECT    g.goods_name goodsName,    g.m_price price,    sum(sku_count) saleCount,    count(DISTINCT(og.order_id)) orderCount,    sum(sex = '男') AS male,    sum(sex = '女') AS female,    sum(        (YEAR(now()) - YEAR(birthday)) < 20    ) ltTwenty,    sum(        (YEAR(now()) - YEAR(birthday)) >= 20        AND (YEAR(now()) - YEAR(birthday)) <= 30    ) twenty2thirty,    sum(        (YEAR(now()) - YEAR(birthday)) > 30        AND (YEAR(now()) - YEAR(birthday)) <= 40    ) thirty2forty,    sum(        (YEAR(now()) - YEAR(birthday)) > 40        AND (YEAR(now()) - YEAR(birthday)) <= 50    ) forty2fifty,    sum(        (YEAR(now()) - YEAR(birthday)) > 50    ) gtFiftyFROM    order_goods ogINNER JOIN goods g ON (og.goods_id = g.id)INNER JOIN orders o ON (og.order_id = o.id)INNER JOIN muser m ON (o.muser_id = m.id)WHERE    DATE_FORMAT(pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')GROUP BY    goodsNameORDER BY    saleCount DESC;地区销售占比表 SELECT    a.city,    sum(total_price) turnover,    count(*) orderCount,    ROUND(        (            sum(total_price) / (                SELECT                    sum(total_price)                FROM                    orders                WHERE                    DATE_FORMAT(orders.pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')            )        ),        4    ) cityTurnoverRate,    count(*) orderCount,    ROUND(        (            count(*) / (                SELECT                    count(*)                FROM                    orders                WHERE                    DATE_FORMAT(orders.pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')            )        ),        4    ) cityCountRate,    sum(sex = '男') AS male,    sum(sex = '女') AS female,    sum(        (YEAR(now()) - YEAR(birthday)) < 20    ) ltTwenty,    sum(        (YEAR(now()) - YEAR(birthday)) >= 20        AND (YEAR(now()) - YEAR(birthday)) <= 30    ) twenty2thirty,    sum(        (YEAR(now()) - YEAR(birthday)) > 30        AND (YEAR(now()) - YEAR(birthday)) <= 40    ) thirty2forty,    sum(        (YEAR(now()) - YEAR(birthday)) > 40        AND (YEAR(now()) - YEAR(birthday)) <= 50    ) forty2fifty,    sum(        (YEAR(now()) - YEAR(birthday)) > 50    ) gtFiftyFROM    orders oINNER JOIN muser m ON (o.muser_id = m.id)INNER JOIN address a ON (m.id = a.muser_id)WHERE    a.tag = '0'AND DATE_FORMAT(o.pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')GROUP BY    a.cityORDER BY    orderCount DESC 每月区域销售表 SELECT        g.goods_name goodsName,        g.m_price price,        sum(sku_count) saleCount,        a.city,        sum(sex = '男') AS male,        sum(sex = '女') AS female,        sum(            (YEAR(now()) - YEAR(birthday)) < 20        ) ltTwenty,        sum(            (YEAR(now()) - YEAR(birthday)) >= 20            AND (YEAR(now()) - YEAR(birthday)) <= 30        ) twenty2thirty,        sum(            (YEAR(now()) - YEAR(birthday)) > 30            AND (YEAR(now()) - YEAR(birthday)) <= 40        ) thirty2forty,        sum(            (YEAR(now()) - YEAR(birthday)) > 40            AND (YEAR(now()) - YEAR(birthday)) <= 50        ) forty2fifty,        sum(            (YEAR(now()) - YEAR(birthday)) > 50        ) gtFifty    FROM        order_goods og    INNER JOIN goods g ON (og.goods_id = g.id)    INNER JOIN orders o ON (og.order_id = o.id)    INNER JOIN muser m ON (o.muser_id = m.id)    INNER JOIN address a ON (m.id = a.muser_id)    WHERE        DATE_FORMAT(pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')    AND a.tag = '0'    GROUP BY        goodsName,        a.city    ORDER BY        goodsName,        saleCount DESC;//订单年月查询SELECT    sum(summ),    paymonthflagFROM    (        SELECT            sum(total_price) summ,            count(*) cnt,            count(pay_datetime),            MONTH (pay_datetime) paymonthflag,            MONTH (gmt_datetime) monthflag        FROM            orders        WHERE            YEAR (gmt_datetime) = 2017        GROUP BY            monthflag,            paymonthflag    ) AS zzWHERE    paymonthflag IS NOT NULLGROUP BY    paymonthflag;SELECT    sum(total_price) monthPay,    sum(delivery_money) monthDelivery,    sum(coupon_val) monthCoupon,    count(*) monthPayCount,    MONTH (pay_datetime) monthPayFlagFROM    ordersWHERE    YEAR (pay_datetime) = 2017GROUP BY    monthPayFlag;SELECT    sum(total_price)FROM    ordersWHERE    YEAR (pay_datetime) = 2017;//购买次数 SELECT    count(1) purchaseCountFROM    (        SELECT            COUNT(1) countP        FROM            muser m        LEFT JOIN orders o ON (m.id = o.muser_id)        WHERE            o.pay_type IS NOT NULL        AND m.user_name IS NOT NULL        GROUP BY            m.id        HAVING            count(m.id) >= 2    ) t

---

• MySQL sum()函数参数为表达式

原数据：

select * from student;

sum条件表达式命令：

SELECT class_id, SUM(sex = '男') AS male, SUM(sex = '女') AS female FROM studentGROUP BY class_id

结果：

---