mysql学习笔记（二）运算符

运算符

1.算术运算符
+ - * /
mysql> select 1+4;
+-----+
| 1+4 |
+-----+
| 5 |
+-----+
1 row in set (0.01 sec)

mysql> select 1+'4a';
+--------+
| 1+'4a' |
+--------+
| 5 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings; ---查看最后一次警告信息
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '4a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)


mysql> select 1+'a4';
+--------+
| 1+'a4' |
+--------+
| 1 |
+--------+
1 row in set, 1 warning (0.00 sec)

2.比较运算符
> >= < <= != <> = 返回值：0为假，1为真

mysql> select 1>2;
+-----+
| 1>2 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)


3.逻辑运算符
and or not(!)

mysql> select 5>3 and 4=1;
+-------------+
| 5>3 and 4=1 |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)

4.聚合函数
sum() 求和
avg() 平均
min() 最小
max() 最大
count() 统计

mysql> create database db;
mysql> use db;
mysql> create table t1(name char(10),yw int,sx int);
mysql> insert into t1 values('zs',80,91);
mysql> insert into t1 values('ls',60,55);
mysql> insert into t1 values('ww',6,90);
mysql> insert into t1 values('yz',80,9);

mysql> select * from t1;
+------+------+------+
| name | yw | sx |
+------+------+------+
| zs | 80 | 91 |
| ls | 60 | 55 |
| ww | 6 | 90 |
| yz | 80 | 9 |
+------+------+------+
4 rows in set (0.00 sec)


mysql> select sum(yw) from t1;
+---------+
| sum(yw) |
+---------+
| 226 |
+---------+

mysql> select max(sx) from t1;
+---------+
| max(sx) |
+---------+
| 91 |
+---------+
1 row in set (0.00 sec)

mysql> select *,yw+sx as sum from t1; --- as相当于取了个别名
+------+------+------+------+
| name | yw | sx | sum |
+------+------+------+------+
| zs | 80 | 91 | 171 |
| ls | 60 | 55 | 115 |
| ww | 6 | 90 | 96 |
| yz | 80 | 9 | 89 |
+------+------+------+------+
4 rows in set (0.00 sec)


mysql> select *,yw+sx as sum,(yw+sx)/2 as avg from t1;
+------+------+------+------+---------+
| name | yw | sx | sum | avg |
+------+------+------+------+---------+
| zs | 80 | 91 | 171 | 85.5000 |
| ls | 60 | 55 | 115 | 57.5000 |
| ww | 6 | 90 | 96 | 48.0000 |
| yz | 80 | 9 | 89 | 44.5000 |
+------+------+------+------+---------+
4 rows in set (0.00 sec)