MySQL Common statement Summary

1. Account number

Connection data: mysql-h host-u root-p Password

Change Password: mysqladmin-u root-p old_password Password New_password

Add Users: Create user ' user1 ' @ '% ' identified by ' password '

User authorization: Grant Select,update,insert,delete on database.table to ' user1 ' @ '% '

2. Database operations

Check all libraries: show databases

Switch libraries: Use database_name

New databases: CREATE DATABASE database_name

Delete databases: DROP database database_name

3. Data Sheet operation

Check all tables: Show tables

Check table structure: describe table_name

New data table: CREATE TABLE (field1 NOT NULL Auto_increment,field varchar (a) Not null,primary key (' ID '))

Delete Data sheet: Drop table_name

Convert storage: Alter database.table engine= ' MyISAM '

Table name modification: ALTER TABLE Old_name Rename to New_name

List fields: Show field from table like '%word% '

All tables: SELECT * from Information_schema.tables

4. Record Operation class

Add record: INSERT into table (FIELD1,FIELD2,FIELD3) VALUES (' value1 ', ' value2 ', ' value3 ')

Modify record: Update table set field1= ' value1 ', field2= ' value2 ' where id=1

Delete Record: Delete from table where id=1

Query history: Select Field1,field2,field3 from table where id>0 ORDER by ID ASC

Connection appearance: Select a.*,b.* from table1 as a joins table2 as B on A.id=b.aid

MySQL Common statement Summary