Mysql資料庫 深度知識點

停止命令：net stop mysql 啟動命令：net start mysql  

mysql登錄命令

mysql -h ip -P 埠 -u 用戶名 -p   mysql --version 或者mysql -V用于在未登錄情況下，查看本機mysql版本 select version();：登錄情況下，查看鏈接的庫版本   顯示所有資料庫：show databases; 進入指定的庫：use 庫名; 顯示當前庫中所有的表：show tables; 查看其他庫中所有的表：show tables from 庫名; 查看表的創建陳述句：show create table 表名; 查看表結構：desc 表名; 查看當前所在庫：select database();  

mysql語法規范

1. 不區分大小寫，但建議關鍵字大寫，表名、列名小寫
2. 每條命令最好用英文分號結尾
3. 每條命令根據需要，可以進行縮進或換行
4. 注釋
• 單行注釋：#注釋文字
• 單行注釋：-- 注釋文字 ，注意， 這里需要加空格
• 多行注釋：/ **注釋文字 **/

SQL的語言分類

• DQL（Data Query Language）：資料查詢語言 select 相關陳述句
• DML（Data Manipulate Language）：資料操作語言 insert 、update、delete 陳述句
• DDL（Data Define Languge）：資料定義語言 create、drop、alter 陳述句
• TCL（Transaction Control Language）：事務控制語言 set autocommit=0、start transaction、savepoint、commit、rollback

  主要包括以下五大類 整數型別：bit、bool、tinyint、smallint、mediumint、int、bigint 浮點數型別：float、double、decimal 字串型別：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext 日期型別：Date、DateTime、TimeStamp、Time、Year 其他資料型別：暫不介紹，用的比較少，  

型別(n)說明

在開發中，我們會碰到有些定義整型的寫法是int(11)，這種寫法個人感覺在開發程序中沒有什么用途，不過還是來說一下，int(N)我們只需要記住兩點：

• 無論N等于多少，int永遠占4個位元組
• N表示的是顯示寬度，不足的用0補足，超過的無視長度而直接顯示整個數字，但這要整型設定了unsigned zerofill才有效

  decimal采用的是四舍五入  float和double采用的是四舍六入五成雙 什么是四舍六入五成雙？

就是5以下舍棄5以上進位，如果需要處理數字為5的時候，需要看5后面是否還有不為0的任何數字，如果有，則直接進位，如果沒有，需要看5前面的數字，若是奇數則進位，若是偶數則將5舍掉

char型別占用固定長度，如果存放的資料為固定長度的建議使用char型別，如：手機號碼、身份證等固定長度的資訊

資料型別選擇的一些建議

• 選小不選大：一般情況下選擇可以正確存盤資料的最小資料型別，越小的資料型別通常更快，占用磁盤，記憶體和CPU快取更小，
• 簡單就好：簡單的資料型別的操作通常需要更少的CPU周期，例如：整型比字符操作代價要小得多，因為字符集和校對規則(排序規則)使字符比整型比較更加復雜，

 

• 盡量避免NULL：盡量制定列為NOT NULL，除非真的需要NULL型別的值，有NULL的列值會使得索引、索引統計和值比較更加復雜，
• 浮點型別的建議統一選擇decimal
• 記錄時間的建議使用int或者bigint型別，將時間轉換為時間戳格式，如將時間轉換為秒、毫秒，進行存盤，方便走索引

 

權限生效時間

用戶及權限資訊放在庫名為mysql的庫中，mysql啟動時，這些內容被讀進記憶體并且從此時生效，所以如果通過直接操作這些表來修改用戶及權限資訊的，需要重啟mysql或者執行flush privileges;才可以生效， 用戶登錄之后，mysql會和當前用戶之間創建一個連接，此時用戶相關的權限資訊都保存在這個連接中，存放在記憶體中，此時如果有其他地方修改了當前用戶的權限，這些變更的權限會在下一次登錄時才會生效，

創建用戶

語法： create user 用戶名[@主機名] [identified by '密碼'];

說明：

1. 主機名默認值為%，表示這個用戶可以從任何主機連接mysql服務器
2. 密碼可以省略，表示無密碼登錄

通過修改mysql.user表修改密碼 use mysql; update user set authentication_string = password('321') where user = 'test1' and host = '%'; flush privileges;   給用戶授權： grant privileges ON database.table TO 'username'[@'host'] [with grant option]   grant命令說明：

• priveleges (權限串列)，可以是all，表示所有權限，也可以是select、update等權限，多個權限之間用逗號分開，
• ON 用來指定權限針對哪些庫和表，格式為資料庫.表名 ，點號前面用來指定資料庫名，點號后面用來指定表名，*.* 表示所有資料庫所有表，
• TO 表示將權限賦予某個用戶, 格式為username@host，@前面為用戶名，@后面接限制的主機，可以是IP、IP段、域名以及%，%表示任何地方，
• WITH GRANT OPTION 這個選項表示該用戶可以將自己擁有的權限授權給別人，注意：經常有人在創建操作用戶的時候不指定WITH GRANT OPTION選項導致后來該用戶不能使用GRANT命令創建用戶或者給其它用戶授權， 備注：可以使用GRANT重復給用戶添加權限，權限疊加，比如你先給用戶添加一個select權限，然后又給用戶添加一個insert權限，那么該用戶就同時擁有了select和insert權限，

查看用戶有哪些權限

show grants for '用戶名'[@'主機'] show grants -- 查看當前用戶的權限 撤銷用戶的權限：revoke privileges ON database.table FROM '用戶名'[@'主機']; 洗掉用戶： 1.drop user '用戶名'[@‘主機’] 2.delete from user where user='用戶名' and host='主機';    

授權原則說明

• 只授予能滿足需要的最小權限，防止用戶干壞事，比如用戶只是需要查詢，那就只給select權限就可以了，不要給用戶賦予update、insert或者delete權限
• 創建用戶的時候限制用戶的登錄主機，一般是限制成指定IP或者內網IP段
• 初始化資料庫的時候洗掉沒有密碼的用戶，安裝完資料庫的時候會自動創建一些用戶，這些用戶默認沒有密碼
• 為每個用戶設定滿足密碼復雜度的密碼
• 定期清理不需要的用戶，回收權限或者洗掉用戶

 

總結

1. 通過命令的方式操作用戶和權限不需要重繪，下次登錄自動生效
2. 通過操作mysql庫中表的方式修改、用戶資訊，需要呼叫flush privileges;重繪一下，下次登錄自動生效
3. mysql識別用戶身份的方式是：用戶名+主機
4. 本文中講到的一些指令中帶主機的，主機都可以省略，默認值為%，表示所有機器
5. mysql中用戶和權限的資訊在庫名為mysql的庫中

   

修改列

alter table 表名 modify column 列名 新型別 [約束]; 或者 alter table 表名 change column 列名 新列名 新型別 [約束]; 2種方式區別：modify不能修改列名，change可以修改列名  

delete單表洗掉

delete [別名] from 表名 [[as] 別名] [where條件];

注意： 如果無別名的時候，表名就是別名 如果有別名，delete后面必須寫別名 如果沒有別名，delete后面的別名可以省略不寫，

示例

-- 洗掉test1表所有記錄delete from test1; -- 洗掉test1表所有記錄delete test1 from test1; -- 有別名的方式，洗掉test1表所有記錄delete t1 from test1 t1; -- 有別名的方式洗掉滿足條件的記錄delete t1 from test1 t1 where t1.a>100;

多表洗掉

可以同時洗掉多個表中的記錄，語法如下：

delete [別名1,別名2] from 表1 [[as] 別名1],表2 [[as] 別名2] [where條件];

說明： 別名可以省略不寫，但是需要在delete后面跟上表名，多個表名之間用逗號隔開，

示例1

delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;

洗掉test1表中的記錄，條件是這些記錄的欄位a在test.c2中存在的記錄

 

drop，truncate，delete區別

• drop (洗掉表)：洗掉內容和定義，釋放空間，簡單來說就是把整個表去掉，以后要新增資料是不可能的，除非新增一個表， drop陳述句將洗掉表的結構被依賴的約束（constrain），觸發器（trigger）索引（index），依賴于該表的存盤程序/函式將被保留，但其狀態會變為：invalid， 如果要洗掉表定義及其資料，請使用 drop table 陳述句，
• truncate (清空表中的資料)：洗掉內容、釋放空間但不洗掉定義(保留表的資料結構)，與drop不同的是，只是清空表資料而已， 注意：truncate不能洗掉具體行資料，要刪就要把整個表清空了，
• delete (洗掉表中的資料)：delete 陳述句用于洗掉表中的行，delete陳述句執行洗掉的程序是每次從表中洗掉一行，并且同時將該行的洗掉操作作為事務記錄在日志中保存，以便進行進行回滾操作， truncate與不帶where的delete ：只洗掉資料，而不洗掉表的結構（定義） truncate table 洗掉表中的所有行，但表結構及其列、約束、索引等保持不變， 對于由foreign key約束參考的表，不能使用truncate table ，而應使用不帶where子句的delete陳述句，由于truncate table 記錄在日志中，所以它不能激活觸發器， delete陳述句是資料庫操作語言(dml)，這個操作會放到 rollback segement 中，事務提交之后才生效；如果有相應的 trigger，執行的時候將被觸發， truncate、drop 是資料庫定義語言(ddl)，操作立即生效，原資料不放到 rollback segment 中，不能回滾，操作不觸發 trigger， 如果有自增列，truncate方式洗掉之后，自增列的值會被初始化，delete方式要分情況（如果資料庫被重啟了，自增列值也會被初始化，資料庫未被重啟，則不變）
• 如果要洗掉表定義及其資料，請使用 drop table 陳述句
• 安全性：小心使用 drop 和 truncate，尤其沒有備份的時候，否則哭都來不及
• 洗掉速度，一般來說: drop> truncate > delete

drop	truncate	delete
條件洗掉	不支持	不支持	支持
洗掉表結構	支持	不支持	不支持
事務的方式洗掉	不支持	不支持	支持
觸發觸發器	否	否	是

別名中有特殊符號的，比如空格，此時別名必須用引號引起來，   不等于符號的使用： <> 這個是最早的用法， !=是后來才加上的， 兩者意義相同，在可移植性上前者優于后者 故而sql陳述句中盡量使用<>來做不等判斷    

 

like（模糊查詢）

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符： %：表示匹配任意一個或多個字符 _：表示匹配任意一個字符，

 

查詢運算子、like、between and、in、not in對NULL值查詢不起效  

<=>（安全等于）

<=>：既可以判斷NULL值，又可以判斷普通的數值，可讀性較低，用得較少  

總結

• like中的%可以匹配一個到多個任意的字符，_可以匹配任意一個字符
• 空值查詢需要使用IS NULL或者IS NOT NULL，其他查詢運算子對NULL值無效
• 建議創建表的時候，盡量設定表的欄位不能為空，給欄位設定一個默認值
• <=>（安全等于）玩玩可以，建議少使用

語法： select 列 from 表 limit [offset,] count;

說明： offset：表示偏移量，通俗點講就是跳過多少行，offset可以省略，默認為0，表示跳過0行；范圍：[0,+∞)， count：跳過offset行之后開始取資料，取count行記錄；范圍：[0,+∞)， limit中offset和count的值不能用運算式，

 

limit中不能使用運算式，limit后面的2個數字不能為負數

 

欄位存在相同的值，當排序程序中存在相同的值時，沒有其他排序規則時，mysql懵逼了，不知道怎么排序了；  建議：分頁排序時，排序不要有二義性，二義性情況下可能會導致分頁結果亂序，可以在后面追加一個主鍵排序 where 多欄位同時限制： SELECT user_id 用戶id, price 最大金額, the_year 年份 FROM t_order t1 WHERE (t1.user_id , t1.price) IN (SELECT t.user_id, MAX(t.price) FROM t_order t GROUP BY t.user_id); 現在我們來討論java輸出的順序為何和sql不一致？

上面java代碼中兩個表的連接查詢使用了嵌套回圈，外回圈每執行一次，內回圈的表都會全部遍歷一次，如果放到mysql中，就相當于內標全部掃描了一次（一次全表io讀取操作），主表（外回圈）如果有n條資料，那么從表就需要全表掃描n次，表的資料是存盤在磁盤中，每次全表掃描都需要做io操作，io操作是最耗時間的，如果mysql按照上面的java方式實作，那效率肯定很低，

那mysql是如何優化的呢？

msql內部使用了一個記憶體快取空間，就叫他join_buffer吧，先把外回圈的資料放到join_buffer中，然后對從表進行遍歷，從表中取一條資料和join_buffer的資料進行比較，然后從表中再取第2條和join_buffer資料進行比較，直到從表遍歷完成，使用這方方式來減少從表的io掃描次數，當join_buffer足夠大的時候，大到可以存放主表所有資料，那么從表只需要全表掃描一次（即只需要一次全表io讀取操作）， mysql中這種方式叫做Block Nested Loop，     欄位值為NULL的時候，not in查詢有大坑，這個要注意 建議創建表的時候，列不允許為空  

delimiter關鍵字  sql陳述句結束符

delimiter用來設定結束符，當mysql執行腳本的時候，遇到結束符的時候，會把結束符前面的所有陳述句作為一個整體運行，存盤程序中的腳本有多個sql，但是需要作為一個整體運行，所以此處用到了delimiter   /*在執行程序中出任何例外設定hasSqlError為TRUE*/ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE; 根據hasSqlError判斷是否有例外，做回滾和提交操作     mysql使用binlog和relaylog記錄資料庫的變化 binlog有三種格式：ROW STATEMENT MIXED ，ROW記錄national資料被修改了，修改成了什么樣子；STATEMENT 記錄修改的sql到binlog；MIXED就是以上兩種模式的混合， binlog是一個二進制檔案，決議binlog有兩種方式： 1.在命令列使用,show binlog events in 'binlog-filename'；方式查看 2.下載分析binlog，然后通過mysqlbinlog工具進行決議     扇區：磁盤存盤的最小單位，扇區一般大小為512Byte 磁盤塊：檔案系統與磁盤互動的最小單位（計算機系統讀寫磁盤的最小單位），一個磁盤塊由連續幾個扇區組成，塊一般大小為4KB   二分法查找資料的優點：定位資料非常快，前提是：目標陣列是有序的，  

總結一下使用索引的一些建議

1. 在區分度高的欄位上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有資料頁，此時和不使用索引差不多
2. 聯合索引注意最左匹配原則：必須按照從左到右的順序匹配，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整
3. 查詢記錄的時候，少使用*，盡量去利用索引覆寫，可以減少回表操作，提升效率
4. 有些查詢可以采用聯合索引，進而使用到索引下推（IPC），也可以減少回表操作，提升效率
5. 禁止對索引欄位使用函式、運算子操作，會使索引失效
6. 字串欄位和數字比較的時候會使索引無效
7. 模糊查詢'%值%'會使索引無效，變為全表掃描，但是'值%'這種可以有效利用索引
8. 排序中盡量使用到索引欄位，這樣可以減少排序，提升查詢效率

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116982.html

標籤：MySQL

上一篇：不就是SELECT COUNT陳述句嗎，竟然能被面試官虐的體無完膚

下一篇：[mybatis] sql陳述句無錯誤，但是執行多條sql陳述句時，拋出java.sql.SQLSyntaxErrorException