Mysql实操基础（数据库作业）

附上官网地址MySQL

1. 登录

mysql -u username -p password

其中，username为数据库的用户名，password为对应的密码。这条命令将会连接到本地默认的MySQL服务器并使用提供的用户名和密码进行身份验证。如果成功登录，则可以开始与MySQL交互了。

然后先创建数据库

CREATE DATABASE 库名;

使用数据库

use 库名;

2. 作业内容

Student(Sno, Sname, Ssex, Sbirthdate, Smajor)

Course(Cno,Cname,Ccredit,Cpno)

SC(Sno,Cno, Grade,Semester,Teachingclass)

创建Student(Sno, Sname, Ssex, Sbirthdate, Smajor):

CREATE TABLE Student ( Sno VARCHAR(10) PRIMARY KEY,  Sname VARCHAR(50), Ssex CHAR(1), SBirthdate DATE, Smajor VARCHAR(50));

创建Course(Cno,Cname,Ccredit,Cpno):

CREATE TABLE Course ( Cno VARCHAR(10) PRIMARY KEY,  Cname VARCHAR(100),  Ccredit INT,  Cpno VARCHAR(10) );

创建SC(Sno,Cno, Grade,Semester,Teachingclass):

CREATE TABLE SC ( Sno VARCHAR(10), Cno VARCHAR(10), Grade INT, Semester VARCHAR(20),Teachingclass VARCHAR(20), PRIMARY KEY (Sno, Cno),  FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );

1. 学生数据

INSERT INTO Student VALUES ('2021214509', 'lht', 'M', '2024-3-21', 'Computer Science');

INSERT INTO Student VALUES ('2021213333', 'Alice', 'F', '1998-05-15', 'Computer Science');

INSERT INTO Student VALUES ('2002151', 'KKK', 'M', '2024-3-21', 'Computer Science');

INSERT INTO Student VALUES ('2031312312', 'JJJ', 'M', '2014-3-19', 'Computer Science');

INSERT INTO Student VALUES ('2000000000', 'NNN', 'F', '2011-1-19', 'Computer Science');

INSERT INTO Student VALUES ('2012100000', 'Bob', 'M', '1999-08-21', 'Engineering');

1. 课程数据：

INSERT INTO Course VALUES ('C001', 'Introduction to Computer Science', 3, null);

INSERT INTO Course VALUES ('C002', 'Programming Fundamentals', 3, null);

INSERT INTO Course VALUES ('C003', 'Discrete Mathematics', 3, null);

INSERT INTO Course VALUES ('C004', 'Data Structures and Algorithms', 4, 'C001');

INSERT INTO Course VALUES ('C005', 'Computer Networks', 3, null);

INSERT INTO Course VALUES ('C006', 'Software Engineering', 4, 'C002');

INSERT INTO Course VALUES ('C007', 'Database Systems', 3, 'C002');

INSERT INTO Course VALUES ('C008', 'Operating Systems', 4, 'C004');

INSERT INTO Course VALUES ('C009', 'Artificial Intelligence', 3, 'C004');

INSERT INTO Course VALUES ('C010', 'Machine Learning', 4, 'C009');

1. 学生选课数据

INSERT INTO SC VALUES ('2021214509', 'C001', 85, '2020-1', '信安');
INSERT INTO SC VALUES ('2021213333', 'C002', 78, '2020-1', '信安');
INSERT INTO SC VALUES ('2002151', 'C003', 92, '2020-1', '信安');
INSERT INTO SC VALUES ('2031312312', 'C001', 88, '2019-2', '智科');
INSERT INTO SC VALUES ('2000000000', 'C002', 90, '2019-2', '信安');
INSERT INTO SC VALUES ('2012100000', 'C003', 86, '2020-1', '智科');
INSERT INTO SC VALUES ('2021214509', 'C004', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021213333', 'C005', 98, '2019-2', '信安');
INSERT INTO SC VALUES ('2021213333', 'C007', 99, '2019-2', '信安');
INSERT INTO SC VALUES ('2002151', 'C004', 92, '2019-2', '信安');
INSERT INTO SC VALUES ('2031312312', 'C004', 93, '2019-2', '智科');
INSERT INTO SC VALUES ('2000000000', 'C006', 99, '2019-2', '信安');
INSERT INTO SC VALUES ('2012100000', 'C006', 91, '2019-2', '智科');
INSERT INTO SC VALUES ('2000000000', 'C007', 98, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C002', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C003', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C005', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C006', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C007', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C008', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C009', 100, '2019-2', '信安');
INSERT INTO SC VALUES ('2021214509', 'C010', 100, '2019-2', '信安');

1. 查询（Insert）——单表

• 查询计算机科学与技术专业的学生信息

SELECT * FROM Student WHERE Smajor = 'Computer Science';

• 统计计算机科学与技术专业男生和女生的人数

SELECT Ssex, COUNT(*) AS Count FROM Student WHERE Smajor = 'Computer Science' GROUP BY Ssex;

• 统计2020年第1学期，每门课程的选修人数

SELECT Cno, COUNT(*) AS Enrollment FROM SC WHERE Semester = '2020-1' GROUP BY Cno;

• 查询2019年第2学期，平均成绩超过90分的学生，按平均成绩降序，取前2名

SELECT Sno, AVG(Grade) AS Average_Grade FROM SC WHERE Semester = '2019-2' GROUP BY Sno HAVING AVG(Grade) > 90 ORDER BY Average_Grade DESC LIMIT 2;

1. 查询（Insert）-多表

• 查询每位学生的选课信息（3表连接）

SELECT Student.Sno, Student.Sname, Course.Cno, Course.Cname, SC.Grade, SC.Semester, SC.Teachingclass FROM Student, SC, Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

• 查询2002151学生的选课信息，给出课程号、课程名、成绩

SELECT Course.Cno, Course.Cname, SC.Grade FROM SC,Course WHERE SC.Cno = Course.Cno AND SC.Sno = '2002151';

• 查询选修了“数据库”课程的学生学号、姓名和成绩（连接、嵌套）

SELECT Student.Sno, Student.Sname, SC.Grade FROM Student,SC WHERE Student.Sno = SC.Sno AND SC.Cno = ( SELECT Cno FROM Course WHERE Cname = 'Database Systems' );

• 查询所有课程的先修课，给出先修课的课程号、课程名（自连接）

SELECT c1.Cno AS Course_ID, c1.Cname AS Course_Name, c2.Cno AS Prerequisite_ID, c2.Cname AS Prerequisite_Name FROM Course c1, Course c2 WHERE c1.Cpno = c2.Cno;

⑤ 查询选修了全部课程的学生学号，姓名（Exists/Not Exists）

SELECT Sno, Sname FROM Student S WHERE NOT EXISTS ( SELECT Cno FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.Cno = Course.Cno AND SC.Sno = S.Sno ) );