关于oracle自带的表

关于oracle自带的表***********************************8

emp：

empno：员工编号；ename：员工名字；job：员工工种；mgr: 上司；hiredate：入职时间；sal：基本工资；comm：补贴；deptno：所属部门编号；

dept：

deptno：部门编号；dname：部门名称；loc：地理位置；

salgrade：

grade：工资等级；losal：最低限额；hisal：最高限额；

dual：

系统自带的一张空表；可用于计算数据：select 2*3 from dual;

**sql_function1**********************************************************

select lower(ename) from emp; 取出的名字全部变成小写。

select ename from emp where lower(ename) like '_a%';取出的名字变成小写后不含字母a

select substr(ename, 2, 3) from emp;从第二个字符截，截取三个字符。

select cha(65) from dual; 将数字转化为字符（显示为a）。

select ascii('A') from dual; 将字符转化为数字。

select round(23.652) from dual; (显示24)

select round(23.652, 2) from dual; （显示23.65）

select round(23.652, -1) from dual; （显示20）

select to_char(sal, '$99,999.9999')from emp;强制转化为指定的格式。

select to_char(sal, 'L0000.0000')from emp;同上。

select to_char(hiredate, YYYY-MM-DD HH:MI:SS) from emp; 对时间格式显示处理。

select to_char(sysdate, YYYY-MM-DD HH:MI:SS) from emp; 12进制。

select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from emp; 24进制。

***********************************************************************

**sql_function2*******************************************************

select ename, hiredate from emp where hiredate > to_date('1981-2-20' 12:34:52, 'YYYY-MM-DD HH24:MI:SS'); 函数to_date 将字符转化为时间格式。

select sal from emp where sal > to_number('$1,250.00', '$9,999.99'); 函数to_number将字符转化为数字格式，以作比较。

select ename sal*12 + nvl(comm 0) from emp; 函数nvl作用为当comm为null的时候当作处理，避免了comm为null给结果带来的不便。

*************************************************************************

**group_function*******************************************************

select max(sal) from emp;输出薪水值最高的。

select min(sal) from emp;输出薪水值最低的。

select avg(sal) from emp;输出平均薪水值。

select to_char(avg(sal),'99999999.99') from emp;按照指定格式输出平均薪水值。

select round(avg(sal),2) from emp; 精确到小数点后面2位。

select sum(sal) from emp; 输出薪水值的总和。

select count(*) from emp;求出一共有多少条记录。

select count(*) from emp where deptno = 10; 求部门为10号的记录条数。

select count(ename) from emp; 求一共有几个名字。

select count(comm) from emp; 求非空comm的记录条数。

select count(deptno) from emp;

select count(distinct deptno) from emp;

*************************************************************************

**group_by*************************************************************

select deptno, avg(sal) from emp group by deptno; 将部门薪水平均分组。

select deptno, job, max(sal) from emp group by deptno; 按组合分组。

select ename, max(sal) from emp where sal = (select max(sal) from emp);

select ename max(sal) from emp group by deptno; 这样是错误的。

select deptno max(sal) from emp group by deptno; 这样可行。

************************************************************************

**having****************************************************************

select avg(sal), deptno from emp group by deptno;

select avg(sal), deptno from emp group by deptno having avg(sal) > 2000; having是对分组进行限制。

1 select avg(sal) 选择

2 from emp 表原

3 where sal > 1200 条件过滤

4 group by deptno 分组

5 having avg(sal) > 1500 对结果进行限制

6 order by avg(sal) desc 对产生的结果进行排序

**********************************************************************

**子查询**************************************************************

select 语句里面套另外一个select语句。

select ename, sal from emp where sal > (select avg(sal) from emp);

select ename,sal from emp join(select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);

**self_table**********************************************************

自连接：select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; 把一个表当成两个来使用。

***********************************************************************

**sql1999_table_connections********************************************

1999年标准：select ename, dname from emp cross join dept;

旧：

新：select ename, dname from emp join dept on (emp.deptno = deptno);

select ename, dname from emp join dept using(deptno);(了解即可，不推荐使用)

select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

左外连接：select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);

右外连接：select ename, dname from emp e right outer join dept d (e.deptno = d.deptno);

全外连接：select ename, dname from emp e full join dept d (e.deptno = d.deptno);

************************************************************************

**求部门平均薪水等级****************************************************

select deptno, avg(grade) from (select deptno, ename, grade from emp join salgrade s on (t.avg_sal between s.losal and s.hisal)) t group by deptno;

*************************************************************************

**部门中那些人是经理人**************************************************

select ename from emp where empno in (select distinct mgr from emp);

************************************************************************

**不用组函数求薪水的最高值（面试题）************************************

select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on

(e1.sal

************************************************************************

**平均薪水最高的部门的编号****************************************

select deptno, avg_sal from

(select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from

(select avg(sal) avg_sal , deptno from emp group by deptno)

)

*********************************************************************

**求平均薪水最高的部门的部门名称

select dname from dept where deptno =

(

select deptno, avg_sal from

(select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from

(select avg(sal) avg_sal , deptno from emp group by deptno)

)

)

方法二：

select dname from dept where deptno =

(

select deptno, avg_sal from

(select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from

(select avg(sal) avg_sal, deptno from emp group by deptno)

)

)

**********************************************************************

**求平均薪水的等级最低的部门的部门名称

**************************************

**creat new user and insert****************************************

1--backup scott

2--create user

create user wp identified by wp default tablespace users quota 10M on users；(创建新用户)

grant create session, create table , create view to wangpeng(赋予新用户权限)

3--import the data

insert into dept values (50, 'game' 'bj'); 插入数据。

insert into dept (deptno, dname) values (60,'game'); 同上。

insert into dept2 select * from dept; 数据又挨着插了一遍。

rollback；回退命令。

create table dept2 as select * from dept; 备份数据。

*************************************************************************

**rownum***************************************************************

select emp, ename from emp where rownum <=5; 取前四行。

rownum只能和< 和<=使用，大于号和等于号不支持。

select ename , sal from

(select ename, sal from emp order by sal desc) where rownum <=5;

select ename, sal from

(

select ename,sal, rownum r from

(select ename, sal from emp order by sal desc)

) where r >=6 and r<=10; 求薪水最高的第6到第10名雇员。

************************************************************************

**update*******************************************************

update emp2 set sal = sal*2, ename=ename||'-' where deptno = 10; 更改。

delete from emp2; 删除。

****************************************************************

**创建新表与约束****************************************************

create table stu

(

id number(6) primary key, //主键约束，主键非空且唯一，也可以写在后面constraint stu_id_pk primary key(id),

name varchar2(20) constraint stu_name_nn not null, //not null指定必须为非空

sex number(1),

age number(3),

sdate date,

grade number(2) default 1,

class number(4),

email varchar2(50),

constraint stu_name_email_uni unique(email, name) //email与name组合唯一

)

外键参考的值段必须是主键，加上constraint stu_class_fk foreign (class) reference class(id), *************************************************************************

**alter修改表结构***************************************************

alter table stu add(addr varchar2(100)); //添加字段

alter table stu drop(asddr);

alter table stu modify(asddr varchar2(50));

alter table stu drop constraint stu_class_fk; //删除约束条件

alter table stu add constraint stu_class_fk foreign key (class) reference class (id); //重新添加约束条件

****************************************************************

**查询系统的表**********************************************

select table_name from user_tables; //当前用户下所有的表

select view_name from user_views; //当前用户下的视图

select constraint_name，table_name from user_constraints;

desc dictionary //数据字典表

******************************************************************

**索引********************************************************

create index idx_stu_email on stu(email); //创建索引

drop index idx_stu_email; //删除索引

select index idx_name from user_indexes;

select view_name from user_views;

desc (视图的名称)

create view v$_stu as select id, name, age from stu; 只给予查看id，name，age 的权力，保护私有数据。

*********************************************************************

**sequence序列*****************************************************

create sequence seq; //创建序列

select seq.nextval from dual;

insert into article values (seq.nextval, 'a', 'b'); //添加数据*********************************************************************

**三范式**********************************************************

第一范式：要有主键且列不可分。

第二范式：不能存在部分依赖：非主键的字段不能依赖于组合主键的一部分。

第三范式：不能存在传递依赖。

*******************************************************************

**PL_SQL语句************************************************************* 简单小程序：

SQL> set serveroutput on;

SQL> begin

dbms_output.put_line('HelloWorld!');

end;

/

HelloWorld! （显示的结果）

运行中dcomcnfg命令是查看系统组件服务

变量申明的规则：

变量名不能使用保留字，如from，select等

第一个字符必须是字母。

变量名最多包含30个字符

不要与数据库的表或者列同名

每一行只能申明一个变量

常用变量的类型：

binary_integer: 整数，主要用来计数而不是用来表示字段类型

number：数字类型

char：定长字符串

varchar2：变长字符串

date：日期

long：长字符串，最长2G

boolean：布尔类型，可取true，false和null值。

---Table变量类型

declare

type_table_emp_empno is table of emp.empno%type index by binary_integer;

begin

v_empnos(0) :=7369;

v_empnos(2) :=7839;

v_empnos(-1) :=9999;

dbms_output.put_line(v_empnos(-1));

end;

---Record变量类型

declare

v_temp dept%rowtype;

begin

v_temp.deptno := 50;

v_temp.dname := 'aaaa';

v_temp.loc := 'bj';

dbms_output.put_line(V_temp.deptno || ' ' || v_temp,dname);

end;

---使用%rowtype申明record变量

declare

v_temp dept%rowtype;

begin

v_temp.deptno := 50;

v_temp.dname := 'aaaa';

v_temp.loc := 'bj';

dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);

end;

----------------------------

declare

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

select ename, sal into v_ename,v_sal from emp where empno = 7369;

ebms_output.put_line(v_ename || ' ' || v_sal);

end;

/

(显示的结果为SMITH 800)

----------------------------

declare

v_deptno emp2.deptno%type :=10;

v_count number;

begin

--update emp2 set sal = sal/2 where deptno = v_deptno;

select count(*) into v_count from emp2;

dbms_output.put_line(sql%rowcount || '条记录被影响');

commit;

end;

--ddl语句---------------------------

begin

execute immediate 'create table T (nnn varchar2(20) default "aaa")'; end;

/

---------------------------------------

declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp

where empno = 7369;

if (v_sal < 1200) then

dbms_output.put_line('low');

elseif (v_sal <2000) then

dbms_output.put_line('middle');

else

dbms_output.put_line('hign');

end if;

end;

/

-----------------------------------------------

declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp

where empno =7839;

if(v_sal < 2500) then

update emp set sal = sal*2 where empno = 7839;

dbms_output.put_line(sal);

if(v_sal = 2500) then

dbms_output.put_line(sal);

then

update emp set sal = sal/2 where empno = 7839;

dbms_output.put_line(sal);

end if;

end;

--错误处理---------------------------------------------------------- create sequence seq_errorlog_id start with 1 increment by 1;

declare

v_deptno dept.deptno%type :=10;

v_errcode number;

v_errmsg varchar2(1024);

begin

delete from dept where deptno = v_deptno;

commit;

exception

when others then

rollback;

v_errcode := SQLCODE;

v_errsmg :=SQLRRM;

insert into errorlog values (seq_errorlog_id.nextval, v_errmsg, sysdate);

commit;

end;

--游标

declare

cursor c is

select * from emp;

v_emp c%rowtype;

begin

open c;

loop

fetch c into v_emp;

exit when (c%notfound);

dbms_output.put_line(v_emp.ename);

end loop;

close c;

end;

/

--使用for循环的游标

declare

cursor c is

select * from emp;

begin

for v_emp in c loop

dbms_output.put_line(v_emp.ename) (v_emp在前面已经申明)

end loop;

end;

--带参数的游标

declare

cursor c(v_deptno emp.deptno%type, v_job emp.job%type)

is

select ename, sal from emp where deptno = v_deptno and job = v_job;

--v_temp c%rowtype;

begin

for v_temp in c(30, 'CLERK') loop

dbms_output.put_line(v_temp.ename);

--存储过程

当有编译的错误时，用命令show error 可显示出错的地方。

create or replace procedure p

is

cursor c is

select * from emp2 for update;

begin

for v_emp in c loop

if (v_emp.deptno = 10) then

update emp2 set sal = sal +10 where current of c;

elseif (v_emp.deptno = 20) then

update emp2 set sal = sal +20 where current of c;

else

update emp2 set sal = sal +20 where current of c;

end if;

end loop;

commit;

end;

select * from emp2;

begin

p;

end;

--带参数的存储过程

create or replace procedure p

(v_a in number, v_b number, v_ret out number, v_temp in out number)

is

.

.

.

*********************************************************************

**trigger************************************************************

创建触发器：

create or replace trigger trig

after update on dept

for each row

begin

update emp set deptno = :NEW.deptno where deprno = :OLD.deptno;

end;

/

然后就可以这样子更改了：update emp set deptno = 99 where deptno = 10;

************************************************************************

**树状结构的存储与展示************************************************** create table article

(

id number primary key,

cont varchar2(4000)

pid number,

isleaf number(1), --0代表非叶子节点，1代表叶子节点alevel number(2),

);

insert into article values (1, '蚂蚁大战大象', 0, 0, 0); insert into article values(2,'大象被打趴下了'，1，0，1)；insert into article values(3,'蚂蚁也不好过'，2，1，2)；insert into article values(4,'瞎说'，2，0，2)；

insert into article values(5,'没有瞎说'，4，1，3)；insert into article values(6,'怎么可能'，1，0，1)；insert into article values(7,'怎么没有可能'，6，1，2)；insert into article values(8,'可能性是很大的'，6，1，2)；insert into article values(9,'大象进医院了'，2，0，2)；insert into article values(10,'护士是蚂蚁'，9，1，3)

ORACLE项目2：关于创建表参数的详细说明

命令格式（红字为关键字，紫字为注释） CREATE TABLE“方案名”.“表名” (字段1 数据类型（长度）， 字段2 数据类型（长度）， ……., 字段n 数据类型（长度）， CONSTRAINT约束名1 约束类型(约束参数)， CONSTRAINT约束名2 约束类型(约束参数) ……… CONSTRAINT约束名n 约束类型(约束参数)) TABLESPACE “表空间名” STORAGE (INITIAL 1M*第一个区的大小*NEXT 1.5M*第二个区的大小* MINIEXTENTS 2 *创建时已分配的区数* MAXEXTENTS 5 *可分配最大区数* PCTINCREASE10 *每个区相对于上个区增长的百分比* BUFFER_POOL DEFAULT *其参数有DEFAUT,RECYCLE和KEEP*) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255; 要点释义 PCTFREE:指定表的每一个数据块为将来更新现有表行所保留的空间百分比。其值必须在0和99之间，默认值为10。 PCTUSED:指定维持表的每个数据块已用空间的最小百分比。当块中已用空间低于PCTUSED的时候，该块就成为新行插入对象。默认值40。 注意:PCTFREE和PCTUSED之和要小于100，两者结合可以确定是把新行插入现有数据块还是新块中。 INITRANS:分配给表的每一个数据块的并发事务条目的初值。范围1-255，默认1。通常不需要改变INITRANS值。 每个更新块的事务需要该块中的一个事务条目，事务条目大小取决于操作系统，该参数保证最小数目的并发事务可以更快，有助避免动态分配事务条目的开销。 MAXTRANS:可更新分配给表的数据块的最大并发事务数。范围1-255。默认255。一般不要更改MAXTRANS的值。 如果一个块中的并发事务超过了INITRANS的值，那么ORACLE在块中动态分配事务条目，直到超过MAXTRANS值。

oracle创建表与增删改

-- 数据准备：创建工资等级表 CREATE TABLE salgrade_test ( grade NUMBER (2), lowsal NUMBER (7, 2), hisal NUMBER (7, 2) ); -- 查看工资等级表结构 DESC salgrade; -- 查看工资等级表数据 SELECT * FROM salgrade_test; -- 插入数据 INSERT INTO salgrade_test VALUES (1, 10001, 99999); INSERT INTO salgrade_test VALUES (2, 8001, 10000); INSERT INTO salgrade_test VALUES (3, 6001, 8000); INSERT INTO salgrade_test VALUES (4, 4001, 6000); INSERT INTO salgrade_test VALUES (5, 1, 4000); -- 计算员工的薪水等级 SELECT NAME, salary, grade FROM emp_test, salgrade_test WHERE emp_test.salary BETWEEN salgrade_test.lowsal AND salgrade_test.hisal; -- ------------------------------------------------------SELECT NAME, salary, grade FROM emp_test

FULL JOIN salgrade_test ON emp_test.salary BETWEEN salgrade_test.lowsal AND salgrade_test.hisal; -- 复制表：只复制结构 , 不复制数据 CREATE TABLE salgrade_copy AS ( SELECT * FROM salgrade_test WHERE 1 <> 1 ); SELECT * FROM salgrade_copy; -- 复制表：复制一部分数据----通过设置别名的方式 , 指定新表中的列名(year_sal) CREATE TABLE emp_test_copy AS ( SELECT emp_id, NAME, salary * 12 year_sal FROM emp_test WHERE dept_test_id = 10 ); SELECT * FROM emp_test_copy; -- 复制表：复制一部分数据 -- 新表中的列名 CREATE TABLE emp_count (did, emp_num) AS ( SELECT dept_test_id, COUNT (*) FROM emp_test GROUP BY dept_test_id ); SELECT * FROM emp_count; -- 创建一个同 emp 表结构相同的空表 , 将部门号为 10 的员工信息放入该表 -- 如果有一张表 emp 的数据量为一百万条 , 此时需要建立 1 张测试表只放入少量测试数据( 如 100条 ) , 执行步骤如下所示： -- 第 1 步创建一个空表 CREATE TABLE emp_copy AS (

oracle创建表

CREATE TABLE DEPT(id NUMBER(7),name VARCHAR2(25)); 2、将Departments表中数据插入到DEPT表中 SELECT * FROM HR.DEPARTMENTS; INSERT INTO DEPT(ID,NAME) SELECT * FROM DEPARTMENTS; CREATE TABLE EMP (id NUMBER(7),last_name VARCHAR2(25),first_name VARCHAR2(25),dept_id NUMBER(7)); 4、修改EMP表，允许输入更长（50）员工的姓氏（LAST_NAME） ALTER table EMP modify(last_name varchar2(50)); 5、在数据字典中确认DEPT表和EMP表的创建结果 DESCRIBE dept; DESCRIBE emp; 6、根据表Employees的结构创建表Employees2表，仅包含EMPLOYEE_ID、FIRST_NAME、 LAST_NAME、SALARY和DEPARTMENT_ID。将新表中的各列分别命名为EMPLOYEE_ID、FIRST_NAME、LAST_NAME、SALARY和DEPT_ID CREATE TABLE employees2 as SELECT employee_id,first_name,last_name,salary,department_id dept_id FROM employees; 7、删除EMP表 HR> drop table emp; 8、将EMPLOYEES2表重命名为EMP HR> alter table employees2 rename to emp; 9、删除表EMP的FIRST_NAME列，并确认

Oracle 建表(一对多)代码及相关约束示例

建表(一对多)代码及相关约束 create table t_class( c_id number(3) primary key, c_name varchar2(20) not null ); create table t_stu( s_id number(5) primary key, s_name varchar2(8) not null, sex char(2) default '男', birthday date, school_age number(2) check(school_age>0), school_score number(5,2), c_id number(3), id_card char(18) unique, foreign key(c_id) references t_class(c_id) ); 附：测试数据： insert into t_class values(1,'订单班'); insert into t_class values(2,'开发班'); insert into t_class values(3,'美工班'); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (1, 'a', '男', to_date('01-01-1980', 'dd-mm-yyyy'), 24, 90, 1, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (2, 'b', '男', to_date('11-05-1981', 'dd-mm-yyyy'), 23, 80, 1, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (3, 'c', '女', to_date('19-09-1982', 'dd-mm-yyyy'), 22, 50, 1, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD) values (4, 'd', '女', to_date('28-01-1984', 'dd-mm-yyyy'), 21, 80, 2, null); insert into T_STU (S_ID, S_NAME, SEX, BIRTHDAY, SCHOOL_AGE, SCHOOL_SCORE, C_ID, ID_CARD)

oracle创建表基础

?VARCHAR2(size)和NVARCHAR2(size)：变长字符型数据。 首先，该数据类型存储变长的字符数据，在使用该数据类型定义数据时，该数据的存储区大小是不固定的，依据存储数据的长度进行动态分配存储区。参数size是该变量存储的最大的字符数，该值最大为4000。size的最小或默认值都是1。一般在定义该数据类型时，都要指定该长度值，即指定size值。NVARCHAR2(size)的不同之处在于它支持全球化数据类型，支持定长和变长字符集。 ?CHAR(size) NCHAR(size)：定长字符型数据 该数据类型一旦定义，则存储该变量的存储区的大小就固定下来。显然在存储区分配上它没有VARCHAR2(size)和NVARCHAR2(size)数据类型具有动态性，但是在实际中，如果可以预测到一个变量存储的字符数量，且数量不是很大，则最好还是使用定长字符型数据来定义该变量，这样可以提高存储的效率。因为使用变长字符型数据要不断的计算存储的数据长度，再分配存储数据块，会消耗计算资源。定长字符型数据的最小值和默认值都为1个字符，而最大值为2000。NCHAR(size)的不同之处在于它支持全球化数据类型，支持定长和变长字符集，此时定长字符型数据的最小和默认值都为1个字节。 ?DATE：日期型数据 ORACLE服务器使用7个定长的存储区存储日期型数据，它可以月，年，日，实际，时，分和秒。日期型数据的取值范围从公元前4712年1月1日到公元9999年12月31日。 ?NUMBER(P,S)：数字型数据。 参数p指十进制数的中长度，s为该十进制数小数点后的位数，如NUMBER(10,2)表示该数字型数据的中长度为10位，而小数后为2为。其中参数p的最大值为38，最小值为1，而参数s的最大值为124，最小值为-84。

Oracle tablespace创建参数

Oracle tablespace创建参数 ORACLE中，表空间是数据管理的基本方法，所有用户的对象要存放在表空间中，也就是用户有空间的使用权，才能创建用户对象．否则是不充许创建对象，因为就是想创建对象,如表,索引等，也没有地方存放,Oracle会提示:没有存储配额．因此，在创建对象之前，首先要分配存储空间. 分配存储，就要创建表空间： 创建表空间示例如下： CREATE TABLESPACE "SAMPLE" LOGGING DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 上面的语句分以下几部分： 第一: CREATE TABLESPACE "SAMPLE" 创建一个名为 "SAMPLE" 的表空间. 对表空间的命名,遵守Oracle 的命名规范就可了. ORACLE可以创建的表空间有三种类型: (1)TEMPORARY: 临时表空间,用于临时数据的存放; 创建临时表空间的语法如下: CREATE TEMPORARY TABLESPACE "SAMPLE"...... (2)UNDO : 还原表空间. 用于存入重做日志文件. 创建还原表空间的语法如下: CREATE UNDO TABLESPACE "SAMPLE"...... (3)用户表空间: 最重要,也是用于存放用户数据表空间 可以直接写成: CREATE TABLESPACE "SAMPLE" TEMPORARY 和 UNDO 表空间是ORACLE 管理的特殊的表空间.只用于存放系统相关数据. 第二: LOGGING 有 NOLOGGING 和 LOGGING 两个选项, NOLOGGING: 创建表空间时,不创建重做日志. LOGGING 和NOLOGGING正好相反, 就是在创建表空间时生成重做日志. 用NOLOGGING时,好处在于创建时不用生成日志,这样表空间的创建较快,但是没能日志,数据丢失后,不能恢复,但是一般我们在创建表空间时,是没有数据的,按通常的做法,是建完表空间,并导入数据后,是要对数据做备份的,所以通常不需要表空间的创建日志,因此,在创建表空间时,选择NOLOGGING,以加快表空间的创建速度. 第三: DATAFILE 用于指定数据文件的具体位置和大小. 如: DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M

Oracle-基本建表语句

--创建用户 create user han identified by han default tablespace users Temporary TABLESPACE Temp; grant connect,resource,dba to han; //授予用户han开发人员的权利 --------------------对表的操作-------------------------- 创建表格语法: create table 表名( 字段名1 字段类型(长度) 是否为空, 字段名2 字段类型是否为空 ); -增加主键 alter table 表名 add constraint 主键名 primary key (字段名1); -增加外键: alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2); 在建立表格时就指定主键和外键 create table T_STU ( STU_ID char(5) not null, STU_NAME varchar2(8) not null, constraint PK_T_STU primary key (STU_ID) ); 主键和外键一起建立： create table T_SCORE ( EXAM_SCORE number(5,2),

EXAM_DATE date, AUTOID number(10) not null, STU_ID char(5), SUB_ID char(3), constraint PK_T_SCORE primary key (AUTOID), constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID) ) --创建表 create table classes( id number(9) not null primary key, classname varchar2(40) not null ) --查询表 select * from classes; --删除表 drop table students; --修改表的名称 rename alist_table_copy to alist_table; --显示表结构 describe test --不对没查到 -----------------------对字段的操作 ----------------------------------- --增加列 alter table test add address varchar2(40); --删除列 alter table test drop column address; --修改列的名称 alter table test modify address addresses varchar(40; --修改列的属性 alter table test modi

Oracle基础练习题及答案(表约束)

练习作业 创建表emp1，字段如下 eno char(3), ename char(6) sex char(2) age number(2) dno char(3) 并插入如下数据 1 ，TOM ，男，21 ，’001’ 2 ，JERRY ，男，21 ，’002’ 3 ，KATE ，女，21 ，’003’ 4 ，MARY ，女，21 ，’004’ 5 ，JACK ，男，21 ，’005’ i.在eno字段上创建主键约束 ii.在ename字段上创建非空约束 iii.创建检查约束判断age在18到60岁之间的男性或者age在18到55岁之间的女性iv.在dno字段上创建唯一性约束 create table emp1( eno char(3) primary key, ename char(6) not null, sex char(2), age number(2), dno char(3) unique, constraint sex_age check( ((age between18and60) and sex='男') or ((age between18and55) and sex='女')) ) insert into emp1 values('1','TOM','男',21,'001') insert into emp1 values('2','JERRY','男',21,'002');

insert into emp1 values('3','KATE','女',21,'003'); insert into emp1 values('4','MARY','女',21,'004'); insert into emp1 values('5','JACK','男',21,'005'); v.创建和emp1表字段相同的emp_bak表，将emp_bak表的eno字段与emp表的eno字段创建外键约束 create table emp_bak as select * from emp1 where1=2 alter table emp_bak add constraint v foreign key(eno) references emp1(eno); 1、请创建一个表，表名为phone,表结构如下 电话号码（PHONENUM VARCHAR2（8）） 电话费（PAY number(8,2)） 号码等级（NUMLEVEL VARCHAR2（4）） 费用日期（PAYDATE varchar2(12) create table phone ( phonenum varchar2(8), pay number(8,2), numlevel varchar2(4), paydate varchar2(12) ) 1）插入以下两条数据. 123456， 600， pt04, 20051220 888888, 900, pt05, 20051019. insert into phone values('123456',600,'pt04', '20051220'); insert into phone values('888888',900,'pt05', '20050919');

Oracle创建表的语法详解

Oracle创建表语句(Create table)语法详解及示例 创建表(Create table)语法详解 1. ORACLE常用的字段类型 ORACLE常用的字段类型有 VARCHAR2 (size) 可变长度的字符串, 必须规定长度 CHAR(size) 固定长度的字符串, 不规定长度默认值为１ NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数 最长38位. 不够位时会四舍五入. DATE 日期和时间类型 LOB 超长字符, 最大可达4G CLOB 超长文本字符串 BLOB 超长二进制字符串 BFILE 超长二进制字符串, 保存在数据库外的文件里是只读的. 数字字段类型位数及其四舍五入的结果 原始数值1234567.89 数字字段类型位数存储的值 Number 1234567.89 Number(8) 12345678 Number(6) 错 Number(9,1) 1234567.9 Number(9,3) 错 Number(7,2) 错 Number(5,-2) 1234600 Number(5,-4) 1230000 Number(*,1) 1234567.9 2. 创建表时给字段加默认值和约束条件 创建表时可以给字段加上默认值 例如 : 日期字段 DEFAULT SYSDATE

这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间 创建表时可以给字段加上约束条件 例如: 非空 NOT NULL 不允许重复 UNIQUE 关键字 PRIMARY KEY 按条件检查 CHECK (条件) 外键 REFERENCES 表名(字段名) 3. 创建表的例子 CREATE TABLE DEPT( EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13)) ; CREATE TABLE region( ID number(2) NOT NULL PRIMARY KEY, postcode number(6) default '0' NOT NULL, areaname varchar2(30) default ' ' NOT NULL); 4. 创建表时的命名规则和注意事项 1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,# 2)大小写不区分 3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来． 4)用和实体或属性相关的英文符号长度有一定的限制 注意事项: 1)建表时可以用中文的字段名, 但最好还是用英文的字段名 2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面 3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引 4)一个表的最多字段个数也是有限制的，254个. 5. 约束名的命名规则和语法

ORACLE建表样例

ORACLE数据库建表： ----------create table CREATE TABLE MMS_APP_ORDER_INFO ( ID_APP_ORDER_INFO VARCHAR2(50) DEFAULT SYS_GUID() NOT NULL, DEVICEID VARCHAR2(50) NOT NULL, LOGIN_APP_ORDER VAECHAR2(1000) NOT NULL, CREATED_BY VARCHAR2(100) NOT NULL , UPDATED_BY VARCHAR2(100) NOT NULL , DATE_CREATED DATE NOT NULL , DATE_UPDATED DATE NOT NULL ); --------ADD COMMENTS TO THE TABLE COMMENT ON TABLE MMS_APP_ORDER_INFO IS 'APP排序信息表'; --------ADD COMMENTS TO THE COLUMNS COMMENT ON COLUMN MMS_USER_APP_INFO.ID_APP_ORDER_INFO IS '编号'; COMMENT ON COLUMN MMS_USER_APP_INFO.DEVICEID IS '设备ID'; COMMENT ON COLUMN MMS_USER_APP_INFO.LOGIN_APP_ORDER IS '设备已登陆APP顺序（倒叙）'; COMMENT ON COLUMN MMS_USER_APP_INFO.DATE_CREATED IS '创建时间'; COMMENT ON COLUMN MMS_USER_APP_INFO.CREATED_BY IS '创建人'; COMMENT ON COLUMN MMS_USER_APP_INFO.DATE_UPDATED IS '更新时间'; COMMENT ON COLUMN MMS_USER_APP_INFO.UPDATED_BY IS '更新人'; ---------create index CREATE UNIQUE INDEX MAAMDATA.PK_MMS_APP_ORDER_INFO ON MAAMDATA.MAAM_APP_ORDER_INFO(ID_APP_LOGIN_INFO); ALTER TABLE MAAMDATA.MAAM_USER_APP_INFO ADD CONSTRAINT PK_MAAM_APP_ORDER_INFO PRIMARY KEY(ID_APP_LOGIN_INFO) USING INDEX PK_MAAM_APP_ORDER_INFO; --------create synonym(同义词) CREATE PUBLIC SYNONYM MAAM_APP_ORDER_INFO FOR MAAMDATA.MAAM_APP_ORDER_INFO; ------create privilege (权限) 使用GRANT命令 GRANT SELECT, INSERT, DELETE, UPDATE ON MAAMDATA.MMS_APP_ORDER_INFO TO DONGHAIHUA; GRANT SELECT ON HMRPTDATA.WIDETABLE_APPID_DEVICEID TO MAAMDATA,DONGHAIHUA;

Oracle_如何创建表空间

SYS用户在CMD下以DBA身份登陆： 在CMD中打sqlplus /nolog 然后再 conn / as sysdba //创建临时表空间 create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; //创建数据表空间 create tablespace test_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; //创建用户并指定表空间 create user username identified by password

default tablespace user_data temporary tablespace user_temp; //给用户授予权限 grant connect,resource to username; //以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，这就不用在每创建一个对象给其指定表空间了 撤权： revoke 权限... from 用户名; 删除用户命令 drop user user_name cascade; 建立表空间 CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 删除表空间 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 一、建立表空间 CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 二、建立UNDO表空间 CREATE UNDO TABLESPACE UNDOTBS02

Oracle建表实例

/*==============================================================*/ /* Database name: Database_1 */ /* DBMS name: ORACLE Version 11g */ /* Created on: 2016/3/26 20:29:00 */ /*==============================================================*/ /*==============================================================*/ /* Database: Database_1 */ /*==============================================================*/ /*==============================================================*/ /* Table: Major */ /*==============================================================*/ create table Major ( MajorID int not null, Name varchar2(20) not null, Remark varchar2(30), constraint PK_MAJOR primary key (MajorID) ); /*==============================================================*/ /* Table: Student */ /*==============================================================*/ create table Student ( StudentID nvarchar2(20) not null, MajorID int, Name varchar2(20) not null, Sex bit not null, Age int, Remark varchar2(30), constraint PK_STUDENT primary key (StudentID) ); alter table Student add constraint FK_STUDENT_REFERENCE_MAJOR foreign key (MajorID) references Major (MajorID);

Oracle建表空间、用户操作详解-入门基础

Oracle建表空间、用户操作详解-入门基础 2009/03/10 14:36 建立表空间 CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 删除表空间 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 修改表空间大小 alter database datafile '/path/NADDate05.dbf' resize 100M 移动表至另一表空间 alter table move tablespace room1; 一、建立表空间 CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 二、建立UNDO表空间 CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M #注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间: ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立临时表空间 CREATE TEMPORARY TABLESPACE temp_data TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M 四、改变表空间状态 1.使表空间脱机 ALTER TABLESPACE game OFFLINE; 如果是意外删除了数据文件，则必须带有RECOVER选项 ALTER TABLESPACE game OFFLINE FOR RECOVER; 2.使表空间联机 ALTER TABLESPACE game ONLINE; 3.使数据文件脱机 ALTER DATABASE DATAFILE 3 OFFLINE; 4.使数据文件联机 ALTER DATABASE DATAFILE 3 ONLINE; 5.使表空间只读 ALTER TABLESPACE game READ ONLY; 6.使表空间可读写 ALTER TABLESPACE game READ WRITE; 五、删除表空间 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 六、扩展表空间

ORACLE11g创建新表和序列插入数据

ORACLE11g创建新表和序列，使用序列插入数据后，为什么序列不是从定义的初始值1开始？ 创建book表 create table book( bookID number(12)constraint pk_book primary key, bookName varchar(32)not null, price number(5,2), printTime date); 创建序列bookID create sequence bookID Start with1 Increment by1 Maxvalue999999999999 nocache nocycle; insert into book(bookid,bookname)values(bookID.Nextval,'三国演义'); insert into book(bookid,bookname)values(bookID.Nextval,'小学五年级数学'); insert into book(bookid,bookname)values(bookID.Nextval,'小学六年级数学'); insert into book(bookid,bookname)values(bookID.Nextval,'小学四年级数学'); select*from book; 插入数据后，序列却是从2开始，如下图所示。奇怪！奇怪！ 问题原因： ·当我们使用序列作为插入数据时，如果使用了“延迟段”技术，则跳过序列的第一个值·Oracle从11.2.0.1版本开始，提供了一个“延迟段创建”特性： 即: 当我们创建了新的表(table)和序列(sequence)， 在插入(insert)语句时，序列会跳过第一个值(1)。 所以结果是插入的序列值从 2(序列的第二个值) 开始，而不是 1开始。 想要解决这个问题有两种方法： 更改数据库的“延迟段创建”特性为false（需要有相应的权限）

Oracle 创建表

Oracle 创建表 创建表时需要使用CREATE TABLE语句，为了在用户自己的模式中创建一个新表，则用户必须具有CREATE TABLE系统权限。如果要在其他用户模式中创建表，则必须具有CREATE ANY TABLE的系统权限。此外，用户还必须在指定的表空间中具有一定的配额存储空间。 应该说，使用CREATE TABLE语句创建表并不困难，困难在于如何合理地确定创建哪些表，这些表应该包含哪些列，以及各列又应该使用什么样的数据类型等。在实际应用中，应该在用户需求调研和分析的基础上，借助于ER图等有效的工具和手段，确认应该创建哪些表和准备如何创建这些表。 例如，下面创建一个存储公司员工信息的EMPLOYEE表，该表包括了员工代号、员工姓名、性别、雇用时间、职位和电子邮件等信息。 SQL> create table employees( 2 empno number(10) not null, 3 ename varchar2(20), 4 sex char(2), 5 salary number(8,2), 6 hiredate date default sysdate, 7 job varchar2(10), 8 email varchar2(50), 9 deptno number(3) not null); 表已创建。 建立表后，可以通过DESCRIBE命令查看表的描述： SQL> desc employees 名称是否为空? 类型 ----------------------------------------- -------- ------------- EMPNO NOT NULL NUMBER(10) ENAME V ARCHAR2(20) SEX CHAR(2) SALARY NUMBER(8,2) HIREDA TE DATE JOB V ARCHAR2(10) EMAIL V ARCHAR2(50) DEPTNO NOT NULL NUMBER(3) 如果要在其他模式中创建表，则必须在表名前加上模式名。例如，下面的语句将在HR 模式中创建EMPLOYEES表： SQL> create table hr.employees(...); 注意在创建表时，表的各列之间需要使用逗号隔开。

oracle创建临时表

Oracle数据库临时表管理心得 我们在创建数据表的时候，若没有特殊的指明，那么我们创建的表是一个永久的关系型表格，也就是说，这个表格中对应的数据，除非是我们显示的删除的话，表中的数据是永远都存在的。相对应的，在Oracle数据库中还有一种类型的表，叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远的存在。当一个会话结束或者事务结束的时候，这个临时表中的数据，不用用户自己删除，数据库自己会自动清 除。 1、事务临时表的管理。 (1) 事务临时表的创建。 Oracle数据库根据临时表的性质不同，可以分为事务临时表与会话临时表。事务临时表是指数据只有在当前事务内有效。一般情况下，如果在创建数据表的时候，没有特殊指明这表是会话临时表的话，则该表默认为事务临时表。 我们可以以下面的语句创建事务临时表。 Create global temporary table Temp_user (ID NUMBER(12) Primary key,name varchar2(10)); 笔者建议： 这个创建临时表的语句中，虽然没有显性的指明该表是事务临时表，但是，默认的情况下，若没有指明是什么临时表的话，系统默认是事务临时表。我们要创建事务临时表时，可以不指定关键字。但是，这查看起来比较麻烦。我建议，无论在建立什么临时表，都要利用具体的关键字来显形的指明，这大家看起来都方便。一般可以利用ON COMMIT DELETE ROWS关键字来说明该表就是事务性的临时表，而不是会话性质 的临时表。 (2) 事务临时表数据的变化分析。 事务临时表的话，当事务结束的时候，就会清空这个事务临时表。所以，当我们在数据库临时表中插入数据后，只要事务没有提交的话，该表中的数据就会存在。但是，当事务提交以后，该表中的数据就会被删除。而且，这个变化不会在重做日志中 显示。 具体事务临时表与会话临时表有什么区别，我们在介绍完会话临时表后会详细介 绍。

实验1oracle创建数据库及创建表

实验一：创建数据库、及表的管理 一、实验目的 1. 掌握使用DBCA创建数据库 2. 掌握手工创建Oracle数据库的方法(学习oracle的体系后再讲) 3.表的管理—见实验2 二、实验内容及步骤 1. 使用DBCA创建数据库 (1) 打开DBCA组件,创建数据库sale. (2) 安装完后进入D:\oracle\product\10.2.0\db_1\install目录（根据自己机子的路径），打开readme文件，查看打开OEM的URL。 (3) 登陆OEM，管理sale数据库。 3.表的管理 2. 手工创建Oracle数据库(暂时不做,等讲完oracle 的体系结构后在做) 数据库名：mydb 安装路径：d:\oracle\product\10.2.0\ (1)、手工创建相关目录 D:\oracle\product\10.2.0\admin\mydb D:\oracle\product\10.2.0\admin\mydb\adump D:\oracle\product\10.2.0\admin\mydb\bdump D:\oracle\product\10.2.0\admin\mydb\udump D:\oracle\product\10.2.0\admin\mydb\cdump D:\oracle\product\10.2.0\admin\mydb\ddump D:\oracle\product\10.2.0\admin\mydb\pfile D:\oracle\product\10.2.0\oradata\mydb (2)、手工创建初始化参数文件D:\oracle\product\10.2.0\admin\mydb\pfile\ init.ora，内容可以copy别的实例init.ora文件后修改。 (3)、在命令提示符下,使用orapwd.exe命令，创建口令文件pwdmydb.ora,命令格式如下： orapwd file=F:\oracle\product\10.2.0\db_2\database\pwdmydb.ora password=123 entries=5 (4)、通过oradim.exe命令，在服务里生成一个新的实例管理服务，启动方式为手工 set ORACLE_SID=mydb oradim -new -sid MYDB -pfile D:\oracle\product\10.2.0\db_2\database\initmydb.ora