MySQL数据库汇总
SELECT i.order_id AS orderId, GROUP_CONCAT(d.item_name, '×', d.num, CHAR(10 using utf8)) AS itemInfo, i.create_time AS createTime, i.user_name AS userName, i.user_phone AS userPhone, i.status AS status, i.total_amount AS totalAmount
FROM order_info i
LEFT JOIN order_detail d
ON i.order_id = d.order_id GROUP BY i.order_id ORDER BY i.order_id ASC;
select t1.order_no,GROUP_CONCAT(t2.barcode) as barCode from mt_bus_order_head as t1
left join mt_bus_order_detail as t2
on t1.id=t2.order_id
where t1.order_type=1
and t1.order_status=1
and t1.order_data_source=1
and t2.barcode is not NULL
and t1.trans_date >= '2022-08-02 00:00:00' and t1.trans_date <= concat('2022-08-02', ' 23:59:59')
and t1.order_no in ()
GROUP BY t1.order_no
select (CASE WHEN IFNULL(t2.is_sales,0)=0 THEN 'haha' ELSE 'hehe' END) as ss from mt_prod_goods as t2
select (case when t2.is_sales=0 THEN 'haha' ELSE 'hehe' END) as ss from mt_prod_goods as t2
insert into mt_org_store_type_config select id,store_no,`name`,type,creator,create_date,updater,update_date from mt_org_store
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 90 DAY),'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(trans_date,'%Y-%m-%d') from mt_bus_order_head where cust_id=1495232982760509441
select cust_id,count(1) from mt_bus_order_head where cust_id=1495232982760509441 and DATE_FORMAT(trans_date,'%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 90 DAY),'%Y-%m-%d') AND DATE_FORMAT(NOW(),'%Y-%m-%d')
-- 格式化当前日期 2020-04-07 23:23:23 date_format(now(),'%Y-%m-%d %H:%i:%s' )
-- 格式化当前时间 23:23:23 time_format(CURTIME(),'%H:%i:%s')
date_format(now(),'%Y-%m-%d %H:%i:%s' )
date_format(current_date()-1,'%Y-%m-%d')
1.给当前日期减一天
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d');
2.给当前日期加一天
SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d');
分组查询
select post_type,post_code,post_name,sort,id from sys_post where `status`=1 GROUP BY post_type,post_code,post_name,sort,id
查询更新
update sys_menu as a
INNER JOIN(
SELECT t1.id as id ,t2.field_value as fieldValue
from sys_menu as t1
left join sys_language as t2
on t2.table_id=t1.id
where t2.language = 'zh-CN'
) as b
on a.id=b.id
set a.menu_name=b.fieldValue
Insert into mt_bus_cust_order_num_log(cust_id,num) select cust_id,COUNT(1) as num from mt_bus_order_head where order_type in (1,2,17) and order_status=1 and trans_date>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) GROUP BY cust_id HAVING COUNT(1) > 0;
查询插入
语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
或者：Insert into Table2 select * from Table1
SELECT * from t_bank_trade_record where payment_order_no='10UM202106181045142089534722' for update;
update t set a = a + 100 where a = 4;
查询一年
select * FROM bl_user_settlement_record io where YEAR(io.create_time)=YEAR(NOW());
查询1～12个月
SELECT * FROM bl_user_settlement_record io where MONTH(io.create_time)=8
查询1～12 个月
select SUM(t1.user_consume_amount) as totalPrice
from bl_user_settlement_record t1
where t1.business_id=#{businessId} and date_format(t1.create_time,'%Y-%m')=#{date}
查询10天为提货订单
select id,
FROM_UNIXTIME(pay_time) as paytime ,
date_add(FROM_UNIXTIME(pay_time), INTERVAL 10 day) as pay10time,
NOW() as nowtime
from bl_order
where promote_state=1 and order_type=1 and
date_add(FROM_UNIXTIME(pay_time), INTERVAL 10 day)<= now()
order by create_time desc
select
GROUP_CONCAT(id)
from sunxing_work_order.task
where type = 'normal' #工单
and task_template_id = 100 # 审核中
and task_type_id = 101
and status = 'Process'
and create_time <'2020-07-16 00:00:00'
select date_format(create_time, '%Y-%m-%d') dat, count(*),
sum(work_indicator) as totalWorkIndicator,
sum(work_compelete_indicator) as totalWorkCompeleteIndicator
from merchants_work_indicator_history
WHERE work_type =#{recordType}
and DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
group by date_format(create_time, '%Y-%m-%d')
order by dat asc;
https://www.cnblogs.com/cxiaocai/p/11594151.html
SHOW STATUS LIKE '%Connection%';
SHOW VARIABLES LIKE '%max_connections%';
SHOW PROCESSLIST;
lock table student write; 加表锁
show open tables;查看表状态（是否被加锁）
内有有一个列为In_use为1的即为已有锁存在。
show status;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 58 |
| Threads_connected | 57 | ###这个数值指的是打开的连接数
| Threads_created | 3676 |
| Threads_running | 4 | ###这个数值指的是激活的连接数，这个数值一般远低于connected数值
数据库性能优化
1.建立索引
2.多表关联进行单表查询
3.数据查询where条件进行从右先过滤大的条件
5、数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，MySQL有自带的binlog实现 主从同步；
数据处理
SELECT
count(1)
FROM
hzc.usr_profiles as u
LEFT JOIN hzc.tblorder AS t ON u.id=t.profile_id
where
u.mobile in (SELECT
a.mobile
FROM
opt.opt_user_travel_info as a) AND t.OrderID IS NULL or t.payment_status in(3,4);
-- SELECT count(TIMESTAMPDIFF(HOUR,now(),DATE_ADD(election_time, INTERVAL 15 DAY))) as hours
-- from apply_city_master
select count(id) from apply_city_master where HOUR(timediff(now(), expiry_time)) <= 24 and apply_status=2
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
非前导则可以使用索引
select name from user where name like 'zhangsan%'
- 1>.InnoDB支持事物，而MyISAM不支持事物
- 2>.InnoDB支持行级锁，而MyISAM支持表级锁
- 3>.InnoDB支持MVCC, 而MyISAM不支持
- 4>.InnoDB支持外键，而MyISAM不支持
- 5>.InnoDB不支持全文索引，而MyISAM支持。
- from (注:这里也包括from中的子语句)
- join
- on
- where
- group by(开始使用select中的别名，后面的语句中都可以使用)
- avg,sum.... 等聚合函数
- having
- select
- distinct
- order by
- limit
1. 怎么验证 mysql 的索引是否满足需求？
使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。
explain 语法：explain select * from table where type=1。
TIMESTAMPDIFF(DAY,now(),min(t2.expiry_time)),
SELECT `userspk`.`avatar` AS `user_avatar`,
`a`.`user_id`,
`a`.`answer_record`,
MAX(`score`) AS `score`
FROM (select * from pkrecord order by score desc) as a
INNER JOIN `userspk` AS `userspk`
ON `a`.`user_id` = `userspk`.`user_id`
WHERE `a`.`status` = 1
AND `a`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953'
GROUP BY `user_id`
ORDER BY `a`.`score` DESC
LIMIT 9; //不使用子查询
SELECT `userspk`.`avatar` AS `user_avatar`,
`pkrecord`.`user_id`,
`pkrecord`.`answer_record`,
`pkrecord`.`id`,
MAX(`score`) AS `score`
FROM pkrecord
INNER JOIN `userspk` AS `userspk`
ON `pkrecord`.`user_id` = `userspk`.`user_id`
WHERE `pkrecord`.`status` = 1
AND `pkrecord`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953'
GROUP BY `user_id`
ORDER BY `pkrecord`.`score` DESC
LIMIT 9;
MySql查询某一天的数据
select SUM(actual_deduction_price) from income_details where user_id=20 and create_time>='2019-07-25 00:00:00' and create_time<='2019-07-25 23:59:59';
select SUM(actual_deduction_price) from income_details where user_id=20 and create_time between '2019-07-25 00:00:00' and '2019-07-25 23:59:59';
SELECT SUM(actual_deduction_price) from income_details where user_id=20 and create_time > '2019-07-25' and create_time < '2019-07-26';
SELECT SUM(actual_deduction_price) from income_details where user_id=20 and (datediff(create_time,'2019-07-25')=0);
索引：B+，B-,全文索引
Mysql的索引是一个数据结构，旨在使数据库高效的查找数据。
常用的数据结构是B+Tree，每个叶子节点不但存放了索引键的相关信息还增加了指向相邻叶子节点的指针，这样就形成了带有顺序访问指针的B+Tree，做这个优化的目的是提高不同区间访问的性能。
什么时候使用索引：
经常出现在group by,order by和distinc关键字后面的字段
经常与其他表进行连接的表，在连接字段上应该建立索引
经常出现在Where子句中的字段
经常出现用作查询选择的字段
1）首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息。
DESC SELECT * FROM `user`
2）优化子查询
在MySQL中，尽量使用JOIN来代替子查询。因为子查询需要嵌套查询，嵌套查询时会建立一张临时表，临时表的建立和删除都会有较大的系统开销，而连接查询不会创建临时表，因此效率比嵌套子查询高。
3）使用索引
索引是提高数据库查询速度最重要的方法之一，使用索引的三大注意事项包括：
LIKE关键字匹配'%'开头的字符串，不会使用索引；
OR关键字的两个字段必须都是用了索引，该查询才会使用索引；
使用多列索引必须满足最左匹配。
4）分解表
对于字段较多的表，如果某些字段使用频率较低，此时应当将其分离出来从而形成新的表。
5）中间表
对于将大量连接查询的表可以创建中间表，从而减少在查询时造成的连接耗时。
6）增加冗余字段
类似于创建中间表，增加冗余也是为了减少连接查询。
7）分析表、检查表、优化表
分析表主要是分析表中关键字的分布；检查表主要是检查表中是否存在错误；优化表主要是消除删除或更新造成的表空间浪费。
分析表： 使用 ANALYZE 关键字，如ANALYZE TABLE user
select t1.id,t1.city_name,GROUP_CONCAT(t2.mobile) as mobile,t2.city_type,t2.apply_status
from city_config as t1
left join apply_city_master as t2
on t1.id=t2.city_id
where t2.apply_status=4
and t1.id=363
GROUP BY city_type
ORDER BY t2.create_time
select GROUP_CONCAT(t2.mobile) as mobile,t2.city_type from apply_city_master as t2 where t2.city_id=363 and t2.apply_status=4 GROUP BY t2.city_type ORDER BY t2.create_time
select
(
CASE
WHEN (a1.city_type=1) THEN a1.mobile
END
)AS citymaster,
(
CASE
WHEN (a1.city_type=2) THEN a1.mobile
END
)AS citymaster2,
(
CASE
WHEN (a1.city_type=3) THEN a1.mobile
END
)AS citymaster3
from
(
select GROUP_CONCAT(t2.mobile) as mobile,t2.city_type from apply_city_master as t2 where t2.city_id=363 and t2.apply_status=4 GROUP BY t2.city_type ORDER BY t2.create_time
) as a1
select
(
CASE
WHEN (a1.city_type=1) THEN a1.mobile
WHEN (a1.city_type=2) THEN a1.mobile
WHEN (a1.city_type=3) THEN a1.mobile
END
)AS citymaster
from
(
select GROUP_CONCAT(t2.mobile) as mobile,t2.city_type from apply_city_master as t2 where t2.city_id=363 and t2.apply_status=4 GROUP BY t2.city_type ORDER BY t2.create_time
) as a1
