MySQL Learning note _3_mysql creating data tables (Medium)

 MySQLCreate data table (medium)Third, data field properties

1 , unsigned "Unsigned"

You can increase the space by a factor of -128~127 to 0 , for example . ~ 255

Note: Can only be used in numeric fields

2 , Zerofill "Leading 0"

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

Note: Can only be used in numeric fields, automatically with unsigned attributes

3 , auto_increment "self-increment" #auto Automatic; Increment increment, Increase

when the insertion value is: NULL , 0 , when left blank, automatically +1 when an existing value is inserted, an error is made

Note: Only for integers, field values are not allowed to be duplicated (need to be implemented in conjunction with other properties, such as: PrimaryKey ) #primary Primary , 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"); can be inserted continuously N Times , NULL can be replaced by 0

insertinto t3 (name) VALUES ("Xiaofang");

when plugged in, the maximum number of 1 the sequential insertion

e.g. Deletefrom t3 where ID >1 and ID <9;

Then follow the previous statement to insert

Select* from T3 order by ID;

Deletefrom T3;

insertinto t3 (name) VALUES ("Xiaofang"); # * 5

select* from T3;

insertinto t3 (id,name) VALUES ("Ashun");

insertinto t3 (name) VALUES ("Jichang") # * 5

Select* from T3 order by ID;

Best Practice: Each table is best set to a ID field, set to self-growing property, auto_increment

4 , NULL and the Notnull

NULL : Default is empty

Recommendation: When creating a table, do not insert a null value for each field because NULL There are many uncertainties in the conversion of values to other programming languages.

Notnull : Non-empty

e.g. createtable if not exists t4 (ID int. not null,name varchar () 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 () 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 (+) NOT NULL default "",

Heightdouble (10,2) NOT null default 1.00,

Ageint unsigned NOT NULL default 1,

Sexvarchar (5) NOT null default ' man ');

Iv. Creating an index

1, primary key index "PrimaryKey" #duplicate copy, so double entry Enter, intrusion

Role: Determines the location of a particular data record in the database table, a single primary key, and the value of the primary key cannot be null.

Recommendation: It's a good idea to 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 (TEN) NOT NULL ",

PrimaryKey (id)); # Specify the primary key index at the end

2. Unique index "unique" #unique Unique, unique

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

CreateTable If not EXISTS users (ID int. NOT NULL Auto_increment,namevarchar (a) NOT null default ' Unique,age int,primary Key (ID));

3. General index "index/key"

Is the most important technology, can improve the performance of the database, is the first consideration of database optimization. Indexes can improve the speed of lookups, but they slow down insertions, deletions, and modifications.

As separate data objects as tables, can be used when tables are created, or they can be used alone

When used alone:createindex ind1 on users (name,age);

Dropindex ind1 on users; # Delete index

Created with:createtable Carts (

Idint NOT NULL auto_increment,

Uidint NOT NULL,

Sidint NOT NULL,

PrimaryKey (ID),

Keycuid (UID),

Indexcsid (SID));

4. Full-Text Indexing

The fulltext type index can only be used on MyISAM table types and only on Varchar,char,text . It can also be used on multiple data columns.

CreateTable Books (

Idint NOT NULL auto_increment,

Booknamevarchar (+) is 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 leaning, leaning;

Can obviously query the speed!

MySQL Learning note _3_mysql creating data tables (Medium)