--创建并使用数据库------------*/
create database 50q;
use 50q;
/*-------------------建表-------------------------*/
 CREATE TABLE STUDENT 
(   SNO       VARCHAR(3) NOT NULL,   
 SNAME     VARCHAR(4) NOT NULL, 
 SSEX      VARCHAR(2) NOT NULL,  
 SBIRTHDAY DATETIME, 
 CLASS     VARCHAR(5) 
 );
 CREATE TABLE COURSE 
(  
 CNO   VARCHAR(5)  NOT NULL,  
 CNAME VARCHAR(10) NOT NULL, 
 TNO   VARCHAR(3) NOT NULL 
 );
 CREATE TABLE SCORE   
(  
 SNO    VARCHAR(3)     NOT NULL,  
 CNO    VARCHAR(5)     NOT NULL,  
 DEGREE NUMERIC(10, 1) NOT NULL  
 ) ; 
  CREATE TABLE TEACHER   
(  
 TNO       VARCHAR(3)  NOT NULL,   
 TNAME     VARCHAR(4)  NOT NULL,   
 TSEX      VARCHAR(2)  NOT NULL,   
 TBIRTHDAY DATETIME    NOT NULL,   
 PROF      VARCHAR(6),   
 DEPART    VARCHAR(10) NOT NULL  
 );
/*---------------添加主键--------------*/
  ALTER TABLE STUDENT ADD PRIMARY KEY (SNO); 
 ALTER TABLE SCORE   ADD PRIMARY KEY (SNO,CNO);  
 ALTER TABLE COURSE  ADD PRIMARY KEY (CNO);  
 ALTER TABLE TEACHER ADD PRIMARY KEY (TNO);
#--------主键在两张table中的数据类型须一致-------*/
  ALTER TABLE SCORE   ADD CONSTRAINT FK_SCORE_STUDENT  FOREIGN KEY (SNO) REFERENCES STUDENT(SNO);  
 ALTER TABLE SCORE   ADD CONSTRAINT FK_SCORE_COURSE   FOREIGN KEY (CNO) REFERENCES COURSE(CNO);  
 ALTER TABLE COURSE  ADD CONSTRAINT FK_COURSE_TEACHER FOREIGN KEY (TNO) REFERENCES TEACHER(TNO);
/*---------------输入记录-------------------*/
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)   
 VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033),  
        (105 ,'匡明' ,'男' ,'1975-10-02',95031),   
        (107 ,'王丽' ,'女' ,'1976-01-23',95033),  
        (101 ,'李军' ,'男' ,'1976-02-20',95033),    
        (109 ,'王芳' ,'女' ,'1975-02-10',95031),  
        (103 ,'陆君' ,'男' ,'1974-06-03',95031);
 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)   
 VALUES (804,'李诚','男','1958-12-02','副教授','计算机系'),  
        (856,'张旭','男','1969-03-12','讲师','电子工程系'),  
        (825,'王萍','女','1972-05-05','助教','计算机系'),   
        (831,'刘冰','女','1977-08-14','助教','电子工程系');
 INSERT INTO COURSE(CNO,CNAME,TNO)
       VALUES ('3-105' ,'计算机导论',825),  
              ('3-245' ,'操作系统' ,804), 
              ('6-166' ,'数据电路' ,856),  
              ('9-888' ,'高等数学' ,831); 
 INSERT INTO SCORE(SNO,CNO,DEGREE)
        VALUES (103,'3-245',86),(105,'3-245',75), 
               (109,'3-245',68),(103,'3-105',92),  
               (105,'3-105',58),(109,'3-105',46), 
               (101,'3-105',64),(107,'3-105',91),  
               (108,'3-105',78),(101,'6-166',85),  
               (107,'6-166',79),(108,'6-166',81);