榆林网站建设vs0912,营销的方法和技巧,wordpress is_user_logged_in(),企业网络专线文章目录 #x1f308; 一、insert 添加数据⭐ 1. 单行数据 全列插入⭐ 2. 多行数据 指定列插入⭐ 3. 插入否则更新⭐4. 插入否则替换 #x1f308; 二、select 查询数据⭐ 1. select 列#x1f319; 1.1 全列查询#x1f319; 1.2 指定列查询#x1f319; 1.3 查询字段… 文章目录 一、insert 添加数据⭐ 1. 单行数据 全列插入⭐ 2. 多行数据 指定列插入⭐ 3. 插入否则更新⭐4. 插入否则替换 二、select 查询数据⭐ 1. select 列 1.1 全列查询 1.2 指定列查询 1.3 查询字段为表达式 1.4 为查询结果指定别名 1.5 结果去重 ⭐ 2. where 条件查询 2.1 运算符介绍 2.2 where 使用案例 ⭐ 3. order by 结果排序 3.1 order by 语法格式 3.2 order by 使用案例 ⭐ 4. limit 筛选分页结果 4.1 limit 语法格式 4.2 limit 使用案例 三、update 修改数据⭐ 1. update 语法格式⭐ 2. update 使用案例 四、delete 删除数据⭐ 1. delete 语法格式⭐ 2. 删除指定记录⭐ 3. 删除全表数据⭐ 4. truncate 截断表 4.1 truncate 语法格式 4.2 truncate 使用案例 五、插入查询结果⭐ 1. 语法格式⭐ 2. 使用案例 六、聚合函数⭐ 1. 常见聚合函数⭐ 2. 聚合函数案例 七、group by 分组查询⭐ 1. 分组概念⭐ 2. group by 语法格式⭐ 3. group by 使用案例 3.1 准备工作 3.2 使用案例 ⭐ 4. having 条件 4.1 having 使用案例 4.2 where 和 having 的区别 一、insert 添加数据
INSERT [INTO] 表名 [(列名1 [, 列名2, ..., 列名n])] VALUES (值1, 值2, ..., 值n) [, (值1, 值2, ..., 值n)];参数说明
虽然 MySQL 不区分大小写但此处还是要用大写清楚的表示哪些是关键字。方括号 [ ] 括起来的是可选项。
准备工作
为了方便之后的操作现在创建一张名为 students 的学生表。 表中包含自增长的主键 id、非空且唯一键的学号 sn、非空的姓名 name 和唯一键的 qq 号这四个字段。 ⭐ 1. 单行数据 全列插入
INSERT [INTO] 表名 VALUES (给第1列的值) [, (给第2列的值, ..., 给第n列的值)];每次向表中插入一行数据在插入数据时不指定字段名表示按照表中默认的字段顺序进行全列插入插入的数据的类型要和表中对应字段一致。 插入单行数据时可以对指定列进行插入也可以进行全列插入这里就只演示全列插入。 ⭐ 2. 多行数据 指定列插入
INSERT [INTO] 表名 (列名1 [, 列名2, ..., 列名n])] VALUES (值1, 值2, ..., 值n) [, (值1, 值2, ..., 值n)];可以一次性向表中插入多条数据插入的多条数据之间使用逗号隔开。 插入多行数据时可以对指定列进行插入也可以进行全列插入这里就只演示对指定的 sn、name、qq 这三列进行插入。 注在对指定列插入数据时只有允许为空的字段和设置了自增长属性的字段能不指定值插入不允许为空的字段必须指定值插入。 ⭐ 3. 插入否则更新
在往向表中插入数据时如果主键或唯一键的值与已有数据发生了冲突会导致本次操作被 MySQL 拦截。 此时就需要使用插入否则更新的 sql 语句出马了。
1. 插入否则更新的语法格式
INSERT ... ON DUPLICATE KEY UPDATE 列名1 值1 [, 列名2 值2, ..., 列明n 值n];
// 语句中的 字段值表示当插入数据时如果出现冲突则需要更新的字段值。如果表中 未 发生数据冲突则插入数据到表中。如果表中 有 发生数据冲突则更新表中的数据。
2. 插入否则更新的使用示例
向表中插入数据时如果发生了主键冲突则直接更新表中学号 sn 字段和姓名 name 字段的值QQ 号就不更新了。 3. 判读数据的插入情况
执行插入否则更新的语句之后能通过反映回来的受影响的数据行数来判断数据的插入情况。 0 row affected表中发生数据冲突但冲突数据的值和更新的值相等。1 row affected表中没有发生数据冲突数据直接被插入。2 rows affected表中发生数据冲突并且数据已经被更新。
⭐4. 插入否则替换
REPLACE INTO 表名 [(列名1 [, 列名2, ..., 列名n])] VALUES (值1, 值2, ..., 值n) [, (值1, 值2, ..., 值n)];
// 只是将 插入数据 语法中的 INSERT 替换成了 REPLACE 而已当 未 和表中现有的主键或唯一键字段的数据发生冲突时会直接将数据插入到表中。当 有 和表中现有的主键或唯一键字段的数据发生冲突时会先将表中发生冲突的数据删除然后再插入新的数据。
1. 插入否则替换的使用示例
不和现有数据发生冲突执行的仅仅是插入功能。 和现有数据发生冲突执行的是替换功能。 2. 判断数据替换的情况
执行插入否则替换的语句之后能通过反映回来的受影响的数据行数来判断数据的插入情况。 1 row affected表中没有发生数据冲突数据直接被插入。2 rows affected表中发生了数据冲突表中的冲突数据被删除后插入了新的数据。 二、select 查询数据
SELECT [DISTINCT] {* 或 {列名1 [, 列名2, ..., 列名n] ...}} FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];准备工作
为了方便之后的操作演示先创建一张名为 exam_result 的学生成绩表。 表中包含自增长的主键 id非空的学生姓名 name语文成绩 chinese、数学成绩 math 和英语成绩 engilsh 这五个字段。 创建完表之后再插入几条测试数据方便之后进行查询操作。 ⭐ 1. select 列 1.1 全列查询
全列查询表示的是要将信息全部读取出来。
SELECT [DISTINCT] * FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];通常情况下不建议使用 * 进行全列查询 被查询到的数据需要通过网络从 MySQL 服务器传输到本主机查询的列数越多意味着需要传输的数据量越大。 使用全列查询可能还会影响到索引的使用。 1.2 指定列查询
在查询数据时也可以只对指定的某些列进行查询。
SELECT [DISTINCT] 字段1 [, 字段2, ..., 字段n] FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];指定查询成绩表 exam_result 中的姓名 name 字段和数学 math 字段。表示当前只想查看所有学生的数学成绩。 指定查询 id、name、english 这三列的内容。 1.3 查询字段为表达式
select 是个很特殊的关键字它可以是 select 自带的各种子句、指定表的字段名、普通表达式等。 select 不仅能够用来查询数据还可以用来计算某些表达式的值或执行某些函数。 列表中的表达式也可以包含多个表中已有的字段可以通过表达式计算这些字段获得其他特别的数据。 求每名同学的语数英三科成绩的总分。 1.4 为查询结果指定别名
可以给表的某一列取个别名用来更好的分辨某一列的功能。对列做重命名属于显示的范畴是最后一步已经拿完数据了只是做个改名操作而已只能在 select 语句这里进行重命名。 执行顺序在 select 之前的子句无法对列取别名。
SELECT 指定列名 [AS] 指定列的别名 [...] FROM 表名;将成绩表中每名同学的语数英成绩加起来并对该表达式起个名为总分的列别名。 虽然 as 可带可不带但是为了更好的阅读体验建议还是带上。 还可以为表中自带的字段取别名。 1.5 结果去重
SELECT DISTINCT 字段列表 from 表名;在进行查询时可能会有重复的数据出现。 可以在 select 关键字的后面加上 distinct 关键字当要筛选的那一列出现重复的数据时只保留一份数据。 ⭐ 2. where 条件查询
在查询时也不是每次都要查询表中所有的行的数据还需要根据一些筛选条件查看指定行的数据。where 筛选影响的是显示出来的行数。在查询数据时如果使用了 where 子句则会先根据 where 子句筛选出符合条件的行数据然后将符合条件的行数据作为数据源依次执行 select 语句从而找出符合条件的列数据。 2.1 运算符介绍
where 子句可以指定 1/ n 个筛选条件where 使用特定的比较运算符和逻辑运算符类决定如何进行筛选。
1. 比较运算符
比较运算符说明大于大于等于小于小于等于等于NULL 不安全如NULL NULL 的结果是 NULL等于NULL 是安全的如NULL NULL 的结果是 TRUE!不等于BETWEEN 值1 AND 值2在 [值1, 值2] 这个范围之内取值 (含最小值和最大值)IN (…)从 IN 之后的多个值之中进行多选一IS NULL是 NULLIS NOT NULL不是 NULLLIKE 占位符模糊匹配占位符如果是下划线 _ 表示任意一个字符如果是百分号 % 表示任意 0 ~ n 个字符
2. 逻辑运算符
MySQL 支持通过逻辑运算符将多条筛选语句组合起来。
逻辑运算符说明AND 或 并且 (多个条件需要同时成立)OR 或 ||或者 (多个条件任意一个成立)NOT 或 !非 (条件为真结果为假条件为假结果为真) 2.2 where 使用案例
当前准备了如下的 where 子句的使用案例
查询英语不及格的同学及其英语成绩。查询语文成绩在 [80, 90] 分之间的同学及其语文成绩。查询数学成绩是 58 或者是 59 或者是 98 或者是 99 分的同学及其数学成绩。查询姓赵的同学以及赵某同学。查询语文成绩优于英语成绩的同学。查询总分在 200 分以下的所有同学的信息。查询语文成绩 80 并且不姓李的同学。查询重某同学否则要求总成绩 200 并且 语文成绩 数学成绩 并且 英语成绩 80 分。NULL 的查询。
1. 查询英语不及格的同学及其英语成绩
在 where 子句中指定筛选条件为 english 60在 select 的字段列表中指明要查询的字段为 name 和 english。 2. 查询语文成绩在 [80, 90] 分之间的同学及其语文成绩
在 where 子句中指定筛选条件为 chinese 80 chinese 90在 select 的字段列表中指明要查询的列为 name 和 chinese。 也可以在 where 子句中使用 between 80 and 90 查询 [80, 90] 分的同学的信息。 3. 查询数学成绩是 58 或者是 59 或者是 98 或者是 99 分的同学及其数学成绩
在 where 子句中指定筛选条件为数学成绩为 58 || 59 || 98 || 99在 select 的字段列表指定要查询的列为 name 和 math。 除了用或的方式筛选之外还可以使用 in(58, 59, 98, 99) 从这 4 个值中任选一个。 4. 查询姓赵的同学以及赵某同学
查询姓赵的同学即不管姓名有几个字只要姓赵即可。在 where 子句中可用 name like ‘赵%’% 能匹配任意多个字符。 查询赵某同学即查询姓赵且名字个数为 2 的同学。在 where 子句中可使用 name like ‘赵_’ 来匹配_只能匹配一个字符。 由于表中只有一个赵六姓赵所以查询效果会看着和使用 % 没什么区别。 5. 查询语文成绩优于英语成绩的同学
在 where 子句中指定筛选条件为 chinese english在 select 的字段列表中指定要查询的列为 name、chinese 和 english。 6. 查询总分在 200 分以下的所有同学的信息
在 where 子句中的筛选条件为 chinese math english 200在 select 的字段列表中指定要查询的列为 name 和三科总分。 7. 查询语文成绩 80 并且不姓李的同学
在 where 子句中指定筛选调教为 chinese 80 并且 name 非 like ‘李%’。 8. 查询重某同学否则要求总成绩 200 并且 语文成绩 数学成绩 并且 英语成绩 80 分
被查询的人要么是重某要么是总成绩 200 并且 chinese math 并且 english 80 的同学。 重八满足条件 1王五满足条件 2都符合 或者 的定义因此是这两个人被查询出来了。 9. NULL 的查询
为了方便演示此处使用在添加数据那里使用的 students 学生表进行查询。 查询 qq 号已知的同学即查询 qq 号 is not null 不为空的同学。 查询 qq 号未知的同学即查询 qq 号 is null 为空的同学。 ⭐ 3. order by 结果排序
由于查询的结果是个表结构可能会有多行信息通常将一行数据称之为记录此时就需要对这些筛选出来的记录进行排序。 3.1 order by 语法格式
SELECT ... FROM 表名 [WHERE ...] ORDER BY 字段1 [ASC 或 DESC], [...];ASC 表示升序DESC 表示降序默认为升序 ASC。如果查询语句中没有 order by 子句则返回的顺序是未定义的。可以根据多个字段进行排序当根据字段 1 的值排序完之后出现了重复值则再根据字段 2 的值对重复的部分排序以此类推。 3.2 order by 使用案例
当前准备了如下 order by 子句的使用案例。
查询所有的同学及其数学成绩查询结果按 数学成绩 升序显示。查询所有的同学及其 qq 号查询结果按 qq 号排序显示。查询同学的各门成绩查询结果依次按 数学降序、英语升序、语文升序 的方式进行显示。查询所有同学的成绩总分查询结果按 总分 降序显示。查询姓 赵 的的同学或者姓 王 的同学的数学成绩查询结果按 数学成绩 降序显示。
1. 查询所有的同学及其数学成绩查询结果按 数学成绩 升序显示
在成绩表中查询在 select 的字段列表中指定要查询的列为 name 和 math在 order by 子句中指定按照 math 进行 asc 排序。 2. 查询所有的同学及其 qq 号查询结果按 qq 号排序显示
在学生表中查询排升序在 select 的字段列表中指定要查询的列为 name 和 qq在 order by子句中按照 qq 号进行 asc 排序。 注由于 qq 的数据类型是 varchar因此采用 ASCII 码进行比较。 在学生表中查询排降序在 select 的字段列表中指定要查询的列为 name 和 qq在 order by子句中按照 qq 号进行 desc 排序。 3. 查询同学的各门成绩查询结果依次按 数学降序、英语升序、语文升序 的方式进行显示
在成绩表中查询在 select 的字段列表中指明要查询的列为 name、math、english 和 chinese在 order by 子句中指明依次按照 math desc, english asc, chinese asc 进行排序。 首先按照数学成绩进行降序排序的在相同的数学成绩之间按照英语进行升序排序。 4. 查询所有同学的成绩总分查询结果按 总分 降序显示
在成绩表中查询在 select 的字段列表中指中指定要查询的列为 name 和 总分 (表达式查询)在 order by 子句中指明按照 chinese math english 进行 desc 排序。 5. 查询姓 赵 的的同学或者姓 王 的同学的数学成绩查询结果按 数学成绩 降序显示
在成绩表中查询在 where 子句中指定筛选条件为姓 赵 / 王再在 order by 子句中指定为按 math 进行 desc 排序。 ⭐ 4. limit 筛选分页结果
对获取出来的结果信息进行分页显示。 4.1 limit 语法格式 从第 0 行数据开始向后筛选出 n 行数据。 SELECT ... FROM 表名 [WHERE ..] [ORDER BY ...] LIMIT n;从第 s 行数据开始向后筛选出 n 行数据。 SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT s, n从第 s 行数据开始向后筛选出 n 行数据 (比第二种用法更明确建议使用)。 SELECT ... FROM 表名 [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;注意limit 子句在筛选记录时记录的下标从 0 开始即第一行数据的下标为 0。建议对未知表进行查询时最好加一条 LIMIT 1避免因为表中数据过大查询全表数据导致数据库卡死。 4.2 limit 使用案例
当前准备了如下 limit 子句的使用案例。
按 id 对成绩表进行分页每页 3 行数据分别显示第 1、2、3 页。查询班级总分第一名的学生
1. 按 id 对成绩表进行分页每页 3 行数据分别显示第 1、2、3 页
成绩表 exam_result 当前的表中数据如下 查询第一页从第 0 行数据开始向后筛选出 3 行数据 (即筛出下标为 0、1、2 这三行数据)。 不要被主键 id 的数字影响。 查询第二页从下标为 3 的记录开始往后筛选出 3 条记录。 查询第三页从下标为 6 的记录开始向后筛选出 3 条记录。 如果从表中筛选出的记录不足 n 个则筛选出几个就显示几个。 2. 查询班级总分第一名的学生
在 select 中显示的列为 name 和 总分在 order by 中按照三科总分降序 desc 排序再使用 limit 中筛选出第一行数据。 由于 order by 的执行顺序在 select 之后因此可以使用 select 定义的对三科总分的别名。 三、update 修改数据
⭐ 1. update 语法格式
UPDATE 表名 SET 列名1 值1 [, 列名2 值2, ..., 列名n 值n] [WHERE ...] [ORDER BY ...] [LIMIT ...]where 和 limit 是用来筛选出具体要修改的是哪几行如果不加筛选条件则默认是修改所有行的指定列。慎用能够对全表进行更新的语句。
⭐ 2. update 使用案例
当前准备了如下 update 的使用案例。
将李四同学的数学成绩变更为 80 分。将赵六同学的数学成绩变更为 60 分语文成绩变更为 70 分。将总成绩倒数前三的同学的数学成绩加上 30 分。将所有同学的语文成绩变更为原来的 2 倍。
1. 将李四同学的数学成绩变更为 80 分
在 where 子句中使用 name ‘李四’ 找出该同学在在 update 中使用 set math 80 将数学成绩变更为 80 分。 如果不设置像 where 这样的筛选条件所有人的数学成绩都会被弄成 80。 2. 将赵六同学的数学成绩变更为 60 分语文成绩变更为 70 分
在 where 子句中使用 name ‘赵六’在 update 总使用 set math 60, chinese 70。 3. 将总成绩倒数前三的同学的数学成绩加上 30 分
找出倒数前三在 order by 子句中将总成绩按照升序排序再使用 limit 3 显示排序后的前 3 行数据. 将这 3 个家伙的数学成绩统统加上 30 分即在 update 中使用 set math math 30。 这 3 个人数学各加了 30 分之后可能就不再是倒数前三了因此再执行一次查询倒数前三的操作后显示出来的数据可能会发生变化。 4. 将所有同学的语文成绩变更为原来的 2 倍
不加任何筛选条件直接在 update 中使用 set chinese chinese * 2 即可将所有人的语文成绩变成原来的 2 倍。 慎用能够对全表进行更新的语句。 四、delete 删除数据
⭐ 1. delete 语法格式
DELETE FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];在删除数据前需要先找到要删除的的记录delete 语句中的 where、order by 和 limit 子句就是用筛选要删除的数据的。 delete 删除的数据以行为单位。 如果不添加筛选条件的话默认就是删除整张表的数据。 慎用能够对全表进行删除的语句。
⭐ 2. 删除指定记录
删除李四同学的考试成绩
即将李四同学从 exam_result 成绩表中删除在 where 子句中使用 name ‘李四’ 筛选出李四所在的这一行数据。 ⭐ 3. 删除全表数据
先创建一张名为 for_delete 的测试删除数据表表中分别包含 id 和 name 两个字段。 往表中插入一些临时数据用来后续对其进行删除。 在 delete 语句中只指定要删除数据的表名而不指定筛选条件即可删除整张表的数据。 慎用能够对全表进行删除的语句。 如果之后再向该表中插入数据但不指明自增长字段的值会发现自增长 id 值是在之前的基础上继续增长的。 在查看 for_delete 的建表语句时会发现有一个 AUTO_INCREMENTn 的字段该字段表示下一次插入数据时自增长字段的值应该为 n。 使用 delete 删除整表数据时不会重置 AUTO_INCREMENTn 字段因此删除整表数据后再插入自增长字段的值会在原基础上递增。 ⭐ 4. truncate 截断表 4.1 truncate 语法格式
TRUNCATE [TABLE] 表名;属于清空表数据的一种在效果上和 delete 的清空表数据一致但细节和原理上有所差别。truncate 只能对整张表进行操作不能像 delete 一样针对部分数据进行操作。truncate 实际上是不对数据进行操作的在清空数据方面会比 delete 更快。truncate 在删除数据的时候不会经过真正的事务 (即不会将自己的操作记录在日志中)因此无法对数据进行回滚。truncate 会重置 AUTO_INCREMENTn 这一项。 4.2 truncate 使用案例
创建一张名为 for_truncate 的测试截断表表中包含一个自增长的主键 id 和 name 这两个字段。 往表中插入一些临时数据用来后续对其进行截断。 在 truncate 语句指定要截断的表名为 for_truncate即可清空该表的数据。 因为 truncate 不会对数据进行操作因此执行 truncate 语句后看到影响行数为 0。 由于 truncate 会重置 AUTO_INCREMENTn再往表中重新插入数可以看到自增长字段的值从 1 开始继续递增了。 五、插入查询结果
⭐ 1. 语法格式
MySQL 也支持将对表的查询结果插入到另一张表中。
INSERT [INTO] 表名 [(列1 [, 列2, ..., 列n] ...)] SELECT ... [WHERE ...] [ORDER BY ...] [LIMIT ...];该语句的作用是将从其他表中筛选出来的数据插入到指定的表中。其中的列1 ~ 列n 表示将筛选出的记录的各个列插入到表中的指定列。
⭐ 2. 使用案例
案例删除表中重复的记录让重复的数据只能有一份。
1. 准备工作
创建一张名为 duplicate_table 的测试用表表中包含 id 和 name 两个字段。 往表中插入一些包含重复数据的测试数据。 2. 删除表中重复的数据
创建一张临时的空表 no_duplicate_table其表结构和 duplicate_table 一致。 由于两张表的表结构相同因此在创建临时表时可以借助 like。 将 duplicate_table 的数据使用 DISTINCT 去重后插入到 no_duplicate_table 表中。 由于两张表的表结构一致并且 select 进行的是全列查询因此在插入时不用在表名后指定字段列表。 分别对两张表进行重命名操作实现原子的去重操作。 将 duplicate_table 测试表重命名为其他名字 (相当于对去重前的数据进行备份如果不需要可以直接删除)将 no_duplicate_table 临时表重命名为 duplicate_table 测试表的名字此时便完成了对原始表数据的去重操作。 六、聚合函数
select 函数名(参数) from 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];聚合函数能够对一组值进行计算并返回单一的值。这些函数以查询出的记录为单位做聚合统计。
⭐ 1. 常见聚合函数
聚合函数可以在 select 语句中使用select 在每处理一条记录时都会将对应的参数传递给这些聚合函数。
函数说明COUNT ( [DISTINCT] expr )返回查询到的数据的数量SUM ( [DISTINCT] expr )返回查询到的数字的总和expr 参数如果不是数字则没有意义AVG ( [DISTINCT] expr )返回查询到的数据的平均值expr 参数如果不是数字则没有意义MAX ( [DISTINCT] expr )返回查询到的数据的最大值expr 参数如果不是数字则没有意义MIN ( [DISTINCT] expr )返回查询到的数据的最小值expr 参数如果不是数字则没有意义
⭐ 2. 聚合函数案例
当前准备了如下聚合函数使用案例。
统计班级共有多少名同学。统计班级收集的 qq 号有多少。统计本次考试的数据成绩分数个数。统计所有同学的数学成绩总分。统计所有同学的三科总分的平均值。返回英语成绩的最高分。返回 80 分的的数学的最低分。
1. 统计班级共有多少名同学
使用 * 统计在 select 语句中使用 count 函数时可将 * 作为参数传递给它用以统计表中有多少行数据。 对学生表进行操作。 使用该表达式统计在 select 语句中使用 count 函数时可将表达式作为参数传递给它用以统计表中有多少行数据。 使用表达式做统计这种写法相当于在查询表中数据时临时新增了一列名为对应表达式的列用 count 函数统计该列中有多少行数据。 在这里只是新增了一列数字 1然后 count 统计的是这一列的 1 的个数。 2. 统计班级收集的 qq 号有多少
在 select 语句中使用 count 函数统计 qq 那一列中数据的个数。 由于 count 函数的参数是一个确定的字段名因此 count 会自动忽略 null 值。 3. 统计本次考试的数据成绩分数个数
统计数学分数的个数在 select 语句中使用 count 函数统计 math 那一列中数据的个数。 对成绩表进行操作。 统计数学分数的种类即要执行去重操作在传递给 count 函数的参数中加上一个 distinct 即可。 4. 统计所有同学的数学成绩总分
统计数学成绩总分在 select 语句中使用 sum 函数统计 math 这一列数据的总和。 统计不及格的数学成绩总分在 where 子句种指定筛选条件为 math 60在 select 语句种使用 sum 函数统计 math 这一列被 where 子句筛选出来的的数据之后。 5. 统计所有同学的三科总分的平均值
在 select 语句中使用 avg 函数计算所有同学的 chinese math english 的平均值。 6. 返回英语成绩的最高分
在 select 语句中使用 max 函数 english 这一列中所有数据的最大值。 7. 返回 80 分的的数学的最低分
在 where 子句中指定筛选条件为 math 80然后在 select 语句中使用 min 函数取 math 这一列被筛选出来的数据的最小值。 七、group by 分组查询
⭐ 1. 分组概念 分组是指对表中的数据进行分组分组的目的是为了方便聚合统计。 例根据性别将成绩分成两组再对这两组数据分别进行聚合统计。 指定列名实际分组是用所指定发的列的不同的行数据来进行分组的。 分组就是将一张表按照指定条件分成了多个组进行各自组内的统计。 分组也被称为 “分表”就是将一张表按照指定的条件再逻辑上拆分成了多个子表然后再分队对各自的子表进行聚合统计。 在 MySQL 中一切皆表只要能够处理好对一张表的增删查改则所有的 sql 场景都能用统一的方式进行。
⭐ 2. group by 语法格式
select 字段列表 from 表名 [where 分组前过滤条件] group by 分组字段名 [having 分组后过滤条件];⭐ 3. group by 使用案例 3.1 准备工作
1. 创建数据库
创建一个名为 scott 的数据库并将其设置成当前操作数据库。
DROP database IF EXISTS scott;
CREATE database IF NOT EXISTS scott;USE scott;2. 创建雇员信息表
创建一张雇员信息表表中包含三张表员工表 (emp)、部门表 (dept)、工资等级表 (salgrade)。
部门表 dept 包含的字段有部门编号 (deptno)、部门名称 (dname)、部门所在地 (loc)。
DROP TABLE IF EXISTS dept;
CREATE TABLE dept
(deptno int(2) unsigned zerofill NOT NULL COMMENT 部门编号,dname varchar(14) DEFAULT NULL COMMENT 部门名称,loc varchar(13) DEFAULT NULL COMMENT 部门所在地点
);员工表 emp 包含的字段有员工编号 (empno)、员工姓名 (ename)、员工职位 (job)、员工领导编号 (mgr)、雇佣时间 (hiredate)、月薪 (sal)、奖金 (comm)、部门编号 (deptno)。
DROP TABLE IF EXISTS emp;
CREATE TABLE emp
(empno int(6) unsigned zerofill NOT NULL COMMENT 雇员编号,ename varchar(10) DEFAULT NULL COMMENT 雇员姓名,job varchar(9) DEFAULT NULL COMMENT 雇员职位,mgr int(4) unsigned zerofill DEFAULT NULL COMMENT 雇员领导编号,hiredate datetime DEFAULT NULL COMMENT 雇佣时间,sal decimal(7, 2) DEFAULT NULL COMMENT 工资月薪,comm decimal(7, 2) DEFAULT NULL COMMENT 奖金,deptno int(2) unsigned zerofill DEFAULT NULL COMMENT 部门编号
);工资等级表 salgrade 包含的字段有 等级 (grade)、该等级所对应的最低工资 (losal)、该等级所对应的最高工资 (hisal)。
DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade
(grade int(11) DEFAULT NULL COMMENT 等级,losal int(11) DEFAULT NULL COMMENT 此等级最低工资,hisal int(11) DEFAULT NULL COMMENT 此等级最高工资
);3. 插入数据
往部门表 dept 中插入数据
insert into dept (deptno, dname, loc) values (10, ACCOUNTING, NEW YORK);
insert into dept (deptno, dname, loc) values (20, RESEARCH, DALLAS);
insert into dept (deptno, dname, loc) values (30, SALES, CHICAGO);
insert into dept (deptno, dname, loc) values (40, OPERATIONS, BOSTON);往员工表 emp 中插入数据
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, SMITH, CLERK, 7902, 1980-12-17, 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, WARD, SALESMAN, 7698, 1981-02-22, 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, JONES, MANAGER, 7839, 1981-04-02, 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, MARTIN, SALESMAN, 7698, 1981-09-28, 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, CLARK, MANAGER, 7839, 1981-06-09, 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, SCOTT, ANALYST, 7566, 1987-04-19, 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, KING, PRESIDENT, null, 1981-11-17, 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, TURNER, SALESMAN, 7698, 1981-09-08, 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, ADAMS, CLERK, 7788, 1987-05-23, 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, JAMES, CLERK, 7698, 1981-12-03, 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, FORD, ANALYST, 7566, 1981-12-03, 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, MILLER, CLERK, 7782, 1982-01-23, 1300, null, 10);往 工资等级表 salgrade 中插入数据
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);3.2 使用案例
当前为 group by 准备了如下两种案例
显示每个部门的平均工资和最高工资。显示每个部门的每种岗位的平均工资和最低工资。显示不包含员工名为 SMITH 在内的每个部门的每种岗位最高和最低工资。
1. 显示每个部门的平均工资和最高工资
在员工表 emp 中查询在 group by 子句中指定按照部门编号 deptno 进行分组。在 select 语句中使用 avg 函数和 max 函数然后查询筛选出来的所有分组的平均工资和最高工资。 2. 显示每个部门的每种岗位的平均工资和最低工资
在员工表 emp 中查询在 group by 子句中指定按照部门编号 deptno 先分成多个大组再按照岗位 job 将这些大组划分成多个小组。在 select 语句中使用 avg 函数和 min 函数然后查询筛选出来的所有小组的平均工资和最低工资。 3. 显示不包含员工名为 SMITH 在内的每个部门的每种岗位最高和最低工资
在 where 子句中指定员工姓名 ename ! ‘SMITH’。在 group by 子句中指定按照部门编号 deptno 分大组再按照岗位 job 分小组。在 sleect 语句中使用 max 和 min 求每个小组的薪水 sal 最大最小值。 ⭐ 4. having 条件
SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];在 having 子句中可以指定 1 ~ n 个筛选条件。 4.1 having 使用案例
显示平均工资低于2000的部门和它的平均工资
统计每个部门的平均工资在 group by 子句中指定按照部门编号 deptno 进行分组。在 select 语句中使用 avg 函数查询每个分组的平均工资。通过 having 子句筛选出平均工资低于 2000 的部门在 having 子句中指定筛选条件为 avg(sal) 2000。 4.2 where 和 having 的区别 where 子句放在表名之后而 having 子句必须搭配 group by 子句使用放在group by 子句之后。 where 子句是具体的任意列进行条件筛选而 having 子句是对分组聚合后的数据进行条件筛选。 where 子句中不能使用聚合函数和别名而 having 子句中可以使用聚合函数和别名。 where 的执行顺序在分组之前而 having 的执行顺序在分组之后。
