mysql> select e. 学历, xs.性别, count(xs.性别) AS 人数
-> from employees e
-> join xs on e.姓名 = xs.姓名
-> group by e.学历, xs.性别
-> with rollup;
+------+------+------+
| 学历 | 性别 | 人数 |
+------+------+------+
| 大专 | 1 | 2 |
| 大专 | NULL | 2 |
| NULL | NULL | 2 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> select left (电话, 1) as 电话首位,
-> count(*) AS 人数
-> from employees
-> group by 电话首位;
+----------+------+
| 电话首位 | 人数 |
+----------+------+
| 3 | 2 |
| 5 | 2 |
| 6 | 1 |
| 8 | 8 |
+----------+------+
4 rows in set (0.00 sec)
mysql> select d.部门名称, avg(s.收入) AS 平均收入
-> from departments d
-> join employees e on d.部门号 = e.部门号
-> join (
-> select s.编号, s.收入
-> from salary s
-> join employees e on s.编号 = e.编号
-> ) s on e.编号 = s.编号
-> group by d.部门名称;
+------------+-------------------+
| 部门名称 | 平均收入 |
+------------+-------------------+
| 人力资源部 | 2100.800048828125 |
| 市场部 | 2004.296630859375 |
| 研发部 | 2526.276611328125 |
| 经理办公室 | 2749.989990234375 |
| 财务部 | 2147.574981689453 |
+------------+-------------------+
5 rows in set (0.00 sec)
mysql> SELECT MONTH(出生日期) AS 出生月, COUNT(*) AS 人数
-> FROM xs
-> GROUP BY 出生月
-> ;
+--------+------+
| 出生月 | 人数 |
+--------+------+
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
| 5 | 1 |
| 6 | 1 |
| 8 | 2 |
| 9 | 1 |
| 10 | 2 |
+--------+------+
8 rows in set (0.00 sec)
mysql> select xs.学号,xs.姓名,group_concat(kc.课程名 separator ' ') as 课程名,group_concat(xs_kc.成绩 separator ' ') as 成绩
-> from xs
-> join xs_kc on xs.学号=xs_kc.学号
-> join kc on xs_kc.课程号=kc.课程号
-> where xs.学号 in (
-> select 学号
-> from xs_kc
-> group by 学号
-> having count(distinct 课程号)>1
-> )
-> and xs.姓名 not in (
-> select 姓名
-> from xs
-> group by 姓名
-> having count(*)>1
-> )
-> and xs_kc.成绩 between 60 and 69
-> group by xs.学号,xs.姓名
-> ;
+--------+--------+---------------------+-------+
| 学号 | 姓名 | 课程名 | 成绩 |
+--------+--------+---------------------+-------+
| 081203 | 严红 | 计算机网络 软件工程 | 68 60 |
| 081303 | 孙祥欣 | 计算机基础 | 63 |
| 081304 | 孙研 | 计算机基础 | 65 |
+--------+--------+---------------------+-------+
3 rows in set (0.00 sec)
mysql> select 学号,姓名,课程名,成绩
-> from xs_kc
-> join kc using(课程号)
-> join xs using(学号)
-> where 姓名 in (
-> select 姓名
-> from xs_kc
-> join xs using(学号)
-> where exists(select 1 from xs_kc as sub_xs_kc where sub_xs_kc.学号=xs.学号 and 成绩 between 60 and 69)
-> group by 姓名
-> having count(课程号) > 1
-> ) order by xs.学号,kc.课程名;
+--------+--------+----------------+------+
| 学号 | 姓名 | 课程名 | 成绩 |
+--------+--------+----------------+------+
| 081203 | 严红 | 数据结构 | 88 |
| 081203 | 严红 | 计算机基础 | 85 |
| 081203 | 严红 | 计算机网络 | 68 |
| 081203 | 严红 | 软件工程 | 60 |
| 081303 | 孙祥欣 | 操作系统 | 81 |
| 081303 | 孙祥欣 | 计算机基础 | 63 |
| 081304 | 孙研 | 操作系统 | 76 |
| 081304 | 孙研 | 程序设计与语言 | 84 |
| 081304 | 孙研 | 计算机基础 | 65 |
+--------+--------+----------------+------+