MySql基本查询

1.插入否则更新(duplicate)

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

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，并且数据已经被更新

-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT();

 

2.替换(replace)

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

3.读取(retrieve)

我们先直接创建一个表

全列查询

-- 通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。（索引待后面课程讲解）
SELECT * FROM 表名;

指定列查询

SELECT 列名1, 列名2, ... FROM 表名;

查询字段为表达式

结果去重

当然我们也可以对多个字段去重

为查询结果指定别名

当然我们也可以对多组字段进行指定别名

这个as可以有 也可以没有

条件查询

我们先来看看运算符

我们直接上题目

第一题

英语不及格的同学及英语成绩 ( < 60 )

第二题

语文成绩在 (80, 90) 分的同学及语文成绩

当然了 用 between  ... and也可以

第三题

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

当然用in也可以

第四题

姓孙的同学 及 孙某同学

_（下划线）：是 MySQL 中的单字符通配符，它表示匹配任意一个字符。

%（百分号）：是 MySQL 中的多字符通配符，它表示匹配任意零个或多个字符。

第五题

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

第六题

总分在 200 分以下的同学

注意where后面只能接原本表存在的字段 取别名的字段无法使用

第七题

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

第八题

孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

NULL的查询

null不参与计算 因此> < =等比较符无法使用

当然 我们如果硬要比较NULL 就要用<=>符

如果比较的双方都是 NULL，NULL <=> NULL 的结果为 TRUE（1）。 如果一方是 NULL，另一方是具体的值，NULL <=> 具体值 的结果为 FALSE（0）

结果排序

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

第九题

同学及数学成绩，按数学成绩升序显示

写不写asc都可以 因为你没写默认就算asc

第十题

同学及 qq 号，按 qq 号排序显示

我们发现NULL 视为比任何值都小，降序出现在最下面

第十一题

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

-- 多字段排序，排序优先级随书写顺序

先排数学 在数学相等的情况下才再排英语，若英语数学都相等才再排英语

第十二题

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

我们发现order by后面是可以加取的别名的和where不一样的

第十三题

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

筛选分页结果

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

这里的下表是从0开始的

4.更新(update)

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

第一题

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

第二题

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

第三题

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

但是这个地方注意我们只能用limit 3 不能用limit 0,3 以及limit 0 offset 3尽管左后结果是一致的

第四题

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

没有where语句则更新全表

5.删除数据（delete）

第一题

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

删除整表

我们发现在删除整表后

-- 再插入一条数据，自增 id 在原值上增长

截断表

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事

物，所以无法回滚

3. 会重置 AUTO_INCREMENT 项

插入查询结果

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

第二题

删除表中的的重复复记录，重复的数据只能有一份

6.聚合函数

第一题

统计班级共有多少同学

这个地方一共是6个人 没有id2

count(1)和count(*)基本上一致

第二题

统计班级收集的 qq 号有多少

-- NULL 不会计入结果

第三题 

统计本次考试的语文成绩分数个数（去重的）

第四题

统计110分以上的语文成绩总分

当查找的数据没有时就会返回NULL；

第五题

统计平均总分

第六题

返回英语最高分

第七题

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

分组（group by）

分组前我们先建表

第一题

查看每个部门的平均工资

第二题

查看平均工资高于8000部门其的平均工资

having可以搭配group by使用 功能上和where相似

1. WHERE 的作用：筛选原始数据行 

比如要查询部门 1 中薪水大于 8000 的员工：

SELECT name, sal 
FROM employees 
WHERE dep = 1 AND sal > 8000; -- 筛选原始行，不涉及分组

2. HAVING 的作用：筛选分组后的结果 

比如要查询平均薪水大于 9000 的部门：

SELECT dep, AVG(sal) AS avg_sal 
FROM employees 
GROUP BY dep -- 先按部门分组
HAVING avg_sal > 9000; -- 筛选分组后的平均薪水>9000的部门

1. HAVING 可以和 SELECT 单独搭配使用吗？

可以，但通常需要配合聚合函数。

当SELECT中只有聚合函数且无GROUP BY时，HAVING可以单独使用（此时默认整个表为一个分组）。

示例：查询所有员工的总薪水是否大于 10 万：

SELECT SUM(sal) AS total_sal 
FROM employees 
HAVING total_sal > 100000; -- 无GROUP BY，默认整个表为一组

2. WHERE 可以单独和 GROUP BY 搭配使用吗？

可以。WHERE在GROUP BY之前执行，先筛选行再分组，是常见用法。

示例：先筛选部门 1 的员工，再计算该部门的平均薪水：

SELECT dep, AVG(sal) 
FROM employees 
WHERE dep = 1 -- 先筛选部门1的员工
GROUP BY dep; -- 再对筛选后的结果分组

总结

WHERE：分组前 “筛行”，不支持聚合函数，可单独用。

HAVING：分组后 “筛组”，支持聚合函数，通常需配合GROUP BY。

记住一句话：先 WHERE 筛行，再 GROUP BY 分组，最后 HAVING 筛组