MySQL用戶管理

1.創(chuàng)建一個(gè)普通用戶并授權(quán)

[root@gary-tao ~]# mysql -uroot -p'szyino-123'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'szyino-123';  //創(chuàng)建一個(gè)普通用戶并授權(quán)
Query OK, 0 rows affected (0.00 sec)

用法解釋說明：

• grant：授權(quán)；
• all:表示所有的權(quán)限（如讀、寫、查詢、刪除等操作）；
• .：前者表示所有的數(shù)據(jù)庫,后者表示所有的表；
• identified by：后面跟密碼，用單引號括起來；
• 'user1'@'127.0.0.1':指定IP才允許這個(gè)用戶登錄，這個(gè)IP可以使用%代替，表示允許所有主機(jī)使用這個(gè)用戶登錄;

2.測試登錄

[root@gary-tao ~]# mysql -uuser1 -pszyino-123 //由于指定IP，報(bào)錯(cuò)不能登錄
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
[root@gary-tao ~]# mysql -uuser1 -pszyino-123 -h227.0.0.1 //加-h指定IP登錄，正常
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> grant all on *.* to 'user1'@'localhost' identified by 'szyino-123';  //授權(quán)l(xiāng)ocalhost，所以該用戶默認(rèn)使用（監(jiān)聽）本地mysql.socket文件，不需要指定IP即可登錄
Query OK, 0 rows affected (0.00 sec)

mysql> ^DBye
[root@gary-tao ~]# mysql -uuser1 -pszyino-123  //正常登錄
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

3.查看所有授權(quán)

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*B1E761CAD4A61F6FD6B02848B5973BC05DE1C315' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.指定用戶查看授權(quán)

mysql> show grants for user1@'127.0.0.1';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*B1E761CAD4A61F6FD6B02848B5973BC05DE1C315' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意：假設(shè)你想給同個(gè)用戶授權(quán)增加一臺(tái)電腦IP授權(quán)訪問，你就可以直接拷貝查詢用戶授權(quán)文件，復(fù)制先執(zhí)行一條命令再執(zhí)行第二條，執(zhí)行的時(shí)候把IP更改掉,這樣就可以使用同個(gè)用戶密碼在另外一臺(tái)電腦上登錄。

常用sql語句

1.最常見的查詢語句

第一種形式:

創(chuàng)新互聯(lián)，是成都地區(qū)的互聯(lián)網(wǎng)解決方案提供商，用心服務(wù)為企業(yè)提供網(wǎng)站建設(shè)、成都app軟件開發(fā)公司、小程序制作、系統(tǒng)按需求定制開發(fā)和微信代運(yùn)營服務(wù)。經(jīng)過數(shù)十載的沉淀與積累，沉淀的是技術(shù)和服務(wù)，讓客戶少走彎路，踏實(shí)做事，誠實(shí)做人，用情服務(wù)，致力做一個(gè)負(fù)責(zé)任、受尊敬的企業(yè)。對客戶負(fù)責(zé)，就是對自己負(fù)責(zé)，對企業(yè)負(fù)責(zé)。

mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from mysql.user; 
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

//注釋：mysql.user表示mysql的user表，count(*)表示表中共有多少行。

第二種形式:

mysql> select * from mysql.db;

//它表示查詢mysql庫的db表中的所有數(shù)據(jù)

mysql> select db from mysql.db;
+---------+
| db      |
+---------+
| test    |
| test\_% |
+---------+
2 rows in set (0.00 sec)

//查詢db表里的db單個(gè)字段

mysql> select db,user from mysql.db;
+---------+------+
| db      | user |
+---------+------+
| test    |      |
| test\_% |      |
+---------+------+
2 rows in set (0.00 sec)

//查看db表里的db，user多個(gè)字段

mysql> select * from mysql.db where host like '192.168.%'\G;

//查詢db表里關(guān)于192.168.段的ip信息

2.插入一行

mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from db1.t1;  
Empty set (0.00 sec)

mysql> insert into db1.t1 values (1, 'abc');  //插入一行數(shù)據(jù)
Query OK, 1 row affected (0.01 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)
mysql> insert into db1.t1 values (1, '234');
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
|    1 | 234  |
+------+------+
2 rows in set (0.00 sec)

3.更改表的一行。

mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    1 | aaa  |
+------+------+
2 rows in set (0.00 sec)

4.清空某個(gè)表的數(shù)據(jù)

mysql> truncate table db1.t1;  //清空表
Query OK, 0 rows affected (0.03 sec)

mysql> select * from db1.t1;
Empty set (0.00 sec)
mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.刪除表

mysql> drop table db1.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

6.刪除數(shù)據(jù)庫

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

mysql數(shù)據(jù)庫備份恢復(fù)

1.備份恢復(fù)庫

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql > /tmp/mysql.sql  //備份庫
Warning: Using a password on the command line interface can be insecure.
[root@gary-tao ~]# mysql -uroot -pszyino-123 -e "create database mysql2"  //創(chuàng)建一個(gè)新的庫
Warning: Using a password on the command line interface can be insecure.
[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/mysql.sql  //恢復(fù)一個(gè)庫
Warning: Using a password on the command line interface can be insecure.
[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select database();
+------------+
| database() |
+------------+
| mysql2     |
+------------+
1 row in set (0.00 sec)

2.備份恢復(fù)表

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql user > /tmp/user.sql  //備份表
Warning: Using a password on the command line interface can be insecure.
[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/user.sql  //恢復(fù)表
Warning: Using a password on the command line interface can be insecure.

3.備份所有庫

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -A > /tmp/mysql_all.sql
Warning: Using a password on the command line interface can be insecure.
[root@gary-tao ~]# less /tmp/mysql_all.sql

4.只備份表結(jié)構(gòu)

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -d mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.