当前位置： 首页 > news >正文

济南自助建站系统网站制作软件手机

news 2025/11/14 16:45:41
济南自助建站系统,网站制作软件手机,亿星网站建设,关键词分析软件【本文谢绝转载原文来自http://990487026.blog.51cto.com】大纲数据库编程1 Oracle 过滤 函数 分组 外连接 自连接本文实验基于的数据表:winsows安装好Oracle11g之后,开始实验SQLplus 登陆 ORaclesqlplus 退出的方式查看用户之下有什么表查看表的所有记录#xff0c;不… 【本文谢绝转载原文来自http://990487026.blog.51cto.com】大纲数据库编程1 Oracle 过滤 函数 分组 外连接 自连接本文实验基于的数据表:winsows安装好Oracle11g之后,开始实验SQLplus 登陆 ORaclesqlplus 退出的方式查看用户之下有什么表查看表的所有记录不区分大小写设置SQLplus行宽,页宽,列宽清屏命令select as 语法1,as别名的使用2,没有引号带有空格的别名,无法识别:3,带有空格的别名解决办法:年收入抛出问题,年收入显示为空解决办法,nvl(a,b)函数1,NULL空值 任何数与NULL运算都是NULL2,空值不是空 null ! null ed修改上一条命令:保存退出,/开始执行 where 过滤,查询奖金为空的员工: ||连接符,与虚表 dual虚表 DISTINCT 删除重复行 DISTINCT修饰多个字段时,当两个数据完全一样才认为是重复的 SQLplus工具的 help edit的帮助: Oracle系统常用的参数 默认的时间显示 临时修改系统时间的显示方式: where过滤字句  大于 where过滤字句between and where过滤字句between A  and B,A 必须小于等于 B where过滤字句 in where过滤字句 not in where过滤字句 not in 与null在一起,受影响 where like模糊查询,查询员工姓名S开头的信息: where like模糊查询,查询员工姓名是4个字母 where like模糊查询,转义字符 where order by 工资排序,默认升序 where order by 别名 order by 遇到null1,任何表达式与null运算都为空2,null!nullnulls last 把null放在最后 order修饰多个列,desc的作用范围只是最近的哪一个! count函数字符函数大小写转换函数 LOWER UPPER INITCAP字符串连接: concatsubstr(a,b) 从a中第b位开始取字符串 length 字符数lengthb 字节数instr求子串的位置lpad,rpad左填充右填充:trim去除前后指定字符:replace 字符串替换数字函数round 四舍五入tuec截断函数mod求余函数to_char格式化输出两个日期相减,返回日期之间相差的天数 精确显示员工入职月数,时间运算months_between add_months()向指定的日期中加若干的月数 3,1隐式转换,字符串转日期 3,1显示转换,字符串转日期 字符串与字符串比较 to_char()对数字转字符 to_number()字符转数字 通用函数 nvl2(a,b,c)当a为null时返回c,否则返回bnullif(a,b)当a,b相等时返回NULL,否则返回a案例:总裁涨1000,经理涨800,其他涨500 方法1,SQL规范方法:方法2:Oracle专有方法组函数: avg sum max min count 组函数与null在一起组函数会自动过滤空值:修正:分组数据: 求出各个部门的平均工资分组最难的地方: having 分组过滤: 求10号部门的平均工资的两种方法 1,先分组再过滤2,先过滤再分组SQL优化问题: 多表查询: [等值连接],显示员工信息,员工号,姓名,月薪 [不等值连接]显示员工信息,员工号,姓名,月薪,薪水级别 多表查询4,求每个部门的人数:(需要分组) [少了一个部门,这个方法有漏洞,因为40号部门没有员工]  [外连接]技术引出: 右外连接,在等号的左边左外连接,在等号的右边[难点]:[自连接]显示老板的信息:[优化显示][再优化][难点]:查询每个部门的人数 [说明]本文实验基于以下数据表dept表; emp表; salgrade表; ______________________________________________________________________________ SQL select * from dept;DEPTNO DNAME                        LOC ---------- ---------------------------- --------------------------10 ACCOUNTING                   NEW YORK20 RESEARCH                     DALLAS30 SALES                        CHICAGO40 OPERATIONS                   BOSTON ______________________________________________________________________________SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      307566 JONES                MANAGER                  7839 02-4月 -81           2975                 207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      307876 ADAMS                CLERK                    7788 23-5月 -87           1100                 207900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 101 tom_abc                                                                 8000                    10已选择15行。 ______________________________________________________________________________SQL select * from salgrade;GRADE      LOSAL      HISAL ---------- ---------- ----------1        700       12002       1201       14003       1401       20004       2001       30005       3001       9999 ______________________________________________________________________________winsows安装好Oracle11g之后,实验基于[OracleServiceORCL]后台服务进程的开启:以超级用户方式登录C:\Users\VMwaresqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 24 22:03:26 2016 Copyright (c) 1982, 2010, Oracle.  All rights reserved.SQL connect /as sysdba 已连接。  SQL1,SQLplus 登陆 ORacle:winsows安装好Oracle11g之后修改scott的密码C:\Users\VMwaresqlplus请输入用户名:  scott 输入口令:tiger ERROR: ORA-28001: the password has expired 更改 scott 的口令 新口令: 重新键入新口令: 口令已更改连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options输入账户和密码直接登录C:\Users\VMwaresqlplus scott/11SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 24 22:00:50 2016Copyright (c) 1982, 2010, Oracle.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQLsqlplus 退出的方式C:\Users\VMwaresqlplus scott/11 SQL quit SQL exit查看用户之下有什么表tab是关键字 C:\Users\VMwaresqlplus scott/11 SQL select * from tab;TNAME                                                        TABTYPE ------------------------------------------------------------ --------------CLUSTERID ---------- BONUS                                                        TABLEDEPT                                                         TABLEEMP                                                          TABLETNAME                                                        TABTYPE ------------------------------------------------------------ --------------CLUSTERID ---------- SALGRADE                                                     TABLESQL查看表的所有记录不区分大小写SQL select * from dept;DEPTNO DNAME                        LOC ---------- ---------------------------- --------------------------10 ACCOUNTING                   NEW YORK20 RESEARCH                     DALLAS30 SALES                        CHICAGO40 OPERATIONS                   BOSTONSQL查看表结构SQL desc dept;名称                                      是否为空? 类型----------------------------------------- -------- ----------------------------DEPTNO                                    NOT NULL NUMBER(2)DNAME                                              VARCHAR2(14)LOC                                                VARCHAR2(13)SQL设置SQLplus行宽,页宽,列宽设置行宽前 SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE ---------- -------------------- ------------------ ---------- --------------SAL       COMM     DEPTNO ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80800                    207499 ALLEN                SALESMAN                 7698 20-2月 -811600        300         307521 WARD                 SALESMAN                 7698 22-2月 -811250        500         30EMPNO ENAME                JOB                       MGR HIREDATE ---------- -------------------- ------------------ ---------- --------------SAL       COMM     DEPTNO ---------- ---------- ----------7566 JONES                MANAGER                  7839 02-4月 -812975                    20 设置行宽后 SQL set linesize 999; SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      307566 JONES                MANAGER                  7839 02-4月 -81           2975                 207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      307876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择14行。SQL      设置页宽      SQL set pagesize 999; SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      307566 JONES                MANAGER                  7839 02-4月 -81           2975                 207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      307876 ADAMS                CLERK                    7788 23-5月 -87           1100                 207900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择14行。设置数字的列宽: oracle 9代表占位: SQL set linesize 199; SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      307566 JONES                MANAGER                  7839 02-4月 -81           2975                 207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      307876 ADAMS                CLERK                    7788 23-5月 -87           1100                 207900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择14行。SQL col empno for 9999999999999999999 SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO -------------------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         307566 JONES                MANAGER                  7839 02-4月 -81           2975                207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                307782 CLARK                MANAGER                  7839 09-6月 -81           2450                107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                207839 KING                 PRESIDENT                     17-11月-81           5000                107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         307876 ADAMS                CLERK                    7788 23-5月 -87           1100                207900 JAMES                CLERK                    7698 03-12月-81            950                307902 FORD                 ANALYST                  7566 03-12月-81           3000                207934 MILLER               CLERK                    7782 23-1月 -82           1300                10已选择14行。设置字符的列宽:60个字符的宽度 SQL col ename for a60; SQL select * from emp;EMPNO ENAME                                                        JOB                       MGR HIREDATE              SAL       COMM     DEPTNO -------------------- ------------------------------------------------------------ ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                                                        CLERK                    7902 17-12月-80            800                    207499 ALLEN                                                        SALESMAN                 7698 20-2月 -81           1600        300         307521 WARD                                                         SALESMAN                 7698 22-2月 -81           1250        500         307566 JONES                                                        MANAGER                  7839 02-4月 -81           2975                    207654 MARTIN                                                       SALESMAN                 7698 28-9月 -81           1250       1400         307698 BLAKE                                                        MANAGER                  7839 01-5月 -81           2850                    307782 CLARK                                                        MANAGER                  7839 09-6月 -81           2450                    107788 SCOTT                                                        ANALYST                  7566 19-4月 -87           3000                    207839 KING                                                         PRESIDENT                 17-11月-81       5000                    107844 TURNER                                                       SALESMAN                 7698 08-9月 -81           1500          0         307876 ADAMS                                                        CLERK                    7788 23-5月 -87           1100                    207900 JAMES                                                        CLERK                    7698 03-12月-81            950                    307902 FORD                                                         ANALYST                  7566 03-12月-81           3000                    207934 MILLER                                                       CLERK                    7782 23-1月 -82           1300                    10已选择14行。恢复,便于后面做实验: SQL col ename for a20; SQL col empno for 999999 SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      307566 JONES                MANAGER                  7839 02-4月 -81           2975                 207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      307876 ADAMS                CLERK                    7788 23-5月 -87           1100                 207900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择14行。清屏命令:SQL host clsselect 语法:select 参与运算语法:SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      307521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      307566 JONES                MANAGER                  7839 02-4月 -81           2975                 207654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      307698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      307876 ADAMS                CLERK                    7788 23-5月 -87           1100                 207900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 10 SQL select ename, empno, job, sal, sal*12 from emp;ENAME                  EMPNO JOB                       SAL     SAL*12 -------------------- ------- ------------------ ---------- ---------- SMITH                   7369 CLERK                     800       9600 ALLEN                   7499 SALESMAN                 1600      19200 WARD                    7521 SALESMAN                 1250      15000 JONES                   7566 MANAGER                  2975      35700 MARTIN                  7654 SALESMAN                 1250      15000 BLAKE                   7698 MANAGER                  2850      34200 CLARK                   7782 MANAGER                  2450      29400 SCOTT                   7788 ANALYST                  3000      36000 KING                    7839 PRESIDENT                5000      60000 TURNER                  7844 SALESMAN                 1500      18000 ADAMS                   7876 CLERK                    1100      13200 JAMES                   7900 CLERK                     950      11400 FORD                    7902 ANALYST                  3000      36000 MILLER                  7934 CLERK                    1300      15600select as 语法as 可以省略 冒号可以省略 别名之间不能有空格1,as别名的使用 SQL select ename as 员工编号, empno　编号, job 工作 , sal 月薪 , sal*12 年薪  from emp;员工编号                   编号 工作                     月薪       年薪 -------------------- ---------- ------------------ ---------- ---------- SMITH                      7369 CLERK                     800       9600 ALLEN                      7499 SALESMAN                 1600      19200 WARD                       7521 SALESMAN                 1250      15000 JONES                      7566 MANAGER                  2975      35700 MARTIN                     7654 SALESMAN                 1250      15000 BLAKE                      7698 MANAGER                  2850      34200 CLARK                      7782 MANAGER                  2450      29400 SCOTT                      7788 ANALYST                  3000      36000 KING                       7839 PRESIDENT                5000      60000 TURNER                     7844 SALESMAN                 1500      18000 ADAMS                      7876 CLERK                    1100      13200 JAMES                      7900 CLERK                     950      11400 FORD                       7902 ANALYST                  3000      36000 MILLER                     7934 CLERK                    1300      15600已选择14行。2,没有引号带有空格的别名,无法识别:SQL select ename as 员工编号, empno　编号, job 工作 , sal 月 薪 , sal*12 年薪  from emp; select ename as 员工编号, empno　编号, job 工作 , sal 月 薪 , sal*12 年薪  from emp* 第 1 行出现错误: ORA-00923: 未找到要求的 FROM 关键字3,带有空格的别名解决办法:SQL select ename as 员工编号, empno　编号, job 工作 , sal 月  薪 , sal*12 年薪  from emp;员工编号                   编号 工作                   月  薪       年薪 -------------------- ---------- ------------------ ---------- ---------- SMITH                      7369 CLERK                     800       9600 ALLEN                      7499 SALESMAN                 1600      19200 WARD                       7521 SALESMAN                 1250      15000 JONES                      7566 MANAGER                  2975      35700 MARTIN                     7654 SALESMAN                 1250      15000 BLAKE                      7698 MANAGER                  2850      34200 CLARK                      7782 MANAGER                  2450      29400 SCOTT                      7788 ANALYST                  3000      36000 KING                       7839 PRESIDENT                5000      60000 TURNER                     7844 SALESMAN                 1500      18000 ADAMS                      7876 CLERK                    1100      13200 JAMES                      7900 CLERK                     950      11400 FORD                       7902 ANALYST                  3000      36000 MILLER                     7934 CLERK                    1300      15600已选择14行。年收入抛出问题,年收入显示为空SQL select ename as 员工编号, empno　编号, job 工作 , sal 月  薪 , sal*12comm 年收入  from emp;员工编号                   编号 工作                   月  薪     年收入 -------------------- ---------- ------------------ ---------- ---------- SMITH                      7369 CLERK                     800 ALLEN                      7499 SALESMAN                 1600      19500 WARD                       7521 SALESMAN                 1250      15500 JONES                      7566 MANAGER                  2975 MARTIN                     7654 SALESMAN                 1250      16400 BLAKE                      7698 MANAGER                  2850 CLARK                      7782 MANAGER                  2450 SCOTT                      7788 ANALYST                  3000 KING                       7839 PRESIDENT                5000 TURNER                     7844 SALESMAN                 1500      18000 ADAMS                      7876 CLERK                    1100 JAMES                      7900 CLERK                     950 FORD                       7902 ANALYST                  3000 MILLER                     7934 CLERK                    13001,NULL空值 任何数与NULL运算都是NULL2,空值不是空 null ! null解决办法,nvl(a,b)函数,当a是null是返回b,b可以是数字/字符串SQL select ename,empno, job, sal, nvl(sal*12comm,0) from emp;ENAME                     EMPNO JOB                       SAL NVL(SAL*12COMM,0) -------------------- ---------- ------------------ ---------- ------------------ SMITH                      7369 CLERK                     800                  0 ALLEN                      7499 SALESMAN                 1600              19500 WARD                       7521 SALESMAN                 1250              15500 JONES                      7566 MANAGER                  2975                  0 MARTIN                     7654 SALESMAN                 1250              16400 BLAKE                      7698 MANAGER                  2850                  0 CLARK                      7782 MANAGER                  2450                  0 SCOTT                      7788 ANALYST                  3000                  0 KING                       7839 PRESIDENT                5000                  0 TURNER                     7844 SALESMAN                 1500              18000 ADAMS                      7876 CLERK                    1100                  0 JAMES                      7900 CLERK                     950                  0 FORD                       7902 ANALYST                  3000                  0 MILLER                     7934 CLERK                    1300                  0已选择14行。SQL select ename,empno, job, sal, nvl(sal*12comm,0) 年收入 from emp;ENAME                     EMPNO JOB                       SAL     年收入 -------------------- ---------- ------------------ ---------- ---------- SMITH                      7369 CLERK                     800          0 ALLEN                      7499 SALESMAN                 1600      19500 WARD                       7521 SALESMAN                 1250      15500 JONES                      7566 MANAGER                  2975          0 MARTIN                     7654 SALESMAN                 1250      16400 BLAKE                      7698 MANAGER                  2850          0 CLARK                      7782 MANAGER                  2450          0 SCOTT                      7788 ANALYST                  3000          0 KING                       7839 PRESIDENT                5000          0 TURNER                     7844 SALESMAN                 1500      18000 ADAMS                      7876 CLERK                    1100          0 JAMES                      7900 CLERK                     950          0 FORD                       7902 ANALYST                  3000          0 MILLER                     7934 CLERK                    1300          0已选择14行。SQL select ename,empno, job, sal, nvl(sal*12comm,0)as  年收入 from emp;ENAME                     EMPNO JOB                       SAL     年收入 -------------------- ---------- ------------------ ---------- ---------- SMITH                      7369 CLERK                     800          0 ALLEN                      7499 SALESMAN                 1600      19500 WARD                       7521 SALESMAN                 1250      15500 JONES                      7566 MANAGER                  2975          0 MARTIN                     7654 SALESMAN                 1250      16400 BLAKE                      7698 MANAGER                  2850          0 CLARK                      7782 MANAGER                  2450          0 SCOTT                      7788 ANALYST                  3000          0 KING                       7839 PRESIDENT                5000          0 TURNER                     7844 SALESMAN                 1500      18000 ADAMS                      7876 CLERK                    1100          0 JAMES                      7900 CLERK                     950          0 FORD                       7902 ANALYST                  3000          0 MILLER                     7934 CLERK                    1300          0已选择14行。ed修改上一条命令:保存退出,/开始执行SQL ed 已写入 file afiedt.buf1* select ename,empno, job, sal, nvl(sal*12comm,0)as  年 入 from emp SQL /ENAME                     EMPNO JOB                       SAL      年 入 -------------------- ---------- ------------------ ---------- ---------- SMITH                      7369 CLERK                     800          0 ALLEN                      7499 SALESMAN                 1600      19500 WARD                       7521 SALESMAN                 1250      15500 JONES                      7566 MANAGER                  2975          0 MARTIN                     7654 SALESMAN                 1250      16400 BLAKE                      7698 MANAGER                  2850          0 CLARK                      7782 MANAGER                  2450          0 SCOTT                      7788 ANALYST                  3000          0 KING                       7839 PRESIDENT                5000          0 TURNER                     7844 SALESMAN                 1500      18000 ADAMS                      7876 CLERK                    1100          0 JAMES                      7900 CLERK                     950          0 FORD                       7902 ANALYST                  3000          0 MILLER                     7934 CLERK                    1300          0已选择14行。where 过滤,查询奖金为空的员工:SQL select * from emp where comm is null;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 17-12月-80            800                 207566 JONES                MANAGER                  7839 02-4月 -81           2975                 207698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 307782 CLARK                MANAGER                  7839 09-6月 -81           2450                 107788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 207839 KING                 PRESIDENT                     17-11月-81           5000                 107876 ADAMS                CLERK                    7788 23-5月 -87           1100                 207900 JAMES                CLERK                    7698 03-12月-81            950                 307902 FORD                 ANALYST                  7566 03-12月-81           3000                 207934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择10行。||连接符,与虚表SQL select Hello  || World from emp;HELLO||WORLD ---------------------- Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World已选择14行。dual虚表:SQL select Hello  || World from dual;HELLO||WORLD ---------------------- Hello World利用伪表,显示系统时间SQL select sysdate from dual;SYSDATE -------------- 25-8月 -16DISTINCT 删除重复行,显示部门个数:SQL select DISTINCT deptno from emp;DEPTNO ----------302010DISTINCT修饰多个字段时,当两个数据完全一样才认为是重复的SQL select deptno,job from emp;DEPTNO JOB ---------- ------------------20 CLERK30 SALESMAN30 SALESMAN20 MANAGER30 SALESMAN30 MANAGER10 MANAGER20 ANALYST10 PRESIDENT30 SALESMAN20 CLERK30 CLERK20 ANALYST10 CLERK已选择14行。SQL select DISTINCT deptno,job from emp;DEPTNO JOB ---------- ------------------20 CLERK30 SALESMAN20 MANAGER30 CLERK10 PRESIDENT30 MANAGER10 CLERK10 MANAGER20 ANALYST已选择9行。SQLplus工具的 helpSQL help topicHelp is available on the following topics:/ACCEPT APPEND ARCHIVE LOG ATTRIBUTE BREAK BTITLE CHANGE CLEAR COLUMN COMPUTE CONNECT COPY DEFINE DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT GET HELP HOST INDEX INPUT LIST MENU PASSWORD PAUSE PRINT PROMPT QUIT RECOVER REMARK REPFOOTER REPHEADER RESERVED WORDS (PL/SQL) RESERVED WORDS (SQL) RUN SAVE SET SHOW SHUTDOWN SPOOL SQLPLUS START STARTUP STORE TIMING TOPICS TTITLE UNDEFINE VARIABLE WHENEVER OSERROR WHENEVER SQLERROR XQUERYSQLedit的帮助:SQL ? editEDIT----Invokes an operating system text editor on the contents of thespecified file or on the contents of the SQL buffer. The bufferhas no command history list and does not record SQL*Plus commands.ED[IT] [file_name[.ext]]SQLOracle系统常用的参数SQL set pagesize 299 SQL set linesize 299 SQL select * from v$nls_parameters;PARAMETER                         VALUE                                                                                        ----------------------------- -----------  NLS_LANGUAGE                      SIMPLIFIED CHINESE NLS_TERRITORY                     CHINA NLS_CURRENCY                       NLS_ISO_CURRENCY                  CHINA NLS_NUMERIC_CHARACTERS            ., NLS_CALENDAR                      GREGORIAN NLS_DATE_FORMAT                   DD-MON-RR NLS_DATE_LANGUAGE                 SIMPLIFIED CHINESE NLS_CHARACTERSET                  AL32UTF8 NLS_SORT                          BINARY NLS_TIME_FORMAT                   HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT              DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT                HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT           DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY                  NLS_NCHAR_CHARACTERSET            AL16UTF16 NLS_COMP                          BINARY NLS_LENGTH_SEMANTICS              BYTE NLS_NCHAR_CONV_EXCP               FALSE已选择19行。默认的时间显示SQL select sysdate from dual;SYSDATE -------------- 25-8月 -16临时修改系统时间的显示方式:SQL alter session set NLS_DATE_FORMATyyyy-mm-dd;会话已更改。SQL select sysdate from dual;SYSDATE ---------- 2016-08-25SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         307521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         307566 JONES                MANAGER                  7839 1981-04-02       2975                    207654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         307698 BLAKE                MANAGER                  7839 1981-05-01       2850                    307782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         307876 ADAMS                CLERK                    7788 1987-05-23       1100                    207900 JAMES                CLERK                    7698 1981-12-03        950                    307902 FORD                 ANALYST                  7566 1981-12-03       3000                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择14行。where过滤字句 大于:查询入职比80年1月1号晚的员工:日期格式是敏感的SQL select * from emp where hiredate  1980-01-01;EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         307521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         307566 JONES                MANAGER                  7839 1981-04-02       2975                    207654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         307698 BLAKE                MANAGER                  7839 1981-05-01       2850                    307782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         307876 ADAMS                CLERK                    7788 1987-05-23       1100                    207900 JAMES                CLERK                    7698 1981-12-03        950                    307902 FORD                 ANALYST                  7566 1981-12-03       3000                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择14行。where过滤字句between and,查询工资在1000 ~ 2000之间的SQL select * from emp where sal between 1000 and 2000;EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         307521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         307654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         307844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         307876 ADAMS                CLERK                    7788 1987-05-23       1100                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择6行。where过滤字句between A  and B,A 必须小于等于 BSQL select * from emp where sal between 9000 and 200;未选定行SQLwhere过滤字句 andSQL select * from emp where sal 9000 and sal  2000;EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7566 JONES                MANAGER                  7839 1981-04-02       2975                    207698 BLAKE                MANAGER                  7839 1981-05-01       2850                    307782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107902 FORD                 ANALYST                  7566 1981-12-03       3000                    20已选择6行。where过滤字句 inSQL select * from emp where deptno in (10,20);EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207566 JONES                MANAGER                  7839 1981-04-02       2975                    207782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107876 ADAMS                CLERK                    7788 1987-05-23       1100                    207902 FORD                 ANALYST                  7566 1981-12-03       3000                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择8行。where过滤字句 not inSQL select * from emp where deptno not in (10,20);EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         307521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         307654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         307698 BLAKE                MANAGER                  7839 1981-05-01       2850                    307844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         307900 JAMES                CLERK                    7698 1981-12-03        950                    30已选择6行。where过滤字句 not in 与null在一起,受影响SQL select * from emp where deptno in (10,20);EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207566 JONES                MANAGER                  7839 1981-04-02       2975                    207782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107876 ADAMS                CLERK                    7788 1987-05-23       1100                    207902 FORD                 ANALYST                  7566 1981-12-03       3000                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择8行。SQL select * from emp where deptno in (10,20,null);EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207566 JONES                MANAGER                  7839 1981-04-02       2975                    207782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107876 ADAMS                CLERK                    7788 1987-05-23       1100                    207902 FORD                 ANALYST                  7566 1981-12-03       3000                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择8行。SQL select * from emp where deptno not in (10,20,null);未选定行where like模糊查询,查询员工姓名S开头的信息:SQL select * from emp;EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         307521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         307566 JONES                MANAGER                  7839 1981-04-02       2975                    207654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         307698 BLAKE                MANAGER                  7839 1981-05-01       2850                    307782 CLARK                MANAGER                  7839 1981-06-09       2450                    107788 SCOTT                ANALYST                  7566 1987-04-19       3000                    207839 KING                 PRESIDENT                     1981-11-17       5000                    107844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         307876 ADAMS                CLERK                    7788 1987-05-23       1100                    207900 JAMES                CLERK                    7698 1981-12-03        950                    307902 FORD                 ANALYST                  7566 1981-12-03       3000                    207934 MILLER               CLERK                    7782 1982-01-23       1300                    10已选择14行。SQL select * from emp where ename like S%;EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH                CLERK                    7902 1980-12-17        800                    207788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20 转载于:https://blog.51cto.com/990487026/1843080
http://www.zqtcl.cn/news/633747/

相关文章：

  • 大学生网站作业北京网站优化技术
  • 静安区网站开发固原网络推广
  • WordPress网站修改志成网站设计制作
  • 做网站需要注意的昭通网站seo优化
  • 站群软件lanyun网站开发
  • 固始网站制作html美食网页设计源码
  • 软件研发过程管理岳阳seo
  • 舟山网站建设代理门户网站建设流程
  • 天水建设银行网站网站建设方案免费下载
  • 长城宽带魔方优化大师官网下载
  • 宁波建设工程主管部门网站长沙网站维护
  • 网站推广机构我的世界怎么做赞助网站
  • 做网站的公司图sae wordpress storage
  • 做塑料的网站有哪些东道设计公司待遇如何
  • 烟台做网站哪家好网站加速器下载
  • 哪些网站是响应式河北省住房和城乡建设厅信用网站
  • 彩票网站html模板新闻html网页设计代码范文
  • 建网站视频怎么建网站卖产品
  • 做翻糖的网站深圳做购物网站
  • 国外界面设计网站海淘网站
  • 全国住房城乡建设厅网站wordpress 宽版
  • 网站建设实训意见中国建设人才信息网站
  • 如何给网站做301跳转中国做机床的公司网站
  • 网站建设课程体系济南建站详情
  • jsp网站空间网站开发北京 广告 手机网站
  • 郑州网站建设推广爱站网seo综合查询工具
  • 2017年网站开发用什么语言手游排行榜
  • 鞍山百度网站怎么制作建设部网站建造师公示
  • 建设部网站业绩补录营销型网站制作的方法
  • 建设网站的功能及目的是什么意思兰州网络优化seo