Methods of creating, modifying and deleting mysql tables, views and indexes

The following is about how to create, modify, and delete mysql tables, views, and indexes. The secret of the text lies in being close to the topic. So, no gossip, let's read the following directly, I believe you will benefit from reading this article on how to create, modify, and delete mysql tables, views, and indexes.

1. Table correlation

1. Create

CREATE TABLE `test` (`id` int (11) NOT NULL AUTO_INCREMENT, `year` int (11) NOT NULL COMMENT 'year', `month` tinyint (4) NOT NULL COMMENT 'month', `updator` varchar (11) NOT NULL, `updateTime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ix_year_ month` (`year`, `month`) ENGINE=InnoDB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8 COMMENT=' test table'

2. Modification

(1) modify the table name:

Alter table table_name rename table_new_name

(2) New fields:

ALTER TABLE table_name ADD column_name datatype

(3) modify the field name:

ALTER TABLE table_name ALTER COLUMN column_name new_column_name varchar (100)

(4) modify the field type:

ALTER TABLE table_name ALTER COLUMN column_name datatype

(5) Delete fields:

ALTER TABLE table_name DROP COLUMN column_name

3. Delete

DROP TABLE `test`

Second, view dependence

1. Create

CREATE VIEW view_name AS SELECT column_name (s) FROM table_name WHERE condition

2. Modification

CREATE OR REPLACE VIEW v_test1 AS SELECT * FROM ceshi

3. Delete

DROP VIEW view_name

III. Index correlation

1. Create

(1) create a simple index on the table

CREATE INDEX index_name ON table_name (column_name,column_name2)

(2) create a unique index on the table

CREATE UNIQUE INDEX index_name ON table_name (column_name)

2. Delete

ALTER TABLE table_name DROP INDEX index_name

Is there anything you don't understand about the above methods of creating, modifying, and deleting mysql tables, views, and indexes? Or if you want to know more about it, you can continue to follow our industry information section.