本节重点:

表1 foreign key 表2
则表1的多条记录对应表2的一条记录,即多对一

利用foreign key的原理我们可以制作两张表的多对多,一对一关系
多对多:
    表1的多条记录可以对应表2的一条记录
    表2的多条记录也可以对应表1的一条记录

一对一:
    表1的一条记录唯一对应表2的一条记录,反之亦然

分析时,我们先从按照上面的基本原理去套,然后再翻译成真实的意义,就很好理解了

mysql续,mysql生成连续日期

接上篇博客,写完以后看了看,还是觉的写的太简单,就算是自己复习都不够,所以再补充一些

1.创建多表关联

需求:图书管理系统,创建几张表,包含书籍,出版社,作者,作者详细信息等内容

分析:

  (1)图书只有一个出版社,出版社可以出版很多书,多对一关系

  (2)图书可以有多个作者,一个作者也可以写多本书,多对多关系

  (3)作者的详细信息表,与作者一对一关系

  (4)多对一关系,用外键;一对一关系,在外键上加唯一约束;多对多关系,用第三张表存储关系

  (5)需要建立五张表,书籍,出版社,作者,作者详细信息,图书与作者关系表

代码贴到这,用pycharm书写,推荐mysql命令大写

 1 create database book_manage_system character set utf8;
 2 
 3 
 4 
 5 --创建出版社表,字段有id,name,city
 6 create table publish(id int primary key auto_increment,
 7                         name varchar(20),
 8                         city varchar(20));
 9 
10 
11 --创建书籍表,书籍表是出版社表的子表,字段有id,name,price,publish_id
12 create table book (id int primary key auto_increment,
13                     name varchar(20),
14                     price double(6,2),
15                     publish_id int,
16                     foreign key (publish_id) references publish(id)
17                     --增加外键,与出版社表和作者表建立关系
18                     );
19 
20 
21 --创建作者详细信息表,字段有ID,name,age,city,phone_num,email
22 create table auth_detail (id int primary key auto_increment,
23                             name varchar(20),
24                             age int,
25                             city varchar(20),
26                             phone_num varchar(11),
27                             email varchar(30));
28 
29 --创建作者表,为作者详细信息表的子表,字段有ID,name,auth_detail_id
30 create table author(id int primary key auto_increment,
31                         name varchar(20),
32                         auth_detail_id int unique,
33                         foreign key (auth_detail_id) references auth_detail(id)
34                         --增加外键,与作者详细信息表建立联系,一对一关系,唯一性约束
35                         );
36 
37 
38 
39 --创建作者与书籍的关系表,字段有auth_id,book_id
40 create table book_to_auth(id int primary key auto_increment,
41                            auth_id int,
42                            foreign key (auth_id) references author(id),
43                             book_id int,
44                              foreign key (book_id) references book(id));
45                              --创建外键,作者信息与书籍信息联系

2.用pycharm来编写mysql语句

虽然mysql的命令我们都应该熟记于心,但是当我们已经记住后,从开发效率方面考虑,我们就可以使用一些文本编辑器来帮助我们写这些语句了,下面说一下配置pycharm的流程

(1)图片 1新建一个sql文件,打开pycharm右边的database

(2)图片 2

(3)图片 3

(4)图片 4

ok,这就可以了,妈妈再也不用担心我忘记大写了,效率也是提升了不知多少倍

但是需要注意,这只能操作一个数据库,想切换数据库的话,要重新走一遍刚才的流程,把想要操作的数据库添加进来就行了

3.联合主键

上一篇博客写了设置主键可以用primary key,也可以用not null unique

这里要详细说一下,

  • 如果表中只有一个非空且唯一约束,自动就会识别为主键
  • 非空且唯一约束并非只能设置一次,但是第一个设置这个约束的被识别为主键
  • 每张表只能有一个主键,但是主键并不一定在一个字段上,即联合主键

CREATE TABLE union_primary_key (id int,
                                name VARCHAR(20),
                                age int,
                                PRIMARY KEY (id,name))

看一下表结构,

图片 5

看到没,两个主键了,这就叫联合主键。

应用的话,可以参照我们最开始设计的那个图书管理系统,在我们创建图书和作者关系的表的时候,图书id和作者id理论上都应该是必须同时有值且不能重复,就可以把他俩设置成联合主键

 4.存储引擎

不知你有没有注意过,当我们用create命令查看表的创建信息时,会得到类似这样一些数据

| book  | CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `price` double(6,2) DEFAULT NULL,
  `publish_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `publish_id` (`publish_id`),
  CONSTRAINT `book_ibfk_1` FOREIGN KEY (`publish_id`) REFERENCES `publish` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

最下边有一个  ENGINE=InnoDB

这就是存储引擎,存储引擎的作用是存储数据,为数据建立索引,查询数据

在MySQL中提供了多种存储引擎,有innodb,memory,blackhole等

innodb是我们最常用的,memory是基于内存的存储机制,不会长久保存,而blackhole叫黑洞引擎,所有丢到里面的数据都会消失,我至今也没理解是干什么用的,据说是为了测试。而我们最关心
的应该是innodb。外键这个东西就是innodb独有的。

我们可以通过命令SHOW
ENGINES查看引擎(tips:在命令提示行中是显示不全的,可以在命令后面加一个G,就可以正常显示了)

以innodb
创建一个表之后,就会有分别以opt,frm,ibd为后缀的三个文件创建,不同的引擎,创建的文件也不一样

5.索引(index或key)

索引在mysql中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。当表中的数据量很大时,索引能轻易的将查询性能提高好几个数量级。虽然在创建索引时很费时间,但是一旦创建好,就能大大提高查询速度。

 创建索引的方式:

(1)创建普通索引

CREATE TABLE student(
          id INT,
          name VARCHAR(20),
          INDEX index_name(name)
);

(2)创建唯一索引

CREATE TABLE student(
          id INT,
          name VARCHAR(20),
          UNIQUE INDEX index_name(name)
);

(3)创建全文索引

CREATE TABLE student(
          id INT,
          name VARCHAR(20),
          FULLTEXT INDEX index_name(name)
);

(4)创建多列索引

CREATE TABLE student(
          id INT,
          name VARCHAR(20),
          INDEX index_name(name,id)
);

(5)在已存在的表上创建索引

# 1.create方法
CREATE INDEX 索引名
    ON 表名 (字段名 )

# 2.alter方法
ALTER TABLE 表名 ADD INDEX
    索引名 (字段名)

删除索引:

DROP INDEX 索引名 on 表名

6.还有pymsql和orm,下次写数据库的时候一块说

接上篇博客,写完以后看了看,还是觉的写的太简单,就算是自己复习都不够,所以再补充一些
1.创建多表关联…

  • 如何找出两张表之间的关系
  • 表的三种关系

1、先确立关系

 

2、找到多的一方,吧关联字段写在多的一方

一、介绍

因为有foreign key的约束,使得两张表形成了三种了关系:

  • 多对一
  • 多对多
  • 一对一

一、多对一或者一对多(左边表的多条记录对应右边表的唯一一条记录)

二、重点理解如果找出两张表之间的关系

图片 6

分析步骤:
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)

#3、总结:
#多对一:
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表

#多对多
如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

图片 7

 

需要注意的:1.先建被关联的表,保证被关联表的字段必须唯一。

三、表的三种关系

(1)书和出版社

  一对多(或多对一):一个出版社可以出版多本书。看图说话。

  关联方式:foreign key

 图片 8

 

图片 9图片 10

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

create table book(
    id int primary key auto_increment,
    name varchar(20),
    press_id int not null,
         constraint fk_book_press foreign key(press_id) references press(id)
    on delete cascade
    on update cascade
);

# 先往被关联表中插入记录
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;

# 再往关联表中插入记录
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;

查询结果:
mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+
6 rows in set (0.00 sec)

mysql> select * from press;
+----+--------------------------------+
| id | name                           |
+----+--------------------------------+
|  1 | 北京工业地雷出版社             |
|  2 | 人民音乐不好听出版社           |
|  3 | 知识产权没有用出版社           |
+----+--------------------------------+
3 rows in set (0.00 sec)

书和出版社(多对一)

 

(2)作者和书籍的关系

多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多。看图说话。

关联方式:foreign key+一张新的表

图片 11

 

 

图片 12图片 13

# 创建被关联表author表,之前的book表在讲多对一的关系已创建
create table author(
    id int primary key auto_increment,
    name varchar(20)
);
#这张表就存放了author表和book表的关系,即查询二者的关系查这表就可以了
create table author2book(
    id int not null unique auto_increment,
    author_id int not null,
    book_id int not null,
    constraint fk_author foreign key(author_id) references author(id)
    on delete cascade
    on update cascade,
    constraint fk_book foreign key(book_id) references book(id)
    on delete cascade
    on update cascade,
    primary key(author_id,book_id)
);
#插入四个作者,id依次排开
insert into author(name) values('egon'),('alex'),('wusir'),('yuanhao');

# 每个作者的代表作
egon: 九阳神功、九阴真经、九阴白骨爪、独孤九剑、降龙十巴掌、葵花宝典
alex: 九阳神功、葵花宝典
wusir:独孤九剑、降龙十巴掌、葵花宝典
yuanhao:九阳神功

# 在author2book表中插入相应的数据

insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
# 现在就可以查author2book对应的作者和书的关系了
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         1 |       4 |
|  5 |         1 |       5 |
|  6 |         1 |       6 |
|  7 |         2 |       1 |
|  8 |         2 |       6 |
|  9 |         3 |       4 |
| 10 |         3 |       5 |
| 11 |         3 |       6 |
| 12 |         4 |       1 |
+----+-----------+---------+
12 rows in set (0.00 sec)

作者与书籍关系(多对多)

 

 

(3)用户和博客

  一对一:一个用户只能注册一个博客,即一对一的关系。看图说话

关联方式:foreign key+unique

 

图片 14

 

 

图片 15图片 16

#例如: 一个用户只能注册一个博客

#两张表: 用户表 (user)和 博客表(blog)
# 创建用户表
create table user(
    id int primary key auto_increment,
    name varchar(20)
);
# 创建博客表
create table blog(
    id int primary key auto_increment,
    url varchar(100),
    user_id int unique,
    constraint fk_user foreign key(user_id) references user(id)
    on delete cascade
    on update cascade
);
#插入用户表中的记录
insert into user(name) values
('alex'),
('wusir'),
('egon'),
('xiaoma')
;
# 插入博客表的记录
insert into blog(url,user_id) values
('http://www.cnblog/alex',1),
('http://www.cnblog/wusir',2),
('http://www.cnblog/egon',3),
('http://www.cnblog/xiaoma',4)
;
# 查询wusir的博客地址
select url from blog where user_id=2;

用户和博客(一对一)

 

      2.在创建关联表,关联字段一定保证是要有重复的。

其实上一篇博客已经举了一个多对一关系的小例子了,那我们在用另一个小例子来回顾一下。

这是一个书和出版社的一个例子,书要关联出版社(多个书可以是一个出版社,一个出版社也可以有好多书)。

谁关联谁就是谁要按照谁的标准。

图片 17

书要关联出版社
被关联的表
create  table press(
id int primary key auto_increment,
name char(20)
);
关联的表
create table book(
book_id int primary key auto_increment,
book_name varchar(20),
book_price int,
press_id int,
constraint Fk_pressid_id foreign key(press_id) references press(id)
on delete cascade
on update cascade
);

插记录
insert into press(name) values('新华出版社'),
                              ('海燕出版社'),
                              ('摆渡出版社'),
                              ('大众出版社');
insert into book(book_name,book_price,press_id) values('Python爬虫',100,1),
                                                       ('Linux',80,1),
                                                       ('操作系统',70,2),
                                                       ('数学',50,2),
                                                       ('英语',103,3),
                                                       ('网页设计',22,3);

运行结果截图:

图片 18

二、一对一

例子一:用户和管理员(只有管理员才可以登录,一个管理员对应一个用户)

管理员关联用户

发表评论

电子邮件地址不会被公开。 必填项已用*标注