• SQL函数汇总


    常见的一些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;
    
  • 相关阅读:
    请教JDBC中的thin和OCI的区别
    ORACLE中查询被锁定的表,以及如何解锁
    oracle批量插入测试数据
    oracle查看用户表
    decode 函数及其用法
    oracle序列详解
    MySQL数据库远程连接开启方法
    CentOS下MySQL忘记root密码解决方法【转载】
    oracle实现同时多表插入
    MyCat:第七章:MyCAT的亿级别任务
  • 原文地址:https://www.cnblogs.com/jly1/p/12977477.html
Copyright © 2020-2023  润新知