oracle where ( ),oracle数据库(where语句)

--取消用户权限

revoke connect,resource from scott

--分配权限

grant dba to scott

--删除用户

drop user scott cascade

--取消用户权限

revoke resource,connect from scott

--设置用户权限

grant connect,resource to scott

--创建用户

create user scott identified by tiger

--创建表格

create table emp;

--删除表格

drop table dept;

--select语句

--从部门选择

select * from dept;

--从员工表选择

select * from emp;

--从员工表选择员工编号，员工姓名，补助消息

select empno,ename,comm from emp;

--对薪水使用算数表达式

select sal,sal*1.1 from emp;

--对薪水使用算术表达式

select sal,sal*1.5+100000 from emp;

--使用字段别名

select empno 编号，ename 姓名，sal 月薪,sal*12+12000 年收入 from emp;

--使用 as加字段别名 as可有可无

select empno as 编号，ename as 姓名， sal as 月薪 from emp;

--别名需要双引号吗？空格，大小写原样输出

select empno

"编 号"，ename

"姓 名" from emp;

--除了使用双引号外(2,3种)，其他都使用单引号''

select * from emp;

select distinct sal from emp;

select distinct job from emp;

select distinct hiredate from emp;

--order by

select * from emp;

--按照工资升序排列

select * from emp order by sal;

--按照工资降序排列

select * from emp order by sal desc;

--按照工资升序排列

select * from emp order by sal asc;

--工资相同，按照时间进行排序

select * from emp order by sal desc ,hiredate asc;

select * from emp;

--选择员工编号是7698的员工

select * from emp where empno=7369

select * from emp where job='CLERK';

select * from emp where job='clerk';--字符数据区分大小写

select * from emp where JoB='CLERK';--关键字，表明，列名不区分大小写 ，建议小写

select * from emp where job != 'CLERK'; //不等于

select * from emp where job <> 'CLERK'; //不等于

select * from emp where hiredate =

'17-11月-1981';//默认的日期字符串格式

select * from emp where hiredate <

'17-11月-1981';//比指定日期小的日期，如17-11月-1980

select * from emp where sal>1500 and

sal<3000;//工资在1500到3000之间不包含1500和3000

select * from emp where sal between 1500 and

3000;//工资在1500到3000之间包含1500和3000

--岗位是CLERK或者MANAGER

select * from emp where job = 'CLERK' or job= 'MANAGER';

select * from emp where job in('CLERK','MANAGER');

select * from emp where job not in('CLERK','MANAGER');

--姓名以S开头

select * from emp order by ename;

select * from emp where ename like 'S%';

select * from emp where ename like '%K%';

select * from emp where ename like '_L%' ;//第二个字母是大写L的名字

select * from emp where ename not like '_L%';

select * from emp where ename like '%\_%' escape '\'

;//模糊查询名字中包含_线的

select * from emp where ename like '%A_%' escape

'A';//任意字符都可以做转移符，需要escape标记

--没有补助的

select * from emp where comm=0;

select * from emp where comm is null;

select * from emp where comm is not null;

--not !!! not in ,not like,is not null

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 * from emp where job='SALESMAN' or (job= 'CLERK' and

sal>=1280;//增加可读性