Let's learn what a MySQL database is (3)

Free learning recommendation: mysql video tutorial

##Directory

• ##Fuzzy query
  • Constraints of tables
  • Associations between tables
  • Many-to-one association
    • Many-to-many association
    • One-to-one association

Fuzzy query

We can find the data we want based on the roughly provided content. It is different from = query. Take char type data and varchar type data as examples:

---

create table c1(x char(10));create table c2(x varchar(10));insert c1 values('io');insert c2 values('io');

Fuzzy query uses like

select * from c1 where x like 'io';select * from c2 where x like 'io';

It can be found that x in c1 is of char type. We can’t display it through fuzzy query whether there is io data, and We can query it through =

Fuzzy query is more accurate. To query in this way, you must enter the entire content of this field before you can query it. The data stored in the char type here , the length is less than 10, so spaces are used to supplement it, so when querying, you need to bring spaces;

We can also use the query method provided by fuzzy query, % means any 0 or more characters.

select * from c1 where x like 'io%';

If we only know that the second digit is an o and don’t know the beginning and end, we can use: _ to represent any single character, and then use % to match the following characters

select * from c1 where x like '_o%';

The syntax of SQL fuzzy query is "SELECT column FROM table WHERE column LIKE ';pattern';".

SQL provides four matching modes:

% represents any 0 or more bai characters. The following statement:

SELECT * FROM user WHERE name LIKE ';%三%';
1. will put the names of "Zhang San", "Three-legged Cat", "Tang Sanzang" and so on. Find it;
   
   _ represents any single character. Statement:
SELECT * FROM user WHERE name LIKE ';
2. 三
   '; Only find "Tang Sanzang" whose name has three characters and the middle character is "三"; SELECT * FROM user WHERE name LIKE ';三__';
   Only find "three-legged cat" whose name has three characters and the first character is "三";
   
   [ ] represents one of the characters listed in brackets (similar to a regular expression). Statement:
SELECT * FROM user WHERE name LIKE ';[张李王]三';
3. will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San" ");
   If [ ] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e"
   SELECT * FROM user WHERE name LIKE '; old [1 -9]';
   will find "Old 1", "Old 2",..., "Old 9";
   If you want to find the "-" character, please put it first: ';Zhang San [-1-9]';
   
   [^ ] represents a single character not listed in brackets. Statement:
SELECT * FROM user WHERE name LIKE ';[^Zhang Liwang]三';
4. will find "Zhao San", "Sun" who are not surnamed "Zhang", "Li", "Wang" Three" and so on;
   SELECT * FROM user WHERE name LIKE ';老[^1-4]';
   Will exclude "old 1" to "old 4" to find "old 5", "old 6", ..., "Old 9".
   
   

Table constraints

Introduction:

---

Constraints and data types The width is the same and they are all optional parameters
Function: Used to ensure the integrity and consistency of data
Mainly divided into:

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录FOREIGN KEY (FK)    标识该字段为该表的外键NOT NULL    标识该字段不能为空UNIQUE KEY (UK)    标识该字段的值是唯一的AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）DEFAULT    为该字段设置默认值UNSIGNED 无符号
ZEROFILL 使用0填充

not null: The literal meaning is that after setting, every time a value is inserted, the value must be set for the field

default: If no value is set for the field, use the value we defined in A default value after default

UNIQUE KEY: After a field sets this constraint, the value it sets can only have one (unique) value for this field in the entire table

PRIMARY KEY : The primary key is the basis for the innodb storage engine to organize data. Innodb calls it an index-organized table. There must be and only one primary key in a table.

The primary key is the unique identifier that can determine a record

AUTO_INCREMENT: After setting, this field will automatically increase by a number every time a value is inserted into the table, but this field must be of integer type , but also the primary key

FOREIGN KEY: Foreign key, which associates a field of this table with a field of another table.

After the association, the value of this field must correspond to the value of the associated field.

我们创建表，通常会有一个id字段作为索引标识作用，并且会将它设置为主键和自增。

实例：

create table test(
    id int primary key auto_increment,
    identity varchar(18) not null unique key, --身份证必须唯一
    gender varchar(18) default '男');insert test(identity) values('123456789012345678');

当身份字段插入相同值，则会报错，因为字段设置了唯一值

insert test(identity,gender) values('0123456789012345678','女');

我们会发现，id不对劲啊，那是因为笔者之前进行两次插入值操作，但是值并没有成功插入进去，但是这个自增却受到了影响.

这个时候，我们进行两部操作就可以解决这个问题。

alter table test drop id;alter table test add id int primary key auto_increment first;

删除id字段，再重新设置。

很神奇是不是，这个MySQL的底层机制。vary 良心

还需要注意的是：我们使用delete删除一条记录时，并不会影响自增

delete from test where id = 2;insert test(identity,gender) values('111111111111111111','男');

关于这个操作，如果我们只是删除单条记录的话，可以使用上序提供的方法还调整自增的值，而如果是删除整个表记录的话，使用以下方法：

truncate test;

效果演示：delete删除整个表记录

效果演示：truncate删除整个表记录

联合主键

确保设置为主键的某几个字段的数据相同

主键的一个目的就是确定数据的唯一性，它跟唯一约束的区别就是，唯一约束可以有一个NULL值，但是主键不能有NULL值，再说联合主键，联合主键就是说，当一个字段可能存在重复值，无法确定这条数据的唯一性时，再加上一个字，两个字段联合起来确定这条数据的唯一性。比如你提到的id和name为联合主键,在插入数据时，当id相同，name不同，或者id不同，name相同时数据是允许被插入的，但是当id和name都相同时，数据是不允许被插入的。

实例：

create table test(
    id int,
    name varchar(10),
    primary key(id,name));
    insert test values(1,1);

如果再次插入两个主键相同的数据，则会报错

只要设置主键的两个字段，在一条记录内，数据不完全相同就没有问题。

外键的话，我们在表之间的关联进行演示

表之间的关联

---

我们这里先介绍表之间的关联，后面再学习联表查询

通过某一个字段，或者通过某一张表，将多个表关联起来。

我们一张表处理好不行吗，为什么要关联，像这样？

有没有发现一个问题，有些员工它们对应的是相同部门，一张表就重复了很多次记录，随着员工数量的增加，就会出现越来越多个重复记录，相对更占用空间了。

那么我们需要将部门单独使用一张表，再将员工这个使用一个字段关联到另一个表内，我们可以使用外键，也可以不使用外键，先来演示外键的好处吧

多对一关联

---

如：多个员工对应一个部门。

员工表，先别急着创建，请向下看

create table emp(
	id int primary key auto_increment,
	name varchar(10) not null,
	dep_id int,
	foreign key(dep_id) references dep(id) 
	on update cascade   # 级联更新
	on delete cascade); # 级联删除

上面外键的作用就是：

dep_id字段关联了dep表的id字段：
当dep表的id字段值修改后，该表的dep_id字段下面如果有和dep表id相同值的则会一起更改。
如果dep表删除了某一条记录，当emp表的dep_id与dep表删除记录的id值对上以后，emp表这条记录也会被随之删除。

注意：必须是外键已存在，所以需要先创建部门表，再创建员工表

部门表

create table dep(
	id int primary key auto_increment,
	name varchar(16) not null unique key,
	task varchar(16) not null);

emp表的dep_id字段设置的数据必须是dep表已存在的id

所以我们需要先向dep表插入记录

insert dep(name,task) values('IT','技术'),('HR','招聘'),('sale','销售');

员工表插入记录

insert emp(name,dep_id) values
	('jack',1),
	('tom',2),
	('jams',1),
	('rouse',3),
	('curry',2);
	# ('go',4) 报错，在关联外键的id字段中找不到

注意：如果我们emp表的dep_id字段插入的数据，在dep表中的id字段不存在该数据时，就会报错。

查询我们创建后的效果

这样就把这两个表关联起来了，目前我们先不了解多表查询，这个先了解的是，表之间的关联。

我们再来看一下同步更新以及删除，外键的改动被关联表会受到影响

update dep set id=33333 where id = 3;

再来体验一下同步删除

delete from dep where id = 33333;

这就是外键带给我们的效果，有利也有弊：

• 优点：关联性强，只能设置已存在的内容，并且同步更新与删除
• 缺点：当删除外键表的某一条记录，关联表中有关联性的记录会被全部删除

多对多关联

---

多张表互相关联

如：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

这时使用外键会出现一个弊端，那就是先创建哪张表呢？它们都互相对应，是不是很矛盾呢？解决办法：第三张表，关联书的id与作者的id

book表

create table book(
	id int primary key auto_increment,
	name varchar(30));

author表

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

中间表：负责将两张表进行关联

create table authorRbook(
	id int primary key auto_increment,
    author_id int,
    book_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);

多名作者关联一本书，或者一名作者关联多本书，书也要体现出谁关联了它

book表插入数据：

insert book(name) values
	('斗破苍穹'),
	('斗罗大陆'),
	('武动乾坤');

author表插入数据：

insert author(name) values
	('jack'),
	('tom'),
	('jams'),
	('rouse'),
	('curry'),
	('john');

关联表插入数据：

insert authorRbook(author_id,book_id) values
	(1,1),
	(1,2),
	(1,3),
	(2,1),
	(2,3),
	(3,2),
	(4,1),
	(5,1),
	(5,3),
	(6,2);

目前的对应关系就是：

jack：斗破苍穹、斗罗大陆、武动乾坤
tom：斗破苍穹、武动乾坤
jams：斗罗大陆
rouse：斗破苍穹
curry：斗破苍穹、武动乾坤
jhon：斗罗大陆

一个作者可以产于多本书的编写，同时，每本书都会标明产于的作者

一对一关联

---

路人有可能变成某个学校的学生，即一对一关系。

在这之前，路人不属于学校。

原理就是：学校通过广告，或者通过电话邀请，将路人变成了学生。

路人表

create table passers_by(
	id int primary key auto_increment,
	name varchar(10),
	age int);
	insert passers_by(name,age) values
	('jack',18),
	('tom',19),
	('jams',23);

学校表

create table school(
	id int primary key auto_increment,
	class varchar(10),
	student_id int unique key,
	foreign key(student_id) references passers_by(id)
	on update cascade
	on delete cascade);insert school(class,student_id) values
	('Mysql入门到放弃',1),
	('Python入门到运维',3),
	('Java从入门到音乐',2);

数据存储的设计，需要提前设计好表的关联 关系，将关系全部设计好以后，剩下的只是往里存数据了，后续我们会了解到联表查询相关内容，将有关联性的内容，以虚拟表的形式查询出来，查询出来的数据可能来自多个表。

表的关联，建议使用以下方式

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

相关免费学习推荐：mysql数据库(视频)

The above is the detailed content of Let's learn what a MySQL database is (3). For more information, please follow other related articles on the PHP Chinese website!