SQLリレーショナルモデルの知識まとめ

リレーショナルモデル

リレーショナル・データベースは、リレーショナル・モデルに基づいて構築されています。そして、リレーショナルモデルとは、基本的にデータを格納する2次元のテーブルのことで、Excelのテーブルを多数並べたものと考えることができる。

テーブルの各行がレコードと呼ばれ、レコードは論理的な意味でのデータである。

テーブルの各列はカラムと呼ばれ、同じテーブルの各レコード行は同じ数のフィールドを持つ。

フィールドは、データ型（整数、浮動小数点、文字列、日付など）を定義し、そのデータ型に対して NULL . ただし NULL は、そのフィールドデータが存在しないことを示す。整数値のフィールドで NULL の文字列フィールドは、値が 0 であることを意味しない。 NULL は、その値がヌル文字列であることを意味するものでもありません。 '' .

NULLを禁止することで、クエリ条件を簡素化し、クエリを高速化し、アプリケーションがNULLかどうかを判断せずにデータを読み込めるようになります。

リレーショナル・データベースは、Excelのテーブルとは異なり、アプリケーションのロジックに従って整理・保存できるように、テーブル間に1対多、多対1、1対1のリレーションシップを必要とします。これにより、アプリケーションのロジックに従ってデータを整理し、保存することができます。

リレーショナルデータベースでは、主キーと外部キーによって関係が維持される。

プライマリーキー

例えば name フィールドを主キーとするレコードは、Xiao Ming または Xiao Hong という名前で一意に識別することができます。しかし、この設定では、同じ主キーを持つ2つのレコードを挿入することができないため、同じ名前の学生を保存することはできません。

主キーの重要な要件は、一度テーブルにレコードを挿入したら、主キーはできれば変更しないことです。主キーはレコードを一意に特定するために使用され、主キーを変更すると一連の影響を引き起こす可能性があるからです。

主キーの役割は重要であるため、どのように選択するかはビジネス展開に大きな影響を与える。学生のID番号を主キーにすると、レコードの位置が一意に特定されるようです。しかし、ID番号が上がってしまったり、主キーとして変更しなければならなくなったりして、ビジネスに重大な影響を及ぼす可能性があるのも、このID番号のシナリオなのです。

ですから、主キー選びの基本は、業務に関係するフィールドを主キーにしないことです。

したがって、ID番号、携帯電話番号、メールアドレスなど、一意になりそうなフィールドは はありえない が主キーとして使用されます。

主キーは完全にビジネスに依存しないフィールドであることが望ましいので、一般的にこのフィールドに名前を付けます。 id .

外部キー

一対多

主キーでレコードを一意に特定したら、そのレコードに students テーブルを使用して、任意の学生レコードを識別します。

We can also add the classes table to identify any of the class records in.

But how do we determine the
students
table for a single record, for example, the
id=1
of Xiaoming belongs to which class?
Since a class can have more than one student, the relationship between these two tables can be called "one-to-many" in the relational model, i.e., a
classes
record can correspond to multiple
students
table.
In order to express this one-to-many relationship, we need to add the
students
table with a column
class_id
so that its value is the same as the
classes
table corresponding to a record in the

In this way, we can create a new table based on the
class_id
column directly to locate a
students
The records in the table should correspond to the
classes
to which record in the
Example.
Xiao Ming's
class_id
is
1
and therefore, the corresponding
classes
table record is
id=1
of a class; Red's
class_id
is
1
and therefore, the corresponding
classes
table record is
id=1
of a class; White's
class_id
is
2
and therefore, the corresponding
classes
table record is
id=2
of the second class.
In
students
table, pass the
class_id
field, the data can be associated with another table, such columns are called foreign keys.
Foreign keys are not implemented by column names, but by defining foreign key constraints: the
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes(id)


where the name of the foreign key constraint
fk_class_id
can be arbitrary, the
FOREIGN KEY (class_id)
specifies the
class_id
as a foreign key, the
REFERENCES classes (id)
specifies that this foreign key will be associated with the
classes
table of the
id
column (i.e.
classes
table's primary key).
By defining foreign key constraints, relational databases can ensure that invalid data cannot be inserted. **i.e. if
classes
table does not exist
id=99
the record of
students
table will not be able to insert
class_id=99
in the /code table.
Since foreign key constraints degrade database performance, most Internet applications do not set foreign key constraints in the pursuit of speed, but rely solely on the application itself to ensure that the logic is correct. In this case, the
class_id
is just a normal column, except that it acts as a foreign key.
To delete a foreign key constraint, it is also done with the
ALTER TABLE
This is achieved by.
ALTER TABLE students
DROP FOREIGN KEY fk_class_id


Note: Removing a foreign key constraint does not remove the foreign key column. Deleting a column is done by DROP COLUMN ... This is achieved.
Many-to-many
We can define a one-to-many relationship by associating a foreign key of one table to another table. In some cases, it is also necessary to define "many-to-many" relationships. For example, a teacher can have multiple classes, and a class can have multiple teachers, so there is a many-to-many relationship between the class table and the teacher table.
A many-to-many relationship is actually achieved by two one-to-many relationships, i.e., by associating two one-to-many relationships with an intermediate table, a many-to-many relationship is formed
one-to-one
A one-to-one relationship is one in which a record in one table corresponds to a unique record in another table.
If you are careful, you will notice that since it is a one-to-one relationship, why not give the
students
table with a
mobile
column, so that it can be combined into one?
If business allows, it is perfectly possible to combine the two tables into one. However, there are times when a student does not have a cell phone number, so the
contacts
table will not have a corresponding record. In fact, the one-to-one relationship is, to be precise
contacts
table corresponds one-to-one to
students
table.
There are also applications that split a large table into two one-to-one tables with the goal of separating frequently read and infrequently read fields for higher performance. For example, splitting a large user table into a basic user information table
user_info
and the user details table
user_profiles
Most of the time, you only need to query the
user_info
table, you don't need to query the
user_profiles
table, which improves query speed.
To summarize: relational databases allow one-to-many, many-to-many, and one-to-one relationships through foreign keys. Foreign keys can be either constrained by the database or unconstrained, relying only on the application logic to ensure it.
Index
Concept and usage of indexes
In a relational database, if there are tens of thousands or even hundreds of millions of records, and you want to get very fast when looking up the records, you need to use indexes.
An index is a data structure in a relational database that preorders the values of one or more columns. **By using an index, it allows the database system to not have to scan the entire table, but to locate the eligible records directly, which speeds up the query considerably.
For example, for
students
table.

If it is necessary to frequently change the code according to the
score
column, you can query the
score
column by creating an index of
ALTER TABLE students
ADD INDEX idx_score(score);


Using ADD INDEX idx_score (score) creates an index with the name idx_score, using the column score.
The index name is arbitrary, and the index can be written sequentially in parentheses if it has multiple columns.
ALTER TABLE students
ADD INDEX idx_name_score (name, score);


The efficiency of an index depends on whether the values of the indexed column are hashed, i.e., the more dissimilar the values of the column are, the more efficient the index is. ** Conversely, if a record has a large number of identical values in a column, for example
gender
column, about half of the record's values are
M
and the other half are
F
so it makes no sense to create an index for that column.
Multiple indexes can be created for a single table. The advantage of indexes is that they improve query efficiency. The disadvantage is that the indexes need to be modified at the same time when inserting, updating, and deleting rows, so the more indexes there are, the slower the insertion, update, and deletion of rows will be.
For primary keys, relational databases automatically create primary key indexes on them. Using a primary key index is the most efficient because the primary key will guarantee absolute uniqueness.
Unique Indexes
When designing relational data tables, columns that look unique, such as ID numbers, email addresses, etc., are not appropriate as primary keys because they have business implications.
However, these columns have a uniqueness constraint based on business requirements: that is, there cannot be two records that store the same ID number. At this point, it is possible to add a unique index to the column. For example, let's assume that
students
table of the
name
cannot be duplicated.
ALTER TABLE students
ADD UNIQUE INDEX uni_name(name);


by
UNIQUE
keyword we have added a unique index.
It is also possible to add a unique constraint to just one column without creating a unique index: the
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);


In this case, the
name
column is not indexed, but still has a uniqueness guarantee.
Whether indexes are created or not, it makes no difference to users and applications using relational databases.
The idea here is that when we query a database, the database system will automatically use the index to make the query more efficient if there is a corresponding index available, and if there is no index, the query will execute normally, it will just be slower.
Therefore, indexes can be gradually optimized as you use the database.
Tips
1. By creating indexes on database tables, you can improve the query speed. However, the more indexes you have, the slower you can insert and update.
2. Poorly added indexes do not make queries faster, but even slower.
3. By creating a unique index, you can ensure that the value of a column is unique.
4. Database indexes are transparent to both the user and the application.
The above is a summary of SQL relational model knowledge, more information about SQL relational model, please pay attention to other related articles in the Codedevlib!