MySQL:基本查询操作

插入

• 基本插入语法：

insert [into] 表名 (列1, 列2 ...) values (值1, 值2 ...);

create table students(
id int unsigned primary key auto_increment,
sn int not null unique comment '学号',
name varchar(20) not null,
tel varchar(20)
);

一次性指定所有值，那么省略(列1, 列2 ...)部分：

insert into students values (100, 20240001, '张三', NULL);

插入时只指定部分值，那么就需要通过(列1, 列2 ...)来指定：

insert into students (id, sn, name) values (101, 20240006, '小明');

也可以一次性插入多行，每一行的数据用(值1, 值2 ...)表示，不同行数据之间用逗号隔开：

insert into students (id, sn, name) values 
(105, 20240052, '小张'), 
(109, 20240096, '小李');

插入冲突

在插入数据时，有可能因为主键和唯一键的存在，导致冲突而插入失败。此时可以利用插入替换，或者直接替换。

插入替换：

• 插入替换语法：

insert ... on duplicate key update 列1=值1, 列2=值2 ...;

示例：

insert into students (id, sn, name) values (101, 20240066, '昊天') 
on duplicate key update sn=20240066;

其中insert ...表示一个完整的插入语句，该语句的作用是：insert语句如果冲突，则执行后面的列1=值1, 列2=值2 ...进行值替换。

插入替换的作用是：插入失败时，只替换部分指定的值，保留部分原先的值。 

直接替换：

插入时，也可以直接替换，只需把insert改为replace即可：

replace [into] 表名 (列1, 列2 ...) values (值1, 值2 ...);

这种直接替换，如果插入时发生冲突，会直接删除原先的行，重新插入当前行。

replace into students (id,sn,name) values (109, 20240100, '小杨');

查询

示例表格：

create table exam_result (
id ind unsigned primary akey 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),
('小朵', NULL, NULL, NULL);

• 基本查询语法

select 表达式1, 表达式2 ... from 表名;

此处的表达式含义非常广，它可以是列名，通配符*，或者基本字面量，计算式等。

1.通配符*做表达式，输出所有的列

测试表中，有id, name, chinses, math, english列，通过*全部都查询出来了

2.列名做表达式，输出指定的列

此处的表达式为name, chinese，所以只输出这两列。

3.基本字面量与计算式做表达式，输出计算结果

在计算表达式时，如果指定了表，那么这个表有几行，表达式就计算多少次。如果表达式中没有依赖表中的列，而是单纯的计算，那么可以不指定表名：

这次的查询中，没有指定表名，也没有使用表的列，那么表达式默认只计算一次，输出一行。

计算的意义在于：列与列之间同样可以计算！

第二列中，通过chinese + english + math计算出了总分。

但是这样看表的话，chinese + english + math这个整体作为列名有点长了，此时可以使用as来对列重命名。

• 使用as对列重命名：

select 表达式1 as 新名1, 表达式2 as 新名2 ... from 表名;

as也可以省略：

select 表达式1 新名1, 表达式2 新名2 ... from 表名;

distinct

直接查询math列：

此处出现了两个98，能否对查询结果去重？

• 使用distinct对查询结果去重：

select distinct 表达式1, 表达式2 ... from 表名;

where

观察一下之前的查询，会发现我们一直在对列做限制，而每次查询出来的行数目是一样的。如果说我们想对数据的值做限制，比如“数学成绩大于等于60分”，那么就需要where子句。

• 通过where子句来查询符合要求的数据：

select 表达式1, 表达式2 ... from 表名 where 表达式;

• 查询英语小于60分的同学：

select name, english from exam_result where english < 60;

• 查询数学成绩为临界值(58, 59, 98, 99)的学生：

select name, math from exam_result where math in (58, 59, 98, 99);

• 查询数学成绩大于成绩的同学：

select name, math, english from exam_result where math > english;

• 查询总分200以上的同学：

select name, math + english + chinese as total from exam_result 
where math + english + chinese > 200;

order by

如果想要对查询的结果进行排序，就需要order by子句。

• 通过order by子句对查询结果排序：

select ... from 表名 where ... order by 列1 asc|desc, 列2 asc|desc ... ;

asc表示升序，desc表示降序，如果不指定那么默认为asc升序。

• 以数学成绩升序展示所有成绩：

select name, math, chinese, english from exam_result order by math asc;

此处NULL被放在了第一列，在SQL中认为NULL比所有值都小。 

• 以总分降序查询：

select name, chinese + math + english as total from exam_result
order by chinese + math + english desc;

limit

假设已经查询好数据，排序完成了，如果只需要取用前三条数据，这要如何完成？此时就需要limit，其用于展示查询到的数据的指定部分。

• 输出前x条数据：

select ... from 表名 where ... order by ... limit x;

• 输出下标为[x, y]的数据（下标从0开始）：

select ... from 表名 where ... order by ... limit x, y;

select ... from 表名 where ... order by ... limit x offset y;

查询前三行：

select * from exam_result limit 3;

查询第2到5行：

select * from exam_result limit 1, 4;

[2, 5]的下标是[1, 4]，注意不要搞错了。

limit的执行顺序非常靠后，在整个数据查询，排序完毕后，才执行limit。

删除

MySQL中，有两种删除数据的方法，分别是delete和truncate，两者功能略有差别。

delete

• 通过delete删除数据

delete from 表名 where ... order by ... limit ...;

• 删除整张表的数据：

delete from exam_result;

执行select时查询不到任何数据，说明确实删除成功了。在show create table时，字段auto_increment=9，说明delete是不会清空自增长的。 

•  删除小朵所在行：

delete from exam_result where name = '小朵';

truncate

相比于delete，truncate有以下特点：

1. truncate只能用于删除整个表
2. truncate不经过事务，速度更快
3. 会重置auto_increment

• 使用truncate清空表的所有数据：

truncate table 表名;

truncate后，auto_increment字段的值就被重置了。

更新

• 更新表中数据

update 表名 set 列1 = 值1, 列2 = 值2 where ... order by ... limit ...

• 将小红的英语成绩改为100：

update exam_result set english = 100 where name = '小红';

• 将数学成绩倒数的三名同学，数学成绩加上10分：

update exam_result set math = math + 10 order by math asc limit 3;