Iv. MySQL data types and operators

4.1. mysql Data type introduction

MySQL supports a variety of data types, primarily numeric types, date/time types, and string types.

Numeric data type: Includes integer types TINYINT, SMALLINT, Mediumint, INT, Bifint, Floating point small data type float, DOUBLE Fixed decimal Type Decimal Date/Time Type: Year, time, DATE, DATETIME, TIMESTAMP String type: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

Integer class

Integer data types in MySQL

Type name	Description	Storage requirements
TINYINT	A very small integer.	1 bytes
SMALLINT	A small integer	2 bytes
Mediumint	A medium-sized integer	3 bytes
Int	An integer of normal size	4 bytes
BIGINT	Large integer	5 bytes

Range of values for different integer types

Data type	have symbols	No sign
TINYINT	-128~127	0-255
SMALLINT	-32768~32767	0~65535
Mediumint	-8388608~8388607	0~16777215
Int	-2147483648~2147483647	0~4294967295
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551615

MYSQL> CREATE TABLE TMP1 (x tinyint,y smallint,z mediumint,m int,n  BIGINT); query ok, 0 rows affected  (0.05 sec) mysql> desc tmp1;+-------+----- ---------+------+-----+---------+-------+| field | type          | null | key | default | extra |+-------+----------- ---+------+-----+---------+-------+| x     | tinyint (4)    |  YES  |     | NULL    |        | |  y     | smallint (6)   | YES  |      | null    |       | |  z     | mediumint (9)  | YES  |      | null    |       | |  m     | int (one)       | yes  |      | null    |       | |  n     | bigint (   | YES  |  )    | null    |       |+-------+---- ----------+------+-----+---------+-------+5 rows in set  (0.00 sec)

Floating-point type and fixed-point number type

Data type	Description	Storage requirements
FLOAT	Single-precision floating-point number	4 bytes
DOUBLE	Double-precision floating-point number	8 bytes
decimal (m,d)	compression" strict "fixed-point number	m+2 bytes

mysql> create table tmp2  (X float (5,1),  y double (5,1), Z DECIMAL (5,1 )); query ok, 0 rows affected  (0.02 sec) mysql> desc tmp2;+-------+----- ---------+------+-----+---------+-------+| field | type          | null | key | default | extra |+-------+----------- ---+------+-----+---------+-------+| x     | float (5,1)    |  YES  |     | NULL    |        | |  y     | double (5,1)   | YES  |      | null    |       | |  z     | decimal (5,1)  | YES  |      |&nbsP null    |       |+-------+--------------+------+-----+- --------+-------+3 rows in set  (0.00 sec)

Date and Time type

Type name	Date format	Date Range	Storage requirements
Year	YYYY	1901~2155	1 bytes
Time	HH:MM:SS	-838:59:59~838:59:59	3 bytes
DATE	Yyyy-mm-dd	1000-01-01~9999-12-31	3 bytes
Datetime	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~ 9999-12-31 23:59:59	8 bytes
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 utc~ 2038-01-19 03:14:07 UTC	4 bytes

Text string type

Type name	Description	Storage requirements
CHAR (M)	Fixed-length non-binary string	M bytes, 1<=m<=255
VARCHAR (M)	Variable length non-binary string	L+1 bytes, l<=m and 1<=m<=255
Tinytext	Very small non binary string	l+1 bytes, l<2^8
TEXT	Small Non binary string	l+2 bytes, l<2^16
Mediumtext	A medium-sized binary string	L+3 bytes, l<2^24
Longtext	Large non-binary string	L+4 bytes, l<2^32
Enum	Enumeration type, only one enumeration string value	1 or 2 bytes, depending on the number of enumeration values (max. 65535)
SET	A setting in which a string object can have 0 or Multiple Set Members	1,2,3,4 or 8 bytes, depending on the number of members of the collection (up to 64 members)

Binary string type

Type name	Description	Storage requirements
BIT (M)	Bit field type	Approx. (m+7)/8 bytes
BINARY (M)	Fixed-length binary string	M bytes
VARBINARY (M)	Variable-length binary string	M+1 bytes
Tinyblob (M)	Very small blob	l+1 bytes, l<2^8
BLOB (M)	Small blob	l+2 bytes, l<2^16
Mediumblob (M)	Medium-sized BLOBs	L+3 bytes, l<2^24
Longblob (M)	Very large blob	L+4 bytes, l<2^32

4.2. Introduction to common operators

The operator joins each operand in an expression, which is used to indicate the operation performed on the operand. Operators make it more flexible to work with the data in a table, with the usual types of operators: arithmetic, comparison, logical, and bitwise operators.

Arithmetic operators

Arithmetic operators are the most basic operators in SQL, including addition, subtraction, multiplication, addition, and redundancy.

+	addition operation
-	subtraction operation
*	multiplication operation
/	division operation, return quotient
%	remainder operation, return Remainder

mysql> create table tmp14  (Num int); query ok, 0 rows affected  (0.03 sec) mysql> insert into tmp14  value (64); query ok, 1 row affected  (0.01 sec) mysql> select num,num+10,num-3+5 , num+36.5 from tmp14;+------+--------+---------+----------+| num  | num+10  | num-3+5 | num+36.5 |+------+--------+---------+----------+|   64 |      74 |      66 |     100.5 |+------+--------+---------+----------+1 row in set  (0.00 sec) mysql>  select num,num/0,num%0 from tmp14;+------+-------+-------+| num  |  num/0 | num%0 |+------+-------+-------+|   64 |  null |   null |+------+-------+-------+1 row in set  (0.00 SEC) 

Comparison operators

Operator	Role
=	Equals
<=>	Security is equal to
<> (! =)	Not equal to
<=	Less than or equal
>=	Greater than or equal
>	Greater than
Is NULL	Determines whether a value is null
is not NULL	Determines whether a value is not NULL
LEAST	Returns the minimum value when there are two or more parameters
Greatest	Returns the maximum value when two or more parameters are
Between and	Determines whether a value falls between two values
ISNULL	Same as is NULL action
Inch	To determine that a value is any value in the in list
Not in	To determine that a value is not a value in the in list
Like	Wildcard match
Regexp	Regular expression Matching

logical operators

Operator	Role
Not OR |	Logical Non-
And OR &&	Logic and
OR OR | |	Logical OR
Xor	Logical XOR or

Bitwise operators

Operator	Role
|	Bit or
&	Bit and
^	Bit XOR or
<<	Bit left shift
>>	Bit right Shift
~	bitwise inverse, reverses all bits

Precedence of Operators

Priority level	Operator
Minimum	= (Assignment operation),: =
	| |,or Xor &&,and Not Between,case,when,then,else = (comparison operation), <=>,>=,>,<=,<,<>,!=,is,like,regexp,in | & <<,>> -,+ *,/(DIV),% (MOD) ^ -(symbol), ~ (bit reversal)
Highest	！

This article is from the "Wind and Drift" blog, please be sure to keep this source http://yinsuifeng.blog.51cto.com/10173491/1952481

Iv. MySQL data types and operators