MySQL table management commands

There are many MySQL table management commands. The following describes some of the most common MySQL table management commands. If you are interested in MySQL table management, take a look.

# Creating a database

 
 

  
  
CREATE DATABASE GXDATA DEFAULT CHARACTER SET gb2312;  
 
 

# Create a department information table

 
 

  
  
CREATE TABLE depinfo (  
  
  
Dep_no CHAR(2) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,  
  
  
Dep_name VARCHAR(50) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,  
  
  
Dep_Address VARCHAR(50) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,  
  
  
Dep_Tel VARCHAR(50) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL  
  
  
)ENGINE=MyISAM DEFAULT CHARSET=gb2312;  
 
 

When the temporary table database is closed, the temporary table is automatically deleted)

 
 

  
  
Create temporary table table_name (... defines the column ......)
  
  
 
 
 

Clone table

 
 

  
  
CREATE TEMPORARY TABLE new_table LIKE original_table  
  
  
 
 
 

Create a table based on the query results

 
 

  
  
CREATE TEMPORARY TABLE table_name SELECT ... ...  
  
  
 
 
 

Query and change the table Storage Engine

 
 

  
  
SELECT ENGIN FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' AND TABLE_NAME ='tabne_name' 
  
  
 
  
  
SHOW TABLE STATUS LIKE 'table_name' \G  
  
  
 
  
  
SHOW CREATE TABLE table_name \G  
  
  
 
 
 

Change storage engine

 
 

  
  
ALTER TABLE table_name ENGINE=InnoDB 
  
  
 
 
 

Quick table creation in mysql

In-depth study on MySQL's data deletion from multiple tables

Simple MySQL large table backup method

Combination of multiple MySQL table result sets

Analysis of MySQL Chinese Table creation Problems