• Day 47 MySQL Navcat、PyMYsql

    Day 47 MySQL Navcat

    文章目录

    1、Navicat可视化软件

    可以充当很多数据库软件的客户端 最主要的用于MySQL
    直接从官网下载 然后网上找破解工具

    1.1、多表查询练习

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

    1. 先确定需要几张表

    2. 简单查看每张表的数据

      select * from course;
select * from teacher;

      • 1
      • 2

    3. 思考查询逻辑 多表查询(连表操作)

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

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

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

    1. 先确定需要几张表 成绩表 学生表

    2. 简单查看表中数据

      select * from student;
select * from score;

      • 1
      • 2

    3. 先查询成绩表中平均成绩大于80分的数据

    4. 按照学生编号分组 利用聚合函数avg求出所有学生编号对应的平均成绩

      select student_id,avg(num) from score group by student_id;

      • 1

    5. 基于上述分组之后的结果筛选出平均成绩大于80的数据

      select student_id,avg(num) from score group by student_id having avg(num) > 80;

      • 1

    6. .最终的结果需要从上述sql语句的结果表中获取一个字段和学生表中获取一个字段

      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;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 需要 给avg（num）取别名 不然显示字段那 会再执行一遍 取平均值 然后 取不到inum 会报错

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

    1. 先确定需要用到的表

    2. 简单的查看表中数据

    3. 解题思路

      1. 直接查其他老师教的课然后一步步查到学生
      2. 查报了李平老师课的学生编号然后取反即可(推荐)

    4. 先获取李平老师教授的课程id号

      select tid from teacher where tname='李平老师'

      • 1

    5. 子查询获取课程编号

      select cid from course where teacher_id=(select tid from teacher where tname='李平老师')

      • 1

    6. 根据课程编号去成绩表中筛选出所有报了课程编号的数据

      select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))

      • 1

    7. 根据上述学生id号去学生表中取反 获取没有报李平老师课程的学生姓名

      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 =( SELECT tid FROM teacher WHERE tname = '李平老师' )) )

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)

    1. 先确定需要几张表

    2. 简单的查看表里面的数据

    3. 先获取物理和体育课程的id号

      select cid from course where cname in ('物理','体育');

      • 1

    4. 根据课程的id号先去成绩表中过滤掉没有选择这些课程的数据

      select * from score where course_id in (select cid from course where cname in ('物理','体育'))

      • 1

    5. 基于上述表统计每个学生编号报了几门课

      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

      • 1

    6. 根据上述学生id获取学生姓名

      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)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22

    查询挂科超过两门(包括两门)的学生姓名和班级

    1. 先确定需要几张表

    2. 简单的查看表里面的数据

    3. 先筛选出分数小于60的数据

      select * from score where num < 60;

      • 1

    4. 按照学生id分组然后计数即可

      select student_id from score where num < 60 group by student_id having count(course_id) >= 2;

      • 1

    5. 先连接班级表和学生表

      select * from class inner join student on class.cid = student.class_id;

      • 1

    6. 连表操作

      SELECT
	class.caption,
	student.sname 
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( course_id ) >= 2 );

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    2、python操作MySQL

    第三方模块:pip3 install pymysql

    import pymysql


# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db2',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'select * from user'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res = cursor.fetchall() # 获取所有数据
print(res)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    2.1、结果获取

    cursor.fetchall()  # 获取结果的所有数据
cursor.fetchone()   # 获取结果集中一条数据
cursor.fetchall()   # 获取结果集中所有数据
cursor.fetchmany(1)  # 获取结果集中指定条的数据

    • 1
    • 2
    • 3
    • 4

    类似于文件光标的概念

    # cursor.scroll(2, mode='relative')  # 基于当前位置往后移动
cursor.scroll(0, mode='absolute')  # 基于数据集开头的位置往后移动

    • 1
    • 2

    2.2、SQL注入问题

    前戏
    只需要用户名即可登录
    不需要用户名也能登录

    问题

    SQL注入
select * from userinfo where name='jason' -- haha' and pwd=''
select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''

    • 1
    • 2
    • 3

    本质
    利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑

    措施
    针对用户输入的数据不要自己处理 交给专门的方法自动过滤

    import pymysql

# 连接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db2',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)

# 产生一个游标对象（等待输入命令）  固定搭配
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 获取用户数据
user_name = input('username>>>').strip()
user_pwd = input('userpwd>>>').strip()

# 编写sql语句
sql1 = 'select * from user where name=%s and pwd=%s'

# 发送给服务端
cursor.execute(sql1, (user_name, user_pwd))
# 获取命令 的执行结果
res = cursor.fetchone()
if res:
    print('登录成功')
else:
    print('账号或密码错误')


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    补充

    cursor.executemany()  # 可以一次性插入多行数据

    • 1

    3、关于MySQL知识点补充

    as语法
    给字段起别名 给表起别名

    comment语法
    给表、字段添加注释信息

    create table server(id int) comment '这个server意思是服务器表'
	create table t1(
    	id int comment '用户编号',
       name varchar(16) comment '用户名'
    ) comment '用户表';

    • 1
    • 2
    • 3
    • 4
    • 5

    • 查看注释

      show create table #查看字段注释
use information_schema #查看表注释

      • 1
      • 2

    concat、concat_ws语法
    concat用于分组之前多个字段数据的拼接
    concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码

    4.exists语法

    select * from userinfo where exists (select * from department where id<100)
#exists后面的sql语句如果有结果那么执行前面的sql语句  如果没有结果则不执行

    • 1
    • 2
  • 相关阅读:
    echarts案例之日历
    2、数组、Map+HashMap、Set+Hashset、Char和Character类、String类和Char类、Math类
    如何利用快解析远程访问家庭智能网关
    编译支持国密的抓包工具 WireShark
    memset和bzero性能分析
    普通Java工程可执行JAR两种打包方式探讨
    ChatGPT生成量化交易策略，真好玩
    如何在不使用非安全引导程序的情况下使用TF-A直接引导Linux内核?
    扫地机器人遇冷？科沃斯、石头科技求变
    leetcode笔记
  • 原文地址：https://blog.csdn.net/weixin_71967396/article/details/126414976