oracle学习笔记之基本查询

3/7/2017来源:SQL技巧人气:434

把学习的Oracle的基本操作记录一下,同时也加深一下记忆

数据库,Oracle 11g,用户Scott

清屏 SQL> host cls SQL> --清屏 当前用户 SQL> --当前用户 SQL> show user USER 为 "SCOTT" 查询当前用户下的表 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE

查看emp表的表结构

SQL> desc emp 名称 是否为空? 类型 ----------------------------------------- -------- ---------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) EMPJOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)

设置行宽和列宽

SQL>set linesize 120 SQL>col ename for a8 SQL>col sal for 9999

在编写sql语句时尽量少使用 *,用列名替代,提高查询效率

c命令 用来修改上一次输入的错误命令,/ 表示执行上一条sql语句。如:

SQL> select empno , ename , sal , sal*12 年薪 2 form emp; form emp * 第 2 行出现错误: ORA-00923: 未找到要求的 FROM 关键字 SQL> c /form/from 2* from emp SQL> / EMPNO ENAME SAL 年薪 ---------- -------- ----- ---------- 7369 SMITH 800 9600 7499 ALLEN 1600 19200 7521 WARD 1250 15000 7566 JONES 2975 35700 7654 MARTIN 1250 15000 7698 BLAKE 2850 34200 7782 CLARK 2450 29400 7788 SCOTT 3000 36000 7839 KING 5000 60000 7844 TURNER 1500 18000 7876 ADAMS 1100 13200 EMPNO ENAME SAL 年薪 ---------- -------- ----- ---------- 7900 JAMES 950 11400 7902 FORD 3000 36000 7934 MILLER 1300 15600 已选择14行。 所有包含null的sql表达式都为null,null永远都不等于null,需要用is null代替,如:当查询员工的年收入(由月薪*12+奖金组成)时,可以看到没有奖金的员工的年收入查出来为空

SQL> select sal*12 , comm ,sal*12+comm from emp;

SAL*12 COMM SAL*12+COMM ---------- ---------- ----------- 9600 19200 300 19500 15000 500 15500 35700 15000 1400 16400 34200 29400 36000 60000 18000 0 18000 13200

解决办法,nvl函数,nvl函数会判断奖金(comm)值是否为空,如果为空comm的值则为0,否则为comm的值。如:

SQL> select sal*12 , comm ,sal*12+nvl(comm,0) from emp; SAL*12 COMM SAL*12+NVL(COMM,0) ---------- ---------- ------------------ 9600 9600 19200 300 19500 15000 500 15500 35700 35700 15000 1400 16400 34200 34200 29400 29400 36000 36000 60000 60000 18000 0 18000 13200 13200 concat字符串拼接函数 SQL> select concat('Hello ',' World') from dual; CONCAT('HELL ------------ Hello World SQL>--dual伪表 ‘||’用法,字符串拼接 SQL> select ename||'的薪水是'||sal 字符串 from emp; 字符串 ---------------------------------------------------------- SMITH的薪水是800 ALLEN的薪水是1600 WARD的薪水是1250 JONES的薪水是2975 修改系统默认日期格式。select * from v$nls_parameters,查询会话参数,可以看到日期对应的格式是 DD-MON-RR,修改其值即可,如 SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd'; 会话已更改。 SQL> select sysdate from dual; SYSDATE ---------- 2017-03-06 between … and … SQL> select * from emp where sal between 1000 and 2000; in 在集合中查询 SQL> select * from emp where deptno in (10,20); 如果在集合中含有null,不能使用not in,可以使用in SQL> select * from emp where deptno not in (10,20,null); 未选定行 SQL> select * from emp where deptno in (10,20,null); EMPNO ENAME EMPJOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- --------- ---------- ---------- ----- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7566 JONES MANAGER 7839 1981-04-02 2975 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10

like模糊查询

SQL> --查询名字以S开头的员工 SQL> select ename,empjob from emp where ename like 'S%'; ENAME EMPJOB -------- --------- SMITH CLERK SCOTT ANALYST SQL> --查询名字是四个字的员工 SQL> select ename , empjob from emp where ename like '____'; ENAME EMPJOB -------- --------- WARD SALESMAN KING PRESIDENT FORD ANALYST order by,作用域后面所有的列,先按照第一个列排序,如果相同再按照第二个排序,以此类推。此外,desc作用于靠它最近的列 SQL> select ename , empjob ,deptno , sal from emp order by deptno , sal desc; ENAME EMPJOB DEPTNO SAL -------- --------- ---------- ----- KING PRESIDENT 10 5000 CLARK MANAGER 10 2450 MILLER CLERK 10 1300 SCOTT ANALYST 20 3000 FORD ANALYST 20 3000 JONES MANAGER 20 2975 ADAMS CLERK 20 1100 SMITH CLERK 20 800 BLAKE MANAGER 30 2850 ALLEN SALESMAN 30 1600 TURNER SALESMAN 30 1500

a命令用于追加命令

SQL> select ename , empjob,deptno from emp order by deptno ; ENAME EMPJOB DEPTNO -------- --------- ---------- CLARK MANAGER 10 KING PRESIDENT 10 MILLER CLERK 10 JONES MANAGER 20 FORD ANALYST 20 ADAMS CLERK 20 SMITH CLERK 20 SCOTT ANALYST 20 WARD SALESMAN 30 TURNER SALESMAN 30 ALLEN SALESMAN 30 已选择14行。 SQL> a desc; 1* select ename , empjob,deptno from emp order by deptno desc SQL> / ENAME EMPJOB DEPTNO -------- --------- ---------- BLAKE MANAGER 30 TURNER SALESMAN 30 ALLEN SALESMAN 30 MARTIN SALESMAN 30 WARD SALESMAN 30 JAMES CLERK 30 SCOTT ANALYST 20 JONES MANAGER 20 SMITH CLERK 20 ADAMS CLERK 20 FORD ANALYST 20

函数:函数分为单行函数与分组函数,单行函数只对一行进行变换,每行只返回一个结果。分组函数作用于一组数据,并对一组数据返回一个值。

单行函数操作:

字符函数,lower()转小写,upper()转大写,initcap()首字母大写

SQL> select lower('Hello') 转小写 , upper('Hello') 转大写 , initcap('hello') 首字母大写 from dual; 转小写 转大写 首字母大写 ----- ----- ----- hello HELLO Hello 取子串函数,substr(a,b)从a中,第b位开始取。substr(a, b,c)从a中第b位开始取,取c位 SQL> select substr('Hello World',3) 子串 from dual; 子串 --------- llo World SQL> select substr('Hello World',3,3) 子串 from dual; 子 --- llo length()返回字符长度,lengthb()返回字节长度 SQL> select length('世界你好') 字符 , 2 lengthb('世界你好') 字节 from dual; 字符 字节 ---------- ---------- 4 8 instr(a,b)查找函数,在a中查找b,返回位置 SQL> select instr('Hello World','ll') 位置 from dual; 位置 ---------- 3 lpad左填充,rpad右填充 SQL> select lpad('abcd',10,'*') 左 , rpad('abcd',10,'*') 右 2 from dual; 左 右 ---------- ---------- ******abcd abcd****** trim()去掉前后指定的字符 SQL> select trim('H' from 'Hello WorldH') from dual; TRIM('H'FR ---------- ello World 四舍五入 SQL> select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2) from dual; ROUND(45.926,2) ROUND(45.926,1) ROUND(45.926,0) ROUND(45.926,-1) ROUND(45.926,-2) --------------- --------------- --------------- ---------------- ---------------- 45.93 45.9 46 50 0 SQL> select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1),trunc(45.926,-2) from dual; TRUNC(45.926,2) TRUNC(45.926,1) TRUNC(45.926,0) TRUNC(45.926,-1) TRUNC(45.926,-2) --------------- --------------- --------------- ---------------- ---------------- 45.92 45.9 45 40 0 格式化显示时间 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2017-03-06 15:05:12

多行函数

sum()函数计算总和 SQL> select sum(sal) from emp; SUM(SAL) ---------- 29025 count()计算个数 SQL> select count(*) from emp; COUNT(*) ---------- 14

组函数自动滤空,可以嵌套滤空函数来屏蔽滤空功能

多表查询

等值连接

SQL> select e.ename , e.empno ,e.sal , d.dname 2 from emp e ,dept d 3 where e.deptno=d.deptno; ENAME EMPNO SAL DNAME -------- ---------- ----- -------------- CLARK 7782 2450 ACCOUNTING KING 7839 5000 ACCOUNTING MILLER 7934 1300 ACCOUNTING JONES 7566 2975 RESEARCH FORD 7902 3000 RESEARCH ADAMS 7876 1100 RESEARCH SMITH 7369 800 RESEARCH SCOTT 7788 3000 RESEARCH WARD 7521 1250 SALES TURNER 7844 1500 SALES 右外连接 SQL> select d.deptno , d.dname , count(e.empno) from 2 emp e , dept d 3 where e.deptno(+)=d.deptno 4 group by d.deptno , d.dname; DEPTNO DNAME COUNT(E.EMPNO) ---------- -------------- -------------- 10 ACCOUNTING 3 40 OperaTIONS 0 20 RESEARCH 5 30 SALES 6 左外连接语法,e.deptno=d.deptno(+),符号左边的表将比包含。

暂时先记这么多吧。。。。。。。