hello oracle I

整数类型 number(10)

数值类型 number(m,n)

字符相关类型固定字符长度如果没有填充满，尾部将以空格填充。 char(m)

可变字符长度最大长度不填空格 varchar2(m)

可变长度国际化字符串最大长度4GB nvarchar2(m)

可变长度大字符串最大长度 2¹⁶-1 字节 clob

可变长度国际化大字符串最大长度 4G nclob

日期 date 日期时间数据

timestamp 时间戳

二进制类型 blob

CREATE TABLE T_Person

(

FName VARCHAR2(20) NOT NULL,

FAge NUMBER(10) NOT NULL,

FNumber VARCHAR2(20) NOT NULL,

FISMaster VARCHAR2(5) DEFAULT 'NO',

PROMARY KEY (FNumber)

)

CREATE TABLE T_PersonalMember 
(FCompanyName VARCHAR2(20), 
FInternalNumber VARCHAR2(20),
FName VARCHAR2(20), 
PRIMARY KEY (FCompanyName,FInternalNumber)
)

外键

CREATE TABLE T_Department 
(
FId VARCHAR2(20),
FName VARCHAR2(20), 
FLevel NUMBER (10) ,
PRIMARY KEY (FId)
)

CREATE TABLE T_Employee 
(
FNumber VARCHAR2(20),
FName VARCHAR2(20), 
FDepartmentId VARCHAR2(20), 
FOREIGN KEY (FDepartmentId) REFERENCES T_Department (FId)
)

修改表结构

ALTER TABLE T_Person ADD FFavorite VARCHAR2(20),
ALTER TABLE T_Person DROP FAge

insert

INSERT INTO T_Person (FName, FAge, FRemark) VALUES('TOM',18,'USA')

INSERT 也并不需要我们指定所有的列，如果某些字段没有值，我们可以忽略这些字段。

我们插入一条没有备注信息的数据。

INSERT INTO T_Person(FAge, FName) VALUES(22,'LXF')

外键对数据插入的影响

外键值必须是在目标表中存在的，如果插入的数据在目标表中不存在就会违反外键约束异常。

简单的数据更新

UPDATE T_Person
SET FRemark='SuperMan'
WHERE FNAME='TOM'

数据删除

DELETE FROM T_Person

由于 T_Debt 表中的FPerson字段是指向表T_Person的FName字段的外键，所以必须先删除T_Debt表中的数据后才能删除T_Person中的数据。

带WHERE语句的删除

DELETE FROM T_Person WHERE FAge>20 or FRemark='Mars'

表结构的删除

DROP TABLE T_Debt

检索

检索出需要的列

SELECT FName AS Name,FAge AS Age FROM T_Employee

聚合函数

MAX,MIN,AVG,SUM,COUNT

SELECT MAX(FSalary) AS MAX_SALARY FROM T_Empolyee WHERE FAge>25

SELECT AVG(FAge) FROM T_Employee WHERE FSalary>3800
SELECT SUM(FSalary) FROM T_Employee
SELECT MIN(FSalary), MAX(FSalary) FROM T_Employee
SELECT COUNT(*),COUNT(FNumber) FROM T_Employee

COUNT(*) 统计的结果集的总条数，而COUNT(FName)统计的结果集中FName不为空值的记录的总条数。

排序

SELECT * FROM T_Employee
ORDER BY FAge ASC

ASC DESC

SELECT * FROM T_Employee
WHERE FAge>23
ODER BY FAge DESC, FSalary DESC

高级过滤

单字符匹配'_'

//Jerry, Kerry
SELECT * FROM T_Employee
WHERE FName LIKE '_erry'

多字符匹配'%'

SELECT * FROM T_Employee
WHERE FName LIKE '%n%'

空值检测

SELECT * FROM T_Employee WHERE FNAME=null
SELECT * FROM T_Employee WHERE FNAME IS NULL
SELECT * FROM T_Employee WHERE FNAME IS NOT NULL
SELECT * FROM T_Employee WHERE FNAME IS NOT NULL AND FSalary<5000

反

SELECT * FROM T_Employee WHERE FAge<>22 AND FSalary>=2000

SELECT * FROM T_Employee WHERE NOT(FAge=22) AND NOT(FSalary<2000)

多值检索

SELECT * FROM T_Employee
WHERE FAge IN (23,25,28)

SELECT *  FROM T_Employee
WHERE (FSalary BETWEEN 2000 AND 3000)
OR (FSalary BETWEEN 5000 AND 8000)

数据分组

需要分组的所有列都必须位于GROUP BY子句的列名中，也就是没有出现在GROUP BY子句中的列（聚合函数除外）是不能放大SELECT 语句后的列名中的。

SELECT FAge, FSalary FROM T_Employee GROUP BY FAge

SELECT FAge, AVG(FSalary) FROM T_Employee GROUP BY FAge

分组函数与聚合函数

每个年龄段的员工的人数

SELECT FAge, COUNT(*) AS CountOfThisAge FROM T_Employee
GROUP BY FAge

多个分组

SELECT FSubCompany, FAge, COUNT(*) AS CountOfThisSubCompAge FROM T_Employee
GROUP BY FSubCompany,FAge
ORDER BY FSubCompany

HAVING 语句

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee 
GROUP BY FAge 
HAVING COUNT(*)>1

HAVING 语句中不能包含为分组的列名

限制结果集行数

检索成绩排前三名的学生

//MYSQL
SELECT * FROM T_Employee ORDER BY FScore DESC LIMIT 0,3

ORACLE

SELECT * FROM 
( 
SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) row_num, 
FNumber,FName,FSalary,FAge FROM T_Employee 
) a 
WHERE a.row_num>=3 AND a.row_num<=5

Oracle 支持标准的函数 ROW_NUMBER(),不过 Oracle为每个结果集都增加了一个默认的表示行号的列，　rownum.

得到工资从高到低排序的前六名员工的信息

SELECT * FROM T_Employee
WHERE rownum<=6
ORDER BY FSalary DESC

ROW_NUMBER() check sample from oracle http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

select ename, deptno, row_number() over (partition by deptno order by ename) rn from emp;

ENAME          DEPTNO         RN
---------- ---------- ----------
CLARK              10          1
KING               10          2
MILLER             10          3
FORD               20          1
JONES              20          2
SCOTT              20          3
ALLEN              30          1
BLAKE              30          2
MARTIN             30          3
TURNER             30          4
WARD               30          5
SMITH              99          1

表分页

抑制数据重复

SELECT DISTINCT FDepartment FROM T_Employee

计算字段

常量字段

SELECT '行信息', FName, FAge FROM T_Employee

字段间计算

SELECT FNumber,FName,FAge * FSalary AS FSalaryIndex FROM T_Employee

数据处理函数

除了聚合函数，SQL中还有数值处理函数日期处理函数字符串处理函数

SELECT LENGTH(FName) as namelength FROM T_Employee

select SUBSTR(CONN_ID,0,5) from ACL_FILTER

SELECT FName,FAge,SIN(FAge),ABS(SIN(FAge)) FROM T_Employee

Oracle

Oracle中使用"||"进行字符串拼接，类似于MYSQL中的CONCAT,不要和+混淆

//MYSQL
SELECT CONCAT('工号为:', FNumber,'工资为:',FSalary) FROM T_Employee
SELECT CONCAT_WS(',',FNumber,FAge,FDepartment,FSalary) FROM T_Employee

SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee WHERE FName IS NOT NULL 

SELECT CONCAT('工号:',FNumber) FROM T_Employee 

//Oracle的CONCAT()函数只支持两个参数
SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓名为'),FName) FROM 
T_Employee 
WHERE FName IS NOT NULL

年龄全部加一

UPDATE T_Employee SET FAge=FAge+1

在Oracle中是不允许使用这种不带FROM子句的SELECT语句的，不过我们可以使用Oracle的系统表来作为FROM子句中的表名，最常用的系统表为DUAL

SELECT 1,LENGTH('ABC') FROM DUAL

联合结果集

有的时候我们需要组合两个完全不同的查询结果集，而这两个结果之间没有任何必然的联系，我们只是需要将它们显示在一个结果集中而已。

联合结果集的原则：

每个结果集必须有相同的列数，每个结果集的列必须类型相容

SELECT FNumber,FName,FAge,FDepartment FROM T_Employee 
UNION 
SELECT FIdCardNumber,FName,FAge,'临时工，不属于任何一个部门' FROM 
T_TempEmployee

在默认情况下，UNION运算合并了两个结果查询集，其中完全重复的数据行被合并为了一条。

如果需要在联合结果集中返回所有的记录而不管他们是否唯一，则需要用UNION ALL

SELECT FName,FAge FROM T_Employee 
UNION ALL 
SELECT FName,FAge FROM T_TempEmployee

这样即使有同名同年的员工，也不会合并。

SELECT '正式员工最高年龄',MAX(FAge) FROM T_Employee 
UNION 
SELECT '正式员工最低年龄',MIN(FAge) FROM T_Employee 
UNION 
SELECT '临时工最高年龄',MAX(FAge) FROM T_TempEmployee 
UNION 
SELECT '临时工最低年龄',MIN(FAge) FROM T_TempEmployee



SELECT FNumber,FSalary FROM T_Employee 
UNION 
SELECT '工资合计',SUM(FSalary) FROM T_Employee


SELECT 1,1 * 1 FROM DUAL  
UNION  
SELECT 2,2 * 2 FROM DUAL  
UNION  
SELECT 3,3 * 3 FROM DUAL  
UNION  
SELECT 4,4 * 4 FROM DUAL  
UNION  
SELECT 5,5 * 5 FROM DUAL


SELECT '以下是正式员工的姓名' FROM DUAL  
UNION ALL  
SELECT FName FROM T_Employee  
UNION ALL  
SELECT '以下是临时工的姓名' FROM DUAL  
UNION ALL  
SELECT FName FROM T_TempEmployee

函数

数学函数

ABS, POWER,SQRT,dbms_random.value,dbms_random.value(low,high),dbms_random.normal,dbms_random.string(opt,len),CEIL,FLOOR,ROUND(m,n),Round(value),SIN,COS,SIGN(求符号),MOD(求余数),LN,LOG(m,n),POWER(m,n)

字符串函数

LENGTH,LOWER,UPPER,LTRIM,RTRIM,TRIM,SUBSTR(s,s,l),INSTR(string,substring),REPLACE(string,string_tobe_replace,string_to_replace),ASCII('A'),CHR(56),SOUNDEX(str),

时间日期函数

TO_DATE('2008-08-08', 'YYYY-MM-DD  HH24:MI:SS')
TO_DATE('2008-08-08  08:00:00',  'YYYY-MM-DD HH24:MI:SS')
TO_DATE('08:00:00', 'YYYY-MM-DD HH24:MI:SS')

//当前时间
SELECT SYSTIMESTAMP FROM DUAL
SELECT SYSDATE FROM DUAL
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL

//日期增减:ORACLE中直接用+ -
SELECT FBirthDay, FBirthDay+3, FBirthDay-10 
FROM T_Person 

SELECT FBirthDay,ADD_MONTHS(FBirthDay,2),ADD_MONTHS(FBirthDay,-10) FROM T_Person

//星期几
TO_CHAR(date,format)
SELECT FBirthDay, 
TO_CHAR(FBirthDay, 'YYYY') as yyyy, 
TO_CHAR(FBirthDay, 'MM') as mm, 
TO_CHAR(FBirthDay, 'MON') as mon, 
TO_CHAR(FBirthDay, 'WW') as ww 
FROM T_Person 

SELECT 
FBirthDay,TO_CHAR(FBirthDay, 'DAY') as birthwk, 
FRegDay,TO_CHAR(FRegDay, 'DAY') as regwk 
FROM T_Person

其他函数

TO_CHAR(exp,format),TO_DATE(exp,format),TO_NUMBER(exp,format)

SELECT HEXTORAW('7D'), 
RAWTOHEX ('a'), 
HEXTORAW(RAWTOHEX('w')) 
FROM DUAL 

COALESCE()函数 
主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理

SELECT FBirthDay,FRegDay,  
COALESCE(FBirthDay,FRegDay,TO_DATE('2008-08-08', 'YYYY-MM-DD HH24:MI:SS')) 

NVL(expression,value) 

//CASE
SELECT  
  FName, 
 (CASE FName  
  WHEN 'Tom' THEN 'GoodBoy'  
  WHEN 'Lily' THEN 'GoodGirl'  
  WHEN 'Sam' THEN 'BadBoy'  
  WHEN 'Kerry' THEN 'BadGirl' 
  ELSE 'Normal'  
  END) as isgood 
FROM T_Person

Oracle中独有的函数

填充函数

SELECT FName, 
LPAD(FName,5,'*'),
RPAD(FName,5,'#') 
FROM T_Person

返回当月的最后一天

SELECT FName,FBirthDay,  
LAST_DAY(FBirthDay)  
FROM T_Person 
WHERE FBirthDay IS NOT NULL

计算最大最小值

SELECT GREATEST(2,7,1,8,30,4,5566,99,2,222,12), 
LEAST(2,7,1,8,30,4,3,99,-2,222,12) 
FROM DUAL

辅助功能函数

USER函数用来取得当前登录用户名

SELECT USER() 
FROM DUAL

USERENV()函数用来取得当前登录用户相关的环境信息

SELECT USERENV('ISDBA') AS ISDBA, 
USERENV('LANGUAGE') AS LANGUAGE,
USERENV('TERMINAL') AS TERMINAL, 
USERENV('SESSIONID') AS SESSIONID, 
USERENV('ENTRYID') AS ENTRYID, 
USERENV('INSTANCE') AS INSTANCE, 
USERENV('LANG') AS LANG 
FROM DUAL

posted on 2012-05-28 10:35 grep 阅读(350) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

简单复杂

hello oracle I

导航