【数据库】mysql基础（...

1、定义模式

create schema 模式名 authorization 用户名

2、创建表（按书上）

student course sc

CREATE TABLE Student
 
 (sno CHAR(9)PRIMARY KEY,
 
 Sname CHAR(20)UNIQUE,
 
 Ssex CHAR(2),
 
 Sage SMALLINT,
 
 Sdept CHAR(20));

 CREATE	 TABLE Course
 (cno CHAR(4)PRIMARY KEY,
 Cname CHAR(40) NOT NULL,
 Cpno CHAR(4),
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno)REFERENCES Course(Cno) /*表级完整约束条件,cpno是外码，被参照course cno*/
 );
 

CREATE TABLE SC
(Sno CHAR(9),
 Cno CHAR(4),
 Grade SMALLINT,
PRIMARY KEY (Sno,Cno), /*主码两个属性，表级完整性*/
FOREIGN KEY (Sno)REFERENCES Student(Sno),
FOREIGN KEY (Cno)REFERENCES Course(Cno)
);

3、导入数据

student

insert into Student(sno,Sname,Ssex,Sage,Sdept )VALUES('201215121','李勇','男','20','CS');
insert into Student(sno,Sname,Ssex,Sage,Sdept )VALUES('201215122','刘晨','女','22','CS');
insert into Student(sno,Sname,Ssex,Sage,Sdept )VALUES('201215123','王敏','女','21','MA');
insert into Student(sno,Sname,Ssex,Sage,Sdept )VALUES('201215125','张旭','男','24','IS');

course

INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('2','数学',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('4','操作系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('6','数据处理',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)    VALUES ('7','Pascal语言',NULL,4);

sc

INSERT  INTO SC(Sno,cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,cno,Grade) VALUES ('201215122 ','3',80);