mysql常规操作

sql文档

SELECT
category_name
FROM
tc_productcategorydetail
WHERE
id
IN ( SELECT detail_id FROM tc_goods_categorydetail a, order_ticket o WHERE a.goods_id = o.goods_id )
===按月日查询=
1，按日
SELECT
DATE_FORMAT(RECORD_INSERT_TIME, ‘%Y-%m-%d’), count(1)
FROM
order_ticket
GROUP BY DATE_FORMAT(RECORD_INSERT_TIME, ‘%Y-%m-%d’);

2，按月
SELECT
DATE_FORMAT(RECORD_INSERT_TIME, ‘%Y-%m’), count(1)
FROM
order_ticket
GROUP BY DATE_FORMAT(RECORD_INSERT_TIME, ‘%Y-%m’);

=通过字段查询表=====
USE information_schema;
SELECT * FROM columns WHERE column_name=‘query_type’;

//查询与该表有联系的所有表
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME=‘tc_productcategorydetail’

//单表省市联动查询

select
a2.id as parent_id,
a2.areaName as parent_name,
a1.id as child_id,
a1.areaName as child_name
from area as a1
left join area as a2
on a1.parent_id = a2.id

// 左连接查询
SELECT p.id pId, p.category_name, s.id sId, s.parent_id s_parentId, s.category_name
FROM tc_productcategorydetail AS s LEFT JOIN tc_productcategorydetail AS p
ON s.parent_id = p.id ;

//循环插入
BEGIN
declare i int default 8;
declare j int default 1;
while j < 134 do
while i < 32 do
insert into time_range(reserve_date,range_tem,reserve_range, ins_id) values(concat(‘2020-05-’,i),
‘7:00-8:00,8:00-9:00,9:00-10:00,10:00-11:00,11:00-12:00,12:00-13:00,13:00-14:00,14:00-15:00,15:00-16:00,16:00-17:00,17:00-18:00,18:00-19:00’,
‘7:00-8:00,8:00-9:00,9:00-10:00,10:00-11:00,11:00-12:00,12:00-13:00,13:00-14:00,14:00-15:00,15:00-16:00,16:00-17:00,17:00-18:00,18:00-19:00’,
j
);
set i = i + 1;
end while;
set i = 8;
set j = j + 1;
END WHILE;
END

//插入多条数据
insert into time_range(ins_id) select id from instruments

//***自动定时清理数据
//可预约时间查询
select distinct t.range,t.unreserve_date from time_range t, instruments_reserve r
WHERE t.ins_id = r.ins_id and t.unreserve_date = r.unreserve_date;

select distinct t.range, t.unreserve_date from time_range t, instruments_reserve r
WHERE t.ins_id = r.ins_id and t.unreserve_date = r.unreserve_date

//去重复
select distinct t.* from time_range t, instruments_reserve r
WHERE t.ins_id = r.ins_id and t.reserve_date

//

//模糊查询
select *
from instruments_reserve r
where r.ins_id NOT IN (
select r.ins_id from instruments_reserve r
where r.reserve_date LIKE concat(CONCAT(“2020”))
and r.reserve_time_range LIKE concat(CONCAT("")));

– SELECT t.reserve_data FROM time_range t WHERE find_in_set(“2020”, t.reserve_data);
//查询某字段所在位置
select * from time_range t where position(“20” in t.reserve_data);

//不在的字段
select * from time_range t where t.id not in(
select id from time_range t where position(“2020” in t.reserve_data));

//更新数据
update time_range t set t.reserve_data = concat(t.reserve_data,’,“ins_id”:“10”,“day”,“2020-4-21”’) where position(“8:00 -” in t.range);
//当concat（中有字段为空值时，返回空值） 解决办法：CONCAT(IFNULL(字段1,’’)）

update time_range
set reserve_date = concat(reserve_date, ‘,’ , “2020-4-21”), ins_id = ‘7’
where position(“15:00 -” in time_range.range);

//================================
update time_range t,instruments_reserve r
set t.reserve_date = concat(r.reserve_date, ‘; ‘, IFNULL(t.reserve_date,’’)),
t.ins_id = concat(r.ins_id, ‘; ‘, IFNULL(t.ins_id,’’))
WHERE position(r.reserve_time_range in t.range);

//原有字符串上更新数据
UPDATE time_range SET reserve_date= CONCAT(’“user”’,",",reserve_date),ins_id= CONCAT(‘0001’,",",ins_id);

//两表互插
INSERT INTO instruments(time) SELECT reserve_date
FROM time_range t, instruments ins
where t.ins_id = ins.id;

– update time_range set time_range.reserver_time = CONCAT(time_range.reserver_time,’,’,’"{2020-4-22}"’);

//触发器
CREATE TRIGGER reserve_trig
AFTER UPDATE on instruments_reserve
for EACH ROW

BEGIN
update time_range t,instruments_reserve r
set t.reserve_date = concat(r.reserve_date, ‘; ‘, IFNULL(t.reserve_date,’’)),
t.ins_id = concat(r.ins_id, ‘; ‘, IFNULL(t.ins_id,’’))
WHERE position(r.reserve_time_range in t.range);
END;

//触发器，删除前插入另一个表
BEGIN
update time_range t, instruments_reserve r
set result_time= concat(old.reserve_time_range, '; ’ , result_time)
where t.reserve_date = r.reserve_date
and t.ins_id = r.ins_id;
END

//触发器 模糊查询添加后，删除指定字段
BEGIN
update time_range t, instruments_reserve r
set result_time= REPLACE (result_time, new.reserve_time_range, ‘’)
where t.reserve_date = r.reserve_date
and t.ins_id = r.ins_id
and result_time like concat(’%’,new.reserve_time_range,’%’);
END

//6，字段完全匹配
select * from time_range where id not in
(select id from time_range where 17<!-number-> in (ins_id));

//7, 替换字段中部分内容
UPDATE base_cities SET NAME=REPLACE(NAME,‘市’,’’) WHERE id>0

//8，varchar转换为date
SELECT reserve_date
FROM time_range
WHERE str_to_date(reserve_date, ‘%Y-%m-%d’) BETWEEN str_to_date(‘2020-04-01’, ‘%Y-%m-%d’) AND str_to_date(‘2028-04-5’, ‘%Y-%m-%d’)
ORDER BY str_to_date(reserve_date, ‘%Y-%m-%d’)

//一个字段是否包含另一一段
INSTR(t.reserve_range,’%’+r.reserve_time_range+’%’)

根据字段查询数据库表
select table_schema,table_name from information_schema.columns where column_name = ‘user_active’

9：最近2天数据 // 1小时
select * from instruments_reserve where create_date > DATE_SUB(CURDATE(), INTERVAL 2 DAY);
select * from instruments_reserve where create_date > DATE_SUB(NOW(),INTERVAL 1 HOUR);

json 字段=========
1：查询
select * from goods where gc_id -> ‘$.val’ = ‘eb7f320c0421436c81dac59688cae3fc’