20141230 MySQL numeric type and column properties one

Enumeration String

The enumeration string refers to the definition of the string to be stored at the beginning of what is to be stored, and then when the data is stored can only store the already defined string, can only use any one string. (Radio box)

Syntax: enum

Field name Enum (string 1, String 2,...); Enumeration holds data up to 2 bytes and can store up to 65,535 data

Meaning of enumerations

1. Specification data: Data that is not maintained cannot be inserted

2. Space saving: The actual data stored is digital

Although SQL is a strongly typed language, SQL can automatically convert data types (like PHP)

Select has a feature: The following can be followed by any expression

Prove that the enumeration stores a number

The relationship of the enumerated string to the numeric value

Enumerations can be inserted into data by numeric values because they enumerate the values of the internal real store.

Enumerate storage Principles

Enumeration of applications

1. Fixed immutable, but data with multiple values can use enumerations

Collection String

The collection string is roughly the same as the enumeration string, but the collection data refers to some or all of the data that can be selected in the middle of the defined value. Multi-Select

Syntax: Set

Field Name Set (String 1, String 2,...);

The data in the collection, each of which takes a bit of one byte to represent it, and then saves the value but all the bits are converted into decimal.

The principle of the placeholder of data in the collection

When converting, you need to invert the binary and then convert it to decimal.

Set takes up to 8 bytes to store data: 8 bytes Total 64 bits, up to a collection can hold only 64 elements, but the combination has 2^64.

The meaning of the collection

1. Specification of data, only defined data can be stored

2. Save space

Although collections and enumerations can save space for the database and can standardize the data, as a PHP programmer, PHP is more difficult to maintain data, so PHP programmers basically do not use these two data types.

Time Date Type

data types used in SQL to store time or date

type	display Format	Take value	Storage Space	0 Value
Datetime	YYYY-MM-DD HH:MM:SS	' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '	8	0000-00-00 00:00:00
TIMESTAMP	YYYY-MM-DD HH:MM:SS	Yes ' 1970-01-01 00:00:00 ' to 2038-01-19 03:14:07	4	0000-00-00 00:00:00
DATE	Yyyy-mm-dd	' 1000-01-01 ' to ' 9999-12-31	3	0000-00-00
Time	HH:MM:SS	-838:59:59 ' to ' 838:59:59 '	3	00:00:00
Year	YYYY	1901 to 2155	1	0000

DateTime: DateTime, 8 bytes of storage

Timestamp: timestamp, using standard integer storage, not integer data, but as data in datetime format

Date part of Date:datetime

The time portion of the time:datetime, time is not the point of time, it is the period

Year: Vintage, 1 bytes stored, can only represent 255 years

Time format: Represents a time period

As a PHP programmer, there is basically no time format for SQL, it is time stamp using PHP

In MySQL, any record cannot be longer than 65,535 bytes in length.

The theoretical data length of varchar is 65,535 characters

Gbk/utf8 Code Maximum varchar length

In SQL, all fields are allowed to be empty as long as there is one field, so the whole record needs to leave a byte to hold null, and if more than one field is empty, only one byte is required.

Text text type does not occupy the total length of the record, is the additional space to store data, but the text field itself to occupy a certain length of data (10 bytes)

Column Properties

In addition to data type data specifications for columns, there are additional restrictions to constrain fields.

Column properties: Null/not null,default,primary key,auto_increment,comment,unique Key

NULL

Whether the field is allowed to be empty, not allowed to be empty, or not NULL.

Syntax: Field name data type Null/not NULL, default is NULL

Default Value

Keyword default, when the structure is defined, if the field is not assigned data, the system uses the default data to populate the data.

Use of default values

1. To use the default value, do not insert data into the field (field list cannot appear corresponding field)

2. To use the default value, you can use the Defaults keyword where the data value is inserted

PRIMARY Key

The primary key, the field that is decorated with the primary key, cannot be null, and there is no duplication of any data. The ability to uniquely identify a record. A table can have only one primary key.

To add a primary key:

1. Add the primary key keyword after the field you want to master key

2. After all the fields, specify the primary key, you can specify the composite primary key (multi-character Chini)

Syntax: Primary key (Field list)

3. Assuming that the table already exists, but you need to increase the primary key, you can increase the primary key by modifying the structure of the table: you must implement a guarantee that the field's data is not duplicated.

A) Modify the field to add the primary key property to the field

b) The primary key (composite primary key) can be added directly by modifying the table structure

Primary effect: Unique, cannot insert duplicate data

The primary key cannot be modified, but can be deleted

Syntax: Modifying table structure

ALTER TABLE name drop PRIMARY key;

Self-growth

Auto_increment, when a field has a self-growth property, if the field is added to the data, the data is not specified, then the system automatically automatically +1 on the original basis, indicating that the field type must be integral type.

Auto-grow is usually used with the primary key

Self-growing applications

1. Do not assign a value to the current field

2. Calls that can be displayed using the Null/default keyword for the autogrow field

Autogrow is able to populate the corresponding data because the corresponding values have been specified in the table's options

Modify autogrow: Modify the auto-grow next sub-growth value, but the modified value can only be larger than the value of the current data, not small.

ALTER TABLE name Auto_increment = value

Effect

Delete self-growth

ALTER TABLE table name modify field no longer adds Auto_increment property;

Auto_increment often paired with primary key: Delete primary key

To delete a primary key that has autogrow, you must first delete the autogrow and then delete the primary key.

Self-growth control: initial value and step size

Show variables like ' auto_increment% ';

Note: A single table can only have one self-growth.

Modify Step Size

Set auto_increment_increment = 2;

Effect

This modification is valid for the current user's current connection (session level)

Homework:

1. Create a database of teaching management systems: Students, classes, teachers, etc. to determine the corresponding attributes and data types according to the needs of different data

2. Making a small website: teaching Management System

A) login verification to view all student information

b) The teacher is able to view all the information

20141230 MySQL numeric type and column property one