MySQL study note _ 3_MySQL create a data table (medium) and study note _ 3_mysql

MySQL study note _ 3_MySQL create a data table (medium) and study note _ 3_mysql
Creating a data table in MySQL 3. Data field attributes

1. unsigned [unsigned]

Space can be doubled, for example,-128 ~ 127 increase to 0 ~ 255

Note: Only numeric fields can be used.

2. zerofill [leading zero]

E.g. createtable if not exists t2 (num int (5) zerofill, price float (7,2) zerofill, name varchar (10 ));

Note: it can only be used in numeric fields, and the unsigned attribute is automatically added.

3. auto_increment [auto increment] # auto increment

When the inserted value is NULL, 0, and left blank, the value is automatically + 1. When an existing value is inserted, an error is returned.

Note: it can only be used as an integer, and the field values cannot be repeated (it must be implemented with other attributes, such as primarykey) # primary; elementary, basic.

E.g. createtable if not exists t3 (id int auto_increment primary key, namechar (10 ));

Insertinto t3 (id, name) values (null, "xiaofang"); # It can be inserted n times in a row. If it is null, it can be changed to 0.

Insertinto t3 (name) values ("xiaofang ");

During insertion, the data is inserted in the order of the maximum number plus 1.

E.g. deletefrom t3 where id> 1 and id <9;

Insert According to the preceding statement.

Select * from t3 order by id;

Deletefrom t3;

Insertinto t3 (name) values ("xiaofang"); # * 5

Select * from t3;

Insertinto t3 (id, name) values (100, "ashun ");

Insertinto t3 (name) values ("jichang") # * 5

Select * from t3 order by id;

Best Practice: it is best to set an ID field for each table to the auto-increment attribute and auto_increment

4. NULL and NOTNULL

NULL: The default value is NULL.

Suggestion: when creating a table, do not insert NULL values for each field, because there are many uncertainties when converting NULL values to other programming languages.

NOTNULL: Not empty

E.g. createtable if not exists t4 (id int not null, name varchar (10) notnull, price double (7,2) not null );

5. default [default value]

E.g. createtable if not exists t5 (id int not null default 0, name varchar (10) notnull default "NULL", price double (7,2) not null default 0.00 );

6. Comprehensive

Createtable users (

Idint unsigned not null auto_increment primary key,

Namevarchar (30) not null default "",

Heightdouble (1.00) not null default,

Ageint unsigned not null default 1,

Sexvarchar (5) not null default "man ");

4. Create an index

1. Primary Key Index [primarykey] # duplicate replication, enabling double entry and intrusion

Purpose: determine the location of a specific data record in the database table,A table can have only one primary key, and the value of the primary key cannot be blank.

Suggestion: We recommend that you define a primary key for each data table!

E.g. 1) create table t7 (id int not null auto_increment primary key, namevarchar (10 ));

2) createtable t7 (

Idint not null auto_increment,

Namevarchar (10) not null '',

Primarykey (id); # specify the primary key index at the end

2. unique index [unique] # unique and unique

Can prevent duplicate values from being created. However, each table can have multiple unique indexes.

Createtable if not exists users (id int not null auto_increment, namevarchar (30) not null default ''unique, age int, primary key (id ));

3. Regular index [index/key]

It is the most important technology that can improve the performance of the database and is the first consideration for database optimization. Indexes can increase the search speed, but slow down insertion, deletion, and modification.

It is an independent data object like a table and can be used when creating a table or separately.

For separate use: createindex ind1 on users (name, age );

Dropindex ind1 on users; # delete an index

Use createtable carts (

Idint not null auto_increment,

Uidint not null,

Sidint not null,

Primarykey (id ),

Keycuid (uid ),

Indexcsid (sid ));

4. Full-text index

Fulltext indexes can only be used for MyISAM Table types, but only for varchar, char, and text. It can also be used in multiple data columns.

Createtable books (

Idint not null auto_increment,

Booknamevarchar (30) not null unique,

Pricedouble,

Detailtext not null,

Fulltext (detail ),

Indexind (bookname ),

Primarykey (id ));

Original query: select * from books where bookname like '% C ++ % ';

Query now: selectbookname, price from books where match (detail) against ('C ++ ');

Select match (detail) against ('C ++ ') from books; # match matching; against relies on;

The query speed is obvious!