MySQL基础学习 之 DQL查询/排序/函数/分组

目录

一、简单查询

1.查询字段

2.给查询字段起别名

注意

3.字段参与数学运算：

二、条件查询

1.定义：

2.where语句

3.条件符号

(1)大于，小于，等于，在两者之间，是否为空is (not) null 

(2)并且and，或者or，包含in

(3)模糊查询like

三、排序

1.定义：

2.order by 语句：

3.多个字段实现排序

4.综合案例

四、数据处理函数

1.定义：

2.常见的单行处理函数

（1）lower函数

（2）substr函数

（3）concat函数

（4）length函数

（5）trim函数

（6）round函数

（7） rand( )函数 

（8）ifnull函数

（9）case...when...then...when...then...else...end语句

五、分组函数

介绍：

五个分组函数：

使用注意事项：

 六、分组查询（很重要！！！）

1.使用情景：

2.group by 语句

3.having语句

4.综合案例：

书写顺序：

执行顺序：

1.找出每个部门的工资总和

2.找出每个部门里，不同工作岗位的最高薪资

3.找出每个部门的最高薪资，显示薪资大于1000的人员信息

七、总结

1.单表查询的所有命令

书写顺序：

执行顺序：

2.综合案例

3.特别注意

---

一、简单查询

•  select 和 from 是关键字，字段名和表名是标识符。
•  SQL语句是通用的，以分号结尾，且不区分大小写。

1.查询字段

//查询一个字段
select 字段名 from 表名;

//查询两个及以上的字段
select 字段名1，字段名2 from 表名;

//查询所有字段
select * from 表名;

2.给查询字段起别名

//给查询字段起别名
select 字段初始名 as 字段修改名 from 表名;

//示例
select name1,name2 as Name from aTable;
//该命令执行后,会修改name2显示的字段名为Name,显示name1和Name字段下的数据

//起别名语句中去掉as也可以正常运行
select name1,name2 Name from aTable;

注意

•  起别名操作中的as 去掉也可正常运行；
• 如果字段名中有空格（eg:A name）或者中文，用单引号/双引号把该字段名括起来。
• 单引号是标准，数据库中的字符串都用单引号括起来。双引号在MySQL中可用，在Oracle中不可用。
•  起别名操作不会修改原表中的原始字段名，只是将显示的字段名显示为该字段的别名。
• select操作只有查询功能，永远不会对数据进行更改操作。

3.字段参与数学运算：

//字段能够参与数学运算，可以使用数学表达式，能直接在字段后加上数学运算。
select name，monthSalary *12 as YearSalary;

二、条件查询

1.定义：

查询/显示符合条件的数据。

2.where语句

select 
    字段1，字段2，字段3....
from
    表名
where
    条件;

3.条件符号

(1)大于，小于，等于，在两者之间，是否为空is (not) null 

示例：

通过判断工资的大小，显示符合条件的员工的姓名、编号和工资

//等于 =
select name,no,salary from aTable where salary = 1000;

//不等于 <> 或 ！=
select name,no,salary from aTable where salary != 1000;
select name,no,salary from aTable where salary <> 1000;

//小于 <
select name,no,salary from aTable where salary < 1000;

//大于等于 >=
select name,no,salary from aTable where salary >= 1000;

//在两个值之间 >= and <=
select name,no,salary from aTabel where salary salary >= 1000 and salary <= 2000;

//在两个值之间 between...and...
select name,no,salary from aTabel where salary between 1000 and 2000;
--必须左小右大

//为空 is null 或者 不为空 is not null
select name,no,salary from aTable where salary is null;
select name,no,salary from aTable where salary is not null;

//错误示例 = null 
select name,no,salary from aTable where salary = null;
--不可以使用等号=，因为null不是一个具体数值

(2)并且and，或者or，包含in

//并且and
select name,no,salary,job from aTable where job = 'boss' and salary > 1000;
--显示职位为boss 并且 工资大于1000的员工的名字、编号、工资、职位

//或者or
select name,no,salary,job from aTable where job = 'boss' or job = 'manager';
--显示职位为 boss 或者 manager 的员工的名字、编号、工资、职位

//and 优先级高于 or,两个同时出现,and会先执行
select * from aTable where salary > 1000 and job = 'boss' or job = 'manager';
--显示工资大于1000并且工作是boss的人，或者工作是manager的人

//可以通过给or加小括号避免这种情况
select * from aTable where salary > 1000 and (job = 'boss' or job = 'manager';)
--显示工资大于1000，并且工作是boss的人或者工作是manager的人

//包含in（不是区间，里面是具体的值）
select * from aTable where salary > 1000 and job in('boss','manager');
--等价于第二条语句：显示职位为 boss 或者 manager 的员工的名字、编号、工资、职位

//不包含not in
select * from aTable where salary > 1000 and job not in('boss','manager');
--显示职位不是 boss 或者 manager 的员工的名字、编号、工资、职位

(3)模糊查询like

%代表任意多个字符，_代表任意一个字符。

示例：

--显示名字里含有‘R’字母的员工的名字
select name from aTable where name like '%R%';

--显示名字以‘A’字母开头的员工的名字
select name from aTable where name like 'A%';

--显示名字以‘B’字母结尾的员工的名字
select name from aTable where name like '%B';

--显示名字的第三个字母是‘S’字母的员工的名字
select name from aTable where name like '__S%';

--显示名字里有下划线的员工的名字
select name from aTable where name like '%_%';
//错误，会显示所有员工的名字，_有特殊含义

---显示名字里有下划线的员工的名字
select name from aTable where name like '%\_%';
//使用转义字符\,可以正确标识_

三、排序

1.定义：

默认为升序排序，想要实现降序需要加上desc.

2.order by 语句：

--升序（默认）：
select 
    字段A,字段B....
from
    表名
order by
    排序字段 asc;

--降序(加上desc)：
select 
    字段A,字段B....
from
    表名
order by
    排序字段 desc;

3.多个字段实现排序

• 'order by'语句中，哪个字段在前，哪个字段就起主导作用。

--两个字段实现排序:
--根据工资和姓名进行排序，先对薪资进行排序，在薪资相同的情况下，姓名使用升序排序。
select salary,name from aTable order by salary asc,name asc;

//'order by'语句中，salary在name前，则salary起到主导作用，只有salary相同的情况下才考虑name.

4.综合案例

• 对表中薪资在1000-2500之间的员工，根据薪资进行降序排序

--对表中薪资在1000-2500之间的员工，根据薪资进行降序排序
select 
    name,salary
from
    aTable
where
    salary between 1000 and 2500
order by 
    salary desc;

！！！
//排列顺序：
select 
from
where
order by

！！！
//执行顺序：
from
where
select 
order by

• 排列顺序：select  -> from  -> where  -> order by
• 执行顺序：from ->  where  -> select  -> order by

四、数据处理函数

1.定义：

• 又称单行处理函数
• 一个输入对应一个输出
• 与多行处理函数(多个输入对应以个输出)相对

2.常见的单行处理函数

函数名	作用
lower	转换小写；
upper	转换大写；
substr	取子串；
concat	实现字符串的拼接；
length	返回字符串的长度；
trim	去掉字符串的空格；
format	设置千分位；
round	四舍五入；
rand（）	生成随机数；
ifnull	将null转换成一个具体的值；
case...when...then...when ...then...else ...end
str_to_date	将字符串转换为日期；
date_format	将日期格式化；

（1）lower函数

--将名字变为小写，显示字段名为lower(name)的数据
select lower(name) from aTable;

--将名字变为小写，给lower(name)起别名为Name
select lower(name) as Name from aTable;

（2）substr函数

• 起始下标从1开始
• substr(字段名，起始下标，截取长度)

--显示name字段下，所有数据的第一个字符
select substr(name,1,1) from aTable;

--显示名字中第一个字母是A的员工信息
select name,salary from aTable where substr(name,1,1) = 'A';
--或：
select name,salary from aTable where name like 'A%';

（3）concat函数

• 实现字符串的拼接

--将name中的首字母大写，其他字母小写
select 
    concat(substr(name,1,1),substr(name,2,length(name)-1) 
as
    resultName
from
    aTable

（4）length函数

--显示姓名长度
select length(name) as nameLength from aTable;

（5）trim函数

select * from aTable where name = '   Reina';
//无法查询出

select * from aTable where name = trim('   Reina');
//查询成功，会显示name为Reina的学生的所有信息

（6）round函数

• select后可以跟字段名或者字面值/字面量。

--aTabel中不存在字段名NoPerson
select Noperson from aTabel;
//显示无法找到

--将Noperson改为字面量
select 'Noperson' from aTabel;
//将会根据aTabel的表结构（有几条数据行就显示几个），显示数据全为Noperson的表

select 1234.678 from aTabel;
//将会根据aTabel的表结构（有几条数据行就显示几个），显示数据全为1234.678的表

• round(数据，保留小数位)

select round(1236.567，1) as result from aTable;
//1236.6

select round(1236.567，0) as result from aTable;
//1237

select round(1236.567，-1) as result from aTable;
//1240

（7） rand( )函数 

• rand( )*n  
• n为范围

--生成100以内的随机数
select rand()*100 from aTable;
//根据表结构，生成对应数量的100以内的随机数

--生成100以内的随机数并进行整数位的四舍五入
select round(rand()*100,0) from aTable

（8）ifnull函数

• 空处理函数，专门处理值为null的数据
• 在数据库当中，凡是有null参与的数学运算，最终结果一定为null。
• ifnull(数据，被当做的值)

//计算员工的月薪：

--在aTable表中部分员工的津贴allowance为null
select name,salary + allowance as monthMoney from aTable;

--当津贴为null时，给津贴赋值为0
select name,salary + ifnull(allowance,0) as monthMoney from aTable;


//计算员工的年薪：

select name,(salary + ifnull(allowance,0) )*12 as yearMoney from aTable;

（9）case...when...then...when...then...else...end语句

• case表示匹配的数据
• 并不修改数据库内容，只是将查询结果显示为工资上调后的数据

--当员工的工作是manager的时候，工资上调10%;
--当员工的工作是salesman的时候，工资上调50%;
--其他正常

select 
    job,salary 
case 
    job 
when 
    'manager' then salary*1.1 
when
    'salesman' then salary*1.5
else 
    end

五、分组函数

介绍：

• 又称多行处理函数

• 多行输入，最终只有一个输出

• 使用时必须先进行分组，再使用函数

• 如果使用时没有进行分组，整张表默认为一个组

五个分组函数：

函数名	功能	实现
count	计数	select count(name)  from aTable;
sum	求和	select sum(salary)  from aTable;
avg	平均数	select avg(salary)  from aTable;
max	最大值	select max(salary)  from aTable;
min	最小值	select min(salary)  from aTable;

使用注意事项：

• 分组函数自动忽略null，不需要提前对null进行处理
• count（*）与count（具体字段）的区别：
  • count（*）表示：统计表当中的总行数
  • count（具体字段）表示：该字段下所有不为null的元素的总和（只要有一行数据，count则++）
  • 数据库中不存在所有数据都为null的数据行，一行数据中只要有一个不为NULL，它就为有效数据
• 分组函数不能用在where语句后面

select name,salary from aTable where salary > min(salary);
//报错

• 所有分组函数可以组合起来一起用

select max(salary),min(salary),count(*),avg(allowance),sum(salary) from aTable;

 六、分组查询（很重要！！！）

1.使用情景：

在实际应用中，有时需要先进行分组，再对每一组数据进行操作。

eg：求公司的每一个部门的工资总和，计算每一个工作岗位的平均薪资

2.group by 语句

select
    字段A，字段B....
from
    表名
group by
    ...;

3.having语句

• 可以对分完组的数据进行进一步的过滤。
• 不能够单独使用
• 不能够代替where
• 必须和 group by 联合使用
• 在where可以用的时候，优先使用where

4.综合案例：

集合目前所学的所有关键字，其书写顺序如下（不可以颠倒！！！）

书写顺序：

• select ...
• from   ...
• where  ...
• group by...
• order by  ...

执行顺序：

• from
• where
• group by
• select 
• order by

！！！分完组后进行查询，查询后在进行排序，排序后进行输出。

select name,salary from aTable where salary > min(salary);
//报错
--执行顺序:from,where,group by....
--where语句的执行在gruop by语句之前，因此在执行where语句中的分组函数时还没进行分组，而分组函数的使用必须先分组再使用，因此报错。

select sum(salary) from aTable;
//正确
--执行顺序:from,where,group by....
--select在group by之后执行，因此执行group by时自动将整张表分为一组，执行select语句时已经分好组了，因此不发生错误。

1.找出每个部门的工资总和

思路：先对各个部门进行分组，再在组内求和。

//先从aTable表中提取数据，再根据job字段进行分组，再对各组的数据进行sum求和
select 
    job,sum(salary) 
from 
    aTable 
group by
    job;


select 
    name,job,sum(salary) 
from 
    aTable 
group by
    job;
//可执行，但毫无意义，job分为四组，而name有14组，无法匹配，得出的表无任何意义

结论：当select语句中有group by语句时，select语句后只能跟参与分组的字段名和分组函数。

2.找出每个部门里，不同工作岗位的最高薪资

思路：将两个字段当成一个字段来看

select
    depart,job,max(salary)
from 
    aTable
group by
    job,depart;

3.找出每个部门的最高薪资，显示薪资大于1000的人员信息

方法一：先分组再筛选

第一步：先按照部门分组，找出每个部门里的最高薪资

--先按照部门分组，找出每个部门里的最高薪资
select 
    depart,max(salary)
from
    aTable
gruop by
    depart;

第二步：要求显示最高薪资大于1000的

select 
    depart,max(salary)
from
    aTable
group by
    depart
having
    max(salary) > 3000;
    

 方法二：先筛选，再分组

直接找出薪资大于1000的，再按照部门分组

select
    depart,salary
from
    aTable
where
    salary > 1000
group by
    depart;

优化策略：对于where和having，优先选择where，当where实在完成不了了再选择having。

七、总结

1.单表查询的所有命令

书写顺序：

不能够更改顺序！！！

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

执行顺序：

from      --选择要查询的表

where     --筛选符合条件的数据

group by  --对符合条件的数据进行分组

having    --对分完组的数据进行二次筛选

select    --选择需要显示的字段/数据 

order by  --对数据进行排序

2.综合案例

要求：找出各个工作岗位的平均薪资，显示除了'manager'岗位以外，所有平均薪资大于1000的员工信息，并按照平均薪资薪资降序排序。

--要求：找出各个工作岗位的平均薪资
--显示除了'manager'岗位以外，所有平均薪资大于1000的员工信息;并按照平均薪资薪资降序排序。

select
    job,avg(salary) as averageSalary
from
    aTable
where
    job != 'manager'
group by
    job
having
    avg(salary) > 1000
order by
    avg(salary) desc;

3.特别注意

1. 'order by'语句中，哪个字段在前，哪个字段就起主导作用。
2. 当select语句中有group by语句时，select语句后只能跟：参与分组的字段名和分组函数。
3. 使用分组函数前，必须先进行分组，才能使用分组函数。
4. where语句中不能使用分组函数
5. where语句无法实现时，可以使用having进行二次过滤，having语句必须和group by一起使用
6. 牢记六条语句的书写顺序和执行顺序