Shulou(Shulou.com)06/01 Report--

The following content mainly brings MySQL master key and index handout to you. The knowledge mentioned here is slightly different from books. It is summarized by professional technicians in the process of contacting users. It has certain experience sharing value and hopes to bring help to readers.

1) Main health

1. Constraints of operation table

(1) Non-empty constraints

(2) Field default values

(3) Unique constraints

(4) Principal constraints

(5) Add main key (key)

(6) Automatic increase

2) Index

1. Create a general index (focus)

2. Create a unique index

3. Create a full-text index

4. Create a multi-column index

5. Delete index

[Main]

1. Constraints of operation table

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

Constraint Key Word Meaning #

The field value of the not null constraint cannot be null #

default Settings field defaults #

unique key (uk) constraint field value is unique #

primary key (pk) constraint field is the primary key of the table and can be used as the unique identifier of the record #

auto_increment constraint field is autoincrement #

foreign key (fk) constraint field is the external key of the table #

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

#Set non-empty constraints

#Explanation: Non-null constraint is set, field content cannot be null

mysql> create table t1(

-> id int(20) not null,

-> loc varchar(40)

-> );

#Set default values for fields

#Explanation: When inserting a record for a field in a table without assigning a value to it, the system inserts a default value for this field

mysql> create table t1(

-> id int(20) not null,

-> dname varchar(20) default 'cjgong',

-> loc varchar(40)

-> );

#Set unique constraints

#Explanation: The content of a field in the database table is not allowed to be repeated

mysql> create table t1(

-> id int(20) not null,

-> dname varchar(20) unique,

-> loc varchar(40)

-> );

#Set primary constraints

#Explanation: Use a field in a database table to identify all records

mysql> create table t1(

-> id int(20) primary key,

-> loc varchar(40)

-> );

#Add main key

alter table student change id id int primary key auto_increment;

#Set field value to automatically increment

#Explanation: When a new record is inserted into a database table, the value on the field automatically generates a unique ID

mysql> create table t1(

-> id int(20) primary key auto_increment,

-> loc varchar(40)

-> );

[Index]

1. Create an ordinary index

#Explanation: Ordinary indexes are not subject to 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 index name (field name)

-> );

#Create a normal index when creating tables

mysql> create table t1(

-> id int(20) primary key auto_increment,

-> loc varchar(40)

-> index index_deptno(deptno)

-> );

#Create a normal index on an existing table

mysql> create index name on table name (field name)

mysql> create index index_deptno on t1(deptno)

#Create a normal index by sql statement alter table

mysql> alter table_name add index name (field name);

mysql> alter table t1 add index index_deptno(deptno);

Creating and viewing unique indexes

mysql> create table t1(

-> Field Name Database Class,

-> Field Name Database Class,

-> ... ...

-> unique index index name (field name)

-> );

#Create a unique index when creating a table

#Explanation: When creating an index, the index value must be unique

mysql> create table t1(

-> id int(20) primary key auto_increment,

-> loc varchar(40)

-> unique index index_depktno(deptno)

-> );

#Create a unique index on an existing table

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

mysql> create unique index index_deptno on t1(deptno)

#Create a unique index by sql statement alter table

mysql> alter table_name add unique index index name (field name);

mysql> alter table t1 add unique index index_deptno(deptno);

Creating and viewing full-text indexes

#Explanation: Full-text index should be associated with char, varchar, text fields, so as to quickly query a large number of string type fields

mysql> create table t1(

-> Field Name Database Class,

-> Field Name Database Class,

-> ... ...

-> fulltext index index name (field name)

-> );

#Create a full-text index when creating a table

mysql> create table t1(

-> id int(20) primary key auto_increment,

-> loc varchar(40)

-> fulltext index index_depktno(deptno)

-> );

#Create a full-text index on an existing table

mysql> create fulltext index name on table name (field name)

mysql> create fulltext index index_deptno on t1(deptno)

#Create full-text index by sql statement alter table

mysql> alter table_name add fulltext index name (field name);

mysql> alter table t1 add unique fulltext index index_deptno(deptno);

Creating and viewing multi-column indexes

#Explanation: Multi-column index When creating an index, the associated fields are multiple fields. Although you can query through the associated fields, a multi-column index will only be used 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)

-> );

#Create a multi-column index when creating tables

mysql> create table t1(

-> Field Name Database Class,

-> Field Name Database Class,

-> ... ...

-> index index name (field name 1, field name 2)

-> );

#Create a multi-column index when creating a table

mysql> create table t1(

-> id int(20) primary key auto_increment,

-> loc varchar(40)

-> index index_deptno(deptno,id)

-> );

#Create a multi-column index on an existing table

mysql> create index name on table name (field name 1, field name 2)

mysql> create index index_deptno on t1(deptno,id)

#Create a multi-column index with sql statement alter table

mysql> alter table_name add index name (field name 1, field name 2);

mysql> alter table t1 add index index_deptno(deptno,id);

5. Delete index

The syntax is as follows:

drop index name on table name

mysql> drop index index_deptno on t1;

For the above on MySQL main health, index handouts, if you still need to know more can continue to pay attention to our industry push new, if you need to get professional answers, you can contact the official website pre-sales after-sales, I hope this article can bring you a certain knowledge update.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.