sql系列(基础篇)-第二章 约束和排序数据
更好的看↑代码点击VIEW PLAN
第二章 约束和排序数据 1. 在 emp 表中选择工资介于 1500 到 2500 的员工的信息; 注意:使用 between 下边界 and 上边界时,条件包括边界值; SCOTT@ORCL>l 1 select * from emp 2* where sal between 1500 and 2500 SCOTT@ORCL>/ EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 2. 在emp表中选择位于20,30 部门的员工的信息; SCOTT@ORCL>select *from emp 2 where deptno in (20,30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 11 rows selected. 3. 在emp表中选择位于员工的名字中包含大写字符 ‘A’ 的员工的信息; SCOTT@ORCL>select * from emp 2 where ename like '%A%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7 rows selected. 注意:如果查询的名字中包含%或者_ ,而且查询的时候又要查询这样的信息,需要用到换位码。 注意:通配符%,表示0或者多个字符一样;通配符_,表示1个字符一样; 3.1 创建与 emp 表结构相同的表; SCOTT@ORCL>create table emp_n /* 参照emp表创建新表emp_n */ 2 as select * from emp where 1=2; /* 加where 1=2 一致表结构没有数据 */ Table created. SCOTT@ORCL>select * from emp_n; no rows selected 3.2 添加包含通配符的测试用数据; SCOTT@ORCL>insert into emp_n(empno,ename,sal) values(&empno,&ename,&sal); Enter value for empno: 1001 Enter value for ename: 'whwh%gogo' Enter value for sal: 1000 old 1: insert into emp_n(empno,ename,sal) values(&empno,&ename,&sal) new 1: insert into emp_n(empno,ename,sal) values(1001,'whwh%gogo',1000) 1 row created. SCOTT@ORCL>/ Enter value for empno: 1002 Enter value for ename: '%whwh' Enter value for sal: 2000 old 1: insert into emp_n(empno,ename,sal) values(&empno,&ename,&sal) new 1: insert into emp_n(empno,ename,sal) values(1002,'%whwh',2000) 1 row created. SCOTT@ORCL>/ Enter value for empno: 1003 Enter value for ename: 'whwh_gogo' Enter value for sal: 3000 old 1: insert into emp_n(empno,ename,sal) values(&empno,&ename,&sal) new 1: insert into emp_n(empno,ename,sal) values(1003,'whwh_gogo',3000) 1 row created. SCOTT@ORCL>/ Enter value for empno: 1004 Enter value for ename: '_gogo' Enter value for sal: 4000 old 1: insert into emp_n(empno,ename,sal) values(&empno,&ename,&sal) new 1: insert into emp_n(empno,ename,sal) values(1004,'_gogo',4000) 1 row created. SCOTT@ORCL>commit; Commit complete. SCOTT@ORCL>select * from emp_n; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1001 whwh%gogo 1000 1002 %whwh 2000 1003 whwh_gogo 3000 1004 _gogo 4000 3.3 换位码的使用方法;(此处以\作为换位码,换位码还可以指定其他字符) 例:检索包含%的记录信息; SCOTT@ORCL>select * from emp_n 2 where ename like '%\%%' escape '\'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1001 whwh%gogo 1000 1002 %whwh 2000 SCOTT@ORCL>select * from emp_n 2 where ename like '%\%%'; no rows selected 例:检索以%开头的记录信息; SCOTT@ORCL>select * from emp_n 2 where ename like '\%%' escape '\'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1002 %whwh 2000 例:检索包含_的记录信息; SCOTT@ORCL>select * from emp_n 2 where ename like '%\_%' escape '\'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1003 whwh_gogo 3000 1004 _gogo 4000 例:检索以_开头的记录信息; SCOTT@ORCL>select * from emp_n 2 where ename like '\_%' escape '\'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1004 _gogo 4000 4 复合条件的使用 4.1 对于and条件的复合(可以将between...and...进行转换) 例:在emp表中选择工资介于2000到3000的员工的信息; SCOTT@ORCL>select * from emp 2 where sal>=2000 and sal<=3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 SCOTT@ORCL>select * from emp 2 where sal between 2000 and 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 4.2 对于or条件复合(可以将in()进行转换) 例:在emp表中选择10号和20号部门的员工信息; SCOTT@ORCL>select * from emp 2 where deptno=10 or deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 8 rows selected. SCOTT@ORCL>select * from emp 2 where deptno in (10,20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 8 rows selected. 5 对于表中数据的排序 5.1 asc 表示按照所给字段进行升序排列(默认升序) desc 表示按照所给字段进行降序排列 例:将emp表中10号部门的员工信息按照sal列升序排列 SCOTT@ORCL>select * from emp 2 where deptno=10; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 SCOTT@ORCL>select * from emp 2 where deptno=10 3 order by sal asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 SCOTT@ORCL>select * from emp 2 where deptno=10 3 order by sal; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 例:将emp表中20号部门的员工信息按照sal列降序排列 SCOTT@ORCL>select * from emp 2 where deptno=20 3 order by sal desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 5.2 如果order by 后面跟多个字段,则将结果集先按照第1个字段进行排序【条件1】, 再按第2个字段进行排序【条件2】; 注意:【条件1】如果按照第1个字段分不开先后顺序的时候,才会按照第2个字段排序; asc 或者desc 影响的字段,仅仅是它紧挨着的那个字段; 例:将emp表中10号部门的员工信息按sal降序排列,empno升序排列; SCOTT@ORCL>select * from emp 2 where deptno=10 3 order by sal desc,empno asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 例:将emp表中20号部门的员工信息按照empno 降序排列,sal升序排列; SCOTT@ORCL>select * from emp 2 where deptno=20 3 order by empno desc,sal asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 5.3 除了可以使用字段排序,还可以使用字段所在的先后位置排序; 确定字段在表中的先后顺序; SCOTT@ORCL>set lines 100 SCOTT@ORCL>desc emp; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ 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) 例:将emp表中10号部门的员工信息按sal降序排列,empno升序排列; SCOTT@ORCL>select * from emp 2 where deptno=10 3 order by 6 desc,1 asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 例:将emp表中20号部门的员工信息按照empno 降序排列,sal升序排列; SCOTT@ORCL>select * from emp 2 where deptno=20 3 order by 1 desc,6 asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 5.4 除了可以使用number 类型的字段进行排序外,还可以使用字符串或者时间类型的字段进行排序; 注意:字符串排序:按照字符对应的ASCII码的先后进行排序; 日期排序:按照日期的先后进行排序,时间越往后越大; 例:将emp表中员工按照job升序、ename降序进行排列;(先按Job排序,同样的job按ename降序) SCOTT@ORCL>select * from emp 2 order by job asc,ename desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7900 JAMES CLERK 7698 03-DEC-81 950 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 14 rows selected. 例:将emp表中员工按照 HIREDATE 升序,sal降序排列(如果HIREDATE相同,按照sal降序) 先将HIREDATE显示格式设定 SCOTT@ORCL>alter session set nls_date_format='yyyy-mm-dd'; Session altered. SCOTT@ORCL>select * from emp 2 order by hiredate asc,sal desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7839 KING PRESIDENT 1981-11-17 5000 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7934 MILLER CLERK 7782 1982-01-23 1300 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7876 ADAMS CLERK 7788 1987-05-23 1100 20 14 rows selected. 6. 使用结果集中的别名进行排序; 例:按照emp表中员工对于的年薪(sal*12)进行排序; SCOTT@ORCL>select empno,ename,hiredate,sal*12 year_sal 2 from emp 3 order by year_sal desc; EMPNO ENAME HIREDATE YEAR_SAL ---------- ---------- ---------- ---------- 7839 KING 1981-11-17 60000 7902 FORD 1981-12-03 36000 7788 SCOTT 1987-04-19 36000 7566 JONES 1981-04-02 35700 7698 BLAKE 1981-05-01 34200 7782 CLARK 1981-06-09 29400 7499 ALLEN 1981-02-20 19200 7844 TURNER 1981-09-08 18000 7934 MILLER 1982-01-23 15600 7521 WARD 1981-02-22 15000 7654 MARTIN 1981-09-28 15000 7876 ADAMS 1987-05-23 13200 7900 JAMES 1981-12-03 11400 7369 SMITH 1980-12-17 9600 14 rows selected. 注意:当然也可以按照字符串、日期对应的别名进行排序; 7. 如果排序的字段中包含null值,结果会怎么样? 注意:在字段进行比较大小的时候,null 值比任何值都大; 例:emp表中员工的信息按照comm降序排列; SCOTT@ORCL>select * from emp 2 order by comm desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7566 JONES MANAGER 7839 1981-04-02 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7934 MILLER CLERK 7782 1982-01-23 1300 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 14 rows selected. 注意:null 值之间的排序不考虑(因为Null 和 null 之间无法比较大小) 如果我想将comm有值的部分换到上面,该怎么办? SCOTT@ORCL>select * from emp 2 order by comm desc nulls last; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7369 SMITH CLERK 7902 1980-12-17 800 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10 14 rows selected. 同理:自己验证 升序 排列 SCOTT@ORCL>select * from emp 2 order by comm asc nulls first; </span>
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。