--desc 表名 描述表的内容
desc emp;
--加上数学表达式和列名 ""保持格式
select ename "name space", sal*12 year_sal from emp;
select 2*3 from dual;
select sysdate from dual;
--空值的数学表达式 结果都是空值
select ename, sal*12 + comm from emp;
--"||"字符串连接 单引号中的是字符串,字符串中的单引号,''表示
select ename||sal from emp;
--distinct 修饰两个字段
select distinct deptno, job from emp;
select ename, sal from emp where sal between 800 and 1500;
select ename, sal from emp where sal >= 800 and sal <= 1500;
select ename, sal from emp where comm is null;
--一个事务开始于一条ddl语句,结束语rollback commit ddl数据定义语句 dcl数据控制语句语句
--正常断开连接 提交 非正常断开连接 回滚
select ename, sal from emp where sal not in (800 ,1500);
-- 模糊查询
select ename, sal from emp where ename like '_A%';
select ename, sal from emp where ename like '%\%%';
--转义字符
select ename, sal from emp where ename like '%$%%' escape '$';
--排序
select * from dept order by deptno desc;
select ename, sal from emp order by deptno asc, ename desc;
--函数
select lower(ename) from emp;
select substr(ename,1,3) from emp;
select round(23.652, 1) from dual;
select to_char(sal, '$99,999.999') from emp;
select to_char(sal, 'L000,000.000') from emp;
select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
select ename, hiredate from emp
where hiredate > to_date('1982-09-11 12:44:44', 'YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal > to_number('$1,250.000', '$9,999.000');
select ename,sal*12 + nvl(comm, 0) from emp;
--组函数 多行输入,只有一行输出 select ename, max(sal) from emp; 结果不能保证只有一行
select max(sal) from emp;
select sum(sal) from emp;
select count(distinct deptno) from emp;
--分组函数
select deptno,avg(sal) from emp group by deptno;
select deptno, job, max(sal) from emp group by deptno, job;
--出现在select后,不是组函数的,必须出现在group by里
--where语句对单挑记录进行过滤,分组后过滤用having
--子查询 把一次查询结果当成表
select ename from emp where sal = (select max(sal) from emp);
select ename, sal from emp
join (select max(sal)max_sal, deptno from emp group) t
on(t.max_sal = emp.sal and t.deptno = emp.deptno);
--自连接
select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno;
select ename, dname, grade from emp e, dept d, salgrade s where e.deptno = d.deptno and
e.sal between
s.losal and s.hisal and job <> 'CLERK';
--SQL 1999 链接条件不写在where中,where只负责过滤数据
select ename, dname from emp cross join dept;
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 e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
--左外连接
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
--右外连接
select ename, dname from emp e right 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 deptno, max(sal) max_sal from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
--求部门平均薪水的等级
select deptno, grade from salgrade s
join (select avg(sal) avg_sal, deptno from emp group by deptno) t
on (avg_sal between losal and hisal);
--求平均薪水最高的部门的部门标号
select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(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)
)
)
--建立视图
create view v$_dept_avg_sal_info as
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);
--变简单了
select dname, t1.deptno, grade, avg_sal from
v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from v$_dept_avg_sal_info
)
--求部门平均薪水的等级
select deptno, avg_sal, grade from
(select avg(sal) avg_sal, deptno from emp group by deptno)
join salgrade s
on (avg_sal between s.losal and s.hisal);
--求部门平均的薪水等级
select avg(grade), deptno from
(
select deptno, ename, grade from emp
join salgrade s
on emp.sal between s.losal and s.hisal
)
group by deptno
--雇员中有哪些人是经理人
select ename from emp where empno in
(select distinct mgr from emp);
--不使用组函数,求薪水的最高值
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 dname from dept where deptno =
(
select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
)
)
--求比普通员工的最高薪水还要高的经理人名称
select ename from emp
where empno in (select distinct mgr from emp where mgr is not null) and sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
)
--rownum只能和小于等于号联合使用
--使用rownum 要关联表
select ename from
(select rownum r, ename from emp) where r > 10;
--薪水最高的5个人
select ename, sal from
(select ename, sal from emp order by sal desc)
where rownum <= 5;
--薪水最高的第六个到第十个雇员
select ename, sal from
(
select ename, sal, rownum r from
(select ename, sal from emp order by sal desc)
)
where r >= 6 and r <= 10
--http://penghao122.javaeye.com/blog/54810
--SQL面试题
--找出没选过“黎明”老师的所有学生姓名。
select sname from s
join sc on (sc.sno = s.sno)
join c on (c.cno = s.cno)
where c.cteacher <> 'liming';
--列出2门以上(含2门)不及格学生姓名及平均成绩。
select sname where sno in
(
select sno from sc where scgrade < 60
group by sno having count(*) >= 2
)
--即学过1号课程有学过2号课所有学生的姓名
select sno from sc where cno = 1 and sno in
(
select sno from sc where cno = 2
);
--一个事务开始于一条ddl语句,结束语rollback commit ddl数据定义语句 dcl数据控制语句语句
--正常断开连接 提交 非正常断开连接 回滚
--desc 表名 描述表的内容 desc emp; --加上数学表达式和列名 ""保持格式 select ename "name space", sal*12 year_sal from emp; select 2*3 from dual; select sysdate from dual; --空值的数学表达式 结果都是空值 select ename, sal*12 + comm from emp; --"||"字符串连接 单引号中的是字符串,字符串中的单引号,''表示 select ename||sal from emp; --distinct 修饰两个字段 select distinct deptno, job from emp; select ename, sal from emp where sal between 800 and 1500; select ename, sal from emp where sal >= 800 and sal <= 1500; select ename, sal from emp where comm is null; --一个事务开始于一条ddl语句,结束语rollback commit ddl数据定义语句 dcl数据控制语句语句 --正常断开连接 提交 非正常断开连接 回滚 select ename, sal from emp where sal not in (800 ,1500); -- 模糊查询 select ename, sal from emp where ename like '_A%'; select ename, sal from emp where ename like '%\%%'; --转义字符 select ename, sal from emp where ename like '%$%%' escape '$'; --排序 select * from dept order by deptno desc; select ename, sal from emp order by deptno asc, ename desc; --函数 select lower(ename) from emp; select substr(ename,1,3) from emp; select round(23.652, 1) from dual; select to_char(sal, '$99,999.999') from emp; select to_char(sal, 'L000,000.000') from emp; select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp; select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; select ename, hiredate from emp where hiredate > to_date('1982-09-11 12:44:44', 'YYYY-MM-DD HH24:MI:SS'); select sal from emp where sal > to_number('$1,250.000', '$9,999.000'); select ename,sal*12 + nvl(comm, 0) from emp; --组函数 多行输入,只有一行输出 select ename, max(sal) from emp; 结果不能保证只有一行 select max(sal) from emp; select sum(sal) from emp; select count(distinct deptno) from emp; --分组函数 select deptno,avg(sal) from emp group by deptno; select deptno, job, max(sal) from emp group by deptno, job; --出现在select后,不是组函数的,必须出现在group by里 --where语句对单挑记录进行过滤,分组后过滤用having --子查询 把一次查询结果当成表 select ename from emp where sal = (select max(sal) from emp); select ename, sal from emp join (select max(sal)max_sal, deptno from emp group) t on(t.max_sal = emp.sal and t.deptno = emp.deptno); --自连接 select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno; select ename, dname, grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK'; --SQL 1999 链接条件不写在where中,where只负责过滤数据 select ename, dname from emp cross join dept; 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 e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno); --左外连接 select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno); --右外连接 select ename, dname from emp e right 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 deptno, max(sal) max_sal from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno); --求部门平均薪水的等级 select deptno, grade from salgrade s join (select avg(sal) avg_sal, deptno from emp group by deptno) t on (avg_sal between losal and hisal); --求平均薪水最高的部门的部门标号 select deptno, avg_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = (select max(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) ) ) --建立视图 create view v$_dept_avg_sal_info as 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); --变简单了 select dname, t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min(grade) from v$_dept_avg_sal_info ) --求部门平均薪水的等级 select deptno, avg_sal, grade from (select avg(sal) avg_sal, deptno from emp group by deptno) join salgrade s on (avg_sal between s.losal and s.hisal); --求部门平均的薪水等级 select avg(grade), deptno from ( select deptno, ename, grade from emp join salgrade s on emp.sal between s.losal and s.hisal ) group by deptno --雇员中有哪些人是经理人 select ename from emp where empno in (select distinct mgr from emp); --不使用组函数,求薪水的最高值 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 dname from dept where deptno = ( select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal, deptno from emp group by deptno) ) ) --求比普通员工的最高薪水还要高的经理人名称 select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) ) --rownum只能和小于等于号联合使用 --使用rownum 要关联表 select ename from (select rownum r, ename from emp) where r > 10; --薪水最高的5个人 select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5; --薪水最高的第六个到第十个雇员 select ename, sal from ( select ename, sal, rownum r from (select ename, sal from emp order by sal desc) ) where r >= 6 and r <= 10 --http://penghao122.javaeye.com/blog/54810 --SQL面试题 --找出没选过“黎明”老师的所有学生姓名。 select sname from s join sc on (sc.sno = s.sno) join c on (c.cno = s.cno) where c.cteacher <> 'liming'; --列出2门以上(含2门)不及格学生姓名及平均成绩。 select sname where sno in ( select sno from sc where scgrade < 60 group by sno having count(*) >= 2 ) --即学过1号课程有学过2号课所有学生的姓名 select sno from sc where cno = 1 and sno in ( select sno from sc where cno = 2 ); --一个事务开始于一条ddl语句,结束语rollback commit ddl数据定义语句 dcl数据控制语句语句 --正常断开连接 提交 非正常断开连接 回滚
送上mysql中的,oracle默认表(emp,dept, salgrade)的建表语句
CREATE TABLE EMP
(EMPNO numeric(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR numeric(4),
HIREDATE DATE,
SAL numeric(7, 2),
COMM numeric(7, 2),
DEPTNO numeric(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
'1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
'1981-11-20', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
'1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
'1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
'1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
'1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
'1981-01-09', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
'1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
'1981-11-07', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
'1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
'1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
'1981-11-03', 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
'1981-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
'1982-01-12', 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO numeric(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE BONUS
(ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL numeric,
COMM numeric);
CREATE TABLE SALGRADE
(GRADE numeric,
LOSAL numeric,
HISAL numeric);
INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
CREATE TABLE DUMMY
(DUMMY numeric);
INSERT INTO DUMMY VALUES (0);