MySQL—多表查询综合练习

本文介绍: 创建dept表deptno INT(2) NOT NULL COMMENT ‘部门编号’,dname VARCHAR (15) COMMENT ‘部门名称’,loc VARCHAR (20) COMMENT ‘地理位置’添加dept表主键。

创建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> select empno 编号,ename 姓名, job 职位,dname 部门名称,loc 位置

from emp inner join dept on emp.deptno=dept.deptno;

2.要求查询出每一位雇员的姓名、职位、领导的姓名

#在MySQL中，可以使用自连接来查询同一张表中的数据。自连接是指一个表与自身进行连接。在进行自连接时，需要为表定义别名。这样可以更方便地在查询中引用表的不同行。

SQL语句分析：

#这个查询也是从员工表（emp）中选取每个员工的姓名、职位和他们的经理的姓名。但是连接条件是员工的经理编号（mgr）等于他们自己的员工编号（empno）。这个查询返回的结果集包含所有员工，对于没有经理的员工，经理的姓名字段将为NULL。

mysql> select e.ename 姓名,e.job 职位,b.ename from emp e left join emp b on e.mgr=b.empno;

3.查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。

mysql> select e.empno 编号,e.ename 姓名,e.sal 基本工资,e.job 职位,b.ename 领导的名,dname 部门名称,loc 位置 from emp e

> left join emp b on e.mgr=b.empno

> inner join dept on e.deptno=dept.deptno;

4.列出在部门”销售部”工作的员工的姓名，假定不知道销售部的部门编号。

mysql> select ename 姓名,dname 销售部 from emp e inner join dept d on

e.deptno=d.deptno where dname=’销售部’;

5.列出与”李兴”从事相同工作的所有员工。

#(select job from emp where ename=’李兴’)：这部分是子查询，它的作用是从员工表（emp）中选取名为”李兴”的员工的职位。

mysql> select ename 姓名 from emp where job=(select job from em

p where ename=’李兴’);

6.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

mysql> select ename 姓名,sal 薪金 from emp where sal>(select max(sal) from emp where deptno=30);

注意：

mysql> select ename 姓名,sal 薪金 from emp where sal > (select sal from emp where deptno=30);

ERROR 1242 (21000): Subquery returns more than 1 row

错误原因在于子查询 (select sal from emp where deptno=30) 可能会返回多行数据，如果部门号为30的员工有多人，那么这个子查询就会返回多个薪金值，这将导致主查询无法正常运行。