MySQL基本查询

---

前言

---

一、表的增删改查

1、Create

语法：

insert into 表名 (要插入数据的列) values(数据);

下面我们先创建一个students表。

然后我们向表中插入数据。如果我们不指定要插入数据的列，那么就是全列插入，即我们需要为每一列都输入数据。如果我们指定了要插入数据的列，那么只需输入指定列数据即可。我们还可以进行多行插入，每一行数据之间使用逗号分割开。

插入更新

当我们插入数据时，可能会由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。

此时我们可以选择性的进行同步更新操作。
语法：

insert into students (列名称) values(每一列值) on duplicate key update 如果发生冲突，需要更新的新数据。

下面的操作中，我们看到刚开始表中已经有id为10的数据了，然后我们再次插入id为10的貂蝉的数据，按理来说会失败，但是我们使用了插入更新，所以如果当前面貂蝉的数据中遇到了主键id冲突时，那么就会将id为10的数据更新为我们update后新给定的数据。我们看到这样的操作有两行数据受到了影响。

当表中已经有一个和我们update更新的数据一样的数据时，我们看到此时虽然发生了主键id冲突，但是并没有数据行受到影响，这是因为表中虽然有冲突，但是冲突数据的值和update更新的值相等，所以不会进行数据更新。

我们看到当没有冲突产生时，只有一行数据受到了影响。这就是表中没有冲突数据，所以on duplicate后面的update也不会执行。而是直接将前面没有产生冲突的数据插入到表中即可。

当主键或唯一键其中一个或两个都产生冲突时，才会执行后面update。而且新的数据可以和前面插入的数据不一样。

替换

替换是当主键或者唯一键有冲突时，会先将以前的数据删除，然后再将新的数据插入。
语法：

replace into students (列名称) values(列数据);

我们看到下面当主键和唯一键产生冲突时，有两行数据被影响。这就是因为发生了冲突，所以会先将表中原来的冲突数据删除，然后将新的数据插入到表中。

我们看到当插入的数据没有产生冲突时，那么只有一行数据收到了影响，这就是因为如果没有冲突，那么会直接将数据插入到表中。

2、Retrieve

select为检索语句。
语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]  //条件
[ORDER BY column [ASC | DESC], ...]  //分组或排序
LIMIT ...  //限制行数

下面我们先创建一个表。

下面我们向表中插入数据。

SELECT 列

下面我们来属性select的列查询。
通常情况下，我们不建议使用 * 来进行全列查询，因为查询的列越多，意味着需要传输的数据量越大。如果表中数据很多时，那么这个select * 查询就会占用很多资源。

我们看到select是可以查询并计算表达式的。

所以我们也可以在查询表中数据时，计算表达式。

我们还可以通过下面的语法来为查询结果指定别名。其中as是可以省略的。

select id,name,chinese+math+english as 总分 from exam_result;

我们还可以通过下面的语法来对查询结果进行去重。

select distinct math from exam_result;

WHERE 条件

比较运算符：

逻辑运算符：

运算符	说明
AND	多个条件必须都为TRUE(1)，结果才是TRUE(1)
OR	任意一个条件为TRUE(1)，结果为TRUE(1)
NOT	条件为TRUE(1)，结果为FALSE(0)

在这里我们需要特别注意的是 = 和 < = >。当比较NULL和NULL是否相等时，需要使用< = >。

下面我们来进行一些练习。
英语不及格的同学及英语成绩 ( < 60 )
我们可以先查看所有同学的英语成绩，然后再使用where条件筛选出来英语成绩小于60的同学的数据进行显示。select后面决定要显示哪些列，而where可以筛选出来满足条件的行数据。

select name,english from exam_result where english<60;

语文成绩在 [80, 90] 分的同学及语文成绩

//使用 AND 进行条件连接
select name,chinese from exam_result where chinese>=80 and chinese<=90;

//使用 BETWEEN ... AND ... 条件
select name,chinese from exam_result where chinese between 80 and 90;

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

//使用 OR 进行条件连接
select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

//使用 IN 条件
select name,math from exam_result where math in (58,59,98,99);

姓孙的同学及孙某同学
这个题我们可以使用模糊匹配。需要注意的是在LIKE后面如果跟%，那么表示后面可以为任意多个字符并且也可以为0个字符。而LIKE后面如果跟_，表示任意一个字符。

//姓孙的同学。% 匹配任意多个（包括 0 个）任意字符
select name from exam_result where name like '孙%';

//孙某同学。 _ 匹配严格的一个任意字符
select name from exam_result where name like '孙_';

语文成绩好于英语成绩的同学

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

总分在 200 分以下的同学

//WHERE 条件中使用表达式。别名不能用在 WHERE 条件中
select name,chinese+english+math as 总分 from exam_result where chinese+english+math<200;

我们看到在where后面我们需要重新输入一遍chinese+english+math，那么在where后面我们是否可以使用前面的重命名来表示chinese+english+math这个表达式呢？我们尝试后发现并不可以，并且错误信息提示我们说total这一列找不到。

这其实就和select语句执行的顺序有关了。当MySQL执行下面的语句时，会先分析要查询的表，然后分析where后面的满足条件的数据，然后再分析select后面的按照什么样的样式显示查询到的数据。所以当执行到第二步total<200时，此时还没有进行chinese+english+math as total的重命名，所以会找不到total。而且我们需要注意的是select控制要显示的列，所以只有在select后面才能进行重命名。

语文成绩 > 80 并且不姓孙的同学

//AND 与 NOT 的使用
select name,chinese from exam_result where chinese>80 and name not like '孙%';

孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
当where后面有多个条件时，我们可以通过加()的方式来使条件看起来更清晰。

//综合性查询
select name,chinese,english,math,chinese+english+math as total from exam_result where (name like '孙%' or (chinese<math and english>80);

NULL 的查询

我们先创建一个表。然后插入数据。

查找name为NULL的人

//使用 IS NULL
select id,name from test where name is null;

//使用<=>
select id,name from test where name<=>null;

查找名字为空串的人
我们需要注意空串不等于NULL，NULL表示什么都没有，并且不参与运算。而空串表示有，但是内容为空串。

select id,name from exam_result where name='';

ORDER BY 结果排序

结果排序可以将select显示的数据进行排序后显示。
语法：

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

我们需要注意没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

同学姓名及数学成绩，按数学成绩升序显示
order by默认的顺序为升序排序。

//指明排序顺序
select name,math from exam_result order by math asc;

//使用order by的默认升序排序
select name,math from exam_result order by math;

当有NULL时，NULL 视为比任何值都小，降序出现在最下面

select id,name from test order by id desc;

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
会先按照数学成绩降序排序，当数学成绩相同时才会按照英语升序排序，当英语成绩也相同时才会按照语文升序的方式排序。

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

查询同学及总分，由高到低

//不使用别名
select name,chinese+math+english as total from exam_result order by chinese+math+english desc;

//ORDER BY 子句中可以使用列别名
select name,chinese+math+english as total from exam_result order by total desc;

我们看到上面的语句中order by子句中可以使用列别名了。这是因为order by的执行顺序在select子句的后面。即先执行from exam_result子句确定要查询的表，然后再执行where子句进行数据筛选，然后执行select子句设置要显示的列，然后再执行order by子句将要显示的数据进行排序显示。

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

//结合 WHERE 子句 和 ORDER BY 子句
select name,math from exam_result where name like '孙%' like '曹%' order by math desc;

LIMIT 筛选分页结果

语法：

-- 起始下标为 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;

当我们对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

显示指定行的数据

显示指定区间的数据
我们看到当我们输入limit 2，4时，显示的数据id为3，6。这是因为limit 2，4的写法中2表示开始位置(下标从0开始，即0才代表id为1的数据)，4表示步长，即从指定位置开始，向后连续读取多少条记录。所以我们可以看到当输入limit 0,3时和输入limit 3显示的数据相同。因为limit 3是默认从0位置开始向后读取数据。


limit的offset写法
limit的这个写法中可以看着更明确。即limit后面的4表示读取4条数据，offset后面的2表示从2位置开始。

按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页
limit可以很好的实现分页显示。


得到总分大于200的前三的同学的成绩

select name,chinese+math+english as total from exam_result where chinese+math+english>200 order by total desc limit 3 offset 0;

下面我们来分析limit子句的执行顺序。我们需要知道limit的功能是分页显示，即只有当数据都准备好了并且排序好了之后，才进行分页显示，所以limit子句的执行顺序在最后。

3、Update

对查询到的结果进行列值更新。
语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

将孙悟空同学的数学成绩变更为 80 分

update exam_result set math=80 where name='孙悟空';

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60,chinese=70 where name='曹孟德';

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

update exam_result set math=math+30 order by chinese+math+english asc limit 3;

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

update exam_result set chinese=chinese*2;

4、Delete

删除数据

语法：

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

删除孙悟空同学的考试成绩

delete from exam_result where name='孙悟空';

删除总分为倒数第一名的人。

delete from exam_result order by chinese+math+english asc limit 1;

删除整张表数据
我们先创建一个表。并且设置表的主键id为自增长。然后我们向表中插入数据。

我们可以通过下面的语句来将表中数据全部删除。我们看到表中数据被删除完了。

delete from for_delete;

然后我们再次向表中插入数据，我们看到主键id是从4开始的，而不是从1重新开始。

截断表

语法：

TRUNCATE [TABLE] table_name

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

下面我们创建一个表，并且向表中插入数据。

然后我们使用下面的语句来截断表。

truncate for_truncate;

然后我们再次向表中插入数据，我们看到主键id是从1重新开始的，即TRUNCAST会重置 AUTO_INCREMENT 项。

5、插入查询结果

语法：

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

删除表中的的重复记录，重复的数据只能有一份
我们先创建一个表，然后向表中插入数据。

我们通过select distinct可以进行去重显示表中数据。

那么我们就可以先创建一个新表，然后将select distinct去重后的数据插入到新表中，这样新表中的数据就是去重后的数据了。然后我们再将新表进行重命名。
我们可以通过下面的语句来创建一个新表，并且使这个新表的表结构和旧表的表结构一样。

create table no_duplicate_table like duplicate_table;

然后我们通过下面的语句将查询结果插入到新表中。

insert into no_duplicate_table select distinct * from duplicate_table;

最后我们再对新表进行重命名，这样我们就完成了删除表中的重复记录。

6、聚合函数

这些聚合函数都可以添加distinct去重操作。

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

统计班级共有多少同学

select count(*) 总数 from exam_result;

统计数学成绩数量

select count(math) from exam_result;

统计去重后的有效数学成绩

select count(distinct math) as res from exam_result;

统计数学成绩总分

select sum(math) from exam_result;

计算英语不及格的同学的英语平均分
我们可以通过sum和count聚合函数运算求得平均分。

select sum(english)/count(english) from exam_result where english<60;

我们也可以直接使用AVG聚合函数来求出平均分。

select avg(english) from exam_result where english<60;

统计平均总分

select avg(chinese+math+english) from exam_result;

返回英语最高分

select max(english) from exam_result;

返回数学成绩 > 70 分以上的数学最低分

select min(math) from exam_result where math>70;

7、group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询。
语法：

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

我们先创建一个雇员信息表（来自oracle 9i的经典测试表）

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

我们查看创建的3个表。

显示每个部门的平均工资和最高工资
我们可以通过下面的语句显示员工中的最高工资和平均工资。

select max(sal) 最高, avg(sal) 平均 from emp;

那么我们想要显示每个部门的平均工资和最高工资时，只需要根据部门来分组即可，即根据员工的deptno部门编号来进行分组即可。然后显示的结果中就会使deptno相同的员工中的最高工作和平均工资显示出来。

select max(sal) 最高, avg(sal) 平均 from emp group by deptno;

显示每个部门的每种岗位的平均工资和最低工资
在分组时可以用逗号隔开不同的分组条件。

select min(sql) 最低, avg(sal) 平均 from emp group by deptno,jog;

在group by语句中，select后面只能跟分组的列，或者聚合函数。剩下的列都不能跟。

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

select deptno, avg(sal) 平均 from emp group by deptno having avg(sal)<2000;

下面我们来了解where和having条件筛选的区别。wherw是对具体的任意列进行条件筛选，而having是对分组聚合之后的结果进行条件筛选。所以where和having条件筛选的阶段是不同的。

显示每个部门的每种岗位的平均工资低于2000的岗位，但是SMITH员工不参与统计
此时我们就可以使用where和having组合的条件筛选。即我们先使用where条件筛选将SMITH员工除去，形成一个没有SMITH员工信息的中间表。然后我们再通过having条件筛选选出平均工资低于2000的岗位，然后将这个表显示出来。

select deptno,job, avg(sal) as myavg from emp where ename!='SMITH' group by deptno,job having myavg<2000;

下面为语句的执行顺序。我们不应该单纯的认为MySQL中只有真实存在的表才叫做表。我们在执行语句中间通过where和having筛选出来的每一个表，包括最终结果，其实都是一个表。只不过这些表没有真实的表名，只是逻辑上的表。所以我们就可以认为MySQL中一切皆为表。