ORACLE 数据库常用sql语句

sqlplus scott/mima@orcl 用scott登录
select username,account_status FROM dba_users;查看用户
create user stz identified by oracle;创建用户密码
grant connect to xxx;赋予链接权限
grant resource to xxx;赋予建表权限
select*from emp;查询某公司员工信息
select empno,sal from emp;查询所有员工的 empno，sal。
select sal,job from emp where sal>2000;查询sal大于2000的员工信息。
desc emp;查看表结构
select 10*50 from dual;基本算术运算 
select round((20+8)/3,2) from dual;保留两位小数
select mod(100,21) from dual;求余
select empno,ename,(sal+nvl(comm,0))*12 from emp;用NVL函数来计算包含奖金的年新 nvl (空值，实际的值）
select empno,ename,(sal+nvl(comm,0))*12 as salary from emp;列别名
select empno AS "编号",ename AS "姓名",(sal+nvl(comm,0))*12 as salary from emp;中文列别名
select 'ksajdskjaskd' as "sdasda" from dual;字符串列别名。
select ename ||'是'||hiredate||'进入公司的' from emp;字符串连接
select distinct deptno from emp;   distinct去重
select*from emp where deptno=10;
select*from emp where sal between 800 and 2000;
select*from emp where comm in(300,500);
select*from emp where job like '_L%_';
 
select*from emp where sal>1500 or job like '_A%';
or 如果其中一个条件为真，则返回 TRUE
AND如果两个条件都为真，则返回 TRUE 
NOT 如果条件为假，则返回 TRUE 
select*from emp
  2  where deptno=10
  3  and sal>all(select sal from emp where deptno=20);部门为10的大于部门为20的最大工资值
  select *from emp where COMM IS NOT NULL;查询是不是空或非空；
  select*from emp  order by deptno,sal desc;排序。desc降序
  
select lower('ASSsdsadDS') FROM DUAL;将字符串转换成小写  upper大写
select initcap('ssd sdsa  dsdDS') FROM DUAL;字符串中每一个首字母改成大写
select table_name ,owner from dba_tables where table_name=upper('emp');或 where lower(table_name)='emp';
select concat('asd ','sdad') from dual;连接两个特定字符
select substr('tehis sjdj sajdj', 5) from dual；截取字符串的某一部分
select length('shjadhjsdj') from dual;返回字符串的长度
select INSTR('sdjksjsd sdda' ,'s') from dual;字符在字符串中的位置
select rpad(ename,6,'*') from scott.emp;用*补齐字符串中第6位后的
select ltrim('  sdsad sd s   ') from dual;去掉字符串左边rtrim右边 trim 左右两边
select trunc (12398.88,2) from dual;截取小数点右边的2位小数
select power(2,4) from dual;2的4次方
select sign(-22) from dual;正值返回1 负值返回—1 ，0返回0
 select sysdate from dual;当前数据库的日期和时间