mysql常用命令集錦

標籤：

一、DCL語句(資料控制語句)

1、授權遠端存取，針對IP和使用者、DB的

grant {privilege list} on {dbname}.* to ‘{user}‘@‘{ip}‘ identified by ‘{pwd}‘ with grant option

ex: grant all privilege on *.* to ‘root‘@‘%‘ identified by ‘***‘ with grant option; -- %代表所有IP

grant all privileges on *.* to ‘exam‘@‘192.168.0.%‘ identified by ‘***‘ with grant option; -- 指定IP段被授權所有許可權，用指定賬戶/密碼

注意事項： mysql.user表中，如果沒有localhost/127.0.0.1這樣的記錄(預設會產生的)，則通過ssh的串連，也會失敗，如果是自己機器上的庫，因為預設是localhost的

二、DDL語句(資料定義語句)

1 、create user {usrname} identified by ‘{pwd}‘;

2、create database {dbname} character set ‘utf8‘ collate ‘utf8_general_ci‘;

三、DML語句(資料動作陳述式)

show databases //列出 mysql server 資料庫

show tables [from db_name] //列出資料庫資料表

show table status [from db_name] //列出資料表及表狀態資訊

desc tb_name //列出資料表資訊

show full columns/fields from tbl_name //列出表欄位完整屬性

show index from tbl_name [from db_name] //列出表索引

show status //列出 db server 狀態

show variables //列出 mysql 系統內容變數

show processlist //列出DB的執行進程

show grants for user //列出某使用者權限

show index from db.tbname // 查看錶的索引

show (full) processlist // 查看會話進程，有full顯示全部，無full顯示前100

kill id // 殺死某個會話， id可通過show processlist看到

兩張表間的批次更新(不同於sqlserver、oracle等，因為mysql的update中不允許有from語句)

update moa_ios_devicetoken m1, moa_ios_devicetoken m2 set m1.device_token=m2.device_token where m1.user_id=m2.user_id and m1.line_id in (1,2,4) and m2.line_id in (5,6,8);

Mysql匯出表結構及表資料 mysqldump用法：(mysqldump用法前提，必須有DB，且use DB)

命令列下具體用法如下： mysqldump -u用戶名 -p密碼 -d/-t 數據庫名 表名 指令碼名; (-d表示結構，-t表示資料， 都沒有表示結構和資料)

1、匯出數據庫為dbname的表結構(其中用戶名為root,密碼為dbpasswd,產生的指令碼名為db.sql)

mysqldump -uroot -pdbpasswd -d dbname >db.sql;

2、匯出數據庫為dbname某張表(test)結構

mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

3、到處資料庫的所有對象，不包括資料

mysqldump -uroot -pdbpasswd -R --no-data dbname >db.sql;

4、完整匯出資料庫(包括表對象、PROC、FUNC、TRIGGER、資料)

mysqldump -uroot -pdbpasswd -R dbname >db_all.sql;

5、完整匯入資料庫(包括表對象、PROC、FUNC、TRIGGER、資料)

source db_all.sql; -- 有人說用 mysqldump -uroot -pdbpasswd dbname

修改表欄位的語句：

1、增加欄位： alter table tbname add columnName 欄位類型 [是否允許非空]

2、改欄位名：alter table tbname change oldColumnName newColumnName 欄位類型 [是否允許非空]

3、改欄位類型：alter table tbname modify columnName 欄位類型 [是否允許非空];

4、刪除欄位：alter table tbname drop column columnName;

5、修改欄位注釋：alter table tbname modify column columnName 欄位類型 default defvalue comment ‘欄位注釋‘;

mysql啟動停止： service mysql start mysqladmin -uroot -ppwd shutdown

mysql串連授權：grant all privileges on im.* to [email protected]‘%‘ identified by ‘123456‘ -- im是我自己的庫，%代表任何串連

常用操作集錦：

1、重複資料刪除記錄的方法：

a 擷取重複記錄，插入到暫存資料表中。

create table user_info_a select recordid,name,userid,min(recordid) as mid from user_info group by userid having count(1) > 1;

b 級聯表刪除記錄

delete user_info from user_info,user_info_a where user_info.userid=user_info_a.userid and user_info.recordid>user_info_a.mid;

c drop table user_info_a

2、匯入excel到mysql中的簡易方法(這裡拿人員舉例)

a 待匯入資料

b 按表結構構造後資料

E列就是構造出來的SQL， 然後可直接copy到txt中，並儲存成.sql檔案，通過source 匯入到庫中。

E列寫法： ="insert into user_info(recordid,companyid,department,app_sys_ids,userjid,userid,name,creator,createdate) values(default,‘ZTE‘,114,‘1,2‘,"&"‘"&TRIM(A1052)&"@moaservice.zte.com.cn‘,"&C1052&","&D1052&",‘admin‘,now());"

產生的SQL，以第一行為例： insert into user_info(recordid,companyid,department,app_sys_ids,userjid,userid,name,creator,createdate) values(default,‘ZTE‘,114,‘1,2‘,‘[email protected]‘,‘10009240‘,‘餘昆‘,‘admin‘,now());

四、備註陳述式

mysql下支援3種注釋，#,/**/，-- , 最後一個-- 必須後加一個空格，否則無效，這個不同於oracle，使用時應注意。

五、丟失root密碼時，如何重設密碼?

mysqld_safe --skip-grant-tables&

mysql -u root mysql

mysql> UPDATE user SET password=PASSWORD("new password") WHERE user=‘root‘; mysql> FLUSH PRIVILEGES;

六、windows下mysql的zip包如何安裝?

     解壓mysql壓縮包後，cmd到其解壓後目錄的bin目錄下， 運行如下命令： mysqld -install， 即可將mysql安裝成windows服務。 之後可將此服務設定成自啟動， 或手工啟動; 命令列啟動/停止： net start mysql / net stop mysql

mysql常用命令集錦