The syntax of MySQL and how to use it

Let's first look at the classification of SQL statements supported by MySQL,
1,                 database selection, create, 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,                 retrieve information
from the data table select
union
4,               transactions
begin
commit
rollback
set   autocommit
5,                 modifies the information in the data table
delete
insert
load   data
replace
update
6,                 management command
flush
grant
revoke
One, the naming rule
1MySQL allows system characters to be used in names.
Add "_"   or   "$"
to any alphanumeric number Length of name.
Database, data table, data column, index, etc. Name up to 64 letters
256 aliases have a maximum of 256 letters
3. Name qualifier
Depending on the context, it is sometimes necessary to add a restriction to certain names: full qualified, partial qualified, and unbounded data columns, for example select   *   from   db_name.tbl_name...
Second, the case problem in MySQL
Keyword and function name: no difference
Database name data table name:
according to the server host system Data column name index name: does not distinguish
Alias: case sensitive
In general, it is a good programming practice to write the names in the same query in the same case regardless of whether the system is sensitive to the case in the database name and the case in the table name.
Third, MySQL supports the name of the data table type
1,ISAM data table
This is the only MySQL branch table type before version 3.23. As it has passed, the MyIASM processor library has gradually replaced the ISAM processor. No one is using
for this old table type
2,MyIASM data sheet
The & # 8226;                 this is the data table type currently used by MySQL by default. The advantage is
The & # 8226;                 if the host operating system supports large size files, the table size can be large and more data can be accessed The & # 8226;                 data table contents are independent of the hardware which means that you can copy the data table from machine to machine at will
The & # 8226;                 The & # 8226;                 offers better index key compression
The & # 8226;               auto_incremnet The & # 8226;                 improved the integrity checking mechanism for data tables
The & # 8226;                 supports fulltext full-text search

3. Merge data sheet
This is a way of organizing MyIASM data tables of the same structure into a logical unit 4,HEAP data sheet
This is a table that USES memory, and the length of each row is fixed. These two features make this type of table very fast to retrieve. As a temporary table, HEAP is useful in certain situations.
5,BDB data sheet
This data table supports the transaction processing mechanism

has good concurrency performance 6,InnoBDB data table
This is the latest data table type to be added to MySQL, with many new
features Support for transaction processing mechanism

recovers immediately after a crash Support for foreign key functionality, including cascading to remove
With concurrent function
This data table on the hard disk of the file storage mode
IASM                 Frm   isd   ism
MyISAM                 Frm   myd   myi
Merge                 Frm   mrg
Heap                 Frm
BDB                 Frm   db
InnoBDB                 frm
Portability of data tables
General method: export the contents of the data table to a text file, then copy to the destination hard disk, in the use of scripts loaded into the database, this is the first method we should master. But for file-level operations, some data tables can be copied separately. Watch the
ISAM                 No
MyIASM                 Yes
BDB                 No
InnoBDB                 Yes

Four, the preliminary knowledge of the index
1. Indexes are the basic means to speed up the performance of data table contents access. The basic feature is
A composite index
with multiple data columns can be constructed for individual data columns that can be indexed The index can contain the duplicate key value
Multiple indexes
can be created for a data table 2.
should be treated differently when different data tables have different index features 3. How to create index
Create the index
with the alter   table command Create the index
with the create   index   command Create index
at create   table  
Five, change the structure of the data table
When we find that the structure of a data table cannot meet our requirements, we need to change its structure. It could be that some of the information in the table is not used; Maybe one of the existing data columns is too narrow... In these cases, alter   is used to mean
1, rename the data table
alter   A   rename   to   // data sheet A was renamed B
rename   table A A to B         // data sheet A was renamed B
rename     toC,B     alter   A               // A in database S moves to
in database B rename     S A         // S A S A 2, change the type of data column
We are now going to change the smallint   unsigned data column I again to   mediumint   unsigned  
in the data table A alter   table   A   motify   I   mediumint   unsigned
alter   table   A   change   I   I   mediumint   unsigned
Note the feature of the change clause: not only can you change the type of data column, you can also change the name of the data column. This is what the modify clause cannot do. I'm going to rename this data column.
alter   table   A   change   I   J   mediumint   unsigned
3. Change the data table from variable length data rows to fixed length data rows
Sometimes this is necessary to improve performance, but there is one caveat: you must change all the columns at once with the same alter command, not just one! Here's an example:
create   table   A(name   varchar(40),address   varchar(80))
When we start to change the command, it should be:
alter   table   A   modify   name   char(40),modify   address   char(80);
4. Change the data table from fixed length data rows to variable length data rows
If you feel like you're not using the space very well, then you need to switch back, which is pretty easy, there's no
requirement alter   table   A   modify   name   varchar(40)
5. Convert data table type
We know that there are many table types in the MySQL database, but each type has different characteristics.
If you want your tables to support transaction processing. Then you have to make it BDB or innoBDB
alter   table   A   type=   BDB
alter   table   A   type=   InnoBDB