创建表 tmp15 其中包括varchar类型的字段note 和int类型的字段price ,练习运算符(时间:60分钟)
5.1.1 任务描述
创建表 tmp15 其中包括varchar类型的字段note 和int类型的字段price ,使用运算符对表中不同的字段进行计算;使用逻辑操作符对数据进行逻辑操作;
mysql> create database yunsuanfu;
Query OK, 1 row affected (0.05 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| csdn |
| csdn3g |
| csdn9 |
| fenzu |
| gujianpeng |
| mysql |
| performance_schema |
| shujuku |
| student |
| waijian |
| webnews |
| yunsuanfu |
| zuoye |
+--------------------+
14 rows in set (0.00 sec)
mysql> use yunsuanfu;
Database changed
mysql> create table temp15(note varchar(20),price int(20));
Query OK, 0 rows affected (0.86 sec)
mysql> insert into temp15 values('张三','50');
Query OK, 1 row affected (0.09 sec)
mysql> insert into temp15 values('李四','40');
Query OK, 1 row affected (0.08 sec)
mysql> insert into temp15 values('王五','40');
Query OK, 1 row affected (0.11 sec)
mysql> insert into temp15 values('梨园春','65');
Query OK, 1 row affected (0.33 sec)
mysql> insert into temp15 values('水立方','65');
Query OK, 1 row affected (0.08 sec)
mysql> desc temp15;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| note | varchar(20) | YES | | NULL | |
| price | int(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> select * from temp15;
+-----------+-------+
| note | price |
+-----------+-------+
| 张三 | 50 |
| 李四 | 40 |
| 王五 | 40 |
| 梨园春 | 65 |
| 水立方 | 65 |
+-----------+-------+
5 rows in set (0.00 sec)
mysql> select note,sum(price) `价格和` from temp15 group by note;
+-----------+-----------+
| note | 价格和 |
+-----------+-----------+
| 张三 | 50 |
| 李四 | 40 |
| 梨园春 | 65 |
| 水立方 | 65 |
| 王五 | 40 |
+-----------+-----------+
5 rows in set (0.00 sec)
mysql> select note,max(price) `最高价格`,sum(price) `价格和`, avg(price) `平均工
资` from temp15 group by note;
+-----------+--------------+-----------+--------------+
| note | 最高价格 | 价格和 | 平均工资 |
+-----------+--------------+-----------+--------------+
| 张三 | 50 | 50 | 50.0000 |
| 李四 | 40 | 40 | 40.0000 |
| 梨园春 | 65 | 65 | 65.0000 |
| 水立方 | 65 | 65 | 65.0000 |
| 王五 | 40 | 40 | 40.0000 |
+-----------+--------------+-----------+--------------+
5 rows in set (0.00 sec)
第一大题的答案
1.对tmp15表中的整数值字段price进行算术运算
求和 平均值
价格的总和
mysql> select sum(price) as '总的价格和' from temp15;
+-----------------+
| 总的价格和 |
+-----------------+
| 260 |
+-----------------+
1 row in set (0.00 sec)
平均值的和
mysql> select avg(price) as '平均值' from temp15;
+-----------+
| 平均值 |
+-----------+
| 52.0000 |
+-----------+
1 row in set (0.00 sec)
第二大题的答案
对tmp15中的整型数值字段price进行比较运算,和60比较
mysql> select note,price,(price>60) from temp15;
+-----------+-------+------------+
| note | price | (price>60) |
+-----------+-------+------------+
| 张三 | 50 | 0 |
| 李四 | 40 | 0 |
| 王五 | 40 | 0 |
| 梨园春 | 65 | 1 |
| 水立方 | 65 | 1 |
+-----------+-------+------------+
5 rows in set (0.00 sec)
mysql>
第三大题的答案
3.判断price值是否落在30~80区间;返回与70和30之间相比最大的值,判断price是否为IN列表(10, 20, 50, 35)中的某个值
判断price值是否落在30~80区间;
mysql> select (price>=30 and price<=80) from temp15;
+---------------------------+
| (price>=30 and price<=80) |
+---------------------------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---------------------------+
5 rows in set (0.00 sec)
返回与70和30之间相比最大的值
mysql> select max(price) from temp15 where price between 30 and 70;
+------------+
| max(price) |
+------------+
| 65 |
+------------+
1 row in set (0.05 sec)
判断price是否为IN列表(10, 20, 50, 35)中的某个值
mysql> select * from temp15 price where price in(10,20,50,35);
+--------+-------+
| note | price |
+--------+-------+
| 张三 | 50 |
+--------+-------+
1 row in set (0.00 sec)
第四大题的答案
1--》 对tmp15中的字符串数值字段note进行比较运算,判断表tmp15中note字段是否为空;
2--》 使用LIKE判断是否以字母'd'开头;
3--》 使用REGEXP判断是否以字母'y'尾;
4--》 判断是否包含字母'g'或者'm'
填入的新数据
mysql> select * from temp15;
+-----------+-------+
| note | price |
+-----------+-------+
| 张三 | 50 |
| 李四 | 40 |
| 王五 | 40 |
| 梨园春 | 65 |
| 水立方 | 65 |
| 18 | 90 |
| dd | 77 |
| liy | 77 |
| ggg | 77 |
| mmm | 77 |
+-----------+-------+
10 rows in set (0.00 sec)
1--》的答案
插入一个新的数据
mysql> insert into temp15 values (18,90);
Query OK, 1 row affected (0.23 sec)
mysql> select * from temp15;
+-----------+-------+
| note | price |
+-----------+-------+
| 张三 | 50 |
| 李四 | 40 |
| 王五 | 40 |
| 梨园春 | 65 |
| 水立方 | 65 |
| 18 | 90 |
+-----------+-------+
6 rows in set (0.00 sec)
1--》 对tmp15中的字符串数值字段note进行比较运算,判断表tmp15中note字段是否为空;
mysql> select note,note>8 from temp15;
+-----------+--------+
| note | note>8 |
+-----------+--------+
| 张三 | 0 |
| 李四 | 0 |
| 王五 | 0 |
| 梨园春 | 0 |
| 水立方 | 0 |
| 18 | 1 |
+-----------+--------+
6 rows in set, 5 warnings (0.00 sec)
判断表tmp15中note字段是否为空;
mysql> select note,note<=>null from temp15;
+-----------+-------------+
| note | note<=>null |
+-----------+-------------+
| 张三 | 0 |
| 李四 | 0 |
| 王五 | 0 |
| 梨园春 | 0 |
| 水立方 | 0 |
| 18 | 0 |
+-----------+-------------+
6 rows in set (0.10 sec)
2--》 使用LIKE判断是否以字母'd'开头;
mysql> select note,note like '%d' from temp15;
+-----------+----------------+
| note | note like '%d' |
+-----------+----------------+
| 张三 | 0 |
| 李四 | 0 |
| 王五 | 0 |
| 梨园春 | 0 |
| 水立方 | 0 |
| 18 | 0 |
| dd | 1 |
| liy | 0 |
| ggg | 0 |
| mmm | 0 |
+-----------+----------------+
10 rows in set (0.00 sec)
3--》 使用REGEXP判断是否以字母'y'尾;
mysql> select note,note regexp 'y$' from temp15;
mysql> select note,note regexp 'y$' from temp15;
+-----------+------------------+
| note | note regexp 'y$' |
+-----------+------------------+
| 张三 | 0 |
| 李四 | 0 |
| 王五 | 0 |
| 梨园春 | 0 |
| 水立方 | 0 |
| 18 | 0 |
| dd | 0 |
| liy | 1 |
| ggg | 0 |
| mmm | 0 |
+-----------+------------------+
10 rows in set (0.00 sec)
mysql>
4--》 判断是否包含字母'g'或者'm'
mysql> select note,note regexp 'g|f' from temp15;
+-----------+-------------------+
| note | note regexp 'g|f' |
+-----------+-------------------+
| 张三 | 0 |
| 李四 | 0 |
| 王五 | 0 |
| 梨园春 | 0 |
| 水立方 | 0 |
| 18 | 0 |
| dd | 0 |
| liy | 0 |
| ggg | 1 |
| mmm | 0 |
+-----------+-------------------+
10 rows in set (0.00 sec)
mysql>
第五大题的答案
5.将price字段值与NULL,0进行逻辑运算
mysql> select note,price||0 from temp15;
+-----------+----------+
| note | price||0 |
+-----------+----------+
| 张三 | 1 |
| 李四 | 1 |
| 王五 | 1 |
| 梨园春 | 1 |
| 水立方 | 1 |
| 18 | 1 |
| dd | 1 |
| liy | 1 |
| ggg | 1 |
| mmm | 1 |
+-----------+----------+
10 rows in set (0.01 sec)
mysql>
mysql> select note,price&&0 from temp15;
+-----------+----------+
| note | price&&0 |
+-----------+----------+
| 张三 | 0 |
| 李四 | 0 |
| 王五 | 0 |
| 梨园春 | 0 |
| 水立方 | 0 |
| 18 | 0 |
| dd | 0 |
| liy | 0 |
| ggg | 0 |
| mmm | 0 |
+-----------+----------+
10 rows in set (0.00 sec)
mysql>
mysql> select note,price&&null from temp15;
+-----------+-------------+
| note | price&&null |
+-----------+-------------+
| 张三 | NULL |
| 李四 | NULL |
| 王五 | NULL |
| 梨园春 | NULL |
| 水立方 | NULL |
| 18 | NULL |
| dd | NULL |
| liy | NULL |
| ggg | NULL |
| mmm | NULL |
+-----------+-------------+
10 rows in set (0.00 sec)
mysql>
mysql> select note,price||null from temp15;
+-----------+-------------+
| note | price||null |
+-----------+-------------+
| 张三 | 1 |
| 李四 | 1 |
| 王五 | 1 |
| 梨园春 | 1 |
| 水立方 | 1 |
| 18 | 1 |
| dd | 1 |
| liy | 1 |
| ggg | 1 |
| mmm | 1 |
+-----------+-------------+
10 rows in set (0.00 sec)
mysql>