Mysql

一、MySQL用命令行复制表的方法

参考网址：https://www.cnblogs.com/zhangjpn/p/6231662.html

常见面试题：https://www.cnblogs.com/frankielf0921/p/5930743.html

1、只复制表结构到新表

CREATE TABLE 新表 LIKE 旧表 ;

2、复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表

二、查询数据带分页

select * from orders_history where type=8 order by id limit 10000,10;

3、将t2表的查询结果插入到t1表中

INSERT INTO t1(id,username,score)
SELECT t2.id,t2.username,t2.score FROM t2 where t2.username = 'lucy'

4、表联合更新

UPDATE t_zeus_apply a,
    t_zeus_provider_ref b 
SET 
    a.city_id = b.city_id,
    a.city_name = b.city_name
WHERE
    a.supplier_company_id = b.supplier_company_id;

5、Case When THEN END 用法

SELECT 
    apply_id,
    CASE
        WHEN status = 20 THEN '审核通过'
        WHEN status = 30 THEN '审核拒绝'
        WHEN status = 40 THEN '审核撤销'
        WHEN status = 10 THEN '审核中'
        ELSE '其它'
    END status,
    state_name
FROM
    t_zeus_apply
ORDER BY apply_id;

6、加字段

alter table t_zeus_apply add `city_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '城市id';
alter table domino_instance add `source` varchar(50) NOT NULL DEFAULT '' COMMENT '来源';

7、修改字段

alter table domino_transfer modify column `notify` varchar(50) NOT NULL DEFAULT '' COMMENT '事件标识';

8、条件查询

SELECT
    COUNT(*) AS total,
    SUM(IF(state_code =  "WANSHANZILIAO", 1, 0)) AS 待上传资料总数,
    SUM(IF(state_code =  "WANSHANZILIAO", 1, 0)) AS 待上传资料总数,
    SUM(IF(state_code = "BUCHONGZILIAO", 1, 0)) AS 审核驳回总数,
    SUM(IF(state_code = "FENGKONGMOXINGSHENHE", 1, 0)) AS 待系统审核总数,
    SUM(IF(state_code = "SHANGCHUANXIANCHANGZHOUYANBAOGAO", 1, 0)) AS 待城市审核总数,
    SUM(IF(state_code = "FENGKONGCHUSHEN", 1, 0)) AS 待风控初审总数,
    SUM(IF(state_code = "FENGKONGRENGONGSHENHE", 1, 0)) AS 待风控复审总数,
    SUM(IF(state_code = "ZONGBUSHENHE", 1, 0)) AS 待总部审核总数,
    SUM(IF(state_code = "SHANGCHUANHETONGHUOBAOZHENGJIN", 1, 0)) AS 待准入凭证上传总数,
    SUM(IF(state_code = "ZONGBUQUEREN", 1, 0)) AS 待总部确认总数
FROM
    t_zeus_apply

9、条件查询二维

SELECT city_name as 所属城市,
SUM(IF(state_code = "WANSHANZILIAO", 1, 0)) AS 待上传资料总数,
SUM(IF(state_code = "BUCHONGZILIAO", 1, 0)) AS 审核驳回总数,
SUM(IF(state_code = "FENGKONGMOXINGSHENHE", 1, 0)) AS 待系统审核总数,
SUM(IF(state_code = "SHANGCHUANXIANCHANGZHOUYANBAOGAO", 1, 0)) AS 待城市审核总数,
SUM(IF(state_code = "SHANGCHUANHETONGHUOBAOZHENGJIN", 1, 0)) AS 待准入凭证上传总数
FROM
t_zeus_apply where city_id != 4 group by city_name;

9、length

SELECT LENGTH('www.111cn.net');

10、近N天

今天：select * from 表名 where to_days(时间字段名) = to_days(now());
昨天：SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1;
近7天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);
近30天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名);
本月：SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
上一月：SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now( ), '%Y%m' ) , date_format( 时间字段名,'%Y%m')) =1;
本季度：select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
上季度：select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
本年：select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
上年：select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
这周：SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
上周：SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
当月：select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

11、distinct 多列

INSERT INTO t1(id,username,score)
SELECT t2.id,t2.username,t2.score FROM t2 where t2.username = 'lucy' group by t2.username;