汇点远程app下载安装,上海关键词优化方法,如何做网站权重,百度seo教程网一、mysql查询的五种子句 where子句#xff08;条件查询#xff09;#xff1a;按照“条件表达式”指定的条件进行查询。 group by子句#xff08;分组#xff09;#xff1a;按照“属性名”指定的字段进行分组。group by子句通常和count()、sum()等聚合函数一起使用。 h…一、mysql查询的五种子句 where子句条件查询按照“条件表达式”指定的条件进行查询。 group by子句分组按照“属性名”指定的字段进行分组。group by子句通常和count()、sum()等聚合函数一起使用。 having子句筛选有group by才能having子句只有满足“条件表达式”中指定的条件的才能够输出。 order by子句排序按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出默认是按照“asc”来排序即升序。 limit限制结果集。 1、where——基础查询 where常用运算符 运算符说明 比较运算符小于小于或等于等于! 或 不等于大于等于大于in在某集合内between在某范围内 逻辑运算符not 或 !逻辑非or 或 ||逻辑或and 或 逻辑与 2、group by 分组 “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组所谓的分组就是将一个“数据集”划分成若干个“小区域”然后针对若干个“小区域”进行数据处理。 select 类别, sum(数量) as 数量之和
from A
group by 类别 注group by语句中select指定的字段必须是“分组依据字段”其他字段若想出现在select中则必须包含在聚合函数中。 mysql中五种常用的聚合函数 1max(列名)求最大值。 2min(列名)求最小值。 2sum(列名)求和。 4avg(列名)求平均值。 5count(列名)统计记录的条数。 3、having having子句可以让我们筛选成组后的各种数据where子句在聚合前先筛选记录也就是说作用在group by和having子句前。而 having子句在聚合后对组记录进行筛选。 示例 select 类别, sum(数量) as 数量之和 from A
group by 类别
having sum(数量) 18 示例Having和Where的联合使用方法 select 类别, SUM(数量)from A
where 数量 8
group by 类别
having SUM(数量) 10 where和having的区别 作用的对象不同。WHERE 子句作用于表和视图HAVING 子句作用于组。WHERE 在分组和聚集计算之前选取输入行因此它控制哪些行进入聚集计算 而 HAVING 在分组和聚集之后选取分组的行。因此WHERE 子句不能包含聚集函数 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反HAVING 子句总是包含聚集函数。严格说来你可以写不使用聚集的 HAVING 子句 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。在上面的例子中我们可以在 WHERE 里应用数量字段来限制因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算。综上所述having一般跟在group by之后执行记录组选择的一部分来工作的。where则是执行所有数据来工作的。再者having可以用聚合函数如having sum(qty)1000 例子where group by having 函数 综合查询 练习表 查询出两门及两门以上不及格者的平均成绩(注意是所有科目的平均成绩) 错误情况1题意理解错误理解成查出不及格科目的平均成绩。 错误情况2count()不正确SQL错误。 count(a),无论a是什么都只是数一行count时每遇到一行就数一个a跟条件无关 正解count(score60)达不到想要的结果并不是条件的问题而是无论count()里的表达式是什么都会数一行。score60 返回 1 或 0所以可以用sum(score60)来计算不及格的科目数 4、order by 排序 1order by price //默认升序排列2order by price desc //降序排列3order by price asc //升序排列与默认一样4order by rand() //随机排列效率不高 5、limit limit [offset,] Noffset 偏移量可选不写则相当于limit 0,NN 取出条目 示例取价格第4-6高的商品 select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3; 总结 select子句顺序 子句 说明 是否必须使用 select 要返回的列或表示式 是 form 从中检索数据的表 仅在从表选择数据时使用 where 行级过滤 否 group by 分组说明 仅在按组计算聚集时使用 having 组级过滤 否 order by 输出排序顺序 否 limit 要检索的行数 否 二、mysql子查询 1、where型子查询把内层查询结果当作外层查询的比较条件 1查询id最大的一件商品(使用排序分页实现) SELECT goods_id,goods_name,shop_price FROM goods ORDER BY goods_id DESC LIMIT 1; 2查询id最大的一件商品(使用where子查询实现) SELECT goods_id,goods_name,shop_price FROM goods WHERE goods_id (SELECT MAX(goods_id) FROM goods); 3查询每个类别下id最大的商品(使用where子查询实现) SELECT goods_id,goods_name,cat_id,shop_price FROM goods WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id); 2、from型子查询(把内层的查询结果当成临时表供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。) 1查询每个类别下id最大的商品(使用from型子查询) SELECT goods_id,goods_name,cat_id,shop_price FROM
(SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id ASC,goods_id DESC) AS tmp
GROUP BY cat_id; 子查询查出的结果集看第二张图可以看到每个类别的第一条的商品id都为该类别下的最大值。然后将这个结果集作为一张临时表巧妙的使用group by 查询出每个类别下的第一条记录即为每个类别下商品id最大。 3、exists型子查询把外层sql的结果拿到内层sql去测试如果内层的sql成立则该行取出。内层查询是exists后的查询。 1从类别表中取出其类别下有商品的类别(如果该类别下没有商品则不取出)[使用where子查询] SELECT c.cat_id,c.cat_name FROM category c WHERE c.cat_id IN (SELECT g.cat_id FROM goods g GROUP BY g.cat_id); 2从类别表中取出其类别下有商品的类别(如果该类别下没有商品则不取出)[使用exists子查询] SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id c.cat_id); exists子查询如果exists后的内层查询能查出数据则表示存在为空则不存在。 三、连接查询 学习连接查询先了解下笛卡尔积看下百度给出的解释 在数据库中一张表就是一个集合每一行就是集合中的一个元素。表之间作联合查询即是作笛卡尔乘积比如A表有5条数据B表有8条数据如果不作条件筛选那么两表查询就有 5 X 8 40 条数据。 先看下用到的测试表基本信息我们要实现的功能就是查询商品的时候从类别表将商品类别名称关联查询出来。 行数类别表14条商品表4条。 结构商品表和类别表都有一个cat_id 1、全相乘(不是全连接、连接查询)全相乘是作笛卡尔积 两表全相乘就是直接从两张表里查询从查询的截图看出总共查出了 4 X 14 56 条记录这些记录是笛卡尔乘积的结果即两两组合 但我们要的是每个商品信息显示类别名称而已这里却查出了56条记录其中有52条记录都是无效的数据全相乘的查询效率低。 SELECT goods_id,goods_name,cat_name FROM mingoods,category; 如果在两张表里有相同字段做联合查询的时候要区别表名否则会报错误(模糊不清)。 SELECT goods_name,cat_id,cat_name FROM mingoods,category; 添加条件使两表关联查询这样查出来就是商品和类别一一对应了。虽然这里查出来4条记录但是全相乘效率低全相乘会在内存中生成一个非常大的数据(临时表)因为有很多不必要的数据。 如果一张表有10000条数据另一张表有10000条数据两表全相乘就是100W条数据是非常消耗内存的。而且全相乘不能好好的利用索引因为全相乘生成一张临时表临时表里是没有索引的大大降低了查询效率。 SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id, c.cat_name FROM mingoods g, category c WHERE g.cat_id c.cat_id; 2、左连接查询 left join ... on ... 语法 select A.filed, [A.filed2, .... ,] B.filed, [B.filed4...,] from left table as A left join right table as B on expression 假设有A、B两张表左连接查询即 A表在左不动B表在右滑动A表与B表通过一个关系来关联行B表去匹配A表。 2.1、先来看看on后的条件恒为真的情况 SELECT g.goods_name,g.cat_id, c.cat_id ,c.cat_name FROM mingoods g LEFT JOIN category c ON 1; 跟全相乘相比从截图可以看出总记录数仍然不变还是 4 X 14 56 条记录。但这次是商品表不动类别表去匹配因为每次都为真所以将所有的记录都查出来了。左连接其实就可以看成左表是主表右表是从表。 2.2 、根据cat_id使两表关联行 SELECT g.goods_name,g.cat_id,c.cat_id,c.cat_name FROM mingoods g LEFT JOIN category c ON g.cat_id c.cat_id; 使用左连接查询达到了同样的效果但是不会有其它冗余数据查询速度快消耗内存小而且使用了索引。左连接查询效率相比于全相乘的查询效率快了10倍以上。 左连接时mingoods表(左表)不动category表(右表)根据条件去一条条匹配虽说category表也是读取一行行记录然后判断cat_id是否跟mingoods表的相同但是左连接使用了索引cat_id建立了索引的话查询速度非常快所以整体效率相比于全相乘要快得多全相乘没有使用索引。 2.3、查询出第四个类别下的商品要求显示商品名称 SELECT g.goods_name,g.cat_id,c.cat_name,g.shop_price FROM goods g LEFT JOIN category c ON g.cat_id c.cat_id WHERE g.cat_id 4; 2.4 、对于左连接查询如果右表中没有满足条件的行则默认填充NULL。 SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id,c.cat_id FROM mingoods g LEFT JOIN mincategory c ON g.cat_id c.cat_id; 3、右连接查询 right join ... on ... 语法 select A.field1,A.field2,..., B.field3,B.field4 from left table A right join right table B on expression 右连接查询跟左连接查询类似只是右连接是以右表为主表会将右表所有数据查询出来而左表则根据条件去匹配如果左表没有满足条件的行则左边默认显示NULL。左右连接是可以互换的。 SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id,c.cat_name FROM mingoods g RIGHT JOIN mincategory c ON g.cat_id c.cat_id; 4、内连接查询 inner join ... on ... 语法 select A.field1,A.field2,.., B.field3, B.field4 from left table A inner join right table B on expression 内连接查询就是取左连接和右连接的交集如果两边不能匹配条件则都不取出。 SELECT g.goods_name,g.cat_id, c.* from mingoods g INNER JOIN mincategory c ON g.cat_id c.cat_id; 5、全连接查询 full join ... on ... 语法 select ... from left table full join right table on expression 全连接会将两个表的所有数据查询出来不满足条件的为NULL。 全连接查询跟全相乘查询的区别在于如果某个项不匹配全相乘不会查出来全连接会查出来而连接的另一边则为NULL。 6、联合查询 union 语法 select A.field1 as f1, A.field2 as f2 from table1 A union (select B.field3 as f1, field4 as f2 from table2 B) union是求两个查询的并集。union合并的是结果集不区分来自于哪一张表所以可以合并多张表查询出来的数据。 6.1、将两张表的数据合并查询出来 SELECT id, content, user FROM comment UNION (SELECT id, msg AS content, user FROM feedback); 6.2、union查询列名不一致时以第一条sql语句的列名对齐 SELECT id, content, user FROM comment UNION (SELECT id, msg, user FROM feedback); 6.3、使用union查询会将重复的行过滤掉 SELECT content,user FROM comment UNION (SELECT msg, user FROM feedback); 6.4、使用union all查询所有重复的行不会被过滤 SELECT content,user FROM comment UNION ALL (SELECT msg, user FROM feedback); 6.5、union查询如果列数不相等会报列数不相等错误 6.6、union 后的结果集还可以再做筛选 SELECT id,content,user FROM comment UNION ALL (SELECT id, msg, user FROM feedback) ORDER BY id DESC; union查询时order by放在内层sql中是不起作用的因为union查出来的结果集再排序内层的排序就没有意义了因此内层的order by排序在执行期间被mysql的代码分析器给优化掉了。 (SELECT id,content,user FROM comment ORDER BY id DESC) UNION ALL (SELECT id, msg, user FROM feedback ORDER BY id DESC); order by 如果和limit一起使用就显得有意义了就不会被优化掉。 ( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id 3 ORDER BY shop_price DESC LIMIT 3 )
UNION
( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id 4 ORDER BY shop_price DESC LIMIT 2 ); 6.7、练习 SELECT name, SUM(money) FROM ( ( SELECT * FROM A ) UNION ALL ( SELECT * FROM B ) ) tmp GROUP BY name; 连接查询总结 1、在数据库中一张表就是一个集合每一行就是集合中的一个元素。连接查询即是作笛卡尔积比如A表有1W条数据B表有1W条数据那么两表查询就有 1W X 1W 100W 条数据 2、如果在两张表里有相同字段做联合查询的时候要区别表名否则会报错误(ambiguous 模糊不清) 3、全相乘效率低全相乘会在内存中生成一个非常大的数据(临时表)因为有很多不必要的数据。 如果一张表有10000条数据另一张表有10000条数据两表全相乘就是100W条数据是非常消耗内存的。 而且全相乘不能好好的利用索引因为全相乘生成一张临时表临时表里是没有索引的大大降低了查询效率。 4、左连接查询时以左表为主表会将左表所有数据查询出来左表不动右表根据条件去一条条匹配如果没有满足条件的记录则右边返回NULL。 右连接查询值以右表为主表会将右表所有数据查询出来右表不动左表则根据条件去匹配如果左表没有满足条件的行则左边返回NULL。 左右连接是可以互换的A left join B B right join A (都是以A为主表) 。 左右连接既然可以互换出于移植兼容性方面的考虑尽量使用左连接。 5、连接查询时虽说也是读取一行行记录然后判断是否满足条件但是连接查询使用了索引条件列建立了索引的话查询速度非常快所以整体效率相比于全相乘要快得多全相乘是没有使用索引的。 使用连接查询查询速度快消耗内存小而且使用了索引。连接查询效率相比于全相乘的查询效率快了10倍以上。 6、内连接查询就是取左连接和右连接的交集如果两边不能匹配条件则都不取出。 7、MySql可以用union(联合查询)来查出左连接和右连接的并集。 union查询会过滤重复的行union all 不会过滤重复的行。 union查询时union之间的sql列数必须相等列名以第一条sql的列为准列类型可以不一样但没太大意义。 union查询时order by放在内层sql中是不起作用的因为union查出来的结果集再排序内层的排序就没有意义了因此内层的order by排序在执行期间被mysql的代码分析器给优化掉了。 但是order by 如果和limit一起使用就显得有意义了会影响最终结果集就不会被优化掉。order by会根据最终是否会影响结果集而选择性的优化。 注union和union all的区别union会去掉重复的记录在结果集合并后悔对新产生的结果集进行排序运算效率稍低union all直接合并结果集如果确定没有重复记录建议使用union all。 8、 LEFT JOIN 是 LEFT OUTER JOIN 的缩写同理RIGHT JOIN 是 RIGHT OUTER JOIN 的缩写JOIN 是 INNER JOIN 的缩写。 关联查询 1、使用join关键字关联查询 1、内连接inner join 连接两张表连接条件使用on关键字内连接只会显示匹配的数据记录。 eg:查询学生姓名、科目、分数 select a.name 姓名,b.subject 科目,b.score 分数 from student a inner join score b on a.id b.sid; 2、左连接left join 返回左表中所有记录以及右表中符合连接条件的所有记录。 eg: 使用左连接查询学生姓名、科目、分数 select a.name 姓名,b.subject 科目,b.score 分数 from student a left join score b on a.id b.sid; 3、右连接right join 返回右表中所有记录以及左表中符合连接条件的所有记录。 eg:使用右连接查询学生姓名、科目、分数 select a.name 姓名,b.subject 科目,b.score 分数 from student a right join score b on a.id b.sid; 注内外连接区别内连接只会显示匹配的数据记录外连接例如左连接会把左边表中所有记录显示出来即使在右边表中没有匹配记录也会显示左表的数据右连接反之。 2、使用表和表之间相同id关联查询 这种关联方式和内连接一样只会显示出匹配的数据 select a.name 姓名,b.subject 科目,b.score 分数 from student a,score b where a.id b.sid;
