SQL Learning Notes------------datasheet

Work with data tables

Basic Database Operations

With MySQL, currently (version 3.23) You can choose between three basic database table types. 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 save the table and column definitions. Depending on the type of table, indexes and data are stored in other files.

You can use the ALTER table statement to transform between tables of different types.

myisam

MyISAM is the default table type, which is based on ISAM code and has many useful extensions. The index is stored in a. MYI (myindex) extension file and the data is stored in a file that has a. 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 disappear soon, because MyISAM is a better realization of the same thing. ISAM uses a B-tree index, which is stored in a. ISM extension file and the data is stored in a file with a. ISD extension, you can check/repair the ISAM table using the Isamchk utility. The ISAM table is not
Binary portability across os/platforms.


HEAP

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

Displaying data table information with the Show/describe statement

Syntactic:
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 an alternative to the tbl_name from db_name syntax.

SHOW tables lists the tables in a given database. You can also use the Mysqlshow db_name command to get this form.

Note: If a user does not have any permissions on a table, the table will not appear in the output in show TABLES or Mysqlshow db_name.

SHOW COLUMNS lists the columns in a given table. If the column type differs from what you expect to be based on the Crea TE table statement, be aware that MySQL sometimes alters 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 STA TUS (introduced in version 3.23) runs like SHOW STA TUS, but provides more information on 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 indexing information in a format that is very similar to the ODBC SQLStatistics call.

Use the Mysqlshow tool to get 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.

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

Creating a data table with the CREATE TABLE statement

Create a table with the CREA TE table statement. The complete syntax for this statement is quite complex, because there are so many
Optional clauses, but in practice the application of this statement is fairly straightforward.
Interestingly, most of the complexities are clauses, and these clauses are thrown out of MySQL after analysis.

1. Basic syntax for 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 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 Reea TE table statement, which has two parts: the first part specifies the name of the table, and 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. Visitor field stores the name of the visitor
Word, the Comments field stores the visitor's opinion of your site, and the EntryDate field stores the day that visitors visit your site
Period and time.

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

mysql> CREA TE 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 CREA TE 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 that are greater than 3 characters are changed to VARCHAR columns. 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 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 applied in a general way and are ignored if you specify them. 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.

Create a table with 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 each SELECT

The result of the statement is also a table that consists of a row and a column. In many cases, the "table" from select is just an image of the rows and columns that are scrolled along with your work on the display. 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. CREA TE TABLE ... The SELECT statement eliminates these wasted time, making it possible to derive a new table directly from the results of a select query. You can complete the data type that you know or specify the columns you are retrieving in a single step. 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 CREA TE statement, the new field will be created for all cells in the SELECT. For example:

mysql> CREA TE 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> CREA TE TABLE Test SELECT * from Test2;
mysql> CREA TE TABLE Test SELECT * from test2 where 0;

Creating an empty copy is useful if you want to use the load DA TA to load a data file into the original file and not be sure if you have the correct data format specified. You don't want to end up with a malformed record in the original table when you don't get the right option for the first time. 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. Once you're satisfied, you can use the data
Loaded into the original table.
You can use CREA TE temporary table and SELECT to create a temporary table as a copy of itself, 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 pre-written scripts that utilize the original table name, you do not need to edit these scripts to reference different tables, just add the CREA TE emporary table statement at the beginning of the script. The corresponding script creates a temporary copy and operates on the copy, which is automatically deleted by the server at the end of the script.
To create a table as an empty copy of itself, you can temporary with the create ... SELECT to use the WHERE 0 clause

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. 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 legitimate nickname for this column: If you select a column with the same name from a different table, you will have 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 an alias
Specifies the name of the column that is unique in the new table, such as creating a table by selecting the data to populate and automatically copying the index of the original table.

modifying the structure of a table with the ALTER TABLE statement

Sometimes you might need to change the structure of an existing table, so the Alter table statement will be the right choice for you.

Add column
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 column
ALTER TABLE tbl_name drop Col_name
For example, delete column weight:
mysql>alter table Pet drop weight;

Change column
ALTER TABLE tbl_name modify Col_name type
For example, change the types of weight:
mysql> ALTER TABLE pet Modify weight SAMLL int
Another method is:
ALTER TABLE tbl_name change old_col_name col_name type
For example:
mysql> ALTER TABLE pet Change weight we Ight Samllint;

Renaming the column
Mysql>alter table pet Change weight wei;

Rename the table
ALTER TABLE tbl_name rename NEW_TBL
For example, rename the pet table to animal
Mysql>alter table Pet Rename animal;

Change the type of table
Additionally, you can add or remove properties such as indexes for columns

Delete 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;

Summarize
This section describes most of the operations on tables, and now summarizes the following:

Three types of MySQL tables

How to create a table, delete a table

How to change the structure and name of a table

How to use the Mysqlshow utility

SQL Learning Notes------------datasheet