Navicat软件安装
参考：https://www.cnblogs.com/xuexianqi/p/12797170.html
练习题
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
score.student_id,
avg( num ) AS avg_num
FROM
score
INNER JOIN student ON score.student_id = student.sid
GROUP BY
score.student_id
HAVING
AVG( num ) > 80
) AS t1 ON student.sid = t1.student_id;
7、 查询没有报李平老师课的学生姓名
# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
score.student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
);
8、 查询没有同时选修物理课程和体育课程的学生姓名
(只要选了一门的 选了两门和没有选的都不要)
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) )
GROUP BY
score.student_id
HAVING
COUNT( score.course_id ) = 1
);
9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
class.caption,
student.sname
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2
);
pymysql模块
"""
支持python代码操作数据库MySQL
"""
pip3 install pymysql
##使用
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = 'root',
charset = 'utf8',
database = 'oldboy'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from teacher;'
res = cursor.execute(sql)
# 查
print(cursor.fetchone())
print(cursor.fetchall()) #读取数据类似文件光标的移动
print(cursor.fetchmany(3))
cursor.scroll(1, 'relative') #相对于光标的位置继续往后移动1位
cursor.scroll(1, 'absolute') #相对于数据的开头往后移动
# 增
sql = 'insert into user(name,password) values(%s,%s)'
# rows = cursor.execute(sql,('jackson',123))
rows = cursor.executemany(sql,[('xxx',123),('ooo',123),('yyy',123)])
print(rows)
conn.commit() # 确认
# 修改
sql = 'update user set name="jasonNB" where id=1'
rows = cursor.execute(sql)
print(rows)
conn.commit() # 确认
# 删除
sql = 'delete from user where id=7'
rows = cursor.execute(sql)
print(rows)
conn.commit() # 确认
# 还可以一次性插入N多条数据
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)])
sql注入
"""
利用一些语法的特性 书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法
select * from user where name='jason' -- jhsadklsajdkla' and password=''
select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''
"""
# 敏感的数据不要自己做拼接 交给execute帮你拼接即可
# 结合数据库完成一个用户的登录功能？
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
) # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
print('登录成功')
print(cursor.fetchall())
else:
print('用户名密码错误')