本文以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