database table relationships (one stacking, many-to-many)

1. Remember, always remember: the only way to associate tables with tables is by foreign keys

Tables are associated with tables only through foreign keys.

2. So the database table and table, one-to-many, many-to-many relationships. This is how to establish a foreign key relationship, which table establishes a foreign key.

Maintains relationships between tables through a foreign key.

3.

6 table-to-table relationships

L : For example , T_person and t_card tables, i.e. people and identity cards. This situation requires finding out the master-slave relationship, i.e. who is the primary table and who is from the table. People can have no ID, but the ID must be someone to do, so the person is the main table, and the identity card is from the table. There are two possible scenarios for designing from a table:

• Add a foreign key column (relative to the t_user table) in the T_card table , and add a unique constraint to the foreign key;
• Add a foreign key constraint (relative to the t_user table) to the primary key of the T_card table , that is, The primary key of the T_card table is also a foreign key.

L One-to-many (multi-to-a): The most common is a pair of more! A pair of many and many to one, which is from which angle to see. The relationship between T_user and t_section , from t_user , is one-to-many, and from t_ section angle is more to one! This situation is created in the multi-party foreign key!

L Many-to-many: for example , T_stu and t_teacher table, that is, a student can have more than one teacher, and a teacher can have more than one student. This situation often requires creating an intermediate table to handle many-to-many relationships. For example, create a table t_stu_tea table, give two foreign keys, a foreign key relative to the t_stu table, and another relative t_teacher The foreign key for the table.

7. Encoding

the data that the server sends to the client is encoded as Character_set_result, and if it is UTF8, then the console uses GBK Decoding is also bound to appear garbled. Because the console encoding cannot be modified, the Character_set_result can only be modified to GBK.

L Modify character_set_client variable:set CHARACTER_SET_CLIENT=GBK;

L Modify character_set_results variable:set CHARACTER_SET_RESULTS=GBK;

Setting the encoding is only valid for the current connection, which means that each login The MySQL prompt modifies both encodings, but you can handle this problem by modifying the configuration file: Configuration file path:D:\Program files\mysql\mysql Server 5.1\ My.ini

3　MySQL Tools

Use MySQL tool is not garbled, because they will be modified each time the connection character_set_client,character_set_results , character_set_connection the encoding. In this way , the configuration on the My.ini is overwritten, and there will be no garbled characters.

MySQL Database backup and restore

backing up and recovering data

1 generating SQL scripts

Use the mysqldump command in the console to generate script text for the specified database, but be aware that the script text contains only the contents of the database, and there is no statement to create the database! So when recovering data, you also need to manually create a database and then restore the data.

mysqldump –u user name –p Password database name > generated script file path

can now be in C- Drive to find the mydb1.sql file!

Note thatthe mysqldump command is executed under the Windows console without logging in to mysql!!!

2 Execute SQL script

Execution SQL scripts need to log in to MySQLandthen enter the specified database before you can execute SQL scripts!!!

Execution SQL scripts are not just used to recover a database, you can write SQL scripts in peacetime , and then use the Execute SQL script to manipulate the database! As you know, when you write SQL statements in a black screen , you may not be able to modify them even if you find an error. So I recommend that you use a script file to write the SQL code and then execute it!

SOURCE C:\mydb1.sql

Note that you need to check to see if the tables in the current database conflict with the statements in the script file when you execute the script! For example, there is a statement for CREATE table A in the script file , and a table already exists in the current database , then an error occurs!

You can also execute script files in the following ways:

Mysql-uroot-p123 Mydb1<c:\mydb1.sql

Mysql–u user name –p Password database < to execute script file path

this way without logging in to MySQL!

database table relationships (one stacking, many-to-many)