MySQL数据库基本语句

142次阅读

共计 7531 个字符，预计需要花费 19 分钟才能阅读完成。

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

通过 CentOS 7 编译安装 MySQL 数据库系统可以了解并掌握 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 数据库基本语句

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>