sql语句

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 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;






展开阅读全文

没有更多推荐了,返回首页