基本查询语句:
as 别名名命
|| 拼接
distinct 去重
select empno as 员工编号,mgr as mgr from emp; 别名命名,as可以省略
select distinct job from emp; 必须所有的列全部相同才能去重
select ‘部门编号是:’ || empno || ‘。员工姓名为:’ || ename || ‘并且其工资为:’ || sal from emp; --拼接用||
四则运算:
只能数值类型应用,不建议应用,影响速度
限定查询语句:
or 或
and 和
in not in
between。。and
not取反
模糊查询:
like,
% 匹配任意长度的内容,
—— 匹配一个长度的内容
!= 不等于
<>等于!=
select * from emp where sal>1500;
select * from emp where comm is not null;–奖金不为空
select * from emp where sal > 1500 and comm is not null;–工资大于1500且奖金不为空
select * from emp where sal > 1500 or comm is not null;–工资大于1500且奖金不为空
select * from emp where sal <= 1500 and comm is null;–
select * from emp where not(sal >1500 or comm is not null);–与上条相反
select * from emp where sal > 1500 and sal<3000;
select * from emp where sal between 1500 and 3000;–包含边界
select * from emp where hiredate between to_date(‘1981/1/1’,‘yy/MM/dd’) and to_date(‘1981/12/31’,‘yy-MM-dd’);
select * from emp
select * from emp where ename = ‘SMITH’;–语句不区分大小写,所查询的值区分大小写。
select * from emp where sal = 1500;
select * from emp where empno = 7369 or empno = 7499;–查询储empon7369或7499的人
select * from emp where empno in (7369,7499);–empon满足in中的人
select * from emp where ename like ‘M%’;–查询员工姓名第二字带m的人
select * from emp where ename like '%M’–查询员工姓名倒数第二个字为m的人
select * from emp where ename like ‘%’;
select * from emp where empno <> 7369;–不等于
select * from emp where empno != 7369;–不等于
排序:
在where 后order by
asc 升序 默认为升序
decs 降序
select * from emp where sal > 1500 order by sal asc;–工资大于1500升序排序
select * from emp where sal > 1500 order by sal;–同上
select * from emp where sal > 1500 order by sal desc;–工资大于1500降序排序
select * from emp where sal > 1500 order by sal desc,hiredate desc;–工资大于1500降序排序,当工资相同时,按照入职日期降序排序
oracle数据库知识(一) 基本语句,限定查询,模糊查询,排序。
最新推荐文章于 2021-04-15 04:26:19 发布