Mysql实操基础(数据库作业)

附上官网地址MySQL

1. 登录

mysql -u username -p password

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

然后先创建数据库

CREATE DATABASE 库名;

使用数据库

use 库名;

2. 作业内容

1)创建关系(create

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

2)录入数据( Insert)
  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 ) );

  • 9
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

白鹿依海

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值