PHP基础重点---高级查询0604

高级查询
一：分组查询
1、group by
select * from car group by powers;
-------------------------------------------------------
| num | d_id | name | age | sex | homeaddr |
-------------------------------------------------------
| 1 | 1001 | 张三 | 26 | 男 | beijinghdq|
| 2 | 1002 | 李四 | 24 | 女 | beijingcpq|
| 3 | 1003 | 王五 | 25 | 男 |changshaylq|
| 4 | 1004 | Aric | 15 | 男 | England |
--------------------------------------------------------
select * from employee group by d_id,sex;

2、group by group_concat(字段名）
select sex,group_concat(name) from employee group by sex;
--------------------------------
| sex | group_concat(name) |
-------------------------------
| 女 | 李四 |
| 男 | 张三，王五，Aric |
-------------------------------

select sex,group_concat(d_id) from employee group by sex;

--------------------------------
| sex | group_concat(d_id) |
--------------------------------
| 女 | 1002 |
| 男 | 1001,1003,1004 |
--------------------------------
3、group by + 函数
4、group by + with rollup
select sex,count(age) from employee group by sex with rollup;
----------------------
| sex | count(age) |
---------------------
| 女 | 1 |
| 男 | 3 |
| NULL| 4 |
---------------------

select sex,group_concat(age) from employee group by sex with rollup;
------------------------------
| sex | group_concat(age) |
------------------------------
| 女 | 24 |
| 男 | 26,25,15 |
| NULL | 24,26,25,15 |
-----------------------------
5、group by + having + 条件
select powers,count(*) from car group by powers having count(*)>1;
select powers,avg(price) from car group by powers having avg(price)>50;
二：链接查询
1、内链接
select a.sid,sname,gcourse,gscore from student as a,score as b where a.sid = b.sid and a.sid = 3 and b.gcourse = 'chinese';

select a.sid,,sname,gcourse,gscore from student as a inner join score as b where/on a.sid = b.sid and a.sid = 3 and b.gcourse = 'chinses';
2、左链接
select a.sid,sname,gcourse,gscore from student as a left join score as b on a.sid = b.sid and a.sid = 3 and b.gcourse = 'chinese';
3、右链接
select a.sid,sname,gcourse,gscore from student as a right join score as b on a.sid = b.sid and a.sid = 3 and b.gcourse = 'chinese';
4、自然链接
select * from student as a natural join score as b;
自动清除笛卡尔积，把重复的列合并
5、交叉链接
select * from student as a corss join score as b;
三：子查询
//select name,price from(select * from car where powers = 130) as aa; (必须起别名）

any/some

select * from student where sid> any/some(select gid from score)
all
select * from student whhere sid > all(select gid from score)
exists
select * from student where exists (select sid from score)
//因为score表中sid没有等于88的 所以导致外层查询不执行

select * from studemt where sid = 1 and exists (select sid from score where sid = 1)

select * from student where exists (子查询是否返回有结果集，如果有，则执行外层查询码，如果没有 则不执行外层查寻）

四：联合查询：查询时，查询的字段个数要一样
union all

select * from student
union all
select * from score
把两张表拼在一起，没有去重效果

select * from student
union
select * from score
两个表联合查询时，如果两行数据每一列都相同（无论是什么类型，只要值一样）就合并去重