MySQL operator operation

1. Adding and removing changes and checking operation

Inquire:

SELECT * from table name where field name operator number/character;

Modify:

Update table name SET field name = value,... where field name operator number/character

Delete:

Delete from table name where field name operator number/character

2. Operators

Numeric comparison operators:

=

!=

>

>=

<

<=

Character comparison operators:

=

!=

Logical operators:

and

Or

In-range comparison:

Between and

Inch

Not in

Model:

Create Database Sky;

Use Sky;

CREATE TABLE M1 (

ID Int (11),

Name Char (20),

Age tinyint (10),

Sex enum (' Male ', ' female '),

Score tinyint (10),

Address char (20)

) default Charset=utf8;

INSERT INTO M1 values

(1, ' L1 ', 21, ' Male ', 90, ' Beijing '),

(2, ' L2 ', 19, ' Male ', 91, ' Shanghai '),

(3, ' L3 ', 24, ' female ', 95, ' Guangzhou '),

(4, ' L4 ', 22, ' Male ', 89, ' Guangzhou '),

(5, ' L5 ', 20, ' female ', 86, ' Shanghai '),

(6, ' L6 ', 19, ' Female ', 99, ' Guangzhou ');

Look for people with a table ID between 1 and less than 5

Mysql> SELECT * FROM M1 where ID >=1 and ID <5;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

+------+------+------+------+-------+---------+

4 rows in Set (0.00 sec)

Field name between value 1 and value 2

Field name in (value 1, value 2,...)

Field name not in (value 1, value 2,...)

Find people with IDs between 2 and 5 and address people in Guangzhou

Mysql> SELECT * FROM M1 where ID between 2 and 5 and address= "Guangzhou";

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

+------+------+------+------+-------+---------+

2 rows in Set (0.00 sec)

Or

Mysql> SELECT * FROM M1 where (ID between 2 and 5) and (address= "Guangzhou");

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

+------+------+------+------+-------+---------+

2 rows in Set (0.00 sec)

Show the person with ID 1,2,5, in

Mysql> SELECT * FROM M1 where ID in (1,2,5);

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

| 5 |   L5 | 20 |    Women | 86 | Shanghai |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

+------+------+------+------+-------+---------+

3 Rows in Set (0.00 sec

Show people with addresses not in Beijing

Mysql> SELECT * FROM M1 where address isn't in ("Beijing");

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

| 5 |   L5 | 20 |    Women | 86 | Shanghai |

+------+------+------+------+-------+---------+

4 rows in Set (0.00 sec)

Display ID 1 or 2 or 5 address in Shanghai and named L2 user

Mysql> SELECT * FROM M1 where ID in (1,2,5) and address= "Shanghai" or name= "L2";

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

| 5 |   L5 | 20 |    Women | 86 | Shanghai |

+------+------+------+------+-------+---------+

2 rows in Set (0.00 sec)

MySQL operator operation