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