下文主要给大家带来快速了解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为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 -h292.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 -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.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 -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; #查看数据库，发现oracle数据不见啦
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)对于以上关于MySQL的入门知识，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
