SQL 21 天自学通学习笔记 2

日期、时间函数：

 

select * from project;

 

针对日期型：

 

1. add_months:  增加月份

    select * from task,startdate,enddate,original_end,add_months(enddate,2) from project;

 

    select  task tasks_shorter_than_one_month from project where add_months(startdate,1) > enddate;

 

2. last_day: 最后一天

 

select enddate,last_day (enddate) from project;

 

select distinct last_day('1-FEB-95') NON_LEAP,LAST_DAY('1-FEB-96') LEAP FROM PROJECT;

 

3. months_between: 中间有几个月

 

select task,startdate,enddate,months_between(enddate,startdate) duration from project;

 

select * from project where months_between('19 MAY 95',STARTDATE) > 0;

 

4. new_time: 调整时区

 

select enddate edt,new_time(enddate,'edt','pdt') from project;

 

5. next_day: 返回与指定日期同一星期或下一星期的，星期几的确切日期；

 

select startdate,next_day(startdate,'friday') from project;

 

6. sysdate:系统日期和时间

 

select distinct dysdate from project;

 

select * from project where startdate > sysdate;

 

 

数学函数：

 

1. ABS : 绝对值 

 

select ABS(A) absolute_value from numbers;

 

2. CELL (比参数大的最小整数) 和 FLOOR(比参数小的最大整数)

 

select B，cell(B) ceiling from numbers;

 

select A，floor(A) floor from numbers;

 

3. COS COSH SIN SINH TAN TANH 三角函数，默认参数认定为弧度制；

 

4. EXP : 返回给定参数为指数，e为底数的幂值；

 

select A,EXP(A) from numbers;

 

5. LN : 返回给定参数的自然对数；

 

6. LOG: 返回结合参数的对数；

 

7. MOD: 取模；

 

SELECT A B MOD A B FROM NUMBERS

 

8. POWER: 幂数；

 

SELECT A B POWER A B FROM NUMBERS；

 

9. SIGN：如果参数的值为负数那么SIGN 返回-1 如果参数的值为正数那么SIGN 返回1

如果参数为零那么SIGN 也返回零；

 

SELECT A SIGN A FROM NUMBERS

 

 

10. SQRT：开方

 

SELECT A SQRT A FROM NUMBERS

 

 

字符串函数：

 

1. concat : 连接，等同“|| ”;

 

2. initcap：首字母转大写

 

select firstname before,initcap(firstname) after from characters;

 

3.  upper,lower

 

select firstname,upper(firstname),lower(firstname) from characters

 

4. lpad,rpad  填充字符串 ， 至少两个参数

 

SQL>  select lastname,lpad(lastname,20,'*') from characters;

 

LASTNAME                             LPAD(LASTNAME,20,'*')

------------------------------------ ----------------------------------------

PURVIS                               **************PURVIS

TAYLOR                               **************TAYLOR

CHRISTINE                            ***********CHRISTINE

ADAMS                                ***************ADAMS

COSTALES                             ************COSTALES

KONG                                 ****************KONG

 

6 rows selected

 

SQL>  select lastname,rpad(lastname,20,'*') from characters;

 

LASTNAME                             RPAD(LASTNAME,20,'*')

------------------------------------ ----------------------------------------

PURVIS                               PURVIS**************

TAYLOR                               TAYLOR**************

CHRISTINE                            CHRISTINE***********

ADAMS                                ADAMS***************

COSTALES                             COSTALES************

 

 

5. ltrim,rtrim 截取字符串  ，至少一个参数

 

SQL> select lastname,rtrim(lastname) from characters;

 

LASTNAME                             RTRIM(LASTNAME)

------------------------------------ ------------------------------------

PURVIS                               PURVIS

TAYLOR                               TAYLOR

CHRISTINE                            CHRISTINE

ADAMS                                ADAMS

COSTALES                             COSTALES

KONG                                 KONG

 

6 rows selected

 

6. replace : 替换 

 

SQL> select lastname,replace(lastname,'ST','') replacement from characters;

 

LASTNAME                             REPLACEMENT

------------------------------------ ------------------------------------

PURVIS                               PURVIS

TAYLOR                               TAYLOR

CHRISTINE                            CHRIINE

ADAMS                                ADAMS

COSTALES                             COALES

KONG                                 KONG

 

6 rows selected

 

SQL> select lastname,replace(lastname,'ST',null) replacement from characters;

 

LASTNAME                             REPLACEMENT

------------------------------------ ------------------------------------

PURVIS                               PURVIS

TAYLOR                               TAYLOR

CHRISTINE                            CHRIINE

ADAMS                                ADAMS

COSTALES                             COALES

KONG                                 KONG

 

6 rows selected

 

SQL> select lastname,replace(lastname,'ST','*') replacement from characters;

 

LASTNAME                             REPLACEMENT

------------------------------------ ------------------------------------

PURVIS                               PURVIS

TAYLOR                               TAYLOR

CHRISTINE                            CHRI*INE

ADAMS                                ADAMS

COSTALES                             CO*ALES

KONG                                 KONG

 

6 rows selected

 

7. substr ： 截取字串

 

SQL> select firstname,substr(firstname,2,3) from characters;

 

FIRSTNAME                            SUBSTR(FIRSTNAME,2,3)

------------------------------------ ---------------------

kelly                                ell

CHUCK                                HUC

LAURA                                AUR

FESTER                               EST

ARMANDO                              RMA

MAJOR                                AJO

 

6 rows selected

 

SQL> select firstname,substr(firstname,3) from characters;

 

FIRSTNAME                            SUBSTR(FIRSTNAME,3)

------------------------------------ --------------------------------------------------------------------

kelly                                lly

CHUCK                                UCK

LAURA                                URA

FESTER                               STER

ARMANDO                              MANDO

MAJOR                                JOR

 

6 rows selected

 

 

8. translate : 翻译函数 ，三个参数，对大小写敏感

 

select firstname,translate(firstname,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA')

  2  from characters;

 

FIRSTNAME                            TRANSLATE(FIRSTNAME,'012345678

------------------------------------ ------------------------------------------------------------------------

kelly                                kelly

CHUCK                                AAAAA

LAURA                                AAAAA

FESTER                               AAAAAA

ARMANDO                              AAAAAAA

MAJOR                                AAAAA

 

6 rows selected

 

 

9. instr 搜索函数

 

SQL> select lastname, instr(lastname,'O',2,1)from characters;

 

LASTNAME                             INSTR(LASTNAME,'O',2,1)

------------------------------------ -----------------------

PURVIS                                                     0

TAYLOR                                                     5

CHRISTINE                                                  0

ADAMS                                                      0

COSTALES                                                   2

KONG                                                       2

 

6 rows selected

 

10 lenght : 返回字符串的长度

 

SQL>  select firstname,length(rtrim(firstname)) from characters;

 

FIRSTNAME                            LENGTH(RTRIM(FIRSTNAME))

------------------------------------ ------------------------

kelly                                                       5

CHUCK                                                       5

LAURA                                                       5

FESTER                                                      6

ARMANDO                                                     7

MAJOR                                                       5

 

6 rows selected

 

 

转换函数：

 

1. to_char: 转换成字符串；

 

select testnum,length(to_char(testnum)) from conversions;

 

TESTNUM LENGTH(TO_CHAR(TESTNUM))

------- ------------------------

     95                        2

     23                        2

     68                        2

 

2. to_number: 转换成数字；

 

select name,testnum,testnum*to_number(name) from conversions;

 

NAME            TESTNUM TESTNUM*TO_NUMBER(NAME)

--------------- ------- -----------------------

40                   95                    3800

13                   23                     299

74                   68                    5032

 

3. greatest , least

 

SQL> select greatest('ALPHA','BRAVO','FOXTROT','DELTA') FROM CONVERSIONS;

 

GREATEST('ALPHA','BRAVO','FOXT

------------------------------

FOXTROT

FOXTROT

FOXTROT

 

SQL> select least(34,567,3,45,1090) from conversions;

 

LEAST(34,567,3,45,1090)

-----------------------

                      3

                      3

                      3

 

4. user 查询表所属的用户；

 

SQL> select user from characters;

 

USER

------------------------------

oracle