Java学习笔记——Oracle（上）

– 称为DB,全称”关系数据库管理系统”(RDBMS),一种用来存储管理数据的软件

一.基础

二. 查询 ( select )

三. Case…when

四. 函数 ( 单行函数 组函数 )

五. 总结

六. 练习

---

一.基础

１.数据库里的基本概念

1）表（table）:实际负责数据存储的单元

2）行（row）:表示一组有挂链的业务数据，也称记录

3）列（column）:表示业务数据的一个具体的一个具体的属性，也称 “记录”

a 主键（primary key）：用来唯一标识表里的一行记录【非空且唯一】，表里最多只有一主键 b 外键（foreign key）：用来指向另外一个表的“主键”或“唯一键”，唯一一种可体现两表有关系的方式

2.工作方式C/S（client/server）

服务器： OracleServiceXe:实际负责存储管理数据 OracleXETNSListener：提供Oracle对外链接支持

客户端： (1)Sqlplus — oracle提供的内置的基于Dos界面的client 进入 — sqlplus 用户名/密码 退出 — exit 或者 ctrl+c 执行sqlplus命令 — desc 表名 (查看表结构)

(2). iSqlplus — 提供了基于浏览器的client 进入 — 在地址栏输入 http://127.0.0.1:8080/apex 执行命令 — select * from employees;

(3). 第三方client ( PLSqlDeveloper MyEclipse(jdbc) )

二. 查询 ( select )

１.简单查询 （select…from…） 1 查询一部分字段

--- 查所有 Select * from employees; --- 对结果字段做数学运算 Select employee_id id, salary*12 “anual salary “ from employees; 注意:可以为查询到的结果字段定义别名,如果别名里含有特殊字符或者需要区分别 名的大小写, 可以用”双引号” --- 可以用符号 || 连接查询结果 Select last_name||’ ‘||first_name from employees; 注意: 如果命令中使用到 ”字符串字面值” 或者 “日期字面值” 必须使用 单引号

2.排序

语法 — select … From … Order by 排序条件[desc| asc ];

-- 查询员工信息,并按照工资排序(默认升序) Select * from employees order by salary ; -- select employee_id,last_name,salary*12+10000 from employees order by 3 desc; -- select employee_id,last_name,salary,department_id from employees order by department_id,salary desc ; 按部门升序排列,部门一样按工资降序排列 ***注意:在oracle中null算最大值*** 3. 条件查询 ( where) ---- 筛选条件( 逐行筛选　） 语法 --- select ... From ... Where ... Order by ... 1) 等值条件查询 = != -- 请打印30部门的员工信息 Select * from employees where department_id = 30; -- 请打印King( last_name )的详细信息 Select * from employees where last_name =’King’ ; 注意: 字符串字面值比较时,严格区分大小写 2) 一般条件查询 > >= <= < and or -- 请打印工资在5000~10000的员工信息 Select * from employees where salary>=5000 and salary<=10000; 　 3) 范围查询 between ... And [了解] 语法 --- where 字段 between 小值 and 大值; -- 包括边界 -- select * from employees where salary between 5000 and 10000; 4) 枚举 in( 值1, 值2 , ...... ) --- 请查询70,40,30部门员工信息 Select * from employees where department_id=30 or department_id=40 or ......=70; Select * from employees where department_id in (30,40,70); 5) 查询 null值语法 [ is null , is not null ] Select * from employees where department_id = null; ---- error Select * from employees where department_id is null; ----ok 注意: sql命令的表达式只要含有null值,结果一定为null 6) 模糊查询 语法 --- where 字段 like ‘格式字符串’ ; 格式字符串组成 --- 普通字符 + ( % 表示0~n个字符) 或者 ( _ 表示1个字符) --- ‘张%’ ‘张_’ ‘%_@_%’ --- 查询last_name是以’K’开头的员工信息 Select * from employees where last_name like ‘K%’; --- select * from employees where last_name like ‘____’; --- 请查询last_name是以’K_’开头的员工信息 Select * from employees where last_name like ‘K_%’; --- error Select * from employees where last_name like ‘K\_%’ escape ‘\’ ; 转义字符: 使得紧跟在它之后的字符按本意处理,不做特殊用途,通常系统默认的转义 字符为”\” , oracle里没有默认转义字符,使用时需要声明

三. Case…when

类似于java里的switch…case语法: case when boolean表达式 then 结果1 When 表达式2 then 结果2 …….. Else 结果n End

注意: 1) else不是必须的,如果所有条件都不满足,则语法结构返回 null 2) 永远 只有一个分支被执行 3) 要求 所有结果 类型必须一致

--- 请打印员工的工资级别,并按级别排序( 级别分类: A 5000 B 5001~8000 C8001~12000 D: 12001以上 ) Select employee_id,last_name,salary,( case when salary<=5000 then ‘A’ when salary<=8000 then ‘B’ when salary<=12000 then ‘C’ else ‘D’ end) grade from employees order by 4; --- 请打印员工的编号,姓名,以及性别( 奇数’男’ 偶数’女’) --- 行列转换 ( 列转行 )

四. 函数 ( 单行函数 组函数 )

单行函数 — 针对表里的每一行数据运行一次,得到一个结果

— abs(num) : 计算num的绝对值 — select abs(salary) from employees; 得到107个salary的绝对值 — select abs(-1) from employees; 得到了107个1 — dual : oracle server提供的单行单列表,用来完善select语句. select abs(-1) from dual;

— sysdate : 获取当前数据库服务器系统时间 systimestamp — select sysdate from dual; ( oracle标准日期格式 dd-mon-rr )

— to_char( 日期值 , ‘格式字符串’ ) : 将给定的日期值按格式要求转换成字符串 格式字符串组成:

— 请打印当前系统时间(显示出小时,分钟秒和星期) Select to_char(sysdate,’yyyy-mm-dd,day,hh24:mi:ss’) from dual;

— 请打印当前月份 Select to_char( sysdate,’mm’) from dual;

— 请打印公司里1997年入职的员工信息 Select * from employees where hire_date like ‘%97’; Select * from employees where to_char(hire_date,’yyyy’) = 1997 ;

— to_date ( 字符串日期值 , ‘格式字符串’ ) : 将给定的String日期表示转换成标准日 期值 — 请打印生日当天是星期几 Select to_char( to_date(‘12-06-95’,’dd-mm-yy’) , ‘yyyy-mm-dd,day’ ) from dual;

— dbms_random.random() : 获取一个随机数( 整数, 可正可负 ) Select mod( abs( dbms_random.random() ) , 44) +1 from dual;

组函数 — 作用于分好的每一组数据执行一次,得到一个结果 常见组函数 : max() – 最大值 min() — 最小值 avg() – 平均数 sum() – 求和 count() — 计算个数 注意 : 组函数统计结果时,会忽略null

– 请打印公司的平均工资,和最高工资 Select avg(salary) , max(salary) from employees;

– count( 字段 ) : 统计该字段里非空值的个数 Count(*) : 统计指定结果里非空行的数量

--- 请统计公司里6月入职的员工人数 Select count(*) from employees where to_char(hire_date,’mm’)=6;

分组语句 group by 语法 — select … From …. Where … Group by … Order by …

— 请打印公司部门编号,以及各部门平均工资 1) 确定表 from employees 2) 筛选条件 — 没有 3) 确定分组条件 : group by department_id 4) 确定查询数据 : select department_id , avg(salary) 5) 合并 : select department_id,avg(salary) from employees group by department_id;

— 请统计公司里1997年各月入职的员工人数(按人数多少降序排列) 1) from employees 2) where to_char(hire_date,’yyyy’) = 1997 3) group by to_char(hire_date,’mm’) 4) select to_char(hire_date,’mm’) , count(*) 5) order by 2 desc;

语法规定: (首位呼应) 1) 只有出现在group by里的字段 , 才能单独出现在select和order by里 2) 没有出现在group by里的字段 , 需要配合组函数才能出现在select和order by里 3) 如果在group by里应用了某个单行函数,则select和order by里必须同样处理

分组后的条件筛选 — having 语法 — select … From … Where … Group by … Having … Order by … 作用 — 对分组后的数据做筛选

— 请统计公司里1997年各月入职的员工人数( 只显示人数大于等于2的月份数据 ) 1) from employees 2) where to_char(hire_date,’yyyy’) = 1997 3) group by to_char(hire_date,’mm’) 4) having count(*)>=2 5) select to_char(hire_date,’mm’) , count(*)

— 请打印部门编号,以及各部门人数(要求,只显示人数大于2的部门) 1) from employees 2) group by department_id 3) having count(*)>2 4) select department_id,count(*)

Where 和 having的区别 1) where 筛选时数据以 “行” 为单位存在, 可以使用单行函数 2) having 筛选时数据以 “组”为单位存在, 可以使用组函数 3) having和where都可以完成功能,则优选where

五. 总结

语法 — select…from…where…group by …having…order by 执行顺序: 1. From 表名 —- 确定基础数据来源表 2. Where 条件 —- 对基础数据按条件”逐行”筛选,生成结果数据 3. Group by 分组条件 — 对结果数据按条件分组 4. Having 条件 — 对分组后的数据再次按条件筛选 5. Select —- 对处理好的数据按用户要求生成结果 6. Order by — 对最终结果数据排序 显示

六. 练习

练习

1. 查询员工表所有数据 2. 打印公司里所有的manager_id 3. 查询所员工的email全名,公司email 统一以 "@zpark.cn" 结尾 4. 按照入职日期由新到旧排列员工信息 5. 查询80号部门的所有员工 6. 查询50号部门每人增长1000元工资之后的人员姓名及工资. 7. 查询80号部门工资大于7000的员工的全名与工资. 8. 查询80号部门工资大于8000并且佣金高于0.3的员工姓名,工资以及提成 9. 查询职位(job_id)为'AD_PRES'的员工的工资 10. 查询佣金(commission_pct)为0或为NULL的员工信息 11. 查询入职日期在1997-5-1到1997-12-31之间的所有员工信息 12. 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息 13. 查询电话号码以5开头的所有员工信息. 14. 查询80号部门中last_name以n结尾的所有员工信息 15. 查询所有last_name 由四个以上字母组成的员工信息

单行函数练习

1. 把hiredate列看做是员工的生日,查询本月过生日的员工(考察知识点:单行函数) 2. 请用三种以上的方式查询2002年入职的员工(考察知识点:单行函数) 3. 查询2002年下半年入职的员工(考察知识点:单行函数) 4. 打印自己出生了多少 5. 打印入职时间超过10年的员工信息

组函数练习

1. 显示各种职位的最低工资(组函数) 2. 求1997年各个月入职的的员工个数(考察知识点:组函数) 3. 查询每个部门,每种职位的最高工资(考察知识点:分组) 4. 查询各部门的总工资 5. 查询50号部门,60号部门,70号部门的平均工资 6. 查询各部门的最高工资,最低工资. 7. 查询各岗位的员工总数. 8. 查询各部门中各个岗位的平均工资. 9. 查询平均工资高于8000元的部门的最高工资.

(笔者第一次写博客，格式掌握的还不是很好，希望大家能多多给我指出不足，互相交流学习经验，谢谢大家。)