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

Source: Internet
Author: User

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

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 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. If yes;
  • Extra: Additional information related to the given column that can be obtained. 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 detailed statements for creating a table;
  • It can also be used to view the storage engine and character encoding;
  • The parameter '\ G' is added to 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(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!


Change MYSQL Data Table Name

It is impossible to implement a query statement.
One statement is
Rename table tb_name TO new_name;
I teach you a trick.
Use the program to output a set of statements and execute them in mysql.

Example: php
I would like to write the program code to you, do not know your mysql DATA STRUCTURE, 154651166 park412@hotmail.com
You add me
If you are interested, you can solve it for you.

Mysql two table data query change statement

Update A set [key] = 'php' from B where A. id = B. aid and B. body like '% php %'
 

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.