[TOC] ## 删除某个字段重读的记录 ``` delete from hs_user_tree where tree_id not in ( select t.minno from ( select min(tree_id) as minno from hs_user_tree GROUP BY user_id ) t ) ``` ## 对表中的字段进行替换 ``` //对write_price表中所有phone的157 替换为168 update write_price set phone=replace(phone, 157, 168); ``` ## 不精确的count `SHOW TABLE STATUS like "im_msg_201905"` ## 判断表是否存在 `SELECT 1 from im_msg_2018 limit 1` ## update 只设置一次起作用 `update socre=new_xxx form user where socre=xxx` ## 循环执行的sql操作的 过程 ``` DROP PROCEDURE IF EXISTS proc_initData; DELIMITER $ CREATE PROCEDURE proc_initData() BEGIN DECLARE i INT DEFAULT 1; DECLARE c VARCHAR(10); DECLARE dept VARCHAR(10); DECLARE tree_id VARCHAR(10); set c="hh3k2"; set dept="7700164"; set tree_id="05D6CA4F-50AC-984C-87AE-D6727AB62"; WHILE i<=10 DO INSERT INTO hs_user(user_id,user_login,user_name,user_status,USER_PWD,USER_PWD_TYPE) VALUES(concat(c,i),concat(c,i),concat(c,i),1,'202cb962ac59075b964b07152d234b70',1); insert into hs_user_tree(tree_id,dept_id,user_id,user_name) values(concat(tree_id,i),dept,concat(c,i),concat(c,i)); SET i = i+1; END WHILE; END $ CALL proc_initData(); ``` ## HAVING 用法 用于配合group方法完成从分组的结果中筛选 ``` select user_id from im_log_201905 GROUP BY USER_ID HAVING count(user_id)>500 ``` ## "_" 单个通配符 表示查询第二个字母为"d"的"user_login" ``` SELECT * FROM `hs_user` where user_login like "_d%" ``` 表示查询"name"中为"o" 被两个字母隔开的情形 ``` SELECT name FROM world WHERE name LIKE '%o__o%' ``` 查看"name"为4个长度的名字 ``` SELECT name FROM world WHERE name LIKE '____' ``` ## "%"通配符 ``` select capital ,name FROM world WHERE capital like concat('%',name,'%') ``` 发现所有首都和其国家名字，而首都要有国家名字中出现。 ## 可以在 select 中进行子查询 ``` SELECT NAME, population, population / ( SELECT population FROM world WHERE NAME = 'Germany' ) FROM world WHERE continent = 'Europe' ``` 查询欧洲人员,并以德国人口的百分比显示 ## All / any 用于子查询: - any 是任意一个 - all 是所有 ``` select name from world where gdp > ALL(select gdp from world where continent='Europe' ) ``` 列出gdp 比欧洲所有国家都要高的国家 ## group_concat - 分组排序功能 获取所有用户最近的一笔消费 ```sql //通过对 money的create_time排序来获取最近一笔输出 select t.user_id, group_concat( t.money order by t.create_time desc ) moneys ... group by t.user_id ```