簡單介紹下吧，MySQL應用的場景大多數互聯網公司第一次賣身是賣個了sun好像是10億，第二次是連同sun自己，以74億美元被賣給了Orecle~后面MySQL原作者站出來說，MySQL會存在閉源風險，整了個MariaDB~我也是醉了，也不考慮下我們的痛苦！下面簡要介紹下MySQL的入門知識。
一、MySQL有三種定義語言
DDL：定義語言，比如：創建一張表，定義表的屬性如索引、寬位等待
DML：操作語言，增刪查改
DCL：控制語言，比如限定那個賬戶只能通過那個IP登入，又比如那個賬戶能訪問那些資源
二、MySQL事務：
1、MyISAM不支持
2、InnoDB支持
下面的圖是自己捯飭捯飭整的，如有不適請發私信給Me~ ^-^
三、SQL語言
A、DDL定義語言命令包含如下：
1、CREATE
2、ALTER
3、DROP
1、CREATE
1.1、創建數據庫
mysql> SHOW DATABASES; #查看MySQL中的數據庫 +--------------------+ | Database | +--------------------+ | information_schema| | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> CREATE DATABASE Oracle; #創建數據庫Oracle Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; #查看是否創建成功 +--------------------+ | Database | +--------------------+ | information_schema | | Oracle | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec)
1.2、創建表
mysql> SELECT DATABASE(); #查看當前所在數據庫位置DATABASE()為MySQL內置函數 +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> USE Oracle #切換到我們之前創建的Oracle數據庫中 Database changed mysql> SELECT DATABASE(); #查看是否切換到Oracle +------------+ | DATABASE() | +------------+ | Oracle | +------------+ 1 row in set (0.00 sec) mysql> CREATE table BranchTab( #創建表 -> Id INT, -> Name CHAR(30) -> ); Query OK, 0 rows affected (0.09 sec) mysql> SHOW TABLES; #查看BranchTab表是否創建成功 +------------------+ | Tables_in_Oracle| +------------------+ | BranchTab | +------------------+ 1 row in set (0.00 sec)
2、ALTER 修改表
mysql> SELECT DATABASE(); #查看當前所在數據庫為準 +------------+ | DATABASE() | +------------+ | Oracle | +------------+ 1 row in set (0.00 sec) mysql> SHOW TABLES; #查看當前所在數據庫位置中的表 +------------------+ | 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)
3、DROP
3.1、刪除表
mysql> SELECT DATABASE(); #查看當前所在數據庫位置 +------------+ | DATABASE() | +------------+ | Oracle | +------------+ 1 row in set (0.00 sec) mysql> SHOW TABLES; #查看當前所在數據庫位置中的表 +------------------+ | 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)
3.2、刪除數據庫Oracle
mysql> SHOW DATABASES; #查看MySQL中的所有庫，發現Oracle庫 +--------------------+ | Database | +--------------------+ | information_schema | | Oracle | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> DROP DATABASE Oracle; #DROP掉Oracle數據庫 Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES; #查看Oracke是否被刪 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec)
B、DML操縱語言命令如下
1、INSERT
2、DELETE
3、SELECT
4、UPDATE
操作前先建庫建表，并先使用下未介紹到SHOW CREATE TABLE TABLE_NAME,DESC TABLE_NAME
mysql> CREATE DATABASE oracle; #創建oracle數據庫 Query OK, 1 row affected (0.00 sec) mysql> use oracle #切換到oracle數據庫 Database changed mysql> CREATE TABLE branch( -> Id INT, -> Name CHAR(30) -> ); Query OK, 0 rows affected (0.16 sec) mysql> DESC branch; #查看表結構，簡要增加數據最好看下別弄錯 +-------+----------+------+-----+---------+-------+ | 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; #查看表結構明細 Empty set (0.00 sec) mysql> SHOW CREATE TABLE branch\G *************************** 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)
1、INSERT 插入數據
mysql> SELECT DATABASE(); #查看自己所在數據庫位置是否正確 +------------+ | DATABASE() | +------------+ | oracle | +------------+ 1 row in set (0.00 sec) mysql> DESC branch; #查看表結構 +-------+----------+------+-----+---------+-------+ | 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 #插入數據到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)
2、DELETE 刪除數據
mysql> SELECT DATABASE(); #查看所在數據庫位置 +------------+ | DATABASE() | +------------+ | oracle | +------------+ 1 row in set (0.00 sec) mysql> DESC branch; #查看branch表結構 +-------+----------+------+-----+---------+-------+ | 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; #刪除表數據，沒加WHERE條件就是刪除這張表里面的所有內容 Query OK, 2 rows affected (0.00 sec) mysql> SELECT * FROM branch; #查看是否刪除成功 Empty set (0.00 sec) mysql> INSERT INTO branch VALUES #插入新的數據 -> (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表里面的內容加了條件判斷WHERE Id=1 Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM branch; #查看是否刪除我們指定的數據 +------+-------+ | Id | Name | +------+-------+ | 2 | jeery | +------+-------+ 1 row in set (0.00 sec) mysql> DELETE FROM branch WHERE Name=jeery; #刪除branch表里面的內容加了條件判斷 WHERE Name=jeery;但是jeery沒加單引號報錯 ERROR 1054 (42S22): Unknown column 'jeery' in 'where clause' mysql> DELETE FROM branch WHERE Name='jeery'; #刪除branch表里面的內容加了條件判斷 WHERE Name='jeery';加了單引號成功 Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM branch; #查看是否刪除我們指定你的數據 Empty set (0.00 sec)
3、SELECT 查看數據
mysql> DESC branch; #查看表結構 +-------+----------+------+-----+---------+-------+ | 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 #插入一些數據 -> (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表中的數據 +------+----------+ | 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表中的數據，以條件 "WHERRE Id=1" +------+----------+ | Id | Name | +------+----------+ | 1 | Sunshine | +------+----------+ 1 row in set (0.00 sec) mysql> SELECT Name FROM branch; #查看branch表中Name字段的數據 +----------+ | Name | +----------+ | Sunshine | | jeery | | Alis | | Tom | +----------+ 4 rows in set (0.00 sec) mysql> SELECT Name FROM branch WHERE Id=1; #查看branch表中Name字段的數據，以條件 "WHERRE Id=1" +----------+ | Name | +----------+ | Sunshine | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM branch; #使用count內置函數查看branch表中有多少行 +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM bransh where Id=1; #使用count內置函數查看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)
4、UPDATE 更改數據
mysql> DESC branch; #查看表結構 +-------+----------+------+-----+---------+-------+ | 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; #更改數據,Id=5，生產環境中最好加條件，不然就呵呵了~ 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'; #更改數據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)
C、DCL控制語言命令如下
1、GRANT
2、REVOKE
1、GRANT
mysql> CREATE TABLE branchone( #為了區別，我們這里在創建一個表 -> 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'; #授權sunshine用戶只能通過192.168.11.28這個IP訪問數據庫，而且只有oracle數據庫branch的查看權限 Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'sunshine'@'192.168.11.28'; #查看是否授權成功，我們看到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系統，查看本機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用戶連接數據庫 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; #查看數據庫 +--------------------+ | Database | +--------------------+ | information_schema | | oracle | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> USE oracle #進入oracle數據庫 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; #查看表結構 +-------+----------+------+-----+---------+-------+ | 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 #插入數據，提示權限拒絕command denied -> (10,'Test'); ERROR 1142 (42000): INSERT command denied to user 'sunshine'@'gitlab.jinr.com' for table 'branch' mysql> DELETE FROM branch; #刪除數據，提示權限拒絕 command denied ERROR 1142 (42000): DELETE command denied to user 'sunshine'@'gitlab.jinr.com' for table 'branch' mysql> UPDATE branch SET Id=1; #更改數據，提示權限拒絕 command denied ERROR 1142 (42000): UPDATE command denied to user 'sunshine'@'gitlab.jinr.com' for table 'branch' mysql> SELECT * FROM branch; #查看數據，正常 +------+----------+ | 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系統，查看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)
2、REVOKE
mysql> SHOW GRANTS FOR 'sunshine'@'192.168.11.28'; #查看權限，發現 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'; #收回授權 Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'sunshine'@'192.168.11.28'; #查看權限，沒發現 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，因為第一次授權了，就算收回，公共庫的權限還是有的 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; #查看數據庫，發現oracle數據不見啦 +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
原創文章，作者：N24-SunshineBoy，如若轉載，請注明出處：http://www.www58058.com/50246
