一、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', '系统维护', 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` = '系统维护', `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
-- 当前时间的2个月之前
SELECT DATE_SUB(NOW(),INTERVAL 2 MONTH)
-- 当前时间
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
-- 表与表之间插入数据（从一张表中向另一张表中插入数据）
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()函数](https://www.cnblogs.com/yaoze2018/p/11318268.html)
将查询结果拼接成一个字符串，返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
eg：select contact('11','22','33');
返回结果：112233
