5. mysql main health and index

<--Directory--

1) Main Health

1, the Operation table constraints

(1) Non-null constraints

(2) Field default value

(3) Unique constraint

(4) Main health constraint

(5) Add the main health (emphasis)

(6) Automatic increase

2) Index

1. Create a normal index (emphasis)

2. Create a unique index

3. Create a full-text index

4. Create multi-column indexes

5. Delete Index

"Main Health"

1, the Operation table constraints

###########################################################################

Constraint key word meaning #

The field value of the NOT NULL constraint cannot be empty #

Default Set field's value #

Unique key (UK) Constraint field value is only #

The primary KEY (PK) constraint field is the Master of the table and can be used as a unique identifier for the record #

The value of the auto_increment constraint field is automatically incremented #

Foreign key (FK) Constraint field is a table's outer health #

###########################################################################

#设置非空约束

#解释: A non-null constraint is set and the field contents are not allowed to be empty

mysql> CREATE TABLE T1 (

ID Int (a) is not NULL,

Loc varchar (40)

);

#设置字段的默认值

#解释: When you insert a record for a field in a table without assigning him a value, the system inserts a default value for the field

mysql> CREATE TABLE T1 (

ID Int (a) is not NULL,

-dname varchar (+) Default ' Cjgong ',

Loc varchar (40)

);

#设置唯一约束

#解释: The contents of a field in a database table are not allowed to be duplicated

mysql> CREATE TABLE T1 (

ID Int (a) is not NULL,

Dname varchar (a) unique,

Loc varchar (40)

);

#设置主健约束

#解释: Identify all records with a field in a database table

mysql> CREATE TABLE T1 (

ID int (primary key),

Loc varchar (40)

);

#添加主健

ALTER TABLE student change ID ID int primary key auto_increment;

#设置字段值自动增加

#解释: When you insert a new record into a database table, the value on the field automatically generates a unique ID

mysql> CREATE TABLE T1 (

-ID int (primary) key auto_increment,

Loc varchar (40)

);

Index

1. Create a normal index

#解释: Normal indexes do not attach any restrictions and can be created on fields of any data type

mysql> CREATE TABLE T1 (

, field name Database class,

, field name Database class,

.....

Index name (field name)

);

#创建表时创建普通索引

mysql> CREATE TABLE T1 (

-ID int (primary) key auto_increment,

Loc varchar (40)

-Index Index_deptno (DEPTNO)

);

#在已经存在的表上创建普通索引

Mysql> CREATE index name on table name (field name)

Mysql> CREATE index Index_deptno on T1 (DEPTNO)

#通过sql语句alter table to create a normal index

Mysql> ALTER TABLE table_name ADD index index name (field name);

Mysql> ALTER TABLE T1 add index Index_deptno (DEPTNO);

2. Create and view unique indexes

mysql> CREATE TABLE T1 (

, field name Database class,

, field name Database class,

.....

Unique index name (field name)

);

#创建表时创建唯一索引

#解释: When an index is created, the value of the index must be unique

mysql> CREATE TABLE T1 (

-ID int (primary) key auto_increment,

Loc varchar (40)

Unique index Index_depktno (deptno)

);

#在已经存在的表上创建唯一索引

Mysql> create unique index name on table name (field name)

Mysql> Create unique index index_deptno on T1 (DEPTNO)

Create a unique index #通过sql语句alter table

Mysql> ALTER TABLE table_name add unique index name (field name);

Mysql> ALTER TABLE t1 add unique index Index_deptno (DEPTNO);

3. Create and view full-text indexes

#解释: A full-text index is associated on a char, varchar, text field to quickly query a field of a large number of string types

mysql> CREATE TABLE T1 (

, field name Database class,

, field name Database class,

.....

-Fulltext Index index name (field name)

);

#创建表时创建全文索引

mysql> CREATE TABLE T1 (

-ID int (primary) key auto_increment,

Loc varchar (40)

-Fulltext Index Index_depktno (DEPTNO)

);

#在已经存在的表上创建全文索引

Mysql> Create FULLTEXT index index name on table name (field name)

Mysql> Create FULLTEXT Index index_deptno on T1 (DEPTNO)

To create a full-text index #通过sql语句alter table

Mysql> ALTER TABLE TABLE_NAME ADD FULLTEXT index name (field name);

Mysql> ALTER TABLE T1 add unique fulltext index Index_deptno (DEPTNO);

4. Create and view multi-column indexes

#解释: Multiple-column indexes when an index is created, the associated field is multiple fields, although you can query by the associated field, but the multicolumn index is used only if the first field in the associated field is used in the query condition

mysql> CREATE TABLE T1 (

, field name Database class,

, field name Database class,

.....

-Fulltext Index index name (field name)

);

#创建表时创建多列索引

mysql> CREATE TABLE T1 (

, field name Database class,

, field name Database class,

.....

Index name (field name 1, field name 2)

);

#创建表时创建多列索引

mysql> CREATE TABLE T1 (

-ID int (primary) key auto_increment,

Loc varchar (40)

-Index Index_deptno (deptno,id)

);

#在已经存在的表上创建多列索引

Mysql> CREATE index name on table name (field name 1, field name 2)

Mysql> CREATE index Index_deptno on T1 (deptno,id)

To create a multicolumn index #通过sql语句alter table

Mysql> ALTER TABLE table_name ADD index index name (field name 1, field name 2);

Mysql> ALTER TABLE T1 add index Index_deptno (deptno,id);

5. Delete Index

The syntax form is as follows:

Drop index index name on table name

Mysql> DROP index index_deptno on T1;

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1787651

5. mysql main health and index