一、mysql基本查詢

1、查詢
select * from t_admin_info

2、插入
INSERT INTO `test_lib_dev`.`t_admin_info`(`admin_account`, `admin_name`, `pwd`, `gender`, `age`, `id_num`, `tel_num`, `mobile_phone`, `email`, `image`, `birth_date`, `address`, `role_id`, `library_code`, `library_name`, `status`, `hobby`, `created_by`, `created_dt`, `updated_by`, `updated_dt`) VALUES ('20199298631b261', '運維', '78dcf987fbd773afabd5669d38c586e9', '女', 20, '412700199102021051', '020-8888888', '18680368888', NULL, NULL, '2019-09-29', '綠地中央廣場', 1, '10001', '系統(tǒng)維護', 0, '運維', '1', '2019-09-29 23:33:18', '1', '2019-09-29 23:33:18');

3、更新
UPDATE `test_lib_dev`.`t_admin_info` SET `admin_name` = '運維', `pwd` = '78dcf987fbd773afabd5669d38c586e9', `gender` = '女', `age` = 20, `id_num` = '412700199102021051', `tel_num` = '020-8888888', `mobile_phone` = '18680368888', `email` = NULL, `image` = NULL, `birth_date` = '2019-09-29', `address` = '中央廣場', `role_id` = 1, `library_code` = '10001', `library_name` = '系統(tǒng)維護', `status` = 0, `hobby` = '運維', `created_by` = '1', `created_dt` = '2019-09-29 23:33:18', `updated_by` = '1', `updated_dt` = '2019-09-29 23:33:18' WHERE `admin_account` = '20199298631b261';
4、刪除
delete from t_admin_info where admin_account = '20204058631b262'

select count(*) from t_admin_info;
select count(admin_account) from t_admin_info;
-- 最大、最小的id
select max(id),min(id) from user
-- 當(dāng)前時間的2個月之前
SELECT DATE_SUB(NOW(),INTERVAL 2 MONTH) 
-- 當(dāng)前時間
SELECT NOW()
-- 默認為ASC ORDER BY 
SELECT * FROM user ORDER BY id DESC
-- 分組 GROUP BY 及別名tai
SELECT tai.gender,count(*) FROM t_admin_info tai GROUP BY tai.gender 
-- 使用HAVING 找出大于1的記錄
SELECT tai.gender,count(*) as cnt FROM t_admin_info tai GROUP BY tai.gender HAVING cnt > 1

-- 表與表之間插入數(shù)據(jù)（從一張表中向另一張表中插入數(shù)據(jù)）
insert into t_user_ar(name,age) select name,age from t_user;
-- 插入多條記錄
insert temp_table(province) values('江蘇省'),('廣東'),('浙江省'),('山東'),('山西省');    

二、添加字段和索引
    alter table qb_factory.upper_batch_order
--  在machine_num 字段后添加：AFTER，在 machine_num字段前：BEFORE
    add `machine_num` varchar(50) NOT NULL DEFAULT '' COMMENT '機臺編號' AFTER machine_id;
-- 添加索引  idx_parent_id   
    alter table qb_factory.upper_batch_order
    ADD  INDEX `idx_parent_id` (`parent_id`) USING BTREE;
-- case when
        CASE
                WHEN main.start_time > DATE_SUB(NOW(),INTERVAL 1 MONTH) THEN 1
        ELSE 0
        END AS flag,

sum() 求和
avg() 求平均
-- 如果kk.output_qty為空，則為0
IFNULL(kk.output_qty, 0)  

-- 生成插入語句
SELECT output_date,min(id) min ,max(id) max,count(1) cnt,concat('insert into qb_dev.output_summary_bak_20200102(factory_id, output_date, machine_id, machine_num, order_id, efficiency, output_qty, active, user_def1, user_def2, user_def3, user_def4, user_def5, user_def6, user_def7, user_def8, user_def9, user_def10, create_user, create_time, update_user, update_time, output_qty_new, shuttle, avg_speed, upper_axis_id, recalculate_type, device_group_id, maxlap, minlap, device_source ) select factory_id, output_date, machine_id, machine_num, order_id, efficiency, output_qty, active, user_def1, user_def2, user_def3, user_def4, user_def5, user_def6, user_def7, user_def8, user_def9, user_def10, create_user, create_time, update_user, update_time, output_qty_new, shuttle, avg_speed, upper_axis_id, recalculate_type, device_group_id, maxlap, minlap, device_source from qb_analysis.output_daily_summary_ar where output_date ="', output_date,'" and id >= ' ,min(id),' and id <= ',max(id),' ; ') from qb_dev.output_summary_ar group by output_date order by output_date asc;

# [Mysql之CONTACT()函數(shù)](https://www.cnblogs.com/yaoze2018/p/11318268.html)

將查詢結(jié)果拼接成一個字符串，返回結(jié)果為連接參數(shù)產(chǎn)生的字符串。如有任何一個參數(shù)為NULL ，則返回值為 NULL。
eg：select contact('11','22','33');
返回結(jié)果：112233