熟悉MySQL和HDFS操作

1.使用Python操作MySQL数据库

在Windows系统中安装好MySQL8.0.23和Python3.8.7，然后再完成下面题目中的各项操作。

现有以下三个表格：

表1 学生表：Student（主码为Sno）

表2 课程表：Course（主码为Cno）

表3 选课表：SC（主码为Sno，Cno）

通过编程实现以下题目：

1. 查询学号为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()

   

2. 查询每位学生成绩大于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()

   

3. 由于培养计划改，现需将课程号为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()

   

4. 将学号为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()

   

5. 将学号为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目录中，并查看上传后的文件内容；

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://uudwc.com/A/nPLLW

hadoop fs -get /user/xiaoguan/test/test.txt D:\test
dir D:\test