MySQL之day5
函数
函数种类:
- 算术函数
- 字符串函数
- 日期函数
- 转换函数
- 聚合函数
1. 算术函数
- + - * /
- abs --绝对值
- mod(被除数,除数) --求余
- round(对象数值, 保留小数位数) --四舍五入
2. 字符串函数
- concat(str, str2 ,...) -- 字符串拼接
- length(字符串) --计算长度
注意: 中文一个字相当于两个字节,一个半角字符一个字节
没有SQL 还有 char_length()函数
- lower(字符串)
- upper()
- replace(对象字符,替换前字符,替换后字符)
- substring(对象字符串 from 截取的起始位置 for 截取的字符数 )
3. 日期函数
mysql> select current_date; +--------------+ | current_date | +--------------+ | 2019-07-08 | +--------------+ 1 row in set (0.03 sec) mysql> select current_time; +--------------+ | current_time | +--------------+ | 10:38:43 | +--------------+ 1 row in set (0.02 sec) mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2019-07-08 10:39:07 | +---------------------+ 1 row in set (0.04 sec)
3.2 截取日期元素
- extract(日期元素 from 日期)
mysql> select current_timestamp, -> extract(year from current_timestamp) as year, -> extract(month from current_timestamp) as month, -> extract(day from current_timestamp) as day, -> extract(hour from current_timestamp) as hour, -> extract(minute from current_timestamp) as minute, -> extract(second from current_timestamp) as second; +---------------------+------+-------+------+------+--------+--------+ | current_timestamp | year | month | day | hour | minute | second | +---------------------+------+-------+------+------+--------+--------+ | 2019-07-08 11:01:10 | 2019 | 7 | 8 | 11 | 1 | 10 | +---------------------+------+-------+------+------+--------+--------+ 1 row in set (0.03 sec)
4. 转换函数
- 字符串 ----> 数值
select cast(<转换前的值> as signed integer) as <别名>
- 字符串 ----->日期
select cast(<日期> as date ) as <别名>
- 将null ----->其他值
coalesce(数据1, 数据2,......) --- 如果有NULL 则转换为对于数据字符 ,没有不会转换
mysql> select * from product; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0006 | 叉子 | 厨房用品 | 5000 | NULL | 2009-09-20 | NULL | | 0007 | 砧板 | 厨房用品 | 8800 | 395 | 2008-04-28 | NULL | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 12 rows in set (0.04 sec) mysql> select coalesce( purchase_price , 'null') -> from product; +------------------------------------+ | coalesce( purchase_price , 'null') | +------------------------------------+ | 500 | | 320 | | 2800 | | 1400 | | 2500 | | null | | 395 | | null | | 3000 | | null | | 3000 | | 40001 | +------------------------------------+ 12 rows in set (0.00 sec)
谓词
like
- 前方一致 ddd%
- 中间一致 %ddd%
- 后方一致 %ddd
- 下划线 ddd_ ----一个下划线一个字符,以此类推
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
mysql> select * -> from product -> where product_name like '%视%'; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 4 rows in set (0.00 sec)
between ---包括边界
mysql> select * -> from product -> where purchase_price between 400 and 3000; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 6 rows in set (0.00 sec)
in 和 not in
mysql> select * -> from product -> where product_id in ('0002','0011'); +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 2 rows in set (0.37 sec)
mysql> select * from shopproduct; +---------+-----------+------------+----------+ | shop_id | shop_name | product_id | quantity | +---------+-----------+------------+----------+ | 000A | 东京 | 0001 | 30 | | 000A | 东京 | 0002 | 50 | | 000A | 东京 | 0003 | 15 | | 000B | 名古屋 | 0002 | 30 | | 000B | 名古屋 | 0003 | 120 | | 000B | 名古屋 | 0004 | 20 | | 000B | 名古屋 | 0006 | 10 | | 000B | 名古屋 | 0007 | 40 | | 000C | 大阪 | 0003 | 20 | | 000C | 大阪 | 0004 | 50 | | 000C | 大阪 | 0006 | 90 | | 000C | 大阪 | 0007 | 70 | | 000D | 大阪 | 0001 | 100 | +---------+-----------+------------+----------+ 13 rows in set (0.00 sec) mysql> select product_name ,sale_price -> from product -> where product_id in (select product_id -> from shopproduct -> where shop_id = '000B'); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 打孔器 | 500 | | 运行T恤 | 4000 | | 菜刀 | 30000 | | 叉子 | 5000 | | 砧板 | 8800 | +--------------+------------+ 5 rows in set (0.00 sec)
exists --判断是否满足某种条件的记录 ,, 存在 的意思
注意: 关联子查询作为exists 参数
mysql> SELECT product_name, sale_price -> FROM Product AS P -> WHERE EXISTS (SELECT * -> FROM ShopProduct AS SP -> WHERE SP.shop_id = '000C' -> AND SP.product_id = P.product_id); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 运行T恤 | 4000 | | 菜刀 | 30000 | | 叉子 | 5000 | | 砧板 | 8800 | +--------------+------------+ 4 rows in set (0.00 sec)
case
分为简单CASE表达式和搜索CASE表达式两种
CASE WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> . . . ELSE <表达式> END --不可省略
mysql> select sum(case when sale_price < 1000 then 1 else 0 end ) as low_price, -> sum(case when sale_price between 1000 and 3000 then 1 else 0 end ) as mid_price, -> sum(case when sale_price >3001 then 1 else 0 end ) as high_price -> from product; +-----------+-----------+------------+ | low_price | mid_price | high_price | +-----------+-----------+------------+ | 2 | 1 | 8 | +-----------+-----------+------------+ 1 row in set (0.08 sec)
