操作系统：CentOS 8
数据库版本：8.0.26 Source distribution
一、安装与配置
1、安装
yum install -y mysql-server.x86_64
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]#
此时，可以查询是谁占用了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
于是，我选择修改MySQL的启动端口，将启动端口修改为3307，编辑下面的配置文件
vim /etc/my.cnf.d/mysql-server.cnf
添加一行内容，如下：
port=3307
此时配置文件就变成了：
[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 ~]#
然后重启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.
修改MySQL的密码，修改密码之后，再登录MySQL需要使用密码。
mysql # 进入MySQL命令行
# 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
设置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]#
[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]#
创建一个可以远程连接的root用户
CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
二、常用命令
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
.........
.........
然后，我们查看一下MySQL中有哪些数据库，命令如下：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
现在创建一个名为 game的数据库
mysql> create database game;
Query OK, 1 row affected (0.00 sec)
mysql>
此时再查看一下数据库，会发现多了一个game
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| game |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
2、删除数据库
现在删除刚才新建的数据库game
mysql> DROP DATABASE game;
Query OK, 0 rows affected (0.01 sec)
此时再查看，game已被删除
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
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>
选择使用game数据库
mysql> use game;
Database changed
mysql>
在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)
使用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>
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>
修改字段名称
字段的名称也可以修改，此时我要把name修改为nick_namealter 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>
添加字段。
在表中添加一个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>
删除字段。
将刚刚添加的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>
删除表
删除表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>
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>
插入数据
在表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>
没有列出的字段名将会以默认值填充。下面这条命令只插入了id, name, level这三个字段的数据，剩下的exp和glod字段将被默认值填充。
mysql> insert into player (id, name, level) values (3, '王五', 0);
Query OK, 1 row affected (0.00 sec)
mysql>
如果字段名和要插入的信息一一对应，那么字段名可以省略。
mysql> insert into player values (2,'李四', 2, 2, 2);
Query OK, 1 row affected (0.00 sec)
mysql>
使用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
查询数据
查看刚才插入的数据，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>
建表时，没有指定默认值，所以现在的默认值是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>
此时再插入一条数据，看看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>
更新数据
现在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>
这样每次修改一条数据太低效，把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>
删除数据
删除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
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 ~]#
查看文件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`
文件内容太多，省略了
数据库的导入
在Linux命令行，将刚才导出的数据库game导入。在导入sql文件之前，要确保已经存在一个数据库，数据库的名字和sql文件的名字相同。-u指定数据库的用户名，-p指定数据库密码，mysql -u root -p 后加要导入的数据库的名字，<后加需要被导进去的sql文件
[root@iZ56kkvaq4nlfhZ ~]# mysql -u root -p game <game.sql
Enter password:
[root@iZ56kkvaq4nlfhZ ~]#
把练习用的的数据库导进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 ~]#
进入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>
创建和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)
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
导入 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、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>
逻辑运算符
使用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>
查找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>
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>
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>
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 |
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>
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>
匹配姓王或姓张的玩家
匹配条件也可以换成 [王张]
即 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 |
查找邮件地址以“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>
查找邮件地址以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 |
查找邮件地址以“.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 |
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>
查找填写了邮箱的玩家，可以使用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 |
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>
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>
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 |
。。。。。。。。。省略。。。。。。。。。
如果想要降序排列，则在语句后面加上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 |
按照多个字段排序，字段之间用, 分割。
按照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 |
也可以按照字段序号来排序，字段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 |
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)
查看玩家平均等级level
mysql> select avg(level) from player;
+------------+
| avg(level) |
+------------+
| 55.0813 |
+------------+
1 row in set (0.00 sec)
查看玩家金币gold的最小值
mysql> select min(gold) from player;
+-----------+
| min(gold) |
+-----------+
| 1.00 |
+-----------+
1 row in set (0.00 sec)
其他两个函数用法与此类似。
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)
查看表中每个等级的玩家数量
mysql> select level, count(level) from player group by level;
+-------+--------------+
| level | count(level) |
+-------+--------------+
| 3 | 1 |
| 4 | 1 |
| 64 | 3 |
| 30 | 1 |
| 95 | 5 |
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 |
将表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 |
统计玩家中每个姓氏的数量，将结果按照数量来降序排列，只显示数量大于等于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>
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>
在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>
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)
查询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)
将这两条查询结果使用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>
如果不想去重，可以使用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>
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;
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;
四、子查询
子查询，也被称为内部查询或嵌套查询，是指在一条SELECT语句中嵌入了另外一条SELECT语句，子查询的结果可以作为另一个SQL语句（主查询）的数据来源或者判断条件。
查询玩家的平均等级
1、使用子查询查询数据
mysql> select avg(level) from player;
+------------+
| avg(level) |
+------------+
| 55.0813 |
+------------+
1 row in set (0.00 sec)
查询所有等级大于平均等级的玩家
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 |
查询所有玩家等级和平均等级之间的差值，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 |
结果的后两列列名直接用了表达式，这样的结果不方便阅读，可以使用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 |
子查询可以在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>
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>
将这些记录插入到表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>
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>
查询是否存在等级大于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>
五、表关联
在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、内连接
内连接就是使用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>
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 |
。。。。。。。省略。。。。。。。。
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>
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)
也可以在表名后面加上别名，使用别名来指定关联的条件，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>
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、 CREATE INDEX 创建索引
索引可以在建表时创建，也可以在建表后指定。
创建索引的格式如下：
# 其中，CREATE INDEX是创建索引的关键字
# [] 内是可选的索引类型，UNIQUE表示唯一索引
# FULLTEXT表示全文索引
# SPATIAL 表示空间索引
# index_name 表示索引的名称
# ON 关键字后面加表名，表示要在哪张表上创建索引
# 括号括起来一个或多个字段，这些字段名表示要对哪些字段创建索引
# 一般会对主键字段或经常查询的字段创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name....)
创建表fast
mysql> create table fast (id int,name varchar(255),age int,email varchar(255));
Query OK, 0 rows affected (0.01 sec)
mysql>
在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 ;
运行以下命令来调用存储过程并开始插入数据，开始插入数据，共两千万条。执行时间较长。
CALL insert_data();
执行的时间太长了，现在插入几百万条数据了，够用了，现在把这个插入进程停止掉
# 查看进程
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>
查看表fast中的数据条数
mysql> select count(*) from fast;
+----------+
| count(*) |
+----------+
| 4966660 |
+----------+
1 row in set (0.78 sec)
在没有创建索引的情况下，对表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>
给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>
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>
使用索引再来执行刚才的查询语句
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>
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
5、在修改表时创建索引
使用ADD INDEX语句加表名，括号内加要建立索引的字段，来添加索引。
ALTER TABLE fast ADD INDEX name_index (name);
也可以在建表时添加索引。
七、视图
视图是一种虚拟存在的表，它本身并不包含数据，而是作为一个查询语句，保存在数据字典中。当查询视图时，它会根据查询语句的定义，来动态的生成数据。
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>
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>
当表中数据发生变化时，视图中的数据也会相应变化。
比如此时把第一名的等级改为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>
3、修改视图
修改视图使用ALTER VIEW语句后面加上视图名称，然后是AS关键字和新的语句
现在把数据改成从小到大排序文章来源：https://www.toymoban.com/news/detail-708378.html
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>
4、删除视图
使用DROP VIEW语句，后面加上要删除的视图名称。
删除视图top5文章来源地址https://www.toymoban.com/news/detail-708378.html
mysql> drop view top5;
Query OK, 0 rows affected (0.00 sec)
mysql>
到了这里，关于MySQL基础【学习至基本语句】的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
