MySQL的表关系

多读书多看报少吃零食多睡觉.

已于 2023-07-14 08:37:26 修改

阅读量291

点赞数

文章标签： mysql 数据库

于 2023-07-14 08:26:36 首次发布

本文链接：https://blog.csdn.net/weixin_53667158/article/details/131705937

版权

外键（Foreign Key）是数据库中用于建立关系的一种约束条件。在关系数据库中，数据以表的形式组织，不同表之间存在关联关系。通过外键，可以将一个表中的字段与另一个表中的字段关联起来，从而实现数据的一致性和完整性。

外键通常由一个或多个字段组成，在包含外键的表中被称为“引用表”，在被引用的表中被称为“被引用表”。通过外键，可以定义两个表之间的关系，其中被引用表中的字段值必须在引用表的字段值中存在。这样的关系被称为“父表-子表”关系。

数据库表格之间的关系

一对一

两个表格之间是一对一的关系，即一个表示对另外一个表的扩充说明。

例如，一个人和他的身份证号码之间的关系。

一对多

一个表的记录可以对应另一个表中的多个记录，而另一个表的记录只能对应一个记录。

例如，一个班级和它的学生之间的关系。一个学生只能属于一个班级，故学生表中每个学生的记录只能对应一个唯一的班级id号，而一个班级中可以有多个学生，故班级表中存放班级id号与其对应的班级。

多对多

多个记录可以对应另一个表中的多个记录，并且另一个表中的多个记录也可以对应多个记录。为了表示这种关系，需要使用一个中间表来连接两个表。

例如，学生和课程之间的关系，一个学生可以选择多门课程，而一门课程也可以有多个学生选修。学生表中可以存放学生信息和学生id，课程表中存放课程信息和对应的课程编号，此时还需要第三个表来存放学生选课信息，即存放学生的id和课程编号。

多表查询

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);

1.子查询

即分步骤操作，上一步的结果，是下一步骤的搜索条件。

查看姓名为jason的部门名称:

1. 先查询部门id
select dep_id from emp where name='jason'; #得到id为200

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.连表查询

把多张实际的表按照表关系联系成一张临时在内存中存的虚拟表。

inner join 内连接，数据只取两张表中共有的数据

select * from emp inner join dep on emp.dep_id = dep.id

left join 左连接，以左边的表格为准，显示左边表的所有数据，右边表格没有的用NULL填充

select * from emp left join dep on emp.dep_id=dep.id;

right join 右连接，以右边的表格为准，显示右边表的所有数据，左边表格没有的用NULL填充

select * from emp right join dep on emp.dep_id=dep.id;

union

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;

还可以给表名起别名

select * from emp as e inner join dep as d on e.dep_id=d.id;

#数据

/*
 数据导入：
 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. 结合数据库实现注册和登录功能
建立一张用户表，username， password

import pymysql


def register():
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123',
        db='db4',
        charset='utf8',
        autocommit=True  # 加了此行代码后可以不用二次提交
    )
    # 获得游标
    q = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 用字典的形式返回
    name = input('请输入你的用户名：').strip()
    pwd = int(input('请输入你的密码：').strip())
    # connect_sql("insert into information(username, password) values (%s, %s) " % (name, pwd))
    print("注册成功")

    sql = "insert into information(username, password) values (%s, %s) "
    infer = (name, pwd)

    q.execute(sql, infer)

    # conn.commit()   #除了查询，增加、删除、修改数据时都需要二次提交


def login():
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123',
        db='db4',
        charset='utf8',
        autocommit=True  # 加了此行代码后可以不用二次提交
    )
    # 获得游标
    q = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 用字典的形式返回

    sql = "select username,password from information"
    q.execute(sql)
    list1 = q.fetchall()
    name = input('请输入你的用户名：').strip()
    pwd = int(input('请输入你的密码：').strip())
    login_success =False
    for i in list1:
        if i.get('username') == name and i.get('password') == pwd:
           login_success = True
           break
    if login_success:
        print('登录成功')
    else:
        print('登录失败，请重新登录')
        login()



login()

2. py操作mysql
3. 多表查询练习题
1、查询所有的课程的名称以及对应的任课老师姓名

select distinct cname,tname from course left join teacher on course.teacher_id =teacher.tid ;

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

select sname,avg(num) from score left join student on score.student_id =student.sid group by score.student_id ;

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

select distinct cid, cname,tname from course left join teacher on course.teacher_id =teacher.tid where tname='李平老师' ;

然后再：

SELECT sname FROM student WHERE sid NOT IN(
SELECT student_id FROM score WHERE course_id in (2,4));

总：

SELECT sname FROM student WHERE sid NOT IN
-- 查询参加了李平老师教授课程的学生id
(SELECT student_id FROM score WHERE course_id IN
-- 查询由李平老师授课的课程id
(SELECT cid FROM course WHERE teacher_id IN
-- 查询教师名字为“李平老师”的教师id
(SELECT tid FROM teacher WHERE tname ='李平老师')));

select distinct student_id from score left join student on score.course_id i not in(2,4);
4、查询挂科超过两门(包括两门)的学生姓名和班级

select sname as '学生姓名',caption as '班级' from student inner join class on
student.class_id=class.cid
where sid in (select student_id from score where num<60 group by student_id having count(num<60) >=2);

'''可能有点难，自己做，能做几个做几个.'''

多读书多看报少吃零食多睡觉.

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
MySQL的表关系

外键（Foreign Key）是数据库中用于建立关系的一种约束条件。在关系数据库中，数据以表的形式组织，不同表之间存在关联关系。通过外键，可以将一个表中的字段与另一个表中的字段关联起来，从而实现数据的一致性和完整性。外键通常由一个或多个字段组成，在包含外键的表中被称为“引用表”，在被引用的表中被称为“被引用表”。通过外键，可以定义两个表之间的关系，其中被引用表中的字段值必须在引用表的字段值中存在。这样的关系被称为“父表-子表”关系。
复制链接

扫一扫