10.27-基础查询与条件查询

基础查询

简单查询

select empno from emp_li; //单列查询
select empno,ename from emp_li; //多列查询
select * from emp_li; //全部列查询

通配符 * 不建议使用，会降低查询速度和影响英语程序性能(从SQL优化角度来说)

条件查询

//查询1005员工信息
select empno,ename from emp_li where empno=1005;

执行过程:from(指定表)->where(记录过滤)->select (结果集)

操作符

=  等于
!=或<> 不等于
> 大于
>= 大于等于
< 小于
<= 小于等于
and 并且
or 或者
any 任意
all 所有

带有表达式的查询

列别名

可以将表达式查询的名称替换为别名

//查询员工的月薪与年薪
select ename,salary,salary*12 year_salary
from emp_li;

image-20221027100027383

表别名

与列别名相似

//使用限定表名
select e.ename from emp_li e;

Null空值

//查询员工的月薪
select ename,salary+bonus mouth_salary,salary,bonus
from emp_li;

注意

• null值参与算数运算时结果均为 null

• null 参与连接操作结果也为 null

• 任何数据类型都可以取值 null

空值处理函数

ifnull(e1,e2); //如果e1为空，则取e2代替

所以正确的查询应该为下面这样

//查询员工的月薪
select ename,salary+ifnull(bonus,0) mouth_salary,salary,bonus
from emp_li;

任何数据均可插入空值

//插入记录:
empno:1013
ename:'欧阳锋'
//其他字段为空
//全部字段
insert into emp_li values(1013,"欧阳锋",null,null,null,null,null,null);
//指定字段
insert into emp_li(empno,ename) values(1013,"欧阳锋");

简单练习

//查询员工姓名和职位，要求没有职位显示"No Position"
select ename,ifnull(position,"No Position") position
from emp_li;

拼接数据

concat(a,b);
//测试
select concat('a','b') from dual;
//空值拼接
select concat('a','b',null) from dual;

练习

//查询员工信息，要求将员工姓名和职位拼接在一起
select concat(ename,ifnull(position,"No Position")) message
from emp_li;

去重

distinct 只能附带在 select

//查询有哪些职位
select distinct position from emp_li;

如果 distinct 后接多个字段，则代表联合去重，只保留唯一的组合结果

select distinct deptno,position from emp_li;

大小写区分

//查询职位为"Analyst"的员工信息
select empno,ename,position
from emp_li
where position="analyst";

默认来说，MySQL对大小写并不敏感，无论大写小写均能正常匹配

当然，可以在创建表/查询表时添加 binary 让其对大小写敏感

SELECT empno,ename,position
from emp_li
where  binary position="Analyst"; //此时，如果position="analyst"则会无匹配返回

between 低值 and 高值

格式

肯定形式:包含临界值
[低值,高值]
否定形式:不包含临界值
no between 低值 and 高值

例子

//查询薪水大于5000并且小于等于10000的员工信息
//普通写法
select empno,ename,salary
from emp_li
where salary>=5000 
and salary<=10000;
//between and写法
select empno,ename,salary
from emp_li
where salary
between 5000
and 10000;

in的使用

格式

in(列表项) //判断等于任意一项

例子

//查询职位是"Manager"或者是"Analyst"的员工姓名及职位
//普通写法
select ename,position
from emp_li
where position="Manager"
or position="Analyst"
//in 写法
select ename,position
from emp_li
where position
in("Manager","Analyst");

注意,在比较时空值不与null匹配，且不能与任何数据匹配

模糊查询

like 为模糊查询命令

_表示一个字符，% 表示0到多个字符

//查询员工表中名字包含"张"字的员工
select empno,ename
from emp_li
where ename like "%张%"
//查询职位中第二个字符是"a"的员工姓名与职位
select ename,position
from emp_li
where position like "_a%";

空值的判断

空值不能用等于或不等于跟任何数据比较，包括自身

\\查询哪些员工没有奖金
select ename,bonus
from emp_li
where bonus is null;
\\查询哪些人有奖金
select ename,bonus
from emp_li
whrer bouns is not null

常用函数

单行函数

每行数据处理后返回一个结果

数字函数

//用于对数字进行四舍五入处理，保留到小数点后指定位数
round(数字,位数) 
//例子
select round(123.456,2) from dual; //123.46
select round(123.456,0) from dual; //123
select round(123.456,-2) from dual; //100

//用于对数字进行截取
truncate(数字,位数) 用于对数字进行截取
//测试
select truncate(123.456,2) from dual; //123.45
select truncate(123.456,0) from dual; //123

//更多的一些函数
abs(数字) //返回数字绝对值
rand() //返回0-1随即数
sqrt(数字) //返回数据的平方根
pow(x,y) //返回x的y次方
mod(x,y) //返回x除以y的余数
//测试
select abs(-2);
select rand();
select sqrt(16);
select pow(2,3);
select mod(4,3);

字符串函数

trim(字符串) \\去重空格
ltrim(字符串) \\去除左边
rlrim(字符串)  \\去除右边
//测试'( abc )'->'(abc)'
select concat('(',trim(' abc '),')');

//大小写转换
upper(小写) //小写转大写
lower(大写) //大写转小写
//测试
select upper("hello")

//字符串长度获取
length(字符串)
select ename,length(ename) from emp_li;

//从指定字符串指定位置截取指定数量字符
substring(字符串，起始位置,最大字符数量)
select ename,substring(ename,2,2)
from emp_li;

日期时间函数

now() //当前系统时间
date(日期) //返回日期部分
time(日期时间) //返回时间部分

//测试
select date(now());
select year(now());

adddate(时间,范围) //时间推算
//测试 day/week/month/year
select adddate(now(),interval -2 day);

date_format(时间，格式) 格式化时间数据
select date_format(now(),'%X-%m-%d %H:%i:%s');

组函数(聚合函数)

count(字段) //求记录数
sum(字段) //求和
avg(字段) //求平均
max(字段) //求最大值
min(字段) //求最小值

//求记录数
select count(empno) from emp_li;
select count(ename) from emp_li;
select count(position) from emp_li;
select count("*") from emp_li;

//查询员工表中薪水总和
select sum(salary) from emp_li;

//查询员工表中人数总和、薪水总和、平均薪水
select count(*),sum(salary),avg(ifnull(salary,0)) avg_salary from emp_li;

//查询员工表中最高薪水/最低薪水
select max(salary),min(salary) from emp_li;

总结:

count\sum\avg\max\min 如果函数中写列名，默认都是忽略空值，count(*)不忽略空值，sum/avg用于处理数值

多行数据处理他，返回一个结果

排序

对查询结果进行排序，强调的是先有结果再排序

使用 order by 字段 规则

规则: asc升序(默认) desc 降序

//查询员工姓名和薪水，要求薪水从低到高进行排序
select ename,salary
from emp_li
where salary is not null;
order by salary asc; //升序，asc可省略

select ename,salary
from emp_li
order by salary desc //降序 排序是空值为最小值

//按照部门号升序，同一部门按照薪水降序显示信息
select deptno,salary
from emp_li
where deptno is not null
order by deptno asc,salary desc; //字段名排序

select deptno d,salary s
from emp_li
where deptno is not null
order by d asc,s desc; //别名排序

select deptno,salary
from emp_li
where deptno is not null
order by 1 asc,2 desc; //数字排序

排序语句的执行强调的是 select 之后的内容，因此排序可以使用列名、列别名、函数、表达式和数字(查询结果集对应的顺序,第一列用数字1表示，以此类推)

select empno,ename
from emp_li
order by convert(ename using gbk); //按照首个字母排序中文

分组

group by 字段为分组

//查询每个部门的最高薪水和最低薪水，要求没有部门的不算在内
select deptno,max(salary),min(salary)
from emp_li
where deptno is not null
group by deptno;

//查询每个部门的薪水总和和平均薪水，要求没有部门的不算在内
select deptno,sum(salary),avg(ifnull(salary,0)) avg_salary
from emp_li
where deptno is not null
group by deptno;

//按照职位分组，每个职位的最高薪水、最低薪水、人数总和，要求没有职位的不算在内
select position,max(salary),min(salary),count(*)
from emp_li
where position is not null
group by position;

select position,group_concat(ename)
from emp_li
where position is not null
group by position; //拼接组内ename所有数据输出

分组语句当中，select 后面的内容，要么被组函数包围，要么出现在 group by后面字段

having 对分组之后的数据再进行过滤

//查询平均薪水大于5000的部门和平均薪水，没有部门的不算在内
select deptno,avg(ifnull(salary,0)) avg_salary
from emp_li
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000;

//查询薪水总和大于20000的部门号和薪水总和，要求没有部门的不算
select deptno,sum(salary) total
from emp_li
where deptno is not null
group by deptno 
having sum(ifnull(salary,0))>20000
order by total asc; //根据薪水总和升序排序

总结

写法顺序

select->from->where->group by->having->order by

执行顺序

from(表) -> where(记录过滤)->group by(分组)->having(分组后过滤) ->select(结果集)->order by(排序)