记录操作

一、总览
1、插入数据insert
1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2 WHERE …;


2、更新数据update
语法：
    UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;
注：mysql库的user表存储的是用户的信息



3、删除数据delete
语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user WHERE password=’’;




4、查询数据select
select * from 表；

查询分单表查询和多表查询



二、单表查询语法
SELECT [DISTINCT] 字段1,字段2... FROM 表名
                     WHERE 条件
                     GROUP BY 字段
                     HAVING 筛选
                     ORDER BY 字段[ASC/DESC]
                     LIMIT 限制条数
                     
1、关键字执行的优先级
from
where
group by
having
select
distinct 
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.distinct去重

7.将结果按条件排序：order by

8.限制结果的显示条数



2、区别
DDL语句数据库定义语言：操作的是数据库、表的结构、视图、索引、存储过程
create:插入数据
alter:跟新数据
drop:删除数据
show/desc:查询数据

DML语句数据库操纵语言： 操作的是表的具体的记录
insert:插入数据
update:跟新数据
delete:删除数据
select:查询数据
                                                    
DCL语句数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE



3、简单查询

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name     | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+


#插入记录
#三个部门：IT，策划，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('小明','male',18,'20170301','外交',7300.33,401,1), #以下是IT部
('晓东','male',22,'20150302','IT',1000000.31,401,1),
('张三','male',81,'20130305','IT',8300,401,1),
('李四','male',73,'20140701','IT',3500,401,1),
('王铭','male',28,'20121101','IT',2100,401,1),
('小晶','female',18,'20110211','IT',9000,401,1),
('小红','male',18,'19000301','IT',30000,401,1),
('张一帆','male',48,'20101111','IT',10000,401,1),

('依依','female',48,'20150311','plan',3000.13,402,2),#以下是策划部门
('尔尔','female',38,'20101101','plan',2000.35,402,2),
('伞伞','female',18,'20110312','plan',1000.37,402,2),
('思思','female',18,'20160513','plan',3000.29,402,2),
('呜呜','female',28,'20170127','plan',4000.33,402,2),

('张三丰','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('朱一','male',18,'19970312','operation',20000,403,3),
('朱二','female',18,'20130311','operation',19000,403,3),
('朱三','male',18,'20150411','operation',18000,403,3),
('朱四','female',18,'20140512','operation',17000,403,3)
;

如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

建表和数据准备

查询：
#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT distinct post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;  # 查询一年的工资 表中显示的列名是emp_name, salary*12
    SELECT emp_name, salary*12 as year_salary FROM employee;  #as给salary*12起别名 表中显示的列名是emp_name, year_salary
    SELECT emp_name, salary*12 year_salary FROM employee;  # 或者可以不写as，空一格直接写别名 表中显示的列名是emp_name, year_salary

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS year_salary FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS year_salary FROM employee;

# CASE语句：when 条件 then 返回值 ... as 列名
   SELECT
       (
           CASE
           WHEN emp_name = '小明' THEN
               emp_name
           WHEN emp_name = '晓东' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;


例子：
1 查出所有员工的名字，薪资,格式为 <名字:小明>    <薪资:7300.33>
mysql> select concat('<名字：',emp_name,'>   < 薪资：',salary,'>') from employee;


2 查出所有的岗位（去掉重复）
mysql> select distinct post from employee;


3 查出所有员工名字，以及他们的年薪,年薪的字段名为yesr_salary
mysql> select emp_name,salary*12 as year_salary from employee;




4、where约束
1.where字句中可以使用：
(1) 比较运算符：> < >= <= <> !=
(2) between 80 and 100 值在80和100之间的数
(3) in(80,90,100) 值是80或90或100
(4) like 'abc%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符 
(5) 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not


2.示例
(1)单条件查询
SELECT emp_name FROM employee WHERE post='plan';
        
        
(2)多条件查询
SELECT emp_name,salary FROM employee WHERE post='IT' AND salary>10000;


(3)关键字BETWEEN AND
SELECT emp_name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;

SELECT emp_name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;
    
    
(4)关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL;

SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL;
        
SELECT emp_name,post_comment FROM employee WHERE post_comment='';    # 注意''是空字符串，不是null


(5)关键字IN集合查询
SELECT emp_name,salary FROM employee WHERE salary=1000 OR salary=2000 OR salary=3000 OR salary=4000 ;
    
SELECT emp_name,salary FROM employee WHERE salary IN (1000,2000,3000,4000) ;  # 这个查询跟上句查询是一样的结果

SELECT emp_name,salary FROM employee WHERE salary NOT IN (1000,2000,3000,4000) ;


(6)关键字LIKE模糊查询
通配符’%’ 可匹配任意长度的字符
SELECT * FROM employee WHERE emp_name LIKE '小%';

通配符’_’ 可匹配任意一个字符
SELECT * FROM employee WHERE emp_name LIKE '朱_';   #此查询有4条记录：朱一、朱二、朱三、朱四

SELECT * FROM employee WHERE emp_name LIKE '朱__';   #此查询无结果，因为有多少个'_'就得匹配多少个字符，少一个都不符合 



3.例题
(1) 查看岗位是IT的员工姓名、年龄
select emp_name,age from employee where post='IT';


(2) 查看岗位是IT且年龄大于30岁的员工姓名、年龄
select emp_name,age from employee where post='IT' and age>30;


(3) 查看岗位是IT且薪资在9000-10000范围内的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post='IT' and salary between 9000 and 10000;


(4) 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null;


(5) 查看岗位是IT且薪资是9000或10000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post='IT' and salary in (9000,10000,30000);


(6) 查看岗位是IT且薪资不是9000或10000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post='IT' and salary not in (9000,10000,30000);


(7) 查看岗位是IT且名字是'张'开头的员工姓名、年薪
select emp_name,salary*12 as year_salary from employee where post='IT' and emp_name like '张%';




5、group by 
(1)
单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

(2)
GROUP BY关键字和GROUP_CONCAT()函数一起使用
GROUP_CONCAT：把分组的内容拼成一列展示出来
    SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;  # 按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(emp_name,age) FROM employee GROUP BY post;  # 按照岗位分组，并查看组内成员名和年龄
    SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

(3)
GROUP BY与聚合函数一起使用
count：数分组的记录的数量
    SELECT post,COUNT(id) AS count FROM employee GROUP BY post;  # 按照岗位分组，并查看每个组有多少人


(4)group_concat对比concat
4-1.
GROUP_CONCAT()里面的参数只能用逗号隔开，不能完全自由拼接
SELECT post,GROUP_CONCAT(emp_name,age) FROM employee GROUP BY post; 
+-----------+-------------------------------------------------------------------+
| post      | GROUP_CONCAT(emp_name,age)                                        |
+-----------+-------------------------------------------------------------------+
| IT        | 小红18,晓东22,张三81,李四73,王铭28,小晶18,张一帆48                |
| operation | 朱一18,朱二18,朱三18,朱四18,张三丰28                              |
| plan      | 呜呜28,思思18,伞伞18,尔尔38,依依48                                |
| 外交      | 小明18                                                            |
+-----------+-------------------------------------------------------------------+


4-2.
CONCAT() 函数用于连接字符串，完全可以按照自己的想法来拼接字符串
SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS year_salary FROM employee;
+--------------------------------------+
| year_salary                          |
+--------------------------------------+
| 姓名: 小明  年薪: 87603.96           |
| 姓名: 晓东  年薪: 12000003.72        |
| 姓名: 张三  年薪: 99600.00           |
| 姓名: 李四  年薪: 42000.00           |
| 姓名: 王铭  年薪: 25200.00           |
| 姓名: 小晶  年薪: 108000.00          |
| 姓名: 小红  年薪: 360000.00          |
| 姓名: 张一帆  年薪: 120000.00        |
| 姓名: 依依  年薪: 36001.56           |
| 姓名: 尔尔  年薪: 24004.20           |
| 姓名: 伞伞  年薪: 12004.44           |
| 姓名: 思思  年薪: 36003.48           |
| 姓名: 呜呜  年薪: 48003.96           |
| 姓名: 张三丰  年薪: 120001.56        |
| 姓名: 朱一  年薪: 240000.00          |
| 姓名: 朱二  年薪: 228000.00          |
| 姓名: 朱三  年薪: 216000.00          |
| 姓名: 朱四  年薪: 204000.00          |
+--------------------------------------+

4-3.
CONCAT_WS() 第一个参数为分隔符，跟GROUP_CONCAT类似，不能完全自由拼接，但是可自定义拼接符号
SELECT CONCAT_WS(':',emp_name,salary*12) AS year_salary FROM employee;
+---------------------+
| year_salary         |
+---------------------+
| 小明:87603.96       |
| 晓东:12000003.72    |
| 张三:99600.00       |
| 李四:42000.00       |
| 王铭:25200.00       |
| 小晶:108000.00      |
| 小红:360000.00      |
| 张一帆:120000.00    |
| 依依:36001.56       |
| 尔尔:24004.20       |
| 伞伞:12004.44       |
| 思思:36003.48       |
| 呜呜:48003.96       |
| 张三丰:120001.56    |
| 朱一:240000.00      |
| 朱二:228000.00      |
| 朱三:216000.00      |
| 朱四:204000.00      |
+---------------------+



6、聚合函数
聚合函数聚合的是分组后每一组的内容，若是没有分组，则默认所有数据都是一组

函数：
    COUNT:数分组的记录的数量
    MAX:分组内最大的数值
    MIN:分组内最小的数值
    AVG:分组内的数值的平均数
    SUM:分组内的数值的和


示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

例子：
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(emp_name) as name from employee group by post;


2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) as count from employee group by post;


3. 查询公司内男员工和女员工的个数
select sex,count(id) as count from employee group by sex;


4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;


5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;


6. 查询岗位名以及各岗位的最低薪资
 select post,min(salary) from employee group by post;


7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;




7、HAVING过滤
(1)HAVING与WHERE不一样的地方在于：
    1.执行优先级从高到低：where > group by > having 
    2. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
    3. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
    
(2)例子：
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(emp_name) as name,count(id) as count from employee group by post having count(id)<2;


3. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;


4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;





8、ORDER BY 查询排序
(1)按单列排序
    SELECT * FROM employee ORDER BY salary;  # 默认是升序排序
    SELECT * FROM employee ORDER BY salary ASC;     # 升序排序(默认)
    SELECT * FROM employee ORDER BY salary DESC;   # 降序排序

(2)按多列排序:先按照age升序排序，如果年纪相同，则按照薪资降序排序
    SELECT * from employee ORDER BY age,salary DESC;


(3)例子：
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age,hire_date DESC;


2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary);


3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;




9、LIMIT 限制查询的记录数
用法：LIMIT n,m  ---> n是起始位置，m是包括起始记录在内，一共要查的记录数量
    #默认初始位置为0，即取索引0,1,2的记录 
    SELECT * FROM employee ORDER BY salary DESC LIMIT 3;    
    
    #从索引为0的记录(第一条记录)开始，往后再查4条记录(第五条记录)
    SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; 

    #从索引为5的记录(第六条记录)开始，往后再查4条记录(第十条记录)
    SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; 
    
    


10、使用正则表达式查询(也类似于模糊查询like)
1.MySQL中使用 REGEXP 操作符来进行正则表达式匹配
    SELECT * FROM employee WHERE emp_name REGEXP '^小';

    
2.示例：
SELECT * FROM employee WHERE emp_name REGEXP '^小';  # 匹配以'小'开头的人的信息

SELECT * FROM employee WHERE emp_name REGEXP '三$';  # 匹配以'三'结尾的人的信息

SELECT * FROM employee WHERE emp_name REGEXP 'a{2}';  # # 匹配有两个'a'的人的信息


3.小结：对字符串匹配的方式
WHERE emp_name = '小明';
WHERE emp_name LIKE '小%';
WHERE emp_name REGEXP '三$';


4.例子：
查看所有员工中名字是'小'开头的员工信息
select * from employee where emp_name regexp '^小';



二、多表查询
1、表department与表employee

#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('cat','male',18,200),
('dog','female',48,201),
('pig','male',38,201),
('bird','female',28,202),
('tiger','male',18,200),
('lion','female',18,204);

数据准备

2、连接语法
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;


2-1、交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | cat   | male   |   18 |    200 |  200 | 技术         |
|  1 | cat   | male   |   18 |    200 |  201 | 人力资源     |
|  1 | cat   | male   |   18 |    200 |  202 | 销售         |
|  1 | cat   | male   |   18 |    200 |  203 | 运营         |
|  2 | dog   | female |   48 |    201 |  200 | 技术         |
|  2 | dog   | female |   48 |    201 |  201 | 人力资源     |
|  2 | dog   | female |   48 |    201 |  202 | 销售         |
|  2 | dog   | female |   48 |    201 |  203 | 运营         |
|  3 | pig   | male   |   38 |    201 |  200 | 技术         |
|  3 | pig   | male   |   38 |    201 |  201 | 人力资源     |
|  3 | pig   | male   |   38 |    201 |  202 | 销售         |
|  3 | pig   | male   |   38 |    201 |  203 | 运营         |
|  4 | bird  | female |   28 |    202 |  200 | 技术         |
|  4 | bird  | female |   28 |    202 |  201 | 人力资源     |
|  4 | bird  | female |   28 |    202 |  202 | 销售         |
|  4 | bird  | female |   28 |    202 |  203 | 运营         |
|  5 | tiger | male   |   18 |    200 |  200 | 技术         |
|  5 | tiger | male   |   18 |    200 |  201 | 人力资源     |
|  5 | tiger | male   |   18 |    200 |  202 | 销售         |
|  5 | tiger | male   |   18 |    200 |  203 | 运营         |
|  6 | lion  | female |   18 |    204 |  200 | 技术         |
|  6 | lion  | female |   18 |    204 |  201 | 人力资源     |
|  6 | lion  | female |   18 |    204 |  202 | 销售         |
|  6 | lion  | female |   18 |    204 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)



2-2、内连接：只连接匹配的行
说明：
    找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
    department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-------+------+--------+--------------+
| id | name  | age  | sex    | name         |
+----+-------+------+--------+--------------+
|  1 | cat   |   18 | male   | 技术         |
|  2 | dog   |   48 | female | 人力资源     |
|  3 | pig   |   38 | male   | 人力资源     |
|  4 | bird  |   28 | female | 销售         |
|  5 | tiger |   18 | male   | 技术         |
+----+-------+------+--------+--------------+
5 rows in set (0.00 sec)


上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;



2-3、外链接之左连接：优先显示左表全部记录
说明：
    以左表为准，即找出所有员工信息，包括没有部门的员工
    本质就是：在内连接的基础上增加左表有而右表没有的结果
    
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+-------+--------------+
| id | name  | depart_name  |
+----+-------+--------------+
|  1 | cat   | 技术         |
|  5 | tiger | 技术         |
|  2 | dog   | 人力资源     |
|  3 | pig   | 人力资源     |
|  4 | bird  | 销售         |
|  6 | lion  | NULL         |
+----+-------+--------------+
6 rows in set (0.00 sec)



2-4、外链接之右连接：优先显示右表全部记录
说明：
    以右表为准，即找出所有部门信息，包括没有员工的部门
    本质就是：在内连接的基础上增加右表有而左表没有的结果
    
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-------+--------------+
| id   | name  | depart_name  |
+------+-------+--------------+
|    1 | cat   | 技术         |
|    2 | dog   | 人力资源     |
|    3 | pig   | 人力资源     |
|    4 | bird  | 销售         |
|    5 | tiger | 技术         |
| NULL | NULL  | 运营         |
+------+-------+--------------+
6 rows in set (0.00 sec)



2-5、全外连接：显示左右两个表全部记录
说明：
    在内连接的基础上增加左表有右表没有的和右表有左表没有的结果
    注意：mysql不支持全外连接 FULL JOIN
    但是：mysql可以使用UNION这种方式间接实现全外连接，需要注意的是union与union all的区别：union会去掉相同的纪录

select * from employee left join department on employee.dep_id=department.id 
union 
select * from employee right join department on employee.dep_id=department.id;

+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | cat   | male   |   18 |    200 |  200 | 技术         |
|    5 | tiger | male   |   18 |    200 |  200 | 技术         |
|    2 | dog   | female |   48 |    201 |  201 | 人力资源     |
|    3 | pig   | male   |   38 |    201 |  201 | 人力资源     |
|    4 | bird  | female |   28 |    202 |  202 | 销售         |
|    6 | lion  | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.02 sec)



3、示例
示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
mysql> select employee.name,employee.age,department.name as depart_name from employee inner join department on employee.dep_id=department.id where employee.age>25;

+------+------+--------------+
| name | age  | depart_name  |
+------+------+--------------+
| dog  |   48 | 人力资源     |
| pig  |   38 | 人力资源     |
| bird |   28 | 销售         |
+------+------+--------------+
3 rows in set (0.00 sec)


示例2：以内连接的方式查询employee和department表，且employee表中的age字段值必须大于25，以age字段的升序方式显示
# 若某字段在两个表中都是唯一的，那么可以直接使用而不需要在前面加表名，
# 若某字段在两个表中不是唯一的，那么使用时需要在前加表名区分，
# 例如employee表中有name，department表中也有name，使用哪个表的name就在name前加哪里表名，例如：employee.name
# 而employee表中的age,sex等字段是唯一的，即department表中没有这些字段，那么可以直接使用age,sex不需要加表名，当然加了也是可以的。

mysql> select employee.name,sex,age,department.name from employee inner join department on employee.dep_id=department.id where age>25 order by age;

+------+--------+------+--------------+
| name | sex    | age  | name         |
+------+--------+------+--------------+
| bird | female |   28 | 销售         |
| pig  | male   |   38 | 人力资源     |
| dog  | female |   48 | 人力资源     |
+------+--------+------+--------------+
3 rows in set (0.00 sec)



示例3：给表起别名
mysql> select a.name,sex,age,b.name from employee as a inner join department as b on a.dep_id=b.id where age>25 order by age asc;

+------+--------+------+--------------+
| name | sex    | age  | name         |
+------+--------+------+--------------+
| bird | female |   28 | 销售         |
| pig  | male   |   38 | 人力资源     |
| dog  | female |   48 | 人力资源     |
+------+--------+------+--------------+
3 rows in set (0.00 sec)



4、子查询
(1)子查询是将一个查询语句嵌套在另一个查询语句中。
(2)内层查询语句的查询结果，可以为外层查询语句提供查询条件。
(3)子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
(4)还可以包含比较运算符：= 、 !=、> 、<等



4-1、带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

+------+--------------+
| id   | name         |
+------+--------------+
|  201 | 人力资源     |
|  202 | 销售         |
+------+--------------+
2 rows in set (0.04 sec)


#查看技术部员工姓名
mysql> select name from employee where dep_id=(select id from department where name='技术');

+-------+
| name  |
+-------+
| cat   |
| tiger |
+-------+
2 rows in set (0.00 sec)



#查看不足1人的部门名(没有人的部门)
mysql> select name from department where id not in (select distinct dep_id from employee);
+--------+
| name   |
+--------+
| 运营   |
+--------+
1 row in set (0.00 sec)




4-2、带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>

#查询大于所有人平均年龄的员工名与年龄(where里面是不能使用聚合函数的)
mysql> select name,age from employee where age>(select avg(age) from employee);
+------+------+
| name | age  |
+------+------+
| dog  |   48 |
| pig  |   38 |
+------+------+
2 rows in set (0.00 sec)



#查询大于部门内平均年龄的员工名、年龄
mysql> select name,age from employee group by dep_id having age>avg(age);
+------+------+
| name | age  |
+------+------+
| dog  |   48 |
+------+------+
1 row in set (0.00 sec)



4-3、带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个bool值，True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=200，Ture
mysql> select * from employee where exists (select id from department where id=200);
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | cat   | male   |   18 |    200 |
|  2 | dog   | female |   48 |    201 |
|  3 | pig   | male   |   38 |    201 |
|  4 | bird  | female |   28 |    202 |
|  5 | tiger | male   |   18 |    200 |
|  6 | lion  | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)