前言導讀
在日常開(kāi)發(fā)中，存儲數據的最常用的方式便是數據庫了，其中最為著(zhù)名的便是
MySQL數據庫，因他簡(jiǎn)便易于上手而且可擴展性強大，跨平臺使得它廣為使用，今天我們就來(lái)具體聊聊。
1.安裝
2.數據類(lèi)型
3.庫 表常見(jiàn)操作
4.索引
5.視圖
6.函數
7.游標
8.觸發(fā)器
9.存儲過(guò)程
10.事務(wù)
11.備份與還原
12.用戶(hù)賬號
13.其他
一、安裝過(guò)程
我們所使用的是MySQL 5.7版本，這個(gè)版本算是挺不錯的。下面我們來(lái)介紹下這個(gè)版本的具體安裝過(guò)程吧。
1.解壓安裝
因為官網(wǎng)目前下載不了，為了大家安裝過(guò)程中方便，我們下載一個(gè)免安裝版mysql，
地址：
https://590m.com/file/7715018-442009503，下載好后將這個(gè)壓縮包文件解壓到指定的磁盤(pán)下，比如我想解壓到E盤(pán)的mysql文件夾中。如圖：
為了讓mysql 可以在任意目錄運行，我們可以將mysql的bin目錄添加到環(huán)境變量，如圖：
3.修改配置文件
在mysql目錄下創(chuàng )建一個(gè)my.ini配置文件，修改配置：
[mysql]# 設置mysql客戶(hù)端默認字符集default-character-set=utf8[mysqld]#設置3306端口port = 3306# 設置mysql的安裝目錄basedir='E:\mysql\mysql-5.7.11-winx64'# 設置mysql數據庫的數據的存放目錄datadir='E:\mysql\mysql-5.7.11-winx64\data'# 允許最大連接數max_connections=200# 服務(wù)端使用的字符集默認為8比特編碼的latin1字符集character-set-server=utf8# 創(chuàng )建新表時(shí)將使用的默認存儲引擎default-storage-engine=INNODB4.安裝mysql
以管理員身份運行命令提示符，輸入mysqld install命令（注意是mysqld而不是mysql）回車(chē)，即可安裝MySQL，提示：Service successfully installed，則表示安裝成功，如下圖所示：
移除服務(wù)：mysqld remove
5.啟動(dòng)mysql服務(wù)
在命令提示符下執行命令，
啟動(dòng)mysql：net start mysql
停止：net stop mysql
發(fā)現啟動(dòng)不了，如圖：
這是怎么回事了，哦~原來(lái)是因為還沒(méi)有初始化，那么我們現在來(lái)進(jìn)行初始化，
執行指令：mysqld --initialize-insecure --user=mysql，
mysqld --initialize-insecure：自動(dòng)生成無(wú)密碼的root用戶(hù)，
mysqld --initialize：自動(dòng)生成帶隨機密碼的root用戶(hù)，
mysqld --initialize --user=mysql --console：生成隨機密碼并顯示在控制臺窗口，
data文件夾不為空是不能執行這個(gè)命令的（刪掉data文件即可），
執行mysqld --initialize-insecure --user=mysql命令會(huì )自動(dòng)新建data文件夾。執行結果如圖：
可以看到執行成功了，再次啟動(dòng)mysql服務(wù)，啟動(dòng)成功。如圖：
6.進(jìn)入mysql，修改登陸密碼確保安全
第一次進(jìn)入mysql 是沒(méi)有密碼就可以登陸了，我們可以設置登陸密碼，
更改root密碼，輸入：
update mysql.user set password=password('123321') where user = 'root';因為新版mysql數據庫下沒(méi)有password字段，所以需要使用如下命令：
update mysql.user set authentication_string=password('123321') where user='root';刷新權限：flush privileges;這是必須的，不然操作沒(méi)有效果。
也可以這樣修改密碼：
set password = password('123456');這樣就將root用戶(hù)的密碼設置為了 123456了，下面我們退出mysql再重新進(jìn)去，順帶說(shuō)下退出命令：
退出mysql：quit;接下來(lái)在進(jìn)入mysql，如圖：
這樣root用戶(hù)就有了密碼，他的密碼就是123321.
整個(gè)安裝過(guò)程就完成了，是不是很簡(jiǎn)單了。
另外，為了提高交互效果，我們還可以一個(gè)軟件-------sqlyong，他可以和mysql建立鏈接，
下載地址：
https://590m.com/file/7715018-442029096，
然后輸入壓縮包內自帶的注冊碼破解就可以免費使用了。
2.mysql 數據類(lèi)型
1.數值類(lèi)型
MySQL支持所有標準SQL數值數據類(lèi)型。
這些類(lèi)型包括嚴格數值數據類(lèi)型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類(lèi)型(FLOAT、REAL和DOUBLE PRECISION)。
關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。
BIT數據類(lèi)型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作為SQL標準的擴展，MySQL也支持整數類(lèi)型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個(gè)整數類(lèi)型的存儲和范圍。
類(lèi)型 大小 范圍（有符號） 范圍（無(wú)符號） 用途 TINYINT 1 字節 (-128，127) (0，255) 小整數值 SMALLINT 2 字節 (-32 768，32 767) (0，65 535) 大整數值 MEDIUMINT 3 字節 (-8 388 608，8 388 607) (0，16 777 215) 大整數值 INT或INTEGER 4 字節 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整數值 BIGINT 8 字節 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 極大整數值 FLOAT 4 字節 (-3.402 823 466 E 38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E 38) 0，(1.175 494 351 E-38，3.402 823 466 E 38) 單精度 浮點(diǎn)數值 DOUBLE 8 字節 (-1.797 693 134 862 315 7 E 308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308) 雙精度 浮點(diǎn)數值 DECIMAL 對DECIMAL(M,D) ，如果M>D，為M 2否則為D 2 依賴(lài)于M和D的值 依賴(lài)于M和D的值 小數值
2.日期和時(shí)間類(lèi)型
表示時(shí)間值的日期和時(shí)間類(lèi)型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每個(gè)時(shí)間類(lèi)型有一個(gè)有效值范圍和一個(gè)'零'值，當指定不合法的MySQL不能表示的值時(shí)使用'零'值。
TIMESTAMP類(lèi)型有專(zhuān)有的自動(dòng)更新特性，將在后面描述。
類(lèi)型 大小 (*字節)** 范圍 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 時(shí)間值或持續時(shí)間 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時(shí)間值 TIMESTAMP 4 1970-01-01 00:00:00/2037 年某時(shí) YYYYMMDD HHMMSS 混合日期和時(shí)間值，時(shí)間戳
3.字符串類(lèi)型
字符串類(lèi)型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類(lèi)型如何工作以及如何在查詢(xún)中使用這些類(lèi)型。
類(lèi)型 大小 用途 CHAR 0-255字節 定長(cháng)字符串 VARCHAR 0-65535 字節 變長(cháng)字符串 TINYBLOB 0-255字節 不超過(guò) 255 個(gè)字符的二進(jìn)制字符串 TINYTEXT 0-255字節 短文本字符串 BLOB 0-65 535字節 二進(jìn)制形式的長(cháng)文本數據 TEXT 0-65 535字節 長(cháng)文本數據 MEDIUMBLOB 0-16 777 215字節 二進(jìn)制形式的中等長(cháng)度文本數據 MEDIUMTEXT 0-16 777 215字節 中等長(cháng)度文本數據 LONGBLOB 0-4 294 967 295字節 二進(jìn)制形式的極大文本數據 LONGTEXT 0-4 294 967 295字節 極大文本數據
CHAR和VARCHAR類(lèi)型類(lèi)似，但它們保存和檢索的方式不同。它們的最大長(cháng)度和是否尾部空格被保留等方面也不同。在存儲或檢索過(guò)程中不進(jìn)行大小寫(xiě)轉換。
BINARY和VARBINARY類(lèi)類(lèi)似于CHAR和VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說(shuō)，它們包含字節字符串而不是字符字符串。這說(shuō)明它們沒(méi)有字符集，并且排序和比較基于列值字節的數值值。
BLOB是一個(gè)二進(jìn)制大對象，可以容納可變數量的數據。有4種BLOB類(lèi)型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長(cháng)度不同。
有4種TEXT類(lèi)型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類(lèi)型，有相同的最大長(cháng)度和存儲需求。
Enum('fds','fsa','fasf') ：枚舉類(lèi)型
set(val1,val2,val3)：集合類(lèi)型
3.庫表操作
１、MySQL觸發(fā)器的創(chuàng )建語(yǔ)法：CREATE[DEFINER = { 'user' | CURRENT_USER }]TRIGGER trigger_nametrigger_time trigger_eventON table_nameFOR EACH ROW[trigger_order]trigger_body2、MySQL創(chuàng )建語(yǔ)法中的關(guān)鍵詞解釋?zhuān)鹤侄? 含義 可能的值DEFINER= 可選參數，指定創(chuàng )建者， DEFINER='root@%' 默認為當前登錄用戶(hù)（CURRENT_USER）； 該觸發(fā)器將以此參數指定的用戶(hù)執行， DEFINER=CURRENT_USER 所以需要考慮權限問(wèn)題；?trigger_name 觸發(fā)器名稱(chēng)，最好由表名 觸發(fā)事件關(guān)鍵詞 觸發(fā)時(shí)間關(guān)鍵詞組成； trigger_time 觸發(fā)時(shí)間，在某個(gè)事件之前還是之后； BEFORE、AFTER? INSERT：插入操作觸發(fā)器，INSERT、LOAD DATA、REPLACE時(shí)觸發(fā)； UPDATE：更新操作觸發(fā)器，UPDATE操作時(shí)觸發(fā)；trigger_event 觸發(fā)事件，如插入時(shí)觸發(fā)、刪除時(shí)觸發(fā)； DELETE：刪除操作觸發(fā)器，DELETE、REPLACE操作時(shí)觸發(fā)； INSERT、UPDATE、DELETEtable_name 觸發(fā)操作時(shí)間的表名； 可選參數，如果定義了多個(gè)具有相同觸發(fā)事件和觸法時(shí)間的觸發(fā)器時(shí)（ 如：BEFORE UPDATE），默認觸發(fā)順序與觸發(fā)器的創(chuàng )建順序一致，可以trigger_order 使用此參數來(lái)改變它們觸發(fā)順序。mysql 5.7.2起開(kāi)始支持此參數。 FOLLOWS：當前創(chuàng )建觸發(fā)器在現有觸發(fā)器之后激活； FOLLOWS、PRECEDES PRECEDES：當前創(chuàng )建觸發(fā)器在現有觸發(fā)器之前激活； trigger_body 觸發(fā)執行的SQL語(yǔ)句內容，一般以begin開(kāi)頭，end結尾 BEGIN .. END 觸發(fā)執行語(yǔ)句內容（trigger_body）中的OLD，NEW觸發(fā)執行語(yǔ)句內容（trigger_body）中的OLD，NEW：在trigger_body中，我們可以使用NEW表示將要插入的新行（相當于MS SQL的INSERTED），OLD表示將要刪除的舊行（相當于MS SQL的DELETED）。通過(guò)OLD，NEW中獲取它們的字段內容，方便在觸發(fā)操作中使用，下面是對應事件是否支持OLD、NEW的對應關(guān)系：事件 OLD NEWINSERT × √DELETE √ ×UPDATE √ √?由于UPDATE相當于刪除舊行（OLD），然后插入新行（NEW），所以UPDATE同時(shí)支持OLD、NEW；DELIMITER $... --觸發(fā)器創(chuàng )建語(yǔ)句；$ --提交創(chuàng )建語(yǔ)句；DELIMITER ;??select * FROM information_schema.triggers;SHOW TRIGGERS; #查看觸發(fā)器-- 通過(guò)information_schema.triggers表查看觸發(fā)器：select * FROM information_schema.triggers;-- mysql 查看當前數據庫的觸發(fā)器SHOW TRIGGERS;-- mysql 查看指定數據庫'people'的觸發(fā)器SHOW TRIGGERS FROM people;?創(chuàng )建測試表DROP TABLE IF EXISTS tb;CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);創(chuàng )建觸發(fā)器DELIMITER $#drop trigger if exists df_names$ 刪除前先判斷觸發(fā)器是否存在CREATE DEFINER =CURRENT_USERTRIGGER df_namesBEFORE INSERT ON tbAFTER UPDATE ON btFOR EACH ROWBEGIN #set new.ct=new.id*5; #SET @ct=12; 變量 #SET @pass='hjfd'; IF old.type=1 THEN UPDATE bt SET ct=old.ct WHERE fid=old.id; ELSE IF old.type=2 THEN UPDATE bt SET pass=old.pass WHERE fid=old.id;END$DELIMITER;?測試INSERT INTO tb(id) VALUES(4);SELECT *FROM tb;?4.索引
SET AUTOCOMMIT=off ;禁用或啟用事務(wù)的自動(dòng)提交模式 off ONSET SESSION AUTOCOMMIT = OFF； 禁用或啟用事務(wù)的session自動(dòng)提交模式 off ONSHOW VARIABLES LIKE '%auto%'; -- 查看變量狀態(tài)?執行DML語(yǔ)句是其實(shí)就是開(kāi)啟一個(gè)事務(wù)只能回滾insert、delete和update語(yǔ)句對于create、drop、alter這些無(wú)法回滾事務(wù)只對DML有效果rollback，或者commit后事務(wù)就結束了自動(dòng)提交模式用于決定新事務(wù)如何及何時(shí)啟動(dòng)START TRANSACTION; 啟用自動(dòng)提交模式下顯式地啟動(dòng)事務(wù)COMMIT和ROLLBACK; 禁用自動(dòng)提交模式顯式地提交或回滾-- 事務(wù) transaction 指一組sql語(yǔ)句-- 回退 rollback 指撤銷(xiāo)指定sql語(yǔ)句的過(guò)程-- 提交 commit 指將未存儲的sql語(yǔ)句結果寫(xiě)入數據庫表-- 保留點(diǎn) savepoint 指事務(wù)處理中設置的臨時(shí)占位符，可以對它發(fā)布回退（與回退整個(gè)事務(wù)處理不同）?-- 控制事務(wù)處理# 開(kāi)始事務(wù)及回退 SELECT * FROM ordertotals; # 查看ordertotals表顯示不為空START TRANSACTION; # 開(kāi)始事務(wù)處理 DELETE FROM ordertotals; # 刪除ordertotals表中所有行SELECT * FROM ordertotals; # 查看ordertotals表顯示 為空ROLLBACK; # rollback語(yǔ)句回退 SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表顯示不為空?# commit 提交 START TRANSACTION;DELETE FROM orderitems WHERE order_num = 20010;DELETE FROM orders WHERE order_num = 20010;COMMIT; # 僅在上述兩條語(yǔ)句不出錯時(shí)寫(xiě)出更改 ?# savepoint 保留點(diǎn) # 創(chuàng )建保留點(diǎn)SAVEPOINT delete1;# 回退到保留點(diǎn) ROLLBACK TO delete1;# 釋放保留點(diǎn) RELEASE SAVEPOINT delete1;?-- 更改默認的提交行為 SET autocommit = 0; # 設置autocommit為0（假）指示MySQL不自動(dòng)提交更改5.視圖
１、MySQL觸發(fā)器的創(chuàng )建語(yǔ)法：CREATE[DEFINER = { 'user' | CURRENT_USER }]TRIGGER trigger_nametrigger_time trigger_eventON table_nameFOR EACH ROW[trigger_order]trigger_body2、MySQL創(chuàng )建語(yǔ)法中的關(guān)鍵詞解釋?zhuān)鹤侄? 含義 可能的值DEFINER= 可選參數，指定創(chuàng )建者， DEFINER='root@%' 默認為當前登錄用戶(hù)（CURRENT_USER）； 該觸發(fā)器將以此參數指定的用戶(hù)執行， DEFINER=CURRENT_USER 所以需要考慮權限問(wèn)題；?trigger_name 觸發(fā)器名稱(chēng)，最好由表名 觸發(fā)事件關(guān)鍵詞 觸發(fā)時(shí)間關(guān)鍵詞組成； trigger_time 觸發(fā)時(shí)間，在某個(gè)事件之前還是之后； BEFORE、AFTER? INSERT：插入操作觸發(fā)器，INSERT、LOAD DATA、REPLACE時(shí)觸發(fā)； UPDATE：更新操作觸發(fā)器，UPDATE操作時(shí)觸發(fā)；trigger_event 觸發(fā)事件，如插入時(shí)觸發(fā)、刪除時(shí)觸發(fā)； DELETE：刪除操作觸發(fā)器，DELETE、REPLACE操作時(shí)觸發(fā)； INSERT、UPDATE、DELETEtable_name 觸發(fā)操作時(shí)間的表名； 可選參數，如果定義了多個(gè)具有相同觸發(fā)事件和觸法時(shí)間的觸發(fā)器時(shí)（ 如：BEFORE UPDATE），默認觸發(fā)順序與觸發(fā)器的創(chuàng )建順序一致，可以trigger_order 使用此參數來(lái)改變它們觸發(fā)順序。mysql 5.7.2起開(kāi)始支持此參數。 FOLLOWS：當前創(chuàng )建觸發(fā)器在現有觸發(fā)器之后激活； FOLLOWS、PRECEDES PRECEDES：當前創(chuàng )建觸發(fā)器在現有觸發(fā)器之前激活； trigger_body 觸發(fā)執行的SQL語(yǔ)句內容，一般以begin開(kāi)頭，end結尾 BEGIN .. END 觸發(fā)執行語(yǔ)句內容（trigger_body）中的OLD，NEW觸發(fā)執行語(yǔ)句內容（trigger_body）中的OLD，NEW：在trigger_body中，我們可以使用NEW表示將要插入的新行（相當于MS SQL的INSERTED），OLD表示將要刪除的舊行（相當于MS SQL的DELETED）。通過(guò)OLD，NEW中獲取它們的字段內容，方便在觸發(fā)操作中使用，下面是對應事件是否支持OLD、NEW的對應關(guān)系：事件 OLD NEWINSERT × √DELETE √ ×UPDATE √ √?由于UPDATE相當于刪除舊行（OLD），然后插入新行（NEW），所以UPDATE同時(shí)支持OLD、NEW；DELIMITER $... --觸發(fā)器創(chuàng )建語(yǔ)句；$ --提交創(chuàng )建語(yǔ)句；DELIMITER ;??select * FROM information_schema.triggers;SHOW TRIGGERS; #查看觸發(fā)器-- 通過(guò)information_schema.triggers表查看觸發(fā)器：select * FROM information_schema.triggers;-- mysql 查看當前數據庫的觸發(fā)器SHOW TRIGGERS;-- mysql 查看指定數據庫'people'的觸發(fā)器SHOW TRIGGERS FROM people;?創(chuàng )建測試表DROP TABLE IF EXISTS tb;CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);創(chuàng )建觸發(fā)器DELIMITER $#drop trigger if exists df_names$ 刪除前先判斷觸發(fā)器是否存在CREATE DEFINER =CURRENT_USERTRIGGER df_namesBEFORE INSERT ON tbAFTER UPDATE ON btFOR EACH ROWBEGIN #set new.ct=new.id*5; #SET @ct=12; 變量 #SET @pass='hjfd'; IF old.type=1 THEN UPDATE bt SET ct=old.ct WHERE fid=old.id; ELSE IF old.type=2 THEN UPDATE bt SET pass=old.pass WHERE fid=old.id;END$DELIMITER;?測試INSERT INTO tb(id) VALUES(4);SELECT *FROM tb;?6.函數
SET AUTOCOMMIT=off ;禁用或啟用事務(wù)的自動(dòng)提交模式 off ONSET SESSION AUTOCOMMIT = OFF； 禁用或啟用事務(wù)的session自動(dòng)提交模式 off ONSHOW VARIABLES LIKE '%auto%'; -- 查看變量狀態(tài)?執行DML語(yǔ)句是其實(shí)就是開(kāi)啟一個(gè)事務(wù)只能回滾insert、delete和update語(yǔ)句對于create、drop、alter這些無(wú)法回滾事務(wù)只對DML有效果rollback，或者commit后事務(wù)就結束了自動(dòng)提交模式用于決定新事務(wù)如何及何時(shí)啟動(dòng)START TRANSACTION; 啟用自動(dòng)提交模式下顯式地啟動(dòng)事務(wù)COMMIT和ROLLBACK; 禁用自動(dòng)提交模式顯式地提交或回滾-- 事務(wù) transaction 指一組sql語(yǔ)句-- 回退 rollback 指撤銷(xiāo)指定sql語(yǔ)句的過(guò)程-- 提交 commit 指將未存儲的sql語(yǔ)句結果寫(xiě)入數據庫表-- 保留點(diǎn) savepoint 指事務(wù)處理中設置的臨時(shí)占位符，可以對它發(fā)布回退（與回退整個(gè)事務(wù)處理不同）?-- 控制事務(wù)處理# 開(kāi)始事務(wù)及回退 SELECT * FROM ordertotals; # 查看ordertotals表顯示不為空START TRANSACTION; # 開(kāi)始事務(wù)處理 DELETE FROM ordertotals; # 刪除ordertotals表中所有行SELECT * FROM ordertotals; # 查看ordertotals表顯示 為空ROLLBACK; # rollback語(yǔ)句回退 SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表顯示不為空?# commit 提交 START TRANSACTION;DELETE FROM orderitems WHERE order_num = 20010;DELETE FROM orders WHERE order_num = 20010;COMMIT; # 僅在上述兩條語(yǔ)句不出錯時(shí)寫(xiě)出更改 ?# savepoint 保留點(diǎn) # 創(chuàng )建保留點(diǎn)SAVEPOINT delete1;# 回退到保留點(diǎn) ROLLBACK TO delete1;# 釋放保留點(diǎn) RELEASE SAVEPOINT delete1;?-- 更改默認的提交行為 SET autocommit = 0; # 設置autocommit為0（假）指示MySQL不自動(dòng)提交更改7.游標
創(chuàng )建、打開(kāi)、關(guān)閉游標 # 定義名為ordernumbers的游標，檢索所有訂單DELIMITER //CREATE PROCEDURE processorders()BEGIN -- decalre the cursor 聲明游標 declare ordernumbers CURSOR FOR SELECT order_num FROM orders; -- open the cursor 打開(kāi)游標 open ordernumbers; -- close the cursor 關(guān)閉游標 close ordernumbers;END //DELIMITER ;?-- 使用游標數據 # 例1：檢索 當前行 的order_num列，對數據不做實(shí)際處理DELIMITER //CREATE PROCEDURE processorders()BEGIN? -- declare local variables 聲明局部變量 DECLARE o INT; -- decalre the cursor 聲明游標 declare ordernumbers CURSOR FOR SELECT order_num FROM orders; -- open the cursor 打開(kāi)游標 open ordernumbers; -- get order number 獲得訂單號 FETCH ordernumbers INTO o; /*fetch檢索 當前行 的order_num列（將自動(dòng)從第一行開(kāi)始）到一個(gè)名為o的局部聲明變量中。 對檢索出的數據不做任何處理。*/ -- close the cursor 關(guān)閉游標 close ordernumbers;?END //DELIMITER ;?# 例2：循環(huán)檢索數據，從第一行到最后一行，對數據不做實(shí)際處理DELIMITER //CREATE PROCEDURE processorders()BEGIN -- declare local variables 聲明局部變量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; -- decalre the cursor 聲明游標 declare ordernumbers CURSOR FOR SELECT order_num FROM orders; -- declare continue handler DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1; -- SQLSTATE '02000'是一個(gè)未找到條件，當REPEAT由于沒(méi)有更多的行供循環(huán)而不能繼續時(shí)，出現這個(gè)條件。 -- open the cursor 打開(kāi)游標 open ordernumbers; -- loop through all rows 遍歷所有行 REPEAT -- get order number 獲得訂單號 FETCH ordernumbers INTO o; -- FETCH在REPEAT內，因此它反復執行直到done為真 -- end of loop UNTIL done END REPEAT; -- close the cursor 關(guān)閉游標 close ordernumbers;?END //DELIMITER ;??# 例3：循環(huán)檢索數據，從第一行到最后一行，對取出的數據進(jìn)行某種實(shí)際的處理DELIMITER //CREATE PROCEDURE processorders()BEGIN -- declare local variables 聲明局部變量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); -- declare the cursor 聲明游標 DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; -- declare continue handler DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- create a table to store the results 新建表以保存數據 CREATE TABLE IF NOT EXISTS ordertotals (order_num INT,total DECIMAL(8,2)); -- open the cursor 打開(kāi)游標 OPEN ordernumbers; -- loop through all rows 遍歷所有行 REPEAT -- get order number 獲取訂單號 FETCH ordernumbers INTO o; -- get the total for this order 計算訂單金額 CALL ordertotal(o,1,t); # 參見(jiàn)23章代碼，已創(chuàng )建可使用 -- insert order and total into ordertotals 將訂單號、金額插入表ordertotals內 INSERT INTO ordertotals(order_num,total) VALUES(o,t); -- end of loop UNTIL done END REPEAT; -- close the cursor 關(guān)閉游標 close ordernumbers;?END // DELIMITER ;# 調用存儲過(guò)程 precessorders()CALL processorders();# 輸出結果SELECT * FROM ordertotals;?8.觸發(fā)器
１、MySQL觸發(fā)器的創(chuàng )建語(yǔ)法：CREATE[DEFINER = { 'user' | CURRENT_USER }]TRIGGER trigger_nametrigger_time trigger_eventON table_nameFOR EACH ROW[trigger_order]trigger_body2、MySQL創(chuàng )建語(yǔ)法中的關(guān)鍵詞解釋?zhuān)鹤侄? 含義 可能的值DEFINER= 可選參數，指定創(chuàng )建者， DEFINER='root@%' 默認為當前登錄用戶(hù)（CURRENT_USER）； 該觸發(fā)器將以此參數指定的用戶(hù)執行， DEFINER=CURRENT_USER 所以需要考慮權限問(wèn)題；?trigger_name 觸發(fā)器名稱(chēng)，最好由表名 觸發(fā)事件關(guān)鍵詞 觸發(fā)時(shí)間關(guān)鍵詞組成； trigger_time 觸發(fā)時(shí)間，在某個(gè)事件之前還是之后； BEFORE、AFTER? INSERT：插入操作觸發(fā)器，INSERT、LOAD DATA、REPLACE時(shí)觸發(fā)； UPDATE：更新操作觸發(fā)器，UPDATE操作時(shí)觸發(fā)；trigger_event 觸發(fā)事件，如插入時(shí)觸發(fā)、刪除時(shí)觸發(fā)； DELETE：刪除操作觸發(fā)器，DELETE、REPLACE操作時(shí)觸發(fā)； INSERT、UPDATE、DELETEtable_name 觸發(fā)操作時(shí)間的表名； 可選參數，如果定義了多個(gè)具有相同觸發(fā)事件和觸法時(shí)間的觸發(fā)器時(shí)（ 如：BEFORE UPDATE），默認觸發(fā)順序與觸發(fā)器的創(chuàng )建順序一致，可以trigger_order 使用此參數來(lái)改變它們觸發(fā)順序。mysql 5.7.2起開(kāi)始支持此參數。 FOLLOWS：當前創(chuàng )建觸發(fā)器在現有觸發(fā)器之后激活； FOLLOWS、PRECEDES PRECEDES：當前創(chuàng )建觸發(fā)器在現有觸發(fā)器之前激活； trigger_body 觸發(fā)執行的SQL語(yǔ)句內容，一般以begin開(kāi)頭，end結尾 BEGIN .. END 觸發(fā)執行語(yǔ)句內容（trigger_body）中的OLD，NEW觸發(fā)執行語(yǔ)句內容（trigger_body）中的OLD，NEW：在trigger_body中，我們可以使用NEW表示將要插入的新行（相當于MS SQL的INSERTED），OLD表示將要刪除的舊行（相當于MS SQL的DELETED）。通過(guò)OLD，NEW中獲取它們的字段內容，方便在觸發(fā)操作中使用，下面是對應事件是否支持OLD、NEW的對應關(guān)系：事件 OLD NEWINSERT × √DELETE √ ×UPDATE √ √?由于UPDATE相當于刪除舊行（OLD），然后插入新行（NEW），所以UPDATE同時(shí)支持OLD、NEW；DELIMITER $... --觸發(fā)器創(chuàng )建語(yǔ)句；$ --提交創(chuàng )建語(yǔ)句；DELIMITER ;??select * FROM information_schema.triggers;SHOW TRIGGERS; #查看觸發(fā)器-- 通過(guò)information_schema.triggers表查看觸發(fā)器：select * FROM information_schema.triggers;-- mysql 查看當前數據庫的觸發(fā)器SHOW TRIGGERS;-- mysql 查看指定數據庫'people'的觸發(fā)器SHOW TRIGGERS FROM people;?創(chuàng )建測試表DROP TABLE IF EXISTS tb;CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);創(chuàng )建觸發(fā)器DELIMITER $#drop trigger if exists df_names$ 刪除前先判斷觸發(fā)器是否存在CREATE DEFINER =CURRENT_USERTRIGGER df_namesBEFORE INSERT ON tbAFTER UPDATE ON btFOR EACH ROWBEGIN #set new.ct=new.id*5; #SET @ct=12; 變量 #SET @pass='hjfd'; IF old.type=1 THEN UPDATE bt SET ct=old.ct WHERE fid=old.id; ELSE IF old.type=2 THEN UPDATE bt SET pass=old.pass WHERE fid=old.id;END$DELIMITER;?測試INSERT INTO tb(id) VALUES(4);SELECT *FROM tb;?9.存儲過(guò)程
SET AUTOCOMMIT=off ;禁用或啟用事務(wù)的自動(dòng)提交模式 off ONSET SESSION AUTOCOMMIT = OFF； 禁用或啟用事務(wù)的session自動(dòng)提交模式 off ONSHOW VARIABLES LIKE '%auto%'; -- 查看變量狀態(tài)?執行DML語(yǔ)句是其實(shí)就是開(kāi)啟一個(gè)事務(wù)只能回滾insert、delete和update語(yǔ)句對于create、drop、alter這些無(wú)法回滾事務(wù)只對DML有效果rollback，或者commit后事務(wù)就結束了自動(dòng)提交模式用于決定新事務(wù)如何及何時(shí)啟動(dòng)START TRANSACTION; 啟用自動(dòng)提交模式下顯式地啟動(dòng)事務(wù)COMMIT和ROLLBACK; 禁用自動(dòng)提交模式顯式地提交或回滾-- 事務(wù) transaction 指一組sql語(yǔ)句-- 回退 rollback 指撤銷(xiāo)指定sql語(yǔ)句的過(guò)程-- 提交 commit 指將未存儲的sql語(yǔ)句結果寫(xiě)入數據庫表-- 保留點(diǎn) savepoint 指事務(wù)處理中設置的臨時(shí)占位符，可以對它發(fā)布回退（與回退整個(gè)事務(wù)處理不同）?-- 控制事務(wù)處理# 開(kāi)始事務(wù)及回退 SELECT * FROM ordertotals; # 查看ordertotals表顯示不為空START TRANSACTION; # 開(kāi)始事務(wù)處理 DELETE FROM ordertotals; # 刪除ordertotals表中所有行SELECT * FROM ordertotals; # 查看ordertotals表顯示 為空ROLLBACK; # rollback語(yǔ)句回退 SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表顯示不為空?# commit 提交 START TRANSACTION;DELETE FROM orderitems WHERE order_num = 20010;DELETE FROM orders WHERE order_num = 20010;COMMIT; # 僅在上述兩條語(yǔ)句不出錯時(shí)寫(xiě)出更改 ?# savepoint 保留點(diǎn) # 創(chuàng )建保留點(diǎn)SAVEPOINT delete1;# 回退到保留點(diǎn) ROLLBACK TO delete1;# 釋放保留點(diǎn) RELEASE SAVEPOINT delete1;?-- 更改默認的提交行為 SET autocommit = 0; # 設置autocommit為0（假）指示MySQL不自動(dòng)提交更改10.事務(wù)
SET AUTOCOMMIT=off ;禁用或啟用事務(wù)的自動(dòng)提交模式 off ONSET SESSION AUTOCOMMIT = OFF； 禁用或啟用事務(wù)的session自動(dòng)提交模式 off ONSHOW VARIABLES LIKE '%auto%'; -- 查看變量狀態(tài)?執行DML語(yǔ)句是其實(shí)就是開(kāi)啟一個(gè)事務(wù)只能回滾insert、delete和update語(yǔ)句對于create、drop、alter這些無(wú)法回滾事務(wù)只對DML有效果rollback，或者commit后事務(wù)就結束了自動(dòng)提交模式用于決定新事務(wù)如何及何時(shí)啟動(dòng)START TRANSACTION; 啟用自動(dòng)提交模式下顯式地啟動(dòng)事務(wù)COMMIT和ROLLBACK; 禁用自動(dòng)提交模式顯式地提交或回滾-- 事務(wù) transaction 指一組sql語(yǔ)句-- 回退 rollback 指撤銷(xiāo)指定sql語(yǔ)句的過(guò)程-- 提交 commit 指將未存儲的sql語(yǔ)句結果寫(xiě)入數據庫表-- 保留點(diǎn) savepoint 指事務(wù)處理中設置的臨時(shí)占位符，可以對它發(fā)布回退（與回退整個(gè)事務(wù)處理不同）?-- 控制事務(wù)處理# 開(kāi)始事務(wù)及回退 SELECT * FROM ordertotals; # 查看ordertotals表顯示不為空START TRANSACTION; # 開(kāi)始事務(wù)處理 DELETE FROM ordertotals; # 刪除ordertotals表中所有行SELECT * FROM ordertotals; # 查看ordertotals表顯示 為空ROLLBACK; # rollback語(yǔ)句回退 SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表顯示不為空?# commit 提交 START TRANSACTION;DELETE FROM orderitems WHERE order_num = 20010;DELETE FROM orders WHERE order_num = 20010;COMMIT; # 僅在上述兩條語(yǔ)句不出錯時(shí)寫(xiě)出更改 ?# savepoint 保留點(diǎn) # 創(chuàng )建保留點(diǎn)SAVEPOINT delete1;# 回退到保留點(diǎn) ROLLBACK TO delete1;# 釋放保留點(diǎn) RELEASE SAVEPOINT delete1;?-- 更改默認的提交行為 SET autocommit = 0; # 設置autocommit為0（假）指示MySQL不自動(dòng)提交更改11.備份與還原
備份一個(gè)數據庫:mysqldump -u root -p --opt people> df.txt #--opt優(yōu)化執行速度備份兩個(gè)數據庫:mysqldump -u root -p --opt --databases people hw > all.txt備份全部數據庫:mysqldump -u root -p --opt --all-DATABASES > all.txt恢復數據庫:mysqldump -u root -p --opt --databases people hw < all.txt1. 導出一張表mysqldump -u用戶(hù)名 -p密碼 庫名 表名 > 文件名(D:/a.sql)2. 導出多張表mysqldump -u用戶(hù)名 -p密碼 庫名 表1 表2 表3 > 文件名(D:/a.sql)3. 導出所有表mysqldump -u用戶(hù)名 -p密碼 庫名 > 文件名(D:/a.sql)4. 導出一個(gè)庫mysqldump -u用戶(hù)名 -p密碼 --lock-ALL-TABLES --database 庫名 > 文件名(D:/a.sql)可以-w攜帶WHERE條件12.用戶(hù)賬號
創(chuàng )建賬戶(hù):CREATE USER IF NOT EXISTS 'hw'@'localhost' IDENTIFIED BY '5201314'; #創(chuàng )建用戶(hù)hw,密碼5201314?給該用戶(hù)授予所有權限并可授權給其他用戶(hù):GRANT ALL PRIVILEGES ON people.df TO 'hw'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;?重命名用戶(hù)名: RENAME USER 'hw' TO 'gh'; 必須將localhost改為%如果希望該用戶(hù)能夠在任何機器上登陸mysql，則將localhost改為 '%' 授權給其他用戶(hù) WITH GRANT OPTION privileges包括： alter：修改數據庫的表 create：創(chuàng )建新的數據庫或表 delete：刪除表數據 drop：刪除數據庫/表 index：創(chuàng )建/刪除索引 insert：添加表數據 select：查詢(xún)表數據 update：更新表數據 all：允許任何操作 usage：只允許登錄?刷新權限,使新創(chuàng )建的用戶(hù)能夠使用: FLUSH PRIVILEGES; 收回用戶(hù)權限: REVOKE ALL PRIVILEGES ON people.df FROM 'root'@'localhost'; 刪除用戶(hù): DROP USER IF EXISTS 'hw'@'localhost';?設置指定用戶(hù)的密碼:SET PASSWORD FOR'hw'@'localhost' = PASSWORD('123321'); UPDATE USER SET PASSWORD = PASSWORD('123321') WHERE USER = 'hw';? 設置密碼: SET PASSWORD = PASSWORD('123321'); 13.其他
SHOW STATUS;顯示廣泛的服務(wù)器狀態(tài)信息SHOW PROCEDURE STATUS;SHOW GRANTS;顯示授予用戶(hù)的安全權限SHOW ERRORS;顯示服務(wù)器的錯誤信息SHOW WARNINGS;顯示服務(wù)器的警告信息SHOW PROCESSLIST;顯示哪些線(xiàn)程正在運行SHOW VARIABLES;顯示系統變量信息SELECT DATABASE(); 查看當前數據庫SELECT NOW(), USER(), VERSION():顯示當前時(shí)間、用戶(hù)名、數據庫版本SHOW ENGINES 引擎名 {LOGS|STATUS}:顯示存儲引擎的日志和狀態(tài)信息SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%'; 確定所用系統的字符集和校對SHOW VARIABLES LIKE 'character_set_client%'; 客戶(hù)端向服務(wù)器發(fā)送數據時(shí)使用的編碼SHOW VARIABLES LIKE 'character_set_results%'; 服務(wù)器端將結果返回給客戶(hù)端所使用的編碼SHOW VARIABLES LIKE 'character_set_connection%'; 連接層編碼SHOW CHARACTER SET;查看所支持的字符集完整列表SHOW COLLATION;查看所支持校對的完整列表,以及它們適用的字符集?SET character_set_client = gbk; SET character_set_results = gbk;SET character_set_connection = gbk;SET NAMES GBK; -- 相當于完成以上三個(gè)設置?創(chuàng )建window服務(wù):sc CREATE mysql binPath= mysqld_bin_path數據文件目錄:DATA DIRECTORY='目錄'索引文件目錄:INDEX DIRECTORY = '目錄'希望能幫到大家，謝謝?。?！
聯(lián)系客服
