4-mysql-表关系(一对多、一对一、多对多)、多表查询相关(核心)、navicat可视化工具、python操作MySQL

林光虚霁晓

已于 2023-11-08 19:42:04 修改

阅读量1.2k

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 navicate 一对一一对多多对多多表查询

于 2023-07-13 17:45:38 首次发布

本文链接：https://blog.csdn.net/weixin_44145338/article/details/131704814

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、表关系

外键的前戏

建立一张表：
	CREATE TABLE `emp` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(20) NOT NULL,
	  `sex` enum('male','female') NOT NULL DEFAULT 'male',
	  `age` smallint(3) unsigned NOT NULL DEFAULT '28',
	  `hire_date` date NOT NULL,
	  `post` varchar(50) DEFAULT NULL,
	  `post_comment` varchar(100) DEFAULT NULL,
	  `salary` double(15,2) DEFAULT NULL,
	  `office` int(11) DEFAULT NULL,
	  `depart_id` int(11) DEFAULT NULL,
	  PRIMARY KEY (`id`)
	) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
	
	1.表不清晰，现在到底是员工还是部门表
	2.字段需要重复到的写，浪费资源
	3.兼容性差，牵一发而动全身(这个问题是最不能容忍的)


那么，以上问题我们该如何解决？
	思路：把一张表拆分成两张表
	表拆分之后，最大的问题就是，两张表没有任何关系
	外键：其实就是通过字段查询到另一张表的数据

1.一对多

以图书表和出版社为例：
	1.先站在图书表的角度
	问：
		一本图书能否有多个出版社？
		答：不能
	2.粘在出版社的角度：
	问：一个出版社能否有多本图书？
		答：能

	注意：
		得出结论：一个能，一个不能，那么，表关系就是'一对多'
注意：
	一对多的表关系外键字段建在多的一方

在SQL层面建立一对多的关系：
	多表的创建，先创建表的基本字段，再添加外键字段

1，被关联表
create table publish(
	id int primary key auto_increment comment '出版社的编号',
	title varchar(128) comment '出版社名称'
);
2.外键表
create table book(
	id int primary key auto_increment comment '图书编号',
	name varchar(128) comment '图书名称',
	price decimal(8, 2) comment '图书价格',
	publish_id int comment '出版社的编号',
	foreign key(publish_id) references publish(id)
)comment 'publish_id是book表的外键';

往表中录入数据：
被关联表：
insert into publish(title) values('北京出版社');
insert into publish(title) values('上海出版社');

insert into book(name,price,publish_id) values('古惑仔',1800,1);
insert into book(name,price,publish_id) values('刀郎', 1200, 2);



注意：
	外键约束：
		1.在创建表的时候，应该先创建被关联表(没有外键字段的表)
		2.在录入数据的时候，应该先录入被关联表(没有外键字段的表)
		3.在录入数据点的时候，应该录入被关联表中已经存在的值
		4.如果对被关联表中的数据进行修改和删除的时候，
			需要把关联表中的数据也跟着修改或删除(不现实)
		则，需要用以下方式创建表关系
create table publish(
	id int primary key auto_increment comment '出版社的编号',
	title varchar(128) comment '出版社名称'
);

create table book(
	id int primary key auto_increment comment '图书编号',
	name varchar(128) comment '图书名称',
	price decimal(8, 2) comment '图书价格',
	publish_id int comment '出版社的编号',
	foreign key(publish_id) references publish(id)
	# 意思是：book表中的publish_id和publish表中的id是外键关系 
	on update cascade  # 级联更新
	on delete cascade  # 级联删除
)comment 'publish_id是book表的外键';

-- 由于创建了外键关系和级联更新级联删除，那么，两张表之间就有了强制的约束关系，
这就增加了表与表之间的耦合度，所以，在实际的大多数项目中，我们使用的是建立逻辑意义上的关系

2.一对一

我们以作者表和作者详情表为例:
1.站在作者表的角度
	问：
    	一个作者能否有多个作者详情信息?
     答：不能
2.再站在作者详情表的角度
	问：
    	一个作者详细信息能否对应多个作者、
     答：不能
 '''得出结论：两个都不能，表关系就是一对一'''

问题：外键字段建在哪里?
答案：一对一的外键字段可以建在任何一张表中，但是，推荐建在查询频率较高的一张表中
"""在SQL层面建立一对一的关系"""
create table author_detail(
	id int primary key auto_increment comment '作者详情信息的id',
	addr varchar(32) comment '详细地址',
	height decimal(5, 2) comment '身高(米)'
);

create table author(
	id int primary key auto_increment comment '作者的id',
	name varchar(32) comment '作者名',
	author_detail_id int unique comment '作者的详细信息是唯一的',
	foreign key(author_detail_id) references author_detail(id)
) comment '作者详情id是作者表的外键';

3.多对多

举例：我们以图书表和作者表为例

我们站在图书表的角度
	问：
    	一本图书能否有多个作者来写?
     答：能
再站在作者表的角度
	问：
    	一个作者能否写多本书、
     答：能
 '''得出结论：此时表关系就是多对多'''

问题：外键字段建在哪里?
答案：多对多的外键字段需要建立第三张表来存储
"""在SQL层面建立多对多的关系"""
先创建图书表
create table book(
	id int primary key auto_increment comment '书名id',
	title varchar(128) comment '图书名称',
	price decimal(8, 2) comment '图书价格'
);

create table author(
	id int primary key auto_increment comment '作者id',
	name varchar(32) comment '作者名'
);

# 建立第三张表来保存两张表的关系
create table book2autor(
	id int primary key auto_increment comment '联表的id',
	book_id int comment '图书的id',
	author_id int comment '作者的id',
	foreign key(book_id) references book(id)
	on update cascade
	on delete cascade,
	foreign key(author_id) references author(id)
	on update cascade
	on delete cascade
);


insert into book2author(book_id, author_id) values(1,1),(1,2),(2,1);

二、多表查询相关(核心)

1.数据准备

create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

2.多表查询

多表的意思就是多张表连在一起使用

多表查询的思路：
1. 子查询
	一条SQL语句的执行结果当成另外一条SQL语句的执行条件
    大白话：分步操作

问题：查看姓名为jason的部门名称:
    1. 先查询部门id
    select dep_id from emp where name='jason';
    2. 拿着部门id作为条件，在去部门表中查询部门名称
    select * from dep where id=200;
    3. 把上述两条SQL语句合并为一条SQL语句
    select * from dep where id=(select dep_id from emp where name='jason');

2.连表查询
把多张实际存在的表按照表关系连成一张虚拟表(不是实际存在的表，而是临时在内存中存的)
    select * from emp,dep where emp.dep_id = dep.id;
    
    我们连表的时候有专业的连表语法
    inner join  内连接，数据只取两张表中共有的数据
    left join	 左连接，数据以左表为准，展示左表所有的数据，右表没有的数据使用NULL填充
    right join  右连接，数据以右表为准，展示右表所有的数据，左表没有的数据使用NULL填充
    union		连接多条SQL语句执行的结果
    
    1. inner join  内连接
    select * from emp inner join dep on emp.dep_id=dep.id;
    
    2. left join 左连接
    select * from emp left join dep on emp.dep_id=dep.id;
    
    3. right join 右连接
    select * from emp right join dep on emp.dep_id=dep.id;
    
    4. union  连接多条SQL语句执行的结果
    select * from emp left join dep on emp.dep_id=dep.id
    union
    select * from emp right join dep on emp.dep_id=dep.id;
    
    5. 还可以给表名起别名
    select * from emp as e inner join dep as d on e.dep_id=d.id;

三、可视化工具

navicate破解：https://www.cnblogs.com/pengpengdeyuan/p/16853594.html

四、python操作MySQL

Python操作MySQL，对于Python这门语言来说，就是客户端
使用Python操作mysql的时候，也要保证MySQL的服务端正常启动

如何操作MySQL呢
需要借助于第三方模块
1. pymysql
2. mysqlclient----->非常好用，一般情况下很难安装成功
3. mysqldb

pip install pymysql;
import pymysql

1. 连接MySQL的服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='root',
    db='db8',
    charset='utf8',
    autocommit=True
)

2. 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

3. 执行SQL语句了
# sql = 'select *from emp;'
sql = "insert into emp (name, sex, age, dep_id, gender) values('aa', 'male', 10, 1, 0)"

4. 开始执行
affect_rows = cursor.execute(sql) # 影响的行数
print(affect_rows) # 6行

'''增加,修改，删除的数据的时候，需要二次提交， 只有查询的时候不需要二次提交'''
# conn.commit()
# 5. 如何拿到具体的数据
# print(cursor.fetchall())

# for i in cursor.fetchall():
#     pass

# {'id': 1, 'name': 'jason', 'sex': 'male', 'age': 18, 'dep_id': 200, 'gender': 0}
# print(cursor.fetchone())  # None
#
# print(cursor.fetchmany(3))

作业

/*
 数据导入：
 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;

---------------------------------------------------------------------------------------------------------

SELECT * FROM class;  # cid
SELECT * FROM course;  # cid teacher_id
SELECT * FROM score; # sid  student_id course_id
SELECT * FROM student; # sid class_id
SELECT * FROM teacher; # tid


-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT * FROM course c,teacher t WHERE c.cid=t.tid;

-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 根据学生id统计出平均分
SELECT student_id, avg(num) AS avg_score FROM score GROUP BY student_id HAVING avg( num ) > 80;

SELECT sname '学生姓名',sc.avg_score '平均分'
FROM student
INNER JOIN ( 
		SELECT student_id, avg(num) AS avg_score
		FROM score 
		GROUP BY student_id 
		HAVING avg( num ) > 80 
) AS sc ON sc.student_id = student.sid;

-- 3、查询没有报李平老师课的学生姓名
-- SELECT * from teacher, course, student WHERE tname='李杰老师';
SELECT c.cid FROM course c INNER JOIN teacher ON c.teacher_id = teacher.tid WHERE teacher.tname = "李杰老师";

SELECT sname '学生姓名'
FROM student 
WHERE sid NOT IN (
	SELECT score.student_id 
	FROM score 
	WHERE course_id IN (
		SELECT c.cid 
		FROM course c 
		INNER JOIN teacher ON c.teacher_id = teacher.tid 
		WHERE teacher.tname = "李杰老师";
	) 
);


-- 4、查询挂科超过两门(包括两门)的学生姓名和班级
-- 4.1先查出学生的id人数
SELECT student_id FROM score WHERE num < 60;
-- 4.2根据学生的id分组并且统计人数
SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count(student_id) >= 2;
-- 4.3查询学生的姓名和班级
SELECT sname '学生姓名', caption '班级'
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( student_id ) >= 2 
		) 
) stu ON stu.class_id = class.cid;

林光虚霁晓

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
4-mysql-表关系(一对多、一对一、多对多)、多表查询相关(核心)、navicat可视化工具、python操作MySQL

【代码】表关系(一对多、一对一、多对多)、多表查询相关(核心)、navicat可视化工具、python操作MySQL。
复制链接

扫一扫