sql函数练习Oracal/plsql

初始数据

  CREATE TABLE TEST01(TEST_ID NUMBER(5),
                        TEST_NAME VARCHAR2(10),
                        TEST_NUMBER52 NUMBER(5, 2),
                        TEST_VARCHAR2BIG VARCHAR2(10),
                        TEST_VARCHAR2SMALL VARCHAR2(10));

    INSERT INTO TEST01
        (TEST_ID, TEST_NAME, TEST_NUMBER52, TEST_VARCHAR2BIG, TEST_VARCHAR2SMALL)
    VALUES
        (1, 'liming', 100.02, 'HELLO', 'hello');

    INSERT INTO TEST01 VALUES (2, '张三', 100.02, 'HELLO', 'hello');

    INSERT INTO TEST01 VALUES (3, '小李', -20, 'ENGLISH', 'english');

    INSERT INTO TEST01 VALUES (4, '李四', 5.5, 'MATH', 'math');

    INSERT INTO TEST01 VALUES (5, '王五', 5.4, 'MATH', 'math');

    SELECT * FROM TEST01;

–INITCAP(CHAR) 首字母大写

SELECT TEST_NAME 姓名, TEST_VARCHAR2SMALL 转换前, INITCAP(TEST_VARCHAR2SMALL) 转换后
      FROM TEST01;

– LOWER(CHAR) 转换为小写

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2BIG 转换前,
       LOWER(TEST_VARCHAR2BIG) 转换后
  FROM TEST01;

– UPPER(CHAR) 转换为大写

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 转换前,
       UPPER(TEST_VARCHAR2SMALL) 转换后
  FROM TEST01;

– LTRIM(CHAR,SET) 左剪裁

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 剪裁前,
       LTRIM(TEST_VARCHAR2SMALL, 'he') 剪裁后
  FROM TEST01;

– RTRIM(CHAR,SET) 右剪裁

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 剪裁前,
       RTRIM(TEST_VARCHAR2SMALL, 'llo') 剪裁后
  FROM TEST01;

– TRANSLATE(CHAR,FROM ,TO) 按字符翻译

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 翻译前,
       TRANSLATE(TEST_VARCHAR2SMALL, 'he', '12') 翻译后
  FROM TEST01;
  

– REPLACE(CHAR, SEARCH_STR, REPLACE_STR) 字符串替换

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 替换前,
       REPLACE(TEST_VARCHAR2SMALL, 'he', '??') 替换后
  FROM TEST01;

– INSTR(CHAR, SUBSTR[,POS]) 查找子串位置

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 数据,
       INSTR(TEST_VARCHAR2SMALL, 'el') 位置
  FROM TEST01;

– SUBSTR(CHAR,POS,LEN) 取子字符串

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 数据,
       SUBSTR(TEST_VARCHAR2SMALL, 1, 2) 子字符串
  FROM TEST01;

– CONCAT(CHAR1,CHAR2) 连接字符串

SELECT TEST_NAME 姓名,
       TEST_VARCHAR2SMALL 数据1,
       TEST_VARCHAR2SMALL 数据2,
       CONCAT(TEST_VARCHAR2SMALL, TEST_VARCHAR2SMALL) 连接字符串
  FROM TEST01;

添加数据

INSERT INTO TEST01 VALUES (3, '小李', -20, 'ENGLISH', 'english');
INSERT INTO TEST01 VALUES (4, '李四', 5.5, 'MATH', 'math');

– ABS(N) 取绝对值

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, ABS(TEST_NUMBER52) 取绝对值
  FROM TEST01;

– CEIL(N) 向上取整

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, CEIL(TEST_NUMBER52) 向上取整
  FROM TEST01;

– SIN(N) 正弦

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, SIN(TEST_NUMBER52) 正弦
  FROM TEST01;

– COS(N) 余弦

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, COS(TEST_NUMBER52) 余弦
  FROM TEST01;

– SIGN(N) 取符号

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, SIGN(TEST_NUMBER52) 取符号
  FROM TEST01;

– FLOOR(N) 向下取整

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, FLOOR(TEST_NUMBER52) 向下取整
  FROM TEST01;

– POWER(M,N) m的n次幂

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, POWER(TEST_NUMBER52, 2) 平方
  FROM TEST01;

– MOD(M,N) 取余数

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, MOD(TEST_NUMBER52, 2) 除2取余数
  FROM TEST01;

– ROUND(M,N) 四舍五入

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, ROUND(TEST_NUMBER52) 四舍五入
  FROM TEST01;

– TRUNC(M,N) 截断

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, TRUNC(TEST_NUMBER52) 截断
  FROM TEST01;

– SQRT(N) 平方根

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, SQRT(TEST_NUMBER52) 平方根
  FROM TEST01
 WHERE TEST_NUMBER52 > 0;

–添加列 alter table [表名] add 列名 [数据类型][约束]

alter table TEST01 add TEST_DATE DATE;
alter table TEST01 add TEST_DATESTRING NVARCHAR2(20);

初始化数据

CREATE TABLE TEST02(
       TEST_ID NUMBER(2),
       TEST_DATE DATE,
       TEST_DATE2 DATE,
       TEST_DATESTRING NVARCHAR2(20)
);

INSERT INTO TEST02
values
  (1, to_date('2022-06-08', 'yyyy-mm-dd'),to_date('2012-07-08', 'yyyy-mm-dd'), '2022/06/08');

INSERT INTO TEST02
values
  (3,
   to_date('2002-06-08', 'yyyy-mm-dd'),
   to_date('2012-07-08', 'yyyy-mm-dd'),
   '2002/06/08');

INSERT INTO TEST02
values
  (4,
   to_date('2022-05-08', 'yyyy-mm-dd'),
   to_date('2012-07-08', 'yyyy-mm-dd'),
   '2022/05/08');

SELECT * FROM TEST02;

– sysdate 返回系统时间

INSERT INTO TEST02
values
  (2, sysdate, to_date('2012-07-08', 'yyyy-mm-dd'), '2022/06/08');

– EXTRACT 从date类型数据抽取出某一部分

SELECT TEST_ID 序号,
       TEST_DATE 日期1,
       EXTRACT(YEAR FROM TEST_DATE),
       TEST_DATE2 日期2,
       EXTRACT(YEAR FROM TEST_DATE2)
  FROM TEST02;

– MONTHS_BETWEEN 返回两个日期间的月份

SELECT TEST_ID 序号,
       TEST_DATE 日期1,
       TEST_DATE2 日期2,
       MONTHS_BETWEEN(TEST_DATE, TEST_DATE2) 两个日期间的月份
  FROM TEST02;

– ADD_MONTHS 返回把月份数加到日期上的新日期

SELECT TEST_ID 序号, TEST_DATE 日期, ADD_MONTHS(TEST_DATE, 3) 新日期
  FROM TEST02;

–NEXT_DAY 返回指定日期后的星期对应的新日期

SELECT TEST_ID 序号, TEST_DATE 日期, NEXT_DAY(TEST_DATE, '星期一') 新日期
  FROM TEST02;

– LAST_DAY 返回指定日期所在的月的最后一天

SELECT TEST_ID 序号, TEST_DATE 日期, LAST_DAY(TEST_DATE) 本月最后一天
  FROM TEST02;

– ROUND 按指定格式对日期进行四舍五入

SELECT TEST_ID 序号, TEST_DATE 日期, ROUND(TEST_DATE, 'YEAR') 新格式
  FROM TEST02;
SELECT TEST_ID 序号, TEST_DATE 日期, ROUND(TEST_DATE, 'MONTH') 新格式
  FROM TEST02;

– TRUNC 对日期按指定方式进行截断

SELECT TEST_ID 序号, TEST_DATE 日期, TRUNC(TEST_DATE, 'MONTH') 新格式
  FROM TEST02;
  

–转换函数

– TO_CHAR

SELECT TEST_NAME 姓名,
     TEST_NUMBER52 数据,
       TO_CHAR(TEST_NUMBER52, '$999.99') 新数据
  FROM TEST01;

– TO_DATE

SELECT TEST_ID 序号,
       TEST_DATESTRING 日期,
       TO_DATE(TEST_DATESTRING, 'yyyy/mm/dd') 新格式
  FROM TEST02;

– TO_NUMBER

SELECT TEST_NAME 姓名, TEST_NUMBER52 数据, TO_NUMBER(TEST_NUMBER52) 新数据
  FROM TEST01;

修改数据

alter table TEST02 add NULLSTRING nvarchar2(20);
SELECT * FROM TEST02;

– NVL(EXP1,EXP2) 若EXP1值为null，返回EXP2的值，否则返回EXP1的值

SELECT TEST_ID 序号,
       TEST_DATE 日期,
       NULLSTRING 空数据,
       NVL(NULLSTRING, TEST_DATE2)
  FROM TEST02;

– NVL2(EXP1,EXP2,EXP3) 若EXP1值为null，返回EXP3的值，否则返回EXP2的值

SELECT TEST_ID 序号,
       TEST_DATE 日期1,
       TEST_DATE2 日期2,
       NULLSTRING 空数据,
       NVL2(NULLSTRING, TEST_DATE, TEST_DATE2)
  FROM TEST02;
  

– DECODE(VALUE,IF1,THEN1,IF2,THEN2,…,ELSE) 如果valus的值为if1则返回then1的值

SELECT TEST_ID 序号,
       TEST_DATE 日期1,
       TEST_DATE2 日期2,
       DECODE(1, 1, TEST_DATE, 2, TEST_DATE2),
       DECODE(2, 1, TEST_DATE, 2, TEST_DATE2)
  FROM TEST02;