当前位置:   article > 正文

Linux学习之MySQL连接查询_linux查询mysql连接

linux查询mysql连接

接上一篇

MySQL执行顺序

连接查询

连接查询也中多表查询,常用于查询来自于多张表的数据,通过不同的连接方式把多张表组成一张新的临时表,再对临时表做数据处理。

#表基础信息,内容可从上一篇博客中查看
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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
内连接
#1.等值连接查询
#1)查询每个员工所在的部门名
mysql> select emp.name,dep.dept_name from employees emp,departments dep where emp.dept_id=dep.dept_id;
或
mysql> select emp.name,dep.dept_name from employees emp join departments dep on emp.dept_id=dep.dept_id;
#2) 查询员工编号8的员工所有部门的部门名称
mysql> select emp.name,dep.dept_name from employees emp,departments dep where emp.dept_id=dep.dept_id and emp.employee_id=8;
或
mysql> select emp.name,dep.dept_name from employees emp join departments dep on emp.dept_id=dep.dept_id and emp.employee_id=8;
#3)查询每个员工所有信息及所有部门名称
mysql> select emp.*,dep.dept_name from employees emp,departments dep where emp.dept_id=dep.dept_id;
或
mysql> select emp.*,dep.dept_name from employees emp join departments dep on emp.dept_id=dep.dept_id;
#4)查询每个员工姓名、部门编号、部门名称
mysql> select emp.name,dep.dept_id,dep.dept_name from employees emp,departments dep where emp.dept_id=dep.dept_id;
或
mysql> select emp.name,dep.dept_id,dep.dept_name from employees emp join departments dep on emp.dept_id=dep.dept_id;
#5)查询11号员工的名字及2018年每个月总工资
mysql> select emp.name,(sal.basic+sal.bonus) as 总工资 from employees emp join salary sal  on emp.employee_id=sal.employee_id where emp.employee_id=11 and year(date)=2018;
#6) 查询每个员工2018年的总工资
mysql> select emp.name,sum(sal.basic+sal.bonus) as 总工资 from employees emp join salary sal on emp.employee_id=sal.employee_id where year(date)=2018 group by name;
#7) 查询每个员工2018年的总工资,按总工资升序排列
mysql> select emp.name,sum(sal.basic+sal.bonus) as 总工资 from employees emp join salary sal on emp.employee_id=sal.employee_id where year(date)=2018 group by name order by 总工资;
#8) 查询2018年总工资大于30万的员工,按2018年总工资降序排列
mysql> select emp.name,sum(sal.basic+sal.bonus) as 总工资 from employees emp join salary sal on emp.employee_id=sal.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_id=s.employee_id;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
外连接
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_id=dep.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_id=dep.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 date=20180110)union(select date,min(basic) from salary where date=20180110);
#3)union 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);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
嵌套查询

嵌套查询:是指在一个完整的查询语句之中,包含若干个不同功能的小查询;从而一起完成复杂查询的一种编写形式。包含的查询放在()里 , 包含的查询出现的位置:

  • SELECT之后
  • FROM之后
  • WHERE
  • HAVING之后
#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_id=100);
#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_id=e.dept_id ) as tmp_table  where dept_id=3;
#4. select之后嵌套查询
#查询每个部门的人数: dept_id dept_name 部门人数
mysql> select  d.* , ( select count(name) from employees as e  where d.dept_id=e.dept_id) as 部门人数  from departments as d;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/191995
推荐阅读
相关标签
  

闽ICP备14008679号