------------------------------------ 高级查询

-- as 起别名

select name as 名字 from studnets;

-- 消除重复的行 -- 查看有哪几种xxx

select distinct gender from students; -- 查看有哪几种性别

select distinct name,gender from students; -- 查看有哪几种(性别，姓名)组合

------ where 运算符

---- 比较

-- 等于: =

-- 大于: >

-- 大于等于: >=

-- 小于: <

-- 小于等于: <=

-- 不等于: != 或 <>

select * from students where id <= 4;

---- 逻辑

-- and or not

select * from students where id < 4 or is_delete=0;

-------- 模糊查询

-- like

-- %表示任意多个任意字符

-- _表示一个任意字符

select * from students where name like 'M%';

select * from students where name like 'Mik_';

-------- 范围查询

-- in表示在一个非连续的范围内

select * from students where id in (1, 3);

-- between ... and ...表示在一个连续的范围内

select * from students where cls_id between 1 and 3; -- 1，2，3

--------- 空判断

select * from students where cls_id is not null;

--------------------------------------------------------排序

select * from students order by name asc默认/desc;

select * from students [where age in (10, 11)] order by name; -- 先拿到数据集再排序

--------------------------------------------------------聚合函数

select count(*) from students;

-- select max min avg

select sum(age) from students;

-------------------------------------------------------- 分组

select gender from students group by gender;

-- +--------+

-- | gender |

-- +--------+

-- | 男 |

-- | 女 |

-- | 中性 |

-- | 保密 |

-- +--------+

-------------------- group by + group_concat()

select gender, group_concat(name) from students group by gender;

-- +--------+-----------------------------------------------------------+

-- | gender | group_concat(name) |

-- +--------+-----------------------------------------------------------+

-- | 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 |

-- | 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 |

-- | 中性 | 金星 |

-- | 保密 | 凤姐 |

-- +--------+-----------------------------------------------------------+

-------------------- group by + 集合函数

-- 分别统计性别为男/女的人年龄平均值

select gender,avg(age) from students group by gender;

-------------------- group by + having 过滤

-- 平均年龄大于10的性别

select gender, avg(age) from students group by gender having avg(age) > 10;

-- 人数大于1的性别和人数

select gender, count(*) from students group by gender having count(*) > 1;

-------------------- group by + with rollup 汇总

---- with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;

----------------------------------------分页

select * from students limit 3; -- 取前3条，相当于0,3

---- select * from 表名 limit start,count

select * from students limit 2, 4; -- 跳过2条 从第3条开始取4条

---------------------------------------- 链接查询

select * from classes as c [inner] join students as s on c.id = s.cls_id;

-- +----+-----------+----+------+------+--------+--------+--------+

-- | id | name | id | name | age | height | gender | cls_id |

-- +----+-----------+----+------+------+--------+--------+--------+

-- | 1 | 精英班 | 1 | Mike | 10 | 170.00 | 男 | 1 |

-- | 1 | 精英班 | 2 | John | 11 | 180.00 | 男 | 1 |

-- +----+-----------+----+------+------+--------+--------+--------+

select * from classes as c left join students as s on c.id = s.cls_id;

-- +----+-----------+------+------+------+--------+--------+--------+

-- | id | name | id | name | age | height | gender | cls_id |

-- +----+-----------+------+------+------+--------+--------+--------+

-- | 1 | 精英班 | 1 | Mike | 10 | 170.00 | 男 | 1 |

-- | 1 | 精英班 | 2 | John | 11 | 180.00 | 男 | 1 |

-- +----+-----------+------+------+------+--------+--------+--------+

select * from classes as c right join students as s on c.id=s.cls_id;

-- +------+-----------+----+------+------+--------+--------+--------+

-- | id | name | id | name | age | height | gender | cls_id |

-- +------+-----------+----+------+------+--------+--------+--------+

-- | 1 | 精英班 | 1 | Mike | 10 | 170.00 | 男 | 1 |

-- | 1 | 精英班 | 2 | John | 11 | 180.00 | 男 | 1 |

-- +------+-----------+----+------+------+--------+--------+--------+

---------------------------------------自关联

-- 通常大分类有小分类这种形式的数据放到一个表中，并且pid指向表的id

CREATE TABLE `areainfo` (

`id` int(10) unsigned NOT NULL,

`name` varchar(32) DEFAULT NULL,

`pid` int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`id`)

)

--- 查询广东省下的所有地级市

select c.name from areainfo as p inner join areainfo as c

on c.pid=p.id where p.name='广东省';

--- 查询山东省下的所有地级市和县区

-- 注意： [a join b on 条件] 结果是一个`表`********************

select city.name, county.name from areainfo as county join

(areainfo as city join areainfo as province

on city.pid=province.id and province.pid is null)

on city.id=county.pid

where province.name='山东省';

select city.name, county.name from (areainfo as county join

(areainfo as city join areainfo as province

on city.pid=province.id and province.pid is null)

on city.id=county.pid)

where province.name='山东省';

---------------------------------- 子查询

--- 每个SQL包含两部分 主查询 和 子查询

-- 子查询有三种类型

-- 标量子查询: 子查询返回的结果是一个数据(一行一列)

-- 列子查询: 返回的结果是一列(一列多行)

-- 行子查询: 返回的结果是一行(一行多列)

-- 标量子查询： 将子查询的结果当成一个值

-- 查询大于平均年龄的学生

select * from students where age > (select avg(age) from students);

-- 列子查询： 将子查询的结果当成同一属性(列)多个值的的集合

-- 查询班级还存在的学生的名字

select name from students where cls_id in (select id from classes);

-- 行子查询： 将多列数据看成一条数据

-- 查找班级年龄最大,身高最高的学生

select * from students where (height, age)=

(select max(height), max(age) from students);

-- 只有在最大身高、最大年龄刚好是一个人的时候才能查找到数据。