MySQL: database entry 3: mysql database entry

MySQL: database entry 3: mysql database entry

 

1. Logic execution sequence of SQL statements

(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) group by <group_by_list>
(6) HAVING (9) order by <order_by_condition>
(10) LIMIT <limit_number>

2.Foreign key constraint
Add a foreign key:
Alter table person add CONSTRAINT fk_id FOREIGN key (dept_id) REFERENCES dept (did);

Delete foreign key
Alter table person drop foreign key fk_id

Note: when data is inserted, data in the master table is inserted before data in the slave table.

When deleting data, you must first Delete the data from the table and then the data in the master table.

3. unique constraints
Create table t4 (
Id int (10) not null,
Name varchar (255 ),
Unique id_name (id, name)
);
ALTER table t4 add UNIQUE id_name (id, name)

Alter table t4 DROP index id_name

4. Default Value Constraints
Create table t5 (
Id int (10) not null primary key,
Name varchar (255) default 'zhang san'
);

INSERT into t5 VALUES (3, DEFAULT), (4, DEFAULT );

5. Relationship between tables
1. One-to-many, one-to-one, and many-to-many

7. Three paradigms of Database Design
1. The first paradigm (ensuring that each column remains atomic)

The first paradigm is the most basic paradigm. If all the field values in the database table are unrecoverable Atomic values, it indicates that the database table meets the first paradigm.

The rational compliance of the first paradigm needs to be determined based on the actual needs of the system. For example, the "Address" attribute must be used in some database systems. You can directly design the "Address" attribute as a field in a database table. However, if the system frequently accesses the "city" section in the "Address" attribute, therefore, you have to re-split the "Address" attribute into provinces, cities, detailed addresses, and other parts for storage. This makes it very convenient to operate a part of the address. This design satisfies the first paradigm of the database.

 

2. Second Paradigm (ensure that each column in the table is related to the primary key)

The second paradigm goes further on the basis of the first paradigm. The second paradigm needs to ensure that each column in the database table is related to the primary key, rather than only a part of the primary key (mainly for the joint primary key ). That is to say, in a database table, only one type of data can be saved in one table, and multiple types of data cannot be saved in the same database table.

 

3. Third Paradigm (ensure that each column is directly related to the primary key column, rather than indirectly related)

The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, but not indirectly related.