數(shù)據(jù)庫(kù)增刪改查語句
創(chuàng)建數(shù)據(jù)庫(kù)：CREAT DATABASE base_name;
刪除數(shù)據(jù)庫(kù)：DROP database base_name;
創(chuàng)建數(shù)據(jù)表：CREATE TABLE table_name (column_name column_type);
刪除數(shù)據(jù)表：DROP table table_name累驮；
增加數(shù)據(jù)：INSERT INTO table_name values（）挣输；
刪除數(shù)據(jù)：DELETE FROM table_name WHERE column_name;
修改數(shù)據(jù)：UPDATE table_name SET 修改后 where 修改前奶陈；
查詢數(shù)據(jù)：SELECT column_name,column_name
FROM table_name
WHERE 埠褪；
模糊查詢：SELECT * FROM table_name WHERE column_name LIKE'%?' ；
數(shù)據(jù)分組：GROUP BY 語句根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組侈离。
在分組的列上我們可以使用 COUNT, SUM, AVG,等函數(shù)痢法。
UNION 語句：用于將不同表中相同列中查詢的數(shù)據(jù)展示出來凉夯；（不包括重復(fù)數(shù)據(jù)）
UNION ALL 語句：用于將不同表中相同列中查詢的數(shù)據(jù)展示出來；（包括重復(fù)數(shù)據(jù)）
使用形式如下：
SELECT 列名稱 FROM 表名稱 UNION SELECT 列名稱 FROM 表名稱 ORDER BY 列名稱垢粮；
SELECT 列名稱 FROM 表名稱 UNION ALL SELECT 列名稱 FROM 表名稱 ORDER BY 列名稱顿颅；
MySQL 索引
MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度足丢。
多表連接
INNER JOIN（內(nèi)連接,或等值連接）：獲取兩個(gè)表中字段匹配關(guān)系的記錄：
select * from emp e inner join dept d on d.deptid=e.deptid;
內(nèi)外連接
內(nèi)連接和外連接
1粱腻、內(nèi)聯(lián)接（典型的聯(lián)接運(yùn)算，使用像 = 或 <> 之類的比較運(yùn)算符）斩跌。包括相等聯(lián)接和自然聯(lián)接绍些。
內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行。
2耀鸦、外聯(lián)接柬批。外聯(lián)接可以是左向外聯(lián)接啸澡、右向外聯(lián)接或完整外部聯(lián)接。
1）LEFT JOIN或LEFT OUTER JOIN
左向外聯(lián)接的結(jié)果集包括 LEFT OUTER子句中指定的左表的所有行氮帐，而不僅僅是聯(lián)接列所匹配的行嗅虏。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值上沐。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接皮服。將返回右表的所有行。如果右表的某行在左表中沒有匹配行参咙，則將為左表返回空值龄广。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部聯(lián)接返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒有匹配行時(shí)蕴侧，則另一個(gè)表的選擇列表列包含空值择同。如果表之間有匹配行，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值净宵。
左右連接
LEFT JOIN（左連接）：獲取左表所有記錄敲才，即使右表沒有對(duì)應(yīng)匹配的記錄:
select * from dept d left join emp e on d.deptid=e.deptid;
RIGHT JOIN（右連接）： 與 LEFT JOIN 相反，用于獲取右表所有記錄择葡，即使左表沒有對(duì)應(yīng)匹配的記錄:
select * from emp e right join dept d on d.deptid=e.deptid;
數(shù)據(jù)庫(kù)標(biāo)識(shí)符
MySQL事務(wù)
事務(wù)是必須滿足4個(gè)條件（ACID）：原子性（Atomicity归斤，或稱不可分割性）、一致性（Consistency）刁岸、隔離性（Isolation脏里，又稱獨(dú)立性）、持久性（Durability）虹曙。
原子性：一個(gè)事務(wù)（transaction）中的所有操作迫横，要么全部完成，要么全部不完成酝碳，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)矾踱。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)疏哗，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣呛讲。
一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒有被破壞返奉。
隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力贝搁，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。
持久性：事務(wù)處理結(jié)束后芽偏，對(duì)數(shù)據(jù)的修改就是永久的雷逆，即便系統(tǒng)故障也不會(huì)丟失。
數(shù)據(jù)庫(kù)優(yōu)化
SQL編寫技巧
SQL編寫有以下幾個(gè)通用的技巧：
-
合理使用索引
索引少了查詢慢污尉；索引多了占用空間大膀哲，執(zhí)行增刪改語句的時(shí)候需要?jiǎng)討B(tài)維護(hù)索引往产，影響性能。選擇率高（重復(fù)值少）且被where頻繁引用需要建立B樹索引某宪；
一般join列需要建立索引仿村；復(fù)雜文檔類型查詢采用全文索引效率更好；索引的建立要在查詢和DML性能之間取得平衡兴喂；復(fù)合索引創(chuàng)建時(shí)要注意基于非前導(dǎo)列查詢的情況
-
使用UNION ALL替代UNION
UNION ALL的執(zhí)行效率比UNION高蔼囊，UNION執(zhí)行時(shí)需要排重；UNION需要對(duì)數(shù)據(jù)進(jìn)行排序瞻想，而UNION ALL沒有去除重復(fù)數(shù)據(jù)的步驟。
-
避免select * 寫法
執(zhí)行SQL時(shí)優(yōu)化器需要將 * 轉(zhuǎn)成具體的列娩嚼；每次查詢都要回表蘑险，不能走覆蓋索引。
-
JOIN字段建議建立索引
一般JOIN字段都提前加上索引
-
避免復(fù)雜SQL語句
提升可閱讀性岳悟；避免慢查詢的概率佃迄；可以轉(zhuǎn)換成多個(gè)短查詢，用業(yè)務(wù)端處理
-
避免where 1=1寫法
-
避免order by rand()類似寫法
RAND()導(dǎo)致數(shù)據(jù)列被多次掃描
優(yōu)化案例
表結(jié)構(gòu)
CREATE TABLE a
(
id int(11) NOT NULLAUTO_INCREMENT,
seller_id bigint(20) DEFAULT NULL,
seller_name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
gmt_create varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE b
(
id int(11) NOT NULLAUTO_INCREMENT,
seller_name varchar(100) DEFAULT NULL,
user_id varchar(50) DEFAULT NULL,
user_name varchar(100) DEFAULT NULL,
sales bigint(20) DEFAULT NULL,
gmt_create varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE c
(
id int(11) NOT NULLAUTO_INCREMENT,
user_id varchar(50) DEFAULT NULL,
order_id varchar(100) DEFAULT NULL,
state bigint(20) DEFAULT NULL,
gmt_create varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);
三張表關(guān)聯(lián)贵少，查詢當(dāng)前用戶在當(dāng)前時(shí)間前后10個(gè)小時(shí)的訂單情況呵俏，并根據(jù)訂單創(chuàng)建時(shí)間升序排列，具體SQL如下
select a.seller_id,
a.seller_name,
b.user_name,
c.state
from a,
b,
c
where a.seller_name = b.seller_name
and b.user_id = c.user_id
and c.user_id = 17
and a.gmt_create
BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create滔灶；
查看數(shù)據(jù)量
原執(zhí)行時(shí)間
原執(zhí)行計(jì)劃
初步優(yōu)化思路
SQL中 where條件字段類型要跟表結(jié)構(gòu)一致普碎，表中 user_id 為varchar(50)類型，實(shí)際SQL用的int類型录平，存在隱式轉(zhuǎn)換麻车，也未添加索引。將b和c表 user_id 字段改成int類型斗这。
因存在b表和c表關(guān)聯(lián)动猬，將b和c表 user_id創(chuàng)建索引
因存在a表和b表關(guān)聯(lián)，將a和b表 seller_name字段創(chuàng)建索引
利用復(fù)合索引消除臨時(shí)表和排序
初步優(yōu)化SQL
alter table b modify user_id int(10) DEFAULT NULL;
alter table c modify user_id int(10) DEFAULT NULL;
alter table c add index idx_user_id(user_id);
alter table b add index idx_user_id_sell_name(user_id,seller_name);
alter table a add index idx_sellname_gmt_sellid(gmt_create,seller_name,seller_id);
查看優(yōu)化后執(zhí)行時(shí)間
查看優(yōu)化后執(zhí)行計(jì)劃
查看warnings信息
繼續(xù)優(yōu)化alter table a modify "gmt_create" datetime DEFAULT NULL;
查看執(zhí)行時(shí)間
查看執(zhí)行計(jì)劃
總結(jié)
查看執(zhí)行計(jì)劃 explain
如果有告警信息表箭，查看告警信息 show warnings;
查看SQL涉及的表結(jié)構(gòu)和索引信息
根據(jù)執(zhí)行計(jì)劃赁咙，思考可能的優(yōu)化點(diǎn)
按照可能的優(yōu)化點(diǎn)執(zhí)行表結(jié)構(gòu)變更、增加索引免钻、SQL改寫等操作
查看優(yōu)化后的執(zhí)行時(shí)間和執(zhí)行計(jì)劃
如果優(yōu)化效果不明顯彼水，重復(fù)第四步操作
————————————————
