Oracle基本入门
一、数据的存储
1.java 程序中的对象:数组、集合保存。当运行的程序结束的时候，里面的数据就消亡。
2.文件存储系统：
存在的缺陷：
2.1）没有明确的数据类型划分。
2.2）没有用户身份验证机制 不安全。
2.3）不利于维护。
二、数据库概述
1.概念： 数据库是将数据以某种特定的结构组织、管理存储的系统。
2.数据库分类：
2.1)关系型数据库
--关系型数据库：是将数据，以一张二维表的形式进行存储，表与表之间存在这交叉引用关系。
--基本的名词：
1）table(表)：是数据库中保存数据的基本单元。也称之为实体集 entitySet.
2）Row(行)：代表具体某一条数据 也称之为entity(实体).
3）Column(列)：代表当前列 存储数据的特点。
4）主键（primary key）：唯一标识表中的一条数据，不能为null；
5) 外键：（foreign key）:代表的是本表记录与其他表之间的关系，可以为null.
--管理形式:基于用户的形式管理，在访问数据库中的数据之前，需要验证身份。
--常见的关系型数据库：
Oracle | 甲骨文
DB2 | IBM
SqlServer | Microsoft 微软
MySql | 甲骨文—开源，从SUN公司手中收购。
2.2)非关系型数据库（NOSQL-not only sql）
Redis // 基于key-value形式存储数据。
MongoDB// 基于文档的存储形式。
HBase Hive
三、Oracle数据库
1.可以去官网下载
2.安装
2.1）注意事项：建议安装在磁盘根目录，目录不允许有中文或者空格。
2.2）关于卸载：再次运行安装软件，千万不要手动删除；
3.启动核心服务
在开始-》运行输入servcies.msc，进入系统服务目录。
OracleServiceXE:Oracle数据库服务的核心服务。
OracleXETNSListener:是Oracle对外提供访问的服务，比如可以通过浏览器访问、程序访问。
1)命令解锁hr用户：alter user hr account unlock;
2)修改账户密码：alter user hr identified by 新密码；
4.访问方式
4.1）基于sqlplus命令窗口访问：是oracle系统默认提供；
4.2）通过浏览器访问。
4.3）PL-SQL访问—第三方提供的。
基本操作：新建—》sql窗口—》书写命令—》执行命令F8或者
5.SQL（Structured Query Language 结构化查询语言）：sql是所有关系型数据库的标准查询语言，是操作数据库的基本方式。
6.基础的查询
1.简单的查询
语法：SELECT 字段名1，字段名2 FROM 表名。
说明：SELECT指定要查询的字段名，多个字段使用“，”隔开。
FROM后面指定的是从哪张表查询数据。
书写sql语句简单思路：
1)确定从哪张表查询。
2)确定查询的字段。
2.案例
2.1）查询多个字段
SELECT FIRST_NAME,LAST_NAME,EMAIL FROM EMPLOYEES;
2.2）查询所有字段信息
SELECT*FROM EMPLOYEES;
SELECT 所有字段 FROM EMPLOYEES;
注意：*的方式虽然写着简单，但是执行的效率相比直接写出字段名要低，开发的时候不建议使用*，sql命令书写的时候可以不区分大小写，但是最终执行的时候是大写，建议开发使用大写。
3.算数运算：+ - * / // 不支持 %运算
--查询所有员工的姓、名、年薪，部门编号
SELECT LAST_NAME,FIRST_NAME,(SALARY/30)*12,DEPARTMENT_ID
FROM EMPLOYEES;
--查询所有员工入职日期的前一天
select hire_date-1 from employees;
注意：字符串不支持算术运算，日期支持，运算单位是（天）
4.字段起别名：select 字段名 as 别名 from 表名；
-- 查询姓、名、年薪
select last_name AS "xing",first_name AS "ming",salary*12 AS yearSalary from employees;
注意：对于字段的别名，如果是英文，加双引号严格区分大小写，不加则不区分。
起别名过程中，可以省略 “AS”关键字，但是不规范。
5.字符串拼接 字符串1||字符串2
--查询所有员工姓名、年薪
select last_name||'_'||first_name as 姓名,salary*12 as 年薪 from employees;
6.排序查询【重点】
6.1 语法：select 字段 …from 表名 order by 字段 ..[asc|desc]
6.2 说明：asc :升序排序【默认】
desc：降序排序
--查询所有员工信息，按照工资降序排序
select last_name||。，'_'||first_name as 姓名,salary as 工资
from employees order by salary;
--查询所有员工信息，按照工资降序排序
selectlast_name||'_'||first_name as 姓名,
salary as 工资,department_id as 编号
from employees order by department_id desc;
注意：在oracle中null值最大。
7.条件查询【重点】
7.1）语法：select 字段… from 表名 where 条件 order by …
1)等值查询： = /!=(<>)
-- 查询工资是24000的员工信息
select* from employees where salary = 24000
--查询工资不是24000的员工信息
select* from employees where salary ！= 24000
一、伪列
1.概念：伪列是通过select * 直接查询不到的，必须通过手动指定才能看见。
2.常见的伪列
2.1）rowid:
rowid是oracle系统对每一条数据的物理地址计算后得到的一个18位字符
当已知一条数据的rowid情况下，可以作为查询条件，查询效率高。
select * from employees where rowid = 'AAAC9EAAEAAAABXABK';
2.2）rownum[重点]:
概念：oracle数据库系统，会对出现在查询结果中的数据进行一个排序，这一列就是rownum，永远从1开始，每次递增1。
-- 查询员工编号、姓名、薪资、rownum
select employee_id as 编号, last_name||'_'||first_name as 姓名,salary as 薪资,rownum from employees ;
--查询前五名员工信息
select employee_id as 编号,last_name||'_'||first_name as 姓名,salary as 薪资,rownum from employees where rownum <=5;
1）注意：当rownum 与 * 一起出现的时候，表要起别名。
select emp.*,rownum from employees emp where rownum <=5;
//oracle数据库系统会认为 * 指的就是所有字段，到后面去解析from关键字，此时*后面不是 from关键字，所以会报错。
解决方式：给目标查询的表起别名，在 *前面用 别名.* 的方式。//注意：表起别名不能使用as关键字。
2）rownum与order by 一起使用的时候
--案例：
--查询工资最高的前五个人的信息：
select emp.*,rownum from employees emp where rownum <=5 order by salary desc//语法没问题！执行结果错误！【子查询】
3）rownum在where子句中的使用，只能做 < <= >=1(0) =1运算，不能做>1运算
--查询第6-10员工信息
select emp.*,rownum from employees emp where rownum>=6 and rownum <=10//错误，语法没问题，结果不对！【子查询】
二、子查询（嵌套查询）
--查询工资最高得到员工信息
分析：1)分析出最高工资—》作为条件—》查询出最高员工信息。
2.1）子查询结果为一行一列：用于where中做条件判断。
select * from employees where salary =(select max(salary) from employees)
2.2）子查询结果为多行一列：用于作where条件：枚举查询
-- 查询与last_name 为’King’的人在同一个部门的员工信息。
--分析：1)查询 ‘King’ 在哪一个部门。
2）查询出和 'King'在同一部门的
select * from employees where department_id in (select department_id from employees where last_name = 'King');
2.2)子查询结果为多行多列：
--查询工资最高的前五人
分析：1)按照工资进行排序（desc）
2) 取出前五个人信息
--1)select * from employees order by salary desc;--tab1
--2)select * from tab1 where rownum <=5
--3)汇总：select * from (select * from employees order by salary desc) where rownum <=5
子查询提高：--数据库分页查询【重点】
1.分页查询的简述：实质上就是将数据库中表的信息，进行分段展示。
--查询第6-10员工信息 //欺骗数据库，数据库就看不见rownum关键字，也就没有了rownum的限制—（rownum）起别名。
1）rownum 起别名
2）查询第6-10人员信息
--1)select emp.*,rownum rn from employees emp ;
--2）select * from (select emp.*,rownum rn from employees emp) where rn>=6 and rn<=10
注意：子查询效率并不高，很少使用。
三、表连接查询--【重点】
1.概述：未来需求中的数据，可能来源于1-n张表中，当一张表中信息无法满足需求时，需要使用连接查询，其实就是将多张表的数据合并在一起展示出来。
语法：
select 字段 …from tab1 t1 连接关键字 tab2 t2 on 连接条件 where…group by..having…order by…
2.分类：
2.1)内连接
关键字：[inner] join
--查询员工姓名，工资，部门编号，部门名称、
select
emp.last_name||'_'||emp.first_name as 姓名,
emp.salary as 工资,
emp.department_id as 部门编号emp,
dept.department_id as 部门编号dept,
dept.department_name as 部门名称
from employees emp inner join departments dept on emp.department_id = dept.department_id
特点：不满足连接条件的数据，不会出现在查询结果中。
表连接的思路：
1）明确被连接的表
2）确定连接条件：观察连接表之间 哪两个字段有关系
3）确定查询那些字段
2.2）外连接
1）左外连接【重点】
关键字：left [outer] join
--查询员工姓名，工资，部门编号，部门名称
select
emp.last_name||'_'||emp.first_name as 姓名,
emp.salary as 工资,
emp.department_id as 部门编号emp,
dept.department_id as 部门编号dept,
dept.department_name as 部门名称
from employees emp left join departments dept on emp.department_id =dept.department_id
特点：左表成为主表，右表成为辅表，主表的数据全部出现在查询结果中，辅表中的数据没有对应关系的自动以null补齐。
2）右外连接
关键字：right [outer] join
特点：右表成为主表，左表成为辅表，主表的数据全部出现在查询结果中，辅表中的数据没有对应关系的自动以null补齐。
3）全外连接
关键字：full[outer] join
特点：两张表中的数据全部出现在查询结果当中。
2.3）自连接
1）自连接查询，指的是将一张表想象成两张表，语法与外连接的语法一致。
--查询员工信息以及其领导的信息。
select
e1.first_name||'_'||e1.last_name as 员工信息,
e2.first_name||'_'||e2.last_name as 领导信息
from employees e1 left join employees e2 on e1.manager_id = e2.employee_id
2.4）多表连接
1）连接的语法与外连接的语法一致
--查询员工编号、员工姓名、工资、部门编号、部门名称、部门所在城市。
select
emp.employee_id as 员工编号,
emp.last_name||'_'||emp.first_name as 员工姓名,
emp.salary as 工资,
emp.department_id as 部门编号,
dept.department_name as 部门名称,
dept.location_id as 地区编号,
lo.city as 部门所在城市
from employees emp left join departments dept
on emp.department_id = dept.department_id left join locations lo on dept.location_id = lo.location_id
2.5）笛卡尔连接【了解】
关键字：cross join
select
e.*,dept.* from employees e cross join departments dept。
一、建表【重点】
1.基本语法:
create table 表名(
字段1 数据类型 约束，
字段2 数据类型 约束
//最后一个字段后没有 “，”
);
2.标识符命名规范：针对于表名和字段名的规范。（望文知义）
2.1、由字母、数字、下划线、$、#组成，数字不能开头。
2.2、不能与关键字重名
2.3、最长不能超过30个字符
3.数据类型
3.1 数值类型
1)number：最长允许定义的长度为38为整数
字段 number（10）//
number(6,2)—值的总长度为6，其中小淑数位2，整数位4位，
3.2 字符串类型
1）varchar2(n)：n是一个整数，指定存储最大的字节数，n最大为4000，意为存储最大长度为4000Byte;
2)char(n):n是一个整数，指定存储最大的字节数，最大可以存储2000byte.
char与varchar2区别：
varchar2是可变长的字符串
char是定长字符串
3.3 日期类型
1）date [重点]
2）timestamp
3.4 大数据类型
1）clob:字符型大对象，最大存储是4G适用于存储文本类型。
2）blob:二进制型大对象,最多存储4G，适用于存储音频、图片、视频等。
3）long:字符型大对象、最大存储2G，适用于存储文本文档。
4.约束：约束指的是，按照您的意愿指定字段存储的长度、格式。
4.1）主键约束：
关键字：primary key
作用：指定了约束primary key的字段，每一张表只能有一个主键，主键的值不能为null，并且必须唯一。
4.2）非空约束
关键字: not null
作用：字段值不能为null.
4.3）唯一约束：
关键字：unique
作用：字段有值则必须唯一；可以为null,可以重复
4.4）检查约束
关键字：check(约束条件)
案例：mobile varchar2(20) check(length(mobile)=11)
email varcahr2(30) check(email like ‘%@%.%’)
sex varchar2(10) check(sex in(‘男’,’女’))
4.5）外键约束（foreign key）
外键的概念：代表本表记录与其他表之间的关系
外键的特点：外键的值可以为null,有值的时候，值必须来源于其他表的主键或者 唯一键
外键声明：员工表中
department_id number(10) references departments(department_id)
做个小练习
--1.查询员工表所有数据 select * from employees; --2.打印公司里所有的manager_id select manager_id from employees ; --3.查询80号部门的所有员工 select First_name,Last_name from employees where department_id= 80; select * from employees where department_id = 80 --4.查询50号部门每人增长1000元工资之后的人员姓名及工资. select first_name,last_name,salary+1000 from employees where department_id = 50 --5.查询80号部门工资大于7000的员工的全名与工资. select first_name||''||last_name ,salary from employees where department_id = 80 and salary>7000; select first_name||last_name ,salary from employees where department_id = 80 and salary>7000; --6.查询80号部门工资大于8000并且佣金高于0.3的员工姓名,工资以及提成 select first_name||last_name,salary,commission_pct*salary from employees where department_id = 80 and salary>8000 and commission_pct>0.3; select * from employees; --7.查询职位(job_id)为'AD_PRES'的员工的工资 select salary from employees where job_id = 'AD_PRES'; --8.查询佣金(commission_pct)为0或为NULL的员工信息 select * from employees where commission_pct = 0 or commission_pct is null; --9.查询入职日期在1997-5-1到1997-12-31之间的所有员工信息 select * from employees where to_char(hire_date,'yyyy-mm-dd') between '1997-05-01' and '1997-12-31'; select * from employees where to_char(hire_date,'yyyymmdd') between 19970501 and 19971231; select * from employees where hire_date between to_date('1997-5-1','yyyy-mm-dd') and to_date('1997-12-31','yyyy-mm-dd'); --10.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息 select * from employees where first_name||last_name like '%SM%' or first_name||last_name not like '%L%'; --11.查询电话号码以5开头的所有员工信息. select * from employees where phone_number like '5%'; --12.查询80号部门中last_name以n结尾的所有员工信息 select * from employees where last_name like '%n'; --13.查询所有last_name 由四个以上字母组成的员工信息 单行函数练习 --1.1997年入职的员工(考察知识点:单行函数) select first_name||'-'||last_name,hire_date from employees where to_char(hire_date,'yyyy') = '1997'; 2.查询2002年下半年入职的员工(考察知识点:单行函数) select first_name||'-'|| last_name,hire_date from employees where to_char(hire_date,'yyyy')='1997' and to_char(hire_date,'mm')>=6; select * from employees where to_char(hire_date,'yyyy')=1997 select * from employees where to_char (hire_date,'yyyy')='1997' select * from employees where to_char(hire_date,'yyyy') like 2002 select * from employees where to_char(hire_date,'yyyy') in 2002 3.打印自己出生了多少天 select sysdate-to_date('1997-12-17','yyyy-mm-dd') from dual; --4.打印入职时间超过10年的员工信息 select count(*) from employees where sysdate-hire_date>=6750; select count(1) from employees where to_char(sysdate,'yyyy')-to_char(hire_date,'yyyy')>=10 select count(*) from employees where months_between(sysdate,hire_date)/12>10; 组函数练习 --1.求1997年各个月入职的的员工个数(考察知识点:组函数) select count(1) from employees where to_char(hire_date,'yyyy') = '1997' group by to_char(hire_date,'mm'); select count(1),rownum from employees where to_char(hire_date,'yyyy') = '1997' group by to_char(hire_date,'mm'); --2.查询各部门的总工资 select department_id ,sum(salary) from employees group by department_id; --3.查询50号部门,60号部门,70号部门的平均工资 select avg(salary) from employees where department_id in(50,60,70) group by department_id order by department_id; select department_id ,salary from employees where department_id =50 or department_id = 70 --4.查询各部门的最高工资,最低工资. select department_id, max(salary),min(salary) from employees group by department_id; --5.查询各部门中各个岗位的平均工资. select job_id,avg(salary) from employees group by department_id,job_id; --6.查询平均工资高于8000元的部门的最高工资. select department_id ,max(salary) from employees group by department_id having avg(salary)>8000 ; --查询工资最高的前5名 select salary,rownum from employees where rownum<=5 order by salary desc --查询5-10 员工的工资 select * from (select emp.*, rownum rm from employees emp order by rm ) where rm<=10 and rm>=6
习题： 分页查询练习 1.查询工资最高的3名员工信息
2.查询工资排名第5到第10的员工信息
首先将想到 使用rownum,但是Oracle规定rowNum只能从1开始，所以要给rownum起别名，这样我表面上就不是rownum了
3.查询first_name是以大写D开头的第3到第5个员工信息
子查询练习 1.显示工资比’Allan’(first_name)高的所有员工的姓名和工资
2.显示与’Allan’(first_name)从事相同工作的员工的详细信息
3.显示与30号部门first_name为’Guy’员工工资相同的员工姓名和工资
4.查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SA_REP')(job_id)
5.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
表连接查询练习 1.显示所有职员的姓名及其所在部门的名称和工资
2.查询在研发部('IT')工作员工的编号，姓名，工作部门，工作所在地
3.查询各个部门的名称和员工人数
4.查询工资相同的员工的工资和姓名
5.显示10号部门的经理和20号部门的所有职员的详细信息
6.查询员工的基本信息，附加其上级的姓名
7.求入职日期相同（年月日相同）的员工(考察知识点:自连接)
8.显示各个部门经理的基本工资
9.** 查询平均工资最高的部门信息(考察知识点:子查询,组函数,连接查询)
综合查询练习
水果销售，有表如下： Id Name Price 1 Apple 20 2 Oranage 10 3 Banana 12 4 Tinga 23 产品表 (product) 用户表（users） Id Name 1 lisi 2 Wangwu 3 Zhaoliu 用户购买产品表 (user_product) Userid ProductId Total 1 1 2 1 2 3 2 3 1 2 2 2 1.查询每个用户购买的水果总价 2.所有卖出的水果总价 3.没有购买3号水果的用户 4.卖出水果最少的前两位 一、学分统计 课程表(course) Id Name Base 1 corejava 1 2 Web 1.2 3 Ssh 1.3 Base :为学分基 学生表(students) Id Name 1 Zhangsan 2 Lisi 3 Wangwu 学生成绩表(score) Sid cid score 1 1 80 1 2 78 2 1 86 1.学生每门课程的学分 = score * 学分基(base) 1.查询每个学生的总学分 2.查询所有已选课程的总学分 3.查询没有选学3号课程的学生
答案：
分页查询练习 1. 查询工资最高的3名员工信息 select t1.* from (select * from employees order by salary desc) t1 where rownum<=3 2. 查询工资排名第5到第10的员工信息 select t2.* from (select t1.*,rownum rn from (select * from employees order by salary desc)t1 where rownum<=10)t2 where rn between 5 and 10 3. 查询first_name是以大写D开头的第3到第5个员工信息 select t2.* from (select t1.*,rownum rn from (select * from employees where first_name like 'D%')t1 where rownum<=5)t2 where rn between 3 and 5 子查询练习 1. 显示工资比’Allan’(first_name)高的所有员工的姓名和工资 select first_name,salary from employees where salary> (select salary from employees where first_name='Allan') 2. 显示与’Allan’(first_name)从事相同工作的员工的详细信息 select * from employees where job_id= (select job_id from employees where first_name='Allan') 3. 查询工资大于本部门平均工资的员工基本信息 (1)select e1.employee_id,e1.last_name,e1.salary from employees e1 where e1.salary> (select avg(salary) from employees e2 where e2.department_id=e1.department_id) (2)select e.employee_id,e.last_name,e.salary from employees e join(select department_id,avg(salary) avgSalary from employees group by department_id )t1 on e.department_id=t1.department_id and e.salary>t1.avgSalary 4. 显示与30号部门first_name为’Guy’员工工资相同的员工姓名和工资 select first_name,salary from employees where salary= (select salary from employees where department_id=30 and first_name='Guy') 5. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SA_REP')(job_id) select * from employees where job_id='SA_REP' and salary>(select avg(salary) from employees) 6. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位 select job_id,count(*) from employees where salary>(select avg(salary) from employees) group by job_id 表连接查询练习 1. 显示所有职员的姓名及其所在部门的名称和工资 select e1.first_name,d1.department_name,e1.salary from employees e1 left join departments d1 on e1.department_id=d1.department_id 2. 查询在研发部('IT')工作员工的编号，姓名，工作部门，工作所在地 select e.department_id,e.first_name,d.department_id,l.city from employees e join departments d on e.department_id=d.department_id join locations l on d.location_id=l.location_id where d.department_name= 'IT' 3. 查询各个部门的名称和员工人数 select d.department_name,count(*) from employees e join departments d on e.department_id=d.department_id group by d.department_name 4. 查询工资相同的员工的工资和姓名 (第1人employ_id salary 第2人 employee_id salary) select e1.employee_id,e1.salary,e2.employee_id,e2.salary from employees e1 join employees e2 on e1.salary=e2.salary and e1.employee_id<e2.employee_id 5. 显示10号部门的经理和20号部门的所有职员的详细信息 select e1.* from employees e1 where e1.job_id like '%MAN' and e1.department_id=10 union select e2.* from employees e2 where e2.department_id=20 6. 查询员工的基本信息，附加其上级的姓名 select e1.*,e2.first_name from employees e1 left join employees e2 on e1.manager_id=e2.employee_id 7. 求入职日期相同（年月日相同）的员工(考察知识点:自连接) select e1.first_name,e2.first_name from employees e1 join employees e2 on e1.hire_date=e2.hire_date and e1.employee_id<e2.employee_id 8. 显示各个部门经理的基本工资 select distinct e1.salary ,e1.employee_id from employees e1 join employees e2 on e1.employee_id=e2.manager_id 9. ** 查询平均工资最高的部门信息(考察知识点:子查询,组函数,连接查询) select d.* from departments d join (select t.department_id from (select department_id,avg(salary) from employees e group by department_id order by 2 desc) t where rownum=1)t1 on d.department_id=t1.department_id 一、 水果销售： 1. 查询每个用户购买的水果总价 select name,sum(totalprice) from (select u.name name,up.total*p.price TotalPrice from user1 u left join user_product up on u.id=up.userid left join product p on up.productid=p.id) group by name 2. 所有卖出的水果总价 select name,sum(total) from (select p.name,up.total*p.price total from user_product up join product p on up.productid=p.id) group by name 3. 没有购买3号水果的用户 select * from user1 where id not in (select userid from user_product up where productid in 3) 4. 卖出水果最少的前两位 select name,sum(totalprice) price from (select u.name name,up.total*p.price TotalPrice from user1 u left join user_product up on u.id=up.userid left join product p on up.productid=p.id) group by name order by nvl(price,0) 二、 学分统计 1. 查询每个学生的总学分 select name,sum(grades) from (select st.name,sc.score*c.base grades from students st left join score sc on st.id=sc.sid left join course c on c.id=sc.cid) group by name 2. 查询所有已选课程的总学分 select c.name,t.s*c.base from course c join (select cid ,sum(score) s from score sc group by cid)t on c.id=t.cid 3. 查询没有选学3号课程的学生 select * from students where id not in (select sid from score where cid=3)
