mysql----单表多表查询

 

建表
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
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204);

#重点：外链接语法
#简单查询
select * from department,employee; #笛卡尔积
select * from department,employee where department.id=employee.dep_id;

#内连接：按照on条件只两张表的相同的部分，连接成一张虚拟的表
select * from employee inner join department on department.id=employee.dep_id;
select * from department inner join employee on department.id=employee.dep_id;
#select * from employee,department where department.id=employee.dep_id;



#左链接：在按照on的条件取到两张表共同部分的基础上，保留左表的记录
select * from employee left join department on department.id=employee.dep_id;

#右链接：在按照on的条件取到两张表共同部分的基础上，保留右表的记录
select * from employee right join department on department.id=employee.dep_id;

#full jion：
select * from employee left join department on department.id=employee.dep_id
union
select * from employee right join department on department.id=employee.dep_id;




#子查询：
mysql> select * from employee where dep_id in (select id from department where name in ('技术','销售'));
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | egon      | male   |   18 |    200 |
|  4 | yuanhao   | female |   28 |    202 |
|  5 | liwenzhou | male   |   18 |    200 |
+----+-----------+--------+------+--------+
3 rows in set (0.02 sec)


#查询平均年龄在25岁以上的部门名
select name from department where id in (
select dep_id from employee group by dep_id having avg(age) > 25
);
#查看技术部员工姓名
select name from employee where dep_id = (select id from department where name='技术');

#查看小于2人的部门名
select name from department where id in (
select dep_id from employee group by dep_id having count(id) < 2
)
union
select name from department where id not in (select distinct dep_id from employee);

#提取空部门                              #有人的部门
select * from department where id not in (select distinct dep_id from employee);


或者：
select name from department where id in
(
select dep_id from employee group by dep_id having count(id) < 2
union
select id from department where id not in (select distinct dep_id from employee)
);

#exists  判断有没有接管
mysql> select * from employee where exists (select id from department where name='hahahahah');
Empty set (0.00 sec)

mysql> select * from employee where exists (select id from department where name='技术');
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)

 

 

单表查询

 group by

 

表在习题整理的单表查询中

大前提：可以按照任意字段分组,但分完组后，只能查看分组的那个字段，
要想取的组内的其他字段信息，需要借助函数
单独使用group by关键字分组
    select post from employee group by post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，
    想要获取组内的其他相关信息，需要借助函数
group by关键字和group_concat()函数一起使用
    select post,group_concat(name) from employee group by post;#按照岗位分组，并查看组内成员名
    select post,group_concat(name) as emp_members from employee group by post;
group by与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

练习
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
答案：
#题1：分组
mysql> select post,group_concat(name) from employee group by post;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | group_concat(name)                                      |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| 老男孩驻沙河办事处外交大使              | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+


#题目2：
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使              |         1 |
+-----------------------------------------+-----------+


#题目3：
mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+

#题目4：
mysql> select post,avg(salary) from employee group by post;
+-----------------------------------------+---------------+
| post                                    | avg(salary)   |
+-----------------------------------------+---------------+
| operation                               |  16800.026000 |
| sale                                    |   2600.294000 |
| teacher                                 | 151842.901429 |
| 老男孩驻沙河办事处外交大使              |   7300.330000 |
+-----------------------------------------+---------------+

#题目5
mysql> select post,max(salary) from employee group by post;
+-----------------------------------------+-------------+
| post                                    | max(salary) |
+-----------------------------------------+-------------+
| operation                               |    20000.00 |
| sale                                    |     4000.33 |
| teacher                                 |  1000000.31 |
| 老男孩驻沙河办事处外交大使              |     7300.33 |
+-----------------------------------------+-------------+

#题目6
mysql> select post,min(salary) from employee group by post;
+-----------------------------------------+-------------+
| post                                    | min(salary) |
+-----------------------------------------+-------------+
| operation                               |    10000.13 |
| sale                                    |     1000.37 |
| teacher                                 |     2100.00 |
| 老男孩驻沙河办事处外交大使              |     7300.33 |
+-----------------------------------------+-------------+

#题目七
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |

order by与limit

 

按单列排序
    select * from employee order by salary;
    select * from employee order by salary asc;#升序
    select * from employee order by salary desc; #降序
 select * from emp order by age asc,salary desc; #先按照年龄从小到大
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    select* from employee
        order by age,
        salary desc;
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
答案：
#题目1
mysql> select * from employee ORDER BY age asc,hire_date desc;

#题目2
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

#题目3
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 151842.901429 |
| operation |  16800.026000 |
+-----------+---------------+


limit
   限制查询记录数
   示例：
    select * from employee order by salary desc
        limit 3;                    #默认初始位置为0

    select *  from employee order by salary desc
        limit 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    select * from employee order by salary desc
        limit 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
分页显示，每页5条
select * from employee limit 0,5;

select * from employee limit 5,5;

select * from employee limit 10,5;

使用正则表达式查询
select * from employee where name regexp '^ale';

select * from employee where name regexp 'on$';

select * from employee where name regexp 'm{2}';

小结：对字符串匹配的方式
where name = 'egon';
where name like 'yua%';
where name regexp 'on$';

练习
查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where name regexp '^jin.*[gn]$';

where 约束

 

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
    select name from employee
        where post='sale';

#2:多条件查询
    select name,salary from employee
        where post='teacher' and salary>10000;

#3:关键字between and
   select name,salary from employee
        where salary between 10000 and 20000;

    select name,salary from employee
        where salary not between 10000 and 20000;

#4:关键字is null(判断某个字段是否为null不能用等号，需要用is)
    select name,post_comment from employee
        where post_comment is null;

    select name,post_comment from employee
        where post_comment is not null;

    select name,post_comment from employee
        where post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字in集合查询
    select name,salary from employee
        where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;

    select name,salary from employee
        where salary in (3000,3500,4000,9000) ;

   select name,salary from employee
        where salary not in (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    select * from employee
            where name like 'eg%';

    通配符’_’
    select * from employee
           where name like 'al__';
表在习题整理的单表查询中
 练习
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
答案：
select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age > 30;
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post='teacher' and name like 'jin%';

优先级

 

select 分组字段,count(id) from t1 where 条件 group by 分组字段

max
min
sum
avg

group_concat

#关键字执行优先级
from
where
group by
按照select后的字段取得一张新的虚拟表，有聚合函数则执行聚合函数
having


select max(salary) from t1 where id > 2 group by depart_id having count(id) > 2;
select 333333333 from t1 where id > 2 group by depart_id having 4 > 2;

#练习
select post,count(id),group_concat(name) from emp group by post having count(id) < 2;


select post,avg(salary) as 平均工资 from emp group by post having avg(salary) > 10000;
select post 岗位名,avg(salary) 平均工资 from emp group by post having avg(salary) > 10000;



#order by关键字
select * from emp order by salary;
select * from emp order by salary asc; #升序
select * from emp order by salary desc; #降序

 select * from emp order by age asc,salary desc; #先按照年龄从小到大排，如果年龄分不出胜负（即值相同）再按照salary从大到小排。

#练习：

select * from emp order by age asc,hire_date desc;

select post 岗位名,avg(salary) 平均工资 from emp group by post having avg(salary) > 10000 order by avg(salary) asc;



#limit
mysql> select * from emp limit 10;
#从哪开始，往后取几条
mysql> select * from emp limit 0,3;
mysql> select * from emp limit 3,3;
mysql> select * from emp limit 6,3;


select name,id from emp where id>15 having id > 16;


#distinct
mysql> select * from emp limit 0,3;


#多表查询
create table department(
id int primary key auto_increment,
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,
foreign key(dep_id) references department(id)
);

#简单查询
select * from department,employee; #笛卡尔积
select * from department,employee where department.id=employee.dep_id;

#内连接：按照on条件只两张表的相同的部分，连接成一张虚拟的表
select * from employee inner join department on department.id=employee.dep_id;
select * from department inner join employee on department.id=employee.dep_id;
#select * from employee,department where department.id=employee.dep_id;



#左链接：在按照on的条件取到两张表共同部分的基础上，保留左表的记录
select * from employee left join department on department.id=employee.dep_id;

#右链接：在按照on的条件取到两张表共同部分的基础上，保留右表的记录
select * from employee right join department on department.id=employee.dep_id;

#full jion：
select * from employee left join department on department.id=employee.dep_id
union
select * from employee right join department on department.id=employee.dep_id;




#子查询：
mysql> select * from employee where dep_id in (select id from department where name in ('技术','销售'));
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | egon      | male   |   18 |    200 |
|  4 | yuanhao   | female |   28 |    202 |
|  5 | liwenzhou | male   |   18 |    200 |
+----+-----------+--------+------+--------+
3 rows in set (0.02 sec)


#查询平均年龄在25岁以上的部门名
select name from department where id in (
select dep_id from employee group by dep_id having avg(age) > 25
);
#查看技术部员工姓名
select name from employee where dep_id = (select id from department where name='技术');

#查看小于2人的部门名
select name from department where id in (
select dep_id from employee group by dep_id having count(id) < 2
)
union
select name from department where id not in (select distinct dep_id from employee);

#提取空部门                              #有人的部门
select * from department where id not in (select distinct dep_id from employee);


或者：
select name from department where id in
(
select dep_id from employee group by dep_id having count(id) < 2
union
select id from department where id not in (select distinct dep_id from employee)
);

#exists
mysql> select * from employee where exists (select id from department where name='hahahahah');
Empty set (0.00 sec)

mysql> select * from employee where exists (select id from department where name='技术');
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

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

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

4.按照select后的字段得到一张新的虚拟表，如果有聚合函数，则将组内数据进行聚合

5.将4的结果过滤：having

6.查出结果：select

7.去重

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

9.限制结果的显示条数


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

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

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

#通过四则运算查询
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary
   FROM employee;

   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary
   FROM employee;

聚合函数与过滤

 

先from找到表
再用where的条件约束去表中取出记录
然后进行分组group by，没有分组则默认一组
然后进行聚合
最后select出结果
示例：
    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;


having过滤
having与where在语法上是一样的

select * from employee where salary > 10000;
select * from employee having salary > 10000;
having与where不一样的地方在于!!!!!!

！！！执行优先级从高到低：where > group by > 聚合函数 > having
1. Where 是一个约束声明，使用Where约束来自数据库的数据，
Where是在结果返回之前起作用的（先找到表，按照where的约束
条件，从表(文件)中取出数据），Where中不能使用聚合函数。

2. Having是一个过滤声明，是在查询返回结果集以后对查询结果进行
的过滤操作（先找到表，按照where的约束条件，从表(文件)中取出数据，
然后group by分组，如果没有group by则所有记录整体为一组，然后执
行聚合函数，然后使用having对聚合的结果进行过滤），在Having中可
以使用聚合函数。

3. having可以放到group by之后，而where只能放到group by之前

4. 在查询过程中聚合语句(sum,min,max,avg,count)要比having子
句优先执行。而where子句在查询过程中执行优先级高于聚合语句。


#验证之前再次强调：执行优先级从高到低：where > group by > 聚合函数 > having
select count(id) from employee where salary > 10000;
 #正确，分析：where先执行，后执行聚合count(id),然后select出结果
select count(id) from employee having salary > 10000;
#错误，分析：先执行聚合count(id)，后执行having过滤，无法对id进行salary>10000的过滤

#以上两条sql的顺序是
1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目
2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having
 基于上一步聚合的结果（此时只有count(id)字段了）进行salary>10000的
 过滤，很明显，根本无法获取到salary字段

 练习
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
答案
#题1：
mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
+-----------------------------------------+--------------------+-----------+
| post                                    | group_concat(name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| 老男孩驻沙河办事处外交大使              | egon               |         1 |
+-----------------------------------------+--------------------+-----------+

#题目2：
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

#题目3：
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
+-----------+--------------+
| post      | avg(salary)  |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+