Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to operate the datasheet in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

With MySQL, you can currently choose between three basic database table formats. When you create a table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the type of table, indexes and data are stored in other files.

You can use ALTER TABLE statements to transform between different types of tables.

MyISAM

In MySQL 3.23, MyISAM is the default table type, it is based on ISAM code and has many useful extensions. The index is stored in a file with a .MYI (MYindex) extension and the data is stored in a file with a .MYD (MYData) extension. You can use the myisamchk utility to check / repair the MyISAM table.

ISAM

You can also use abandoned ISAM. This will disappear soon, because MyISAM is a better implementation of the same thing. ISAM uses an B-tree index, which is stored in a file with an .ISM extension and the data is stored in a file with an .ISD extension. You can use the isamchk utility to check / repair the ISAM table. ISAM tables are not binary portable across OS/ platforms.

HEAP

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

Display the information of the data table with SHOW/ DESCRIBE statement

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}

You can use db_name.tbl_name as an alternative to tbl_name FROM db_name syntax.

SHOW TABLES lists the tables in a given database. You can also get this table with the mysqlshow db_name command.

Note: if a user does not have any permissions on a table, the table will not be displayed in the output in SHOW TABLES or mysqlshow db_name.

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 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 "_" wildcards of SQL. This statement is provided for compatibility with.

SHOW TABLE STATUS (introduced in version 3.23) runs similar to SHOW STATUS, but provides more information about each table. You can also use the mysqlshow-- status db_name command to get this 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 index information in a format very similar to ODBC's SQLStatistics call.

Use the mysqlshow tool to get information

Here's a brief introduction to the use of the mysqlshow utility, which is very convenient to get information about databases and tables.

Get a list of existing databases:

Shell > mysqlshow

List the tables that already exist 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 index of a table:

Shell > mysqlshow-k db_name tbl_name

Create a data table with a CREATE TABLE statement

Create the table with the CREATE TABLE statement. The complete syntax of this statement is quite complex because there are so many optional clauses, but in practice the application of this statement is quite simple. For example, all the CREATE TABLE statements we used in Chapter 1 are less complex.

Interestingly, most complex things are clauses that MySQL is discarded after analysis. See Appendix 1 to see these complexities.

1. The basic syntax of 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, with or without the current database.

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 all goes well, congratulations, you have built your first table!

The table you create is called guestbook, and you can use this table to store information about visitors to your site. You created 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 properties of the fields enclosed in parentheses, 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 of your site, and the entrydate field stores the date and time when the visitor visited your site.

Notice 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 data type of the field. The data type determines what kind of data a field can store. Because the field comments contains text information, its data type is defined as text.

2. How to specify the type of table

You can also specify the type of table when you create the table. If you do not specify the type of table, the default is ISAM table in 3.22 and earlier, and MyISAM table in 3.23 version. You should try to use the MyISAM table. Specifying the type of table is often used to create an HEAP table:

Mysql > CREATE TABLE fast (id int,articles TEXT) TYPE=HEAP

3. Changes in implied column descriptions

In some cases, MySQL implicitly changes a column description given in a CREATE TABLE statement. (this may also be in ALTER TABLE. VARCHAR whose length is 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 columns. This doesn't affect how you use columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL implements this change because it saves space and makes table operations faster.

The display size of TIMESTAMP must be even and in the range of 2 ~ 14. If you specify a display size of 0 or greater than 14, the size is forced to 14. The odd size from 1 to 13 is forced to the next larger even number.

You cannot store a text in a TIMESTAMP column. NULL; sets it to NULL and sets it to the current date and time. Because this is how the TIMESTAMP column behaves, the NULL and NOT NULL properties are not used in a normal way and will be ignored if you specify them. DESCRIBE tbl_name always reports that the TIMESTAMP column may be assigned a null value.

If you want to know if 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 results 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 just an image of rows and columns scrolling on the display as you work. Prior to MySQL 3.23, if you want to save the results of SELECT in a table for use by future queries, you must make special arrangements:

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

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

In MySQL 3.23, all changes have been made. CREATE TABLE... The SELECT statement eliminates these time-wasting things, making it possible to directly derive a new table from the results of the SELECT query. You can complete the task in one step without having to know or specify the data type of the column being retrieved. This makes it easy to create a table that is completely populated with the data you like and prepares for further queries.

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

Mysql > CREATE TABLE test

-> (an int not null auto_increment,primary key (a), key (b)

-> SELECT BBJ c from test2

This will create a table with three columns (aforce bdiary c), where the data for the bcentary c column comes from the table test2. Note that if any error occurs while copying the data into the table, the table will be deleted automatically.

You can copy a table by selecting the entire contents of a table (no WHERE clause), or create an empty table with a WHERE clause that always fails, 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 load a data file into the original file using LOAD DATA and are not sure if it has the correct data format specified. You don't want to end up with a deformed record in the original table the first time you don't get the right option. Using an empty copy of the original table allows you to experiment with LOAD DATA's options 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.

You can use CREATE TEMPORARY TABLE with SELECT to create a temporary table as a copy of itself, such as:

This allows you to modify the content 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 pre-written scripts with original table names, you don't need to edit these scripts to reference different tables; you just need to add CREATE TEMPORARY TABLE statements at the beginning of the script. The corresponding script creates a temporary copy and operates on it, which is automatically deleted by the server when the script ends.

About how to operate the data table in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.