MySQL基础-note-4.23

最新推荐文章于 2024-07-01 08:30:00 发布

oa_s_i_s

最新推荐文章于 2024-07-01 08:30:00 发布

阅读量112

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/oa_s_i_s/article/details/116043081

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文介绍了MySQL数据库的SQL操作，包括增删改查、多表查询及Navicat可视化工具的使用。重点讲解了多表查询的实践，如内连接、外键、分组和聚合函数等，并提供了复杂查询的解题思路和SQL示例。此外，还探讨了Python的pymysql模块如何连接和操作MySQL数据库，以及如何防止SQL注入。

摘要由CSDN通过智能技术生成

昨日内容

1.修改表SQL语句

modify
rename
change
add
	first
	after
drop

2.查询关键字

1.select

2.from

3.where 筛选
	select * from 表名 where 筛选条件
		(1)数学运算符
		(2)成员运算
		(3)逻辑运算
		(4)模糊查询
			like % _

4.group by 分组
	按照指定的条件将单个个体组织成整体
	# 分组之后select后默认只能直接获取分组依据 其他字段都无法直接获取
	严格模式
		set global sql_mode='only_full_group_by'
	聚合函数
		max\ min\ sum\ count\ avg

5.having 过滤
功能与where一致，where用在分组之前，having用在分组之后

6.distinct 去重
	数据必须是一模一样的情况下才可以去重

7.order by 排序
	默认升序	asc
	也可以降序	desc

8.limit 分页
	当标中数据特别多的情况下，会采取limit限制展示条数

9.regexp 正则
	利用特殊符号的组合去字符串中筛选出符合条件的数据

3.多表查询

1.子查询
	将一张表的查询结果的sql语句用括号括起来，当作另外一条sql语句的查询条件
	
2.连表操作（链表操作，涉及到字符段名，最好都加上表名前缀）
	inner join	内连接
		select * from emp inner join dep on emp.dep_id=dep.id
	left join	左连接
	right join	右连接
	union	全联接	左右全部展示

4.Navicat可视化软件

1.选择需要连接的数据库服务端
2.输入用户名和密码测试之后确认
3.创建库、表、记录
	创建表的时候，针对主键一定不要忘了自增勾选

今日内容

1.Navicat详细操作

1.外键如何创建
	有一些功能如果没有，可以直接在SQL预览里面进行修改
2.逆向数据库到模型
	主要用来查看数据库下表之间的关系
3.转储SQL文件
	数据库表记录同步
4.自定义查询语句
	自动提示功能 更加便捷方便的书写SQL语句

2.多表查询练习题(偏难)

数据导入

/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

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

# 遇到较为复杂的查询题目可以先写中文步骤，之后再写SQL语句
# 在MySQL中，注释语句有两种，一种是#，一种是--
#SQL语句查询出来的虚拟表(SQL语句动态查询出来的)，如果后续还需使用，一般都是起别名 as……

(1).查询所有的课程的名称以及对应的任课老师姓名
select
	teacher.tname,
	course.cname
from
	teacher
	inner join coursse on teacher.tid=course.teacher_id

(4).查询平均成绩大于八十分的同学的姓名和平均成绩
#1.去成绩表中按照学生id分组
select score.student_id,avg(score.num) as 'avg_num'
from score group by score.student_id having avg(score.num)>80;
#2.将上述查询出来的虚拟表与学生表做连表操作
select * from student inner join 
(select score.student_id,avg(score.num) as 'avg_num'
from score group by score.student_id having avg(score.num)>80) as t1 on studen.sid=t1.student_id;
#3.明确select后面的字段
select student.sname,t1.avg_num from student inner join(
select score.student_id,avg(score.num) as 'avg_num' from score group by score.student_id 
having avg(score.num)>80) as t1 on student.sid=t1.student_id)

(7).查询没有报李平老师课的学生姓名(反向思维)
#1.先查询李平老师id号
select tid from teacher where tname='李平老师';
#2.再查询对应的课程id号
select cid from course where teahcer_id=(select tid from teacher where tname='李平老师');
#3.去成绩表中筛选出所有报了李平老师课程的学生id号
select distinct score.student_id from score where course_id in (
select cid from course where teahcer_id=(select tid from teacher where tname='李平老师'));
#4.最后去学生表中取反，获取没有报李平老师课程的学生姓名
select student.sname from student where student.sid not in (
select distinct score.student_id from score where course_id in (
select cid from course where teahcer_id=(select tid from teacher where tname='李平老师')));

(8).查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的，不要两门都报和一门都没有报的)
#1.先查询物理和体育课程的id号
select cid from course where cname in ('物理','体育');
#2.再去成绩表中先筛选出所有抱了物理和体育的数据(两门都报了，只报了其中一门)
select score.student_id from score where coursr_id in (
select cid from course where cname in ('物理','体育'));
#3.按照学生id分组，然后对课程计数，筛选出课程数是1的数据
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;
#4.根据学生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);

(9).查询挂科超过两门(包括两门)的学生姓名和班级
#1.先去成绩表中筛选出成绩小于60分的数据
select * from score where num<60;
#2.按照学生id分组，统计个数，筛选出大于等于2的
select student_id from score where nhum<60 group by student_id having(count(num))>=2;
#3.将学生表与班级表连表
#4.根据步骤2查询出来的学生id筛选数据即可
select student.sname,class.caption from student inner join class on student.class_id=class.cid where student.sid in (
select student_id from score where nhum<60 group by student_id having(count(num))>=2);

3.python操作MySQL

(1).下载模块
pymysql模块
	能够通过python代码操作MySQL数据库
pip3	install pymysql

(2).导入
#在给py文件命名的时候尽量不要与模块名冲突
inport pymysql
(3).使用
#产生一个链接对象
conn=pymysql.connect(
	host='127.0.0.1',	#本地回环地址
	port=3306,	#mysql默认的端口好
	user='root',	#用户名
	password='666',	#密码
	database='db4_2',	#需要指定操作的库
	charset='utf8'	#指定编码
)
#产生一个游标对象
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor）
#自定义SQL语句
sql='select * from student;'
#执行sql语句
affect_rows=cursor.execute(sql)
#返回值是当前SQL语句影响的行数
print(affect_rows)
#查询结果
res=cursor.fetchall()	#获取所有的返回结果
print(res)	#列表套字典

3.SQL注入
利用一些特殊符号的组合具有特殊含义，从而导致逻辑缺陷产生
select * from userinfo where name='kevin' -- dhhasj' and password=''
select * from userinfo where name='xx' or 1=1 --dsjds' and password=''

如何解决
	将关键性的的数据交由模块校验再拼接
#去MySQL中查询数据
sql='select * from userinfo where name=%s and password=%s'
#执行sql语句(execute本身也能识别%s)
cursor.execute(sql,(username,password))

4.增删改查
查询只是查看数据，并没有修改
而增删改都涉及到数据的变更，危险性更大，级别更高；需要进行二次确认	
conn.commit()	#二次确认

或者
conn=pymysql.connect(
	host='127.0.0.1',	#本地回环地址
	port=3306,	#mysql默认的端口号
	user='root',	#用户名
	password='666',	#密码
	database='db4_3',	#需要制定操作的库
	charset='utf8',	#制定代码
	autocommit=True	#针对增删改自动二次确认

4.知识点补充

sql='insert into userinfo(name,password) values(%s,%s)'
cursor.executemany(sql,[('jack','222'),('egon','333')])

sql='select * from userinfo'
cursor.execute(sql)
res=cursor.fetchall() 	#拿一次后，指针到最后
res=cursor.fetchone()
res=cursor.fetchmany(2)	可以指定数量
print(res)

5.作业

1.安装Navicat
2.完成课上五道多表查询练习题
3.结合pymysql 开发用户注册登录功能
ps：也可以再扩展一些额外功能
	e.g.创建数据库、表……
4.总结本周内容

5.MySQL练习题，尽可能多的完成，复杂的可以忽略
https://www.cnblogs.com/Dominic-Ji/p/10875493.html