组函数和表关系

第三章组函数和表关系

字符串函数

-- concat   连接两个字符串

select concat("abc","ABC") from dual;

select "abc"||"ABC" from dual;

select concat(first_name,"_")||last_name con,t.* from EMPLOYEES t;

-- initcap  返回字符串，第一个大写，其余小写

select initcap(email) from EMPLOYEES;

-- length   返回字符串长度

select length(email),t.* from EMPLOYEES t;

-- lower    所有字符小写

-- upper    所有字符大写

select lower(first_name),upper(last_name),t.* from EMPLOYEES t;

-- substr 字符串截取

select substr("123456789",3,4) from dual;

从第 3个 截取，截取 4 个字符

-- replace 字符串替换

select replace("he love you", "he", "i") from dual;

I love you

数学函数

-- CEIL 返回大于或等于给出数字的最大整数

select ceil("123.456789") from dual;

select ceil("123") from dual;

-- FLOOR 返回小于或等于给出数字的最小整数

select floor("123.456789") from dual;

-- Round 函数进行四舍五入

select round(124.1666,-2),round(124.1666,2) from dual;

-2,小数点前两位，四舍五入

2，小数点前两位，四舍五入

-- trunc 函数进行截取，直接截取，不进行四舍五入

select trunc(124.1666,-2) , trunc(124.16666,2) from dual;

日期函数

select sysdate from dual;

select to_char(sysdate,"yyyy-mm-dd hh24:mi:ss") from dual;

select to_char(sysdate,"yyyy-mm-dd hh12:mi:ss") from dual;

select to_date("2019-02-27 18:30:31","yyyy-mm-dd hh24:mi:ss") from dual;

select systimestamp from dual;

select to_timestamp("2019-02-27 18:30:31.123","yyyy-mm-dd hh24:mi:ss.ff") from dual;

-- add_Months 增加或减去月份

select add_months(sysdate，2) from dual;

select add_months(sysdate，-2) from dual;

-- months_between 显示日期相差的月数

select months_between(

       to_date("2019-02-01","yyyy-mm-dd")，

       to_date("2019-10-01","yyyy-mm-dd")

) from dual;

聚合函数，组函数

-- max 最大值

select department_id,max(salary) from EMPLOYEES  group by department_id;

-- min 最小值

select manager_id,min(salary) from EMPLOYEES group by manager_id;

-- avg 平均

SELECT department_id,AVG(salary) AVGSAL FROM EMPLOYEES GROUP BY department_id;

-- sum 总和

SELECT department_id,SUM(salary) SUMSAL FROM EMPLOYEES GROUP BY department_id;

-- count 统计

select count(1) from EMPLOYEES;

表关系 设计阶段

多表连接 sql实现阶段

示例：