MySQL运算符

来源：互联网发布：阿里外包员工出路知乎编辑：程序博客网时间：2024/05/21 10:03

创建表 tmp15 其中包括varchar类型的字段note 和int类型的字段price ，使用运算符对表中不同的字段进行计算；使用逻辑操作符对数据进行逻辑操作；

1.对tmp15表中的整数值字段price进行算术运算

2.对tmp15中的整型数值字段price进行比较运算

3.判断price值是否落在30~80区间；返回与70和30相比最大的值，判断price是否为IN列表（10, 20, 50, 35）中的某个值

4..对tmp15中的字符串数值字段note进行比较运算，判断表tmp15中note字段是否为空；使用LIKE判断是否以字母'd'开头；使用REGEXP判断是否以字母'y'尾；判断是否包含字母'g'或者'm'

5.将price字段值与NULL，0进行逻辑运算

创建表 tmp15 其中包括varchar类型的字段note 和int类型的字段price ，使用运算符对表中不同的字段进行计算；使用逻辑操作符对数据进行逻辑操作；

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.13-enterprise-commercial-advanced MySQL Enterprise Server -

Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use csdn;

Database changed

mysql> create table tmp15(note varchar(20),price int);

Query OK, 0 rows affected (0.76 sec)

mysql> desc tmp15;

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

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

| price | int(11) | YES | | NULL | |

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

2 rows in set (0.02 sec)

mysql> insert into tmp15 values('football',28);

Query OK, 1 row affected (0.08 sec)

mysql> insert into tmp15 values('book',35);

Query OK, 1 row affected (0.09 sec)

mysql> insert into tmp15 values('bag',38);

Query OK, 1 row affected (0.08 sec)

mysql> insert into tmp15 values('shoes',50);

Query OK, 1 row affected (0.09 sec)

mysql> insert into tmp15 values('mp3',60);

Query OK, 1 row affected (0.09 sec)

mysql> select * from tmp15;

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

| note | price |

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

| football | 28 |

| book | 35 |

| bag | 38 |

| shoes | 50 |

| mp3 | 60 |

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

5 rows in set (0.00 sec)

.1、对tmp15表中的整数值字段price进行算术运算

mysql> select sum(price) `价格和` from tmp15;

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

| 价格和 |

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

| 211 |

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

1 row in set (0.00 sec)

mysql> select max(price)`最高价格`from tmp15;

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

| 最高价格 |

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

| 60 |

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

1 row in set (0.05 sec)

mysql> select min(price)`最低价格`from tmp15;

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

| 最低价格 |

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

| 28 |

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

1 row in set (0.00 sec)

mysql> select avg(price)`平均价格` from tmp15;

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

| 平均价格 |

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

| 42.2000 |

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

1 row in set (0.00 sec)

mysql> select count(note)`商品数` from tmp15;

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

| 商品数 |

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

| 5 |

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

1 row in set (0.00 sec)

mysql> select sum(price) `价格和`,max(price)`最高价格`,min(price)`最低价格`,avg(

price)`平均价格`,count(note)`商品总数` from tmp15;

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

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

| 211 | 60 | 28 | 42.2000 | 5 |

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

1 row in set (0.00 sec)

2.对tmp15中的整型数值字段price进行比较运算

与50比较：

mysql> select 50<28&&50<60;

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

| 50<28&&50<60 |

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

| 0 |

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

1 row in set (0.00 sec)

mysql> select 50<28||50<60;

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

| 50<28||50<60 |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select 50<>28;

+--------+

| 50<>28 |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

mysql> select 50<>60;

+--------+

| 50<>60 |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

3.判断price值是否落在30~80区间

mysql> select * from tmp15 where price>30 and price <80;

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

| note | price |

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

| book | 35 |

| bag | 38 |

| shoes | 50 |

| mp3 | 60 |

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

4 rows in set (0.00 sec)

返回与70和30相比最大的值：

mysql> select max(price)`最大值` from tmp15 where price between 30 and 70;

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

| 最大值 |

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

| 60 |

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

1 row in set (0.00 sec)

判断price是否为IN列表（10, 20, 50, 35）中的某个值

mysql> select * from tmp15 where price in(10,20,50,35);

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

| note | price |

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

| book | 35 |

| shoes | 50 |

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

2 rows in set (0.00 sec)

4..对tmp15中的字符串数值字段note进行比较运算

判断表tmp15中note字段是否为空；

mysql> select note from tmp15 where note is null;

Empty set (0.00 sec)

mysql> select note from tmp15 where note is not null;

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

| note |

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

| football |

| book |

| bag |

| shoes |

| mp3 |

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

5 rows in set (0.00 sec)

使用LIKE判断是否以字母'd'开头；

mysql> select note from tmp15 where note like 'd%';

Empty set (0.05 sec)

使用REGEXP判断是否以字母'y'尾

mysql> select note from tmp15 where note regexp'y$';

Empty set (0.00 sec)

mysql> select note from tmp15 where note regexp'k$';

+------+

| note |

+------+

| book |

+------+

1 row in set (0.07 sec)

判断是否包含字母'g'或者'm'

mysql> select note from tmp15 where note like '%g%' or '%m%';

+------+

| note |

+------+

| bag |

+------+

1 row in set, 1 warning (0.00 sec)

5.将price字段值与NULL，0进行逻辑运算

mysql> select * ,price && null from tmp15;

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

| note | price | price && null |

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

| football | 28 | NULL |

| book | 35 | NULL |

| bag | 38 | NULL |

| shoes | 50 | NULL |

| mp3 | 60 | NULL |

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

5 rows in set (0.00 sec)

mysql> select * ,price && 0 from tmp15;

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

| note | price | price && 0 |

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

| football | 28 | 0 |

| book | 35 | 0 |

| bag | 38 | 0 |

| shoes | 50 | 0 |

| mp3 | 60 | 0 |

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