作业20200508

SQL查询练习

6、 查询姓李老师的个数

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

8、 查询物理课程比生物课程高的学生的学号

SELECT
	t1.student_id 
FROM
	( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '物理' ) ) AS t1
	INNER JOIN ( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '生物' ) ) AS t2 ON t1.student_id = t2.student_id 
WHERE
	t1.num > t2.num;

12、查询李平老师教的课程的所有成绩记录

SELECT
	course.cname,
	t1.num_list 
FROM
	course
	INNER JOIN (
	SELECT
		course_id,
		group_concat( num ) AS num_list 
	FROM
		score 
	WHERE
		score.course_id IN ( SELECT course.cid FROM course WHERE teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) 
	GROUP BY
		course_id 
	) AS t1 
WHERE
	course.cid = t1.course_id

13、查询全部学生都选修了的课程号和课程名

SELECT
	cid,
	cname 
FROM
	course 
WHERE
	course.cid IN ( SELECT course_id FROM score GROUP BY course_id HAVING count( student_id ) = ( SELECT count( sid ) FROM student ) )

18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT
	student.sname,
	t1.num 
FROM
	student
	INNER JOIN ( SELECT student_id, num FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname = '生物' ) AND num < 60 ) AS t1 
WHERE
	student.sid = t1.student_id

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

SELECT
	student_id,
	avg( num ) AS avg_num 
FROM
	score 
WHERE
	course_id IN ( SELECT cid FROM course WHERE teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) 
GROUP BY
	student_id 
ORDER BY
	avg_num DESC 
	LIMIT 1

登录注册

import pymysql


class MyDBHandle:
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='123456',
            db='day49',
            charset='utf8',
            autocommit=True
        )
        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)

    def _db_handle_select(self, *args):
        sql = 'select * from user where username=%s'
        res = self.cursor.execute(sql, args)
        if not res:
            return {}
        else:
            return self.cursor.fetchone()

    def _db_handle_insert(self, *args):
        sql = 'insert into user(username, password) values(%s, %s)'
        self.cursor.execute(sql, args)

    def login_interface(self, name, pwd):
        user_dict = self._db_handle_select(name)
        if not user_dict:
            return False, '用户名不存在'
        else:
            if pwd != user_dict.get('password'):
                return False, '用户名或密码错误'
        return True, '登录成功'

    def register_interface(self, name, pwd):
        user_dict = self._db_handle_select(name)
        if user_dict:
            return False, '用户名已经存在'
        self._db_handle_insert(name, pwd)
        return True, '注册成功'


class MyTest:

    def __init__(self, db_handle_obj):
        self.db_handle = db_handle_obj

    def run(self):
        while 1:
            cmd_list = [('登录', 'login'), ('注册', 'register')]
            for index, item in enumerate(cmd_list):
                print(index, item[0])
            cmd = input('请输入功能编号：').strip()
            if not cmd.isdigit() or int(cmd) not in range(len(cmd_list)):
                continue
            cmd_func = cmd_list[int(cmd)][1]
            if hasattr(self, cmd_func):
                func = getattr(self, cmd_func)
                func()

    def login(self):
        while 1:
            username = input('username>>:').strip()
            password = input('password>>:').strip()
            flag, msg = self.db_handle.login_interface(username, password)
            print(msg)
            if flag: break

    def register(self):
        while 1:
            username = input('username>>:').strip()
            password = input('password>>:').strip()
            re_pwd = input('re_pwd>>:').strip()
            if re_pwd != password:
                print('两次密码输入不一致')
                continue
            flag, msg = self.db_handle.register_interface(username, password)
            print(msg)
            if flag: break


if __name__ == '__main__':

    test = MyTest(MyDBHandle())
    test.run()