MySQL data types and common curd statements

1. Integral type

MySQL data type	Meaning (Signed)
tinyint (m)	1 byte range ( -128~127)
smallint (m)	2 byte range ( -32768~32767)
Mediumint (M)	3 byte range ( -8388608~8388607)
Int (m)	4 byte range ( -2147483648~2147483647)
BigInt (M)	8 byte Range (18 +-9.22*10 of the X-square)

If unsigned is added to the value range, the maximum value is doubled, such as the tinyint unsigned value range (0~256).
m in int (m) is the display width in the result set of the select query, does not affect the actual range of values, does not affect the width of the display, do not know what the use of this m.

2. Float type (float and double)

mysql data type	meaning
float (m,d)	single-precision floating-point     8 bit accuracy (4 bytes)      m total number, D decimal place
double (m,d)	double-precision floating-point type     16 bit accuracy (8 bytes) Total number of     m, D decimal place

Set a field defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is actually the same, that is, 6 bits.

3. Fixed-point number

Floating-point types hold approximate values in the database, while the fixed-point type stores the exact values in the database.
The decimal (m,d) parameter m<65 is the total number, d<30 and d<m is the decimal place.

4. String (Char,varchar,_text)

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 characters
VARCHAR (n)	Fixed length, up to 65,535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65,535 characters
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

char and varchar:
1.char (n) If the number of characters in the deposit is less than n, then the space is appended to it, and then the space is removed when queried. So the char type stores a string with no spaces at the end, and varchar is not limited to this.
2.char (n) fixed length, char (4) regardless of whether it is deposited in several characters, will occupy 4 bytes, varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4), A deposit of 3 characters will take up 4 bytes.
the string retrieval speed of the 3.char type is faster than the varchar type.

varchar and text:
1.varchar can specify that the N,text cannot be specified, the internal storage varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), the text is the actual number of characters + 2 bytes.
The 2.text type cannot have a default value.
3.varchar can create indexes directly, text creates indexes to specify the number of characters before. varchar queries are faster than text, and the index of text does not seem to work in the case of all indexes being created.

5. Binary data (_BLOB)

1._blob and _text are stored in different ways, _text stored in text, English storage is case-sensitive, and _blob is stored in binary mode, regardless of case.
The data stored by 2._blob can only be read out in its entirety.
3._text can specify a character set, _blo does not specify a character set.

6. Date and Time type

MySQL data type	Meaning
Date	Date ' 2008-12-2 '
Time	Time ' 12:25:36 '
Datetime	Date Time ' 2008-12-2 22:06:44 '
Timestamp	Automatically store record modification times

If you define a field that is timestamp, the time data in the fields is automatically refreshed when the other fields are modified, so the field of this data type can hold the last time the record was modified.

Properties of the data type

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT	Default value
PRIMARY KEY	Primary key
Auto_increment	Auto-increment for integer type
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

MySQL's curd operation

Query: ' Select *from ' Think_data "'

Insert: "INSERT into Think_data (NAME,PWD) VALUES (' Naem1 ', Pwd1 ')"

Update: ' Update think_data set name= ' testname ' where id=1 '

Delete: "Delete from Think_data whrer name= ' testname '"

The above curd operations can be done in the mysql_query function.

Execute in thinkphp is typically used to execute SQL statements such as INSERT or update, and query is commonly used to execute statements such as SELECT.

MySQL data types and common curd statements