学生管理系统（MySQL版）

学生管理系统数据库版

思维导图

一、 数据源

学号姓名性 别年龄班级专业学院电话2021001李晓红女302021级软件4班软件技术人工智能与大数据学院159454567802021002王晓刚男182021级软件4班软件技术人工智能与大数据学院138909045672021003唐雨涵女192021级软件4班软件技术人工智能与大数据学院188787890232021104张三丰男182021级大数据1班大数据技术与应用人工智能与大数据学院159454567802021105肖雨林男182021级大数据1班大数据技术与应用人工智能与大数据学院188909045602021106郑小翠女192021级大数据1班大数据技术与应用人工智能与大数据学院15890904567

二、建立数据库

导入MySQL数据源，并建立链接

1、首先新建MySQL连接

输入连接名“student”
主机名可填写“local host”或“127.0.0.1”
MySQL默认端口号为“3306”
用户名和密码可自己任意填写

建立主程序

1.主界面

2.登入后界面

3.定义插入记录

#插入学生记录defadd_student():
    cursor = main()
    id1 =int(input('学号：'))
    name =input('姓名：')
    gem =input('性别：')
    age =int(input('年龄：'))
    class1 =input('班级：')
    major1 =input('专业：')
    college =input('学院：')
    telephone =int(input('电话：'))
    add = cursor.execute('insert into stu (id, name, genden, age, class, major, college,telephone) values(%s,%s,%s,%s,%s,%s,%s,%s)',(id1, name, gem, age, class1, major1, college,telephone))if add >=1:
        conn.commit()print('插入记录成功！')else:print('插入记录失败！')

4.定义查询记录

#查询的菜单defquery_student():whileTrue:print('查询学生记录')print('================')print('1、按学号查询学生记录')print('2、按姓名查询学生记录')print('3、查询全部学生记录')print('4、返回上级菜单')print('=================')
        mc3 =int(input('请输入查询的菜单号：'))if mc3 ==1:
            by_id()elif mc3 ==2:
            by_name()elif mc3 ==3:
            by_all()else:break#按学号查询defby_id():
    cursor = main()
    choice_id =int(input('请输入学号：'))
    cursor.execute('select * from stu where id =%s',(choice_id))
    students = cursor.fetchall()for stu in students:print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6], stu[7])print('查询成功')
        yn =input('是否继续查询(yes/no)：')if yn =='yes':
            by_id()else:
            query_student()#按姓名查询（以防学号输入错误）defby_name():
    cursor = main()
    choose_name =input('请输入姓名：')
    cursor.execute('select * from stu where name =%s',(choose_name))
    students = cursor.fetchall()for stu in students:print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6], stu[7])print()
        re =input('是否继续查询yes/no：')if re =='yes':
            by_name()else:
            query_student()#查询所有学生defby_all():
    cursor = main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()for student in students:print('\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}'.format(student[0], student[1], student[2], student[3], student[4], student[5], student[6], student[7]))

5.定义删除记录

#删除学生记录defdel_student():
    cursor = main()id=int(input('输入想要删除学生的学号：'))
    dele = cursor.execute('delete from stu where id = {}'.format(id))if dele ==1:
        conn.commit()print('删除成功！')else:print('删除失败！')#删除的菜单defdelete1_student():print('============================')print('1、删除学生信息')print('2、回到初始界面')print('============================')
    mc4 =int(input('Input menu number:'))if mc4 ==1:
        del_student()elif mc4 ==2:
        login()

6.定义修改记录

defup_student():
        cursor = main()
        cur=int(input('请输入需要修改学生的学号：'))
        cursor.execute('select * from stu where id = %s',(cur))print('==============')print('1、修改姓名')print('2、修改性别')print('3、修改年龄')print('4、修改班级')print('5、修改专业')print('6、修改学院')print('7、修改电话')print('8、返回上级菜单')print('==============')
        mc2 =int(input('请输入菜单号：'))if mc2 ==1:
            name =input('请输入需要修改的名字：')
            a = cursor.execute('update stu set name = %s where id = %s',(name, cur))if a ==1:
                conn.commit()print('修改成功！')else:print('修改失败！')elif mc2 ==2:
            gender1 =input('请输入需要修改的性别：')
            a = cursor.execute('update stu set genden = %s where id = %s',(gender1, cur))if a >1:
                conn.commit()print('修改成功！')else:print('修改失败！')elif mc2 ==3:
            age1 =int(input('请输入需要修改的年龄：'))
            a = cursor.execute('update stu set age = %s where id = %s',(age1, cur))if a >1:
                conn.commit()print('修改成功！')else:print('修改失败！')elif mc2 ==4:
            class1 =input('请输入需要修改的班级：')
            a = cursor.execute('update stu set class = %s where id = %s',(class1, cur))if a >1:
                conn.commit()print('修改成功！')else:print('修改失败！')elif mc2 ==5:
            major1 =input('请输入需要修改的专业：')
            a = cursor.execute('update stu set major = %s where id = %s',(major1, cur))if a >1:
                conn.commit()print('修改成功！')else:print('修改失败！')elif mc2 ==6:
            college1 =input('请输入需要修改的学院：')
            a = cursor.execute('update stu set college = %s where id = %s',(college1, cur))if a >1:
                conn.commit()print('修改成功！')else:print('修改失败！')elif mc2 ==7:
            telephone1 =input('请输入需要修改的电话：')
            a = cursor.execute('update stu set college = %s where id = %s',(telephone1, cur))whileTrue:iflen(telephone1)!=11:print('请重新输入电话号码!')else:breakif a >1:
                        conn.commit()print('修改成功！')else:print('修改失败！')else:
            login()

7.定义显示所有记录

#显示所有的学生记录       defprint_student():
    cursor=main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()for stu in students:print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6],stu[7])