【MySQL】多表查询

2022-03-15 20:39:00

【MySQL】

文章目录

【MySQL】

实际开发中往往数据来自不同的表，所以需要多表查询。今天我们用一个简单的公司管理系统，有三张表 emp, dept, salgrade 来演示如何进行多表查询。

一、前期准备

1.1 导入数据库

MariaDB [(none)]> source scott_data.sql

数据库下载：scott_data.sql 提取码：vrps

数据库部分代码

DROP database  IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '员工编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '员工职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '员工领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '入职时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪水',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '薪水级别',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低薪水',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高薪水'
);

1.2 查看数据库内容

MariaDB [scott]> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)

可以看到该数据库有三张表dept、emp以及salgrade，它们的表结构是这样的。

MariaDB [scott]> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
| dname  | varchar(14)              | YES  |     | NULL    |       |
| loc    | varchar(13)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [scott]> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

MariaDB [scott]> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES  |     | NULL    |       |
| losal | int(11) | YES  |     | NULL    |       |
| hisal | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

三张表的结构如下图所示。

二、多表查询

2.1 显示员工姓名、员工薪水以及所在的部门名称

观察可知员工姓名和薪水是来自emp表，部门名称是来自dept表，因此单表查询对于实际开发是远远不够的，此时就需要用到多表查询。

select * from emp;

select * from dept;

select * from  emp,dept;

那么显示员工姓名、员工薪水以及所在的部门名称，只需要让emp.deptno=dept.deptno

select emp.ename, emp.sal, dept.dname from emp,dept where emp.deptno=dept.deptno;

2.2 显示部门编号为10的部门名称，员工姓名和薪水

select dept.dname, emp.ename, emp.sal from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;

2.3 显示各个员工的姓名，薪水和工资级别

select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal;

码农公寓