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

Data table type

First, create a data table

CREATE TABLE IF NOT EXISTS user (

Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

Name VARCHAR (255) NOT NULL UNIQUE

Sex TINYINT NOT NULL DEFUALT 1

Age TINYINT NOT NULL DEFAULT 0

) ENGINE = MyISAM DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS table name (

Field name 1 Type (length) attribute Index

Field name 2 Type (length) attribute Index

Field name 3 Type (length) attribute Index

Field name 4 Type (length) attribute Index

Field name 5 Type (length) attribute Index

...

Field name N Type (length) attribute Index

) ENGINE = MyISAM DEFAULT CHARSET = utf8

Html sets character set utf-8

Mysql sets character set utf8

II. Mysql data type

1. Integer type (emphasis)

Integer type

Number of bytes

Unsigned range

Signed range

TINYINT (Micro Integer)

one

+ 0-+ 255

-128-+ 127

SMALLINT (small Integer)

two

0-65535

-32768-32767

MEDIUMINT (medium Integer)

three

0-16777215

-8388608-8388607

INT (Integer)

four

0-4294967295

-2147483648-2147483647

BIGINT (large integer)

eight

0-188446744073709551615

The width of the data type supported by mysql is specified after that, and the basic form is as follows:

Data type (display width)

Default display width: it can be understood that the default width of an integer type is exactly the width of all values of that data type. The default length is actually the current digit minus one why minus one because the first bit is our symbol bit.

N1 | tinyint (4)

N2 | smallint (6)

N3 | mediumint (9)

N4 | int (11)

N5 | bigint (20)

In the integer type, the zerofill parameter zerofill can be configured to indicate that the display space with insufficient numbers is filled by 0.

The reason for the following error is

Out of range value for column 'n1' at row 1

Your length is out of range.

Note:

If you use zerofill, mysql will automatically add the unsigned (unsigned) attribute, then this integer type can only indicate that the display length of the unsigned counter is one less than the default display width.

When the display width is set, if the inserted data width is larger than the set display width, the data can still be inserted and displayed, but the width of the setting is invalid.

The width of the data cannot be greater than the default width, if it is greater than the default width, then the data has exceeded the maximum value of the type, because the width of the maximum value must be less than or equal to the default width. If a value is greater than the maximum width, it cannot be inserted.

-- the integer type also has a property, AUTO_INCREMENT, which is self-incremented.

-- self-increasing attributes need to be used with the index.

If there is an error below, please add your index content after your self-added attribute.

-ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

-- key (index) index

-- General index (index)

-- unique index (unique) unique index all values cannot be duplicated

-- Primary key index primary key can only have one unique function in a data table. Generally, the setting is self-incrementing field.

Zerofill zero filling

Unsigned unsigned

Auto_increment self-increasing

Can only modify integer types

two。 Floating point number and fixed point number

Floating-point and fixed-point numbers are used to represent decimals in mysql

Floating point numbers: single precision floating point numbers (float) and double precision floating point numbers (double)

Fixed points: DECIMAL

The number of fixed points in storage space is determined according to the accuracy.

The precision of floating-point numbers can be specified in mysql: data type (MPerry D)

M precision is the total length of the data. The decimal point is not occupied.

The length after the decimal point on the D scale

When storing decimals, it is recommended to use decimal type fixed-point numbers to be stored as strings, so the precision is higher than floating-point numbers, and floating-point numbers are prone to errors. This is always the problem of floating-point numbers, if the precision of data is very high, or it is safer to choose fixed-point numbers.

If the scale exceeds the digits, then it will be rounded, and if your precision is the same as the scale, then our integer bits can only be zero.

3. time

-- timestamp

-- 2147483647

INT 2147483647

Because php can have a date function, you can do whatever you want, so we use timestamps to store our php time.

4. String type

String type is the data type used to store strings in mysql. String types include: VARCHAR CHAR TEXT ENUM SET

CHAR and VARCHAR

String type (M) M-the maximum length of the string M

CHAR fixed-length character type 0-255

VARCHAR variable-length character types before 5. 0-255 5. 0 and after 0-65535

The difference between char (5) and varchar (5)

Insert value char occupies words varchar occupies words

1 5 2

123 5 4

1234 5 5

12345 5 6

CHAR fixed length character type takes up as much space as it allocates without calculating the required length to process data quickly.

The VARCHAR variable length character type allocates space according to the length of the string and needs to calculate the length to process the data a little slower.

When to use char and when to use varchar

When the length of the data you insert remains the same, please use the char password and use char.

You need to use varchar to use the varchar user name when the length of the data you insert changes at any time.

ENUM types enumerated types

ENUM ('value 1, value 2, value 3, value n')

N color-represents the nth value n 65535 in the parameter list

The value of ENUM can only go to one element in the list

1/65535

SET Typ

The range of values that are of type set when creating a table is specified in the form of a list

Set ('value 1, value 2, value 3. Value n')

The value of the set type is only 64 elements at most. The value can be one or more other properties similar to enum.

1-64Comp64

Three view the table structure

DESC table name: abbreviated version

DESCRIBE table name

View table creation statement

SHOW CREATE TABLE table name

Four, create a table

1. Table building syntax

CREATE TABLE IF NOT EXISTS table name (

Field name 1 Type (length) attribute Index

Field name 2 Type (length) attribute Index

Field name 3 Type (length) attribute Index

Field name 4 Type (length) attribute Index

Field name 5 Type (length) attribute Index

...

Field name N Type (length) attribute Index

) ENGINE = MyISAM DEFAULT CHARSET = utf8

a. Set the primary key

PRIMARY KEY

Field name data type PRIMARY KEY

b. Set the non-empty constraint of the table

Field name data type NOT NULL

Not empty, just not for null

c. Set the uniqueness of the table

Field name data type UNIQUE

d. Set the self-increment of the table field value

Field name data type AUTO_INCREMENT

If you set self-increment, you must add the index PRIMARY KEY.

e. Set field defaults for the table

Field name data type DEFAULT default

f. The number type of the set field cannot be negative.

Field name data type UNSIGNED

g. Table engine

MyISAM

InnoDB

The difference between MyISAM and InnoDB

Fast reading speed of myisam does not support transactions

InnoDB read speed is slightly slower to support transaction rollback

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.