oracle - to build a small table title

2010-09-30  来源:本站原创  分类:Database  人气:129 

create table myemp as select * from emp; //  This statement is executed, will complete table structure and data copied out  ,  Create table emp as temporary table goes  

  Add Data  :
insert  Statement, and insert a null value is not inserted  ,  Effect is the same, if you do not insert  ,  Then the default value is populated with an empty
  Insert date in sql  ,  If you are using a good table in a fixed format, directly into a fixed format string
  If you use a good format is not fixed, then use  TO_DATE("2001-01-03",'yyyy-mm-dd');

  Modify data  :
update emp set comm = 100 where empno = '11';

  Note  :  Additions and deletions to the data, if we do not  commit ,  Then these data will be locked, others can not move his  ,
  Only if we commit or  rollback  After the others to move  

emp{empno,ename,job,mgr(  The number of employees in leadership  ),hiredate,sal,comm,deptno}

delete  Clear data can be used  rollback  Rollback
truncate  Clear data, you can not roll back  ,  Immediately release the resources
truncate table   Table name / / This is not to modify the table structure  ,  But it has also used the keyword table  ,  But this imperative Oracle's own command  

Oracle  Commonly used data types  :NUMBER,VARCHAR,DATE
VARCHAR,VARCHAR2 :   That string has length  ,  As 255
NUMBER(n)   Equivalent to  INT, NUMBER(m,n)  Equivalent to  FLOAT
CLOB : char large object :   Large text objects, the maximum storage  4G  Text
BLOB: byte large object :   Large binary objects, the maximum storage  4G,  Put non-text, such as film  ,mp3,  Picture  

  Table creation and deletion  

create table   Table Name  (
     Field name Field type   {default   Defaults  }

create table   Table Name   AS(  Subquery  )

  If the subquery is written now  : select * from emp ;  Copy Table structure and table content
  If the subquery is written now  :select * from emp where 1==2,  Added a condition can never be established  ,
  Then copy the table structure that only at this time, do not copy the contents of the table  

  Create a person table  :
create table person(
   pid VARCHAR2(10),
   name VARCHAR2(200),
   age NUMBER(3),
   birthday DATE,
   sex VARCHAR2(2) DEFAULT '  Male  '

); //  Java table create statements and the statement is contrary to  

  Remove Table  :
drop table person;

  Modify table structure grammar  :
alter table   Table Name   add(
     pid VARCHAR2(10),
     name VARCHAR2(200) default '  Male  '
alter table   Table Name   modify(
   pid VARCHAR2(5)  DEFAULT '8888'
) // If you modify the table structure, and if we assume that the corresponding field in the database which has a length of more than modification of data fields  ,  It certainly can not be achieved
  Note  :  Additions and deletions to the structure of the table  :
create table XX() ,
drop table XX
alter table XX add ()
alter table XX modify()

ALTER TABLE office_organization MODIFY(desc VARCHAR2(20) CONSTRAINT nn_desc NOT NULL) ;

  Rename the table
  Can be used in Oracle  rename
rename   The old table name to the new table name  

DB2  Database Directive does not alter  ,DB2  Is the most large-scale data, the world's massive database of which ten  7  One is  DB2

  There is a state table, only the name of a country field  ,  As follows  :"  China, U.S.  ,  Brazil, the Netherlands  ",
  Now required to achieve battle function by querying
  China  --->  United States
  China  --->  Brazil
  China  --->  Netherlands
  United States  --->  China
  United States  --->  Brazil
  United States  --->  Netherlands  

  Ask  :  How to implement this program  ?
create table conutry(
   name vatchar2(4)

select ||'---->'||
from country c1,country c2
where <>


  Topic  :
  Games are a student of the game information database, save the following table  :
  Athlete  sport(  Athlete Number sporterid, player name  name,  Athlete sex sex, number belongs to the Department   department);
  Project  item (  Item Number itemid, item name  itemname,  Project venue  location)
  Score  grade(  Athlete Number sportid, item number  itemid,  Integral  mark)

1.  Construction of the table
(1)  Define the primary key of each table foreign key constraint
(2)  Department of athlete's name and do not belong can not be null
(3)  Points is either empty or is  6,4,2,0,  Represent one. Two  ,  Three points, and other ranking  

create table sporter(
   sporterid varchar2(128) ,
   name varchar2(10) NOT NULL,
   sex varchar2(2),
   department varchar2(128) NOT NULL,
   constraint sport_sporterid_pk PRIMARY KEY(sporterid),
   constraint sport_sex_ck CHECK(sex IN ('  Male ',' female  '))
  Project  item (  Item Number itemid, item name  itemname,  Project venue  location)
create table item(
   itemid varchar2(128),
   itemname varchar2(50),
   location varchar2(128),
   CONSTRANINT item_itemid_pk PRIMARY KEY(itemid)
  Score  grade(  Athlete Number sportid, item number  itemid,  Integral  mark)
create table grade(
   sportid varchar2(128),
   itemid varchar2(128),
   mark NUMBER(1),
   CONSTRAINT sporter_grade_sportid_fk FORIGEN KEY(sportid) REFERENCES sport(sportid) ON DELETE CASCADE,
   CONSTRAINT item_grade_itemid_fk FOREIGEN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
   CONSTRAINT grade_mark_ck CHECK(mark IN(6,4,2,0))