整理了一些MySQL的常識和小技巧

》navicat premium 快捷鍵

1.ctrl+q 打開查詢窗口

2.ctrl+/ 註釋sql語句

3.ctrl+shift +/ 解除註釋

4.ctrl+r 運行查詢窗口的sql語句

5.ctrl+shift+r 只運行選中的sql語句

6.F6 打開一個mysql命令行窗口

7.ctrl+l 刪除一行

8.ctrl+n 打開一個新的查詢窗口

9.ctrl+w 關閉一個查詢窗口

》SELECT子句順序：

SELECT子句順序

SELECT要返回的列或表達式

FROM從中檢索數據的表

WHERE行級過濾

GROUNP BY分組說明

HAVING組級過濾

ORDER BY輸出排序順序

LIMIT要檢索的行數

》數據類型

整數類型：int、integer、tinyint、smallint。

布爾類型：bit、boolean。

字符型：varchar、tinytext、longtext、text、longvarchar、char、nchar、nvarchar、clob、nclob。

數值類型：decimal、numeric、real、float、double。

時間日期類型：datetime、date、timestamp、time。

二進制類型：blob、tinyblob、longblob、binary、varbinary、longvarbinary、image。

》mysql 執行狀態分析 show processlist

show processlist；

kill id；

show index from xxx;查看錶的索引

》刪除記錄之前-切記要檢查是不是存在該記錄

刪除一條記錄時，一定要先檢查該記錄是不是存在，如果為空值，可能會刪掉整個表

原因是第一次刪掉後，第二次數據庫中已經不存在該值，結果就把整個表刪了。。。。。。

》INTERVAL使用

》mysql 數據相除並保留2為小數點

decimal(a,b)

a指定指定小數點左邊和右邊可以存儲的十進制數字的最大個數，最大精度38。

b指定小數點右邊可以存儲的十進制數字的最大個數。小數位數必須是從 0 到 a之間的值。默認小數位數是 0。

SELECT CAST(CAST(112500 AS DECIMAL(18,2)) / CAST(46408 AS DECIMAL(18,2)) AS DECIMAL(18,2))

》多表查詢：

SELECT

A.ID,A.NUMBER,A.PRICE,A.ORDER_TIME,

B.USER_ID,B.STARTIME,B.STOPTIME,

C.CHANNEL_PAY,D.COMPANY

FROM

D表 D

LEFT JOIN A表 A ON A.COMPANY_ID = D.UID

LEFT JOIN B表 B ON B.COMPANY_ID = D.UID

LEFT JOIN C表 C ON C.COMPANY_ID = D.UID

有條件的再加行：

WHERE ..........

排序(如A表的ID由大到小排):

ORDER BY A.ID DESC

》case when 來實現批量update

（單個）

UPDATE categories

SET display_order =

CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END

WHERE id IN (1,2,3)

（多個）

UPDATE categories

SET display_order =

CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END,

title =

CASE id

WHEN 1 THEN 'New Title 1'

WHEN 2 THEN 'New Title 2'

WHEN 3 THEN 'New Title 3'

END

WHERE id IN (1,2,3)

》子查詢總結

- 子查詢需用括號包裹。

-- from型

from後要求是一個表，必須給子查詢結果取個別名。

- 簡化每個查詢內的條件。

- from型需將結果生成一個臨時表格，可用以原表的鎖定的釋放。

- 子查詢返回一個表，表型子查詢。

select * from (select * from tb where id>0) as subfrom where id>1;

-- where型

- 子查詢返回一個值，標量子查詢。

- 不需要給子查詢取別名。

- where子查詢內的表，不能直接用以更新。

select * from tb where money = (select max(money) from tb);

-- 列子查詢

如果子查詢結果返回的是一列。

使用 in 或 not in 完成查詢

exists 和 not exists 條件

如果子查詢返回數據，則返回1或0。常用於判斷條件。

select column1 from t1 where exists (select * from t2);

-- 行子查詢

查詢條件是一個行。

select * from t1 where (id, gender) in (select id, gender from t2);

行構造符：(col1, col2, ...) 或 ROW(col1, col2, ...)

行構造符通常用於與對能返回兩個或兩個以上列的子查詢進行比較。

1.用於過濾

>SELECT cust_id

FROM orders

WHERE order_num

IN (SELECT order_num FROM orderitems)

2.作為字段

>SELECT cust_name,

cust_state , (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders

FROM customers

ORDER BY cust_name

》mysql 連接表 一般大表放在前面

mysql 連接表 一般大表放在前面

例：在t_team表中生成1000條數據，在t_people表中生成100000條數據。

方法一：select * from t_people p left join t_team t on p.team_id=t.id order by p.pname limit 10; [語句①]

方法二： select * from t_people p left join t_team t on p.team_id=t.id limit10; [語句②]

方法三：select * from t_people p order by p.pname limit 10; [語句③]

方法四：select * from (select * from t_people p order by p.pname limit 10) p left join t_team t on p.team_id=t.id limit 10; [語句④]

[語句④] 效率最高

》group by

1.group by 可以包含任意數目的列

2.group by 中每個列都必須是檢索列或有效的表達式（但不能使聚集函數）

3.除聚集函數外，select語句中的每個列都必須在group by子句中出現

4.如果分組列有Null值，Null將作為一個分組返回

5.group by 子句必須出現在where子句之後, order by 之前

》where和having區別

where在分組前過濾，having在分組後過濾

having 字段必須是查詢出來的，where 字段必須是數據表存在的。

where 不可以使用字段的別名，having 可以。因為執行WHERE代碼時，可能尚未確定列值。

where 不可以使用合計函數。一般需用聚合函數才會用 having

》優化GROUP BY語句

如果查詢中包括group by但用戶想要避免排序結果的消耗，則可以使用order by null禁止排序

有些情況下，可以使用連接來替代子查詢。

》left join on 後跟and和where區別

通過多表聯查查詢數據時，數據庫會產生一箇中間臨時表，然後將臨時表返回給用戶。

1、ON條件是在生成臨時表時使用的條件，它不管ON中的條件是否為真，都會返回左邊表中的記錄；

2、WHERE條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有LEFT JOIN的含義（必須 返回左邊表的記錄）了，條件不為真的就全部過濾掉；

3、AND 是過濾之後再連接，WHERE是連接之後再過濾。

4、而inner jion沒這個特殊性，則條件放在on中和where中，返回的結果集是相同的。

》UNION自動去除重複行；UNION ALL 重複行保留

》mysql註釋

# 到該行結束、-- 到該行結束 以及 /* 行中間或多個行 */ 的註釋方格：

mysql> SELECT 1+1; # 這個註釋直到該行結束

mysql> SELECT 1+1; -- 這個註釋直到該行結束

mysql> SELECT 1 /* 這是一個在行中間的註釋 */ + 1;

注意 -- (雙長劃) 註釋風格要求在兩個長劃後至少有一個空格！

》

-- 數值函數

abs(x) -- 絕對值 abs(-10.9) = 10

format(x, d) -- 格式化千分位數值 format(1234567.456, 2) = 1,234,567.46

ceil(x) -- 向上取整 ceil(10.1) = 11

floor(x) -- 向下取整 floor (10.1) = 10

round(x) -- 四捨五入去整

mod(m, n) -- m%n m mod n 求餘 10%3=1

pi() -- 獲得圓周率

pow(m, n) -- m^n

sqrt(x) -- 算術平方根

rand() -- 隨機數

truncate(x, d) -- 截取d位小數

-- 時間日期函數

adddate() 增加一個日期-天或周

addtime() 增加一個時間

curdate() 返回當前日期

curtime() 返回當前時間

date() 返回日期時間的日期部分

datediff() 計算兩個日期差

date_add() 高度靈活的日期運算函數

day() 返回一個日期的天數部分

dayofweek() 對於一個日期，返回對應的星期幾

now(), current_timestamp(); -- 當前日期時間

date('yyyy-mm-dd hh:ii:ss'); -- 獲取日期部分

time('yyyy-mm-dd hh:ii:ss'); -- 獲取時間部分

date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化時間

unix_timestamp(); -- 獲得unix時間戳

from_unixtime(); -- 從時間戳獲得時間

-- 常用字符串函數

length(str) 字符串長度

locate(substr,str) 返回子串 substr 在字符串 str 中第一次出現的位置。如果子串 substr 在 str 中不存在，返回值為 0

LOCATE(substr,str,pos) 返回子串 substr 在字符串 str 中的第 pos 位置後第一次出現的位置。如果 substr 不在 str 中返回 0

left(str, length) 返回字符串左邊字符

right(str, length) 返回字符串右邊字符

substring(str, pos); substring(str, pos, len)

SUBSTRING_INDEX(str,delim,count)

mid(), substr() 等價於substring() 函數

ltrim(str) 去掉字符串左邊空格

rtrim(str) 去掉字符串右邊空格

trim(str) 去掉字符串兩邊空格

lower(str) 轉為小寫

upper(str) 轉為大寫

SELECT LOCATE(’bar’, ‘foobarbar’);#4

SELECT LOCATE(’bar’, ‘foobarbar’,5);#7

select substring(‘sqlhahaha.com‘, 4); #從字符串的第 4 個字符位置開始取，直到結束。

select substring(‘sqlhahaha.com‘, 4, 2);#從字符串的第 4 個字符位置開始取，只取 2 個字符。

select substring(‘sqlhahaha.com‘, -4);#從字符串的第 4 個字符位置（倒數）開始取，直到結束。

select substring(‘sqlhahaha.com‘, -4, 2);#從字符串的第 4 個字符位置（倒數）開始取，只取 2 個字符。

select substring_index(‘www.baidu.com.cn‘, ‘.‘, 2); #www.baidu#截取第二個 ‘.‘ 之前的所有字符。

select substring_index(‘www.baidu.com.cn‘, ‘.‘, -2); #com.cn #截取第二個 ‘.‘ （倒數）之後的所有字符。

select substring_index(‘www.baidu.com.cn‘, ‘.coc‘, 1);#www.baidu.com.cn #如果在字符串中找不到 delim 參數指定的值，就返回整個字符串

》MySQL中NULL和空值的區別：

判斷NULL用is null 或者 is not null。 sql語句裡可以用ifnull()函數來處理

判斷空字符串‘’，要用 ='' 或者 <>''。sql語句裡可以用if(col,col,0)處理，即：當col為true時(非null，及非'')顯示，否則打印0

mysql使用中一些小總結：

1、僅列出需要查詢的字段，這對速度不會明顯的影響，主要是考慮節省應用程序服務器的內存。

原來語句： select * from admin

優化為： select admin_id,admin_name,admin_password from admin

2、儘量避免在列上做運算，這樣導致索引失效。

原語句： select * from admin where year(admin_time)>2014

優化為： select * from admin where admin_time> '2014-01-01′

3、使用JOIN 時候，應該用小的結果驅動大的結果（left join 左邊表結果儘量小 如果有條件應該放到左邊先處理，right join 同理反向），同事儘量把牽涉到多表聯合的查詢拆分多個query（多個連表查詢效率低，容易到之後鎖表和阻塞）。

原來語句 select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10

優化為： select * from (select * from admin where admin_id>10) T1 lef join log on T1.admin_id = log.admin_id

4、注意LIKE 模糊查詢的使用， 避免使用 %% ,可以使用 後面帶% ，雙%是不走索引的。

原來語句： select * from admin where admin_name like ‘%de%'

優化為： select * from admin where admin_name >='de' and admin_nam

5、使用批量插入節省交互 （當如如果使用存儲過程來處理批量的sql 各種邏輯是更好的選擇）。

原來語句：insert into admin(admin_name,admin_password) values (‘test1′,'pass1′);

insert into admin(admin_name,admin_password) values (‘test2′,'pass2′);

insert into admin(admin_name,admin_password) values (‘test3′,'pass3′)

優化為： insert into admin(admin_name,admin_password) values(‘test1′,'pass1′),(‘test2′,'pass2′),(‘test3′,'pass3′)

6、limit 的基數比較大時使用between。

原來語句：select * from admin order by admin_id limit 100000,10

優化為： select * from admin where admin_id between 100000 admin 100010 order by admin_id

7、不要使用rand函數獲取多條隨機記錄。

原來語句： select * from admin order by rand() limit 20

優化為： select * from admin as t1 Join(select round(rand()*((select max(admin_id) from admin)-(select min(id) from admin))+(select min(id) from admin)) as id) as t2 where t1.id>=t2.id order by t1.id limit

8、避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

9. 不要使用 count(id) 建議使用 count(*) or count(1)。

10、不要做無謂的排序操作，而應該使用索引完成排序。

11.不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

12.很多時候用 exists 代替 in 是一個好的選擇：

select num from a where num in(select num from b)

閱讀更多 產品GEEK 的文章

關鍵字: 技術 常識 UNIX