数据准备
1.员工表(tb_emp)
CREATE TABLE `tb_emp` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
`first_name` varchar(20) DEFAULT NULL COMMENT '名',
`last_name` varchar(25) DEFAULT NULL COMMENT '性',
`email` varchar(25) DEFAULT NULL COMMENT '邮箱',
`phone_number` varchar(20) DEFAULT NULL COMMENT '电话号码',
`job_id` varchar(10) DEFAULT NULL COMMENT '工种id',
`salary` double(10,2) DEFAULT NULL COMMENT '工资',
`commission_pct` double(4,2) DEFAULT NULL COMMENT '奖金率',
`manager_id` int(6) DEFAULT NULL COMMENT '领导编号',
`department_id` int(4) DEFAULT NULL COMMENT '部门编号',
`hiredate` datetime DEFAULT NULL COMMENT '入职日期',
PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;
2.部门表(tb_emp)
CREATE TABLE `tb_dep` (
`department_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
`department_name` varchar(3) DEFAULT NULL COMMENT '部门名称',
`manager_id` int(6) DEFAULT NULL COMMENT '部门领导编号',
`location_id` int(4) DEFAULT NULL COMMENT '位置编号',
PRIMARY KEY (`department_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
3.工种表(tb_job)
CREATE TABLE `tb_job` (
`job_id` varchar(10) NOT NULL COMMENT '工种编号',
`job_title` varchar(35) DEFAULT NULL COMMENT '工种名称',
`min_salary` int(6) DEFAULT NULL COMMENT '最低工资',
`max_salary` int(6) DEFAULT NULL COMMENT '最高工资',
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
4.工资等级表
CREATE TABLE `tb_job_grade` (
`grade_level` varchar(3) DEFAULT NULL COMMENT '工资等级',
`lowest_sal` int(11) DEFAULT NULL COMMENT '等级的最小工资',
`highest_sal` int(11) DEFAULT NULL COMMENT '等级的最大工资'
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
1.等值内连接
1.1两张表的等值连接
需求:查询部门的员工个数大于5的部门名称和该部门的员工个数(coun),并按照员工个数按照降序取Top3.
mysql> SELECT department_name,count(1) coun
FROM tb_emp e
INNER JOIN
tb_dep d
ON e.department_id = d.department_id
GROUP BY department_name
HAVING coun>5
ORDER BY coun DESC
LIMIT 3;
+-----------------+------+
| department_name | coun |
+-----------------+------+
| Shi | 45 |
| Sal | 34 |
| Fin | 6 |
+-----------------+------+
3 rows in set
1.2.多表的等值连接
多张表的连接使用多个inner join 连接即可;
需求:查询员工名、部门名、工种名
,并且按照员工名称升序取Top5;
分析:
1.员工名、部门名、工种
名分写来自于tb_emp、tb_dep、tb_job
,那么需要将这三张表进行连接
2.找出连接条件
3.升序order by asc
,top5即limit 5
;
mysql> SELECT last_name,department_name,job_title
FROM tb_emp e
INNER JOIN tb_dep d
ON e.department_id=d.department_id
INNER JOIN tb_job j ON e.job_id=j.job_id
ORDER BY last_name ASC
LIMIT 5;
+-----------+-----------------+---------------------------------+
| last_name | department_name | job_title |
+-----------+-----------------+---------------------------------+
| Abel | Sal | Sales Representative |
| Ande | Sal | Sales Representative |
| Atkinson | Shi | Stock Clerk |
| Austin | IT | Programmer |
| Baer | Pub | Public Relations Representative |
+-----------+-----------------+---------------------------------+
5 rows in se
2.非等值内连接
需求:查询工资等级的个数(coun)大于30的等级名称和个数,并按照个数降序取TOP3
需求分析:
1.等级的个数、等级名称
:需要将tb_emp
和tb_job_grade
进行连接;
2.连接条件:根据工资等级表的各个等级的工资范围
判断各个员工的工资在哪一个等级;
3.等级的个数:需要进行分组
,并且分组的条件是等级名称;
4.个数降序:order by desc
;
5.取出top3:limit
3。
mysql> SELECT COUNT(1) coun ,grade_level
FROM tb_emp e
JOIN tb_job_grade g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING coun > 30
ORDER BY coun
DESC LIMIT 3;
+------+-------------+
| coun | grade_level |
+------+-------------+
| 38 | C |
+------+-------------+
1 row in set
3.自连接
需求:查询员工名、以及该员工对应的上级名称
需求分析:
1.员工名和上级名称对来自于tb_emp;
2.连接的条件:员工表1的上级id等于员工表2的员工id
mysql> SELECT e1.last_name ,e2.last_name
FROM tb_emp e1
JOIN tb_emp e2 ON e1.manager_id = e2.employee_id
limit 5;
+-----------+-----------+
| last_name | last_name |
+-----------+-----------+
| Kochhar | K_ing |
| De Haan | K_ing |
| Hunold | De Haan |
| Ernst | Hunold |
| Austin | Hunold |
+-----------+--
在sql92中自连接的写法如下:
mysql> SELECT e1.last_name ,e2.last_name
FROM tb_emp e1,tb_emp e2
WHERE e1.manager_id = e2.employee_id
limit 5;
+-----------+-----------+
| last_name | last_name |
+-----------+-----------+
| Kochhar | K_ing |
| De Haan | K_ing |
| Hunold | De Haan |
| Ernst | Hunold |
| Austin | Hunold |
+-----------+-----------+
5 rows in set
last edit date:2018-11-18
location:广州-海珠-西畔里