1. Create data table---base (master skipped)
Orthodox method: Create [temporary] table name [if not EXISTS]
(Definition of the column item created)
[Table Options]
[Partitioning options]; #正统的创建方式, specific parameters, please refer to the MySQL manual, here do not make a detailed explanation, just say something more special.
Cases:
Copy Code code 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 Database structure:
Copy Code code as follows:
Create [temporary] table name [if not exists] like already existing table name;//Simulate existing tables creating a table with exactly the same structure
Mysql>create table vip_user like user;
Mysql>create Table Vip_user Select * from user where 0;//The example can also do this, only copy the structure
copy and copy the database
Copy Code code as follows:
Create [temporary] table name select * from old table name; #用户可以人为的指定后续select组合成需要的语句.
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 user-compliant databases, here is just a start.
2. View the modified data table structure
Copy Code code as follows:
Mysql> desc data table name; #查看数据表的结构
Mysql> Show CREATE TABLE datasheet name \g #查看数据表的构成语句, \g and; the meaning of the same, but \g is vertical display, so see more clearly.
Mysql> Show Table status like ' data table name ' \g #查看数据表的状态
Mysql> Show columns from data table name; #查看数据表的结构, same as DESC, but use the like ' field ' after this statement to display only the specified field
3. Change the structure of the database
We first create a table
Mysql> CREATE TABLE VIP (ID int null,username varchar (30));
modifying data types in a table structure
Copy Code code as follows:
Mysql>alter table VIP Modify ID smallint default 1; #更改数据记录为1.
Mysql> #alter table VIP Modify ID smallint auto_increment; #大家执行一下这句话会提示错误 because the columns that require automatic growth in MySQL are set as the primary key
Mysql>alter Table VIP Add primary (ID); #设vip中的值的主键为id, the execution of this statement before the execution of the above is no problem.
Mysql>alter Table VIP Modify username char (a) not null; #改变vip的username为char (30);
Mysql>alter Table VIP Modify username Mediumtext; #改变vip中的username, and set it in the front field, and one after field, which refers to a field
Add a field to an existing table
Mysql> ALTER TABLE VIP add sex enum (' m ', ' F ') default ' m ' NOT null after ID; #新增sex字段为enum类型, placed behind the ID.
Change the name of an existing field
Mysql>alter table VIP change sex usersex tinyint default 0 is not NULL after username, renamed field sex to Usersex and changed type and position.
NOTE: Alter Modify,change is very similar, but modify can only change the structure, but change is renamed, can also be changed type.
Delete a field
Mysql>alter table VIP Drop usersex #删除 usersex field, warns that all of the field data will be lost.
Database Renaming
Mysql>alter table VIP Rename to vip_user database for renaming.
Changing data table engine
Mysql>alter table VIP engine= "MyISAM";
Note that modifying the data structure is a very dangerous thing, and it's best to back it up in case it's not.
There are also parts of ALTER's statements and techniques we will be involved in later one by one arrival ...
4. About Data constraints
Data constraints are getting better in mysql5, but existing data constraints are limited to InnoDB, and legendary mysql5.2 supports data constraints (expecting ...). ）
First, let's take a look at what data constraints are, because we normally create tables where we can have interconnected information, and data constraints are a link between two tables.
For example: Two tables, a usertype, a userid,usertype keyword key is a user type number, UserID table also has a user_key corresponding to the usertype table
1. First of all, we want to make sure that all the values in the UserID table are in usertype.
2. Secondly, we want to ensure that the value key in the usertype changes userid the User_key value in the table will also change.
The value in 3.usertype cannot be deleted at will, unless the value of the usertype type is not present in the UserID table, and if you want to force the deletion, the values of all usertype in UserID are deleted.
Without data constraints, we may need to use several statements per insert/update to guarantee the correct integrity of the data, and if you use data constraints you just need to deal with them at the time of the definition without worrying too much. And the most important thing is to use data constraints can be a good guarantee of data, business integrity.
Oh, said so much, have not said the shortcomings of data constraints: slow, using data constraints is much slower than not using data constraints, and each time a user inserts or changes data, the database system spends a certain amount of time checking. But as MySQL matures, this speed can be greatly improved.
Personally, I think it is necessary to use data constraints in the case of non-commercial, real-time systems with high requirements for data business integrity. In other cases, the beholder is different.
5. Concise Analysis of data constraints
Therefore, the definition of a foreign key must meet the following three conditions:
1. Two tables must be InnoDB table type
2. Columns specified as foreign keys must be indexed
3. The associated foreign key types in the two tables must match.
Let's take a look at the example and learn in the example:
Mysql> CREATE TABLE parent (ID int null,primary key (id)) Engine=innodb; #创建一个主表
Mysql> CREATE TABLE child (ID int,parent_id int,
Foreign KEY (PARENT_ID)
References parent (ID) on delete restrict on UPDATE cascade
) Engine=innodb; #创建一个从表, and about the foreign key keyword for parent_id, establishes the association relationship between.
Mysql> INSERT into parent values (1), (2), (3); #对主表插入数据
Mysql> INSERT into the child values (1,1), (1,2), (1,3); #对子表进行插入数据, the ID values for the different parent_id child children are 1;
Mysql> #insert into the child values (1,1), (1,2), (1,3), (1,4); #看一下这会发生什么? It's an error, right? What's the reason? Let's think about it.
Description: Because we are in the creation of the statement when the data is a foreign key association, and the parent does not exist in the ID value of 4 of the primary key, then the child key is certainly not updated.
The values in the database are:
Parent Child
ID 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; #改变parent的值看一下child的反应
Mysql> SELECT * from parent;
Mysql> select * from child;
The values in the database are:
Parent Child
ID ID parent_id
2 1 4
3 1 2
4 1 3
From the above example, you can clearly see that the user only changes the value of parent, and the associated child value will automatically change. We continue to
Mysql> inserts into the child values (2,4), (3,4), (4,4), #为子表再添加一些其它的值.
Mysql> #delete from parent where id=4; #大家执行这条语句看一下有什么结果, is it wrong? Let's analyze the hint error.
Let's review the details and key statements that we created the foreign key:
Foreign key (parent_id) #这句话的意思是指定对外关联键为本表的parent_id;
References parent (ID) on delete restrict on update cascade# This sentence is a constraint statement, references can constrain the foreign key of this database, that is, parend_id corresponds to the ID subkey of the parent data table , and constrains the operation on Delete,on update, MySQL has the following operations:
(1) Restrict, no action means that if there is a matching record in the child table, then the corresponding candidate key for the parent table is not allowed to update/delete operations, we now understand? Why we execute the delete from parent where id=4, an error occurs because the value is also present in the child table.
(2) Set NULL sets the column of the matching record on the child table to NULL when the record is update/delete on the parent table, but note that the foreign key column of the child table cannot be not NULL
(3) Cascade the matching record of the child table when update/delete the record on the parent table, synchronously Update/delete
You can set different actions according to their needs, for example, we want to delete the parent table automatically deletes the associated values of the child table, we need to set:
References parent (ID) on DELETE cascade on UPDATE cascade, we need to know before the experiment
1. Already defined foreign keys can not be changed, you must delete the foreign key after the row to create (there may be another way, which master to point out)
Mysql> Show CREATE TABLE child/g #得到 The name of the constraint (constraint)
Description: Constraint can be the default, to specify the name of the constraint, if not specified then 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;
This allows us to specify the overall name of the constraint as Fk_child_key, which can then be manipulated.
mysql> ALTER TABLE child drop foreign key fk_child_key; #删除约束
Mysql> ALTER TABLE Child add foreign key (' parent_id)
References parent (ID) on DELETE cascade
ON UPDATE cascade;
Mysql> Show CREATE TABLE child/g #至此约束已更改, users can view the changes
Mysql>delete from parent where id=4; #我们再执行上面的那句, is there anything wrong?
Mysql>select * from parent;
Mysql>select * from child; #我们可以看到现在与parent_id为4的全部删除. Oh, can be easily used in the future
The values in the database are:
Parent Child
ID ID parent_id
2 1 2
3 1 3
6. Additional Instructions for data constraints
If you define a data constraint, the data is slow to insert or change, especially if you change the data structure, and when you insert it, it is inefficient.
When the client executes load data (which is described later), ALTER TABLE recommends that the following command be used to temporarily turn off the data constraint and then turn it on until it is complete, at least 20 times times faster.
Mysql> set foreign_key_checks=0; #关闭数据约束
mysql> load Data infile ' file absolute address ' into table name; #从文本文件中载入大量数据
Mysql> set Foreign_key_checks=1; #打开数据约束
