標籤：索引 tiny 原理 之間 process 掃描 rand unix 利用
Mysql常用命令
note:粗體字部分是使用者需要根據自己的實際情況替換的部分，斜體部分為可選內容，#開頭的命令是在終端中執行，>開頭的命令是在mysql中執行
1. 登陸mysql
- # mysql -u username -p （斷行符號後輸入密碼）
- # msyql -uroot -p123（root為使用者名稱，123為密碼 -u和root中間，-p和123中間沒有空格）
- 遠端連線mysql
用法：# mysql -uusername -ppassword -h host -P 連接埠號碼 -D databases
樣本：# mysql -uroot -p123456 -h 192.168.1.91 -P 3306 -D test
2. 執行sql檔案
- > source /path/source.sql;
- # mysql -uroot -p123 < source.sql
3.1 Database Backup
假設要備份temp這個資料庫：
- # mysqldump -uroot -p123 temp > dump.sql
假設要備份temp這個資料庫中的表table1和table2：
- # mysqldump -uroot -p123 temp table1 table2 > dump.sql
利用gzip壓縮資料：
- # mysqldump -uroot -p123 temp table1 table2 | gzip > dump.sql.gz
備份多個資料庫：
- # mysqldump -uroot -p123 --databases db1 [db2 db3..] > dump.sql
增加where條件:
- # mysqldump -uroot -p123 temp table --where="" > dump.sql
當不能使用mysqldump時
- # mysql -uroot -p123 -Ddatabase -e "select concat(‘insert into table values (‘,id,‘)‘"
3.2 資料庫恢複
- # mysql -uroot -p123 temp < dump.sql
從壓縮檔直接恢複：
- # gunzip < dump.sql.gz | mysql -uroot -p123 temp
4. 表結構修改
- 4.1 增加一個新列用法：> alter table tablename add 列名 類型 約束條件;
樣本：> alter table user add name varchar(32) not null default ‘unknow‘;
- 4.2 刪除列用法：> alter table tablename drop column 列名;
樣本：> alter table user drop column name;
- 4.3 重新命名列用法：> alter table tablename change 原列名 新列名 原類型 約束條件;
樣本：> alter table user change name nickname varchar(32) not null default ‘unknow‘;
- 4.4 改變列的類型用法：> alter table tablename change 列名 列名 新類型 約束條件;
樣本：> alter table user change name name tinyint not null default ‘0‘;
用法：> alter table tablename alter column 列名 新類型;
樣本：> alter table user alter column name tinyint;
- 4.5 重新命名表用法：> alter table tablename1 rename tablename2;
樣本：> alter table user rename new_user;
- 4.6 加索引用法：> alter table tablename add index 索引名(欄位名1[，欄位名2 …]);
樣本：> alter table user add index name_age(name,age);
- 4.7 加主關鍵字的索引用法：> alter table tablename add primary key(id);
樣本：> alter table user add primary key(id);
- 4.8 加唯一限制條件的索引用法：> alter table tablename add unique 索引名(欄位名);
樣本：> alter table user add unique index_name(name);
- 4.9 刪除某個索引用法：> alter table tablename drop index 索引名;
樣本：> alter table user drop index_name;
5. 查看錶的索引資訊
- > show index from tablename;
6. sql效能分析
- sql使用索引情況，掃描的行數
> explain select * from user;
7. 資料庫正在執行的sql
> show processlist;
8. 隨機數
mysql機函數rand(),會產生大於等於0,小於1(0到1)之間的小數，floor函數都可以把小數變為整數整數
- 0到99的隨機數
> select floor(rand()*100);
- 1到99的隨機數
> select floor(1+rand()*99);
9. 建立使用者
建立使用者前需確保沒有使用者名稱為""的使用者，如果有需要先刪除
> select user,password from mysql.user;
> delete from mysql.user where user = ‘‘;
> create User 使用者命 IDENTIFIED BY ‘密碼‘;
> GRANT ALL ON 表名.* TO 使用者命;
> flush privileges; (授權後還要重新整理系統許可權表)
給使用者賦予許可權
用法：> GRANT 操作 ON 庫名.表名 TO 使用者名稱; （庫名表名可以用*匹配）
樣本：> GRANT SELECT, INSERT,UPDATE ON temp.user TO newuser;
10. 清空表中資料
用法：# mysqldump -u使用者名稱 -p密碼 --add-drop-table --no-data 資料庫 | mysql -u使用者名稱 -p密碼 資料庫
樣本：# mysqldump -uroot -p123 --add-drop-table --no-data temp | mysql -uroot -p123 temp
樣本：# mysqldump -uroot -p123 --add-drop-table --no-data temp | grep user | mysql -uroot -p123 temp
原理說明：根據mysqldump的備份參數，包括drop table和建表create table語句，其實是先把database裡的所有表drop後，再重建表結構。
11. 大量刪除指定表
用法：# mysqldump -u使用者名稱 -p密碼 --add-drop-table --no-data 資料庫 | grep \^DROP | grep 條件 | mysql -u使用者名稱 -p密碼 資料庫
樣本：# mysqldump -uroot -p123 --add-drop-table --no-data temp | grep \^DROP | grep user | mysql -uroot -p123 temp
12. 啟動mysql
- # service mysqld start
- # /etc/inint.d/mysqld start
13. 關閉mysql
- # service mysqld start
- # /etc/inint.d/mysqld start
14. 重啟mysql
- # service mysqld restart
- # /etc/inint.d/mysqld restart
15. 設定mysql終端編碼
- 在終端中使用msyql中文經常會亂碼,這個時候可以set names utf8;
16. 禁止mysql緩衝結果
- select SQL_NO_CACHE count(*) from tablename; 需要注意這個是禁止查詢的結果放入緩衝中,而不是禁止從緩衝中查詢
17. 查詢n次的耗時
- select benchmark(n, (select COUNT(*) FROM tablename)); 測試表明測試的語句不會被緩衝
benchmark會重複計算運算式n次,benchmark函數只能測量數字運算式的效能，雖然說運算式可以是一個子查詢，但子查詢返回的只能是單個值
18. 變數
- 測試表明使用變數,不會做任何緩衝,對比使用SQL_NO_CACHE,SQL_NO_CACHE會緩衝一些sql分析,執行計畫
> set @id := 0;
> select COUNT(*) FROM tablename where id > @id;
19. 查詢行數比較多的表
> select table_name,table_rows from information_schema.tables order by table_rows desc limit 10;
20. 不產生binlog的刪除資料方式
> truncate tablename;
21. 秒數和日期轉換
> FROM_UNIXTIME(1466780897)
> UNIX_TIMESTAMP(‘2016-05-25 00:00:00‘)
22. 查出的列拼成一行
> select group_concat(id) from tablename;
MySQL常用命令
