国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

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

meteor199 / 2461人閱讀

摘要:在經(jīng)濟管理的日常工作中,常常需要把某些相關(guān)的數(shù)據(jù)放進這樣的倉庫,并根據(jù)管理的需要進行相應的處理。數(shù)據(jù)分類結(jié)構(gòu)化數(shù)據(jù)能夠用數(shù)據(jù)或統(tǒng)一的結(jié)構(gòu)加以表示,我們稱之為結(jié)構(gòu)化數(shù)據(jù),如數(shù)字符號。傳統(tǒng)的關(guān)系數(shù)據(jù)模型行數(shù)據(jù),存儲于數(shù)據(jù)庫,可用二維表結(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ù)庫
 數(shù)據(jù)庫(Database)是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的建立在計算機存儲設備上的倉庫。

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

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

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

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

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

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

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

關(guān)系模型:使用表格表示實體和實體之間關(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ù)字、符號。傳統(tǒng)的關(guān)系數(shù)據(jù)模型、行數(shù)據(jù),存儲于數(shù)據(jù)庫,可用二維表結(jié)構(gòu)表示。

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

介于完全結(jié)構(gòu)化數(shù)據(jù)(如關(guān)系型數(shù)據(jù)庫、面向?qū)ο髷?shù)據(jù)庫中的數(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ù)

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

關(guān)系模型:

數(shù)據(jù)庫:一個方案、一個項目

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

結(jié)構(gòu)化查詢語言包含6個部分
結(jié)構(gòu)化查詢語言;簡稱SQL,是一種特殊目的的編程語言,是一種數(shù)據(jù)庫查詢和程序設計語言,用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng);同時也是數(shù)據(jù)庫腳本文件的擴展名。 
數(shù)據(jù)查詢語言(DQL:Data Query Language)
 其語句,也稱為“數(shù)據(jù)檢索 語句”,用以從表中獲得數(shù)據(jù),確定數(shù)據(jù)怎樣在應用程序給出。保留字 SELECT是DQL(也是所有SQL)用得最多的動詞,其他DQL常用的保留字有WHERE,ORDER BY,GROUP BY和HAVING。這些DQL保留字常與其他類型的SQL語句一起使用。 
數(shù)據(jù)操作語言(DML:Data Manipulation Language)
 其語句包括動詞 INSERT, UPDATE和 DELETE。它們分別用于添加,修改和刪除表中的行。也稱為動作查詢語言。   
事務處理語言(TPL:Dransaction Processing Language)
它的語句能確保被DML語句影響的表的所有行及時得以更新。TPL語句包括BEGIN TRANSACTION,COMMIT和ROLLBACK
數(shù)據(jù)控制語言(DCL:DataBase Control Language)
 它的語句通過GRANT或REVOKE獲得許可,確定單個用戶和用戶組對數(shù)據(jù)庫對象的訪問。某些RDBMS可用GRANT或REVOKE控制對表單個列的訪問。   
數(shù)據(jù)定義語言(DDL:Data Definition Language)
 其語句包括動詞CREATE和DROP。在數(shù)據(jù)庫中創(chuàng)建新表或刪除表(CREAT TABLE 或 DROP TABLE);為表加入索引等。DDL包括許多與人數(shù)據(jù)庫目錄中獲得數(shù)據(jù)有關(guān)的保留字。它也是動作查詢的一部分。   
指針控制語言(CCL)
 DDL:Data Defined Language(數(shù)據(jù)定義)

CREATE, ALTER, DROP、SHOW (增刪改查)

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

INSERT, DELETE, UPDATE, SELECT  (增刪改查)
SQL代碼:
存儲過程:procedure

存儲函數(shù):function

觸發(fā)器:trigger

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

例程:routine  
SQL語言

CREATE建 ALTER改 DROP刪

創(chuàng)建數(shù)據(jù)庫
mysql> SHOW DATABASES;                    #查看MySQL中的數(shù)據(jù)庫
+--------------------+
| Database     |
+--------------------+
| information_schema|
| mysql       |
| test       |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> CREATE DATABASE Oracle;            #創(chuàng)建數(shù)據(jù)庫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();                  #查看當前所在數(shù)據(jù)庫位置DATABASE()為MySQL內(nèi)置函數(shù)
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
     
mysql> USE Oracle                           #切換到我們之前創(chuàng)建的Oracle數(shù)據(jù)庫中
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();                   #查看當前所在數(shù)據(jù)庫為準
+------------+
| DATABASE() |
+------------+
| Oracle     |
+------------+
1 row in set (0.00 sec)
 
mysql> SHOW TABLES;                          #查看當前所在數(shù)據(jù)庫位置中的表
+------------------+
| 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();                                    #查看當前所在數(shù)據(jù)庫位置
+------------+
| DATABASE() |
+------------+
| Oracle     |
+------------+
1 row in set (0.00 sec)
 
mysql> SHOW TABLES;                                       #查看當前所在數(shù)據(jù)庫位置中的表
+------------------+
| 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ù)庫
mysql> SHOW DATABASES;                         #查看MySQL中的所有庫,發(fā)現(xiàn)Oracle庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Oracle             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> DROP DATABASE Oracle;                          #DROP掉Oracle數(shù)據(jù)庫
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ù)庫
Query OK, 1 row affected (0.00 sec)
 
mysql> use oracle                               #切換到oracle數(shù)據(jù)庫
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),簡要增加數(shù)據(jù)最好看下別弄錯
+-------+----------+------+-----+---------+-------+
| 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)明細
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ù)庫位置是否正確
+------------+
| 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ù)庫位置
+------------+
| 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沒加單引號報錯
ERROR 1054 (42S22): Unknown column "jeery" in "where clause"
mysql> DELETE FROM branch WHERE Name="jeery";                                   #刪除branch表里面的內(nèi)容加了條件判斷 WHERE Name="jeery";加了單引號成功
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)限級別:管理權(quán)限,數(shù)據(jù)庫,表,字段,存儲例程

權(quán)限管理 GRANT
mysql> CREATE TABLE branchone(                                              #為了區(qū)別,我們這里在創(chuàng)建一個表
    -> Id INT,
    -> Name CHAR(30)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW TABLES;                                                          #查看oracle庫有幾張表
+------------------+
| 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這個IP訪問數(shù)據(jù)庫,而且只有oracle數(shù)據(jù)庫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),查看本機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ù)庫
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ù)庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| oracle             |
| test               |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> USE oracle                                                                                    #進入oracle數(shù)據(jù)庫
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;                                                                                  #查看自己是否只能看到我們設定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"                                                  #使用另外一臺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                                                                             #查看本機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,因為第一次授權(quán)了,就算收回,公共庫的權(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ù)庫,發(fā)現(xiàn)oracle數(shù)據(jù)不見啦
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

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

轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/26985.html

相關(guān)文章

  • MySQL必知必會(1):了解Mysql

    摘要:比如用戶表,第一行表示張三第二行表示李四,每一行都包含所有的列也就是字段。也是一種數(shù)據(jù)庫管理系統(tǒng)。但是,活躍的社區(qū)都給出了解決辦法,也在逐步成長。 本文主要介紹Mysql最基礎知識:Mysql基本概念。 1.1 數(shù)據(jù)庫基礎 1.1.1 簡介 數(shù)據(jù)庫,從字面意思來理解就是一個用來裝數(shù)據(jù)的容器。每天我們都在和數(shù)據(jù)庫打交道,比如你每天打開APP瀏覽新聞,瀏覽微博,這些都是間接的使用數(shù)據(jù)庫。 ...

    陳偉 評論0 收藏0
  • 立足Docker運行MySQL:多主機網(wǎng)絡下Docker Swarm模式的容器管理

    摘要:本文將以多主機網(wǎng)絡環(huán)境為基礎,探討如何利用內(nèi)置編排工具模式對各主機上的容器加以管理。在本文中,我們將立足于臺主機與在負載均衡之上部署應用程序容器,同時將其接入一套覆蓋網(wǎng)絡。管理節(jié)點會利用負載均衡以將服務公布至集群之外。 本文將以多主機網(wǎng)絡環(huán)境為基礎,探討如何利用內(nèi)置編排工具 Docker Swarm模式對各主機上的容器加以管理。 Docker Engine – Swarm模式 在...

    20171112 評論0 收藏0
  • MySQL數(shù)據(jù)庫管理

    摘要:在經(jīng)濟管理的日常工作中,常常需要把某些相關(guān)的數(shù)據(jù)放進這樣的倉庫,并根據(jù)管理的需要進行相應的處理。數(shù)據(jù)分類結(jié)構(gòu)化數(shù)據(jù)能夠用數(shù)據(jù)或統(tǒng)一的結(jié)構(gòu)加以表示,我們稱之為結(jié)構(gòu)化數(shù)據(jù),如數(shù)字符號。傳統(tǒng)的關(guān)系數(shù)據(jù)模型行數(shù)據(jù),存儲于數(shù)據(jù)庫,可用二維表結(jié)構(gòu)表示。 博文參考 http://www.178linux.com/79462 http://iceyao.blog.51cto.com/9426658/1...

    lmxdawn 評論0 收藏0

發(fā)表評論

0條評論

最新活動
閱讀需要支付1元查看
<