Relational Model
Relational databases are built on relational models. The relational model is essentially several two-dimensional tables that store data, which can be regarded as many Excel tables.
Each row of a table is called a record, and a record is a logical data.
Each column of the table is called a field (Column), and each row of the same table records have the same several fields.
The field defines the data type (integral, floating point, string, date, etc.) and whether it is allowed to beNULL. NoticeNULLIndicates that the field data does not exist. An integer field ifNULLIt does not mean that its value is 0, and likewise, a string field isNULLIt does not mean that its value is an empty string''。
Typically, fields should avoid allowing NULL. Not allowing NULL can simplify query conditions and speed up query speed, which also helps the application to read data without having to determine whether it is NULL.
What is different from Excel tables is that a relational database table needs to be established between "one-to-many", "many-to-one" and "one-to-one" relationship, so that data can be organized and stored according to the logic of the application.
In relational databases, relationships are maintained by primary and foreign keys.
Primary key
For example, suppose we putnameAs the primary key, a record can be uniquely determined by the name Xiao Ming or Xiao Hong. However, with this setting, it is impossible to store students with the same name, because inserting two records with the same primary key is not allowed.
The most critical point for the primary key is: once the record is inserted into the table, it is best not to modify the primary key, because the primary key is used to uniquely locate the record. Modifying the primary key will cause a series of impacts.
Since the role of primary keys is very important, how to select primary keys will have an important impact on business development. If we use the student's ID number as the primary key, it seems that we can uniquely locate the record. However, ID number is also a business scenario. If the ID number is upgraded or needs to be changed, as the primary key and has to be modified, it will have a serious impact on the business.
Therefore, a basic principle of choosing a primary key is: do not use any business-related fields as the primary key.
Therefore, the fields that seem unique to the ID number, mobile number, and email address are allNoUsed as primary key.
As a primary key, it is best to be a completely business-independent field. We generally name this field asid。
Foreign keys
One to many
When we uniquely identify the record with the primary key, we canstudentsThe records for any student are determined in the table:
| id | name | other columns… |
|---|---|---|
| 1 | Xiao Ming | … |
| 2 | Xiaohong | … |
We can stillclassesDetermine any class records in the table:
| id | name | other columns… |
|---|---|---|
| 1 | Class 1 | … |
| 2 | Class 2 | … |
But how do we determinestudentsa record in the table, for example,id=1Which class does Xiao Ming belong to?
Since a class can have multiple students, in the relationship model, the relationship between these two tables can be called "one-to-many", i.e., oneclassesThe records can correspond to multiplestudentsrecord of the table.
To express this one-to-many relationship, we need tostudentsAdd a column to the tableclass_id, let its value be withclassesA record in the table corresponds to:
| id | class_id | name | other columns… |
|---|---|---|---|
| 1 | 1 | Xiao Ming | … |
| 2 | 1 | Xiaohong | … |
| 5 | 2 | noob | … |
In this way, we canclass_idThis column directly locates astudentsThe records in the table should correspond toclassesWhich record?
For example:
Xiao Ming'sclass_idyes1, therefore, the correspondingclassesThe record in the table isid=1The first class; Xiaohong'sclass_idyes1, therefore, the correspondingclassesThe record in the table isid=1Class one; novicesclass_idyes2, therefore, the correspondingclassesThe record in the table isid=2Class 2.
existstudentsIn the table, byclass_idThe field of , can associate the data with another table, and this column is called a foreign key.
Foreign keys are not implemented through column names, but by defining foreign key constraints:
ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes(id)
where, the name of the foreign key constraintfk_class_idCan be any,FOREIGN KEY (class_id)Designatedclass_idAs a foreign key,REFERENCES classes (id)This foreign key is specified to be associated withclassesTable ofidColumn (i.e.classesprimary key of the table).
By defining foreign key constraints, a relational database can ensure that invalid data cannot be inserted. **IfclassesThe table does not existid=99record ofstudentsThe table cannot be insertedclass_id=99record.
Since foreign key constraints will reduce the performance of the database, most Internet applications do not set foreign key constraints in order to pursue speed, but rely solely on the application itself to ensure the correctness of the logic. In this case,class_idIt's just an ordinary column, it just acts as a foreign key.
To delete a foreign key constraint, it is also throughALTER TABLEImplemented:
ALTER TABLE students DROP FOREIGN KEY fk_class_id
Note: Deleting the foreign key constraint does not delete the foreign key column. Deletion of columns is achieved through DROP COLUMN...
Many-to-many
By associating the foreign keys of one table to another table, we can define a one-to-many relationship. Sometimes, it is also necessary to define a "many-to-many" relationship. For example, a teacher can correspond to multiple classes, and a class can correspond to multiple teachers. Therefore, there is a many-to-many relationship between the class table and the teacher table.
Many-to-many relationships are actually realized through two one-to-many relationships, that is, through an intermediate table, the two one-to-many relationships are associated, and a many-to-many relationship is formed.
One-to-one
A one-to-one relationship refers to a record in one table corresponding to a unique record in another table.
If you are careful, you will find that since it is a one-to-one relationship, why not give it to me.studentsAdd one to the tablemobileSo that can we combine two into one?
If the business allows it, it is possible to combine two tables into one table. However, sometimes, if a student does not have a mobile phone number,contactsThere is no corresponding record in the table. In fact, one-to-one relationship is to be precise,contactsTable one to one correspondencestudentssurface.
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, split a large user table into a user basic information tableuser_infoand user details tableuser_profilesMost of the time, you only need to queryuser_infoTables, no query is requireduser_profilestable, thus improving query speed.
Summary: Relational databases can realize one-to-many, many-to-many and one-to-one relationships through foreign keys. Foreign keys can be constrained by the database or set without setting constraints, and rely solely on the logic of the application to ensure.
index
The concept and usage of index
In a relational database, if there are tens of thousands or even hundreds of millions of records, when searching for records, you need to use indexes.
An index is a data structure in a relational database that pre-sorts the values of a column or columns. **By using indexes, the database system can not have to scan the entire table, but directly locate records that meet the conditions, which greatly speeds up the query.
For example,studentssurface:
| id | class_id | name | gender | score |
|---|---|---|---|---|
| 1 | 1 | Xiao Ming | M | 90 |
| 2 | 1 | Xiaohong | F | 95 |
| 3 | 1 | Xiaojun | M | 88 |
If you want to follow it regularlyscoreQuery the column and you canscoreColumn creation index:
ALTER TABLE students ADD INDEX idx_score(score);
Using ADD INDEX idx_score (score) creates an index with the name idx_score and column score.
The index name is arbitrary. If there are multiple columns in the index, you can write it in parentheses.
ALTER TABLE students ADD INDEX idx_name_score (name, score);
The efficiency of an index depends on whether the values of the index column have been hashed, that is, if the values of the column are different, the higher the index efficiency. **Conversely, if there are a large number of identical values in the column of the record, e.g.genderColumn, about half of the record value isM, the other half isF, therefore, it makes no sense to create an index for that column.
You can create multiple indexes on a table. The advantage of indexing is that it improves query efficiency, and the disadvantage is that when inserting, updating and deleting records, the index needs to be modified at the same time. Therefore, the more indexes, the slower the insertion, updating and deleting records.
For primary keys, the relational database automatically creates a primary key index on it. Using primary key indexing is the most efficient, because primary keys are guaranteed to be absolutely unique.
Unique index
When designing relationship data tables, columns that seem unique, such as ID number, email address, etc., are not suitable as primary keys because they have business meanings.
However, these columns have unique constraints according to business requirements: that is, two records cannot appear and store the same ID number. At this time, you can add a unique index to the column. For example, we assumestudentsTable ofnameCannot be repeated:
ALTER TABLE students ADD UNIQUE INDEX uni_name(name);
passUNIQUEWe add a unique index to keywords.
You can also add a unique constraint to a column without creating a unique index:
ALTER TABLE students ADD CONSTRAINT uni_name UNIQUE (name);
In this case,nameColumns have no index, but are still guaranteed to be unique.
Whether or not an index is created, there will be no difference between using a relational database for users and applications.
What this means here is that when we query in the database, if there is a corresponding index available, the database system will automatically use the index to improve the query efficiency. If there is no index, the query can be executed normally, but the speed will slow down.
Therefore, indexes can be gradually optimized during the process of using the database.
Tips
1. By creating indexes on database tables, you can improve query speed. But the more indexes, the slower the insertion and update.
2. If the index is not added well, the query will not become faster or even slower.
3. By creating a unique index, you can ensure that the value of a certain column is unique.
4. Database indexes are transparent to users and applications.
The above is the detailed content of the summary of the knowledge of SQL relational model. For more information about SQL relational model, please pay attention to my other related articles!