MySQL实验05

create schema lienci;
use lienci;

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));

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));

INSERT INTO Student VALUES('411420156','蔡徐坤','男','22','Wl');

 INSERT INTO Student VALUES
('411420001','陈武','男','23','CS'),
('411420002','潘璋','女','25','CS'),
('411420003','蒋钦','男','19','MA'),
('411420004','周泰','女','18','IS');

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

 SELECT Sname FROM Student WHERE ssex='女';

INSERT into fstu values
('411420002','潘璋','女','25','CS'),
('411420004','周泰','女','18','IS');

INSERT into student values
('411420005','王志朔','男','18','Wl'),
('411420006','魏午珅','男','21','Wl'),
('411420007','龚聒杰','女','24','Wl');

INSERT INTO Course VALUES
('1','数据库',NULL,4),
('2','数学',NULL,2),
('3','信息系统',NULL,4),
('4','操作系统',NULL,3),
('5','数据结构',NULL,4),
('6','数据处理',NULL,2);
SELECT *from course;

update course set ccredit=ccredit+1;
select*from course;

update course set cname ='数据库原理及应用'where cname ='数据库';
SELECT *from course;

update student set sage =sage+1 where sno='411420001';
select *from student;

insert into sc values
('411420001','1',20),
('411420002','2',11),
('411420003','3',23),
('411420004','2',19),
('411420005','3',23);
select *from sc;

delete from sc where  cno='2';
select *from sc;

delete from course where  cname='操作系统';
select *from course;

UPDATE Course SET Cpno='5'WHERE Cno='1';
UPDATE Course SET Cpno='1'WHERE Cno='3';
UPDATE Course SET Cpno='6'WHERE Cno='4';
UPDATE Course SET Cpno='5'WHERE Cno='2';

ALTER TABLE student  
ADD address CHAR(40) DEFAULT '信阳';

UPDATE student  
SET address = '郑州'  
WHERE sname = '陈武';

UPDATE student  
SET address = '开封'  
WHERE sname = '周泰';

update student
set sage=21
where sno='411420001';

update course set cname ='数据库原理'where cname ='数据库原理及应用';
update course set ccredit=1 where cname ='数据库原理';
SELECT *from course;

insert into student values
('201215127','刘武','男',18,'CS','南阳'),
('201215128','欧阳磊','男',18,'CS');

insert into sc values('201215127','1',99);
select *from sc;

delete from student
where sno='411420156';

DROP TABLE student;

SET foreign_key_checks = 0;  
DROP TABLE student;