Characteristics of relational database
1, the data appears in tabular form
2, the names of the various records for each behavior
3, the data field corresponding to the data name for each column
4 Many rows and columns form a table
5 several forms composed of databases
Terms
Database : The collection of associated tables
Data sheet:: Table is the matrix of the data
Columns: data elements that contain the same data
Rows: tuples or records , a set of related data
Redundancy : Store twice times data, reduce performance, but improve data security (frequent switching between two tables, you can add the contents of Table 1 to table 2)
Primary key: The primary key is unique. You can use a primary key to query the data
foreign key : used to correlate two tables
Compound key : Use multiple columns as an index key
index : Use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.
referential integrity: referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity are the integrity constraints that a relational model must meet in order to ensure data consistency
Data Type
A data type is a basic rule that defines what data can be stored in a column and how that data is actually stored
In practical programming, the basic principle of selecting a column's base type is: The minimum type of data that can be satisfied
String data type:
The length of the fixed-length string is specified when the table is created, does not allow more than the specified character data length, the actual storage space is as many as specified, char is afixed-length string
Variable-length string stores text with variable lengths, saved only by specified data, and text is a variable-lengthstring
Comparison: variable-length string data types are flexible, fixed-length string data types are efficient, MySQL does not allow for variable-length strings (or variable portions of a column) to be indexed
Data type |
Description |
Main purpose |
Char |
A fixed-length string of 1~255 characters whose length must be specified at creation time, otherwise MySQL is assumed to be char (1) char (n=1~255) CHAR (8), the character you enter is less than 8 o'clock, and it will be followed by a null value. When you enter a character that is larger than the specified number, it intercepts the characters that are out of the bounds. |
Mainly used for headings, authors and other data types with small amounts |
Varchar |
Variable lengths of up to 255 bytes, such as when you specify varchar (n) on creation, you can store a variable-length string of 0~n characters. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation. |
Mainly used for indeterminate lengths such as: path, article content (Varchar (800)), etc. |
Text |
The text field with a maximum length of 64K and a regular size of a variable length. |
Article content |
Tinytext |
Same text with a maximum length of 255 bytes |
Meduimtext |
Same text with a maximum length of 16K |
Longtext |
Same text with a maximum length of 4GB (plain text, usually not to 4G) |
Blob |
Text is case-sensitive, two blobs are not distinguished, others are the same. |
A binary type can store any data, such as text, images, multimedia, and so on. Also includes Tityblob,mediumblob,longblob |
Two special types of |
Enum |
Accepts a string from a predefined collection of up to 64K strings |
enumeration, which can hold only one of the listed values or null for that type of column |
SET |
accepts 0 or more strings of a predefined set consisting of up to 64K strings |
The value in the specified column must come from a specified value in a specific collection, for example: set (' C ', ' H ', ' P ', ' f ', ' s ', ' j ', ' A ', ' B ') |
Note: Regardless of the string data type in any form, the string value must be within quotation marks (usually a single quotation mark is better), if the numeric value is used in the calculation, it should be stored in the numeric data type column, if used as a string (such as phone number, postal code) should be saved in the String data type column.
Numeric type: (int and floating point type)
Integral type
signed value: 32768 to 32767 (-2^15 to 2^15–1)
data type |
value range |
storage space |
description |
main purpose |
tinyint[(m)] |
signed values: 128 to 127 (-2^7 to 2^7– 1) |
1 bytes |
very small integer |
The age of the person or number of siblings, then tinyint is the most appropriate |
smallint[(m)] | TD style= "FONT-SIZE:16PX; line-height:1.8 "align=" center ">
2 bytes |
small integer |
For category ID, column smallint (5) |
Mediumint[(m)] |
Signed values: 8388608 to 8388607 (-2^23 to 2^23–1) Unsigned values: 0 to 16777215 (0 to 2^24–1) |
3 bytes |
Medium integer |
The "Apply to Article ID" Mediumint can represent a value of millions of and can be used for more types of values, but the storage cost is large |
Int[(m)] |
Signed values: 2147683648 to 2147683647 (-2^31 to 2^31-1) Unsigned values: 0 to 4294967295 (0 to 2^32–1) |
4 bytes |
General integer |
Applies to Dates
|
Bigint[(m)] |
Signed values: 9223372036854775808 to 9223373036854775807 (-2^63 to 2^63-1) Unsigned values: 0 to 18446744073709551615 (0 to 2^64–1) |
8 bytes |
Large integer |
Floating point Type
data type |
value range |
storage space |
description |
main purpose |
float |
depends on precision |
variable |
|
double occupies twice times the space of float. Unless high-precision or wide-range values are particularly required, the data should generally be represented by a float type that uses only half the cost of storage. |
float[(M, d)] |
minimum non 0 value: ±1.175494351e–38 |
4 bytes |
single precision floating point number |
Equivalent to float (4), but specifies the display width and number of decimal places |
Double[(M, D)] |
Minimum non 0 value: ±2.2250738585072014e–308 |
8 bytes |
Double-precision floating-point number |
Equivalent to float (8), but specifies the display width and the number of decimal places |
Decimal (M, D) |
Signed values: 2147683648 to 2147683647 (-2^31 to 2^31-1) Unsigned values: 0 to 4294967295 (0 to 2^32–1) |
4 bytes |
General integer |
Applies to Dates
|
Bigint[(m)] |
M bytes (MySQL < 3.23), m+2 bytes (mysql > 3.23) |
M+2 |
The range of values depends on M and D |
Floating point number, stored in char, range depends on display width m |
Date and time
data type |
storage bytes |
value range |
description |
DATE |
4 |
1000-01-01--9999-12-31 |
Shown in YYYY-MM-DD format, if we want to insert the current system time, you can insert current_date or now (). |
Time |
3 |
-838:59:59--838:59:59 |
Although the hour range is generally 0~23, MySQL expands the hour range for time and supports negative values in order to represent certain special intervals. Assigning a value to the time type, the standard format is ' HH:MM:SS ', but not necessarily in this format. If the ' D HH:MM:SS ' format is inserted, it is similar to ' (D*24+HH): Mm:ss '. Inserting ' 2 23:50:50 ', for example, is equivalent to inserting ' 71:50:50 '. If you insert a ' hh:mm ' or ' SS ' format, the effect is to assign a value of zero to other values that are not represented by a bit. For example, insert ' 30 ', which is equivalent to inserting ' 00:00:30 ', if inserting ' 11:25 ', it is equivalent to inserting ' 11:25:00 '. In MySQL, for the ' HHMMSS ' format, the system can be automatically converted to a standard format. |
datetime |
8 |
1000-01-01 00:00:00--9999-12-31 23:59:59 |
|
timestamp |
4 |
19700101080001--20380119111407 |
timestamp value range is small, no datetime value range is large, so enter the value must be guaranteed within the range of timestamp. Its insertion is also similar to inserting other date and time data types. So how does the timestamp type insert the current time? First, you can use Current_timestamp; second, enter NULL, the system automatically enters the current TIMESTAMP; third, without any input, the system automatically enters the current TIMESTAMP. There is a special point: the value of timestamp is associated with the time zone. |
Year |
1 |
1901--2155 |
There are three ways to copy the year type: the first is to insert a 4-bit string directly or 4-bit numbers, and the second is to insert a 2-bit string, in which case inserting ' 00 ' ~ ' 69 ' is equivalent to inserting 2000~2069, or inserting ' 70 ' ~ ' 99 ', which is equivalent to insert 1970~ 1999. In the second case, if it is ' 0 ', it is the same as inserting ' 00 ', which is the 2000; the third is to insert a 2-digit number, which differs from the second (inserting a two-bit string) only: If you insert a number 0, it means 0000, not 2000. So when assigning a value to year type, be sure to distinguish between 0 and ' 0 ', although the difference between the two quotes, but the actual effect is indeed a difference of 2000 years. |
MySQL Data type concept