Mysql-6-data types and operators, mysql-6-Data Types

Mysql-6-data types and operators, mysql-6-Data Types

1. mysql Data Type

(1) numeric data type: including integer tinyint, smallint, mediumint, int, bigint, float and double Floating Point decimal type.

(2) date/time type: including year, time, date, datetime, and timestamp.

(3) string type: including char, varchar, binary, varbinary, blob, text, enum, and set

(4) binary type: including bit, binary, varbinary, tinyblob, blob, mediumblob, and longblob.

 

2. Integer type

Type name	Description	Storage Type
Tinytin	Small integer	1 byte
Smallint	Small integer	2 bytes
Mediumint	An integer of medium size	3 bytes
Int	Integer of normal size	4 bytes
Bigint	Big integer	8 bytes

 

3. floating point type and fixed point type (decimal number)

Type name	Description	Storage Requirements
Float	Single-precision floating point number	4 bytes
Double	Double-precision floating point number	8 bytes
Decimal	Number of strictly compressed points	M + 2 bytes

Decimal is stored in strings, unlike float and double.

 

Example: mysql> create table ex2 (a float (4.23), B double (4.26), c decimal (); mysql> insert into ex2 values, 4.234); mysql> select * from ex2; + ------ + | a | B | c | + ------ + | 4.2 | 4.3 | 4.2 | + ------ + 1 row in set (0.02 sec) M becomes precision: indicates the total number of digits; D becomes scale: indicates the number of digits in decimal places.

 

 

4. Date and Time types

Type name	Date Format	Date range	Storage Requirements
Year	Yyyy	1901-2155	1 byte
Time	Hh: mm: ss	-838: 59: 59	3 bytes
Date	Yyyy-mm-dd		3 bytes
Datetime	Yyyy-mm-dd hh: mm: ss		8 bytes
Timestamp	Yyyy-mm-dd hh: mm: ss		4 bytes

Insert into ex4 values (now (); indicates the current time.

 

5. string type

 

Type name	Description	Storage Requirements
Char	Non-binary string with Fixed Length	M bytes, 1 <= m <= 255
Varchar	Variable Length non-binary string	L + 1 byte, where L <= M and 1 <= m <= 255
Tinytext	Very small non-binary string	L + 1 byte, where L <28
Text	Small non-binary string	L + 2 bytes, L <28
Mediumtext
Longtext
Longtext	Enumeration type. Only one enumerated string value is allowed.	1 or 2 bytes
Set	A set of string objects that can have zero or more

 

6. Binary type

Type name	Description
Bit	Bit Field Type
Binary	Fixed-length Binary Character Set
Varbinary	Variable-length binary string
Tinyblob	Very small blob
Blob	Small blob
Mediumblob	Blob of medium size
Longblob	Very large blob

 

7. How to Select a data type

(1) integer and floating point number

If the inserted data does not require decimal part, the data is stored as an integer. If the decimal part is required, the floating point type is used.

(2) floating point and fixed point

Float and double are different from decimal of the fixed point type. When the length is fixed, the floating point type can indicate a larger range. When the precision requirement is high, you must use a fixed-point decimal for storage.

Be sure to use a decimal of the fixed point type when comparing values.

(3) Date and Time types

A. Select the date type that meets the minimum storage needs of the application.

B. If you want to record year, month, day, hour, minute, and second, and the record has a long history, it is best to use datetime.

C. If the recorded date needs to be used by users in different time zones, use timestamp

(4) char and varchar

Char is a fixed-length string, and varchar is a variable-length character type.

(5) enum and set

Enum can only take a single value. Its data list is an enumeration type. Set can take multiple values. All appear in the form of strings, but in mysql, they are actually stored in the form of numerical indexes.

(6) blob and text

Generally, you can select char or varchar when saving a small number of strings. When saving a large file, you usually choose text or blob. Difference: blob can be used to save binary data, for example, photo and Audio Information. text can only store character data, such as an article or diary.

 

8. Common Operators

Operator	Function
+	Addition operation
-	Subtraction
*	Multiplication
/	Division operation, return Operator
%	Returns the remainder of an operation.

mysql> insert into tmp14 value(99);mysql> select num,num+1 from tmp14;+------+-------+| num  | num+1 |+------+-------+|   99 |   100 |+------+-------+

 

9. Comparison Operators

Operator	Function
=	Equal
<=>	Security equals (null can be compared)
<> (! =)	Not equal
<=	Less than or equal
=>	Greater than or equal
<	Less
>	Greater
Is null	Determines whether a value is null.
Is not null	Judge whether a value is not null
Least	Returns the minimum value when two or more parameters exist.
Createst	Returns the maximum value when two or more parameters exist.
Between and	Determines whether a value falls between two values.
Isnull	Same as is null
In	Determines whether a value is any value in the in list.
Not in	Judge whether a value is not any value in the in list.
Like	Wildcard match
Regexp	Regular Expression matching

Example:

mysql> select 1=2,3=3,4>5,9>2;+-----+-----+-----+-----+| 1=2 | 3=3 | 4>5 | 9>2 |+-----+-----+-----+-----+|   0 |   1 |   0 |   1 |+-----+-----+-----+-----+1 row in set (0.00 sec)

 

10. logical operations

Operator	Function
Not or!	Non-logical
And or &&	Logic and
Or |	Logic or
Xor	Logic exclusive or

 

For example, mysql> select not 2, not (2-2); + ------- + ----------- + | not 2 | not (2-2) | + ------- + ----------- + | 0 | 1 | + ------- + ----------- +

 

11. bitwise OPERATOR: used to test, shift, or test bitwise in binary bytes.

Operator	Function
|	Bit or
&	Bitwise AND
^	Bitwise OR
<	Shift left
>	Shift right
~	Bitwise inversion: reverse all bits

Example: mysql> select 10 | 15, 9 | 4 | 2; + ------- + | 10 | 15 | 9 | 4 | 2 | + ------- + | 15 | 15 | + ------- + 1 row in set (0.00 sec)

 

12. Operator priority: determines the order in which different operators are computed in the expression.

Priority	Operator
Lowest	= Value assignment
	|, Or
	Xor
	&, And
	Not
	Between and, case, when, then, else
	= Comparison operation, <=>, is like regexp in
	|
	&
	<,>
	-, +
	*,/, %
	^
	~
Highest	!