"Database" MySQL Database (iii)

One, the index in MySQL:

We have seen the index in the array, and the advantage is that it can be quickly detected by subscript or index;
The ability to quickly locate a message;

What is the index in 1.MySQL?

It is the field that has the index in our table, stored separately in a table (MyISAM storage engine),
When we query the data in the table again, if you search for the condition that is the field that has the index, this will, it
Instead of traversing all the information in the table, go to the Index table and quickly navigate to the data you want to search,
It has a pointer that points to the source information in our data table, so that we can quickly
A database with a large order of magnitude accurately and quickly take out a piece of information;

How the table is stored in the 2.MySQL (MyISAM storage engine);

1. Structure of the FRM data table

2.. Data for MYD data tables

3.. MYI index fields in a data table

What kinds of indexes are there in 3.MySQL?

The index, after we define it, does not have to be used deliberately, when we have indexed fields in the query table
The index will automatically take effect;

1> Normal index (MUL stands for normal index)
Features: Without any restrictions, when we define a normal index, we can use it to search the data directly.

① when creating a table, define a normal index
Create Tabel test1 (
ID int unsigned NOT NULL,
Name varchar (+) is not NULL,
Sex enum (' m ', ' W ') NOT null default ' W ',
Age tinyint NOT null default 18,
Index ID (ID) index type indexed name (field name)
);

② to add a normal index to a field after a table is under construction
Create index ID on test1 (ID);
Create index type index name on table name (field name);

③ How to delete a normal index
Drop index ID on test1;
Drop index type index name on table name;

2> Unique index (unique) (UNI stands for unique index)
Features: A field with a unique index, its value can only appear once, duplicate values will be an error!
At the same time, you can have multiple fields in a table to add unique indexes

① method of creating a unique index when building a table
CREATE TABLE Test1 (
ID int unsigned NOT NULL,
Name varchar (+) is not NULL,
Sex enum (' W ', ' m ') not null default ' m ',
Age tinyint NOT null default 18,
Unique index name (name)//index type index name (field name)
);

② How to create a unique index when building a table two
CREATE TABLE Test1 (
ID int unsigned NOT NULL,
Name varchar (+) NOT NULL unique,//directly adds a unique index to the field
Sex enum (' W ', ' m ') NOT null default ' W ',
Age tinyint NOT NULL default 18
);

③ Add a unique index after building a table
Create unique index ID on test1 (ID);
Create index type index name on table name (field name);

④ method to delete a unique index in a table
Drop index ID on test1;
Drop index type index name on table name;

3> primary key index (primary key)
Characteristics: Its unique index is basically consistent with the method and the characteristics, the only difference is that the unique index in
A table can be defined multiple times, the primary key index can be defined only once, and the primary key index generally we
is added to the ID field

① a method for creating a primary key index when a table is built
CREATE TABLE Test1 (
ID int unsigned NOT NULL auto_increment primary key,//add primary key
Name varchar (+) is not NULL,
Sex enum (' W ', ' m ') not null default ' m ',
Age tinyint NOT NULL default 18
);

② a method for adding a primary key index after a table is built

1.alter table test1 Change ID ID int. unsigned NOT NULL auto_increment primary key;
ALTER TABLE name change field original name segment new name type constraint condition ...;

2.alter table test1 Modify ID int unsigned NOT NULL auto_increment Priamry key;
ALTER TABLE name modify field name type constraint ...;

③ How to delete a primary key index

Because the primary key index is very special, we must first look at the table structure when we delete the primary key index, see the table
The field with the primary key index, whether it has the auto_increment constraint, if any,
Delete the auto_increment constraint before you can delete the primary key index

1. Review the table structure to see if you have auto_increment keywords
DESC table name;

2. If you have a auto_increment keyword, you need to delete the keyword first
ALTER TABLE test1 Modify ID int unsigned NOT NULL;
ALTER TABLE name modify field type constraint condition;

3. Delete PRIMARY key index
ALTER TABLE test1 drop PRIMARY key;
ALTER TABLE name drop PRIMARY key index;

4> Full-Text index

Second, storage engine (understand):

Transaction processing: Sometimes, when you perform an operation, a power outage can cause some unnecessary hassles, such as
Electronic transfer operation, if the power outage at this time, all transaction operations will have a rollback effect, revert to the last
The location where the breakpoint is stored to avoid other problems

1.MyISAM Storage Engine
For the operation of one of our tables, if it is a table with more frequent queries, we use the MyISAM storage engine to
is stored because it does not support transactional operations

2.InnoDB Storage Engine
Because this kind of storage engine supports transaction operation, the increment, delete and change operation of a table is more frequent, it needs
Our tables support transaction processing, which in this way greatly reduces the query speed of the table.

3. Choose what kind of storage engine, the key is the various functions of your project needs the different tables, to choose a
A more appropriate storage engine

4. How to specify the storage engine for a table:

CREATE TABLE Test1 (
ID int unsigned NOT NULL Auto_increment primary key,
Name varchar (+) is not null unique,
Sex enum (' W ', ' m ') not null default ' m '
) Engine=myisam[innodb];


5. How to view the storage engine for a table

Show create table table name;

Third, the encoding format in MySQL:

1. Check the encoding format we can set:

Show character set;

2.4 levels of encoding type in MySQL server

1> Server-level

2> database level

3> data Table level

4> data Field Level

3. One feature of the encoding level:

It has an inherited feature, and when we set the server-level encoding type, we are in the server
All databases, data tables, and data fields created below are followed by the server-level encoding type.

4. How to set an encoding type

1> Setting the encoding type at the server level

Set character_set_server = "UTF8";

2> Setting the encoding type at the database level

① Setting the default encoding type when creating a database
Create DATABASE test default charset= "UTF8";
Create database name default encoding type = "UTF8";

② Modifying the encoding type of a database
ALTER DATABASE test default charset= "UTF8";
ALTER DATABASE name default encoding type = "UTF8";

3> Setting the encoding type at the data table level

① Setting the default encoding type when creating a data table
CREATE TABLE Test (
ID int unsigned NOT NULL auto_increment Priamry key
) Engine=myisam default charset= "UTF8";

② Modifying the encoding type of a data table
ALTER TABLE test default charset= "UTF8";

4> Setting the encoding type for the data field level

① Modifying a data field-level encoding
ALTER TABLE test Modify name varchar (+) Character set "UTF8";

5> setting the encoding format for DOS command boxes
Set names UTF8;

Iv. Modification of table structure

1. Add a table field:
ALTER TABLE TEST1 add name varchar (+) NOT NULL unique;//does not specify a location, the default appears in the last
ALTER TABLE TEST1 add name varchar (+) NOT null unique after id;//specify Add Name field after ID
ALTER TABLE TEST1 add name varchar (+) NOT NULL unique first;//adds the name field at the beginning of the table

2. Modify the table field:
ALTER TABLE test1 modify field type constraint condition ...;

ALTER TABLE test1 change original field name new field name segment type constraint ...;

3. Delete the table field:
ALTER TABLE test1 drop field name;

4. Renaming the table:
ALTER TABLE test1 rename Test2;

5. Actions to delete multiple tables:
drop table name 1, table name 2, table name 3 ...;

Database MySQL Database (iii)