Num01-->mysql賬戶管理

Test01-->定義

在生產(chǎn)環(huán)境下操作數(shù)據(jù)庫時，絕對不可以使用root賬戶連接，而是創(chuàng)建特定的賬戶，授予這個賬戶特定的操作權限，然后連接進行操作，主要的操作就是數(shù)據(jù)的crud

MySQL賬戶體系：根據(jù)賬戶所具有的權限的不同，MySQL的賬戶可以分為以下幾種

1、服務實例級賬號：啟動了一個mysqld，即為一個數(shù)據(jù)庫實例；如果某用戶如root,擁有服務實例級分配的權限，那么該賬號就可以刪除所有的數(shù)據(jù)庫、連同這些庫中的表

2、數(shù)據(jù)庫級別賬號：對特定數(shù)據(jù)庫執(zhí)行增刪改查的所有操作

3、數(shù)據(jù)表級別賬號：對特定表執(zhí)行增刪改查等所有操作

4、字段級別的權限：對某些表的特定字段進行操作

5、存儲程序級別的賬號：對存儲程序進行增刪改查的操作

6、賬戶的操作主要包括創(chuàng)建賬戶、刪除賬戶、修改密碼、授于權限等

注意：進行賬戶操作時，需要使用root賬戶登錄，這個賬戶擁有最高的實例級權限

Test02-->授予權限

需要使用實例級賬戶登錄后操作，以root為例
常用權限主要包括：create、alter、drop、insert、update、delete、select
如果分配所有權限，可以使用all privileges

創(chuàng)建賬戶并授權
語法如下：
grant 權限列表 on 數(shù)據(jù)庫 to '用戶名'@'訪問主機' identified by '密碼';

授權
語法如下：
grant 權限名稱 on 數(shù)據(jù)庫 to 賬戶1,賬戶2,... with grant option;

示例：
step1：使用root登錄
mysql -uroot -p
回車后寫密碼，然后回車

step2：創(chuàng)建賬戶并授予所有權限，說明如下
用戶名為py1，密碼為123
操作python數(shù)據(jù)庫的所有對象python.*
訪問主機通常使用百分號%表示此賬戶可以使用任何ip的主機登錄訪問此數(shù)據(jù)庫
訪問主機可以設置成localhost或具體的ip，表示只允許本機或特定主機訪問
grant all privileges on python.* to 'py1'@'%' identified by '123';

step3：退出root的登錄
quit

step4：使用py1賬戶登錄
mysql -u py1 -p
回車后寫密碼，然后回車

Test03-->回收權限

需要使用實例級賬戶登錄后操作，以root為例
如果不希望某用戶擁有此權限，可以將此權限從用戶上撤銷
語法如下：
revoke 權限列表 on 數(shù)據(jù)庫名.* from  '用戶名'@'主機';

示例
step1：使用py1登錄后，向表classes中插入數(shù)據(jù)python3
use python;
insert into classes(name) values('python3');

step2：退出py1
quit
使用root登錄
mysql -uroot -p
回車后寫密碼，然后回車

step3：回收insert權限
revoke insert on python.* from 'py1'@'%';

step4：退出root
quit

step5：使用py1賬戶登錄
mysql -u py1 -p
回車后寫密碼，然后回車

step6：向表classes中插入數(shù)據(jù)python3，就會報錯
use python;
insert into classes(name) values('python3');

Test04-->賬戶操作

需要使用實例級賬戶登錄后操作，以root為例
主要操作包括：
查看所有用戶
修改密碼
刪除用戶

1、查看所有用戶
所有用戶及權限信息存儲在mysql數(shù)據(jù)庫的user表中
查看user表的結構
desc user\G;

主要字段說明：
host表示允許訪問的主機
user表示用戶名
authentication_string表示密碼，為加密后的值
查看所有用戶
select host,user,authentication_string from user;

2、修改密碼
語法1：不需登錄
mysqladmin -u py1 -p password '新密碼'
例：
mysqladmin -u py1 -p password '123456'
回車后寫密碼，然后回車
語法2：使用root登錄，修改mysql數(shù)據(jù)庫的user表
使用password()函數(shù)進行密碼加密
注意修改完成后需要刷新權限
update user set authentication_string=password('新密碼') where user='用戶名';
例：
update user set authentication_string=password('123') where user='py1';

刷新權限：flush privileges
語法1用于賬戶自己修改密碼
語法2用于修改自己或其它賬戶的密碼，一般是dba或經(jīng)理修改員工的密碼

3、刪除賬戶
語法1：使用root登錄
drop user '用戶名'@'主機';
例：
drop user 'py1'@'%';
語法2：使用root登錄，刪除mysql數(shù)據(jù)庫的user表中數(shù)據(jù)
delete from user where user='用戶名';
例：
delete from user where user='py1';
推薦使用語法1刪除用戶
如果主機的字母大寫時，使用語法1刪除失敗，采用語法2方式

Num02-->mysql存儲過程

Test01-->定義

存儲過程，也翻譯為存儲程序，是一條或者多條SQL語句的集合，可以視為批處理，但是其作用不僅僅局限于批處理

Test02-->mysql創(chuàng)建存儲過程

語法如下
delimiter //
create procedure 存儲過程名稱(參數(shù)列表)
begin
sql語句
end
//
delimiter ;
說明：delimiter用于設置分割符，默認為分號
在“sql語句”部分編寫的語句需要以分號結尾，此時回車會直接執(zhí)行，所以要創(chuàng)建存儲過程前需要指定其它符號作為分割符，此處使用//，也可以使用其它字符

示例
要求：創(chuàng)建查詢過程，查詢學生信息
step1：設置分割符
delimiter //
step2：創(chuàng)建存儲過程
create procedure proc_stu()
begin
select * from students;
end
//
step3：還原分割符
delimiter ;

Test03-->mysql查看存儲過程

所有存儲過程和函數(shù)，都存儲在mysql數(shù)據(jù)庫下的proc表中
查看表結構
desc mysql.proc\G;

主要字段說明：
name表示名稱
type表示類型，為存儲過程、函數(shù)
body表示正文腳本
db表示屬于的數(shù)據(jù)庫

查看python數(shù)據(jù)庫的所有存儲過程
select name,type,body from mysql.proc where db='python';

Test04-->mysql調用存儲過程

語法如下
call 存儲過程(參數(shù)列表);

示例：
要求：調用存儲過程proc_stu
call proc_stu();

Test05-->mysql刪除存儲過程

語法如下
drop procedure 存儲過程名稱;
說明：存儲過程內(nèi)部的sql語句無法修改，如果之前創(chuàng)建的存儲過程不能滿足要求，可以刪除后重新創(chuàng)建

示例：
要求：刪除存儲過程proc_stu
drop procedure proc_stu;

Num03-->mysql函數(shù)

Test01-->mysql創(chuàng)建函數(shù)

語法如下
delimiter $$
create function 函數(shù)名稱(參數(shù)列表) returns 返回類型
begin
sql語句
end
$$
delimiter ;
說明：delimiter用于設置分割符，默認為分號
在“sql語句”部分編寫的語句需要以分號結尾，此時回車會直接執(zhí)行，所以要創(chuàng)建存儲過程前需要指定其它符號作為分割符，此處使用//，也可以使用其它字符
示例

要求：創(chuàng)建函數(shù)py_trim，用于刪除字符串左右兩側的空格

step1：設置分割符
delimiter $$

step2：創(chuàng)建函數(shù)
create function py_trim(str varchar(100)) returns varchar(100)
begin
declare x varchar(100);
set x=ltrim(rtrim(str));
return x;
end
$$

step3：還原分割符
delimiter ;

Test02-->mysql查看函數(shù)

所有函數(shù)存儲在mysql數(shù)據(jù)庫下的proc表中
說明：存儲過程與函數(shù)都存儲在proc表中，區(qū)別在type字段，func表中無數(shù)據(jù)

查看python數(shù)據(jù)庫中的函數(shù)
select name,type from mysql.proc where db='python';

Test03-->mysql函數(shù)調用

語法如下
select 函數(shù)名稱(參數(shù)列表);

示例
要求：調用自定義函數(shù)py_trim
select py_trim(' a ');

Test04-->mysql函數(shù)刪除

說明：函數(shù)的代碼無法修改，如果不能滿足要求可以刪除后重新創(chuàng)建
語法如下
drop function 函數(shù)名稱;

示例
要求：刪除函數(shù)py_trim
drop function py_trim;

Test05-->mysql函數(shù)變量

聲明變量，語法如下
declare 變量名 類型 default 默認值;

例：
declare x varchar(100);
設置變量值，語法如下
set 變量名=值;

例：
set x='abc';
使用變量：將變量寫到表達式中，即可調用變量的值

Test06-->mysql函數(shù)判斷

語法如下
if 條件1 then
語句1;
elseif 條件2 then
語句2;
else
語句
end if;

Test07-->mysql函數(shù)循環(huán)

語法如下
while 條件 do
語句;
end while;

退出循環(huán)：leave，相當于break
退出本次循環(huán)：iterate，相當于continue

Num04-->mysql視圖

對于復雜的查詢，在多個地方被使用，如果需求發(fā)生了改變，需要更改sql語句，則需要在多個地方進行修改，維護起來非常麻煩
解決：定義視圖
視圖本質就是對查詢的封裝
定義視圖，建議以v_開頭
create view 視圖名稱 as select語句;

例：創(chuàng)建視圖，查詢學生對應的成績信息
create view v_stu_sco as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

查看視圖：查看表會將所有的視圖也列出來
show tables;

刪除視圖
drop view 視圖名稱;

例：
drop view v_stu_sco;

使用：視圖的用途就是查詢
select * from v_stu_score;

Num05-->mysql事務

Test01-->定義

為什么要有事務

事務廣泛的運用于訂單系統(tǒng)、銀行系統(tǒng)等多種場景
例如：A用戶和B用戶是銀行的儲戶，現(xiàn)在A要給B轉賬500元，那么需要做以下幾件事：
檢查A的賬戶余額>500元；
A賬戶扣除500元；
B賬戶增加500元；
正常的流程走下來，A賬戶扣了500，B賬戶加了500，皆大歡喜。那如果A賬戶扣了錢之后，系統(tǒng)出故障了呢？A白白損失了500，而B也沒有收到本該屬于他的500。以上的案例中，隱藏著一個前提條件：A扣錢和B加錢，要么同時成功，要么同時失敗。事務的需求就在于此
事務(Transaction)是并發(fā)控制的基本單位。所謂事務,它是一個操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個不可分割的工作單位。例如，銀行轉帳工作：從一個帳號扣款并使另一個帳號增款，這兩個操作要么都執(zhí)行，要么都不執(zhí)行。所以，應該把他們看成一個事務。事務是數(shù)據(jù)庫維護數(shù)據(jù)一致性的單位，在每個事務結束時，都能保持數(shù)據(jù)一致性
事務四大特性(簡稱ACID)

原子性(Atomicity)：事務中的全部操作在數(shù)據(jù)庫中是不可分割的，要么全部完成，要么均不執(zhí)行
一致性(Consistency)：幾個并行執(zhí)行的事務，其執(zhí)行結果必須與按某一順序串行執(zhí)行的結果相一致
隔離性(Isolation)：事務的執(zhí)行不受其他事務的干擾，事務執(zhí)行的中間結果對其他事務必須是透明的
持久性(Durability)：對于任意已提交事務，系統(tǒng)必須保證該事務對數(shù)據(jù)庫的改變不被丟失，即使數(shù)據(jù)庫出現(xiàn)故障
事務命令

要求：表的引擎類型必須是innodb類型才可以使用事務，這是mysql表的默認引擎
查看表的創(chuàng)建語句，可以看到engine=innodb
show create table students;
修改數(shù)據(jù)的命令會觸發(fā)事務，包括insert、update、delete

開啟事務，命令如下：

開啟事務后執(zhí)行修改命令，變更會維護到本地緩存中，而不維護到物理表中
begin;
提交事務，命令如下
將緩存中的數(shù)據(jù)變更維護到物理表中
commit;
回滾事務，命令如下：
放棄緩存中變更的數(shù)據(jù)
rollback;

Test02-->提交

為了演示效果，需要打開兩個終端窗口，使用同一個數(shù)據(jù)庫，操作同一張表
step1：連接
終端1：查詢學生信息
select * from students;

step2：增加數(shù)據(jù)
終端2：開啟事務，插入數(shù)據(jù)
begin;
insert into students(sname) values('張飛');
終端2：查詢數(shù)據(jù)，此時有新增的數(shù)據(jù)
select * from students;

step3：查詢
終端1：查詢數(shù)據(jù)，發(fā)現(xiàn)并沒有新增的數(shù)據(jù)
select * from students;

step4：提交
終端2：完成提交
commit;

step5：查詢
終端1：查詢，發(fā)現(xiàn)有新增的數(shù)據(jù)
select * from students;

Test03-->回滾

為了演示效果，需要打開兩個終端窗口，使用同一個數(shù)據(jù)庫，操作同一張表

step1：連接
終端1
select * from students;

step2：增加數(shù)據(jù)
終端2：開啟事務，插入數(shù)據(jù)
begin;
insert into students(sname) values('張飛');
終端2：查詢數(shù)據(jù)，此時有新增的數(shù)據(jù)
select * from students;

step3：查詢
終端1：查詢數(shù)據(jù)，發(fā)現(xiàn)并沒有新增的數(shù)據(jù)
select * from students;

step4：回滾
終端2：完成回滾
rollback;

step5：查詢
終端1：查詢數(shù)據(jù)，發(fā)現(xiàn)沒有新增的數(shù)據(jù)
select * from students;

Num06-->mysql索引

思考：在圖書館中是如何找到一本書的？
一般的應用系統(tǒng)對比數(shù)據(jù)庫的讀寫比例在10:1左右，而且插入操作和更新操作很少出現(xiàn)性能問題，遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，所以查詢語句的優(yōu)化顯然是重中之重
當數(shù)據(jù)庫中數(shù)據(jù)量很大時，查找數(shù)據(jù)會變得很慢

優(yōu)化方案：索引
主鍵和唯一索引，都是索引，可以提高查詢速度

主鍵是數(shù)據(jù)物理存儲的位置
索引會單獨創(chuàng)建一個目錄，對應數(shù)據(jù)的位置

索引分單列索引和組合索引
單列索引，即一個索引只包含單個列，一個表可以有多個單列索引
組合索引，即一個索引包含多個列

語法
查看索引
show index from 表名;

創(chuàng)建索引
如果指定字段是字符串，需要指定長度，建議長度與定義字段時的長度一致
字段類型如果不是字符串，可以不填寫長度部分
create index 索引名稱 on 表名(字段名稱(長度))

刪除索引：
drop index 索引名稱 on 表名;
缺點
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE，因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件
建立索引會占用磁盤空間的索引文件
示例
創(chuàng)建測試表testindex
create table test_index(title varchar(10));
向表中加入十萬條數(shù)據(jù)
創(chuàng)建存儲過程proc_test，在存儲過程中實現(xiàn)插入數(shù)據(jù)的操作

step1：定義分割符
delimiter //

step2：定義存儲過程
create procedure proc_test()
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end //

step3：還原分割符
delimiter ;
執(zhí)行存儲過程proc_test
call proc_test();
查詢
開啟運行時間監(jiān)測：
set profiling=1;
查找第1萬條數(shù)據(jù)test10000
select * from test_index where title='test10000';
查看執(zhí)行的時間：
show profiles;
為表title_index的title列創(chuàng)建索引：
create index title_index on test_index(title(10));
執(zhí)行查詢語句：
select * from test_index where title='test10000';
再次查看執(zhí)行的時間
show profiles;

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySql高級知識總結

MySql高級知識總結

Num01-->mysql賬戶管理

Test01-->定義

Test02-->授予權限

Test03-->回收權限

Test04-->賬戶操作

Num02-->mysql存儲過程

Test01-->定義

Test02-->mysql創(chuàng)建存儲過程

Test03-->mysql查看存儲過程

Test04-->mysql調用存儲過程

Test05-->mysql刪除存儲過程

Num03-->mysql函數(shù)

Test01-->mysql創(chuàng)建函數(shù)

Test02-->mysql查看函數(shù)

Test03-->mysql函數(shù)調用

Test04-->mysql函數(shù)刪除

Test05-->mysql函數(shù)變量

Test06-->mysql函數(shù)判斷

Test07-->mysql函數(shù)循環(huán)

Num04-->mysql視圖

Num05-->mysql事務

Test01-->定義

Test02-->提交

Test03-->回滾

Num06-->mysql索引

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySql高級知識總結

Num01-->mysql賬戶管理

Test01-->定義

Test02-->授予權限

Test03-->回收權限

Test04-->賬戶操作

Num02-->mysql存儲過程

Test01-->定義

Test02-->mysql創(chuàng)建存儲過程

Test03-->mysql查看存儲過程

Test04-->mysql調用存儲過程

Test05-->mysql刪除存儲過程

Num03-->mysql函數(shù)

Test01-->mysql創(chuàng)建函數(shù)

Test02-->mysql查看函數(shù)

Test03-->mysql函數(shù)調用

Test04-->mysql函數(shù)刪除

Test05-->mysql函數(shù)變量

Test06-->mysql函數(shù)判斷

Test07-->mysql函數(shù)循環(huán)

Num04-->mysql視圖

Num05-->mysql事務

Test01-->定義

Test02-->提交

Test03-->回滾

Num06-->mysql索引

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av