表关系（外键）
什么是外键？
外键就是通过一个字段可以查询到另一张表上的内容
为什么要有外键？
"""
1、表的数据不够清晰，分不清表的具体用处
2、字段需要重复的写太浪费资源
3、兼容性很差，牵一发而动全身
"""
使用外键如何解决?
把一张表拆成两张表，每个表上面是自己独有的数据
外键的约束
1、在创建表的时候应该先创建被关联的表（没有外键字段的表）
2、在录入数据的时候应该先录入被关联的表（没有外键字段的表）
3、在录入数据的时候，应该录入被关联表中已存在的值
4、如果对被关联的表进行修改和删除的时候，需要把关联的表中的数据也跟着修改或删除。
需要使用以下方式进行关联
先创建出版表
create table publish(
id int primary key auto_increment,
title varchar(128)
);
再创建图书表
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 # 级联更新（删除）
);
"""
但是，由于创建了外键关系和级联更新级联删除，那么，两张表之间就有了强制的约束关系，这样就增加了表与表之间的强耦合度
所以，以后实际项目中，我们大多数不建立这种强耦合关系，我们使用的是建立逻辑意义上的关系
"""
表关系
"""
一对多、多对多、一对一、没有关系
"""
# 一对多
以图书和出版社为例
先站在图书表的角度
问：
1. 一本图书能否有多个出版社出版?
答：不能
站在出版社的角度问：
1. 一个出版社能否出版多本图书?
答：能
得出结论：一个能，一个不能，那么，表关系就是'一对多'
"""一对多的关系外键一般建在多的一方"""
在数据库层面建立一对多的关系
# 多表的创建，先创建表的基本字段、再添加外键字段
1、先创建出版表
mysql> create table publish(id int primary key auto_increment,
title varchar(128));
Query OK, 0 rows affected (0.02 sec)
2、再创建图书表
mysql> 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是外键关系
-> );
Query OK, 0 rows affected (0.02 sec)
3、先在出版社中插入数据
mysql> insert into publish(title) values('北京出版社');
Query OK, 1 row affected (0.01 sec)
mysql> insert into publish(title) values('上海出版社');
Query OK, 1 row affected (0.00 sec)
4、再往图书中插入数据
mysql> insert into book(title,price,publish_id) values('西游记',1000,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into book(title,price,publish_id) values('名侦探柯南',1000,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into book(title,price,publish_id) values('地狱乐',1000,2);
Query OK, 1 row affected (0.00 sec)
多对多的关系
# 举例：我们以图书表和作者表为例
我们站在图书表的角度
问：
一本图书能否有多个作者来写?
答：能
再站在作者表的角度
问：
一个作者能否写多本书、
答：能
'''得出结论：此时表关系就是多对多'''
问题：外键字段建在哪里?
答案：多对多的外键字段需要建立第三张表来存储
"""在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)
foreign key(author_id) references author(id)
);
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语句的执行条件
# 大白话就是分步操作
eg:查看姓名为Jason的部门名称；
1、先查看部门id
select dep_id from emp where name = 'jason';
2、在查看那个部门的id为200：
select * from dep where id = 200;
3、将两者合并：
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
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+-------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
2、left join
mysql> select *from emp left join dep on emp.dep_id =dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
3、right join
mysql> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 保洁 |
+------+-------+--------+------+--------+-----+--------------+
6 rows in set (0.00 sec)
4、union
mysql> 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;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | 保洁 |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
5. 还可以给表名起别名
select * from emp as e inner join dep as d on e.dep_id=d.id;
pycharm操作数据库
# 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())
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、查询挂科超过两门(包括两门)的学生姓名和班级
'''可能有点难，自己做，能做几个做几个.'''