首页 > 代码库 > MySQL入门知识
MySQL入门知识
简单介绍下吧，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)
本文出自 “SunshineBoySZF” 博客，请务必保留此出处http://sunshineboyszf.blog.51cto.com/12087328/1859165
MySQL入门知识
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。