Oracle中常用SQL语句（二）

素颜马尾好姑娘i 2022-06-13 08:21 212阅读 0赞

**1、时间处理**

**（1）to\_char和to\_date基本使用**

--日期
    --年 yyyy yyy yy year
    --月 month mm mon month
    --日+星期 dd ddd(一年中第几天) dy day 
    --小时 hh hh24 
    --分 mi
    --秒 ss
    
    --如：
    
    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')currenttime, to_char(sysdate,'yyyy') year, to_char(sysdate,'mm') month, to_char(sysdate,'dd') day, to_char(sysdate,'day') week, to_char(sysdate,'hh24')hour, to_char(sysdate,'mi') minute, to_char(sysdate,'ss') second from dual;

![这里写图片描述][SouthEast]

**（2）months\_between**

select months_between(to_date('07-01-2017','MM-DD-YYYY'),to_date('07-07-2017','MM-DD-YYYY')) "MONTHS" FROM DUAL;

**（3）next\_day**

select sysdate today, next_day(sysdate,6) nextweek from dual;

**3、字符函数**

**（1）字符函数**

--字符函数
    select substr('abcdefg',1,5)substr,                     --字符串截取
           instr('abcdefg','bc') instr,                     --查找子串
    
           'Hello'||'World' concat,                         --连接
    
           trim(' wish ') trim,                           --去前后空格
           rtrim('wish ') rtrim,                           --去后面空格
           ltrim(' wish') ltrim,                           --去前面空格
    
           trim(leading 'w' from 'wish') deleteprefix,      --去前缀
           trim(trailing 'h' from 'wish') deletetrailing,   --去后缀
           trim('w' from 'wish') trim1,
    
           ascii('A') A1, 
           ascii('a') A2,                 --ascii(转换为对应的十进制数)
           chr(65) C1, 
           chr(97) C2,                    --chr(十进制转对应字符)
    
           length('abcdefg') len,                         --length 
    
           lower('WISH')lower,                            --变小写
           upper('wish')upper,                            --变大写
           initcap('wish')initcap,                        --大小写变换
    
           replace('wish1','1','youhappy') replace,       --替换
    
           translate('wish1','1','y')translate,           --转换，对应一位(前面的位数大于等于后面的位数)
           translate('wish1','sh1','hy')translate1,
    
           concat('11','22') concat          　　　　　　　　 --连接
    
    
    from dual;

![这里写图片描述][SouthEast 1]

**（2）to\_number**

--to_number(expr)
    --to_number(expr,format)
    --to_number(expr,format,'nls-param')
    
    select to_number('0123')number1, --converts a string to number trunc(to_number('0123.123'),2) number2, to_number('120.11','999.99') number3, 　　　 to_number('0a','xx') number4, --converts a hex number to decimal to_number(100000,'xxxxxx') number5 from dual;

![这里写图片描述][SouthEast 2]

**4、聚合函数**

**（1）count**

--count (distinct|all)
    select count(1) as count from student; --效率最高
    select count(*) as count from student;    
    select count(distinct score) from student;

**（2）avg**

--avg (distinct|all)
    select avg(score) score from student;
    select avg(distinct score) from student;
    select classno,avg(score) score from student group by classno;

**（3）max**

--max (distinct|all)
    select max(score) from student;
    select classno, max(score) score from student group by classno;

**（4）min**

--min (distinct|all)
    select min(score) from student;
    select classno, min(score) score from student group by classno;

**（5）sum**

--sum
    select sum(score) from student;
    select classno, sum(score) score from student group by classno;

[SouthEast]: /images/20220613/24fde3c7fc084abea6b67f268bdd2c52.png
[SouthEast 1]: /images/20220613/95c4feb280104ea995e5ad5441030fb8.png
[SouthEast 2]: /images/20220613/f7b9b25fd61b4d2ebcfc0d144adce4ba.png