数据库高级语句2

LEAST、GREATEST

least：当有两个或者多个参数时，返回其中最小值。如果其中一个值为null，则返回结果为null
greatest：当有两个或多个参数时，返回其中最大值，如果其中一个值为null，则返回结果为null

支持不同类型的比较
mysql> select least(10.1,11),greatest(10,11.1);
+----------------+-------------------+
| least(10.1,11) | greatest(10,11.1) |
+----------------+-------------------+
|           10.1 |              11.1 |
+----------------+-------------------+
1 row in set (0.00 sec)

least包含字符和数值的函数不能识别，greatest会屏蔽字符，比较数值
mysql> select least(10.1,20,'a'),greatest(10,20.1,'a');
+--------------------+-----------------------+
| least(10.1,20,'a') | greatest(10,20.1,'a') |
+--------------------+-----------------------+
| 0                  | 20.1                  |
+--------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

如果比较有null那结果就为null
mysql> select least(10.1,20,null),greatest(10,20.1,null);
+---------------------+------------------------+
| least(10.1,20,null) | greatest(10,20.1,null) |
+---------------------+------------------------+
|                NULL |                   NULL |
+---------------------+------------------------+
1 row in set (0.00 sec)

IN、NOT IN

IN：判断一个值是否在对应列表中，如果是，返回1，否则返回0
NOT IN：判断是否在不在对应列表中，不是返回1，否则返回0

判断c在不在abc里是返回了1，判断2在不在123里用not in在返回了0
mysql> select 'c'in('a','b','c'),2 not in(1,2,3);
+--------------------+-----------------+
| 'c'in('a','b','c') | 2 not in(1,2,3) |
+--------------------+-----------------+
|                  1 |               0 |
+--------------------+-----------------+
1 row in set (0.00 sec)

LIKE、NOT LIKE

LIKE用来匹配字符，如果匹配成功则返回1，反之返回0，like支持两种通配符：%用于匹配任意数目的字符，而_只能匹配一个字符。not like与like相反匹配成功返回0，失败返回1。

mysql> select 'bdqn' like 'b%','bdqn' not like 'bdq_';
+------------------+------------------------+
| 'bdqn' like 'b%' | 'bdqn' not like 'bdq_' |
+------------------+------------------------+
|                1 |                      0 |
+------------------+------------------------+
1 row in set (0.00 sec)

逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用true和false表示，mysql中支持使用逻辑运算符有四种。

且 0&&0=0 1&&0=0 0&&1=0 1&&1=1
或 0||0=0 1||0=1 0||1=1 1||1=1
异或 0^0=0 1 ^0=1 0 ^1=1 1 ^1=0

逻辑非

逻辑运算中最简单的就是逻辑非，逻辑非使用not或！表示。逻辑非将跟在他后面的逻辑测试取反，并把假变真，真便假。如果not后面为0时所得为1，如果非0时所得为0，如果为null时，所得null。

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

逻辑与

通常用于判断两个值或多个值的有效性，如果所有值都是真返回1，否则返回0，逻辑与用and或者&&表示。

非0都看为1，通过上面的运算规则 1&&1=1 0配任何数都为0null也一样，有数值为null，默认为null。
mysql> select 2&&3,0&&2,0&&null,1 and null;
+------+------+---------+------------+
| 2&&3 | 0&&2 | 0&&null | 1 and null |
+------+------+---------+------------+
|    1 |    0 |       0 |       NULL |
+------+------+---------+------------+
1 row in set (0.00 sec)

逻辑或

逻辑或表示包含的操作数，任意一个为非零值并且不是一个null值时，返回1，否正返回0，逻辑通常用or和||表示，||不建议使用建议使用or来进行逻辑或，不然容易出错。

只要有一个匹配上就为1
mysql> select 2 or 3,1 or 0;
+--------+--------+
| 2 or 3 | 1 or 0 |
+--------+--------+
|      1 |      1 |
+--------+--------+
1 row in set (0.00 sec)

位运算符

是对二进制数进行计算的运算符，mysql内运算会先操作数变成二进制格式，然后进行运算，最后将其结果从二进制变为十进制，方便查看。

2的4次方 2的3次方 2的2次方 2的1次方 2的0次方
16 8 4 2 1
1010 10
1111 15

1.进行与运算
1&&1 1  0&&1  0 1&&1  0&&1 0=1010转换十进制=10
2.进行或运算
1||1 1  0||1  1  1||1  1  0||1 1=1111转换十进制=15
3.异或运算
1^1 0   0 ^1 1 1 ^1  0  0 ^1 1=0101转换十进制=5
4.5&~1
5且1的取反数值
0001取反1110 ~1
0101 5
0100转换十进制=4

<<左移位运算向左移动位数，空缺处补0
1<<2 1向左移动2位
0001移动两位0100
转换十进制=4
>>右移位运算向右移动位数，多直接删除
15>>2 15向右移动两位
1111移动两位0011
转换十进制=3

不管那种运算符都有优先级的问题，运算符中的优先级决定了不通的运算符在计算过程中的先后顺序，级别高的会先进行计算，如果运算级别相同，mysql会依照顺序从左到右一次进行计算。如果不能确定所使用的运算符的优先级，可以使用（）改变优先级。！优先级最高，=优先级最低。

连接查询

mysql连接查询，通常都是将来自两个或多个表结合起来，基于这些表之间的共同字段，进行数据拼接。首先要确定一个主表作为结果集，然后其他的表的行有选择性的连接到选定主表上。较多使用查询包括：内连接、外连接（左连接、右连接）。

内连接

两张或多张表中同时符合某种条件的数据记录组合
from子句中使用inner join关键字连接多表并使用on设置连接条件
是系统默认的表连接方式，可以省略inner关键字
多表支持连续使用inner join，建议不超过三个表

用第一张表的id将两张表一样的name关联起来，输出chen的idname，from来自那张表inner join 连接的表on设置连接条件id=id
mysql> select chen.id,chen.name from chen inner join chen1 on chen.id=chen1.id;
+----+------+
| id | name |
+----+------+
|  1 | wang |
|  2 | li   |
|  3 | ai   |
|  4 | po   |
|  5 | pp   |
|  6 | ol   |
+----+------+
6 rows in set (0.00 sec)

左连接

也被称为左外连接
在from子句中使用left join关键字来表示
匹配左表中所有的行及右表中符合条件的行

输出第一张所有的内容第二张表输出其能对应的内容
mysql> select chen.id,chen.name from chen left join chen11 on chen.id=chen1.id;
+----+------+
| id | name |
+----+------+
|  1 | wang |
|  2 | li   |
|  3 | ai   |
|  4 | po   |
|  5 | pp   |
|  6 | ol   |
+----+------+
6 rows in set (0.00 sec)

右连接

右连接和左连接反过来，右表当主表输出所有的内容，左表则输出对应内容。

mysql> select chen.id,chen.name from chen right join chenn1 on chen.id=chen1.id;
+------+------+
| id   | name |
+------+------+
|    1 | wang |
|    2 | li   |
|    3 | ai   |
|    4 | po   |
|    5 | pp   |
|    6 | ol   |
+------+------+
6 rows in set (0.00 sec)

数据库函数

mysql提供了实现各种共能的函数
常用函数分类：
数学函数
聚合函数
字符串函数
日期时间函数

常用的数学函数

abs(x)：返回x的绝对值

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

rand()：返回0到1的随机数（能出现0不能出现1）

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.28312114466272403 |
+---------------------+
1 row in set (0.00 sec)

mod(x,y)：返回x除以y以后的余数，取余

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

power(x,y)：返回x的y次方

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

round(x)：返回离x最近的整数

mysql> select round(2.1);
+------------+
| round(2.1) |
+------------+
|          2 |
+------------+
1 row in set (0.01 sec)

round(x,y)：保留x的y位小数四舍五入后的值

mysql> select round(2.155,2);
+----------------+
| round(2.155,2) |
+----------------+
|           2.16 |
+----------------+
1 row in set (0.00 sec)

sqrt(x)：返回x的平方根

mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

truncate(x,y)：返回数字x截断位y小数的值（不会四舍五入该多少就是多少）

mysql> select truncate(2.458,2);
+-------------------+
| truncate(2.458,2) |
+-------------------+
|              2.45 |
+-------------------+
1 row in set (0.00 sec)

ceil(x)：返回大于或等于x的最小整数

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

floor(x)：返回小于或等于x的最大整数

mysql> select floor(1.2);
+------------+
| floor(1.2) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

greatest(x1,x2…)：返回集合中最大的值

mysql> select greatest(1,2,4,10);
+--------------------+
| greatest(1,2,4,10) |
+--------------------+
|                 10 |
+--------------------+
1 row in set (0.00 sec)

least(x1,x2…)：返回集合中最小的值

mysql> select least(1,2,4,10);
+-----------------+
| least(1,2,4,10) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

混合使用

使用rand输出0-1的数值然后x100使用ceil能够让返回值变为最大整数，mod取余除3，
这样就可以做出简单的石头剪刀布的游戏机制了。

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

聚合函数

对表中数据记录进行集中概括而设计的一类函数
常用函数
avg() ：返回指定列的平均值

mysql> select avg(id) from heng;
+---------+
| avg(id) |
+---------+
|  2.0000 |
+---------+
1 row in set (0.00 sec)

count()：返回指定列中非null值的个数
统计个数

mysql> select count(id) from heng;
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

min()：返回指定列最小值
max()：返回指定列的最大值

最大数
mysql> select max(id) from heng;
+---------+
| max(id) |
+---------+
|       3 |
+---------+
1 row in set (0.06 sec)
最小数
mysql> select min(id) from heng;
+---------+
| min(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

sum()：返回指定列的所有值之和

mysql> select sum(id) from heng;
+---------+
| sum(id) |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

综合列出前三id
倒叙列出heng表id的前三

mysql> select id from heng order by id desc limit 3;
+----+
| id |
+----+
|  3 |
|  2 |
|  1 |
+----+
3 rows in set (0.00 sec)

字符串函数

常用的字符串函数
length(x)：返回字符串x的长度（空格字符占位）
null输出只为null

mysql> select length(' abc');
+----------------+
| length(' abc') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

trim()：返回去除指定格式的值（去除空格）
#能去除头尾中间除去不了

mysql> select trim(' a bc ');
+----------------+
| trim(' a bc ') |
+----------------+
| a bc           |
+----------------+
1 row in set (0.00 sec)

concat(x,y)：将提供参数x和y拼接成一个字符串

mysql> select concat('abc','ccc');
+---------------------+
| concat('abc','ccc') |
+---------------------+
| abcccc              |
+---------------------+
1 row in set (0.00 sec)

upper(x)\lower(x)：将字符串x所有字母变成大\小写字母

mysql> select upper('abc'),lower('ABC');
+--------------+--------------+
| upper('abc') | lower('ABC') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.00 sec)

left(x,y)\right(x,y)：返回字符串x前y\后y个字符
左右查看

mysql> select left('abcd',2),right('abcd',2);
+----------------+-----------------+
| left('abcd',2) | right('abcd',2) |
+----------------+-----------------+
| ab             | cd              |
+----------------+-----------------+
1 row in set (0.00 sec)

repeat(x,y)：将字符串x重复y次

mysql> select repeat('a',6);
+---------------+
| repeat('a',6) |
+---------------+
| aaaaaa        |
+---------------+
1 row in set (0.00 sec)

space(x)：返回x个空格

mysql> select length(space(6));
+------------------+
| length(space(6)) |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

replace(x,y,z)：将字串z替换字符串x中的y

mysql> select replace('abc','c',1);
+----------------------+
| replace('abc','c',1) |
+----------------------+
| ab1                  |
+----------------------+
1 row in set (0.00 sec)

strcmp(x,y)：比较x和y，返回值小于-1，等于0，大于1

mysql> select strcmp('a','b');
+-----------------+
| strcmp('a','b') |
+-----------------+
|              -1 |
+-----------------+
1 row in set (0.00 sec)

substring(x,y,z)：获取字符串x中第y个位置开始长度为z的字符

mysql> select substring('abcdef',2,3);
+-------------------------+
| substring('abcdef',2,3) |
+-------------------------+
| bcd                     |
+-------------------------+
1 row in set (0.00 sec)

reverse(x)：将字符串x反转

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

日期时间函数