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;当前数据库的日期和时间
