查看表结构:DESC STUDENT ;
查看所有表:SELECT * FROM TAB;
ORACLE查看所有用户:SELECT * FROM ALL_USERS;
例1-1: (建立数据库表) 建立教学数据库的四个数据库表,其中Student表中不包含SSEX(C,2) 字段,Sname 字段为Sname(C,8)且可为空。
学生情况表:
CREATE TABLE STUDENT
(SNO CHAR(5) CONSTRAINT PK_S PRIMARY KEY,
SNAME VARCHAR2(8),
SDEPT CHAR(2) NOT NULL,
SCLASS CHAR(2) NOT NULL,
SAGE NUMBER(2) CHECK (SAGE BETWEEN 5 AND 50)
);
课程名称表:
CREATE TABLE COURSE
(CNO CHAR(3),
CNAME VARCHAR2(16)NOT NULL UNIQUE,
CTIME NUMBER(3),
CONSTRAINT PK_C PRIMARY KEY(CNO)) ;
教师授课表:
CREATE TABLE TEACH
(TNAME VARCHAR2(8) ,
TSEX CHAR(2) CHECK(TSEX IN('男','女')),
CNO CHAR(3) NOT NULL,
TDATE DATE NOT NULL,
TDEPT CHAR(2) NOT NULL,
CONSTRAINT PK_T PRIMARY KEY (TNAME,CNO,TDEPT),
CONSTRAINT FK_T_C FOREIGN KEY (CNO) REFERENCES COURSE(CNO)
);
成绩表:
CREATE TABLE SCORE
(SNO CHAR(5),
CNO CHAR(3),
SCORE NUMBER(5,2),
CONSTRAINT PK_SC PRIMARY KEY (SNO,CNO),
CONSTRAINT FK_SC_S FOREIGN KEY (SNO)
REFERENCES STUDENT(SNO) ON DELETE CASCADE,
CONSTRAINT FK_SC_C FOREIGN KEY (CNO) REFERENCES COURSE(CNO));
例1-2: (修改数据库表) 在Student表中增加SEX(C,2) 字段。
ALTER TABLE STUDENT ADD SEX CHAR(2);
例1-3: (修改列名) 将Student表中列名SEX修改为SSEX。
ALTER TABLE STUDENT RENAME COLUMN SEX TO SSEX;
例1-4: (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。
ALTER TABLE STUDENT MODIFY (SNAME VARCHAR2(10));
ALTER TABLE STUDENT MODIFY SNAME NOT NULL;
例1-5: (建立索引) 为Score表按课程号升序、分数降序建立索引,索引名为SC_GRADE。
CREATE INDEX SC_GRADE ON SCORE(CNO ASC,SCORE DESC);
例1-6: (删除索引) 删除索引SC_GRADE。
DROP INDEX SC_GRADE;
例1-7: (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA),其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。
CREATE TABLE S1
(SNO CHAR(5) CONSTRAINT PK_S1 PRIMARY KEY,
SNAME VARCHAR2(8),
SD CHAR(2) NOT NULL,
SA NUMBER(2) CHECK (SA BETWEEN 5 AND 50));
例1-8: (修改数据库表) 删除成绩表Score的参照完整性约束关系。
ALTER TABLE SCORE DROP CONSTRAINT FK_SC_C;
ALTER TABLE SCORE DROP CONSTRAINT FK_SC_S;
例1-9: (修改数据库表) 添加成绩表Score的参照完整性约束关系。
ALTER TABLE SCORE ADD CONSTRAINT FK_SC_C FOREIGN KEY (CNO)
REFERENCES COURSE (CNO);
ALTER TABLE SCORE ADD CONSTRAINT FK_SC_S FOREIGN KEY (SNO)
REFERENCES STUDENT(SNO) ON DELETE CASCADE,
例1-10: (修改数据库表名) 将数据库表S1改名为Student_Temp。
RENAME S1 TO STUDENT_TEMP;
2. SQL数据操纵语句:
例2-1: (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。
学生情况表:
INSERT INTO STUDENT VALUES('96001','马小燕','CS','01',21,'女');
INSERT INTO STUDENT VALUES('96002','黎明','CS','01',18,'男');
INSERT INTO STUDENT VALUES('96003','刘东明','MA','01',18,'男');
INSERT INTO STUDENT VALUES('96004','赵志勇','IS','02',20,'男');
INSERT INTO STUDENT VALUES('97001','马蓉','MA','02',19,'女');
INSERT INTO STUDENT VALUES('97002','李成功','CS','01',20,'男');
INSERT INTO STUDENT VALUES('97003','黎明','IS','03',19,'女');
INSERT INTO STUDENT VALUES('97004','李丽','CS','02',19,'女');
INSERT INTO STUDENT VALUES('96005','司马志明','CS','02',18,'男');
课程名称表:
INSERT INTO COURSE VALUES('001','数序分析','144');
INSERT INTO COURSE VALUES('002','普通物理',144);
INSERT INTO Course VALUES('003','微机原理',72);
INSERT INTO COURSE VALUES('004','数据结构',72);
INSERT INTO COURSE VALUES('005','操作系统',64);
INSERT INTO COURSE VALUES('006','数据库原理',64);
INSERT INTO COURSE VALUES('007','DB_Design',48);
INSERT INTO COURSE VALUES('008','程序设计',56);
教师授课表:
INSERT INTO TEACH VALUES('王成刚','男','004',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'CS');
INSERT INTO TEACH VALUES('李正科','男','003',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'CS');
INSERT INTO TEACH VALUES('严敏','女','001',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'MA');
INSERT INTO TEACH VALUES('赵高','男','004',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'IS');
INSERT INTO TEACH VALUES('李正科','男','003',TO_DATE( '2000-02-23', 'YYYY-MM-DD'),'MA');
INSERT INTO TEACH VALUES('刘玉兰','女','006',TO_DATE( '2000-02-23', 'YYYY-MM-DD'),'CS');
INSERT INTO TEACH VALUES('王成刚','男','004',TO_DATE( '2000-02-23', 'YYYY-MM-DD'),'IS');
INSERT INTO TEACH VALUES('马悦','女','008',TO_DATE( '2000-09-06', 'YYYY-MM-DD'),'CS');
INSERT INTO TEACH VALUES('王成刚','男','007',TO_DATE( '1999-09-05', 'Y