MySQL study Note 5 related data table operations

When creating a table, MySQL creates a. frm file to save the table and column definitions. Indexes are stored in a file with the. MYI (MYindex) Extension and data is stored in a file with the. MYD (MYData) extension.

1. Use the SHOW/DESCRIBE statement to display data table information

Syntax:
Show tables [FROM db_name] [LIKE wild]

Or show columns from tbl_name [FROM db_name] [LIKE wild]

Or show index from tbl_name [FROM db_name]

Or show table status [FROM db_name] [LIKE wild]

{DESCRIBE | DESC} tbl_name {col_name | wild}

Show tables lists TABLES in a given database. You can also run the mysqlshow db_name command to obtain the table. Of course, when using mysqlshow, the parameter-u username-p xx is required;

Show columns lists the COLUMNS in a given table. If the column type is different from what you expect to be based on the create table statement, note that MySQL sometimes changes the column type.

The explain DESCRIBE statement provides information similar to show columns. DESCRIBE provides information about columns in a table. Col_name can be a column name or a string containing the SQL "%" and "_" wildcards. This statement is provided for compatibility with Oracle.

Running show table status (introduced in version 3.23) is similar to show status, but more information is provided for each TABLE. You can also run the mysqlshow -- status db_name command to obtain the table.

Show fields is a synonym for show columns, and show keys is a synonym for show index.

You can also use mysqlshow db_name tbl_name or mysqlshow-k db_name tbl_name to list the columns or indexes of a table.

 Show index returns the INDEX information in a format similar to that called by ODBC SQLStatistics.

Ii. Use the mysqlshow tool to obtain information

The following describes the usage of the mysqlshow utility. It is very convenient to get the database and table information. Of course, when using mysqlshow, the parameter-u username-p xx is required;

Obtain the list of existing databases:

Shell> mysqlshow

List existing tables in a database db_name:

Shell> mysqlshow db_name

List the structure information of a database table db_name.tbl_name:

Shell> mysqlshow db_name tbl_name

List the indexes of a table:

Shell> mysqlshow-k db_name tbl_name

3. Use the create table statement to CREATE a data TABLE

1. Basic syntax of the create table statement

Create table tbl_name (create_definition,...) [TYPE = table_type]

Create_definition: col_name type [not null | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [primary key]

In MySQL 3.22 or later versions, the table name can be specified as db_name.tbl_name, regardless of whether the current database exists.

For example, create a visitor message table:

Shell> mysql-u root-p

Mysql> create database mytest;

Mysql> create table guestbook
-> (
-> Visitor VARCHAR (40 ),
-> Comments TEXT,
-> Entrydate DATETIME
-> );

If everything is normal, congratulations! You have created your first table!

The name of the table you created is guestbook. You can use this table to store the information of your site visitors. You created this TABLE using the reeate table statement. This statement has two parts: the first part specifies the name of the TABLE;

The second part is the names and attributes of fields enclosed in brackets, which are separated by commas.

The table guestbook has three fields: visitor, comments, and entrydate. The visitor field stores the visitor's name, the comments field stores the visitor's opinion on your site, and the entrydate field stores the visitor's access to you.

The date and time of the site.

Note that each field name is followed by a special expression. For example, the field name comments is followed by the expression TEXT. This expression specifies the field data type. The data type determines what a field can be stored.

. Because the field comments contains text information, its data type is defined as text type.

2. How to specify the table Type

You can also specify the table type when creating a table. If the table type is not specified, the ISAM table is used by default in versions 3.22 and earlier, and the MyISAM table is used by default in version 3.23. You should try to use the MyISAM table. Specify the table Type

It is often used to create a HEAP table:

Mysql> create table fast (id int, articles TEXT) TYPE = HEAP;

3. Implicit column description changes

In some cases, MySQL implicitly modifies a column description in a create table statement. (This may also be in alter table)

The VARCHAR with the limit length less than 4 is changed to CHAR.

If any column in a table has a variable length, the result is that the entire row is variable length. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns larger than three characters will be changed to VARCHAR.

Column. This does not affect how you use columns in any way. in MySQL, VARCHAR is only a different method for storing characters. MySQL implements this change because it saves space and makes table operations faster.

The display size of  TIMESTAMP must be an even number and must be 2 ~ Within the range of 14. If you specify 0 display size or greater than 14, the size is forced to be 14. From 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number.

You cannot store a text NULL in a TIMESTAMP column; if it is set to NULL, it is set to the current date and time. Because the TIMESTAMP column is like this, the NULL and not null attributes are NOT used in general mode and

If you specify them, they will be ignored. DESCRIBE tbl_name always reports that the TIMESTAMP column may be assigned a NULL value.

If you want to know whether MySQL uses a column type other than the one you specified, issue a DESCRIBE tbl_name statement after creating or changing your table.

4. Create a table using the SELECT result

An important concept of relational database is that any data is expressed as a table composed of rows and columns, and the results of each SELECT statement are also a table composed of rows and columns. In many cases, the "table" from SELECT is only one

As you work on the display, the scrolling rows and columns of the image. Before MySQL 3.23, if you want to save the SELECT results in a table for future query, you must make special arrangements:

1) Run DESCRIBE or show columns query to determine the column type in the table from which you want to obtain information.

2) create a table and specify the name and type of the column you just viewed.

3) after creating the table, publish an INSERT... SELECT query, retrieve the results, and INSERT them into the created table.

All changes have been made in MySQL 3.23. The create table... SELECT statement eliminates these time-consuming tasks, so that a new TABLE can be obtained directly using the results of the SELECT query. You can do this in one step.

Task, you do not have to know or specify the Data Type of the retrieved column. This makes it easy to create a table that is fully filled with your favorite data and prepares for further queries.

If you specify a SELECT statement after the CREATE statement, MySQL will CREATE a new field for all cells in the SELECT statement. For example:

Mysql> create table test

-> (A int not null auto_increment, primary key (a), key (B ))

-> SELECT B, c from test2;

This will create a table with three columns (a, B, c), where the data of Column B and column c comes from Table test2. NOTE: If any errors occur when copying data into the table, the table will be automatically deleted.

You can select all the contents of a table (without the WHERE clause) to copy a table, or use a WHERE clause that is always failed to create an empty table, for example:

Mysql> create table test SELECT * from test2;

Mysql> create table test SELECT * from test2 where 0;

If you want to use load data to LOAD a DATA file into the original file, and are not sure whether the specified DATA format is correct, it is useful to create an empty copy. You do not want

The malformed records in the original table end. Using the empty copy of the original table allows you to experiment with the load data option for specific columns and row delimiters until you are satisfied with the interpretation of the input DATA. After satisfaction, you can load the data

The original table is created.

You can use create temporary table and SELECT to CREATE a temporary table as its own copy, for example:

This allows you to modify the content of my_tbl without affecting the original content. This is useful when you want to experiment with queries that modify table content, but do not want to change the original table content. To use a pre-written script using the original table name, do not

You need to edit these scripts to reference different tables. You only need to add the create temporary table statement at the beginning of the script. The script creates a temporary copy and performs the copy operation.

The server automatically deletes the copy.

To CREATE a table as its own empty copy, you can use the WHERE 0 clause with create temporary... SELECT. For example:

Note the following when creating an empty table. When you create a table that is filled by data selection, its column names come from the selected column names. If a column is calculated as the result of an expression, the column "name" is the text of the expression. Expression

The column name is not legal. You can run the following query in mysql to learn about this:

To work properly, a legal alias can be provided for this column:

If you select columns with the same names from different tables, it may be difficult. Assume that both table t1 and table t2 have column c, and you want to create a table from all the combinations of rows in the two tables. So we can provide different

Name indicates the unique column name in the new table, for example:

Create a table by selecting data for filling and the index of the original table is automatically copied.

5. Use the alter table statement to modify the TABLE structure

Sometimes you may need to change the structure of the existing Table, so the Alter Table statement will be your proper choice.

Add columns to a sequence

Alter table tbl_name add col_name type

For example, add a weight column to the table.

Mysql> alter table pet add weight int;

 Delete column

Alter table tbl_name drop col_name

For example, to delete a weight column:

Mysql> alter table pet drop weight;

Modify column

Alter table tbl_name modify col_name type

For example, to change the weight type:

Mysql> alter table pet modify weight samllint;

Another method is:

Alter table tbl_name change old_col_name col_name type

For example:

Mysql> alter table pet change weight samllint;

Rename a column by alias

Mysql> alter table pet change weight wei;

Rename a table

Mysql> alter table tbl_name rename new_tbl

For example, rename the pet table to animal

Mysql> alter table pet rename animal;

6. Use the drop table statement to delete a data TABLE

Drop table [if exists] tbl_name [, tbl_name,...]

Drop table: delete one or more database tables. The data and table definitions in all tables are deleted. Therefore, use this command carefully!

In MySQL 3.22 or later versions, you can use the keyword if exists to avoid an error that does not exist in the table.

For example:

Mysql> USE mytest;

Mysql> drop table guestbook;

Alternatively, you can specify both the database and table:

Mysql> drop table mytest. guestbook;