mysql作业

--1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
    cname,
    tname 
FROM
    course
    INNER JOIN teacher ON course.teacher_id = teacher.tid

--2、查询学生表中男女生各有多少人
SELECT
    gender,
    count( sid ) 
FROM
    student 
GROUP BY
    gender

--3、查询物理成绩等于100的学生的姓名
SELECT
    sname 
FROM
    student 
WHERE
    sid IN (
    SELECT
        student_id 
    FROM
        course
        INNER JOIN score ON course.cid = score.course_id 
    WHERE
        course.cid = 2 
        AND score.num = 100 
    )

--4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
    student.sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) AS avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        avg(num) > 80
) AS t1 ON student.sid = t1.student_id;

--5、查询所有学生的学号，姓名，选课数，总成绩
SELECT
    student.sid,
    student.sname,
    t1.course_num,
    t1.total_num
FROM
    student
LEFT JOIN (
    SELECT
        student_id,
        COUNT(course_id) course_num,
        sum(num) total_num
    FROM
        score
    GROUP BY
        student_id
) AS t1 ON student.sid = t1.student_id;

用数据库实现登入注册

#程序入口
import os,sys

sys.path.append(os.path.dirname(__file__))

from core import src
if __name__ == '__main__':
    src.run()

#core中的src
from interfaces import interface



def login():
    while True:
        name = input('输入账号：').strip()
        pwd = int(input('输入密码：').strip())
        flag,msg = interface.login_interface(name,pwd)
        if flag:
            print(msg)
            break
        else:
            print(msg)

def register():
    while True:
        username = input('请输入用户名: ').strip()
        password = input('请输入密码: ').strip()
        re_password = input('请确认密码: ').strip()
        if password == re_password:
            flag, msg = interface.register_interface(username, int(password))
            if flag:
                print(msg)
                break
            else:
                print(msg)
        else:
            print('两次密码不一致!')


func_dic = {
    '1': login,
    '2': register,
}

def run():
    while True:
        print('''
        ====== 欢迎来到选课系统 ======
              1.登入功能
              2.注册功能
              3.退出
        =========== end =============
        ''')

        cmd = input('选择功能编号：').strip()
        if cmd == '3':
            break
        if cmd not in func_dic:
            print('请选择正确的功能')
            continue
        func_dic.get(cmd)()

#interface接口
from db import db_handler

def login_interface(user,pwd):
    flag,msg = db_handler.select(user)
    print(msg)
    if flag:
        if msg.get('pwd')==pwd:
            return True,'登入成功'
        else:
            return False,'密码错误'
    else:
        return False,'用户不存在'

#注册接口
def register_interface(username,password):
    flag,msg = db_handler.select(username)
    if flag:
        return False,'用户已存在'
    flag = db_handler.save(username,password)
    if flag:
        return True,f'用户{username}创建成功!'

#db_hander
import pymysql

def connt_mysql():
    connt = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = None,
        database = 'db1',
        charset = 'utf8',
        autocommit=True
    )
    cursor = connt.cursor(cursor=pymysql.cursors.DictCursor)
    return cursor

def select(username):
    cursor = connt_mysql()
    sql = 'select * from t_user where username = %s'
    row = cursor.execute(sql,(username))
    data = cursor.fetchone()
    return row,data

def save(username,pwd):
    cursor = connt_mysql()
    sql = 'insert into t_user values(%s,%s)'
    row = cursor.execute(sql,(username,pwd))
    return row