快速了解MySQL的入门知识

下文主要给大家带来快速了解MySQL的入门知识，希望这些文字能够带给大家实际用处，这也是我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为branchtabQuery 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 changedmysql> 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: branchCreate Table: CREATE TABLE `branch` (  `Id` int(11) DEFAULT NULL,  `Name` char(30) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1                   #可以看出我们使用的是MyISMA1 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: 0mysql> 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: 0mysql> 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=1Query 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: 0mysql> 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 existmysql> SELECT count(*) FROM bransh;ERROR 1146 (42S02): Table 'oracle.bransh' doesn't existmysql> 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: 0mysql> 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: 0mysql> 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 -h292.168.11.28 -usunshine -psunshine                                     #使用sunshine用户连接数据库Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 deniedERROR 1142 (42000): DELETE command denied to user 'sunshine'@'gitlab.jinr.com' for table 'branch'mysql> UPDATE branch SET Id=1;                                                                       #更改数据，提示权限拒绝 command deniedERROR 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 -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';                                                    #查看权限，发现 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.28ifconfig | 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 -h292.168.11.28 -usunshine -psunshineWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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)

对于以上关于MySQL的入门知识，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。