MySql查询（三）

Select 5种子句详解

1:where子句  条件查询

查出一张表的所有行,所有列

Select * from tableName;

模糊查询:

案例:想查找"诺基亚"开头的所有商品

Like->像

% --> 通配任意字符

'_' --> 单个字符

 

查询模型(重要)

列就是变量,在每一行上,列的值都在变化.

Where条件是表达式,在哪一行上表达式为真,哪一行就取出来

比如下面的条件, shop_price在不同的行,有不同的值.

在哪一行时,shop_price>5000如果为真,则这行取出来.

查询结果集--在结构上可以当成表看
select count(*) from 表名, 查询的就是绝对的行数,哪怕某一行所有字段全为NULL,也计算在内.

而select couht(列名) from 表名,

查询的是该列不为null的所有行的行数.

用count(*),count(1),谁好呢?

其实,对于myisam引擎的表,没有区别的.

这种引擎内部有一计数器在维护着行数.

Innodb的表,用count(*)直接读行数,效率很低,因为innodb真的要去数一遍.

 

group by

作用:把行按字段分组

语法:group by col1,col2,...colN

运用场合
常见于统计场合,如按栏目计算帖子数,

统计每个人的平均成绩等.

思考:

全班同学排队,

校长对老师说: 统计班级同学的姓名和平均年龄[返回1行]

语义上的疑问: 平均年龄好算,只有一个结果,但是,把谁的姓名和平均年龄放在一块返回呢?

语义上就解释不通,但是mysql中却偏偏可以取姓名,而且是把队伍的第一位同学的姓名返回.

这是mysql的一个特点,出于可移植性和规范性,不推荐这么写.

严格的讲,以group by  a,b,c 为列,则select的列,只能在a,b,c里选择,语义上才没有矛盾.

 

having

having与where异同点

having与where类似,可筛选数据

where后的表达式怎么写,having就怎么写

where针对表中的列发挥作用,查询数据

having针对查询结果中的列发挥作用,筛选数据

Order by

当最终结果集出来后,可以进行排序.

排序的语法:

Order by 结果集中的列名desc/asc

 

例:order byshop_price desc ,按价格降序排列

Order by add_time asc ,按发布时间升序排列.

 

多字段排序也很容易

Order by 列1 desc/asc , 列2 desc/asc  , 列3 desc,asc

 

Limit 在语句的最后, 起到限制条目的作用

Limit [offset,] N

Offset: 偏移量,----跳过几行

N: 取出条目

Offset,如果不写,则相当于  limit 0,N

良好的理解模型

Where 表达式 ,把表达式放在行中,看表达式是否为真

列: 理解成变量,可以运算

取出结果: 可以理解成一张临时表

子查询

Where型子查询: 指把内层查询的结果作为外层查询的比较条件.

典型题:查询最大商品,最贵商品

 

Where型子查询

如果 where 列=(内层sql),则内层sql返回的必是单行单列,单个值

如果 where 列 in (内层sql), 则内层sql只返回单列,可以多行.

 

From 型子查询: 把内层的查询结果当成临时表,供外层sql再次查询

典型题:查询每个栏目下的最新/最贵商品

Exists子查询 : 把外层的查询结果,拿到内层,看内层的查询是否成立.

典型题: 查询有商品的栏目

集合(set)的知识

高中时学过的集合

集合的特点: 无序性,

集合的特点:唯一性

集合的运算: 求并集,求交集,笛卡尔积(相乘)

笛卡尔积,即集合的元素,做两两的组合.

表与集合的关系

一张表就是一个集合

每一行就是一个元素

集合不能重复,但我有可能两行数据完全一样

答:mysql内部每一行,还有一个rowid.

 

两表做全相乘

从行的角度来看: 就是2表每一行,两两组合.

从列的角度来看: 结果集中的列,是两表的列名的相加.

 
作全相乘时,也可以有针对性的取出某几列.

左连接的语法:

假设A表在左,不动,B表在A表的右边滑动.

A表与B表通过一个关系来筛选B表的行.

语法:

A left join B on 条件 条件为真,则B表对应的行,取出

A left join B on 条件

这一块,形成的也是一个结果集,可以看成一张表 设为C

既如此,可以对C表作查询,自然where,group ,having ,order by ,limit 照常使用

 

问:C表的可以查询的列有哪些列?

答: A B的列都可以查

左连接右连接,内连接的区别在哪儿?

同学见面会:

男生表

姓名	配偶
屌丝	A
李四	B
王五	C
高富帅	D
郑七	E

 

女生表

姓名	配偶
空姐	B
大S	C
阿娇	D
张柏芝	D
林黛玉	E
宝钗	F

 

主持人大声说:

所有的男士,站到舞台上,带上自己的配偶,(没有的拿块牌子, 上写NULL)

思考:张三上不上舞台呢?

答:上,

问:张三没有对应的行怎么办?

答:用NULL补齐

结果如下

姓名	配偶	女生姓名	配偶
屌丝	A	NULL	NULL
李四	B	空姐	B
王五	C	大S	C
高富帅	D	阿娇	D
高富帅	D	张柏芝	D
郑七	E	林黛玉	E

 

这种情况就是  男生 left join 女生.

 

主持人说:所有女生请上舞台,有配偶的带着, 没有的,写个NULL补齐.

Select 女生 left join 男生 on 条件

 

左右连接是可以互换的

A left join B, 就等价于 B right join A

注意：既然左右连接可以互换，尽量用左连接，出于移植时兼容性方面的考虑．

内连接的特点

内连接：查询左右表都有的数据，即：不要左右连接中null的那一部分，是左右连接的交集。

主持人说：　所有有配偶的男生／女生，走到舞台上来

这种情况下：　屌丝和宝钗都出局

姓名	配偶	女生姓名	配偶
李四	B	空姐	B
王五	C	大S	C
高富帅	D	阿娇	D
高富帅	D	张柏芝	D
郑七	E	林黛玉	E

如果从集合的角度

A inner join B

和　left join/right　join的关系

答：　内连接是左右连接的交集

 

Union:合并2条或多条语句的结果

语法:

Sql1 union sql2

能否从2张表查询再union呢?

答:可以,union 合并的是"结果集",不区分在自于哪一张表.

 

问:取自于2张表,通过"别名"让2个结果集的列一致.

那么,如果取出的结果集,列名字不一样,还能否union.

答:可以,如下图,而且取出的最终列名,以第1条sql为准

 

问:union满足什么条件就可以用了?

答:只要结果集中的列数一致就可以.

问: union后结果集,可否再排序呢?

答:可以的.

Sql1 union sql2 order by 字段

注意: order by 是针对合并后的结果集排的序.

 

问: 使用order by 的注意事项

如下,内层语句的desc怎么没发挥作用呢?

 

思考如下语句:

(SELECTgoods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 4 ORDER BYshop_price DESC)

UNION

(SELECTgoods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 5 ORDER BYshop_price DESC)

order by shop_price asc;

 

外层语句还要对最终结果,再次排序.

因此,内层的语句的排序,就没有意义.

因此:内层的order by语句单独使用,不会影响结果集,仅排序,

在执行期间,就被Mysql的代码分析器给优化掉了.

内层的order by 必须能够影响结果集时,才有意义.

比如配合limit使用.如下例.

 

思考下个问题:

查出: 第3个栏目下,价格前3高的商品,和第4个栏目下,价格前2高的商品.

用union来完成

这一次:内层的order by 发挥了作用,因为有limit ,order 会实际影响结果集,有意义.

如果Union后的结果有重复(即某2行,或N行,所有的列,值都一样),怎么办?

答:这种情况是比较常见的,默认会去重.

 

问:如果不想去重怎么办?

答: union all