mysql数据库part3(查询进阶)

一、查询语法

sql查询语句基本语法：

select .. from .. where ..group by .. having .. order by .. limit..

1、where条件的使用

功能：对表中的数据进行过滤筛选

1. 判断的符号：
   < > = <= >= != <>(不等于)

2. 拼接条件的关键字：
   and or not

3. 查询范围区间between
   between 小值 and 大值 [小值,大值] 查询两者之间的这个范围所有数据

4. 查询某个值在具体某个范围里 in
   in(1,2,3,4)

5. 模糊查询 like “%” “_” 通配符
   like “%a” 匹配以a结尾的任意长度字符串
   like “a%” 匹配以a开头的任意长度字符串
   like “%a%” 匹配含有a字母的任意长度字符串
   like “_a” 个数一共是两个字符，必须以a结尾，前面字符随意
   like “a__” 个数一共是三个字符，必须以a开头，后面字符随意

(1) 单条件查询
查询部门是sale的所有员工姓名：

select emp_name from employee where post="sale";

(2) 多条件查询
部门是技术部，收入大于10000的所有人员及对应收入

select emp_name,salary from employee where post="技术部" and salary > 10000;

(3) between … and …
收入在10000-20000之间的所有员工姓名和对应收入

select emp_name,salary from employee where salary between 10000 and 20000;

收入不在10000-20000之间的所有员工姓名和对应收入

(4) null关键字，在查询的时候，要使用is进行判断，不能用=

select * from employee where post_comment is null;

(5) in 在…之中
查询收入是3000、4000、5000、8300所有员工的姓名和收入

select emp_name,salary from employee where salary in (3000,4000,5000,8300);

not … in … 不在所列数据之中：

select emp_name,salary from emnployee where salary not in (3000,4000,5000,8300);

写法二：

select emp_name,salary from employee where salary=3000 or salary=4000 or salary=5000 or salary=8300;

(6) 模糊查询

“%”通配符

select emp_name,salary from employee where emp_name like "L%";

“_”通配符

select emp_name,salary from employee where emp_name like "H__sir";

(7) concat数据拼接(as 别名)

select concat("姓名:",emp_name,"收入:",salary) as info from employee;

concat_ws(拼接的符号,参数1,参数2…):

select concat_ws(":",emp_name,salary) as info from employee;

计算年薪，可以使用运算符：

select concat_ws(":",emp_name,salary) as info from employee;

2、group by

子句，分组分类
group by 字段 对字段进行分组，by后面搜索什么字段，select后面就搜什么字段(必要)

1. 基本写法

select post from employee grop by post;

2. group_concat
   按照分类的形式进行数据拼接

select post,group_concat(emp_name) from employee group by post;

3. 聚合函数
   count统计总数：

select count(*) from employee;

max统计最大值：

select min(salary) from employee;

min统计最小值：

select min(salary) from employee;

avg统计平均值：

select avg(salary) from employee;

sum统计总和：

select sum(salary) from employee;

4. group by与聚合函数配合使用
   查询部门名以及各部门的平均薪资：

select post,avg(salary) from employee group by post;

查询部门名及各部门最高薪资：

select post,max(salary) from employee group by post;

查询部门名以及各部门最低薪资：

select post,min(salary) from employee group by post;

查询公司内男女员工个数：

select sex,count(*) from employee group by sex;

可以group by 两个字段，搜索两个字段：

select emp_name,salary from employee group by emp_name,salary;

3、having

数据在分类分组之后，进行二次数据过滤，一般配合group by使用，分组后过滤
找平均薪资大于10000以上的所有部门：

select post,avg(salary) from employee group by post having avg(salary) > 10000;

查询各岗位内包含的员工个数小于6的岗位名，包含员工名字、个数：

select post,group_concat(emp_name),count(*) from employee group by post having count(*) < 6;

查询各岗位平均薪资小于10000的岗位名、平均工资：

select post,avg(salary) from employee group by post having avg(salary) < 10000;

查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资：

select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;

4、order by

排序，按照什么字段排序
asc 升序：从小到大(默认)
desc 降序：从大到小

select * from employee order by age;

select * from employee order by age desc;

查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序

select * from employee order by age,hire_date desc;

查询各岗位平均薪资大于10000的岗位名、平均工资、结果按平均薪资升序排列：

select post,avg(salary) from employee group by post order by avg(salary);

5、limit 限制查询条数(数据分页)

limit m,n --> m代表从第几条数据进行查询，0代表第一条，n代表查询几行

从第一行开始搜索，搜索五条数据：

select * from employee limit 0,5;

从第六行开始搜索，搜索三条数据：

select * from employee limit 5,3;

只搜索三条数据：

select * from employee limit 3 #默认第一行开始搜索

搜索表中最后一条数据：

select * from employee order by id desc limit 1;

6、(了解)可以使用正则表达式查询数据(不推荐，效率不高，基本可使用模糊查询代替)

注：mysql不识别正则中的“？”
筛选出以ir结尾的员工名：

select * from employee where emp_name regexp ".*ir$";

筛选出含有程字的员工名：

select * from employee where emp_name regexp "程";

二、多表查询

1、内连接（inner join）

两表或多表满足条件的所有数据查询出来(两表之间的共有数据)：
表1: department

表2: employee

1）两表查询

select 字段 from 表1 inner join 表2 on 必要的关联条件

2）多表查询

select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2...

基本语法inner join on + 条件：

select * from employee inner join department on employee.dep_id=department.id;

用as起别名(推荐)：

select * from employee as e inner join department as d on e.dep_id=d.id;

as可以省略：

select * from employee e inner join department d on e.dep_id=d.id;

where实现联表查询：

select * from employee e , department d where e.dep_id=d.id;

2、外连接

1）左连接(左联查询 left join)：
以左表为主，右表为辅，完整查询左表所有数据，右表没有的补null

select * from employee e left join department d on e.dep_id=d.id; 

2）右连接(右联查询 right join)：
以右表为主，左表为辅，完整查询右表所有数据，左表没有的补null

select * from employee e right join department d on e.dep_id=d.id; 

3）全连接(左连接+union+右连接)

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

三、子查询

子查询：嵌套查询
1）sql语句当中又嵌套了另一条sql语句，用括号包起来，表达一个整体
2）一般应用在from字符后面(表达一张表)，where子句后面(表达一个条件)
3）查询速度从快到慢：单表查询 > 联表查询 > 子查询

一、找出平均年龄大于25岁的部门：
普通where方式查询

select d.id,d.name,avg(age) as avg_age from employee e ,department d where e.dep_id=d.id group by d.id,d.name having avg(age)>25;

用内连接查询上例：

select d.id,d.name,avg(age) as avg_age from employee e inner join department d on e.dep_id=d.id group by d.id,d.name having avg(age)>25;

用子查询查询上例：

select * from department where id in (select dep_id from employee group by dep_id having avg(age)>25);

二、查看技术部门员工姓名：

where普通写法：

select d.name,e.name from employee e ,department d where e.dep_id=d.id and d.name="技术";

运行结果：

内连接写法：

select d.name,e.name from employee e inner join department d on e.dep_id=d.id where d.name="技术"

子查询写法：

select name from employee where dep_id=(select id from department where name="技术");

三、查看哪个部门没员工：

select d.name from employee e right join department d on e.dep_id=d.id where e.dep_id is null;

四、把大于其本部门平均年龄的员工名和姓名查出来
第一步：先计算各部门平均年龄：

select d.id,avg(e.age) as avg_age from employee e inner join department d on e.dep_id=d.id group by d.id; 

第二步：将以上结果通过子查询拼接到原employee表中：

select * from employee e inner join (select d.id,avg(e.age) as avg_age from employee e inner join department d on e.dep_id=d.id group by d.id) d on e.dep_id=d.id;

第三步：进行筛选

select name from employee e inner join (select d.id,avg(e.age) as avg_age from employee e inner join department d on e.dep_id=d.id group by d.id) d on e.dep_id=d.id where age > avg_age;

带EXISTS关键字的子查询：

EXISTS关键字表示存在：
如果内层sql能够查询到数据，返回True，外层sql执行查询语句
如果内层sql不能够查询到数据，返回False，外层sql不执行查询语句

子查询总结：

• 子查询可以单独作为一个临时数据表，临时数据，临时字段
• 一般用在from where select 子句后面
• 可以通过查询出的临时数据和另一个表做一次联表，变成更大的表，做单表查询，以得到想要的结果