How to create a table for MySQL, query a table, create an index instance

Create a table

Data type int (shaping, Integer) not Null,char (character) tinyint (smallest shaping) varchar (variable-length character type)

CREATE TABLE Xiaohu (

ID Int (4) is not NULL,

Name Char (a) is not NULL,

Age tinyint (2) is not null default ' 0 ', (Can not be empty, but can give 0)

Dept varchar (+) default null (can be NULL)

Such as

Mysql> CREATE TABLE Student (

-ID Int (4) NOT NULL,

, name char (a) is not NULL,

-Age tinyint (+) NOT null default ' 0 ',

-Dept varchar (+) default null

);

Query OK, 0 rows affected (0.01 sec)

To view statements that have seen a table:

Show create table name \g;

Mysql> Show CREATE TABLE student\g;

1. Row ***************************

Table:student

Create table:create Table ' student ' (

' ID ' int (4) is not NULL,

' Name ' char (a) is not NULL,

' Age ' tinyint (+) not NULL DEFAULT ' 0 ',

' Dept ' varchar (+) DEFAULT NULL

) Engine=myisam DEFAULT Charset=utf8

1 row in Set (0.00 sec)

View table Structure

Desc Plus table Name

mysql> desc Student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (4) |     NO | |       NULL | |

| name | char (20) |     NO | |       NULL | |

| Age | tinyint (20) |     NO | |       0 | |

| Dept | varchar (16) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

4 rows in Set (0.01 sec)

Create index to raise query speed

Index classification:

Primary key index primary key column All content must be unique such as school number, ticket number

Mysql> CREATE TABLE student (ID int (4) NOT null auto_increment, increment name char () not NULL, age tinyint (2) is not null Defaul T ' 0 ', Dept varchar (+) default NULL, PRIMARY key (ID), primary key index key index_name (name) normal index);

Query OK, 0 rows affected (0.01 sec)

What if I forget to add an index when I create a table?

can add syntax

mysql> ALTER TABLE student change ID ID int primary key auto_increment;

Query OK, 0 rows Affected (0.00 sec)

records:0 duplicates:0 warnings:0

Mysql> desc Student

;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| ID | Int (11) | NO | PRI | NULL | auto_increment |

| name | char (20) |     NO | |                NULL | |

| Age | tinyint (2) |     NO | |                0 | |

| Dept | varchar (16) |     YES | |                NULL | |

+-------+-------------+------+-----+---------+----------------+

4 rows in Set (0.00 sec)

Normal index:

Mysql> ALTER TABLE student Add index index_name (name); Change the name of the table to add an indexed index name (on which column to add)

Query OK, 0 rows affected (0.01 sec)

records:0 duplicates:0 warnings:0

mysql> desc Student;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| ID | Int (11) | NO | PRI | NULL | auto_increment |

| name | char (20) | NO | MUL |                NULL | |

| Age | tinyint (2) |     NO | |                0 | |

| Dept | varchar (16) |     YES | |                NULL | |

+-------+-------------+------+-----+---------+----------------+

4 rows in Set (0.00 sec)

Specify first n characters to create an index

Mysql> CREATE index index_dept on student (Dept (8)); Create index index name in table (number of columns (first few characters))

Query OK, 0 rows Affected (0.00 sec)

records:0 duplicates:0 warnings:0

View index Details

Mysql> Show index from 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:

2. Row ***************************

Table:student

Non_unique:1

Key_name:index_name

Seq_in_index:1

Column_name:name

Collation:a

Cardinality:null

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

3. Row ***************************

Table:student

Non_unique:1

Key_name:index_x

Seq_in_index:1

Column_name:age

Collation:a

Cardinality:null

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

4. Row ***************************

Table:student

Non_unique:1

Key_name:index_dept

Seq_in_index:1

Column_name:dept

Collation:a

Cardinality:null

Sub_part:8

Packed:null

Null:yes

Index_type:btree

Comment:

4 rows in Set (0.00 sec)

Federated Index Syntax

Create INDEX index_name_dept on student (name,dept);

Table:student

Non_unique:1

Key_name:ind_name_dept

Seq_in_index:2

Column_name:dept

Collation:a

Cardinality:null

Sub_part:null

Packed:null

Null:yes

Index_type:btree

Comment:

6 rows in Set (0.00 sec)

Mysql> DROP index ind_name_dept on student; Delete index index name in which table

Query OK, 0 rows Affected (0.00 sec)

records:0 duplicates:0 warnings:0

Create indexes before creating federated indexes with several characters

Create INDEX ind_name_dept on student (name (8), Dept (10));

What exactly do you want to index that data?

Index occupies space, update database also need to maintain index data write frequently read good statements fewer indexes ten to hundreds of rows of small tables without creating an index

Try to make an index on a large table with a unique value

Operation is a slow-query statement, open a monitor.

How enterprises Create tables, query tables, create index instances for MySQL