MySQL Learning notes

-----------------beginner MySQL, always update--------------


/* Data operation */------------------

--add INSERT [into] table name [(field list)] Values (value list) [, (Value list), ...]        --If the list of values you want to insert contains all the fields and the order is consistent, you can omit the field list.        --Multiple data records can be inserted simultaneously! REPLACE is exactly the same as INSERT, interchangeable.
INSERT [into] table name SET field name = value [, Field name = value, ...]

--Check the SELECT field list from table name [other clauses]--multiple fields that can come from multiple tables--other clauses may not be used--the field list can be replaced by *, representing all fields
 
--delete delete from table name [delete condition clause] without a conditional clause, all
  
--Change the Update table name SET field name = new value [, Field name = new value] [UPDATE condition]

/* Character Set encoding */------------------
--MySQL, database, table, field can be set encoding
--data encoding does not need to be consistent with client code
Show VARIABLES like ' character_set_% '--View all character set encoding entries
The encoding used by the Character_set_client client to send data to the server Character_set_results server side Returns the result to the client using the encoding Character_set_connect Ion Connection Layer Encoding
    
SET variable name = variable Value
Set character_set_client = GBK;
Set character_set_results = GBK;
Set character_set_connection = GBK;
SET NAMES GBK; --equivalent to completing the above three settings
 
--Proofing Set
The proofing set is used to sort show CHARACTER set [like ' Pattern ']/show CHARSET [like ' pattern '] View all character sets show COLLATION [like ' pattern '] View all proofing sets CharSet character set encoding set character set encoding collate proofing set encoding set proofing set encoding

/* Data type (column type) */------------

1. Numeric type

--A. Integral type----------
Type byte range (signed bit)
Tinyint 1 bytes-128 ~ 127 unsigned bit: 0 ~ 255
SmallInt 2 bytes-32768 ~ 32767
Mediumint 3 Bytes-8388608 ~ 8388607
int 4 bytes
BigInt 8 bytes
        
Int (m) m indicates total number of digits

-The symbol bit is present by default, unsigned property modification
-Display width, if a number is not sufficient to define the number of bits set when the field, the previous 0 fill, Zerofill Property Modification Example: Int (5) Inserts a number ' 123 ', after filling for ' 00123 '-the smaller the better if the requirement is met.
1 indicates bool value TRUE, 0 denotes bool value false. MySQL does not have a Boolean type, represented by integers 0 and 1. The common tinyint (1) represents the Boolean type.

--B. Floating-point----------
Type byte range
Float (single precision) 4 bytes
A double (double) 8-byte floating-point type supports both the symbol bit unsigned property and the display width Zerofill property.  Different from the integral type, the front and rear will fill 0. When defining floating-point types, you specify the total number of digits and decimal digits.
Float (M, d) double (M, d) M represents the total number of digits, and D represents the number of decimal digits. The size of M and D determines the range of floating-point numbers.     is different from the fixed range of the integral type.     M represents both the total number of digits (excluding the decimal point and the sign) and also the display width (all display symbols are included).     Support scientific notation. A floating-point number represents an approximation.

--C. Fixed-point number----------decimal-Variable length decimal (M, D) M also represents the total number of digits, and D represents the number of decimal digits. Save an exact value without changing the data, unlike the rounding of floating-point numbers. The floating-point number is converted to a string to be saved, and every 9 digits is saved as 4 bytes.

2. String type

-A. Char, varchar----------char fixed-length string, fast, but wasted space
varchar variable length string, slow, but save space m represents the maximum length that can be stored, this length is the number of characters, non-bytes. Different coding, the space occupied by different.
Char, up to 255 characters, regardless of encoding.
varchar, up to 65535 characters, related to encoding.    A valid record cannot exceed 65,535 bytes maximum. UTF8 maximum is 21,844 characters, GBK maximum is 32,766 characters, latin1 maximum is 65,532 characters

VarChar is long and requires a storage space to hold the length of the varchar, and if the data is less than 255 bytes, a byte is used to hold the length, whereas two bytes are required to save.
The maximum effective length of varchar is determined by the maximum row size and the character set used.    The maximum effective length is 65532 bytes, because the first byte is empty when the string is stored, no data exists, and then two bytes are required to hold the length of the string, so the effective length is 64432-1-2=65532 bytes. Example: If a table is defined as CREATE table TB (c1 int, C2 char (+), C3 varchar (N)) Charset=utf8; Q What is the maximum value of n? Answer: (65535-1-2-4-30*3)/3

--B. Blob, text----------
Blob binary string (byte string) Tinyblob, blob, Mediumblob, Longblob
Text non binary string (character string) Tinytext, text, Mediumtext, Longtext
When text is defined, it does not need to define the length, nor does it calculate the total length.
The text type cannot be given the default value when it is defined
 
--C. Binary, varbinary----------
Similar to char and varchar, used to hold a binary string, that is, to hold a byte string rather than a character string.
char, varchar, text corresponds to binary, varbinary, blob.

3. DateTime types are typically saved with an integer timestamp, because PHP can easily format the timestamp.

DateTime 8 bytes Date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59
Date 3 byte dates 1000-01-01 to 9999-12-31
Timestamp 4 bytes Timestamp 19700101000000 to 2038-01-19 03:14:07
Time 3 bytes -838:59:59 to 838:59:59
Year 1 byte years 1901-2155

DateTime "Yyyy-mm-dd hh:mm:ss"
Timestamp "Yy-mm-dd hh:mm:ss" "Yyyymmddhhmmss" "Yymmddhhmmss" Yyyymmddhhmmss             Yymmddhhmmssdate "Yyyy-mm-dd" "Yy-mm-dd" "YYYYMMDD" "YYMMDD" YYYYMMDD Yymmddtime "Hh:mm:ss" "Hhmmss" Hhmmss
Year "YYYY" "yy" YYYY yy
        
4. Enumerations and collections
--enum (enum)----------

Enum (Val1, Val2, Val3 ...) Make a single selection in a known value.    The maximum number is 65535. The enumeration value is saved with a 2-byte integer (smallint).    Each enumeration value is incremented by the position in which it was saved, starting at 1.    behaves as a string type, and the store is an integral type.    The index of the null value is null. The index value of an empty string error value is 0.

--Collection (set)----------
 
Set (Val1, Val2, Val3 ...)

CREATE Table tab (gender set (' Male ', ' female ', ' none '));
Insert into tab values (' Male, female '); You can have up to 64 different members. stored in bigint, a total of 8 bytes.    Take the form of bit arithmetic. When you create a table, trailing spaces for the value of the set member are automatically deleted.

/* Select Type */
--PHP angle
1. Function satisfaction
2. The storage space is as small as possible, the processing efficiency is higher
3. Consider compatibility issues

--IP Storage----------
 
1. Only storage, available strings

2. If you want to calculate, find, etc., can be stored as a 4-byte unsigned int, i.e. unsigned
1) PHP function conversion Ip2long can be converted to integral type, but the problem of carrying symbols appears.     You need to format an integer that is unsigned.     Use the sprintf function to format the string sprintf ("%u", Ip2long (' 192.168.3.134 ')); Then use LONG2IP to turn the integer back to the IP string
2) MySQL function conversion (unsigned integer, UNSIGNED) Inet_aton (' 127.0.0.1 ') converts the IP to integer inet_ntoa (2130706433) to the integer type to IP

MySQL Learning notes