• MySQL基础【学习至数据的导入导出】

    文章目录


    操作系统：CentOS 8
    数据库版本：8.0.26 Source distribution

    一、安装与配置

    1、安装

    yum install -y mysql-server.x86_64

    • 1

    2、MySQL安装完成后，启动报错，查看MySQL的状态，发现是3306端口被占用

    [root@iZ56kkvaq4nlfhZ etc]# systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2023-09-12 18:58:37 CST; 10s ago
  Process: 655712 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 655672 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE)
  Process: 655635 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 655610 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 655672 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 98 (Address already in use)

Sep 12 18:58:34 iZ56kkvaq4nlfhZ systemd[1]: Starting MySQL 8.0 database server...
Sep 12 18:58:37 iZ56kkvaq4nlfhZ systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
Sep 12 18:58:37 iZ56kkvaq4nlfhZ systemd[1]: mysqld.service: Failed with result 'exit-code'.
Sep 12 18:58:37 iZ56kkvaq4nlfhZ systemd[1]: Failed to start MySQL 8.0 database server.
[root@iZ56kkvaq4nlfhZ etc]# 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    此时，可以查询是谁占用了3306，然后把占用进程kill掉，此处就可以使用命令kill 57039，但是我不想这么做，这个进程和我的一个服务有关，不能杀

    [root@iZ56kkvaq4nlfhZ etc]# lsof -i :3306
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  57039  www   35u  IPv6 149967      0t0  TCP *:mysql (LISTEN)
[root@iZ56kkvaq4nlfhZ etc]# netstat -anp | grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      57039/mysqld 

    • 1
    • 2
    • 3
    • 4
    • 5

    于是，我选择修改MySQL的启动端口，将启动端口修改为3307，编辑下面的配置文件

     vim /etc/my.cnf.d/mysql-server.cnf

    • 1

    添加一行内容，如下：

    port=3307

    • 1

    此时配置文件就变成了：

    [root@iZ56kkvaq4nlfhZ ~]# cat /etc/my.cnf.d/mysql-server.cnf 
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
port=3307    # 添加这一行，端口修改为3307

[root@iZ56kkvaq4nlfhZ ~]# 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    然后重启MySQL，就可以正常启动了

    [root@iZ56kkvaq4nlfhZ etc]# systemctl restart mysqld.service 
[root@iZ56kkvaq4nlfhZ etc]# systemctl status mysqld.service 
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor prese>   Active: active (running) since Tue 2023-09-12 19:09:51 CST; 16min ago
  Process: 655712 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=>  Process: 656204 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, st>  Process: 656122 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (>  Process: 656097 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, stat> Main PID: 656159 (mysqld)
   Status: "Server is operational"
    Tasks: 37 (limit: 10836)
   Memory: 354.7M
   CGroup: /system.slice/mysqld.service
           └─656159 /usr/libexec/mysqld --basedir=/usr

Sep 12 19:09:50 iZ56kkvaq4nlfhZ systemd[1]: Starting MySQL 8.0 database server...
Sep 12 19:09:51 iZ56kkvaq4nlfhZ systemd[1]: Started MySQL 8.0 database server.

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    修改MySQL的密码，修改密码之后，再登录MySQL需要使用密码。

    mysql  # 进入MySQL命令行

    • 1
    # MySQL的用户名密码存储在这个数据库中，修改密码，必须使用这个数据库
mysql> use mysql;   
# 查看数据库中的用户
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
# 修改'root'@'localhost'的密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '12345';
Query OK, 0 rows affected (0.01 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
# 此时端口开放3307
mysql> show variables like '%port%' ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| admin_port               | 33062 |
| large_files_support      | ON    |
| mysqlx_port              | 33060 |
| mysqlx_port_open_timeout | 0     |
| port                     | 3307  |
| report_host              |       |
| report_password          |       |
| report_port              | 3307  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
10 rows in set (0.01 sec)

mysql> exit
Bye

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    设置MySQL监听所有IP，更改配置文件后重启MySQL

    [root@iZ56kkvaq4nlfhZ etc]# vim /etc/my.cnf.d/mysql-server.cnf 
[root@iZ56kkvaq4nlfhZ etc]# cat /etc/my.cnf.d/mysql-server.cnf
#
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
port=3307
bind-address=0.0.0.0   # 添加这一行
[root@iZ56kkvaq4nlfhZ etc]#

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    [root@iZ56kkvaq4nlfhZ etc]# systemctl restart mysqld.service 
[root@iZ56kkvaq4nlfhZ etc]# systemctl status mysqld.service 
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-09-12 20:56:18 CST; 11s ago
  Process: 657283 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 657493 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 657411 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 657385 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 657448 (mysqld)
   Status: "Server is operational"
    Tasks: 38 (limit: 10836)
   Memory: 356.1M
   CGroup: /system.slice/mysqld.service
           └─657448 /usr/libexec/mysqld --basedir=/usr

Sep 12 20:56:17 iZ56kkvaq4nlfhZ systemd[1]: Starting MySQL 8.0 database server...
Sep 12 20:56:18 iZ56kkvaq4nlfhZ systemd[1]: Started MySQL 8.0 database server.
[root@iZ56kkvaq4nlfhZ etc]#

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    创建一个可以远程连接的root用户

    CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;


    • 1
    • 2
    • 3
    • 4

    二、常用命令

    1、创建数据库

    先登录数据库

    [root@iZ56kkvaq4nlfhZ ~]# mysql -u root -p
Enter password:     # 这里输入数据库密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
.........
.........

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    然后，我们查看一下MySQL中有哪些数据库，命令如下：

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    现在创建一个名为 game的数据库

    mysql> create database game;
Query OK, 1 row affected (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4

    此时再查看一下数据库，会发现多了一个game

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| game               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    2、删除数据库

    现在删除刚才新建的数据库game

    mysql> DROP DATABASE game;
Query OK, 0 rows affected (0.01 sec)

    • 1
    • 2

    此时再查看，game已被删除

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    3、创建表

      在MySQL中，数据类型定义了每个列可以存储的数据类型。以下是一些主要的数据类型
      1、字符串数据类型：包括CHAR，VARCHAR，TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT和BLOB。CHAR和VARCHAR都用于存储非二进制字符串，但CHAR存储固定长度的字符串（最大255个字符），而VARCHAR存储可变长度的字符串（最大65535个字符）。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT用于存储大量文本数据，它们之间的主要区别在于它们可以存储的文本的最大长度32。BLOB用于存储二进制对象，通常用于存储文件、图像等多媒体文件
       2、数值数据类型：包括整数类型（如INTEGER，SMALLINT），定点类型（如DECIMAL，NUMERIC），浮点类型（如FLOAT，DOUBLE）和位值类型（如BIT）。整数类型用于存储整数值，定点类型用于存储精确的小数值，浮点类型用于存储近似的小数值，位值类型用于存储位值。
      3、日期和时间数据类型：包括DATE，TIME，DATETIME和TIMESTAMP。DATE用于存储日期部分但没有时间部分的值，TIME用于存储时间部分但没有日期部分的值，DATETIME和TIMESTAMP都用于存储包含日期和时间部分的值
      4、空间数据类型：MySQL支持OpenGIS类对应的空间数据类型。这些类型包括单一几何值的类型（如GEOMETRY，POINT，LINESTRING和POLYGON）以及集合值的类型（如MULTIPOINT，MULTILINESTRING，MULTIPOLYGON和GEOMETRYCOLLECTION）。
      5、JSON数据类型：MySQL从5.7.8版本开始支持原生JSON数据类型。这种数据类型允许您更有效地存储JSON文档。

    先新建一个数据库名为game

    mysql> CREATE DATABASE game;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| game               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    选择使用game数据库

    mysql> use game;
Database changed
mysql>

    • 1
    • 2
    • 3

    在game数据库中新建一个名为player的表，其中id为int类型，姓名字段name为varchar类型，等等

    mysql> create table player (id int,name varchar(100),level int, exp int,glod decimal(10,2));
Query OK, 0 rows affected (0.02 sec)
 
mysql> show tables;  # 查看数据库中的表
+----------------+
| Tables_in_game |
+----------------+
| player         |
+----------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    使用desc语句查看表

    mysql> desc player;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int           | YES  |     | NULL    |       |
| name  | varchar(100)  | YES  |     | NULL    |       |
| level | int           | YES  |     | NULL    |       |
| exp   | int           | YES  |     | NULL    |       |
| glod  | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    4、修改表

    修改字段类型
    此时，我发现player表中，name字段的长度不够，想增加长度，这时可以使用alter语句来修改表结构
    alter table后加表名，modify column表示修改列，后面加列名，varchar(200)是修改后的name字段的长度。

    mysql> alter table player modify column name varchar(200);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc player;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int           | YES  |     | NULL    |       |
| name  | varchar(200)  | YES  |     | NULL    |       |
| level | int           | YES  |     | NULL    |       |
| exp   | int           | YES  |     | NULL    |       |
| glod  | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    修改字段名称
    字段的名称也可以修改，此时我要把name修改为nick_name
    alter table后加表名, rename column后加需要修改的字段名，to 后加修改后的字段名。

    mysql> alter table player rename column name to nick_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc player;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  |     | NULL    |       |
| nick_name | varchar(200)  | YES  |     | NULL    |       |
| level     | int           | YES  |     | NULL    |       |
| exp       | int           | YES  |     | NULL    |       |
| glod      | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    添加字段。
    在表中添加一个last_login字段，用来记录上次登录游戏的时间。alter table后加表名，add column后加即将被添加的字段名称，后面跟这个字段的数据类型，此处数据类型是datetime

    mysql> alter table player add column last_login datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc player;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | YES  |     | NULL    |       |
| nick_name  | varchar(200)  | YES  |     | NULL    |       |
| level      | int           | YES  |     | NULL    |       |
| exp        | int           | YES  |     | NULL    |       |
| glod       | decimal(10,2) | YES  |     | NULL    |       |
| last_login | datetime      | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    删除字段。
    将刚刚添加的last_login字段删除。
    alter table后加表名，drop column后加需要删除的字段名。

    mysql> alter table player drop column last_login;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc player;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  |     | NULL    |       |
| nick_name | varchar(200)  | YES  |     | NULL    |       |
| level     | int           | YES  |     | NULL    |       |
| exp       | int           | YES  |     | NULL    |       |
| glod      | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    删除表
    删除表player，drop table后加需要删除的表的名称。

    mysql> drop table player;
Query OK, 0 rows affected (0.01 sec)

# 此时再查看表player，就会提示错误，表不存在
mysql> desc player;  
ERROR 1146 (42S02): Table 'game.player' doesn't exist
mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5、操作表中的数据(数据的增删改查)

    把删除的表player再新建回来。

    mysql> create table player (id int,name varchar(100),level int, exp int,glod decimal(10,2));
Query OK, 0 rows affected (0.02 sec)

mysql> desc player;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int           | YES  |     | NULL    |       |
| name  | varchar(100)  | YES  |     | NULL    |       |
| level | int           | YES  |     | NULL    |       |
| exp   | int           | YES  |     | NULL    |       |
| glod  | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    插入数据
    在表player中插入一条数据，insert into后加表名，后面再跟字段名，values后加要插入的信息。

    mysql> insert into player (id, name, level, exp, glod) values (1,' 张三', 1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4

    没有列出的字段名将会以默认值填充。下面这条命令只插入了id, name, level这三个字段的数据，剩下的exp和glod字段将被默认值填充。

    mysql> insert into player (id, name, level) values (3, '王五', 0);
Query OK, 1 row affected (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4

    如果字段名和要插入的信息一一对应，那么字段名可以省略。

    mysql> insert into player values (2,'李四', 2, 2, 2);
Query OK, 1 row affected (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4

    使用insert关键字，可以一次插入多条数据，多条数据之间用,隔开。

    mysql> insert into player (id, name) values (4,'Tom'),(5,'Jerry');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

    • 1
    • 2
    • 3

    查询数据
    查看刚才插入的数据，select后面跟要查询的列名，* 代表查询所有列，from后加要查询的表名，表示从哪个表来查询数据。

    mysql> select * from player;
+------+--------+-------+------+------+
| id   | name   | level | exp  | glod |
+------+--------+-------+------+------+
|    1 | 张三   |     1 |    1 | 1.00 |
|    2 | 李四   |     2 |    2 | 2.00 |
|    3 | 王五   |     0 | NULL | NULL |
|    4 | Tom    |  NULL | NULL | NULL |
|    5 | Jerry  |  NULL | NULL | NULL |
+------+--------+-------+------+------+
5 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    建表时，没有指定默认值，所以现在的默认值是NULL，现在修改表结构，为其中level字段指定默认值。default后面跟默认值

    mysql> alter table player modify level int default 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5

    此时再插入一条数据，看看level的默认值是否生效

    mysql> insert into player (id, name) values (6,'菜虚鲲');
Query OK, 1 row affected (0.00 sec)

mysql> select * from player;
+------+-----------+-------+------+------+
| id   | name      | level | exp  | glod |
+------+-----------+-------+------+------+
|    1 | 张三      |     1 |    1 | 1.00 |
|    2 | 李四      |     2 |    2 | 2.00 |
|    3 | 王五      |     0 | NULL | NULL |
|    4 | Tom       |  NULL | NULL | NULL |
|    5 | Jerry     |  NULL | NULL | NULL |
|    6 | 菜虚鲲    |     1 | NULL | NULL |
+------+-----------+-------+------+------+
6 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    更新数据
    现在Tom和Jerry的level还是NULL，可以使用update关键字来让他们的level变为1。
    updata后加表名，set后加字段名，=后加修改后的值，where后加需要修改的数据的唯一特征。

    mysql> update player set level = 1 where name = 'Tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update player set level = 1 where name = 'Jerry';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from player;
+------+-----------+-------+------+------+
| id   | name      | level | exp  | glod |
+------+-----------+-------+------+------+
|    1 | 张三      |     1 |    1 | 1.00 |
|    2 | 李四      |     2 |    2 | 2.00 |
|    3 | 王五      |     0 | NULL | NULL |
|    4 | Tom       |     1 | NULL | NULL |
|    5 | Jerry     |     1 | NULL | NULL |
|    6 | 菜虚鲲    |     1 | NULL | NULL |
+------+-----------+-------+------+------+
6 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    这样每次修改一条数据太低效，把where子句去掉就可以对全部数据生效了。现在把所有玩家的exp和gold的都改为0，多个字段之间用,隔开。

    mysql> update player set exp = 0, glod = 0;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from player;
+------+-----------+-------+------+------+
| id   | name      | level | exp  | glod |
+------+-----------+-------+------+------+
|    1 | 张三      |     1 |    0 | 0.00 |
|    2 | 李四      |     2 |    0 | 0.00 |
|    3 | 王五      |     0 |    0 | 0.00 |
|    4 | Tom       |     1 |    0 | 0.00 |
|    5 | Jerry     |     1 |    0 | 0.00 |
|    6 | 菜虚鲲    |     1 |    0 | 0.00 |
+------+-----------+-------+------+------+
6 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    删除数据
    删除gold为0的玩家，然后所有的玩家都被删除了。
    delete from后加表名，where后加筛选条件。

    mysql> delete from player where glod = 0;
Query OK, 6 rows affected (0.00 sec)

mysql> select * from player;
Empty set (0.00 sec)

mysql> exit;     # 退出数据库
Bye

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    6、数据的导入导出

    数据库的导出
    导出数据库game在CnetOS命令行输入以下命令，mysqldump -u root -p后加数据库名和表名，>后加导出到的目录和文件名。表名可以省略不写，这样会将数据中的所有数据都导出

    [root@iZ56kkvaq4nlfhZ ~]# mysqldump -u root -p game player > game.sql
Enter password: 
[root@iZ56kkvaq4nlfhZ ~]# ls 
14290                          game.sql    pikachu
frp_0.44.0_linux_amd64.tar.gz  index.html  sqli-labs
[root@iZ56kkvaq4nlfhZ ~]# 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    查看文件game.sql的内容，发现里面是一条条的sql语句，是建立这个数据库用的

    [root@iZ56kkvaq4nlfhZ ~]# cat game.sql 
-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost    Database: game
-- ------------------------------------------------------
-- Server version       8.0.26

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `player`
--

DROP TABLE IF EXISTS `player`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `player` (
  `id` int DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `level` int DEFAULT '1',
  `exp` int DEFAULT NULL,
  `glod` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `player`
文件内容太多，省略了

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    数据库的导入
    在Linux命令行，将刚才导出的数据库game导入。在导入sql文件之前，要确保已经存在一个数据库，数据库的名字和sql文件的名字相同。
    -u指定数据库的用户名，-p指定数据库密码，mysql -u root -p 后加要导入的数据库的名字，<后加需要被导进去的sql文件

    [root@iZ56kkvaq4nlfhZ ~]# mysql -u root -p game 
Enter password: 
[root@iZ56kkvaq4nlfhZ ~]# 

    • 1
    • 2
    • 3

    把练习用的的数据库导进MySQL中。需要用到的数据库放在下面了。

    链接：https://pan.baidu.com/s/1tKT4I_O4m66iBwXKRFVz9Q?pwd=ti80
    提取码：ti80

    我将练习用的sql文件放在sql_dir 文件夹中，一会要登录数据库创建和sql文件同名的数据库

    [root@iZ56kkvaq4nlfhZ buqian_log]# cd
[root@iZ56kkvaq4nlfhZ ~]# ls sql_dir/
city_data.sql  game.sql    sale.sql  world.sql
company.sql    sakila.sql  shop.sql
[root@iZ56kkvaq4nlfhZ ~]#

    • 1
    • 2
    • 3
    • 4
    • 5

    进入MySQL，删除数据库game中的数据，以便稍后导入game.sql

    mysql> use game;
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> drop table player;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    创建和sql文件同名的数据库

    mysql> create database city_data;
Query OK, 1 row affected (0.00 sec)

mysql> create database sale;
Query OK, 1 row affected (0.01 sec)

mysql> create database world;
Query OK, 1 row affected (0.01 sec)

mysql> create database company;
Query OK, 1 row affected (0.00 sec)

mysql> create database sakila;
Query OK, 1 row affected (0.01 sec)

mysql> create database shop;
Query OK, 1 row affected (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| city_data          |
| company            |
| game               |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sale               |
| shop               |
| sys                |
| world              |
+--------------------+
11 rows in set (0.00 sec)

mysql> exit;
Bye 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    导入 sql文件

    [root@iZ56kkvaq4nlfhZ ~]# cd sql_dir/
[root@iZ56kkvaq4nlfhZ sql_dir]# ls
city_data.sql  game.sql    sale.sql  world.sql
company.sql    sakila.sql  shop.sql
[root@iZ56kkvaq4nlfhZ sql_dir]# 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p city_data < city_data.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p game < game.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p sale < sale.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p world < world.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p company < company.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p sakila < sa
sakila.sql  sale.sql    
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p sakila < sakila.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# mysql -u root -p shop < shop.sql 
Enter password: 
[root@iZ56kkvaq4nlfhZ sql_dir]# 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    三、 常用语句

    1、where子句

    where子句用来查找哪些满足指定标准的记录，可以同select，update，delete一起使用。
    比较运算符
    查找表player中level为1的玩家。除了等号之外，其他的比较运算符（<、>、>=、<=、!=等）也可以使用。

    mysql> select * from player where level=1;
+------+-----------+------+---------------------+-------+------+-------+
| id   | name      | sex  | email               | level | exp  | gold  |
+------+-----------+------+---------------------+-------+------+-------+
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com  |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net |     1 |   61 | 90.00 |
+------+-----------+------+---------------------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    逻辑运算符
    使用and连接多个条件
    查找level>1且level<5的玩家。除了and之外还可以使用not和or，当三者同时使用时，其优先级是not > and > or，也可以shiyong()来改变优先级

    mysql> select * from player where level > 1 and level < 5;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
+------+-----------+------+-----------------------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    查找level大于1小于5或exp大于1小于5的玩家

    mysql> select * from player where level > 1 and level < 5 or exp > 1 and exp < 5;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com      |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net   |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|   19 | 佟湘玉    | 女   | tongxiangyu@163.com     |    99 |    4 | 10.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
+------+-----------+------+-------------------------+-------+------+-------+
6 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    in 指定多个值
    查找level为1、3、5的玩家

    mysql> select * from player where level in (1,3,5);
+------+-----------+------+---------------------+-------+------+-------+
| id   | name      | sex  | email               | level | exp  | gold  |
+------+-----------+------+---------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com  |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com  |     1 |    2 |  3.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com  |     5 |   49 | 38.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net |     1 |   61 | 90.00 |
+------+-----------+------+---------------------+-------+------+-------+
4 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    between and来指定某个字段查找范围
    查找level在1到10之间的玩家（包括1和10）

    这条语句等价于level >= 1 and level <= 10;
mysql> select * from player where level between 1 and 10;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   11 | 胡一菲    | 女   | huyifei@163.com       |     6 |   53 | 38.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|   31 | 周伯通    | 男   | zhoubotong@163.com    |     8 |   45 | 70.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com    |     5 |   49 | 38.00 |
|   49 | 安欣      | 男   | anxin@gmail.com       |     8 |   43 | 33.00 |
|   87 | 小鱼儿    | 男   | xiaoyuer@163.com      |     6 |   55 |  4.00 |
|   91 | 余则成    | 男   | yuzecheng@163.com     |    10 |   53 | 29.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
+------+-----------+------+-----------------------+-------+------+-------+
10 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    not 取反
    查找等级不在1到10之间的玩家，not可以加在任何一个条件语句之前。

    mysql> select * from player where level between 1 and 10;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   11 | 胡一菲    | 女   | huyifei@163.com       |     6 |   53 | 38.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    like 模糊查询
    查找姓王，或名字中带王的玩家，可以使用%和_来进行匹配，%表示任意个字符，_任意一个字符。

    名字第一个字是“王”，后面是任意多个字符。
mysql> select * from player where name like "王%";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   54 | 王语嫣    | 女   | wangyuyan@geekhour.net    |    71 |   97 | 85.00 |
|   82 | 王小蒙    | 女   | wangxiaomeng@geekhour.net |    95 |   77 | 44.00 |
|  169 | 王昭君    | 女   | wangzhaojun@gmail.com     |    96 |   48 | 11.00 |
+------+-----------+------+---------------------------+-------+------+-------+
6 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    regexp 正则匹配
    使用正则表达式进行匹配。

    . ：号表示匹配一个字符。
    ^ ：匹配开头
    $ ：匹配结尾
    [abc] ：匹配其中任意一个字符
    [a-z] ：匹配范围内的任意一个字符
    A|B ：|表示或者匹配A或B

    查找姓王，并且名字为两个字的玩家

    mysql> select * from player where name regexp "^王.$";
+------+--------+------+----------------+-------+------+-------+
| id   | name   | sex  | email          | level | exp  | gold  |
+------+--------+------+----------------+-------+------+-------+
|    3 | 王五   | 女   | wangwu@163.com |    64 |   38 | 15.00 |
+------+--------+------+----------------+-------+------+-------+
1 row in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    匹配姓王或姓张的玩家

    匹配条件也可以换成 [王张]
即 select * from player where name regexp "[王张]";
mysql> select * from player where name regexp "王|张";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com        |     3 |   19 | 20.00 |
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   15 | 张伟      | 男   | zhangwei@163.com          |    23 |   73 | 73.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   41 | 张无忌    | 男   | zhangwuji@gmail.com       |    26 |   20 | 26.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    查找邮件地址以“zhangsan”开头的玩家

    mysql> select * from player where email regexp "^zhangsan";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com        |     3 |   19 | 20.00 |
|   46 | 张三丰    | 男   | zhangsanfeng@geekhour.net |    13 |   79 | 12.00 |
+------+-----------+------+---------------------------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    查找邮件地址以a/b/c开头的玩家

    mysql> select * from player where email regexp "^[abc]";
+------+--------------+------+--------------------------+-------+------+--------+
| id   | name         | sex  | email                    | level | exp  | gold   |
+------+--------------+------+--------------------------+-------+------+--------+
|   14 | 陈美嘉       | 女   | chenmeijia@geekhour.net  |    95 |   71 |  66.00 |
|   20 | 白展堂       | 男   | baizhantang@qq.com       |    89 |   85 |  13.00 |
|   49 | 安欣         | 男   | anxin@gmail.com          |     8 |   43 |  33.00 |
|   55 | 阿朱         | 女   | azhu@163.com             |    79 |   81 |  65.00 |
|   56 | 阿紫         | 女   | azi@qq.com               |    91 |   60 |  56.00 |
|   57 | 阿碧         | 女   | abi@gmail.com            |   100 |    9 |  92.00 |
|   88 | 包青天       | 男   | baoqingtian@qq.com       |    28 |   60 |   4.00 |
|  104 | 艾琳         | 女   | ailin@qq.com             |    86 |   73 |  22.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    查找邮件地址以“.net”结尾的玩家

    此处也可以使用like： select * from player where email like "%.net";
mysql> select * from player where email regexp ".net$";
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|    6 | 马大帅       | 男   | madashuai@geekhour.net     |    87 |   98 |  98.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net      |    98 |   51 |  62.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    null 空值
    null值与任何值都不相等，包括null本身，所以使用null值进行判断的时候不能使用=，而要使用is。
    查找邮箱为空的玩家

    mysql> select * from player where email is null;
+------+-----------+------+-------+-------+------+-------+
| id   | name      | sex  | email | level | exp  | gold  |
+------+-----------+------+-------+-------+------+-------+
|   12 | 吕子乔    | 男   | NULL  |    36 |  100 | 46.00 |
|   13 | 吕小布    | 男   | NULL  |    81 |   88 | 25.00 |
+------+-----------+------+-------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    查找填写了邮箱的玩家，可以使用is not null来判断

    mysql> select * from player where email is not null;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    MySQL中还提供一个专门用来比较null值的比较操作符：<=>，但是使用is来判断更直观。is在其他数据库也是通用的

    mysql> select * from player where email <=> null;
+------+-----------+------+-------+-------+------+-------+
| id   | name      | sex  | email | level | exp  | gold  |
+------+-----------+------+-------+-------+------+-------+
|   12 | 吕子乔    | 男   | NULL  |    36 |  100 | 46.00 |
|   13 | 吕小布    | 男   | NULL  |    81 |   88 | 25.00 |
+------+-----------+------+-------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    null值用is来判断，而空字符串要用=来判断。null表示没有填写值，而空字符串表示填写了个空的值
    查询email为空字符串或为null的玩家

    mysql> select * from player where email = "" or email is null;
+------+-----------+------+-------+-------+------+-------+
| id   | name      | sex  | email | level | exp  | gold  |
+------+-----------+------+-------+-------+------+-------+
|   12 | 吕子乔    | 男   | NULL  |    36 |  100 | 46.00 |
|   13 | 吕小布    | 男   | NULL  |    81 |   88 | 25.00 |
|  190 | 吕布      | 男   |       |    77 |   43 | 31.00 |
+------+-----------+------+-------+-------+------+-------+
3 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    2、order by排序

    order by用来对查询结果按照某个字段进行排序。
    按照等级level升序排列（默认是升序，也可以加一个ASC）

    mysql> select * from player order by level;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   17 | 吕秀才       | 男   | lvxiucai@gmail.com         |     1 |    2 |   3.00 |
|  131 | 弈星         | 男   | yixing@geekhour.net        |     1 |   61 |  90.00 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
。。。。。。。。。省略。。。。。。。。。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    如果想要降序排列，则在语句后面加上DESC

    mysql> mysql> select * from player order by level desc;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   57 | 阿碧         | 女   | abi@gmail.com              |   100 |    9 |  92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com        |    99 |    4 |  10.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    按照多个字段排序，字段之间用, 分割。
    按照level降序排列，等级相同时按exp升序排列。

    mysql> select * from player order by level desc,exp;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   57 | 阿碧         | 女   | abi@gmail.com              |   100 |    9 |  92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com        |    99 |    4 |  10.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    也可以按照字段序号来排序，字段level是第5列，所以按照level降序排列可以写成：

    mysql> select * from player order by 5 desc;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   57 | 阿碧         | 女   | abi@gmail.com              |   100 |    9 |  92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    3、聚合函数

    聚合函数用来对某列执行一些计算，比如求和、平均值、最大值、最小值等等。

    MySQL中有5种常用的聚合函数：
      1、AVG()：只适用于数值类型的字段或变量，不包含NULL值。它用于计算指定字段的平均值。
      2、SUM()：只适用于数值类型的字段或变量，不包含NULL值。它用于计算指定字段的总和。
      3、 MAX()：适用于数值类型、字符串类型、日期时间类型的字段（或变量），不包含NULL值。它用于获取指定字段在分组中的最大值。
      4、 MIN()：适用于数值类型、字符串类型、日期时间类型的字段（或变量），不包含NULL值。它用于获取指定字段在分组中的最小值。
      5、 COUNT()：计算指定字段在查询结构中出现的个数（不包含NULL值）。它可以统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。
       这些聚合函数通常与GROUP BY子句一起使用，以便对数据进行分组处理。例如，你可以使用GROUP BY子句将表中的数据分成若干组，然后对每个组应用聚合函数。

    计算表player中玩家的总人数

    mysql> select count(*) from player;
+----------+
| count(*) |
+----------+
|      209 |
+----------+
1 row in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    查看玩家平均等级level

    mysql> select avg(level) from player;
+------------+
| avg(level) |
+------------+
|    55.0813 |
+------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    查看玩家金币gold的最小值

    mysql> select min(gold) from player;
+-----------+
| min(gold) |
+-----------+
|      1.00 |
+-----------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    其他两个函数用法与此类似。

    4、GROUP BY 分组

    使用group by对查询结果进行分组，后面加上一个或多个列名。
    查看表中的男女人数

    以count(*)和count(sex)为例，总结COUNT(列名)和COUNT(*)的区别：
      SELECT sex, COUNT(sex) FROM player GROUP BY sex;：这条语句会返回每个sex和对应的player数量。这里的COUNT(sex)只会计算sex字段非NULL的记录数。所以，你看到的结果中，NULL的计数是0，因为COUNT(sex)不会计算NULL值。

      SELECT sex, COUNT(*) FROM player GROUP BY sex;：这条语句也会返回每个sex和对应的player数量。但是，这里的COUNT(*)会计算所有的记录数，包括sex字段为NULL的记录。所以，你看到的结果中，NULL的计数是3，因为在你的数据中有3条记录的sex字段是NULL。

      总结一下，当你使用COUNT(列名)时，它只会计算那些列值非NULL的记录数。而当你使用COUNT(*)时，它会计算所有的记录数，包括那些指定列值为NULL的记录。

    mysql> select sex, count(*) from player group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| 男   |      140 |
| 女   |       65 |
| NULL |        3 |
|      |        1 |
+------+----------+
4 rows in set (0.00 sec)

mysql> select sex, count(sex) from player group by sex;
+------+------------+
| sex  | count(sex) |
+------+------------+
| 男   |        140 |
| 女   |         65 |
| NULL |          0 |
|      |          1 |
+------+------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    查看表中每个等级的玩家数量

    mysql> select level, count(level) from player group by level;
+-------+--------------+
| level | count(level) |
+-------+--------------+
|     3 |            1 |
|     4 |            1 |
|    64 |            3 |
|    30 |            1 |
|    95 |            5 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    5、GROUP BY 和 HAVING

    group by 经常和having一起使用，用来对分组后的结果进行筛选

    将表player中的数据按等级level进行分组，只保留level大于4的结果

    # having后面加条件，用来对结果进行筛选
mysql> select level, count(level) from player group by level having level > 4;
+-------+--------------+
| level | count(level) |
+-------+--------------+
|    64 |            3 |
|    30 |            1 |
|    95 |            5 |
|    87 |            2 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    将表player中的数据按等级level进行分组，只保留level大于4的结果，并按照等级数量降序排序。

    mysql> select level, count(level) from player group by level having level > 4 order by count(level) desc;
+-------+--------------+
| level | count(level) |
+-------+--------------+
|    13 |            9 |
|    96 |            7 |
|    95 |            5 |
|    88 |            5 |
|    54 |            5 |
|    97 |            4 |
|    38 |            4 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    统计玩家中每个姓氏的数量，将结果按照数量来降序排列，只显示数量大于等于5的姓氏。
    完成这个查询需要使用函数SUBSTR用来截取字段、字符串的一部分。
    SUBSTR(字段名,开始位置,截取长度)，开始位置从1开始计数。

    mysql> select substr(name,1,1),count(substr(name,1,1)) from player group by substr(name,1,1) having count(substr(name,1,1)) >= 5 order by count(substr(name,1,1)) desc;
+------------------+-------------------------+
| substr(name,1,1) | count(substr(name,1,1)) |
+------------------+-------------------------+
| 李               |                      11 |
| 张               |                       8 |
| 王               |                       6 |
| 小               |                       5 |
| 阿               |                       5 |
+------------------+-------------------------+
5 rows in set (0.00 sec)

# 如果只想返回上一步查询结果的前三名，可以加个LIMT 3 来实现
mysql>
mysql> select substr(name,1,1),count(substr(name,1,1)) from player group by substr(name,1,1) having count(substr(name,1,1)) >= 5 order by count(substr(name,1,1)) desc limit 3;
+------------------+-------------------------+
| substr(name,1,1) | count(substr(name,1,1)) |
+------------------+-------------------------+
| 李               |                      11 |
| 张               |                       8 |
| 王               |                       6 |
+------------------+-------------------------+
3 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    LIMIT 还可以指定一个偏移量，返回排名第四名到第六名的姓氏，此时去掉having子句，因为如果加上having子句，只会返回5个结果。limit后的第一个3表示偏移量，从第四个开始，第二个3
    表示返回的数量。

    mysql> select substr(name,1,1),count(substr(name,1,1)) from player group by substr(name,1,1) order by count(substr(name,1,1)) desc limit 3,3;
+------------------+-------------------------+
| substr(name,1,1) | count(substr(name,1,1)) |
+------------------+-------------------------+
| 阿               |                       5 |
| 小               |                       5 |
| 马               |                       4 |
+------------------+-------------------------+
3 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    在MySQL的SELECT语句中，你不仅可以选择字段名，还可以使用各种表达式和函数。以下是一些例子：
    1、算术表达式：例如，SELECT price * quantity AS total FROM orders;这将返回订单的总价（价格乘以数量）。
    2、 字符串表达式：例如，SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; 这将返回用户的全名（名和姓之间用空格分隔）。
    3、日期和时间表达式：例如，SELECT DATE_ADD(birth_date, INTERVAL 1 YEAR) AS next_birthday FROM users; 这将返回用户下一次生日的日期。
    4、 条件表达式：例如，SELECT IF(score >= 60, 'Pass', 'Fail') AS result FROM exams; 这将返回考试结果（如果分数大于或等于60，则为"Pass"，否则为"Fail"）。
    5、 聚合函数：例如，SELECT COUNT(*) FROM users; 这将返回用户表中的记录数。
    6、子查询：例如，SELECT (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count FROM users; 这将返回每个用户的订单数。

    6、DISTINCT 去重

    DISTINCT关键字可以用来去除重复的记录
    查询所有玩家的性别

    mysql> select distinct sex from player;
+------+
| sex  |
+------+
| 男   |
| 女   |
| NULL |
|      |
+------+
4 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    7、UNION 合并查询结果

    查询level为1-3的玩家

    mysql> select * from player where level between 1 and 3;
+------+-----------+------+---------------------+-------+------+-------+
| id   | name      | sex  | email               | level | exp  | gold  |
+------+-----------+------+---------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com  |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com  |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net |     1 |   61 | 90.00 |
+------+-----------+------+---------------------+-------+------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    查询exp为1-3的玩家

    mysql> select * from player where exp between 1 and 3;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
|  145 | 大乔      | 女   | daqiao@gmail.com        |    87 |    1 | 82.00 |
+------+-----------+------+-------------------------+-------+------+-------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    将这两条查询结果使用UNION合并，UNION默认会去除重复的记录。玩家“吕秀才”就被去重的，他既满足level1-3，又满足exp1-3，所以合并的时候就被去掉了一条。

    mysql> select * from player where level between 1 and 3
    -> union
    -> select * from player where exp between 1 and 3;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com      |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net     |     1 |   61 | 90.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
|  145 | 大乔      | 女   | daqiao@gmail.com        |    87 |    1 | 82.00 |
+------+-----------+------+-------------------------+-------+------+-------+
6 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    如果不想去重，可以使用UNION ALL 来合并。

    mysql> select * from player where level between 1 and 3 union all select * from player where exp between 1 and 3;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com      |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net     |     1 |   61 | 90.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
|  145 | 大乔      | 女   | daqiao@gmail.com        |    87 |    1 | 82.00 |
+------+-----------+------+-------------------------+-------+------+-------+
7 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    8、INTERSECT 合并结果集（交集）

    INTERSECT在MySQL 8.0.31之后开始支持
    查找level1-3和exp1-3的玩家，将结果取交集

    mysql> select * from player where exp between 1 and 3
    -> intersect 
    -> select * from player where level between 1 and 3;

    • 1
    • 2
    • 3

    9、EXCEPT 合并结果集（差集）

    EXCEPT在MySQL 8.0.31之后开始支持
    EXCEPT查找两个结果的差集，查找等级level为1-3的玩家但经验exp不在1-3的玩家。

    mysql> select * from player where exp between 1 and 3
    -> except 
    -> select * from player where level between 1 and 3;

    • 1
    • 2
    • 3

    四、子查询

    子查询，也被称为内部查询或嵌套查询，是指在一条SELECT语句中嵌入了另外一条SELECT语句，子查询的结果可以作为另一个SQL语句（主查询）的数据来源或者判断条件。
    查询玩家的平均等级

    1、使用子查询查询数据

    mysql> select avg(level) from player;
+------------+
| avg(level) |
+------------+
|    55.0813 |
+------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    查询所有等级大于平均等级的玩家

    mysql> select * from player where level > (select avg(level) from player);
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |
|    5 | 范德彪       | 男   | fandebiao@gmail.com        |    95 |   89 |  44.00 |
|    6 | 马大帅       | 男   | madashuai@geekhour.net     |    87 |   98 |  98.00 |
|    7 | 王小二       | 男   | wangxiaoer@163.com         |    70 |   30 |  54.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net      |    98 |   51 |  62.00 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    查询所有玩家等级和平均等级之间的差值，ROUND函数可以四舍五入。

    mysql> select level,round((select avg(level) from player)), level - round((select avg(level) from player))  from player;
+-------+----------------------------------------+------------------------------------------------+
| level | round((select avg(level) from player)) | level - round((select avg(level) from player)) |
+-------+----------------------------------------+------------------------------------------------+
|     3 |                                     55 |                                            -52 |
|     4 |                                     55 |                                            -51 |
|    64 |                                     55 |                                              9 |
|    30 |                                     55 |                                            -25 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    结果的后两列列名直接用了表达式，这样的结果不方便阅读，可以使用AS关键字给列起别名。

    mysql> select level,round((select avg(level) from player)) as average, level - round((select avg(level) from player)) as diff  from player;
+-------+---------+------+
| level | average | diff |
+-------+---------+------+
|     3 |      55 |  -52 |
|     4 |      55 |  -51 |
|    64 |      55 |    9 |
|    30 |      55 |  -25 |
|    95 |      55 |   40 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    子查询可以在UPDATE、DELETE、CREATE、INSERT等各种语句中使用。

    2、使用子查询创建表

    使用子查询来创建一个新表。将所有等级小于5的玩家插入到一个新表player2中

    mysql> create table player2 select * from player where level < 5;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from player2;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
+------+-----------+------+-----------------------+-------+------+-------+
4 rows in set (0.01 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    3、使用子查询插入数据

    将表player中，等级level在6-10的玩家插入到player2中。
    可以先查询下level在6-10的玩家。

    mysql> select * from player where level between 6 and 10;
+------+-----------+------+--------------------+-------+------+-------+
| id   | name      | sex  | email              | level | exp  | gold  |
+------+-----------+------+--------------------+-------+------+-------+
|   11 | 胡一菲    | 女   | huyifei@163.com    |     6 |   53 | 38.00 |
|   31 | 周伯通    | 男   | zhoubotong@163.com |     8 |   45 | 70.00 |
|   49 | 安欣      | 男   | anxin@gmail.com    |     8 |   43 | 33.00 |
|   87 | 小鱼儿    | 男   | xiaoyuer@163.com   |     6 |   55 |  4.00 |
|   91 | 余则成    | 男   | yuzecheng@163.com  |    10 |   53 | 29.00 |
+------+-----------+------+--------------------+-------+------+-------+
5 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    将这些记录插入到表player2中

    mysql> insert into player2 select * from player where level between 6 and 10;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from player2;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
|   11 | 胡一菲    | 女   | huyifei@163.com       |     6 |   53 | 38.00 |
|   31 | 周伯通    | 男   | zhoubotong@163.com    |     8 |   45 | 70.00 |
|   49 | 安欣      | 男   | anxin@gmail.com       |     8 |   43 | 33.00 |
|   87 | 小鱼儿    | 男   | xiaoyuer@163.com      |     6 |   55 |  4.00 |
|   91 | 余则成    | 男   | yuzecheng@163.com     |    10 |   53 | 29.00 |
+------+-----------+------+-----------------------+-------+------+-------+
9 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    4、子查询与EXISTS 判断查询结果

    EXISTS用来判断一个查询是否有结果，返回值只有0和1两种。
    查询是否有等级大于100的玩家。结果为0，所以不存在

    mysql> select exists(select * from player where level > 100);
+------------------------------------------------+
| exists(select * from player where level > 100) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    查询是否存在等级大于10的玩家。返回值是1，说明存在。

    mysql> select exists(select * from player where level > 10);
+-----------------------------------------------+
| exists(select * from player where level > 10) |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    五、表关联

    在MySQL中，表关联是一种将两个或多个表中的行结合起来的操作。这通常是通过在这些表之间的某些列（通常是具有相同名称和数据类型的列）上进行比较来完成的。
    表关联用来查询多个表中的数据，关联的表之间必须有相同的字段，一般会使用表的主键和外键来关联。

    MySQL支持以下几种类型的表关联：
    内连接（INNER JOIN）：只返回两个表中满足连接条件的行。
    左连接（LEFT JOIN）：返回左表的所有行，即使右表没有匹配的行，右表没有匹配的数据用NULL填充。
    右连接（RIGHT JOIN）：返回右表的所有行，即使左表没有匹配的行，左表没有匹配的数据用NULL填充。

    查看存储装备的表equip，三个字分别是装备的id、名称以及所属玩家的id

    mysql> desc equip;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int          | YES  |     | NULL    |       |
| name      | varchar(100) | YES  |     | NULL    |       |
| player_id | int          | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    1、内连接

    内连接就是使用INNER JOIN 关键字来指定关联的表，然后是ON关键字和两个表中关联的字段，最后可以加上WHERE关键字和查询条件。
    现在结果中既包含玩家的信息，又包含玩家对应装备的信息，它们之间使用玩家id这个字段来关联起来。

    mysql> select * from player
    -> inner join equip
    -> on player.id = equip.player_id;
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
| id   | name      | sex  | email               | level | exp  | gold  | id   | name            | player_id |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 |    6 | 大师之剑        |        76 |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 |    2 | 丈八蛇矛        |       157 |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 |    7 | 金箍棒          |       161 |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 |    1 | 青龙偃月刀      |       177 |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 |    3 | 七星宝刀        |       186 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    9 | 赤兔马          |       190 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    8 | 方天画戟        |       190 |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
7 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    2、左连接

    使用左连接来关联这两个表。此时，结果中包含了玩家表中所有数据，然后是玩家对应的装备信息，有装备的话就显示装备信息，没有就用NULL来填充。

    mysql> select * from player left join equip on player.id = equip.player_id;
+------+--------------+------+----------------------------+-------+------+--------+------+-----------------+-----------+
| id   | name         | sex  | email                      | level | exp  | gold   | id   | name            | player_id |
+------+--------------+------+----------------------------+-------+------+--------+------+-----------------+-----------+
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 | NULL | NULL            |      NULL |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 | NULL | NULL            |      NULL |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 | NULL | NULL            |      NULL |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 | NULL | NULL            |      NULL |
|    5 | 范德彪       | 男   | fandebiao@gmail.com        |    95 |   89 |  44.00 | NULL | NULL            |      NULL |
|    6 | 马大帅       | 男   | madashuai@geekhour.net     |    87 |   98 |  98.00 | NULL | NULL            |      NULL |
。。。。。。。省略。。。。。。。。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    3、右连接

    右连接是查询右表中的所有数据和左表中匹配的数据，左表中没有的数据用NULL来填充。
    上面的两个表改成右连接

    mysql> select * from player right join equip on player.id = equip.player_id;
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
| id   | name      | sex  | email               | level | exp  | gold  | id   | name            | player_id |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 |    1 | 青龙偃月刀      |       177 |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 |    2 | 丈八蛇矛        |       157 |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 |    3 | 七星宝刀        |       186 |
| NULL | NULL      | NULL | NULL                |  NULL | NULL |  NULL |    4 | 长剑            |      NULL |
| NULL | NULL      | NULL | NULL                |  NULL | NULL |  NULL |    5 | 铁盾            |      NULL |
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 |    6 | 大师之剑        |        76 |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 |    7 | 金箍棒          |       161 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    8 | 方天画戟        |       190 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    9 | 赤兔马          |       190 |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
9 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    4、使用WHERE关键字来代替JION ON

    表连接除了使用JOIN和ON关键字来指定关联的字段之外，还可以使用WHERE关键字来指定。
    还是上面的两个表，把JOIN去掉，表名后面加上,然后在WHERE后面加上关联的条件，结果是一样的。

    mysql> select * from player, equip where player.id = equip.player_id;
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
| id   | name      | sex  | email               | level | exp  | gold  | id   | name            | player_id |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 |    6 | 大师之剑        |        76 |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 |    2 | 丈八蛇矛        |       157 |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 |    7 | 金箍棒          |       161 |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 |    1 | 青龙偃月刀      |       177 |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 |    3 | 七星宝刀        |       186 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    9 | 赤兔马          |       190 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    8 | 方天画戟        |       190 |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
7 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    也可以在表名后面加上别名，使用别名来指定关联的条件，player的别名指定为p，equip的别名指定为e。

    mysql> select * from player p, equip e where p.id = e.player_id;
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
| id   | name      | sex  | email               | level | exp  | gold  | id   | name            | player_id |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 |    6 | 大师之剑        |        76 |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 |    2 | 丈八蛇矛        |       157 |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 |    7 | 金箍棒          |       161 |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 |    1 | 青龙偃月刀      |       177 |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 |    3 | 七星宝刀        |       186 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    9 | 赤兔马          |       190 |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 |    8 | 方天画戟        |       190 |
+------+-----------+------+---------------------+-------+------+-------+------+-----------------+-----------+
7 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    5、多表关联

    game数据库中还有个存放技能的表skill

    6、笛卡尔积

    笛卡尔积，也被称为笛卡尔乘积或直积，是在数学中，两个集合X和Y的笛卡尔积（Cartesian product），表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。例如，假设集合A= {a, b}，集合B= {0, 1, 2}，则两个集合的笛卡尔积为 { (a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

    如果连接没有指定条件，或者条件不正确时，就会产生笛卡尔积。
    比如将上面的查询条件去掉，就会产生以下结果。可以看到，结果中的每一条数据都会和另一个表中的数据进行组合。

    mysql> select * from player p, equip e;
+------+--------------+------+----------------------------+-------+------+--------+------+-----------------+-----------+
| id   | name         | sex  | email                      | level | exp  | gold   | id   | name            | player_id |
+------+--------------+------+----------------------------+-------+------+--------+------+-----------------+-----------+
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    9 | 赤兔马          |       190 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    8 | 方天画戟        |       190 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    7 | 金箍棒          |       161 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    6 | 大师之剑        |        76 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    5 | 铁盾            |      NULL |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    4 | 长剑            |      NULL |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    3 | 七星宝刀        |       186 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    2 | 丈八蛇矛        |       157 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |    1 | 青龙偃月刀      |       177 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    9 | 赤兔马          |       190 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    8 | 方天画戟        |       190 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    7 | 金箍棒          |       161 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    6 | 大师之剑        |        76 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    5 | 铁盾            |      NULL |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    4 | 长剑            |      NULL |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    3 | 七星宝刀        |       186 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    2 | 丈八蛇矛        |       157 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |    1 | 青龙偃月刀      |       177 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    9 | 赤兔马          |       190 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    8 | 方天画戟        |       190 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    7 | 金箍棒          |       161 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    6 | 大师之剑        |        76 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    5 | 铁盾            |      NULL |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    4 | 长剑            |      NULL |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    3 | 七星宝刀        |       186 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    2 | 丈八蛇矛        |       157 |
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |    1 | 青龙偃月刀      |       177 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    9 | 赤兔马          |       190 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    8 | 方天画戟        |       190 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    7 | 金箍棒          |       161 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    6 | 大师之剑        |        76 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    5 | 铁盾            |      NULL |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    4 | 长剑            |      NULL |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    3 | 七星宝刀        |       186 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    2 | 丈八蛇矛        |       157 |
|    4 | 刘能         | 男   | liuneng@qq.com             |    30 |   95 |   1.00 |    1 | 青龙偃月刀      |       177 |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    六、索引

    索引是一种用来提高查询效率的数据结构，它可以帮助我们快速定位到我们想要的数据。如果没有索引，就只能从头开始遍历所有的数据，直到找到满足条件的数据，当数据量非常大时，查询效率会很低。

    1、 CREATE INDEX 创建索引

    索引可以在建表时创建，也可以在建表后指定。
    创建索引的格式如下：

    # 其中，CREATE INDEX是创建索引的关键字 
# [] 内是可选的索引类型，UNIQUE表示唯一索引
# FULLTEXT表示全文索引
# SPATIAL 表示空间索引
# index_name 表示索引的名称
# ON 关键字后面加表名，表示要在哪张表上创建索引
# 括号括起来一个或多个字段，这些字段名表示要对哪些字段创建索引
# 一般会对主键字段或经常查询的字段创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name....)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    创建表fast

    mysql> create table fast (id int,name varchar(255),age int,email varchar(255)); 
Query OK, 0 rows affected (0.01 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4

    在MySQL命令行中输入以下命令，这将创建一个名为insert_data的存储过程，每次插入10条数据，共插入两千万条数据。

    DELIMITER $$
CREATE PROCEDURE insert_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 20000000 DO
    INSERT INTO fast (id, name, age, email) 
    VALUES (i, CONCAT('Name', i), FLOOR(1 + RAND() * 100), CONCAT('email', i, '@example.com')),
           (i+1, CONCAT('Name', i+1), FLOOR(1 + RAND() * 100), CONCAT('email', i+1, '@example.com')),
           (i+2, CONCAT('Name', i+2), FLOOR(1 + RAND() * 100), CONCAT('email', i+2, '@example.com')),
           (i+3, CONCAT('Name', i+3), FLOOR(1 + RAND() * 100), CONCAT('email', i+3, '@example.com')),
           (i+4, CONCAT('Name', i+4), FLOOR(1 + RAND() * 100), CONCAT('email', i+4, '@example.com')),
           (i+5, CONCAT('Name', i+5), FLOOR(1 + RAND() * 100), CONCAT('email', i+5, '@example.com')),
           (i+6, CONCAT('Name', i+6), FLOOR(1 + RAND() * 100), CONCAT('email', i+6, '@example.com')),
           (i+7, CONCAT('Name', i+7), FLOOR(1 + RAND() * 100), CONCAT('email', i+7, '@example.com')),
           (i+8, CONCAT('Name', i+8), FLOOR(1 + RAND() * 100), CONCAT('email', i+8, '@example.com')),
           (i+9, CONCAT('Name', i+9), FLOOR(1 + RAND() * 100), CONCAT('email', i+9, '@example.com'));
    SET i = i + 10;
  END WHILE;
END$$
DELIMITER ;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    运行以下命令来调用存储过程并开始插入数据，开始插入数据，共两千万条。执行时间较长。

    CALL insert_data();

    • 1

    执行的时间太长了，现在插入几百万条数据了，够用了，现在把这个插入进程停止掉

    # 查看进程
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+---------+----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time    | State                      | Info                                                                                                 |
+----+-----------------+-----------+------+---------+---------+----------------------------+------------------------------------------------------------------------------------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 1604482 | Waiting on empty queue     | NULL                                                                                                 |
| 36 | root            | localhost | game | Query   |       0 | waiting for handler commit | INSERT INTO fast (id, name, age, email) 
    VALUES (i, CONCAT('Name', i), FLOOR(1 + RAND() * 100),  |
| 37 | root            | localhost | game | Query   |       0 | init                       | SHOW PROCESSLIST                                                                                     |
+----+-----------------+-----------+------+---------+---------+----------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

# 进程对应的id为36，杀掉
mysql> kill 36;
Query OK, 0 rows affected (0.00 sec)

# 再查看，进程已被干掉
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+---------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time    | State                  | Info             |
+----+-----------------+-----------+------+---------+---------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 1604548 | Waiting on empty queue | NULL             |
| 37 | root            | localhost | game | Query   |       0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+---------+------------------------+------------------+
2 rows in set (0.00 sec)

mysql> 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    查看表fast中的数据条数

    mysql> select count(*) from fast;
+----------+
| count(*) |
+----------+
|  4966660 |
+----------+
1 row in set (0.78 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在没有创建索引的情况下，对表fast进行查询，耗时3.82 sec

    mysql> select * from fast  where email like 'email263118%' order by id;
+---------+-------------+------+--------------------------+
| id      | name        | age  | email                    |
+---------+-------------+------+--------------------------+
|  263118 | Name263118  |   36 | email263118@example.com  |
| 2631180 | Name2631180 |   75 | email2631180@example.com |
| 2631181 | Name2631181 |   32 | email2631181@example.com |
| 2631182 | Name2631182 |   37 | email2631182@example.com |
| 2631183 | Name2631183 |   88 | email2631183@example.com |
| 2631184 | Name2631184 |   27 | email2631184@example.com |
| 2631185 | Name2631185 |   73 | email2631185@example.com |
| 2631186 | Name2631186 |   80 | email2631186@example.com |
| 2631187 | Name2631187 |   83 | email2631187@example.com |
| 2631188 | Name2631188 |   74 | email2631188@example.com |
| 2631189 | Name2631189 |   20 | email2631189@example.com |
+---------+-------------+------+--------------------------+
11 rows in set (3.82 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    给fast表的email字段创建一个索引，索引名称为email_index

    mysql> create index email_index on fast( email);
Query OK, 0 rows affected (28.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5

    2、查看索引

    使用SHOW INDEX FROM后面加上表名来查询

    mysql> show index from fast;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| fast  |          1 | email_index |            1 | email       | A         |     4849809 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    使用索引再来执行刚才的查询语句

    3、使用索引

    现在已经创建了索引，使用在没有创建索引时的查询命令，再来查询一次，耗时0.00 sec，一瞬间就返回了查询结果，而刚才没有使用索引时的查询时间是3.82sec，可见在数据量非常巨大时，使用索引可以大大减少查询时间，提高查询效率。

    mysql> select * from fast  where email like 'email263118%' order by id;
+---------+-------------+------+--------------------------+
| id      | name        | age  | email                    |
+---------+-------------+------+--------------------------+
|  263118 | Name263118  |   36 | email263118@example.com  |
| 2631180 | Name2631180 |   75 | email2631180@example.com |
| 2631181 | Name2631181 |   32 | email2631181@example.com |
| 2631182 | Name2631182 |   37 | email2631182@example.com |
| 2631183 | Name2631183 |   88 | email2631183@example.com |
| 2631184 | Name2631184 |   27 | email2631184@example.com |
| 2631185 | Name2631185 |   73 | email2631185@example.com |
| 2631186 | Name2631186 |   80 | email2631186@example.com |
| 2631187 | Name2631187 |   83 | email2631187@example.com |
| 2631188 | Name2631188 |   74 | email2631188@example.com |
| 2631189 | Name2631189 |   20 | email2631189@example.com |
+---------+-------------+------+--------------------------+
11 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    4、删除索引 DROP INDEX

    删除索引使用DROP INDEX语句，后面加索引名称，然后是ON关键字和表的名称。
    删除刚才创建的索引email_index

    mysql> drop index email_index on fast;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除索引后再查看索引，此时为空
mysql> show index from fast;
Empty set (0.00 sec)

mysql

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    5、在修改表时创建索引

    使用ADD INDEX语句加表名，括号内加要建立索引的字段，来添加索引。

    ALTER TABLE fast ADD INDEX name_index (name);

    • 1

    也可以在建表时添加索引。

    七、视图

    视图是一种虚拟存在的表，它本身并不包含数据，而是作为一个查询语句，保存在数据字典中。当查询视图时，它会根据查询语句的定义，来动态的生成数据。

    1、创建视图

    创建一个玩家表中等级在前5的排行榜视图
    CREATE VIEW后面加上视图的名称，AS关键字后面加上要查询的SELECT语句
    从表player中创建一个视图，包含等级前五的玩家

    mysql> create view top5
    -> as
    -> select * from player order by level desc limit 5;
Query OK, 0 rows affected (0.01 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    2、视图的使用

    视图的使用和正常的数据表是一样的。可以使用select语句来查询视图。

    mysql> select * from top5;
+------+--------------+------+----------------------+-------+------+-------+
| id   | name         | sex  | email                | level | exp  | gold  |
+------+--------------+------+----------------------+-------+------+-------+
|   57 | 阿碧         | 女   | abi@gmail.com        |   100 |    9 | 92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com |   100 |  100 |  1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com  |    99 |    4 | 10.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com       |    99 |   93 | 31.00 |
|   64 | 风清扬       | 男   | fengqingyang@qq.com  |    99 |   80 | 81.00 |
+------+--------------+------+----------------------+-------+------+-------+
5 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    当表中数据发生变化时，视图中的数据也会相应变化。
    比如此时把第一名的等级改为10，然后执行update语句，再来查询视图，视图中的数据也会发生变化，视图中的数据是动态的。

    mysql> update player set level = 10 where id = 64;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from top5;
+------+--------------+------+----------------------+-------+------+-------+
| id   | name         | sex  | email                | level | exp  | gold  |
+------+--------------+------+----------------------+-------+------+-------+
|   57 | 阿碧         | 女   | abi@gmail.com        |   100 |    9 | 92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com |   100 |  100 |  1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com  |    99 |    4 | 10.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com       |    99 |   93 | 31.00 |
|  164 | 娜可露露     | 女   | nakelulu@qq.com      |    99 |   16 | 33.00 |
+------+--------------+------+----------------------+-------+------+-------+
5 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    3、修改视图

    修改视图使用ALTER VIEW语句后面加上视图名称，然后是AS关键字和新的语句
    现在把数据改成从小到大排序

    mysql> alter view top5
    -> as
    -> select  * from player order by level limit 5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from top5;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com    |     5 |   49 | 38.00 |
+------+-----------+------+-----------------------+-------+------+-------+
5 rows in set (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    4、删除视图

    使用DROP VIEW语句，后面加上要删除的视图名称。
    删除视图top5

    mysql> drop view top5;
Query OK, 0 rows affected (0.00 sec)

mysql>

    • 1
    • 2
    • 3
    • 4
  • 相关阅读:
    springboot 反射调用ServiceImpl，报错：java.lang.NullPointerException，mapper为null
    目标检测重要概念——IOU、感受野、空洞卷积、mAP
    el-date-picker自定义只能选中当前月份和半年内月份等
    基于JAVA+SpringMVC+Mybatis+MYSQL的物流管理系统
    java 检查异常与非检查异常
    MySQL基础篇-基本sql语句
    怎么看电脑配置？电脑配置好不好？详细教程来了！
    面向对象编程有四大特性
    Mysql---三张表（student，课程，分数） 查询课程为数学的学生姓名，编号，成绩
    R语言描述性统计：使用mean函数计算dataframe数据中指定数据列的均值
  • 原文地址：https://blog.csdn.net/m0_47584619/article/details/132839385