常用命令
打開CMD命令窗口（記住使用管理員身份運(yùn)行）良哲，我們就可以在命令窗口中做一些MySQL的命令操作了：
image
服務(wù)啟動(dòng)和關(guān)閉
這個(gè)我們上一個(gè)章節(jié)使用過了：net start mysql捐凭，這是服務(wù)命令涨薪，不是語句命令，后面不需要加引號(hào)
net start mysq --代表啟動(dòng)MySQL服務(wù)
image
MySQL登錄命令
這個(gè)我們前面一章我們也了解過潜支，使用過了：mysql -h 主機(jī)名 -P 端口 -u 用戶名 -p蒸矛，前面一章有對(duì)-h，-u闽颇，-p 作過解釋盾戴，這邊 -P 是指Port 端口。
mysql -h localhost -P 3306 -u root -p
image
默認(rèn)情況下登陸本機(jī)兵多，其實(shí)端口和主機(jī)都可以省略的：
mysql -u 用戶名 -p
查看數(shù)據(jù)庫版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.30 |
+-----------+
1 row in set (0.00 sec)
顯示所有數(shù)據(jù)庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
使用某個(gè)庫：use關(guān)鍵字
mysql> use mysql;
Database changed
顯示所選庫中的所有表信息
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
37 31 rows in set (0.00 sec)
從其他數(shù)據(jù)庫中查詢表
show tables from databasename
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
輸出表的創(chuàng)建語句腳本
用于獲取腳本進(jìn)行表結(jié)構(gòu)遷移之類的
mysql> show create table columns_priv;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| columns_priv | CREATE TABLE `columns_priv` ( 6 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges' |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看表結(jié)構(gòu)
以表格的信息輸出表的結(jié)構(gòu)尖啡，一目了然
mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
查看當(dāng)前所在庫
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
查看所有庫引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
創(chuàng)建用戶
create user user[@host] [identified by 'password'];
方括號(hào)中的內(nèi)容是可以省略的，比如設(shè)置默認(rèn)主機(jī)%剩膘，這樣允許用戶從其他主機(jī)登錄衅斩，比如不設(shè)置密碼，這樣默認(rèn)無需密碼登錄怠褐。
我們創(chuàng)建一個(gè)用戶并登錄試試畏梆，紅色字體是關(guān)鍵語句：
mysql> create user user1@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)
D:\Setup\mysql-5.7.30-winx64\bin>mysql -u user1 -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.30 MySQL Community Server (GPL) 9
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 11
Oracle is a registered trademark of Oracle Corporation and/or its 13 affiliates. Other names may be trademarks of their respective 14 owners. 15
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
修改用戶密碼：4種方式
登錄MySQL并修改
格式如下： set password for 用戶名@主機(jī) = password('新密碼');
mysql> set password for root@localhost = password('Helenlyn');
使用 mysqladmin 操作
格式如下：mysqladmin -u 用戶名 -p 舊密碼 password 新密碼
mysqladmin -u root -p Helenlyn password Helenlyn1
使用腳本語句修改 user 表
打開mysql數(shù)據(jù)庫，直接更新user表的authentication_string字段奈懒。這邊localhost可以用%代替奠涌。
需要注意：flush privileges是刷新權(quán)限，必須執(zhí)行磷杏，才能對(duì)用戶生效溜畅；
我們用的是5.7.30版本，所以密碼字段是authentication_string茴丰，5.7之前的版本一般來說是password达皿。
mysql> use mysql;
mysql> update user set authentication_string=password('1234567') where user='user1' and host='localhost'; 3 mysql> flush privileges;
直接設(shè)置密碼
創(chuàng)建用戶的時(shí)候同時(shí)設(shè)置密碼，或者登錄的用戶修改自己的密碼贿肩，這個(gè)在前面部署服務(wù)和創(chuàng)建用戶的時(shí)候都有說明過了：
create user user[@host] [identified by 'pwd'];
或
set password = password('pwd');
刪除用戶：2種方式
直接drop
格式如下：drop user 'username'[@'host']
mysql> drop user user1@localhost;
Query OK, 0 rows affected (0.00 sec)
刪除完峦椰，用戶重啟就生效了，可以創(chuàng)建個(gè)用戶試試汰规。
刪除user表的用戶
同樣的汤功，執(zhí)行完需要執(zhí)行 flush privileges刷新權(quán)限才會(huì)對(duì)用戶生效，格式如下：
delete from user where user='用戶名' and host='主機(jī)';
flush privileges;
mysql> use mysql;
Database changed
mysql> delete from user where user='user1' and host='localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
給用戶授權(quán)
創(chuàng)建用戶之后溜哮，需要給不同的用戶分別授權(quán)滔金，這樣才能根據(jù)不同用戶的角色來界定職責(zé)和管理范疇。
grant privilegesCate on database.table to 'uname'[@'host'] [with grant option]
說明：
1茂嗓、grant 是授權(quán)的關(guān)鍵字餐茵。
2、privilegesCate 代表勸降類型述吸，包含：all privileges：所有權(quán)限忿族；select：讀權(quán)限；delete：刪除權(quán)限；update：更新權(quán)限道批；create：創(chuàng)建權(quán)限错英；drop：刪除數(shù)據(jù)庫、數(shù)據(jù)表權(quán)限隆豹。
3椭岩、on 用來表實(shí)授權(quán)的范圍具體到那些庫和表，看示例中璃赡，格式為數(shù)據(jù)庫.表名 判哥，點(diǎn)號(hào)前面指的是數(shù)據(jù)庫名，后面指的是表名鉴吹，如果想要授權(quán)所有庫表姨伟，可以用 . 表示。
4豆励、to 表示權(quán)限授予的具體用戶, 格式：uname@host夺荒，uname即用戶名，host指的是主機(jī)良蒸，可以是IP技扼、域名等，如果不做host限制嫩痰，我們之前的文章也說過了剿吻，使用%表示。
5串纺、with grant option 這個(gè)選項(xiàng)表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人丽旅。如果不加這一項(xiàng)，用戶只有使用權(quán)限的權(quán)力纺棺，而沒有使用grant命令創(chuàng)建并給其它用戶授權(quán)的榄笙。
6、權(quán)限取并集祷蝌，如果對(duì)用戶授權(quán)了 select+ update茅撞，后面又對(duì)用戶授予了 select + delete, 那么用戶同時(shí)擁有 select + update + delete的權(quán)限。
mysql> create user brand identified by '123456';
Query OK, 0 rows affected
mysql> grant all on *.* to brand@'%';
Query OK, 0 rows affected
說明：這邊是創(chuàng)建了一個(gè)用戶并把所有的權(quán)限都授予他巨朦。如果要做限制米丘，可以把 . 改成具體的數(shù)據(jù)庫或者表，也可以吧%改成具體的host糊啡。如果要開放授權(quán)能力拄查，可以在末尾加上 with grant option ，這樣就基本等同于dba的權(quán)限了棚蓄。
查看用戶的權(quán)限
show grants for 'uname'[@'host']
這邊需注意靶累，主機(jī)可以省略腺毫，默認(rèn)值為%癣疟，測試下：
mysql> show grants for brand;
+---------------------------------------------------------------------------------------------------------------+
| Grants for brand@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set
當(dāng)前登錄者的權(quán)限挣柬，如：
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set
取消用戶的權(quán)限
revoke privilegesCate on database.table FROM 'uname'[@'host'];
可以先查show grants，再撤銷revoke睛挚，再查show grants邪蛔，來理解整個(gè)過程，示例：
mysql> show grants for brand;
+---------------------------------------------------------------------------------------------------------------+
| Grants for brand@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> revoke delete on *.* from brand;
Query OK, 0 rows affected
mysql> show grants for brand; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for brand@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
上面我們先查詢用戶的權(quán)限扎狱，是all的權(quán)限侧到，然后撤銷他的delete的權(quán)限，再查詢淤击，發(fā)現(xiàn)它確實(shí)少了delete的選項(xiàng)匠抗，這個(gè)符合我們的預(yù)期，說明授權(quán)操作沒有問題污抬。
關(guān)于授權(quán)的一些注意點(diǎn)
1汞贸、權(quán)限最小化原則，有需要再去開通印机，如果只有查詢矢腻，就只開通select權(quán)限即可
2、讀寫用戶分離射赛，讀用戶只需給select權(quán)限多柑，不要賦予update、insert楣责、delete甚至drop之類的權(quán)限
3竣灌、盡量設(shè)置復(fù)雜密碼或者讓使用者重置密碼
4、沒有特定情況秆麸，一般不需要授予 WITH GRANT OPTION
5初嘹、定期清理垃圾用戶，回收權(quán)限或者刪除用戶
總結(jié)
還有很多常用的命令操作蛔屹，后續(xù)再融入到他章節(jié) 一 一 解讀削樊。命令是用戶進(jìn)入正式數(shù)據(jù)庫編程之前應(yīng)知應(yīng)會(huì)的部分，所以需要熟練掌握兔毒。
使用過程中需要注意以下細(xì)節(jié)：
命令的方式操作用戶和權(quán)限不需要刷新漫贞，下一次登錄就會(huì)自動(dòng)生效，在mysql庫表進(jìn)行修改的育叁，需要調(diào)用flush privileges; 刷新一下迅脐，才會(huì)在下次登錄生效。
文中所說的host部分可以省略豪嗽，默認(rèn)值為%谴蔑，表示所有機(jī)器豌骏，這個(gè)文中反復(fù)說過了。
mysql中用戶名隐锭、密碼和權(quán)限的信息存儲(chǔ)在庫名為mysql的user表中窃躲，可以打開看看。
