Basic MySQL data table operation 2: table structure View, modification, and table operation _ MySQL

Source: Internet
Author: User
1. View Data Table Structure 1) view the basic table structure statement DESCRIBE syntax: DESCRIBE table name; example: mysqlDESCRIBEproduct; + ---------------- + ------------ + ------ + ----- + --------- + ---------------- + | Field 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
 <表名\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
 <旧表名>
  
RENAME [TO]
  <新表名>
   
;
  
 

Example

ALTER TABLE bm RENAME department;

2) modify the field data type

Syntax

ALTER TABLE
 <表名>
  
MODIFY
  <字段名> 
   <数据类型>
    
;
   
  
 

Example

ALTER TABLE department MODIFY id VARCHAR(11);

3) modify the field name

Syntax

ALTER TABLE
 <表名>
  
CHANGE
  <旧字段名>
   <新字段名>
    <新数据类型>
     
;
    
   
  
 

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
 <表名>
  
ADD
  <新字段名> 
   <数据类型>
    
[Constraints] [FIRST | AFTER a field name already exists]; // 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
 <表名>
  
DROP
  <字段名>
   
;
  
 
Example
ALTER TABLE employees DROP manager_id;ALTER TABLE employees DROP hire_date;

6) modify the field arrangement position

Syntax

ALTER TABLE
 <表名>
  
MODIFY
  <字段1>
   <数据类型>
    
FIRST | AFTER
    <字段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
 <表名>
  
ENGINE =
  <更改后的存储引擎名>
   
;
  
 
Example
ALTER TABLE department ENGINE=MyISAM;

8) delete the foreign key constraint of the table

Syntax

ALTER TABLE
 <表名>
  
DROP FOREIGN KEY
  <外键约束名>
   
;
  
 
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!
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.