SQL函数汇总
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL函数汇总,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2644字,纯文字阅读大概需要4分钟。
内容图文
常见的一些SQL函数, 包含数值, 字符串, 日期和IF, CASE等函数
-- 数值函数
-- ROUND 四舍五入, CEILING,最大值
SELECT ROUND(5.73, 1);
SELECT CEILING(5.7);
SELECT FLOOR(5.2);
SELECT ABS(-5.2);
-- 0-1 , 随机数字
SELECT RAND();
-- 字符串函数
SELECT LENGTH(‘sky‘);
SELECT UPPER(‘sky‘);
SELECT LOWER(‘Sky‘);
-- 去空格
SELECT LTRIM(‘ Sky‘);
SELECT LTRIM(‘Sky ‘);
SELECT TRIM(‘Sky ‘);
-- 取一段字符串, 从1开始数
SELECT LEFT(‘Kindergarten‘, 4);
SELECT RIGHT(‘Kindergarten‘, 4);
SELECT SUBSTRING(‘Kindergarten‘, 3, 5);
-- 取字符位置
SELECT LOCATE(‘q‘, ‘Kindergarten‘);
-- 替代
SELECT REPLACE(‘Kindergarten‘, ‘garten‘, ‘garden‘);
-- 连接
SELECT CONCAT(‘first‘, ‘last‘);
USE sql_store;
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM customers;
-- 时间函数
SELECT NOW(), CURDATE(), CURTIME();
SELECT(YEAR(NOW()));
SELECT(MONTH(NOW()));
SELECT(DAY(NOW()));
SELECT(HOUR(NOW()));
SELECT(MINUTE(NOW()));
SELECT(SECOND(NOW()));
SELECT(HOUR(NOW()));
SELECT(DAYNAME(NOW()));
SELECT(MONTHNAME(NOW()));
SELECT EXTRACT(DAY FROM NOW());
-- Exercise
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW());
SELECT DATE_FORMAT(NOW(), ‘%M %d %Y‘);
-- 计算日期和函数
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);
SELECT DATEDIFF(‘2019-01-05‘, ‘2019-01-01‘);
SELECT TIME_TO_SEC(‘09:00‘) - TIME_TO_SEC(‘09:02‘);
-- IFNULL
USE sql_store;
SELECT
order_id,
shipper_id,
IFNULL(shipper_id, ‘Not assigned‘) AS shipper
FROM orders;
-- COALESCE
SELECT
order_id,
shipper_id,
comments,
COALESCE(shipper_id, comments, ‘Not assigned‘) AS shipper
FROM orders;
-- Exercise
SELECT
CONCAT(first_name, ‘ ‘, last_name) AS customer,
IFNULL(phone, ‘Unknown‘) AS phone
FROM customers;
SELECT
CONCAT(first_name, ‘ ‘, last_name) AS customer,
COALESCE(phone, ‘Unknown‘) AS phone
FROM customers;
-- IF
SELECT
order_id,
order_date,
IF(
YEAR(order_date) = 2019,
‘Active‘,
‘Archived‘) AS category
FROM orders;
-- Exercise
SELECT
product_id,
name,
COUNT(*) AS orders,
IF(
COUNT(*) > 1,
‘Many times‘,
‘Once‘
) AS frequency
FROM products
JOIN order_items USING(product_id)
GROUP BY product_id, name;
-- CASE
SELECT
order_id,
CASE
WHEN YEAR(order_date) = 2019 THEN ‘Active‘
WHEN YEAR(order_date) = 2018 THEN ‘Last Year‘
WHEN YEAR(order_date) < 2018 THEN ‘Archived‘
ELSE ‘Future‘
END AS category
FROM orders;
SQL函数汇总
标签:order replace one case time archive ring signed round
本文系统来源:https://www.cnblogs.com/jly1/p/12977477.html
内容总结
以上是互联网集市为您收集整理的SQL函数汇总全部内容,希望文章能够帮你解决SQL函数汇总所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。