school

引用块内容

1. 列表内容

创建数据库school

CREATE DATABASE school;

创建Student表

CREATE TABLE Student(
Sid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Sname VARCHAR(10) NOT NULL,
Ssex CHAR(2) NOT NULL,
sage INT NOT NULL
) AUTO_INCREMENT=1001 ;

向Student表添加数据

INSERT INTO Student(Sname,Ssex,sage)
VALUES(‘武心仰’,’女’,19),
(‘郭凯’,’男’,20),
(‘师达’,’男’,20);
SELECT * FROM Student;

创建Course表

CREATE TABLE Course(
Cid INT(2) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
Cname VARCHAR(10) NOT NULL,
Ctime INT NOT NULL
);

向Course表插入数据

INSERT INTO Course(Cname,Ctime) VALUES(‘数学’,180),(‘英语’,200),(‘C语言’,60);
SELECT * FROM Course;

创建成绩表

CREATE TABLE Grade(
gSid INT UNSIGNED ,
gCid INT(2) ZEROFILL UNSIGNED,
grade DOUBLE(3,1) NOT NULL,
PRIMARY KEY(gSid,gCid),
FOREIGN KEY (gSid) REFERENCES Student(Sid)ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (gCid) REFERENCES Course(Cid)ON DELETE CASCADE ON UPDATE CASCADE
);

向Grade表添加数据

INSERT INTO Grade VALUES(1001,01,88.5),(1001,02,91.0),(1001,03,79.0),
(1002,01,80.0),(1002,02,82.0),(1002,03,77.0),
(1003,01,80.0),(1003,02,59.0),(1003,03,90.0);
SELECT * FROM Grade;

1,显示年龄在19~20之间的所有男同学的姓名

SELECT Sname FROM Student
WHERE sage BETWEEN 19 AND 20
AND Ssex=’男’

2,显示英语课程号和学时数

SELECT Cid,Ctime FROM Course
WHERE Cname=’英语’

3,列出所有学生的姓名、课程名和成绩

SELECT Sname,Cname,grade FROM Student,Course,Grade
WHERE Student.Sid=Grade.gSid AND Course.Cid=Grade.gCid

4,显示student表中学生的姓名、性别和年龄加1的结果

SELECT Sname,Ssex,sage+1 FROM Student

5,将student表中的记录按年龄从小到大顺序显示

SELECT * FROM Student ORDER BY Sage ASC

6,按成绩由低到高显示学生姓名、课程名和成绩

SELECT Sname,Cname,grade
FROM Student,Course,Grade
WHERE Student.Sid=Grade.gSid AND Course.Cid=Grade.gCid ORDER BY grade ASC

7,(有问题)

列出考分最高的学生姓名、性别、年龄、课程名和成绩

SELECT Sname,Ssex,Sage,Cname,grade
FROM Student,Course,Grade
WHERE
Student.Sid=Grade.gSid AND Course.Cid=Grade.gCid
ORDER BY Grade DESC LIMIT 1

8,列出任何成绩大于80分的学生姓名、性别、课程名和成绩

SELECT Sname,Ssex,Cname,grade
FROM Student,Course,Grade
WHERE
Student.Sid=Grade.gSid AND Course.Cid=Grade.gCid AND grade>80