Shulou(Shulou.com)06/01 Report--

1. Numerical type

1. Data types are: numeric type, daily I type, string type.

2. Int (20), int refers to the range of values of the integer, in which the parameter 20 only indicates the width of the data display. The display width is independent of the range of values of the data type. The display width only indicates the maximum width that can be displayed by mysql. If the inserted data is larger than the display width, as long as the value does not exceed the value range of the data type, the value can still be inserted and all can be displayed. For example: specify id int (4), insert a value of 123456 into id, the display is still 123456, regardless of the width. The width of the data type can be specified or not specified, and the default width is specified according to the data type.

3. Integer types: tinyint (very small), smallint (small), mediumint (middle), int (normal), bigint (large)

4. Decimals are divided into floating point type and fixed point type.

5. Floating point:: single precision floating point (float) double precision floating point (double)

6. Fixed point: decimal

7. Whether it is a fixed point or a floating point, it will be rounded out of the precision range.

8. The form of floating-point expression (M, N), M is precision (a total of several digits), and N is scale (decimal places).

9. Advantages and disadvantages of floating point and fixed point:

(1) floating-point advantage: if the length is fixed, floating-point represents a larger data range; floating-point disadvantage: there will be accuracy problems.

(2) the precision requires high use of fixed points, so it is best to use fixed points for decimal comparison, addition and subtraction to avoid floating points.

II. Date type

1 、 year,date,time,datetime,timestamp

2. Year range:

(1) 2-bit string 0-69 (19xx) 70-99 (20xx)

(2) 2 is the number 1-69 (19xx) 70-99 (20xx)

3 、 time and date

(1) 1112 and '1112' means minutes and seconds. 00. 11. 12. 12. 12. 12. Time and 11:12:00.

(2) date: you can't use 1919-02-09 with '1919-02-09' or 90-09-02 with 900920.

(3) mysql allows' lax 'syntax in dates, and any kind of symbol can be used for spacers between dates (-,., /, @)

(4) Delete the data in the table: delete from test4

Before deleting:

Mysql > SELECT * FROM test4

+-+

| | id | t | d | |

+-+

| | 127 | NULL | NULL |

| | NULL | 00:11:12 | NULL |

| | NULL | 00:11:12 | NULL |

| | NULL | 11:12:00 | NULL |

| | NULL | NULL | 1990-02-09 |

| | NULL | NULL | 1990-02-09 |

+-+

6 rows in set (0.00 sec)

After deletion:

Mysql > SELECT * FROM test4

Empty set (0.00 sec)

4. Modify the time zone: set time_zone='+10:00' is changed to East 10, and each additional urban time is increased by one hour.

III. Text string

1. Char (M), varchar (M), in which M must be selected, and selection must be suitable.

Field | Type | Null | K

-+-

C1 | char (1) | YES |

C2 | varchar (5) | YES |

Mysql > INSERT INTO test5 VALUES ('abcd','abcde')

ERROR 1406 (22001): an error was reported when the length of Data too long for column 'c1' at row 1 exceeded the char.

2. Text saves non-binary strings, such as article content, comments, etc.

3. Enum is a string object whose value is a list of values enumerated in the column specification when the table is created

L

Mysql > CREATE TABLE test6

-> (

-> soc INT, levevl enum ('excellent','good','bad')

->)

Query OK, 0 rows affected (0.02 sec)

Mysql > INSERT INTO test6 VALUES (70 ~ (2)), (90 ~ (1)), (30 ~ (3))

Mysql > SELECT * FROM test6

+-+ +

| | soc | levevl | I |

+-+ +

| | 70 | good |

| | 90 | excellent |

| | 30 | bad |

+-+ + h

3 rows in set (0.00 sec)

4 、 set

(1) set is a string object that can have zero or more values and up to 64 members.

(2) fields of type enum can only be inserted by selecting one value from the defined column, and columns of type set can select the union of multiple characters from the defined column values.

(3) if there is a duplicate column value inserted into the set field, the duplicate value is automatically deleted (the case is also duplicated).

(4) the values inserted must be data in set. If you insert data that does not appear, ignore these values and give a warning.

(5) examples:

Mysql > CREATE TABLE test7

-> (

-> s SET ('1th, June, week, week

->)

Query OK, 0 rows affected (0.02 sec)

Correct:

Mysql > INSERT INTO test7 VALUES ('1Magi a'), ('a dint kjol yu'), ('1rect Yu')

Mysql > SELECT * FROM test7

+-+

| | s |

+-+

| | 1par a |

| | arecom kjinyu | |

| | 1Baiyu |

+-+

3 rows in set (0.00 sec)

Out-of-range error:

Mysql > INSERT INTO test7 VALUES ('a ~ ~ c')

ERROR 1265 (01000): Data truncated for column's'at row 1

5. Binary string type: BIT,BINARY,VARBINARY,TINYBLOB,BLOB,MENDIUMBLOB,LONGBLOB.

6. The choice of char and varchar:

(1) it is realized with char if the speed is required.

(2) for MYISAM storage engine: use char, trade space for time.

(3) InnoDB storage engine: with varchar, space can be saved.

7. The choice of blob and text:

BLOB mainly stores pictures and audio information, while TEXT can only store plain text files.

Four: operators

1. Type: arithmetic operator, comparison operator, logical operator, bit operation operator.

2 、

(1) arithmetic operations (+, -, *, /,% (complementary or modular operations).

(2) compare (>, =, CREATE TABLE test8 (num INT (4))

Mysql > INSERT INTO test8 VALUES (64)

Mysql > SELECT * FROM test8

+-+

| | num |

+-+

| | 64 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT num, num+10,num-10,num*10,num/10,num FROM test8

+-+ +

| | num | num+10 | num-10 | num*10 | num/10 | num | |

+-+ +

| | 64 | 74 | 54 | 640 | 6.4000 | 4 |

+-+ +

1 row in set (0.00 sec)

(2) comparison operation

1 > wait for comparison

Mysql > SELECT 1, 2, 1, 1, 1, 1, 12, 11.

+-+

| | 1 / 2 | 1 / 1 | 12 | 11 | |

+-+

| | 0 | 1 | 0 | 1 |

+-+

1 row in set (0.00 sec)

= has the same effect as.

2 >

If one or two parameters are NULL, the result of the comparison operation is NULL;. If both parameters in the same comparison operation are strings, compare them according to strings; if both parameters are integers, compare them according to integers; if a string and a number are equal, the string is automatically converted into a number.

3 >

Mysql > SELECT 'goodwill girls' baddies, baddies, '123', '123', 'rts.'

+-+

| | 'goodwill customers please bad' |' 123 clients' 123 | 'rts''rt' | |

+-+

| | 1 | 0 | 1 |

+-+

1 row in set (0.00 sec)

! = same as the effect

The usage of 4 > = is the same as the above.

5 > ISNULL ISNULL (), IS NOT NULL usage.

Mysql > SELECT NULL ISNULL, NULL ISNULL,10 IS NOT NULL

+-+

| | NULL ISNULL | ISNULL | 10 IS NOT NULL |

+-+

| | 1 | NULL | 1 |

+-+

1 row in set (0.04 sec)

Mysql > SELECT ISNULL (NULL), ISNULL (1)

+-+ +

| | ISNULL (NULL) | ISNULL (1) | |

+-+ +

| | 1 | 0 |

+-+ +

1 row in set (0.03 sec)

6 > between.. And...

Mysql > SELECT 4 BETWEEN 2 AND 5, 5 BETWEEN 2 AND 3

+-+ +

| | 4 BETWEEN 2 AND 5 | 5 BETWEEN 2 AND 3 | |

+-+ +

| | 1 | 0 |

+-+ +

1 row in set (0.00 sec)

7 > find the maximum and minimum value

Minimum value

Mysql > SELECT LEAST (12, 34, 5, 6, 67, 10, 7)

+-+

| | LEAST (12, 34, 56, 67, 10, 7) | |

+-+

| | 7 |

+-+

1 row in set (0.03 sec)

Maximum value

Mysql > SELECT GREATEST (20, 12, 45, 16, 87, 3)

+-+

| | GREATEST (20, 12, 45, 16, 87, 3) | |

+-+

| | 87 |

+-+

1 row in set (0.00 sec)

When comparing maximum and minimum values, if there is NULL in the list of comparison values, the size cannot be judged, and the return value is NULL

When comparing strings, the larger the letter is, and the smaller the reverse value is.

8 > in, not in determines whether the Operand is a value in the list.

Mysql > SELECT 3 IN (1, 4, 3, 2, 6), 4 NOT IN (1, 4, 5, 5, 6, 7)

+-+ +

| | 3 IN (1, 4, 3, 2, 6) | 4 NOT IN (1, 4, 5, 5, 6, 7) | |

+-+ +

| | 1 | 0 |

+-+ +

1 row in set (0.03 sec)

9 > LIKE has only two wildcards (%, which can match any number of characters, even zero characters; _ can match one character. )

Mysql > SELECT 'phon'like' phon','phon'like 'pho_','phon'like' p%'

+-+

| | 'phon'like' phon' | 'phon'like' pho_' | 'phon'like' p%'|

+-+

| | 1 | 1 | 1 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT 'phon' like''

+-+

| | 'phon' like'%'|

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

10 > regexp regular wildcards

'^' matches a string that begins with the character after that character

'$' matches a string that ends with the character after that character

'.' Match any single character

'[.]' Match any character in parentheses

The'* 'matches zero or more characters in front of him.

Mysql > SELECT 'ssky' REGEXP' ^ Sky 'REGEXP'. Sky'

+-- +

| | 'ssky' REGEXP' ^ s'| 'ssky' REGEXP' yearly'| 'ssky' REGEXP' s.sky' |

+-- +

| | 1 | 0 | 0 |

+-- +

1 row in set (0.00 sec)

Mysql > SELECT 'abdedey' REGEXP' de*'

+-- +

| | 'abdedey' REGEXP' de*' |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

* add one of the characters to match any string, similar to like.

5. Logical operation

1 > NOT or!

The logical non-operator indicates that when the current Operand is 0, the resulting value is 1; if the Operand is not 00:00, the resulting value is 0; when the Operand is null, the return value is null.

2 > AND or & &

Logic and operators indicate that when all operands are non-zero and not null, the result is 1; when one or more operands are at 00:00, the result is 0.

3 > OR or | |

Logic or operator, when both operands are non-null and either Operand is non-zero (1or 2 or 0 is 1), the result is 1 or null, null;1or null 1, or null null

4 > XOR operation

1 xor 1 and 0 xor0 are 0, 1 xor0 is 1 xor0, 1 xor null is null.

There are several operands in 1 xor 1 xor 1. The operation order is calculated from left to right. The result of 1 xor 1 is 0, and then XOR with 1 is 1.

The a xor b calculation is equivalent to (an AND (NOTb)) or ((NOTa) AND b).

6. Bit operation: used to test, shift, or test bits in binary bytes.

Type of bit operation: bit or (|), bit and (&), bit XOR (^), bit left shift (), bit reverse, reverse all bits (~)

1 > bit and bit OR

Mysql > SELECT 3 & 4 Pol 3 | 4

+-+ +

| | 3-4 | 3 | 4 | |

+-+ +

| | 0 | 7 |

+-+ +

1 row in set (0.03 sec)

2 > the bit XOR operation corresponds to a different binary, and the corresponding result is 1, as shown in

3:011, 4:100 . 3 ^ 4 is 011 ^ 100. The result is 111 / 7.

Mysql > SELECT 3 ^ 4

+-+

| | 3 ^ 4 |

+-+

| | 7 |

+-+

1 row in set (0.00 sec)

3 > move to the left, move to the right

Is to move the position of the middle 1 of the binary to the left or right, and the position to be moved out by 0. 5%.

Column: 11

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.