Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> --查看表的结构
SQL> desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL> --打开显示操作时间
SQL> set timing on;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
Executed in 0.172 seconds
SQL> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected
Executed in 0.172 seconds
SQL> create table users(userid varchar2(20),username varchar2(30),userpss varchar2(30));
Table created
Executed in 0.547 seconds
SQL> insert into users values('a0001','维唯为为呵呵哈哈','luowei505005@126.com');
1 row inserted
Executed in 0 seconds
SQL> --疯狂复制
SQL> insert into users (userid,username,userpss) select * from users;
SQL> delete from users;
131072 rows deleted
Executed in 6.5 seconds
SQL> insert into users values('a0001','维唯为为呵呵哈哈','luowei505005@126.com');
SQL> insert into users values('a0001','维唯为为呵呵哈哈','luowei505005@126.com');
1 row inserted
Executed in 0.031 seconds
SQL> insert into users (userid,username,userpss) select * from users;
1 row inserted
Executed in 0.125 seconds
SQL> insert into users (userid,username,userpss) select * from users;
2 rows inserted
Executed in 0.031 seconds
SQL> insert into users (userid,username,userpss) select * from users;
4 rows inserted
Executed in 0.032 seconds
SQL> insert into users (userid,username,userpss) select * from users;
8 rows inserted
Executed in 0 seconds
SQL> insert into users (userid,username,userpss) select * from users;
16 rows inserted
Executed in 0.016 seconds
SQL> insert into users (userid,username,userpss) select * from users;
32 rows inserted
Executed in 0.016 seconds
SQL> select deptno,job from emp;
SQL> select deptno,job from emp;
DEPTNO JOB
------ ---------
20 CLERK
30 SALESMAN
30 SALESMAN
20 MANAGER
30 SALESMAN
30 MANAGER
10 MANAGER
20 ANALYST
10 PRESIDENT
30 SALESMAN
20 CLERK
30 CLERK
20 ANALYST
10 CLERK
14 rows selected
Executed in 0.172 seconds
SQL> select distinct deptno,job from emp;--消除重复行
DEPTNO JOB
------ ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
9 rows selected
Executed in 0.156 seconds
SQL> select sal*13,ename from emp where ename='smith';
SAL*13 ENAME
---------- ----------
Executed in 0.062 seconds
SQL> select sal*13 "年工资",ename from emp;
年工资 ENAME
---------- ----------
10400 SMITH
20800 ALLEN
16250 WARD
38675 JONES
16250 MARTIN
37050 BLAKE
31850 CLARK
39000 SCOTT
65000 KING
19500 TURNER
14300 ADAMS
12350 JAMES
39000 FORD
16900 MILLER
14 rows selected
Executed in 0.187 seconds
SQL> select sal*13+comm*13 "年工资",ename,comm from emp;
年工资 ENAME COMM
---------- ---------- ---------
SMITH
24700 ALLEN 300.00
22750 WARD 500.00
JONES
34450 MARTIN 1400.00
BLAKE
CLARK
SCOTT
KING
19500 TURNER 0.00
ADAMS
JAMES
FORD
MILLER
14 rows selected
Executed in 0.297 seconds
SQL> --上面所有奖金为空的，他的年工资就为空了
SQL> --处理null值
SQL> select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;
年工资 ENAME COMM
---------- ---------- ---------
10400 SMITH
24700 ALLEN 300.00
22750 WARD 500.00
38675 JONES
34450 MARTIN 1400.00
37050 BLAKE
31850 CLARK
39000 SCOTT
65000 KING
19500 TURNER 0.00
14300 ADAMS
12350 JAMES
39000 FORD
16900 MILLER
14 rows selected
Executed in 0.172 seconds
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
Executed in 0.266 seconds
SQL> select ename,sal from emp where sal>3000;
ENAME SAL
---------- ---------
KING 5000.00
Executed in 0.079 seconds
SQL> --找出1982.1.1后入职的员工
SQL> select ename,hiredate from emp where hiredate>'1-1月-1982';
ENAME HIREDATE
---------- -----------
SCOTT 1987-4-19
ADAMS 1987-5-23
MILLER 1982-1-23
Executed in 0.079 seconds
SQL> select ename,sal from emp where sal>=2000 and sal<=3000;
ENAME SAL
---------- ---------
JONES 2975.00
BLAKE 2850.00
CLARK 2450.00
SCOTT 3000.00
FORD 3000.00
Executed in 0.157 seconds
SQL> --like操作符，%：代表0到多个字符 _:表示任意单个字符
SQL> select ename,sal from emp where ename like 'S%';
ENAME SAL
---------- ---------
SMITH 800.00
SCOTT 3000.00
Executed in 0.078 seconds
SQL> select ename,sal from emp where ename like '__o%';
ENAME SAL
---------- ---------
Executed in 0.047 seconds
SQL> select ename,sal from emp where ename like '__O%';
ENAME SAL
---------- ---------
SCOTT 3000.00
Executed in 0.062 seconds
SQL> --where条件中使用in
SQL> select * from emp where empno in(123,234,456);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
Executed in 0.109 seconds
SQL> select * from emp where empno in(7844,234,456);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
Executed in 0.11 seconds
SQL> --显示没有上级的那个人
SQL> select * from emp where mgr=null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
Executed in 0.046 seconds
SQL> select * from emp where mgr is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 1981-11-17 5000.00 10
Executed in 0.078 seconds
SQL> --工资大于500，或者岗位是manager,并且名字首字母为“J”
SQL> select * from emp where (sal>500 or job='manager') and ename like 'J';
SQL> SELECT * FROM EMP ORDER BY SAL; --默认从低到高排列
SQL> SELECT * FROM EMP ORDER BY SAL desc;
SQL> --按照部门号升序而雇员的工资降序排列
SQL> select ename,deptno "部门号",sal*12 "年薪" from emp order by deptno asc,sal desc;
SQL> select ename,deptno "部门号",sal*12 "年薪", hiredate "入职时间" from emp order by deptno asc,sal desc,hiredate desc;
SQL> select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
SQL> select max(sal),min(sal) from emp;
SQL> select ename,sal from emp where sal=(select max(sal) from emp);
SQL> select * from emp where sal>(select avg(sal) from emp);
SQL> --对查询的结果进行分组显示
SQL> select avg(sal),max(sal),deptno from emp group by deptno;
SQL> --显示每个部门的每种岗位的平均工资和最低工资
SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,job;
SQL> select avg(sal),min(sal),max(sal),deptno,job from emp group by deptno,job;
SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,job;
SQL> select min(sal),avg(sal),max(sal),deptno,job from emp group by deptno, job;
SQL> select min(sal),avg(sal),max(sal),deptno from emp group by deptno;
SQL> select avg(sal),min(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
SQL> --对分组的结果进行筛选之后再将平均工资进行升序排列
SQL> select avg(sal),min(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);
SQL> select * from dept;
SQL> select * from emp;
SQL> --显示SMITH的部门的名称，job,sal,ename
SQL> select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;
SQL> select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno and a1.ename='smith';
SQL> select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno and a1.ename='SMITH';
SQL> select * from emp;
SQL> select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;
SQL> --显示员工的姓名、工资、及工资的级别
SQL> select * from salgrade;
SQL> select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
SQL> --显示雇员名，雇员工资及所在部门的名字，并按部门排序
SQL> select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a2.deptno;
SQL> select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';
SQL>
SQL> --子查询
SQL> --1.查询出smith的部门号
SQL> select deptno from emp where ename='SMITH';
SQL> select * from emp where deptno=
2 (select deptno from emp where ename='SMITH');
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10;
SQL> SELECT * FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO=10 );
SQL> --显示工资比部门号为30的所有员工的工资高的员工的姓名、工资和部门号
SQL> select ename,sal,deptno from emp where sal>all
2 (select sal from emp where deptno=30);
SQL> select max(sal) from emp;
SQL> select * from emp where sal>(select max(sal) from emp where deptno=30);
SQL> select ename,sal,dept from emp where sal>
2 (select min(sal) from emp where deptno=30);
SQL> select * from emp where sal>
2 (select min(sal) from emp where deptno=30);
SQL> select deptno,job from EMP where ename='SMITH';
SQL> SELECT * FROM EMP WHERE (deptno,job)=(select deptno,job from EMP where ename='SMITH');
SQL> --显示出高于自己部门平均工资的员工的信息
SQL> --1.查询出各个部门的平均工资和部门号
SQL> select deptno,avg(sal) mysal from emp group by deptno;
SQL> --2.把上面的查询看做是一张子表
SQL> select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,
2 (select deptno,avg(sal) mysal from emp group by deptno) a1
3 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
SQL> --分页
SQL> select * from emp;
SQL> select a1.*,rownum rn from(select * from emp) a1;
SQL> select a1.*,rownum rn from(select * from emp) a1 where rownum<=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 1
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 2
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 3
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 4
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 5
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 8
7839 KING PRESIDENT 1981-11-17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10
10 rows selected
SQL> select * from (select a1.*,rownum rn from(select * from emp) a1 where rownum<=10)where rn>=6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 8
7839 KING PRESIDENT 1981-11-17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10
SQL> --指定查询列，只需修改最底层的列
SQL> select * from (select a1.*,rownum rn from(select ename,sal from emp) a1 where rownum<=10)where rn>=6;
ENAME SAL RN
---------- --------- ----------
BLAKE 2850.00 6
CLARK 2450.00 7
SCOTT 3000.00 8
KING 5000.00 9
TURNER 1500.00 10
SQL> --排序,只需要修改最底层的列就行
SQL> select * from (select a1.*,rownum rn from(select ename,sal from emp order by sal) a1 where rownum<=10)where rn>=6;
ENAME SAL RN
---------- --------- ----------
MILLER 1300.00 6
TURNER 1500.00 7
ALLEN 1600.00 8
CLARK 2450.00 9
BLAKE 2850.00 10
SQL> select * from (select a1.*,rownum rn from(select ename,sal from emp order by sal desc) a1 where rownum<=10)where rn>=6;
ENAME SAL RN
---------- --------- ----------
CLARK 2450.00 6
ALLEN 1600.00 7
TURNER 1500.00 8
MILLER 1300.00 9
WARD 1250.00 10
SQL> select * from (select a1.*,rownum rn from(select ename,sal from emp order by sal desc) a1 where rownum=9)where rn=4;
ENAME SAL RN
---------- --------- ----------
SQL> select * from (select a1.*,rownum rn from(select ename,sal from emp order by sal desc) a1 where rownum<=9)where rn>=4;
ENAME SAL RN
---------- --------- ----------
JONES 2975.00 4
BLAKE 2850.00 5
CLARK 2450.00 6
ALLEN 1600.00 7
TURNER 1500.00 8
MILLER 1300.00 9
6 rows selected
SQL> --分页有3种方式---------
SQL> --根据ROWID来分
SQL> --2.按分析函数-----
SQL> --3按ROWNUM来分-------
SQL> --显示总共有多少行
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> -----用查询结果创建新表
SQL> create table myemp2 (id,ename,sal) as
2 select empno,ename,sal from emp;
Table created
SQL> desc myemp2;
SQL> desc myemp2;
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
ID NUMBER(4) Y
ENAME VARCHAR2(10) Y
SAL NUMBER(7,2) Y
SQL> select * from myemp2;
ID ENAME SAL
----- ---------- ---------
7369 SMITH 800.00
7499 ALLEN 1600.00
7521 WARD 1250.00
7566 JONES 2975.00
7654 MARTIN 1250.00
7698 BLAKE 2850.00
7782 CLARK 2450.00
7788 SCOTT 3000.00
7839 KING 5000.00
7844 TURNER 1500.00
7876 ADAMS 1100.00
7900 JAMES 950.00
7902 FORD 3000.00
7934 MILLER 1300.00
14 rows selected
SQL> select ename,sal,job from emp where sal>2500 union
2 select ename,sal,job from emp where job='MANERGE';
ENAME SAL JOB
---------- ---------- ---------
BLAKE 2850 MANAGER
FORD 3000 ANALYST
JONES 2975 MANAGER
KING 5000 PRESIDENT
SCOTT 3000 ANALYST
SQL> --union all 不会取消重复的记录
SQL> select ename,sal,job from emp where sal>2500 union all
2 select ename,sal,job from emp where job='MANERGE';
ENAME SAL JOB
---------- ---------- ---------
JONES 2975 MANAGER
BLAKE 2850 MANAGER
SCOTT 3000 ANALYST
KING 5000 PRESIDENT
FORD 3000 ANALYST
SQL> ---intersect 取交集
SQL> select ename,sal,job from emp where sal>2500
2 intersect select ename,sal,job from emp where job='MANAGER';
ENAME SAL JOB
---------- ---------- ---------
BLAKE 2850 MANAGER
JONES 2975 MANAGER
SQL> --合并查询，差集
SQL> select ename,sal,job from emp where sal>2500 minus
2 select ename,sal,job from emp where job='manager';
ENAME SAL JOB
---------- ---------- ---------
BLAKE 2850 MANAGER
FORD 3000 ANALYST
JONES 2975 MANAGER
KING 5000 PRESIDENT
SCOTT 3000 ANALYST
SQL>--- OK ! ^_^ 先到此为止！！
