oracle初学(-)

本文以Scott用户作为讲解的实例，安装oracle11g：

下载链接：

http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html

我在oracle官网注册了，可以直接使用：用户名：wenzibo259@126.com 密码：ohe5xAkl

我已经安装好了(Oracle11g 个人习惯设置的密码，实际中你们可以根据自己的习惯设定

:全局ID:oracle;Sys/system:Admin259;scott/Scott259)

下面在安装过程中，或安装后会出现常见的错误

 

如果网络环境发生改变，则修改 D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora,tnsnames.ora两个文件中的host 如果监听无法启动：regedit打开注册表 ImagePath D:\app\Administrator\product\11.2.0\dbhome_1\BIN\TNSLSNR.EXE(.exe有可能没有，该项如果没有则建立一份) 如果出现emca错误 emca -config dbcontrol db -repos drop,然后create重建一份

安装成功后启动sqlplus，有一些基本的命令，先来熟悉一下 Set linesize 300;// 每行300 Set pagesize 30;每页显示30行 ed a;可以保存sql语句；@a;执行 也可以使用 D:/demo.sql;可以使用 @d:demo Select * from tab;//查多少个表 Show user;// 显示当前用户 Conn 用户名/密码 [AS SYSDBA] //切换用户，中括号可选的 Select * from scott.emp;// 模式名或用户名.表名，方可查询其他用户的表 SHUTDOWN IMMEDIATE;// 关闭数据库实例 Select * from scott.emp;就会出现以下错误 第 1 行出现错误: ORA-01034: ORACLE not  available 进程 ID: 4152 会话 ID: 197 序列号: 34 此时使用/nolog 登陆 SQL> /nolog SP2-0042: 未知命令 "/nolog" - 其余行忽略。 使用startup启动数据库实例   可以使用windows命令只要使用Host命令 如：HOST COPY D:/demo.sql  D;/demo.txt;

 

Scott 作为案例：

部门表dept

 

No.	名称	类型	描述
1	DEPTNO	NUMBER(2)	部门编号，由2位数字组成
2	DNAME	VARCHAR2(14)	部门名称，由14个字符组成
3	LOC	VARCHAR2(13)	部门所在的位置，又13个字符组成

 

雇员表emp

 

No.	名称	类型	描述
1	EMPNO	NUMBER(4)	雇员编号，由4位数字组成
2	ENAME	VARCHAR2(10)	雇员名称
3	JOB	VARCHAR2(9)	雇员职位
4	MGR	NUMBER(4)	雇员对应的领导编号
5	HIREDATE	DATE	雇员雇佣的日期
6	SAL	NUMBER(7,2)	雇员的基本薪资，其中有5位整数，两位小数，一共7位
7	COMM	NUMBER(7,2)	雇员（销售人才）的佣金
8	DEPTNO	NUMBER(2)	雇员所在的部门编号

 

工资等级表salgrade

 

No.	名称	类型	描述
1	GRADE	NUMBER	工资等级
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

 

工资表bonus

 

No.	名称	类型	描述
1	ENAME	VARCHAR2(10)	雇员名称
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	雇员的工资
4	COMM	NUMBER	雇员的佣金

 

 

SQL 种类：   . DML (Data Manipulation  Language,数据操作语言) -- 用于建设或修改数据   . DDL(Data Definiton Language,数据库定义语言) -- 用于定义数据库结构，创建、删除、修改数据库对象   . DCL(Data Control Language , 数据库控制语言)--   用于定义数据用户的权限

简单查询：

语法：SELECT [DISTINCT] * | 字段名[别名] … FROM 表名称[别名]

范例：查询出每个雇员的编号、姓名、年薪（不包含佣金）

 

支持数学四则运算

如果员工每月的福利：200元住房补助+200元车补,年底多发一个月薪水

SELECT e.empno, e.ename,(e.sal + 200 + 200)*12 + e.sal income FROM EMP e;

范例：查询员工的职位

 

 

显示格式：

“雇员编号是：7369的雇员姓名是：SMITH，基本工资是：800，职位是：CLERK!”

 

 SELECT '雇员编号是：'|| e.empno ||'雇员姓名是：' ||e.ename || '基本工资是：'||e.sal || '职位是：'|| e.job||'!' 雇员信息 from EMP e;

限定查询：

SELECT [DISTINCT] * | 字段名[别名] … FROM 表名称[别名]

[WHERE 条件(s)]

常用的运算符：>/</<=/>=/!=(<>)/BETWEEN…AND/LIKE/ISNULL/IN/AND/OR/NOR;等

范例：查询工作大于1500的雇员信息

SELECT * FROM emp WHEREsal>1500;

查询办事员是CLERK的雇员信息

SELECT * FROM emp WHEREjob='CLERK';

注意：oracle是区分大小写的。

范例：查询职位是办事员，或者是销售人员的全部信息，并且要求雇员的工作大于1200；

SELECT * FROM emp WHERE(job='CLERK' OR job='SALESMEN') AND sal>1200;

范例：查询所有的职位不是办事员的雇员信息

SELECT * FROM emp WHERE NOTjob='CLERK';

SELECT * FROM emp WHERE job!='CLERK';

SELECT * FROM emp WHERE job<>'CLERK';

范例：范围 BETWEEN … AND（表示大于等于..小于等于），可操作数字、日期

查询工资在1500~3000的雇员信息: SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

查询1981年雇佣的雇员信息：SELECT * FROM emp WHERE hiredate BETWEEN '01-1月 -1981' AND '31-12月 -81';

范例：判断是否为空：IS(NOT) NULL 和0以及空字符串是不同的概念

查询奖金的雇员信息：SELECT * FROMemp WHERE comm IS NOT NULL;

SELECT * FROM emp WHERE NOTcomm IS NULL;

范例：制定范围的判断：(NOT)IN 操作符

查询雇员编号是：7369、7566、7799的雇员信息

SELECT * FROM emp WHERE empnoIN (7369,7566,7788);

注意 IN(7369,null)—正常查询，NOT IN(null)-不会返回结果

范例：模糊查询LIKE

 _ 单个字符

% 所有字符

查询雇员姓名中带有字母A的雇员信息

SELECT * FROM emp WHERE enameLIKE '%A%';

查询雇员姓名中第二字母以A开头的雇员信息

SELECT * FROM emp WHERE enameLIKE '_A%;'

SELECT * FROM emp WHERE enameLIKE '%%';--表示查询所有信息

范例：排序 ORDER BY 字段[ASC,DESC] [,字段 [ASC,DESC]] 默认是升序：ASC

查询所有雇员信息，工作由高到底排序，如果工资相同，按在雇员日期又早到晚排序

SELECT * FROM emp ORDER BYsal DESC, hiredate ASC;

排序在sql语句最后端

2 单行行数

单行函数主要分为以下5类：字符函数、数学函数、日期函数、转换函数、通用函数

2.1、字符函数

    字符函数的功能：主要是进行字符串的操作，下面给出几个函数：

    UPPER(字符串|列)：将输入的字符或列以大写的形式返回

    LOWER(字符串|列)：将输入的字符或列以小写的形式返回

    INITCAP(字符串|列)：首字母大写

    LENGTH(字符串|列)：求出字符串的长度

    INITCAP(字符串|列)：首字母大写

REPLACE(字符串|列)：进行字符串替换

SUBSTR(字符串|列, 开始点 [, 结束点])：字符串截取

 

SQL> SELECT * FROM empWHERE ename=UPPER('&str'); &str—表示输入变量

输入 str 的值:  smith

将雇员的名称首字母大写: SELECTINITCAP(ename) FROM emp;

查询姓名长度为5的雇员信息：SELECT LOWER(ename),LENGTH(ename) l FROM emp WHERE LENGTH(ename)=5;

范例：使用字符’_’替换雇员姓名’A’: SELECT REPLACE(ename,'A','_') FROM emp;

范例：字符串截取 SELECT ename,SUBSTR(ename, 0, 3) FROM emp;

语法一：SUBSTR(字符串|列,开始点) 从开始点截取到最后

语法二：SUBSTR(字符串|列,开始点,结束点) 从开始点截取到结束点最后(包括首尾0和1是一样的)

字符串截取后三位,可以使用负数

SELECT ename, SUBSTR(ename,-3) FROM emp;

2.2数字函数

ROUND(数字|列 [,保留几位小数]) – 四舍五入

TRUNC(数字|列 [,保留几位小数]) – 舍弃指定的内容

MOD(字符1,字符2) 对字符1和字符2求模即余数

2.3 日期函数

SELECT SYSDATE FROM dual;--查询当前日期

日期+数字—若干天后的日期

日期-数字—若干天前的日期

SELECT SYSDATE+3, SYSDATE+300FROM dual;

日期-日期—日期间的天数，大日期减去小日期

没有日期加日期

范例：求出雇员的雇佣日期距离今天的天数

SELECT ename,SYSDATE-hiredate FROM emp;

LAST_DAY(日期)：求出指定日期的最后一天

NEXT_DAY(日期,星期数)：求出下一个指定星期数的日期

ADD_MONTHS(日期，数字):求若干月之后的日期

MONTHS_BETWEEN(日期1，日期2);求出日期之间的月份

范例：求出雇员的雇佣日期距离今天的天数

开发中如果日期操作建议使用以上函数，避免闰年的问题。

2.4 转换函数（核心）

目前接触的oracle数据类型：number、varchar2、date

转换函数的主要功能，完成数据之间的转换，下面来看下三种转换函数

TO_CHAR(字符串 | 列， 格式字符串)：将日期或数字转换为字符串显示；

TO_DATE(字符串，格式字符串)：将字符串转换为日期显示

TO_NUMBER(字符串)：将字符串转换为数字显示

fm:表示前导0

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss')FROM dual;

2012-11-13 00:37:02

可以格式数字

SELECT TO_CHAR(132132465464,’999,999,999,999’) FROM dual;

SELECT TO_CHAR(132132465464,'L999,999,999,999.00') FROM dual;--9表示数字，L表示当前环境下的货币符号

SELECTTO_DATE(‘2012-01-02’, ‘yyyy-mm-dd’) FROM dual;--显示 02-1月 -12

TO_NUMBER一般不用，用和不用一样的

通用函数之oracle特色函数

NVL()，DECODE(数值|列，显示值1，判断值1，显示值2，判断值2，…)—类似if…else

范例：查询雇佣的年薪

SELECTsal, comm, (sal+NVL(comm,0))*12 income, NVL(comm,0) FROM emp;

SELECTjob, DECODE(job, 'CLERK','办事员','SALESMAN','销售','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') FROM emp;

范例：显示的所有员工姓名、加入公司的年份和月份，雇佣日期所在月排序，若月份相同个年份排在最前面

 SELECTename, TO_CHAR(hiredate, 'yyyy') year, TO_CHAR(hiredate, 'mm') months,TO_CHAR(hiredate, 'dd') day FROM emp ORDER BY months,year