MySQL基础原创笔记（一）
對表的增刪改操作：
創建表：
create table student (
???????? id ?int ?primary key ?auto_increment,
???????? name ?varchar(10) ?character set utf8 ?not null,
???????? sex??? char(2)? default ‘M’,
???????? constraint? fk_student_score? foreign key(id)? references score(id)
)? engine=InnoDB;
?
primary key :指定表的主鍵；
如果是指定單個主鍵，可直接在該字段后聲明；
如果是指定多個主鍵，只能采用primary key (X,Y)的形式；
?
定義外鍵：
constraint fk_T2_T1 foreign key(t2_id) references T1(t1_id); //將T1表中的t1_id字段設置成為T2表的外鍵；
增加外鍵
alter table T2 orderitemds add constraint fk_T2_T1 foreign key(id) references T1(id); //將T1表中的t1_id字段設置成為T2表的外鍵；
?
auto_increment :讓Mysql自動對該列增量；每個表只允許有一個auto_increment列，且必須被索引（以最大的數去遞增），從而可將該列當作主鍵；
注：select last_insert_id():該語句可獲得最后一個auto_increment的值；
default:為字段設置默認值；
?
character set …：指定編碼格式，通常是為了讓其能識別中文；
?
engine = …：指定mysql引擎；
常用的引擎類型：
InnoDB:可靠地事物處理引擎，但不支持全文檢索；
MyISAM：性能極高的引擎，支持全文檢索，但不支持事務處理；
MEMORY：功能同于MyISAM,但由于數據庫在內存里，速度很快；適用于臨時表；
注：新版Mysql默認引擎是InnoDB；
?
更新表：使用alter table語句
增加列：
alter table T add Tname varchar(20); //為T表增加一個名為Tname的列
刪除列：一次只能刪除一列；
alter table T drop column Tname; ///刪除T表中名為Tname的列
?
刪除表：使用Drop table語句
例：drop table T;
?
重命名表：使用rename table語句
例：rename table T1 to t1, T2 to t2, T3 to t3;
?
對表中數據的查詢操作：
SQL語句格式：
select
from
where
group by
having
order by
limit
?
去重:distinct
例：select distinct * from T
數據分組： group by , having , rollup
group by? :進行分組; 如果分組中具有null值，則null將作為一個分組返回；
having ??：過濾分組; 它支持所有where中的操作符；
注：可以使用rollup關鍵字來顯示出分組前所有的數據；例：group by prod_id with rollup;
除聚集計算語句外，select語句中使用的每個選擇列或表達式列，都必須在group by子句中給出；
排序：order by
select * from XXX order byXXX (desc(降序)/asc(升序))
限制檢索行數：limit， limit offset
例： ???select * from XXX limit 0，2 （從第0行開始，取2行）
等價于? select * from xxx limit 1 offset 2(從第2行開始取1行)
起別名：
可以為表或列起別名；
有兩種形式：
(1)table1 t1
(2)table1 as t1
?
where語句中注意的問題：
注：and比or的優先級高
? ??‘<>’ 等價于 ‘!=’f
??? between A and B：包括邊界
???? is null
???? in ：指定條件范圍 取值時用","分隔，相當于or的作用，但比or操作符執行的更快；并且可以包含select語句；
???? not
???? like: %:任意字符（null除外）?? _：單個字符
??? 注：用通配符過濾：花費時間較長，避免過度使用；可以考慮用正則表達式；
?
mysql支持部分正則表達式,可以匹配基本的字符和字符串；
形如： select * from products where prod_name regexp ‘XXXX’
例：檢索到prod_name包含文本1000的所有行；
select * ??from products ??where prod_name regexp ‘1000’
mysql的預定義字符集：
[:digit:]---任意的數字(同[0-9])
[:lower:]---任意小寫字母(同[a-z])
[:upper:]---任意大寫字母(同[A-Z])
[:alpha:]---任意字符(同[a-zA-Z])
[:alnum:]---任意數字和字母（同[a-zA-Z0-9]）
[:blank:]---空格和制表符（同[\\t]）
[:space:]---任意的空白字符，包括空格；(同[\\f\\n\\r\\t\\v])
例：-- 表示檢索出現連在一起的任意4位數字和小寫字母的所有行
select prod_name
from products
where prod_name regexp ‘[[:digit:][:lower:]]{4}’??
like 和 regexp的區別：
like是匹配這個列，如果被匹配的文本僅在列值中出現，like并不會找到它，相應的行將不會返回（使用通配符除外）。
regexp是匹配列值，如果被匹配的文本在列值中出現，regexp會找到它，相應的行將會返回（可以用定位符號^和$,讓regexp匹配這個列，而非列值）。
?
當一些操作,不需要從表上選擇數據時，mysql中提供了一個內置的特殊的表dual（空表），來方便操作；
?
mysql中提供的一些常用函數：
聚集函數（匯總函數）：
max(), ??min(),? ?sum(),? avg(),? count();
注：它們都忽略列值為null的行；
它們都可以和distinct關鍵字聯合使用；例如：count(distinct prod_price)
?
文本處理函數：
concat( , ) ：用來拼接字段
例:
select concat(vend_name, vend_country)
from vendors
trim( , ):去掉字段中值中的所有空格
rtrim( , )：去掉字段中值右邊的所有空格
ltrim( , )：去掉字段中值左邊的所有空格
soundex( ):能對串進行發音比較；可將任何文本串轉化為描述其語音表示的字母數字；
例如：soundex(cust_contact) = soundex(‘Y.Lie’) //可找到表中cust_contact=Y Lee的這條記錄；
日期和時間處理函數：
Now() :返回當前日期和時間
CurData()：返回當前日期
Curtime():返回當前時間
Year(D):返回回一個日期D中的年份部分 類似有：Month(D)……
DateOfWeek(D):返回對于一個日期D，返回對應的星期幾
DateDiff(D1,D2):計算D1到D2兩個日期之差
Date_Format(D,F):按照表示式F的要求顯示日期D;
AddDate(D,n):計算起始日期D加上n天的日期；
SubDate(D,n): 計算起始日期D減去n天的日期；
?
子查詢：嵌套在其他查詢中的查詢
使用子查詢的兩種方式：
1.一般與in操作符結合使用，但也可以用于測試等不等于；
2.用子查詢去創建計算字段；將子查詢整體作為一個計算字段使用；
注：一切的問題都可以采用子查詢寫出；
?
連接：兩個表之間字段取交集
等值連接：where …=…
例： from T1,T2?? where T1.p_id=T2.p_id;
內部連接：inner join…on…
例：from T1 inner join T2 on T1.p_id =T2.p_id;
注：推薦用inner join on子句，它的性能好于where.
外連接：left outer join … on… 、right outer join …on…
左外連接：得到的內容為 兩者的公共交集+ 左邊的表中非關聯部分
右外連接：得到的內容為 兩者的公共交集+ 右邊的表中非關聯部分
例：from T1 left outer join T2 on T1.p_id =T2.p_id;
自連接：速度很好，推薦使用
可以通過子查詢來實現，
select
from t
where? t_id = {? }
也可以通過不同的別名來實現；
select
from? t as t1, t as t2
where? t1_id = t2_id
?
組合查詢：多個查詢并將結果作為一個查詢結果集返回，使用union操作字
需要使用組合查詢的情況：
在各select語句之間放上關鍵字union，每個查詢必須包含相同的列、表達式或聚集函數。
它會從查詢結果中自動去除重復的行，如果需要不取消行，可使用union all；
注：使用uinon可以替代where，能夠用較簡單的方式去完成由where非常復雜才得到的結果；
?
范式：
第一范式（1NF）：確保每列保持原子性
第二范式（2NF）：符合1NF，并且，確保表中的每列都和主鍵相關
第三范式（3NF）：符合2NF，并且，確保每列都和主鍵列直接相關,而不是間接相關；
BC范式（BCNF）：符合3NF，并且，主屬性不依賴于主屬性
第四范式：要求把同一表內的多對多關系刪除。
第五范式：從最終結構重新建立原始結構。
?
對表中數據的增刪改操作：
注：T代表某個表
向表中增加行：有四種形式；
第一種形式：插入完整行，必須按默認順序為每個列提供一個值；
insert into T
values(……)
注：若該列沒有值則用null表示；
第二種形式：插入部分行，可按自己指定的順序為每個列賦值；
insert into T(……)
values(……)
第三種形式：插入多行；
insert into T(……)
values(……) ;
insert into T(……)
values(……) ;
或
（Mysql特有的）
insert into T(……)
values(……) ,(……);
第四種形式：插入部分某些查詢結果；
insert into T(……)
select ……
?
修改表中的數據：
update T set ……
例：
//修改編號為1002顧客的顧客名和顧客電話
update customers set cust_name=’Tom’, cust_tele=”10086” where cust_id=1002;
//刪除編號為1001顧客的郵件地址
updata customers set cust_email=null where cust_id=1001;
?
從表中刪除行：
delete from T where……
注：也可以用truncate table語句刪除表中所有數據，它的速度更快，相當于刪除原表，在建一個空表；
?
視圖：即：虛擬的表，它只是一個SQL查詢，不包含任何數據；
查看創建視圖的語句：show create view <視圖名稱>
創建視圖：
create view <視圖名稱>
as
select查詢語句
刪除視圖：drop view <視圖名稱>
注： 通常視圖都是和select語句使用的，
雖然視圖是可更新的，即可以對視圖使用insert，update,delete；但其實更新一個視圖，實際上是更新其基表，是對基表的增刪改，所以并不推薦這樣使用；
優點：可重用SQL語句；
????? 化簡了復雜的SQL操作
????? 使用的部分表，而不是整個表；
????? 能保護數據；
????? 可更改數據格式和表示；
缺點：視圖會影響性能，因此使用大量視圖時要慎重；
?
?
Mysql高級語言
有三種形式的注釋：
行注釋：?? #XXX ????-- XXX（之間至少有一個空格符）
塊注釋： ??/* XXX */
?
變量：局部變量用@開頭；全局變量用@@開頭；
?
聲明變量: 用declare語句 , 變量不能加@符號；使用游標是只能用declare定義
daclare var_name[,……]? type? (default value)
注：declare語句必須用在begin……end語句塊中且必須在最前面；變量名不區分大小寫； mysql中是一般可以不用事先申明變量，可直接用set或select賦值，
?
變量賦值：；使用變量時用“@var_name”表示即可； 打印時只能用以下的方式；
用set語句直接賦值：可以用”=”或”:=”賦值
set ?@var_name=value[,…] 或set? @var_name:=value[,…]
用select語句查詢賦值：只能用”=”賦值，因為在select語句中=被看作比較運算符；
①select? @var_name:=value[,…]
②select? @var_name[,…]
from……
where……
③select ?@col_name[,…] into var_name[,…] ??#把選定的列賦值給變量
from ……where……
注：直接用set/select語句定義的變量必須加@符號，除非該變量已經被declare聲明過；
例：//通過連續賦值，加起來賦為t1,并把每個值都打印出來；
select @t1:=(@t2=1)+@t3:=4,@t1,@t2,@t3; from…where…?
?
變量的分類：
mysql手冊將變量分系統變量和用戶變量
但實際中常有以下幾種：
局部變量（作用域是當前語句塊，即：begin……end）：必須用declare聲明
用戶變量（作用域是當前整個連接）：無需使用declare申明，可用select或set會話變量：（作用域是當前整個連接）：
定義：
set @var_name=value;
set session @var_name=value;
set @@session var_name=value;
查看：
select @@var_name;
select @@session.var_name;
show session variables like “%var%”
全局變量（作用域是整個數據庫）：
set global @@var_name=value;
查看：
select @@global.var_name;
show global variables like “%var%”
?
?
存儲過程中的控制語句：
if(expr1, expr2, expr3):相當于java中的三目運算符，
expr1是true，則返回expr2,否則返回expr3;
?
ifnull(expr1, expr2):
若expr1非null,返回本身；否則返回expr2;
?
nullif(expr1,expr2):
若兩者相等，返回null,否則返回expr1;
?
分支語句之if：
if ?condition? then ?result
[else ?result]
end if;
?
分支語句2之case：
形式一：
case value
when compare-value? then? result
[when compare-value ?then? result
……]
[else result]
end case;
形式二：====java中的if else語句
case
when condition? then? result
[when condition? then ?result
……]
[else result]
end case;
注：在流程控制中end后不用寫case;
???????? condition是一個表達式；
?
循環語句之while循環：執行前檢查是否滿足循環條件
while condition do
???????? ……
end while;
循環語句之repeat循環：執行前檢查是否不滿足循環條件
repeat
???????? ……
???????? until condition (沒有‘;’)
end repeat;
循環語句之loop循環：
lable_1:loop
???????? ……
?????????????????? leave lable_1;
end loop;
注：
leave語句：只能循環語句中使用，意思離開語句標號為lable_1的語句，相當于“跳出整個循環”（類似java中的break）；當滿足了特定條件時，通過“leave 循環標號”語句跳出整個循環；
iterater語句：只能循環語句中使用，意思直接跳轉到語句標號為lable_1的語句，相當于“跳過本次循環” （類似java中的continue）；
當滿足了特定條件時，通過“iterater 循環標號”語句跳過本次循環；
語句標號：lables可以用到while, repeat ,loop等循環控制語句中；
?
存儲過程和函數：統稱為PL/SQL子程序，它們是被命名的PL/SQL塊，均存儲在數據庫中，以便共享；
?
唯一區別：函數向調用者返回數據，而過程不返回數據；函數可以在SQL語句內部被調用，而過程則不能被調用；
存儲過程和函數的優點：簡單，安全，高性能；
?
創建存儲過程：
注：為了安全可以先執行這句：drop procedure if exists ?<過程名稱>;
delimiter //(有空格符)
create procedure <過程名稱> (
???????? 參數聲明
)
begin
???????? 過程體
end //
delimiter ;(有空格符)
注：
①delimiter意思是定義符號， 目的是用delimiter關鍵字申明當前段的自定義分隔符，將其之間的內容當作存儲過程，使mysql編譯器不會執行這些的代碼；記得用完后要把分隔符還原；
②存儲過程定義時有三種參數類型： in,? out,? inout
in 輸入參數：在調用時指定，執行中不能被修改并返回；
out 輸出參數：執行中能被修改并返回；
inout 輸入輸出參數：在調用時指定，執行中能被修改并返回；
?
調用存儲過程：
call proc_name (…);
修改存儲過程：
alter procedure proc_name ……;
刪除存儲過程：
drop procedure proc_name ;
查看存儲過程：可以獲取何時，有誰創建的信息
查看所有存儲過程的信息：
show procedure status;
查看指定的存儲過程：
show create procedure proc_name;
?
函數：大體類似于存儲過程，只是將關鍵字procedure變為function
但注意創建函數時不能使用in,out,inout關鍵字,因為它默認其所有的參數都是in的；
注：為了安全可以先執行這句：drop function if exists ?<函數名稱>;
delimiter //(有空格符)
create function <函數名稱> (
???????? 參數聲明 （參數不能是帶@的）
)
returns 參數
begin
???????? 過程體
???????? return 參數；
end //
delimiter ;(有空格符)
?
函數不能用call調用外，但它能在SQL語句內部被調用
例如：select func_name(…) from … where…
?
使用存儲過程和函數的選擇：
1.返回多個值或不返回值，用存儲過程；返回一個值，用函數；
2.存儲過程一般用于執行一個指定的動作；
函數一般用于計算和返回一個值；
3.要在SQL語句內部被使用，只能用函數；
?
游標cursor：只能用與存儲過程或函數;
能在檢索出來的行中前進或后退一行或多行；
它不是select語句，而是被該語句檢索出來的結果；
創建游標：定義時并未檢索數據? 只能用declare定義變量；
decalar cur_name cursor for 定義使用的select語句;
打開游標：打開時才按定義檢索數據
open cur_name;
使用游標： 通常在while,repeat,loop循環中使用； 游標也可以嵌套；
關閉游標：
close cur_name;
注意：使用后必須關閉游標；存儲過程或函數中定義的局部變量必須放在游標之前，而定義的句柄必須放在游標之后；
?
例：創建一個存儲過程，使用游標輸出orders表中所有的order_num;
但是這樣會有多個結果窗口；
delimiter //
create procedure p_1()
begin
?
declare o int;? #局部變量最先定義
declare done boolean default 0;
declare ordernumbers cursor for #游標在其后定義
select order_num from orders;
declare continue handler for? #處理器在最后定義
sqlstate '02000' set done = 1;
?
open ordernumbers;
fetch ordernumbers into o;
repeat
select o;
#最好將fetch語句放在循環的最后，當其發生異常時能夠立即終止循環；
fetch ordernumbers into o;
until done
end repeat;
close ordernumbers;
?
end //
delimiter ;
注：
fetch cursor ?into var_name：獲取游標當前指向的數據行并將指針指向下一行，然后賦給一個變量；（只能取一行數據）
declare continue handler forsqlstate '02000' set done = 1;
它表示fetch的執行過程中發生滿足sql狀態碼所代表的異常時，會被自動執行的代碼；
?
觸發器trigger: 讓某些語句在事件發生前或后自動執行；
創建觸發器：
delimiter $
create trigger <觸發器名稱>
[before|after] [insert|update|delete]? on <表名稱>
for ench row
?
begin
<觸發器SQL語句>
end $
delimiter ;
支持的事件類型：insert, update, delete;
例：在增加tab1表記錄后，自動將記錄中的tab1_id列中的數據增加到tab2表中；
delimiter $
create trigger t_1
after insert on tab1
/* 一次只能定義一種事件類型；
before update on tab1
after delete on tab1
*/
for each row
?
begin
#在增加tab1表中記錄后，自動將在指定的新增數據添加到tab2表中；
insert into tab2(tab2_id) values (new.tab1_id);
/*
#在tab1表中修改tab1_name列中數據時，會自動將數據變為大寫；
set tab1_name = Upper(new.tab1_name);
#在刪除tab1表中記錄后，自動將指定的刪除數據添加到tab2表中；
insert into tab2(tab2_id) values (old.tab1_id);
*/
end $
delimiter ;
注：
在insert觸發器中是不能使用old關鍵字的，只能用new；
在delete觸發器中是不能使用new關鍵字的，只能用old；
在updeta觸發器中，before時用new， after時用old;
因為new中的值可被更新修改；而old中的值都是只讀的，不能更新修改；
?
事務處理：它是一種用來維護數據庫完整性的機制，可以保證一組操作的原子性，即：要不整體執行，要不執行；如果成功，則寫入數據庫；如果發生錯誤，則可以進行回退到以前的某種狀態；一般用于增刪改操作；
（內部實現的就是讓操作在內存中執行，然后一次性將結果寫入硬盤）
涉及的關鍵字：
transaction:事務
rollback:回退
commit：提交到硬盤
savepoint：保留點，它是事務處理中設置的臨時占位符，可以對它發布回退，使其恢復到之前的狀態；
例：
start transaction; #開始事務處理
delete from tab1 where id= 0;
savepoint point1; #設置保留點
delete from tab1 where id= 1;
delete from tab1 where id= 2;
rollback to point1; #回退到保留點point1之前的狀態；
rollback; #回退到開始事務處理之前的狀態
?
commit; #將結果提交到硬盤
注：如果不用事務處理，Mysql默認是自動提交的，但可以用set autocommit=0 語句，取消自動提交；這樣就可以使用回退操作；
?
安全管理：
創建MySql用戶賬號：
create user 用戶名 indentified by ‘密碼’;
更改用戶名：
rename user … to…;
刪掉用戶名：
drop user 用戶名;
設置訪問權限：
show grants for
?
設置口令：
set password for …… =password(‘ 口令’)； //設置自己的口令
set password for …… =password(‘ 口令’)；
?
數據庫維護：
?
備份數據庫：使用mysqldump命令
?
備份多個數據庫：
mysqldump –u username –p –all –database dbname1 dbname2 > Backup.sql
?
備份所有數據庫：
mysqldump –u username –p –all --database > Backup.sql
注：
-n:不創建數據庫
-d:不導出任何數據，只導出數據庫的表結構
-t:只導出數據
-r:導出存儲過程以及自定義函數
?
還原數據：
mysql –u root –p dbname < backup.sql
?
?
?
數據庫易混點：
數據庫中用“=”表示是否相等而不是“==”；
null和任何值操作的結果都是null;
轉載于:https://www.cnblogs.com/zhaojinxin/p/5722715.html
總結
以上是生活随笔為你收集整理的MySQL基础原创笔记（一）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： FileZilla等软件搭建ftp服务器
- 下一篇： ewebeditor 路径