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)
