One, MySQL commonly used commands:
1. Connect to the database: mysql>mysql-uroot-p Enter, then password
Mysql-h 192.168.0.200-p 3306-u root-p
2. mysql>status; View Run Environment information
3. Show all databases: show databases;
4. Switch the database connection syntax: Use MyDatabase;
5. Display all tables in the database: show tables;
6. Display all information when a table is created: show CREATE TABLE table_name;
7. View the specific property information of the table and the description of the fields in the table: DESC table_name;
8. Import SQL file
1 Link Data 2 Select database 3 source/data/tmp/sql.txt
Second, MySQL commonly used SQL modification statements (we take the blog_archive table as an example):
1, create the database: creating databases database_name;
2. Create data table: Creation table blog_archive (aid int unsigned auto_increment,
CID smallint unsigned NOT null default 0,
Title varchar (+) NOT null default ' ',
Contents text NOT null default ' ',
Click Mediumint unsigned NOT NULL default 0,
Primary key (AID),
Key CID (CID)
) Engine=myisam default Charset=utf8;
3. Add a field:
ALTER TABLE blog_archive add column sort smallint NOT null default 0 after Contens;
ALTER TABLE blog_archive add column Is_del tinyint (1) not null default 1;
4. Delete a field:
ALTER TABLE blog_archive drop column Is_del;
ALTER TABLE blog_archive drop Is_del; You can also do this without adding a column
5. Modify the Field name and field type:
ALTER TABLE blog_archive Change click Click_num smallint NOT null default 0;
ALTER TABLE blog_archive change click_num click_num mediumint NOT null default 0;
6, modify the table name: ALTER TABLE blog_archive rename to blog_article;
7. Modify the table engine: ALTER TABLE blog_archive ENGINE=INNODB
7, empty a table: Truncate TABLE blog_archive;
Third, MySQL index-related statements
1. Add an index:
1. Add PRIMARY key (primary key index) Mysql>alter table ' table_name ' Add PRIMARY KEY (' column ') 2. Add unique (unique index) mysql>alter table ' table_name ' Add UNIQUE (' column ') 3. Add index (normal index) mysql>alter TABLE ' table_name ' Add index index_name (' column ')
Mysql>create index index_name on ' table_name ' (' column ') 4. Add fulltext (full-text index) mysql>alter table ' table_name ' add Fulltext (' column ') 5. Add multi-column index mysql>alter TABLE ' table_name ' Add index index_name (' Column1 ', ' column2 ', ' column3 ')
6. View index: Show index from blog_archive;
7. Create multi-column indexes
CREATE TABLE Tomener (
ID int unsigned NOT NULL auto_increment,
Name varchar (TEN) NOT null default ' ',
Age smallint unsigned NOT null default 0,
Primary key (ID),
index Index1 (name,age)
) Engine=myisam default Charset=utf8;
8. Create a full-text index
Fulltext Index INDEX2 (name)
9. Delete Index
Drop index name on table name
iv. MySQL Common SQL query statements
1. Filter duplicate data When querying: Select DISTINCT title from Blog_archive where aid > LIMIT 5
7. Random reading of several data: SELECT * FROM Blog_archive ORDER by rand () limit 10
V. MySQL optimization statement
1. View MySQL data fragments
SELECT table_schema,table_name,data_free,engine from Information_schema.tables WHERE table_schema not in (' information ', ' MySQL ') and data_free > 0;
2. Optimize MySQL Data fragmentation
Optimize table name
3. mysql Repair table
Repair table Name
4.explainSELECT * FROM table
descSELECT * FROM table
