oracle基础知识1_单表查询

 

本机安装成功后,访问链接: http://localhost:5560/isqlplus/

 

 

前提: 本机win7下安装oracle10g_32, 安装过程见

oracle 10g 安装 和 常遇到的错误 (http://chengjianxiaoxue.iteye.com/blog/2074281)

安装后仅限于学习个人学习oracle之用

 

0 oracle数据库和oracle实例的区别:

 

   a) oracle服务器 = oracle数据库 + oracle实例

   b) oracle数据库: 用于实际存放数据的文件,存储在硬盘中这些文件组织在一起形成的逻辑整体叫做数据库

   c) oracle实例: 位于物理内存的数据结构中，  = 一个共享内存池 + 多个后台进程组成

   d) 用户存取数据库中的数据(硬盘文件),必须通过oracle实例操作。

   e) 任何时刻，一个实例只能与一个数据库关联；大多数情况下，一个数据库上只有一个实例对其操作

  

 

1 表空间和数据文件:

 

   a) 数据文件:  1) 用于存储物理数据； 2）后缀是 .dbf；

   b) 表空间 :  是一个逻辑概念，表名称  eg: table user;

   c) 一个user表可以有多个数据文件，eg: USER01.dbf,  USER02.dbf; 是一对多的关系

 

 

2 本机安装oracle后，将开机自动启动修改成手动启动(如果安装后是为了学习之用，节省本机资源)

 

 

 

3 学习中需要操作的表:

 

 4 基本指令:

 

    1 显示当前用户: show user   结果: USER 为 "SCOTT"

    2 显示当前用户的表: select * from tab; (区别于mysql的 show tables;)

 

TNAME TABTYPE CLUSTERID

DEPT	TABLE
EMP	TABLE
BONUS	TABLE
SALGRADE	TABLE

 

    3  显示表结构(和mysql写法一样) : 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)

 

    4 查询表数据: select  columnname1, columnname2... from tablename (建议查询语句中用字段名称取代*，在oracle9i低版本的oracle查询时，使用*作为查询会耗费oracle判断时间，效率会降低)

 

   5 sql语句中的null:

     a) 包含null的表达式都为null

     b) null != null

     c) 如果集合中含有null,不能使用not in,可以使用 in

     eg1: 查询emp表的年薪:

select empno, ename,sal, comm ,sal*12 + comm as 年薪  from emp; 结果会发现，在comm(奖金)为null时，年薪结果为null;

EMPNO ENAME SAL COMM 年薪

7369	SMITH	800
7499	ALLEN	1600	300	19500
7521	WARD	1250	500	15500
7566	JONES	2975
7654	MARTIN	1250	1400	16400

 

 改进方式是使用 nal(a,b)函数

(NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL)

 

select empno, ename,sal, comm ,sal*12 + nvl(comm,0) as 年薪  from emp;

EMPNO ENAME SAL COMM 年薪

7369	SMITH	800		9600
7499	ALLEN	1600	300	19500
7521	WARD	1250	500	15500
7566	JONES	2975		35700
7654	MARTIN	1250	1400	16400

 

 

   eg2: 查询奖金为null的员工:

select empno, ename,sal from emp where comm is null;    不能用 = 和 !=，用 is null / is not null取代

 

  eg3: select * from emp where deptno in (10,20,null);

 

  6  字符串和日期   a) 这两者需要放在单引号内  b) 对于字符串，大小写敏感区分  c) 日期格式敏感，默认格式为 DD-MON-RR(日-月-年);

  eg:

select empno, ename,sal from emp where ename = 'KINg';   查询不到结果

select empno, ename,sal from emp where ename = 'KING';   查询出结果

select empno, ename,sal from emp where hiredate = '17-11月-81';  查询的出结果

 

  7 查看系统默认日期类型

 select sysdate from dual;

SYSDATE

04-6月 -14

 

 

  8  between and :  小值在前，大值在后。

 

  9  in/not in(num1,num2...)

select * from emp where deptno not in (10,20);

  10  like   %代表多个长度， _代表一个长度

    eg1: 查询名字是四个字的员工

select * from emp where ename like '____';  (要用单引号，引号内内容为4个_)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7521	WARD	SALESMAN	7698	22-2月 -81	1250	500	30
7839	KING	PRESIDENT		17-11月-81	5000		10
7902	FORD	ANALYST	7566	03-12月-81	3000		20

  

   eg2: 查询名称中含有_的员工信息(取_字符本身，而不是取_在模糊查询的含义):

insert into emp(empno,ename,sal,deptno) values(1001,'Tom_ABCD',6000,10);

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

1001	Tom_ABCD				6000		10

 

  11 order by 后面+ 列名/表达式/别名

  eg1: select ename, sal*12 as 年薪 from emp  order by 年薪

  eg2: select ename, deptno,sal*12 as 年薪 from emp  order by deptno, sal;  先按照第一列排序，如果相同，再按照第二列排序，以此类推

  eg: 降序排列时，如果有空值，如何将空值排在后面(按照人们的习惯来显示数据，office好用就在于按照人的使用习惯排列按钮让人去点)

 

 select * from emp  order by comm desc nulls last;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7654	MARTIN	SALESMAN	7698	28-9月 -81	1250	1400	30
7521	WARD	SALESMAN	7698	22-2月 -81	1250	500	30
7499	ALLEN	SALESMAN	7698	20-2月 -81	1600	300	30
7844	TURNER	SALESMAN	7698	08-9月 -81	1500	0	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

 

 

 

 

 

 

12 DISTINCT 排除重复，如果放在多个字段前，那么这多个字段是作为一个比较条件来进行排重

 

select DISTINCT deptno,job from emp;

DEPTNO JOB

20	CLERK
30	SALESMAN
20	MANAGER
30	CLERK
10	PRESIDENT
30	MANAGER
10	CLERK
10
10	MANAGER
20	ANALYST