Java学习笔记——Oracle(上)
– 称为DB,全称”关系数据库管理系统”(RDBMS),一种用来存储管理数据的软件
一.基础
二. 查询 ( select )
三. Case…when
四. 函数 ( 单行函数 组函数 )
五. 总结
六. 练习
一.基础
1.数据库里的基本概念
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 )
1.简单查询 (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元的部门的最高工资.
(笔者第一次写博客,格式掌握的还不是很好,希望大家能多多给我指出不足,互相交流学习经验,谢谢大家。)