单表查询

最简单的查询语句

select * from dept

select * from emp;
– 显示部分列
select empno,ename,sal ,comm,deptno from emp
– 显示部分行 where
select empno,ename,sal ,comm,deptno from emp where sal<2500

– 别名
select empno 编号,ename 姓名,sal 工资 ,comm 补助,deptno 部门编号 from emp where sal<2500
– as可以省略 alias 别名
select empno as 编号,ename as 姓名,sal as 工资 ,comm as 补助,deptno as 部门编号 from emp where sal<2500

select empno ‘编 号’,ename “姓 名”,sal SALARY ,comm 补助,deptno 部门编号 from emp where sal<2500

– 算术运算
select empno,ename,sal ,comm,sal1.1, deptno from emp where sal<2500
– update emp set sal = sal1.1
select empno,ename,sal ,comm,sal*1.1, sal+comm ,deptno from emp where sal<2500 – ???

– 去重 distinct
select job from emp
select distinct job from emp
select job,deptno from emp

– 自动对后面所有列去重
select distinct job,deptno from emp

– 排序 order by
select * from emp – Oracle 并不一定按照主键排序 MySQL中按照主键排序
select * from emp order by empno – 指定按照主键排序
select * from emp order by empno asc – asc 升序排列 可以省略 默认升序
select * from emp order by sal desc – desc 降序排列 de-asc
select * from emp order by sal desc ,hiredate – 日期的大小是数值，不是早晚

– desc emp

where子句

– 最简单的条件? 关系运算符
select * from emp
select * from emp where deptno = 10
select * from emp where deptno < 10
select * from emp where deptno <= 10
select * from emp where deptno <> 10
select * from emp where deptno != 10
select * from emp where job = ‘CLERK’
select * from emp where job = ‘clerk’
select * from emp where binary job = ‘clerk’ – binary 区分大小写
select * from emp where hiredate < ‘1981-12-25’

– 逻辑运算符 and
select * from emp where sal >1500 and sal <3000
select * from emp where sal >1500 and sal <3000 order by sal
select * from emp where sal >=1500 and sal <=3000

select * from emp where sal between 1500 and 3000 – [1500,3000]

select * from emp where job = ‘CLERK’ and deptno = 20
select * from emp where job = ‘CLERK’ && deptno = 20
– 逻辑运算符 or
select * from emp where deptno = 10 or deptno = 20
select * from emp where deptno = 10 || deptno = 20

select * from emp where deptno in (10,20)
select * from emp where job in (‘CLERK’,‘MANAGER’,‘ANALYST’) order by job
select * from emp where job = ‘CLERK’ or deptno = 20

– 模糊匹配 % _
– % 代表任意多个字符 0，1，2 …
select * from emp where ename like ‘%A%’; – like
– _ 任意一个字符
select * from emp where ename like ‘__A%’
select * from emp where ename not like ‘__A%’

– null
select * from emp where comm is null

select * from emp where comm is not null

– 小括号 ()
select * from emp where job=‘SALESMAN’ or job=‘CLERK’ and sal>=1280 – 优先级别 先and 再or
select * from emp where (job=‘SALESMAN’ or job=‘CLERK’) and sal>=1280 – 改变运算顺序
select * from emp where job=‘SALESMAN’ or (job=‘CLERK’ and sal>=1280) – 提高可读性

单行函数

– 使用函数
select empno, ename,lower(ename),upper(ename),sal from emp

select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp

– 函数作用：提高select查询数据的能力
– 函数不会修改数据库表的数据

– 函数的分类
– 单行函数 lower(ename),upper(ename)
– 多行函数 max(sal),min(sal),count(sal),sum(sal),avg(sal)
– 区别1：是对单行还是多行进行操作，而不是指结果有几行
– 区别2：除了多行函数（max(sal),min(sal),count(sal),sum(sal),avg(sal)），都是单行函数

– 单行函数
– 字符串函数
select ename,length(ename),substring(ename,2,3) from emp – 索引从1开始

– 数值函数
select abs(-5),ceil(5.4),floor(5.6),round(3.14) from dual – dual 伪表 虚拟表 为了练习函数

select abs(-5),ceil(5.4),floor(5.6),round(3.14) – 如果没有where条件，from dual都可以省略

select 10/3,-10%-3,mod(-10,3) from dual

– 日期与时间函数
select * from student – 1999-12-23
select curdate(),curtime()
select now(),sysdate() ,sleep(3),now(),sysdate() from dual
insert into student values (null,‘zhangsan’,‘男’,23,now(),‘Java001’,‘zh@sxt.cn’)
insert into student values (null,‘zhangsan’,‘男’,23,sysdate(),‘Java001’,‘zh2@sxt.cn’)
desc student

– 流程函数
select empno,ename,sal,if(sal>=2500,‘high’,‘low’) as grade from emp order by grade – if–else
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp – if
select nullif(1,1),nullif(1,2)

多行函数

– 单行流程函数
select * from emp

– case 等值判断 switch
select empno,ename,job,
case job
when ‘CLERK’ then ‘店员’
when ‘SALESMAN’ then ‘销售’
when ‘MANAGER’ then ‘经理’
else ‘其他’
end ‘岗位’,
sal from emp

– case 区间判断 if-else if - else if—else
select empno,ename,sal ,
case
when sal<=1000 then ‘A’
when sal <=2000 then ‘B’
when sal <=3000 then ‘C’
else ‘D’
end 等级
from emp

– 其他函数
select database(),user(),version()
select password(“bjsxt”) – MySQL8中不再支持password()

– 多行函数
select * from emp
select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp
– 多行函数会自动的忽略null值
select max(comm),min(comm),count(comm),sum(comm ),avg(comm) from emp
– max() min() count() 针对所有数据类型，sum() avg() 只针对数值类型
select max(ename),min(ename),count(ename) from emp
select ename,sal from emp
select count(ename),max(sal) from emp

select * from emp
-- 统计表的记录数
select count(*) from emp
select count(*) from dept
--  只有count支持*
select count(*) from emp 

select 1 from dual
select 1 from emp 
-- 统计表的记录数（推荐使用）
select count(1) from emp

分组和having

– 统计各个部门的平均工资(只显示平均工资2000以上的)
select avg(sal) from emp – 1
select deptno from emp – 14
– 字段、多行函数不能共存，除非使用分组，除非这个字段出现子分组语句中
select deptno,avg(sal)
from emp

– 字段、多行函数不能共存，除非使用分组，除非这个字段出现子分组语句中
select deptno,avg(sal),count(1)
from emp
group by deptno
– where avg(sal) > 2000
having avg(sal)>2000
order by max(sal)

– select * from emp order by deptno

– 统计各个岗位的平均工资，除了MANAGER
select job,avg(sal),count(1)
from emp
where job != ‘MANAGER’
GROUP BY JOB
order by avg(sal) desc

select job,avg(sal)
from emp
group by job
having job != ‘MANAGER’
– order by avg(sal) desc
order by job

– where子句中不能出现多行函数

单表查询总结案例

– 统计各个部门的平均工资(只显示平均工资2000以上的)
select avg(sal) from emp – 1
select deptno from emp – 14
– 字段、多行函数不能共存，除非使用分组，除非这个字段出现子分组语句中
select deptno,avg(sal)
from emp

– 字段、多行函数不能共存，除非使用分组，除非这个字段出现子分组语句中
select deptno,avg(sal),count(1)
from emp
group by deptno
– where avg(sal) > 2000
having avg(sal)>2000
order by max(sal)

– select * from emp order by deptno

– 统计各个岗位的平均工资，除了MANAGER
select job,avg(sal),count(1)
from emp
where job != ‘MANAGER’
GROUP BY JOB
order by avg(sal) desc

select job,avg(sal)
from emp
group by job
having job != ‘MANAGER’
– order by avg(sal) desc
order by job

– where子句中不能出现多行函数