Mysql basic database creation article
- 2020-05-09 19:29:06
- OfStack
1. Create data tables -- basics (skipped by the master)
Orthodox method: create [TEMPORARY] table table name [if not exists]
(created column definition)
[table options]
[options for partitioning]; Please refer to the mysql manual for the establishment of the orthodox way. For the specific parameters, please refer to the mysql manual.
Ex. :
Copy database structure:
Copy and copy database
In order to create a variety of user requirements to create the database, here is just to do a lead.
2. View the modified data table structure
3. Further change the structure of the database
Let's first create a table
mysql > create table vip(id int null,username varchar(30));
Modify the data types in the table structure
Add fields to existing tables
mysql > alter table add sex enum('M','F') default 'M' not null after id; The new sex field is of type enum, after id.
Rename an existing field
mysql > alter table vip change sex tinyint default 0 not null after username; Rename the field sex to usersex and change the type and location.
Note: modify and change are similar in alter only, but modify cannot be renamed, only the structure can be changed, but change can be renamed or the type can be changed.
Delete the field
mysql > alter table vip drop usersex; Delete the usersex field, warning, all data for this field will be lost.
Database renaming
mysql > alter table vip rename to vip_user; Rename the database.
Change the data table engine
mysql > alter table vip engine="MyISAM";
Note that modifying the data structure is a very dangerous thing to do, and it is best to make a backup in case of accidents.
There are also some alter statements and tips that we will cover in the coming 11...
4. About data constraints
Data constraints are getting better and better supported in mysql5, but existing data constraints are limited to innodb, and legend has it that mysql5.2 will also support data constraints (expect...)
First of all, let's understand what data constraints are, because we usually create tables in which there may be information related to each other, and data constraints are a kind of tie that connects two tables.
For example, there are two tables,1 usertype,1 userid, 1 keyword key in usertype for the user type number, and 1 user_key in userid for the usertype table
1. First we need to make sure that all the values in userid are in usertype
2. Secondly, we need to ensure that the value key in usertype is changed. The value user_key in userid table is also changed.
3. Values in usertype cannot be deleted at will unless values of the usertype type do not exist in the userid table, or all usertype values in userid are deleted if forced deletion is required.
If there are no data constraints, insert/update may need several statements at a time to ensure the correct integrity of the data. If we use data constraints, we only need to do 1 processing at the time of definition without worrying too much. And most importantly, the use of data constraints can ensure the integrity of data and business.
Ha ha, say so many, also did not say the disadvantage of data constraints: slow, use the data constraints are much slower than do not use the data constraints, and each time the user to insert data or change the data, the database system will spend 1 time for 1 set of tests. But as the mysql matures, the speed will be greatly improved.
Personally, I think it is necessary to use data constraints when non-commercial, real-time systems require high data business integrity. In other cases, it's a matter of opinion.
5. Concise analysis of data constraints
Therefore, the definition of foreign keys must satisfy the following three conditions:
1. Both tables must be of type innodb
2. Columns specified as foreign keys must be indexed
3. The foreign key types associated in the two tables must match.
Let's look at the examples first, and learn from them:
mysql > create table parent(id int null,primary key(id)) engine=innodb; Create 1 main 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 1 from the table, and about the foreign key keyword parent_id, establish the correlation between them.
mysql > insert into parent values (1), (2), (3); Insert data into the main table
mysql > insert into child values (1, 1), (1, 2), (1, 3); Insert data into the subtable, and the id values of parent_id subchild corresponding to different parent_id subchild are all 1;
mysql > # insert into child values (1, 1), (1, 2), (1, 3), (1, 4); So let's take a look at 1 and what happens here? Error reporting, right? What's the reason? You think about
Note: since we agreed when we created the statement that the data is foreign key related, there is no primary key with id value of 4 in parent, so of course the subkey cannot be updated.
The value in the database is:
parent child
id id parent_id
1 1 1
2 1 2
3 1 3
We continue the operation to reflect the role of the foreign key association table
mysql > update parent set id=4 where id=1; So let's change the value of parent and see what child does
mysql > select * from parent;
mysql > select * from child;
The value in the database is:
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, while the associated value of child will automatically change. We continue to
mysql > insert into child values (2, 4), (3, 4), (4, 4); Add some other values to the child table.
mysql > #delete from parent where id=4; So let's go ahead and see what happens in 1. Error? Let's analyze 1 for errors
Let's review 1 of the details and key statements we created for the foreign key:
foreign key(parent_id) # means to specify parent_id as the external key of this table;
references parent(id) on delete on on cascade# is a constraint statement. references can constrain the foreign key of this database, that is, parend_id corresponding to the id subkey of parent data table, and restrict the operation of on delete,on update. mysql has the following operations:
(1) restrict, no action means that if there are matching records in the child table, update/delete operation on the corresponding candidate key of the parent table is not allowed. Do you understand now? Why do we execute delete from parent where id=4; , because there are still values in the child table (child).
(2) when set null records update/delete on the parent table, set the column of the matching record on the child table to null, but note that the foreign key column of the child table cannot be not null
(3) when cascade records update/delete on the parent table, update/delete synchronizes the matching records of the child tables
You can set different actions according to your own needs. For example, if we want to automatically delete the associated value of the child table when the parent table is deleted, we need to set:
references parent(id) on delete cascade update update cascade, we need to know before we do the experiment
1. The foreign key that has been defined and set can't be changed any more. It must be created after deleting the foreign key.
mysql > show create table child/G # gets the name constraint (constraint)
Note: constraint can be used by default to specify the name of the constraint. 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;
So we specify the overall name of the constraint as fk_child_key, and we can do something with that later.
mysql > alter table child foreign key fk_child_key; # delete constraint
mysql > alter table child add foreign key ('parent_id)
references parent(id) on delete cascade
on update cascade;
mysql > show create table child/G # now that the constraint has changed, the user can see 1 of the changes
mysql > delete from parent where id=4; Let's execute the above sentence again. Are there any errors now?
mysql > select * from parent;
mysql > select * from child; We can now see the full deletion of parent_id for 4 with parent_id. Ha ha, later can be convenient to use
The value in the database is:
parent child
id id parent_id
2 1 2
3 1 3
6. Additional specification of data constraints
If data constraints are defined, data can be inserted or changed slowly, especially when changing data structures, which can be horribly inefficient when inserting data.
When the customer executes load data (loading data, to be covered later), alter table recommends the following command to temporarily close the data constraint and open it after completion, so that the speed can be increased by at least 20 times.
mysql > set foreign_key_checks = 0; # close data constraints
mysql > load data infile 'absolute file address' into table table name; Load a lot of data from a text file
mysql > set foreign_key_checks = 1; Open data constraints
Orthodox method: create [TEMPORARY] table table name [if not exists]
(created column definition)
[table options]
[options for partitioning]; Please refer to the mysql manual for the establishment of the orthodox way. For the specific parameters, please refer to the mysql manual.
Ex. :
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:
create [temporary] table The name of the table [if not exists] like The existing table name ;// Model existing table creation 1 Two tables with exactly the same structure
mysql>create table vip_user like user;
mysql>create table vip_user select * from user where 0;// You can also do this in the example above , Copy only structure
Copy and copy database
create [temporary] table The new name of the table select * from The old name of the table ;# The user can manually specify the follow-on select Compose the required statements.
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 requirements to create the database, here is just to do a lead.
2. View the modified data table structure
mysql> desc The data table name ;# View the structure of the data table
mysql> show create table The data table name \G # View the statements that make up the data table ,\G and ; Same meaning, except \G It's vertical, so you can see it more clearly.
mysql> show table status like ' The data table name '\G # View the status of the data table
mysql> show columns from The data table name ;# View the structure of the data table , with desc1 Like, but add after this statement like ' field ' You can display only the specified fields
3. Further change the structure of the database
Let's first create a table
mysql > create table vip(id int null,username varchar(30));
Modify the data types in the table structure
mysql>alter table vip modify id smallint default 1;# Change the data record to 1.
mysql>#alter table vip modify id smallint auto_increment;# We perform 1 This sentence will prompt an error, because mysql The column that requires automatic growth is set as the primary key
mysql>alter table vip add primary(id);# set vip The primary key of the value is id, Execute this statement and then execute the above statement.
mysql>alter table vip modify username char(30) not null;# change vip the username for char(30);
mysql>alter table vip modify username mediumtext first; # change vip In the username, And I'm going to put it in the front field, and there's also 1 One is after A field is placed after a field
Add fields to existing tables
mysql > alter table add sex enum('M','F') default 'M' not null after id; The new sex field is of type enum, after id.
Rename an existing field
mysql > alter table vip change sex tinyint default 0 not null after username; Rename the field sex to usersex and change the type and location.
Note: modify and change are similar in alter only, but modify cannot be renamed, only the structure can be changed, but change can be renamed or the type can be changed.
Delete the field
mysql > alter table vip drop usersex; Delete the usersex field, warning, all data for this field will be lost.
Database renaming
mysql > alter table vip rename to vip_user; Rename the database.
Change the data table engine
mysql > alter table vip engine="MyISAM";
Note that modifying the data structure is a very dangerous thing to do, and it is best to make a backup in case of accidents.
There are also some alter statements and tips that we will cover in the coming 11...
4. About data constraints
Data constraints are getting better and better supported in mysql5, but existing data constraints are limited to innodb, and legend has it that mysql5.2 will also support data constraints (expect...)
First of all, let's understand what data constraints are, because we usually create tables in which there may be information related to each other, and data constraints are a kind of tie that connects two tables.
For example, there are two tables,1 usertype,1 userid, 1 keyword key in usertype for the user type number, and 1 user_key in userid for the usertype table
1. First we need to make sure that all the values in userid are in usertype
2. Secondly, we need to ensure that the value key in usertype is changed. The value user_key in userid table is also changed.
3. Values in usertype cannot be deleted at will unless values of the usertype type do not exist in the userid table, or all usertype values in userid are deleted if forced deletion is required.
If there are no data constraints, insert/update may need several statements at a time to ensure the correct integrity of the data. If we use data constraints, we only need to do 1 processing at the time of definition without worrying too much. And most importantly, the use of data constraints can ensure the integrity of data and business.
Ha ha, say so many, also did not say the disadvantage of data constraints: slow, use the data constraints are much slower than do not use the data constraints, and each time the user to insert data or change the data, the database system will spend 1 time for 1 set of tests. But as the mysql matures, the speed will be greatly improved.
Personally, I think it is necessary to use data constraints when non-commercial, real-time systems require high data business integrity. In other cases, it's a matter of opinion.
5. Concise analysis of data constraints
Therefore, the definition of foreign keys must satisfy the following three conditions:
1. Both tables must be of type innodb
2. Columns specified as foreign keys must be indexed
3. The foreign key types associated in the two tables must match.
Let's look at the examples first, and learn from them:
mysql > create table parent(id int null,primary key(id)) engine=innodb; Create 1 main 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 1 from the table, and about the foreign key keyword parent_id, establish the correlation between them.
mysql > insert into parent values (1), (2), (3); Insert data into the main table
mysql > insert into child values (1, 1), (1, 2), (1, 3); Insert data into the subtable, and the id values of parent_id subchild corresponding to different parent_id subchild are all 1;
mysql > # insert into child values (1, 1), (1, 2), (1, 3), (1, 4); So let's take a look at 1 and what happens here? Error reporting, right? What's the reason? You think about
Note: since we agreed when we created the statement that the data is foreign key related, there is no primary key with id value of 4 in parent, so of course the subkey cannot be updated.
The value in the database is:
parent child
id id parent_id
1 1 1
2 1 2
3 1 3
We continue the operation to reflect the role of the foreign key association table
mysql > update parent set id=4 where id=1; So let's change the value of parent and see what child does
mysql > select * from parent;
mysql > select * from child;
The value in the database is:
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, while the associated value of child will automatically change. We continue to
mysql > insert into child values (2, 4), (3, 4), (4, 4); Add some other values to the child table.
mysql > #delete from parent where id=4; So let's go ahead and see what happens in 1. Error? Let's analyze 1 for errors
Let's review 1 of the details and key statements we created for the foreign key:
foreign key(parent_id) # means to specify parent_id as the external key of this table;
references parent(id) on delete on on cascade# is a constraint statement. references can constrain the foreign key of this database, that is, parend_id corresponding to the id subkey of parent data table, and restrict the operation of on delete,on update. mysql has the following operations:
(1) restrict, no action means that if there are matching records in the child table, update/delete operation on the corresponding candidate key of the parent table is not allowed. Do you understand now? Why do we execute delete from parent where id=4; , because there are still values in the child table (child).
(2) when set null records update/delete on the parent table, set the column of the matching record on the child table to null, but note that the foreign key column of the child table cannot be not null
(3) when cascade records update/delete on the parent table, update/delete synchronizes the matching records of the child tables
You can set different actions according to your own needs. For example, if we want to automatically delete the associated value of the child table when the parent table is deleted, we need to set:
references parent(id) on delete cascade update update cascade, we need to know before we do the experiment
1. The foreign key that has been defined and set can't be changed any more. It must be created after deleting the foreign key.
mysql > show create table child/G # gets the name constraint (constraint)
Note: constraint can be used by default to specify the name of the constraint. 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;
So we specify the overall name of the constraint as fk_child_key, and we can do something with that later.
mysql > alter table child foreign key fk_child_key; # delete constraint
mysql > alter table child add foreign key ('parent_id)
references parent(id) on delete cascade
on update cascade;
mysql > show create table child/G # now that the constraint has changed, the user can see 1 of the changes
mysql > delete from parent where id=4; Let's execute the above sentence again. Are there any errors now?
mysql > select * from parent;
mysql > select * from child; We can now see the full deletion of parent_id for 4 with parent_id. Ha ha, later can be convenient to use
The value in the database is:
parent child
id id parent_id
2 1 2
3 1 3
6. Additional specification of data constraints
If data constraints are defined, data can be inserted or changed slowly, especially when changing data structures, which can be horribly inefficient when inserting data.
When the customer executes load data (loading data, to be covered later), alter table recommends the following command to temporarily close the data constraint and open it after completion, so that the speed can be increased by at least 20 times.
mysql > set foreign_key_checks = 0; # close data constraints
mysql > load data infile 'absolute file address' into table table name; Load a lot of data from a text file
mysql > set foreign_key_checks = 1; Open data constraints