MySQL Modify table structure

Modifications to the table after creation

The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:

To add a column:
Basic form: ALTER TABLE name add list column data type [after insertion position];

示例：    在表的最后追加到address：alter table students add address char(60);        在名为age的列后插入列birthday：alter table students add birthday date after age;    

To modify a column:
Basic form: ALTER TABLE name change column Name column new name new data type;

Example:
Rename Table Tel column to: ALTER TABLE students change Tel telphone char (+) Default '-';

将name列的数据类型改为char(16);alter table students change name name char(16) not null;

Delete Column
Basic form: ALTER TABLE name drop column name;

Renaming a table

基本形式alter table 表名 rename 新表名;示例：    重命名 students表为workmates;alter table students rename workmates;    

Delete entire table

基本形式：drop table 表名;示例：删除workmates表；drop table workmates;

Delete a database

基本形式：drop database 数据库名;示例：删除samp_db数据库；drop database samp_db;

The data structure and algorithm principle behind MySQL index

Summary

This article takes MySQL database as the research object and discusses some topics related to database indexing. In particular, MySQL supports multiple storage engines, and various storage engines support indexes differently.
So MySQL database supports multiple index types, such as Btree Index, hash index, full-text index, and so on. To avoid confusion, this article will focus only on the Btree index,
Since this is the usual index to work with MySQL, the hash index and the full index are not discussed at this time.

The main content of the article is three parts.

The first part mainly discusses the mathematical basis of MySQL database index from the data structure and algorithm theory level.
The second part of the integrated MySQL database in the MyISAM and InnoDB data Storage Engine index of the schema implementation of the discussion of clustered index, nonclustered index and overlay index and other topics.
The third part discusses the strategy of high performance using indexes in MySQL based on the theoretical basis above.

Data structure and Algorithm basics

The nature of the index

MySQL official definition of the index is: index is to help MySQL efficient data structure to obtain data, extract the sentence backbone, you can get the essence of the index: The index is a data structure.

We know that database query is one of the most important functions of database. We all want to query the data as fast as possible, so the designers of the database system are optimized from the perspective of the query algorithm,
The most basic query algorithm, of course, is the sequential lookup (linear search), this kind of review degree O (n) algorithm is obviously bad when the amount of data is large, fortunately, the development of computer science provides a lot of excellent search algorithm,
such as binary search (binary search), binary tree lookup (binary trees Search), and so on. If you analyze it a little bit, you will find that each finding algorithm can only be applied to a particular data structure, such as a binary search that requires the data to be retrieved in order.
Binary tree lookups can only be applied to binary search trees, but the structure of the data itself cannot fully satisfy various data structures.
(for example, it is not theoretically possible to organize both columns sequentially), so the data path system maintains the data structure that satisfies a particular lookup algorithm in addition to the data.
These data structures reference (point) data in some way, so that advanced find algorithms can be implemented in these data structures. This data structure is the index.

MySQL Modify table structure