创建表 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)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| note | varchar(20) | YES | | NULL | |
| 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 |
+----------+-------+------------+