MySQL---多表分组查询综合练习

创建dept表

CREATE TABLE dept (

deptno INT(2) NOT NULL COMMENT '部门编号',

dname VARCHAR (15) COMMENT '部门名称',

loc VARCHAR (20) COMMENT '地理位置'

);

添加dept表主键

mysql> alter table dept add primary key(deptno);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

dept表插入数据

INSERT INTO dept (deptno,dname,loc)VALUES (10,'财务部','高新四路');

INSERT INTO dept (deptno,dname,loc)VALUES (20,'人事部','科技二路');

INSERT INTO dept (deptno,dname,loc)VALUES (30,'销售部','长安区');

INSERT INTO dept (deptno,dname,loc)VALUES (40,'运输部','雁塔区');

查询dept表内容

mysql> select * from dept;

创建emp表

CREATE TABLE emp(

empno INT(4) NOT NULL COMMENT '员工编号',

ename VARCHAR(10) COMMENT '员工名字',

job VARCHAR(10) COMMENT '职位',

mgr INT(4) COMMENT '上司',

hiredate DATE COMMENT '入职时间',

sal INT(7) COMMENT '基本工资',

comm INT(7) COMMENT '补贴',

deptno INT(2) COMMENT '所属部门编号'

);

emp表添加主键

mysql> alter table emp add primary key(empno);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

emp表添加外键约束

在MySQL中，可以使用alter table 语句来添加外键约束。以下是基本的语法：

ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES other_table(other_column);
在这个语句中：

table_name是你想要添加外键的表的名称。
column_name是你想要设置为外键的列的名称。
other_table是包含外键引用的表的名称。
other_column是在other_table中的列的名称。

ALTER TABLE emp ADD CONSTRAINT f_ed_key FOREIGN KEY (deptno) REFERENCES dept(deptno);

emp表中插入数据

INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');

INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');

INSERT INTO `emp` VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');

INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');

INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');

INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');

INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');

INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');

INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');

INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');

INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');

INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');

INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');

INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');

查询emp表的内容

mysql> select * from emp;

1.按照部门编号分组，求出每个部的人数，平均工资

#在 MySQL 中，group by 语句用于根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 count , sum , avg 等函数。

注意：

当在查询中使用 group by 语句时，只能在 select 语句中包含分组列或使用聚合函数的列。否则，将会报错。

mysql> select deptno 部门号,count(*) 部门的人数,avg(sal) 平均工资 from emp group by deptno;

2.按照职位分组，求出每个职位的最高和最低工资

mysql> select job 职位,max(sal) 最高工资,min(sal) 最低工资 from emp group by job;

3.计算出每个各职位的平均工资

mysql> select job 职位,avg(sal) from emp group by job;

4.查询出每个部门的名称、部门的人数、平均工资

# 在MySQL中，inner join 是一个非常重要的操作，它允许我们将两个或多个表中匹配的行连接在一起。

# inner join on 语法使用 on 关键字来指定连接条件。当我们需要连接两个表时，需要指定一个列或表达式作为连接条件。

mysql> select dname 部门名称,count(*) 部门人数,avg(sal) 平均工资 from emp e

inner join dept d on e.deptno=d.deptno

group by dname;

5.要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000；

mysql> select d.deptno 部门编号,d.dname 部门名称,loc 位置,count(*) 人数 ,avg(sal) 平均工资 from emp e

inner join dept d on e.deptno=d.deptno

group by dname,d.deptno,loc having avg(sal)>2000;

6.要求查询出工资比华仔还要高的全部雇员信息

SQL语句分析：先找到“华仔”的工资，然后再利用子查询

mysql> select sal from emp where ename='华仔';

子查询：

# 在WHERE子句中使用子查询，可以使用比较运算符，例如=，>，<等将子查询返回的单个值与WHERE子句中的表达式进行比较。

注意：

子查询可以在任何使用表达式的地方使用，并且必须在括号中关闭。

mysql> select * from emp where sal > (select sal from emp where ename='华仔');

7.要求查询出高于公司平均工资的全部雇员信息

将题目信息进行分解，先找到平均工资

mysql> select avg(sal) from emp;

利用子查询，找到高于公司平均工资的全部雇员信息

mysql> select * from emp where sal>(select avg(sal) from emp);

8.查询出每个部门的编号、名称、位置、部门人数、平均工资

#在MySQL中，left outer join 是一种连接查询，它从左表返回所有的行，即使在右表中没有匹配的行。如果在右表中没有匹配的行，结果是NULL。

mysql> select d.deptno 部门编号,dname 名称,loc 位置,count(*) 部门人数,avg(sal) from dept d left outer join emp e on d.deptno=e.deptno

group by d.deptno,d.dname,d.loc;