1.使用Python操作MySQL数据库
在Windows系统中安装好MySQL8.0.23和Python3.8.7,然后再完成下面题目中的各项操作。
现有以下三个表格:
表1 学生表:Student(主码为Sno)
学号(Sno) |
姓名(Sname) |
性别(Ssex) |
年龄(Sage) |
所在系别(Sdept) |
10001 |
Jack |
男 |
21 |
CS |
10002 |
Rose |
女 |
20 |
SE |
10003 |
Michael |
男 |
21 |
IS |
10004 |
Hepburn |
女 |
19 |
CS |
10005 |
Lisa |
女 |
20 |
SE |
表2 课程表:Course(主码为Cno)
课程号(Cno) |
课程名(Cname) |
学分(Credit) |
00001 |
DataBase |
4 |
00002 |
DataStructure |
4 |
00003 |
Algorithms |
3 |
00004 |
OperatingSystems |
5 |
00005 |
ComputerNetwork |
4 |
表3 选课表:SC(主码为Sno,Cno)
学号(Sno) |
课程号(Cno) |
成绩(Grade) |
10002 |
00003 |
86 |
10001 |
00002 |
90 |
10002 |
00004 |
70 |
10003 |
00001 |
85 |
10004 |
00002 |
77 |
10005 |
00003 |
88 |
10001 |
00005 |
91 |
10002 |
00002 |
79 |
10003 |
00002 |
83 |
10004 |
00003 |
67 |
通过编程实现以下题目:
- 查询学号为10002学生的所有成绩,结果中需包含学号、姓名、所在系别、课程号、课程名以及对应成绩。
import pymysql.cursors connect = pymysql.Connect( host='localhost', port=3306, user='root', passwd='abc123', db='school', charset='utf8' ) cursor = connect.cursor() cursor.execute(""" SELECT Student.Sno, Student.Sname, Student.Sdept, Course.Cno, Course.Cname, SC.Grade FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno WHERE Student.Sno = '10002'; """) result = cursor.fetchall() for x in result: print(x) connect.close()
- 查询每位学生成绩大于85的课程,结果中需包含学号、姓名、所在系别、课程号、课程名以及对应成绩。
import pymysql.cursors connect = pymysql.Connect( host='localhost', port=3306, user='root', passwd='abc123', db='school', charset='utf8' ) cursor = connect.cursor() cursor.execute(""" SELECT Student.Sno, Student.Sname, Student.Sdept, Course.Cno, Course.Cname, SC.Grade FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno WHERE SC.Grade > 85; """) result = cursor.fetchall() for x in result: print(x) connect.close()
- 由于培养计划改,现需将课程号为00001、课程名为DataBase的学分改为5学分。
import pymysql.cursors connect = pymysql.Connect( host='localhost', port=3306, user='root', passwd='abc123', db='school', charset='utf8' ) cursor = connect.cursor() cursor.execute(""" UPDATE Course SET Credit = 5 WHERE Cno = '00001' AND Cname = 'DataBase'; """) print("修改成功!") cursor.execute(""" SELECT Cno,Cname, Credit FROM Course WHERE Cno = '00001'; """) result = cursor.fetchone() print(result) connect.close()
- 将学号为10005的学生, OperatingSystems(00004)成绩为73分这一记录写入选课表中。
import pymysql.cursors connect = pymysql.Connect( host='localhost', port=3306, user='root', passwd='abc123', db='school', charset='utf8' ) cursor = connect.cursor() cursor.execute(""" INSERT INTO SC (Sno, Cno, Grade) VALUES ('10005', '00004', 73); """) connect.commit() print("添加成功!") cursor.execute(""" SELECT Student.Sno, Student.Sname, Student.Sdept, Course.Cno, Course.Cname, SC.Grade FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno WHERE Student.Sno = '10005'; """) result = cursor.fetchall() for x in result: print(x) connect.close()
- 将学号为10003的学生从这三个表中删除。
import pymysql.cursors connect = pymysql.Connect( host='localhost', port=3306, user='root', passwd='abc123', db='school', charset='utf8' ) cursor = connect.cursor() cursor = connect.cursor() cursor.execute(""" DELETE FROM Student WHERE Sno = '10003'; """) connect.commit() cursor.execute(""" DELETE FROM SC WHERE Sno = '10003'; """) connect.commit() print("删除成功!") connect.close()
2.使用Shell命令操作HDFS
在Windows系统中安装Hadoop3.1.3,然后完成下面题目中的各项操作:
1.使用自己的用户名登录Windows系统,启动Hadoop,为当前登录的Windows用户在HDFS中创建用户目录“/user/[用户名]”;
hadoop fs -mkdir /user
hadoop fs -mkdir /user/xiaoguan
hadoop fs -ls /user
2.接着在HDFS的目录“/user/[用户名]”下,创建test目录;
hadoop fs -mkdir /user/xiaoguan/test
hadoop fs -ls /user/xiaoguan
3.将Windows系统本地的一个文件上传到HDFS的test目录中,并查看上传后的文件内容;文章来源:https://www.uudwc.com/A/nPLLW/
hadoop fs -put E:\test.txt /user/xiaoguan/test
hadoop fs -ls /user/xiaoguan/test
hadoop fs -cat /user/xiaoguan/test/test.txt
4.将HDFS目录test复制到Windows本地文件系统的某个目录下。文章来源地址https://www.uudwc.com/A/nPLLW/
hadoop fs -get /user/xiaoguan/test/test.txt D:\test
dir D:\test