MySQL | MySQL 数据库系统(二)- SQL语句的基本操作

这是我参与更文挑战的第 22 天,活动详情查看:更文挑战


一名致力于在技术道路上的终身学习者、实践者、分享者,一位忙起来又偶尔偷懒的原创博主,一个偶尔无聊又偶尔幽默的少年。

欢迎各位掘友们微信搜索「杰哥的IT之旅」关注!

原文链接:MySQL | MySQL 数据库系统(二)- SQL语句的基本操作

前言

在前面的文章中,我们熟悉了如何搭建及登录 MySQL 数据库系统,文章《手把手教你在 Linux 环境下部署 MySQL 数据库!》那么,接下来,将通过本篇文章学习如何使用 MySQL 数据库系统的基本操作使用,也是我们在今后的工作中会有所运用的。

一、查看当前服务器中有哪些库

show databases 语句: 用于查看当前 MySQL 数据库系统中包含哪些库。

初始化后的 MySQL 数据库,默认有四个库分别是:

information_schema
mysql
performance_schema
test
(mysql库中包含了用户认证的相关表)
复制代码
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
复制代码

查看当前使用的库中有哪些表

show tables 语句: 用于查看当前所在的库中所包含的表。

操作前,需先使用 use 语句来切换到所要使用的库。

若你想要知道自己当前所使用的 mysql 库中包含哪些表,即可切换到 mysql 的目标库中,执行语句:show tables; 来进行查看 mysql 库中的表。

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)
复制代码

查询表的结构

describe 语句: 用于显示表的结构,组成表的各字段信息。

需指定“库名,表名”来作为参数,指定表名时,需先使用 use 语句来切换到所要使用的库。

若查询表结构时,可通过如下语句来进行查看 mysql 库中的 user 表结构,同时也可以执行语句:describe mysql.user; 查看,所输出的结构是一致的。

mysql> describe 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       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
复制代码

查看当前数据库版本

select version(); 语句:用于查看当前 mysql 数据库版本;

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.22-log |
+------------+
1 row in set (0.02 sec)
复制代码

查看当前所在哪个库

select database(); 语句: 用于查看当前所在哪个库;

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)
复制代码

以列的格式进行显示

在 MySQL 数据库中,执行 SQL 语句后面加 \G 表示将查询结果按列打印。

默认情况下,MySQL 数据库的查询结果是横向输出的。

第一行代表的是列头,第二列代表的是记录集;

mysql> select * from db\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
2 rows in set (0.00 sec)
复制代码

二、创建库

create database 语句:用于创建一个新库,需指定新的数据库名称来作为参数;

auth 将作为本次新建库的库名,其中该数据库不包含任何表。

mysql> create database auth;
Query OK, 1 row affected (0.45 sec)
复制代码

在刚刚新建库的 /usr/local/mysql/data 目录下会自动生成一个与新建库名所相同的文件夹。

[root@localhost ~]# cd /usr/local/mysql/data
[root@localhost data]# ls
auth     ib_logfile0  localhost.err  mysql             mysql-bin.000002  mysql-bin.000004  mysql.error.log     test
ibdata1  ib_logfile1  localhost.pid  mysql-bin.000001  mysql-bin.000003  mysql-bin.index   performance_schema
复制代码

三、创建表

create table 语句:用于在当前 auth 库中创建新的表,指定数据表名称作为创建新表的参数,并定义该创建新表的所使用的字段;

在创建表之前,需明确你所要建立表结构,字段名称,类型等信息。

创建表的基本语法格式

create table 表名 (字段1名称 类型,字段2名称 类型,......, PRIMARY KEY (主键名))
复制代码

创建好的 users 表,将字段定义部分的 default 语句用于设置默认的密码字符,primary 语句用于设置主键字段名;

mysql> use auth;
Database changed
mysql> create table users (user_name char(16) not null, user_passwd char(48) default '', primary key (user_name));
Query OK, 0 rows affected (0.66 sec)
复制代码

四、删除数据表及数据库

drop table 语句:用于删除库的表,需指定“库名,表名” 作为参数;

若要指定表名参数,先执行 “use” 语句切换到目标库;

执行以下操作可以删除 auth 库中的 users 表。

mysql> drop table auth.users;
Query OK, 0 rows affected (0.09 sec)
复制代码

drop database 语句:用于删除指定的库,需指定库名来作为参数;

执行以下操作可以删除 auth 库。

mysql> drop database auth;
Query OK, 0 rows affected (0.02 sec)
复制代码

五、管理表中的数据记录

在前面的步骤中介绍了创建库及表,下面我们将基于 auth 库来管理表中的数据。(插入、查询、修改及删除数据库中的记录);

插入数据

insert into 语句:用于向目标表中插入新的数据。

语句基本格式
insert into 表名(字段 1 ,字段 2 ,···) values(字段 1 的值,字段 2 的值,···)
复制代码

如下操作,将向 auth 库中的 user 表插入一条记录,user_name(用户名)为:'jacktian', 所对应的 user_passwd(密码)为:'666666' 。

需要注意的是,在 values 所插入的值必须与前面指定的各字段所对应。

mysql> use auth;
Database changed
mysql> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| servers        |
| users          |
+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO users(user_name,user_passwd) VALUES('jacktian', PASSWORD('666666'));                                                                                                        
Query OK, 1 row affected (0.00 sec)
复制代码

查看表结构是否建立正确;

mysql> describe users;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name   | char(16) | NO   | PRI | NULL    |       |
| user_passwd | char(48) | YES  |     |         |       |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
复制代码

插入新的数据记录,如果这条记录完整包括表中所有字段的值,则所插入的语句中指定字段的部分字段可省略。

mysql> insert into users values ('jake' , password('888888'));
Query OK, 1 row affected (0.01 sec)
复制代码

查看数据

select 语句:用于查看当前所指定的表中查看符合条件的数据记录;

语句基本格式
select 字段名 1,字段名 2,··· from 表名 where 条件表达式
复制代码

若要查询所有字段时,可使用通配符“*”,来显示所有的数据记录,同时将省略 where 条件。执行如下操作时,将可以查看 auth 库中的 users 表内的所有数据,其中 user_passwd 字串是加密的,因此将不显示出实际的密码内容。

mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jacktian  | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| jake      | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB |
+-----------+-------------------------------------------+
2 rows in set (0.07 sec)
复制代码

当需要根据指定的条件查询数据时,可使用 where 条件来进行查询。比如你只想查询 auth 库中的 users 表中的 user_name(用户名)为:'jacktian' 的记录,其显示出的数据包括:用户名及密码字段信息。

mysql> select user_name,user_passwd from auth.users where user_name='jacktian';
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jacktian  | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
+-----------+-------------------------------------------+
1 row in set (0.01 sec)
复制代码

修改数据

update 语句:用于修改,更新目标表中的数据。

语句基本格式
update 表名 set 字段名 1=字段值 1[,字段名 2=字段值 2] where 条件表达式
复制代码

通过如下操作来修改 users 表中用户名为 'jake' 的记录,将 password 设置为空值,并进行验证记录是否发现用户名为 jake 的密码已修改为空值。

mysql> update auth.users set user_passwd=password ('') where user_name = 'jake';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jacktian  | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| jake      |                                           |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
复制代码

通常情况下,在 MySQL 数据库中,我们用于访问数据库的各种用户信息都将保存在 mysql 库中的 user 表中,可直接修改其中的数据。

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| user                      |
+---------------------------+
23 rows in set (0.06 sec)
复制代码

这时,我们可以将数据库用户 root 的密码设置为 '666666' ,当再次访问 MySQL 数据库时,则需要使用 "mysql -u root -p" 来进行访问 MySQL 数据库系统,且必须使用此密码验证,否则将无法登录到 MySQL 数据库系统中。

需要注意的是,此数据库用户密码,可以设置复杂些,保证数据的安全性;

mysql> update mysql.user set password=password ('666666') where user= 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> FLUSH PRIVILEGES; // 刷新用户授权信息
复制代码

若你是在 Linux 系统命令行终端下,还可以使用 mysqladmin 工具来设置密码。

[root@localhost data]# mysqladmin -u root -p password '666666'
Enter password: 
复制代码

删除数据

delete 语句:用于删除表中的指定数据;

语句基本格式
delete from 表名 where 条件表达式
复制代码

若要删除 auth 库的 users 表中 user_name 用户名为:'jake' 的数据,可执行如下语句来进行操作,并验证该数据内容是否已经发现用户 jake 的数据已经删除了。

mysql> delete from auth.users where user_name = 'jake';
Query OK, 1 row affected (0.03 sec)

mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jacktian  | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
复制代码

在 MySQL 数据库系统中,默认添加了从本机访问数据库的空用户。如下 user、password 字段均为空值。为考虑数据库的安全性可直接删除这些空用户。where 后的条件表达式,将 user 字段为空的用户进行筛选,并验证该空用户的数据是否已经删除。

mysql> select user,host,password from mysql.user where user = '';
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
|      | localhost |          |
+------+-----------+----------+
1 row in set (0.00 sec)

mysql> delete from mysql.user where user = '';
Query OK, 1 row affected (0.04 sec)

mysql> select user,host,password from mysql.user where user = '';
Empty set (0.00 sec)
复制代码

推荐阅读

手把手教你在 Linux 环境下部署 MySQL 数据库!


原创不易,如果你觉得这篇文章对你有点用的话,麻烦你为本文点个赞、评论或转发一下,因为这将是我输出更多优质文章的动力,感谢!

对了,掘友们记得给我点个免费的关注哟!防止你迷路下次就找不到我了。

我们下期再见!

上一篇:Q:su命令无法切换用户问题,密码正确可登录


下一篇:NestJS - Session, JWT & Red