1.分组查询配合GROUP_CONCAT()来使用，可以看到每个组中的详细信息：
按照性别分组，得到每组中人员的名称
SELECT *,GROUP_CONCAT(username) FROM employee GROUP BY sex;

2.统计员工表中员工数目，以及薪水的总和、最大值、最小值、平均值
SELECT id AS '编号',username AS '用户名',COUNT(*) AS '员工总数',SUM(salary) AS '总薪水',MAX(s
alary) AS '最高薪水',MIN(salary) AS '最低薪水',AVG(salary) AS '平均薪水' FROM employee\G;

3.按照性别分组，统计出每个组中年龄最大值、最小值，薪水最大值，每个组中的人数，人名，以及平均薪水。
SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total_peo,AVG(salary)
AS avg_salary ,GROUP_CONCAT(username)FROM employee GROUP BY sex;

4.使用HAVING 对分组结果进行二次筛选
按照性别分组，并找到分组后组中人数大于3的组
SELECT id,sex,COUNT(*) AS total_peo FROM employee GROUP BY sex HAVING COUNT(*)>3;

5.子查询，查询所有拥有toyname为pig的人的姓名：
select username from user where user_id in (select user_id from toy where toyname='onepiece');

6.INTERSECT是交集. EXCEPT是差集的意思
返回这两个select查询相同的部分
select user_id from user intersect select user_id from toy;
就是返回第一个select 减去第二个select 结果的部分
select user_id from user except select user_id from toy;