MySQL study notes Day fifth

Using partitioned data tables:
Partitioned data tables and merge data tables have similar effects, but partitioned data tables are really a data table
, unlike merge, which lists the logical relationships of a data table, and the partitioned data table can include a MyISAM
Data Sheet.
To create a partitioned data table:
CREATE table gives data columns and indexes, and then uses partition by to define a data row to allocate
partition function to each partition: [Divide the data table into four zones]

Create Tablelog_partition (dtdatetime  not NULL, Infovarchar( -) not NULL,Index(DT)) partition byRange Year(DT)) (Partition P0ValuesLess Than ( .), partition P1ValuesLess Than ( .), partition P2ValuesLess Than ( -), Partition P3ValuesLess than MaxValue);

To partition the MaxValue again
Alter  log_partition REORGANIZE partitionintovalues less than ( 　　values less than (thevalues less than MaxValue); 

Partitions are included in subdirectories of the database to which the partition data table belongs, requiring the use of the data_directory and index_dirctory partitioning options;

Federated (league) engine
Access to other hosts ' data tables managed by another MySQL server

Delete data table:
drop table table_name1,table_name2,...]
If you use the drop table if exist table_name when you are unsure whether the deleted database exists, it is more secure to use if exist when creating the script
to delete the temporary data table plus the TEMPORARY keyword: dro P temporary table table_name;
CREATE INDEX:
Unique index: Do not allow duplicate values for index entries (PRIMARY,UNQIUE)
Normal index: Allow indexes to duplicate values
Fulltext INDEX: Full-text index, only suitable for MyISAM engine
　　　　　　　　Spatial indexes can only be used with the MyISAM data table, only for not NULL properties, all the columns involved are indexed
Hash (hash index): This is the default index type of memory, but the memory index can also use btree;
Hash is the exact lookup, compare to find the time to use btree better
use ALTER TABLE and CREATE INDEX to add an index to an existing data table
ALTER TABLE table_name ADD [Index,unique,prima Ry Key,fulltext,spatial] Index_name (index_columns)      ps:index_name is optional
If an index is primary Key and spatial, the index must be a NOT NULL property
If you want to limit values that cannot be duplicated alone, you can create a primary key or a unique index
(1) But each data table has only one primary key, And unique can have more than
(2) Primary key cannot include a null value, but unqie can, and can make multiple null values
In addition to primary key, the other indexes almost all can use the CREATE INDEX statement: CREATE INDEX index_name on table_name (index_name) (only one index can be created at a time)
Define the index when creating the table:

Create Table  int,index[index_name](ID)); for primary Key and Unqie can be:createtableintprimarykey [unqie] );

The default index for Menory is hash (hash index), but a range comparison with memory data table (ID<100) creates btree better than hash

Create Table  int,index  using Btree (ID)) engine=memory;

To edit an index on a string prefix, the syntax is columns (n) n is the preceding n characters

 create  table   Tb_name (name  char  (30 ) not  null  ,adress  binary  (60 ) not  null  ,  index  (Name (10   index  (Adress (15  

Only prefix indexes are created for BLOB and text data columns
Rebuild Index:
Repair table table_name Quick;
View index:
Show index from table_name or show keys from table_name PS: View index_name
To delete an index:
Drop INDEX index_name on Tb_name
ALTER TABLE Tb_name DROP INDEX Index_name