Oracle学习总结
DQL数据查询语言
显示表结构
查询所有或指定字段
筛选查询
SELECT 字段1,… FROM 表名 WHERE 筛选条件; 起别名(sql语言不区分大小写,默认使用大写)
字段 AS 别名 字段 别名 注:"别名"表示以原格式显示 连接符
把列与列,列与字符连接在一起 用’||‘来表示连接(类似于JAVA中的’+'来连接字符串) 字符串
字符串可以是查询列表中的字符,数字,日期 日期和字符只能在单引号中出现 每当返回一行时,字符串就输出一次 字符串内严格区分大小写 默认日期格式 DD-MON月-RR SQL与SQL*Plus区别
SQL:一种语言,ANSI标准,关键字不能缩写,使用语句控制表与数据 SQL*Plus:一种环境,Oracle特性之一,关键字可缩写,命令不能改变数据,集中运行 运算符
比较运算符 =,>,>=,<,<=,<>或!= 赋值运算符 := 逻辑运算符 BETWEEN…AND,IN(值列表),LIKE,IS NULL 关键字
排序子句
order by 字段 [asc升序/desc降序] 分组子句
分页子句
distinct 去重 all 表示值列表中所有的元素 any/some 表示值列表中某一个元素 set 设置某些属性 执行顺序:①from ② where ③ group by ④ select ⑤ having ⑥ order by ⑦ limit 多表查询
等值连接 SELECT 查询列表 FROM 表1 别名,表2 别名 WHERE 表1别名.列名 = 表2别名.列名 [AND 其它连接条件或筛选条件] 非等值连接 SELECT 查询列表 FROM 表1 别名,表2 别名 WHERE 表1别名.列名 除=外的其它运算符 表2别名.列名 [AND 其它连接条件或筛选条件] 内连接:结果集不包含多个表中不匹配的行 SELECT 查询列表 FROM 表1名 别名 INNER JOIN 表2名 别名 ON 连接条件(可多个) [WHERE 筛选条件] [其他子句] 外连接:结果集不包含从表的不匹配的行
左外连接 SELECT 查询列表 FROM 表1名 别名 LEFT [OUTER] JOIN 表2名 别名 ON 连接条件(可多个) [WHERE 筛选条件] 右外连接 SELECT 查询列表 FROM 表1名 别名 RIGHT [OUTER] JOIN 表2名 别名 ON 连接条件(可多个) [WHERE 筛选条件] 全外连接 SELECT 查询列表 FROM 表1名 别名 FULL [OUTER] JOIN 表2名 别名 ON 连接条件(可多个) [WHERE 筛选条件] 交叉连接(笛卡尔积) SELECT 查询列表 FROM 表1名 CROSS JOIN 表2名 自然连接(自动根据两个表之间相同字段(名,含义,类型都一致)进行连接) SELECT 查询列表 FROM 表1名 NATURAL JOIN 表2名 USING关键字连接(根据两张表指定的同名字段进行连接) SELECT 查询列表 FROM 表1名 JOIN 表2名 USING(同名字段列表) 联合查询(使用SET操作符)
将两个查询语句的结果取并集
格式: 查询语句1 UNION(去重)/UNION ALL 查询语句2 将两个查询语句的结果取交集
格式: 查询语句1 INTERSECT 查询语句2 将两个查询语句的结果取差集(从第1个语句中去除掉与第二个语句重复的部分)
注:每条查询语句列名或表达式在数量和类型上要相对应,括号可改变执行顺序,ORDER BY只能在语句最后出现且可以使用查询语句1的列名,别名或相对位置(例order by 1按第一列排) 子查询(内查询)
优先主查询执行,结果被主查询使用 子查询要包含在括号里,一般放在条件的右侧 单行子查询使用单行操作符,多行子查询使用多行操作符 多列子查询:返回多列的子查询 where (列名1,列名2,…) IN (多列子查询) 或where 列名1 IN (多列子查询) AND 列名2 IN (多列子查询) [AND …]; 单列子查询:返回单列的子查询 Oracle8只能在from与where子句以及insert语句中values列表中使用 Oracle9可在decode与case以及查询语句中除order by之外的所有子句中使用 相关子查询
主查询每一行都会执行一次子查询,子查询中使用主查询中的列
EXISTS操作符(检查子查询中是否存在满足条件的行,返回true或false) NOT EXISTS操作符 相关更新:更新语句中子查询中使用主查询中的列 相关删除:删除语句中子查询中使用主查询中的列 WITH 别名 AS (子查询语句) 在查询语句外建立语句块.以便多次使用,语句执行一次存储在用户的临时表空间
函数
单行函数
字符型
大小写转换
返回全大写 UPPER() 返回全小写 LOWER() 返回首字母大写 INITCAP() 连接多个字符 CONCAT(‘字符1’,‘字符2’…) 截取字符 SUBSTR(‘字符’,开始索引,截取长度) 获取字符长度 LENGTH(‘字符’) 获取指定字符在字符串中的位置 INSTR(‘字符’,‘指定字符’) 左填充 LPAD(‘字符’,返回位数,‘填充字符’) 右填充 RPAD(‘字符’,返回位数,‘填充字符’) 移除首尾指定字符 TRIM(‘指定字符’ FROM ‘字符’) 替换指定字符 REPLACE(‘字符’,‘指定字符’,‘替换字符’) 数值型
四舍五入 ROUND(原数值[,位数]) 注:正整数表示小数位保留位数,负正数表示小数点前第几位四舍五入,不写则保留整数 截断 TRUNC(原数值[,位数]) 注:正整数表示小数位保留位数,负正数表示小数点前第几位截断,不写则保留整数 求余 MOD(被除数,除数) 日期型
日期运算
加上或减去一个数字仍为日期 两个日期想减返回相差天数 可用数字除以24来为日期加减天数 比较两个日期相差的月数 MONTHS_BETWEEN() 向指定日期加若干月数 ADD_MONTHS(date,日期天数) 获得时间后第一个星期x的时间 NEXT_DAY(date,星期x) 获得特定日期所在月份的最后一天 LAST_DAY(date) 获取以fmt为单位距离的离指定日期date最近的日期时间值 ROUND(date[,fmt]) 获取以fmt为单位距离的离指定日期date之前最近的日期时间值 TRUNC(date[,fmt]) 转换型
隐式转换(自转) date<->varchar2或char<->number 显示转化(转换成有格式的数据)
TO_DATE(char,fmt) TO_CHAR(date/number,fmt) TO_NUMBER(date/char,fmt) 通用型
NVL(日期/字符/数字,代替值)
NVL2(日期/字符/数字,代替值1,代替值2)
NULLIF(值1,值2)
COALESCE(expression1,expression2,…expressionn)
返回第一个不为空的表达式,如果所有的表达式都是空值,最终将返回一个空值 条件分支语句
CASE 变量|字段|表达式 WHEN 常量1 THEN 要显示的值或表达式 WHEN 常量2 THEN 要显示的值或表达式 … ELSE 要显示的值或表达式 END DECODE(变量|字段|表达式, 常量1,值或表达式, … 值或表达式 )
多行函数
STDDEV() 获得样本的标准偏差 MAX() 获得最大值 MIN() 获得最小值 SUM() 获得总值 AVG() 获得平均值 COUNT() 统计总数 注: 与分组语句搭配使用,不可在where子句中使用
DML数据操作语言
插入语句
方式一 INSERT INTO 表名 [(字段名1,字段名2,…)] VALUES (字段值1,字段值2,…)[,(字段值1,字段值2,…),]…; 方式二 INSERT INTO 表名 SELECT * FROM 拷贝表 WHERE 查询条件 方式三 INSERT INTO 表名 [(字段名1,字段名2,…)] SELECT [(字段名1,字段名2,…)] FROM 拷贝表 WHERE 字段 LIKE ‘format’; 方式四(创建脚本) INSERT INTO 表名 [(字段名1,字段名2,…)] VALUES (&字段名1,&字段名2,…) 在弹出的框中输入值 修改语句
修改指定列符合条件的值 UPDATE 表名 SET 列名1=值,列名2=值,… WHERE 条件; 修改指定列所有值 UPDATE 表名 SET 列名1=值,列名2=值,…; 删除语句
删除表中所有数据 DELETE FROM 表名; 删除表中符合条件的数据 DELETE FROM 表名 [WHERE 条件]; 注:DML语言可回滚rollback
DDL数据定义语言
命名规则
必须以字母开头 必须在1-30个字符之间 必须只能包含A-Z,a-z,0-9,_,$,# 不能和用户的其他对象重名,不能是Oracle保留字 数据类型
VARCHAR2(size) 可变长字符数据 CHAR(size) 定长字符数据 NUMBER(p,s) 可变长数值类型 DATE 日期型数据 LONG 可变长字符数据最大可达2G CLOB 字符数据最大可达4G BLOB 二进制数据最大可达4G RAW(LONG,RAW) 原始的二进制数据 BFILE 存储外部文件的二进制数据最大可达4G ROWID 行地址 常见约束
PRIMARY KEY 主键,FOREIGN KEY 外键,NOT NULL 非空(只定义在列级约束),UNIQUE 唯一,CHECK(列表) 检查 列级约束(只作用在一个列上)
创建表时写在字段后格式:[CONSTRAINT 约束名(表名_列名_约束名)] 约束类型 表级约束(可作用在多个列上)
创建表时写在字段同级格式:[CONSTRAINT 约束名(表名_列名_约束名)] 约束类型(约束字段) 外键表级约束格式:[CONSTRAINT 约束名(fk_主表名_从表名_字段名)] 约束类型(约束字段) REFERENCES 主表(主表字段) 外键级联操作:主从表同时操作 开启级联删除 ON DELETE CASCADE 开启级联置空 ON DELETE SET NULL 开启级联更新 ON UPDATE CASCADE 添加约束
约束不能修改,只能添加或删除约束 添加:ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(约束字段); 删除:ALTER TABLE 表名 DROP CONSTRAINT 约束名; 有效化或无效化约束 有效化:ALTER TABLE 表名 ENABLE CONSTRAINT 约束名;//主键或唯一系统会自动创建 无效化:ALTER TABLE 表名 DISABLE CONSTRAINT 约束名; 添加NOT NULL使用MODIFY语句 ALTER TABLE 表名 MODIFY 约束字段 字段类型 NOT NULL; 查询约束
约束表,记录当前用户的所有约束 USER_CONSTRAINTS 约束字段表,记录每个约束涉及到的所有字段 USER_CONS_COLUMNS 例:SELECT constraint_name,constraint_type,search_condition FROM USER_CONSTRAINTS WHERE table_name=‘EMPLOYEES’; 创建表
CREATE TABLE 表名{ 列名1 数据类型 [列级约束], 列名1 数据类型 [列级约束], … [,表级约束], … } 复制表
CREATE TABLE 表名 AS SELECT 查询列表 FROM 现有表名 WHERE 筛选条件; 修改表
修改表名
ALTER TABLE 原表名 RENAME TO 新表名; RENAME 原表名 TO 新表名; 添加新的列或表级约束
ALTER TABLE 表名 ADD ( COLUMN 新列名 新数据类型 [约束] [,COLUMN 新列名 新数据类型 [约束]] …); 修改列的数据类型或约束
ALTER TABLE 表名 MODIFY ( 原列名 新类型 [约束] [, 原列名 新类型 [约束]] …); 修改列名
ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名; 删除列
ALTER TABLE 表名 DROP COLUMN 列名; 删除表
清空表
TRUNCATE TABLE 表名; 注:DDL语言不可回滚rollback
数据库事务
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态 组成:一个DDL语句,一个或多个DML语句,一个DCL语句(以一个DML语句开始,以COMMIT,ROLLBACK语句,DDL语句,用户会话正常结束或系统异常终止作为结束) COMMIT;(提交确认数据更改) ROLLBACK;(回滚使数据还原) SAVEPOINT 回滚点名;(设置回滚点) ROLLBACK 回滚点名;(使数据还原到回滚点) 其他用户不能看到当前用户所做的改变,直到当前用户结束事务 DML语句所涉及的行被锁定,其他用户不能操作,只有当锁被释放时,其他用户才可以操作涉及到的数据
常见的数据库对象
表:基本的数据存储集合,由行与列组成 视图:从表中抽取的逻辑上相关的数据集合 序列:提供有规律的数值 索引:提高查询的效率 同义词:给对象起别名
视图
建立在已有表的基础上,被视图依赖的表称为基表,为视图提供数据的语句是SELECT,可将视图理解为存储起来的SELECT语句,对视图操作会对等到基表中 好处;可控制数据访问,简化查询,避免重复访问相同数据, 语法
创建 CREATE VIEW 视图名 AS SELECT查询语句 [WITH READ ONLY] //屏蔽DML操作 修改 CREATE OR REPLACE VIEW 视图名[(字段列表)] AS SELECT查询语句 删除 DROP VIEW 视图名; 查询 SELECT * FROM user_views; 视图数据操作
查询语句与查询表操作一致 可以在简单视图中执行DML操作,复杂视图有时不能执行DML操作,因此视图不建议更新 当视图定义中包含组函数,GROUP BY子句,DISTINCT关键字,ROWNUM伪列时不能使用DELETE 当视图定义中包含组函数,GROUP BY子句,DISTINCT关键字,ROWNUM伪列或列的定义为表达式时不能使用UPDATE 当视图定义中包含组函数,GROUP BY子句,DISTINCT关键字,ROWNUM伪列,列的定义为表达式或表中非空的列在视图定义中未包括时不能使INSERT Top-N 分析
查询一个列中最大或最小的n个值 格式 SELECT ROWNUM,查询列表 FROM( SELECT 查询列表 FROM table ORDER BY Top-N_column ) WHERE ROWNUM <= N; (对伪列ROWNUM只能使用<或<=, 而不能用=,>,>=返回任何数据) 解决(把伪列变为真实列) SELECT 别名,查询列表 FROM( SELECT ROWNUM 别名,查询列表 FROM( SELECT 查询列表 FROM table ORDER BY Top-N_column ) ) WHERE ROWNUM > N AND ROWNUM<Y;
序列
可供多个用户用来产生唯一数值的数据库对象 特点:自动提供唯一数值,共享对象,主要用于提供主键值,将序列值装入内存可提高访问效率 语法
创建 CREATE SEQUENCE 序列名称 [INCREMENT BY n] //序列值为n,如果n是正数则递增,如果是负数则递减默认为1 [START WITH n] //开始值,递增默认是minvalue 递减是maxvalue [{MAXVALUE n | NOMAXVALUE}] //最大值 [{MINVALUE n | NOMINVALUE}] //最小值 [{CYCLE | NOCYCLE}] //是否循环 [{CACHE n | NOCACHE}];//是否缓存 修改 ALTER SEQUENCE 序列名称 [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; //只有将来的序列值会被修改,修改初始值只能删除后重建 删除 DROP SEQUENCE 序列名称; 使用 产生序列下一个值:序列名.nextval 查看当前值:序列名.currval 序列第一次必须先调用nextval获取一个序列值才能使用currval查看当前值 序列的起始值不能小于最小值 创建一个循环序列,则必须要设定最大值 如果创建带缓存的序列,缓存的值必须满足约束公式: 最大值-最小值>=(缓存值-1)*每次循环的值 如果不将序列装入内存,可使用表USER_SEQUENCE查看序列当前有效值 裂缝(数值缺号)出现情况:回滚,系统异常,多个表同时使用一个序列 使用缓存会有产生断号的现象 清空cache中缓存的序列值alter system flush 序列名;
索引
一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间 索引被删除或损坏不会对表产生影响,其影响只是查询的速度 索引一旦建立,系统会对其自动维护,并决定何时使用索引用户不能在查询语句中指定使用那个索引 再删除一个表时,所有基于该表的索引会自动被删除 通过指针加速服务器的查询速度,通过快速定位数据的方法,减少磁盘I/O
创建
自动创建:在定义主键或唯一约束后系统自动在响应列上创建唯一性约束 手动创建:用户在其他列上创建非唯一的索引,以加速查询 CREATE INDEX 索引名//格式:表名_列名_索引名 ON 表名(column[,column,]…); 删除
创建索引时机
列中数据值分布范围很广 列经常在WHERE子句或连接条件中出现 表经常被访问而且数据量大,访问数据大概占数据总量的2%到4%
表很小 列不经常在WHERE子句或连接条件中出现 查询数据大于总量的2%到4% 表经常更新
同义词
含义:为数据库对象起别名 创建 CREATE SYNONTM 同义词名 FOR 表或视图名; 删除 DROP SYNONTM 同义词名;
DCL数据库控制语言
前提:系统权限由数据库管理员(DBA)提供,对用户和角色有100多个不同的可用系统权限
用户权限(图像化界面里的用户-系统) 创建用户(图像化界面里的用户-一般信息) CREATE USER 用户名 IDENTIFIED BY 密码; 修改用户密码 ALTER USER 用户名 IDENTIFIED BY 密码; ALTER USER 用户名 PASSWORD EXPIRE;//清除密码 赋予用户权限 GRANT 用户权限1 [,用户权限2…] TO 用户名[,用户名…];
CREATE SESSION//创建会话 CREATE TABLE//创建表 CREATE SEQUENCE//创建序列 CREATE VIEW//创建视图 CREATE PROCEDURE//创建过程 创建表空间 ALTER USER 角色名 QUOTA UNLIMITED ON 表名; 创建角色(图像化界面里的用户-角色) CREATE ROLE 角色名; 赋予角色权限 GRANT 用户权限1 [,用户权限2…] TO 角色名[,角色名…]; 赋予用户角色 GRANT 角色名 TO 用户名; 对象拥有者赋予对象权限(图像化界面里的用户-对象) GRANT 对象权限[(指定被授予权限的列)] [,…]|ALL ON 对象名 TO 用户名[,…]|角色名[,…]|PUBLIC [WITH GRANT OPTION]; 说明:ALL 指定所有对象权限,PUBLIC 授予权限给所有用户,WITH GRANT OPTION 允许被授予权限的人再授予对象权限给其他用户和角色 对象拥有者收回对象权限 REVOKE 对象权限[(指定被授予权限的列)] [,…]|ALL ON 对象名 FROM 用户名[,…]|角色名[,…]|PUBLIC [CASCADE CONSTRAINTS]; 说明:CASCADE CONSTRAINTS用于删除任何与该对象相关的约束和对象,例如索引、触发器、权限、完整性约束等
对象权限 TABLE VIEW SEQUENCE PROCEDURE ALTER √ √ DELETE √ √ EXECUTE √ INDEX √ INSERT √ √ REFERENCES √ √ SELECT √ √ √ UPDATE √ √
数据字典视图 说明 ROLE_SYS_PRIVS 授予角色的系统权限 ROLE_TAB_PRIVS 授予角色的表权限 USER_ROLE_PRIVS 可由用户访问的角色 USER_TAB_PRIVS_MADE 授予用户的对象上的对象权限 USER_TAB_PRIVS_RECD 授予用户的对象权限 USER_COL_PRIVS_MADE 授予用户对象的列上的对象权限 USER_COL_PRIVS_RECD 授予用户在指定列上的对象权限 USER_SYS_PRIVS 授予用户的系统权限