Shulou(Shulou.com)06/03 Report--

The relevant knowledge points of the database, the three major characteristics of the database are: entity attributes and relationships.

Entities: tables; attributes: data (fields) in tables; relationships: relationships between tables

Three paradigms of database design (key points):

The first normal form (1NF): each column (each field) in the data table must be an inseparable small unit and ensure the atomicity of each column.

For example: userInfo: 131777368781 userAds, Yantai City, Shandong Province: userTel:131777368781, Yantai City, Shandong Province

The second normal form (2NF): after meeting the 1NF, all columns in the table must depend on the primary key, and no column has nothing to do with the primary key, which means that a table only describes one thing.

For example, the order table only describes the information related to the order, so all fields must only describe the information related to the product with the order id related product table, so all fields must be related to the product id. Therefore, both order information and product information cannot appear in the same table, as shown in the following figure:

The third normal form (3NF): the second normal form (2NF) must be satisfied first, requiring that each column in the table is only directly related to the primary key, not indirectly (each column in the table can only rely on the primary key)

For example, customer-related information is needed in the order table. After the customer table is separated, only one user id is needed in the order table, and there can be no other customer information. Because other customer information is directly related to the user id, not directly to the order id.

[how to better distinguish the three paradigms]

The first paradigm and the second paradigm is whether there are two tables, the second paradigm is that a table contains different entity attributes, then it must be divided into multiple tables, and the third paradigm requires that it has been divided into multiple tables, then one table can only have the id (primary key) in another table, and there can not be any other information (all other information is queried in the other table with the primary key).

[five database constraints]

1.primary KEY: setting primary key constraint

2.UNIQUE: set constraint, no duplicate values

3.DEFAULT default value constraint, height DOUBLE (3) DEFAULT 1.2 height defaults to 1 height if not input

4.NOT NULL: set a non-empty constraint. This field cannot be empty.

5.FOREIGN key: sets foreign key constraints.

[primary key]

1. What are the precautions of the primary key?

The primary key is non-empty by default and is constrained by default. Only the primary key can set automatic growth. Automatic growth must be the primary key, and the primary key does not necessarily grow automatically.

two。 How to set the primary key?

Set: ID INT PRIMARY KEY when defining a column

Set: primary KEY (id) after the column is defined

[foreign key]

1. Considerations for setting foreign keys:

Only INNODB's database engine supports foreign keys. Modifying my.ini file settings default-storage-engine=INNODB foreign keys must be the same as the data type of the reference column (numeric values require the same length and unsigned, string requires the same type, but the length can be different).

2 set the syntax for foreign keys:

CONSTRAINT foreign key name FOREIGN KEY (foreign key field) REFERENCES reference table (reference field) ON DELETE SET NULL ON UPDATE CASCADE sets referential integrity

3. Reference operations for foreign key constraints?

Test communication group: 1017539290

Integrity operation of the reference table: how to deal with foreign keys in the foreign key table when the reference fields of the reference table are deleted or updated

Optional values for reference operation:

RESTRICT rejects the reference table to delete or update reference fields

RESTRICT is the same as NO ACTION, but this directive only takes effect on mysql

When CASCADE deletes or updates the reference field of the reference table, the record of the foreign key table is deleted and updated synchronously.

When SET NULL deletes or updates the reference field of the reference table, the foreign key of the foreign key table is set to NULL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.