数据库Day02

DQL查询表中数据

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

DQL是实际开发过程中使用最多的。

1. 数据准备

#创造员工表
create database zqwl01;

#选择zqwl01
use zqwl01;

#创建员工表

create table emp (
eid int,
ename varchar(10),
sex char(1),
salary double(8,1),
hire_date date,
dept_name varchar(20)
);

简单查询：
        select 字段名, 字段名, ... from 表名;
        
        注意：
            1.查询所有字段可以使用*代替所有字段
            2.查询部分字段，必须手动指定查询的字段
            3.查询的字段可以进行数学计算，函数操作，...

        为字段定义别名，仅改变展示出来的字段名，数据库中的字段名不会改变。
  

#将查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;

#查询所有的字段可以使用 * , * 代表所有的字段
select * from emp;

#查询emp表中所有的记录, 只显示eid和ename字段
select eid ,ename from emp;

#查询所有的部门(出现重复部门)
select dept_name from emp;

#使用去重查询 关键字 distinct
select distinct dept_name from emp;

#支持算术运算符 + - * / % 的运算
select eid ,ename , salary, salary+1000 from emp;

#仅改变展示的名称
select eid, ename, sex, salary, hire_date, dept_name from emp;

select eid as '编号', ename as '姓名', sex as '性别', 
			salary as '薪资', hire_date as '入职日期', dept_name as '部门名称' from emp;

#定义别名简化，as可以省略不写
select eid '编号', ename '姓名', sex '性别', 
			salary '薪资', hire_date '入职日期', dept_name '部门名称' from emp;

     

    条件查询：
        select 字段名, 字段名, ... from 表名 where 条件;
        执行顺序：
            1.从哪个表查询  from
            2.按照指定条件过滤 where
            3.指定返回的字段值 select
        
        条件：
                    > < >= <= != <>
                    and && 多个条件同时成立
                    between ... and ... 区间值（前后包含）
                    or ||  多个条件成立任意一个即可
                    in(值,值,...) 等于任意一个值即可
                    注意：
                        值为null，判断时，is null  is not null

#1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename = '黄蓉';

#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;

#3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary != 5000; 
select * from emp where salary <> 5000; 

#4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary > 6000; 

#5 查询薪水价格在5000到10000之间的员工信息 
select * from emp where salary >= 5000 and salary <= 10000; 
select * from emp where salary between 5000 and 10000; 

#6 查询薪水价格是3600或7200或者20000的员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);

模糊查询，通配符

#1 查询含有'八'字的员工信息
select * from emp where ename like '%八%';
 
#2 查询以'孙'字开头的员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的员工信息 
select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息 
select * from emp where dept_name is null;

#5 查询有部门的员工信息
select * from emp where dept_name is not null;

单列排序

-- 升序排序(默认 asc)
select * from emp order by salary;

-- 降序排序(desc)
select * from emp order by salary desc;

组合排序

-- 组合排序
select * from emp order by salary, hire_date desc;

函数

单行函数:

单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

多行函数:

我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

• 多行函数会忽略null空值。

• 多行函数也称为分组函数, 聚合函数。

使用多行函数，是对某一列的值进行计算，然后返回一个单一的值

 其它函数 

日期与时间函数

数值函数

/*
	数值函数：对数字进行操作。MySQL提供了一张虚拟表，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。
*/

select abs(1), abs(-2) from dual; -- dual虚表，例如直接操作值，值不属于某个表的字段，可以使用虚表。

select abs(1), abs(-2); -- dual虚表可以省略

select ceil(3.5), floor(3.5), mod(3,2), pi(), pow(2,3), rand(), round(1.245, 2), truncate(1.245,2);

/*
	日期函数：
*/

select curdate(), curtime(), now(), sysdate();
select year("2020-10-11"), month("2020-10-11"), day("2020-10-11");

/*
	流程函数：
*/
#1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
select eid, ename, sex, salary, hire_date, dept_name from emp;

select eid, ename, sex, salary, if(salary>=10000, "高工资", "低工资"), hire_date, dept_name from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, sex, salary "月薪", salary*12 "年薪", hire_date, dept_name from emp;

select eid, ename, sex, salary "月薪", salary*12 "年薪", salary*12+30000 "年薪含年终奖", hire_date, dept_name from emp;

select eid, ename, sex, salary "月薪", salary*12 "年薪", ifnull(salary, 0)*12+30000 "年薪含年终奖", hire_date, dept_name from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, sex, salary, 
	case
		when salary>=15000 then "优秀"
		when salary>=9000 then "坚持住"
		when salary>=5000 then "加油哦"
		when salary>=3000 then "加把劲"
		else "不及格"
	end, hire_date, dept_name from emp;

分组

分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。

# 按照性别分组查询
select sex from emp group by sex;

select * from emp group by sex;

/*
	分组：按照指定的字段值分组，相同的值分为1组。
	
	select 分组字段|多行函数(分组函数) from 表名 group by 分组字段;
*/

#1 查询每个部门的名称
select dept_name from emp group by dept_name; -- 4


#2 查询每个部门名称及部门的平均薪资 
select dept_name,avg(salary) from emp group by dept_name; 


#3 查询每个部门名称及部门的平均薪资, 部门名称不能为空
/*
	1.from 找表
	2.where 过滤
	3.group by 分组
	4.select 返回需要的数据(分组字段，分组的统计)
*/
select dept_name,avg(salary) from emp where dept_name is not null group by dept_name ; 


#4 查询每个部门名称及部门的平均薪资 , 只显示平均工资在4000以上的 
-- > 1111 - Invalid use of group function  where 后不允许出现分组函数，where在分组之前执行。
select dept_name,avg(salary) from emp where avg(salary) > 4000 group by dept_name; 


/*
	过滤：
		where：分组之前执行，不允许使用分组函数
		having：分组之后执行，允许使用分组函数
*/
select dept_name,avg(salary) from emp group by dept_name having avg(salary)>4000; 



#4 查询每个部门名称及部门的平均薪资 , 只显示平均工资在4000以上的 并且 部门不能为null
select dept_name,avg(salary) from emp where dept_name is not null  group by dept_name having avg(salary)>4000; 

select dept_name,avg(salary) from emp group by dept_name having avg(salary)>4000 and dept_name is not null; 

/*
	from -- where -- group by -- having –- select -- order by
*/


#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称及最小薪资
select dept_name, min(salary) from emp group by dept_name having min(salary)<4000;


#2 统计平均工资大于6000的部门名称 
select dept_name, max(salary) from emp group by dept_name having avg(salary) > 6000;


#3 统计人数小于4个人部门的平均工资
select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4;


#4 统计每个部门最高工资, 排除最高工资小于10000的部门
select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000;

limit关键字

作用:

• limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)

• limit 语法是 MySql的方言, 用来完成分页

/*
	from -> where(分组前过滤) -> group by(分组) -> having(分组后过滤) -> select -> order by -> limit
*/

-- limit 限制返回的条数  limit 从哪开始,获取几个

select * from emp;

select *  from emp limit 0,3;

select * from emp limit 3; -- 0可以省略

select * from emp limit 3,2;

# 获取每个部门的薪资总 和 部门名称，部门平均工资 > 3000
select dept_name, sum(salary) from emp group by dept_name having avg(salary)>3000;

# 获取每个部门的薪资总 和 部门名称，部门平均工资 > 3000，按照 薪资降序
select dept_name, sum(salary) from emp group by dept_name having avg(salary)>3000 order by avg(salary) desc;

# 获取每个部门的薪资总 和 部门名称，部门平均工资 > 3000，按照 薪资降序, 只要平均薪资最高的
select dept_name, sum(salary) from emp group by dept_name having avg(salary)>3000 order by avg(salary) desc limit 1;