Basic MySQL Data Table operation 2: Table Structure View, modification, and table operation

Basic MySQL Data Table operation 2: Table Structure View, modification, and table operation

1. view the data table structure

1) view the basic table structure statement DESCRIBE

Syntax:DESCRIBETable Name;

Example:

mysql> DESCRIBE product;+--------------+--------------+------+-----+---------+----------------+| Field        | Type         | Null | Key | Default | Extra          |+--------------+--------------+------+-----+---------+----------------+| product_id   | int(11)      | NO   | PRI | NULL    | auto_increment || product_name | varchar(50)  | 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(50)  | NO   |     | NULL    |                || description  | varchar(200) | YES  |     | NULL    |                |+--------------+--------------+------+-----+---------+----------------+

Note:

NULL: indicates whether the column can store NULL values. Key: indicates whether the column has been indexed. PRI indicates that the column is a part of the table's primary key; UNI indicates that the column is part of the UNIQUE index; MUL indicates that a given value in the column can appear multiple times; Default: indicates whether the column has a Default value, extra indicates the additional information related to the given column. For example, AUTO_INCREMENT;

2) view the detailed TABLE structure statement SHOW CREATE TABLE

Function Description:

Used to display statements used to create a table

Syntax:

Show create table <TABLE Name \ G>;

Tip:

This statement can be used to view the detailed statements for creating a table. It can also be used to view the storage engine and character encoding. With the '\ G' parameter, the display results are more intuitive and easy to view;

Example:

mysql> show create table bm\G;*************************** 1. row ***************************       Table: bmCreate Table: CREATE TABLE `bm` (  `id` int(11) NOT NULL,  `name` varchar(22) DEFAULT NULL,  `location` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `STH` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.02 sec)

2. modify a data table

1) modify the table name

Syntax

Alter table <old TABLE name> RENAME [TO] <new TABLE Name>;

Example

ALTER TABLE bm RENAME department;

2) modify the field data type

Syntax

Alter table <TABLE Name> MODIFY <field Name> <data type>;

Example

ALTER TABLE department MODIFY id VARCHAR(11);

3) modify the field name

Syntax

Alter table <TABLE Name> CHANGE <old field Name> <new data type>;

Example

Alter table employees CHANGE location loc VARCHAR (350); // The type can be the same as the original one without modification

4) add fields

Syntax

Alter table <TABLE Name> ADD <new field Name> <data type> [constraints] [FIRST | AFTER an existing field name]; // ADD it to the end of the last column by default.

Example

Alter table employees ADD manager_id INT (10); alter table employees ADD age INT (11) not null; // non-empty constraint alter table employees ADD sal float first; // add alter table employees ADD hire_date date after manager_id to the first column of the TABLE; // ADD it AFTER the specified Column

5) delete a field

Syntax

Alter table <TABLE Name> DROP <field Name>;

Example

ALTER TABLE employees DROP manager_id;ALTER TABLE employees DROP hire_date;

6) modify the field arrangement position

Syntax

Alter table <TABLE Name> MODIFY <Field 1> <data type> FIRST | AFTER <Field 2>;

Example

Alter table employees MODIFY name VARCHAR (22) FIRST; // move name to the FIRST column alter table department MODIFY location VARCHAR (350) AFTER department_id; // move location column to department_id

7) change the storage engine of the table

Main storage engines supported by MySQL

Engine name	Supported?
FEDERATED	No
MRG_MYISAM	Yes
MyISAM	Yes
BLACKHOLE	Yes
CSV	Yes
MEMORY	Yes
ARCHIVE	Yes
InnoDB	Default
PERFORMANCE_SCHEMA	Yes

Syntax

Alter table <TABLE Name> ENGINE = <name of the changed storage ENGINE>;

Example

ALTER TABLE department ENGINE=MyISAM;

8) Delete the foreign key constraint of the table

Syntax

Alter table <TABLE Name> drop foreign key <foreign key constraint name>;

Example 

ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept; 

3. delete a data table

1) Delete the table not associated

Syntax

Drop table [if exists] TABLE 1, TABLE 2,... TABLE n;

Example

DROP TABLE IF EXISTS employees; 

2) Delete the primary table associated with another table

Note: You must delete 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    -> (    -> id int(11) primary key,    -> name varchar(22),    -> location varchar(200)    -> );Query OK, 0 rows affected (0.07 sec)mysql> create table employee    -> (    -> id int(11) primary key,    -> name varchar(25),    -> department_id int(11),    -> salary float,    -> constraint fk_emp_dept foreign key(department_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 drop 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 encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!