MySQL用戶管理及SQL語句詳解_ZenDei技術網路在線

1.1 MySQL用戶管理 1.1.1 用戶的定義用戶名+主機域 1.1.2 用戶的作用 1、用戶登錄 2、用於管理資料庫及數據 1.1.3 連接資料庫定義用戶：用戶名+主機域，密碼定義許可權：對不同的對象進行許可權(角色)定義命令: grant 許可權 on 許可權範圍 to 用戶 identif ...

1.1 MySQL用戶管理

1.1.1 用戶的定義

　　用戶名+主機域

mysql> select user,host,password from mysql.user;
+--------+------------+-------------------------------------------+
| user   | host       | password                                  |
+--------+------------+-------------------------------------------+
| root   | localhost  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root   | 127.0.0.1  |                                           |
| znix   | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| clsn   | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root   | 10.0.0.1   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+--------+------------+-------------------------------------------+
6 rows in set

1.1.2 用戶的作用

　　　　1、用戶登錄

　　　　2、用於管理資料庫及數據

1.1.3 連接資料庫

　　定義用戶：用戶名+主機域，密碼

　　定義許可權：對不同的對象進行許可權(角色)定義

命令:

  grant 許可權 on 許可權範圍  to 用戶  identified  by '密碼'

許可權

        對資料庫的讀、寫等操作
    （insert update、select、delete、drop、create等）

角色

    資料庫定義好的一組許可權的定義
    （all privileges、replication slave等）

許可權範圍

    全庫級別： *.*
    單庫級別：clsn.*
    單表級別：clsn.t1

用戶

    'clsn'@'localhost'  本地
    'clsn'@'192.168.66.149'
    'clsn'@'192.168.66.%'
    'clsn'@'192.168.66.14%'

1.1.4 【練習題】按照要求創建用戶

用戶只能通過10.0.0.0/24網段訪問，用戶名為clsn 密碼為123

這個用戶只能對clsn資料庫下的對象進行增insert create、改update 、查select；

創建命令：

    grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';

查看用戶許可權

mysql>  show grants for clsn@'172.16.1.%'\G
*************************** 1. row ***************************
Grants for clsn@172.16.1.%: GRANT USAGE ON *.* TO 'clsn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)

查看當前存在的用戶:

select user,host from mysql.user;

創建用戶語法

CREATE USER '用戶'@'主機' IDENTIFIED BY '密碼';

示例：

create user 'clsn'@'localhost' identified by 'clsn123';

註意這個樣創建的用戶只有連接許可權

企業里創建用戶一般是授權一個內網網段登錄，最常見的網段寫法有兩種。

方法1：172.16.1.%（%為通配符，匹配所有內容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是個小遺憾。

標準的建用戶方法：

create user 'web'@'172.16.1.%' identified by 'web123';

查看用戶對應的許可權

show grants for oldboy@localhost\G

1.1.5 用戶刪除

刪除用戶語法：

drop user 'user'@'主機域'

【練習】用戶優化：只保留

| root | 127.0.0.1 |
| root | localhost |

特殊的刪除方法:(慎用，儘量不要直接去修改表)

mysql> delete from mysql.user where  user='clsn' and host='localhost'; 
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;

1.1.6 用戶授權

給用戶授權

# 創建用戶
create user 'clsn'@'localhost' identified by 'clsn123';
# 查看用戶
select user,host from mysql.user;
# 授權所有許可權給clsn用戶
GRANT ALL ON *.* TO 'clsn'@'localhost';
# 查看clsn用戶的許可權
SHOW GRANTS FOR 'clsn'@'localhost'\G

創建用戶的同時授權

grant all on *.* to clsn@'172.16.1.%' identified by 'clsn123';
# 刷新許可權
flush privileges; #<==可以不用。

創建用戶然後授權

create user 'clsn'@'localhost' identified by 'clsn123';
GRANT ALL ON *.* TO 'clsn'@'localhost';

授權和root一樣的許可權

grant all on *.* to system@'localhost' identified by 'clsn123' with grant option;

授權給用戶select,create,insert,update 許可權

grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';

回收許可權

REVOKE INSERT ON *.* FROM clsn@localhost;

可以授權的用戶許可權

INSERT,SELECT, UPDATE, DELETE, 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

【示例】博客授權收回示例

grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';
revoke create,drop on blog.* from 'blog'@'172.16.1.%';

授權博客類的最多許可權：select,insert,update,delete

1.2 MySQL 客戶端工具

1.2.1 MysQL客戶端命令介紹

　　mysql命令客戶端

　　　　用於資料庫連接管理

　　　　將用戶SQL 語句發送到伺服器

　　mysqladmin命令：命令行管理工具

　　mysqldump命令：備份資料庫和表的內容

1.2.2 mysql命令說明

　　用於連接資料庫

　　用於管理資料庫通過下列方式進行管理

命令介面自帶命令

　　DDL：數據定義語言

　　DCL：數據控制語言

　　DML：數據操作語言

　　mysql命令介面自帶命令說明

命令	命令說明
\h 或 help 或 ?	獲取幫助
\G	格式化輸出（行轉列）
\T 或 tee	記錄操作日誌 tee /tmp/mysql.log
\c 或 CTRL+c	退出mysql
\s 或 status	查看資料庫狀態信息
\. 或 source	mysql> source /tmp/world.sql
\!	使用shell中的命令 mysql> \! cat /etc/redhat-release CentOS release 6.9 (Final)
\u 或use	use world show databases 看當前所有資料庫的名字 show tables 查看當前use到的資料庫所有的表 show tables from world 查看目標資料庫下的表
快捷鍵	上下翻頁、TAB鍵、ctrl +C 、ctrl +L

1.2.2.1 mysql中help命令的使用

　　在mysql命令行中輸入mysql或 ?都可以查看幫助

mysql> help

　　使用 help contents查看完整的sql類別列表幫助

mysql> help contents

　　有關特定 SQL 類別或語句的幫助

mysql> help Account Management

　　查看 grant 的幫助

mysql> help GRANT

　　有關與狀態相關的 SQL 語句的幫助

mysql> help status

1.2.2.2 source命令的使用

在 mysql 中處理輸入文件：

如果這些文件包含 SQL 語句，則稱為“腳本文件”或“批處理文件”。

使用 SOURCE 命令：

mysql> SOURCE /data/mysql/world.sql

　　或者使用非互動式：(儘量避免使用mysql 導入數據，會產生大量的無用日誌)

mysql</data/mysql/world.sql

1.2.3 mysqladmin命令說明

基本語法

mysqladmin -u<name> -p<password> commands

　　命令說明圖表

功能選項	說明
mysqladmin -u用戶 -p密碼 ping	“強制回應 (Ping)”伺服器。
mysqladmin -u用戶 -p密碼 shutdown	關閉伺服器。
mysqladmin -u用戶 -p密碼 create databasename	創建資料庫。
mysqladmin -u用戶 -p密碼drop databasename	刪除資料庫
mysqladmin -u用戶 -p密碼 version	顯示伺服器和版本信息
mysqladmin -u用戶 -p密碼 status	顯示或重置伺服器狀態變數
mysqladmin -u用戶 -p密碼 password	設置口令
mysqladmin -u用戶 -p密碼 flush-privileges	重新刷新授權表。
mysqladmin -u用戶 -p密碼 flush-logs	刷新日誌文件和高速緩存。
以上信息通過mysqladmin --help 獲得

1.2.4 mysqldump簡單說明

　　mysqldump是一款資料庫備份工具。

命令幫助及基本語法：

[root@db02 ~]# mysqldump --help
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

　　情參照mysqldump --help

1.3 SQL語句入門

1.3.1 DDL語句(數據定義語言)

定義範圍：

庫：名字、特性

表：表名字、列

1.3.1.1 資料庫查看

查看資料庫--查看全部

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clsn               |
+--------------------+

查看資料庫--模糊匹配

mysql> show databases like "%s%";
+--------------------+
| Database (%s%)     |
+--------------------+
| information_schema |
| clsn               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

查看你相關的幫助

mysql> ? show databases;
Name: 'SHOW DATABASES'
Description:
Syntax:
SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

1.3.1.2 資料庫操作

　　創建一個資料庫

mysql> create database haha;

通過show 命令能夠查看創建的資料庫的格式

mysql> show create database haha;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| haha     | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

創建資料庫時定義字元編碼

mysql> create database clsn charset utf8 ;
mysql> show create database clsn;     查詢資料庫定義信息。

存在的資料庫修改字元編碼：

mysql> alter database clsn charset gbk;

修改資料庫編碼格式示例

mysql> alter database haha charset gbk
    -&