昨天作业的回顾 **
显示员工的id,last_name,salary,部门名称 , 要求, 把没有部门编号的员工也给查询到:
前置条件:
公司业务需要, 把员工编号为23 24 25 的员工 分配到了新的部门, 但是部门还未成立! update s_emp set dept_id = null where id in(23,24,25);commit;
语句: select e.id,e.last_name,e.salary,d.name from s_emp e,s_dept d where e.dept_id=d.id(+);
显示每个员工的工资 和对应的工资级别 , 超出工资范围的 也要显示出来 :
工资表: salgrade
前置条件:
公司老板觉得自己的工资太高了, 不合适 , 给自己降了-10000的工资 ! 还让自己的秘书 以身作则 , 一起降了工资 !update s_emp set salary=12500 where id=1;update s_emp set salary=500 where id=22;commit;
如果在外连接时, 使用between 需要在最小范围后面加入(+) , 最大范围后面加入(+)
salary between losal(+) and hisal(+)
语句:
select salary,grade,id,last_name from s_emp e,salgrade g where salary between losal(+) and hisal(+);
显示每个部门的名称 和 对应的地区的名称, 没有地区编号的也要显示出来 :
前置条件:
因为业务需要, 增加了新的部门, 但是还没有确定办公的地点:
insert into s_dept values(99,'chiji',null);commit;select d.name,r.name from s_dept d,s_region r where d.region_id = r.id(+);
group by having 分组子句 **
格式 … group by 分组标准字段 having 分组过滤条件
分组标准字段: 指的是查询的结果, 按照什么字段进行分组 ,例如: s_emp表格按照部门编号分组!
分组过滤条件: 对分组后的数据, 进行过滤
分组查询的限制:
在带有分组的查询语句中, select与from中间编写的查询字段, 只能存在两种: - 分组标准字段 (在一个组中唯一的)- 组函数的结果 (统计一个组中的某数据, 也是只有一个的)上面这两种可以查询的数据类型, 都是为了保证分组后的查询的列数据能一一对应!
分组查询:
1. 按照部门的编号进行分组 , 统计每个部门的人数 !select dept_id,count(*) from s_emp group by dept_id;2. 按照部门的编号进行分组, 统计每个部门的最高薪资,要求,部门人数低于3的不显示!select dept_id,max(salary) from s_emp group by dept_id having count(*)>2; 3. 按照部门编号进行分组, 显示部门的编号, 平均薪资 , 平均薪资高于1400的才展示出来!select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>1400; 4. 统计每个部门中薪资大于1400的人数 , 低于2人的不显示 !select dept_id,count(*) from s_emp where salary>1400 group by dept_id having count(*)>1;5. 按照 部门编号分组 , 显示部门的人数, 部门的编号, 部门的名称select count(*),dept_id,max(name) from s_emp e,s_dept d where e.dept_id = d.id group by dept_id;
分组过程中 容易出现的bug **
按照部门的编号进行分组 , 统计每个部门的人数 !
select id from s_emp group by dept_id;
select id,count(*) from s_emp group by dept_id;
select dept_id,id from s_emp group by dept_id;
上述三行代码都出现了错误:
ORA-00979: 不是 GROUP BY 表达式00979. 00000 - "not a GROUP BY expression"
原因在于, 数据的结果 , 没有一一对应!
select与from中间编写的查询字段, 只能存在两种:
- 分组标准字段 (在一个组中唯一的)- 组函数的结果 (统计一个组中的某数据, 也是只有一个的)
where条件 与 having条件的区别 *****
where 条件 对表格中所有的数据 进行过滤!having 条件 是对于分组后的组中数据进行过滤!where条件执行在having之前
select语句 *****
完整格式:
select 字段 from 表名/视图 [where条件] [group by 分组标准字段 [having 过滤条件]] [order by 排序字段 排序规则]
执行顺序
from--> where条件 -->group by -->having条件-->select -->order by
子查询 ***
概念: 把一个select 语句的查询结果, 当作另一个select 语句的一部分 !
子查询的子语句, 使用小括号括住!
子查询的语句, 可以出现的位置有三个:
1. 应用到from 之后2. 应用到where之后3. 应用到having之后
应用到where条件之后
把一个select的结果, 当前where后的筛选条件 !
案例: 查询id最大的员工信息(id,salary,last_name)
步骤1. 先得到最大的idselect max(id) from s_emp;步骤2. 根据最大的id 进行信息的查询select id,salary,last_name from s_emp where id=(select max(id) from s_emp);
练习:查询部门编号最大的部门员工信息(id,salary,last_name) !
步骤1. 得到最大的部门编号select max(dept_id) from s_emp;步骤2. 根据得到的部门编号进行查询select id,salary,last_name from s_emp where dept_id=(select max(dept_id) from s_emp);
练习: 查询公司所有的领导
1. 先查寻到公司所有的领导id(manager_id) , 并去重
select distinct manager_id from s_emp;
select distinct manager_id from s_emp where manager_id is not null;
2. 根据上面查询到的领导id , 通过in去匹配公司的所有领导select id,last_name,salary from s_emp where id in(select distinct manager_id from s_emp where manager_id is not null);
练习: 查询公司所有的普通员工
1. 先查寻到公司所有的领导id(manager_id) , 并去重
select distinct manager_id from s_emp where manager_id is not null;
2. 根据上面查询到的领导id,通过not in匹配公司所有的普通员工!select id,last_name,salary from s_emp where id not in(select distinct manager_id from s_emp where manager_id is not null);
应用到having之后
把一个select语句的结果, 当作另一个分组查询语句 分组后的组过滤条件!
寻找部门平均薪资 大于 32号部门平均薪资的 部门信息 : 显示部门编号 和 平均薪资!
步骤1. 先找出32号部门的平均薪资select avg(salary) from s_emp where dept_id=32;步骤2. 根据部门进行分组查询, 过滤条件为: 计算后部门的平均薪资大于上面 查询的结果select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=32);
应用到from之后
每一个select语句的结果, 就类似存在与内存中的一个表.
把select语句要查询的表格 , 更改为另一条查询语句, 即可!
案例: 查询员工的id,last_name,salary,条件为薪资大于1400
select id,last_name,salary from s_emp where salary>1400;
根据上次查询的结果, 使用子查询更快的获取员工id为5的员工信息id,last_name,salary,
select * from (select id,last_name,salary from s_emp where salary>1400) where id=5;
DDL (数据定义语句)
创建表 *****
数据类型:
- number:数字类型- varchar2: 变长字符串 ,编写为varchar也可以使用, 会自动转换为varchar2- date: 日期类型
创建表格的格式:
create table 表名(
字段1 数据类型(长度),
字段2 数据类型(长度),
…
字段n 数据类型(长度)
);
注意: 关于number类型长度的编写,如下所示:
id number(2,3): 2表示整数长度为2,最大能存储99 , 3 表示小数点后长度为3位
练习:
创建一个表格person15字段: 1. id 长度为5 , 小数点后允许0位的number类型数据2. name 长度为10的varchar2类型的数据3. age长度为3,小数点后允许0位的number类型数据
create table person15(
id number(5,0),
name varchar2(10),
age number(3,0)
);
练习:
创建一个表格表名为: user15字段:1. id 长度为5的number类型2. uname 长度为32的varchar2类型3. upass 长度为32的varchar2类型create table user15(id number(5),uname varchar2(32),upass varchar2(32));id uname upass1 aaa bbbcreate table userPhoneNumber(userid number(5),phoneNumber number(11));userid phoneNumber1 13843838438
删除一个表格 *****
格式: drop table 表名;
注意: 在删除一个表格时, 如果此表格与外部其他表格不存在任何数据和格式上的关联 , 则会立即被删除
在表格删除时, 同时表格中的数据也会被清空!
练习:
删除你的person表和user15表
语句: drop table person15;
drop table user15;
修改表结构 (了解)
很耗费系统的性能:
修改表结构的流程: 1. 先将表格中的数据备份2. 将表格清空, 修改结构3. 将备份的数据 依次插入
如果表格在创建时就有缺陷怎么办?
两种方案: 1. 更改 2. 扩展表格 进行关联
其实更多的时候 ,我们应尽量避免 表格被重构 !
在设计表格时, 应尽量的让表格中的字段完善 ! 并设置保留字段 ,留待以后添加新的业务 !
修改表结构的两种操作
删除表中的字段
格式: alter table 表名 drop column 列名;
练习:
创建上面案例的person15 和 user15删除person15中的age字段
alter table person15 drop column age;
添加表中的字段
格式: alter table 表名 add 字段名 数据类型(长度);
练习:
向person15表中添加一个age字段 长度为3 nummber类型
alter table person15 add age number(3);
DML 数据操作语言
对于数据库中表格数据的 添加 , 删除 修改
事务: 将一系列的dml语句 看作一个业务流程 , 看作一个整体, 统一进行处理, 要么一起成功, 要么一起失败!
关于DML语句 , 所有的操作, 都需要提交事务 !
在Oracle数据库中 事务的自动提交 是关闭的 !
提交事务 *****
commit;
事务回退 **
rollback;
INSERT语句 插入数据 *****
全字段添加数据
格式: insert into 表名 values(数据列表);
注意: 数据列表 , 表示的是要插入的数据列表 ,传入的顺序, 按照字段创建的顺序来插入 , 多个值之间使用逗号隔开
数据列表中的顺序, 是按照创建时字段的顺序插入的但是对于表格的修改(增加删除字段), 会影响到字段的顺序 !
练习:
向user15表格中注册一组用户: 1. id:10001 , 帐号:dongfei 密码:ytdhytytwjinsert into user15 values(10001,'dongfei','ytdhytytwj');2. id:10002 , 帐号:gaofan 密码:lzlhlzlhinsert into user15 values(10002,'gaofan','lzlhlzlh');3. id:10003 , 帐号:gaosen 密码:hahahahahainsert into user15 values(10003,'gaosen','hahahahaha');4. id:10004 , 帐号:lukuan 密码:wdjzdbshjsainsert into user15 values(10004,'lukuan','wdjzdbshjsa');5. id:10005 , 帐号:yuhang 密码:dgnmadgnlinsert into user15 values(10005,'yuhang','dgnmadgnl');-- 不要忘记提交事务啊commit;
选择字段插入数据
格式: insert into 表名(插入的字段列表) values(数据列表);
数据列表: 它数据顺序, 与前面的字段列表一致 !
注意: 也可以选择全部字段, 来进行数据的添加! 例如如下两段语句 作用相同:
insert into user15 values(10003,‘gaosen’,‘hahahahaha’);
insert into user15(id,uname,upass) values(10003,‘gaosen’,‘hahahahaha’);
练习：
向数据库中的user15表格 插入一组数据
id:10006 , 帐号:dongfei
insert into user15(id,uname) values(10006,‘dongfei’);id:10007 , 帐号:gaofan
insert into user15(id,uname) values(10007,‘gaofan’);id:10008 , 帐号:gaosen
insert into user15(id,uname) values(10008,‘gaosen’);id:10009 , 帐号:lukuan
insert into user15(id,uname) values(10009,‘lukuan’);id:10010 , 帐号:yuhang
insert into user15(id,uname) values(10010,‘yuhang’);
– 不要忘记提交事务啊
commit;
数据的删除*****
格式1: 一次删除一个表格中的所有数据行
格式: delete from 表名;
练习: 删除user15表格中的所有数据
delete from user15;
格式2: 根据where条件, 删除匹配的数据行
格式: delete from 表名 where 条件;
练习: user15表格中id为10001,10002,10003的用户 ,要求使用1行语句完成!
delete from user15 where id<10004;
注意:
where 条件的使用 与 select语句中基本一致!
数据的修改*****
格式: update 表名 set 字段名1=字段值1,字段名2=字段值2 … 字段名n=字段值n [where条件];
如果在修改时, 不添加where条件, 则表示修改表中的所有数据 !
添加where条件后, 表示修改匹配条件的数据行!
案例: 修改user15表格中 id为10001的用户的 密码为 123456
update user15 set upass='123456' where id=10001;commit;
练习: 修改user15表格中的 帐号为gaosen的用户的信息, 修改密码(upass)为234567
update user15 set upass=‘123456’ where uname=‘gaosen’;
commit;
练习: 修改user15表格中的 所有用户的帐号(uname)为******
update user15 set uname=’******’;
commit;
TCL事务控制语句
事务: 将一系列的dml语句 看作一个业务流程 , 看作一个整体, 统一进行处理, 要么一起成功, 要么一起失败!
关于DML语句 , 所有的操作, 都需要提交事务 !
在Oracle数据库中 事务的自动提交 是关闭的 !
在mysql中 事务是自动提交的 ! 在JDBC中事务是自动提交的
事务特性***
原子性: 同一个事务中的多个DML操作, 不可分割 要么一起成功, 要么一起失败!一致性:通一个事务的操作, 结果应保持一致!隔离性:同一个事务的dml操作, 在没有提交之前,对于其他的事务来说, 数据应是被隔离的 ,不可见的!持久性:存储的数据要保持一直存在!
如何开始一个事务
从上一次事务的结束之后, 进行的第一次dml操作, 就自动开启了事务!
如何结束一个事务
结束事务有两种不同的操作:
提交事务commit;
结束事务, 并将事务中所有的dml操作提交, 将数据的更改 添加 删除 应用到数据库中!
事务回退rollback;
也称为事务的回滚 , 表示本次事务中 所有的dml操作 撤回 ! 不对数据库中的数据 产生任何的更改!
关于回退事务, 有两种不同的操作:
一次回退所有dml操作 ***
使用格式: rollback;
回退到某一个保存点 了解
使用格式: rollback to 保存点名称;
添加一个保存在: 在任意的dml语句后, 加入: savepoint 名称; 即可完成保存点的添加!
案例:
对user15表格进行数据的添加: --1. id:10006 , 帐号:dongfei insert into user15(id,uname) values(10006,'dongfei');--2. id:10007 , 帐号:gaofan insert into user15(id,uname) values(10007,'gaofan');commit;--3. id:10008 , 帐号:gaosen insert into user15(id,uname) values(10008,'gaosen');--4. id:10009 , 帐号:lukuan insert into user15(id,uname) values(10009,'lukuan');--5. id:10010 , 帐号:yuhanginsert into user15(id,uname) values(10010,'yuhang'); -- 不要忘记提交事务啊rollback;
添加保存点的案例:
对于person15表格进行数据的插入insert into person15 values(1,'董飞',8);insert into person15 values(2,'董飞',18);savepoint a;insert into person15 values(3,'董飞',28);savepoint b;insert into person15 values(4,'董飞',38);savepoint c;insert into person15 values(5,'董飞',8);savepoint d;rollback b;commit;
