1. 查詢技巧

1.1 邏輯操作符

主要是對(duì) and or in not的結(jié)合使用

select * from tb where field <2 and field2 >3;

1.2去重

使用關(guān)鍵字distinct，放在所有查詢字段的前面，表示后面的查詢字段組合不能重復(fù)

SELECT DISTINCT stuname,sex,class FROM stu_info;

1.3 設(shè)置結(jié)果查詢行數(shù)

使用limit語句啡专，limit m,n
limit m前m行广匙；
limit m,n從第m+1行開始斗搞，查詢n行捷泞；

select * from tb_name where xxx limit m,n

1.4查詢結(jié)果排序

使用ORDER BY field_name ASC/DESC # 升序/降序

1.5以上四條總結(jié)

SELECT * FROM tb_name WHERE xxx ORDER BY field_name ASC/DESC LIMIT m,n;

1.6 列別名及計(jì)算

• 有時(shí)為了便于區(qū)別不同表中相同字段或者減少書寫量钱反，可以給字段取一個(gè)別名：field_name as alias或者field_name alias家卖；
• 有些數(shù)據(jù)需要計(jì)算眨层，可以直接對(duì)查詢出的字段進(jìn)行+ - * / 運(yùn)算。

2.MySQL 函數(shù)

MySQL支持利用函數(shù)來處理數(shù)據(jù)上荡，函數(shù)在數(shù)據(jù)上執(zhí)行趴樱，它給數(shù)據(jù)的轉(zhuǎn)換和處理提供了方便。MySQL支持的函數(shù)有：

• 文本處理函數(shù)
• 日期和時(shí)間處理函數(shù)
• 數(shù)值處理函數(shù)
• 系統(tǒng)函數(shù)

2.1 文本處理函數(shù)

• concat(field1,field2)返回字符串拼接后的結(jié)果
• length() 返回字符串的長(zhǎng)度
• lower() 小寫
• upper() 大寫
• trim() 去掉左右兩邊的空格
• triml() 去掉左邊的空格
• trimr() 去掉右邊的空格
• substring(field,m,n) 從m位置開始取n個(gè)

2.2 時(shí)間處理函數(shù)

curdate()當(dāng)前日期
curtime()當(dāng)前時(shí)間
now()當(dāng)前日期及時(shí)間
date(日期值) 返回日期部分
time(日期值)返回時(shí)間部分
year(日期值)返回年
month(日期值)返回月
day(日期值)返回日
DATEDIFF('2021-06-29','2008-12-30')返回兩個(gè)日期之間的天數(shù)
DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%S') 指定格式顯示
DATE_ADD(date,INTERVAL expr type)向日期添加指定的時(shí)間間隔
DATE_SUB(date,INTERVAL expr type)函數(shù)從日期減去指定的時(shí)間間隔
date是合法的日期表達(dá)式
INTERVAL 是關(guān)鍵字
expr 時(shí)間間隔
type 參考下圖

image.png

image.png

2.3數(shù)值處理函數(shù)

round(x,y) x四舍五入的y為小數(shù)的值
truncate(x,y) x截?cái)酁閥位小數(shù)的值
abs(x)x的絕對(duì)值
rand() 0~1內(nèi)的隨機(jī)值

2.4系統(tǒng)函數(shù)

version()返回?cái)?shù)據(jù)庫版本號(hào)
database()當(dāng)前連接的數(shù)據(jù)庫
user()當(dāng)前登錄的用戶

2.5 聚合函數(shù)

聚合函數(shù)運(yùn)算在列上，計(jì)算和返回單個(gè)值的函數(shù)叁征，有：
sum()求和
count()個(gè)數(shù)
max()最大值
min()最小值
avg()平均值

3.分組查詢及子查詢

3.1 分組查詢

分組查詢使用group by語句纳账，根據(jù)一個(gè)或多個(gè)列隊(duì)結(jié)果集進(jìn)行分組。在分組的裂傷可以使用聚合函數(shù)捺疼。

SELECT field_name,function(field_name) FROM tb_name WHERE field_name operator value GROUP BY field_name HAVING xxx ORDER BY field_name;
注意上述代碼順序：WHERE xxx GROUP BY yyy ORDER BY zzz

image.png

• 分組采用GROUP BY塞祈，過濾是HAVING;
• WHERE field_nama operator value GROUP BY xxx HAVING gather_function(field) ORDER BY field_name;
• HAVING一般與聚合函數(shù)連用。

3.2 子查詢

嵌套在其他查詢中的查詢帅涂，即：一個(gè)查詢語句（select - from tb_name - where）塊可以嵌套在另外一個(gè)查詢塊的where子句中议薪。

• SELECT m,a.n FROM (SELECT sex AS n,address m FROM sys_user) AS a;
• SELECT * FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE DATE(order_date)='2021-05-16'); # 查詢2021年5月16日的下訂單的用戶信息

4.關(guān)系表

1. 假設(shè)有一個(gè)包含產(chǎn)品目錄的數(shù)據(jù)庫表，其中每種類別的產(chǎn)品占一行媳友。對(duì)于每種物品要存儲(chǔ)的信息包括產(chǎn)品描述和價(jià)格斯议，以及生成該產(chǎn)品的供應(yīng)商信息。
2. 現(xiàn)在醇锚，假如有由同一供應(yīng)商生產(chǎn)的多種物品哼御，那么在何處存儲(chǔ)供應(yīng)商信息（如：供應(yīng)商名、地址焊唬、聯(lián)系方式等）呢恋昼？將這些數(shù)據(jù)與產(chǎn)品信息分開存儲(chǔ)還是存儲(chǔ)在同一張表里呢？通常的原則是分開存儲(chǔ)赶促，理由：
   • 節(jié)約存儲(chǔ)空間液肌，因?yàn)橥还?yīng)商生產(chǎn)的每個(gè)產(chǎn)品的供應(yīng)商信息都是相同的，對(duì)每個(gè)產(chǎn)品重復(fù)此信息既浪費(fèi)時(shí)間又浪費(fèi)存儲(chǔ)空鸥滨；
   • 便于數(shù)據(jù)更正嗦哆，如果供應(yīng)商信息改變（如：供應(yīng)商搬家或電話號(hào)碼變動(dòng)，只需要改動(dòng)一次即可）
   • 保證數(shù)據(jù)唯一婿滓，如果有重復(fù)數(shù)據(jù)（即每種產(chǎn)品都存儲(chǔ)供應(yīng)商信息）老速，很難保證每次輸入該數(shù)據(jù)的方式都相同，不一致的數(shù)據(jù)在報(bào)表中很難利用凸主。
3. 在這個(gè)例子中橘券，可建立兩個(gè)表，一個(gè)存儲(chǔ)供應(yīng)商信息（vendors表）卿吐，另一個(gè)存儲(chǔ)產(chǎn)品信息（products表）旁舰。關(guān)系數(shù)據(jù)可以有效地存儲(chǔ)和方便地處理。因此但两，關(guān)系數(shù)據(jù)庫的可伸縮性遠(yuǎn)比非關(guān)系數(shù)據(jù)庫要好鬓梅。

5.表連接

5.1 內(nèi)連接

只有當(dāng)連接的兩個(gè)或多個(gè)表中都存在滿足條件的記錄時(shí)，才返回行谨湘。JOIN 或者 INNER JOIN

內(nèi)連接.png

5.2 左連接

返回左表（table1）中的所有記錄绽快，當(dāng)右表中沒有匹配記錄時(shí)芥丧，左連接任然返回一行，只是該行左表字段有值坊罢，右表字段以NULL填充续担。

SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2;

左連接

5.3 右連接

返回右表（table1）中的所有記錄，當(dāng)左表中沒有匹配記錄時(shí)活孩，右連接任然返回一行物遇，只是該行右表字段有值，左表字段以NULL填充憾儒。

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column1 = table2.column2

右連接

5.5 全連接

全連接

5.6 交叉連接

也稱為笛卡爾連接起趾，將左表的每一行與右表的每一行合并

SELECT * FROM table1 CROSS JOIN table2

5.6 自連接

用于將一個(gè)表和自身連接诗舰，就像存在兩個(gè)表一樣。通常用于將表的某個(gè)字段與該表的同一字段的其他值進(jìn)行比較训裆。

SELECT * FROM table1 AS a, table1 AS b WHERE a.common_column < b.common_column;

6.視圖

視圖是基于SQL語句的結(jié)果集的可視化的表眶根。包含行和列，就像一個(gè)真實(shí)的表边琉。

• 創(chuàng)建視圖

• CREATE VIEW view_name AS SELECT * FROM tb_name WHERE condition;
• create view zhang as select * from students INNER JOIN courses ON students.sid = courses.ssid;
• create view wang as select name, age from students INNER JOIN courses ON students.sid = courses.ssid;

• 刪除視圖

DROP VIEW view_name;

7.存儲(chǔ)過程

一組為了完成特定功能的SQL語句集属百，用戶可以通過調(diào)用過程來實(shí)現(xiàn)相應(yīng)的操作。好處是簡(jiǎn)化了復(fù)雜的操作变姨。

• 創(chuàng)建存儲(chǔ)過程

DELIMITER $$
CREATE PROCEDURE proc_zhang()
BEGIN
CREATE VIEW view_zhang AS SELECT name, age FROM students;
SELECT * FROM view_zhang;
END $$
DELIMITER ;

• 調(diào)用存儲(chǔ)過程

CALL proc_zhang();

• 刪除存儲(chǔ)過程

DROP PROCEDURE procdure_name;

• 查看所有存儲(chǔ)過程

SHOW PROCEDURES STATUS

8. 索引

索引增加提升查詢效率族扰，降低增刪改速度。

• 普通索引：最基本的索引钳恕，沒有任何限制别伏，創(chuàng)建命令：

create index pro_index on product(name);

• 唯一索引：索引列的值必須唯一，允許空值

create unique index pro_index on product(amount);

• 主鍵索引

SHOW INDEX FROM product;#查看product表的索引

• 刪除索引

DROP INDEX prod_index ON product;

• 索引規(guī)則：
  1. 表的主鍵忧额、外鍵必須有索引；
  2. 數(shù)據(jù)量大的表應(yīng)有索引愧口；
  3. 經(jīng)常與其他表連接的表睦番，應(yīng)在連接字段上建立索引；
  4. 經(jīng)常出現(xiàn)在where語句中的自動(dòng)耍属，應(yīng)該建立索引托嚣。

8.數(shù)據(jù)導(dǎo)入及導(dǎo)出

8.1 導(dǎo)出

• 導(dǎo)出數(shù)據(jù)庫

mysqldump -uuser_name -ppasswprd database_name > /path/database.sql

• 導(dǎo)出數(shù)據(jù)表

mysqldump -u root -p crashcourse customers vendors >/home/charles/customersback.sql

8.2 導(dǎo)入

• 檢查是否需要新建表庫或表
• 建立庫及表CREATE DATABASE db_name
• 使用庫USE db_name;
• 導(dǎo)入source your_sql_file.sql;

9.參考資料

MySQL文檔
MySQL官網(wǎng)
MySQL 教程|菜鳥教程