xml地图|网站地图|网站标签 [设为首页] [加入收藏]

表与表之间的关系,mysql生成连续日期

2019-11-29 16:45栏目:联系我们
TAG:

本节重点:

表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.创建多表关联...

  • 一 、介绍
  • 二 、not null与default
  • 三 、unique
  • 四 、primary key
  • 五 、auto_increment
  • 六 、foreign key
  • 如何找出两张表之间的关系
  • 表的三种关系

1、先确立关系

一 、介绍

约束条件与数据类型的宽度一样,都是可选参数

作用:用于保证数据的完整性和一致性
主要分为:

图片 6图片 7

PRIMARY KEY (PK)    标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

详细点击

说明:

图片 8图片 9

1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值(无符号) 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

具体说明

 

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

二 、not null与default

是否可空,null表示空,非字符串
not null - 不可空
null - 可空

默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)

图片 10图片 11

==================not null====================
mysql> create table t1(id int); #id字段默认可以插入空
mysql> desc t1;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | YES  |     | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
mysql> insert into t1 values(); #可以插入空


mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | NO   |     | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value



==================default====================
#设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;

具体实例

一、介绍

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

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

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

三 、unique

图片 12图片 13

============设置唯一约束 UNIQUE===============
方法一:
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

具体设置

图片 14图片 15

mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | NO   | PRI | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.00 sec)

not null unique

图片 16图片 17

create table service(
id int primary key auto_increment,
name char(20),
host char(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

联合唯一

 

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

图片 18

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

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

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

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

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

图片 19

 

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

四 、primary key

从约束角度看primary key字段的值不为空且唯一,那我们直接使用not null unique不就可以了吗,要它干什么?

主键primary key是innodb存储引擎组织数据的依据,innodb称之为索引组织表,一张表中必须有且只有一个主键。

一个表中可以:

单列做主键
多列做主键(复合主键)

图片 20图片 21

============单列做主键===============
#方法一:not null unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
 --------- -------------- ------ ----- --------- ------- 
| Field   | Type         | Null | Key | Default | Extra |
 --------- -------------- ------ ----- --------- ------- 
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
 --------- -------------- ------ ----- --------- ------- 
rows in set (0.01 sec)

#方法二:在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
 --------- -------------- ------ ----- --------- ------- 
| Field   | Type         | Null | Key | Default | Extra |
 --------- -------------- ------ ----- --------- ------- 
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
 --------- -------------- ------ ----- --------- ------- 
rows in set (0.00 sec)

#方法三:在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
 --------- -------------- ------ ----- --------- ------- 
| Field   | Type         | Null | Key | Default | Extra |
 --------- -------------- ------ ----- --------- ------- 
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
 --------- -------------- ------ ----- --------- ------- 
rows in set (0.01 sec)

主键的几种方式

PS:有的童靴可能会问,我刚开始创建表的时候,也没指定主键,在Mysql系统中依靠主键为组织数据的依据,Mysql系统会自动绑定的合适n的对象,

还有一种情况,没有合适的对象,就隐藏在系统中,是看不到的。总之一张表必须有且只有一个主键

图片 22图片 23

==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
 -------------- ------------- ------ ----- --------- ------- 
| Field        | Type        | Null | Key | Default | Extra |
 -------------- ------------- ------ ----- --------- ------- 
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
 -------------- ------------- ------ ----- --------- ------- 
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

复合主键

 

三、表的三种关系

(1)书和出版社

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

  关联方式:foreign key

 图片 24

 

图片 25图片 26

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 一张新的表

图片 27

 

 

图片 28图片 29

# 创建被关联表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

 

图片 30

 

 

图片 31图片 32

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

#两张表: 用户表 (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.在创建关联表,关联字段一定保证是要有重复的。

五 、auto_increment

约束字段为自动增长,被约束的字段必须同时被key约束

图片 33图片 34

#不指定id,则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
 ------- ----------------------- ------ ----- --------- ---------------- 
| Field | Type                  | Null | Key | Default | Extra          |
 ------- ----------------------- ------ ----- --------- ---------------- 
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
 ------- ----------------------- ------ ----- --------- ---------------- 
mysql> insert into student(name) values
    -> ('qian'),
    -> ('best')
    -> ;

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  1 | qian | male |
|  2 | best | male |
 ---- ------ ------ 


#也可以指定id
mysql> insert into student values(4,'duo','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'liang','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
 ---- ------ -------- 
| id | name | sex    |
 ---- ------ -------- 
|  1 | qian | male   |
|  2 | best | male   |
|  4 | duo  | female |
|  7 | liang  | female |
 ---- ------ -------- 


#对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ze');
mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  8 | ze  | male |
 ---- ------ ------ 

#应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('many');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  1 | many | male |
 ---- ------ ------ 
1 row in set (0.00 sec)

使用和删除的特殊性

了解知识

图片 35图片 36

#在创建完表后,修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

mysql> insert into student(name) values('qian');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  3 | qian | male |
 ---- ------ ------ 
row in set (0.00 sec)

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


#也可以创建表时指定auto_increment的初始值,注意初始值的设置为表选项,应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver:自增步长
    基于表级别
    create table t1(
        id int。。。
    )engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长:
    show session variables like 'auto_inc%';

    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长(所有会话都生效)


#!!!注意了注意了注意了!!!
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译:如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略 ,这相当于第一步步子就迈大了,扯着了蛋
比如:设置auto_increment_offset=3,auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
 -------------------------- ------- 
| Variable_name            | Value |
 -------------------------- ------- 
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
 -------------------------- ------- 



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('qian1'),('qian2'),('qian3');
mysql> select * from student;
 ---- ------- ------ 
| id | name  | sex  |
 ---- ------- ------ 
|  3 | qian1 | male |
|  8 | qian2 | male |
| 13 | qian3 | male |
 ---- ------- ------ 

步长:auto_increment_increment,起始偏移量:auto_increment_offset

 

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

六 、foreign key

一 、快速理解foreign key

员工信息表有三个字段:工号  姓名  部门

公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费

解决方法:

我们完全可以定义一个部门表

然后让员工信息表关联该表,如何关联,即foreign key

图片 37图片 38

#表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
create table department(
id int primary key,
name char(20) not null
)engine=innodb;

#dpt_id外键,关联父表(department主键id),同步更新,同步删除
create table employee(
id int primary key,
name char(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,'技术有限事业部'),
(2,'人力资源部'),
(3,'销售部');


#再往子表employee中插入记录
insert into employee values
(1,'qian',1),
(2,'liang1',2),
(3,'liang2',2),
(4,'liang3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;


#删父表department,子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
 ---- ------- -------- 
| id | name  | dpt_id |
 ---- ------- -------- 
|  1 | qian    |      1 |
|  2 | liang1 |      2 |
|  3 | liang2 |      2 |
|  4 | liang3 |      2 |
 ---- ------- -------- 


#更新父表department,子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
 ---- ------- -------- 
| id | name  | dpt_id |
 ---- ------- -------- 
|  1 | qian  |      1 |
|  3 | liang2 |  22222 |
|  4 | liang3 |  22222 |
|  5 | liang1 |  22222 |
 ---- ------- -------- 

举例说明

二、如何找出两张表之间的关系 

图片 39图片 40

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

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

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

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

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

分析步骤

三 、建立表之间的关系

1、一对多或称为多对一 三张表:出版社,作者信息,书 一对多(或多对一):一个出版社可以出版多本书

  关联方式:foreign key

图片 41图片 42

=====================多对一=====================
create table press(
id int primary key auto_increment,
name char(20)
);

create table book(
id int primary key auto_increment,
name char(20),
press_id int not null,
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)
;

多对一举例

 

2、多对多 三张表:出版社,作者信息,书 多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多  

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

图片 43图片 44

=====================多对多=====================
create table author(
id int primary key auto_increment,
name char(20)
);


#这张表就存放作者表与书表的关系,即查询二者的关系查这表就可以了
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('qian'),('ze'),('liang'),('duoduo');

#每个作者与自己的代表作如下
1 qian: 
      1 九阳神功
      2 九阴真经
      3 九阴白骨爪
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典


2 ze: 
      1 九阳神功
      6 葵花宝典

3 liang:
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典

4 duoduo:
      1 九阳神功


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

多对多举例

 

3、一对一 两张表:学生表和客户表 一对一:一个学生是一个客户,一个客户有可能变成一个学校,即一对一的关系

  关联方式:foreign key unique

图片 45图片 46

#一定是student来foreign key表customer,这样就保证了:
#1 学生一定是一个客户,
#2 客户不一定是学生,但有可能成为一个学生


create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);


create table student(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);


#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;

#增加学生
insert into student(class_name,customer_id) values
('超神一班',3),
('超神二班',4),
('超神三班',5)
;

一对一举例

 

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

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

图片 47

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

运行结果截图:

图片 48

二、一对一

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

管理员关联用户

 图片 49

===========例子一:用户表和管理员表=========
先建被关联的表
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');

 

运行结果截图:

图片 50

例子二:学生表和客户表

图片 51

 

========例子二:学生表和客户表=========
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);

 

运行结果截图:

图片 52

三、多对多(多条记录对应多条记录)

书和作者(我们可以再创建一张表,用来存book和author两张表的关系)

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

联合唯一:unique(book_id,author_id)

联合主键:alter table t1 add primary  key(id,avg)

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

图片 53

图片 54图片 55

========书和作者,另外在建一张表来存书和作者的关系
#被关联的
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);

 多对多关系举例

用户表,用户组,主机表

-- 用户组
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);

 

 

-- 主机表
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);

 

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

练习

图片 56

 

版权声明:本文由可提现彩票app-支持提现彩票app-10元可提现的彩票app发布于联系我们,转载请注明出处:表与表之间的关系,mysql生成连续日期