数据库建表

建表

CREATE  TABLE  STAB

(SNO CHAR(7) PRIMARY  KEY,
SNAME  CHAR(10) NOT NULL,
SSX CHAR(20),
SWT SMALLINT);
CREATE TABLE  Departments
(DNO   CHAR(4)  PRIMARY KEY,
DNAME  CHAR(10),
DHEADNO  CHAR(7),
FOREIGN KEY (DHEADNO) REFERENCES  Teachers(Tno));
CREATE TABLE Students
(SNO CHAR(9) PRIMARY KEY,
SNAME  CHAR(10) NOT NULL,
SEX     CHAR(2)CHECK(SEX=’’OR SEX=’’),
BIRTHDAY  DATE,
ENROLLYEAR  CHAR(4),
SPECIALITY  CHAR(20),
DNO         CHAR(3),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE COURSES 
(CNO  CHAR(7) PRIMARY KEY,
CNAME  CHAR(20) NOT NULL,
PERIOD  SMALLINT,
CREDIT  SMALLINT);
CREATE TABLE TEACHES
  (TNO   CHAR(7) PRIMARY KEY,
   CNO   CHAR(5),
   TCSCORE   SMALLINT ,
   PRIMARY KEY(TNO,CNO),
   FOREIGN KEY (TNO)REFERENCES TEACHERS(TNO),
   FOREIGN KEY(CNO)REFERENCES COURSES(CNO));
 更新：
ALTER TABLE COURSES ADD PNO  CHAR(5);
ALTER TABLE STUDENTS  ALTER SEX SET DEFAULT’女’；
ALTER TABLE STUDENTS  ALTER SEX DROP DEFAULT;
ALTER TABLE COURSES  DROP PNO;
删除SC 表
DROP TABLE SC RESTRICT;
DROP TABLE SC CASCADE;
建立索引
CREATE INDEX Student_Dept ON Students(Dno);
DROP INDEX Student_Dept;
SELECT CNO,CNAME,PERIOD,CREDIT
FROM COURSES;
SELECT CNO,CREDIT
FROM COURSES;
SELECT 2007-year(Birthday)AS Age
FROM Students;
SELECT DISTINCT 2007-year(Birthday)Age
FROM Students;
SELECT Tname,Sex
FROM TEACHERS
WHERE TITLE=’讲师’;
SELECT DISTINCT SNO
FROM SC
WHERE Grade<60;
SELECT Sname,Speciality
FROM Students
WHERE  year(Birthday)BETWEEN 1987AND 1990;
SELECT Sno,Sname
FROM Students
WHERE Speciality IN(‘软件学院’,’计算机’);