20141230 MySQL numeric type and column properties one

Review

Database basics, relational databases (rows/Records, columns/fields, SQL)

Basic SQL Operations: library operations, table operations (fields), and data operations

Character

Proofing Sets

1. What is a proofing set?

The collation set refers to the way in which the data in the same character set is compared, and the set of specifications for comparison is called the proofing set.

Collation Set classification

_bin: Binary comparison, case-sensitive

_ci:case insensitive, case insensitive, case insensitive

_cs:case sensitive, case-sensitive, uppercase and lowercase

See what proofing sets are supported by the database

Show collation;

A total of 197 proofing Sets

The implementation of the proofing set: Specify the proofing set when creating a data table (library): Collate = Proofing Set

Inserting data

Use of proofing sets: automatically enabled when data needs to be compared

The collation set is used when sorting the data.

Syntax: After the Where condition, use the order by field to sort [Asc|desc]

ASC: Ascending, is the default

Desc: Descending

Although Chinese can be compared, but there is no comparative significance, GBK support Chinese comparison, UTF8 not support, GBK comparison is not Chinese itself, only than pinyin.

Conclusion: The proofing set of a table should be specified when the table is created, but not when the table already exists (data) to try to modify the proofing set, the modification is invalid.

garbled Problem Solving

Garbled reason: Two data sets are inconsistent.

Ideal solution: three yards in Unity

Storage Engine

MySQL offers a lot of storage engines to choose from, but the usual ones are MyISAM and InnoDB, as these two are free.

All Storage engines

5.5 Previous storage Engine The default is myisam,5.5 after all is InnoDB.

Oracle's acquisition of Sun has enabled MySQL to stop technical support for the MyISAM storage engine and to use all teams to support InnoDB

InnoDB and MyISAM differences

1. The file is stored in a different format

2. MyISAM BULK INSERT and query efficiency, while the innodb of the high efficiency of the modification and deletion

3. Transaction security only InnoDB support, MyISAM does not support

4. InnoDB supports foreign keys, MyISAM does not support

field (column) type

1. What is a field type?

The so-called field type refers to the type of data that the current field can store

The SQL language is a strongly typed language, so you must specify the corresponding data type for the field at the time the table is created, and only the specified data type when the data is stored.

Data Type Classification

In SQL, data types are also divided into three categories: numeric, String, date-time

Numeric: Integer, Decimal (floating-point and fixed-point)

String type: fixed length, indefinite length, text, collection, enumeration

DateTime: Time date, date, time, year, timestamp

S

Numeric Type Integral type

The data type that holds the integer. There are different needs in life to use the different range of integral types, and SQL needs to be based on different requirements to set different integral type (allocate different memory) to record, improve the utilization of space.

The integral type is divided into the following five kinds

Tinyint: Mini integer, 11-byte storage, up to 256 data

smallint: Small integer, 2-byte storage, up to 65,536 data

Mediumint: Medium integer, 3-byte storage

int: Standard integer, 4-byte storage

bigint: Large integer, 8-byte storage

Create a reshape table

Inserting data

In SQL, numeric data is signed by default, so there are positive and negative points that should be split into the data

type	bytes	minimum Value (signed / no sign)	Maximum value (signed / no sign)
TINYINT	1	-128/0	127/255
SMALLINT	2	-32768/0	32767/65535
Mediumint	3	-8388608/0	8388607/16777215
Int/intege	4	-2147483648/0	2147483647 /4294967295
BIGINT	8	-9223372036854775808/0	9223372036854775807/18446744073709551615

No sign

Syntax: After the data type, use the unsigned keyword

View Data Structures

To do this without changing the original value size, you can only make 0 padding on the left side of the data

The default system does not automatically use 0 padding and requires 0 padding according to the instructions, Zerofill

Syntax: After data type Zerofill

Effect

Display width considerations

Decimal Type

Float type: Decimal type with possible loss of precision

FLOAT: single-precision floating-point type, 7 accurate values around

Double: dual-precision floating-point type, around 15 bits

Floating-point usage syntax

Field Float/float (m,d)

m represents the entire length, and D represents the length of the fractional part

Create a table

Inserting data

Floating point data query: The system will automatically lose all data after the precision, rounding

Application of floating-point numbers: If the amount of data is particularly large, the data that is not high in precision will use floating point.

Fixed-point type

Decimals that do not lose precision within the specified range.

Syntax: decimal (M,D), M total length, D fractional part length

Fixed-point applications

String Type

String types are divided into: fixed-length strings, variable-length strings, text strings, collection strings, and enumeration types

Fixed length string

Fixed length refers to the space allocated by the disk is a fixed-size storage space, but the data stored in it can be no longer space.

Fixed length: Char

Syntax: Field name char (L), L represents the length of the character that can be stored, the maximum length of L is 255

variable length string

Variable length refers to the allocation of disk space based on what is actually stored.

Variable length: varchar

Syntax: the field name varchar (l), L represents the length of the character that can be stored, the maximum value of L is theoretically 65535, because varchar is longer, so the length of the actual stored data is unknown, so any variable length data need to open up an additional 1 or 2 bytes to store the length of the data.

Fixed length vs. variable length: UTF8

String	Char (4)	Varchar (4)	char actual byte length	varchar actual byte length
A	A	A	4 * 3 (UTF8) = 12	1 * 3 (UTF8) + 1 = 4
Abcd	Abcd	Abcd	4 * 3 (UTF8) = 12	4 * 3 (UTF8) + 1 = 13

Application of fixed length and variable length

1. Length fixed small string using fixed length, mobile phone number, ID card, MD5 encryption, etc.

2. Length is not fixed small string use variable length, name, hometown, home address, etc.

3. Use a file string if the string length is longer (usually more than 255 characters)

The advantages and disadvantages of fixed length and variable length

1. Fixed length: wasted space, but high reading efficiency

2. Variable length: Saves space, but reduces efficiency

In Wamp, MySQL uses a compact mode that does not use strict mode to constrain data, but a standalone installation of MySQL uses strict mode.

Difference

1. Error handling is different: In thin mode, if there is an error, the system will try to reduce the error cost (the data is too long to intercept), in strict mode, the direct error.

2. The processing of an empty string is different: When the primary key grows

text string

Text strings are divided into two types: character text, binary text

Text string: Text

Binary string: Blob

Images or files can be stored, but not directly stored, but the location (path) of the corresponding file is stored.

Data that is generally considered to be more than 255 characters are stored using a text string.

20141230 MySQL numeric type and column properties one