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