Day 25 数据库查询

数据库查询

一：基本查询

1.简介

单表查询

简单查询

通过条件查询

查询排序

限制查询记录数

使用集合函数查询

分组查询

使用正则表达式查询

2.案例

创建案例所需表：company.employee5

	雇员编号	 id				        int
	雇员姓名	 name			       varchar(30)
	雇员性别	 sex				   enum
	雇用时期	 hire_date			   date
	职位		  post					varchar(50)
	职位描述	 job_description	    varchar(100)
	薪水		   salary				double(15,2)
	办公室		  office			    int
	部门编号	 dep_id				   int

MySQL [(none)]> CREATE TABLE company.employee5(
          id int primary key AUTO_INCREMENT not null,
          name varchar(30) not null,
          sex enum('male','female') default 'male' not null,
          hire_date date not null,
          post varchar(50) not null,
          job_description varchar(100),
          salary double(15,2) not null,
          office int,
          dep_id int
          );

插入模拟数据：

MySQL [(none)]> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
    ('jack','male','20180202','instructor','teach',5000,501,100),
    ('tom','male','20180203','instructor','teach',5500,501,100),
    ('robin','male','20180202','instructor','teach',8000,501,100),
    ('alice','female','20180202','instructor','teach',7200,501,100),
    ('','male','20180202','hr','hrcc',600,502,101),
    ('harry','male','20180202','hr',NULL,6000,502,101),
    ('emma','female','20180206','sale','salecc',20000,503,102),
    ('christine','female','20180205','sale','salecc',2200,503,102),
    ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
    ('gougou','male','20180205','sale','',2200,503,102);

语法格式：

select 字段名称,字段名称2… from 表名 [条件]

a.简单查询

MySQL [company]> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 |           | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

MySQL [company]> select name, salary, dep_id from employee5 where id <=5;
+-------+---------+--------+
| name  | salary  | dep_id |
+-------+---------+--------+
| jack  | 5000.00 |    100 |
| tom   | 5500.00 |    100 |
| robin | 8000.00 |    100 |
| alice | 7200.00 |    100 |
|       |  600.00 |    101 |
+-------+---------+--------+
5 rows in set (0.00 sec)

b.避免重复

不能部分使用DISTINCT，通常仅用于某一字段

MySQL [company]> SELECT post FROM employee5;
+------------+
| post       |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr         |
| hr         |
| sale       |
| sale       |
| sale       |
| sale       |
+------------+
10 rows in set (0.00 sec)

MySQL [company]> SELECT distinct post  FROM employee5;
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.00 sec)

c.四则运算查询

MySQL [company]> SELECT name, salary, salary*14 FROM employee5;
+-----------+----------+-----------+
| name      | salary   | salary*14 |
+-----------+----------+-----------+
| jack      |  5000.00 |  70000.00 |
| tom       |  5500.00 |  77000.00 |
| robin     |  8000.00 | 112000.00 |
| alice     |  7200.00 | 100800.00 |
|           |   600.00 |   8400.00 |
| harry     |  6000.00 |  84000.00 |
| emma      | 20000.00 | 280000.00 |
| christine |  2200.00 |  30800.00 |
| zhuzhu    |  2200.00 |  30800.00 |
| gougou    |  2200.00 |  30800.00 |
+-----------+----------+-----------+
10 rows in set (0.01 sec)

MySQL [company]> SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
+-----------+----------+---------------+
| name      | salary   | Annual_salary |
+-----------+----------+---------------+
| jack      |  5000.00 |      70000.00 |
| tom       |  5500.00 |      77000.00 |
| robin     |  8000.00 |     112000.00 |
| alice     |  7200.00 |     100800.00 |
|           |   600.00 |       8400.00 |
| harry     |  6000.00 |      84000.00 |
| emma      | 20000.00 |     280000.00 |
| christine |  2200.00 |      30800.00 |
| zhuzhu    |  2200.00 |      30800.00 |
| gougou    |  2200.00 |      30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)

MySQL [company]> SELECT name, salary, salary*14 Annual_salary FROM employee5;
+-----------+----------+---------------+
| name      | salary   | Annual_salary |
+-----------+----------+---------------+
| jack      |  5000.00 |      70000.00 |
| tom       |  5500.00 |      77000.00 |
| robin     |  8000.00 |     112000.00 |
| alice     |  7200.00 |     100800.00 |
|           |   600.00 |       8400.00 |
| harry     |  6000.00 |      84000.00 |
| emma      | 20000.00 |     280000.00 |
| christine |  2200.00 |      30800.00 |
| zhuzhu    |  2200.00 |      30800.00 |
| gougou    |  2200.00 |      30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)

d.定义显示格式

CONCAT() 函数用于连接字符串

MySQL [company]> SELECT concat(name, 's annual salary: ', salary*14)  AS Annual_salary FROM employee5;
+------------------------------------+
| Annual_salary                      |
+------------------------------------+
| jacks annual salary: 70000.00      |
| toms annual salary: 77000.00       |
| robins annual salary: 112000.00    |
| alices annual salary: 100800.00    |
| s annual salary: 8400.00           |
| harrys annual salary: 84000.00     |
| emmas annual salary: 280000.00     |
| christines annual salary: 30800.00 |
| zhuzhus annual salary: 30800.00    |
| gougous annual salary: 30800.00    |
+------------------------------------+
10 rows in set (0.00 sec)

e.单条件查询

MySQL [company]> SELECT name,post FROM employee5 WHERE post='hr';
+-------+------+
| name  | post |
+-------+------+
|       | hr   |
| harry | hr   |
+-------+------+
2 rows in set (0.00 sec)

f.多条件查询

MySQL [company]> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>10000;
Empty set (0.00 sec)

MySQL [company]> select * from employee5 where salary>5000 and salary<10000  or dep_id=102;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
8 rows in set (0.00 sec)

g.关键字

BETWEEN AND

MySQL [company]> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| tom   | 5500.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
5 rows in set (0.00 sec)

MySQL [company]> SELECT name,salary FROM employee5  WHERE salary NOT BETWEEN 5000 AND 15000;
+-----------+----------+
| name      | salary   |
+-----------+----------+
|           |   600.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
5 rows in set (0.00 sec)

IS NULL

MySQL [company]> SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| harry  | NULL            |
| zhuzhu | NULL            |
+--------+-----------------+
2 rows in set (0.00 sec)

MySQL [company]> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
+-----------+-----------------+
| name      | job_description |
+-----------+-----------------+
| jack      | teach           |
| tom       | teach           |
| robin     | teach           |
| alice     | teach           |
|           | hrcc            |
| emma      | salecc          |
| christine | salecc          |
| gougou    |                 |
+-----------+-----------------+
8 rows in set (0.00 sec)

MySQL [company]> SELECT name,job_description FROM employee5  WHERE job_description='';
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| gougou |                 |
+--------+-----------------+
1 row in set (0.00 sec)

注意：（NULL说明）

等价于没有任何值、是未知数

NULL与0、空字符串、空格都不同,NULL没有分配存储空间

对空值做加、减、乘、除等运算操作，结果仍为空

比较时使用关键字用“is null”和“is not null”

排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前

IN集合查询

MySQL [company]> SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

MySQL [company]> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

MySQL [company]> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| tom       |  5500.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
|           |   600.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
8 rows in set (0.01 sec)

h.模糊查询

关键字LIKE

通配符%：所有字符

通配符_：一个字符

MySQL [company]> SELECT * FROM employee5 WHERE name LIKE 'al%';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

MySQL [company]> SELECT * FROM employee5 WHERE name LIKE 'al___';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

i.排序查询

MySQL [company]> select name,salary from employee5 order by salary;
+-----------+----------+
| name      | salary   |
+-----------+----------+
|           |   600.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
| jack      |  5000.00 |
| tom       |  5500.00 |
| harry     |  6000.00 |
| alice     |  7200.00 |
| robin     |  8000.00 |
| emma      | 20000.00 |
+-----------+----------+
10 rows in set (0.01 sec)

MySQL [company]> select name,salary from employee5 order by salary desc; 
+-----------+----------+
| name      | salary   |
+-----------+----------+
| emma      | 20000.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
| harry     |  6000.00 |
| tom       |  5500.00 |
| jack      |  5000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
|           |   600.00 |
+-----------+----------+

MySQL [company]> select name,salary from employee5 order by salary desc limit 3;   //控制显示前3行
+-------+----------+
| name  | salary   |
+-------+----------+
| emma  | 20000.00 |
| robin |  8000.00 |
| alice |  7200.00 |
+-------+----------+
3 rows in set (0.00 sec)

MySQL [company]> select name,salary from employee5 order by salary desc limit 1,3;  //从序号1开始显示三行的内容
+-------+---------+
| name  | salary  |
+-------+---------+
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
3 rows in set (0.00 sec)

注意：

ascending 美音 /ə’sɛndɪŋ/ 升序

descending 美音 /dɪ’sɛndɪŋ/ 降序

j.集合函数查询

count：可以查看共有多少条记录

MySQL [company]> select count(*)  from employee5;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

MySQL [company]> select count(name) from employee5;
+-------------+
| count(name) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

max：查看最大值

MySQL [company]> select max(salary) from employee5;
+-------------+
| max(salary) |
+-------------+
|    20000.00 |
+-------------+
1 row in set (0.00 sec)

min：查看最小值

MySQL [company]> select min(salary) from employee5;
+-------------+
| min(salary) |
+-------------+
|      600.00 |
+-------------+
1 row in set (0.00 sec)

avg：查看平均值

MySQL [company]> select avg(salary) from employee5;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
1 row in set (0.00 sec)

sum：求和

sale这个部门的总工资

MySQL [company]> select concat("Total Department Wages：",sum(salary)) from employee5 where post='sale';
+-------------------------------------------------+
| concat("Total Department Wages：",sum(salary))  |
+-------------------------------------------------+
| Total Department Wages：26600.00                |
+-------------------------------------------------+
1 row in set (0.00 sec)

获取薪水最高的这个人的详细信息

MySQL [company]> select * from employee5 where salary = (select max(salary) from employee5);
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex    | hire_date  | post | job_description | salary   | office | dep_id |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
|  7 | emma | female | 2018-02-06 | sale | salecc          | 20000.00 |    503 |    102 |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
1 row in set (0.00 sec)

k.分组查询

GROUP BY和GROUP_CONCAT()函数一起使用

获取部门ID相同的员工并把名字拼接到一起

MySQL [company]> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
+--------+------------------------------+
| dep_id | GROUP_CONCAT(name)           |
+--------+------------------------------+
|    100 | jack,tom,robin,alice         |
|    101 | ,harry                       |
|    102 | emma,christine,zhuzhu,gougou |
+--------+------------------------------+
3 rows in set (0.01 sec)

GROUP BY和集合函数一起使用

获取部门最高薪资

MySQL [company]> SELECT post,max(salary) FROM employee5 GROUP BY post;
+------------+-------------+
| post       | max(salary) |
+------------+-------------+
| hr         |     6000.00 |
| instructor |     8000.00 |
| sale       |    20000.00 |
+------------+-------------+
3 rows in set (0.00 sec)

l.正则查询

以什么开头
MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP '^ali';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

以什么结尾
MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP 'ce$';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.01 sec)

连续出现n次
MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex    | hire_date  | post | job_description | salary   | office | dep_id |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
|  7 | emma | female | 2018-02-06 | sale | salecc          | 20000.00 |    503 |    102 |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
1 row in set (0.00 sec)

二：多表联合查询

【扩展了解】

1.数据准备

表：company.employee6

创建表：

MySQL [company]> create table employee6( 
     emp_id int auto_increment primary key not null, 
     emp_name varchar(50), 
     age int, 
     dept_id int);
Query OK, 0 rows affected (0.65 sec)

查看表结构：

MySQL [company]> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_name | varchar(50) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| dept_id  | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入模拟数据：

MySQL [company]> insert into employee6(emp_name,age,dept_id) values
     ('',19,200),
     ('tom',26,201),
     ('jack',30,201),
     ('alice',24,202),
     ('robin',40,200),
     ('xingdian',16,200),
     ('natasha',28,204);

查看数据：

MySQL [company]> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 |          |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | xingdian |   16 |     200 |
|      7 | natasha  |   28 |     204 |
+--------+----------+------+---------+
7 rows in set (0.00 sec)

表：company.department6

创建表：

MySQL [company]> create table department6(
     dept_id int,
     dept_name varchar(100)
     );
Query OK, 0 rows affected (0.33 sec)

查看表结构：

MySQL [company]> desc department6;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id   | int(11)      | YES  |     | NULL    |       |
| dept_name | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

模拟插入数据：

MySQL [company]> insert into department6 values
     (200,'hr'),
     (201,'it'),
     (202,'sale'),
     (203,'fd');

查看数据：

MySQL [company]> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
|     202 | sale      |
|     203 | fd        |
+---------+-----------+
4 rows in set (0.01 sec)

2.多表的连接查询

交叉连接：生成笛卡尔积，它不使用任何匹配条件；交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合

内连接：只连接匹配的行

外连接

左连接：会显示左边表内所有的值，不论在右边表内匹不匹配

右连接：会显示右边表内所有的值，不论在左边表内匹不匹配

全外连接：包含左、右两个表的全部行

交叉连接

MySQL [company]> select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
+----------+------+---------+-----------+
| emp_name | age  | dept_id | dept_name |
+----------+------+---------+-----------+
|          |   19 |     200 | hr        |
|          |   19 |     200 | it        |
|          |   19 |     200 | sale      |
|          |   19 |     200 | fd        |
| tom      |   26 |     201 | hr        |
| tom      |   26 |     201 | it        |
| tom      |   26 |     201 | sale      |
| tom      |   26 |     201 | fd        |
| jack     |   30 |     201 | hr        |
| jack     |   30 |     201 | it        |
| jack     |   30 |     201 | sale      |
| jack     |   30 |     201 | fd        |
| alice    |   24 |     202 | hr        |
| alice    |   24 |     202 | it        |
| alice    |   24 |     202 | sale      |
| alice    |   24 |     202 | fd        |
| robin    |   40 |     200 | hr        |
| robin    |   40 |     200 | it        |
| robin    |   40 |     200 | sale      |
| robin    |   40 |     200 | fd        |
| xingdian |   16 |     200 | hr        |
| xingdian |   16 |     200 | it        |
| xingdian |   16 |     200 | sale      |
| xingdian |   16 |     200 | fd        |
| natasha  |   28 |     204 | hr        |
| natasha  |   28 |     204 | it        |
| natasha  |   28 |     204 | sale      |
| natasha  |   28 |     204 | fd        |
+----------+------+---------+-----------+
28 rows in set (0.00 sec)

内连接

获取有部门的员工 (部门表中没有natasha所在的部门)

MySQL [company]> select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6 where employee6.dept_id=department6.dept_id;
+----------+------+---------+-----------+
| emp_name | age  | dept_id | dept_name |
+----------+------+---------+-----------+
|          |   19 |     200 | hr        |
| tom      |   26 |     201 | it        |
| jack     |   30 |     201 | it        |
| alice    |   24 |     202 | sale      |
| robin    |   40 |     200 | hr        |
| xingdian |   16 |     200 | hr        |
+----------+------+---------+-----------+
6 rows in set (0.00 sec)

MySQL [company]> select employee6.emp_name,department6.dept_name from employee6 inner join department6 on employee6.dept_id=department6.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
|          | hr        |
| tom      | it        |
| jack     | it        |
| alice    | sale      |
| robin    | hr        |
| xingdian | hr        |
+----------+-----------+
6 rows in set (0.01 sec)

外连接

语法：

SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

注意：

先用谁谁就是左

左连接 left join

找出所有员工及所属的部门，包括没有部门的员工
MySQL [company]> select emp_id,emp_name,dept_name from  employee6 left join department6 on employee6.dept_id = department6.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 |          | hr        |
|      5 | robin    | hr        |
|      6 | xingdian | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      7 | natasha  | NULL      |
+--------+----------+-----------+
7 rows in set (0.00 sec)

右连接right join

找出所有部门包含的员工，包括空部门
MySQL [company]> select emp_id,emp_name,dept_name from  employee6 right join department6 on employee6.dept_id = department6.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 |          | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      5 | robin    | hr        |
|      6 | xingdian | hr        |
|   NULL | NULL     | fd        |
+--------+----------+-----------+
7 rows in set (0.00 sec)

全外连接

MySQL [company]> select * from employee6 full join department6;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 |          |   19 |     200 |     200 | hr        |
|      1 |          |   19 |     200 |     201 | it        |
|      1 |          |   19 |     200 |     202 | sale      |
|      1 |          |   19 |     200 |     203 | fd        |
|      2 | tom      |   26 |     201 |     200 | hr        |
|      2 | tom      |   26 |     201 |     201 | it        |
|      2 | tom      |   26 |     201 |     202 | sale      |
|      2 | tom      |   26 |     201 |     203 | fd        |
|      3 | jack     |   30 |     201 |     200 | hr        |
|      3 | jack     |   30 |     201 |     201 | it        |
|      3 | jack     |   30 |     201 |     202 | sale      |
|      3 | jack     |   30 |     201 |     203 | fd        |
|      4 | alice    |   24 |     202 |     200 | hr        |
|      4 | alice    |   24 |     202 |     201 | it        |
|      4 | alice    |   24 |     202 |     202 | sale      |
|      4 | alice    |   24 |     202 |     203 | fd        |
|      5 | robin    |   40 |     200 |     200 | hr        |
|      5 | robin    |   40 |     200 |     201 | it        |
|      5 | robin    |   40 |     200 |     202 | sale      |
|      5 | robin    |   40 |     200 |     203 | fd        |
|      6 | xingdian |   16 |     200 |     200 | hr        |
|      6 | xingdian |   16 |     200 |     201 | it        |
|      6 | xingdian |   16 |     200 |     202 | sale      |
|      6 | xingdian |   16 |     200 |     203 | fd        |
|      7 | natasha  |   28 |     204 |     200 | hr        |
|      7 | natasha  |   28 |     204 |     201 | it        |
|      7 | natasha  |   28 |     204 |     202 | sale      |
|      7 | natasha  |   28 |     204 |     203 | fd        |
+--------+----------+------+---------+---------+-----------+
28 rows in set (0.00 sec)

3.复合条件连接查询

案例一

找出公司所有部门中年龄大于25岁的员工

以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25

MySQL [company]> select emp_id,emp_name,dept_name FROM employee6,department6 WHERE employee6.dept_id = department6.dept_id AND age > 25;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      5 | robin    | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
+--------+----------+-----------+
3 rows in set (0.01 sec)

案例二

以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示

MySQL [company]> select emp_id,emp_name,dept_name FROM employee6,department6 WHERE employee6.dept_id = department6.dept_id ORDER BY age asc;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      6 | xingdian | hr        |
|      1 |          | hr        |
|      4 | alice    | sale      |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      5 | robin    | hr        |
+--------+----------+-----------+

4.子查询

子查询是将一个查询语句嵌套在另一个查询语句中

内层查询语句的查询结果，可以为外层查询语句提供查询条件

子查询中可以包含：IN、NOT IN等关键字；还可以包含比较运算符：= 、 !=、> 、<等

案例一

带IN关键字的子查询；查询employee表，但dept_id必须在department表中出现过

MySQL [company]> select * from employee6 WHERE dept_id IN (select dept_id FROM department6);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 |          |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | xingdian |   16 |     200 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)

案例二

带比较运算符的子查询；查询年龄大于等于25岁员工所在部门（查询老龄化的部门）

MySQL [company]> select dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERE age >=25);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     201 | it        |
|     200 | hr        |
+---------+-----------+
2 rows in set (0.00 sec)