MySQL開發(fā)心得筆記超詳細(xì)

一彼妻、理解MySQL基本概念

    1肉津、MySQL軟件：MySQL實(shí)際上就是一軟件，是一工具洛二，是關(guān)系型數(shù)據(jù)庫管理系統(tǒng)軟件

    2馋劈、MySQL數(shù)據(jù)庫：就是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫

    3晾嘶、MySQL數(shù)據(jù)庫實(shí)例：

            ①M(fèi)ySQL是單進(jìn)程多線程（而Oracle是多進(jìn)程）侣滩，也就是說MySQL實(shí)例在系統(tǒng)上表現(xiàn)就是一個(gè)服務(wù)進(jìn)程，即進(jìn)程变擒；

            ②MySQL實(shí)例是線程和內(nèi)存組成君珠，實(shí)例才是真正用于操作數(shù)據(jù)庫文件的；

    一般情況下一個(gè)實(shí)例操作一個(gè)或多個(gè)數(shù)據(jù)庫娇斑；集群情況下多個(gè)實(shí)例操作一個(gè)或多個(gè)數(shù)據(jù)庫策添。

二、MySQL數(shù)據(jù)庫啟動以及啟動的判斷

    1毫缆、啟動MySQL數(shù)據(jù)實(shí)例：

            shell> service mysqld start        #rpm包安裝的mysql

    如果是源碼安裝的話唯竹，推薦使用mysqld_safe命令的安全啟動(可以看到啟動信息)。

    2苦丁、判斷MySQL數(shù)據(jù)庫是否啟動：

            shell> netstat -tulnp|grep 3306        #如果可以過濾出來(有輸出)證明已啟動

            shell> mysqladmin -uroot -p123 ping        #出現(xiàn)mysqld is alive證明是活躍的

三浸颓、如何使用官方文檔和help

    1、基本技能：DBA所有的操作必須來自于官方文檔

    2旺拉、mysql> help contents;        #尋求help幫助的入口

四产上、官方文檔概覽

    1、Tutorial：將MySQL常用的一些操作使用一個(gè)場景串聯(lián)起來

            只是關(guān)注里面的灰色部分就可以蛾狗，按照里面的灰色操作部分順一遍

    2晋涣、server Administrator：MySQL管理需要的一些命令、工具沉桌、參數(shù)等

    3谢鹊、SQL Syntax

            SQL語法算吩，使用最多，特別是DDL語句一定要使用SQL語法進(jìn)行參考

    4佃扼、Server Option / Variable Reference：MySQL的參數(shù)和狀態(tài)值偎巢，使用較多

    5、Functions and Operators

            MySQL常用函數(shù)和操作符兼耀，使用較多

    6艘狭、Views and Stored Programs

            視圖、存儲過程翠订、函數(shù)巢音、觸發(fā)器、event語法參考

    7尽超、Optimization：優(yōu)化

            非常值得細(xì)致的看一遍官撼，此篇文檔不僅僅用來參考，更多的是用來學(xué)習(xí)優(yōu)化知識似谁，算是DBA進(jìn)階寶典

    8傲绣、Partitioning

            如果是要進(jìn)行表分區(qū)，此文檔是必須參考的資料巩踏，也是唯一參考的資料

    9秃诵、Information Schema、Performance Schema

            中級DBA常用的兩個(gè)參考資料

    10塞琼、Spatial Extensions

            地理位置信息

    11菠净、Replication

            MySQL使用復(fù)制功能，常用的參考資料

    12彪杉、Semisynchronous Replication

            半同步復(fù)制毅往，個(gè)別場合會用到

五、如何使用官方文檔

    1派近、參考官方文檔修改密碼強(qiáng)度(降低密碼強(qiáng)度)攀唯、修改密碼

    ①改密碼強(qiáng)度：

            mysql> show variables like 'validate_password%';

            mysql> SET GLOBAL validate_password_policy=0;

    ②修改密碼：set、alter

    2渴丸、參考官方文檔查詢當(dāng)前數(shù)據(jù)庫連接的數(shù)量(查詢狀態(tài)值Threads_connected)

            mysql> show status like '%Threads_connected%';

            注意：查看狀態(tài)值是show status

                        查看變量值是show variables

    3侯嘀、建立一個(gè)數(shù)據(jù)庫指定字符集

            mysql> create database test_db character set utf8;

    4、給一個(gè)表增加一個(gè)列谱轨，要求這個(gè)列的數(shù)據(jù)類型是字符串戒幔、非空（alter）

            ALTER TABLE tbl_name ADD COLUMN col_name varchar(20) not null;

    5、用函數(shù)將兩個(gè)字符串串聯(lián)起來(concat：合并多個(gè)字符串)

            CONCAT()：returns NULL if any argument is NULL.

            CONCAT_WS(separator,str1,str2,...)

    6碟嘴、mysqladmin的使用：類同于ping數(shù)據(jù)庫是否活躍溪食、關(guān)閉數(shù)據(jù)庫

            shell> mysqladmin -uroot -p123 ping

            mysqld is alive

            shell> mysqladmin -uroot -p123 shutdown

    7、如何啟動數(shù)據(jù)庫：mysqld_safe命令(切記掛后臺&娜扇，否則占領(lǐng)當(dāng)前會話無法退出)

            shell> mysqld_safe --defaults-file=/etc/my.cnf &

    官方文檔對于具有一定基礎(chǔ)知識的人來說错沃，是一個(gè)最合適的工具，可以使DBA的操作變得沒有障礙     

六雀瓢、登錄MySQL查看當(dāng)前會話的狀態(tài)

    mysql> status

七枢析、描述MySQL在Linux平臺下的大小寫、同時(shí)演示大小寫的區(qū)別

    1刃麸、數(shù)據(jù)庫名醒叁、表名、表別名嚴(yán)格區(qū)別大小寫

    2泊业、列名把沼、列別名忽略大小寫

    3、變量名嚴(yán)格區(qū)別大小寫

    4吁伺、MySQL在windows下各個(gè)對象都不區(qū)別大小寫

    mysql> show variables like 'lower%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_file_system | OFF  |
    | lower_case_table_names | 0    |
    +------------------------+-------+
    ①lower_case_file_system是對實(shí)際的文件系統(tǒng)的反應(yīng)饮睬，為只讀變量，不能修改篮奄。Off表示MySQL所在的文件系統(tǒng)大小寫敏感捆愁，也就是說進(jìn)入MySQL所在的文件系統(tǒng)查看里面的內(nèi)容，發(fā)現(xiàn)有mysql文件夾窟却，此時(shí)新建一個(gè)名為MYSQL的文件夾是可以的昼丑，說明大小寫敏感。
    ②lower_case_table_names表示表名或數(shù)據(jù)庫存儲是否區(qū)別大小寫夸赫，為只讀變量菩帝，可以在配置文件my.cnf里面修改：

            0表示區(qū)分大小寫，按照新建數(shù)據(jù)庫的大小寫形式存儲顯示茬腿；

            1表示無論新建數(shù)據(jù)庫大小寫都以小寫的形式存儲顯示胁附。

八、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        #變量名字=一個(gè)值
            |[GLOBAL | SESSION] system_var_name = expr
            |[@@global. | @@session. | @@]system_var_name = expr

    ①set global表示修改后對全部會話生效他匪，為全局修改變量
    ②set session表示修改后對本次會話生效

    ③如果變量是只讀變量可以通過修改MySQL的配置文件my.cnf來修改變量菇存，在[mysqld]下添加一行數(shù)據(jù)：user_var_name=expr，然后重啟數(shù)據(jù)庫再登錄即可邦蜜。

十依鸥、MySQL的狀態(tài)參數(shù)如何查看、如何參考閱讀其內(nèi)容

    在官方文檔的Server Option / Variable Reference部分悼沈，進(jìn)行參考查看MySQL的參數(shù)變量以及狀態(tài)值



    1贱迟、cmd-line表示能否在mysql安全啟動(mysqld_safe)中進(jìn)行參數(shù)設(shè)置 --var_name=……

    2姐扮、option file表示能否在mysql的參數(shù)文件中進(jìn)行參數(shù)設(shè)置

    3、system var表示是否是系統(tǒng)變量

    4衣吠、status var表示是否是狀態(tài)變量

    5茶敏、var scope表示變量的范圍：全局global、會話session

    6缚俏、dynamic表示是否是動態(tài)參數(shù)惊搏，yes是動態(tài)，no是靜態(tài)

十一忧换、如何查看某個(gè)數(shù)據(jù)庫里面有多少表恬惯、每一個(gè)表的列的信息

    1、show tables;        desc tbl_name;

    2亚茬、mysql> select * from information_schema.TABLES

                    -> where TABLE_NAME='tbl_name'\G;

    ①information_schema數(shù)據(jù)庫：也稱為數(shù)據(jù)字典酪耳，記錄了各數(shù)據(jù)庫的表、視圖才写、索引葡兑、存儲過程、函數(shù)等信息……

    ②information_schema.TABLES：記錄了MySQL中每一個(gè)數(shù)據(jù)庫中表所在的數(shù)據(jù)庫赞草、表的名字讹堤、表的行數(shù)等信息。

十二厨疙、如何查看一個(gè)表的建表語句洲守、一個(gè)數(shù)據(jù)庫的建庫語句

    1、show create table tbl_name;

    2沾凄、show create database db_name;

十三梗醇、如何查看MySQL支持的數(shù)據(jù)類型以及數(shù)據(jù)類型如何使用

    mysql> help contents;

    mysql> help data types;

    mysql> help ……

十四、列舉show命令常用的語法

    1撒蟀、show status like …… 查看狀態(tài)值

    2叙谨、show variables like …… 查看變量參數(shù)值

    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用戶名組成以及特點(diǎn)

    1切蟋、MySQL用戶身份識別認(rèn)證：用戶名user统捶、密碼password、登錄mysqld主機(jī)host

    shell> mysql -uroot -p123 -h172.16.11.99

            -u：登錄的用戶名

            -p：登錄用戶對應(yīng)的密碼

            -h：MySQL服務(wù)器主機(jī)IP，默認(rèn)是localhost的IP

    2喘鸟、MySQL的用戶管理模塊的特點(diǎn)：客戶端請求連接匆绣，提供host、username迷守、password犬绒，用戶管理模塊進(jìn)行驗(yàn)證請求連接旺入，通過mysql.user表進(jìn)行校驗(yàn)信息兑凿。

十七、如何查看MySQL有多少用戶以及對應(yīng)的權(quán)限

    1茵瘾、mysql> select count(*) from mysql.user;        #查看MySQL有多少用戶

    2礼华、mysql> select * from mysql.user\G;        #用戶信息查詢（權(quán)限）

十八、建立一個(gè)用戶

    1拗秘、本地登錄

            mysql> create user 'u1'@'localhost' identified by '123';

    2圣絮、任意都可以登錄

            mysql> create user 'u2'@'%' identified by '123';

    3、某一個(gè)網(wǎng)段可以登錄

            mysql> create user 'u3'@'172.16%' identified by '123';

    4雕旨、具體主機(jī)可以登錄

            mysql> create user 'u4'@'172.16.12.24' identified by '123';

十九扮匠、使用help grant，給用戶賦權(quán)

二十凡涩、建立一個(gè)db1數(shù)據(jù)庫的只讀用戶

    建用戶然后授權(quán)

    mysql> GRANT SELECT ON db1.* TO 'olr_user'@'%';

二十一棒搜、建立一個(gè)只能進(jìn)行系統(tǒng)狀態(tài)信息查詢的管理用戶

    mysql> grant select on information_schema.* to 'admin_user'@'%';

二十二、建立一個(gè)db1的生產(chǎn)用戶活箕，只能進(jìn)行dml力麸、select，不能進(jìn)行ddl

    mysql> grant select,insert,update,delete on *.* to 'pro_user'@'%';

二十三育韩、建立一個(gè)可以進(jìn)行DDL的管理用戶

    mysql> grant create,drop,alter on *.* to 'admin_user'@'%';

二十四克蚂、建立一個(gè)工資表，只有指定的用戶可以訪問工資列筋讨，其他用戶都不能訪問工資列

    實(shí)現(xiàn)步驟：

            先在mysql.user里將所有用戶檢索出來埃叭，進(jìn)行跑批處理(腳本或存儲過程)revoke對該表列的權(quán)限；

            然后grant創(chuàng)建用戶悉罕，并對該表列賦訪問權(quán)限赤屋。 

二十五、查詢上述用戶以及所賦權(quán)限是否正確蛮粮，同時(shí)進(jìn)行驗(yàn)證

    mysql> select * from mysql.user\G;        #查看MySQL用戶信息

    進(jìn)行用戶登錄驗(yàn)證

二十六益缎、解釋with grant option，并且演示其功能

    mysql> grant all on *.* to 'zhang'@'%' identified by '123' with grant option;

    with grant option子句：通過在grant語句的最后使用該子句然想，就允許被授權(quán)的用戶把得到的權(quán)限繼續(xù)授給其他用戶莺奔。也就是說，客戶端用zhang用戶登錄MySQL，可以將zhang用戶有的權(quán)限使用grant進(jìn)行授權(quán)給其他用戶令哟。

二十七恼琼、查詢某一個(gè)表上的權(quán)限、查看某一個(gè)列上的權(quán)限屏富、查看某一個(gè)數(shù)據(jù)庫上面的權(quán)限

    1晴竞、 查詢所有數(shù)據(jù)庫的權(quán)限

            mysql> select * from mysql.user;

    2、 查詢某個(gè)數(shù)據(jù)庫的權(quán)限

            mysql> select * from mysql.db;

    3狠半、 查詢某個(gè)數(shù)據(jù)庫中某個(gè)表的權(quán)限

            mysql> select * from mysql.tables_priv;

    4噩死、 查詢某個(gè)數(shù)據(jù)庫某個(gè)表中某個(gè)列的權(quán)限

            mysql> select * from mysql.columns_priv;

二十八、修改參數(shù)運(yùn)行使用grant建立用戶神年，修改參數(shù)禁止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參數(shù)中的NO_AUTO_CREATE_USER值：不自動創(chuàng)建用戶

    mysql>set @@session.sql_mode=……;         #設(shè)置sql_mode參數(shù)

二十九已维、修改mysql的用戶密碼，分別使用grant已日、alter垛耳、set修改

    ①mysql> grant all on *.* to '用戶名'@'登錄主機(jī)' identified by '密碼';

    ②mysql> alter user '用戶名'@'登錄主機(jī)' identified by '密碼(自定義)';

    ③mysql> SET PASSWORD FOR '用戶名'@'登錄主機(jī)' = PASSWORD('密碼');

三十、破解密碼步驟：

    ①到/etc/my.cnf 里將 validate_password=off 行注釋        //關(guān)閉密碼策略

    ②shell> mysqld_safe --skip-grant-tables &        //重啟數(shù)據(jù)庫

    ③shell> mysql -uroot        //無密碼登錄

    ④mysql> flush privileges;        //刷新權(quán)限使密碼生效

    ⑤修改密碼飘千，退出堂鲜，重啟數(shù)據(jù)庫，進(jìn)入

三十一护奈、使用revoke進(jìn)行權(quán)限的收回缔莲，將上面用戶的授權(quán)分別收回，同時(shí)查看收回后的結(jié)果

    ①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简僧、列運(yùn)算

            select clm_name+123 from tbl_name;

三十三建椰、concat函數(shù)的使用

    1、concat函數(shù)：將多個(gè)字符串參數(shù)首尾相連后返回

    2岛马、concat_ws函數(shù)：將多個(gè)字符串參數(shù)以給定的分隔符棉姐，首尾相連后返回

    3、group_concat：函數(shù)的值等于屬于一個(gè)組的指定列的所有值啦逆，以逗號隔開伞矩，并且以字符串表示

三十四、演示打開和關(guān)閉管道符號“|”的連接功能

    PIPES_AS_CONCAT：將“||”視為字符串的連接操作符而非或運(yùn)算符

    || 管道連接符：

            mysql> select  列名1 || 列名2 || 列名3  from  表名;

    在mysql中夏志，進(jìn)行上式連接查詢之后乃坤，會將查詢結(jié)果集在一列中顯示，列名是‘列名1 || 列名2 || 列名3’

    mysql> select s_no || s_name || s_age
        -> from student;
    +-------------------------+
    | s_no || s_name || s_age |
    +-------------------------+
    | 1001張三23              |
    | 1002李四19              |
    +-------------------------+
    如果不顯示結(jié)果，是因?yàn)閟ql_mode參數(shù)中沒有PIPES_AS_CONCAT湿诊，只要給sql_mode參數(shù)加入PIPES_AS_CONCAT狱杰，就可以實(shí)現(xiàn)像CONCAT一樣的功能；
    如果不給sql_mode參數(shù)加入PIPES_AS_CONCAT的話厅须，|| 默認(rèn)是or的意思仿畸，查詢結(jié)果是一列顯示是1。

三十五朗和、使用mysql> help functions; 學(xué)習(xí)MySQL各類函數(shù)

三十六错沽、常見功能函數(shù)

    1、upper(……)例隆、lower(……)大小寫變換

    2甥捺、user()查看登錄用戶抢蚀、current_user()查看當(dāng)前用戶

    3镀层、database()查看使用的數(shù)據(jù)庫MySQL開發(fā)心得筆記超詳細(xì)及SQL語法考核

三十八、時(shí)區(qū)

    1皿曲、查看操作系統(tǒng)時(shí)區(qū)唱逢、數(shù)據(jù)庫時(shí)區(qū)

    查看操作系統(tǒng)時(shí)區(qū)：

            shell> cat /etc/sysconfig/clock

            ZONE="Asia/Shanghai"

            shell> ls /usr/share/zoneinfo

            ……

            mysql> show variables like 'system_time%';        #查看MySQL系統(tǒng)時(shí)區(qū)

            mysql> show variables like 'time_zone%';        #查看數(shù)據(jù)庫時(shí)區(qū)

    2、修改數(shù)據(jù)庫時(shí)區(qū)為東八區(qū)屋休，去掉數(shù)據(jù)庫時(shí)區(qū)對os時(shí)區(qū)的依賴(查看官方文檔)

    加載系統(tǒng)時(shí)區(qū)：將Linux時(shí)區(qū)導(dǎo)入到數(shù)據(jù)庫中

            shell> mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p123 mysql

            mysql> set @@global.time_zone='Asia/Shanghai'; 

    修改數(shù)據(jù)庫時(shí)區(qū)為東八區(qū)坞古，同時(shí)在參數(shù)文件中進(jìn)行修改，永久保存

    3劫樟、時(shí)區(qū)在什么時(shí)候有用：

    如果數(shù)據(jù)庫里面沒有timestamp這個(gè)數(shù)據(jù)類型痪枫，那么時(shí)區(qū)參數(shù)沒有意義！

    你如何確認(rèn)你的數(shù)據(jù)庫里面是否有timestamp類型的列叠艳？

    mysql> select table_name,column_name,data_type
        -> from information_schema.columns
        -> where data_type='timestamp';
    ……

    時(shí)區(qū)原理描述：insert過程和select過程的描述：相對應(yīng)的0時(shí)區(qū)的轉(zhuǎn)換

    4奶陈、時(shí)區(qū)的正確實(shí)踐(timestamp)

            insert以前：你的values對應(yīng)的時(shí)間到底是哪個(gè)時(shí)區(qū)，然后設(shè)置set @@session.time_zone為對應(yīng)的時(shí)區(qū)

            select獲取以前：你想得到什么時(shí)區(qū)的時(shí)間附较，就設(shè)置set @@session.time_zone為對應(yīng)的時(shí)區(qū)

三十九吃粒、字符集

    1、查看服務(wù)器的字符集

            mysql> show variables like 'character_set_server';

    2拒课、查看數(shù)據(jù)庫字符集

            mysql> show variables like 'character_set_database';

    一般在數(shù)據(jù)庫實(shí)現(xiàn)字符集即可徐勃，表和列都默認(rèn)采用數(shù)據(jù)庫的字符集

    gbk

    utf8

    3、查看表的字符集早像、查看列的字符集

            mysql> show create table tbl_name;

    4僻肖、字符集原理描述、字符集正確實(shí)踐

    對于insert過程描述卢鹦、對于select過程描述

            ①對于insert來說臀脏，character_set_client、character_set_connection相同，而且正確反映客戶端使用的字符集

            ②對于select來說谁榜，character_set_results正確反映客戶端字符集

            ③數(shù)據(jù)庫字符集取決于我們要存儲的字符類型

            ④字符集轉(zhuǎn)換最多發(fā)生一次幅聘，這就要求character_set_client、character_set_connection相同

            ⑤所有的字符集轉(zhuǎn)換都發(fā)生在數(shù)據(jù)庫端 

    總述：

    1)建立數(shù)據(jù)庫的時(shí)候注意字符集(gbk窃植、utf8)

    2)連接數(shù)據(jù)庫以后帝蒿，無論是執(zhí)行dml還是select，只要涉及到varchar巷怜、char列葛超，就需要設(shè)置正確的字符集參數(shù):

            character_set_client、character_set_connection延塑、character_set_results

    5绣张、客戶端字符集如何來理解？

    取決于客戶端工具

            shell> mysql -uroot -p123456 -hserver_host -P3306

    mysql工具本身沒有字符集关带，因此客戶端字符集取決于工具所在的os的字符集（windows：gbk侥涵、linux：utf8）

    sqlyog工具本身帶字符集，此時(shí)客戶端os字符集就沒有意義

    6宋雏、如何判斷字符集出現(xiàn)了問題芜飘？

    所有設(shè)置都正確，但是查詢到的還是亂碼磨总，這就是出現(xiàn)問題了

四十嗦明、如何識別變量參數(shù)、狀態(tài)參數(shù)status var

    show variables……

    show status……

    識別判斷都是查看官方文檔System Var蚪燕、Status Var

四十一娶牌、如何識別動態(tài)參數(shù)、靜態(tài)參數(shù)

    動態(tài)參數(shù)dynamic：Yes

    靜態(tài)參數(shù)dynamic：No

四十二馆纳、對于動態(tài)參數(shù)如何設(shè)置诗良，如何判斷動態(tài)參數(shù)是否可以在全局級別或者會話級別修改

    1、set

    2厕诡、修改參數(shù)文件/etc/my.cnf：弊端是需要重啟才能生效（很少用）

    判斷：參考官方文檔Option/Variable Summary累榜，通過Var scope來進(jìn)行判斷動態(tài)參數(shù)的全局global、both

四十三灵嫌、對于靜態(tài)參數(shù)如何修改

    靜態(tài)參數(shù)壹罚，在整個(gè)實(shí)例聲明周期內(nèi)都不得進(jìn)行更改，就好似是只讀的寿羞；

    一般靜態(tài)參數(shù)都是在配置文件中修改/etc/my.cnf猖凛，當(dāng)然靜態(tài)參數(shù)能否寫入配置文件還要看官方文檔對該參數(shù)的Option File的描述Yes與否。

四十四绪穆、掌握@@辨泳、@的區(qū)別

    1虱岂、@@var_name表示的系統(tǒng)變量

            根據(jù)系統(tǒng)變量的作用域可分：全局變量、會話變量

    2菠红、@var_name表示的用戶變量

            ①用戶變量和數(shù)據(jù)庫連接有關(guān)第岖，連接后聲明變量，連接斷開后试溯，自動消失蔑滓；

            ②select一個(gè)沒有賦值的用戶變量，返回NULL遇绞，也就是沒有值键袱；

    Mysql的變量類似于動態(tài)語言，變量的值隨所要賦的值的類型而改變摹闽。

四十五蹄咖、set @@session.和set @@global.的生效時(shí)間

    對于一個(gè)新建立的連接，只有全局變量付鹿，會話變量還不存在澜汤，這個(gè)時(shí)候會從全局變量拷貝過來。

    1倘屹、set @@session.：只對當(dāng)前連接起作用

    2银亲、set @@global.：對全局變量的修改會影響到整個(gè)服務(wù)器

    注意：set系統(tǒng)變量時(shí)，不帶作用域修飾纽匙，默認(rèn)是指會話作用域；

    （特別注意拍谐，有些系統(tǒng)變量不帶作用域修飾烛缔，無法設(shè)置，因此最好都帶上作用域設(shè)置系統(tǒng)變量）轩拨。

四十六践瓷、動態(tài)參數(shù)最佳實(shí)踐

    1、盡量先進(jìn)行會話級別的設(shè)置set @@session亡蓉，確認(rèn)生效而且效果不錯(cuò)以后晕翠，再進(jìn)行全局設(shè)置，如果需要馬上生效砍濒，殺掉所有的會話：

            mysql> select concat('kill ',conn_id,';') from sys.session;

    2淋肾、確認(rèn)沒有問題以后，修改參數(shù)文件爸邢，下次系統(tǒng)啟動一直生效樊卓。

四十七、select書寫技巧

    1杠河、確認(rèn)需要訪問數(shù)據(jù)來自于哪幾張表

            from來自某張表或者某幾張表

            join添加某張表

            on表連接條件

    記住一點(diǎn)：每關(guān)聯(lián)一個(gè)表就需要加上對應(yīng)的on條件(on條件就是主外鍵條件)

    2碌尔、通過where條件來過濾數(shù)據(jù)

    3浇辜、確認(rèn)需求里面是否有分組聚合的含義

            分組：group by

            聚合：聚合函數(shù)

            聚合條件過濾：having

    4、是否需要排序

            order by

四十八唾戚、MySQL內(nèi)置函數(shù)(將列出的常見的一些函數(shù)熟悉過一遍)

    1柳洋、內(nèi)置函數(shù)的多少是一個(gè)數(shù)據(jù)庫是否成熟的標(biāo)志

    2、學(xué)會使用help Functions學(xué)習(xí)和使用函數(shù)(重點(diǎn)!!!!!!!!!!!)

    3叹坦、常用函數(shù)要過一遍

            ①日期時(shí)間相關(guān)的函數(shù)

            CURDATE膳灶、DATEDIFF、DATE_FORMAT立由、DAYOFWEEK轧钓、LAST_DAY、EXTRACT锐膜、STR_TO_DATE

            ②比較操作符要求都過一遍毕箍，help Comparison operators;

            ③流程控制行數(shù)help Control flow functions;

            ④加密函數(shù)help Encryption Functions;

                    只需要看看decode、password兩個(gè)函數(shù)即可

            ⑤信息獲取函數(shù)help Information Functions;

                    通過這些函數(shù)可以知道一些信息道盏，過一遍即可

            ⑥邏輯操作符help Logical operators;

                    而柑！、and荷逞、or媒咳，這些常用的要過一遍

            ⑦雜項(xiàng)函數(shù)help Miscellaneous Functions;

                    簡單瀏覽一下里面的函數(shù)，對于名字有個(gè)印象即可

            ⑧數(shù)值函數(shù)help Numeric Functions;

                    使用數(shù)據(jù)庫來進(jìn)行數(shù)學(xué)運(yùn)算的情況不多种远，常用的加減乘除涩澡、TRUNCATE、ROUND

            ⑨字符串函數(shù)help String Functions;

            CONCAT坠敷、CONCAT_WS妙同、CAST、FORMAT膝迎、LIKE粥帚、REGEXP、STRCMP限次、TRIM芒涡、SUBSTRING、UPPER卖漫，其它函數(shù)名字過一遍

    4费尽、聚合分組函數(shù)的使用了解

            ①select后面得列或者出現(xiàn)在group by中，或者加上聚合函數(shù)

    select c1,c2,sum(c3),count(c4)
    from t1
    group by c1,c2;
                    ②help contents;

            查看聚合函數(shù)help Functions and Modifiers for Use with GROUP BY;

            AVG懊亡、MAX依啰、MIN、SUM店枣、COUNT速警、COUNT DISTINCT叹誉、GROUP_CONCAT、BIT_AND闷旧、BIT_OR长豁、BIT_XOR

四十九、隱式類型轉(zhuǎn)換忙灼，要避免隱式類型轉(zhuǎn)換

    1匠襟、最常用的幾個(gè)數(shù)據(jù)類型：數(shù)字、字符串该园、日期時(shí)間

    2酸舍、字符串里面可以存放數(shù)字和日期，但是在設(shè)計(jì)表的時(shí)候里初，要注意不要將日期和數(shù)字列設(shè)計(jì)成字符串列

    3啃勉、對于字符串列的比較，一定要加上引號：

            mysql> select * from t where name_phone='1301110001';

五十双妨、limit使用很頻繁淮阐，注意其使用方法

    1、limit使用的場合

            從結(jié)果集中選取最前面或最后面的幾行

    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互相轉(zhuǎn)換

    1、in和exists可以互相轉(zhuǎn)換

    select * from players a where a.teamno in (select teamno from team where teamname='騎士隊(duì)');
    
    select * from players a where exists (select 1 from team b where a.teamno=b.teamno and b.teamname='騎士隊(duì)');
    2暑劝、not in和not exists可以互相轉(zhuǎn)換

    3、not in颗搂、not exists可以轉(zhuǎn)換成left join

    select * from 學(xué)生信息 a where a.stuno not in (select stuno from 選課信息表);
    
    select * from 學(xué)生信息 a
    left join 選課信息 b
    on  a.stuno=b.stuno
    where b.成績 is null;
            4担猛、in、exists可以轉(zhuǎn)換成distinct join
    select * from 學(xué)生信息 a where a.stuno in (select stuno from 選課信息表 b);
    
    select * from 學(xué)生信息 a where exists (select 1 from 選課信息 b where a.stuno=b.stuno);
    
    select distinct a.*
    from 學(xué)生信息
    join 選課信息 b
    on a.stuno=b.stuno;

五十二丢氢、連接的具體使用含義

    1傅联、理解為什么會出現(xiàn)表連接：查詢的列來自于多個(gè)表

    select 列
    from ..
    where 列
    group by 列
    having 列
    order by 列
    limit x
    2、理解表連接的書寫方式

            join一個(gè)表疚察、on一個(gè)條件

    3蒸走、理解表連接的注意條件

            ①兩個(gè)表要連接一定要存在主外鍵關(guān)系(有可能需要第三張表協(xié)助關(guān)聯(lián))

                    實(shí)際上存在外鍵約束

                    存在外鍵列，但是沒有外鍵約束

            ②防止扇形陷阱(兩個(gè)表需要關(guān)聯(lián)貌嫡，但是沒有直接主外鍵比驻，借助第三個(gè)表進(jìn)行關(guān)聯(lián)该溯，但是存在扇形問題，此時(shí)不能借助第三個(gè)表進(jìn)行關(guān)聯(lián))

            示例：學(xué)院表虱饿、專業(yè)表平斩、學(xué)生表

                    學(xué)院實(shí)體和專業(yè)實(shí)體之間是一對多的聯(lián)系蒿往；

                    學(xué)院實(shí)體和學(xué)生實(shí)體之間也是一對多的聯(lián)系；

                    而學(xué)生和專業(yè)之間沒有聯(lián)系氯庆；

            如果學(xué)生和專業(yè)通過學(xué)院表進(jìn)行關(guān)聯(lián)，就會出現(xiàn)扇形問題扰付。

    4堤撵、外連接：左外連接、右外連接

            外連接是為了防止出現(xiàn)某一個(gè)表的數(shù)據(jù)被遺漏

            開發(fā)人員非常喜歡使用外連接.

五十三羽莺、子查詢

    1实昨、子查詢可能出現(xiàn)的位置

            ①select from之間可能會出現(xiàn)子查詢

            ②from后面

            ③join后面可能會出現(xiàn)子查詢

            ④where后面可能會出現(xiàn)子查詢

            ⑤having后面可能會出現(xiàn)子查詢

    2、盡最大程度的不要使用子查詢

    3禽翼、相關(guān)子查詢屠橄、無關(guān)子查詢

            相關(guān)子查詢特別容易出現(xiàn)在select from之間、where后面

            相關(guān)子查詢不能獨(dú)立執(zhí)行闰挡，子查詢執(zhí)行次數(shù)取決于父查詢返回的行數(shù)

            無關(guān)子查詢可以獨(dú)立執(zhí)行锐墙，子查詢執(zhí)行一次

五十四、子查詢出現(xiàn)的場合

    1长酗、where中出現(xiàn)的子查詢溪北，一般可使用表連接進(jìn)行改寫

            ①select 列(涉及到A表，沒有涉及到B表)

            ②where 條件(涉及到B表)

    2夺脾、from后面的子查詢

            ①對于取出來的數(shù)據(jù)再次進(jìn)行復(fù)雜的處理

                    例如分組聚合之拨、having條件、where條件等

            ②對一個(gè)結(jié)果集再次進(jìn)行復(fù)雜的查詢

            意味著我們?nèi)?shù)據(jù)的這個(gè)過程中咧叭，對數(shù)據(jù)進(jìn)行處理的力度很復(fù)雜

    3蚀乔、select from之間的子查詢

            對于返回的每一行數(shù)據(jù)，select和from之間的子查詢都要執(zhí)行一次

            select后面的列要進(jìn)行復(fù)雜的處理菲茬，如果這個(gè)處理涉及到另外一個(gè)表吉挣，若這個(gè)表很可能沒有出現(xiàn)在from和join里面，則進(jìn)行子查詢：

示例：將每一個(gè)同學(xué)的成績列出來婉弹，同時(shí)計(jì)算他的成績和本組平均成績的差距

select 學(xué)生成績,
學(xué)生成績-(select avg(成績) from 選課表 a  where a.組ID=b.組ID)
from 選課表 b;

五十五睬魂、select執(zhí)行的順序

    select ...
    from ...
    join ...
    on ...
    where ...
    group by ..
    having ...
    order by ...
    1、先從表中取數(shù)據(jù)镀赌，訪問innodb buffer pool

            from ...

            join ...

            on ...

            where

    2氯哮、分組、聚合商佛，數(shù)據(jù)已經(jīng)進(jìn)入用戶工作空間

            group by ...

            having ...

    3喉钢、select ....：取列數(shù)據(jù)

    4姆打、order by：排序輸出

五十六、集合操作

    union：結(jié)果集去重

    union all：結(jié)果集不去重

五十七出牧、insert增

    1穴肘、insert values一條數(shù)據(jù)

            表的名字后面最好加上列的名字

    2、insert values多條數(shù)據(jù)

    3舔痕、insert into select

            select可以非常復(fù)雜评抚，語法完全就是select

五十八、update改

    基本格式：update 一個(gè)表 set 列 where 列條件;

    1伯复、一定要帶上where條件

    2慨代、update分為下面的幾個(gè)步驟操作

            ①找到需要update的數(shù)據(jù)，此操作取決于where條件

            where條件可以是一個(gè)復(fù)雜的where條件啸如，比如是一個(gè)子查詢

    示例：將平均成績75分以上的學(xué)生的級別設(shè)置為優(yōu)等生
    
    update 學(xué)生信息表 a
    set grade=‘優(yōu)等生’
    where a.stuno in (select b.stuno from 成績表 b group by b.stuno having avg(成績)>=75);
                    ②set后面的列侍匙，也可以很復(fù)雜，比如是一個(gè)相對子查詢
    
    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可以改寫成一個(gè)select語句
            把1和2改寫成一個(gè)select語句想暗，不要對一個(gè)update在生產(chǎn)里面直接進(jìn)行優(yōu)化

    4、update可以使用order by帘不，數(shù)據(jù)按照順序進(jìn)行更新

    5说莫、update可以使用limit，限制每次更新的行數(shù)

五十九寞焙、replace替代已有的行

    使用場合insert+update储狭，兩個(gè)表數(shù)據(jù)合并到一起

六十、delete刪

    1捣郊、絕大多數(shù)情況下需要加上where條件

    2辽狈、where條件可以很復(fù)雜，例如是一個(gè)子查詢

    3呛牲、理解delete和truncate的區(qū)別

            truncate：清空全部數(shù)據(jù)刮萌、速度快、釋放空間(不刪表)

            delete：全部或者部分刪除數(shù)據(jù)娘扩、速度慢尊勿、不釋放空間

六十一、臨時(shí)表

    1畜侦、只是針對當(dāng)前會話有效，臨時(shí)表和數(shù)據(jù)都存儲在用戶工作空間

    2躯保、臨時(shí)表的使用很消耗資源

            ①create旋膳、insert、drop途事，因此在非常頻繁的查詢環(huán)境下验懊，不宜使用臨時(shí)表擅羞；

            ②臨時(shí)表需要使用用戶工作空間，臨時(shí)表中存在的數(shù)據(jù)不易過多义图，否則容易出現(xiàn)磁盤臨時(shí)表减俏；

    3、臨時(shí)表的使用場合

            需要暫存結(jié)果集數(shù)據(jù)碱工，后面的操作需要訪問這些暫存結(jié)果集娃承，主要是為了可讀性。

    4怕篷、有一種誤區(qū)一定要注意历筝，一定不要將普通表作為臨時(shí)表來使用

    原因：普通表當(dāng)做臨時(shí)表來使用，下面的操作需要手工去做

            ①create廊谓、insert梳猪、truncate或者drop

            ②對于普通表的所有操作都會產(chǎn)生redo(事務(wù))，非常消耗資源

六十二蒸痹、關(guān)于約束

    1春弥、非空

    2、default約束

    3叠荠、主鍵約束

    4匿沛、外鍵約束

    5、SET蝙叛、ENUM約束

    約束注意點(diǎn)：

    ①盡量選擇列都為非空

    ②對于bool俺祠、時(shí)間列經(jīng)常會出現(xiàn)default約束

    ③每一個(gè)表盡最大程度要有主鍵

    ④唯一鍵可以有多個(gè)，唯一鍵可以有空值

    ⑤外鍵列一般會有借帘，但是外鍵約束不建議使用蜘渣，在應(yīng)用層面保證主表和外表的一致性

    ⑥合理使用set和enum約束，提升數(shù)據(jù)的質(zhì)量

    ⑦外鍵約束中on delete肺然、update蔫缸，盡量不要設(shè)置級聯(lián)刪除操作(很危險(xiǎn)！＜势稹拾碌！)

六十三、表的DDL

    1街望、極其嚴(yán)肅的一個(gè)動作

    2校翔、使用help書寫DDL語句

    3、ddl動作的后遺癥和危險(xiǎn)性

            ①影響I灾前、D防症、U、S

            ②長時(shí)間鎖表、產(chǎn)生海量IO

    4蔫敲、測試DDL的影響范圍---優(yōu)化對象

            ①鎖表時(shí)間

            ②IO情況

            ③具體測試要求

    示例：產(chǎn)生一個(gè)500萬行的表(寫一個(gè)存儲過程實(shí)現(xiàn))饲嗽，對表進(jìn)行增加列、刪除列奈嘿、修改列的名字貌虾、將列的長度變長、將列的長度變短
    
    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 |
    +----------+

    看一下上面的這些操作裙犹，哪些操作時(shí)間長尽狠、哪些操作時(shí)間短，并對其進(jìn)行初步的原理分析

    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);
    總結(jié)：對于一個(gè)大表而言伯诬，將列的長度變長時(shí)間是最長的晚唇，其他的操作處理時(shí)間都還挺短。

六十四盗似、視圖的最佳實(shí)踐

    1哩陕、視圖就是select的一個(gè)名字

    2、不建議使用復(fù)雜視圖

            select語句里面不要帶有distinct赫舒、group by悍及、聚合函數(shù)、union等操作

    3接癌、不建議在視圖中嵌套視圖

    4心赶、視圖的主要使用場合

            統(tǒng)一訪問接口(select)---主要的好處

            規(guī)范訪問

            隱藏底層表結(jié)構(gòu)、ddl不影響應(yīng)用訪問

    5缺猛、視圖在安全方面的意義

六十五缨叫、存儲過程(腳本)

    1、存儲過程使用的場合

            ①重復(fù)性很高的復(fù)合操作(dml)

            ②統(tǒng)一訪問接口(dml荔燎、事務(wù))

            ③批量業(yè)務(wù)(跑批)

    2耻姥、存儲過程結(jié)構(gòu)分析

            ①存儲過程中嵌入了dml、select

            ②存儲過程有參數(shù)有咨，參數(shù)的不同會產(chǎn)生不同的事務(wù)

                    in琐簇、out、inout

            ③存儲過程里面有結(jié)構(gòu)化語句座享，即流程控制語句：

                    循環(huán)

                    條件判斷

            使得在執(zhí)行dml婉商、select的時(shí)候，變得方便

            ④存儲過程可以定義變量

                    select取出來的結(jié)果可以存儲到變量中

                    dml需要的輸入值可以通過變量來實(shí)現(xiàn)

            ⑤存儲過程里面可以有游標(biāo)渣叛，游標(biāo)的核心就是可以對一個(gè)結(jié)果集進(jìn)行處理

                    1)定義游標(biāo)(游標(biāo)和一個(gè)select關(guān)聯(lián))

                    2)打開游標(biāo)(將select的結(jié)果賦給游標(biāo)丈秩，可以是N行列)

                    3)遍歷游標(biāo)(一行行數(shù)據(jù)獲取，每一行數(shù)據(jù)賦給N個(gè)變量)

                    4)關(guān)閉游標(biāo)

            ⑥存儲過程有異常處理部分

                    1)異常處理是一個(gè)存儲過程是否可以產(chǎn)品化淳衙、商業(yè)化很重要的一個(gè)標(biāo)志

                    2)異常處理只關(guān)心SQL語句的異常

            每一個(gè)存儲過程都要對著三類SQLWARNING癣籽、NOT FOUND挽唉、SQLEXCEPTION進(jìn)行處理；

            存儲過程異常處理通常只是進(jìn)行錯(cuò)誤的記錄筷狼，或者空處理。

            ⑦存儲過程書寫過程

                    1)定義一個(gè)結(jié)構(gòu)

                            存儲過程基本結(jié)構(gòu)

                            參數(shù)

                            異常處理

                    2)書寫涉及到SQL語句

                    3)考慮使用變量匠童、游標(biāo)埂材、條件判斷、循環(huán)將SQL語句組合起來

                    4)經(jīng)常使用begin end來將一組SQL語句或者語句組合起來汤求，作為一個(gè)語句來出現(xiàn)

    3俏险、存儲過程安全方面的意義：防止對底層表直接進(jìn)行dml

六十六、自定義函數(shù)

    1扬绪、自定義函數(shù)和存儲過程的區(qū)別

    ①有一個(gè)返回值

    CREATE FUNCTION SimpleCompare(n INT, m INT)
    RETURNS VARCHAR(20)
    ……
    ②調(diào)用的時(shí)候必須放在=的右邊

            set @ax = SimpleCompare(1,2);

    2竖独、整理筆記，將函數(shù)定義和函數(shù)調(diào)用整理一個(gè)例子出來

六十七挤牛、觸發(fā)器

    1莹痢、盡量少使用觸發(fā)器，不建議使用

    2墓赴、觸發(fā)器是一個(gè)begin end結(jié)構(gòu)體

    3竞膳、觸發(fā)器和存儲過程的唯一區(qū)別就是在于被執(zhí)行方式上的區(qū)別

            存儲過程需要手工去執(zhí)行

            觸發(fā)器被DML自動觸發(fā)

    4、觸發(fā)器被觸發(fā)的條件

            ①for each row(每一行都被觸發(fā)一次诫硕，這就決定了頻繁dml的表上面不要有觸發(fā)器)

            ②增刪改都可以定義觸發(fā)器

            ③before坦辟、after可以定義觸發(fā)的時(shí)機(jī)

    5、觸發(fā)器中經(jīng)常使用new章办、old

            insert里面可以有new

            delete里面可以有old

            update里面可以有new锉走、old

    6、使用觸發(fā)器的場合

    一般用來進(jìn)行審計(jì)使用：產(chǎn)品價(jià)格表里面的價(jià)格這個(gè)列藕届，只要是有人對這個(gè)表的這個(gè)列進(jìn)行更新挪蹭，就要保存修改前和修改后的值，將這個(gè)信息記錄到一個(gè)單獨(dú)的表中(審計(jì)表)

    7翰舌、要求你將觸發(fā)器的例子保存到筆記中

            ①insert觸發(fā)器（new）

            ②delete觸發(fā)器（old）

            ③update觸發(fā)器（new嚣潜、old）

            ④before、after

六十八椅贱、event

    1懂算、周期性執(zhí)行

            ①linux里面的at、crontab

            ②MySQL里面的event

    2庇麦、event的核心知識點(diǎn)

 ①執(zhí)行一次

    CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO
            begin
                    UPDATE t1 SET mycol = mycol + 1;
            end
 ②周期性執(zhí)行
    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