View MySQL-supported storage engines: show engines; View the current default storage engine: Show variables like '%storage_engine% '; View the storage engine used by all tables in the Library Show table status from DB _name; View storage engine for the specified table in the Library Show table status like ' Tb_name '; show create table tb_name; Set table storage Engine: ALTER table Tb_name Engine=innodb; SQL statement classification: **********ddl:data defination language**************create, DROP, alter******** view database list: show DATABASES; View all the table states in the Library: Show Table status from db_name Creating data table CREATE TABLE [IF not EXISTS] ' tbl_name ' (col1 type1 modifier, col2 type2 modifier, ...) ALTER TABLE ' tbl_name ' field: Add Field: Addadd col1 data_type [first| After Col_name] Delete field: Drop Modify field: Alter (default), Change (field name), Modify (field property) Index: Add Index: DROP Index Table option Modify: View index on table: SHO W INDEXES from [Db_name.] tbl_name; View index on table: show INDEXES from [Db_name.] Tbl_name; Example: CREATE TABLE students (ID int UNSIGNED notnull PRIMARY key,name VARCHAR () notnull,age tinyint UNSIGNED); Change table alter tables s1 ADD phone varchar (one) Aftername;******dml:data manipulation Language******insert, DELETE, Updateupdate [ Low_priority] [IGNORE] Table_refErenceset col_name1={expr1| DEFAULT} [, col_name2={expr2| DEFAULT}] ... [WHERE Where_condition] [ORDER by ...] [Limit Row_count] Note: Be sure to have a restriction, otherwise the specified field restrictions for all rows will be modified: wherelimit example: Inserting a statement into a table: INSERT into students (Id,name) VALUES (3, ' Jack '), (4, ' Allen '); SELECT * from students ORDER by name DESC LIMIT 1,2;******dcl:data Control language******grant, Revokegrant Priv_type [(Co Lumn_list)],... On [object_type] priv_level to ' user ' @ ' host ' [identified by ' Password '] [with GRANT OPTION]; Reclaim authorization: REVOKE Priv_type [(column _list)] [, Priv_type [(column_list)] ... On [object_type] priv_level from user [, user] ... Example: Granting Permissions: Grant SELECT (col1), INSERT (col1,col2) on mydb.mytbl to ' someuser ' @ ' somehost '; Reclaim permission: REVOKE DELETE on testdb.* from ' testuser ' @ '% ' ******dql:data Query language******select
The following three types of internal connections are available:
Equivalent connections: The fields between tables are "equivalent" to establish a connection relationship;
Natural connection: The equivalent connection does not remove the repeating attribute, and the natural connection removes the duplicate attribute, or it can be said that the natural connection is the equivalent connection that removes the repeating column.
Non-equivalent connection: Equivalent connection: Mainly used in addition to the equal sign operator, such as:<>, >, <, >=, <=, like, in, between ... and.
Organize MySQL statements