select empno,ename,sal 月薪,comm as 奖金 ,deptno as 部门,sal*12 as 年薪,sal*12+comm,sal*12+nvl(comm,0) from emp ;
select * from emp where comm is null;--null表示此字符串是用来填值的
select * from emp where deptno=30 and comm is not null;--查询20部门奖金不为空的人
select * from emp where ename like 'M%';--找到名字以M开头的员工
select * from emp where ename like '%M%';--找到名字里带m的员工, %后面有大于等于0个字符
select * from emp where ename like '_M%';--找到名字里第二个字母一定为M的员工,前面若有n个下划线则查询第n+1个字母为M的员工
--查询部门号不为30的员工
select * from emp where deptno !=30;
select 8 from emp where deptno <> 30;
select * from dept;
select * from emp where deptno = 30;
select deptno from dept where dname = 'SALES';--找到销售部的编号
--找到销售部门所有的员工
select * from emp where deptno =
(select deptno from dept where dname='SALES');
--找到销售部和财务部所有的员工
--1
select * from emp where
deptno = (select deptno from dept where dname='SALES')
or
deptno = (select deptno from dept where dname='ACCOUNTTNG');
--2
select * from emp where
deptno = (select deptno from dept where dname in('SALES','ACCOUNTTNG'));--注:单行子查询返回多个行
-- 1.等值/不等值查询 =; <>,!=
--2.范围查询 >= ,<=, int/not ,in is null/is not null;
--3.模糊查询 like
select * from emp where comm is null;--null表示此字符串是用来填值的
select * from emp where deptno=30 and comm is not null;--查询20部门奖金不为空的人
select * from emp where ename like 'M%';--找到名字以M开头的员工
select * from emp where ename like '%M%';--找到名字里带m的员工, %后面有大于等于0个字符
select * from emp where ename like '_M%';--找到名字里第二个字母一定为M的员工,前面若有n个下划线则查询第n+1个字母为M的员工
--查询部门号不为30的员工
select * from emp where deptno !=30;
select 8 from emp where deptno <> 30;
select * from dept;
select * from emp where deptno = 30;
select deptno from dept where dname = 'SALES';--找到销售部的编号
--找到销售部门所有的员工
select * from emp where deptno =
(select deptno from dept where dname='SALES');
--找到销售部和财务部所有的员工
--1
select * from emp where
deptno = (select deptno from dept where dname='SALES')
or
deptno = (select deptno from dept where dname='ACCOUNTTNG');
--2
select * from emp where
deptno = (select deptno from dept where dname in('SALES','ACCOUNTTNG'));--注:单行子查询返回多个行
-- 1.等值/不等值查询 =; <>,!=
--2.范围查询 >= ,<=, int/not ,in is null/is not null;
--3.模糊查询 like
select * from emp order by deptno asc ,sal desc,empno;--order by 列名 acc,列名2 desc,列名(没有指明排序规则默认升序排列)
select * from emp order by comm desc,empno asc;--按照字段排序,如何字段值为(null)的问题
select sysdate 今天,saydate-1 昨天,syadate+1 明天 from dual;--伪表
select * from emp;
select * from emp order by comm desc,empno asc;--按照字段排序,如何字段值为(null)的问题
select sysdate 今天,saydate-1 昨天,syadate+1 明天 from dual;--伪表
select * from emp;