ORACLE.docx
《ORACLE.docx》由会员分享，可在线阅读，更多相关《ORACLE.docx（12页珍藏版）》请在冰点文库上搜索。
ORACLE
ORACLE
1.用sqlplusw
2.主机字符串：
表示选择数据库的名称，如果不输，表示默认的数据库服务。
3.可用命令：
a）Setlinesize300;设行的长度为300
b）Setpagesize50;每页显示50条
c）删除数据库用户：
dropuserUSERNAMEcascade
d）Ed文件名用记事本进行编辑
e）@文件名的方式执行命令，也可以通过@找到磁盘上的文件，执行的时候要指定文件的路径：
@d:
\demo.txt，也可以不要\，如果后缀是.sql可以不要输入后缀。
f）在sqlplusw也可以使用其他用户连接。
如：
sys或system：
conn用户名/密码[assysdba|sysoper]，sys是超级管理员，必须加上assysdba等。
g）在sys下，如果想访问其他用户下的表的话，则必须加上用户名如：
select*fromscott.emp;
h）Showuser，显示当前正在连接的用户是什么。
i）Select*fromtab;可以得到这个用户下所有的表。
j）Desc表名称;可以查看这个表的结构。
其中number（7,2）表示：
小数位有2位，整数位有5位，共7位。
k）/表示重复执行上一行命令。
4.SQL语句
a）首先，了解scott用户下的表
i.Emp（雇员表）
雇员表（emp）
No
字段
类型
描述
1
EMPNO
NUMBER（4）
雇员编号
2
ENAME
VARCHAR2（10）
雇员名称
3
JOB
VARCHAR2（9）
工作职位
4
MGR
NUMBER（4）
雇员的领导编号
5
HIREDATE
DATE
雇佣日期
6
SAL
NUMBER（7,2）
表示工资
7
COMM
NUMBER（7,2）
表示奖金，或者称为佣金
8
DEPTNO
NUMBER
（2）
部门编号
ii.部门表（dept）
iii.工资等级表（salgrade），用此表表示一个工资的等级。
iv.奖金表（bonus），来用表示雇员的工资和奖金。
b）SQL（StructuredQueryLanguage结构查询语句）。
i.DML：
DataManipulationLanguage，数据库操作语言。
用于检索或者修改数据。
ii.DDL：
DataDefinitionLanguage，数据定义语言。
用于定义数据的结构，如创建、修改、或者删除数据库对象。
iii.DCL：
DataControlLanguage，数据控制语言。
用于定义数据库用户的权限。
c）select语句
i.起别名：
selectempno职员编号fromemp;
ii.去重复项：
distinct。
Selectdistinct*fromemp;但，在消除重复列的时候，必须保证所有列都重复才能消除（即两条数据是守全相同的，包括主键）。
iii.要实现：
“编号是7369的雇员，姓名是：
SMITH，工作是：
CLERK”要使用“||”表示，如果要加入一些显示信息的话，所有的其他的固定的信息要使用“’”括起来。
如：
select'编号是：
'||empno||'的雇员，姓名是：
'||ename||'，工作是：
'||jobfromemp;
结果为：
编号是：
7369的雇员，姓名是：
SMITH，工作是：
CLERK。
iv.在查询中也可以使用四则运算功能，例如：
要求出雇员的姓名及年薪。
如：
selectename,sal*12incomefromemp;程序中可以支持“+、-、*、/”的语句，要有优先顺序，先乘除后加减。
d）限定查询（where语句）
i.Select{distinct}*|具体的列别名from表名{where条件}
ii.例如：
工资>2000，select*fromempwheresal>2000;
iii.空的表示：
ISNULL。
不为空的表示：
ISNOTNULL。
iv.NOT,AND,OR,BETWEENAND的用法
v.例：
查询1981年雇佣的雇员
1981年1月1日~1981年12月31日之间雇佣的雇员
日期表示时要加入“’”。
select*fromempwherehiredatebetween'1-1月-81'and'31-12月-81';
vi.Oracle中是对大小写敏感的，所以在查询中要注意大小写。
如：
查姓名为：
SMITH的雇员。
Select*fromempwhereename=’smith’;结果没有数据。
Select*fromempwhereename=’SMITH’;即可查出。
vii.IN操作符，语法：
字段IN（值1，值2，…，值n）;
select*fromempwhereempnoin（7369,7499,7521）;
可用NOTIN取反。
IN操作符不光可以用在数字上，也可以用在字符串的信息上。
如：
要求查询出姓名是SMITH、ALLEN、KING的信息。
select*fromempwhereenamein（'SMITH','ALLEN','KING'）;
viii.LIKE通配符：
%：
可以匹配任意长度的内容。
_：
可以匹配一个长度的内容。
查询出姓名第二位字母为“M”的雇员信息：
select*fromempwhereenamelike'_M%';
姓名中有“M”的雇员信息：
select*fromempwhereenamelike'%M%';
如果在使用LIKE的时候没有指定关键字，则表示查询全部。
select*fromempwhereenamelike'%%';查询出了所有记录。
ix.>,>=,=,<,<=等计算的符号。
不等于符号：
“<>”、“!
=”两种表示方法。
e）对结果进行排序（orderby子句）
i.ASC|DESC（升序|降序）默认升序
ii.select*fromemporderbysal;
iii.当有两个排序条件时，先按前一个，若前一个条件里的数据相等，再按第二个。
select*fromemporderbysaldesc,hiredate;这里就是当工资相等时，按雇佣日期排序。
f）单行函数
数据库系统中，每个数据库之间最大的区别点就在于函数的支持上，使用函数可以完成一系列的操作功能。
i.语法：
function_name（column|expression,[arg1,arg2,…]）
（一）function_name:
函数名称
（二）column:
数据库列名
（三）expression:
字符串或计算表达式
（四）arg1,arg2:
函数中使用参数
单行函数分类：
●字符函数：
接受字符输入并且返回字符或数值
是专门处理字符的，可将大写转成小写，还能求出字符长度。
1.将小写转换为大写：
selectupper（'smith'）fromdual（dual是什么？
）;
2.SELECT*FROMempWHEREename=UPPER（'smith'）;
3.转小写：
SELECTLOWER（'HELLOWORLD'）FROMdual;
4.将单词首字母变大写：
SELECTINITCAP（'HELLOWORLD'）FROMdual;
5.字符串除了可以使用”||”连接之外，还可以使用CONCAT（）函数进行连接。
不如：
“||”好用。
SELECTCONCAT（'HELLO','WORLD'）FROMdual;
6.字符串截取：
substr（）
7.字符串长度：
length（）
8.内容替换：
replace（）
SELECTsubstr（'hello',1,3）截取字符串,
length（'hello'）字符串长度,
replace（'hello','l','x'）字符串替换
FROMdual;
9.Oracle中substr从0或从1开始效果是一样的。
范例：
要求显示所有雇员的姓名及姓名的后三位。
a）SELECTename姓名,substr（ename,length（ename）-2）后三位FROMemp;
b）SELECTename姓名,substr（ename,-3）后三位FROMemp;（只在Oracle中可用，其他数据库不明）
●数值函数：
接受数值输入并返回数值
1.四舍五入：
ROUND（）
a）SELECTROUND（789.536）FROMdual;
b）保留两位小数：
SELECTROUND（789.536,2）FROMdual;
c）可直接对整数进行四舍五入：
SELECTROUND（789.536,-2）FROMdual;
2.截断小数位：
TRUNC（）
a）与ROUND（）不同，在TRUNC（）操作中，不会保留任何小数，而且小数点也不会执行四舍五入的操作。
b）SELECTTRUNC（789.536）FROMDUAL;结果：
789
c）也可以保留小数点的位数：
SELECTTRUNC（789.536,2）FROMDUAL;结果：
789.53
d）SELECTTRUNC（789.536,-2）FROMDUAL;结果：
700
3.取余（取模）：
MOD
a）SELECTMOD（10,3）FROMDUAL;结果：
1
●日期函数：
接受日期型数据进行操作
a）日期-数字=日期
日期+数字=日期
日期-日期=数字（天数）
b）显示10部门雇员进入公司的星期数
要想完成此操作，则应先知道当前日期，在Oracle中可以通过以下操作求出当前日期，用sysdate表示。
SELECTSYSDATEFROMDUAL;
SELECTempno,ename,ROUND（SYSDATE-hiredate）/7FROMemp;
c）MONTHS_BETWEEN（）:
求出给写日期范围的月数
ADD_MONTH（）:
在指定日期上加上指定月数，求出之后的日期
NEXT_DAY（）:
下一个的今天是那一个日期
下一个星期一是是几号：
selectnext_day（sysdate,'星期一'）fromdual;
LAST_DAY（）:
求出给定日期的最后一天日期
本月的最后一天：
selectlast_day（sysdate）fromdual;
d）
●转换函数：
从一种数据类型转换为另一种数据类型
TO_CHAR（）：
转换成字符串
将日期拆分：
selectempno,ename,TO_CHAR（hiredate,'yyyy'）year,
TO_CHAR（hiredate,'mm'）month,
TO_CHAR（hiredate,'dd'）day
fromemp;
结果：
按中国人的显示方式：
selectempno,ename,TO_CHAR（hiredate,'yyyy-mm-dd'）fromemp;
结果：
从运行结果可以发现，如果是5月，则会用05表示，这个0称为前导0，如果不想显示，可以使用fm去掉这些0。
selectempno,ename,TO_CHAR（hiredate,'fmyyyy-mm-dd'）fromemp;
显示工资时，按中国人的习惯，用‘，’分割，遂，可用TO_CHAR（）进行格式化;
⏹9:
表示一位数字。
selectempno,ename,TO_CHAR（sal,'99,999'）fromemp;
⏹$:
表示美元
⏹L：
Local，表示用本地语言进行金额显示。
selectTO_CHAR（sal,'L99,999'）fromemp;
TO_NUMBER（）：
转换成数字
⏹把字符串变成数字相加：
selectto_number（'123'）+to_number（'123'）fromdual;
TO_DATE（）：
将字符串转换成日期
selectto_date（'2011-03-14','yyyy-mm-dd'）fromdual;
●通用函数：
NVL函数、DECODE函数
⏹NVL：
可以将一个指定的null值变为指定的内容。
selectempno,ename,（sal+NVL（comm,0））*12fromemp;
⏹DECODE（）：
类似于IF…ELSE语句
selectdecode（1,1,'内容是1',2,'内容是2',3,'内容是3'）fromdual;
范例：
要求查询出雇员的编号，姓名，雇佣日期及工作，将工作替换成中文显示。
selectempno雇员编号,ename雇员名称,hiredate雇佣日期,DECODE（job,'CLERK','业务员',upper（'salesman'）,'销售人员',upper（'manager'）,'经理',upper（'analyst'）,'分析员',upper（'president'）,'总裁'）职位fromemp;
g）多表查询（重点）
查询使用到一张以上的表，则为多表查询。
可用where：
select*fromempe,deptdwheree.deptno=d.deptno;
表自身关联，如查上级。
SELECTe.ename,e.job,m.ename
FROMempe,empm
WHEREe.mgr=m.empno;
h）左、右连接（重点）
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno（+）=d.deptno;右连接
WHEREe.deptno=d.deptno（+）;左连接
（+）在=的左边是右连接，在=的右边是左连接，默认是左连接。
（+）在=的左边，以右边的表为准，反之，则以左边的表为准。
i）有关连接的语法
SELECTtabel1.column,table2.column
FROMtable1[CROSSJOINtable2]|
[NATURALJOINtable2]|
[JOINtable2USING（column_name）]|
[JOINtable2ON（table1.column_name=table2.column_name）]|
[LEFT|RIGHT|FULLOUTERJOINtable2ON（table1.coulumn_name=table2.column_name）];
j）交叉连接（CROSSJOIN）：
产生笛卡尔积
SELECT*FROMempCROSSJOINdept;
k）自然连接（NATUALJOIN）：
自动进行关联字段的匹配
SELECT*FROMempNATURALJOINdept;去除了笛卡尔积
l）USING子句：
直接关联的操作列
SELECT*FROMempeJOINdeptdUSING（deptno）WHEREdeptno=30;
m）ON子句：
用户自己编写连接的条件
SELECT*FROMempeJOINdeptdON（e.deptno=d.deptno）WHEREe.deptno=30;
结果与USING是一样的。
n）左连接（左外连接）、右连接（右外连接）：
LEFTJOIN,RIGHTJOIN。
SELECTe.ename,d.deptno,d.dname,d.locFROMempeRIGHTJOINdeptdON（e.deptno=d.deptno）;
5.组函数和分组统计（重点）
a）分组：
例如，把所有的男生分为一组，女生分为一组
如果想求出每一组的平均身高，平均年龄等，这就需要使用分组函数。
6.组函数
a）在SQL中常用的组函数有以下几个：
i.COUNT（）:
求出全部的记录数
SELECTCOUNT（empno）FROMemp;
ii.MAX（）:
求出一组中的最大值
iii.MIN（）:
求出最小值
求最大最小值，一般针对于数字的应用上。
求出所有员工的最低工资。
SELECTMIN（sal）fromemp;
iv.AVG（）:
求出平均值
v.SUM（）:
求和
7.分组统计
8.
9.
升级会员 