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

The following to understand the detailed method of operating the mysql data table, I believe you will benefit a lot after reading, the text in the essence is not much, hope to operate the detailed method of the mysql data table this short content is what you want.

Create a tabl

Basic grammatical forms:

Create table [if not exists] Table name (field list [, index or constraint list]) [list of table options]

Field setting form:

Field name type [field property 1 field property 2 … ..]

Description:

1. You can take the field name yourself.

two。 Types are the data types learned earlier: int, tinyint, float, double, char (6), varchar (25), text, datetime.

3. There can be multiple field properties (depending on your specific needs), directly separated by spaces; the main ones are as follows:

The attribute name means auto_increment: only for integer types, so that the value of this field automatically gets a growth value. It is usually used to set the first field of a table, and it is also usually used as the primary key primary key: used to set the field as the primary key, so that the value of the field can be "uniquely determined" a row of data unique key: set that the field is "unique", that is, it is not repeated. Not null: used to set that the field cannot be null. If it is not set, it can be empty by default. Comment: field description text index

An index is a hidden "data table" that is automatically maintained within the system, and its function is to greatly speed up the search of data!

The search speed of this hidden data table is based on which the data is sorted automatically.

The form is:

Index type (field name to be indexed) Index type formal meaning ordinary index key (field name) is just an index, has no other function, but can only speed up the search speed unique index unique key (field name) is an index, and can also set its field value can not be repeated (unique) primary key index primary key (field name) is an index, and It also has the ability to distinguish any row of data in the table (which is actually unique), which actually has a little more function than the uniqueness index: uniqueness can be empty null, and the primary key cannot be empty full-text index fulltext (field name).

Foreign key index foreign key (field name) references other tables (corresponding to field names in other tables)

Index creation syntax:

Foreign key index:

Foreign key (field name) references other tables (corresponding to field names in other tables)

Foreign key: one field in the table is not the primary key or candidate key of this table, but the primary key or candidate key of another table.

Candidate key or candidate key: if a table has an attribute that can uniquely identify a row, it is called a candidate key, and any one of the candidate keys is the primary key.

Example:

Note: when inserting data in the xuesheng table, the value of the banji_id field cannot be inserted casually, but must be the same data value as the id field in the banji table before it can be inserted.

Full-text index: only understand, because it is not friendly to Chinese.

I. Overview

Full-text retrieval in MySQL makes use of the correlation between query keywords and query column content, and full-text index can be used to improve the speed of matching.

II. Grammar

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

Search_modifier: {IN BOOLEAN MODE | WITH QUERY EXPANSION}

For example: SELECT * FROM tab_name WHERE MATCH (col1,col2) AGAINST (search_word)

The table here needs to be a table of type MyISAM, and the col1 and col2 need to be of type char, varchar, or text. Before querying, you need to establish a full-text index on col1 and col2.

Constraint

Constraint is a kind of "stipulation" that requires what conditions the data needs to meet.

The formal meaning of the constraint type primary key constraint primary key (field name) enables the value of the set field to be used to "uniquely determine a row of data", which means "primary key". The unique constraint unique key (field name) makes the value of the set field "unique" and is naturally distinguishable. Foreign key constraint foreign key (field name) references other table names (corresponding to field names in other tables) so that the value of the set field must already have the value in the corresponding field in the corresponding table. The non-empty constraint not null is actually the "not null" attribute that is written when a field is set. This constraint can only be written on field properties. The default constraint default XX value is actually the "default default" property that is written when setting a field. This constraint can only be written on field properties. Check constraint check (some kind of judgment statement)

For example:

Create table tab1 (age tinyint,check (age > = 0 and age)

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.