开发笔记:登陆+注册

多表查询练习

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()