MySQL数据库基本语句

MySQL数据库系统是一个典型的C/S（客户端/服务器）架构的应用，要访问MySQL数据库需要使用专门的客户端软件。在Linux系统中，最简单、易用的MySQL客户端软件是其自带的mysql命令工具。

通过CentOS 7 编译安装MySQL数据库系统可以了解并掌握MySQL数据库的安装方式，作为一名合格的运维工程师，MySQL数据库的基本操作也是必须要掌握的。

MySQL是一套数据库管理系统，在每台MySQL服务器中，均支持运行多个库，每个库相当于一个容器，存放着许多表，表中的每行记录包含一条具体的数据关系信息，称为数据记录。如图：

登录到数据库
经过安装后的初始化过程，MySQL数据库的默认管理用户名为root，密码为空。

[root@localhost ~]# mysql -u root

mysql>           //表示登录成功

登录mysql选项的作用：
-u：表示用于指定认证用户；
-p：进行密码校验（没有密码需省略）；
-h：远程登录时，需指定IP地址；
-P：远程登录是需指定端口号；

执行MySQL操作语句
MySQL操作语句与SQL server语句语法一模一样，对SQL语句不太了解的朋友可以参考博文SQL Server 语句操纵数据库。在MySQL数据库中，“；”表示结束，输入时不区分大小写。

mysql> show databases;                                      //查看当前服务器中有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;                                  //切换到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
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)

mysql> select database();                         //查看当前处于哪个数据库
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

MySQL数据库的数据文件存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为“.frm”“.myd”和“.myi”。
.frm：表结构；
.myd：存放数据；
.myi：表中索引信息。

查看表结构
DESCRIBE语句：用于显示表的结构，即组成表的各字段（列）的信息，需要指定“库名.表名”作为参数。

mysql> desc mysql.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       
                                               ………………    //省略部分内容

desc mysql.user;和describe user；作用一样。

mysql> create database auth;                            //创建数据库名为auth
Query OK, 1 row affected (0.00 sec)

mysql> use auth;                                                 //切换到auth数据库
Database changed

mysql> create table test (id int,name varchar(10));        //在auth数据库创建test表（表列的参数自定义）
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test values (1,'xioazhang');              //向表中插入数据
Query OK, 1 row affected (0.00 sec)

mysql> alter table test add comment varchar(100) null;         //向表中添加一列
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1,'xiaozhang','renshi');               //向表中插入一条数据记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from auth.test;                                     //查看表中内容
+------+-----------+---------+
| id   | name      | comment |
+------+-----------+---------+
|    1 | xiaozhang | renshi  |
+------+-----------+---------+
1 row in set (0.00 sec)

mysql> update auth.test  set id=10
    -> where name='xiaozhang';                                       //修改表中数据，小张的ID改为10
Query OK, 1 row affected (0.00 sec)

mysql> select * from auth.test;                                      //再次查看表中内容
+------+-----------+---------+
| id   | name      | comment |
+------+-----------+---------+
|   10 | xiaozhang | renshi  |
+------+-----------+---------+
1 row in set (0.00 sec)

mysql> delete from auth.test 
    -> where id=10;                                                      //删除表中ID等于10的用户
Query OK, 1 row affected (0.00 sec)

mysql> select * from auth.test;                                //再次查看表中已没有数据
Empty set (0.00 sec)

mysql> drop table auth.test;                                    //删除auth数据库中test表       
Query OK, 0 rows affected (0.00 sec)

mysql> drop database auth;                                   //删除数据库auth
Query OK, 0 rows affected (0.00 sec)

数据库用户授权

GRANT语句：专门用于设置数据库用户的访问权限。当指定用户不存在时，GRANT语句将会自动创建新的用户；如果用户已经存在，则GRANT语句用于修改用户信息。
使用GRANT语句时，需要注意的事项：

mysql> grant all on mysql.* to 'xiaoli'@'%' identified by '123456';
//创建名为xiaoli的数据库用户，密码为123456，并允许其在任何客户端登录访问，对mysql数据库有绝对权限。
Query OK, 0 rows affected (0.00 sec)

其中%表示所有，localhost表示本机。

mysql> show grants for 'xiaoli'@'%';                            //查看用户xiaoli在所有客户端的权限
+-------------------------------------------------------------------------------------------------------+
| Grants for xiaoli@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoli'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'xiaoli'@'%'                                                     |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

另一台客户机使用xiaoli访问
前提是CentOS 7客户端需安装MariaDB工具集，否则没有办法使用mysql工具。

[root@localhost ~]# rpm -ivh /mnt/Packages/mariadb-5.5.52-1.el7.x86_64.rpm 

[root@localhost ~]# mysql -u xiaoli -p -h 192.168.1.1         
Enter password: 
MySQL [(none)]>             //这样的标志表示客户端登录成功

撤销用户权限

mysql> revoke all on mysql.* from 'xiaoli'@'%';       //撤销xiaoli对mysql数据库的所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'xiaoli'@'%';                      //再次查看权限列表，确认用户已无权限
+-------------------------------------------------------------------------------------------------------+
| Grants for xiaoli@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoli'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit                       //退出MySQL数据库系统
Bye

设置数据库管理员密码
第一种方法：

mysql> update mysql.user set password=PASSWORD('123456')
    -> where user='root';
Query OK, 4 rows affected (0.00 sec)
mysql> flush privileges;                  //刷新用户授权信息
Query OK, 0 rows affected (0.00 sec)
[root@mysql /]# mysql -u root -p      //登录输入设置的密码
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, 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>

第二种方法：

[root@mysql /]# mysqladmin -u root  password '123456'
//输入警告信息可以忽略！

登录MySQL服务器就不用输入密码了。

[root@mysql /]# mysqladmin -u root -p'654321' password 
//使用mysqladmin工具更改密码；、
//-p后面跟旧密码，password后面是新密码
New password:       //新密码可以为空，输入新密码确认
Confirm new password: 
[root@mysql /]# mysql -u root    //不输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, 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>

忘记数据库管理员的密码
第一种方法：

[root@mysql /]# systemctl stop mysqld       //先停止mysql服务
[root@mysql /]# mysqld_safe --skip-grant-tables &
[1] 42462                     //使用原始脚本启动服务，带参数
[root@mysql /]# 190722 10:06:37 mysqld_safe Logging to '/usr/local/mysql/data/mysql.err'.
190722 10:06:37 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@mysql /]# mysql -u root        //不输入密码登录
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, 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>

第二种方法：

[root@mysql /]# vim /etc/my.cnf   //编写MySQL配置文件
                                …………   //省略部分内容
skip-grant-tables       //添加如下内容
[root@mysql /]# systemctl restart mysqld   //重启MySQL服务
[root@mysql /]# mysql -u root     //不用输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, 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管理命令的使用，已经可以满足大多数网络管理员（非专业数据库管理员）的工作需要。