MySQL Database summary

Engine

To view the MySQL default engine:
Show variables like '%storage_engine% ';

To view the table engine:
Show table status from database name;

Modify the Table engine
ALTER TABLE name ENGINE=INNODB;

Define the engine directly at creation time
CREATE table name () Engine=innodb;

Coding

View the string encoding in the database:
Show variables like ' character% ';

To modify the encoding of tables in the database:
ALTER TABLE name convert to character set UTF8;

To view the encoding of a table in the database (displays the complete build statement)
Show CREATE TABLE table name

Specify the character set of the database when you create the database:
Create database name character set UTF8;

Specify the encoding format of the data table when you create the data table:
CREATE TABLE Tb_books (
Name varchar () is not NULL,
Price double is not NULL,
Bookcount int NOT NULL,
Author varchar () NOT NULL) default CharSet = UTF8;

To modify the field encoding format:
ALTER TABLE < table name > change < Field name > < Field name > < type > Character set UTF8;

Change and delete

Increase:
Insert into database name values (content)
INSERT INTO Database name (field) values (content)

Delete:
Delete from table name where field

Rewrite:
Update table name set change content (name=1) where id=1

Check:
SELECT * FROM table name

Modify Table Structure
ALTER TABLE name change old field name new field name segment type

Index

1. General Index
2. Unique index
3. Full-Text Indexing
4. Single-Column indexing
5. Multi-column index
6. Spatial index
7. Primary KEY Index
8. Combined Index

Normal index: Accelerated query only
Unique index: Accelerated query + column value unique (can have null)
Primary key index: Acceleration query + column Value unique + only one in table (cannot have null)
Composite index: Multi-column values make up an index that is specifically used to combine searches that are more efficient than index merges
Full-Text Indexing: Word segmentation for text content, search

To create a table + index:
CREATE TABLE Table name (
Nid int not NULL auto_increment primary key,
Name varchar (+) is not NULL,
Email varchar (+) NOT NULL,
Extra text,
Index name (field name)
)

Create an index
Create index index name on table name (field name)

To delete an index:
Drop index name on table name;

View Index
Show index from table name

To modify the database root password:
Mysql> Set password for user name @localhost = password (' new password ');

Mysqladmin-u username-p Old password password new password

Permissions
Create User (Authorization)
Grant permissions on the database. Table to ' user name ' @ ' login host ' [indentified by ' user password '];

Revoke permissions
Remove permissions on the database. Table from ' User name ' @ ' login host;

To view permissions:
Show grants;//Himself
Show grants for user name @ host name;

MySQL Database summary