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

Source: Internet
Author: User

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

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.