-- 表连接
-- 92语法 99 语法
-- 内连接 外连接
-- 92语法:表连接
-- 笛卡尔积
select * from emp,dept;
select * from emp;
select * from dept;
--等值连接
select * from emp,dept
where emp.deptno = dept.deptno
--员工名称及部门名称
/*
查询列 ename,dname
查询表 emp,dept
过滤条件: deptno
*/
select ename,dname
from emp,dept
where emp.deptno = dept.deptno;
select ename,dname
from emp e,dept d
where e.deptno = d.deptno;
--找出30部门的员工名称及部门名称
/*
查询列 ename,dname
查询表 emp,dept
关联条件 deptno and deptno=30
*/
select ename,dname
from emp e,dept d
where e.deptno=d.deptno and e.deptno=30
-- 查询员工是30部门的员工信息
select emp.*
from emp
where emp.deptno = 30;
select d.dname,e.ename
from dept d,(select emp.*
from emp
where emp.deptno = 30) e
where d.deptno = e.deptno;
-- 非等值连接
--查询员工姓名,工资及等级
/*
查询列 ename,sal,grade
查询表 emp,salgrade
关联条件 emp.sal between salgrade.losal and salgrade.hisal;
*/
select * from salgrade;
select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
-- 自连接
-- 找出 员工姓名 及上级名称
select * from emp;
/*
查询列 ename 员工表,ename 领导表
查询表 emp e1, emp e2
关联条件 e1.mgr = e2.empno
*/
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;
-- 以上所有都是内连接 内连接数无法查询到空值列的
----找出 所有的员工姓名 及上级名称
-- 外连接 是存在主从表关系
-- +号对面是主表
-- 将主表中的所有行记录全部查出,如果从表中没有对应的数据 通过null值填充
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno(+);
-- 99语法
-- 笛卡尔积
select *
from emp
cross join dept;
-- 等值连接
--员工名称及部门名称
-- 多张表存在主外键关联 此时使用using 或者是多张表中存在同名列
select *
from emp
join dept using(deptno);
-- join on
select *
from emp
join dept on emp.deptno=dept.deptno;
-- 非等值连接
--查询员工姓名,工资及等级
select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
-- 查询员工名称和上机领导名称
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;
select e1.ename,e2.ename
from emp e1
inner join emp e2
on e1.mgr = e2.empno;
-- 外联
-- left join 左侧是主表
-- right join 右侧是主表
select e1.ename,e2.ename
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;
select e1.ename,e2.ename
from emp e1
right join emp e2
on e1.mgr = e2.empno;
-- 全连接 oracle的方言
select e1.ename,e2.ename
from emp e1
full join emp e2
on e1.mgr = e2.empno;
----部门编号为30 的员工名称 工资等级
/*
查询列 ename,grade
查询表 emp,salgrade
关联条件 emp.sal between salgrade.losal and salgrade.hisal and emp.deptno=30
*/
select ename,grade,deptno
from emp
join salgrade
on emp.sal between salgrade.losal and salgrade.hisal and emp.deptno=30;
--所有部门的 部门名称,员工数
/*
查询列 dname,count(*)
查询表 dept, select deptno,count(*) from emp group by deptno;
关联条件 deptno
*/
-- 按照部门编号分组 查询每组员工的个数
select deptno,count(*) from emp group by deptno;
select d.dname,e.c
from dept d
join (select deptno,count(*) c from emp group by deptno) e
using(deptno);
----部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
/*
查询列 ename,dname,grade,ename
查询表 emp e1,dept d,salgrade s,emp e2
关联条件
*/
select e1.ename,d.dname,s.grade,e2.ename
from emp e1
join dept d
on e1.deptno=d.deptno
join salgrade s
on e1.sal between s.losal and s.hisal
join emp e2
on e1.mgr = e2.empno;
/*
1: 索引是用来提高查询效率的
2:如何给表添加索引:
sql create index 索引名 on 表名(列名 )
通过图形化界面
3:给列添加索引的前提:
a:列一定是频繁被查询的
b:最好选择唯一性较好的列进行索引添加
c:主键列会自动添加索引
*/
组函数
-- 函数
-- 日期函数:
-- 获取当前日期sysdate
select sysdate from dual;
-- 获取当前日期
select current_date from dual;
--修改日期 增加日期
select add_months(sysdate,2) from dual;
select sysdate+2 from dual;
--查询每个员工的转正日期
select ename,add_months(hiredate,3) from emp;
-- 获取两个月份之间的差值 获取入职员工的距今多少年
select ename,hiredate,months_between(sysdate,hiredate)/12 from emp;
-- 获取当前日期的最后一天
select last_day(sysdate) from dual;
-- 获取下个星期一的时间
select next_day(sysdate,'星期四') from dual;
--to_date(c,m) 字符串以指定格式转换为日期
--to_char(d,m) 日期以指定格式转换为字符串
select to_date('1999/12/12','yyyy/mm/dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual;
----查询 82的员工信息
select *
from emp
where hiredate between to_date('1982/01/01','yyyy/mm/dd') and to_date('1982/12/31','yyyy/mm/dd');
-- decode 10 员工涨薪10 20员工涨薪20 30员工涨薪30
select ename,sal,deptno,
decode(
deptno,
10,
sal*1.1,
20,
sal*1.2,
30,
sal*1.3
) as "涨薪之后的工资"
from emp;
select ename,sal,deptno,
(
case deptno
when 10 then sal*1.1
when 20 then sal*1.2
when 30 then sal*1.3
end
) as "涨薪之后的工资"
from emp;
-- 组函数:不能查询其它列
-- 查询员工工资最大值
select max(sal) from emp;
select ename,sal
from emp
where sal = (select max(sal) from emp);
-- 查询最小工资
select min(sal) from emp;
-- 查询平均工资
select avg(sal) from emp;
-- 工资总和
select sum(sal) from emp;
-- count计数
select count(empno) from emp;
-- count 自动忽略null
select count(comm) from emp;
-- 组函数和分组一起使用
-- group by
-- 分组之后只能查询 分组列 分组函数
-- 按照员工的部门编号求平均工资
select deptno,avg(sal)
from emp
group by deptno;
-- 查询员工的工资高于当前部门平均薪资的员工信息
-- where 后不能跟分组函数
/*
查询列 select deptno, avg(sal)
查询表 from emp
过滤条件 where sal > avg(sal)
分组条件 group by deptno
排序列 order by
排序规则
*/
select deptno
from emp
where sal>avg(sal)
group by deptno;
----按 部门 查询 平均工资,且平均工资大于2000的部门编号
/*
查询列:deptno
查询表:emp
分组:deptno
组过滤:avg(sal) > 2000;
*/
select deptno
from emp
group by deptno
having avg(sal) > 2000;
----查询 最低平均工资的部门编号
-- 每个部门的平均工资
-- 求最小值
/*
查询列:deptno
查询表:emp
分组:deptno
*/
select deptno
from emp
group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);
--查看 高于本部门平均薪水员工姓名
--- 查询部门的平均工资
select deptno,avg(sal) from emp group by deptno;
-- 查询每个员工的工资
select ename,sal,deptno from emp;
-- 查询每个员工的工资 高于 他自己部门的平均工资
select e1.ename,e1.sal,e1.deptno
from emp e1
where sal > (select avg(sal) from emp e2 where e1.deptno= e2.deptno group by deptno) ;
/*
查询列:ename
查询表:emp
分组:deptno
*/
-- rowid 它是一个伪列 oracle在添加数据时,会给当前提交的行记录 生产一个rowid的值,然后
-- rowid还是一个物理地址 所以可以通过rowid去唯一区分行记录 以及快速定位当前行
select emp.*,rowid from emp;
-- rownum 是oracle中分页的基础
select rownum ,emp.* from emp;
-- rownum是和排序
select rownum ,emp.* from emp order by sal desc;
-- 查询工资最高的前5个员工信息
select e.*,rownum
from (select emp.*
from emp
order by sal desc) e
where rownum<=5;
-- 查询工资最高的3-5个员工信息
select e.*,rownum
from (select emp.*
from emp
order by sal desc) e
where rownum<=5;
select e2.*,e2.rn
from
(select e1.*,rownum rn
from (select emp.*
from emp
order by sal desc) e1
where rownum<=5) e2
where e2.rn>=3;
/*
实现分页:
select 查询列
from
(select e1.*,rownum rn
from (select 列
from 表
order by 排序列 排序规则) e1
where rownum<=10) e2
where e2.rn>=5;
具体分页的实现机制:
首页 上一页 第一页 。。。。。。 第N也 下一页 尾页
if(current_page==1){
第一页不让点
隐藏上一页
}
查询 current_page的记录数 10
select 查询列
from
(select e1.*,rownum rn
from (select 列
from 表
order by 排序列 排序规则) e1
where rownum<=current_page*page_size ) e2
where e2.rn>=(current_page-1)*page_size+1;
1: 总记录数 totle_size count(*)
2: 每页显示的记录数 page_size 10/5
3: 当前页码数 current_page 1 2 3 4
mysql的分页:
select * from 表 where limit 数值1,数值2;
数值1:第几条记录
数值2:查询多少条记录
select * from (select * from emp order by 排序列 排序规则) where limit 1,10;
select * from 表 where limit 11,10;
第current_page
select * from 表 where limit (current_page-1)*page_size+1,page_size;
*/
约束
-- 创建表
create table t_studnt(
sid number(5),
sname varchar(12),
sage number(3)
);
-- dml语句:
-- 添加、删除、修改
--添加行
-- insert into 表名 values (值1,值2...);
insert into t_studnt values (1,'zhangsan',12);
select * from t_studnt;
insert into t_studnt values (1,'zhangsan',12);
-- 创建表给当前表中的数据增加一些约束用来 规范数据的格式
drop table t_studnt;
create table t_studnt(
sid number(5) ,--可以添加约束
sname varchar(12),
sage number(3)
-- 指定约束
);
-- 修改表中的约束
-- 主键约束、非空约束、唯一约束、检查约束、外键约束
create table t_studnt(
sid number(5) primary key,
sname varchar(12),
sage number(3)
);
insert into t_studnt values (1,'zhangsan',12);
-- 如果添加的所有的数据项都存在 按照 创建表时的列的创建顺序依次添加
-- 如果添加的数据的列和常见表中列的个数顺序类型不同 直接给未指定列的项添加null值
-- insert into 表名 (列名1,列名2,....) values (值1,值2...);
-- 主键约束:非null 唯一
insert into t_studnt (sname,sage) values ('zhangsan',12);
-- not null 不能为null
-- unique 唯一约束
-- 添加默认值
-- 约束名称: 主键:pk fk uk ck nk
create table t_studnt(
sid number(5) primary key,
sname varchar(12) unique,
sage number(3) default 18 check(length(sage) between 1 and 2)
);
insert into t_studnt (sid,sname) values (1,'zhangsan');
select * from t_studnt;
insert into t_studnt (sid,sage,sname) values (2,12,'zhangsan');
-- 删除数据 delete 不会发生
-- delete from 表名;
-- delete from emp; 删除表数据 保留表结构
--delete from dept casecode;
-- delete from 表名 where xxx;
-- delete 删除表的时候 如果删除的表时主表 那么 不能删除
select * from emp;
select * from t_studnt;
create table t_studnt(
sid number(5) primary key,
sname varchar(12),
sage number(3),
isDelete number(2) default 1
);
insert into t_studnt (sid,sname,sage) values(1,'123',12);
insert into t_studnt (sid,sname,sage) values(2,'234',23);
select * from t_studnt;
-- 修改操作
-- update 表名 set 列名=列值 ; 该全表
-- update 表名 set 列名=列值 where 条件
update t_studnt set sage=22 where sid = 1;
update t_studnt set isDelete=0 where sid = 1;
select * from t_studnt where isDelete=1;
-- 凡是写操作都会产生事务 增加、删除、修改
-- oracle中默认的事务隔离级别是 读未提交 脏读
select * from t_studnt ;
insert into t_studnt (sid,sname,sage) values(3,'1234',42);
-- oracle 中默认是事务不提交 不会自动提交 mysql中事务是自动提交
-- 如何提交事务:
commit;
insert into t_studnt (sid,sname,sage) values(4,'124',52);
rollback;
-- delete from emp;
-- 某些sql语句 会自动将之前事务提交 dcl