Oracle建表插数据等等

时间:2023-03-08 16:27:28

Oracle的表的管理:

表名和列的命名规则,详见 数据库命名规范

. 必须以字母开头
. 长度不能超过30个字符
. 不能使用Oracle的保留字
. 只能使用如下字符 column_name-Z,column_name-z,0-9,$,#等

Oracle支持的数据类型

字符类型:

char: 定长,最大2000个字节。
例子:char(10): 如果内容为'小杰',则前四个字节放'小杰',后添6个空格补全
varchar2(): 变长(不定长),最大4000个字符。
例子:varchar2(10): 如果内容为'小杰',Oracle分配四个字符,这样可以节省空间。
char 查询的速度极快但浪费空间,数据定长且数据量少的时候使用,数据量多或不定长用 varchar2  节省空间
long: 存储最大长度为2GB的变长字符数据

blob: 二进制数据可以存放图片/声音,4G,一般来讲,在真实项目中是不会把图片和声音真的往数据库里存放,一般存放图片、视频的路径,如果安全需要比较高的话,则放入数据库。
clob(character large object): 字符型大对象,最大4G

raw: 存储非结构化数据的变长字符数据,最长尾2KB
long raw: 存储非结构化数据的变长字符数据,最长尾2GB
rowid: 存储表中列的物理地址的二进制数据,占用固定的10个字节
urowid: 存储表示任何类型列地址的二进制数据
bfile: 把非结构化的二进制数据作为文件存储在数据库之外

数字类型:

number(n,m): 其中缺省m表示整数,范围:-10的38次方 到 10的38次方
可以表示整数,也可以表示小数,number(5,2) 表示一位小数,有5位有效数,2位小数,范围:-999.99到999.99,number(5) 表示一个5位整数,范围 99999 到 -99999
float: 存储浮点数

日期类型:

date: 包含年月日和时分秒,Oracle默认格式:1-1月-1999,timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。

建表:

create table table_name(
column1_name type [constraint constraint_def] [primary key] [default default_def],
column2_name type [constraint constraint_def] [references table2_name(column2_name)] [default default_def],
......
);

create table tb_Employee(
pk_Employee_ID number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(4)
); create table tb_Department(
pk_Department_ID number(4) primary key,
dname varchar2(14),
loc varchar2(13)
); create table tb_SalGra(
pk_SalGra_ID number primary key,
losal number,
hisal number
);

插入数据:

insert into tb_Department
values (10,'ACCOUNTING','NEW YORK');
insert into tb_Department
values (20,'RESEARCH','DALLAS');
insert into tb_Department
values (30,'SALES','CHICAGO');
insert into tb_Department
values (40,'OPERATIONS','BOSTON'); insert into tb_Employee
values(7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into tb_Employee
values(7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into tb_Employee
values(7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into tb_Employee
values(7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into tb_Employee
values(7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into tb_Employee
values(7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into tb_Employee
values(7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into tb_Employee
values(7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into tb_Employee
values(7788,'SCOTT','ANALYST',7566,'19-4月-1987',3000.00,NULL,20);
insert into tb_Employee
values(7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into tb_Employee
values(7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into tb_Employee
values(7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into tb_Employee
values(7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),1300,NULL,10); insert into tb_SalGra
values (1,700,1200);
insert into tb_SalGra
values (2,1201,1400);
insert into tb_SalGra
values (3,1401,2000);
insert into tb_SalGra
values (4,2001,3000);
insert into tb_SalGra
values (5,3001,9999);

select * from tb_Department;
select * from tb_Employee;
select * from tb_SalGra;

drop table table_name;--删除表
rename table_name to table_newname;--重命名表名或者使用
alter table table_name rename to table_newname
或者create table table_newname as select * from table_name;drop table table_name;
alter table table_name rename column column_name to column_newname;--修改表中的字段名
alter table table_name add column_name newtype;--增加字段
alter table table_name modify column_name newtype;--修改字段的类型
alter table table_name drop column column_name;--删除字段
alter session set nls_date_format='yyyy-mm-dd';--日期设置成中国人习惯的方式
alter session set time_zone;--改变会话时区

约束用于确保数据库数据满足特定的商业规则。

在Oracle中,约束包括:not null、unique,primary key,foreign key和check五种,增加约束
alter table table_name modify column_name not null;--非空
alter table table_name add constraint constraint_def unique(column_name);
alter table table_name add constraint constraint_def primary key(column_name);
alter table table_name add constraint constraint_def foreign key(column_name) references r2(column_name);
alter table table_name add constraint constraint_def check(条件);
alter table table_name drop constraint column_name constraint_def;--删除约束
alter table table_name modify column_name null;--为空
alter table table_name drop constraint primary key cascade;
禁止约束:
约束 disable;--新建约束时
alter table table_name disable constraint constraint_def;
允许约束:
alter table table_name enable constraint constraint_def;
获得约束信息:
select * from user_constraints;

DML(数据操作语言):

select * from tb_Employee;--查询
insert into table_name values(); --插入数据(表中内容)
delete from table_name where ...;--删除元组(表中内容),写日志,可恢复
truncate table table_name;--删除元组(表中内容),速度快,不写日志,不可恢复

update table_name set column_name where ...;--更新数据(表中内容)
update table_name set (a1,a2,a3)=(select (A1,A2,A3) from table_name where...) where...;

分页:
rownum
经典例句:
select * from (select column_name.*,rownum rn from (select * from tb_Employee) column_name where rn<=10) where rn>=6;
根据结果集创建表,复制一部分:
create table tb_Mytable(pk_ID,name) as select pk_Employee_ID,ename from tb_Employee;

进行行迁移:
insert into r2(A1,A2,A3) select a1,a2,a3 from r1 where ...;

常用sql关键词:

distinct;
(=、<、<=、>、>=、<>);--算术运算比较符
in;--包含测试
natural join;--相当于=、join..on、inner join..on
natural left outer join;--相当于left outer join..on =>右边字段(+)
natural right outer join;--相当于right outer join..on =>左边字段(+)
natural full outer join;--相当于natural left outer join union all natural right outer join
cross join;--交叉连接,也称笛卡尔连接
natural
any;
all;
问题:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号?
select ename,sal,deptno from tb_Employee where sal>all(select sal from tb_Employee where deptno=30);
或者select ename,sal,deptno from tb_Employee where sal>(select max(sal) from tb_Employee where deptno=30);--可以用min()或者是max()函数替代
like;--匹配测试
%:表示0到多个字符;_:表示任意单个字符;首字符为S,like 'S%'; 第三个字符为大写S,like '__S%'; 含有S,like '%S%';以"ab%cd"开头,like 'ab\%cd%' escape '\';
desc tb_Employee;--查看表的结构(describe(描述)的意思)
order by;
group by;--group by用于对查询的结果分组统计
having;--having子句用于限制分组显示结果
||;--字符串连接操作符
between and;--范围测试,在某个范围之内
is null;--空值测试
(and、or、not);--复合条件查询