6. MySQL基本查询

1. 表的增删改查

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

2. Create & Insert

语法: insert [info] table_name () values ()

2.1. 案例: 创建一个学生表

指定列单行插入, 如果values前省略, 则默认是全属性插入多行指定列插入, 中间分隔符为','

3. 插入替换

3.1. on duplicate: 语句1 on duplicate 语句2

insert into students values () on duplicate key update sn=132,name='xuyou',qq='111'

执行逻辑: 如果语句1插入失败, 则执行语句2, 语句2执行失败, 则报错

现象:

• 1 row affected: 表中没有冲突数据, 数据被插入
• 2 row affected: 表中存在冲突数据, 数据被更新
• 0 row affected: 表中有冲突数据, 且冲突数据的值和update的值相等(update执行失败)

3.2. replace: replace 语句1

执行逻辑: 主键 or 唯一键 没有冲突, 则直接插入; 主键 or 唯一键 存在冲突, 则删除后再插入

现象:

• 1 row affected: 表中没有冲突数据, 数据被插入
• 2 row affected: 表中存在冲突数据, 删除后重新插入

4. 基本select指令(查询)

4.1. 全列查询

举例: select * from table_name

可能会影响到索引的使用, 效率比较低

• 指定一/多列查询 select id, math from table_name
• select 后可以跟表达式 select math+chinese+english from table_name(计算总成绩)
• select 可以对表达式重命名 select math+chinese+english as(可省略) total from table_name(计算总成绩)
• select 支持去重 select distinct math from table_name

5. where子句 判断条件(过滤条件)

语法符号:

• >, >=, <, <=
• = 注: null不安全
• <=>注: null安全
• != 注: null不安全
• <>
• between a0 and a1 范围匹配
• in (option...)
• is null
• is not null
• like(% _) 模糊匹配
• and or not

5.1. 知识点: select * from table_name where 语句的执行顺序(⭐⭐⭐)

• 明确从哪个数据表中拿数据 from table_name
• 其次看要筛选哪些条件 where 语句
• 最后进行显示 select *

5.2. 案例

• 案例1: 找出成绩单中英语不及格(<60)的人的姓名 以及 对应的英语成绩
• 案例2: 语文成绩在[80, 90]区间的同学的姓名 + 对应的语文成绩
• 案例3: 数学成绩在58 || 59 || 98 || 99分的同学姓名 + 成绩
• 案例4: 找一下成绩单中姓孙的同学
• 案例5: 语文成绩好于英语成绩的同学
• 案例6: 总分在200分以下的同学
• 案例7: 语文成绩>80分 并且 不姓孙的同学
• 案例8: 孙某同学，否则要求总成绩>200 并且 语文成绩<数学成绩 并且 英语成绩>80
• 案例9: null的查询
• name is null
• "" 与 null区分
• 查询不为空

6. order by子句(排序指令)

语法: select ... from table_name where ... order by column (asc/desc);

• asc 升序(默认)
• desc 降序

6.1. 执行顺序: select ... from table_name where ... order by ...

• FROM：先确定表的来源，并且进行表连接操作（要是有多个表的话）。
• WHERE：对 FROM 子句产生的记录进行过滤，筛选出符合条件的记录。
• GROUP BY：按照指定的列对记录进行分组。
• HAVING：对分组后的结果进行过滤。
• SELECT：从符合条件的记录中选择需要返回的列。
• ORDER BY：对查询结果按照指定的列进行排序。
• LIMIT/OFFSET：限制返回记录的数量或者跳过前面的若干条记录。

6.2. 案例

• 案例1: 同学及数学成绩, 按照升序显示
• 案例2: 同学以及qq号进行排序
• 案例3: 依次查询同学们的各科成绩, 如果有相等的, 按照数学降序, 语文降序, 英语升序的排序进行类推排序显示
• 案例4: 查询同学的总分, 从高到低查询显示
• 案例5: 查询姓孙或姓曹的同学, 结果按照数学成绩从高到低进行显示(降序)

7. limit 子句(显示分页)

语法: limit offset_rows(default: 0) rows; || limit rows offset offset_rows;limit的本质是控制最后的显示, 前面经过筛选条件判断的数据要显示什么

建议: 对未知表进行查询时, 最好加一条limit 10, 避免因为表中数据过大造成数据库卡死.

应用: 网站过长文章分页显示.

8. update

语法: update table_name set column = exp where ...(一般是加条件筛选的!) order by ... limit ...

8.1. 案例

• 案例1: 把孙悟空同学的数学成绩变更为80分
• 案例2: 将曹孟德同学的数学成绩变更为60分, 语文成绩变更为70分
• 案例3: 将总成绩倒数前三的三位同学, 数学成绩 += 30分

• 确定目标表：明确要更新的表是 student。
• 生成待更新记录集合：对 student 表的所有记录按 math + chinese + english 的总分升序排序，取前 3 条记录形成临时待更新集合。
• 执行更新操作：对临时集合中的每条记录，将其 math 列的值增加 30。
• 要点:

• • 排序与限制的作用：ORDER BY 和 LIMIT 这两个子句是在确定最终要更新哪些记录时发挥作用的，而不是在更新操作执行之后才起作用。
  • 更新的原子性：一旦确定了要更新的 3 条记录，更新操作就会按照这 3 条记录原本的顺序依次执行。即便在更新过程中某些记录的总分发生了变化，也不会影响后续其他记录的更新。
  • 与 SELECT 执行顺序的差异：UPDATE 语句不存在 SELECT 语句里那种先投影列（SELECT 子句）再排序的过程。在 UPDATE 中，排序是直接基于原始表的数据进行的。

• 案例4: 将所有同学的语文成绩更新为原来的两倍

• 没有筛选条件的时候, 直接是做整表的更新

9. delete

语法: delete from table_name where ... order by ... limit ...

9.1. 案例

• 案例1: 删除孙悟空的考试成绩
• 案例2: 删除班级里总成绩倒数第一的人
• 案例3: 删除整张表(内容)

• • • delete from test_table(删除整表)

• truncate

• 只能对整表使用, 不能像delete一样针对部分数据操作
• 实际上mysql不对数据操作, 所以比delete更快, 但是在删除数据的时候不经过真正的事务, 因此不能回滚.
• 会重置 auto_increment

10. 去重表数据

distinct作用: 删除表中的重复记录, 重复的数据只会存在一份.

11. 聚合统计

函数:

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

11.1. 案例:

• 案例1: 统计班级共有多少同学 (注: 使用表达式统计随便写一个数字也可以)
• 案例2: 统计班级收集的 qq 号有多少
• 案例3: 统计本次考试的数学成绩分数个数
• 案例4: 统计数学成绩总分
• 案例5: 统计平均总分
• 案例6: 返回英语最高分

• 不同的数据类型无法聚合

• 案例7: 返回 > 70 分以上的数学最低分

12. 分组聚合统计

分组的目的: 方便聚合统计

12.1. 案例:

• 案例1: 如何显示每个部门的平均工资和最高工资
• 案例2: 显示每个部门的每种岗位的平均工资和最低工资
• 案例3: 显示平均工资低于2000的部门和它的平均工资

• 统计各个部门的平均工资
• having和group by配合使用，对group by结果进行过滤