sql学习笔记

1，orcale 登录：

http://127.0.0.1:5560/isqlplus

D:/oracle/ora92/Apache/Apache/ports.ini
在$ORACLE_HOME/Apache/Apache/目录下查看ports.ini文件，该文件记录着端口号

下找端口号
-------
dos命令行下
sqlpus sys/wanye as sysdba

conn scott/tiger

Oracle中如果不知道system,sys用户的密码后可用如下方法找回：  
   
  首先以一个普通用户等入数据库：  
  在SQL*Plus中执行如下命令：  
  SQL>connect/as   sysdba   (也可以以如下方式连接：SQL>connect   internal)  
  SQL>alter   user   sys   identified   by   sys;     (回车后用户sys的密码会更改为sys)  
  同理可以更改system密码  
  SQL>alter   user   system   identified   by   system;   (回车后用户system的密码会更改为system)  
    -------------------------
获取当前连接Oracle的用户信息
    SELECT ss.username,ss.osuser,ss.machine,ss.program,ss.logon_time FROM v$session ss WHERE ss.sid=(SELECT DISTINCT sid FROM v$mystat);

2,sql语句：
select distinct deptno,job from emp;//distinct修释两个，表示是它们的组合不重复

select * from emp where deptno =10;//
where ename='wanye'
where ename>'wanye'字典比较
where sal>1000
where sal<>1000//不等于
where sal between 800 and 1000//where sal >=800 and sal<=1000 or  

select ename from emp where comm is null;//is not null  不能用=null

select ename from emp where sal not in (800,1500,1200);

select ename from emp where

 

select ename,ename from emp where ename ont like '_A%' order by XXX desc  //desc 倒序 asc升序

sql中的函数：

lower upper:select ename from emp where lower(ename) like '__ A%'//lower(),upper()－－－大小写

substr:select substr(eanme,2,3) from emp;//从第2个取3个字符

chr ascii:select chr(65) from dual;//dual 空表 ascii('a')

round:select round(23.123，2) from dual;//四五入

to_char:select to_char(sal,'$99,999.9999') from emp;
 select to_char(sal,'L99,999.9999') from emp;//L本地货币符
 
 select to_char(hirdate,'YYYY-MM-DD HH:MI:SS') from emp;
 select to_char(hirdate,'YYYY-MM-DD HH24:MI:SS') from emp;
to_date:select ename,hiredate from emp where hiredate>to_date('1981-2-20 12:30:20','YYYY-MM-DD HH:MI:SS');

to_number:select sal from emp where sal>to_number('$1,250.00','$9,999.99');

nvl:select  ename,sal*nvl(commt,0) from emp;//nvl如果是空值换成0

组函数:5个
select sum(sal) from emp;
select count(distinct deptno) from emp;
select round(avg(sal),2) from emp;
select max(sal) from emp;
select min(sal) from emp;

group by having:

select deptno,avg(sal) from emp group by deptno;
select deptno,job,max(sal) from emp group by deptno,job;//deptno和job的组合分组
select avg(sal) from emp group by deptno having avg(sal)>2000;

1,select * from emp
2,where sal>1000
3,group by deptno
4,having
5,order by

子查询：
select ename,sal from emp
join(select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);

自连接
SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

表连接
sql1992:
select ename,dname,grad from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal
and job<>'CLERK';
//过虑条件与连接条件在一起
sql1999:
select ename,dname from emp,dept;//1992
select ename,dname from emp cross join dept;//1999 __迪卡尔乘积
age:
select ename,dname from emp,dept where emp.deptno=dept.deptno;//1992
select ename,dname from emp join dept on(emp.deptno=dept.deptno);//1999
select ename,dname from emp join dept using(deptno);//1999
//过虑条件与连接条件分开
select eanme,dname,grade from
emp e join dept d on(e.deptno=d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%';
//左外连接 把左边的不符合条件的记录也取出来
select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.deptno);
//右外连接
select ename,dname from emp e right outer join dept d on(e.deptno=d.deptno);//outer可去
//全连接
select ename,dname from emp e full join dept d on(e.deptno=d.deptno);

age:
求部门中哪些人的薪水最高
//先用了查询求出每个部门的最高薪水
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on(emp.sal=t.max_sal and emp.deptno=t.deptno)

求部门平均薪水的等级
select deptno,avg_sal,grade from salgrade s
join (select avg(sal) avg_sal,deptno from emp group by deptno) t
on(t.avg_sal between s.losal and s.hisal )

求部门平均的薪水等级
雇员中有哪些人是经理人
select ename from emp where empno in(select distinct mgr from emp)

select ename from emp
join (select distinct mgr t_mgr from emp) t
on (emp.empno = t.t_mgr);

不准用组函数，求薪水的最高值
求平均薪水最高的部门的部门编号
select t.deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
where max(t.avg_sal)=t.avg_sal;
XXXXXXX
select t.deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
where t.avg_sal=
(select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno)

);
求平均薪水最高的部门的部门名称
select dname from dept where deptno=
(
 select t.deptno from
 (select avg(sal) avg_sal,deptno from emp group by deptno) t
 where t.avg_sal=
 (select max(avg_sal) from
  (select avg(sal) avg_sal,deptno from emp group by deptno)

 )
);
求平均薪水的等级最低的部门的部门名称
求部门经理人中平均薪水最低的部门名称
求比普通员工的最高薪水还要高的经理人名称
求薪水最高的前5名雇员
select ename,sal from
(
 select ename,sal,rownum r from
 (select ename,sal from emp order by sal desc)
)
where r <=5;
求薪水最高的第6到10雇员

 

ddl:
create table stu
(
 id number(6),
 name varchar2(20) not null,
 sex number(1),
 age number(3),
 sdate date,
 grade number(2) default 1,
 class number(3),
 email varchar2(50)
);
create table stu
(
 id number(6),
 name varchar2(20) constraint t_stu_nn not null,
 sex number(1),
 age number(3),
 sdate date,
 grade number(2) default 1,
 class number(3),
 email varchar2(50),
 constaint stu_pri primary key(id),
 constaint stu_class_fk foreign key(class) references class(id),
 constaint stu_name_email_nui unique(name,email)
);
//stu_name_email_nui 约束名
//name email 组合不重复

 constaint：5个
 主键 外键 unique not null checkd

create sequence seq;
select seq.nextval from dual;