员工employee表(id,name,age,所属部门id)
部门 department表(部门id,部门名称)。
1.
创建employee表:
create table employee(
id int(4),
name varchar(20),
age int(3),
depId int(2))
;
插入数据:
mysql> insert into employee values(1,"first",25,1);
Query OK, 1 row affected (0.17 sec)
mysql> insert into employee values(2,"second",25,1);
Query OK, 1 row affected (0.08 sec)
mysql> insert into employee values(3,"third",25,1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee values(4,"4th",25,2);
Query OK, 1 row affected (0.06 sec)
mysql> insert into employee values(5,"5th",25,2);
Query OK, 1 row affected (0.09 sec)
mysql> insert into employee values(6,"5th",25,3);
Query OK, 1 row affected (0.07 sec)
2.
创建department表:
mysql> create table department(depId int(2),depName varchar(20));
Query OK, 0 rows affected (0.38 sec)
插入数据:
mysql> insert into department values(1,"R$D");
Query OK, 1 row affected (0.10 sec)
mysql> insert into department values(2,"QA");
Query OK, 1 row affected (0.03 sec)
mysql> insert into department values(3,"HR");
Query OK, 1 row affected (0.04 sec)
3.
查询人数最多的部门名字:
mysql> select count(e.id) as dep_emp_count, e.depId,d.depName from employee e,d
partment d where e.depId = d.depId group by e.depId ;
+---------------+-------+---------+
| dep_emp_count | depId | depName |
+---------------+-------+---------+
| 3 | 1 | R$D |
| 2 | 2 | QA |
| 1 | 3 | HR |
+---------------+-------+---------+
3 rows in set (0.00 sec)
选定第一行 用limit:
mysql> select count(e.id) as dep_emp_count, e.depId,d.depName from employee e,de
partment d where e.depId = d.depId group by e.depId limit 1;
+---------------+-------+---------+
| dep_emp_count | depId | depName |
+---------------+-------+---------+
| 3 | 1 | R$D |
+---------------+-------+---------+
1 row in set (0.00 sec)