MySQL实验04

SQL创建

CREATE SCHEMA list;
USE list;

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
('202300001','蔡徐坤','男',20,'CS'),
('202300002','谷爱凌','女',11,'CS'),
('202300003','马嘉祺','男',23,'MA'),
('202300004','何同学','女',19,'IS'),
('202300005','小黑子','男',23,'MA');

INSERT INTO Course VALUES
('1','高等数学',NULL,4),
('2','C语言',NULL,2),
('3','数字电路',NULL,4),
('4','模拟电路',NULL,3),
('5','STM32',NULL,1),
('6','汇编语言',NULL,5),
('7','数据结构',NULL,3);

INSERT INTO sc VALUES('202300001','1',92);
INSERT INTO sc VALUES('202300001','2',85);
INSERT INTO sc VALUES('202300001','3',88);

INSERT INTO sc VALUES('202300002','1',92);
INSERT INTO sc VALUES('202300002','2',90);
INSERT INTO sc VALUES('202300002','3',80);

INSERT INTO sc VALUES('202300003','1',80);
INSERT INTO sc VALUES('202300003','2',89);
INSERT INTO sc VALUES('202300003','3',93);

INSERT INTO sc VALUES('202300004','1',85);
INSERT INTO sc VALUES('202300004','2',60);
INSERT INTO sc VALUES('202300004','3',59);

INSERT INTO sc VALUES('202300005','1',53);
INSERT INTO sc VALUES('202300005','2',77);
INSERT INTO sc VALUES('202300005','3',99);

ALTER TABLE Student MODIFY COLUMN Sage INT;
ALTER TABLE Course ADD UNIQUE(Cname);

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='7'WHERE Cno='5';

ALTER TABLE Student ADD S_enterance DATE;

下三表在MYSQL中显示不全，故用SQLyog展示

CREATE UNIQUE INDEX stusno ON student(sno);
SHOW INDEX FROM student;

CREATE UNIQUE INDEX coucno ON course (cno);
SHOW INDEX FROM course;

CREATE UNIQUE INDEX scno ON sc(sno ASC,cno DESC);
SHOW INDEX FROM sc;

SQL查询

SELECT Sno,Sname FROM Student;

SELECT Sname,Sno,Sdept FROM Student;

SELECT *FROM student;

SELECT  Sno,Sname,Ssex,Sage,Sdept FROM Student;

SELECT Sname,2014-Sage FROM student;

SELECT Sname,'Year of Birth:',2023-Sage,LOWER(Sdept)FROM student;

SELECT DISTINCT Sno FROM sc;

SELECT Sname FROM Student WHERE Sdept='CS';

SELECT Sname,Sage FROM Student WHERE Sage<20;

SELECT DISTINCT Sno FROM SC WHERE Grade<60;

SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;

SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;

SELECT Sname,Sage FROM student WHERE Sdept IN('CS','MA','IS');

SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN('CS','MA','IS');

SELECT *FROM Student WHERE Sno LIKE 202300001;

SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE'谷%';

SELECT Sno,Grade FROM SC WHERE Cno='3'ORDER BY Grade DESC;

SELECT COUNT(*)FROM Student;

SELECT AVG (Grade) FROM SC WHERE Cno='1';

SELECT MAX(Grade)FROM SC WHERE Cno='1';

SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;

select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where  student.sno;

select student.sno,sname
from student,sc
where student.sno=sc.sno and sc.cno='2'and sc.grade>90;

select first.cname,second.cname from course first,course second where first.cpno=second.cno;

select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno;

--左外连接
select student.sno,sname,ssex,sage,sdept,cno,grade
from Student LEFT OUTER JOIN SC ON (Student.Sno=Sc.Sno);

--右外连接
select student.sno,sname,ssex,sage,sdept,cno,grade
from Student RIGHT OUTER JOIN SC ON (Student.Sno=Sc.Sno);

--right
select course.cno,cname,cpno,ccredit,sc.sno,sc.cno,Grade
from course RIGHT OUTER JOIN SC ON (course.cno=SC.cno);

--left
select course.cno,cname,cpno,ccredit,sc.sno,sc.cno,Grade 
from course LEFT OUTER JOIN SC ON (course.cno=SC.cno);

select student.sno,sname,cname,Grade
 from student,sc,Course
 Where student.sno=sc.sno and sc.cno=course.cno;

Select Sno,Sname 
FROM Student 
WHERE Sno IN 
(SELECT Sno 
FROM SC 
WHERE Cno IN 
(SELECT Cno 
FROM Course 
WHERE Cname='高等数学'));

SELECT Sname,Sage
FROM Student
WHERE Sage>ALL
(SELECT Sage
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';

SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *FROM SC
WHERE Sno-Student.Sno AND Cno='1');

SELECT *FROM Student
WHERE Sdept='CS'
UNION
SELECT*
FROM Student;

SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

SELECT *
FROM Student
WHERE Sdept='CS'INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

SELECT *
FROM Student
WHERE Sdept='CS' AND Sage<=19;

SELECT Student.sname,SC.Sno,SC.Cno   
FROM Student   
INNER JOIN SC ON Student.Sno = SC.Sno   
INNER JOIN  ( SELECT Sno, AVG(Grade) as avg_grade    
FROM SC GROUP BY Sno )
AS Avg_SC ON  SC.Sno = Avg_SC.Sno   
WHERE  SC.Grade >= Avg_SC.avg_grade;