mysql数据库3 表关系/多表查询等

表关系(外键)

# 外键的前戏

建立一张表：emp
"""
    1. 表不清晰，现在到底是员工表还是部门表
    2. 字段需要重复的写，浪费资源
    3. 兼容性很差，牵一发而动全身(这个问题是最不能容忍的)
"""

# 以上问题该如何解决呢?
我们的思路是，把一张表拆分成两张表
拆成emp和depart部门表

# 表拆分之后，最大的问题就是，两张表没有了任何的关系

# 外键：其实就是通过字段可以查询到另外一张表的数据

表关系

一对多
一对一
多对多
没有关系


"""如何判断表关系：换位思考法"""


# 一对多
以图书表和出版社表为例

先站在图书表的角度
    问：
        1. 一本图书能否有多个出版社出版?
            答：不能
 站在出版社的角度问：
        1. 一个出版社能否出版多本图书?
            答：能
          得出结论：一个能，一个不能，那么，表关系就是'一对多'
        
   """一对多的表关系外键字段建在多的一方"""

# 在SQL层面建立一对多的关系
# 多表的创建，先创建表的基本字段， 在添加外键字段
先创建图书表
create table book(
    id int primary key auto_increment,
    title varchar(128),
    price decimal(8, 2),
    publish_id int,
    foreign key(publish_id) references publish(id)  # 创建外键 意思是：book表中的publish_id和publish表中的id是外键关系
);


在创建出版表
create table publish(
    id int primary key auto_increment,
    title varchar(128)
);

# 要先创建出版表，再创建图书表，因为图书表中book表中的id 是外键

# 往表中录入数据
往book表中录入数据
insert into book (title, price, publish_id) values('金梅', 1000, 1);
insert into book (title, price, publish_id) values('西游记', 1000, 2);

往出版社表中录入
insert into publish (title) values ('北京出版社');
insert into publish (title) values ('东京出版社');

'上面是一个有表联关系的两张表'
1. 在创建表的时候，应该先创建被关联表(没有外键字段的表)
2. 在录入数据的时候，应该先录入被关联表(没有外键字段的表)
3. 在录入数据的时候，应该录入被关联表中已经存在的值.
4. 如果对被关联表中的数据进行修改和删除的时候，需要把关联表中的数据也跟着修改或者删除(不现实)

外键约束

1. 在创建表的时候，应该先创建被关联表(没有外键字段的表)
2. 在录入数据的时候，应该先录入被关联表(没有外键字段的表)
3. 在录入数据的时候，应该录入被关联表中已经存在的值.
4. 如果对被关联表中的数据进行修改和删除的时候，需要把关联表中的数据也跟着修改或者删除(不现实)
需要使用以下方式创建表关系
先创建图书表
create table book(
    id int primary key auto_increment,
    title varchar(128),
    price decimal(8, 2),
    publish_id int,
    foreign key(publish_id) references publish(id) # 意思是：book表中的publish_id和publish表中的id是外键关系
    on update cascade  # 级联更新
    on delete cascade   # 
);


在创建出版表
create table publish(
    id int primary key auto_increment,
    title varchar(128)
);

"""
但是，由于创建了外键关系，那么，两张表之间就有了强制的约束关系，这样就增加了表与表之间的强耦合度

所以，以后实际项目中，我们大多数不建立这种强耦合关系，我们使用的是建立逻辑意义上的关系
"""

多对多的表关系

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

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

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


create table author(
    id int primary key auto_increment,
    name varchar(32)
);


# 建立第三张表来保存两张表的关系
create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    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);
# 插入消息

一对一的表关系

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

问题：'外键字段'建在哪里?
答案：一对一的外键字段可以建在任何一张表中，但是，推荐建在查询频率较高的一张表中

"""在SQL层面建立多对多的关系"""
create table author(
    id int primary key auto_increment,
    name varchar(32),
    author_detail_id int unique,
    foreign key (author_detail_id) references author_detail(id)
);

create table author_detail(
    id int primary key auto_increment,
    addr varchar(32),
    height decimal(5,2)
);

数据准备

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. 子查询
    # 一条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;
    # 左连接，数据以左表为准，展示左表所有的数据，右表没有的数据使用NULL填充
    
    3. right join
    select * from emp right join dep on emp.dep_id=dep.id;
    # 右连接，数据以右表为准，展示右表所有的数据，左表没有的数据使用NULL填充
    
    4. 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;
    # 连接多条SQL语句执行的结果
    
    5. 还可以给表名起别名
    select * from emp as e inner join dep as d on e.dep_id=d.id;
    

Navicat客户化工具

# 是一个工具，需要下载使用

 免费试用14天
    
去官网下载:https://www.navicat.com.cn/download/navicat-premium

    
create table t1 (
id int primary key auto_increment comment '主键id',
);

navicat 能够充当多个客户端的数据库

navcat 图形化界面有时反应较慢，可以选择刷新或者关闭当前窗口重试

# 转存文件  将mysql语句复制下来直接运行一次，

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

练习题的SQL文件 

/*
 数据导入：
 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、查询所有的课程的名称以及对应的任课老师姓名
   

    2、查询平均成绩大于八十分的同学的姓名和平均成绩
 
    
    3、查询没有报李平老师课的学生姓名

    4、查询挂科超过两门(包括两门)的学生姓名和班级