108---经典oraclecrud语句

1. dense_rank() over([partition bycol1] order by col2) :1,2,2,3

 dense_rank() 是连续排序，两个第二名仍然跟着第三名

 查询每门功课前三名：select name,score from (selectname,score,dense_rank() over(partition by name order by score desc) tt from t)x where x.tt<=3;

select name,score,dense_rank() over(partition by name order by score) ttfrom t;

2.: rank() over([partition by col1] order by col2) :1,2,2,4

 rank()       是跳跃拍学，两个第二名下来就是第四名

语文成绩70分的同学是排名第几：selectname,score,x.tt from (select name,score,rank() over(partition by name order byscore desc) tt from t) x where x.name='语文' andx.score=70;

 select name,score,rank() over(order by score) tt from t;

 

3.row_number()over([partition by col1] order by col2) 

  row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页

分页查询：select xx.* from (selectt.*,row_number() over(order by score desc) rowno from t) xx where xx.rownobetween 1 and 3;

select name,score,row_number() over(partition by name order by score) ttfrom t;

4.使用分析函数对员工表进行查询

SELECT * 
FROM 
(SELECT employee.* ,
        DENSE_RANK( ) OVER (PARTITION BY deptno ORDER BYsal DESC) "DENSE_RANK"
 FROM employee) e
WHERE DENSE_RANK=2;

5.查询员工表中薪水从高到低排序的第5~9条记录

SELECT * 
FROM (SELECT e.*,rownum rn 
      FROM (SELECT * 
                 FROM employee
                 ORDER BY sal DESC
          ) e
     )  
WHERE rn>=5 AND rn<=9;

6. 创建员工表employee

CREATE TABLEemployee  /*-创建员工信息表-*/
(EMPNO NUMBER(4), --员工编号
ENAME VARCHAR2(10),          --员工姓名
JOB VARCHAR2(9),             --员工工种
MGR NUMBER(4),               --上级经理编号
HIREDATE DATE,               --受雇日期
SAL NUMBER(7,2),             --员工薪水
COMM NUMBER(7,2),            --福利
DEPTNO NUMBER(2)           --部门编号
); 

7.插入数据

INSERT INTOEMPLOYEE VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-2005','dd-mm-yyyy'),5800,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-2006','dd-mm-yyyy'),6600,300,30);
INSERT INTO EMPLOYEE VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-2006','dd-mm-yyyy'),6250,500,30);
INSERT INTO EMPLOYEE VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-2006','dd-mm-yyyy'),7975,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-2006','dd-mm-yyyy'),6250,1400,30);
INSERT INTO EMPLOYEE VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-2006','dd-mm-yyyy'),7850,NULL,30);
INSERT INTO EMPLOYEE VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-2006','dd-mm-yyyy'),7450,NULL,10);
INSERT INTO EMPLOYEE VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7-2012','dd-mm-yyyy'),8000,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-2006','dd-mm-yyyy'),10000,NULL,10);
INSERT INTO EMPLOYEE VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-2006','dd-mm-yyyy'),6500,0,30);
INSERT INTO EMPLOYEE VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-7-2012','dd-mm-yyyy'),7100,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-2006','dd-mm-yyyy'),5950,NULL,30);
INSERT INTO EMPLOYEE VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-2006','dd-mm-yyyy'),5000,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-2007','dd-mm-yyyy'),6300,NULL,10);

8.添加约束

ALTER TABLEemployee
ADD CONSTRAINT PK_empno PRIMARY KEY (empno);


ALTER TABLE employee
   ADD CONSTRAINT FK_deptno          
     FOREIGN KEY(deptno) REFERENCES dept(deptno);

9.向employee 表添加empTel_no 和empAddress 两列

ALTER TABLEemployee
ADD (empTel_no VARCHAR2 (12),
empAddress VARCHAR2(20));

10.删除empTel_no 和empAddress 两列

ALTER TABLE employee DROP (empTel_no,empAddress);  --删除empTel_no和empAddress两列

11.按照薪水从高到低显示数据 

SELECT * FROM employee ORDER BY sal DESC;

12.使用日期函数对员工表进行查询

SELECT ename "姓名",to_char(ROUND(hiredate,'yyyy'),'YYYY')||'年度'  "入职年度" 
  FROM employee;

13.列出至少有一个雇员的所有部门

SELECT DISTINCT deptno
  FROM employee;

14.列出薪金比"SMITH"多的所有雇员

SELECT ename,sal
  FROM employee
 WHERE sal>(SELECT sal FROM employee WHERE UPPER(ename)='SMITH');

15.列出所有“CLERK”（办事员）的姓名及其部门名称

SELECT ename AS 雇员姓名,dname AS 部门名称 
  FROM dept,employee
  WHERE dept.deptno=employee.deptno
    AND UPPER(employee.job)='CLERK';

16.列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

SELECT job AS 工作 , MIN(sal) AS 最低薪金
  FROM employee
 GROUP BY job
HAVING MIN(sal) >1500;

17.找出各月最后一天受雇的所有雇员

SELECT * 
  FROM employee
 WHERE hiredate=last_day(hiredate);

18.在订单表（Orders）中找出总定价在1万到10万之间的订单号、顾客姓氏（Customers表的cust_last_name列）和客户经理名称（Employee表的ename列）。

SELECT order_id,cust_last_name,ename，account_mgr_id
  FROM orders o INNER JOIN customers c
    ON o.customer_id=c.customer_id
 INNER JOIN employee e
    ON c.account_mgr_id=e.empno
 WHERE order_total BETWEEN 10000 AND 100000;

19.在顾客表（Customers）中找出所在地域为AMERICA的客户经理名称（Employee表的ename列）和薪水（employee表的sal列）。

SELECT ename,sal 
  FROM employee
 WHERE empno IN(SELECT account_mgr_id
                  FROM customers
                 WHEREnls_territory='AMERICA');

21.在顾客表（Customers）中找出所在地域为AMERICA、ITALY、INDIA和CHINA的客户编号及语言。

SELECT customer_id,nls_language
  FROM customers
 WHERE nls_territory IN ('AMERICA','ITALY','INDIA','CHINA');

 

21.在顾客表（Customers）中找出姓氏首字母为”F”的客户编号和邮箱。


SELECT customer_id,cust_email 
  FROM customers
 WHERE cust_last_name LIKE 'F%';


22.查出所有客户姓名和所下的订单编号。


SELECT cust_last_name||' '||cust_last_name,order_id
  FROM customers c LEFT JOIN orders o 
    ON o.customer_id=c.customer_id

23.根据员工表（Employee表）编写一个语句，只有当最低工资少于 5000 而且最高工资超过15000时，才显示部门 ID 以及该部门支付的最低工资和最高工资。

SELECT deptno,MAX(sal),MIN(sal) 
  FROM employee
 GROUP BY deptno
HAVING MAX(sal)>15000 AND MIN(sal)<5000;

24.根据员工表（Employee表）编写一个语句，显示各部门的每个工作类别中支付的最高工资

SELECT deptno,job,MAX(sal)
  FROM employee
 GROUP BY deptno,job
 ORDER BY deptno,job;

;