停止命令：net stop mysql 啟動命令：net start mysql mysql登錄命令 mysql -h ip -P 埠 -u 用戶名 -p mysql --version 或者mysql -V用於在未登錄情況下，查看本機mysql版本 select version();：登錄情況下 ...
停止命令：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語法規範
- 不區分大小寫，但建議關鍵字大寫，表名、列名小寫
- 每條命令最好用英文分號結尾
- 每條命令根據需要，可以進行縮進或換行
- 註釋
- 單行註釋：#註釋文字
- 單行註釋：-- 註釋文字 ，註意， 這裡需要加空格
- 多行註釋：/ **註釋文字 **/
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
類型(n)說明
在開發中，我們會碰到有些定義整型的寫法是int(11)，這種寫法個人感覺在開發過程中沒有什麼用途，不過還是來說一下，int(N)我們只需要記住兩點：- 無論N等於多少，int永遠占4個位元組
- N表示的是顯示寬度，不足的用0補足，超過的無視長度而直接顯示整個數字，但這要整型設置了unsigned zerofill才有效
就是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 '密碼'];說明：通過修改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命令說明：
- 主機名預設值為%，表示這個用戶可以從任何主機連接mysql伺服器
- 密碼可以省略，表示無密碼登錄
- 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段
- 初始化資料庫的時候刪除沒有密碼的用戶，安裝完資料庫的時候會自動創建一些用戶，這些用戶預設沒有密碼
- 為每個用戶設置滿足密碼複雜度的密碼
- 定期清理不需要的用戶，回收許可權或者刪除用戶
總結
- 通過命令的方式操作用戶和許可權不需要刷新，下次登錄自動生效
- 通過操作mysql庫中表的方式修改、用戶信息，需要調用flush privileges;刷新一下，下次登錄自動生效
- mysql識別用戶身份的方式是：用戶名+主機
- 本文中講到的一些指令中帶主機的，主機都可以省略，預設值為%，表示所有機器
- 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 | |
| 條件刪除 | 不支持 | 不支持 | 支持 |
| 刪除表結構 | 支持 | 不支持 | 不支持 |
| 事務的方式刪除 | 不支持 | 不支持 | 支持 |
| 觸發觸發器 | 否 | 否 | 是 |
like（模糊查詢）
select 列名 from 表名 where 列 like pattern;pattern中可以包含通配符，有以下通配符： %：表示匹配任意一個或多個字元 _：表示匹配任意一個字元。
查詢運算符、like、between and、in、not in對NULL值查詢不起效
<=>（安全等於）
<=>：既可以判斷NULL值，又可以判斷普通的數值，可讀性較低，用得較少總結
- like中的%可以匹配一個到多個任意的字元，_可以匹配任意一個字元
- 空值查詢需要使用IS NULL或者IS NOT NULL，其他查詢運算符對NULL值無效
- 建議創建表的時候，儘量設置表的欄位不能為空，給欄位設置一個預設值
- <=>（安全等於）玩玩可以，建議少使用
說明： 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 二分法查找數據的優點：定位數據非常快，前提是：目標數組是有序的。總結一下使用索引的一些建議
- 在區分度高的欄位上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有數據頁，此時和不使用索引差不多
- 聯合索引註意最左匹配原則：必須按照從左到右的順序匹配，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的順序可以任意調整
- 查詢記錄的時候，少使用*，儘量去利用索引覆蓋，可以減少回表操作，提升效率
- 有些查詢可以採用聯合索引，進而使用到索引下推（IPC），也可以減少回表操作，提升效率
- 禁止對索引欄位使用函數、運算符操作，會使索引失效
- 字元串欄位和數字比較的時候會使索引無效
- 模糊查詢'%值%'會使索引無效，變為全表掃描，但是'值%'這種可以有效利用索引
- 排序中儘量使用到索引欄位，這樣可以減少排序，提升查詢效率
