数据库常见操作实例

创建表格：

CREATE TABLE Students
(
id           VARCHAR2(5),
name         VARCHAR2(20) NOT NULL,
sex          VARCHAR2(1) DEFAULT 'M',
birth        DATE, 
major_name   VARCHAR2(15), 
CONSTRAINT PK_Students PRIMARY  KEY(id),
CONSTRAINT CHK_Students_Sex
CHECK(sex='M' or sex='F') );


CREATE TABLE Grade
(
id         VARCHAR2(5),
course_id  VARCHAR2(5),
score      NUMBER(3,0),
CONSTRAINT PK_Grade PRIMARY KEY(id,course_id),
CONSTRAINT FK_Grade_TO_Students FOREIGN KEY(id)
REFERENCES Students(id),
CONSTRAINT FK_Grade_TO_Course FOREIGN KEY(course_id)
REFERENCES Course(course_id)
);

插入数据：

INSERT INTO Students VALUES('06001','zhangsan','M',TO_DATE('1995-03-12','YYYY-MM-DD'),'computer');


INSERT INTO Students(id,name) VALUES('06002','lisi'); 

删除记录：

DELETE FROM Grade WHERE id='95001';


DELETE FROM Grade WHERE score is NULL;

更新数据(修改记录)：

Update Students Set major_name='telecom' Where id='05001';


Update Teacher Set wage=wage+10001;