MySQL data table basic Operation II: Table structure view, modify and table operations

First, view the data table structure

1) View the table basic Structure statement DESCRIBE

Syntax: DESCRIBE table name;

Example:

Mysql> DESCRIBE product;+--------------+--------------+------+-----+---------+----------------+| Field        | Type         | Null | Key | Default | Extra          |+--------------+--------------+------+-----+---------+----------------+| product_id   | int (11)      | NO   | PRI | NULL    | auto_increment | | product_name | varchar |  NO   |     | NULL    |                | | description  | varchar (200) | YES  |     | NULL    |                | +--------------+--------------+------+-----+---------+----------------+

Syntax: DESC table name;

Example:

Mysql> DESC product;+--------------+--------------+------+-----+---------+----------------+| Field        | Type         | Null | Key | Default | Extra          |+--------------+--------------+------+-----+---------+----------------+| product_id   | int (11)      | NO   | PRI | NULL    | auto_increment | | product_name | varchar |  NO   |     | NULL    |                | | description  | varchar (200) | YES  |     | NULL    |                | +--------------+--------------+------+-----+---------+----------------+

Description:

• Null: Indicates whether the column can store null values;
• Key: Indicates whether the column is indexed. A PRI representation is a part of a table's primary key; Uni indicates that the column is part of a unique index; Mul indicates that a given value in this column is allowed to occur multiple times;
• Default: Indicates whether the column has a defaults, and if so, how many;
• Extra: Represents additional information that can be obtained about a given column. such as auto_increment, etc.;
  

2) View Table detail Structure Statement show CREATE table

Function Description:

Used to display the statement when the table was created

Grammar:

SHOW CREATE table < table name \g>;

Tips:

• This statement can be used to view the detailed statements that create the table;
• can also be used to view the storage engine and character encoding;
• After adding the parameter ' \g ', it can make the display result more intuitive and easy to view.
  

Example:

Mysql> Show CREATE TABLE bm\g;*************************** 1. Row ***************************       table:bmcreate table:create Table ' BM ' (  ' id ' int (one) not NULL,  ' name ' Varc Har (+) default NULL,  ' location ' varchar (default null),  PRIMARY key (' id '),  UNIQUE key ' STH ' (' name ')) ENG Ine=innodb DEFAULT charset=latin11 Row in Set (0.02 sec)

Second, modify the data sheet

1) Modify the table name

Grammar

ALTER table < old table name > RENAME [to] < new table name >;

Example

ALTER TABLE BM RENAME Department;

2) Modify the data type of the field

Grammar

ALTER table < table name > MODIFY < Field name > < data type >;

Example

ALTER TABLE Department MODIFY ID VARCHAR (11);

3) Modify the field name

Grammar

ALTER table < table name > change < old field name >< new field name >< new data type >;

Example

ALTER TABLE Employees Change Location Loc VARCHAR (350);//type can be same as original, without modification

4) Add Field

Grammar

ALTER table < table name > ADD < new field name > < data type >  [constraint]  [first| After existing field name];//is added to the last column by default

Example

ALTER TABLE Employees ADD manager_id INT (10); ALTER TABLE Employees add age INT (one) not null;//non-null constraint ALTER TABLE employees add SAL FLOAT first;//add ALTER TABLE Emplo in the first column of the table Yees Add hire_date date after manager_id;//added after the specified column

5) Delete Field

Grammar

ALTER table < table name > DROP < field name >;

Example

ALTER TABLE Employees DROP manager_id; ALTER TABLE Employees DROP hire_date;

6) Modify the position of the field

Grammar

ALTER table < table name > MODIFY < field 1>< data type > first| After < field 2>;

Example

ALTER TABLE Employees MODIFY name varchar first;//move name to first column ALTER TABLE Department MODIFY location varchar department_id;//After moving the location column to the department_id column

7) Change the storage engine for the table

Primary storage engine supported by MySQL

Engine name	is supported
Federated	Whether
Mrg_myisam	Is
MyISAM	Is
Blackhole	Is
Csv	Is
MEMORY	Is
ARCHIVE	Is
InnoDB	Default
Performance_schema	Is

Grammar

ALTER TABLE < table name > engine=< changed storage engine name >;

Example

ALTER TABLE Department Engine=myisam;

8) Delete the foreign KEY constraint for the table

Grammar

ALTER table < table name > DROP FOREIGN key < foreign KEY constraint name >;

Example

ALTER TABLE Employees DROP FOREIGN KEY fk_emp_dept;

Third, delete the data table

1) Delete tables that are not associated

Grammar

DROP table [IF EXISTS] table 1, table 2,... Table N;

Example

DROP TABLE IF EXISTS employees;

2) Delete the main table associated with the other table

Description: To remove the foreign key first

Example

mysql> CREATE DATABASE Rm_tab; Query OK, 1 row Affected (0.00 sec) mysql> use rm_tab;database changedmysql> CREATE TABLE Department (-& Gt ID Int (one) primary key, name varchar (+), location varchar (+); Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE employee--ID int (one) primary key, n Ame varchar (+), department_id Int (one), salary float, constraint fk_emp_dept foreign key (depart MENT_ID) References Department (ID)); Query OK, 0 rows affected (0.10 sec) mysql> drop table department; ERROR 1217 (23000): Cannot delete or update a parent row:a FOREIGN KEY constraint failsmysql> ALTER TABLE employee DRO P FOREIGN key fk_emp_dept; Query OK, 0 rows affected (0.22 sec) records:0 duplicates:0 warnings:0mysql> drop table department; Query OK, 0 rows affected (0.04 sec) mysql> show tables;+------------------+| Tables_in_rm_tab |+------------------+|        Employee |+------------------+1 Row in Set (0.00 sec) 

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!

MySQL data table basic Operation II: Table structure view, modify and table operations