Mysql authoritative guide reading notes (IV) _ MySQL

Mysql authoritative guide reading notes (4) Chapter 3, MySQL syntax and usage

First, let's look at the types of SQL statements supported by MySQL.
1. database selection, creation, discard, and change
Use
Create database
Drap database
Alter database
2. create, change, and discard data tables and indexes
Create table
Drop table
Create index
Drop index
Alter index
3. retrieve information from a data table
Select
Union
4. transaction processing
Begin
Commit
Rollback
Set autocommit
5. modify the information in the data table.
Delete
Insert
Load data
Replace
Update
6. management commands
Flush
Grant
Revoke

I. naming rules

1MySQL allows system characters in the name.
Add "_" or "$" to any letter or number"
2. length of the name.
Databases, data tables, data columns, indexes, and other names can contain up to 64 letters
256 alias up to 256 letters
3. name qualifier
Depending on different contexts, you sometimes need to add certain restrictions to certain names, such as full limits, partial limits, and unlimited data columns. this is easy to understand.
Select * from db_name.tbl_name...

II. case sensitivity issues in MySQL

Keyword and function name: No difference
Database name data table name: depends on the server host system
Data column name index name: No difference
Alias: Case Sensitive
Generally, no matter whether the system distinguishes between the database name and data table name from uppercase or lowercase letters, we should write these names in the same query statement in the same case and case, this is a very good programming habit.

III. detailed description of data table types supported by MySQL

1. ISAM data table
This is the only table type of MySQL branch before MySQL 3.23. it is outdated and the MyIASM processing process Library gradually replaces the ISAM processing program. this old table type is no longer in use.

2. MyIASM data table
? This is the default data table type used by MySQL. Its advantages are:
? If the host operating system supports large-sized files, the data table length can be very large and more data can be accessed.
? The data table content is independent of the hardware, that is, the data table can be copied between machines at will.
? Improved indexing functions
? Provides better index key compression performance
? Auto_incremnet enhanced capabilities
? Improved the data table integrity check mechanism
? Supports full fulltext search

3. Merge data table
This is a way to organize a MyIASM data table with the same structure into a logical unit.
4. HEAP data table
This is a data table that uses memory, and the length of each data row is fixed. These two features make it very fast to search for such data tables. as a temporary data table, HEAP is useful in certain situations.
5. BDB data table
This type of data table supports transaction processing.
Excellent concurrency performance
6. InnoBDB data table
This is the data table type recently added to MySQL and has many new features.
Support transaction processing mechanism
Immediate recovery after crash
Supports foreign keys, including cascading deletion.
Concurrency
7. storage of such data tables on hard disks
IASM Frm isd ism
MyISAM Frm myd myi
Merge Frm mrg
Heap Frm
BDB Frm db
InnoBDB frm
8. Data Table portability
Common method: export the data table content to a text file, copy it to the destination hard disk, and load it into the database using scripts. this is the first method we should master. However, for file-level operations, some data tables can be copied separately. View the table
ISAM No
MyIASM Yes
BDB No
InnoBDB Yes

IV. Preliminary indexing knowledge
1. indexing is a basic means to accelerate the access performance of data table content. its basic features are as follows:
For data columns that can be indexed separately, you can also construct a composite index containing multiple data columns.
The index can contain duplicate key values.
You can create multiple indexes for a data table.
2. different data tables have different index features and must be treated differently.
3. how to create an index
① Use the alter table command to create an index
② Use the create index command to create an index
③ Create an index in create table

5. change the data table structure
When we find that the structure of a data table cannot meet our requirements, we need to change its structure. the data table may need to store more information than before, or some information in the data table may be useless; or the width of an existing data column may be too narrow... In these cases, the alter language is used.
1. rename the data table
Alter table A rename to B // Data table A is renamed B
Rename table A to B // rename Data table A to B
Rename A toC, B to A, C to A // name of Data Table A and Data Table B
Alter table S. A rename to T. A // move table A in database S to database B
Rename table S. A to T. A // move table A in database S to database B
2. change the data column type.
Now we need to change the data column I of the smallint unsigned type in table A to the mediumint unsigned type again.
Alter table A motify I mediumint unsigned
Alter table A change I mediumint unsigned
Note the feature of the change clause: it can not only change the type of the data column, but also change the name of the data column. This cannot be completed by the modify clause. Next we will rename this data column.
Alter table A change I J mediumint unsigned
3. convert a data table from a variable-length data row to a fixed-length data row.
Sometimes this change is required to improve performance. However, you must use the same alter command to change all data columns at a time. you cannot change only one data column! Example:
Create table A (name varchar (40), address varchar (80 ))
The command we started to modify should be:
Alter table A modify name char (40), modify address char (80 );
4. convert a data table from a fixed-length row to a variable-length row.
If you think the space utilization is not high, you need to switch back. this is very simple and there are no special requirements.
Alter table A modify name varchar (40)
5. convert the data table type
We know that MySQL databases have multiple data table types, but each type has different features.
If you want your data tables to support transaction processing. Then you must convert it into the BDB or innoBDB format.
Alter table A type = BDB
Alter table A type = InnoBDB