可视化软件Navicat与pymysql模块

第三方开发的用来充当数据库客户端的简单快捷的操作界面
	无论第三方软件有多么的华丽呼哨，底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多，其中针对MySQL最出门的就是Navicat

1.浏览器搜索Navicat直接下载
	版本很多，能够充当的数据库客户端也很多
2.破解方式
	先试用再破解、直接下载破解版（老版本）、失修试用日期
3.常用操作
	有些功能可能需要自己修改SQL预览
    	创建库、表、记录、外键
       逆向数据库到模型、模型创建
    	新建查询可以编写SQL语句并自带提示功能
       SQL语句注释语法:
        -- 
        # 
        \**\
        运行、转储SQL文件

多表查询练习题

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步查一步步补
"""
1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要哪些表  课程表、老师表
# 2.查看表结构
-- 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.查看表数据
-- SELECT * FROM student;
-- SELECT * FROM score;
# 3.两张表需要有姓名和平均成绩，所以要连表查询 
-- 1.先获取学生的平均分数，在score表中
-- SELECT student_id ,AVG(num) FROM score group by student_id;
-- 2.再筛选平均分大于80分的同学，获取id
-- SELECT
-- 	student_id,
-- 	AVG( num ) 
-- FROM
-- 	score 
-- GROUP BY
-- 	student_id 
-- HAVING
-- 	AVG( num ) > 80;-- 3.根据学生id获得学生的名字在student表中
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、查询没有报李平老师课的学生姓名
SELECT
	sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		score.course_id IN (
		SELECT
			cid 
		FROM
			course 
		WHERE
			course.teacher_id =(
			SELECT
				tid 
			FROM
				teacher 
			WHERE
				tname = '李平老师' 
			)));

8、查询没有同时选修物理课程和体育课程的学生姓名
SELECT
	sname 
FROM
	student 
WHERE
	sid = (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course 
		WHERE
		cname IN ( '物理', '生物' )) 
	GROUP BY
		student_id 
	HAVING
	count( course_id )= 1 

9、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT
	class.caption,
	sname_id.sname 
FROM
	class
	INNER JOIN (
	SELECT
		sname,
		class_id 
	FROM
		student 
WHERE
	sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 )) AS sname_id ON class.cid = sname_id.class_id;

python操作MySQL

import pymysql


# 1.连接MySQL服务器
conn = pymysql.connect(
    user='root',     # 登录的用户
    password='123',  # 登录的密码
    host='127.0.0.1',  # 服务端的IP地址
    db='db1',   # 访问的库名
    charset='utf8mb4', # 编码
    autocommit=True   # 默认增删改二次确认
)
# 2.产生游标对象
# cursor = conn.cursor()
# 括号内部填写额外参数，数据是元组，指定性不强
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 这产生就是一个字典的形式
# 3.编写SQL语句
sql = 'select * from emp'
# 4.发送sql语句
affect_rows = cursor.execute(sql)
# execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

pymysql补充说明

1.获取数据
	ferchall()		获取所有的结果
 	fetchone()		获取结果集的第一个数据
   fetchmany()		获取指定数量的结果集
ps:注意三者都有类似于文件光标移动的特性
  cursor.scroll(1,'relative')  # 基于当前位置往后移动
  cursor.scroll(0,'aadsolute')  # 基于数据的开头往后移动
2.增删改查
	autocommit=True  # 针对增删改自动确认
   conn.conmmit()	# 针对增删改需要二次确认（代码确认）