一、sql语句
二、函数
1. 数学函数
常用的数学函数 | 说明 |
---|---|
abs(x) | 返回x的绝对值 |
rand() | 返回0-1的随机数 |
mod(x,y) | 返回x除以y以后的余数 |
power(x,y) | 返回x的y次方 |
round(x) | 返回离x最近的整数,即四舍五入到个位 |
round(x,y) | 返回x的y位小数且四舍五入后的值 |
sqrt(x) | 返回x的平方根 |
truncate(x,y) | 返回数字X截断为y位小数的值 |
ceil(x) | 返回大于或等于x的最小整数 |
floor(x) | 返回小于或等于x的最大整数 |
greatest(x1,x2...) | 返回集合中最大的值 |
least(x1,x2...) | 返回集合中最小的值 |
(1)abs(x)
返回x的绝对值
mysql> select abs(-2.3);
+-----------+
| abs(-2.3) |
+-----------+
| 2.3 |
+-----------+
1 row in set (0.00 sec)
(2)rand()
返回0-1的随机数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.1262695352791525 |
+--------------------+
1 row in set (0.00 sec)
(3)mod(x,y)
返回x除y后的余数
mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
(4)power(x,y)
返回x的y次方
mysql> select power(2,8);
+------------+
| power(2,8) |
+------------+
| 256 |
+------------+
1 row in set (0.00 sec)
(5)round(x)
四舍五入到个位
mysql> select round(3.1415);
+---------------+
| round(3.1415) |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> select round(3.5415);
+---------------+
| round(3.5415) |
+---------------+
| 4 |
+---------------+
1 row in set (0.00 sec)
(6)round(x,y)
返回x保留的y位小数,四舍五入
mysql> select round(3.1415,2);
+-----------------+
| round(3.1415,2) |
+-----------------+
| 3.14 |
+-----------------+
1 row in set (0.00 sec)
mysql> select round(3.1415,3);
+-----------------+
| round(3.1415,3) |
+-----------------+
| 3.142 |
+-----------------+
1 row in set (0.00 sec)
(7)sqrt(x)
返回x的平方根
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select sqrt(8);
+--------------------+
| sqrt(8) |
+--------------------+
| 2.8284271247461903 |
+--------------------+
1 row in set (0.00 sec)
(8)truncate(x,y)
返回x保留y位小数(截断)
mysql> select truncate(3.1415,2);
+--------------------+
| truncate(3.1415,2) |
+--------------------+
| 3.14 |
+--------------------+
1 row in set (0.00 sec)
mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
| 3.141 |
+--------------------+
1 row in set (0.00 sec)
(9)ceil(x)
返回大于或等于x的最小整数,截断取整加1
mysql> select ceil(3.1415);
+--------------+
| ceil(3.1415) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
(10)floor(x)
返回小于或等于x的最大整数,截断取整
mysql> select floor(3.1415);
+---------------+
| floor(3.1415) |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
(11)greatest(x1,x2...)
返回集合中最大的值
mysql> select greatest(3.1415,3,4,5.2);
+--------------------------+
| greatest(3.1415,3,4,5.2) |
+--------------------------+
| 5.2000 |
+--------------------------+
1 row in set (0.00 sec)
(12)least(x1,x2...)
返回集合中最小的值
mysql> select least(3.1415,3,4,5.2);
+-----------------------+
| least(3.1415,3,4,5.2) |
+-----------------------+
| 3.0000 |
+-----------------------+
1 row in set (0.00 sec)
2.聚合函数
常用的聚合函数 | 说明 |
---|---|
avg() | 返回指定列的平均值 |
count() | 返回指定列中非NULL值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum() | 返回指定列的所有值之和 |
(1)avg(‘字段’)
返回指定列的平均值
mysql> select avg(sales) from store_info;
+------------+
| avg(sales) |
+------------+
| 687.5000 |
+------------+
1 row in set (0.00 sec)
(2)count()
- 返回指定列中非NULL值得个数
mysql> select count(store_name) from store_info;
+-------------------+
| count(store_name) |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)
- 返回指定列中非NULL值且去重的个数
mysql> select count(distinct store_name) from store_info;
+----------------------------+
| count(distinct store_name) |
+----------------------------+
| 3 |
+----------------------------+
1 row in set (0.00 sec)
- 返回所有列的行数,统计结果时不会忽略值为NULL的列
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
(3)min()
返回指定列的最小值
mysql> select min(sales) from store_info;
+------------+
| min(sales) |
+------------+
| 250 |
+------------+
1 row in set (0.01 sec)
(4)max()
返回指定列的最大值
mysql> select max(sales) from store_info;
+------------+
| max(sales) |
+------------+
| 1500 |
+------------+
1 row in set (0.00 sec)
(5)sum()
返回指定列的所有值得和
mysql> select sum(sales) from store_info;
+------------+
| sum(sales) |
+------------+
| 2750 |
+------------+
1 row in set (0.00 sec)
3、字符串函数
常用的字符串函数 | 说明 |
---|---|
trim() | 返回去除指定格式的值 |
concat(x,y) | 将提供的参数x和y拼接成一个字符串 |
substr(x,y) | 获取从字符串x中的第y个位置开始的字符串,跟substring()函数作用相同 |
substr(x,y,z) | 获取从字符串x中第y个位置开始长度为z的字符串 |
length(x) | 返回字符串x的长度 |
replace(x,y,z) | 将字符串z替代字符串x中的字符串y |
upper(x) | 将字符串x的所有字母变成大写字符 |
lower(x) | 将字符串x的所有字母变成小写字符 |
left(x,y) | 返回字符串x的前y个字符 |
right(x,y) | 返回字符串x的后y个字符 |
reprat(x,y) | 将字符串x重复y次 |
space(x) | 返回x个空格 |
strcmp(x,y) | 比较x和y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串x反转 |
(1)trim()
返回去除指定字符的值
语法:select trim ([位置] [要移除的字符串] from 字符串 );
[位置]:该值可以为leading(头部),trailing(结尾),both(头和尾)。缺省时为both
[要移除的字符串]:从字串的起头、结尾,或起头即结尾移除的字符串。缺省时为空格。
mysql> select trim('g' from 'guangdong');
+----------------------------+
| trim('g' from 'guangdong') |
+----------------------------+
| uangdon |
+----------------------------+
1 row in set (0.00 sec)
mysql> select trim(leading 'g' from 'guangdong');
+------------------------------------+
| trim(leading 'g' from 'guangdong') |
+------------------------------------+
| uangdong |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(trailing 'g' from 'guangdong');
+-------------------------------------+
| trim(trailing 'g' from 'guangdong') |
+-------------------------------------+
| guangdon |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(both 'g' from 'guangdong');
+---------------------------------+
| trim(both 'g' from 'guangdong') |
+---------------------------------+
| uangdon |
+---------------------------------+
1 row in set (0.00 sec)
(2)concat(x,y)
将参数x,y拼接成一个字符串
mysql> select concat (region,' ',store_name) from location where store_name='Beeijing';
+--------------------------------+
| concat (region,store_name) |
+--------------------------------+
| NorthBeijing |
+--------------------------------+
1 row in set (0.00 sec)
拼接时也可以指定 分割符
select concat (id,',',name) from t_merchant_information where id = 2;
+--------------------------------+
| concat (id,',',name) |
+--------------------------------+
| 2,联调 |
+--------------------------------+
如sql_mode开启了PIPES_AS_CONCAT(可使用"select @@SESSION.sql_mode;"或"select @@GLOBAL.sql_mode;"进行查看),"||"视为字符串的连接操作符而非或运算符,和字符串的拼接函数相类似,这和Oracle数据库使用方法一样。
mysql> select store_name || ' ' || sales from store_info where store_name='Guanngzhou';
+----------------------------+
| store_name || ' ' || sales |
+----------------------------+
| Guangzhou 1500 |
| Guangzhou 300 |
+----------------------------+
2 rows in set (0.00 sec)
(3)substr(x,y)
从字符串x的第y个位置开始获取字符串
mysql> select substr(store_name,3) from location where store_name='Guangzhou';
+----------------------+
| substr(store_name,3) |
+----------------------+
| angzhou |
+----------------------+
1 row in set (0.00 sec)
(4)substr(x,y,z)
从字符串x的第y个位置开始获取长度为z的字符串
mysql> select substr(store_name,3,4) from location where store_name='Guangzhou'';
+------------------------+
| substr(store_name,3,4) |
+------------------------+
| angz |
+------------------------+
1 row in set (0.00 sec)
(5)length(x)
返回字符串x的长度
mysql> select *,length(store_name) from location;
+--------+------------+--------------------+
| region | store_name | length(store_name) |
+--------+------------+--------------------+
| North | Beijing | 7 |
| East | Shanghai | 8 |
| South | Guangzhou | 9 |
| South | Shenzhen | 8 |
+--------+------------+--------------------+
4 rows in set (0.00 sec)
(6)replace(x,y,z)
用字符串z替换字符串x中的字符串y
mysql> select replace(store_name,'ng','xx') from location;
+-------------------------------+
| replace(store_name,'ng','xx') |
+-------------------------------+
| Beijixx |
| Shaxxhai |
| Guaxxzhou |
| Shenzhen |
+-------------------------------+
4 rows in set (0.00 sec)
(7)upper(x)
将字符串中的所有字母变大写
mysql> select upper(store_name) from location;
+-------------------+
| upper(store_name) |
+-------------------+
| BEIJING |
| SHANGHAI |
| GUANGZHOU |
| SHENZHEN |
+-------------------+
4 rows in set (0.00 sec)
(8)lower(x)
将字符串x中的所有字母变小写
mysql> select lower(store_name) from location;
+-------------------+
| lower(store_name) |
+-------------------+
| beijing |
| shanghai |
| guangzhou |
| shenzhen |
+-------------------+
4 rows in set (0.00 sec)
(9)left(x,y)
返回字符串x的前y个字符
mysql> select left('Beijing',3);
+-------------------+
| left('Beijing',3) |
+-------------------+
| Bei |
+-------------------+
1 row in set (0.00 sec)
(10)right(x,y)
返回字符串x的后y个字符
mysql> select right('Beijing',3);
+--------------------+
| right('Beijing',3) |
+--------------------+
| ing |
+--------------------+
1 row in set (0.00 sec)
(11)reprat(x,y)
将字符串x重复y次
mysql> select repeat('Beijing ',3);
+--------------------------+
| repeat('Beijing ',3) |
+--------------------------+
| Beijing Beijing Beijing |
+--------------------------+
1 row in set (0.00 sec)
(12)space(x)
返回x个空格
mysql> select space(10);
+------------+
| space(10) |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> select space(15);
+-----------------+
| space(15) |
+-----------------+
| |
+-----------------+
1 row in set (0.00 sec)
(13)strcmp(x,y)
比较x,y根据小于,等于,大于返回-1,0,1
x=y,返回0
mysql> select strcmp(1,1);
+-------------+
| strcmp(1,1) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
x<y,返回-1
mysql> select strcmp(1,2);
+-------------+
| strcmp(1,2) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
x>y,返回1
mysql> select strcmp(2,1);
+-------------+
| strcmp(2,1) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
(14)reverse(x)
字符串x翻转
mysql> select reverse(sales) from store_info;
+----------------+
| reverse(sales) |
+----------------+
| 0051 |
| 052 |
| 003 |
| 007 |
+----------------+
4 rows in set (0.00 sec)
三、sql查询语句
exists
用来测试内查询有没有产生任何结果,类似布尔值是否为真。
如果有,系统就会执行外查询中的sql语句。如果没有,那整个sql就不会产生任何结果。
语法:select "栏位1" from "表格1" where exists (select * from "表格2" where "条件");
mysql> select sum(sales) from store_info where exists
-> (select * from location where region='North');
+------------+
| sum(sales) |
+------------+
| 2750 |
+------------+
1 row in set (0.00 sec)
四、视图
五、uniom--连集
六、有/无交集值
- 可以使用on语句匹配指定列中的相同行
mysql> select A.store_name from location A inner join store_info B on A.store_name=B.store_name;
+------------+
| store_name |
+------------+
| Guangzhou |
| Shenzhen |
| Guangzhou |
| Beijing |
+------------+
4 rows in set (0.00 sec)
- 当两张表的列名相同时,也可使用using语句进行匹配
mysql> select A.store_name from location A inner join store_info B using(store_name);
+------------+
| store_name |
+------------+
| Guangzhou |
| Shenzhen |
| Guangzhou |
| Beijing |
+------------+
4 rows in set (0.00 sec)
七、case
case是SQL用来做if-then-else之类逻辑的关键字
语法:
select case ("栏位名")
when "条件1" then "结果1"
when "条件2" then "结果2"
...
[else "结果N"]
end
from "表名";
可以是一个数值或是一个公式,else子句并不是必须的
mysql> select store_name,case store_name
-> when 'Beijing' then sales *2
-> when 'Shenzhen' then 1000
-> else sales
-> end
-> 'new sales',date
-> from store_info;
+------------+-----------+------------+
| store_name | new sales | date |
+------------+-----------+------------+
| Guangzhou | 1500 | 2020-12-05 |
| Shenzhen | 1000 | 2020-12-07 |
| Guangzhou | 300 | 2020-12-08 |
| Beijing | 1400 | 2020-12-08 |
+------------+-----------+------------+
4 rows in set (0.00 sec)
then 数字,将直接返回该数字,即重新为匹配行赋值
'new sales'是用于case字段的字段名
八、常用算法
九、空值('NULL')和无值('')的区别
is NULL 或者is not NULL 只能用来判断字段是不是NULL,而不能查出是不是无值
=,!=,<> 可以用来判断是不是无值,但是不能判断是不是null
十、正则表达式
匹配模式 | 描述 | 实例 |
---|---|---|
^ | 匹配文本的开始字符 | '^a'匹配以a开头的字符串 |
$ | 匹配文本的结束字符 | 'a$'匹配以a结尾的字符串 |
. | 匹配任何单个字符 | 'a.c'匹配任何a和c之间有一个字符的字符串 |
* | 匹配零个或多个在它前面的字符 | 'ye*p'匹配p前面有任意个e |
+ | 匹配前面的字符1次或多次 | 'hi+'匹配以hi开头,后面至少一个i的字符串 |
字符串 | 匹配包含指定的字符串 | 'test'匹配含有test的字符串 |
a|b | 匹配a或b | 'test|wrok'匹配test或者work |
[...] | 匹配字符集合中的任意一个字符 | '[abc]'匹配a或者b或者c |
[^...] | 匹配不在括号中的字符 | '[abc]'匹配不包含a或者b或者c的字符串 |
{n} | 匹配前面的字符串n次 | 'a{2}'匹配含有2个a的字符串 |
{n,m} | 匹配前面的字符串至少n次,至多m次 | 'a{1,2}'匹配a至少1次,至多2次 |
语法:select "字段" from "表名" where "字段" regexp {模式};
mysql> select * from store_info where store_name regexp 'ng';
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Guangzhou | 1500 | 2020-12-05 |
| Guangzhou | 300 | 2020-12-08 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
3 rows in set (0.00 sec)
mysql> select * from store_info where store_name regexp '^[A-C]';
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
1 row in set (0.00 sec)
mysql> select * from store_info where store_name regexp 'ei|en';
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Shenzhen | 250 | 2020-12-07 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)