（五）Oracle 的 oracle 表查询
http://www.hechaku.com/Oracle/oracle_tables_chack.html
通过scott用户下的表来演示如何使用select语句，接下来对emp、dept、salgrade表结构进行解说。
emp 雇员表 字段名称 数据类型 是否为空 备注 -------- ----------- -------- -------- EMPNO NUMBER(4) 员工编号 ENAME VARCHAR2(10) Y 员工名称 JOB VARCHAR2(9) Y 职位 MGR NUMBER(4) Y 上级的编号 HIREDATE DATE Y 入职日期 SAL NUMBER(7,2) Y 月工资 COMM NUMBER(7,2) Y 奖金 DEPTNO NUMBER(2) Y 所属部门 ------------------------------------------- job字段： clerk 普员工 salesman 销售 manager 经理 analyst 分析师 president 总裁
dept 部门表 字段名称 数据类型 是否为空 备注 -------- ----------- -------- -------- DEPTNO NUMBER(2) 部门编号 DNAME VARCHAR2(14) Y 部门名称 LOC VARCHAR2(13) Y 部门所在地点 ------------------------------------------- DNAME字段： accounting 财务部 research 研发部 operations 业务部 salgrade 工资级别表 字段名称 数据类型 是否为空 备注 -------- --------- -------- -------- GRADE NUMBER Y 级别 LOSAL NUMBER Y 最低工资 HISAL NUMBER Y 最高工资
1、查看表结构 desc emp;
2、查询所有列
select * from dept; 备注:切忌动不动就用select *，使用*效率比较低，特别在大表中要注意。 3、set timing on/off; 打开显示操作时间的开关，在底部显示操作时间。 eg、sql> insert into tb_stu values('0001', 'zhangsan', 24); 1 row inserted executed in 0.015 seconds
4、insert into...select...表复制语句 语法：insert into table2(field1,field2,...) select value1,value2,... from table1
--创建tb_dept表
create table tb_dept ( deptno number(4) not null, dname varchar2(14), loc varchar2(13) ) --添加主键约束 alter table tb_dept add constraint tb_dept primary key (deptno);
--insert into...select...用法 insert into tb_dept (deptno, dname, loc) select a.deptno, a.dname, a.loc from dept a;
SQL> show linesize; linesize 80 SQL> select * from EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 SQL> set linesize 100; SQL> select * from EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 SQL> set pagesize 20; SQL> select * from EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 SQL> set linesize 120; SQL> select * from EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> desc dept; 名称 是否为空? 类型 ----------------------------------------------------------------- -------- -------------------------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> desc emp; 名称 是否为空? 类型 ----------------------------------------------------------------- -------- -------------------------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> set timing on; SQL> desc emp; 名称 是否为空? 类型 ----------------------------------------------------------------- -------- -------------------------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.03 SQL> select * from EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 已用时间: 00: 00: 00.10 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE 已用时间: 00: 00: 00.04 SQL> create table tb_dept 2 ( 3 deptno number(4) not null, 4 dname varchar2(14), 5 loc varchar2(13) 6 ); 表已创建。 已用时间: 00: 00: 01.37 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE TB_DEPT TABLE 已用时间: 00: 00: 00.00 SQL> desc tb_dept; 名称 是否为空? 类型 ----------------------------------------------------------------- -------- -------------------------------------------- DEPTNO NOT NULL NUMBER(4) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> alter table tb_dept add constraint tb_dept primary key (deptno); 表已更改。 已用时间: 00: 00: 00.43 SQL> desc tb_dept; 名称 是否为空? 类型 ----------------------------------------------------------------- -------- -------------------------------------------- DEPTNO NOT NULL NUMBER(4) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> select * from DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.01 SQL> insert into tb_dept(dept_no,dname,loc) select dept.deptno,dept.dname,dept.loc from dept; insert into tb_dept(dept_no,dname,loc) select dept.deptno,dept.dname,dept.loc from dept * 第 1 行出现错误: ORA-00904: "DEPT_NO": 标识符无效 已用时间: 00: 00: 00.01 SQL> insert into tb_dept(deptno,dname,loc) select dept.deptno,dept.dname,dept.loc from dept; 已创建4行。 已用时间: 00: 00: 00.14 SQL> select * from tb_dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.01 SQL>
5、统计 select count (*) from emp;
SQL> select count(*) from emp; COUNT(*) ---------- 14 已用时间: 00: 00: 00.02
6、查询指定列 select ename, sal, job, deptno from emp;
SQL> select ename,sal,job,deptno from emp; ENAME SAL JOB DEPTNO ---------- ---------- --------- ---------- SMITH 800 CLERK 20 ALLEN 1600 SALESMAN 30 WARD 1250 SALESMAN 30 JONES 2975 MANAGER 20 MARTIN 1250 SALESMAN 30 BLAKE 2850 MANAGER 30 CLARK 2450 MANAGER 10 SCOTT 3000 ANALYST 20 KING 5000 PRESIDENT 10 TURNER 1500 SALESMAN 30 ADAMS 1100 CLERK 20 JAMES 950 CLERK 30 FORD 3000 ANALYST 20 MILLER 1300 CLERK 10 已选择14行。 已用时间: 00: 00: 00.02 SQL>
7、如何取消重复行，select distinct deptno, job from emp;
SQL> select distinct deptno from emp; DEPTNO ---------- 30 20 10 已用时间: 00: 00: 00.05 SQL> select distinct deptno,job from emp; DEPTNO JOB ---------- --------- 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST 已选择9行。 已用时间: 00: 00: 00.02
8、查询smith所在部门，工作，薪水 select deptno, job, sal from emp where ename = '
SMITH'; 注意：oracle对内容的大小写是敏感的，所以ename='smith'和ename='SMITH'是不同的
SQL> select deptno,job,sal from emp where ename='smith'; 未选定行 已用时间: 00: 00: 00.06 SQL> select deptno,job,sal from emp where ename='SMITH'; DEPTNO JOB SAL ---------- --------- ---------- 20 CLERK 800 已用时间: 00: 00: 00.01 SQL>
9、nvl函数
格式为：nvl(string1, replace_with) 功能：如果string1为null，则nvl函数返回replace_with的值，否则返回string1的值。 注意事项：string1和replace_with必须为同一数据类型，除非显示的使用to_char函数。 eg、如何显示每个雇员的年工资？ select sal*13+nvl(comm, 0)*13 "年薪" , ename, comm from emp;
SQL> select sal*13+nvl(comm,0) "年薪",ename,comm from emp; 年薪 ENAME COMM ---------- ---------- ---------- 10400 SMITH 21100 ALLEN 300 16750 WARD 500 38675 JONES 17650 MARTIN 1400 37050 BLAKE 31850 CLARK 39000 SCOTT 65000 KING 19500 TURNER 0 14300 ADAMS 12350 JAMES 39000 FORD 16900 MILLER 已选择14行。 已用时间: 00: 00: 00.06 SQL>
10、使用列的别名 select ename "姓名", sal*12 as "年收入" from emp;
SQL> select ename "姓名"，sal*12 as "年收入" from emp; 姓名 年收入 ---------- ---------- SMITH 9600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 15600 已选择14行。 已用时间: 00: 00: 00.05 SQL>
11、如何处理null值 使用nvl函数来处理
12、如何连接字符串(||) select ename || ' is a ' || job from emp;
SQL> select ename || ' is a ' || job from emp; ENAME||'ISA'||JOB ------------------------- SMITH is a CLERK ALLEN is a SALESMAN WARD is a SALESMAN JONES is a MANAGER MARTIN is a SALESMAN BLAKE is a MANAGER CLARK is a MANAGER SCOTT is a ANALYST KING is a PRESIDENT TURNER is a SALESMAN ADAMS is a CLERK JAMES is a CLERK FORD is a ANALYST MILLER is a CLERK 已选择14行。 已用时间: 00: 00: 00.06 SQL>
13、使用where子句 问题：如何显示工资高于3000的员工？ select * from emp where sal > 3000; 问题：如何查找1982.1.1后入职的员工？ select ename,hiredate from emp where hiredate >'1-1 月-1982'; 问题：如何显示工资在2000到3000的员工？ select ename,sal from emp where sal>=2000 and sal<=3000;
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 已用时间: 00: 00: 00.08 SQL> select * from emp where sal > 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-11月-81 5000 10 已用时间: 00: 00: 00.02 SQL> select ename,hiredate from emp where hiredate>'1-1月-1982'; ENAME HIREDATE ---------- -------------- SCOTT 19-4月 -87 ADAMS 23-5月 -87 MILLER 23-1月 -82 已用时间: 00: 00: 00.03 SQL> select ename,sal from emp where sal>=2000 and sal<=3000; ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 FORD 3000 已用时间: 00: 00: 00.03 SQL>
14、如何使用like操作符 %：表示0到多个字符 _：表示任意单个字符 问题：如何显示首字符为s的员工姓名和工资？ select ename,sal from emp where ename like 's%'; 如何显示第三个字符为大写o的所有员工的姓名和工资？ select ename,sal from emp where ename like '__o%';
转义：scape
查询名字中包含 "_" 的员工
select * from emp where ename like '%\_%' escape '\'
若要转义单引号，需要两个单引号来转义
已用时间: 00: 00: 00.01 SQL> select ename,sal from emp where ename like 'S%'; ENAME SAL ---------- ---------- SMITH 800 SCOTT 3000 已用时间: 00: 00: 00.01 SQL> select ename,sal from emp where ename like '__O%'; ENAME SAL ---------- ---------- SCOTT 3000 已用时间: 00: 00: 00.01
15、在where条件中使用in 问题：如何显示empno为7844,7839,123,456的雇员情况？ select * from emp where empno in (7844, 7839, 123, 456);
SQL> select * from emp where empno in (7844,7839,456); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 已用时间: 00: 00: 00.05 SQL>
16、使用is null的操作符 问题：如何显示没有上级的雇员的情况？ 错误写法：select * from emp where mgr = ''; 正确写法：select * from emp where mgr is null;
SQL> select * from emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择14行。 已用时间: 00: 00: 00.08 SQL> select * from emp where mgr is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-11月-81 5000 10 已用时间: 00: 00: 00.01 SQL>
