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;