Shulou(Shulou.com)06/01 Report--

After we have created the database, we can create the corresponding data table, so the creation table also belongs to the DDL data definition level, so let's learn about it.

1. Create a data table

Create instruction: create table tb_name [Table options]

We found that the instructions are the same as the database creation instructions, and there is no big difference.

PS: of course, before we create a table, we first select the appropriate database.

Next, let's create a student information table.

Create table student_studentinfo (

Id int

Name varchar (5)

Class_id int)

Column types and column constraints that are column properties will be discussed later.

Here are a few things to note: when we create the data table, we select the corresponding database, using the following instructions:

Use db_name

At the same time, when creating a data table, we set the prefix for the table in business logic, and there is usually a prefix in mature projects, so why set the prefix? There are mainly the following factors

A. when we have only one database, if there are multiple items in a database, in order to prevent confusion in the management of data tables, we usually set prefixes for identification and management.

B, when there are a lot of data tables, the use of prefixes can be used for rapid retrieval, such as multiple items in a database, such as student information system, student examination system, teacher management system, then the data table can be quickly retrieved through the prefix.

The rules of data table names also follow the rules of identifiers, which will not be discussed here.

So let's create the data table again, and if we don't select the database, we can create it like this:

Create table db_name.tb_name

Last time we talked about creating a database, we will automatically animate the directory named by the database name in the data directory, so we will automatically create some files when we create the data table.

We find that the teacher information table is generated into two files with the suffix .ibd. In fact, these two files are the structure of the data table and the index and data storage files, which are generated by default by the innodb storage engine, so if it is myisam, three files will be generated.

2. View the data table to view the information of the created data table and the structure of the table

Instruction: show tables; show create table tb_name;describle tb_name

Show tables is to see what data tables are under the current database, just like looking at the library.

Show create table tb_name is to view the creation information of the data table.

Describle tb_name also supports the abbreviation desc tb_name.

By the same token, we also support show tables like directives when we view data tables

There will be no testing here.

3. Delete the data table

We can delete unwanted tables in the same way as deleting a database

Delete instruction: drop table tb_name or drop table if exists tb_name

4. Update operation of data table

A. you can update the table name

Rename table old_tbname to new_tbname

At the same time, the instruction also supports cross-database naming.

So using this function, we can complete the database replication and renaming function.

1. First, let's create a database

2. Use the rename instruction to copy all the tables in the replicated database to the new database

Copying multiple tables can be done like this: rename table tb_name1 to db_name.tb_name,tb_name2 to db_name.tb_name2...

Through these two steps, you can complete the replication of the database.

B. the structure of the table can be updated

The main structure of the update table is to add new column definitions

Modify column definition

Rename column definition

Delete column definition

Alter table tb_name add column definition

Modify column name column definition

Change column name New column definition

Drop column name

Rename a column

Update a column

Column deletion is very simple, and there is no demonstration of how to do it here.

C. option information that can be updated for the table

Update the character encoding or storage engine of the table

Alter table tb_name character set coding

Alter table tb_name engine storage engine

Finally, there is the delete operation of the data table. through the previous knowledge, we already know how to delete the database, so deleting the table is the same as deleting the database. There's no demonstration here.

Generally speaking, data tables cannot be recovered after deletion, so it is best to use the drop command with caution.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.