create table emp(
    id int not null unique auto_increment,
    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
);    

#插入数据
#三个部门：教学部，销售部，运营部

insert into 
emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象',7300.33,401,1),
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),    #以下是销售部门
('呵呵','female',48,'20101101','sale',2000.35,402,2),
('西西','female',38,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('啦啦','female',18,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3),    #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',18000,403,3),
('程咬铜','male',18,'20150411','operation',19000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

mysql> select * from emp;
+----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post                  | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+
|  1 | jason     | male   |  18 | 2017-03-01 | 张江第一帅形象        | NULL         |    7300.33 |    401 |         1 |
|  2 | tom       | male   |  78 | 2015-03-02 | teacher               | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin     | male   |  81 | 2013-03-05 | teacher               | NULL         |    8300.00 |    401 |         1 |
|  4 | tony      | male   |  73 | 2014-07-01 | teacher               | NULL         |    3500.00 |    401 |         1 |
|  5 | owen      | male   |  28 | 2012-11-01 | teacher               | NULL         |    2100.00 |    401 |         1 |
|  6 | jack      | female |  18 | 2011-02-11 | teacher               | NULL         |    9000.00 |    401 |         1 |
|  7 | jenny     | male   |  18 | 1900-03-01 | teacher               | NULL         |   30000.00 |    401 |         1 |
|  8 | sank      | male   |  48 | 2010-11-11 | teacher               | NULL         |   10000.00 |    401 |         1 |
|  9 | 哈哈      | female |  48 | 2015-03-11 | sale                  | NULL         |    3000.13 |    402 |         2 |
| 10 | 呵呵      | female |  48 | 2010-11-01 | sale                  | NULL         |    2000.35 |    402 |         2 |
| 11 | 西西      | female |  38 | 2011-03-12 | sale                  | NULL         |    1000.37 |    402 |         2 |
| 12 | 乐乐      | female |  18 | 2016-05-13 | sale                  | NULL         |    3000.29 |    402 |         2 |
| 13 | 啦啦      | female |  18 | 2017-01-27 | sale                  | NULL         |    4000.33 |    402 |         2 |
| 14 | 僧龙      | male   |  28 | 2016-03-11 | operation             | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation             | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation             | NULL         |   18000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation             | NULL         |   19000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation             | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

当表的字段很多的时候，命令窗口不够宽，感觉数据错乱，怎么办？？？
只需要在select * from emp后面加上 \G
即：select * from emp \G;

个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象
你可以将字符编码统一设置成gbk

select * from emp \G;

mysql> select * from emp \G;
*************************** 1. row ***************************
          id: 1
        name: jason
         sex: male
         age: 18
   hire_date: 2017-03-01
        post: 张江第一帅形象
post_comment: NULL
      salary: 7300.33
      office: 401
   depart_id: 1
*************************** 2. row ***************************
          id: 2
        name: tom
         sex: male
         age: 78
   hire_date: 2015-03-02
        post: teacher
post_comment: NULL
      salary: 1000000.31
      office: 401
   depart_id: 1
*************************** 3. row ***************************
          id: 3
        name: kevin
         sex: male
         age: 81
   hire_date: 2013-03-05
        post: teacher
post_comment: NULL
      salary: 8300.00
      office: 401
   depart_id: 1
*************************** 4. row ***************************
          id: 4
        name: tony
         sex: male
         age: 73
   hire_date: 2014-07-01
        post: teacher
post_comment: NULL
      salary: 3500.00
      office: 401
   depart_id: 1
*************************** 5. row ***************************
          id: 5
        name: owen
         sex: male
         age: 28
   hire_date: 2012-11-01
        post: teacher
post_comment: NULL
      salary: 2100.00
      office: 401
   depart_id: 1
*************************** 6. row ***************************
          id: 6
        name: jack
         sex: female
         age: 18
   hire_date: 2011-02-11
        post: teacher
post_comment: NULL
      salary: 9000.00
      office: 401
   depart_id: 1
*************************** 7. row ***************************
          id: 7
        name: jenny
         sex: male
         age: 18
   hire_date: 1900-03-01
        post: teacher
post_comment: NULL
      salary: 30000.00
      office: 401
   depart_id: 1
*************************** 8. row ***************************
          id: 8
        name: sank
         sex: male
         age: 48
   hire_date: 2010-11-11
        post: teacher
post_comment: NULL
      salary: 10000.00
      office: 401
   depart_id: 1
*************************** 9. row ***************************
          id: 9
        name: 哈哈
         sex: female
         age: 48
   hire_date: 2015-03-11
        post: sale
post_comment: NULL
      salary: 3000.13
      office: 402
   depart_id: 2
*************************** 10. row ***************************
          id: 10
        name: 呵呵
         sex: female
         age: 48
   hire_date: 2010-11-01
        post: sale
post_comment: NULL
      salary: 2000.35
      office: 402
   depart_id: 2
*************************** 11. row ***************************
          id: 11
        name: 西西
         sex: female
         age: 38
   hire_date: 2011-03-12
        post: sale
post_comment: NULL
      salary: 1000.37
      office: 402
   depart_id: 2
*************************** 12. row ***************************
          id: 12
        name: 乐乐
         sex: female
         age: 18
   hire_date: 2016-05-13
        post: sale
post_comment: NULL
      salary: 3000.29
      office: 402
   depart_id: 2
*************************** 13. row ***************************
          id: 13
        name: 啦啦
         sex: female
         age: 18
   hire_date: 2017-01-27
        post: sale
post_comment: NULL
      salary: 4000.33
      office: 402
   depart_id: 2
*************************** 14. row ***************************
          id: 14
        name: 僧龙
         sex: male
         age: 28
   hire_date: 2016-03-11
        post: operation
post_comment: NULL
      salary: 10000.13
      office: 403
   depart_id: 3
*************************** 15. row ***************************
          id: 15
        name: 程咬金
         sex: male
         age: 18
   hire_date: 1997-03-12
        post: operation
post_comment: NULL
      salary: 20000.00
      office: 403
   depart_id: 3
*************************** 16. row ***************************
          id: 16
        name: 程咬银
         sex: female
         age: 18
   hire_date: 2013-03-11
        post: operation
post_comment: NULL
      salary: 18000.00
      office: 403
   depart_id: 3
*************************** 17. row ***************************
          id: 17
        name: 程咬铜
         sex: male
         age: 18
   hire_date: 2015-04-11
        post: operation
post_comment: NULL
      salary: 19000.00
      office: 403
   depart_id: 3
*************************** 18. row ***************************
          id: 18
        name: 程咬铁
         sex: female
         age: 18
   hire_date: 2014-05-12
        post: operation
post_comment: NULL
      salary: 17000.00
      office: 403
   depart_id: 3
18 rows in set (0.00 sec)

#书写顺序
select id,name from emp where id > 1;

#执行顺序
from
where
select
...

"""

虽然执行顺序和书写顺序不一致，你在写sql语句的时候不知道怎么写
你就按照书写顺序的方式写sql
    select * 先用*占位
    之后再去补全后面的sql语句
    最后将*替换成你想要的具体字段
    
"""    

# 作用：是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id >=3 and id <=6;
select id ,name,age from emp where id between 3 and 6;

#2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary in (20000,18000,17000);
select * from emp where salary = 18000 or salary=20000 or salary=17000;

#3.查询员工姓名中包含字母o的员工姓名和  薪资
"""
模糊查询
    like    % 匹配任意多个字符，      _匹配任意单个字符
"""
select name,salary from emp where name like "%o%"; 

#4.查询员工姓名是由4个字符组成的姓名和薪资
select name,salary from emp where name like "____";
select name,salary from emp where char_length(name) = 4;

#5.查询id小于小于3或者大于6的数据
select * from emp where id not between 3 and 6;

#6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);

#7.查询岗位描述为空的员工姓名和岗位
select name,post from emp where post_comment is null;

#分组实际应用场景非常多
    男女比例
    部门平均薪资
    部门秃头率
    国家之间的数据统计

# 1.按照部门分组
select * from emp group by post;
分组之后，最小可操作的单位应该还是组，而不再是组内的单个数据
    上述命令在你没有设置严格模式的时候，是可以正常执行的，返回的是分组之后，
      每个组的第一条数据，但是这个不符合分组的规范，分组之后不应该在考虑单个数据，而是以组为操作单位，
    如果设置了严格模式，那么上述命令会直接报错
"""
"""
如果sql_mode中的配置有：ONLY_FULL_GROUP_BY，执行上面的语句汇报错：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'day47.emp.id' which is not functionally dependent on columns in GROUP 
BY clause; this is incompatible with sql_mode=only_full_group_by
    
解决办法：
    去掉sql_mode中的ONLY_FULL_GROUP_BY即可
    1.执行 
    set global sql_mode         ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    2.查看模式：show variables like "%mode";
    3.重新执行select * from emp group by post;即可
"""

#记得最后设置回去，因为分组之后，最小可操纵的单位应该是组，而不再是组内的某个数据
set global sql_mode         ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

设置严格模式后，分组默认只能拿到分组的依据
select post from emp group by post;  按照什么分组就能只能拿到分组，其他字段不能直接获取，需要借助一些方法(聚合函数)
"""

练习题：
    #什么时候需要分组？？？
        关键字：每个，平均，最高，最低

#1.获得每个部门的最高薪资
select post,max(salary) from emp group by post;

mysql> select post, max(salary) from emp group by post;
+-----------------------+-------------+
| post                  | max(salary) |
+-----------------------+-------------+
| operation             |    20000.00 |
| sale                  |     4000.33 |
| teacher               |  1000000.31 |
| 张江第一帅形象        |     7300.33 |
+-----------------------+-------------+
4 rows in set (0.01 sec)

select post as '部门' ,max(salary) as '最高薪资' from emp group by post;

mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post;
+-----------------------+--------------+
| 部门                  | 最高薪资     |
+-----------------------+--------------+
| operation             |     20000.00 |
| sale                  |      4000.33 |
| teacher               |   1000000.31 |
| 张江第一帅形象        |      7300.33 |
+-----------------------+--------------+
4 rows in set (0.01 sec)


#as 可以给字段起别名，也可以直接省略不写，但是不推荐，因为省略的话语义不明确，容易错乱

#2.获取每个部门的最低薪资
select post as '部门',min(salary) as min_sal from emp group by post;

#3.获取每个部门的平均薪资
select post as '部门',avg(salary) as '平均薪资' from emp group by post;

#4.获取每个部门的总薪资
selece post as '部门', sum(salary) as '总薪资' from emp group by post;

#5.获取每个部门的人数
select post as '部门',count(id) as '总人数' from emp group by post;
select post as '部门',count(name) as '总人数' from emp group by post;
select post as '部门',count(age) as '总人数' from emp group by post;
select post as '部门',count(salary) as '总人数' from emp group by post;
select post as '部门',count(post_comment) as '总人数' from emp group by post;     # 不能对null进行计数，其他都可以

#6.查询分组之后的部门名称，和每个部门下所有员工的姓名
#group_concat 不单单支持你获取分组之后的其他字段，还支持拼接操作
select post as '部门',group_coucat(name) as '部门成员' from emp group by post;

select post as '部门',group_concat(name,'_DSB') as '员工姓名' from emp group by post;

select post,group_concat(name,':',salary) from emp group by post;

#concat 不分组的时候使用
select concat('NAME:',name),concat("SAL:",salary) from emp;

# 补充：as语法不单单可以给字段起别名，还可以给表取别名
select emp.id,emp.name from emp;
select t1.id,t1.name from emp as t1;

#7.查询每个人的年薪   12薪
select name,salary * 12 from emp;

"""
from
where
group by
"""

#关键字where和group by 同时出现的时候，group by必须在where后面
where先对整个数据进行过滤，之后再分组操作
where筛选条件不能使用聚合函数

select id,name,salary from emp where max(salary) > 3000;

mysql> select id,name,age from emp where max(salary) > 3000;
ERROR 1111 (HY000): Invalid use of group function    

select max(salary) from emp;        #不分组默认就是一组

#统计各部门年龄在30岁以上的员工平均工资
select post as '部门', group_concat(name) as '员工姓名', avg(salary) from emp where age > 30 group by post;

"""
having 的语法是跟where是一致的，只不过having是在分组之后使用的过滤操作，即having是可以使用聚合函数的
"""

#统计各部门年龄在30岁以上的员工的平均工资并且保留平均薪资大于10000的部门

select post,avg(salary) from emp where age > 30 group by post having avg(salary) >10000;

"""
一定要注意，必须是完全一样的数据才可以去重！！！！！
一定不要将逐渐忽视了，有主键存在的情况下是一定不可能去重的
"""

[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
ORM  对象关系映射        让不懂sql语句的人也能够非常牛逼的操作数据库
表                            类
一条条数据                    对象
字段对应的值                  对象的属性
你再写类，就意味着在创建表 
用类生成对象，就意味着在创建数据
对象.属性，就是在获取数据字段对应的值
目的就是减轻Python程序员的压力，只需要会Python面向对象的知识点就可以操作mysql
"""
select distinct id,age from emp;    #带有主键，无法去重
select distince age from emp;

select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;

"""
order by 默认是升序，asc   可以忽略不写
order by desc 降序

"""

select * from emp order by age desc,salary asc;
#先按照age降序排列，如果age相同，则再按照salary升序排列

# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) desc;

select * from emp;
"""
针对数据过多的情况，我们通常是都是做分页处理
"""

select * from emp limit 3;    #只展示3条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
#第一条数据表示起始位置
#第二条数据表示取得条数

select * from emp where name regexp '^j.*(n|y)$'
#上面的正则表达式表示：以j开头，以n或者y结尾，中间任意多个字符

前期表准备

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

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

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

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

select * from dep,emp;    #结果叫笛卡尔积

mysql> select * from dep,emp;
+------+--------------+----+-------+--------+------+--------+
| id   | name         | id | name  | sex    | age  | dep_id |
+------+--------------+----+-------+--------+------+--------+
|  200 | 技术         |  1 | jason | male   |   18 |    200 |
|  201 | 人力资源     |  1 | jason | male   |   18 |    200 |
|  202 | 销售         |  1 | jason | male   |   18 |    200 |
|  203 | 运营         |  1 | jason | male   |   18 |    200 |
|  200 | 技术         |  2 | egon  | female |   48 |    201 |
|  201 | 人力资源     |  2 | egon  | female |   48 |    201 |
|  202 | 销售         |  2 | egon  | female |   48 |    201 |
|  203 | 运营         |  2 | egon  | female |   48 |    201 |
|  200 | 技术         |  3 | kevin | male   |   18 |    201 |
|  201 | 人力资源     |  3 | kevin | male   |   18 |    201 |
|  202 | 销售         |  3 | kevin | male   |   18 |    201 |
|  203 | 运营         |  3 | kevin | male   |   18 |    201 |
|  200 | 技术         |  4 | nick  | male   |   28 |    202 |
|  201 | 人力资源     |  4 | nick  | male   |   28 |    202 |
|  202 | 销售         |  4 | nick  | male   |   28 |    202 |
|  203 | 运营         |  4 | nick  | male   |   28 |    202 |
|  200 | 技术         |  5 | owen  | male   |   18 |    203 |
|  201 | 人力资源     |  5 | owen  | male   |   18 |    203 |
|  202 | 销售         |  5 | owen  | male   |   18 |    203 |
|  203 | 运营         |  5 | owen  | male   |   18 |    203 |
|  200 | 技术         |  6 | jerry | female |   18 |    204 |
|  201 | 人力资源     |  6 | jerry | female |   18 |    204 |
|  202 | 销售         |  6 | jerry | female |   18 |    204 |
|  203 | 运营         |  6 | jerry | female |   18 |    204 |
+------+--------------+----+-------+--------+------+--------+
24 rows in set (0.00 sec)


#上面的数据不是我们想要的，我们想要直接把对应的部门添加到每个员工后面
select * from emp,dep where emp.dep_id = dep.id;

mysql> select * from emp ,dep where emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

"""
mysql也知道，你在后面查询数据的过程中，肯定会经常用到这样的操作
所以特定给我们提供了对应的方法
        inner join     内连接
        lefy join       左连接
        right join      右连接
        union     全连接
"""

#inner join
select * from emp inner join dep on emp.dep_id = dep.id;
#只拼接两张表中共有的数据部分
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

#left join
#left join
select * from emp left join dep on emp.dep_id = dep.id;
#左表所有的数据都显示出来，没有对应的数据用null代替
mysql> select * from emp left join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
|  6 | jerry | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.05 sec)

#right join
select * from emp right join dep on emp.dep_id = dep.id;
#右表所有的数据都显示出来，没有对应的数据用null代替
mysql> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 公关部       |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

#union
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

mysql> select * from emp left join dep on emp.dep_id = dep.id
    -> union
    -> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
|    6 | jerry | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 公关部       |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.01 sec)

"""
子查询就是我们平时解决问题的思路
    分步骤解决问题
        第一步
       第二步

将一个查询的结果，当做另外一个查询语句的条件去使用
"""

#查询部门是技术或者人力资源的员工信息

#1.先获取符合条件的部门id
select id from dep where name='技术' or name = '人力资源部';

#2.再去员工表里筛选出对应的员工

select name from emp where emp.dep_id in (select id from dep where name='技术' or name = '人力资源部');

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把他作为一张虚拟表跟其他表关联
"""
多表查询就两种：
    先拼接表再查询
    子查询：一步一步来查询
"""

# 关键字 exists（了解）
只返回布尔值 True False
返回True的时候，外层查询语句执行
返回False的时候，外层查询语句不再执行

select * from emp where exists (select id from dep where id > 203);

mysql> select * from emp where exists (select id from dep where id > 203);
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 18 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)


select * from emp where exists (select id from dep where id > 206);
mysql> select * from emp where exists (select id from dep where id > 206);
Empty set (0.00 sec)