快速了解MySQL的入門知識

下文主要給大家?guī)砜焖倭私?a title="MySQL" target="_blank" href="http://kemok4.com/mysql/">MySQL的入門知識，希望這些文字能夠帶給大家實際用處，這也是我MySQL的入門知識這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

 一、MySQL有三種定義語言

    DDL：定義語言，比如：創(chuàng)建一張表，定義表的屬性如索引、寬位等待

    DML：操作語言，增刪查改

     DCL：控制語言，比如限定那個賬戶只能通過那個IP登入，又比如那個賬戶能訪問那些資源

 二、MySQL事務(wù)：

    1、MyISAM不支持

     2、InnoDB支持

下面的圖是自己捯飭捯飭整的，如有不適請發(fā)私信給Me~ ^-^

 三、SQL語言 

   A、DDL定義語言命令包含如下：

     1、CREATE

     2、ALTER

     3、DROP

1、CREATE

  1.1、創(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)

  1.2、創(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)

2、ALTER 修改表

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)

3、DROP

  3.1、刪除表

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)

  3.2、刪除數(shù)據(jù)庫Oracle

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)

   B、DML操縱語言命令如下

    1、INSERT

    2、DELETE

    3、SELECT

    4、UPDATE

操作前先建庫建表，并先使用下未介紹到SHOW CREATE TABLE TABLE_NAME,DESC TABLE_NAME

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 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 插入數(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)

2、DELETE 刪除數(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)

3、SELECT 查看數(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)

4、UPDATE 更改數(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)

     C、DCL控制語言命令如下

      1、GRANT

      2、REVOKE

1、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 -h292.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;                                                                                  #查看自己是否只能看到我們設(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"                                                  #使用另外一臺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 -h292.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';                                                    #查看權(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 -h292.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)

對于以上關(guān)于MySQL的入門知識，大家是不是覺得非常有幫助。如果需要了解更多內(nèi)容，請繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會喜歡上這些內(nèi)容的。