登陆+注册

多表查询练习

1、查询所有的课程的名称以及对应的任课老师姓名

SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;

2、查询学生表中男女生各有多少人

SELECT
	gender,
	count( gender ) 
FROM
	student 
GROUP BY
	gender 
HAVING
	count( gender );

3、查询物理成绩等于100的学生的姓名

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN (
	SELECT
		* 
	FROM
		score 
	WHERE
	course_id = ( SELECT cid FROM course WHERE cname = '物理' )) AS t ON student.sid = t.student_id 
WHERE
	num = 100;

4、查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT
	student.sname,
	t.avg_num 
FROM
	student
	INNER JOIN 
	( SELECT score.student_id, avg( num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t 
	ON student.sid = t.student_id;

5、查询所有学生的学号，姓名，选课数，总成绩

SELECT
	student.sid,
	student.sname,
	sum_num,
	course_count 
FROM
	student
	INNER JOIN (
	SELECT
		student_id,
		sum( num ) AS sum_num,
		count( student_id ) AS course_count 
	FROM
		score 
	GROUP BY
		student_id 
HAVING
	count( student_id )
    ) AS t 
	ON student.sid = t.student_id;

6、 查询姓李老师的个数

SELECT
	count( tname ) 
FROM
	teacher 
WHERE
	tname LIKE '%李%';

7、 查询没有报李平老师课的学生姓名

SELECT
	sid,
	sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		course_id NOT IN (
		SELECT
			cid 
		FROM
			course 
	WHERE
	teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' )));

登陆及注册

# setting
import os

BASE_DIR = os.path.dirname(os.path.dirname(__file__))

# src
from interface.test_IF import login_IF,register_IF

# 登陆功能
def login():
    while True:
        username = input('name>>').strip()
        password = input('password>>').strip()
        # 调用登陆接口
        flag,msg = login_IF(username,password)
        if flag:
            print(msg)
            break
        else:
            print(msg)

# 注册功能
def register():
    while True:
        username = input('name>>').strip()
        password = input('password>>').strip()
        flag,msg = register_IF(username,password)
        if flag:
            print(msg)
            break
        else:
            print(msg)


# 运行
def run():
    func_dic = {
        '0':exit,
        '1':register,
        '2':login
    }
    print('''
            0 退出
            1 注册
            2 登陆
            
                ''')
    while True:
        cmd = input('请输入命令编号>>').strip()
        if cmd not in func_dic:
            continue
        func_dic.get(cmd)()

# db_handle
import pymysql


def select(username):
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        database='practice',
        user='root',
        passwd='1026',
        charset='utf8',
        autocommit=True
    )
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    sql = 'select * from user where name=%s '
    res = cursor.execute(sql,(username,))
    if res:
        return cursor.fetchone().get('password')

    else:
        return False

def save(username,password):
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        database='practice',
        user='root',
        passwd='1026',
        charset='utf8',
        autocommit=True
    )
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = 'insert into user(name,password) values(%s,%s)'
    res = cursor.execute(sql,(username,password))
    if res:
        return True

# modles
from data.db_handle import select,save

class Base:
    @classmethod
    def select_data(cls,name):
        pwd = select(name)
        return pwd
    def save_data(self,username,pwd):
        save(username,pwd)


class People(Base):
    def __init__(self,name,password):
        self.name = name
        self.password = password

# test_IF
from data import modles

def login_IF(username,password):
    pwd = modles.People.select_data(username)

    if pwd:
        if password == pwd:
            return True,'登陆成功'
        else:
            return False,'密码错误'
    else:
        return False,'登用户名不存在'


def register_IF(username,password):
    pwd = modles.People.select_data(username)
    if pwd:
        return False, '用户已存在'
    else:
        obj = modles.People(username,password)
        obj.save_data(username,password)
        return True, '注册成功'

# start
from core.src import run

if __name__ == '__main__':
    run()