concat函数:拼接字段内容
更新 课时费表
update tb_teacher_classtime_fee set file_name_save =concat('/classTimeFee/2019-05-30/',file_name_save);
replace 替换字段内的某些内容
更新教师头像
UPDATE tb_teacher set avatar_url = replace(avatar_url,"https://avatar.izhikang.com/","https://izhikang-img-1256037416.cos.ap-beijing.myqcloud.com/teacherAvatar/2019-06-03/") WHERE avatar_url like "https://avatar.izhikang.com/%";
#获取入职时间超过一年的老师
SELECT id,username,create_time FROM teachers WHERE year(now())-year(create_time)>1;
# 获取最早的一个订单
SELECT id,created_datetime,user_id FROM orders ORDER BY created_datetime ASC LIMIT 0,1;
# 获取订单量在1-5之间的学员
SELECT user_id,count(user_id) as s FROM orders GROUP BY user_id HAVING s BETWEEN 1 AND 5 ;
# 获取订单量前20的学生
SELECT DISTINCT count(user_id) as s,user_id FROM orders GROUP BY user_id ORDER BY s DESC LIMIT 0,20;
#查询订单数量前20的学员
select user_id, count(user_id) as c FROM orders GROUP BY user_id having c >=(select min(s) from (select DISTINCT count(user_id) as s from orders group by user_id order by s desc limit 200) as a) order by c desc;
#查询订单量前20的不重复订单量
select * from (SELECT DISTINCT count(user_id) as s,user_id FROM orders GROUP BY user_id ORDER BY s DESC LIMIT 0,20) as a group by s ORDER BY s DESC;
1.当列表数据需要去重时
用 count(distinct(id)) as cnt 获取总数,且不能使用count 函数,会导致distinct去重失效
获取数据时,用group by 进行去重处理,因为distinct 不支持多字段查询。
2.当多表联查,速度过慢甚至超时的时候,可以选择分表查询,
且分表查询时,避免超时,尽量使用批量查询。
用 array_column 获取前sql与后sql的关联字段,进行批量查询
循环数组,用关联字段作为下标,赋值查询到的数据。可使用&$value。
3. 使用顺序:where 、group by 、having、order by
4.当需去重且取最新记录,例如最新聊天记录
需先group by 取到去重之后的记录,循环数据,再次查询取到最新记录
查询同一张表两次
5.bigint
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字)。存储大小为 8 个字节。
P.S. bigint已经有长度了,在mysql建表中的length,只是用于显示的位数
int
从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据(所有数字)。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。
smallint
从 -2^15 (-32,768) 到 2^15 – 1 (32,767) 的整型数据。存储大小为 2 个字节。
tinyint:
一个微小的整数,支持 -128到127(SIGNED),0到255(UNSIGNED),需要1个字节存储