Oracle笔记

DDL

Create语句：

CREATE TABLE myTable (

id int,

name varchar2(32)

);

CREATE TABLE student( 

sid integer, 

name varchar(20),  

major char(4),

constraint student_pk  primary key (sid),

constraint student_fk   foreign key (major) references Major (id),

constraint student_uk  unique (name)   

);

Alter 语句：

ALTER TABLE myTable ADD newAtrribute int;

ALTER TABLE myTable DROP COLUMN myColumn;

ALTER TABLE myTable MODIFY (myAttribute DEFAULT 0);

ALTER TABLE myTable ADD CONSTAINT constraint_name FOREIGN KEY (id) REFERENCES my_another_table

Drop 语句：

DROP TABLE myTable;

DROP TABLE myTable CASCADE CONSTRAINT;

DROP TABLE myTable purge;

DML

Select语句：

SELECT *
from my_table
where ROWNUM < 10
order by attribute1 [ASC|DESC]

SELECT email_addr, COUNT(msgID)
from Recipients
where msgID>1
having COUNT(msgID)>1
group by email_addr;

select to_char(date_read,'yyyy-mm') from my_table;

select * from my_table1 join my_table2 on (my_table1.attribute1=my_table1.attribute2);

select * from my_table1 natural join my_table2;

select *  from my_table1 join my_table2 using (attribute1, attribute2);

Insert语句：

INSERT INTO myTable values (1,'msgtxt');

INSERT INTO myTable(attribute1, attribute2) values(1,'msgtxt');

Update语句：

UPDATE myTable set attribute1=10 where attribute2=20;

Delete语句：

DELETE FROM myTable;

DELETE FROM myTable where myAttribute=3;

Trigger

CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
   ON table_name
   [ FOR EACH ROW ]


DECLARE
   -- variable declarations


BEGIN
   -- trigger code


EXCEPTION
   WHEN ...
   -- exception handling


END;