MySQL數(shù)據(jù)庫(kù)管理（一）

meteor199 發(fā)布于2019-06-28 15:54 / 2511人閱讀

摘要：在經(jīng)濟(jì)管理的日常工作中，常常需要把某些相關(guān)的數(shù)據(jù)放進(jìn)這樣的倉(cāng)庫(kù)，并根據(jù)管理的需要進(jìn)行相應(yīng)的處理。數(shù)據(jù)分類結(jié)構(gòu)化數(shù)據(jù)能夠用數(shù)據(jù)或統(tǒng)一的結(jié)構(gòu)加以表示，我們稱之為結(jié)構(gòu)化數(shù)據(jù)，如數(shù)字符號(hào)。傳統(tǒng)的關(guān)系數(shù)據(jù)模型行數(shù)據(jù)，存儲(chǔ)于數(shù)據(jù)庫(kù)，可用二維表結(jié)構(gòu)表示。

博文參考

http://www.178linux.com/79462
http://iceyao.blog.51cto.com/9426658/1571865
http://blog.csdn.net/taochangchang/article/details/16919289

什么是數(shù)據(jù)庫(kù)

 數(shù)據(jù)庫(kù)(Database)是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲(chǔ)和管理數(shù)據(jù)的建立在計(jì)算機(jī)存儲(chǔ)設(shè)備上的倉(cāng)庫(kù)。

簡(jiǎn)單來說是本身可視為電子化的文件柜——存儲(chǔ)電子文件的處所，用戶可以對(duì)文件中的數(shù)據(jù)進(jìn)行新增、截取、更新、刪除等操作。

在經(jīng)濟(jì)管理的日常工作中，常常需要把某些相關(guān)的數(shù)據(jù)放進(jìn)這樣的“倉(cāng)庫(kù)”，并根據(jù)管理的需要進(jìn)行相應(yīng)的處理。

例如， 企業(yè)或事業(yè)單位的人事部門常常要把本單位職工的基本情況(職工號(hào)、姓名、年齡、性別、籍貫、工資、簡(jiǎn)歷等)存放在表中，這張表就可以看成是一個(gè)數(shù)據(jù)庫(kù)。有了這個(gè)“數(shù)據(jù)倉(cāng)庫(kù)“我們就可以根據(jù)需要隨時(shí)查詢某職工的基本情況，也可以查詢工資在某個(gè)范圍內(nèi)的職工人數(shù)等等。這些工作如果都能在計(jì)算機(jī)上自動(dòng)進(jìn)行，那我們的人事管理就可以達(dá)到極高的水平。此外，在財(cái)務(wù)管理、倉(cāng)庫(kù)管理、生產(chǎn)管理中也需要建立眾多的這種“數(shù)據(jù)庫(kù)“，使其可以利用計(jì)算機(jī)實(shí)現(xiàn)財(cái)務(wù)、倉(cāng)庫(kù)、生產(chǎn)的自動(dòng)化管理。

嚴(yán)格來說，數(shù)據(jù)庫(kù)是長(zhǎng)期儲(chǔ)存在計(jì)算機(jī)內(nèi)、有組織的、可共享的數(shù)據(jù)集合。數(shù)據(jù)庫(kù)中的數(shù)據(jù)指的是以一定的數(shù)據(jù)模型組織、描述和儲(chǔ)存在一起、具有盡可能小的冗余度、較高的數(shù)據(jù)獨(dú)立性和易擴(kuò)展性的特點(diǎn)并可在一定范圍內(nèi)為多個(gè)用戶共享。

這種數(shù)據(jù)集合具有如下特點(diǎn)：盡可能不重復(fù)，以最優(yōu)方式為某個(gè)特定組織的多種應(yīng)用服務(wù)，其數(shù)據(jù)結(jié)構(gòu)獨(dú)立于使用它的應(yīng)用程序，對(duì)數(shù)據(jù)的增、刪、改、查由統(tǒng)一軟件進(jìn)行管理和控制。從發(fā)展的歷史看，數(shù)據(jù)庫(kù)是數(shù)據(jù)管理的高級(jí)階段，它是由文件管理系統(tǒng)發(fā)展起來的。

數(shù)據(jù)管理模型

層次模型：用樹狀<層次>結(jié)構(gòu)來表示實(shí)體類型和實(shí)體間聯(lián)系的數(shù)據(jù)模型。

網(wǎng)狀模型：用有向圖表示實(shí)體和實(shí)體之間的聯(lián)系的數(shù)據(jù)結(jié)構(gòu)模型稱為網(wǎng)狀數(shù)據(jù)模型。

關(guān)系模型：使用表格表示實(shí)體和實(shí)體之間關(guān)系的數(shù)據(jù)模型稱之為關(guān)系數(shù)據(jù)模型。

數(shù)據(jù)分類 結(jié)構(gòu)化數(shù)據(jù)

能夠用數(shù)據(jù)或統(tǒng)一的結(jié)構(gòu)加以表示，我們稱之為結(jié)構(gòu)化數(shù)據(jù)，如數(shù)字、符號(hào)。傳統(tǒng)的關(guān)系數(shù)據(jù)模型、行數(shù)據(jù)，存儲(chǔ)于數(shù)據(jù)庫(kù)，可用二維表結(jié)構(gòu)表示。

半結(jié)構(gòu)化數(shù)據(jù)

介于完全結(jié)構(gòu)化數(shù)據(jù)（如關(guān)系型數(shù)據(jù)庫(kù)、面向?qū)ο髷?shù)據(jù)庫(kù)中的數(shù)據(jù)）和完全無結(jié)構(gòu)的數(shù)據(jù)（如聲音、圖像文件等）之間的數(shù)據(jù)，XML、HTML文檔就屬于半結(jié)構(gòu)化數(shù)據(jù)。它一般是自描述的，數(shù)據(jù)的結(jié)構(gòu)和內(nèi)容混在一起，沒有明顯的區(qū)分。

非結(jié)構(gòu)化數(shù)據(jù)

指其字段長(zhǎng)度可變，并且每個(gè)字段的記錄又可以由可重復(fù)或不可重復(fù)的子字段構(gòu)成的數(shù)據(jù)庫(kù)，用它不僅可以處理結(jié)構(gòu)化數(shù)據(jù)（如數(shù)字、符號(hào)等信息）而且更適合處理非結(jié)構(gòu)化數(shù)據(jù)（全文文本、圖象、聲音、影視、超媒體等信息）。非結(jié)構(gòu)化數(shù)據(jù),包括所有格式的辦公文檔、文本、圖片、XML、HTML、各類報(bào)表、圖像和音頻/視頻信息等等

關(guān)系模型：

數(shù)據(jù)庫(kù)：一個(gè)方案、一個(gè)項(xiàng)目

二維關(guān)系：表：row, column 索引：index 視圖：view

結(jié)構(gòu)化查詢語言包含6個(gè)部分

結(jié)構(gòu)化查詢語言；簡(jiǎn)稱SQL，是一種特殊目的的編程語言，是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)；同時(shí)也是數(shù)據(jù)庫(kù)腳本文件的擴(kuò)展名。

數(shù)據(jù)查詢語言（DQL:Data Query Language）

 其語句，也稱為“數(shù)據(jù)檢索 語句”，用以從表中獲得數(shù)據(jù)，確定數(shù)據(jù)怎樣在應(yīng)用程序給出。保留字 SELECT是DQL（也是所有SQL）用得最多的動(dòng)詞，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。這些DQL保留字常與其他類型的SQL語句一起使用。

數(shù)據(jù)操作語言（DML：Data Manipulation Language）

 其語句包括動(dòng)詞 INSERT， UPDATE和 DELETE。它們分別用于添加，修改和刪除表中的行。也稱為動(dòng)作查詢語言。

事務(wù)處理語言（TPL：Dransaction Processing Language）

它的語句能確保被DML語句影響的表的所有行及時(shí)得以更新。TPL語句包括BEGIN TRANSACTION，COMMIT和ROLLBACK

數(shù)據(jù)控制語言（DCL：DataBase Control Language）

 它的語句通過GRANT或REVOKE獲得許可，確定單個(gè)用戶和用戶組對(duì)數(shù)據(jù)庫(kù)對(duì)象的訪問。某些RDBMS可用GRANT或REVOKE控制對(duì)表單個(gè)列的訪問。

數(shù)據(jù)定義語言（DDL：Data Definition Language）

 其語句包括動(dòng)詞CREATE和DROP。在數(shù)據(jù)庫(kù)中創(chuàng)建新表或刪除表（CREAT TABLE 或 DROP TABLE）；為表加入索引等。DDL包括許多與人數(shù)據(jù)庫(kù)目錄中獲得數(shù)據(jù)有關(guān)的保留字。它也是動(dòng)作查詢的一部分。

指針控制語言（CCL）

 DDL：Data Defined Language（數(shù)據(jù)定義）

CREATE, ALTER, DROP、SHOW （增刪改查）

DML: Data Manapulating Language（數(shù)據(jù)操作）

INSERT, DELETE, UPDATE, SELECT  （增刪改查）

SQL代碼：

存儲(chǔ)過程：procedure

存儲(chǔ)函數(shù)：function

觸發(fā)器：trigger

事件調(diào)度器：event scheduler

例程：routine

SQL語言

CREATE建 ALTER改 DROP刪

創(chuàng)建數(shù)據(jù)庫(kù)

mysql> SHOW DATABASES;                    #查看MySQL中的數(shù)據(jù)庫(kù)
+--------------------+
| Database     |
+--------------------+
| information_schema|
| mysql       |
| test       |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> CREATE DATABASE Oracle;            #創(chuàng)建數(shù)據(jù)庫(kù)Oracle        
Query OK, 1 row affected (0.00 sec)
 
mysql> SHOW DATABASES;                    #查看是否創(chuàng)建成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Oracle             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

創(chuàng)建表

mysql> SELECT DATABASE();                  #查看當(dāng)前所在數(shù)據(jù)庫(kù)位置DATABASE()為MySQL內(nèi)置函數(shù)
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
     
mysql> USE Oracle                           #切換到我們之前創(chuàng)建的Oracle數(shù)據(jù)庫(kù)中
Database changed    
mysql> SELECT DATABASE();                   #查看是否切換到Oracle
+------------+
| DATABASE() |
+------------+
| Oracle     |
+------------+
1 row in set (0.00 sec)
mysql> CREATE table BranchTab(              #創(chuàng)建表
    -> Id INT,
    -> Name CHAR(30)
    -> );
Query OK, 0 rows affected (0.09 sec)
 
mysql> SHOW TABLES;                         #查看BranchTab表是否創(chuàng)建成功
+------------------+
| Tables_in_Oracle|
+------------------+
| BranchTab    |
+------------------+
1 row in set (0.00 sec)

修改表

mysql> SELECT DATABASE();                   #查看當(dāng)前所在數(shù)據(jù)庫(kù)為準(zhǔn)
+------------+
| DATABASE() |
+------------+
| Oracle     |
+------------+
1 row in set (0.00 sec)
 
mysql> SHOW TABLES;                          #查看當(dāng)前所在數(shù)據(jù)庫(kù)位置中的表
+------------------+
| Tables_in_Oracle |
+------------------+
| BranchTab        |
+------------------+
1 row in set (0.00 sec)
 
mysql> ALTER TABLE BranchTab RENAME branchtab;    #修改表BranchTab為branchtab
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;                            #查看是否修改成功
+------------------+
| Tables_in_Oracle |
+------------------+
| brannhtab        |
+------------------+
1 row in set (0.00 sec)

刪除表

mysql> SELECT DATABASE();                                    #查看當(dāng)前所在數(shù)據(jù)庫(kù)位置
+------------+
| DATABASE() |
+------------+
| Oracle     |
+------------+
1 row in set (0.00 sec)
 
mysql> SHOW TABLES;                                       #查看當(dāng)前所在數(shù)據(jù)庫(kù)位置中的表
+------------------+
| Tables_in_Oracle |
+------------------+
| branchtab        |
+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE bracnhtab;                                 #DROP掉branchtab表
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;                                       #查看branchtabs是否被刪除
Empty set (0.00 sec)

刪除數(shù)據(jù)庫(kù)

mysql> SHOW DATABASES;                         #查看MySQL中的所有庫(kù)，發(fā)現(xiàn)Oracle庫(kù)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Oracle             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> DROP DATABASE Oracle;                          #DROP掉Oracle數(shù)據(jù)庫(kù)
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW DATABASES;                           #查看Oracke是否被刪
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

DML操作語言命令

INSERT增 DELETE刪 SELECT查 UPDATE改

mysql> CREATE DATABASE oracle;                        #創(chuàng)建oracle數(shù)據(jù)庫(kù)
Query OK, 1 row affected (0.00 sec)
 
mysql> use oracle                               #切換到oracle數(shù)據(jù)庫(kù)
Database changed
mysql> CREATE TABLE branch(
    -> Id INT,
    -> Name CHAR(30)
    -> );
Query OK, 0 rows affected (0.16 sec)
 
mysql> DESC branch;                             #查看表結(jié)構(gòu)，簡(jiǎn)要增加數(shù)據(jù)最好看下別弄錯(cuò)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Id    | int(11)  | YES  |     | NULL    |       |
| Name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM branch;                       #查看表結(jié)構(gòu)明細(xì)
Empty set (0.00 sec)
mysql> SHOW CREATE TABLE branchG
*************************** 1. row ***************************
       Table: branch
Create Table: CREATE TABLE `branch` (
  `Id` int(11) DEFAULT NULL,
  `Name` char(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1                   #可以看出我們使用的是MyISMA
1 row in set (0.00 sec)
mysql> SELECT @@version;                    
+-----------+
| @@version |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

插入數(shù)據(jù)

mysql> SELECT DATABASE();                                                      #查看自己所在數(shù)據(jù)庫(kù)位置是否正確
+------------+
| DATABASE() |
+------------+
| oracle     |
+------------+
1 row in set (0.00 sec)
mysql> DESC branch;                                                            #查看表結(jié)構(gòu)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Id    | int(11)  | YES  |     | NULL    |       |
| Name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO branch VALUES                                               #插入數(shù)據(jù)到branch表中                                 
    -> (1,"Tom"),
    -> (2,"Sunshine");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM branch;                                                   #查看是否插入成功
+------+----------+
| Id   | Name     |
+------+----------+
|    1 | Tom      |
|    2 | Sunshine |
+------+----------+
2 rows in set (0.00 sec)

刪除數(shù)據(jù)

mysql> SELECT DATABASE();                                                       #查看所在數(shù)據(jù)庫(kù)位置
+------------+
| DATABASE() |
+------------+
| oracle     |
+------------+
1 row in set (0.00 sec)
 
mysql> DESC branch;                                                             #查看branch表結(jié)構(gòu)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Id    | int(11)  | YES  |     | NULL    |       |
| Name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> DELETE FROM branch;                                                      #刪除表數(shù)據(jù)，沒加WHERE條件就是刪除這張表里面的所有內(nèi)容
Query OK, 2 rows affected (0.00 sec)
 
mysql> SELECT * FROM branch;                                                    #查看是否刪除成功
Empty set (0.00 sec)
 
mysql> INSERT INTO branch VALUES                                                #插入新的數(shù)據(jù)
    -> (1,"Alis"),
    -> (2,"jeery");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM branch;                                                    #查看是否插入成功
+------+-------+    
| Id   | Name  |
+------+-------+
|    1 | Alis  |
|    2 | jeery |
+------+-------+
2 rows in set (0.00 sec)
 
mysql> DELETE FROM branch WHERE Id=1;                                            #刪除branch表里面的內(nèi)容加了條件判斷WHERE Id=1
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM branch;                                                     #查看是否刪除我們指定的數(shù)據(jù)
+------+-------+
| Id   | Name  |
+------+-------+
|    2 | jeery |
+------+-------+
1 row in set (0.00 sec)
 
mysql> DELETE FROM branch WHERE Name=jeery;                                     #刪除branch表里面的內(nèi)容加了條件判斷 WHERE Name=jeery;但是jeery沒加單引號(hào)報(bào)錯(cuò)
ERROR 1054 (42S22): Unknown column "jeery" in "where clause"
mysql> DELETE FROM branch WHERE Name="jeery";                                   #刪除branch表里面的內(nèi)容加了條件判斷 WHERE Name="jeery";加了單引號(hào)成功
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM branch;                                                    #查看是否刪除我們指定你的數(shù)據(jù)
Empty set (0.00 sec)

查看數(shù)據(jù)

mysql> DESC branch;                                                             #查看表結(jié)構(gòu)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Id    | int(11)  | YES  |     | NULL    |       |
| Name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> INSERT INTO branch VALUES                                                 #插入一些數(shù)據(jù)
    -> (1,"Sunshine"),
    -> (2,"jeery"),
    -> (3,"Alis"),
    -> (4,"Tom");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM branch;                                                    #查看branch表中的數(shù)據(jù)
+------+----------+
| Id   | Name     |
+------+----------+
|    1 | Sunshine |
|    2 | jeery    |
|    3 | Alis     |
|    4 | Tom      |
+------+----------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM branch WHERE Id=1;                                        #查看branch表中的數(shù)據(jù)，以條件 "WHERRE Id=1"
+------+----------+
| Id   | Name     |
+------+----------+
|    1 | Sunshine |
+------+----------+
1 row in set (0.00 sec)
mysql> SELECT Name FROM branch;                                                #查看branch表中Name字段的數(shù)據(jù)
+----------+
| Name     |
+----------+
| Sunshine |
| jeery    |
| Alis     |
| Tom      |
+----------+
4 rows in set (0.00 sec)
 
mysql> SELECT Name FROM branch WHERE Id=1;                                    #查看branch表中Name字段的數(shù)據(jù)，以條件 "WHERRE Id=1"
+----------+
| Name     |
+----------+
| Sunshine |
+----------+
1 row in set (0.00 sec)
 
 
mysql> SELECT count(*) FROM branch;                                          #使用count內(nèi)置函數(shù)查看branch表中有多少行
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
 
mysql> SELECT count(*) FROM bransh where Id=1;                               #使用count內(nèi)置函數(shù)查看branch表中有多少行，以條件 "WHERE Id=1"                                                             
ERROR 1146 (42S02): Table "oracle.bransh" doesn"t exist
mysql> SELECT count(*) FROM bransh;
ERROR 1146 (42S02): Table "oracle.bransh" doesn"t exist
mysql> SELECT count(*) FROM branch WHERE Id=1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

更改數(shù)據(jù)

mysql> DESC branch;                                                          #查看表結(jié)構(gòu)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Id    | int(11)  | YES  |     | NULL    |       |
| Name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> UPDATE branch SET Id=5;                                               #更改數(shù)據(jù),Id=5，生產(chǎn)環(huán)境中最好加條件，不然就呵呵了~
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
 
mysql> SELECT * FROM branch;                                                 #不加條件就變成這樣了，不是我們想要的
+------+----------+
| Id   | Name     |
+------+----------+
|    5 | Sunshine |
|    5 | jeery    |
|    5 | Alis     |
|    5 | Tom      |
+------+----------+
4 rows in set (0.00 sec)
 
mysql> UPDATE branch SET Id=1 WHERE Name="Sunshine";                         #更改數(shù)據(jù)Id=1，加了條件 "WHERE Name="Sunshine""
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM branch;                                                 #查看是否是更改成我們所想要的
+------+----------+
| Id   | Name     |
+------+----------+
|    1 | Sunshine |
|    5 | jeery    |
|    5 | Alis     |
|    5 | Tom      |
+------+----------+
4 rows in set (0.00 sec)

DCL控制語言命令

GRANT REVOKE
權(quán)限級(jí)別：管理權(quán)限，數(shù)據(jù)庫(kù)，表，字段，存儲(chǔ)例程

權(quán)限管理 GRANT

mysql> CREATE TABLE branchone(                                              #為了區(qū)別，我們這里在創(chuàng)建一個(gè)表
    -> Id INT,
    -> Name CHAR(30)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW TABLES;                                                          #查看oracle庫(kù)有幾張表
+------------------+
| Tables_in_oracle |
+------------------+
| branch           |
| branchone        |
+------------------+
2 rows in set (0.00 sec)
mysql> GRANT SELECT ON oracle.branch TO "sunshine"@"192.168.11.28" IDENTIFIED BY "sunshine";            #授權(quán)sunshine用戶只能通過192.168.11.28這個(gè)IP訪問數(shù)據(jù)庫(kù)，而且只有oracle數(shù)據(jù)庫(kù)branch的查看權(quán)限
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR "sunshine"@"192.168.11.28";                                                      #查看是否授權(quán)成功，我們看到GRANT SELECT ON `oracle`.`branch` TO "sunshine"@"192.168.11.28"
+---------------------------------------------------------------------------------------------------------------------+
| Grants for sunshine@192.168.11.28                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO "sunshine"@"192.168.11.28" IDENTIFIED BY PASSWORD "*D6B63C1953E7F096DB307F8AC48C4AD703E57001" |
| GRANT SELECT ON `oracle`.`branch` TO "sunshine"@"192.168.11.28"                                                     |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
[root@redis_master ~]# ifconfig | grep "inet addr:192.168"                                             #使用Linux系統(tǒng)，查看本機(jī)IP，為192.168.11.28
          inet addr:192.168.11.28  Bcast:192.168.11.255  Mask:255.255.255.0
[root@redis_master ~]# mysql -h192.168.11.28 -usunshine -psunshine                                     #使用sunshine用戶連接數(shù)據(jù)庫(kù)
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.1.73 Source distribution
 
Copyright (c) 2000, 2013, 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> SHOW DATABASES;                                                                                #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| oracle             |
| test               |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> USE oracle                                                                                    #進(jìn)入oracle數(shù)據(jù)庫(kù)
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> SHOW TABLES;                                                                                  #查看自己是否只能看到我們?cè)O(shè)定branch表
+------------------+
| Tables_in_oracle |
+------------------+
| branch           |
+------------------+
1 row in set (0.00 sec)
mysql> DESC branch;                                                                                  #查看表結(jié)構(gòu)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Id    | int(11)  | YES  |     | NULL    |       |
| Name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> INSERT INTO branch VALUES                                                                     #插入數(shù)據(jù)，提示權(quán)限拒絕command denied
    -> (10,"Test");
ERROR 1142 (42000): INSERT command denied to user "sunshine"@"gitlab.jinr.com" for table "branch"
mysql> DELETE FROM branch;                                                                           #刪除數(shù)據(jù)，提示權(quán)限拒絕 command denied
ERROR 1142 (42000): DELETE command denied to user "sunshine"@"gitlab.jinr.com" for table "branch"
mysql> UPDATE branch SET Id=1;                                                                       #更改數(shù)據(jù)，提示權(quán)限拒絕 command denied
ERROR 1142 (42000): UPDATE command denied to user "sunshine"@"gitlab.jinr.com" for table "branch"
mysql> SELECT * FROM branch;                                                                         #查看數(shù)據(jù)，正常
+------+----------+
| Id   | Name     |
+------+----------+
|    1 | Sunshine |
|    5 | jeery    |
|    5 | Alis     |
|    5 | Tom      |
+------+----------+
4 rows in set (0.00 sec)
 
[root@jroa ~]# ifconfig  | grep "inet addr:192.168"                                                  #使用另外一臺(tái)Linux系統(tǒng)，查看IP，為192.168.11.21                                         
          inet addr:192.168.11.21  Bcast:192.168.11.255  Mask:255.255.255.0
[root@jroa ~]# mysql -h192.168.11.28 -usunshine -psunshine                                           #嘗試連接，提示需"192.168.11.28" (113) 才能登入
ERROR 2003 (HY000): Can"t connect to MySQL server on "192.168.11.28" (113)

回收授權(quán) REVOKE

mysql> SHOW GRANTS FOR "sunshine"@"192.168.11.28";                                                    #查看權(quán)限，發(fā)現(xiàn) GRANT SELECT ON `oracle`.`branch` TO "sunshine"@"192.168.11.28" 
+---------------------------------------------------------------------------------------------------------------------+
| Grants for sunshine@192.168.11.28                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO "sunshine"@"192.168.11.28" IDENTIFIED BY PASSWORD "*D6B63C1953E7F096DB307F8AC48C4AD703E57001" |
| GRANT SELECT ON `oracle`.`branch` TO "sunshine"@"192.168.11.28"                                                     |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> REVOKE SELECT ON oracle.branch FROM  "sunshine"@"192.168.11.28";                                #收回授權(quán)
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR "sunshine"@"192.168.11.28";                                                    #查看權(quán)限，沒發(fā)現(xiàn) GRANT SELECT ON `oracle`.`branch` TO "sunshine"@"192.168.11.28"
+---------------------------------------------------------------------------------------------------------------------+
| Grants for sunshine@192.168.11.28                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO "sunshine"@"192.168.11.28" IDENTIFIED BY PASSWORD "*D6B63C1953E7F096DB307F8AC48C4AD703E57001" |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
[root@redis_master ~]# !if                                                                             #查看本機(jī)IP，為192.168.11.28
ifconfig | grep "inet addr:192.168"
          inet addr:192.168.11.28  Bcast:192.168.11.255  Mask:255.255.255.0
[root@redis_master ~]# !mys                                                                            #連接mysql，因?yàn)榈谝淮问跈?quán)了，就算收回，公共庫(kù)的權(quán)限還是有的
mysql -h192.168.11.28 -usunshine -psunshine
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.1.73 Source distribution
 
Copyright (c) 2000, 2013, 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> show databases;                                                                                 #查看數(shù)據(jù)庫(kù)，發(fā)現(xiàn)oracle數(shù)據(jù)不見啦
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

GPU云服務(wù)器云服務(wù)器 mysql數(shù)據(jù)庫(kù)管理 mysql數(shù)據(jù)管理 mysql數(shù)據(jù)庫(kù)管理界面 mysql 數(shù)據(jù)庫(kù)管理工具

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://m.hztianpu.com/yun/26985.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

meteor199

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

tensorflowgpu

閱讀 2762·2023-04-25 15:15
秒殺系統(tǒng)企業(yè)級(jí)實(shí)戰(zhàn)應(yīng)用（真實(shí)工業(yè)界案例）

閱讀 1391·2021-11-25 09:43
Wordpress廢棄的函數(shù)

閱讀 1662·2021-11-23 09:51
C/C++游戲項(xiàng)目詳細(xì)教學(xué)：《掃雷》

閱讀 1163·2021-11-12 10:36
C語言實(shí)現(xiàn)入門級(jí)小游戲——三子棋（萬字長(zhǎng)篇）

閱讀 2957·2021-11-11 16:55
RAKsmart：美國(guó)獨(dú)立服務(wù)器，硅谷機(jī)房，月付$76起

閱讀 1027·2021-11-08 13:18
Time4VPS：萬圣節(jié)促銷，全場(chǎng)5折優(yōu)惠，linux vps，3.99 EUR/月起，windos

閱讀 801·2021-10-28 09:31
css基本樣式1（7.1）

閱讀 2115·2019-08-30 15:47

成人无码视频,亚洲精品久久久久av无码,午夜精品久久久久久毛片,亚洲中文字幕日韩无码

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來選購(gòu)！

MySQL數(shù)據(jù)庫(kù)管理（一）

相關(guān)文章

***MySQL必知必會(huì)（1）：了解Mysql***

**立足Docker運(yùn)行MySQL：多主機(jī)網(wǎng)絡(luò)下Docker Swarm模式的容器管理**