mysql实验2

-- CREATE DATABASE XSGL;
USE XSGL;
CREATE TABLE student (
sno CHAR(9) PRIMARY KEY,
ssex CHAR(2),
sname CHAR(10)UNIQUE NOT NULL,
sage INT(4),
sdept CHAR(5)
);
 
 CREATE TABLE course(
cno CHAR(4)PRIMARY KEY,
cname CHAR(40)Not NULL,
credit INT (4),
cpno CHAR(4)
 
);
 
 CREATE TABLE SC(
 sno CHAR(9),
 cno CHAR(4),
 grade FLOAT(5,1),
 FOREIGN KEY(sno)REFERENCES student(sno),
 FOREIGN KEY(cno)REFERENCES course (cno)
 );

 
 INSERT INTO student(sno,sname,ssex,sage,sdept)VALUES 
 (95001,'李勇','男',20,'CS'),
 (95002,'刘晨','女',19,'IS'),
 (95003,'王敏','女',18,'MA'),
 (95004,'张立','男',19,'IS'),
 (95005,'刘云','女',18,'CS');
 
 
 INSERT INTO course(cno,cname,credit,cpno)VALUES 
 (1001,'数据库',4,'1005'),
 (1002,'数学',6,null),
 (1003,'信息系统',3,'1001'),
 (1004,'操作系统',4,'1006'),
 (1005,'数据结构',4,'1007'),
 (1006,'数据处理',3,null),
 (1007,'PASCAL语言',4,'1006');
 
 
 INSERT INTO sc(sno,cno,grade)VALUES 
 (95001, 1001, 92.3),
 (95001, 1002, 85.0),
 (95001, 1003, 88.3),
 (95002, 1002, 90.5),
 (95002, 1003, 80.0),
 (95003, 1002, 85.6),
 (95004, 1001, 58.9),
 (95004, 1002, 85.7);
 
 
 
  ALTER TABLE student DROP Phonenumber;
ALTER TABLE course ADD UNIQUE(cname);

 
 UPDATE student SET sage=sage+1;
 
 UPDATE course SET credit=4 WHERE cno=1004;
 
 UPDATE course SET cpno=null WHERE cno=1007; 
 
  UPDATE sc SET grade=grade+3 WHERE sno=95001 AND  cno=1001;
	
	DELETE FROM student WHERE sno=95005;
	
	
	ALTER TABLE course DROP FOREIGN KEY cno;
	DELETE FROM course ;
		
		
	DELETE FROM sc WHERE grade<60;
	
	
 SELECT *from sc;
 
 

DROP TABLE sc CASCADE;
DROP TABLE course CASCADE;
DROP TABLE student CASCADE;

DROP DATABASE XSGL;