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);--复合条件查询

相关文章