一、了解MySQL基本概念
1、MySQL軟體：MySQL實際上就是一軟體，是一工具，是關系型資料庫管理系統軟體
2、MySQL資料庫：就是按照資料結構來組織、存儲和管理資料的倉庫
3、MySQL資料庫執行個體：
①MySQL是單程序多線程（而oracle是多程序），也就是說MySQL執行個體在系統上表現就是一個服務程序，即程序；
②MySQL執行個體是線程和記憶體組成，執行個體才是真正用于操作資料庫檔案的；
一般情況下一個執行個體操作一個或多個資料庫；叢集情況下多個執行個體操作一個或多個資料庫。
二、MySQL資料庫啟動以及啟動的判斷
1、啟動MySQL資料執行個體：
shell> service mysqld start #rpm包安裝的mysql
如果是源碼安裝的話，推薦使用mysqld_safe指令的安全啟動(可以看到啟動資訊)。
2、判斷MySQL資料庫是否啟動：
shell> netstat -tulnp|grep 3306 #如果可以過濾出來(有輸出)證明已啟動
shell> mysqladmin -uroot -p123 ping #出現mysqld is alive證明是活躍的
三、如何使用官方文檔和help
1、基本技能：DBA所有的操作必須來自于官方文檔
2、mysql> help contents; #尋求help幫助的入口
四、官方文檔概覽
1、Tutorial：将MySQL常用的一些操作使用一個場景串聯起來
隻是關注裡面的灰色部分就可以，按照裡面的灰色操作部分順一遍
2、server Administrator：MySQL管理需要的一些指令、工具、參數等
3、SQL Syntax
SQL文法，使用最多，特别是DDL語句一定要使用SQL文法進行參考
4、Server Option / Variable Reference：MySQL的參數和狀态值，使用較多
5、Functions and Operators
MySQL常用函數和操作符，使用較多
6、Views and Stored Programs
視圖、存儲過程、函數、觸發器、event文法參考
7、Optimization：優化
非常值得細緻的看一遍，此篇文檔不僅僅用來參考，更多的是用來學習優化知識，算是DBA進階寶典
8、Partitioning
如果是要進行表分區，此文檔是必須參考的資料，也是唯一參考的資料
9、Information Schema、Performance Schema
中級DBA常用的兩個參考資料
10、Spatial Extensions
地理位置資訊
11、Replication
MySQL使用複制功能，常用的參考資料
12、Semisynchronous Replication
半同步複制，個别場合會用到
五、如何使用官方文檔
1、參考官方文檔修改密碼強度(降低密碼強度)、修改密碼
①改密碼強度：
mysql> show variables like 'validate_password%';
mysql> SET GLOBAL validate_password_policy=0;
②修改密碼：set、alter
2、參考官方文檔查詢目前資料庫連接配接的數量(查詢狀态值Threads_connected)
mysql> show status like '%Threads_connected%';
注意：檢視狀态值是show status
檢視變量值是show variables
3、建立一個資料庫指定字元集
mysql> create database test_db character set utf8;
4、給一個表增加一個列，要求這個列的資料類型是字元串、非空（alter）
ALTER TABLE tbl_name ADD COLUMN col_name varchar(20) not null;
5、用函數将兩個字元串串聯起來(concat：合并多個字元串)
CONCAT()：returns NULL if any argument is NULL.
CONCAT_WS(separator,str1,str2,...)
6、mysqladmin的使用：類同于ping資料庫是否活躍、關閉資料庫
shell> mysqladmin -uroot -p123 ping
mysqld is alive
shell> mysqladmin -uroot -p123 shutdown
7、如何啟動資料庫：mysqld_safe指令(切記挂背景&，否則占領目前會話無法退出)
shell> mysqld_safe --defaults-file=/etc/my.cnf &
官方文檔對于具有一定基礎知識的人來說，是一個最合适的工具，可以使DBA的操作變得沒有障礙
六、登入MySQL檢視目前會話的狀态
mysql> status
七、描述MySQL在Linux平台下的大小寫、同時示範大小寫的差別
1、資料庫名、表名、表别名嚴格差別大小寫
2、列名、列别名忽略大小寫
3、變量名嚴格差別大小寫
4、MySQL在windows下各個對象都不差別大小寫
mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+ ①lower_case_file_system是對實際的檔案系統的反應，為隻讀變量，不能修改。Off表示MySQL所在的檔案系統大小寫敏感，也就是說進入MySQL所在的檔案系統檢視裡面的内容，發現有mysql檔案夾，此時建立一個名為MYSQL的檔案夾是可以的，說明大小寫敏感。
②lower_case_table_names表示表名或資料庫存儲是否差別大小寫，為隻讀變量，可以在配置檔案my.cnf裡面修改：
0表示區分大小寫，按照建立資料庫的大小寫形式存儲顯示；
1表示無論建立資料庫大小寫都以小寫的形式存儲顯示。
八、MySQL的幾種幫助
1、shell> mysql --help
2、mysql> help show
mysql> show create table tel_name
mysql> help set
九、MySQL的變量如何檢視，如何修改
1、檢視變量用select
局部變量select var_name;
使用者變量select @var_name;
全局變量select @@var_name;
2、修改變量用set
SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name = expr #變量名字=一個值
|[GLOBAL | SESSION] system_var_name = expr
|[@@global. | @@session. | @@]system_var_name = expr ①set global表示修改後對全部會話生效，為全局修改變量
②set session表示修改後對本次會話生效
③如果變量是隻讀變量可以通過修改MySQL的配置檔案my.cnf來修改變量，在[mysqld]下添加一行資料：user_var_name=expr，然後重新開機資料庫再登入即可。
十、MySQL的狀态參數如何檢視、如何參考閱讀其内容
在官方文檔的Server Option / Variable Reference部分，進行參考檢視MySQL的參數變量以及狀态值
1、cmd-line表示能否在mysql安全啟動(mysqld_safe)中進行參數設定 --var_name=……
2、option file表示能否在mysql的參數檔案中進行參數設定
3、system var表示是否是系統變量
4、status var表示是否是狀态變量
5、var scope表示變量的範圍：全局global、會話session
6、dynamic表示是否是動态參數，yes是動态，no是靜态
十一、如何檢視某個資料庫裡面有多少表、每一個表的列的資訊
1、show tables; desc tbl_name;
2、mysql> select * from information_schema.TABLES
-> where TABLE_NAME='tbl_name'\G;
①information_schema資料庫：也稱為資料字典，記錄了各資料庫的表、視圖、索引、存儲過程、函數等資訊……
②information_schema.TABLES：記錄了MySQL中每一個資料庫中表所在的資料庫、表的名字、表的行數等資訊。
十二、如何檢視一個表的建表語句、一個資料庫的建庫語句
1、show create table tbl_name;
2、show create database db_name;
十三、如何檢視MySQL支援的資料類型以及資料類型如何使用
mysql> help contents;
mysql> help data types;
mysql> help ……
十四、列舉show指令常用的文法
1、show status like …… 檢視狀态值
2、show variables like …… 檢視變量參數值
3、show create …… 檢視建表、庫……的語句資訊
4、show procedure status where db='db_name'\G; #檢視存儲過程資訊
5、show warnings\G; #檢視警告資訊
十五、help kill如何使用
mysql> help kill
KILL [CONNECTION | QUERY] processlist_id
注：Thread processlist identifiers can be determined from the ID column of the INFORMATION_SCHEMA.PROCESSLIST table。
mysql> select * from INFORMATION_SCHEMA.PROCESSLIST\G;
十六、描述MySQL使用者名組成以及特點
1、MySQL使用者身份識别認證：使用者名user、密碼password、登入mysqld主機host
shell> mysql -uroot -p123 -h172.16.11.99
-u：登入的使用者名
-p：登入使用者對應的密碼
-h：MySQL伺服器主機IP，預設是localhost的IP
2、MySQL的使用者管理子產品的特點：用戶端請求連接配接，提供host、username、password，使用者管理子產品進行驗證請求連接配接，通過mysql.user表進行校驗資訊。
十七、如何檢視MySQL有多少使用者以及對應的權限
1、mysql> select count(*) from mysql.user; #檢視MySQL有多少使用者
2、mysql> select * from mysql.user\G; #使用者資訊查詢（權限）
十八、建立一個使用者
1、本地登入
mysql> create user 'u1'@'localhost' identified by '123';
2、任意都可以登入
mysql> create user 'u2'@'%' identified by '123';
3、某一個網段可以登入
mysql> create user 'u3'@'172.16%' identified by '123';
4、具體主機可以登入
mysql> create user 'u4'@'172.16.12.24' identified by '123';
十九、使用help grant，給使用者賦權
二十、建立一個db1資料庫的隻讀使用者
建使用者然後授權
mysql> GRANT SELECT ON db1.* TO 'olr_user'@'%';
二十一、建立一個隻能進行系統狀态資訊查詢的管理使用者
mysql> grant select on information_schema.* to 'admin_user'@'%';
二十二、建立一個db1的生産使用者，隻能進行dml、select，不能進行ddl
mysql> grant select,insert,update,delete on *.* to 'pro_user'@'%';
二十三、建立一個可以進行DDL的管理使用者
mysql> grant create,drop,alter on *.* to 'admin_user'@'%';
二十四、建立一個工資表，隻有指定的使用者可以通路工資列，其他使用者都不能通路工資列
實作步驟：
先在mysql.user裡将所有使用者檢索出來，進行跑批處理(腳本或存儲過程)revoke對該表列的權限；
然後grant建立使用者，并對該表列賦通路權限。
二十五、查詢上述使用者以及所賦權限是否正确，同時進行驗證
mysql> select * from mysql.user\G; #檢視MySQL使用者資訊
進行使用者登入驗證
二十六、解釋with grant option，并且示範其功能
mysql> grant all on *.* to 'zhang'@'%' identified by '123' with grant option;
with grant option子句：通過在grant語句的最後使用該子句，就允許被授權的使用者把得到的權限繼續授給其他使用者。也就是說，用戶端用zhang使用者登入MySQL，可以将zhang使用者有的權限使用grant進行授權給其他使用者。
二十七、查詢某一個表上的權限、檢視某一個列上的權限、檢視某一個資料庫上面的權限
1、 查詢所有資料庫的權限
mysql> select * from mysql.user;
2、 查詢某個資料庫的權限
mysql> select * from mysql.db;
3、 查詢某個資料庫中某個表的權限
mysql> select * from mysql.tables_priv;
4、 查詢某個資料庫某個表中某個列的權限
mysql> select * from mysql.columns_priv;
二十八、修改參數運作使用grant建立使用者，修改參數禁止grant建立使用者
mysql> show variables like 'sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.37 sec) sql_mode參數中的NO_AUTO_CREATE_USER值：不自動建立使用者
mysql>set @@session.sql_mode=……; #設定sql_mode參數
二十九、修改mysql的使用者密碼，分别使用grant、alter、set修改
①mysql> grant all on *.* to '使用者名'@'登入主機' identified by '密碼';
②mysql> alter user '使用者名'@'登入主機' identified by '密碼(自定義)';
③mysql> SET PASSWORD FOR '使用者名'@'登入主機' = PASSWORD('密碼');
三十、破解密碼步驟：
①到/etc/my.cnf 裡将 validate_password=off 行注釋 //關閉密碼政策
②shell> mysqld_safe --skip-grant-tables & //重新開機資料庫
③shell> mysql -uroot //無密碼登入
④mysql> flush privileges; //重新整理權限使密碼生效
⑤修改密碼，退出，重新開機資料庫，進入
三十一、使用revoke進行權限的收回，将上面使用者的授權分别收回，同時檢視收回後的結果
①REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
②REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
三十二、select最簡單常用文法
1、全表查詢
select * from tbl_name;
2、某些行查詢
select * from tbl_name where ……;
3、某些列查詢
select clm_name from tbl_name;
4、某些行的某些列查詢
select clm_name from tbl_name where ……;
5、列别名
select clm_name as new_name from tbl_name;
6、列運算
select clm_name+123 from tbl_name;
三十三、concat函數的使用
1、concat函數：将多個字元串參數首尾相連後傳回
2、concat_ws函數：将多個字元串參數以給定的分隔符，首尾相連後傳回
3、group_concat：函數的值等于屬于一個組的指定列的所有值，以逗号隔開，并且以字元串表示
三十四、示範打開和關閉管道符号“|”的連接配接功能
PIPES_AS_CONCAT：将“||”視為字元串的連接配接操作符而非或運算符
|| 管道連接配接符：
mysql> select 列名1 || 列名2 || 列名3 from 表名;
在mysql中，進行上式連接配接查詢之後，會将查詢結果集在一列中顯示，列名是‘列名1 || 列名2 || 列名3’
mysql> select s_no || s_name || s_age
-> from student;
+-------------------------+
| s_no || s_name || s_age |
+-------------------------+
| 1001張三23 |
| 1002李四19 |
+-------------------------+ 如果不顯示結果，是因為sql_mode參數中沒有PIPES_AS_CONCAT，隻要給sql_mode參數加入PIPES_AS_CONCAT，就可以實作像CONCAT一樣的功能；
如果不給sql_mode參數加入PIPES_AS_CONCAT的話，|| 預設是or的意思，查詢結果是一列顯示是1。
三十五、使用mysql> help functions; 學習MySQL各類函數
三十六、常見功能函數
1、upper(……)、lower(……)大小寫變換
2、user()檢視登入使用者、current_user()檢視目前使用者
3、database()檢視使用的資料庫
三十七、使用help來學習下面的資料類型(建立對應類型的列、插入資料、顯示資料)
1、整數：int
2、非負數：unsigned無符号即非負數---e.g：int unsigned
3、小數：dec
4、浮點數以及科學計數法：float、double
如果FLOAT資料在插入的時候，要使用NeM(科學計數法)的方式插入時：
比如
5e2 就是5*10的2次方
5e-2就是5*10 的-2次方
4e-1+5.1e2 就是510.4
5、字元串：varchar
6、布爾：bool、boolean---synonyms(同義詞)：TINYINT(1)
7、位：bit
如何使用16進制常量：hex()
如何使用2進制常量：bin()
date類型以及STR_TO_DATE函數
time類型以及STR_TO_DATE函數
dateime資料類型以及标準寫法、STR_TO_DATE函數
date和time顯示方式以及date_format函數
三十八、時區
1、檢視作業系統時區、資料庫時區
檢視作業系統時區：
shell> cat /etc/sysconfig/clock
ZONE="Asia/Shanghai"
shell> ls /usr/share/zoneinfo
……
mysql> show variables like 'system_time%'; #檢視MySQL系統時區
mysql> show variables like 'time_zone%'; #檢視資料庫時區
2、修改資料庫時區為東八區，去掉資料庫時區對os時區的依賴(檢視官方文檔)
加載系統時區：将Linux時區導入到資料庫中
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p123 mysql
mysql> set @@global.time_zone='Asia/Shanghai';
修改資料庫時區為東八區，同時在參數檔案中進行修改，永久儲存
3、時區在什麼時候有用：
如果資料庫裡面沒有timestamp這個資料類型，那麼時區參數沒有意義！
你如何确認你的資料庫裡面是否有timestamp類型的列？
mysql> select table_name,column_name,data_type
-> from information_schema.columns
-> where data_type='timestamp'; ……
時區原理描述：insert過程和select過程的描述：相對應的0時區的轉換
4、時區的正确實踐(timestamp)
insert以前：你的values對應的時間到底是哪個時區，然後設定set @@session.time_zone為對應的時區
select擷取以前：你想得到什麼時區的時間，就設定set @@session.time_zone為對應的時區
三十九、字元集
1、檢視伺服器的字元集
mysql> show variables like 'character_set_server';
2、檢視資料庫字元集
mysql> show variables like 'character_set_database';
一般在資料庫實作字元集即可，表和列都預設采用資料庫的字元集
gbk
utf8
3、檢視表的字元集、檢視列的字元集
mysql> show create table tbl_name;
4、字元集原理描述、字元集正确實踐
對于insert過程描述、對于select過程描述
①對于insert來說，character_set_client、character_set_connection相同，而且正确反映用戶端使用的字元集
②對于select來說，character_set_results正确反映用戶端字元集
③資料庫字元集取決于我們要存儲的字元類型
④字元集轉換最多發生一次，這就要求character_set_client、character_set_connection相同
⑤所有的字元集轉換都發生在資料庫端
總述：
1)建立資料庫的時候注意字元集(gbk、utf8)
2)連接配接資料庫以後，無論是執行dml還是select，隻要涉及到varchar、char列，就需要設定正确的字元集參數:
character_set_client、character_set_connection、character_set_results
5、用戶端字元集如何來了解？
取決于用戶端工具
shell> mysql -uroot -p123456 -hserver_host -P3306
mysql工具本身沒有字元集，是以用戶端字元集取決于工具所在的os的字元集（windows：gbk、linux：utf8）
sqlyog工具本身帶字元集，此時用戶端os字元集就沒有意義
6、如何判斷字元集出現了問題？
所有設定都正确，但是查詢到的還是亂碼，這就是出現問題了
四十、如何識别變量參數、狀态參數status var
show variables……
show status……
識别判斷都是檢視官方文檔System Var、Status Var
四十一、如何識别動态參數、靜态參數
動态參數dynamic：Yes
靜态參數dynamic：No
四十二、對于動态參數如何設定，如何判斷動态參數是否可以在全局級别或者會話級别修改
1、set
2、修改參數檔案/etc/my.cnf：弊端是需要重新開機才能生效（很少用）
判斷：參考官方文檔Option/Variable Summary，通過Var scope來進行判斷動态參數的全局global、both
四十三、對于靜态參數如何修改
靜态參數，在整個執行個體聲明周期内都不得進行更改，就好似是隻讀的；
一般靜态參數都是在配置檔案中修改/etc/my.cnf，當然靜态參數能否寫入配置檔案還要看官方文檔對該參數的Option File的描述Yes與否。
四十四、掌握@@、@的差別
1、@@var_name表示的系統變量
根據系統變量的作用域可分：全局變量、會話變量
2、@var_name表示的使用者變量
①使用者變量和資料庫連接配接有關，連接配接後聲明變量，連接配接斷開後，自動消失；
②select一個沒有指派的使用者變量，傳回NULL，也就是沒有值；
Mysql的變量類似于動态語言，變量的值随所要賦的值的類型而改變。
四十五、set @@session.和set @@global.的生效時間
對于一個建立立的連接配接，隻有全局變量，會話變量還不存在，這個時候會從全局變量拷貝過來。
1、set @@session.：隻對目前連接配接起作用
2、set @@global.：對全局變量的修改會影響到整個伺服器
注意：set系統變量時，不帶作用域修飾，預設是指會話作用域；
（特别注意，有些系統變量不帶作用域修飾，無法設定，是以最好都帶上作用域設定系統變量）。
四十六、動态參數最佳實踐
1、盡量先進行會話級别的設定set @@session，确認生效而且效果不錯以後，再進行全局設定，如果需要馬上生效，殺掉所有的會話：
mysql> select concat('kill ',conn_id,';') from sys.session;
2、确認沒有問題以後，修改參數檔案，下次系統啟動一直生效。
四十七、select書寫技巧
1、确認需要通路資料來自于哪幾張表
from來自某張表或者某幾張表
join添加某張表
on表連接配接條件
記住一點：每關聯一個表就需要加上對應的on條件(on條件就是主外鍵條件)
2、通過where條件來過濾資料
3、确認需求裡面是否有分組聚合的含義
分組：group by
聚合：聚合函數
聚合條件過濾：having
4、是否需要排序
order by
四十八、MySQL内置函數(将列出的常見的一些函數熟悉過一遍)
1、内置函數的多少是一個資料庫是否成熟的标志
2、學會使用help Functions學習和使用函數(重點!!!!!!!!!!!)
3、常用函數要過一遍
①日期時間相關的函數
CURDATE、DATEDIFF、DATE_FORMAT、DAYOFWEEK、LAST_DAY、EXTRACT、STR_TO_DATE
②比較操作符要求都過一遍，help Comparison operators;
③流程控制行數help Control flow functions;
④加密函數help Encryption Functions;
隻需要看看decode、password兩個函數即可
⑤資訊擷取函數help Information Functions;
通過這些函數可以知道一些資訊，過一遍即可
⑥邏輯操作符help Logical operators;
！、and、or，這些常用的要過一遍
⑦雜項函數help Miscellaneous Functions;
簡單浏覽一下裡面的函數，對于名字有個印象即可
⑧數值函數help Numeric Functions;
使用資料庫來進行數學運算的情況不多，常用的加減乘除、TRUNCATE、ROUND
⑨字元串函數help String Functions;
CONCAT、CONCAT_WS、CAST、FORMAT、LIKE、REGEXP、STRCMP、TRIM、SUBSTRING、UPPER，其它函數名字過一遍
4、聚合分組函數的使用了解
①select後面得列或者出現在group by中，或者加上聚合函數
select c1,c2,sum(c3),count(c4)
from t1
group by c1,c2; ②help contents;
檢視聚合函數help Functions and Modifiers for Use with GROUP BY;
AVG、MAX、MIN、SUM、COUNT、COUNT DISTINCT、GROUP_CONCAT、BIT_AND、BIT_OR、BIT_XOR
四十九、隐式類型轉換，要避免隐式類型轉換
1、最常用的幾個資料類型：數字、字元串、日期時間
2、字元串裡面可以存放數字和日期，但是在設計表的時候，要注意不要将日期和數字列設計成字元串列
3、對于字元串列的比較，一定要加上引号：
mysql> select * from t where name_phone='1301110001';
五十、limit使用很頻繁，注意其使用方法
1、limit使用的場合
從結果集中選取最前面或最後面的幾行
2、limit配合order by使用
3、MySQL5.7 doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
五十一、in、not in、exists、not exists、left join、distinct join互相轉換
1、in和exists可以互相轉換
select * from players a where a.teamno in (select teamno from team where teamname='騎士隊');
select * from players a where exists (select 1 from team b where a.teamno=b.teamno and b.teamname='騎士隊'); 2、not in和not exists可以互相轉換
3、not in、not exists可以轉換成left join
select * from 學生資訊 a where a.stuno not in (select stuno from 選課資訊表);
select * from 學生資訊 a
left join 選課資訊 b
on a.stuno=b.stuno
where b.成績 is null; 4、in、exists可以轉換成distinct join
select * from 學生資訊 a where a.stuno in (select stuno from 選課資訊表 b);
select * from 學生資訊 a where exists (select 1 from 選課資訊 b where a.stuno=b.stuno);
select distinct a.*
from 學生資訊
join 選課資訊 b
on a.stuno=b.stuno; 五十二、連接配接的具體使用含義
1、了解為什麼會出現表連接配接：查詢的列來自于多個表
select 列
from ..
where 列
group by 列
having 列
order by 列
limit x 2、了解表連接配接的書寫方式
join一個表、on一個條件
3、了解表連接配接的注意條件
①兩個表要連接配接一定要存在主外鍵關系(有可能需要第三張表協助關聯)
實際上存在外鍵限制
存在外鍵列，但是沒有外鍵限制
②防止扇形陷阱(兩個表需要關聯，但是沒有直接主外鍵，借助第三個表進行關聯，但是存在扇形問題，此時不能借助第三個表進行關聯)
示例：學院表、專業表、學生表
學院實體和專業實體之間是一對多的聯系；
學院實體和學生實體之間也是一對多的聯系；
而學生和專業之間沒有聯系；
如果學生和專業通過學院表進行關聯，就會出現扇形問題。
4、外連接配接：左外連接配接、右外連接配接
外連接配接是為了防止出現某一個表的資料被遺漏
開發人員非常喜歡使用外連接配接.
五十三、子查詢
1、子查詢可能出現的位置
①select from之間可能會出現子查詢
②from後面
③join後面可能會出現子查詢
④where後面可能會出現子查詢
⑤having後面可能會出現子查詢
2、盡最大程度的不要使用子查詢
3、相關子查詢、無關子查詢
相關子查詢特别容易出現在select from之間、where後面
相關子查詢不能獨立執行，子查詢執行次數取決于父查詢傳回的行數
無關子查詢可以獨立執行，子查詢執行一次
五十四、子查詢出現的場合
1、where中出現的子查詢，一般可使用表連接配接進行改寫
①select 列(涉及到A表，沒有涉及到B表)
②where 條件(涉及到B表)
2、from後面的子查詢
①對于取出來的資料再次進行複雜的處理
例如分組聚合、having條件、where條件等
②對一個結果集再次進行複雜的查詢
意味着我們取資料的這個過程中，對資料進行處理的力度很複雜
3、select from之間的子查詢
對于傳回的每一行資料，select和from之間的子查詢都要執行一次
select後面的列要進行複雜的處理，如果這個處理涉及到另外一個表，若這個表很可能沒有出現在from和join裡面，則進行子查詢：
示例：将每一個同學的成績列出來，同時計算他的成績和本組平均成績的差距
select 學生成績,
學生成績-(select avg(成績) from 選課表 a where a.組ID=b.組ID)
from 選課表 b; 五十五、select執行的順序
select ...
from ...
join ...
on ...
where ...
group by ..
having ...
order by ... 1、先從表中取資料，通路innodb buffer pool
from ...
join ...
on ...
where
2、分組、聚合，資料已經進入使用者工作空間
group by ...
having ...
3、select ....：取列資料
4、order by：排序輸出
五十六、集合操作
union：結果集去重
union all：結果集不去重
五十七、insert增
1、insert values一條資料
表的名字後面最好加上列的名字
2、insert values多條資料
3、insert into select
select可以非常複雜，文法完全就是select
五十八、update改
基本格式：update 一個表 set 列 where 列條件;
1、一定要帶上where條件
2、update分為下面的幾個步驟操作
①找到需要update的資料，此操作取決于where條件
where條件可以是一個複雜的where條件，比如是一個子查詢
示例：将平均成績75分以上的學生的級别設定為優等生
update 學生資訊表 a
set grade=‘優等生’
where a.stuno in (select b.stuno from 成績表 b group by b.stuno having avg(成績)>=75); ②set後面的列，也可以很複雜，比如是一個相對子查詢
UPDATE players_data pd
SET number_mat = (
SELECT count(*)
FROM matches m
WHERE m.playerno = pd.playerno),
sum_penalties = (
SELECT sum(amount)
FROM penalties pen
WHERE pen.playerno = pd.playerno); 3、update可以改寫成一個select語句
把1和2改寫成一個select語句，不要對一個update在生産裡面直接進行優化
4、update可以使用order by，資料按照順序進行更新
5、update可以使用limit，限制每次更新的行數
五十九、replace替代已有的行
使用場合insert+update，兩個表資料合并到一起
六十、delete删
1、絕大多數情況下需要加上where條件
2、where條件可以很複雜，例如是一個子查詢
3、了解delete和truncate的差別
truncate：清空全部資料、速度快、釋放空間(不删表)
delete：全部或者部分删除資料、速度慢、不釋放空間
六十一、臨時表
1、隻是針對目前會話有效，臨時表和資料都存儲在使用者工作空間
2、臨時表的使用很消耗資源
①create、insert、drop，是以在非常頻繁的查詢環境下，不宜使用臨時表；
②臨時表需要使用使用者工作空間，臨時表中存在的資料不易過多，否則容易出現磁盤臨時表；
3、臨時表的使用場合
需要暫存結果集資料，後面的操作需要通路這些暫存結果集，主要是為了可讀性。
4、有一種誤區一定要注意，一定不要将普通表作為臨時表來使用
原因：普通表當做臨時表來使用，下面的操作需要手工去做
①create、insert、truncate或者drop
②對于普通表的所有操作都會産生redo(事務)，非常消耗資源
六十二、關于限制
1、非空
2、default限制
3、主鍵限制
4、外鍵限制
5、SET、ENUM限制
限制注意點：
①盡量選擇列都為非空
②對于bool、時間列經常會出現default限制
③每一個表盡最大程度要有主鍵
④唯一鍵可以有多個，唯一鍵可以有空值
⑤外鍵列一般會有，但是外鍵限制不建議使用，在應用層面保證主表和外表的一緻性
⑥合理使用set和enum限制，提升資料的品質
⑦外鍵限制中on delete、update，盡量不要設定級聯删除操作(很危險！！！)
六十三、表的DDL
1、極其嚴肅的一個動作
2、使用help書寫DDL語句
3、ddl動作的後遺症和危險性
①影響I、D、U、S
②長時間鎖表、産生海量IO
4、測試DDL的影響範圍---優化對象
①鎖表時間
②IO情況
③具體測試要求
示例：産生一個500萬行的表(寫一個存儲過程實作)，對表進行增加列、删除列、修改列的名字、将列的長度變長、将列的長度變短
mysql> delimiter $$
mysql> create procedure do_big(x int)
-> begin
-> declare v int;
-> set v=x;
-> create table test(test_num int auto_increment not null primary key);
-> while v>0 do
-> insert into test values(null);
-> set v=v-1;
-> end while;
-> end $$
mysql> delimiter ;
mysql> call do_big(5000000);
……
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 5000000 |
+----------+ 看一下上面的這些操作，哪些操作時間長、哪些操作時間短，并對其進行初步的原理分析
mysql> insert into test values(123456789);
mysql> delete from test where test_num=123;
mysql> alter table test CHANGE COLUMN
-> test_num
-> test_id int(10) not null auto_increment;
mysql> alter table test modify test_id int(100);
mysql> alter table test modify test_id int(20); 總結：對于一個大表而言，将列的長度變長時間是最長的，其他的操作處理時間都還挺短。
六十四、視圖的最佳實踐
1、視圖就是select的一個名字
2、不建議使用複雜視圖
select語句裡面不要帶有distinct、group by、聚合函數、union等操作
3、不建議在視圖中嵌套視圖
4、視圖的主要使用場合
統一通路接口(select)---主要的好處
規範通路
隐藏底層表結構、ddl不影響應用通路
5、視圖在安全方面的意義
六十五、存儲過程(腳本)
1、存儲過程使用的場合
①重複性很高的複合操作(dml)
②統一通路接口(dml、事務)
③批量業務(跑批)
2、存儲過程結構分析
①存儲過程中嵌入了dml、select
②存儲過程有參數，參數的不同會産生不同的事務
in、out、inout
③存儲過程裡面有結構化語句，即流程控制語句：
循環
條件判斷
使得在執行dml、select的時候，變得友善
④存儲過程可以定義變量
select取出來的結果可以存儲到變量中
dml需要的輸入值可以通過變量來實作
⑤存儲過程裡面可以有遊标，遊标的核心就是可以對一個結果集進行處理
1)定義遊标(遊标和一個select關聯)
2)打開遊标(将select的結果賦給遊标，可以是N行列)
3)周遊遊标(一行行資料擷取，每一行資料賦給N個變量)
4)關閉遊标
⑥存儲過程有異常處理部分
1)異常處理是一個存儲過程是否可以産品化、商業化很重要的一個标志
2)異常處理隻關心SQL語句的異常
每一個存儲過程都要對着三類SQLWARNING、NOT FOUND、SQLEXCEPTION進行處理；
存儲過程異常處理通常隻是進行錯誤的記錄，或者空處理。
⑦存儲過程書寫過程
1)定義一個結構
存儲過程基本結構
參數
異常處理
2)書寫涉及到SQL語句
3)考慮使用變量、遊标、條件判斷、循環将SQL語句組合起來
4)經常使用begin end來将一組SQL語句或者語句組合起來，作為一個語句來出現
3、存儲過程安全方面的意義：防止對底層表直接進行dml
六十六、自定義函數
1、自定義函數和存儲過程的差別
①有一個傳回值
CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
…… ②調用的時候必須放在=的右邊
set @ax = SimpleCompare(1,2);
2、整理筆記，将函數定義和函數調用整理一個例子出來
六十七、觸發器
1、盡量少使用觸發器，不建議使用
2、觸發器是一個begin end結構體
3、觸發器和存儲過程的唯一差別就是在于被執行方式上的差別
存儲過程需要手工去執行
觸發器被DML自動觸發
4、觸發器被觸發的條件
①for each row(每一行都被觸發一次，這就決定了頻繁dml的表上面不要有觸發器)
②增删改都可以定義觸發器
③before、after可以定義觸發的時機
5、觸發器中經常使用new、old
insert裡面可以有new
delete裡面可以有old
update裡面可以有new、old
6、使用觸發器的場合
一般用來進行審計使用：産品價格表裡面的價格這個列，隻要是有人對這個表的這個列進行更新，就要儲存修改前和修改後的值，将這個資訊記錄到一個單獨的表中(審計表)
7、要求你将觸發器的例子儲存到筆記中
①insert觸發器（new）
②delete觸發器（old）
③update觸發器（new、old）
④before、after
六十八、event
1、周期性執行
①linux裡面的at、crontab
②MySQL裡面的event
2、event的核心知識點
①執行一次
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
begin
UPDATE t1 SET mycol = mycol + 1;
end ②周期性執行
CREATE EVENT myevent
ON SCHEDULE EVERY 1 DAY STARTS STR_TO_DATE(‘2017-05-01 20:00:00’,'yyyy-mm-dd hh24:mi:ss')
DO
begin
UPDATE t1 SET mycol = mycol + 1;
end
@author：http://www.cnblogs.com/geaozhang/