数据库的学习③（分组查询、排序、⼦查询、多表查询）
⼀、分组查询
1.1 概念
在使⽤聚合函数对数据进⾏查询时，⽆论通过where条件筛选后的数据有多少条，仅返回聚合后的单⼀结果，如果想对多条数据进⾏分别的聚合查询，就需要使⽤分组group by。这样，在查询数据后就会根据不同的分组，进⾏数据的聚合操作返回对应数据
1.2 group by语法
select聚合函数from表名where条件group by分组字段
查询不同住址的平均年龄
select studentaddress,avg(studentage)from student group by studentaddress
group by ⼦句规则
1. GROUP BY⼦句可以包含任意数⽬的列。这使得能对分组进⾏嵌套，为数据分组提供更细致的控制。
2. 如果在GROUP BY⼦句中嵌套了分组，数据将在最后规定的分组上进⾏汇总。换句话说，在建⽴分组时，指定的所有列都⼀起计算
（所以不能从个别的列取回数据）
3. GROUP BY⼦句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数） 。如果在SELECT中使⽤表达式，则必须在
GROUP BY ⼦句中指定相同的表达式。不能使⽤别名。
4. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY⼦句中给出。
5. 如果分组列中具有NULL值，则NULL将作为⼀个分组返回。如果列中有多⾏NULL值，它们将分为⼀组。
6. GROUP BY⼦句必须出现在WHERE⼦句之后 （如果有 WHERE ⼦句的话 ），ORDER BY⼦句之前。
1.3 having 语法
查询分组后对分组使⽤的聚合函数进⼀步进⾏数据过滤
HAVING ⾮常类似于 WHERE。事实上，⽬前为⽌所学过的所有类型的WHERE⼦句都可以⽤HAVING来替代。唯⼀的差别是WHERE过滤⾏，⽽HAVING过滤分组。
语法
select聚合函数from表名where条件group by分组字段having过滤条件
查询平均年龄⼩于等于20的地址
select studentaddress,avg(studentage)from student group by studentaddress having avg(studentage)<=20
HAVING和WHERE的差别
WHERE在数据分组前进⾏过滤，HAVING在数据分组后进⾏过滤。这是⼀个重要的区别，WHERE排除的⾏不包括在分组中。这可能会改变计算值，从⽽影响HAVING⼦句中基于这些值过滤掉的分组。
但是 where 和 having 可以同时使⽤
⽰例：年龄⼤于25岁的雇员进⾏分组，每个性别 多于 2⼈的数据
SELECT  sex ,max(age)FROM emp  where age >25group by sex having count(*)>2
⼆、排序
2.1 概念
检索出的数据并不是以纯粹的随机顺序显⽰的。如果不排序，数据⼀般将以它在底层表中出现的顺序显⽰。这可以是数据最初添加到表中的顺序。但是，如果数据后来进⾏过更新或删除，则此顺序将会受到MySQL重⽤回收存储空间的影响。如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
为了明确地排序⽤SELECT语句检索出的数据，可使⽤ORDER BY⼦句。ORDER BY⼦句取⼀个或多个列的名字，据此对输出进⾏排序。
2.2 order by语法
select列from表名where条件order by排序字段asc/desc
Order by ⼦句的位置
1. 在给出ORDER BY⼦句时，应该保证它位于FROM⼦句之后
2. 如果使⽤ LIMIT，它必须位于ORDER BY之后。使⽤⼦句的次序不对将产⽣错误消息。
Order By 执⾏顺序
1. 执⾏ from
2. 执⾏ where
程序员web前端
特斯拉车型报价及图片3. 执⾏ select
4. 执⾏ order by
在对单个字段进⾏排序时，可能出现数据相同的情况(各科考试成绩)，那么我们就可以通过对多个字段进⾏分别排序的⽅式进⾏排序。
按照各科成绩语⽂成绩从⼩到⼤ 数学成绩从⼤到⼩ 英语成绩从⼤道⾏排序
select*from score order by语⽂asc，数学desc，英语desc
按照年龄升序排序，年龄相同按学号降序排序
select*from student order by studentage asc,studentid desc
分组与排序的区别
虽然GROUP BY和ORDER BY经常完成相同的⼯作，但它们是⾮常不同的。见下表
ORDER BY GROUP BY
排序产⽣的输出分组⾏。但输出可能不是分组的顺序
任意列都可以使⽤（甚⾄⾮选择的列也可以使⽤）只可能使⽤选择列或表达式列，⽽且必须使⽤每个选择
mysql语句多表查询不⼀定需要如果与聚集函数⼀起使⽤列（或表达式），则必须使⽤
⼀般在使⽤GROUP BY⼦句时，应该也给出ORDER BY⼦句。这是保证数据正确排序的唯⼀⽅法。千万不要仅依赖GROUP BY排序数据。
三、⼦查询
3.1 概念
当本次查询的条件或数据依赖于另⼀次查询的结果时，就需要使⽤⼦查询
⼦查询的本质是书写多条sql语句同时执⾏（在⼀次查询中书写多个select语句）
3.2 作为条件语句进⾏数据筛选
可以使⽤其他查询返回的⼀条或多条数据作为条件的结果
查询与jack住在同⼀个地址的其他学⽣信息
#查询jack所在地址
select studentaddress from student where studentname='jack';
#湖北襄樊继续查询住在湖北襄樊的学⽣信息
select*from student where studentaddress=‘湖北襄樊’;
sql和mysql哪个好#使⽤⼦查询完成
select*from student where studentaddress=(select studentaddress from student where studentname='jack');
#使⽤=只能在返回唯⼀结果单列时使⽤
# 注意：使⽤⼦查询作为条件结果⼀般只查询⼀个字段
radius软件#如果返回多条数据可以使⽤in 进⾏条件筛选
select*from student where studentaddress in(select studentaddress from student where studentname='jack');
3.3 作为临时表数据提供
临时表概念
实际上数据库存储数据的形式，根据引擎⽽定，但是这些数据我们是不能直接查看的，通过sql语句管理软件会根据语句返回对应的临时表的形式进⾏查看
select t.*from(select*from student where studentage>=21) t
#在使⽤临时表时，如果需要展⽰临时表的字段，那么必须设置别名
四、多表连接查询
4.1 概念
在查询时，在满⾜数据库设计三范式的前提下，数据会存储在不同的表中，各个表通过字段产⽣关联，如果想在⼀次查询中将两个或多个表的信息⼀同显⽰，这个时候就需要使⽤连接查询，将多个表的数据连接在⼀起使⽤，进⾏结果的显⽰
4.2 内连接
将多个表中的数据根据连接条件连接为新的表，在新的表上继续进⾏查询操作
查询所有学⽣姓名以及班级
select s.studentname,c.classname from student s inner join class c on s.classid=c.classid
#内连接通过连接条件将符合条件的数据进⾏连接
#如果存在不符合连接条件的数据则不显⽰
4.3 全连接
在使⽤连接时必须on 书写连接条件否则会将所有数据的笛卡尔积返回，但是有时我们还需要这样的数据，不使⽤连接的语法 ，⽽是使⽤where进⾏条件筛选
查询与jack住在同⼀地⽅的学⽣信息
select s2.*
from student s1,student s2
where s1.studentname='jack'
and s1.studentaddress=s2.studentaddress
4.4 外连接
在进⾏连接查询时，内连接只会返回满⾜连接条件的数据，如果连接的两表中，没有与其对应满⾜条件的数据，那么这条数据将不会显⽰，外连接就是⽤于解决这⼀问题的，使⽤外连接即使没有对应满⾜条件的数据，也会将当前数据显⽰
左外连接
以左表为准，即使右表中没有与之对应的数据，那么也会显⽰左表的全部数据，对应右表使⽤null填充
select s.studentname,c.score
from student s
left join computer c
on s.studentid=c.studentid
右外连接
以右表为准，即使左表中没有与之对应的数据，那么也会显⽰右表的全部数据，对应左表使⽤null填充
select s.studentname,c.score
from student s
right join computer c
on s.studentid=c.studentid
五、组合查询
与连接查询不同是，连接查询将量表数据左右连接通过连接条件进⾏拼接，组合查询是将多条拥有相同类型列相同列数的多条sql语句上下拼接
UNION规则
1. UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间⽤关键字 UNION 分隔（因此，如果组合4条SELECT 语句，将要使
⽤3个 UNION 关键字） 。
2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的⽇期类型）
如果遵守了这些基本规则或限制，则可以将并⽤于任何数据检索任务
UNION ALL包含或取消重复的⾏
使⽤UNION ALL，MySQL不取消重复的⾏。因此这⾥的例⼦返回9⾏，其中有⼀⾏出现两次
1. UNION⼏乎总是完成与多个WHERE条件相同的⼯作
2. UNION ALL为UNION的⼀种形式，它完成WHERE⼦句完成不了的⼯作
3. 如果确实需要每个条件的匹配⾏全部出现（包括重复⾏），则必须使⽤UNION ALL⽽不是 WHERE
六、视图
视图与每次sql语句查询的结果虚拟表类似，只不过与普通的虚拟表不同，视图可以通过创建的形式保存虚拟表，并使⽤视图把其当做⼀张已存在的表进⾏使⽤（视图表包含的是已存在表的动态数据，所以对存在表数据操作，视图的数据也会修改）
通常的做法是将经常使⽤的多表的关联创建视图进⾏使⽤
视图的规则和限制
与表⼀样，视图必须唯⼀命名（不能给视图取与别的视图或表相同的名字） 。
对于可以创建的视图数⽬没有限制。
为了创建视图，必须具有⾜够的访问权限。这些限制通常由数据库管理⼈员授予。
视图可以嵌套，即可以利⽤从其他视图中检索数据的查询来构造⼀个视图。
ORDER BY可以⽤在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表⼀起使⽤。例如，编写⼀条联结表和视图的SELECT语句。
七、SQL执⾏顺序
1. from ：数据源，查询数据来源
2. join ：连接数据源，当⼀个表数据不能满⾜需求进⾏连接
3. on ：连接条件 在连接数据源时过滤不必要的数据
4. where ：条件筛选，进⼀步对数据进⾏筛选
5. group by（开始使⽤select中的别名，后⾯的语句中都可以使⽤）
6. avg,sum… （聚合函数）
7. having ：对分组的数据进⼀步进⾏筛选
8. select ：选择结果集中最终展⽰的列
9. distinct ：对相同数据去重
10. order by：对指定列进⾏排序
11. limit ：截取指定长度数据
⼋、SQL优化
硬件优化
使⽤更好的设备，从服务器，存储，内存，运存，⽹络多⽅⾯更新硬件软件优化
主要是服务器管理软件以及数据库管理软件进⾏优化
数据库管理软件⼀般通过修改配置⽂件
thread_concurrency：# 并发线程数量个数
sort_buffer_size：# 排序缓存
read_buffer_size：# 顺序读取缓存
read_rnd_buffer_size：# 随机读取缓存
key_buffer_size：# 索引缓存
thread_cache_size：# (1G—>8, 2G—>16, 3G—>32, >3G—>64) sql优化
数据库结构优化
创建数据库时使⽤数据库三范式进⾏表结构的创建
创建表时对于表的字段类型进⾏优化
在字段类型设置时不要使⽤null 可能使索引失效
web开发的基本技术sql语句优化

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。