SQL的类型
- 1、DML(Data Manipulation Language 数据操作语言): select insert update delete
- 2、DDL(Data Definition Language 数据定义语言): create table,alter table,truncate table,drop table,create/drop view,sequnece,index,synonym(同义词)
- 3、DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)
DML
插入数据
insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10); -- 或 采用地址符 & insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno); 输入 empno 的值: 1002 输入 ename 的值: 'Mary' 输入 sal 的值: 2000 输入 deptno 的值: 20 原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno) 新值 1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,20) -- 批量添加数据 --一次性将emp中，所有10号部门的员工插入到emp10中 insert into emp10 select * from emp where deptno=10;
海量插入数据
1、数据泵（PLSQL程序）
dbms_datapump(程序包)
2、SQL*Loader
3、外部表
delete和truncate的区别
1、delete逐条删除；truncate先摧毁表 再重建
2、delete是DML truncate是DDL
(可以回滚) （不可以回滚）
执行DDL类型的命令会提交事务
3、delete不会释放空间 truncate会
4、delete会产生碎片 truncate不会
- 碎片：在Oracle中有一个指针（高水位线）记录着下次insert的位置，如果我们在数据的中间执行delete命令就会产生碎边（数据不连续）
5、delete可以闪回(flashback) truncate不可以
- 闪回：还原数据
6、delete操作比truncate操作快（原因：可以还原数据）
Oracle中的事务
1、起始标志: 事务中的第一条DML语句
2、结束标志：提交： 显式 commit 隐式： 正常退出 DDL DCL
回滚： 显式 rollback 隐式： 非正常退出 掉电 宕机
创建和管理表（DDL）
1、创建表
CREATE TABLE test1 (
tid NUMBER,
tname VARCHAR2 ( 20 )
);
-- 或直接copy别的表结构
CREATE TABLE emp20 AS SELECT
*
FROM
EMP
WHERE
DEPTNO = 20;-- where条件有效，复制数据，无效，只复制表结构
2、查看表结构
desc test1 -- SQLplus
3、增加新列
ALTER TABLE test1 ADD photo blob; -- blob:二进制大对象，是一个可以存储二进制文件的容器
4、修改列
ALTER TABLE test1 MODIFY tname VARCHAR2 ( 40 ); -- 修改tname列的varchar长度为40
5、删除列
ALTER TABLE test1 DROP COLUMN photo; -- 删除photo字段
6、重命名列
ALTER TABLE test1 RENAME COLUMN tname TO username; -- 修改列名tname为username
7、重命名表
RENAME test1 TO test2;
8、删除表
DROP TABLE test2; -- 表并没有完全被删除，只是别放进了回收站中
回收站
注意：管理员没有回收站
可以使用select查询出（回收站中）表中的内容
查看回收站
show recyclebin;
清空回收站
purge recyclebin;
闪回删除
flashback table test2 to before drop;
9、约束（数据完整性由各种约束保证）
1）检查约束
create table test3(
tid number,
tname varchar2(20),
gender varchar2(2) check (gender in ('男','女')), -- 检查约束
sal number check (sal > 0)
);
SQL> insert into test3 values(1,'Tom','男',2000);
已创建 1 行。
SQL> insert into test3 values(2,'Mike','*啊*',2000);
insert into test3 values(2,'Mike','啊',2000)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005393)
2）各种约束
CREATE TABLE student (
sid NUMBER constraint student_pk primary key, -- 主键约束，constraint字段表示使用约束
sname VARCHAR2 ( 20 ) constraint student_name_notnull NOT NULL, -- 非空约束
gender VARCHAR2 ( 2 ) constraint student_gender CHECK (gender IN ( '男', '女' )), -- 检查约束
email VARCHAR2 ( 40 ) constraint student_email_unique UNIQUE -- 唯一约束
constraint student_email_notnull NOT NULL, -- 一个字段设置两个约束
deptno NUMBER constraint student_fk references dept ( deptno ) ON DELETE SET NULL -- 外键约束，设置级联置空
);
视图
视图就是封装了一条复杂查询的语句。视图是一个虚表，它最大的优点就是简化复杂查询，所以不建议对视图进行其他的操作。
普通用户默认是不能创建视图的，需要管理员进行授权
管理员登录
> sqlplus sys/123 as sysdba -- 用户名和密码可以随便写
SQL> show user
USER 为 “SYS”
为scott用户授权创建视图
grant create view to scott；
创建视图
CREATE 【OR REPLACE】 VIEW empinfoview AS SELECT -- CREATE OR REPLACE表示存在替换，不存在创建 e.empno, e.ename, e.sal, e.sal * 12 annsal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
查看视图
SELECT * FROM empinfoview;
序列
在很多数据库中都存在一个自动增长的列，如果现在要想在Oracle中完成自动增长的功能，则只能依靠序列完成所有的自动增长操作，需要用户手工完成处理。
Oracle将序列装入内存可以提高访问效率
序列创建语法
创建一个简单的序列
create sequence myseq;
索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。
例如：
SELECT * FROM emp WHERE sal>1500;
此时由于在sal上没有设置索引，所以它的查询过程是采用逐行判断的方式完成的，这种操作随着数据量的上升，则性能会出现越来越多的问题。于是引入索引：
索引中保存的是rowid行地址
行地址
SQLplus命令
通过rowid查询数据
1、单列索引
单列索引是基于单个列建立的索引：
CREATE index 索引名 on 表名(列名) -- 示例：create index myindex on emp(deptno)
2、复合索引
复合索引是基于多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同
同义词（表的别名）
语法：
查看其它用户的表
1）为scott用户授权访问hr下的employees表
create synonym hremp for hr.employees;
2）为scott用户授权添加同义词
create synonym hremp for hr.employees;
3）使用scott用户创建hr.employees表的别名
CREATE SYNONYM hremp FOR hr.employees; -- 私有的，只有当前用户可以使用；在create后加上public表示公有的，所有用户都可以使用
4）查询
5）删除同义词
DROP SYNONYM hremp;