1. 插入數(shù)據(jù)

• 簡(jiǎn)單插入數(shù)據(jù)

insert into table [(cols)] #檢查主鍵，有重復(fù)的則報(bào)錯(cuò)
select "xxx", 1, "111" 

• 批量插入數(shù)據(jù)

insert into table [(cols)]
select * from another_table
或者
select * from another_table1
union [all]
select * from another_table2

• insert ignore into table [(cols)] # 檢查數(shù)據(jù)，如果有重復(fù)的數(shù)據(jù)則忽略插入的數(shù)據(jù)
• replace into table [(cols)] # 檢查主鍵/唯一索引，如有重復(fù)則用新的記錄代替覆蓋之

2. union

• union 將兩張表橫向連接（列不變?cè)黾有校瑒h除重復(fù)的記錄
• union all 保留所有記錄

3. 創(chuàng)建表

• create table [if not exsits] table_name #常規(guī)建表，檢查表名是否重復(fù)
• create table table_name as select * from another_table [limit 0]
  可以復(fù)制表的字段結(jié)構(gòu)和所有數(shù)據(jù)，可靈活篩選要復(fù)制哪些字段，可用limit決定是否要復(fù)制數(shù)據(jù)，但不可復(fù)制索引和權(quán)限
• create table table_name like another_table
  可復(fù)制全部的表結(jié)構(gòu)和索引，不可選擇字段，不可復(fù)制數(shù)據(jù)（需要后續(xù)批量插入），不可復(fù)制權(quán)限

4. replace

replace (field_name,’from_str’,'to_str’) 可以在更新數(shù)據(jù)的時(shí)候使用

5. 鍵、約束、索引

• 主鍵，外鍵又被稱為主鍵約束、外鍵約束
• 約束是一列數(shù)據(jù)應(yīng)該滿足的規(guī)則，確保數(shù)據(jù)的準(zhǔn)確性和一致性，約束是為了保證數(shù)據(jù)的完整性，索引是為了輔助查詢。約束除了主鍵、外鍵之外，還有：
  唯一性約束：創(chuàng)建唯一性約束的同時(shí)會(huì)創(chuàng)建唯一性索引，唯一約束靠唯一索引實(shí)現(xiàn)，兩者的不同是創(chuàng)建以及刪除時(shí)的關(guān)鍵字不同，其他使用方法等都一樣（至少在MySQL中）。
  check約束：約定某字段的值的取值范圍，不然就報(bào)錯(cuò)
• 添加約束的方式：

create table table_name 
(
  col1 xx xx,
  col2 yy yy,
  [constraint constraint_name] primary key (col1),
  [constraint constraint_name] foreign key(col2) references another_table(col2),
  [constraint constraint_name] unique [key](col1),
  [constraint constraint_name] check(col1 > 0),
  # 如不指定constraint及其name，那么系統(tǒng)會(huì)自動(dòng)命名
)
# OR
  alter table table_name add [constraint constraint_name] primary key (col1),
  alter table table_name add [constraint constraint_name] foreign key(col2) references     
  another_table(col2),
  alter table table_name add [constraint constraint_name] unique [key](col1),
  alter table table_name add [constraint constraint_name] check(col1 > 0),
  # 如不指定constraint及其name，那么系統(tǒng)會(huì)自動(dòng)命名

• 沒(méi)有索引的時(shí)候，數(shù)據(jù)在磁盤(pán)中按照順序一一排列，因此查詢數(shù)據(jù)時(shí)需要將查詢條件依次與每一條記錄相比較，O(n)的時(shí)間復(fù)雜度，非常慢。為字段添加索引之后，索引值會(huì)形成一個(gè)樹(shù)的結(jié)構(gòu)，查詢時(shí)只需要依次比較樹(shù)的分支節(jié)點(diǎn)直至葉節(jié)點(diǎn)即可，查詢時(shí)間隨數(shù)據(jù)量增大而指數(shù)級(jí)減少時(shí)間復(fù)雜度O(logn)。但是，增刪改數(shù)據(jù)都會(huì)改變索引的數(shù)據(jù)的內(nèi)容，導(dǎo)致系統(tǒng)需要重新更新一次索引，這也造成了額外的時(shí)間開(kāi)銷(xiāo)。同時(shí)，儲(chǔ)存索引還會(huì)造成存儲(chǔ)開(kāi)銷(xiāo)。
• 按數(shù)據(jù)在磁盤(pán)中的物理存儲(chǔ)方式分，索引可分為聚集索引和非聚集索引。聚集索引使數(shù)據(jù)在磁盤(pán)中按照順序排成一個(gè)樹(shù)的結(jié)構(gòu)。一張表中只能有一個(gè)聚集索引，通過(guò)聚集索引可以直接查詢到同一行的數(shù)據(jù)記錄。一張表中可以存在多個(gè)非聚集索引。創(chuàng)建非聚集索引時(shí)，系統(tǒng)會(huì)將相應(yīng)的字段數(shù)據(jù)copy一份出來(lái)用于創(chuàng)建索引。通過(guò)非聚集索引可以查找到表中主鍵/聚集索引的鍵值，再通過(guò)主鍵值間接地查詢出其他字段的數(shù)據(jù)。當(dāng)非聚集索引有組合字段創(chuàng)建時(shí)，則可以通過(guò)組合字段1直接查詢到組合字段2，而無(wú)需經(jīng)過(guò)主鍵/聚集索引中轉(zhuǎn)。
• 主鍵不可null，但唯一索引可為null。在創(chuàng)建主鍵的同時(shí)會(huì)自動(dòng)創(chuàng)建一個(gè)唯一索引。
• 唯一索引可為聚集or非聚集，創(chuàng)建主鍵時(shí)同時(shí)創(chuàng)建的索引也可為聚集or非聚集。但在mysql數(shù)據(jù)庫(kù)innodb引擎里面，創(chuàng)建主鍵的同時(shí)就會(huì)創(chuàng)建一個(gè)聚集的唯一的索引，而在其他數(shù)據(jù)庫(kù)引擎如SQL server中則可以選擇索引要聚集還是非聚集的。
• 主鍵（約束）!= xx索引
• 創(chuàng)建聚集索引：MySQL的聚集索引一般為主鍵，沒(méi)有主鍵時(shí)則會(huì)尋找第一個(gè)非空的唯一索引作為聚集索引，否則就以rowid作為聚集索引。因此，要想在除了主鍵列的其他字段設(shè)置聚集索引，則應(yīng)該刪除主鍵約束后，在某其他字段上建立唯一索引，并確保其not null，這樣系統(tǒng)就會(huì)自動(dòng)把它作為聚集索引而非默認(rèn)的非聚集。
• 添加索引的方式

create table table_name 
(
  col1 xx xx,
  col2 yy yy,
  [unique] index [index_name] (col1)
)
  # 如不指定index_name，那么系統(tǒng)會(huì)自動(dòng)命名
OR
alter table table_name add [unique] index [index_name] (col2)
create index index_name on tb2 (name);

6. 創(chuàng)建視圖

create view view_name as
  select * from table

7. 強(qiáng)制/禁止使用索引

當(dāng)表中有多個(gè)索引時(shí)，可能需要手動(dòng)確定使用/不使用哪一個(gè)索引進(jìn)行查詢

select * from table
force/ignore index(index_name)
where ..........

8. 創(chuàng)建觸發(fā)器

• new表中是即將插入的/更新后的數(shù)據(jù)，old中是刪除后/更新前的數(shù)據(jù)

create trigger trigger_name 
after/before update/delete/insert
**for each row**
begin
  xxxxxxx
  xxxxxxx;
end;

9. update/delete and 子查詢

mySQL不支持對(duì)正在查詢的表進(jìn)行修改，具體而言，不能在update和delete中的where條件里使用子查詢，只能將查詢結(jié)果取名建立臨時(shí)表，再select *

delete from table_name
where col in/not in
(
  select * from
    (
      select xxx from table_name
      where xxx
      group by xxx
    ) temp
);

10. with建立臨時(shí)表

with temp_table_name as
(select xxx from table_name);

11. 同一條件下更新多個(gè)字段

update table_name
set col1 = xxx, col2 = xxx
where col1 = xxxx;

12. alter table 總結(jié)

alter table table_name
{ ADD COLUMN <列名> <類(lèi)型>
| CHANGE COLUMN <舊列名> <新列名> <新列類(lèi)型> #可改字段名and類(lèi)型
| ALTER COLUMN <列名> { SET DEFAULT <默認(rèn)值> | DROP DEFAULT } #不可改類(lèi)型/字段名，只可改默認(rèn)值
| MODIFY COLUMN <列名> <類(lèi)型> #只可改類(lèi)型
| DROP COLUMN <列名>
| RENAME TO <新表名> }

13. concat 函數(shù)

select concat(col1/str1, '分隔符', col2/str2) from table_name

14. length函數(shù)

length(str)

• length('')=0 空字符長(zhǎng)度為0

15. replace函數(shù)

replace(str, 'str中要被替換的字符', '替換后的字符')

16. group_concat函數(shù)

分組后，有時(shí)需要把組內(nèi)某個(gè)字段的所有記錄的值都連接起來(lái)
group_concat(col) 表示將組內(nèi)col字段的所有記錄都以‘，’分隔并連接，如：
10001 123
10001 456
這樣輸出結(jié)果為123,456
group_concat(str/col, col, str/col) 則表示在上面group_concat的結(jié)果中每一個(gè)字段的記錄之前或之后再加一個(gè)東西, 如：
10001 123
10001 456
group_concat(col, col, "+") => 123123+,456456+

17. 存儲(chǔ)過(guò)程與函數(shù)

• 存儲(chǔ)過(guò)程與函數(shù)的基本概念十分相似，都是封裝一段可以實(shí)現(xiàn)一種功能的代碼。存儲(chǔ)過(guò)程封裝的更多是一段相對(duì)獨(dú)立的業(yè)務(wù)邏輯，因此存儲(chǔ)過(guò)程需要單獨(dú)執(zhí)行。而函數(shù)則聚焦在某一種相對(duì)更基本的功能，如加減法，本質(zhì)上是在為實(shí)現(xiàn)一個(gè)更大的功能造輪子，所以函數(shù)需要/可以在其他SQL語(yǔ)句中調(diào)用。
• 存儲(chǔ)過(guò)程可以設(shè)置輸入/輸出參數(shù)，局部or全局地改變變量的值，輸出不限量。而函數(shù)只能且必須輸出一個(gè)值。
• 封裝的好處：將一段代碼預(yù)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，使用時(shí)可以直接調(diào)用，減少了編譯時(shí)間，減少了數(shù)據(jù)傳輸?shù)臄?shù)量（傳幾個(gè)參數(shù)就可以了，沒(méi)有必要傳整段代碼）
• 創(chuàng)建存儲(chǔ)過(guò)程：

delimeter $$
drop procedure if exsits proc_name $$
create procedure proc_name
(
  [in 參數(shù)名 參數(shù)類(lèi)型 #輸入?yún)?shù),
  out 參數(shù)名 參數(shù)類(lèi)型 #輸出參數(shù) 全局地改變變量的值,
  inout 參數(shù)名 參數(shù)類(lèi)型]
)
begin
  SQL語(yǔ)句;
  xxxxxx;
end; $$
delimeter ;
# 調(diào)用
call proc_name(輸入?yún)?shù))；

• 創(chuàng)建函數(shù)

delimeter $$
drop function if exsits func_name $$
create function func_name
(
[輸入?yún)?shù)1 類(lèi)型,
輸入?yún)?shù)2 類(lèi)型,]
) returns (輸出類(lèi)型)
begin
  SQL語(yǔ)句;
  return ;
end;$$
delimeter ;
# 調(diào)用
select func_name(參數(shù))
or select * from table where xxx = func_name(參數(shù))

18. if else

if (比較條件, 為真輸出, 為假輸出)
or
if (比較條件)
then 
  xxxx;
elseif (比較條件)
then 
  xxxx;
else 
  xxxx;
end if;

19. 循環(huán)

• Loop-leave

loop: loop_name
  if (條件)
  then
    leave loop_name (相當(dāng)于if-break)
  end if;
  循環(huán)執(zhí)行語(yǔ)句
end loop

• while

while (i < constant)  do #循環(huán)準(zhǔn)入條件
  set i = i + 1
  循環(huán)體
end while;

• repeat-untill

repeat
  循環(huán)體
  set i = i +1
untill i > constant 
end repeat;

20. 游標(biāo)

常與循環(huán)、存儲(chǔ)過(guò)程搭配，對(duì)結(jié)果集（表）的所有記錄一行一行地依次取出進(jìn)行操作

drop procedure if exists sp_name;
demiliter $$
create procedure sp_name ()
begin
declare flag bool deault False #定義局部變量；## declare 只能用于存儲(chǔ)過(guò)程begin-end中
declare vars int;
declare cur_name cursor for (select xxxxx); #在結(jié)果集上定義游標(biāo)
declare continue handler for not found set flag = True; #游到最后觸發(fā)此條件：繼續(xù)執(zhí)行下面的語(yǔ)句，并且設(shè)置flag(循環(huán)退出條件)
open cur_name; #打開(kāi)游標(biāo)
loop_name: loop
  if (flag = True)
  then leave loop_name
  fetch [next/prior/first/last from] cur_name into (var1, var2) / fetch cur_name into (var1, var2) ; #如不指明 next/prior from 則默認(rèn)next #將一行的記錄存在變量里
  select var1, var2 # or other SQL operations interms of var1 var2
end loop;
close cur_name #關(guān)閉游標(biāo)
end;$$
demiliter ;

21. 定義變量

• 局部變量
  只在begin-end語(yǔ)句塊中有用，用declare聲明
  declare var var_type;
• 用戶變量
  在當(dāng)前數(shù)據(jù)庫(kù)連接中有效，在當(dāng)前會(huì)話中屬于“全局變量”，用set/select定義并且賦初值
  set @var=1/select @var:=null
• 會(huì)話變量
  作用域與用戶變量相同，每個(gè)客戶端連接數(shù)據(jù)庫(kù)時(shí)系統(tǒng)會(huì)用全局變量初始化會(huì)話變量的值，用戶只能改動(dòng)自己的會(huì)話變量，不能改動(dòng)其他的會(huì)話變量。
  set session var = value
• 全局變量
  在整個(gè)服務(wù)器上，跨數(shù)據(jù)庫(kù)有效
  set global var=1