深圳极速网站建设定制,广平网站建设,网页制作代码示例,分享一个网站能用的select高级用法和多表连接 1. select 多子句单表高级实践1.1 select 多子句高级语法1.2 聚合函数1.3 group by 实践1.4 having 筛选1.5 order by 排序1.6 limit 2. 多表连接 1. select 多子句单表高级实践
1.1 select 多子句高级语法
where 和 having 区别是后者是分组后进行… select高级用法和多表连接 1. select 多子句单表高级实践1.1 select 多子句高级语法1.2 聚合函数1.3 group by 实践1.4 having 筛选1.5 order by 排序1.6 limit 2. 多表连接 1. select 多子句单表高级实践
1.1 select 多子句高级语法
where 和 having 区别是后者是分组后进行排名select 语法: select 字段1, 字段2, ... from 表名 [where 条件]其他语句 group by 分组条件: 分组对指定列分组having 条件: 分组后条件判断或过滤order {col_name | expr | position} [ASC | DESC]: 排序ASC升序, DESC降序LIMIT {[offset,] row_count | row_count OFFSET offset}: 限制结果集数量
1.2 聚合函数 什么是聚合函数: 聚合函数是group by 的前提条件 常用聚合函数 序号聚合函数用处1count()返回指定组中数据的数量, 括号内加列名2sum()返回指定组中数据之和, 只能用于数字列3avg()返回指定组中的平均值4max()返回指定数据的最大值5min()返回指定数据的最小值6group_concat()返回指定的数据, 按逗号分割为一行
1.3 group by 实践
查看表结构MariaDB [world] desc city;
-----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
-----------------------------------------------------------统计每个国家的总数 错误select countrycode,sum(population)
from city;正确select countrycode,sum(population)
from city
group by countrycode;统计中国每个省的城市个数SELECT District,COUNT(ID)FROM city WHERE CountryCodechnGROUP BY District;统计中国每个省的人口数SELECT District,SUM(Population)FROM city WHERE CountryCodechnGROUP BY District;1.4 having 筛选
用途: 在group by 之后进行筛选例子 统计中国每个省的城市个数以及城市列表,并把超过十个城市的输出SELECT district,COUNT(*),GROUP_CONCAT(NAME) FROM city WHERE countrycodeCHN GROUP BY district having count(*)10;1.5 order by 排序
查询中国城市人口并按人口数排序输出SELECT NAME,PopulationFROM city WHERE CountryCode CHNORDER BY Population DESC;统计中国每个省的总人口过滤输出总人口超过1000w,从大到小排序输出SELECT District,SUM(Population)FROM city WHERE CountryCode CHNGROUP BY DistrictHAVING SUM(Population)10000000ORDER BY SUM(Population) DESC;1.6 limit
作用与语法: 用于显示指定数据行数, 一般用于order by排序后, 例如选择top3 或者倒数前3例子 显示前两行: limit 2显示3-6行: LIMIT 2,5
2. 多表连接
什么是多表连接: 将多张表用一个相同字段拼接成一张表连接的分类 语法: select 字段1,字段2 from 表1 join 表2 on(注意此时的字段表示方法都应该是: 表.字段)练习请参考: https://www.cnblogs.com/oldboy666/p/15637461.html例子 查询oldboy老师教的学生名SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tnocourse.tno
JOIN sc
ON course.cnosc.cno
JOIN student
ON sc.snostudent.sno
WHERE teacher.tnameoldboy
GROUP BY teacher.tno;查询oldboy所教课程的平均分数SELECT teacher.tname,AVG(sc.score)
FROM teacher
JOIN course
ON teacher.tnocourse.tno
JOIN sc
ON course.cnosc.cno
WHERE teacher.tnameoldboy
GROUP BY teacher.tno;查询oldboy老师教的学生名SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tnocourse.tno
JOIN sc
ON course.cnosc.cno
JOIN student
ON sc.snostudent.sno
WHERE teacher.tnameoldboy
GROUP BY teacher.tno;
