SQL Study Notes (14) ------------ data table, SQL statement for creating a data table

SQL Study Notes (14) ------------ data table, SQL statement for creating a data table

Operations on Data Tables

Basic Database Operations

With MySQL, you can choose between the three basic database table formats (version 3.23. When creating a table, you can tell MySQL which table type it should use for the table. MySQL will always create a. frm file to save the table and column definitions. Indexes and data are stored in other files, depending on the table type.

You can use the alter table statement to convert different types of tables.

 MyISAM

MyISAM is the default table type. It is based on the ISAM code and has many useful extensions. Indexes are stored in a file with the. MYI (MYindex) Extension and data is stored in a file with the. MYD (MYData) extension. You can use the myisamchk utility to check/repair the MyISAM table.

 ISAM

You can also use the abandoned ISAM. This will soon disappear because MyISAM is a better implementation of the same thing. ISAM uses a B-tree index, which is stored in. files with the ISM extension and the data is stored in. in the ISD extension file, you can use the isamchk utility to check/repair the ISAM table. The ISAM table is not
Binary portability across operating systems/platforms.
Bytes

HEAP

HEAP tables use a hashed index and are stored in memory. This makes them faster, but if MySQL crashes, you will lose all the stored data. HEAP is available as a temporary table!

Use SHOW/DESCRIBE statements 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 sta tus [FROM db_name] [LIKE wild]
{DESCRIBE | DESC} tbl_name {col_name | wild}

You can use db_name.tbl_name as another option of tbl_name FROM db_name syntax.
Bytes
Show tables lists TABLES in a given database. You can also run the mysqlshow db_name command to obtain the table.

Note: If a user does not have any permissions for a table, the table is not displayed in the output of show tables or mysqlshow db_name.
Bytes
Show columns lists the COLUMNS in a given table. If the column type is different from what you expect to be based on the crea te table statement, note that MySQL sometimes changes the column type.
Bytes
The 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. Show table sta tus (introduced in version 3.23) runs similar to show sta tus, but provides more information for each TABLE. You can also run the mysqlshow -- status db_name command to obtain the table.
Bytes
Show fields is a synonym for show columns, and show keys is a synonym for show index.
Bytes
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.
Bytes
Show index returns the INDEX information in a format similar to that called by ODBC SQLStatistics.

Use mysqlshow to obtain information
The following describes the usage of the mysqlshow utility. It is very convenient to get the database and table information.

 

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

Use the create table statement to CREATE a data TABLE

Use the crea te table statement to create a TABLE. The complete Syntax of this statement is quite complex, because there are so many
But in practice, the application of this statement is quite simple.
Interestingly, most of the complicated things are clauses that MySQL will discard after analysis.

1. Basic syntax of the create table statement

Crea te 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 reea te table statement. This statement has two parts: the first part specifies the name of the TABLE, and the second part is the name and attribute of each field in brackets, 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 comments of visitors on your site, and the entrydate field stores the day when visitors visit your site.
Period and time.

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 the data that a field can store. 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. The specified table type is often used to create a HEAP table:

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

3. Implicit column description changes

In some cases, MySQL implicitly modifies a column description in a crea te table statement.

(This may also be in alter table. )
Bytes
VARCHAR with a length less than 4 is changed to CHAR.
Bytes
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 3 characters will be changed to VARCHAR columns. 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.
Bytes
The display size of TIMESTAMP must be an even number and be in the range of 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.
Bytes
You cannot store a text NULL in a TIMESTAMP column; setting it to NULL will be set to the current date and time. This is because the TIMESTAMP column is like this. The NULL and not null attributes are NOT used in general mode. 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.

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 each SELECT

Statement results are also a table composed of rows and columns. In many cases, a "table" from SELECT is only an image of rows and columns that scroll along with your work on the display. 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 crea te 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 only need to know or specify the Data Type of the retrieved column in one step. 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 crea te statement, MySQL creates a new field for all cells in the SELECT statement. For example:

Mysql> crea te 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 copy a table by selecting all the content of a table (without the WHERE clause), or create an empty table by using a WHERE clause that is always failed, for example:

Mysql> crea te table test SELECT * from test2;
Mysql> crea te table test SELECT * from test2 where 0;

If you want to use load da ta 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 to end with a malformed record in the original table when you do not get the correct option for the first time. 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 you are satisfied, you can
The original table is loaded.
You can use crea te 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 script written in advance using the original table name, you do not need to edit these scripts to reference different tables; you only need to add the crea te emporary table statement at the beginning of the script. The corresponding script creates a temporary copy and performs the copy operation. When the script ends, 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.

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. The expression is not a legal column name. You can run the following query in mysql to learn about this:
To work properly, you can provide a valid alias for this column: If you select columns with the same name from different tables, it will 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. The alias can be provided.
Specify the unique column name of the new table. For example, you can create a table by selecting data to fill in and the index of the original table is automatically copied.

 Alter table statement

Sometimes you may need to change the structure of the existing Table, so the Alter Table statement will be your proper choice.
Bytes
Add Column
Alter table tbl_name add col_name type
For example, add a weight column to the table.
Mysql> alter table pet add weight int;
Bytes
Delete column
Alter table tbl_name drop col_name
For example, to delete a weight column:
Mysql> alter table pet drop weight;
Bytes
Change 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;
Bytes
Rename a column
Mysql> alter table pet change weight wei;
Bytes
Rename a table
Alter table tbl_name rename new_tbl
For example, rename the pet table to animal
Mysql> alter table pet rename animal;
Bytes
Change table Type
In addition, you can add or delete attributes such as indexes to or from a column.

 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;

Summary
This section describes most of the operations on tables. The following is a summary:
Bytes
Three types of MySQL tables
Bytes
How to create and Delete tables
Bytes
How to change the table structure and name
Bytes
How to Use the mysqlshow Utility