MySQL数据库学习笔记

今日内容概要

• Navicat可视化软件
• 多表查询练习题
• python操作MySQL
• 小知识点补充

今日内容详细

Navicat可视化软件

Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够让我们更加快速的操作数据库，并且提供操作数据库的快捷方式(鼠标点击)。

下载

navicat有很多版本，并且默认都是收费使用。正版可以免费体验14天 ， 针对这种图形化软件版本越新越好。直接百度官网即可下载。

下载官网: http://www.navicat.com.cn/

使用

内部封装了SQL语句，用户只需要鼠标点点点就可以快速操作（用鼠标点击代替了查找数据库的代码）。

连接mysql：

创建数据库：

创建表：

导入外部sql文件：

查询(自己写SQL语句)：选择数据库后，点击查询，然后新建查询，就可以自己写sql语句了。

使用navicat编写SQL，如果自动补全语句，那么关键字都会变大写。SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)。注释的方法一个是#还有一个是--。

多表查询练习题

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

1.先确定需要几张表 (课程表 老师表)
2.简单查看每张表中的数据，确定所需要的数据都在表中
3.思考查询逻辑 多表查询(连表操作)

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

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

1.先确定需要几张表 (成绩表 学生表)
2.简单查看每张表中的数据，确定所需要的数据都在表中
3.先查询成绩表中平均成绩大于80分的数据
SELECT student_id,AVG(num) as avg_num FROM score GROUP BY student_id HAVING AVG(num) > 80;
# 针对select后面通过函数或者表达式编写的字段为了后续取值方便 一般需要重命名成普通字段
4.最终的结果需要从上述sql语句的结果表中获取一个字段和学生表中获取一个字段
# 将SQL语句当做表来使用 连接的时候需要使用as起表名
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;

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

1.先确定需要几张表 (老师表 课程表 成绩表 学生表)
2.简单查看每张表中的数据，确定所需要的数据都在表中
3.查报了李平老师课的学生编号然后取反即可
4.先获取李平老师教授的id号
SELECT tid FROM teacher WHERE tname='李平老师';
5.子查询获取课程编号
SELECT cid FROM course where teacher_id in (SELECT tid FROM teacher WHERE tname='李平老师');
6.根据课程编号去成绩表中筛选出所有报了课程编号的数据
SELECT DISTINCT student_id from score where course_id in (SELECT cid FROM course where teacher_id in (SELECT tid FROM teacher WHERE tname='李平老师'));
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 IN ( SELECT tid FROM teacher WHERE tname = '李平老师' )));

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

1.先确定需要几张表 (课程表 成绩表 学生表)
2.简单查看每张表中的数据，确定所需要的数据都在表中
3.先获取物理和体育课程的id号
SELECT cid from course where cname in ('物理','体育');
4.根据课程的id号先去成绩表中过滤掉没有选择这些课程的数据
select * from score where course_id in (SELECT cid from course where cname in ('物理','体育'));
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;
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 
	);

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

1.先确定需要几张表 (班级表 成绩表 学生表)
2.简单查看每张表中的数据，确定所需要的数据都在表中
3.先筛选出分数小于60的数据
select * from score where num < 60;
4.按照学生id分组然后计数即可
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
5.先连接班级表和学生表
select * from class INNER JOIN student on student.class_id=class.cid;
6.合并45的sql语句
SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON student.class_id = class.cid 
WHERE
	sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

python操作MySQL

第三方模块下载: pip3.8 install pymysql

1.导入模块

import pymysql

2.链接服务端

conn = pymysql.connect(
    host='127.0.0.1',  # MySQL服务端的IP地址
    port=3306,  # MySQL默认PORT地址(端口号)
    user='root',  # 用户名
    password='123',  # 密码  也可以简写 passwd
    database='db5',  # 库名称  也可以简写 db
    charset='utf8mb4'  # 字符编码 千万不要加杠utf-8
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)  # 要善于查看源码获取信息

3.产生获取命令的游标对象(等待输入命令)

cursor = conn.cursor(
      cursor=pymysql.cursors.DictCursor
  )  # 括号内不写参数数据是元组 要是元组不够精确 添加参数则会将数据处理成字典

4.编写SQL语句

# SQL语句会被高亮显示 不用惊慌
sql1 = 'show tables;'
sql2 = 'select * from userinfo;'

5.发送给服务端(执行SQL语句)

'''execute执行sql语句 会自动帮你加分号结束符 如果你没有写的话'''
affect_rows = cursor.execute(sql1)
print(affect_rows)  # 执行SQL语句之后受影响的行数

6.获取结果

print(cursor.fetchall())  # 获取结果集中所有数据
print(cursor.fetchone())  # 获取结果集中的第一条数据
print(cursor.fetchmany(3))  # 获取结果集中的指定条数的数据
'''
控制结果集中光标的移动
类似于文件光标的概念
'''
cursor.scroll(2, mode='relative')  # 基于当前位置往后移动
cursor.scroll(0, mode='absolute')  # 基于数据集开头的位置往后移动

SQL注入问题

注入问题就是利用的是MySQL注释语法及逻辑运算符，对代码的判断进行干扰。

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

问题

SQL注入
# 只需要用户名即可登录
    select * from userinfo where name='jason' -- haha' and pwd=''
# 不需要用户名和密码也能登录
    select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''

解决SQL注入的问题其实也很简单，就是想办法过滤掉特殊符号。

方法：execute方法自带校验SQL注入问题，自动处理特殊符号。

sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password))  # 自动识别%s 并自动过滤各种符合 最后合并数据

# executemany方法(了解)---批量插入数据
sql = 'insert into userinfo(name,password) values(%s,%s)'
cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])

小知识点补充

1.as语法
    给字段起别名、起表名
2.comment语法
    给表、字段添加注释信息
    create table server(id int) comment '这个server意思是服务器表'
    create table t1(
       id int comment '用户编号',
       name varchar(16) comment '用户名'
    ) comment '用户表';
    """
    查看注释的地方
    show create table 
    use information_schema
    """
3.concat、concat_ws语法
    concat用于分组之前多个字段数据的拼接
    concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码
4.exists语法
    select * from userinfo where exists (select * from department where id<100)
    exists后面的sql语句如果有结果那么执行前面的sql语句
    如果没有结果则不执行