MySQL notes: create and operate tables

MySQL notes: create and operate tables
Create and manipulate tables

In the previous study, although we haven't systematically introduced how to create and manipulate tables, we still use a little bit. For example, to create a student table, including the student ID, name, and score, you can run the following command;

create table student(      id int(10) not null,      name varchar(10) not  null,      score int(10) null);

We sometimes want to set the student ID to auto-increment as follows:

create table student4( id int(10) not null auto_increment, name varchar(10) not null, score int(10) null, primary key(id) );

Note: If you want to set the id to auto_increment, you must set the id to the primary key. Otherwise, an error is returned, as shown below:

The above is a common way to create a table when operating the database. The following describes how to create a table in the system.

Table creation Basics

To create a table using create table, the following information must be provided:

1. Name of the new table, which is given after the keyword "create table. 2. Table column names and definitions, separated by commas.

The following is an example:

<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4NCjxwPjxjb2RlIGNsYXNzPQ = "hljs SQL"> As you can see above, it indicates that after the create table keyword, all columns are enclosed in parentheses, and columns are separated by commas, the definition of each column starts with the column name (which must be unique in the table), followed by the column data type. The data type can be seen in the help create table command line.

Note: When creating a new table, the specified table name must not exist. Otherwise, an error is returned. If you only want to create a table when the table does not exist, the if not exists. in this way, before creating a table, the MySQL database will first check whether the table name you want to create exists. As long as the table name exists, no matter whether the structure of the table you create is the same or not.

The above involves some keywords (null, primary key, auto_increment, etc.), which are described one by one below.

Null keyword

For columns with not null modifier, a value must be given when insert into is inserted. For columns with null modifier, if insert into is used to insert data, the value of this column is not given.

When we do not use the null/not null modifier for a column, the default value is null.

We should not confuse null values with empty strings. Null is a null value. It is not an empty string. If ''is specified, this is valid in the not null column.

Primary key keywords

Primary key (id) is to set the id column as the primary key. Of course, we can also set the primary key when creating the table, or set the primary key after creating the table.

The primary key value must be unique.If only one column is set as the primary key, duplicate flag is not allowed in this column. If multiple columns are set as primary keys, the combination of these columns must be unique.

For example, if id = 5 already exists in the table and we want to insert another data with id = 5, an error is returned.


auto_increment

The auto_increment keyword indicates that the id is automatically increased.
When we use insert into to insert data, we can choose not to specify a value for the id because of the automatic growth of the id. We can also choose to give the id a value (This value must be unique), and then the automatic growth of id is based on the maximum id value.

As follows:


Primary Key and null value: the primary key uniquely identifies the columns of each row in the table. Only columns that do not allow null values can be used in the primary key. Columns that allow null values cannot be uniquely identified.

Default Value: default

When creating a table, if we specify default for a column, when we use insert to insert data, if we do not specify a value for this column, it is filled with the default value.

The usage is as follows:

Update table

In the last two weeks of learning, I tried to add a new column to an existing table, but I don't know how to do it, this problem is solved by creating a new table.

Now that we have learned this, let's study it together.

We need to use less to update the table structure. We should consider the structure of the table when creating the table so that we can not make major changes to the table later.

To use alter table to change the table structure, the following information must be provided:

1. Name of the table to be changed after alter table (this table must exist; otherwise, an error is reported) 2. List of changes.

Add a new column

What should we do if we want to add a column (tel) for human_information?

Alter table human_information add tel varchar (10); // add a new column

Delete a column

If we want to delete the tel column added above, what should we do ??

As follows:

alter table human_information drop column tel;

Set primary key

As mentioned above, in addition to setting the primary key when creating a table, you can also set the primary key after creating the table. The method is as follows:

alter table customers add primary key(cust_id);

Set Foreign keys

The preceding section describes how to set a primary key for a table and a foreign key.

For example, the cust_id In the MERs table can be used as the foreign key in the orders table.

Alter table orders add constraint fk_orders_mers MERs foreign key (cust_id) references MERs (cust_id); // set a foreign key for table orders

With the above base for setting the primary key and foreign key, we can continue to practice how to set the primary key and foreign key, as shown below:

Delete table

In the previous study, we already know how to delete table content. For example, if we want to delete a row in the student table, we can do this:

Delete from student where name = 'Z'; // delete the data whose name is z in the student table.

To delete all the contents in the student table, you can use the following two methods:

1、delete from student ;

2、truncate student；

No matter which method we select to delete the table content, the table will not be deleted, that is, the table structure is still there, but the data is gone.

What should we do if we want to delete the student table ??

As follows:

drop table student;

Note: make a complete backup before deleting the table to prevent deletion errors.

Rename a table

The following describes how to delete a table and how to rename a table:

Rename table student to student_rename; // rename student to student_rename;

Summary

The following describes how to create and operate a table. Finally, let's review:

1. Run the following command to create a student table:

create table student( id int(10) not null auto_increment, name varchar(10) not null, sex varchar(10) not null default 'male', score int(10) null, primary key(id) );

The preceding table creation statement basically covers all the knowledge points in the table creation statement, such as null/not null, auto_increment, and primary key;

2. Change the table structure

Alter table student add mathScore int (10); // add a new alter table student drop column mathScore for the student table; // Delete the mathScore column

3. Set the primary key after creating the table

alter table student add primary key(id);

4. Set Foreign keys

Alter table orders add constraint fk_orders_mers MERs foreign key (id) references MERs (id); // set a foreign key for orders

5. delete a table

drop table student;

6. rename a table

rename table student to new_student;

The above is all the content for creating and operating tables.