[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 ```