Mysql syntax and guidelines for its use _mysql

First look at the classification of SQL statements supported by MySQL
1, database selection, creation, discard and change
Use
Create Database
Drap Database
ALTER DATABASE
2, data table and index creation, change and discard
CREATE table
drop table
CREATE index
Drop Index
Alter index
3, retrieving information from the datasheet
Select
Union
4, transaction processing
Begin
Commit
Rollback
Set autocommit
5, modify the information in the datasheet
Delete
Insert
Load data
Replace
Update
6, Management command
Flush
Grant
Revoke
One, naming rules
1MySQL the system characters allowed in the name.
Any alphanumeric plus "_" or "$"
The length of the 2-name word.
Database, datasheet, data column, index, etc. name up to 64 letters
256 aliases up to 256 letters
Qualifier for 3-name characters
Depending on the context, it is sometimes necessary to add a limit to some names: the full, partially qualified, and unrestricted data columns. It's easier to understand.
SELECT * FROM Db_name.tbl_name ...
Second, the case in MySQL problem
Keyword and function name: no difference
Database name data table name: Based on server host system
Data column name Index name: no difference
Aliases: Distinguishing case
In general, regardless of whether the system distinguishes between the database name and the letter case in the data table name, we should write the names in a consistent letter case in the same query statement, which is a very good programming practice.
Three, MySQL supported the name of the type of data table types detailed
1,isam data table
This is the only table type of MySQL support prior to version 3.23, and MYIASM processing threading gradually replaces the ISAM handler, which has been used by the old type of table.

2,myiasm data table
This is the current data table type used by MySQL by default. Its advantage is
• If the host operating system supports large size files, the length of the datasheet can be large enough to allow more data to be satisfied.
• Data table content is independent of hardware that is, you can copy data tables between machines randomly
• Improved indexing capabilities
• Provides a better index key compression effect
auto_incremnet Capacity Enhancement
• Improved integrity check mechanism for data tables
• Support for Fulltext full text Search

3,merge data table
This is a method of organizing the MYIASM data table of the same structure into a logical unit.
4,heap data table
This is a data table with memory, and the length of each data row is fixed, these two characteristics make this type of data table retrieval speed very fast, as a kind of temporary data table, heap in some specific circumstances is useful.
5,BDB data table
This data table supports transaction processing mechanisms
With good concurrency performance
6,INNOBDB data table
This is the recently added MySQL datasheet type, with many new features
Support for transaction processing mechanisms
Can recover immediately after a crash
Support for foreign key features, including cascading deletions
Has concurrent functionality
7 How the data table is stored on the hard disk
Iasm Frm ISD ISM
MyISAM Frm myd myi
Merge Frm MRG
Heap FRM
BDB FRM DB
Innobdb frm
8 Portability of data tables
General method: The content of the data table is exported to a text file, and then copied to the destination hard disk, in the script loaded into the database, this is the first way we should master. However, in the case of file-level operations, some data tables can be copied separately. Look at the table.
ISAM No
Myiasm Yes
BDB No
Innobdb Yes

Iv. preliminary knowledge of the index
1, the index is to speed up the content access performance of the data table basic means, its basic characteristics:
You can also construct a composite index that contains multiple data columns in order to index a separate data column
Indexes can contain duplicate key values
Multiple indexes can be established for a datasheet
2, different data tables have different indexing characteristics to use when the need to be treated differently
3, how to create an index
① to create an index with the ALTER TABLE command
② CREATE index with the CREATE INDEX command
③ creates an index on CREATE table

Five, change the structure of the data table
When the structure of a data table is found to be unable to meet our usage requirements, it is necessary to make changes to its structure. This data table may require more information than before, or it may be that some of the information in this datasheet is useless; perhaps an existing data column is too narrow ... In these cases, alter language is used evenly.
1, rename the datasheet
ALTER TABLE A rename to B//datasheet a renamed to B
Rename Table A to B/datasheet a renamed to B
Rename a toc,b to A,c to a//data table A and datasheet B interchange name
ALTER TABLE S.A Rename to T.A//database S inside a move to database B
Rename Table S.A to T.A//database S inside a move to database B inside
2, change the type of data column
Now we're going to change the data column I of a smallint unsigned type in datasheet a again to the Mediumint unsigned type
ALTER TABLE A motify I mediumint unsigned
ALTER TABLE A change I i mediumint unsigned
Note the characteristics of the change clause: not only can the type of data column be changed, but also the name of the data column can be changed. This is not done by the Modify clause. Here's the name of the data column.
ALTER TABLE A change I J mediumint unsigned
3, converting a datasheet from a variable-length data line to a fixed-length data row
There are times when you need to make this transition to improve performance, but one thing to note is that you have to change all the columns of data at once with the same alter command, not just one data column! Examples are as follows:
CREATE TABLE A (name varchar, address varchar (80))
We start to modify the command should be:
ALTER TABLE A Modify name char (+), modify address char (80);
4, converting data tables from fixed-length data rows to variable-length data rows
If you think the space utilization is not high, then need to change back again, this is very simple, no special requirements
ALTER TABLE A modify name varchar (40)
5, converting data table types
We know that there are many types of data tables in the MySQL database, but the characteristics of each type are not the same.
If you want your datasheet to support the transaction processing mechanism. Then you have to make it into a bdb or innobdb format.
ALTER TABLE A type= BDB
ALTER TABLE A type= INNOBDB