Oracle 表基本查询 - where \ like \ or

2020-10-23  本文已影响0人  Down1

  表基本查询用 PL/SQL 的 scott 用户自带的四个表,选用两个表进行练习。


一、简单查询

desc dept;
select * from dept;
select userId from users;
set timing on;
create table users(userid varchar2(20),username varchar2(30),userpssvarchar2(30));//先创建一个users表
insert into users values('a001','哈哈哈哈哈啊飒飒','qwert12345');//插入一条数据
insert into users values(userid,username,userpss) select * from users;//复制刚刚插入的数据,可以多次复制
select count (*) from users;
select distinct deptno,job from emp;  //取消deptno 和 Job 所出现的重复行
select sal,job,deptno from emp where ename = 'SMITH';
select sal*12,ename from emp;
select sal*12 "年工资",ename from emp;
select sal*12+nvl(comm,0)*12 "年工资",ename,comm from emp;

二、where语句

select ename,sal from emp where sal>3000;
select ename,hiredate from emp where hiredate>='1-1月-1980';
select ename,sal from emp where sal>=2000 and sal<2500;

三、like 操作符

% :表示任意0个到多个字符
_ :表示任意单个字符

select ename,sal from emp where ename like 'S%';
select ename,sal from emp where ename like '__O%';

四、where语句中 in 表达

select * from emp where empno in (7499,7521,7788,7900);

五、is null 语句

select * from emp where MGR is null;

六、逻辑符号

或:or
和:and

select * from emp where (sal>500 or job='MANGER') and ename like 'J%';

七、order by 语句

升序,由低到高:order by 默认排序 或 asc
降序,由高到低:desc

select * from emp order by sal;
select * from emp order by deptno asc,sal desc;
select * from emp order by deptno,sal desc,hiredate desc;
select ename,sal*12 as "年薪" from emp order by "年薪";
上一篇下一篇

猜你喜欢

热点阅读