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;
表别名
与列别名相似
//使用限定表名
select e.ename from emp_li e;Null空值
//查询员工的月薪
select ename,salary+bonus mouth_salary,salary,bonus
from emp_li;注意
null值参与算数运算时结果均为nullnull参与连接操作结果也为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(排序)
最后更新于