一、数据库操作
1. create database Student
2. 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)
);
ALTER TABLE SC
ADD TEST int;
drop table sc;
3. CREATE INDEX I ON `SC`;
二、数据查询
1. SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
SELECT Sno
FROM SC;
SELECT Sno
FROM SC
ORDER BY Sno;
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
2. SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;
SELECT FIRST.Cname,SECOND.Cname
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade