mysq练习题及pymsql使用

昨日内容回顾

多表查询

1.子查询
	将一条sql语句的查询结构作为另一条sql语句的条件
    
2.连表操作
	inner join	内连接
    	select * from emp inner join dep on emp.dep_id=dep.id;
    left join	左连接
    right join	右连接
    union		全连接

可视化软件之navicat

1.百度找一下即可

2.连接也百度

3.基本操作（建库的时候要指定字符编码）

4.逆向数据库到模型

5.转储sql文件

6.运行sql文件

7.新建查询（支持手写sql语句）

8.mysql中的注释
	--
    #

python操作mysql

pymysql模块

import pymysql
conn = pymysql.connect(
	host,
    port,
    user,
    password,
    database,
    charset
)
curcor = conn.cursor()
cursor.excute()
cursor.fetchall()
# MySQL默认的端口号是3306

作业

1.查询平均年龄在25岁以上的部门名
	要求使用两种方式实现
  	子查询
        连表操作
# 子查询求解
	1.先查询平均年龄在25岁以上的部门的id号
    	select dep_id from emp group by dep_id having avg(age)>25;
    2.再去部门表中根据id号查询出部门名称
    	select * from dep where id in (201,202);
select name from dep where id in (select dep_id from emp group by dep_id having avg(age)>25); 

# 连表查询
	1.先拼接表
    	select * from emp inner join dep on emp.dep_id=dep.id;
    2.之后直接分组过滤即可
		select dep.name from emp inner join dep on emp.dep_id=dep.id
        group by dep.name
        having avg(emp.age)>25
        ;
'''有一些复杂的查询可能需要同时使用到子查询和连表操作'''

今日内容概要

mysql 查询练习题
pymysql更多方法
python结合mysql实现用户的登录注册功能

今日内容详细

mysql查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、 查询没有报李平老师课的学生姓名
8、 查询没有同时选修物理课程和体育课程的学生姓名
9、 查询挂科超过两门(包括两门)的学生姓名和班级


-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要用到哪些表		课程表与老师表
# 2.大致浏览相关表数据
# 3.再动手写SQL语句
-- select * from course;
-- select * from teacher;
-- SELECT
-- 	course.cname,
-- 	teacher.tname
-- FROM
-- 	course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确需要用到的表	成绩表与学生表
# 2.大致量相关表数据
# 3.通过平均成绩知道需要使用聚合函数从而明确需要对表进行分组
# 4.考虑使用子查询或者连表操作
-- 先查询平均成绩大于80的学生的id号
-- select student_id,avg(num) from score group by student_id having avg(num)>80;
-- 将上述结果生成的表与学生表做拼接操作
-- 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;
-- 7、查询没有报李平老师课的学生姓名
# 1.需要用到的表	老师表 课程表 学生表 成绩表
# 2.先查李平老师教授的课程id
# 3.再查报了李平老师课程的学生id
# 4.最后去学生表里面取反查询没有报李平老师课程的学生姓名
-- 先查询李平老师教授的课程id号
-- select cid from course where teacher_id in
-- (select tid from teacher where tname='李平老师');
-- 去成绩表中筛选出选了李平老师课程的学生id
-- select DISTINCT student_id from score where course_id in (select cid from course where teacher_id in
-- (select tid from teacher where tname='李平老师'));
-- 取反获取没有报李平老师课程的学生姓名
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid NOT IN (
-- 		SELECT DISTINCT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					teacher_id IN (
-- 						SELECT
-- 							tid
-- 						FROM
-- 							teacher
-- 						WHERE
-- 							tname = '李平老师'
-- 					)
-- 			)
-- 	);
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(只要选了一门的 不要两门都选和一门都没选)
# 1.需要使用的表有  课程表 成绩表 学生表
# 2.先查询物理和体育课程的课程id号
# 3.去成绩表中筛选出所有选了物理、体育的数据
# 4.针对第三步的结果 按照学生分组利用count筛选出只报了一门的数据
-- 先拿课程的id号
-- select cid from course where cname in ('物理','体育');
-- 再去成绩表中筛选出所有跟物理 体育相关的数据
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
-- 按照学生id分组 利用聚合函数count筛选出只报了一门课程的id
-- select score.student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by score.student_id
-- having count(score.course_id)=1;
-- 去学生表里面筛选出学生姓名
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid IN (
-- 		SELECT
-- 			score.student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					cname IN ('物理', '体育')
-- 			)
-- 		GROUP BY
-- 			score.student_id
-- 		HAVING
-- 			count(score.course_id) = 1
-- 	);
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.需要用到的表  成绩表  学生表  班级表
# 2.先取成绩表中筛选出所有分数小于60分的数据
# 3.再按照学生id分组 统计每个学生挂科的门数
# 4.通过学生id去学生表和班级表中筛选出需要的数据
-- select student_id from score where num < 60 group by student_id having count(num)>=2;
-- 先将学生表和班级表拼接起来
SELECT
	sname,
	caption
FROM
	class
INNER JOIN student ON class.cid = student.class_id
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(num) >= 2
	);

pymysql详细操作

# 导入模块
import pymysql

# 连接MySQL服务端
conn = pymysql.connect(
    # 服务端的ip地址
    host='127.0.0.1',  # 本机回环地址
    # 服务端的port地址
    port=3306,  # MySQL默认端口号
    # 用户名
    user='root',
    # 密码
    password='123456',
    # 要操作的数据库名
    database='db9',
    # 字符编码
    charset='utf8',
    # 自动确认
    autocommit=True
)
# 产生一个可以执行命令的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 获取用户的用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 构造数据库的查询语句
# sql = 'select * from userinfo where name="%s" and password="%s"'
sql = 'select * from userinfo where name=%s and password=%s'
# 执行查询语句
# print(sql% (username, password))  # 不要自己拼接关键数据
# cursor.execute(sql % (username, password))
cursor.execute(sql, (username, password))  # 自动拼接 并剔除特殊符号
# 获取结果
res = cursor.fetchall()
if res:
    print('登录成功')
else:
    print('用户名或密码错误')

# 构造SQL语句
# sql = 'select * from teacher;'
# sql = 'insert into userinfo(name,password) values("tom","444");'  # 插入不行
# sql = 'select * from userinfo;'  # 查询可以
# sql = 'update userinfo set name="jasonNB" where id=1;'  # 更新不行
# sql = 'delete from userinfo where id=3;'  # 删除不行

"""针对数据的增 删 改操作 默认不能直接执行 需要你二次确认"""

# 利用cursor发送给服务端执行sql语句
# affect_row = cursor.execute(sql)
# conn.commit()  # 确认可以操作

# print(affect_row)
# print(cursor.fetchall())
# 获取执行之后的结果
# res = cursor.fetchall()  # 查询所有
# print(res)
# res1 = cursor.fetchone()  # 只获取结果的第一条
# print(res1)
# res2 = cursor.fetchmany(2)  # 括号内指定要获取的条数
# print(res2)
# res1 = cursor.fetchone()  # {'tid': 1, 'tname': '张磊老师'}
# res2 = cursor.fetchone()  # {'tid': 2, 'tname': '李平老师'}
# res3 = cursor.fetchall()  # [{'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
# print(res1,res2,res3)
"""fetchone many all跟文件的光标移动类似"""

# 可以人为修改移动
# res1 = cursor.fetchone()
# # cursor.scroll(1,'relative')  # 相对于当前位置往后移动
# # cursor.scroll(2,'absolute')  # 相对于开头往后移动
# res2 = cursor.fetchone()
# print(res1,res2)

作业

1.消化吸收课上练习题，尽量做到脱稿完成
2.尝试着完成下列练习题(至少能独立完成一半即可)
	https://www.cnblogs.com/Dominic-Ji/p/10875493.html
3.利用pymysql编写用户的登录注册功能
	注册
    	用户名不能重复
4.复习python基础和MySQL基础全部内容

posted @ 2021-01-11 11:40 KaiLun 阅读(84) 评论(0) 收藏举报

刷新页面返回顶部

老夫早已天下无敌

mysq练习题及pymsql使用

昨日内容回顾

今日内容概要

今日内容详细

mysql查询练习题

pymysql详细操作

作业

公告