5-基本的sql查询以及函数的使用

基本SQL查询语句以及函数的使用

格式元素	描述
YYYY	四位的年份
MONTH	月份的英文全称
MON	月份的英文简写
MM	月份的数字表示
DD	日起的1-31数字表示
D	星期几的数字表示1-7
DAY	星期几的全称
DY	星期几的英文简写
HH,HH24	时的数字表示1-12,1-24
MI	分
SS	秒
IW	本年过去的周数

--说明:查询基于HR用户表

--判断条件的综合可以使用AND OR

--1.查询EMPLOYEES表的所有内容
SELECT * FROM HR.EMPLOYEES;

--2.查询100号部门的员工
SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=100;

--3.支持进行比较的运算符: <, >, =, <>, >=, <=, IN, LIKE, IS NULL, BETWEEN AND;
--3.1查询工资在10000和20000之间的员工:
--注意(SALARY BETWEEN 值1 AND 值2) 等同于表达式 SALARY>=值1 and SALARY<=值2
SELECT * FROM HR.EMPLOYEES WHERE SALARY BETWEEN 10000 AND 17000;
SELECT * FROM HR.EMPLOYEES WHERE SALARY>=10000 AND SALARY<=17000;

--3.2查询50,60,100部门的员工
SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID IN (50,60,100);
--等同于
SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=50 OR DEPARTMENT_ID=60 OR DEPARTMENT_ID=100;

--3.3在使用LIKE是可以使用_表示任意一个字符,%表示任意多个字符
--3.3.1查询FIRST_NAME以字母S开头的员工
SELECT * FROM HR.EMPLOYEES WHERE FIRST_NAME LIKE 'S%';
--3.3.2查询FIRST_NAME第二字母是a的员工
SELECT * FROM HR.EMPLOYEES WHERE FIRST_NAME LIKE '_a%';
--3.3.3查询FIRST_NAME包含字母n的员工
SELECT * FROM HR.EMPLOYEES WHERE FIRST_NAME LIKE '%n%';
--3.4判断MANAGAER_ID为空的员工
--注意:在判断是不是为空是不能使用=NULL,需要使用IS NULL,同理不为空应为IS NOT NULL;
SELECT * FROM HR.EMPLOYEES WHERE MANAGER_ID IS NULL;

--4.按照工资高低排序显示员工信息
--注意默认是升序ASC ,降序为DESC
SELECT * FROM HR.EMPLOYEES ORDER BY SALARY desc;
--查询100号部门的员工并按照工资高低排序
SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=100 ORDER BY SALARY DESC;

--5.查询有员工的每个部门的员工数:对员工表按照DEPARTMENT_ID分组,使用order by
SELECT DEPARTMENT_ID,COUNT(*) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID;

--6.查询员工数大于5的部门及部门人数:
--此时需要的是对分组好的数据再次进行筛选,可以使用HAVING来增加筛选条件.
SELECT DEPARTMENT_ID,COUNT(*) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID 
HAVING COUNT(*)>5;

--7在分组中可以使用的分组函数
--MAX,MIN,SUM,COUNT,AVG
--7.1所有员工最大工资
SELECT MAX(SALARY) FROM HR.EMPLOYEES;
--7.2每个部门最小工资
SELECT DEPARTMENT_ID,MIN(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID;
--7.3每个部门人数
SELECT DEPARTMENT_ID,COUNT(*) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID;
--7.4每个部门平均工资,且平均工资大于10000的
SELECT DEPARTMENT_ID,AVG(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>10000;
--7.5每个部门的工资总数
SELECT DEPARTMENT_ID,SUM(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID;

--8基本的函数的使用:UPPER,LOWER,NVL
--dual是oracle的虚拟表
--字符函数
SELECT UPPER('abcd') FROM DUAL;
SELECT LOWER('ASV') FROM DUAL;
--to_char转换为字符窜
--转换函数NVL,若为NULL可以转换为指定的值
SELECT NVL(NULL,0) FROM DUAL;
--数值函数
SELECT ROUND(10.5),ROUND(10.4),ROUND(-10.5),ROUND(-10.6),ROUND(-10.4) FROM DUAL;--四舍五入
SELECT FLOOR(10.9),FLOOR(10.1) FROM DUAL;--下取整
SELECT CEIL(10.9),CEIL(10.1) FROM DUAL;--上取整
SELECT MOD(11,2),MOD(11,3) FROM DUAL;--取余数
--日期函数
--转换为日期：TO_DATE
--使用,当表内数据类型为DATE类型时,在插入数据时可以将字符窜转换为日起类型然后插入,或者数据更新时使用
UPDATE EMPLOYEES SET HIRE_DATE='2012-1-1' WHERE EMPLOYEE_ID=199;
--上面的更新语句会发生错误:ORA-01861: 文字与格式字符串不匹配
UPDATE EMPLOYEES SET HIRE_DATE=to_date('2012-1-1','yyyy-mm-dd') WHERE EMPLOYEE_ID=199;
--加上函数to_date()函数修改成功

--系统时间
SELECT SYSDATE FROM DUAL;
/*
不常用可能；
MONTHS_BETWEEN(date1,date2):
返回俩参数之间的月份 date1>date2返回正数反之返回负数结果的非整数部分表示不足一整月的那部分间隔
ADD_MONTHS(date,n):
向date加上n个月，n必须是整数并且不能为负数
NEXT_DAY(date,'char'):
求出date之后一周内某天char的日期，char是一个有效的表示星期几的数字或字符串
LAST_DAY(date):
求出date所在月的最后一天
ROUND(date,[,'fmt']):
将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为'DD',将date四舍五入为最近的天
TRUNC(date,[,'fmt'])
将date按照fmt指定的格式进行截取，fmt为可选项，如果没有指定fmt，则默认为'DD',将date截取为最近的天
*/
--常用的数字格式转换,显示结果随后
SELECT TO_CHAR(10,'9999'),--  10
       TO_CHAR(100,'0000'),--0100
       TO_CHAR(100,'$0000'),--$0100
       TO_CHAR(100,'L0000'),--￥0100
       TO_CHAR(100000000,'000,000,000,000')--000,100,000,000
from dual;