【oracle资料】oracle sql语句 基础 学习

/*
   SQL   Structed Query Language
   SQL95
  1.DDL 数据库定义语句 create alter drop
  2.DML 数据库操作语句 insert delete update select
  3.DCL 数据库控制语句 grant revoke
  4.数据库事物语句 commit rollback savepoint
 
  睁眼-起床-穿衣-吃饭-刷牙-洗脸-出门
 
*/
--create 字符型数据 2000  4000
create table person(
  pname varchar2(20),
  psex varchar2(2),
  page number(3),
  birthday date,
  sal number(9,2)
)
--drop
drop table person
--alter
alter table person add hunfou varchar2(2)
alter table person drop column hunfou
alter table person modify hunfou varchar2(4)
--主键列primay key PK 值不能重复 不能为空null
alter table person add shenfenzheng varchar2(18)
alter table person add constraints PK_PERSON primary key (shenfenzheng)
--DML 数据库操作语句 insert delete update select
--insert now();
insert into student (sno,sname) values (5,'张三');
begin
insert into person (pname,psex,page,birthday,sal,hunfou,shenfenzheng)
values ('张三','男',100,sysdate,100.12,'否','230108198304021418');
insert into person(shenfenzheng,pname)
values ('823838383','小绿');
insert into person  values ('张三','男',100,sysdate,100.12,'否','23010819830402141X');
commit;
end;
--select 查询语句
select psex as "性别",pname as "姓名" from person
--查询emp全部数据
select * from emp
select ename as "姓名",sal*12 as "年薪",sal as "月薪" from emp
--姓名是：SIMTH 月薪是:800 年薪是 9600
select '姓名是:'||ename||' 月薪是:'||sal||' 年薪是:'||sal*12 as "员工信息"
from emp
--条件查询 where
select * from emp where sal != 1000
--年薪在1万以上
select * from emp where sal * 12 > 10000
--组合条件 and
select * from emp
where sal > 1000 and sal < 3000 and job = 'CLERK'
--or
select * from emp
where sal < 1000 or sal > 3000 or job = 'CLERK'
--and 和 or
select * from emp
where sal > 1000 and sal < 3000 or ename = 'SMITH' and sal = '800'
select * from emp
where comm is not null
--like 模糊查询
select * from emp
where ename not like '%_S_%'
--in not in
select * from emp
where ename not in ('SMITH','ALLEN','WARD');
--distinct 无重复

select distinct job from emp

================================

/*
   SQL
   1.DDL  create drop alter
   2.DML  insert select update delete
   3.DCL  grant revoke
   4.数据库事物控制 commit rollback savepoint
*/
--insert  sysdate
insert into emp values ()
--select
select sal as "工  资" from emp '' ""

create table person
--where 条件查询 and or like % _ in not distinct (between and)
select * from emp where sal > 1000 and sal < 3000
select * from emp where sal not between 1000 and 3000
--排序 order by
select * from emp order by sal desc,ename desc
--排序和条件同时出现
select * from emp where sal < 3000 order by sal
--distinct
select distinct job from emp
select distinct job from emp
--删除delete，修改 update
delete from emp where sal > 3000

select * from emp
--把工资在2000以上的经理都删除
delete from emp where sal > 2000 and empno not in
(select distinct mgr from emp where mgr is not null)
--哪个部门没人啊？ 关系型数据库 对象型数据库
select * from dept where deptno not in (select distinct deptno from emp)
--修改
update emp set sal = sal + 300,job='MANAGER' where sal < 1000
update emp set sal = sal + 300,comm = comm+300 where ename='ALLEN'
select * from emp

--进阶 函数： 字符函数 转换函数 日期函数 分组函数
/*
concat || 连接
substr 截取
instr
lpad
rpad
length
lower
upper
*/
--concat
select concat(concat(concat(concat(concat('姓名是:',ename),'月薪是:'),sal),'年薪是:') ,sal * 12)as "员   工   信   息" from emp
--substr
select substr(ename,2,3) from emp
--lower upper
select * from emp where lower(ename) = 'smith'
select upper(ename) from emp
--查询名字中只有首字母是大写的人
select * from emp where upper(substr(ename,1,1))=substr(ename,1,1)
and lower(substr(ename,2,length(ename)-1))=substr(ename,2,length(ename)-1)
--instr
select ename,instr(ename,'S') from emp
--查找到名字中不带R的人
select * from emp where instr(ename,'D',2)=0

select instr(substr(ename,instr(ename,'R')+1,length(ename)),'R') from emp

select rpad(lpad(ename,10,'*'),20,'*') from emp
--不管名字有多长 都是左边4个星，右边4个星
/*
转换函数
to_char
to_date
to_number
nvl
*/
insert into emp (empno,hiredate) values (8888,to_date('1990-1-1 15:22:33','yyyy-mm-dd hh24:mi:ss'))
--to_char
select to_char(hiredate,'mm') from emp
--查询5月份雇佣的人
select * from emp where to_char(hiredate,'mm')=5;
--被雇佣的人从所在雇佣月到年底实发工资
select sal * (12-to_char(hiredate,'mm'))   from emp
--查找工龄在28年以上的员工
select * from emp where to_char(sysdate,'yyyy') - to_char (hiredate,'yyyy') > 30
--曾在本月被雇佣的人
--nvl
select sal+nvl(comm,0) from emp
--日期函数
/*
months_between 代表两个指定日期之间的月分差
next_day       
last_day   每个月的最后一天的日期
add_months   
trunc
round
*/
--查看员工的工龄
select trunc(months_between(sysdate,hiredate)/12,2) from emp
--next_day
select hiredate,next_day(next_day(hiredate,5),5) from emp
--last_day

select hiredate,last_day(hiredate) from emp
--计算员工从雇佣日期开始到他所在雇佣月底实发工资
select sal * months_between(last_day(hiredate),hiredate) from emp
--add_month
select hiredate,add_months(hiredate,3) from emp
--分组函数
/*
 1.sum
 2.max
 3.min
 4.count
 5.avg
*/
--count(*)统计记录个数
select count(*) from emp
select count(distinct job) from emp
select sum(distinct sal) from emp


--查询最大工资的人是谁
select * from emp where sal = (select max(sal) from emp)

--经理中的最高工资
select * from emp where sal =
(select max(sal) from emp where job = 'MANAGER')
and job = 'MANAGER'

=================================

/*
   SQL
   嵌套查询
   函数 字符函数 转换函数 日期函数 分组函数
   字符函数 concat substr instr lpad rpad length upper lower
   转换函数 to_char to_date yyyy-mm-dd hh24:mi:ss to_number
   日期函数 months_between next_day last_day add_months
   分组函数 sum max min avg count
*/  
select count(*) from emp
select count(distinct job) from emp
select ename,max(sal) from emp
--查询工资最高的人
--分组 group by
--每个部门的人数 非分组条件无法进行显示
select deptno,count(*) from emp
group by deptno
--每个部门的平均工资
select round(avg(sal)) from emp group by deptno
--不同职位的最高工资
select job,max(sal) from emp group by job
--每个部门最高工资的人
select * from emp where sal in
(select max(sal) from emp group by deptno) group by deptno  
--having
--部门平均工资在2000以上的部门
select deptno from emp group by deptno having avg(sal) > 2000
--部门平均工龄在28以上的部门的员工工资涨500
update emp set sal = sal + 500
where deptno in (select deptno from emp group by deptno having avg(months_between(sysdate,hiredate)/12)> 28)
--把10号部门工龄在平均工龄以上的员工涨500
update emp set sal = sal + 500
where empno in (
select empno from emp
where months_between(sysdate,hiredate)/12 >
(select avg(months_between(sysdate,hiredate)/12)
from emp where deptno=10))
and deptno = 10
--查询10号部门中工龄大于平均工龄的人
select empno from emp
where months_between(sysdate,hiredate)/12
> (select avg(months_between(sysdate,hiredate)/12) from emp where deptno=10)
and deptno = 10

select * from (select * from emp where deptno=10)
where months_between(sysdate,hiredate)/12
> (select avg(months_between(sysdate,hiredate)/12) from emp where deptno=10)
/*
连接查询 广义笛卡尔积
等只连接
自连接
外连接
表和表之间的关联关系
1.一对多
2.一对一
3.多对多
> any all
*/
--查询公司中哪些人比经理的工资高
select * from emp where sal < any (select sal from emp where job = 'MANAGER')
and job != 'MANAGER'

/*
   

*/
--外键 完整性约束
create table book(
  bid number(5) primary key,
  bname varchar2(20),
  blid number(5)
)
alter table book add constraints FK_B_BL
foreign key (blid) references booklei(shuleiid)
create table booklei(
  shuleiid number(5) primary key,
  blname varchar2(20)
)

select d.dname,count(*) from emp e,dept d
where e.deptno = d.deptno
group by d.dname

select * from emp e,dept d
where e.deptno = d.deptno
--查询部门名称叫Accounting部门下的员工
select * from emp e,dept d
where e.deptno*2 = d.deptno and d.dname='ACCOUNTING'

select * from emp where deptno = (select deptno from dept where dname = 'ACCOUNTING')
--每个部门最高工资的人
select * from emp where sal in
(select deptno,max(sal) from emp group by deptno)
select e.* from emp e,(select deptno,max(sal) as mxsal from emp group by deptno) x
where e.deptno = x.deptno and e.sal = x.mxsal
--查询每个部门中工龄大于部门平均工龄的人

--国家 地区 城市
--中国的城市
select cs.csname from guojia gj,diqu dq,chengshi cs
where gj.gjid=dq.gjid and dq.dqid = cs.dqid
and gj.gjname='中国'
--自连接
select * from emp e,emp e1
where e.mgr = e1.empno
--外连接 左外 右外
select * from emp e
left join dept d on e.deptno = d.deptno

select d.* from dept d
left join emp e on d.deptno = e.deptno
where e.empno is null
--哪个部门下没人

=================================