Linux Learning Note: MySQL CREATE table

Statement

Create table:create Table ' student ' (

' ID ' int (4) is not NULL,

' Name ' varchar (+) is not NULL,

' Score ' int (1) DEFAULT ' 0 '

) Engine=innodb DEFAULT Charset=utf8

Add primary Key

ALTER TABLE student change ID ID int primary key auto_increment;

Generally do not need to modify the primary key, but sometimes the time to build a table forgot to add, to be able to write after the completion

The format is ALTER TABLE < table name > change < old field name > < new field name > Field attribute, here the auto_increment is also after the fill up.

mysql> desc mydb.student;+-------+-------------+------+-----+---------+----------------+|  field | type        | null | key |  default | extra          |+-------+-------------+ + -----+-----+---------+----------------+| id    | int (one)       | no   | pri | null    | auto_increment  | |  name  | varchar ( | no   |     |)  NULL    |                 | |  score | int (1)       | YES  |      | 0       |                 |+-------+-------------+------+-----+---------+-------- --------+3 rows in set  (0.00 SEC)

Add index

Mysql>  alter table mydb.student add index name (name); query ok, 0 rows affected  (0.63 sec) records: 0  duplicates:  0  warnings: 0mysql> desc mydb.student;+-------+-------------+------+-----+------- --+----------------+| field | type        | null  | Key | Default | Extra           |+-------+-------------+------+-----+---------+----------------+| id    |  Int (one)      | NO   | PRI | NULL     | auto_increment | |  name  | varchar ( | NO   | MUL | NULL )    |               & nbsp;| | score | int (1)       | YES  |      | 0       |                 |+-------+-------------+------+-----+---------+--- -------------+3 rows in set  (0.00 SEC)

The name is actually the index name (field name), and the index name can be defined by itself. After success, you can see the key display Mul in Desc.

Delete Index

ALTER TABLE mydb.student DROP INDEX name;

This name is the name of the index.

View Index

show index from mydb.student;    +---------+------------+----------+------- -------+-------------+-----------+-------------+----------+--------+------+------------+---------+------------- --+| table   | non_unique | key_name | seq_in_index |  column_name | collation | cardinality | sub_part | packed |  null | index_type | comment | index_comment |+---------+------------+------ ----+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+- --------------+| student |          0 |  Primary  |            1 | id           | A           |           0 |     null  | NULL   |      | BTREE       |         |                |+---------+------------+----------+--------------+----------- --+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row  in set  (0.00 SEC)

Create an index of partial content

Some of the content is longer, but the first few characters are already unique, and you can use these characters to form an index.

For example, use the first 4 characters of name to form an index

mysql> create index index_name on mydb.student  (Name (4)); query ok, 0 rows affected  (0.20 sec) records: 0  duplicates:  0  warnings: 0mysql> desc mydb.student;                                               +-------+-------------+------+-----+---------+----------------+| field | type         | Null | Key | Default | Extra           |+-------+-------------+------+-----+---------+--------- -------+| id    | int (one)      | NO    | pri | null    | auto_increment | |  name  | varchar ( | NO   | MUL | NULL )    |                 | |  score | int (1)       | YES  |      | 0       |                 |+-------+-------------+------+-----+---------+--------- -------+3 rows in set  (0.00 sec) mysql>  show index from  mydb.student\g;*************************** 1. row ***************************         Table: student   Non_unique: 0      key_name: primary seq_in_index: 1  column_name:  id    collation: a  cardinality: 0     sub_ part: null       packed: null          Null:    Index_type: BTREE       comment: index_comment: *************************** 2. row ************************** *        table: student   non_unique: 1      key_name: index_name seq_in_index: 1  column_name: name     collation: a  cardinality: 0     sub_part : 4       packed: null          null:    index_type: btree      comment:  index_comment: 2 rows in set  (0.00 sec) error: no query specified 

See 2. Row's Sub_part

For index types, refer to http://blog.sina.com.cn/s/blog_6fd335bb0100v1lm.html

Create a federated index

On the basis of the above index, enter multiple field names to create a federated index

For example

mysql> create index index_id_name on mydb.student  (Id,name (4));     Query OK, 0 rows affected  (0.13 sec) records: 0   duplicates: 0  warnings: 0mysql> show index from mydb.student  \g*************************** 1. row ***************************         table: student   non_unique: 0     key_ Name: primary seq_in_index: 1  column_name: id    collation:  A  Cardinality: 0     Sub_part: NULL        Packed: NULL         Null:     Index_type: BTREE      Comment: Index_comment:   2. row ***************************        table:  Student   non_unique: 1     key_name: index_name seq_in _index: 1  column_name: name    collation: a   cardinality: 0     sub_part: 4        Packed: null         null:    index_type :  btree      comment: index_comment: ***************************  3. row ***************************        Table:  Student   non_unique: 1     key_name: index_id_name seq _in_index: 1  column_name: id    collation: a   Cardinality: 0     sub_part: null       packed: null          Null:    Index_type: BTREE       comment: index_comment: *************************** 4. row **         Table: student    non_unique: 1     key_name: index_id_name seq_in_index: 2   Column_name: name    Collation: A  Cardinality: 0      Sub_part: 4       Packed: NULL          Null:    Index_type: BTREE       Comment: Index_comment: 4 rows in set  (0.00  SEC) can see that the key_name of lines 3rd and 4 are the same

Deleting a federated index is the same as using the index name

Mysql> DROP index index_id_name on mydb.student; Query OK, 0 rows Affected (0.00 sec) records:0 duplicates:0 warnings:0

Create a single index and add the unique keyword before the keyword index

Mysql> Create unique index index_id_name on mydb.student (Id,name (4)); Query OK, 0 rows affected (0.23 sec) records:0 duplicates:0 warnings:0

Linux Learning Note: MySQL CREATE table