该博客围绕MySQL的SQL语句展开，介绍了SQL的分类，包括DDL、DQL、DML、DCL等语句，并进行了相关实践，如DML的删除操作、DCL的用户创建与授权等，还提及授权远程登录、查看帮助信息，最后阐述了MySQL底层的SQL解析流程。
文章目录
1.sql语句介绍
1.1 SQL介绍
1.2 SQL分类
1.3 DDL语句实践
# 1.当数据库存在的时候，再创建会报错，可以if判断 CREATE DATABASE edon_db; # 2.加入if判断创建数据库就不会再报错（简单的写法） CREATE DATABASE IF NOT EXISTS edon_db; # 3.删除数据库 DROP DATABASE edon_db; # 4.最完整的创建数据库语句写法，包含了数据库的字符集指定，让你的mysql能够支持utf-8编码，支持中文，且不敏感大小写。 CREATE DATABASE IF NOT EXISTS edon_db DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # 5.查看数据库创建的SQL是什么 SHOW CREATE DATABASE edon_db; # 6.查看所有的数据库信息 SHOW DATABASES; # 7.进入指定数据库 USE mysql; # 8.查看当前位置，在哪个库 select database();
# 1.当数据库存在的时候，再创建会报错，可以if判断
mysql> CREATE DATABASE edon_db;
ERROR 1007 (HY000): Can't create database 'edon_db'; database exists
# 2.加入if判断创建数据库就不会再报错（简单的写法）
mysql> CREATE DATABASE IF NOT EXISTS edon_db;
Query OK, 1 row affected, 1 warning (0.00 sec)
# 3.删除数据库
mysql> DROP DATABASE edon_db;
Query OK, 0 rows affected (0.00 sec)
## 4.最完整的创建数据库语句写法，包含了数据库的字符集指定，让你的mysql能够支持utf-8编码，支持中文，且不敏感大小写。
mysql> CREATE DATABASE IF NOT EXISTS edon_db DEFAULT CHARSET UTF8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
# 5.查看数据库创建的SQL是什么
mysql> SHOW CREATE DATABASE edon_db;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| edon_db | CREATE DATABASE `edon_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.01 sec)
# 6.查看所有的数据库信息
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edon_db |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
# 7.进入指定数据库
mysql> USE mysql;
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
# 8.查看当前位置，在哪个库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
1.4 DQL语句：查询
# 1.查看有多少张表 mysql> show tables; # 2.查看表结构，共有43个字段 mysql> desc user;
# 1.查看有多少张表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
# 2.查看表结构，共有43个字段
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)
# 查询user表中具体字段的信息
mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *1EF384F0CE1379A10F376CE05C274C1EE53BDCB9 |
| s1 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| s1 | | |
| 192.168.75.% | root | *3A0D00841ACB58FA966F7FDDEAC1B36917079CCB |
+--------------+------+-------------------------------------------+
7 rows in set (0.00 sec)
1.5 DML数据操作
insert 插入
update 更新
delete 删除
delete 删除
# 删除一些无用的用户数据 delete from 数据库.数据表 where 条件; # 删除 s1 | root 数据 mysql> delete from mysql.user where host='s1' and user='root';
# 删除一些无用的用户数据
delete from 数据库.数据表 where 条件;
mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *1EF384F0CE1379A10F376CE05C274C1EE53BDCB9 |
| s1 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| s1 | | |
| 192.168.75.% | root | *3A0D00841ACB58FA966F7FDDEAC1B36917079CCB |
+--------------+------+-------------------------------------------+
7 rows in set (0.00 sec)
# 进入到edon_db库
mysql> use edon_db;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| edon_db |
+------------+
1 row in set (0.00 sec)
# 删除 s1 | root 数据
mysql> delete from mysql.user where host='s1' and user='root';
Query OK, 1 row affected (0.00 sec)
# 在edon_db库中查询mysql库中user表，需要指定数据库
mysql> select host,user,password from user;
ERROR 1146 (42S02): Table 'edon_db.user' doesn't exist
mysql>
mysql> select host,user,password from mysql.user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *1EF384F0CE1379A10F376CE05C274C1EE53BDCB9 |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| s1 | | |
| 192.168.75.% | root | *3A0D00841ACB58FA966F7FDDEAC1B36917079CCB |
+--------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
# 删除| s1 | |
mysql> delete from mysql.user where host='s1' and user=' ';
Query OK, 1 row affected (0.00 sec)
mysql> select host,user,password from mysql.user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *1EF384F0CE1379A10F376CE05C274C1EE53BDCB9 |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| 192.168.75.% | root | *3A0D00841ACB58FA966F7FDDEAC1B36917079CCB |
+--------------+------+-------------------------------------------+
5 rows in set (0.00 sec)
1.6 DCL授权控制
创建用户
# 创建mysql的用户，且设置权限 # 开发创建一个账号，用于连接数据库，以及查看数据库信息 # % 符号：表示所有的主机网段，只要公司防火墙允许，公网就可以访问到数据库 语法： create user 名字@'允许登录的主机网段' identified by '密码'; # 创建dahai用户可以从任何网络访问数据库，且设置登录密码。 create user dahai@'%' identified by 'edon999'; # 查看权限 show grants for dahai@'%';
# 创建mysql的用户，且设置权限
# 开发创建一个账号，用于连接数据库，以及查看数据库信息
# % 符号：表示所有的主机网段，只要公司防火墙允许，公网就可以访问到数据库
语法：
create user 名字@'允许登录的主机网段' identified by '密码';
# 创建dahai用户可以从任何网络访问数据库，且设置登录密码。
mysql> create user dahai@'%' identified by 'edon999';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user | host | password |
+-------+--------------+-------------------------------------------+
| root | localhost | *1EF384F0CE1379A10F376CE05C274C1EE53BDCB9 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| dahai | % | *1EF384F0CE1379A10F376CE05C274C1EE53BDCB9 |
| root | 192.168.75.% | *3A0D00841ACB58FA966F7FDDEAC1B36917079CCB |
+-------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)
# 查看权限
mysql> show grants for dahai@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for dahai@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dahai'@'%' IDENTIFIED BY PASSWORD '*1EF384F0CE1379A10F376CE05C274C1EE53BDCB9' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
授权用户DQL权限
给dahai用户，添加登录后允许执行DQL的权限
# 使用root用户给dahai用户添加权限 # 允许 dahai 用户使用（授权）grant命令，添加 with grant option; # grant 给予的权限 on 数据库.数据表 to 用户@'允许登录的主机' with grant option; 授权语句语法： grant 给予的权限 on 数据库.数据表 to 用户@'允许登录的主机'; # 具体授权命令，给dahai查看的权限 grant select on mysql.user to dahai@'%'; # 刷新授权表，让授权语句，立即生效 flush privileges;
# 使用root用户给dahai用户添加权限
# 允许 dahai 用户使用（授权）grant命令，添加 with grant option;
# grant 给予的权限 on 数据库.数据表 to 用户@'允许登录的主机' with grant option;
授权语句语法：
grant 给予的权限 on 数据库.数据表 to 用户@'允许登录的主机';
# 具体授权命令，给dahai查看的权限
mysql> grant select on mysql.user to dahai@'%';
Query OK, 0 rows affected (0.00 sec)
# 刷新授权表，让授权语句，立即生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 服务器查看dahai用户的权限，usage登录和select查看权限
mysql> show grants for dahai@'%';
+-----------------------------------------------+
| Grants for dahai@% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'dahai'@'%' |
| GRANT SELECT ON `mysql`.`user` TO 'dahai'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)
1.7 授权远程登录
# 在服务端给root访问权限 # mysql -uroot -p > grant all privileges on *.* to root@'192.168.75.%' identified by 'edon333'; > flush privileges;
[root@c1 ~]# mysql -uroot -p -h192.168.75.30 -P3306
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.75.30' (113)
[root@c1 ~]# /etc/init.d/mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.. SUCCESS!
[root@c1 ~]# mysql -uroot -p -h192.168.75.30 -P3306
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.75.27' (using password: YES)
# 在服务端给root访问权限
[root@s1 application]# mysql -uroot -p
mysql> grant all privileges on *.* to root@'192.168.75.%' identified by 'edon333';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@c1 ~]# mysql -uroot -p -h192.168.75.30 -P3306
Enter password: # edon333
...
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edon_db |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
1.8 查看帮助信息
DDL语句
mysql> ? data definition
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SCHEMA
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SCHEMA
DROP SERVER
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
FOREIGN KEY
RENAME TABLE
TRUNCATE TABLE
# 查看具体使用帮助信息
mysql> ? CREATE DATABASE
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: {
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://dev.mysql.com/doc/refman/5.6/en/create-database.html
DCL语句
授权语句 ? Account Management
grant
revoke
commit
rollback
mysql> ? Account Management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER USER
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
DML语句
开发重点掌握：增删改查 ? Data Manipulation （数据操作语句）
select
insert
delete
update
mysql> ? Data Manipulation
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
CALL
DELETE
DO
DUAL
HANDLER
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
REPLACE
SELECT
UNION
UPDATE
1.9 mysql底层原理SQL解析流程
扫一扫
507
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
