一. 启动sqlplus并登陆的三种方法
1. 第一种方法
sqlplus
请输入用户名: scott
输入口令:
2. 第二种方法
sqlplus username
请输入用户名: scott
输入口令:
3. 第三种方法
sqlplus username/password
请输入用户名: scott
输入口令:
4. 断开与数据库的连接
disconnect
5. 退出SQL*Plus
exit或quit
二. SQL命令
1. 查询一下
SQL> select empno,ename,job,sal from emp where sal < 2500
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7782 CLARK MANAGER 2450
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
已选择9行。
2. 列出缓冲区的内容
SQL> list
1* select empno,ename,job,sal from emp where sal < 2500
3. 编辑当前行
SQL> select epno,ename,job,sal from emp where sal < 2500;
select epno,ename,job,sal from emp where sal < 2500
*
第 1 行出现错误:
ORA-00904: "EPNO": 标识符无效
SQL> change /epno/empno
1* select empno,ename,job,sal from emp where sal < 2500
SQL> run
1* select empno,ename,job,sal from emp where sal < 2500
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7782 CLARK MANAGER 2450
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
已选择9行。
4. 增加一行
SQL> input
2 order by sal;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
已选择9行。
5. 在一行上添加一原文
SQL> append desc;
2* order by sal desc
SQL> run
1 select empno,ename,job,sal from emp where sal < 2500
2* order by sal desc
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7782 CLARK MANAGER 2450
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7934 MILLER CLERK 1300
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7369 SMITH CLERK 800
已选择9行。
6. 删除一行
SQL> list
1 select empno,ename,job,sal from emp where sal < 2500
2* order by sal desc
SQL> del
SQL> run
1* select empno,ename,job,sal from emp where sal < 2500
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7782 CLARK MANAGER 2450
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
已选择9行。
7. 用系统编辑程序编辑命令
SQL> edit
已写入 file afiedt.buf
8. 保存SAVE命令
SQL> save liyangwei
已创建 file liyangwei.sql
9. 运行命令文件
SQL> start liyangwei
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7782 CLARK MANAGER 2450
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
已选择9行。
10. 清缓冲区
SQL> clear buffer
buffer 已清除
11. 列出表的结构
SQL> desc emp;
名称 是否为空? 类型
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
三. 查询
1． 显示EMP表中所有的部门号、职工名称和管理者号码：
SQL> select deptno,ename,mgr from emp;
DEPTNO ENAME MGR
---------- ---------- ----------
20 SMITH 7902
30 ALLEN 7698
30 WARD 7698
20 JONES 7839
30 MARTIN 7698
30 BLAKE 7839
10 CLARK 7839
20 SCOTT 7566
10 KING
30 TURNER 7698
20 ADAMS 7788
DEPTNO ENAME MGR
---------- ---------- ----------
30 JAMES 7698
20 FORD 7566
10 MILLER 7782
已选择14行。
2． 算术运算符在SQL中的使用
SQL> select ename,sal+250*12 from emp;
ENAME SAL+250*12
---------- ----------
SMITH 3800
ALLEN 4600
WARD 4250
JONES 5975
MARTIN 4250
BLAKE 5850
CLARK 5450
SCOTT 6000
KING 8000
TURNER 4500
ADAMS 4100
ENAME SAL+250*12
---------- ----------
JAMES 3950
FORD 6000
MILLER 4300
已选择14行。
3. 把职工号和职工名字连接起来
SQL> select empno||ename employee from emp;
EMPLOYEE
--------------------------------------------------
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
EMPLOYEE
--------------------------------------------------
7900JAMES
7902FORD
7934MILLER
已选择14行。
4. 把职工号和职工名字中间用‘-’连接起来，并输出‘WORKS IN DEPARTMENT’
SQL> select empno||'-'||ename employee,'works in departiment',deptno from emp;
EMPLOYEE 'WORKSINDEPARTIMENT'
--------------------------------------------------- --------------------
DEPTNO
----------
7369-SMITH works in departiment
20
7499-ALLEN works in departiment
30
7521-WARD works in departiment
30
EMPLOYEE 'WORKSINDEPARTIMENT'
--------------------------------------------------- --------------------
DEPTNO
----------
7566-JONES works in departiment
20
7654-MARTIN works in departiment
30
7698-BLAKE works in departiment
30
EMPLOYEE 'WORKSINDEPARTIMENT'
--------------------------------------------------- --------------------
DEPTNO
----------
7782-CLARK works in departiment
10
7788-SCOTT works in departiment
20
7839-KING works in departiment
10
EMPLOYEE 'WORKSINDEPARTIMENT'
--------------------------------------------------- --------------------
DEPTNO
----------
7844-TURNER works in departiment
30
7876-ADAMS works in departiment
20
7900-JAMES works in departiment
30
EMPLOYEE 'WORKSINDEPARTIMENT'
--------------------------------------------------- --------------------
DEPTNO
----------
7902-FORD works in departiment
20
7934-MILLER works in departiment
10
已选择14行。
5. 如果列举出EMP表中所有部门号
SQL> select deptno from emp;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
----------
30
20
10
已选择14行。
SQL> select distinct deptno from emp;
DEPTNO
----------
30
20
10
6. 按单个字段排序，如按照ENAME排序
SQL> select ename,job,sal*12,deptno from emp order by ename;
ENAME JOB SAL*12 DEPTNO
---------- --------- ---------- ----------
ADAMS CLERK 13200 20
ALLEN SALESMAN 19200 30
BLAKE MANAGER 34200 30
CLARK MANAGER 29400 10
FORD ANALYST 36000 20
JAMES CLERK 11400 30
JONES MANAGER 35700 20
KING PRESIDENT 60000 10
MARTIN SALESMAN 15000 30
MILLER CLERK 15600 10
SCOTT ANALYST 36000 20
ENAME JOB SAL*12 DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 9600 20
TURNER SALESMAN 18000 30
WARD SALESMAN 15000 30
已选择14行。
7. 按多个字段排序：如按部门号升序，按工资降序排序
SQL> select deptno,job,ename from emp order by deptno,sal desc;
DEPTNO JOB ENAME
---------- --------- ----------
10 PRESIDENT KING
10 MANAGER CLARK
10 CLERK MILLER
20 ANALYST SCOTT
20 ANALYST FORD
20 MANAGER JONES
20 CLERK ADAMS
20 CLERK SMITH
30 MANAGER BLAKE
30 SALESMAN ALLEN
30 SALESMAN TURNER
DEPTNO JOB ENAME
---------- --------- ----------
30 SALESMAN MARTIN
30 SALESMAN WARD
30 CLERK JAMES
已选择14行。
8. 查询工作是CLERK的所有职工的姓名，职工号和部门号
SQL> select ename,empno,job,deptno from emp where job='CLERK';
ENAME EMPNO JOB DEPTNO
---------- ---------- --------- ----------
SMITH 7369 CLERK 20
ADAMS 7876 CLERK 20
JAMES 7900 CLERK 30
MILLER 7934 CLERK 10
9. 从DEPT表中查询出部门号大于20的部门名称
SQL> select dname,deptno from dept where deptno > 20;
DNAME DEPTNO
-------------- ----------
SALES 30
OPERATIONS 40
10. 查询工作是MANAGER并且工资大于1500，或者工作是SALESMAN的职工信息
SQL> select empno,ename,job,sal,deptno from emp where sal > 1500 and job = 'MANAGER' or job = 'SALESMAN';
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7499 ALLEN SALESMAN 1600 30
7521 WARD SALESMAN 1250 30
7566 JONES MANAGER 2975 20
7654 MARTIN SALESMAN 1250 30
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
7844 TURNER SALESMAN 1500 30
已选择7行。
11. 查询工资在1000到2000之间的职工名字和工资信息
SQL> select ename,sal from emp
2 where sal between 1000 and 2000;
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
已选择6行。
12. 查询有7902，7566，7788三个MGR号之一的所有职工：
SQL> select empno,ename,sal,mgr from emp
2 where mgr in(7902,7566,7788);
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7369 SMITH 800 7902
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
7902 FORD 3000 7566
13. 查询名字以“S”开始的所有职工
SQL> select ename from emp where ename like 'S%';
ENAME
----------
SMITH
SCOTT
14. 查询名字只有4个字符的所有职工：
SQL> select ename from emp where ename like '_____';
ENAME
----------
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES
已选择8行。
15. 查询没有管理者的所有职工
SQL> select ename,mgr from emp where mgr is null;
ENAME MGR
---------- ----------
KING
16. 单&号替代数字变量输入
SQL> select empno,ename,sal from emp where deptno=&DEPARTMENT_NUMBER;
输入 department_number 的值: 10
原值 1: select empno,ename,sal from emp where deptno=&DEPARTMENT_NUMBER
新值 1: select empno,ename,sal from emp where deptno=10
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
17. 单&号替代字符串变量输入
SQL> select empno,ename,sal*12 from emp where job='&JOB_TITLE';
输入 job_title 的值: MANAGER
原值 1: select empno,ename,sal*12 from emp where job='&JOB_TITLE'
新值 1: select empno,ename,sal*12 from emp where job='MANAGER'
EMPNO ENAME SAL*12
---------- ---------- ----------
7566 JONES 35700
7698 BLAKE 34200
7782 CLARK 29400
18. TO_CHAR 数字数据转换为字符串
SQL> select to_char(8897) from dual;
19. TO_NUMBER字符串数据转换为数字
SQL> select to_number('8897') from dual;
TO_NUMBER('8897')
-----------------
8897
20. TO_DATE字符串数据转换为日期数据
SQL> select to_date('03-12月-81') from dual;
TO_DATE('03-12
--------------
03-12月-81
21. 求平均值
SQL> select avg(sal) from emp;
AVG(SAL)
----------
2073.21429
22. 求最小值
SQL> select min(sal) from emp where job = 'CLERK';
MIN(SAL)
----------
800
23. 求数目
SQL> select count(*) from emp where deptno=20;
COUNT(*)
----------
5
24. GROUP BY子句求每个部门中的平均工资
SQL> select job,avg(sal) from emp group by job;
JOB AVG(SAL)
--------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
25. HAVING子句查询人数超过3人的部门中的平均工资
SQL> select deptno,avg(sal) from emp group by deptno having count(*) > 3;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
26. 连接从EMP和DEPT中查询出职工名字、工作和部门名称
SQL> select ename,job,dname from emp,dept where emp.deptno = dept.deptno;
ENAME JOB DNAME
---------- --------- --------------
SMITH CLERK RESEARCH
ALLEN SALESMAN SALES
WARD SALESMAN SALES
JONES MANAGER RESEARCH
MARTIN SALESMAN SALES
BLAKE MANAGER SALES
CLARK MANAGER ACCOUNTING
SCOTT ANALYST RESEARCH
KING PRESIDENT ACCOUNTING
TURNER SALESMAN SALES
ADAMS CLERK RESEARCH
ENAME JOB DNAME
---------- --------- --------------
JAMES CLERK SALES
FORD ANALYST RESEARCH
MILLER CLERK ACCOUNTING
已选择14行。
27. 子查询: 从EMP中查询出工资最低的职工
SQL> select ename,job,sal from emp
2 where sal=(select min(sal) from emp);
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
28. 子查询: 从EMP中查询出每个部门工资最低的职工
SQL> select ename,sal,deptno from emp where sal in
2 (select min(sal) from emp group by deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
JAMES 950 30
SMITH 800 20
MILLER 1300 10
扫一扫
134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
