处理数据
一)Sql类型:
1.DML(Data Manipulation Language 数据操作语言):insert,update,delete,select
2.DDL(Data Definition Language 数据定义语言):create,alter, drop,truncate,sequence,index
3.DCL(Data Control Language 数据控制语言):grant(授权),revoke(撤销权限)
二)插入数据(insert)
单条插入:
1.INSERT INTO TABLE(COLUMN1[, COLUMN2, …]) VALUES(VALUE1[, VALUE2, …])
2.地址符 &
INSERT INTO TABLE(COLUMN1[, COLUMN2, …]) VALUES(¶m1[, ¶m2, …])
多条插入:
1.INSERT INTO TABLE1( COLUMN1[, COLUMN2, …]) SELECT (COLUMN1[, COLUMN2, …]) FROM TABLE2 [WHERE …]
三)更新数据(update)
UPDATE TABLE
SET COLUMN1 = VALUE1[, COLUMN2 = VALUE2, …]
[WHERE conditions];
四)删除数据(delete)
DELETE [FROM] TABLE [WHERE condition];
删除整张表的数据:
1.DELETE [FROM] TABLE
2.TRUNCATE TABLE
DELETE和TRUNCATE区别:
1.detete逐条删除数据,truncate先摧毁表,再重建表
2.delete不会释放空间,truncate会释放空间
3.delete是DML语句,truncate是DDL语句
4.delete可以闪回,truncate不可以
5.delete会产生碎片,truncate不会产生碎片
6.delete语句效率高。
五)数据库事务
事务组成:
1.一个或多个DML语句;
2.一个DDL语句;
3.一个DCL语句。
事务标志:
1.起始标志:事务中第一条DML语句
2.结束标志:a.提交:显式:commit
隐式:正常退出(exit),DDL语句,DCL语句
b.回滚:显式:rollback
隐式:非正常退出,停电,宕机
保存点:
1.创建保存点a:savepoint a
2.滚到保存点a:rollback to savepoint a
六)隔离级别(待补充)
创建和管理表
一)创建表
必须具备:
a. CREATE TABLE权限
b.存储空间
必须指定:
a.表名
b.列名,数据类型,数据类型的大小
CREATE TABLE TABLE1 (column datatype [DEFAULT expr][,…])
常用数据类型:
类型 | 描述 |
---|---|
VARCHAR2(size) | 可变长字符数据 |
CHAR(size) | 定长字符数据 |
NUMBER(p,s) | 可变长数值数据,p为有效位数,s为精确到小数点左边或右边的位数 |
DATE | 日期型数据 |
LONG | 可变长字符数据,最大可达到2G |
CLOB | 字符数据,最大可达到4G |
RAW and LONG RAW | 原始的二进制数据 |
BLOB | 二进制数据,最大可达到4G |
BFILE | 存储外部文件的二进制数据,最大可达到4G |
ROWID | 行地址(伪列) |
注1:定点数的精度§和刻度(s)遵循以下规则:
1) 当一个数的整数部分的长度> p-s 时,Oracle就会报错
2) 当一个数的小数部分的长度> s 时,Oracle就会舍入。
3) 当s(scale)为负数时,Oracle就对小数点左边的s个数字进行舍入。
4) 当s > p 时, p表示小数点后第s位向左最多可以有多少位数字,如果大于p则Oracle报错,小数点后s位向右的数字被舍入
使用自查询创建表:
CREATE TABLE TABLE1 [(column, column…)] AS subquery;
二)修改表
追加新的列:
ALTER TABLE table1 ADD column datatype;
修改现有的列:
ALTER TABLE table1 MODIFY column datatype;
删除一个列:
ALTER TABLE table1 DROP COLUMN column;
重命名列:
ALTER TABLE table1 RENAME column1 TO column2;
重命名表:
RENAME table1 TO table2;
三)删除表
1.数据和结构都被删除;
2.所有正在运行的相关事务被提交;
3.所有相关索引被删除;
4.DROP TABLE 语句不能回滚,但是可以闪回。
DROP TABLE table1 [PURGE];
只是将表放入到回收站中,并且重命名表。如果加上PURGE参数则不经过回收站直接删除表。
查看回收站:
SHOW RECYCLEBIN;
清空回收站:
PURGE RECYCLEBIN;
查看回收站中的表:
SELECT * FROM “table_recyclebin_name”;
四)约束
约束是表一级的限制;
如果存在依赖关系,约束可以防止错误的删除数据;
约束的类型:
NOT NULL | 非空约束 |
---|---|
UNIQUE | 唯一性约束,不能够重复 |
PRIMARY KEY | 主键约束 |
FOREIGN KEY | 外键约束 |
CHECK | 检查性约束 |
外键约束:
1.FOREIGN KEY:在子表中,定义一个表级的约束
2.REFERENCES:指定表和父表中的列
3.ON DELETE CASCADE:当删除父表时,级联删除子表记录
4.ON DELETE SET NULL:当删除父表时,将子表的相关依赖记录的外键置为null。
使用CONSTRAINT关键字为约束命名:
…, salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0), …
其他数据库对象
一)视图
1.视图是一种虚表,建立在已有表的基础上,视图赖以建立的这些表称为基表。
2.向视图提供数据内容的语句为SELECT语句,可以将视图理解为存储起来的SELECT语
句。
3.视图向用户提供基表数据的另一种表现形式。
视图优点:
1.简化复杂查询;
2.同样的数据,可以有不同的显示方式;
3.限制数据访问;
4.提供数据的相互独立。
**但视图不能呢个提高性能。
创建视图:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view1[(alias[, alias]…)] AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
- FORCE:子查询不一定存在
- NOFORCE:子查询存在(默认)
- WITH CHECK OPTION:通过该视图只能操作看得到的数据,即该视图查到的数据
- WITH READ ONLY:只能做查询操作
二)序列
可供多个用户用来产生唯一数值的数据库对象。
-自动提供唯一的数值
-共享对象
-主要用于提供主键值
-将序列值装入内存可以提高访问效率
-序列在下列情况会出现裂缝:
回滚,系统异常,多个表同时使用同一序列
-如果不将序列的值装入内存(NOCACHE),可使用表USER_SEQUENCES查看序列当前的
有效值。
定义序列:
CREATE SEQUENCE seq [INCREMENT BY n] 步长为n
[START WITH n] 起始值为n
[{MAXVALUE n | NOMAXVALUE}] 有无最大值
[{MINVALUE n | NOMINVALUE}] 有无最小值
[{CYCLE | NOCYCLE}] 是否循环
[{CACHE n | NOCACHE}]序列缓存长度,默认为20,NOCACHE为1
序列取值:
NEXTVAL: 返回序列中下一个有效的值。
CURRVAL:存放序列的当前值。
NEXTVAL应在CURRVAL之前指定,二者应同时有效
删除序列:
DROP SEQUENCE seq; 删除之后序列不能再次被引用
三)索引
一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中
-索引被删除或损坏,不会对表产生影响,其影响的只是查询的速度;
-索引一旦建立,Oracle管理系统会对其进行自动维护,而且由Oracle管理系统决定何
时使用索引;
-在删除一个表时,所有基于该表的索引会自动被删除;
-通过指针加速Oracle服务器的查询速度;
-通过快速定位数据的方法,减少磁盘I/O。
创建索引:
CREATE INDEX index1 ON table1(column1[, column2, …]);
以下情况可以创建索引:
1.列中数据值分布范围很广;
2.列经常在WHERE子句或连接条件中出现;
3.表经常被访问而且数据量很大,访问的数据大概占数据总量的2%到4%。
**索引列经常被更新时不适合创建索引。
删除索引:
DROP INDEX index1;
只有索引的拥有者或者拥有DROP ANY INDEX权限的用户才可以删除索引。
四)同义词
同义词就是别名:
-方便访问其他用户的对象
-缩短对象名字的长度
CREATE [PUBLIC] SYNONYM synonym1 FOR object1;
PLSQL程序设计基础语法
PL/SQL(Procedure Language/SQL)是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支,循环等),使SQL语言具有过程处理能力。PLSQL是面向过程的语言。
结构:
DECLARE
--说明部分(变量说明,光标申明,例外说明)
BEGIN
--语句序列(DML语句)
EXCEPTION
--例外处理
END;
说明部分:
-定义基本变量
-类型:char,varchar2,date,number,Boolean,long
-举例:
var1 char(15);
married boolean := true;
psal number(7,2);
- 引用型变量:
my_name emp.ename%type; 引用表中字段的类型作为变量类型
- 记录型变量:
emp_rec emp%rowtype; 引用表中某一行作为变量。
emp_rec.ename; 引用行中ename属性的值
程序体部分:
- IF语句:
1. IF 条件 THEN 语句1;
语句2;
END IF;
2. IF 条件 THEN 语句序列1;
ELSE 语句序列2;
END IF;
3. IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSE 语句;
END IF
循环语句:
1. WHILE循环语句:
WHILE total <= 25000 LOOP
…
Total := total + salary;
END LOOP;
2.LOOP循环语句:
LOOP
EXIT[WHEN条件]; 当条件成立时退出循环体
……
END LOOP;
3.FOR循环:
FOR i IN 1…3 LOOP
语句序列
END LOOP;