mysql运算符

创建表 tmp15 其中包括varchar类型的字段note 和int类型的字段price

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

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

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

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

5.price字段值与NULL0进行逻辑运算

 

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)

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.
创建表 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> desc tmp15;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| note  | varchar(30) | YES  |     | NULL    |       |
| price | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)
mysql> insert into tmp15 values('范滔滔','35');
Query OK, 1 row affected (0.07 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)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值