【MySQL】基础命令操作

本文记录了MySQL的基础SQL命令。

本文演示所用mariadb版本，可以认为其与MySQL8对应。

1	mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1

前言：SQL语句的分类

学习MySQL之前，我们先了解一下MySQL中SQL的分类

DDL【data definition language】数据定义语言，用来维护存储数据的结构。代表指令: create, drop, alter
DML【data manipulation language】数据操纵语言，用来对数据进行操作。代表指令： insert，delete，update
- DML中又单独分了一个DQL，数据查询语言，代表指令： select
DCL【Data Control Language】数据控制语言，主要负责权限管理和事务。代表指令： grant，revoke，commit

0.连接mysql

默认情况下，我们的mysql没有密码，直接使用如下命令，就能链接上mysql

1	mysql -uroot

mysql数据库文件路径

1	/var/lib/mysql

mysql的语句是大小写不敏感的，大小写都可以。

如果你的MySQL有密码，那就是用如下命令进入MySQL命令行

1 2	mysql -uroot -p123456 # 示例 mysql -u用户名 -p密码 # 基本操作

0.1 查看有谁连接了

1	show processlist;

该命令可以查看当前有谁连接了MySQL服务，所在数据库是什么，正在执行的cmd是什么

0.2 查看支持的存储引擎

mysql支持多种存储引擎

存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

每个引擎各有其优势。可以用下面的语句查询当前支持的引擎

1	show engines;

1.数据库

1.1 创建

1
2
3

create database 数据库名字;
create database if not exists 数据库名字; # 如果不存在才创建
create database hello;

上面的命令创建了一个名为hello的数据库。对于mysql服务端而言，其实际上是在数据文件路径中，帮我们创建了一个对应的文件夹

如果是mysql5.7，在创建语句之前新增一个show，可以看到当前执行语句的详细信息。我当前使用的是高版本的mariadb，不支持这个操作

1	show create database hello;

创建数据库或者表的时候，我们还可以给名称带上如下符号作为分割符

1	create database `数据库名称`;

1 2	MariaDB [(none)]> create database `test2`; Query OK, 1 row affected (0.001 sec)

带上该符号后，可以用于区分你的表名和数据库的内置语句。建议创建、删除操作的时候，都给名字带上这个符号。

当然，创建的数据库、表名最好不要和mysql内置语句冲突，这和写代码的时候不要用编程语言的关键字/库函数名来命名变量一样。

1.2 查看已有

1	show databases;

查看当前已有数据库

+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

1.3 进入

进入这个数据库

1	use hello;

1.4 删除

1	drop database [if exists] 名字;

如下，我先是创建了一个test1数据库

MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| test1              |
+--------------------+
5 rows in set (0.000 sec)

用下面这个命令删除该数据库

1	drop database test1;

成功删除

MariaDB [(none)]> drop database test1;
Query OK, 0 rows affected (0.035 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

数据文件路径中，test1文件夹也被对应删除

1.5 字符集和校验规则

字符集会有自己对应的校验规则

字符集用于将数据以特定编码存入数据库
校验规则用于取数据时对数据进行校验

修改字符集的时候，mysql会为我们自动指定对应的校验规则

1.5.1 字符集

一个数据被存放的时候，是会有其所用的编码的。编码不相同，会导致数据在不同软件中显示出不同的结果。

比如我们使用VS写的C语言代码，文件内的中文注释放入devc++之中，有可能会乱码，这就是字符编码不匹配的原因

show variables like语句可以查看mysql的内置变量，其中如下变量就是数据库字符集的编码格式

1	show variables like 'character_set_database';

可以看到，为utf-8

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.002 sec)

在配置mariadb的时候，就已经修改过了配置文件中数据库的编码规则，所以所有相关字符集编码都是utf-8

1	show variables like 'character_set_%';

MariaDB [(none)]> show variables like 'character_set_%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8                         |
| character_set_connection | utf8                         |
| character_set_database   | utf8                         |
| character_set_filesystem | binary                       |
| character_set_results    | utf8                         |
| character_set_server     | utf8                         |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.002 sec)

使用如下命令可以查看数据库支持的所有编码

1	show charset;

MariaDB [(none)]> show charset;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.001 sec)

1.5.2 校验规则

1	show variables like 'collation_%';

如下可以看到，当前的校验规则都是utf-8相关的

MariaDB [(none)]> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.001 sec)

查看系统中所有校验集

1	show collation;

结果很长，就不全贴出来了。能看到每一个编码集都对应了一个字符集

1.5.3 创建库时指定

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
    [DEFAULT] COLLATE collation_name

其中[]代表是可选项

CHARACTER SET 指定数据库采用的字符集
COLLATE 指定数据库字符集的校验规则

如果在创建数据库时没有指定字符集和校验规则，则会采用数据库默认的。

在数据库的文件夹中，有一个db.opt文件，其中就包含了当前数据库使用的字符集和校验规则

[root@1c2261732150:/var/lib/mysql]# ls hello
db.opt  stu_test.frm  stu_test.ibd
[root@1c2261732150:/var/lib/mysql]# cat hello/db.opt
default-character-set=utf8
default-collation=utf8_general_ci

如果在创建的时候指明字符集编码

1 2	create database `test1` character set gbk; create database `test1` charset=gbk; # 两种写法相同

查看db.opt，可以看到系统自动指定了gbk字符集对应的校验和

[root@1c2261732150:/var/lib/mysql]# ls test1
db.opt
[root@1c2261732150:/var/lib/mysql]# cat test1/db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci

同时指定字符集和编码

1	create database `test2` charset=utf8 collate utf8_bin;

执行成功后，查看本地文件

1
2
3

[root@1c2261732150:/var/lib/mysql]# cat test2/db.opt
default-character-set=utf8
default-collation=utf8_bin

成功设置了字符集和编码规则

虽然在创建的时候可以这么做，但我们保持默认配置的utf8即可

1.5.4 校验的影响

通过指定不同的编码，我创建了两个校验规则不同的数据库

MariaDB [(none)]> create database test_ci charset=utf8;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> create database test_bin charset=utf8 collate utf8_bin;
Query OK, 1 row affected (0.000 sec)

其中一个的校验规则是utf8_general_ci，另外一个是utf8_bin

[root@1c2261732150:/var/lib/mysql]# cat test_bin/db.opt
default-character-set=utf8
default-collation=utf8_bin
[root@1c2261732150:/var/lib/mysql]# cat test_ci/db.opt
default-character-set=utf8
default-collation=utf8_general_ci

utf8_bin

先进入test_bin数据库

1	use test_bin;

创建一个student表，只有一个字符串类型的name字段

1	create table student(name varchar(30));

插入如下数据

MariaDB [test_bin]> insert into student value ('a');
Query OK, 1 row affected (0.042 sec)

MariaDB [test_bin]> insert into student value ('A');
Query OK, 1 row affected (0.008 sec)

MariaDB [test_bin]> insert into student value ('b');
Query OK, 1 row affected (0.003 sec)

MariaDB [test_bin]> insert into student value ('B');
Query OK, 1 row affected (0.004 sec)

MariaDB [test_bin]> insert into student value ('c');
Query OK, 1 row affected (0.009 sec)

MariaDB [test_bin]> insert into student value ('C');
Query OK, 1 row affected (0.007 sec)

查看已有数据，分别是大小写的abc

MariaDB [test_bin]> select * from  student;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
| c    |
| C    |
+------+
6 rows in set (0.001 sec)

查询数据，可以看到，返回的结果是只有小写a的

MariaDB [test_bin]> select * from student where name='a';
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.001 sec)

执行排序（升序）后输出，得到如下结果。我们知道，小写字母的ASCII是在大写字母的之后的，所以这个排序结果是正确的。

MariaDB [test_bin]> select * from student order by name;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| a    |
| b    |
| c    |
+------+
6 rows in set (0.001 sec)

utf8_general_ci

1	use test_ci;

在这个数据库中进行相同的插入工作，创建表，插入大小写abc（命令同上不再重复）

MariaDB [test_ci]> select * from  student;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
| c    |
| C    |
+------+
6 rows in set (0.000 sec)

执行查询语句，发现当我们查询小a的时候，同时返回了A和a的结果

MariaDB [test_ci]> select * from student where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.001 sec)

排序的时候，大小a没有按ASCII码顺序排，而是放到了一起

MariaDB [test_ci]> select * from student order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
| c    |
| C    |
+------+
6 rows in set (0.001 sec)

结论

utf8_bin：查询的时候区分大小写
utf8_general_ci：查询时忽略大小写

在实际场景中，我们就可以根据需求，选择这两个校验规则中的其一作为我们数据库的校验规则。

这两个只是众多校验规则之一，更多编码的区别，还得等后续慢慢探寻。在一般场景中，使用utf8是完全足够的。

1.6 修改

1 2	alter database 数据库名 [create_specification [, create_specification] ...];

一般情况下，我们修改的是数据库的字符集和校验规则。

示例如下，修改数据库test_ci的字符集为gbk

1 2	MariaDB [test_ci]> alter database test_ci charset=gbk; Query OK, 1 row affected (0.006 sec)

修改后查看配置文件，字符集和校验规则确实变化了（系统自动查找对应的校验规则）

[root@1c2261732150:/var/lib/mysql]# cat test_ci/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
[root@1c2261732150:/var/lib/mysql]# cat test_ci/db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci

也可以同时修改校验规则，这里和新建数据库时的操作是一样的

1	alter database test_ci charset=utf8 collate utf8_bin;

[root@1c2261732150:/var/lib/mysql]# cat test_ci/db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci
[root@1c2261732150:/var/lib/mysql]# cat test_ci/db.opt
default-character-set=utf8
default-collation=utf8_bin

1.7 备份数据库

1.7.1 备份一个数据库

如下操作是在linux命令行中执行的

1	mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

示例

1	mysqldump -P3306 -u root -B hello > hello.bak.sql

这会在当前路径中出现了一个sql文件，其内容如下。

-- MySQL dump 10.19  Distrib 10.3.28-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: hello
-- ------------------------------------------------------
-- Server version       10.3.28-MariaDB

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 */;

--
-- Current Database: `hello`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `hello`;

--
-- Table structure for table `stu_test`
--

DROP TABLE IF EXISTS `stu_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `score` decimal(4,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu_test`
--

LOCK TABLES `stu_test` WRITE;
/*!40000 ALTER TABLE `stu_test` DISABLE KEYS */;
INSERT INTO `stu_test` VALUES (2,'小图图',5,72.80),(3,'大司马',42,87.30),(4,'乐迪',32,99.00);
/*!40000 ALTER TABLE `stu_test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-04-15 15:20:25

我们会发现这里面的内容其实都是sql语句，如果将这个备份导入到另外一个数据库中，实际上就是将历史的所有sql语句全部执行一遍！

除此之外，复制/var/lib/mysql下的数据库文件也是可行的一种备份方案。但并不推荐这么做！

1.7.2 备份数据库中的多张表

1	mysqldump -u root -p 数据库名表名1 表名2 > 备份文件名

1.7.3 同时备份多个数据库

1	mysqldump -u root -p -B 数据库名1 数据库名2 > 备份文件名

1.7.4 还原

如下命令需要在mysql中执行

1
2
3

source 备份文件路径
## 示例
source /root/hello.bak.sql;

数据库中的内容和原有数据库是完全相同的

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| stu_test        |
+-----------------+
1 row in set (0.001 sec)

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  2 | 小图图    |    5 | 72.80 |
|  3 | 大司马    |   42 | 87.30 |
|  4 | 乐迪      |   32 | 99.00 |
+----+-----------+------+-------+
3 rows in set (0.001 sec)

1.7.5 化简

如果你看过了上面对校验影响的验证，其中我往test_bin数据库中使用6条语句插入了大小写的abc。

但当我们执行备份的时候，可以看到备份出来的sql文件，六个语句被合并了！

1	INSERT INTO `student` VALUES ('a'),('A'),('b'),('B'),('c'),('C');

这也是mysql备份的智能之处之一！

1.8 清空屏幕

在MySQL中，可以通过system 来执行linux系统命令

1	system clear

比如使用如上命令可以清空屏幕

2.表

如下命令需要先进入特定database才能执行

2.1 创建表

基础的创建操作如下。其中末尾的字符集、校验规则、存储引擎的设置项可以留空不填

field 列名
datatype 列的类型

create table 表名 (
    field1 datatype,
    field2 datatype,
    field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

示例如下，创建了一个student表，并配置了两个键值name和age，类型分别为字符串和int

create table student(
    name varchar(30),
    age int
);

除了基础操作之外，我们还可以给这个表新增一个comment作为列备注

create table student(
    name varchar(30) comment '学生名字',
    age int comment '学生年龄'
);

用下面的语句可以查看创建表时候的操作

1	show create table 表名;

这部分和备份的时候的sql也是一样的，mysql会帮我们自动规整语句，使其语法规则更规整；

如下语句也能看到创建表时所用命令

1	show create table stu_test\G

在数据库帮我们创建这张表的时候，如果没有手动指定引擎和字符集，则会使用数据库的默认设置；

MariaDB [hello]> show create table stu_test\G
*************************** 1. row ***************************
       Table: stu_test
Create Table: CREATE TABLE `stu_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `score` decimal(4,2) DEFAULT NULL,
  `avatar` varchar(100) NOT NULL DEFAULT 'default-avatar.png' COMMENT '头像图片路径',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.000 sec)

这两个show命令的结果是类似的

如果想在建立表的时候，设置数据库所用引擎和字符集，可以在create语句的括号之后，依照格式添加配置项

1	CREATE TABLE xxx() ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.2 查看已有表

1	show tables;

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| student         |
+-----------------+
1 row in set (0.001 sec)

2.3 查看表的属性/结构

desc 表名;

MariaDB [hello]> desc stu_test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)  | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
| score | decimal(4,2) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

还可以使用如下命令，输出的结果包括更多信息

1	SHOW FULL COLUMNS FROM 表名;

2.4 删除该表

1	drop table student;

2.5 修改表的结构

注意，这里是对表的属性的修改，并不是对数据的修改！

比如我想新增字段，或者修改已有字段的属性，或者删除字段

-- 新增字段
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column
datatype]...);
-- 修改字段
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column
datatype]...);
-- 删除字段
ALTER TABLE tablename DROP (column);

2.5.0 注意事项

请注意：修改表的字段是一个很麻烦的操作，其可能会涉及到已有数据的有效性问题

请在生产环境上线前的测试阶段，完成表字段的定义！

说人话就是，前期设计要想的尽量到位，避免后期表中已有数据的时候修改表的属性！

2.5.1 新增字段

比如我给上方2.5中出现过的stu_test表新增一列，可以用如下的命令

1	alter table stu_test add avatar varchar(100) comment '头像图片路径' after score;

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  2 | 小图图    |    5 | 72.80 |
|  3 | 大司马    |   42 | 87.30 |
|  4 | 乐迪      |   32 | 99.00 |
|  5 | dc872458  |   25 |  NULL |
+----+-----------+------+-------+
4 rows in set (0.000 sec)

MariaDB [hello]> alter table stu_test add avatar varchar(100) comment '头像图片路径' after
    -> score;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+--------+
| id | name      | age  | score | avatar |
+----+-----------+------+-------+--------+
|  2 | 小图图    |    5 | 72.80 | NULL   |
|  3 | 大司马    |   42 | 87.30 | NULL   |
|  4 | 乐迪      |   32 | 99.00 | NULL   |
|  5 | dc872458  |   25 |  NULL | NULL   |
+----+-----------+------+-------+--------+
4 rows in set (0.000 sec)

此时可以看到，已有数据都多了一个avatar字段，且这些已有数据的新字段都为NULL

说明新增字段并不会影响旧数据的旧字段。我们可以在新增字段之后，给旧的数据修改默认值

2.5.2 删除字段

如果表中的数据只剩一列（只有一个字段）是不能删除这个字段的（只能删除这个表）

使用如下命令，删除刚刚新增的avatar字段

1	alter table stu_test drop avatar;

可以看到avatar列被删除了

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+--------+
| id | name      | age  | score | avatar |
+----+-----------+------+-------+--------+
|  2 | 小图图    |    5 | 72.80 | NULL   |
|  3 | 大司马    |   42 | 87.30 | NULL   |
|  4 | 乐迪      |   32 | 99.00 | NULL   |
|  5 | dc872458  |   25 |  NULL | NULL   |
+----+-----------+------+-------+--------+
4 rows in set (0.000 sec)

MariaDB [hello]> alter table stu_test drop avatar;
Query OK, 0 rows affected (0.034 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  2 | 小图图    |    5 | 72.80 |
|  3 | 大司马    |   42 | 87.30 |
|  4 | 乐迪      |   32 | 99.00 |
|  5 | dc872458  |   25 |  NULL |
+----+-----------+------+-------+
4 rows in set (0.000 sec)

在具体场景中，如果需要给已有用户新增头像，一般存放的是头像的文件路径（而不是图片），此时我们就可以设计一个默认头像，并在新增字段的时候将默认值（默认的头像文件路径）修改为这个默认头像图片的路径。

比如我现在的头像存储的文件路径是

./images/

此时存放的用户头像文件就应该放到这个目录中，而mysql中只需要存放文件的路径。

比如用户上传了一个头像图片，我们将头像图片保存到这个路径，再往sql中的avatar字段插入路径

1 2	./images/test-img.png sql中只需要保存test-img.png，取图片的时候拼接上前方的文件路径

设计一个默认头像，也是放在这个路径中

1 2	./images/test-img.png ./images/default-avatar.png

这时候新增avatar字段的时候，就可以指定默认值

1 2	alter table stu_test add avatar varchar(100) not null default 'default-avatar.png' comment '头像图片路径' after score;

这时候就能看到，旧数据的头像都是默认头像了！

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+--------------------+
| id | name      | age  | score | avatar             |
+----+-----------+------+-------+--------------------+
|  2 | 小图图    |    5 | 72.80 | default-avatar.png |
|  3 | 大司马    |   42 | 87.30 | default-avatar.png |
|  4 | 乐迪      |   32 | 99.00 | default-avatar.png |
|  5 | dc872458  |   25 |  NULL | default-avatar.png |
+----+-----------+------+-------+--------------------+
4 rows in set (0.000 sec)

默认头像总好过没有图片嘛！

2.5.3 修改字段类型

在设计stu_test表的时候，只给name分配了30个字节的空间。此时如果来了个名字特别特别长的新同学（有些地方的人名确实很长）

我们发现30个字节存不下这个名字了，就可以考虑将名字字段的长度增加

1	alter table stu_test modify name varchar(60);

如下，成功修改！

MariaDB [hello]> desc stu_test;
+--------+--------------+------+-----+--------------------+----------------+
| Field  | Type         | Null | Key | Default            | Extra          |
+--------+--------------+------+-----+--------------------+----------------+
| id     | int(11)      | NO   | PRI | NULL               | auto_increment |
| name   | varchar(30)  | YES  |     | NULL               |                |
| age    | int(11)      | YES  |     | NULL               |                |
| score  | decimal(4,2) | YES  |     | NULL               |                |
| avatar | varchar(100) | NO   |     | default-avatar.png |                |
+--------+--------------+------+-----+--------------------+----------------+
5 rows in set (0.001 sec)

MariaDB [hello]> alter table stu_test modify name varchar(60);
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hello]> desc stu_test;
+--------+--------------+------+-----+--------------------+----------------+
| Field  | Type         | Null | Key | Default            | Extra          |
+--------+--------------+------+-----+--------------------+----------------+
| id     | int(11)      | NO   | PRI | NULL               | auto_increment |
| name   | varchar(60)  | YES  |     | NULL               |                |
| age    | int(11)      | YES  |     | NULL               |                |
| score  | decimal(4,2) | YES  |     | NULL               |                |
| avatar | varchar(100) | NO   |     | default-avatar.png |                |
+--------+--------------+------+-----+--------------------+----------------+
5 rows in set (0.001 sec)

因为是增加字段的长度，所以也不影响已有数据

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+--------------------+
| id | name      | age  | score | avatar             |
+----+-----------+------+-------+--------------------+
|  2 | 小图图    |    5 | 72.80 | default-avatar.png |
|  3 | 大司马    |   42 | 87.30 | default-avatar.png |
|  4 | 乐迪      |   32 | 99.00 | default-avatar.png |
|  5 | dc872458  |   25 |  NULL | default-avatar.png |
+----+-----------+------+-------+--------------------+
4 rows in set (0.000 sec)

如果是减小字段长度，就需要确认已有数据是否有超过新长度的内容了！比如从60减小到30，那么长度为40的数据就会出现错误！

那如果是将varchar直接改成int，或者将int改成char，会发生什么呢？

表中的id字段是一个int，尝试将其修改成char

1	alter table stu_test modify id varchar(32);

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+--------------------+
| id | name      | age  | score | avatar             |
+----+-----------+------+-------+--------------------+
| 2  | 小图图    |    5 | 72.80 | default-avatar.png |
| 3  | 大司马    |   42 | 87.30 | default-avatar.png |
| 4  | 乐迪      |   32 | 99.00 | default-avatar.png |
| 5  | dc872458  |   25 |  NULL | default-avatar.png |
+----+-----------+------+-------+--------------------+
4 rows in set (0.001 sec)

MariaDB [hello]> desc stu_test;
+--------+--------------+------+-----+--------------------+-------+
| Field  | Type         | Null | Key | Default            | Extra |
+--------+--------------+------+-----+--------------------+-------+
| id     | varchar(32)  | NO   | PRI | NULL               |       |
| name   | varchar(60)  | YES  |     | NULL               |       |
| age    | int(11)      | YES  |     | NULL               |       |
| score  | decimal(4,2) | YES  |     | NULL               |       |
| avatar | varchar(100) | NO   |     | default-avatar.png |       |
+--------+--------------+------+-----+--------------------+-------+
5 rows in set (0.002 sec)

可以看到，其不仅成功变成了字符串类型，已有的值也没有出错。毕竟int数字改成字符串是可行的。那反过来呢？

MariaDB [hello]> insert into stu_test (id,name,age,score,avatar) values ('kb','你好',30,60,'测试图片.png');
Query OK, 1 row affected (0.007 sec)

MariaDB [hello]> select * from stu_test;
+----+-----------+------+-------+--------------------+
| id | name      | age  | score | avatar             |
+----+-----------+------+-------+--------------------+
| 2  | 小图图    |    5 | 72.80 | default-avatar.png |
| 3  | 大司马    |   42 | 87.30 | default-avatar.png |
| 4  | 乐迪      |   32 | 99.00 | default-avatar.png |
| 5  | dc872458  |   25 |  NULL | default-avatar.png |
| kb | 你好      |   30 | 60.00 | 测试图片.png       |
+----+-----------+------+-------+--------------------+
5 rows in set (0.000 sec)

此时我往表中插入了一个id为kb的数据，再尝试将id从varchar改回int类型

MariaDB [hello]> alter table stu_test modify id int;
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'kb'
MariaDB [hello]> desc stu_test;
+--------+--------------+------+-----+--------------------+-------+
| Field  | Type         | Null | Key | Default            | Extra |
+--------+--------------+------+-----+--------------------+-------+
| id     | varchar(32)  | NO   | PRI | NULL               |       |
| name   | varchar(60)  | YES  |     | NULL               |       |
| age    | int(11)      | YES  |     | NULL               |       |
| score  | decimal(4,2) | YES  |     | NULL               |       |
| avatar | varchar(100) | NO   |     | default-avatar.png |       |
+--------+--------------+------+-----+--------------------+-------+
5 rows in set (0.001 sec)

此时就会报错，kb不符合int类型；修改失败了！

所以，修改字段的类型，需要保证原有类型和目标类型之间是可以正常双向转换的！

2.5.4 修改字段名字

如下使用change修改字段id -> num，同时还需要注明属性（也可以修改属性）

1	alter table stu_test change id num varchar(32);

这里可以看到0 rows affected，因为我们修改的是字段名字而已，所以不会影响到数据

MariaDB [hello]> alter table stu_test change id num varchar(32);
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hello]> desc stu_test;
+--------+--------------+------+-----+--------------------+-------+
| Field  | Type         | Null | Key | Default            | Extra |
+--------+--------------+------+-----+--------------------+-------+
| num    | varchar(32)  | NO   | PRI | NULL               |       |
| name   | varchar(60)  | YES  |     | NULL               |       |
| age    | int(11)      | YES  |     | NULL               |       |
| score  | decimal(4,2) | YES  |     | NULL               |       |
| avatar | varchar(100) | NO   |     | default-avatar.png |       |
+--------+--------------+------+-----+--------------------+-------+
5 rows in set (0.001 sec)

2.6 修改表名

1	alter table stu_test rename stu;

可以看到修改成功了

MariaDB [hello]> alter table stu_test rename stu;
Query OK, 0 rows affected (0.015 sec)

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| stu             |
| tb_video        |
+-----------------+
2 rows in set (0.000 sec)

2.7 新增自增主键且不影响原有数据

新增了一个自增的int id主键到表内，可以使用如下命令

1	ALTER TABLE 表名 ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

2.8 表字段的约束

参考【mysql字段约束】一文！建议看了这个之后再往下看。

3.数据记录 CURD

下方学习的都是数据操控语言DML；主要针对的就是表的增加删除修改和查询，简称CRUD（增查改删）

Create   创建
Retrieve 读取
Update   更新
Delete   删除

在MySQL中，我们将一行新增的数据，称为一条记录

3.1 新增 Create

要想往一个表里面插入数据，首先需要创建表（好像是一句废话）

先使用如下表进行测试

CREATE TABLE students (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT comment '主键序号',
	sn INT NOT NULL UNIQUE COMMENT '学号',
	name VARCHAR(20) NOT NULL COMMENT  '学生名字',
	qq VARCHAR(20) COMMENT '学生QQ'
);

在学习插入之前，我们首先要会最基本的查询语句

1	select * from 表名; -- 显示表中的所有数据

3.1.1 单行全列插入

所谓单行，代表一次只插入一条记录；

所谓全列插入，代表插入这条数据的时候，将全部列的数据都手动指定。

1 2	insert into students values (1,1000,'小明','123124'); insert into students values (2,1001,'小红',NULL);

如上就是两个全列插入的示例；执行完毕这条sql的时候，MySQL会显示 1 row affected，代表只有一行收到了影响（新增了一条记录）

MariaDB [hello_mysql]> insert into students values (1,1000,'小明','123124');
Query OK, 1 row affected (0.007 sec)

MariaDB [hello_mysql]> insert into students values (2,1001,'小红',NULL);
Query OK, 1 row affected (0.002 sec)

查询表中数据，可以看到我们刚刚插入的记录

MariaDB [hello_mysql]> select * from students;
+----+------+--------+--------+
| id | sn   | name   | qq     |
+----+------+--------+--------+
|  1 | 1000 | 小明   | 123124 |
|  2 | 1001 | 小红   | NULL   |
+----+------+--------+--------+
2 rows in set (0.001 sec)

3.1.2 多行指定列插入

所谓多行插入，就是一次插入多条数据。指定列是在插入的时候，明确告诉MySQL我们想插入的列数是哪几个。

需要注意的是，只要某一列没有DEFAULT值，如果不指定此列，则会被默认为NULL；如果某一列设置了NOT NULL并且没有设置DEFAULT值，那就必须要指定值，否则会报错。

另外，指定的列的个数和values中数据的个数需要对应上。

1
2
3

INSERT INTO students (id, sn, name) VALUES
    (102, 20001, '曹孟德'),
    (103, 20002, '孙仲谋');

执行效果如下

MariaDB [hello_mysql]> INSERT INTO students (id, sn, name) VALUES
    ->     (102, 20001, '曹孟德'),
    ->     (103, 20002, '孙仲谋');
Query OK, 2 rows affected (0.013 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hello_mysql]> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 |  1000 | 小明      | 123124 |
|   2 |  1001 | 小红      | NULL   |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
+-----+-------+-----------+--------+
4 rows in set (0.000 sec)

3.1.3 插入否则更新

如果我们往表里面插入一个已经存在的主键/唯一键的值，就会出现主键/唯一键冲突而导致的拒绝插入。

MariaDB [hello_mysql]> insert into students values (1,1003,'李华','12354');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [hello_mysql]> insert into students values (5,1000,'小可','53134');
ERROR 1062 (23000): Duplicate entry '1000' for key 'sn'

此时我们可以根据具体情况，将插入改成插入否则更新，即如果主键和唯一键冲突，则更新已有记录。不冲突则正常插入；

这样就可以把插入和更新的语句给统一成一个。但需要避免出现明明是要插入一个新的，但却因为主键设置错误而更新了旧值，导致记录丢失，那样就不好了。

插入否则更新的语法是下面这样

1	INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

示例如下，这个语句的意思是，如果唯一键和主键没有冲突，则插入 (100, 10010, '唐大师')的记录，如果id和sn冲突了，则将sn更新为10010，name更新为唐大师。

1 2	INSERT INTO students (id, sn, name) VALUES (1, 10010, '唐大师') ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

执行的效果如下，执行后，可以看到第一行记录因为主键冲突，而更新了这条记录的sn和name

MariaDB [hello_mysql]> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 |  1000 | 小明      | 123124 |
|   2 |  1001 | 小红      | NULL   |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
+-----+-------+-----------+--------+
4 rows in set (0.000 sec)

MariaDB [hello_mysql]> INSERT INTO students (id, sn, name) VALUES (1, 10010, '唐大师')
    -> ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
Query OK, 2 rows affected (0.001 sec)

MariaDB [hello_mysql]> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 | 10010 | 唐大师    | 123124 |
|   2 |  1001 | 小红      | NULL   |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
+-----+-------+-----------+--------+
4 rows in set (0.000 sec)

唯一键冲突的时候，也是一样的效果，下图中是唯一键sn与原有的第二条记录冲突，最终将第二条记录的sn和name做了更新。

MariaDB [hello_mysql]> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 | 10010 | 唐大师    | 123124 |
|   2 |  1001 | 小红      | NULL   |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
+-----+-------+-----------+--------+
4 rows in set (0.000 sec)

MariaDB [hello_mysql]> INSERT INTO students (id, sn, name) VALUES (3, 1001, '唐大师2') ON DUPLICATE KEY UPDATE sn = 1005, name = '唐大师';
Query OK, 2 rows affected (0.001 sec)

MariaDB [hello_mysql]> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 | 10010 | 唐大师    | 123124 |
|   2 |  1005 | 唐大师    | NULL   |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
+-----+-------+-----------+--------+
4 rows in set (0.000 sec)

需要注意的是，在UPDATE语句之后的sn也不能和已有的sn冲突，不然会因为唯一键冲突（因为你需要更新到这个值，而这个值已有了）而拒绝更新

1
2

MariaDB [hello_mysql]> INSERT INTO students (id, sn, name) VALUES (3, 1001, '唐大师2') ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
ERROR 1062 (23000): Duplicate entry '10010' for key 'sn'

3.1.4 替换 Replace

除了上面那种更新的方式，我们还有一个替换语句可以用来替换值

如果没有冲突则直接插入
如果已经冲突了则删除原有的记录，然后再插入

这里要注意替换和插入否则更新的区别！插入否则更新是需要你指定要更新那些列的数据，而替换是会直接将原视数据删除的！

下面给出两条sql语句，其中第一条没有冲突，第二条的sn冲突

-- 无冲突
REPLACE INTO students (sn,name) VALUES (20000,'闰土');
-- 有冲突
REPLACE INTO students (sn,name) VALUES (1005,'周树人');

先执行第一个sql，可以看到返回的是1行受到影响，代表数据被插入

1 2	MariaDB [hello_mysql]> REPLACE INTO students (sn,name) VALUES (20000,'闰土'); Query OK, 1 row affected (0.001 sec)

再执行有冲突的，可以看到是2行受影响，因为这条语句会删除原有的再插入，相当于执行了一次删除和一次插入，影响了两行的数据。

1 2	MariaDB [hello_mysql]> REPLACE INTO students (sn,name) VALUES (1005,'周树人'); Query OK, 2 rows affected (0.001 sec)

3.2 查询 Retrieve

查询的基础语法如下

SELECT
    [DISTINCT] {* | {column [, column] ...}
    [FROM table_name]
    [WHERE ...]
    [ORDER BY column [ASC | DESC], ...]
    LIMIT ...

3.2.1 全列查询

用通配符*表示查询整个表的数据

1	select * from students;

该语句会查表中的所有值，在前面我们已经接触过了

MariaDB [hello_mysql]> select * from  students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 | 10010 | 唐大师    | 123124 |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
| 104 | 20000 | 闰土      | NULL   |
| 105 |  1005 | 周树人    | NULL   |
+-----+-------+-----------+--------+
5 rows in set (0.000 sec)

不过一般情况下不建议用全列查询：

查询的列越多，意味着需要传输的数据量越大；
可能会影响到索引的使用（后续会学习到）

3.2.2 指定列查询

所谓指定列，就是在查询的时候返回特定的列，比如如下就是在学生表里面返回sn和name这两列的数据

1	select sn,name from students;

执行效果如下，最终只显示了我们指定的两个列，并没有把id和qq列也给展示出来

MariaDB [hello_mysql]> select sn,name from  students;
+-------+-----------+
| sn    | name      |
+-------+-----------+
| 10010 | 唐大师    |
| 20001 | 曹孟德    |
| 20002 | 孙仲谋    |
| 20000 | 闰土      |
|  1005 | 周树人    |
+-------+-----------+
5 rows in set (0.000 sec)

3.2.3 表达式查询

为了进行后续的学习，我们先来构造一个测试表

-- 表创建
CREATE TABLE exam_result (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL COMMENT '同学姓名',
    chinese float DEFAULT 0.0 COMMENT '语文成绩',
    math float DEFAULT 0.0 COMMENT '数学成绩',
    english float DEFAULT 0.0 COMMENT '英语成绩'
);
-- 插入多行测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
    ('唐三藏', 67, 98, 56),
    ('孙悟空', 87, 78, 77),
    ('猪悟能', 88, 98, 90),
    ('曹孟德', 82, 84, 67),
    ('刘玄德', 55, 85, 45),
    ('孙权', 70, 73, 78),
    ('宋公明', 75, 65, 30);

在前面两种查询中，我们都只是查询表里面已经有的数据，但实际上我们在查询结果展示的时候，还可以让MySQL帮我们完成一定的表达式计算

1	select id,name,english,10 from exam_result;

比如如上的sql，我们在原本的指定列查询后新增了一个10，这个10就是一个表达式，它并不包含表中已经有的字段，所以会拼接在表的原有数据之后展示出来

MariaDB [hello_mysql]> select id,name,english,10 from exam_result;
+----+-----------+---------+----+
| id | name      | english | 10 |
+----+-----------+---------+----+
|  1 | 唐三藏    |      56 | 10 |
|  2 | 孙悟空    |      77 | 10 |
|  3 | 猪悟能    |      90 | 10 |
|  4 | 曹孟德    |      67 | 10 |
|  5 | 刘玄德    |      45 | 10 |
|  6 | 孙权      |      78 | 10 |
|  7 | 宋公明    |      30 | 10 |
+----+-----------+---------+----+
7 rows in set (0.000 sec)

在这里写个算式，MySQL也会帮我们计算并展示出来

MariaDB [hello_mysql]> select id,name,english,10+20 from exam_result;
+----+-----------+---------+-------+
| id | name      | english | 10+20 |
+----+-----------+---------+-------+
|  1 | 唐三藏    |      56 |    30 |
|  2 | 孙悟空    |      77 |    30 |
|  3 | 猪悟能    |      90 |    30 |
|  4 | 曹孟德    |      67 |    30 |
|  5 | 刘玄德    |      45 |    30 |
|  6 | 孙权      |      78 |    30 |
|  7 | 宋公明    |      30 |    30 |
+----+-----------+---------+-------+
7 rows in set (0.001 sec)

这时候我们就可以在返回结果的时候，对原视的数据做一些处理，比如将数学和英语成绩给加起来返回

MariaDB [hello_mysql]> select id,name,english+math from exam_result;
+----+-----------+--------------+
| id | name      | english+math |
+----+-----------+--------------+
|  1 | 唐三藏    |          154 |
|  2 | 孙悟空    |          155 |
|  3 | 猪悟能    |          188 |
|  4 | 曹孟德    |          151 |
|  5 | 刘玄德    |          130 |
|  6 | 孙权      |          151 |
|  7 | 宋公明    |           95 |
+----+-----------+--------------+
7 rows in set (0.000 sec)

3.2.4 为表达式设置别名

在表达式查询的时候，我们可以给表达式设置一些别名来提高可读性，基础语法如下

1	SELECT column [AS] alias_name [...] FROM table_name;

比如当我们将三科总分加起来返回的时候，就可以将其设置一个总分别名来返回

MariaDB [hello_mysql]> select id,name,chinese+english+math as '总分' from exam_result;
+----+-----------+--------+
| id | name      | 总分   |
+----+-----------+--------+
|  1 | 唐三藏    |    221 |
|  2 | 孙悟空    |    242 |
|  3 | 猪悟能    |    276 |
|  4 | 曹孟德    |    233 |
|  5 | 刘玄德    |    185 |
|  6 | 孙权      |    221 |
|  7 | 宋公明    |    170 |
+----+-----------+--------+
7 rows in set (0.000 sec)

此时MySQL展示的是总分，而不是原视表达式，可读性更好！

3.2.5 结果去重 distinct

有的时候我们想知道班里同学的成绩都有那些分段，并不想看到底是谁考出来的。此时就可以使用distinct去重查询过滤出不重复的一个集和并展示出来

1	select distinct math from exam_result;

如上语句就是在展示math列的结果的时候对结果进行去重，原本的数据中98分是重复的，添加上这个关键字后，重复的98就被去掉了。

MariaDB [hello_mysql]> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   98 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
7 rows in set (0.000 sec)

MariaDB [hello_mysql]> select distinct math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.001 sec)

3.2.6 where条件查询

运算符

说完了基本的查询，就得来看大头条件查询了，基本用法是在表名后用where带上具体条件。在那之前，先来看看where支持什么比较运算符吧

比较运算符	说明
`>,>=,<,<=`	这个应该不需要说吧
`=`	等于，NULL不安全，`NULL=NULL`的结果是NULL
`<=>`	等于，NULL安全，`NULL<=>NULL`的结果是 `TRUE(1)`
`!=,<>`	不等于
`BETWEEN a AND b`	范围匹配，闭区间，如果 `a<=value<=b`返回 `TRUE(1)`
`IN (option,...)`	如果是option中的任意一个，则返回 `TRUE(1)`
`IS NULL`	是NULL
`IS NOT NULL`	不是NULL
`LIKE`	模糊匹配，`%`表示任意多个（包括0个）字符；`_`表示任意一个字符；

除了比较的运算符，还有逻辑运算符

逻辑运算符号	说明
AND	与，相当于cpp中的`&&`，全真为1，有假为0
OR	或，相当于cpp中的`
NOT	逻辑取反，条件为`TRUE(1)`的时候结果为`FALSE(0)`

大于小于和区间筛选

比如最基础的，查询成绩表里面某科成绩低于多少分的用户

1 2	select name,english from exam_result where english < 60;

运行结果如下，成功展示出来了英语成绩小于60分的人

MariaDB [hello_mysql]> select name,english from exam_result where english < 60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.000 sec)

我们还可以用and进行拼接多个条件

1 2	select name,chinese,english from exam_result where english < 60 and chinese < 60;

比如如上sql是查询英语和语文都不及格的人

MariaDB [hello_mysql]> select name,chinese,english from exam_result where english < 60 and chinese < 60;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 刘玄德    |      55 |      45 |
+-----------+---------+---------+
1 row in set (0.000 sec)

还有通过between ... and ...语句查询语文成绩在[30,60]区间的人

MariaDB [hello_mysql]> select name,chinese from exam_result where chinese between 30 and 65;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 刘玄德    |      55 |
+-----------+---------+
1 row in set (0.000 sec)

当然，大于小于的比较也可以用于两个字段之间的比较，只要两个字段是可比较的（比如都是integer），比如如下筛选出语文比英语好的同学

MariaDB [hello_mysql]> SELECT name, chinese, english FROM exam_result WHERE chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.000 sec)

相等筛选

select name,math from exam_result 
where math = 50 
	or math = 59 
	or math = 98 
	or math = 99;

如上语句可以帮我们筛选出数学成绩等于 50、59、98、99其中一个的学生

MariaDB [hello_mysql]> select name,math from exam_result where math = 50 or math = 59 or math = 98 or math = 99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.000 sec)

和这个语句相同效果的，还有如下的in运算符

MariaDB [hello_mysql]> select name,math from exam_result where math in (50,59,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.001 sec)

字符匹配 LIKE

-- % 匹配多个字符，如下语句代表匹配名字为孙开头的人
select name from exam_result where name LIKE '孙%';
-- _ 匹配单个字符，如下语句代表匹配名字开头为孙，但只有两个字的人
select name from exam_result where name LIKE '孙_';

效果如下，可以看到在第二个单字匹配的时候，就没有将孙悟空也匹配进去

MariaDB [hello_mysql]> select name from exam_result where name LIKE '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.013 sec)

MariaDB [hello_mysql]> select name from exam_result where name LIKE '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.000 sec)

如下语句可以筛选出语文成绩大于80分且不姓孙的同学，同时用到了and和not

MariaDB [hello_mysql]> SELECT name, chinese FROM exam_result
    -> WHERE chinese > 80 AND name NOT LIKE '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.000 sec)

计算后筛选

上面我们筛选的都是字段本身，但我们还可以将一些字段加起来之后再进行筛选；

MariaDB [hello_mysql]> SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english < 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
2 rows in set (0.000 sec)

需要注意的是，这里虽然我们给三科分数加起来设置了别名，但是别名是不能在where条件中使用的！你可以理解为别名其实只是最终显示结果的时候被修改了，在查询的过程中别名实际上并没有生效！

1 2	MariaDB [hello_mysql]> SELECT name, chinese + math + english 总分 FROM exam_result WHERE 总分 < 200; ERROR 1054 (42S22): Unknown column '总分' in 'where clause'

NULL的查询

在之前的students表中，我们有些记录的qq号是NULL，这时候应该使用 IS NULL/IS NOT NULL来进行针对NULL的查询

-- qq号不是空的人
MariaDB [hello_mysql]> select * from students where qq is not null;
+----+-------+-----------+--------+
| id | sn    | name      | qq     |
+----+-------+-----------+--------+
|  1 | 10010 | 唐大师    | 123124 |
+----+-------+-----------+--------+
1 row in set (0.000 sec)
-- qq号是空的人
MariaDB [hello_mysql]> select * from students where qq is null;
+-----+-------+-----------+------+
| id  | sn    | name      | qq   |
+-----+-------+-----------+------+
| 102 | 20001 | 曹孟德    | NULL |
| 103 | 20002 | 孙仲谋    | NULL |
| 104 | 20000 | 闰土      | NULL |
| 105 |  1005 | 周树人    | NULL |
+-----+-------+-----------+------+
4 rows in set (0.000 sec)

这里我们还可以试验一下 =和<=>的不同，前文提到，=是NULL不安全的，这是因为NULL=NULL为NULL，而我们预期的结果应该是TRUE(1)。

从测试可以看出，使用 = 筛选出来的是空的，因为NULL在MySQL中认为是假，无法正常进行判断，而使用 <=>的成功筛选出了正确的结果

MariaDB [hello_mysql]> select * from students where qq = null;
Empty set (0.000 sec)

MariaDB [hello_mysql]> select * from students where qq <=> null;
+-----+-------+-----------+------+
| id  | sn    | name      | qq   |
+-----+-------+-----------+------+
| 102 | 20001 | 曹孟德    | NULL |
| 103 | 20002 | 孙仲谋    | NULL |
| 104 | 20000 | 闰土      | NULL |
| 105 |  1005 | 周树人    | NULL |
+-----+-------+-----------+------+
4 rows in set (0.000 sec)

同时我们可以将NULL和01进行一下对比，也能看出来NULL的特殊性；NULL和我们认识的真1与假0没有关系，其为一个独立的特殊值，NULL只能用来和NULL进行比较。

MariaDB [hello_mysql]> SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
|             1 |          0 |          0 |
+---------------+------------+------------+
1 row in set (0.000 sec)

MariaDB [hello_mysql]> SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
|        NULL |     NULL |     NULL |
+-------------+----------+----------+
1 row in set (0.000 sec)

查询条件不一定需要显示

如下，我们使用了math作为查询的条件，但最终展示的结果里面并没有math，这也是被允许的。

MariaDB [hello_mysql]> select name,chinese from exam_result where math > 70;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
| 刘玄德    |      55 |
| 孙权      |      70 |
+-----------+---------+
6 rows in set (0.012 sec)

3.2.7 结果排序

在很多时候，我们除了想获取到指定的结果，还希望对结果进行一定的排序，以筛选出更加精细的数据

-- ASC  为升序（从小到大）
-- DESC 为降序（从大到小）
-- ORDER BY 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
	ORDER BY column [ASC|DESC], [...];

注意：没有ORDER BY子句的查询，返回的顺序是未定义的（乱序）；

如果某个值是NULL，那它比任何值都小！

单字段升序/降序显示

1 2	SELECT name, math FROM exam_result ORDER BY math; -- 默认升序 SELECT name, math FROM exam_result ORDER BY math DESC; -- 降序

使用这个语句，可以让结果以某个字段的升序或者降序来显示；

MariaDB [hello_mysql]> SELECT name, math FROM exam_result ORDER BY math;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.012 sec)

MariaDB [hello_mysql]> SELECT name, math FROM exam_result ORDER BY math DESC;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
| 刘玄德    |   85 |
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
| 宋公明    |   65 |
+-----------+------+
7 rows in set (0.000 sec)

NULL比所有字段都小，比如下方排序的时候，NULL会显示在已有数据的下面；

MySQL对字符串的排序是按字典序的！会根据字符的 Unicode 值进行字典顺序的比较；

MariaDB [hello_mysql]> select name,qq from students order by qq desc;
+-----------+--------+
| name      | qq     |
+-----------+--------+
| 唐大师    | 123124 |
| 曹孟德    | NULL   |
| 孙仲谋    | NULL   |
| 闰土      | NULL   |
| 周树人    | NULL   |
+-----------+--------+
5 rows in set (0.001 sec)

排序的键值不一定需要出现在select的指定列中，如下我们最终返回的结果并没有qq，但依旧可以通过qq进行排序

MariaDB [hello_mysql]> select name from students order by qq desc;
+-----------+
| name      |
+-----------+
| 唐大师    |
| 曹孟德    |
| 孙仲谋    |
| 闰土      |
| 周树人    |
+-----------+
5 rows in set (0.000 sec)

多字段排序

多字段排序中，排序的优先级依照这里的书写顺序；

比如下面的语句就会先按数学进行降序排序，再按英语进行升序排序，最后按语文进行升序排序

1 2	SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;

效果如下

MariaDB [hello_mysql]> SELECT name, math, english, chinese FROM exam_result
    -> ORDER BY math DESC, english, chinese;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      56 |      67 |
| 猪悟能    |   98 |      90 |      88 |
| 刘玄德    |   85 |      45 |      55 |
| 曹孟德    |   84 |      67 |      82 |
| 孙悟空    |   78 |      77 |      87 |
| 孙权      |   73 |      78 |      70 |
| 宋公明    |   65 |      30 |      75 |
+-----------+------+---------+---------+
7 rows in set (0.000 sec)

求和后排序

在ORDER BY里面是支持表达式的，也支持别名

-- 表达式
SELECT name, chinese + english + math FROM exam_result
	ORDER BY chinese + english + math DESC;
-- 别名
SELECT name, chinese + english + math 总分 FROM exam_result
	ORDER BY 总分 DESC;

效果如下

MariaDB [hello_mysql]> SELECT name, chinese + english + math FROM exam_result
    -> ORDER BY chinese + english + math DESC;
+-----------+--------------------------+
| name      | chinese + english + math |
+-----------+--------------------------+
| 猪悟能    |                      276 |
| 孙悟空    |                      242 |
| 曹孟德    |                      233 |
| 唐三藏    |                      221 |
| 孙权      |                      221 |
| 刘玄德    |                      185 |
| 宋公明    |                      170 |
+-----------+--------------------------+
7 rows in set (0.000 sec)

MariaDB [hello_mysql]> SELECT name, chinese + english + math 总分 FROM exam_result
    -> ORDER BY 总分 DESC;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 猪悟能    |    276 |
| 孙悟空    |    242 |
| 曹孟德    |    233 |
| 唐三藏    |    221 |
| 孙权      |    221 |
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
7 rows in set (0.000 sec)

where+orderby

where条件语句和排序语句联合使用

1
2
3

SELECT name, math FROM exam_result
	WHERE name LIKE '孙%' OR name LIKE '曹%'
	ORDER BY math DESC;

MariaDB [hello_mysql]> SELECT name, math FROM exam_result
    -> WHERE name LIKE '孙%' OR name LIKE '曹%'
    -> ORDER BY math DESC;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.001 sec)

3.2.8 筛选分页结果

对表的筛选结果进行分页的语句格式如下

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

为什么需要分页？

假设一个表里面有上千万条数据，如果你使用select * from 表进行全列查询，那可能MySQL数据库就会因为要一次性给你返回大量数据而卡死，直接影响其他应用使用该数据库，导致服务宕机！

所以，在查询一个未知表的时候，建议先使用desc查看表结构，在再加上一个LIMIT 1，来查看很少的几条数据，并对数据进行人为分析，确定这个表的功能；

当然，在项目合作中，肯定需要有人编写文档来说明这些表的作用，遇到这种情况，应该先去查项目文档再进行操作。

1 2	SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;

上面的语句的意思是，从偏移量为0的未知（即表开头）筛选出3个数据；最终展示的就只有3条数据

MariaDB [hello_mysql]> SELECT id, name, math, english, chinese FROM exam_result
    -> ORDER BY id LIMIT 3 OFFSET 0;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  1 | 唐三藏    |   98 |      56 |      67 |
|  2 | 孙悟空    |   78 |      77 |      87 |
|  3 | 猪悟能    |   98 |      90 |      88 |
+----+-----------+------+---------+---------+
3 rows in set (0.000 sec)

修改offset，可以通过id看到数据的变化

MariaDB [hello_mysql]> SELECT id, name, math, english, chinese FROM exam_result
    -> ORDER BY id LIMIT 3 OFFSET 4;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  5 | 刘玄德    |   85 |      45 |      55 |
|  6 | 孙权      |   73 |      78 |      70 |
|  7 | 宋公明    |   65 |      30 |      75 |
+----+-----------+------+---------+---------+
3 rows in set (0.001 sec)

如果offset后并没有3个数据，也不会出问题，能正常显示出来

MariaDB [hello_mysql]> SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  7 | 宋公明    |   65 |      30 |      75 |
+----+-----------+------+---------+---------+
1 row in set (0.001 sec)

如果offset大于表中已有数据数量，那就会显示空，并不会执行出错！

1 2	MariaDB [hello_mysql]> SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 10; Empty set (0.001 sec)

3.2.9 执行顺序

在执行这些sql语句中的语法的时候，MySQL是会有一个执行顺序的

先通过from表从表中拿到数据
再通过where的条件筛选数据
根据 SELECT 子句中指定的列，MySQL 最终确定要返回的数据；
筛选完毕最终需要显示的数据后，根据order by对数据进行排序；排序可以使用别名；排序的字段不一定需要出现在select指定的列中；
如果查询包含 DISTINCT 关键字，MySQL 会进行去重操作，确保结果集中的行是唯一的；

在后文讲到聚合语句的时候，还会重谈一遍执行顺序

3.3 更新 Update

1	UPDATE 表名 SET 字段名 = 新值 WHERE 查询条件...;

3.3.1 更新单个记录

-- 查看原视数据
MariaDB [hello_mysql]> SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
+-----------+------+
1 row in set (0.000 sec)

-- 更新数据
MariaDB [hello_mysql]> UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.019 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 再次查看结果，成功更新
MariaDB [hello_mysql]> SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+
1 row in set (0.000 sec)

3.3.2 一次更新多列

MariaDB [hello_mysql]> SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.001 sec)

-- 一次更新多列 
MariaDB [hello_mysql]> UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hello_mysql]> SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.000 sec)

3.3.3 将总成绩倒数前三的数学成绩加上30分

-- 查看元数据
SELECT name, math, chinese + math + english 总分 FROM exam_result
	ORDER BY 总分 LIMIT 3;
-- 更新，需要注意不支持+=这种运算符
UPDATE exam_result SET math = math + 30
	ORDER BY chinese + math + english LIMIT 3;
-- 查看更新后结果
SELECT name, math, chinese + math + english 总分 FROM exam_result
	WHERE name IN ('宋公明', '刘玄德', '曹孟德');
-- 按总成绩排序后结果
SELECT name, math, chinese + math + english 总分 FROM exam_result
	ORDER BY 总分 LIMIT 3;

结果如下

MariaDB [hello_mysql]> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> ORDER BY 总分 LIMIT 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   65 |    170 |
| 刘玄德    |   85 |    185 |
| 曹孟德    |   60 |    197 |
+-----------+------+--------+
3 rows in set (0.000 sec)

MariaDB [hello_mysql]> UPDATE exam_result SET math = math + 30
    -> ORDER BY chinese + math + english LIMIT 3;
Query OK, 3 rows affected (0.043 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [hello_mysql]> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> WHERE name IN ('宋公明', '刘玄德', '曹孟德');
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 曹孟德    |   90 |    227 |
| 刘玄德    |  115 |    215 |
| 宋公明    |   95 |    200 |
+-----------+------+--------+
3 rows in set (0.000 sec)

MariaDB [hello_mysql]> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> ORDER BY 总分 LIMIT 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   95 |    200 |
| 刘玄德    |  115 |    215 |
| 唐三藏    |   98 |    221 |
+-----------+------+--------+
3 rows in set (0.000 sec)

3.3.4 将所有同学的语文成绩更新为原有的2倍

MariaDB [hello_mysql]> SELECT * FROM exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   80 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   90 |      67 |
|  5 | 刘玄德    |      55 |  115 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.000 sec)

MariaDB [hello_mysql]> UPDATE exam_result SET chinese = chinese * 2;
Query OK, 7 rows affected (0.001 sec)
Rows matched: 7  Changed: 7  Warnings: 0

MariaDB [hello_mysql]> SELECT * FROM exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   80 |      77 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.000 sec)

更新语句的基本操作就只有这些！

3.4 删除 Delete

语法

1	DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

3.4.1 删除孙悟空的考试成绩

1	DELETE FROM exam_result WHERE name = '孙悟空';

效果

MariaDB [hello_mysql]> SELECT * FROM exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   80 |      77 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.000 sec)

MariaDB [hello_mysql]> DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.043 sec)

MariaDB [hello_mysql]> SELECT * FROM exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
6 rows in set (0.000 sec)

3.4.2 删除表中所有数据（不是删除表）

慎用！不然就是从删库到跑路了！

1	DELETE FROM 表名;

注意，这个语句是删除表中所有数据，表不会被删除， AUTO_INCREMENT 项也不会被重置，会在原有基础上继续增加

3.4.3 截断表

语法如下，这个操作更要慎用。

1	TRUNCATE [TABLE] table_name

只能对整表操作，不能像 DELETE 一样针对部分数据操作；
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚；
会重置 AUTO_INCREMENT 项；

-- 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)

截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作

-- 截断表
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)
-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)

截断数据是会重置自增键值的当前值的，新插入的数据会从1开始重新自增；

-- 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D     |
+----+------+
1 row in set (0.00 sec)

在linux的文件操作里面，也有一个O_TRUNC标记位，作用是打开文件的时候，将文件原有内容清空；

3.5 插入查询结果

1	INSERT INTO table_name [(column [, column ...])] SELECT ...

这个语法的作用有两个

将表1的数据移植一部分到完全相同的表2（表结构要完全一致）
将表1的数据拷贝一部分到自己，相当于新增一部分冗余数据

比如我们想将一个表中的重复数据给删除，但是delete语句并没有直接与之相关的语法，这时候我们就可以采取如下措施

创建与表1完全相同的表2（名字改一下）
从表1中查询目标数据，并带上distinct对查询结果去重
将表1查询到的这个去重后的数据插入到表2
删除表1
重命名表2为表1的名字

以下是一个示例操作

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

-- 插入测试数据，有重复
INSERT INTO duplicate_table VALUES
    (100, 'aaa'),
    (100, 'aaa'),
    (200, 'bbb'),
    (200, 'bbb'),
    (200, 'bbb'),
    (300, 'ccc');
Query OK, 6 rows affected (0.002 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.00 sec)

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 重命名表
RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.00 sec)

-- 查看最终结果，数据没有重复
SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)

4.聚合函数

聚合统计是间接或者直接统计列方向上的某些数据

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

4.1 统计班级有多少人

当我们需要统计表中记录个数时，可以用下面的语句。

select count(*) from students;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.000 sec)

也可以使用下面的表达式进行统计。

SELECT COUNT(1) FROM students;
+----------+
| COUNT(1) |
+----------+
|        5 |
+----------+
1 row in set (0.000 sec)

你会发现这里虽然是count1，但是结果依旧是5。这是因为当我们在select的目标列里面添加一个常数或者一个表达式的时候，他会追加到原有表的末尾。这里我们直接写个1，就会把1追加到表中每一行的末尾（相当于新增一列，内容全为1），所以最终统计出来的结果依旧是表中记录的个数！

MariaDB [stu_test]> select * from student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
|    5 | 刘七   |
+------+--------+
5 rows in set (0.000 sec)

MariaDB [stu_test]> select *,1 from student;
+------+--------+---+
| id   | name   | 1 |
+------+--------+---+
|    1 | 张三   | 1 |
|    2 | 李四   | 1 |
|    3 | 王五   | 1 |
|    4 | 赵六   | 1 |
|    5 | 刘七   | 1 |
+------+--------+---+
5 rows in set (0.000 sec)

4.2 统计班级有多少人有qq号

这里能看出来NULL是不计入count的

> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 | 10010 | 唐大师    | 123124 |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
| 104 | 20000 | 闰土      | NULL   |
| 105 |  1005 | 周树人    | NULL   |
+-----+-------+-----------+--------+
5 rows in set (0.000 sec)

> SELECT COUNT(qq) FROM students;
+-----------+
| COUNT(qq) |
+-----------+
|         1 |
+-----------+
1 row in set (0.000 sec)

但空字符串是会被正常统计的。这里又一次展现出了空字符串和NULL的区别，NULL表达的意思就是这条记录的这个字段是没有数据的！而空字符串也是字符串的一种，对于数据库来说依旧代表有数据。

MariaDB [hello_mysql]> insert into students values (2,10123,'胡图图','');
Query OK, 1 row affected (0.001 sec)

MariaDB [hello_mysql]> select * from students;
+-----+-------+-----------+--------+
| id  | sn    | name      | qq     |
+-----+-------+-----------+--------+
|   1 | 10010 | 唐大师    | 123124 |
|   2 | 10123 | 胡图图    |        |
| 102 | 20001 | 曹孟德    | NULL   |
| 103 | 20002 | 孙仲谋    | NULL   |
| 104 | 20000 | 闰土      | NULL   |
| 105 |  1005 | 周树人    | NULL   |
+-----+-------+-----------+--------+
6 rows in set (0.000 sec)

MariaDB [hello_mysql]> SELECT COUNT(qq) FROM students;
+-----------+
| COUNT(qq) |
+-----------+
|         2 |
+-----------+
1 row in set (0.000 sec)

4.3 统计数学成绩分数的个数

-- COUNT(math) 统计的是全部成绩
SELECT COUNT(math) FROM exam_result;
+-------------+
| COUNT(math) |
+-------------+
|           6 |
+-------------+
1 row in set (0.000 sec)

-- COUNT(DISTINCT math) 统计的是去重成绩数量
-- 这里的DISTINCT一定要放在括号里，不然是无效的，这点从语义上也能理解；
SELECT COUNT(DISTINCT math) FROM exam_result;
+----------------------+
| COUNT(DISTINCT math) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.001 sec)

4.4 统计数学成绩总分

MariaDB [hello_mysql]> SELECT SUM(math) FROM exam_result;
+-----------+
| SUM(math) |
+-----------+
|       569 |
+-----------+
1 row in set (0.000 sec)

-- 没有结果的时候，返回NULL
MariaDB [hello_mysql]> SELECT SUM(math) FROM exam_result WHERE math < 60;
+-----------+
| SUM(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.042 sec)

4.5 统计平均分

SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
+--------------+
| 平均总分      |
+--------------+
| 297.5        |
+--------------+

你也可以将sum和count连用来达到这个效果

SELECT SUM(chinese + math + english)/COUNT(1) 平均总分 FROM exam_result;
+--------------+
| 平均总分     |
+--------------+
|        297.5 |
+--------------+
1 row in set (0.001 sec)

4.6 统计最高/最低

返回英语的最高分

SELECT MAX(english) FROM exam_result;
+--------------+
| MAX(english) |
+--------------+
|          90  |
+--------------+
1 row in set (0.00 sec)

返回大于七十分以上的数学最低分

SELECT MIN(math) FROM exam_result WHERE math > 70;
+-----------+
| MIN(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.001 sec)

5.group by

基本语法如下，含义是在select的时候对结果进行一定的分组

1	select column1, column2, .. from table group by column;

5.0 测试表

这里给大家准备了一个比较经典的测试表，来自oracle 9i，你可以在我的Gitee仓库找到他；

使用ftp将这个sql传到linux里面，进入mysql的命令行，使用source 文件路径的语句，就可以将这个表里面的数据导入到mysql中；

1	source /root/scott_data.sql

这个sql里面包含EMP员工表、DEPT部门表、SALGRADE工资等级表三张表结构，很方便我们进行测试；表结构如下

MariaDB [scott]> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
| dname  | varchar(14)              | YES  |     | NULL    |       |
| loc    | varchar(13)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [scott]> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES  |     | NULL    |       |
| losal | int(11) | YES  |     | NULL    |       |
| hisal | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [scott]> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.001 sec)

5.1 如何显示每个部门的平均工资和最高工资

select deptno,avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.012 sec)

需要注意的是，这里的聚合函数（比如avg(sal)）是无法用作where的条件的，因为where的执行顺序早于聚合函数，你连目标数据都还没有通过where筛选出来，怎么可以聚合呢？

1	select deptno,avg(sal),max(sal) from emp where avg(sal)>200 group by deptno;

尝试用聚合函数来做where的筛选条件，执行的时候会报错

1 2	MariaDB [scott]> select deptno,avg(sal),max(sal) from emp where avg(sal)>200 group by deptno; ERROR 1111 (HY000): Invalid use of group function

group by的限制

在MySQL中，当你使用了group by之后，select的结果指定列就只能包含group by里面出现过的列，以及聚合函数；

但在我当前使用的mariadb中并没有出现这个报错，即便group by后没有出现ename字段，数据依旧可以被筛选出来。

MariaDB [scott]> select ename,deptno,job,avg(sal) as 平均工资, min(sal) as 最低工资 from emp group by deptno,job;
+--------+--------+-----------+--------------+--------------+
| ename  | deptno | job       | 平均工资     | 最低工资     |
+--------+--------+-----------+--------------+--------------+
| MILLER |     10 | CLERK     |  1300.000000 |      1300.00 |
| CLARK  |     10 | MANAGER   |  2450.000000 |      2450.00 |
| KING   |     10 | PRESIDENT |  5000.000000 |      5000.00 |
| SCOTT  |     20 | ANALYST   |  3000.000000 |      3000.00 |
| SMITH  |     20 | CLERK     |   950.000000 |       800.00 |
| JONES  |     20 | MANAGER   |  2975.000000 |      2975.00 |
| JAMES  |     30 | CLERK     |   950.000000 |       950.00 |
| BLAKE  |     30 | MANAGER   |  2850.000000 |      2850.00 |
| ALLEN  |     30 | SALESMAN  |  1400.000000 |      1250.00 |
+--------+--------+-----------+--------------+--------------+
9 rows in set (0.001 sec)

为了确认这个问题，我使用docker部署了一个旧版本的MySQL5.7，加载scott表后使用相同的SQL命令进行测试。在Linux中安装完毕docker后，使用如下命令创建一个MySQL5.7版本的docker容器。

sudo docker run -d \
    -p 13306:3306 \
    -v /home/wsl/docker/mysql5.7/log:/var/log/mysql \
    -v /home/wsl/docker/mysql5.7/data:/var/lib/mysql \
    -v /home/wsl/docker/mysql5.7/conf:/etc/mysql/conf.d \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --name mysql57 \
    mysql:5.7

将scott_data.sql拷贝到/home/wsl/docker/mysql5.7/conf里面，并使用如下命令在MySQL命令行中加载

## 在linux中执行如下命令进入docker中MySQL的命令行
sudo docker exec -it mysql57 mysql -uroot -p123456
## 在MySQL命令行中加载sql文件
source /etc/mysql/conf.d/scott_data.sql

随后切换到数据库，尝试执行上面这个group by的SQL语句。

1
2
3

select ename,deptno,job,avg(sal), min(sal)
from emp 
group by deptno,job;

会发现在MySQL5.7中是不允许这么执行的，因为ename并不在group by的分组列中。

mysql> select ename,deptno,job,avg(sal), min(sal)
    -> from emp 
    -> group by deptno,job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果将select中的ename去掉，则可以被执行

mysql> select deptno,job,avg(sal), min(sal) from emp  group by deptno,job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     10 | CLERK     | 1300.000000 |  1300.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     20 | CLERK     |  950.000000 |   800.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

然后，我又在一个MySQL 8.0.30的docker中尝试执行相同操作（这里就不复述加载表的命令了）会发现MySQL8中同样存在这个限制！

mysql> select ename,deptno,job,avg(sal), min(sal)
    -> from emp 
    -> group by deptno,job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select deptno,job,avg(sal), min(sal) from emp  group by deptno,job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     20 | CLERK     |  950.000000 |   800.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     10 | CLERK     | 1300.000000 |  1300.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

结论：MySQL5.7和8.0.30中都存在对select后只能出现group by中已出现的列或聚合函数的限制，而10.6.16-MariaDB不存在这个限制！

5.2 显示每个部门的每种岗位的平均工资和最低工资

使用聚合函数和group by来解决，题目要求是每个部门+每个岗位，所以有两个筛选条件，group by后面也要带上这两个列。

> select avg(sal),min(sal),job, deptno from emp group by deptno, job;
+-------------+----------+-----------+--------+
| avg(sal)    | min(sal) | job       | deptno |
+-------------+----------+-----------+--------+
| 1300.000000 |  1300.00 | CLERK     |     10 |
| 2450.000000 |  2450.00 | MANAGER   |     10 |
| 5000.000000 |  5000.00 | PRESIDENT |     10 |
| 3000.000000 |  3000.00 | ANALYST   |     20 |
|  950.000000 |   800.00 | CLERK     |     20 |
| 2975.000000 |  2975.00 | MANAGER   |     20 |
|  950.000000 |   950.00 | CLERK     |     30 |
| 2850.000000 |  2850.00 | MANAGER   |     30 |
| 1400.000000 |  1250.00 | SALESMAN  |     30 |
+-------------+----------+-----------+--------+
9 rows in set (0.000 sec)

5.3 显示平均工资低于2000的部门和他的平均工资

因为聚合函数的结果无法作为where的条件，所以MySQL额外提供了一个having关键字，用于在聚合查询中，可以使用聚合函数对数据进一步进行筛选。

-- 统计各个部门的平均工资
select avg(sal) from emp group by deptno
-- 通过having筛选出平均工资低于2000的结果
select avg(sal) as myavg from emp group by deptno having myavg<2000;

这里可以看到，having是可以支持select中设置的别名的，这也告诉我们，having的语句是晚于select指定列执行的；

> select avg(sal) as myavg from emp group by deptno having myavg<2000;
+-------------+
| myavg       |
+-------------+
| 1566.666667 |
+-------------+
1 row in set (0.042 sec)

5.4 显示10号部门的某个岗位的平均工资低于2000的岗位

having和where可以同时使用，where用于初步的拆选，having用于最终结果的筛选。

select job,avg(sal),min(sal) from emp
where deptno = 10
group by job
having avg(sal) < 2000;

结果如下，可见在10号部门中，只有CLERK岗位的平均工资低于2000元。

MariaDB [scott]> select job,avg(sal),min(sal) from emp where deptno = 10 group by job having avg(sal) < 2000;
+-------+-------------+----------+
| job   | avg(sal)    | min(sal) |
+-------+-------------+----------+
| CLERK | 1300.000000 |  1300.00 |
+-------+-------------+----------+
1 row in set (0.001 sec)

5.5 小结

group by通过对结果集分组，为聚合统计提供基本的功能支持；
group by后跟随的是分组的字段依据，只有在group by后面出现过的字段，在聚合统计时，才能在select中出现（MySQL中存在此限制，MariaDB取消了此限制）；
where语句是在表中数据初步被筛选的时候，来起效果的;
having语句是在完成整个分组聚合统计后再进行筛选;

6.SQL各个语句执行顺序

面试的时候常考，建议记住

1
2
3

from > on > join > where
> group by > with > having > select
> distinct > order by > limit

以如下的语句为例，执行顺序参考我的圆圈数字标记

select deptno, avg(sal) as myavg ④ from emp ① 
	where sal > 1000 ②
	group by deptno ③
	having myavg < 2000 ⑤;

注意，select必须等having和group by筛选出数据之后才能执行。group by/having中可以使用select中设置的别名是MySQL进行了SQL优化之后的结果。

7.视图

7.1 创建视图

在MySQL中还有一个特殊的虚拟表，被称作视图；其可以将一个select语句的查询结果视作一个新表，供我们使用；

请注意！这里的视图要和事务中的ReadView做区分，两者毫无关系！

1	create view 视图名 as select ...;

比如在scott的测试表中，包含如下3个表；

MariaDB [scott]> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.001 sec)

我们可以用如下语句创建一个视图

create view v_ename_dname as 
	select ename, dname
	from emp,dept 
	where emp.deptno=dept.deptno;

创建了之后，这个视图会以一个新表的形式出现在tables中

MariaDB [scott]> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
| v_ename_dname   |
+-----------------+
4 rows in set (0.000 sec)

这个表的查询结果和直接执行原有的select语句是完全一致的。

我们把视图涉及到的数据原有表叫做基表；

MariaDB [scott]> select * from v_ename_dname;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.001 sec)

进入MySQL的存储路径，你会发现视图只有一个.frm文件，是不会有保存数据的.ibd文件的；这也代表视图是一个变相的数据链接，并不会拷贝数据；

1 2	[root@vm-cnt8:/var/lib/mysql/scott]# ls db.opt dept.frm dept.ibd emp.frm emp.ibd salgrade.frm salgrade.ibd v_ename_dname.frm

如果修改视图，会对基表数据有影响；修改基表，也会对视图有影响；

删除视图不会影响基表中的数据；

1	drop view 视图;

7.2 视图的规则和限制

对于MySQL而言，视图是一个用于权限控制/应用控制的好方式。你可以给某些用户特定视图的权限，而不给他们整张表的权限。这样就能在一定程度上避免数据泄露，保护安全性。

与表一样，必须唯一命名（不能出现同名视图或表名）
创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响
视图不能添加索引，也不能有关联的触发器或者默认值
视图可以提高安全性，必须具有足够的访问权限；
order by 可以用在查询视图的数据中，但是如果从该视图检索数据 select 中也含有 order by，那么该视图查询中的 order by 将被覆盖
视图可以和表一起使用
不是所有视图都可以被更新

8.从文件中加载数据到对应表

8.1 基本语法

mariadb 支持通过 load 命令命令加载文件中的数据到对应表。命令格式如下

1
2
3

LOAD DATA INFILE '/path/to/data/file' INTO TABLE my_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

其中 /path/to/data/file 是数据文件的路径，my_table 是要插入数据的表名。FIELDS TERMINATED BY ',' 指定字段之间的分隔符为逗号，ENCLOSED BY '"' 表示字段值被双引号包围，LINES TERMINATED BY '\n'则表示文件中的每一行以换行符结束。

8.2 测试

比如我有一个 pc.txt 文件, 文件中的格式如下，每一行都是对齐的，每一列用空格作为分割，和字段要求对应。

1
2
3

1001 2.66 1024 250 2114
1002 2.10 512  250 955
1003 1.42 512  80  478

那么在创建出来一个对应的表后，可以用如下命令来加载文件中的数据

create table PC(
    model integer unsigned primary key,
    speed float(4,2),
    ram integer unsigned,
    hd integer unsigned,
    price integer unsigned
);

LOAD DATA INFILE '/root/pc.txt'
INTO TABLE PC
FIELDS TERMINATED BY '\t'
(model,speed,ram,hd,price);

执行后的效果如下，成功加载

MariaDB [products]> LOAD DATA INFILE '/root/pc.txt'
    -> INTO TABLE PC
    -> FIELDS TERMINATED BY '\t'
    -> (model,speed,ram,hd,price);
Query OK, 13 rows affected, 12 warnings (0.015 sec)  
Records: 13  Deleted: 0  Skipped: 0  Warnings: 12

MariaDB [products]> select * from PC;
+-------+-------+------+------+-------+
| model | speed | ram  | hd   | price |
+-------+-------+------+------+-------+
|  1001 |  2.66 | 1024 |  250 |  2114 |
|  1002 |  2.10 |  512 |  250 |   995 |
|  1003 |  1.42 |  512 |   80 |   478 |
|  1004 |  2.80 | 1024 |  250 |   649 |
|  1005 |  3.20 |  512 |  250 |   630 |
|  1006 |  3.20 | 1024 |  320 |  1049 |
|  1007 |  2.20 | 1024 |  200 |   510 |
|  1008 |  2.20 | 2048 |  250 |   770 |
|  1009 |  2.00 | 1024 |  250 |   650 |
|  1010 |  2.80 | 2048 |  300 |   770 |
|  1011 |  1.86 | 2048 |  160 |   959 |
|  1012 |  2.80 | 1024 |  160 |   649 |
|  1013 |  3.06 |  512 |   80 |   529 |
+-------+-------+------+------+-------+
13 rows in set (0.002 sec)

请注意，该命令不是万能的，很有可能出错！在加载之前，请在一个空的数据库中进行测试！

现在还有另外一个办法，就是把表结构和txt内容复制给GPT，让他帮你写个insert的SQL。

The end

基本知识就这些了!