Day04_MySQLLearning
19_order by 与 limit
-
排序语法:
- order by 结果集中的列名 desc/asc
例:order by shop_price desc 按价格将序排列
order by add_time 按发布时间升序排列 - order by 列1 desc/asc , 列2 desc/asc , 列3 desc/asc
- order by 结果集中的列名 desc/asc
-
limit
- limit 在语句的最后 ,起到限制条目的作用
- limit [offset] , N
- offset: 偏移量,即跳过几行
- N: 取出条目
- offset 如果不写,则相当于 limit 0,N
-
五种子句出现的先后顺序
where , group by , having , order by , limit -
代码:
#按shop_price字段来降序排列 select goods_id , goods_name , shop_price from goods where cat_id = 4 order by shop_price desc; #按发布时间升序排列,即发布早的,时间戳小的,靠前 select goods_id , goods_name , shop_price from goods where cat_id = 4 order by add_time asc; #按栏目排序 select goods_id , cat_id , goods_name , shop_price from goods order by cat_id asc; #我们按栏目升序排列,同一个栏目下的商品,再按商品价格降序排列 select goods_id , cat_id , goods_name , shop_price from goods order by cat_id asc , shop_price desc; #限制条目 limit select goods_id , cat_id , goods_name , shop_price from goods where cat_id = 3 order by shop_price asc limit 10; #查询出本店价格最高的前三名 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 0 , 3; #查询出本店最高的 第三名到第五名 商品 #取第三到第五,即意味着跳过第一 ,第二,因此偏移量offset是2 #取第三,四,五条,即取三条,因此N=3 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 2 , 3; #offset是跳过的行数,N是实际取的行数 #取出价格最高的那一行商品 #思路:按价格降序排列,最高的排在前面,取1个,即第一名 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 0 , 1; #如果offset为0,可以不写 #即一下语句也是取出价格最高的一行商品 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 1;
20_子句查询陷阱
-
代码:
#按shop_price字段来降序排列 select goods_id , goods_name , shop_price from goods where cat_id = 4 order by shop_price desc; #按发布时间升序排列,即发布早的,时间戳小的,靠前 select goods_id , goods_name , shop_price from goods where cat_id = 4 order by add_time asc; #按栏目排序 select goods_id , cat_id , goods_name , shop_price from goods order by cat_id asc; #我们按栏目升序排列,同一个栏目下的商品,再按商品价格降序排列 select goods_id , cat_id , goods_name , shop_price from goods order by cat_id asc , shop_price desc; #限制条目 limit select goods_id , cat_id , goods_name , shop_price from goods where cat_id = 3 order by shop_price asc limit 10; #查询出本店价格最高的前三名 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 0 , 3; #查询出本店最高的 第三名到第五名 商品 #取第三到第五,即意味着跳过第一 ,第二,因此偏移量offset是2 #取第三,四,五条,即取三条,因此N=3 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 2 , 3; #offset是跳过的行数,N是实际取的行数 #取出价格最高的那一行商品 #思路:按价格降序排列,最高的排在前面,取1个,即第一名 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 0 , 1; #如果offset为0,可以不写 #即一下语句也是取出价格最高的一行商品 select goods_id , goods_name , shop_price from goods order by shop_price desc limit 1; #查询出每个栏目下id号最大(最新)的一条商品 #第一种错误,直接group by cat_id select goods_id , cat_id , goods_name from goods group by cat_id; #另一种错误 select max(goods_id) , cat_id , goods_name from goods group by cat_id; #我们这么想 , 既然group时,mysql是取每个分组下第一次出现的行 #我们就先把goods_id最大的排在前面 select goods_id , cat_id , goods_name from goods order by cat_id asc , goods_id desc; #再次基础上,我在一分组,不就行了吗? #先order , 再group select goods_id , cat_id , goods_name from goods order by cat_id asc , goods_id desc group by cat_id ; #语法错误 #要用子查询去做
21_where型子查询
-
概念:
指把内层查询的结果作为外层查询的比较条件。
典型题:查询最大商品,最贵商品 -
特点:
- 如果where 列 = (内层sql) ,则内层sql返回的必是单行单列的一个值。
- 如果where 列 in(内层sql), 则内层sql只能返回单列,可以多行。
-
代码:
#接下来学习子查询 #查出本网站最新的(goods_id最大)的一条商品 #思路:按goods_id desc排序,再去第一行 select goods_id , goods_name from goods order by goods_id desc limit 0 , 1; #查出本网站最新的(goods_id最大)的一条商品,要求:不用排序 select goods_id , goods_name from goods where goods_id = 33; #这个做法不具备通用性,增加或者删除了一条商品,则最大goods_id已经不是33了 select max(goods_id) from goods; select goods_id , goods_name from goods where goods_id = 33; #以后,凡需要查最新商品,先用max()查出最大的goods_id, #然后再根据goods_id查询商品 #就算以后商品表再怎么变,select max()语句的返回值,始终是只想最大goods_id的 select goods_id , goods_name from goods where goods_id = (select max(goods_id) from goods); #用where型子查询,查询“每个栏目下goods_id最大的商品” #第一步:先查出每个栏目下,最大的goods_id select max(goods_id) from goods group by cat_id; #下一步,我们只需,再把goods_id = 16 , 32 , 33...28的这几条商品取出来 select goods_id , goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
22_from型子查询
-
概念:
把内层的查询结果当成临时表,供外层sql再次查询
典型题:查询每个栏目下的最新/最贵商品 -
代码:
#这个结果,你能区分是表,还是查询结果吗? select goods_id , shop_price , market_price , market_price - shop_price as discount from goods limit 10; #from型子查询 ,查询每个栏目下,goods_id最大的商品 select goods_id , cat_id , goods_name from goods order by cat_id asc , goods_id desc; #如果存在一张如上表,只要对如上表group一下,即可得到每个栏目goods_id最大的商品 #假设存在这张表,表名叫temp select * from temp group by cat_id; select * from (select goods_id , cat_id , goods_name from goods order by cat_id asc , goods_id desc) as temp group by cat_id;
23_exists型子查询
-
概念:
把外层的查询结果,拿到内层,看内层的查询是否成立
典型题:查询有商品的栏目 -
代码:
#别把14个栏目都取出来,只把下面有商品的栏目取出来 #取栏目表,且只取出下面有商品的栏目 #思考:什么样的表,叫做下面有商品 #答:设某栏目cat_id为N,则select * from goods where cat_id = N; #能取出数据,则说明该栏目有商品 select cat_id , cat_name from categroy where exists (select * from goods where goods_id.cat_id = category.cat_id);
24_奇怪的NULL
-
为什么建表时,加not null default ‘’/default0?
因为不想让表中出现null值 -
为什么不想要null值
- 不好比较,null是一种类型,比较时,只能用专门的 is null 和 is not null 来比较
- 碰到运算符,一律返回null
- 效率不高,影响提高索引效率
- 因此,我们往往在建表时 声明 not null default ‘’/0;
-
代码:
mysql> set names gbk; Query OK, 0 rows affected (0.06 sec) mysql> use worker; Database changed mysql> #建表时,列后面not null default '' , default 0,这是什么意思 mysql> #答:就是让这个列值不为NULL,如果某个列确实没填值,也有默认值,也不为null mysql> #为什么不希望让列的值为null呢: mysql> create table test9 -> (sname varchar(20)) -> engine myisam charset utf8; Query OK, 0 rows affected (0.25 sec) mysql> insert into test9 -> values -> ('lisi'),('wangwu'),('null'); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test9; +--------+ | sname | +--------+ | lisi | | wangwu | | null | +--------+ 3 rows in set (0.08 sec) mysql> insert into test9 values (null); Query OK, 1 row affected (0.01 sec) mysql> insert into test9 values (NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from test9; +--------+ | sname | +--------+ | lisi | | wangwu | | null | | NULL | | NULL | +--------+ 5 rows in set (0.00 sec) mysql> delete from test9 where sname = 'null'; Query OK, 1 row affected (0.05 sec) mysql> select * from test9; +--------+ | sname | +--------+ | lisi | | wangwu | | NULL | | NULL | +--------+ 4 rows in set (0.00 sec) mysql> #查询出用户名不为null的行 mysql> select * from test9 where sname != null; Empty set (0.03 sec) mysql> #lisi wangwu 没查出来?为什么 mysql> #查sname为null的行 mysql> select * from test9 where sname = null; Empty set (0.00 sec) mysql> #又是空 mysql> select 2>1; +-----+ | 2>1 | +-----+ | 1 | +-----+ 1 row in set (0.00 sec) mysql> select 2<1; +-----+ | 2<1 | +-----+ | 0 | +-----+ 1 row in set (0.00 sec) mysql> select 39<60; +-------+ | 39<60 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> select 'lisi' = null; +---------------+ | 'lisi' = null | +---------------+ | NULL | +---------------+ 1 row in set (0.00 sec) mysql> #null为假,lisi = null是假 mysql> select null = null; +-------------+ | null = null | +-------------+ | NULL | +-------------+ 1 row in set (0.01 sec) mysql> #null是空 , 四大皆空的空 mysql> #null的比较需要用特殊的运算符, is null , is not null mysql> select * from test9 where sname is not null; +--------+ | sname | +--------+ | lisi | | wangwu | +--------+ 2 rows in set (0.00 sec) mysql> select * from test9 where sname is null; +-------+ | sname | +-------+ | NULL | | NULL | +-------+ 2 rows in set (0.00 sec)
25_两表全连接查询报价单
-
代码:
mysql> create table test10 -> (id int, -> sname varchar(20)) -> engine myisam charset utf8; Query OK, 0 rows affected (0.36 sec) mysql> create table test11 -> (cat_id int , -> cname varchar(20) ) -> engine myisam charset utf8; Query OK, 0 rows affected (0.05 sec) mysql> insert into test10 -> values -> (1 , '云彩'), -> (2 , '月亮'), -> (3 , '星星'); Query OK, 3 rows affected (0.16 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into test11 -> values -> (95 , '猴子'), -> (96 , '老虎'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test10; +------+-------+ | id | sname | +------+-------+ | 1 | 云彩 | | 2 | 月亮 | | 3 | 星星 | +------+-------+ 3 rows in set (0.09 sec) mysql> select * from test11; +--------+-------+ | cat_id | cname | +--------+-------+ | 95 | 猴子 | | 96 | 老虎 | +--------+-------+ 2 rows in set (0.02 sec) mysql> #test10看成集合,3个元素,test11看成集合,有2个元素 mysql> #如果把test10,test11相乘,得到的新集合,几个元素? mysql> #答:6个元素 mysql> #在数据库的操作上,如何操作表,完成集合相乘的效果? mysql> #答:直接用‘,’隔开表明,查询即可 mysql> select * from test10 , test11; +------+-------+--------+-------+ | id | sname | cat_id | cname | +------+-------+--------+-------+ | 1 | 云彩 | 95 | 猴子 | | 1 | 云彩 | 96 | 老虎 | | 2 | 月亮 | 95 | 猴子 | | 2 | 月亮 | 96 | 老虎 | | 3 | 星星 | 95 | 猴子 | | 3 | 星星 | 96 | 老虎 | +------+-------+--------+-------+ 6 rows in set (0.00 sec) mysql> #ambigous意思是模糊的 mysql> #cat_id ,在2张表都有,到底指哪张表的cat_id 字段? mysql> #如果在多表联查时,某一列名,在2张以上表都有, mysql> #则需要在列名前,指定表名,即 表名.列名 mysql> select goods_id , minigoods.cat_id , goods_name , categroy.cat_id , cat_name -> from minigoods , category; mysql> select goods_id , minigoods.cat_id , goods_name , category.cat_id , cat_name -> from minigoods,category -> where minigoods.cat_id = category.cat_id;
26_ 左连接语法及应用
-
形象描述:
假设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表可以查询的列有哪些?
答:AB的列都可以查
-
代码:
#左连接之后,还能用where等子句吗? #答:当然能 #取出第4个栏目下的商品,以及商品的栏目名 select goods_id , goods_name , cat_name from goods left join category on goods.cat_id = category.cat_id where goods.cat_id = 4; select goods_id , goods.cat_id , goods_name , cat_name from goods left join category on goods.cat_id = category.cat_id where goods.cat_id = 4;
27_左右内连接的区别
-
左右连接是可以互换的
A left join B 就等价于 B right join A -
注意:
- 既然左右连接可以互换,出于时兼容性方面的考虑,尽量用左连接。
- mysql中不支持外连接
-
内连接的特点:
从集合角度看内连接(A inner join B)是左右连接(A left/right join B)的交集 -
代码:
mysql> set names gbk; Query OK, 0 rows affected (0.09 sec) mysql> use worker; Database changed mysql> create table boy -> (bname varchar(20), -> other char(1) ) -> engine myisam charset utf8; Query OK, 0 rows affected (0.33 sec) mysql> insert into boy -> values -> ('屌丝' ,'A'), -> ('李四' ,'B'), -> ('王五' , 'C'), -> ('高富帅' , 'D'), -> ('郑七' , 'E'); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> create table girl -> (gname varchar(20) , -> other char(1) ) -> engine myisam charset utf8; Query OK, 0 rows affected (0.03 sec) mysql> insert into girl -> values -> ('空姐' , 'B'), -> ('大S' , 'C'), -> ('阿娇' , 'D'), -> ('张柏芝' , 'D'), -> ('林黛玉' , 'E'), -> ('宝钗' , 'F'); Query OK, 6 rows affected (0.03 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from boy; +--------+-------+ | bname | other | +--------+-------+ | 屌丝 | A | | 李四 | B | | 王五 | C | | 高富帅 | D | | 郑七 | E | +--------+-------+ 5 rows in set (0.13 sec) mysql> select * from girl; +--------+-------+ | gname | other | +--------+-------+ | 空姐 | B | | 大S | C | | 阿娇 | D | | 张柏芝 | D | | 林黛玉 | E | | 宝钗 | F | +--------+-------+ 6 rows in set (0.00 sec) mysql> select boy.* , girl.* from -> boy left join girl on boy.other = girl.other; +--------+-------+--------+-------+ | bname | other | gname | other | +--------+-------+--------+-------+ | 屌丝 | A | NULL | NULL | | 李四 | B | 空姐 | B | | 王五 | C | 大S | C | | 高富帅 | D | 阿娇 | D | | 高富帅 | D | 张柏芝 | D | | 郑七 | E | 林黛玉 | E | +--------+-------+--------+-------+ 6 rows in set (0.03 sec) mysql> #女生上台,带着另一半,没有另一半的,用null补齐 mysql> select boy.* , girl.* -> from girl left join boy on girl.other = boy.other; +--------+-------+--------+-------+ | bname | other | gname | other | +--------+-------+--------+-------+ | 李四 | B | 空姐 | B | | 王五 | C | 大S | C | | 高富帅 | D | 阿娇 | D | | 高富帅 | D | 张柏芝 | D | | 郑七 | E | 林黛玉 | E | | NULL | NULL | 宝钗 | F | +--------+-------+--------+-------+ 6 rows in set (0.02 sec) mysql> #注意,a left join b , 并不是说a表的就一定在左边,只是在查询数据时,以a表为准 mysql> select other , bname from boy; +-------+--------+ | other | bname | +-------+--------+ | A | 屌丝 | | B | 李四 | | C | 王五 | | D | 高富帅 | | E | 郑七 | +-------+--------+ 5 rows in set (0.00 sec) mysql> #女生上台,带着另一半,没有另一半的,用null补齐 mysql> #不能用左连接来做 mysql> #用右连接来做 mysql> #刚才是女生 left join 男 mysql> #现在是男 right join 女 mysql> select boy.* , girl.* -> from -> boy right join girl on boy.other = girl.other; +--------+-------+--------+-------+ | bname | other | gname | other | +--------+-------+--------+-------+ | 李四 | B | 空姐 | B | | 王五 | C | 大S | C | | 高富帅 | D | 阿娇 | D | | 高富帅 | D | 张柏芝 | D | | 郑七 | E | 林黛玉 | E | | NULL | NULL | 宝钗 | F | +--------+-------+--------+-------+ 6 rows in set (0.00 sec) mysql> select boy.* , girl.* -> from -> boy inner join girl on boy.other = girl.other; +--------+-------+--------+-------+ | bname | other | gname | other | +--------+-------+--------+-------+ | 李四 | B | 空姐 | B | | 王五 | C | 大S | C | | 高富帅 | D | 阿娇 | D | | 高富帅 | D | 张柏芝 | D | | 郑七 | E | 林黛玉 | E | +--------+-------+--------+-------+ 5 rows in set (0.00 sec) mysql> exit