MySQL Learning note Five about data table operations

MySQL when creating a table, create a . frm file to save the table and column definitions. The index is stored in a . MYI (myindex) file extension and data is stored in the . MYD (MYData) extension in the file.

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 use the Mysqlshow db_name command to get this form. of course, when using mysqlshow, you need to connect the parameter-u user name-P xx;

SHOW columns lists the columns in a given table. If the column type differs from what you expect from the CREATE TABLE statement, be aware that MySQL sometimes changes the column type .

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 contains the "%" and "_" wildcard characters of SQL. This statement is provided for compatibility with Oracle.

show table status (introduced in version 3.23) runs like SHOW STATUS, but provides more information for each table. You can also use the Mysqlshow--status db_name command to get this table.

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 very similar to the ODBC SQLStatistics call.

ii. Use of mysqlshow tools to obtain information

The following is a brief introduction to the usage of the Mysqlshow utility, which is very convenient to use in obtaining the information of the database and the table. of course, when using mysqlshow, you need to connect the parameter-u user name-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. Basic syntax for 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 MySQL3.22 or later versions, the table name can be specified as db_name.tbl_name, whether or not the current database is available.

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 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 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 visitor's name, the comments field stores the visitor's comments on your site, the EntryDate field stores the visitor's access to your

The date and time of the site.

Note that each field name is followed by a specialized expression. For example, 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 contains 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, and if you do not specify a table type, the ISAM table defaults to 3.22 and previous versions, and the default is MyISAM table in version 3.23. 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.

 If any column in a table has a variable length, the result is that the entire row is longer. Therefore, if a table contains any variable-length columns (VARCHAR, text, or BLOB), all char columns greater than 3 characters are changed to VARCHAR

Column. This does not affect how you use columns in any way; in MySQL, varchar is just a different way of storing characters. MySQL implements this change 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. If you specify 0 display size or larger than 14, the dimension 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, and set it to null 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 if you specify them, they will be ignored. DESCRIBE Tbl_name always reports that the timestamp column may have been given a null value.

If you want to know if MySQL is using a column type other than what you specified, issue a describe Tbl_name statement after you create or change your table.

4. Create a table with the result of select

An important concept of a relational database is that any data is represented as a table of rows and columns, and 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. Prior to MySQL 3.23, special arrangements must be made if you wanted to save the results of a select in a table for future queries to use:

1) Run the 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 explicitly specifies the name and type of the column you just looked at.

3) After you create the table, publish an INSERT ... SELECT queries, retrieve the results, and insert them into the table you created.

In MySQL 3.23, all of the changes were made. 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 one step to complete

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

 If you specify a select,mysql after the Create statement, the new field will be created for all cells in the Select. For example:

mysql> CREATE TABLE test

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

SELECT b,c from Test2;

This creates a table with 3 columns (A,B,C), where the data for the B,c column comes from the table test2. Note If any errors occur while copying the data into the table, the table is automatically deleted

 You can copy a table by selecting the entire contents of a table (without a WHERE clause), or by using 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;

Creating an empty copy is useful 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 allows you to experiment with the option of load data for specific column and row delimiters until you are satisfied with the interpretation of the input data. After you are satisfied, you can load the 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 allows you to modify the contents of the MY_TBL without affecting the original content. This is useful when you want to experiment with queries that modify the contents of a table without changing the contents of the original table. In order to use a pre-written script that uses 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 creates a temporary copy and operates on the copy, and when the script finishes, it

This copy is automatically deleted by the service Manager.

To create a table as an empty copy of itself, you can temporary with the create ... SELECT uses the WHERE 0 clause together, for example:

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 run the following query in MySQL to understand this:

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

If you select a column with the same name from a different table, there will be some difficulty. Assume that both tables 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 and automatically copy the index of the original table.

V. Modifying 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 the column weight:

Mysql>alter table Pet Drop weight;

 Change Columns

ALTER TABLE Tbl_name modify Col_name type

For example, change the type of weight:

Mysql> ALTER TABLE pet Modify weight samllint;

Another approach is to:

ALTER TABLE tbl_name change old_col_name col_name type

For example:

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 table 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 versions, you can use the keyword if exists class to avoid an error that does not exist on the table.

For example:

Mysql>use mytest;

Mysql>drop TABLE Guestbook;

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

Mysql>drop TABLE Mytest.guestbook;