mysql运算符

最新推荐文章于 2021-01-19 08:16:32 发布

圊年

最新推荐文章于 2021-01-19 08:16:32 发布

阅读量674

点赞数

本文链接：https://blog.csdn.net/u012055664/article/details/11777021

版权

创建表 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进行逻辑运算

Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
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.
创建表 tmp15 其中包括varchar类型的字段note 和int类型的字段price ;
mysql> use csdn;
Database changed
mysql> create table tmp15(note varchar(30),price int);
Query OK, 0 rows affected (0.28 sec)

mysql> insert into tmp15 values('小清辛','60');
Query OK, 1 row affected (0.11 sec)

mysql> insert into tmp15 values('与自豪','80');
Query OK, 1 row affected (0.05 sec)

mysql> insert into tmp15 values('张帅锅','50');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tmp15 values('仰韶春','90');
Query OK, 1 row affected (0.08 sec)

mysql> insert into tmp15 values('小光圈','45');
Query OK, 1 row affected (0.08 sec)
mysql> insert into tmp15 values('松井消磁',null);
Query OK, 1 row affected (0.07 sec)
mysql> select *from tmp15;
+--------------+-------+
| note         | price |
+--------------+-------+
| 范滔滔       |    35 |
| 小清辛       |    60 |
| 与自豪       |    80 |
| 张帅锅       |    50 |
| 仰韶春       |    90 |
| 小光圈       |    45 |
| 松井消磁     | NULL |
+--------------+-------+
7 rows in set (0.00 sec)
1.对tmp15表中的整数值字段price进行算术运算求和，求平均值
mysql> select sum(price) from tmp15;
+------------+
| sum(price) |
+------------+
|        360 |
+------------+
1 row in set (0.11 sec)

mysql> select avg(price) from tmp15;
+------------+
| avg(price) |
+------------+
|    60.0000 |
+------------+
1 row in set (0.00 sec)
2.对tmp15中的整型数值字段price进行比较运算
mysql> select 35 = '35a','35' = '35a';
+------------+--------------+
| 35 = '35a' | '35' = '35a' |
+------------+--------------+
|          1 |            0 |
+------------+--------------+
1 row in set, 1 warning (0.07 sec)
3.判断price值是否落在30~80区间；返回与70和30相比最大的值，判断price是否为IN列表（10, 20, 50, 35）中的某个值
mysql> select 'null'<> 'NULL';
+-----------------+
| 'null'<> 'NULL' |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from tmp15 where price between 30 and 80;
+-----------+-------+
| note      | price |
+-----------+-------+
| 范滔滔    |    35 |
| 小清辛    |    60 |
| 与自豪    |    80 |
| 张帅锅    |    50 |
| 小光圈    |    45 |
+-----------+-------+
5 rows in set (0.00 sec)
mysql> select note,max(price) from tmp15 where price between 30 and 70;
+-----------+------------+
| note      | max(price) |
+-----------+------------+
| 范滔滔    |         60 |
+-----------+------------+
1 row in set (0.00 sec)
mysql> select 35 in(10,20,50,35);
+--------------------+
| 35 in(10,20,50,35) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.29 sec)
mysql> insert into tmp15 values('2wang',20);
Query OK, 1 row affected (0.07 sec)
mysql> select note<3 from tmp15 where price=35;
+--------+
| note<3 |
+--------+
|      1 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select note,price <=> null from tmp15;
+--------------+----------------+
| note         | price <=> null |
+--------------+----------------+
| 范滔滔       |              0 |
| 小清辛       |              0 |
| 与自豪       |              0 |
| 张帅锅       |              0 |
| 仰韶春       |              0 |
| 小光圈       |              0 |
| 松井消磁     |              1 |
| 2wang        |              0 |
+--------------+----------------+
8 rows in set (0.02 sec)
4..对tmp15中的字符串数值字段noteintmp进行比较运算，判断表tmp15中note字段是否为空；使用LIKE判断是否以字母'd'开头；使用REGEXP

判断是否以字母'y'尾；判断是否包含字母'g'或者'm'
mysql> select '范滔滔' like 'd%';
+-----------------------+
| '范滔滔' like 'd%'    |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select note,note regexp 'Y$' from tmp15;
+--------------+------------------+
| note         | note regexp 'Y$' |
+--------------+------------------+
| 范滔滔       |                0 |
| 小清辛       |                0 |
| 与自豪       |                0 |
| 张帅锅       |                0 |
| 仰韶春       |                0 |
| 小光圈       |                0 |
| 松井消磁     |                0 |
| 2wang        |                0 |
+--------------+------------------+
8 rows in set (0.00 sec)
mysql> select note,note regexp 'g|f' from tmp15;
+--------------+-------------------+
| note         | note regexp 'g|f' |
+--------------+-------------------+
| 范滔滔       |                 0 |
| 小清辛       |                 0 |
| 与自豪       |                 0 |
| 张帅锅       |                 0 |
| 仰韶春       |                 0 |
| 小光圈       |                 0 |
| 松井消磁     |                 0 |
| 2wang        |                 1 |
+--------------+-------------------+
8 rows in set (0.00 sec)
5.将price字段值与NULL，0进行逻辑运算
mysql> select note,price||0 from tmp15;
+--------------+----------+
| note         | price||0 |
+--------------+----------+
| 范滔滔       |        1 |
| 小清辛       |        1 |
| 与自豪       |        1 |
| 张帅锅       |        1 |
| 仰韶春       |        1 |
| 小光圈       |        1 |
| 松井消磁     |     NULL |
| 2wang        |        1 |
+--------------+----------+
8 rows in set (0.00 sec)