MySQL database technology (14) [group chart] _ MySQL

MySQL database technology (14) [group chart] 3.4 create, delete, index, and change tables

You can use the c r e ate ta B L E, DROP TABLE, and A LTER TABLE statements to create tables, delete them, and change their structure. For each of these statements, MySQL exclusive extensions make each statement more useful. The c r e ate index and drop index statements enable you to add or delete indexes on an existing table.

3.4.1 create table statement

Use the create table statement to CREATE a TABLE. The complete syntax of this statement is quite complex, because there are so many optional clauses, but in reality the application of this statement is quite simple. For example, all the create table statements we use in chapter 1st are not that complex. Interestingly, most of the complicated things are clauses that MySQL will discard after analysis. See Appendix D to see these complex things. CHECK the terms of the create table Statement. note how many syntaxes are used in the references constraint and CHECK clauses. These clauses involve external keys, integrity of references, and input value constraints. MySQL does not support these functions, but it analyzes its syntax to make it easier to use the table definitions created in other database systems. (This code can be used more easily with less editing work .) If you write your own table description from the beginning, you can ignore these clauses. We will not introduce them much in this section. Create table should at least indicate the TABLE name and the list of columns in the TABLE. For example:

In addition to the columns that make up the table, you can also explain how to index the table when creating the table. Another option is to create a table without indexing, and then add an index later. If you plan to fill the table with a large amount of data before you start using the table for query, it is a good way to create an index later. Updating indexes when each row is inserted is much slower than loading data to an unindexed table and then creating indexes. We have introduced the basic syntax of the c r e ate table statement in chapter 1st, and discussed how to describe the column type in chapter 2nd. It is assumed that you have read these two chapters, so we will not repeat them. In this section, we will introduce some important extensions of the c r e ate table statement in MySQL 3.23. these extensions provide great flexibility in TABLE construction. These extensions are:

■ Table storage type description.

■ Create a table only when the table does not exist.

■ The temporary table is automatically deleted when the client session ends.

■ Create a table by selecting the data you want to store in the table.

1. table storage type description

Before MySQL 3.23, all tables created by users use the ISAM storage method. In MySQL 3.23, you can specify TYPE = type after the list of columns in the create table statement, and CREATE a TABLE with three types. The type can be MYISAM, ISAM, or HEAP. For example: