本文记录了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
mysql数据库文件路径
mysql的语句是大小写不敏感的，大小写都可以。
如果你的MySQL有密码，那就是用如下命令进入MySQL命令行
1 2 mysql -uroot -p123456 mysql -u用户名 -p密码
0.1 查看有谁连接了
该命令可以查看当前有谁连接了MySQL服务，所在数据库是什么，正在执行的cmd是什么
0.2 查看支持的存储引擎
mysql支持多种存储引擎
存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
每个引擎各有其优势。可以用下面的语句查询当前支持的引擎
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 2 3 4 5 6 7 8 9 +--------------------+ | Database | +--------------------+ | hello | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.001 sec)
1.3 进入
进入这个数据库
1.4 删除
1 drop database [if exists] 名字;
如下，我先是创建了一个test1数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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 2 3 4 5 6 7 8 9 10 11 12 13 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
1 2 3 4 5 6 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.002 sec)
在配置mariadb的时候，就已经修改过了配置文件中数据库的编码规则，所以所有相关字符集编码都是utf-8
1 show variables like 'character_set_%';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 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相关的
1 2 3 4 5 6 7 8 9 10 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.5.3 创建库时指定
1 2 3 4 5 6 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文件，其中就包含了当前数据库使用的字符集和校验规则
1 2 3 4 5 [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字符集对应的校验和
1 2 3 4 5 [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 校验的影响
通过指定不同的编码，我创建了两个校验规则不同的数据库
1 2 3 4 5 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
1 2 3 4 5 6 [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数据库
创建一个student表，只有一个字符串类型的name字段
1 create table student(name varchar(30));
插入如下数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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
1 2 3 4 5 6 7 8 9 10 11 12 MariaDB [test_bin]> select * from student; +------+ | name | +------+ | a | | A | | b | | B | | c | | C | +------+ 6 rows in set (0.001 sec)
查询数据，可以看到，返回的结果是只有小写a的
1 2 3 4 5 6 7 MariaDB [test_bin]> select * from student where name='a'; +------+ | name | +------+ | a | +------+ 1 row in set (0.001 sec)
执行排序（升序）后输出，得到如下结果。我们知道，小写字母的ASCII是在大写字母的之后的，所以这个排序结果是正确的。
1 2 3 4 5 6 7 8 9 10 11 12 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
在这个数据库中进行相同的插入工作，创建表，插入大小写abc（命令同上不再重复）
1 2 3 4 5 6 7 8 9 10 11 12 MariaDB [test_ci]> select * from student; +------+ | name | +------+ | a | | A | | b | | B | | c | | C | +------+ 6 rows in set (0.000 sec)
执行查询语句，发现当我们查询小a的时候，同时返回了A和a的结果
1 2 3 4 5 6 7 8 MariaDB [test_ci]> select * from student where name='a'; +------+ | name | +------+ | a | | A | +------+ 2 rows in set (0.001 sec)
排序的时候，大小a没有按ASCII码顺序排，而是放到了一起
1 2 3 4 5 6 7 8 9 10 11 12 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)
修改后查看配置文件，字符集和校验规则确实变化了（系统自动查找对应的校验规则）
1 2 3 4 5 6 [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;
1 2 3 4 5 6 [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文件，其内容如下。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 ; ; ; ; ; ; ; ; ;; CREATE DATABASE `hello` ;USE `hello`; DROP TABLE IF EXISTS `stu_test`;; ; 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; ; LOCK TABLES `stu_test` WRITE; ; INSERT INTO `stu_test` VALUES (2 ,'小图图' ,5 ,72.80 ),(3 ,'大司马' ,42 ,87.30 ),(4 ,'乐迪' ,32 ,99.00 );; UNLOCK TABLES; ; ; ; ; ; ; ; ;
我们会发现这里面的内容其实都是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;
数据库中的内容和原有数据库是完全相同的
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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系统命令
比如使用如上命令可以清空屏幕
2.表
如下命令需要先进入特定database才能执行
2.1 创建表
基础的创建操作如下。其中末尾的字符集、校验规则、存储引擎的设置项可以留空不填
1 2 3 4 5 create table 表名 ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;
示例如下，创建了一个student表，并配置了两个键值name和age，类型分别为字符串和int
1 2 3 4 create table student( name varchar(30), age int );
除了基础操作之外，我们还可以给这个表新增一个comment作为列备注
1 2 3 4 create table student( name varchar(30) comment '学生名字', age int comment '学生年龄' );
用下面的语句可以查看创建表时候的操作
这部分和备份的时候的sql也是一样的，mysql会帮我们自动规整语句，使其语法规则更规整；
如下语句也能看到创建表时所用命令
1 show create table stu_test\G
在数据库帮我们创建这张表的时候，如果没有手动指定引擎和字符集，则会使用数据库的默认设置；
1 2 3 4 5 6 7 8 9 10 11 12 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 2 3 4 5 6 7 MariaDB [hello]> show tables; +-----------------+ | Tables_in_hello | +-----------------+ | student | +-----------------+ 1 row in set (0.001 sec)
2.3 查看表的属性/结构
1 2 3 4 5 6 7 8 9 10 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 删除该表
2.5 修改表的结构
注意，这里是对表的属性的修改，并不是对数据的修改！
比如我想新增字段，或者修改已有字段的属性，或者删除字段
1 2 3 4 5 6 7 8 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;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 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列被删除了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 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)
在具体场景中，如果需要给已有用户新增头像，一般存放的是头像的文件路径（而不是图片），此时我们就可以设计一个默认头像，并在新增字段的时候将默认值（默认的头像文件路径）修改为这个默认头像图片的路径。
比如我现在的头像存储的文件路径是
此时存放的用户头像文件就应该放到这个目录中，而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 '头像图片路径' afterscore;
这时候就能看到，旧数据的头像都是默认头像了！
1 2 3 4 5 6 7 8 9 10 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 );
如下，成功修改！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 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)
因为是增加字段的长度，所以也不影响已有数据
1 2 3 4 5 6 7 8 9 10 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 );
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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数字改成字符串是可行的。那反过来呢？
1 2 3 4 5 6 7 8 9 10 11 12 13 14 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类型
1 2 3 4 5 6 7 8 9 10 11 12 13 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，因为我们修改的是字段名字而已，所以不会影响到数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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;
可以看到修改成功了
1 2 3 4 5 6 7 8 9 10 11 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（增查改删）
1 2 3 4 Create 创建 Retrieve 读取 Update 更新 Delete 删除
在MySQL中，我们将一行新增的数据，称为一条记录
3.1 新增 Create
要想往一个表里面插入数据，首先需要创建表（好像是一句废话）
先使用如下表进行测试
1 2 3 4 5 6 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' );
在学习插入之前，我们首先要会最基本的查询语句
3.1.1 单行全列插入
所谓单行，代表一次只插入一条记录；
所谓全列插入，代表插入这条数据的时候，将全部列的数据都手动指定。
1 2 insert into students values (1 ,1000 ,'小明' ,'123124' );insert into students values (2 ,1001 ,'小红' ,NULL );
如上就是两个全列插入的示例；执行完毕这条sql的时候，MySQL会显示 1 row affected，代表只有一行收到了影响（新增了一条记录）
1 2 3 4 5 6 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)
查询表中数据，可以看到我们刚刚插入的记录
1 2 3 4 5 6 7 8 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 , '孙仲谋' );
执行效果如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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 插入否则更新
如果我们往表里面插入一个已经存在的主键/唯一键的值，就会出现主键/唯一键冲突而导致的拒绝插入。
1 2 3 4 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 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做了更新。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 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冲突
1 2 3 4 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
查询的基础语法如下
1 2 3 4 5 6 SELECT [DISTINCT ] {* | {column [, column ] ...} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC ], ...] LIMIT ...
3.2.1 全列查询
用通配符*表示查询整个表的数据
该语句会查表中的所有值，在前面我们已经接触过了
1 2 3 4 5 6 7 8 9 10 11 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列也给展示出来
1 2 3 4 5 6 7 8 9 10 11 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 表达式查询
为了进行后续的学习，我们先来构造一个测试表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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就是一个表达式，它并不包含表中已经有的字段，所以会拼接在表的原有数据之后展示出来
1 2 3 4 5 6 7 8 9 10 11 12 13 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也会帮我们计算并展示出来
1 2 3 4 5 6 7 8 9 10 11 12 13 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)
这时候我们就可以在返回结果的时候，对原视的数据做一些处理，比如将数学和英语成绩给加起来返回
1 2 3 4 5 6 7 8 9 10 11 12 13 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;
比如当我们将三科总分加起来返回的时候，就可以将其设置一个总分别名来返回
1 2 3 4 5 6 7 8 9 10 11 12 13 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就被去掉了。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 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分的人
1 2 3 4 5 6 7 8 9 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是查询英语和语文都不及格的人
1 2 3 4 5 6 7 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]区间的人
1 2 3 4 5 6 7 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），比如如下筛选出语文比英语好的同学
1 2 3 4 5 6 7 8 9 10 11 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)
相等筛选
1 2 3 4 5 select name,math from exam_result where math = 50 or math = 59 or math = 98 or math = 99 ;
如上语句可以帮我们筛选出数学成绩等于 50、59、98、99其中一个的学生
1 2 3 4 5 6 7 8 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运算符
1 2 3 4 5 6 7 8 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
1 2 3 4 select name from exam_result where name LIKE '孙%' ;select name from exam_result where name LIKE '孙_' ;
效果如下，可以看到在第二个单字匹配的时候，就没有将孙悟空也匹配进去
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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
1 2 3 4 5 6 7 8 9 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)
计算后筛选
上面我们筛选的都是字段本身，但我们还可以将一些字段加起来之后再进行筛选；
1 2 3 4 5 6 7 8 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的查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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)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中认为是假，无法正常进行判断，而使用 <=>的成功筛选出了正确的结果
1 2 3 4 5 6 7 8 9 10 11 12 13 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进行比较。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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，这也是被允许的。
1 2 3 4 5 6 7 8 9 10 11 12 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 结果排序
在很多时候，我们除了想获取到指定的结果，还希望对结果进行一定的排序，以筛选出更加精细的数据
1 2 3 4 5 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 ;
使用这个语句，可以让结果以某个字段的升序或者降序来显示；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 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 值进行字典顺序的比较；
1 2 3 4 5 6 7 8 9 10 11 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进行排序
1 2 3 4 5 6 7 8 9 10 11 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;
效果如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 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里面是支持表达式的，也支持别名
1 2 3 4 5 6 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 ;
效果如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 ;
1 2 3 4 5 6 7 8 9 10 11 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 筛选分页结果
对表的筛选结果进行分页的语句格式如下
1 2 3 4 5 6 7 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT 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条数据
1 2 3 4 5 6 7 8 9 10 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看到数据的变化
1 2 3 4 5 6 7 8 9 10 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个数据，也不会出问题，能正常显示出来
1 2 3 4 5 6 7 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 更新单个记录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 -- 查看原视数据 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 一次更新多列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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分
1 2 3 4 5 6 7 8 9 10 11 12 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 ;
结果如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 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倍
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 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 = '孙悟空' ;
效果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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 删除表中所有数据（不是删除表）
慎用！不然就是从删库到跑路了！
注意，这个语句是删除表中所有数据，表不会被删除， AUTO_INCREMENT 项也不会被重置，会在原有基础上继续增加
3.4.3 截断表
语法如下，这个操作更要慎用。
1 TRUNCATE [TABLE ] table_name
只能对整表操作，不能像 DELETE 一样针对部分数据操作；
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务 ，所以无法回滚；
会重置 AUTO_INCREMENT 项；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 -- 准备测试表 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，所以实际上没有对数据真正操作
1 2 3 4 5 6 -- 截断表 TRUNCATE for_truncate; Query OK, 0 rows affected (0.10 sec) -- 查看删除结果 SELECT * FROM for_truncate; Empty set (0.00 sec)
截断数据是会重置自增键值的当前值的，新插入的数据会从1开始重新自增；
1 2 3 4 5 6 7 8 9 10 11 -- 再插入一条数据，自增 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的名字
以下是一个示例操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 -- 创建原数据表 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 统计班级有多少人
当我们需要统计表中记录个数时，可以用下面的语句。
1 2 3 4 5 6 7 select count (* ) from students;+ | count (* ) | + | 5 | + 1 row in set (0.000 sec)
也可以使用下面的表达式进行统计。
1 2 3 4 5 6 7 SELECT COUNT (1 ) FROM students;+ | COUNT (1 ) | + | 5 | + 1 row in set (0.000 sec)
你会发现这里虽然是count1，但是结果依旧是5。这是因为当我们在select的目标列里面添加一个常数或者一个表达式的时候，他会追加到原有表的末尾 。这里我们直接写个1，就会把1追加到表中每一行的末尾 （相当于新增一列，内容全为1），所以最终统计出来的结果依旧是表中记录的个数！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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的
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 > 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表达的意思就是这条记录的这个字段是没有数据的！而空字符串也是字符串的一种，对于数据库来说依旧代表有数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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 统计数学成绩分数的个数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 -- 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 统计数学成绩总分
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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 统计平均分
1 2 3 4 5 6 SELECT AVG(chinese + math + english) 平均总分 FROM exam_result; +--------------+ | 平均总分 | +--------------+ | 297.5 | +--------------+
你也可以将sum和count连用来达到这个效果
1 2 3 4 5 6 7 SELECT SUM(chinese + math + english)/COUNT(1) 平均总分 FROM exam_result; +--------------+ | 平均总分 | +--------------+ | 297.5 | +--------------+ 1 row in set (0.001 sec)
4.6 统计最高/最低
返回英语的最高分
1 2 3 4 5 6 7 SELECT MAX(english) FROM exam_result; +--------------+ | MAX(english) | +--------------+ | 90 | +--------------+ 1 row in set (0.00 sec)
返回大于七十分以上的数学最低分
1 2 3 4 5 6 7 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工资等级表三张表结构，很方便我们进行测试；表结构如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 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 如何显示每个部门的平均工资和最高工资
1 2 3 4 5 6 7 8 9 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字段，数据依旧可以被筛选出来。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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容器。
1 2 3 4 5 6 7 8 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命令行中加载
1 2 3 4 sudo docker exec -it mysql57 mysql -uroot -p123456source /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的分组列中。
1 2 3 4 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去掉，则可以被执行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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中同样存在这个限制！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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后面也要带上这两个列。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 > 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关键字，用于在聚合查询中，可以使用聚合函数 对数据进一步进行筛选。
1 2 3 4 select avg (sal) from emp group by deptnoselect avg (sal) as myavg from emp group by deptno having myavg< 2000 ;
这里可以看到，having是可以支持select中设置的别名 的，这也告诉我们，having的语句是晚于 select指定列执行的；
1 2 3 4 5 6 7 > 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用于最终结果的筛选。
1 2 3 4 select job,avg (sal),min (sal) from empwhere deptno = 10 group by jobhaving avg (sal) < 2000 ;
结果如下，可见在10号部门中，只有CLERK岗位的平均工资低于2000元。
1 2 3 4 5 6 7 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
以如下的语句为例，执行顺序参考我的圆圈数字标记
1 2 3 4 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个表；
1 2 3 4 5 6 7 8 9 MariaDB [scott]> show tables; +-----------------+ | Tables_in_scott | +-----------------+ | dept | | emp | | salgrade | +-----------------+ 3 rows in set (0.001 sec)
我们可以用如下语句创建一个视图
1 2 3 4 create view v_ename_dname as select ename, dname from emp,dept where emp.deptno= dept.deptno;
创建了之后，这个视图会以一个新表的形式出现在tables中
1 2 3 4 5 6 7 8 9 10 MariaDB [scott]> show tables; +-----------------+ | Tables_in_scott | +-----------------+ | dept | | emp | | salgrade | | v_ename_dname | +-----------------+ 4 rows in set (0.000 sec)
这个表的查询结果和直接执行原有的select语句是完全一致的。
我们把视图涉及到的数据原有表叫做基表 ；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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
如果修改视图，会对基表数据有影响；修改基表，也会对视图有影响 ；
删除视图不会影响基表中的数据；
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
那么在创建出来一个对应的表后，可以用如下命令来加载文件中的数据
1 2 3 4 5 6 7 create table PC( model integer unsigned primary key , speed float (4 ,2 ), ram integer unsigned, hd integer unsigned, price integer unsigned );
1 2 3 4 LOAD DATA INFILE '/root/pc.txt' INTO TABLE PCFIELDS TERMINATED BY '\t' (model,speed,ram,hd,price);
执行后的效果如下，成功加载
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 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
基本知识就这些了!
爱发电
