必发88手机客户端 26

数据库之,外键的变种

 

运营结果截图:

mysql续,mysql生成延续日期

接上篇博客,写完之后看了看,照旧觉的写的太简单,固然是本身复习都不够,所以再补充部分

①.创办多表关联

需要:图书管理连串,创造几张表,包罗图书,出版社,小编,小编详细音讯等情节

分析:

  (一)图书唯有贰个出版社,出版社能够出版许多书,多对一涉及

  (贰)图书能够有多个我,一个小编也能够写多本书,多对多涉及

  (三)我的事无巨细消息表,与作者一对1关联

  (四)多对一提到,用外键;一对一事关,在外键上加唯1约束;多对多涉及,用第1张表存款和储蓄关系

  (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)必发88手机客户端 1新建四个sql文件,展开pycharm左侧的database

(2)必发88手机客户端 2

(3)必发88手机客户端 3

(4)必发88手机客户端 4

ok,那就足以了,阿妈再也不用顾忌小编遗忘大写了,效能也是晋级了不知多少倍

只是供给小心,那只可以操作2个数据库,想切换数据库的话,要双重走2回刚才的流水生产线,把想要操作的数据库增多进去就行了

叁.一同主键

上1篇博客写了设置主键能够用primary key,也足以用not null unique

此间要详细说一下,

  • 借使表中只有二个非空且唯一约束,自动就能够识别为主键
  • 非空且唯1约束并非只可以设置三次,不过首先个设置那个约束的被识别为主键
  • 每张表只可以有1个主键,可是主键并不一定在一个字段上,即共同主键

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

看一下表结构,

必发88手机客户端 5

看来没,三个主键了,那就叫联合主键。

运用的话,能够参照大家最发轫计划的可怜图书管理体系,在我们创造图书和作者提到的表的时候,图书id和我id理论上都应当是必须同时有值且无法重复,就足以把她们设置成联合主键

 四.积攒引擎

不知你有未有理会过,当大家用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中也叫做“键”,是累积引擎用于快捷找到记录的一种数据结构。当表中的数据量不小时,索引能随随意便的将查询品质升高很多少个数据级。就算在创建索引时很费时间,但是纵然成立好,就能够大大升高查询速度。

 创设索引的措施:

(壹)成立普通索引

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

(二)创设唯一索引

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

(三)成立全文索引

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

(四)创立多列索引

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 表名

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

接上篇博客,写完将来看了看,照旧觉的写的太轻松,就到底自身复习都不够,所以再补充部分
一.开立多表关联…

一、介绍

因为有foreign key的约束,使得两张表产生了二种了关联:

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

练习

二、注重理解假若寻觅两张表之间的涉及

必发88手机客户端 6

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

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

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

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

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

必发88手机客户端 7

 

运转结果截图:

本节首要:

何人关联哪个人就是什么人要遵守什么人的正规化。

  • 何以找寻两张表之间的关系
  • 表的三种关系

3、多对多(多条记下对应多条记下)

叁、表的两种关系

(1)书和出版社

  一对多(或多对一):四个出版社能够出版多本书。看图说话。

  关联方式:foreign key

 必发88手机客户端 8

 

必发88手机客户端 9必发88手机客户端 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)

书和出版社(多对一)

 

(二)我和本本的涉嫌

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

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

必发88手机客户端 11

 

 

必发88手机客户端 12必发88手机客户端 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

 

必发88手机客户端 14

 

 

必发88手机客户端 15必发88手机客户端 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;

用户和博客(一对一)

 

事例1:用户和大班(唯有管理员才足以登陆,1个大班对应3个用户)

 

必发88手机客户端 17

一、先创建涉及

二、一对一

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

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

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

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

要把book_id和author_id设置成联合唯壹

那是一个书和出版社的一个事例,书要提到出版社(五个书能够是贰个出版社,3个出版社也足以有很多书)。

书和笔者(大家能够更创制一张表,用来存book和author两张表的关联)

事例二:学生表和客户表

协会者关联用户

 多对多关系比如

 

必发88手机客户端 18

===========例子一:用户表和管理员表=========
先建被关联的表
create table user(
id int primary key auto_increment, #主键自增
name char(10)
);
在建关联表
create table admin(
id int primary key auto_increment,
user_id int unique,
password varchar(16),
foreign key(user_id) references user(id)
on delete cascade
on update cascade
);
insert into user(name) values('susan1'),
                             ('susan2'),
                             ('susan3'),
                             ('susan4'),
                             ('susan5'),
                             ('susan6');
insert into admin(user_id,password) values(4,'sds156'),
                                          (2,'531561'),
                                          (6,'f3swe');

必发88手机客户端 19

========书和作者,另外在建一张表来存书和作者的关系
#被关联的
create table book1(
id int primary key auto_increment,
name varchar(10),
price float(3,2)
);
#========被关联的
create table author(
id int primary key auto_increment,
name char(5)
);
#========关联的
create table author2book(
id int primary key auto_increment,
book_id int not null,
author_id int not null,
unique(book_id,author_id),
foreign key(book_id) references book1(id)
on delete cascade
on update cascade,
foreign key(author_id) references author(id)
on delete cascade
on update cascade
);
#========插入记录
insert into book1(name,price) values('九阳神功',9.9),
                                    ('葵花宝典',9.5),
                                    ('辟邪剑谱',5),
                                    ('降龙十巴掌',7.3);
insert into author(name) values('egon'),('e1'),('e2'),('e3'),('e4');
insert into author2book(book_id,author_id) values(1,1),
                                                 (1,4),
                                                 (2,1),
                                                 (2,5),
                                                 (3,2),
                                                 (3,3),
                                                 (3,4),
                                                 (4,5);

 

用户表,用户组,主机表

 

联合唯一:unique(book_id,author_id)

必发88手机客户端 20

多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多
  
关联方式:foreign key+一张新的表

 

必发88手机客户端,1、多对1要么1对多(左侧表的多条记下对应右侧表的唯1一条记下)

========例子二:学生表和客户表=========
create table customer(
id int primary key auto_increment,
name varchar(10),
qq int unique,
phone int unique
);
create table student1(
sid int primary key auto_increment,
course char(20),
class_time time,
cid int unique,
foreign key(cid) references customer(id)
on delete cascade
on update cascade
);
insert into customer(name,qq,phone) values('小小',13564521,11111111),
                                          ('嘻哈',14758254,22222222),
                                          ('王维',44545522,33333333),
                                          ('胡军',545875212,4444444),
                                          ('李希',145578543,5555555),
                                          ('李迪',754254653,8888888),
                                          ('艾哈',74545145,8712547),
                                          ('啧啧',11147752,7777777);
insert into student1(course,class_time,cid) values('python','08:30:00',3),
                                                 ('python','08:30:00',4),
                                                 ('linux','08:30:00',1),
                                                 ('linux','08:30:00',7);

必发88手机客户端 21

      二.在创设关联表,关联字段一定保障是要有再次的。

实际上壹篇博客已经举了一个多对壹关系的小例子了,那大家在用另一个小例子来回看一下。

必发88手机客户端 22

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

-- 建立user和host的关系
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);

insert into user2host(user_id,host_id) values(1,1),
                                                (1,2),
                                                (1,3),
                                                (1,4),
                                                (1,5),
                                                (1,6),
                                                (1,7),
                                                (1,8),
                                                (1,9),
                                                (1,10),
                                                (1,11),
                                                (1,12),
                                                (1,13),
                                                (1,14),
                                                (1,15),
                                                (1,16),
                                                (2,2),
                                                (2,3),
                                                (2,4),
                                                (2,5),
                                                (3,10),
                                                (3,11),
                                                (3,12);
-- 主机表
CREATE TABLE host(
id int primary key auto_increment,
ip CHAR(15) not NULL UNIQUE DEFAULT '127.0.0.1'
);
insert into host(ip) values('172.16.45.2'),
                             ('172.16.31.10'),
                             ('172.16.45.3'),
                             ('172.16.31.11'),
                             ('172.10.45.3'),
                             ('172.10.45.4'),
                             ('172.10.45.5'),
                             ('192.168.1.20'),
                             ('192.168.1.21'),
                             ('192.168.1.22'),
                             ('192.168.2.23'),
                             ('192.168.2.223'),
                             ('192.168.2.24'),
                             ('192.168.3.22'),
                             ('192.168.3.23'),
                             ('192.168.3.24');

-- 业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);
insert into business(business) values
                                        ('轻松贷'),
                                        ('随便花'),
                                        ('大富翁'),
                                        ('穷一生');

-- 建立host和business关系表
CREATE TABLE host2business(
id int not null unique auto_increment,
host_id int not null ,
business_id int not NULL ,
PRIMARY KEY(host_id,business_id),
foreign key(host_id) references host(id),
FOREIGN KEY(business_id) REFERENCES business(id)
);


insert into host2business(host_id,business_id) values
                                                        (1,1),
                                                        (1,2),
                                                        (1,3),
                                                        (2,2),
                                                        (2,3),
                                                        (3,4);

 必发88手机客户端 23

运作结果截图:

内需小心的:一.先建被波及的表,保险被关联表的字段必须唯壹。

必发88手机客户端 24必发88手机客户端 25

-- 用户组
create table user (
id int primary key auto_increment,
username varchar(20) not null,
password varchar(50) not null
);
insert into user(username,password) values('egon','123'),
                                          ('root',147),
                                          ('alex',123),
                                          ('haiyan',123),
                                          ('yan',123);

-- 用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20)  not null unique
);
insert into usergroup(groupname) values('IT'),
                                        ('Sale'),
                                        ('Finance'),
                                        ('boss');

-- 建立user和usergroup的关系表

create table user2usergroup(
id int not NULL UNIQUE auto_increment,
user_id int not null,
group_id int not NULL,
PRIMARY KEY(user_id,group_id),
foreign key(user_id) references user(id)
ON DELETE CASCADE
on UPDATE CASCADE ,
foreign key(group_id) references usergroup(id)
ON DELETE CASCADE
on UPDATE CASCADE
);

insert into user2usergroup(user_id,group_id) values(1,1),
                                                      (1,2),
                                                      (1,3),
                                                      (1,4),
                                                      (2,3),
                                                      (2,4),
                                                      (3,4);

 

 

一齐主键:alter table t1 add primary
 key(id,avg)

必发88手机客户端 26

书要关联出版社
被关联的表
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);