织梦网站模板后台密码找回,企业vi设计公司性价比高,xin主题wordpress,重庆seo整站优化报价接上一篇 连接查询
连接查询也中多表查询#xff0c;常用于查询来自于多张表的数据#xff0c;通过不同的连接方式把多张表组成一张新的临时表#xff0c;再对临时表做数据处理。
#表基础信息#xff0c;内容可从上一篇博客中查看
mysql desc departments;
---------…接上一篇 连接查询
连接查询也中多表查询常用于查询来自于多张表的数据通过不同的连接方式把多张表组成一张新的临时表再对临时表做数据处理。
#表基础信息内容可从上一篇博客中查看
mysql desc departments;
------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------------
| dept_id | int | NO | PRI | NULL | auto_increment |
| dept_name | varchar(10) | YES | | NULL | |
------------------------------------------------------------
2 rows in set (0.00 sec)mysql desc employees;
---------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------------
| employee_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| email | varchar(25) | YES | | NULL | |
| phone_number | char(11) | YES | | NULL | |
| dept_id | int | YES | MUL | NULL | |
---------------------------------------------------------------
7 rows in set (0.00 sec)mysql desc salary;
-------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------
| id | int | NO | PRI | NULL | auto_increment |
| date | date | YES | | NULL | |
| employee_id | int | YES | MUL | NULL | |
| basic | int | YES | | NULL | |
| bonus | int | YES | | NULL | |
-------------------------------------------------------
5 rows in set (0.00 sec)内连接
#1.等值连接查询
#1查询每个员工所在的部门名
mysql select emp.name,dep.dept_name from employees emp,departments dep where emp.dept_iddep.dept_id;
或
mysql select emp.name,dep.dept_name from employees emp join departments dep on emp.dept_iddep.dept_id;
#2) 查询员工编号8的员工所有部门的部门名称
mysql select emp.name,dep.dept_name from employees emp,departments dep where emp.dept_iddep.dept_id and emp.employee_id8;
或
mysql select emp.name,dep.dept_name from employees emp join departments dep on emp.dept_iddep.dept_id and emp.employee_id8;
#3查询每个员工所有信息及所有部门名称
mysql select emp.*,dep.dept_name from employees emp,departments dep where emp.dept_iddep.dept_id;
或
mysql select emp.*,dep.dept_name from employees emp join departments dep on emp.dept_iddep.dept_id;
#4查询每个员工姓名、部门编号、部门名称
mysql select emp.name,dep.dept_id,dep.dept_name from employees emp,departments dep where emp.dept_iddep.dept_id;
或
mysql select emp.name,dep.dept_id,dep.dept_name from employees emp join departments dep on emp.dept_iddep.dept_id;
#5查询11号员工的名字及2018年每个月总工资
mysql select emp.name,(sal.basicsal.bonus) as 总工资 from employees emp join salary sal on emp.employee_idsal.employee_id where emp.employee_id11 and year(date)2018;
#6) 查询每个员工2018年的总工资
mysql select emp.name,sum(sal.basicsal.bonus) as 总工资 from employees emp join salary sal on emp.employee_idsal.employee_id where year(date)2018 group by name;
#7) 查询每个员工2018年的总工资按总工资升序排列
mysql select emp.name,sum(sal.basicsal.bonus) as 总工资 from employees emp join salary sal on emp.employee_idsal.employee_id where year(date)2018 group by name order by 总工资;
#8) 查询2018年总工资大于30万的员工按2018年总工资降序排列
mysql select emp.name,sum(sal.basicsal.bonus) as 总工资 from employees emp join salary sal on emp.employee_idsal.employee_id where year(date)2018 group by name having 总工资300000 order by 总工资 desc;
#2.非等值连接查询
mysql create table wage_grade(id int primary key auto_increment,grade char(1),low int,high int);
Query OK, 0 rows affected (0.85 sec)mysql insert into wage_grade(grade,low,high)values(A,5000,8000),(B, 8001, 10000),(C, 10001, 15000),- (D, 15001, 20000),(E, 20001, 1000000);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
#1查询2018年12月员工基本工资级别
mysql select employee_id,date,basic,grade from salary as s inner join wage_grade as g on s.basic between g.low and g.high where year(date)2018 and month(date)12;
#2查询2018年12月员工各基本工资级别的人数
mysql select grade,count(grade) from salary as s inner join wage_grade as g on s.basic between g.low and g.high where year(date)2018 and month(date)12 group by grade;
#3查询2018年12月员工基本工资级别员工需要显示姓名
mysql select emp.name,date,basic,grade from salary as s inner join wage_grade as g inner join employees emp on s.basic between g.low and g.high where year(date)2018 and month(date)12 and emp.employee_ids.employee_id;外连接
mysql insert into departments(dept_name) values(小卖部),(行政部),(公关部);
#1. 左连接查询
#1输出没有员工的部门名
mysql select dept_name,emp.name from departments as dep left join employees emp on emp.dept_iddep.dept_id where emp.name is null;
#2. 右连接查询
mysql insert into employees(name) values (bob),(tom),(lily);
# 显示没有部门的员工名
mysql select emp.name,dep.dept_name from departments dep right join employees emp on emp.dept_iddep.dept_id where emp.dept_id is null;
#3. 全外连接查询
#1输出2018年基本工资的最大值和最小值
mysql (select basic from salary where year(date)2018 order by basic desc limit 1) union (select basic from salary where year(date)2018 order by basic limit 1);
#2输出2018年1月10号基本工资的最大值和最小值
mysql (select date , max(basic) as 工资 from salary where date20180110)union(select date,min(basic) from salary where date20180110);
#3union all 不去重显示查询结果
mysql (select employee_id , name , birth_date from employees where employee_id 5) union all (select employee_id , name , birth_date from employees where employee_id 6);
嵌套查询
嵌套查询是指在一个完整的查询语句之中包含若干个不同功能的小查询从而一起完成复杂查询的一种编写形式。包含的查询放在里 包含的查询出现的位置
SELECT之后FROM之后WHEREHAVING之后
#1. where之后嵌套查询
#1查询运维部所有员工信息
mysql select * from employees where dept_id (select dept_id from departments where dept_name运维部);
#2查询人事部2018年12月所有员工工资
mysql select * from salary where year(date)2018 and month(date)12 and employee_id in (select employee_id from employees where dept_id(select dept_id from departments where dept_name事部) );
#3查询人事部和财务部员工信息
mysql select dept_id , name from employees where dept_id in ( select dept_id from departments where dept_name in (人事部, 财务部) );
#4查询2018年12月所有比100号员工基本工资高的工资信息
mysql select * from salary where year(date)2018 and month(date)12 and basic(select basic from salary where year(date)2018 and month(date)12 and employee_id100);
#2. having之后嵌套查询
#查询部门员工总人数比开发部总人数少 的 部门名称和人数
mysql select dept_id ,count(name) as total from employees group by dept_id having total ( select count(name) from employees where dept_id( select dept_id from departments where dept_name开发部) );
#3. from之后嵌套查询
#查询3号部门 、部门名称 及其部门内 员工的编号、名字 和 email
mysql select dept_id, dept_name, employee_id, name, email from ( select d.dept_name, e.* from departments as d inner join employees as e on d.dept_ide.dept_id ) as tmp_table where dept_id3;
#4. select之后嵌套查询
#查询每个部门的人数 dept_id dept_name 部门人数
mysql select d.* , ( select count(name) from employees as e where d.dept_ide.dept_id) as 部门人数 from departments as d;
