alter user account unlock;

desc <>

select * from <>

select xx,xx from <>

select xx,xx "别名" from <>;

任何含有空值的数学表达式都是空值！

select xx||xx from <>; 字符连接相当于JAVA“+”


select xx||'字符' from <>;

select xx||'das''dasd' from <>;

消除重复的行：select distinct xx from <>;
消除组合重复的行：select distinct xx,xx from <>;


select * from <> where xx = > < <> XX;

select ename,sal from emp where sal between 800 and||or1500;相当于select ename,sal from emp where sal>=800 and||or sal<=1500;


空值的处理：错误的 select ename,sal comm from emp where comm = null;
正确的 select ename,sal comm from emp where comm is null;
select ename,sal comm from emp where comm is not null;


where中的in:select ename,sal comm from emp where sal not in||in (800,1500,2000);
select ename,sal comm from emp where ename in('SMITH','KING','ABC');


select ename,sal hiredate from emp where hiredate > '20-2月-81';


select ename from emp where ename like '%ALL%'
第二个字母 select ename from emp where ename like '_A%'


转义字符：select ename from emp where ename like '%&%%' escape '&';


排序：select * from dept order by deptno desc;降序
select empno ename from emp order by empno asc;升序
按两个字段排序：select empno ename from emp where deptno <> 10 order by empno asc,deptno desc;



function
大写转小写：select lower(ename) from emp;
select ename from emp where lower(ename) like '%_a%';

截字符：select substr(ename,1,3) from emp;

转字符：select chr(65) from dual;
select ascii('A') from dual;

四舍五入：
select round(24.561) from dual; 25
select round(24.561,2) from dual 24.56

转化格式：
数字：select to_char(sal,'$999,999.9999') from emp
日期：select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp
select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp

to_date: select ename,hiredate from emp where hiredate > to_date('1981-11-17 00:00:00','YYYY-MM-DD HH24:MI:SS' )

to_number：select sal from emp where sal > to_number('$1,250.00','$9,999.99')
nvl: select ename,sal*12 +nvl(comm,0) from emp



max():select max(sal) from emp
min():select min(sal) from emp
avg():select avg(sal) from emp
select to_char(avg(sal),'9999999.99') from emp
sum():select sum(sal) from emp
count():select count(*) from emp


group by: select deptno,avg(sal) from emp group by deptno

join() on ()


自连接：select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.deptno

CROSS 是迪卡尔积 所得到的结果记录数是相连接的两个表的记录数相乘 cross join
select ename,dname from emp cross join dept (select ename,dname from emp,dept)



等值连接：select ename,dname from emp,dept where emp.deptno = dept.deptno
(select ename,dname from emp join dept on(emp.deptno = dept.deptno))
(select ename,dname from emp join dept using(deptno))

非等值连接：select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal)

select ename,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.empno)
select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno)
select ename,dname from emp e full outer join dept d on (e.deptno =d.deptno)


求部门中哪些人的薪水最高：
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 (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)

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

雇员中哪些人是经理人：select ename from where empno in (select distinct mgr from emp)
(select distinct ename from (select e1.ename,e1.empno,e2.mgr from emp e1 join emp e2 on(e1.empno = e2.mgr)) t )


不准用组函数，求薪水的最高值（面试题）：select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))

求平均薪水最高的部门的部门编号：select deptno,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal = (select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno))

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


求平均薪水的等级最低的部门的部门名称：
select dname,t1.deptno,grade,avg_sal from (select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = (
select min(grade) from (select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)))


rownum < 5
select ename from emp where rownum < 5;
rownum 只能写< <= 没有大于，等于


DML语句
insert update delete

备份
create table emp2 as select * from emp


insert
insert into dept2 values (50,'game','bj')
insert into dept2 (deptno,dname) values (60,'game2')
insert into dept2 select * from dept

update
update emp2 set sal = sal*2,ename=ename||'-' where deptno =10

delete
delete from dept2 where deptno < 25


create
create table t(a varchar2(10))
drop
drop table t

alter(修改表结构)
alter table stu modify (addr varchar2(100))
alter table stu drop (addr)
alter table stu add(addr varchar2(100));

删除或者增加 约束条件
alter table stu drop constraint stu_class_fk;
alter table stu add constraint stu_class_fu foreign key (class) references class(id);


transaction 开始于dml语句 结束于rollback或者committ或者ddl语句或者正常断开连接等


varchar2(),char(),long(),date, (number(1) default 1)......
创建表
create table t (a varchar2(10),b varchar2(10))


非空约束not null
唯一约束unique
CHECK约束
主键约束primary key
外键约束foreign key外键约束参考的字段必须是主键


字段约束：
create table stu
(
id number(6), primary key
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),
email varchar2(50) unique
);

表级约束：
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) ,
constraint stu_class_fk foreign key(class) references class(id),
constraint stu_id_pk primary key (id),
constraint stu_name_email_uni unique(email,name)
) ;



create table class
(
in number(4) primary key,
name varchar2(20) not null
);