Basic database creation for Mysql

1. Create a data table --- BASICS (skipped by experts)
Orthodox method: create [TEMPORARY] table name [if not exists]
(Column item definition created)
[Table options]
[Partition options]; # The Orthodox creation method. For specific parameters, refer to the mysql manual. I will not explain them in detail here, but I will only explain them in some special cases.
Example:
Copy codeThe Code is as follows:
Create table user (id int unsigned not null auto_increment,
Username char (15 ),
Sex enum ('M', 'F') default 'M ',
Userid varchar (20 ),
Primary key (id, userid ),
INDEX idx_user (userid ),
) Engine = 'innodb' charset = utf8;

Copy the database structure:
Copy codeThe Code is as follows:
Create [temporary] table name [if not exists] like an existing table name; // simulate an existing table to create a table with the same structure
Mysql> create table vip_user like user;
Mysql> create table vip_user select * from user where 0; // the preceding example can also be used to copy only the structure

Copy and copy Databases
Copy codeThe Code is as follows:
Create [temporary] table new table name select * from old table name; # You can manually specify the subsequent select statement to combine.
Create table dst_tbl (
Id int not null auto_increment,
Primary key (id)
) Select a, B, c from src_tbl;

In order to create a variety of databases that meet user requirements, here is just a discussion.
2. view the modified data table structure
Copy codeThe Code is as follows:
Mysql> desc data table name; # view the data table structure
Mysql> show create table data table name \ G # view the composition statement of the data table. \ G and; have the same meaning, but \ G is displayed vertically, which makes it clearer.
Mysql> show table status like 'data table name' \ G # view the data table status
Mysql> show columns from data table name; # view the structure of the data table, which is the same as desc. However, if you use the like 'field' clause after this statement, only the specified field can be displayed.

3. Change the database structure
First, create a table.
Mysql> create table vip (id int null, username varchar (30 ));
Modify the data type in the Table Structure
Copy codeThe Code is as follows:
Mysql> alter table vip modify id smallint default 1; # change the data record to 1.
Mysql> # alter table vip modify id smallint auto_increment; # An error will be prompted when you execute this statement, because mysql requires that the columns automatically grow as primary keys.
Mysql> alter table vip add primary (id); # Set the primary key of the value in vip to id. It is okay to execute this statement before executing the preceding statement.
Mysql> alter table vip modify username char (30) not null; # change the vip username to char (30 );
Mysql> alter table vip modify username mediumtext first; # change the username in the vip and set it to the first field. Another field is the after field.

Add fields to an existing table
Mysql> alter table vip add sex enum ('M', 'F') default 'M' not null after id; # add the sex field as the enum type, which is placed behind the id.

Rename existing fields
Mysql> alter table vip change sex usersex tinyint default 0 not null after username; rename the field sex to usersex and change the type and location.
Note: Only modify and change in alter are similar. However, modify cannot be renamed but can only be changed. However, change can be renamed or type changed.

Delete Field
Mysql> alter table vip drop usersex; # Delete the usersex field and warn that all data in this field will be lost.
Database Name Change
Mysql> alter table vip rename to vip_user; rename the database.
Change Data Table Engine
Mysql> alter table vip engine = "MyISAM ";

Note: It is very dangerous to modify the data structure. It is best to back up the data to prevent the data from being deleted.
There are also some alter statements and techniques that will be involved in the future .....
4. Data constraints
Data constraints are more and more supported in mysql5, but the existing data constraints are limited to innodb. In the legend, mysql5.2 also supports data constraints (expectation ..)
First, let's take a look at what is a data constraint, because we usually create tables with information that may be correlated, and a data constraint is a bond that associates two tables.
For example, two tables, one usertype and one userid, one keyword key in usertype is the user type number, and one user_key corresponds
1. Make sure that all values in the userid table are in usertype.
2. Then, make sure that the value key in usertype changes the value of user_key In the userid table.
3. usertype values cannot be deleted at will, unless the userid table does not have a value of this usertype. To force delete a value, all usertypes in userid are deleted.
If there are no data constraints, we may need to use several statements for each insert/update operation to ensure correct data integrity. If we use data constraints, we only need to process them at the time of definition, don't worry too much. The most important thing is that data constraints can ensure data and business integrity.
Haha, I have said so much, but I have not yet mentioned the disadvantages of Data constraints: slow, the use of data constraints is much slower than the use of data constraints, and each time a user inserts or changes data, the database system will spend some time checking. however, with the increasing maturity of mysql, this speed will be greatly improved.
Personally, I think it is necessary to use data constraints when non-commercial and real-time systems have high requirements on Data Service Integrity. In other cases, the benevolent wise sees the wise.

5. Brief Analysis of Data constraints
Therefore, the foreign key definition must meet the following three conditions:
1. The two tables must be innodb tables.
2. columns specified as foreign keys must be indexed
3. The foreign key types associated with the two tables must be consistent.
Let's take a look at the example and learn it in the example:
Mysql> create table parent (id int null, primary key (id) engine = innodb; # create a master table
Mysql> create table child (id int, parent_id int,
Foreign key (parent_id)
References parent (id) on delete restrict on update cascade
) Engine = innodb; # create a slave table with the foreign key keyword parent_id, and establish the association between them.
Mysql> insert into parent values (1), (2), (3); # insert data to the master table
Mysql> insert into child values (), (), (); # insert data to the child table. The IDS of child with different parent_id values are 1;
Mysql> # insert into child values (), (); # What will happen? Yes? Why? Think about it
Note: When we create a statement, we agree that data is associated with foreign keys, and there is no primary key with id 4 in the parent, so the subkeys cannot be updated.
The value in the database is:
Parent child
Id parent_id
1 1 1
2 1 2
3 1 3
We continue to operate to reflect the role of the foreign key Association table.
Mysql> update parent set id = 4 where id = 1; # change the value of parent to see the response of child.
Mysql> select * from parent;
Mysql> select * from child;
The value in the database is:
Parent child
Id parent_id
2 1 4
3 1 2
4 1 3
Through the above example, we can clearly see that the user only changes the value of the parent, and the associated child value will automatically change. Continue
Mysql> insert into child values (2, 4), (3, 4), (4, 4); # add other values for the sub-table.
Mysql> # delete from parent where id = 4; # Run this statement to check the result. Is it incorrect? Let's analyze the error message.
Let's review the details and key statements for creating a foreign key:
Foreign key (parent_id) # This statement indicates that the external Association key is the parent_id of the table;
References parent (id) on delete restrict on update cascade # This statement is a constraint statement. references can constrain the foreign key of the database, that is, parend_id corresponds to the id sub-Key of the parent data table, the on delete and on update operations are restricted. mysql has the following operations:
(1) restrict and no action indicate that if the child table has matched records, the update/delete operation on the candidate keys corresponding to the parent table is not allowed. Do you understand now? Why is an error when executing delete from parent where id = 4; because the sub-table (child) still has a value.
(2) When set null is used to update/delete records in the parent table, the columns matching records in the child table are set to null. However, note that the foreign key column of the child table cannot be not null.
(3) When cascade updates/delete records on the parent table, the matching records of the child table are synchronized with update/delete.
You can set different actions according to your needs. For example, to automatically delete the associated values of a sub-table When deleting a parent table, we need:
References parent (id) on delete cascade on update cascade, we need to know before the experiment
1. the configured foreign key cannot be changed. You must delete the foreign key before creating it. (There may be other methods. Which of the following experts will give you some advice)
Mysql> show create table child/G # obtain the constraint (constraint) Name.
Note: constraint can be used by default to specify the constraint name. If it is not specified, the system will automatically name it. For example, we can:
Constraint fk_child_key
Foreign key (parent_id)
References parent (id) on delete restrict on update cascade;
In this way, the overall name of this constraint is fk_child_key. You can operate on this constraint later.
Mysql> alter table child drop foreign key fk_child_key; # Delete Constraints
Mysql> alter table child add foreign key ('parent _ id)
References parent (id) on delete cascade
On update cascade;
Mysql> show create table child/G # The constraints have been changed. You can check the changes.

Mysql> delete from parent where id = 4; # Let's execute the above sentence again. Isn't it wrong?
Mysql> select * from parent;
Mysql> select * from child; # We can see that all the values with parent_id 4 are deleted now. Haha, it will be easy to use in the future
The value in the database is:
Parent child
Id parent_id
2 1 2
3 1 3
6. Additional description of data constraints
If a data constraint is defined, data insertion or modification is slow, especially when the data structure is changed. This is terrible when data is inserted.
When the customer executes load data (loading data, which will be introduced later), we recommend that you use the following command in alter table to temporarily disable data constraints and enable the command after it is complete, this speed can be increased by at least 20 times.
Mysql> set foreign_key_checks = 0; # disable data constraints
Mysql> load data infile' file absolute address 'into table name; # load a large amount of data from a text file
Mysql> set foreign_key_checks = 1; # enable data constraints