Oracle讲解
1.5 ORACLE的服务
OracleHOME_NAMEINSListener
是Oracle的监听程序。在连接Oracle的时候需要启动此服务。 OracleServiceSID
该服务是为名为SID的数据库实例创建的，在连接时需要启动
Sqlplus与sqlplusw是编辑SQL语句、执行sql语句的工具 如果在sqlplusw中不好编辑，可以使用ed 1打开记事本编辑 执行就是@1 可以通过
Set linesize 300 设置行的长度
Set pagesize 20 设置每页显示的记录数
SQL查询和SQL函数
数据定义语言(DDL)
CREATE(创建)、ALTER(更改)、DROP(删除)命令 数据操纵语言(DML)
INSERT(插入)、SELECT(查询)、DELETE(删除)、UPDATE(更新)命令 事务控制语言
COMMIT(提交)、SAVEPOINT(保存点)和ROLLBACK(回滚)命令 数据控制语言
GRANT(授予)和REVOKE(回收)命令
2.1 Oracle数据类型 CHAR数据类型，固定长度的字符串，可以是1-2000字节 VARCHAR2数据类型，支持可变长度的字符串，1-4000字节
LONG数据类型，此数据类型用来存储可变长度的字符数据，最多存储2GB ? 一个表只有一列可以为LONG数据类型 ? LONG列不能定义唯一约束或主键约束 ? LONG列不能建立索引
? 过程或存储过程不能接受LONG数据类型的参数
NUMBER数据类型可以存储正数、负数、零、定点数和精度为38位的浮点数。该数据类型的格式为NUMBER(p,s) p为精度，表示总位数，s为范围表示小数点右边的数字位数
NUMBER类型详细介绍，此内容只做了解，比较复杂 NUMBER[(p [, s])] s不写就为0 范围： 1 <= p <=38, -84 <= s <= 127
保存数据范围：-1.0e-130 <= number value < 1.0e+126
6
Oracle讲解
保存在机器内部的范围： 1 ~ 22 bytes
有效位：从左边第一个不为0的数算起的位数。 s要分几种情况来看，如果： s > 0
精确到小数点右边s位，并四舍五入。然后检验有效位是否 <= p。 s < 0
精确到小数点左边s位，并四舍五入。然后检验有效位是否 <= p + |s|。 s = 0
此时NUMBER表示整数。 部分示例：
123.89 NUMBER 123.89 123.89 NUMBER(3) 124 123.89 NUMBER(6,2) 123.89
123.89 NUMBER(4,2) Error (有效位为5, 5 > 4) 123.89 NUMBER(6,-2) 100
12345.12345 NUMBER(6,2) Error (有效位为5+2 > 6) 12345678 NUMBER(5,-2) Error (有效位为8 > 7) 123456789 NUMBER(5,-4) 123460000
0.1 NUMBER(4,5) Error (0.10000, 有效位为5 > 4) 0.01234567 NUMBER(4,5) 0.01235 0.09999 NUMBER(4,5) 0.09999
DATE数据类型(公元前4712年1月1日到公元9999年12月31日) SYSDATE获取当前日期 select sysdate from dual;
TIMESTAMP数据类型,SYSTIMESTAMP返回当前日期、时间 RAW数据类型
LONG RAW数据类型 LOB数据类型 ? CLOB ? BLOB ? BFILE 伪列
伪列就像Oracle中的一个表列，但实际上并未存储在表中，它可以从表中查询，但是不能插入、更新或删除它们的值。
数据库中的每一行都有一个行地址，ROWID伪列返回该行地址，可以通过ROWID来定位表中的一行。ROWID
SELECT ROWID,ENAME from EMP WHERE EMPNO=’7900’; ROWNUM
对于一个查询返回的每一行，ROWNUM伪列返回一个数值表示行的次序，第1行是1，第2行是2等
SELECT * FROM EMP WHERE ROWNUM<11;
7
Oracle讲解
可以使用ROWNUM来进行分页查询
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM emp) A WHERE ROWNUM <= 10) WHERE RN >= 5;
2.2 建表
CREATE TABLE student( Stucode varchar(5) , StuName varchar(20) );
表名首字符应该为字母
不能使用Oracle的保留字为表命名 表名的最大长度为30个字符
同一用户模式下的不同表不能具有相同的名称 不能用空格和单引号
GRANT connect,resource to accp; 这样才能操作表空间
//通过system用户给accp用户授权，这样accp用户才能创建约束 使用accp用户建表 CREATE TABLE student (
stuId NUMBER(4) CONSTRAINT pk_stu_Id PRIMARY KEY NOT NULL, stuName VARCHAR2(50) NOT NULL, stuEmail VARCHAR2(50) CONSTRAINT ck_stu_Email CHECK(stuEmail LIKE '%@%'), stuAge NUMBER(2) CONSTRAINT ck_stu_Age CHECK (stuAge BETWEEN 16 AND 45), stuAddress VARCHAR2(100) ); /
创建表的同时加约束 单独加默认约束 ALTER TABLE student MODIFY(stuAddress DEFAULT '荆州市青少年宫');
//建立课程表
CREATE TABLE course ( courseId NUMBER(2) CONSTRAINT pk_course_Id PRIMARY KEY NOT NULL, courseName VARCHAR(10) ); /
//建立成绩表,外键这样添加 CREATE TABLE score (
8
Oracle讲解
scoreId NUMBER(2) CONSTRAINT pk_score_Id PRIMARY KEY NOT NULL, stuId NUMBER(4) NOT NULL, courseId NUMBER(2) NOT NULL,
score NUMBER(4,1) CONSTRAINT ck_score_score CHECK(score BETWEEN 0 AND 100) NOT NULL,
CONSTRAINT fk_student_score FOREIGN KEY(stuId) REFERENCES student(stuId),
CONSTRAINT fk_course_score FOREIGN KEY(courseId) REFERENCES course(courseId) ); /
TRUNCATE TABLE student
删除student表里的所有数据，不删结构 DESC stduent 显示表结构
DROP TABLE stduent 删除表数据及其结构
其他CRUD操作基本与SQLSERVER一样
GRANT SELECT,UPDATE ON 表名 TO 用户名 授予权限
REVOKE SELECT,UPDATE ON 表名 TO 用户名 撤销权限
2.3运算符
+ - * /
= != < > <= >= BETWEEN..AND IN LIKE IS NULL NOT IS NOT NULL AND OR NOT UNION返回两个查询中所有不重复的行
UNION ALL返回两个查询中所有行,包含重复的 INTERSECT返回两个查询都有的行
MINUS返回第一个查询选定但是没有被第二个查询选定的行
|| 连接字符串用的操作符
2.4 SQL函数
ADD_MONTHS(d, n)，d表示日期，那表示加多少月
MONTHS_BETWEEN(d1,d2)函数返回两个日期之间的月数,如果d1晚于d2结果为正
9
Oracle讲解
LAST_DAY(d)返回指定日期当月的最后一天的日期值 ROUND(d,[fmt])四舍五入到指定模型的日期
NEXT_DAY(SYSDATE,’星期四’)返回从当前日期到下一个星期几的日期 TRUC(SYSDAYE,’YEAR’)如果为2009-11-11则返回2009-1-1日 EXTRACT(YEAR FROM SYSDATE) 如果为2009-11-11则返回2009 字符函数与数学函数基本与其他语言一致 转换函数
TO_CHAR将日期转换成字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-SS HH24:MI:SS') 当前时间 FROM dual;
TO_CHAR(SYSDATE,’YYYY”年”fmMM”月”fmDD”日” HH24:MI:SS) 返回:2009年8月11日 15:02:50
fm是格式掩码用来避免空格填充和数字零填充 TO_DATE将字符串转换成日期
SELECT TO_DATE(‘2009-11-12’,’yyyy-mm-dd’) FROM dual; 结果是’12-11月-09’
Dual表是一个特殊的表，只有一行一列，所以在测试函数的时候可以使用它 TO_NUMBER将数字转换成NUMBER数据类型 SELECT SQRT(TO_NUMBER(‘100’)) FROM dual; 返回10
NVL函数将空值替换成指定的值 NVL(expression1,expression2)
如果expression1为NULL,则NVL返回expression2,否则返回expression1 NVL2(expression1, expression2, expression3) 如果expression1为NULL,则返回expression2,否则返回expression3 NULLIF(expr1,expr2)
如果他们相等就返回空值,否则返回expr1,等价于
CASE WHEN expr1=expr2 THEN NULL ELSE expr1 END
分组函数
AVG、MIN、MAX、SUM、COUNT GROUP BY子句 HAVING子句
使用方法与SQLSERVER一样
分析函数
分析函数是对分组或排序号的数据进行分析 ROW_NUMBER
返回一个唯一的排序值从1开始 RANK
在一组中排位，如一组中有两个数据相等，则会产生跳跃计数 DENSE_RANK
与RANK作用相同，只是不会跳跃计数，但也允许有相同的计数
10