MySQL Fifth study notes the operation of the data table

MySQL creates a table and creates a . frm file to save the table and column definitions. The index is stored in one . MYI (Myindex) and the data is stored in the . MYD (MYData) file extension.

First, using Show/describe statement to display the data table information

Grammar:
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 the tables in a given database. You can also get this form with the Mysqlshow db_name command. of course, when using mysqlshow, you need to access the number-u username-p xx;

SHOW columns lists the columns in a given table.

Note that MySQL sometimes alters the column type , assuming that the column type differs from what you expect to be based on the CREATE TABLE statement.

The describe statement provides information similar to show columns.

Describe provides information about the columns of a table. Col_name can be a column name or a string that includes the "%" and "_" wildcard characters of SQL. This statement is provided for compatibility with Oracle.

show table status (introduced in version number 3.23) performs a similar SHOW status, but provides many additional information for each table. You can also use the Mysqlshow--status db_name command to get this form.

show fields is a synonym for SHOW columns. 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 single table.

show index returns indexing information in a format that is similar to ODBC's sqlstatistics invocation.

ii. Use of mysqlshow tools to obtain information

The following is a brief introduction to how the Mysqlshow utility works. It is convenient to use the information of database and table.

of course, when using mysqlshow, you need to access the number-u username-p xx;

Get a list of existing databases:

shell> mysqlshow

List existing tables in a database db_name:

shell> mysqlshow db_name

Lists the structure information for 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

iii. Creating a data table with the CREATE TABLE statement

1. The 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 the MySQL3.22 or later version number, the table name can be specified as db_name.tbl_name, whether or not the current database is available.

For example, create an interview message sheet:

shell> mysql–u root–p

mysql> CREATE DATABASE mytest;

mysql> CREATE TABLE Guestbook
(
Visitor VARCHAR (40),
-Comments TEXT,
-EntryDate DATETIME
);

Assuming everything is OK, congratulations, you've built your first table.

The table you created is named Guestbook, and you can use this table to store information about your site's visitors. You are creating this table with the Reeate table statement, which has two parts: the first part specifies the name of the table.

The second part is the names and attributes of each field enclosed in parentheses, separated by commas.

Table Guestbook has three fields: Visitor,comments and EntryDate. The visitor field stores the name of the caller, the comments field stores the visitors ' comments about your site, and the EntryDate field stores an interview with your visitors.

The date and time of the site.

Note that each field name is followed by a specialized expression. Like what. The field name comments followed by the expression text.

This expression specifies the data type of the field. The data type determines what a field can store

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

2, how to specify the type of table

You can also specify the type of table when you create the table. Assume that you do not specify a table type. The default is the ISAM table in the 3.22 and previous version numbers, and the default is MyISAM table in the 3.23 version number.

You should try to use the MyISAM table. Specifies that the type of the table has been

Often used to create a heap table:

mysql> CREATE TABLE fast (ID int,articles TEXT) type=heap;

3. Changes in implied column descriptions

In some cases, MySQL implicitly changes the description of a column given in a CREATE TABLE statement. (This may also be in alter TABLE)

 varchar with a length of less than 4 is changed to char.

 assume that in a table no matter what column has a variable length, the result is that the entire row is longer. Therefore, suppose that a table includes a column (varchar, text, or blob) of any length that is greater than 3 characters, all char columns are changed to VARCHAR

Column.

This doesn't affect how you use columns in any way. In MySQL. VarChar is just a different way of storing characters. MySQL implemented such a change. is because it saves space and makes table operations faster.

The display size of the timestamp must be even and within the range of 2 to 14. Suppose you specify a 0 display size or a greater than 14. The size is forced to 14. The odd numeric dimensions from the 1~13 range are coerced to the next larger even number.

 You cannot store a literal null in a timestamp column. Setting it to NULL will be set to the current date and time. Because the timestamp column behaves like this, the null and NOT NULL properties are not used in a general manner and

and assume that you specify them, will be ignored. DESCRIBE Tbl_name always reports that the timestamp column may have been given a null value.

Suppose you want to know if MySQL uses a column type other than what you specify after you create or change your table. Make a describe Tbl_name statement.

4. Create a table with the result of select

An important concept of a relational database is that no matter what data is represented as a table of rows and columns, the result of each SELECT statement is a table of rows and columns. In many cases, the "table" from select is only a

As you work on the display, scroll through the rows and columns of the image. In MySQL 3.23 once, it is assumed that the results of select are saved in a table for subsequent queries, and special arrangements must be made:

1) perform a describe or show COLUMNS query to determine the type of column in the table from which you want to get information.

2) Create a table that clearly specifies the name and type of the column you just looked at.

3) After the table has been created. Publish an Insert ... SELECT query. Retrieve the results and insert them into the table you created.

In MySQL 3.23. All were modified. CREATE TABLE ... The SELECT statement eliminates these wasted time, making it possible to derive a new table directly from the results of a select query.

It takes just one step to complete

Task, you do not have to know or specify the data type of the column being retrieved. This makes it very easy to create a table that is completely populated with the data you like. and was prepared for further inquiries.

 Suppose you specify a select,mysql after the CREATE statement to create a new field for all cells in the Select.

Like what:

mysql> CREATE TABLE test

--(a int not null Auto_increment,primary key (a), key (b))

SELECT b,c from Test2;

This creates a 3 column (A, b). c) Table, where the data from B,c column is from table Test2.

Note that when copying data into a table, no matter what error occurs, the table will be deleted on its own initiative

 Ability to copy a table by selecting all the contents of a table (without a WHERE clause). Or use a WHERE clause that always fails to create an empty table, such as:

mysql> CREATE TABLE Test SELECT * from Test2;

mysql> CREATE TABLE Test SELECT * from test2 where 0;

It is useful to create an empty copy if you want to use load data to load a data file into the original file and not be sure if you have the correct data format specified. You do not want the first time you do not get the correct option to

The record of the deformity in the original table ended. An empty copy of the original table agrees to experiment with the option of load data for a specific column and row delimiter until the interpretation of the input data is comfortable. Once you're comfortable, you'll be able to load data into

The original table.

The Create temporary table and select can be used in conjunction with creating a temporary table as its own copy, such as:

This agrees to change the content of the MY_TBL without affecting the original content.

In the query that you want to experiment with the contents of some alter tables. And you don't want to change the contents of the original table. This is very practical.

In order to use a pre-written script that leverages the original table name. No

You need to edit these scripts to refer to different tables, just add the Create temporary table statement at the beginning of the script.

The corresponding script will create a temporary copy. and operate on this copy. When the script finishes, the

This copy will be deleted by the Service Manager on its own initiative.

To create a table as an empty copy of itself, be able to temporary with the create ... SELECT to use the WHERE 0 clause together. Like what:

However, there are a few things to note when creating empty tables. When you create a table that is populated by selecting data, its column name comes from the column name that you select. If a column is evaluated as the result of an expression, the name of the column is the text of the expression.

An expression

is not a valid column name, you can perform the following query in MySQL to understand this:

To work properly, you can provide a valid nickname for the column:

Suppose you select a column with the same name from a different table. There will be some difficulties. Assume that both the table T1 and T2 have column C, and you want to create a table of all the combinations of rows from two tables. Then you can provide

Name specifies the name of the column that is unique in the new table, such as:

You create a table by selecting the data to populate it and actively copy the index of the original table.

V. Altering the structure of a table with an ALTER TABLE statement

Sometimes you may need to change the structure of an existing table, so the ALTER TABLE statement will be the right choice for you.

 Add columns

ALTER TABLE Tbl_name add col_name type

For example, add a column to the table weight

Mysql>alter table Pet Add weight int;

 Delete Columns

ALTER TABLE tbl_name drop Col_name

For example, delete column weight:

Mysql>alter table Pet Drop weight;

 Change Columns

ALTER TABLE Tbl_name modify Col_name type

Like what. Change the type of weight:

Mysql> ALTER TABLE pet Modify weight samllint;

Another way is to:

ALTER TABLE tbl_name change old_col_name col_name type

Like what:

Mysql> ALTER TABLE Pet change weight weight samllint;

 Renaming a column

mysql>alter Table Pet Change weight wei;

 Renaming the table

mysql>alter table tbl_name rename New_tbl

For example, to rename a pet watch to animal

Mysql>alter table Pet Rename animal;

vi. Deleting a data table with a drop TABLE statement

DROP TABLE [IF EXISTS] tbl_name [, Tbl_name,...]

Drop table deletes one or more database tables.

The Data and table definitions in all tables are deleted, so use this command with caution!

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

Like what:

Mysql>use mytest;

Mysql>drop TABLE Guestbook;

Or, you can specify both the database and the table:

Mysql>drop TABLE Mytest.guestbook;

MySQL Fifth study notes the operation of the data table