当前位置:Gxlcms > mysql > mysql运算符(作业)_MySQL

mysql运算符(作业)_MySQL

时间:2021-07-01 10:21:17 帮助过:93人阅读

bitsCN.com

mysql运算符(作业)

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

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

Query OK, 0 rows affected (0.63 sec)

mysql> select * from tmp15;

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

| note | price |

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

| aa | 20 |

| yus | 30 |

| book | 30 |

| mylife | 30 |

| love | 67 |

| ben | 50 |

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

6 rows in set (0.00 sec)

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

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

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

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

| 平均价格 | 价格和 | 最低价格 | 最高价格 |

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

| 37.8333 | 227 | 20 | 67 |

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

1 row in set (0.00 sec)

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

mysql> select 50<20 && 50>30;

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

| 50<20 && 50>30 |

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

| 0 |

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

1 row in set (0.00 sec)

mysql> select 50<20 || 50>30;

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

| 50<20 || 50>30 |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select 50<>20;

+--------+

| 50<>20 |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

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

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

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

| 最大值 |

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

| 67 |

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

1 row in set (0.00 sec)

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

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

| note | price |

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

| aa | 20 |

| ben | 50 |

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

2 rows in set (0.06 sec)

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

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

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

+--------+

| note |

+--------+

| aa |

| yus |

| book |

| mylife |

| love |

| ben |

+--------+

mysql> select note from tmp15 where note is null;

Empty set (0.00 sec)

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

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

+------+

| note |

+------+

| aa |

+------+

1 row in set (0.00 sec)

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

mysql> select 'fo/no' regexp 'y$';

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

| 'fo/no' regexp 'y$' |

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

| 0 |

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

1 row in set (0.06 sec)

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

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

Empty set, 1 warning (0.00 sec)

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

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

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

| note | price | price && not null |

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

| aa | 20 | NULL |

| yus | 30 | NULL |

| book | 30 | NULL |

| mylife | 30 | NULL |

| love | 67 | NULL |

| ben | 50 | NULL |

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

6 rows in set (0.00 sec)

bitsCN.com

人气教程排行