MySQL index, character encoding, table structure

First, Index:

is to improve the search performance only when we have a very large amount of data, the index can show its advantages!
Note: The index, we added after, do not deliberately go to use it, it will automatically take effect

1. General index: No limit, normal index
1> creating a normal index when a table is under construction
CREATE TABLE T1 (
ID int unsigned NOT NULL,
Name varchar (32),
Index ID (ID)//Index index name (field name)
);

2> Adding a normal index to a table field after a build
Create index ID on t2 (ID);
Create index type index name on table name (field name);

3> Delete a normal index in a table
Drop index ID on T2;
Drop index type index name on table name;

2. Unique index (unique)
A data column that defines a unique index, the contents of which must be guaranteed not to be duplicated, and if duplicate content is present, an error will be made!

1> defining a unique index when creating a table
CREATE TABLE T1 (
ID int unsigned NOT NULL,
Name varchar (32),
Unique index name (name)//index type index name (field name)
);

2> defining a unique index when creating a table 2
CREATE TABLE T1 (
ID int unsigned NOT NULL,
Name varchar (unique//) writes the index name after the specified field directly in the construction table
);

3> Add a unique index after table creation
Create unique index name on T1 (name);
Create index type index name on table name (field name)

4> to delete a unique index
Drop index name on T1;
Drop index index name on table name;

3. Primary key index (primary key)
A primary key index is similar to a unique index, and its data column must be guaranteed to be unique, unlike a unique index, which is defined only once in a table

1> Creating a table is adding a primary key index
CREATE TABLE T1 (
ID int unsigned NOT NULL Auto_increment primary key,
Name varchar (32)
);

2> index of the primary key after the construction of the table
Because the primary key index is special, it can only occur once in one table, so adding a primary key index after the table is built using the following method
ALTER TABLE T7 change ID ID int. unsigned NOT NULL auto_increment primary key;
ALTER TABLE name change the original field type (length) constraint condition;

3> Deleting a primary key index
① first, let's see if there's a auto_increment in your table.

② If there is one, remove it first
ALTER TABLE name change ID-ID int unsigned NOT NULL;

② Finally, you can delete the primary key index
ALTER TABLE name drop PRIMARY key;

4. Full-Text Indexing
Users can search for words or phrases without using pattern matching operations. A full-text index is an index of type fulltext in MySQL, but can only be used in myiasm tables and can be created on columns of char, varchar, or text type, or on one or more columns of data. This is a special kind of index.
1> adding a full-text index when creating a table
CREATE TABLE Books (
BookID Int (ten) not NULL auto_increment,
BookName varchar () NOT NULL,
Detail text NOT NULL,
Fulltext (detail),
Primary KEY (BookID)
);
2> queries the records of ' hello ' that appear in the detail field, in order of relevance from highest to lowest.
Select Match (detail) against (' Hello ') from books;

Second, the storage engine
1. We need to analyze what type of project we have and then choose which storage engine to use

1> myisam//because it does not support transactional processing, does not occupy disk space, so the access speed'm going aunt we execute the query is relatively high frequency, the use of MyISAM such a storage engine

2> innodb//can support transactions, take up disk space, reduce access speed because it is secure, so we recommend using the InnoDB storage engine when performing some risky operations, modifying, deleting

2. View the current storage engine we are using

Show variables like ' default_storage_engine ';
+------------------------+--------+
| variable_name | Value |
+------------------------+--------+
| Default_storage_engine | MyISAM |
+------------------------+--------+
1 row in Set, 1 Warning (0.00 sec)

3. Specifying the storage engine when the table is under construction
Mysql> CREATE TABLE T8 (
-ID int unsigned NOT NULL Auto_increment primary key,
-Name varchar (+) NOT null unique
) engine=innodb[myisam];//here is where the storage engine is specified

4. How to view the table storage engine
Show create table table name, view build Table statement

Three, character encoding
To view the encoding of the current server or database, use \s to

1> Server-level

2> database level

3> data Table level

4> data Field Level

If you want to make the data in the database is not garbled, you must ensure that the above four levels of encoding unity is UTF8

We can set the encoding, exactly how many kinds of

Show character set;

Note: Since we MySQL, encoding settings are hereditary, inheritance, so we only set the server-level encoding set to UTF8, then regardless of his database, data table, data fields will inherit UTF8 encoding

1. How to set the server-level encoding

Set character_set_server = ' encode ';

2. Database-level encoding to set

given encoding at creation time: Create database name default charset = ' UTF8 ';

Modify Database encoding: Set character_set_database = ' encoding ';

3. Data table-level encoding to set

given encoding when creating a data table:
CREATE TABLE T9 (
ID int unsigned NOT NULL AUTO_INCR Ement primary KEY,
name varchar (+)
) Engine=myisam default charset=utf8;//Setting the location of the data table encoding

Modify Table encoding: ALTER TABLE T9 charset= ' UTF8 ';

4. Set the encoding for the data field level

must be a string type for the data to be encoded

ALTER TABLE T9 change name name varchar (+) character SE T ' UTF8 ';


Four, modify table structure

1. Add a field

ALTER TABLE name add field name segment Type (length) constraint condition;
ALTER TABLE Stu Add Aihao set ("Dabolang", "Xiaobolang");
ALTER TABLE Stu Add xiaoming varchar (+) after name;
ALTER TABLE stu add SID int unsigned NOT NULL: First

Note: When we add a field, we can specify where it is added, and after or first to control the default position after the last field

2. Delete a field

ALTER TABLE name drop field name;

Note: Delete field Yes, keyword is drop instead of delete this should be noted

Note again: Drop is a keyword when deleting a database or data table or data table structure, delete is a keyword for deleting data in a data table

3. Modifying fields

ALTER TABLE name change original field name new field name Type (length) constraint condition;
For example: ALTER TABLE stu change classid classname varchar (+) default ' lamp149 ';

ALTER TABLE name modify the original field name type (length) constraint condition;
For example: ALTER TABLE Stu modify classname varchar (+) default ' lamp149 ';

Note: Use the Change keyword to modify the field name at the same time, but modify does not have a rename effect, only the data type and constraints can be modified

4. Modify the table name

ALTER TABLE name rename new table name;
For example: ALTER TABLE Stu rename STU1;

MySQL index, character encoding, table structure