尧图网站建设 
DATE1981-12-03TO_DATE(1981-12-03,YYYY-MM-DD)TO_DATE(2005-01-01 13:30:00,YYYY-MM-DD HH24:MI:SS) 24 小时制COMM IS NULLCOMM IS NOT NULLSALNVL(COMM,0)SELECT * FROM EMP WHERE HIREDATE TO_DATE(12031981,MMDDYYYY)SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,YYYYMMDD) 19811203SELECT EMPNO,ENAME,HIREDATE,TO_CHAR(HIREDATE,YYYY年MM月DD日) FROM EMPSELECT * FROM EMPWHERE JOB IN(ANALYST,MANAGER,PRESIDENT)-- 简单建表语法CREATE TABLE 表名 (字段1 数据类型1(长度),字段2 数据类型2(长度),字段3.............);-- EMP表的建表语句案例重点无论表名或字段名不能以数字开头且不能带有除下划线以外的特殊符号CREATE TABLE EMP(EMPNO NUMBER(4) , 默认为最长38位ENAME VARCHAR2(10), 必须指定长度,且最长为4000自动长度JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE, 不能指定长度SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2),PHONE CHAR(13) 必须指定长度且最长为2000少于指定长度的字符使用空格代替定长长度。);CHAR 类型的查询效率比 VARCHAR2 类型要高INSERT INTO USER20260707(U_ID,UNAME) VALUES(1002,李四);DROP TABLE USER20260707;SELECT JOB,AVG(SAL),COUNT(*)FROM EMPGROUP BY JOB -- 先分组HAVING AVG(SAL) 1500; -- 后过滤复制表(查询已有的表结构和数据来建表) ASCREATE TABLE EMP_TEST1027A ASSELECT * FROM EMP;复制表结构不含数据CREATE TABLE EMP_TEST1027B ASSELECT * FROM EMP WHERE 12; -- 设置了没有任何数据符合的条件复制表的部分字段和数据,复制EMP表中10和20的员工号、员工姓名、部门编号CREATE TABLE EMP_TEST1027C ASSELECT EMPNO,ENAME,DEPTNOFROM EMPWHERE DEPTNO 10 OR DEPTNO 20;使用查询结果建表时可以给字段重新起名CREATE TABLE EMP_TEST1027D ASSELECT EMPNO AS 员工号,ENAME AS 姓名,DEPTNO AS 部门编号FROM EMPWHERE DEPTNO 10 OR DEPTNO 20;添加列ALTER TABLE 表名 ADD 列名 数据类型[长度]--示例ALTER TABLE USER221 ADD AGE NUMBER(3);修改列类型ALTER TABLE 表名 MODIFY 列名 数据类型[长度]修改列名ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名ALTER TABLE USER221 RENAME COLUMN UNAME TO 用户名;删除列ALTER TABLE 表名 DROP COLUMN 列名--注意如果列中有数据会一并删除且不可回滚ALTER TABLE USER221 DROP COLUMN BDATE;修改表名ALTER TABLE 表名 RENAME TO 新表名ALTER TABLE USER221 RENAME TO 用户表221;插入数据语句INSERT INTO 表名(指定字段) VALUES(所有字段对应的值);INSERT INTO USER221 VALUES(1,李秀光,NULL,NULL,18);INSERT INTO USER221(UNAME,U_ID) VALUES(何仙姑,5);插入指定的表INSERT INTO 表名 SELECT 查询语句INSERT INTO EMP_TEST1027BSELECT * FROM EMP WHERE DEPTNO 10;INSERT INTO EMP_TEST1027B(EMPNO,ENAME,SAL)SELECT EMPNO AS AAA,JOB,SAL FROM EMP WHERE DEPTNO10;修改数据语句UPDATEUPDATE 表名 SET 修改的字段 值 --修改某字段的所有值UPDATE 表名 SET 修改的字段 值 WHERE 修改数据的条件 --按条件修改某个字段的值UPDATE 表名 SET 修改的字段1 值1,修改的字段2 值2 --同时修改多个字段的值关联SELECT 查询的字段FROM 表1 AINNER JOIN 表2 B --INNER可以省略ON A.关联字段B.关联字段AND/WHERE 过滤;SELECT ENAME,SAL,E.DEPTNO,D.DEPTNO,DNAMEFROM EMP EINNER JOIN DEPT DON E.DEPTNO D.DEPTNOSELECT ENAME,SAL,E.DEPTNO,DNAME,LOCFROM EMP EINNER JOIN DEPT DON E.DEPTNO D.DEPTNO --14AND D.DEPTNO 10 --3--内关联使用AND和WHERE过滤结果是一致的左外关联/左关联 保留主表所有数据以及从表能够关联上的数据从表关联不上补空标准SQL语法SELECT 查询字段FROM 表1 A --主表LEFT JOIN 表2 B --从表ON A.关联字段B.关联字段AND/WHERE 过滤;--示例使用左关联关联EMP和DEPT表SELECT *FROM EMP ELEFT JOIN DEPT DON E.DEPTNO D.DEPTNO --14AND只过滤从表数据WHERE是过滤主从表所有数据假设有两张表A表和B表A表有个ID字段值是 1~6B表有个ID字段值是3~9问题A JOIN B ON A.IDB.ID 有_4_条数据3 4 5 6A LEFT JOIN B ON A.IDB.ID 有_6_条数据1 2 3 4 5 6A LEFT JOIN B ON A.IDB.ID AND A.ID3 有_6_条数据1 2 3 4 5 6A LEFT JOIN B ON A.IDB.ID WHERE A.ID3 有_3_条数据4 5 6A RIGHT JOIN B ON A.IDB.ID 有_7_条数据3 4 5 6 7 8 9A RIGHT JOIN B ON A.IDB.ID AND B.ID6 有_7_条数据3 4 5 6 7 8 9A FULL JOIN B ON A.IDB.ID 有_9_条数据1 2 3 4 5 6 7 8 9A FULL JOIN B ON A.IDB.ID WHERE B.ID5 有_4_条数据6 7 8 9②右外关联/右关联标准SQL语法SELECT 查询字段FROM 表1 A --从表RIGHT JOIN 表2 B --主表ON A.关联字段B.关联字段AND/WHERE 过滤;SELECT *FROM EMP ERIGHT JOIN DEPT DON E.DEPTNO D.DEPTNO --15全外关联保留两表的所有数据关联不上互相补空值标准SQL语法SELECT 查询字段FROM 表1 AFULL JOIN 表2 BON A.关联字段B.关联字段WHERE 过滤;SELECT NVL(A.USER_NAME,B.USER_NAME) USER_NAME,NVL(A.AMOUNT,0)NVL(B.AMOUNT,0) AMOUNTFROM T_CUNKUAN_A AFULL JOIN T_CUNKUAN_B BON A.USER_NAME B.USER_NAME集合UNION ALL(并集)返回各个查询的所有记录包括重复记录。UNION(并集)返回各个查询的所有记录不包括重复记录。INTERSECT(交集)返回两个查询共有的记录。MINUS(补集)返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。--ORACLE场景判断----CASE WHEN语法CASE WHEN 条件1THEN 输出值1WHEN 条件2THEN 输出值2...ELSE 输出值N --ELSE不写其他情况默认补空值END 别名--输出部门代号10号代号120号部门代号230号部门代号3SELECT DEPTNO,CASE WHEN DEPTNO 10 THEN 1WHEN DEPTNO 20 THEN 2-- WHEN DEPTNO 30 THEN 3-- ELSE 4END 部门代号FROM EMP--DECODE()语法DECODE(字段,判断值1,输出值1,判断值2,输出值2,...,输出值N)--输出值N表示其他条件下的输出值不写默认输出空值--示例使用DECODE()将EMP表英文岗位转换成中文岗位SELECT ENAME,JOB,CASE WHEN JOB CLERK THEN 文员WHEN JOB SALESMAN THEN 销售WHEN JOB MANAGER THEN 经理ELSE END 中文工作FROM EMPSELECT ENAME,JOB,DECODE(JOB,CLERK,文员,SALESMAN,销售,MANAGER,经理,) 中文工作FROM EMP--注意DECODE只能适用于等值判断--小练习查询EMP表每一年的入职人数--输出格式如下1980 1981 1982 19871 10 1 2--使用CASE WHENSELECT SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1980 THEN 1 END) 1980,SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1981 THEN 1 END) 1981,SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1982 THEN 1 END) 1982,SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1987 THEN 1 END) 1987FROM EMP--使用DECODESELECT SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1980,1)) 1980,SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1981,1)) 1981,SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1982,1)) 1982,SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1987,1)) 1987FROM EMP--别名不能以数字开头不能使用特殊符号*-,以数字开头或者有特殊符号可以加双引号--分析函数ORACLE函数分为系统函数和自定义函数系统函数又分为单行函数、聚合函数、分析函数/开窗函数单行函数只能输出明细聚合函数只能输出聚合后的值分析函数也叫开窗函数保留明细数据的同时还可以输出聚合后的值--聚合型分析函数①SUM()OVER([PARTITION BY ...][ORDER BY ...]) --PARTITION BY 表示分组--SUM()OVER(ORDER BY ...) 三级形态 --加了ORDER BY,结果会累计求和SELECT ENAME,SAL,SUM(SAL)OVER(ORDER BY SAL)FROM EMP--SUM()OVER(PARTITION BY ... ORDER BY ...) 终极形态SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) 求和FROM EMP②AVG()OVER([PARTITION BY ... ORDER BY ...])--小练习查询每个员工跟部门平均薪资的差值--输出员工姓名、薪资、部门平均薪资、差值SELECT ENAME,SAL,AVG(SAL)OVER(PARTITION BY DEPTNO) 部门平均薪资,SAL-AVG(SAL)OVER(PARTITION BY DEPTNO) 差值FROM EMP③MAX/MIN()OVER([PARTITION BY ... ORDER BY...])--小练习查询每个员工跟部门最高薪资和最低薪资的差值--输出员工姓名、薪资、部门最高薪资、与最高薪资差值、部门最低薪资、与最低薪资差值SELECT ENAME,SAL,MAX(SAL)OVER(PARTITION BY DEPTNO) 部门最高薪资,SAL-MAX(SAL)OVER(PARTITION BY DEPTNO) 与最高薪资差值,MIN(SAL)OVER(PARTITION BY DEPTNO) 部门最低薪资,SAL-MIN(SAL)OVER(PARTITION BY DEPTNO) 与最低薪资差值FROM EMP④COUNT()OVER([PARTITION BY ... ORDER BY ...])--查询每个部门的人数以及按照入职时间的累计人数SELECT DEPTNO,ENAME,HIREDATE,COUNT(*)OVER(PARTITION BY DEPTNO) 每个部门的人数,COUNT(*)OVER(ORDER BY HIREDATE) 入职时间的累计人数FROM EMP--查询员工姓名、入职日期、每一年的累计人数SELECT ENAME,HIREDATE,COUNT(*)OVER(PARTITION BY TO_CHAR(HIREDATE,YYYY) ORDER BY HIREDATE) 每一年的累计人数FROM EMP--一般使用了分析函数的分组PARTITION BY ,就不会再使用GROUP BY--非聚合型分析函数①RATIO_TO_REPORT()OVER([PARTITION BY ...]) --占比函数--示例查询员工占对应部门的薪资占比SELECT ENAME,DEPTNO,SAL,CASE WHEN SUM(SAL)OVER(PARTITION BY DEPTNO) 0 THEN 0ELSE SAL/SUM(SAL)OVER(PARTITION BY DEPTNO)END 占比函数1 --报错除数为0,RATIO_TO_REPORT(SAL)OVER(PARTITION BY DEPTNO) 占比函数2FROM EMP--占比函数可以避免除数为0的报错情况②FIRST_VALUE()OVER([PARTITION BY ...][ORDER BY ...]) --取第一个值--示例(不用分析函数)查询每个部门薪资最高的员工姓名SELECT ENAME,SAL,MAX_SALFROM EMP ELEFT JOIN (SELECT DEPTNO,MAX(SAL) MAX_SALFROM EMPGROUP BY DEPTNO)FON E.DEPTNO F.DEPTNOWHERE SAL MAX_SALSELECT DISTINCT FIRST_VALUE(ENAME)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 员工姓名FROM EMP--分析函数结果往往会有重复所以一般会对结果再次去重③NTILE()OVER([PARTITION BY ...][ORDER BY ...]) --切片函数现有一张手机型号价格表手机型号、价格请根据手机的价格排名来定位手机的级别若手机价格排前30%则是高端机、若手机价格排在40%-70%则是中端机后30%输出低端机;输出字段手机型号、级别SELECT 手机型号,CASE WHEN NT 3 THEN 高端机WHEN NT 7 THEN 中端机ELSE 低端机END 级别FROM (SELECT 手机型号,价格,NTILE(10)OVER(ORDER BY 价格 DESC) NTFROM 手机型号价格表)④WM_CONCAT()OVER([PARTITION BY ...][ORDER BY ...])SELECT WM_CONCAT(ENAME)OVER() FROM EMP; --返回14行拼接结果SELECT WM_CONCAT(ENAME)OVER(ORDER BY SAL) FROM EMP; --累计拼接SELECT E.*,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO) FROM EMP E; --按照部门分组拼接SELECT E.*,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP E; --按部门分组累计⑤排名函数重点ROW_NUMBER()OVER() --不考虑并列 1 2 3 4RANK()OVER() --考虑并列并空出排名 1 2 2 4DENSE_RANK()OVER --考虑并列不空出排名 1 2 2 3--查询每个部门每个工作薪资排名前2的员工信息SELECT *FROM(SELECT ENAME,SAL,DEPTNO,JOB,ROW_NUMBER()OVER(PARTITION BY DEPTNO,JOB ORDER BY SAL DESC) RN1FROM emp)WHERE RN1 3⑥偏移函数LAG(X,Y,Z)OVER([PARTITION BY ...][ORDER BY ...]) --向上偏移,X表示偏移的目标字段Y表示偏移量Z表示取不到时的默认值LEAD(X,Y,Z)OVER([PARTITION BY ...][ORDER BY ...]) --向下偏移--查询每个员工对应的上一个入职的员工SELECT ENAME,HIREDATE,LAG(ENAME,2,)OVER(ORDER BY HIREDATE) 上一个入职的员工FROM EMP--查询每个部门每个员工对应的下一个入职的员工SELECT ENAME,HIREDATE,LEAD(ENAME,1,)OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) 下一个入职的员工FROM EMP--行列转换--①行转列将多行少列的数据转成少行多列CREATE TABLE T_SCORE(NAME VARCHAR2(10),COURSE VARCHAR2(10),SCORE NUMBER);SELECT * FROM T_SCORE;--使用CASE WHEN进行行转列SELECT NAME,SUM(CASE WHEN COURSE 语文 THEN SCORE END) 语文,SUM(CASE WHEN COURSE 数学 THEN SCORE END) 数学,SUM(CASE WHEN COURSE 英语 THEN SCORE END) 英语FROM T_SCOREGROUP BY NAME--使用PIVOT函数语法SELECT * FROM 表 PIVOT(SUM(指标字段) FOR 待拆解的列 IN(值1 字段1,值2 字段2,...));SELECT * FROM T_SCORE PIVOT(SUM(SCORE) FOR COURSE IN(语文 语文,数学 数学,英语 英语));②列转行将少行多列的数据转换成多行少列CREATE TABLE T_SCORE_1 ASSELECT NAME,SUM(CASE WHEN COURSE 语文 THEN SCORE END) 语文,SUM(CASE WHEN COURSE 数学 THEN SCORE END) 数学,SUM(CASE WHEN COURSE 英语 THEN SCORE END) 英语FROM T_SCOREGROUP BY NAMESELECT * FROM T_SCORE_1--使用UNPIVOT语法SELECT * FROM 表 UNPIVOT(指标字段 FOR 待合并的列 IN(列1,列2,...));SELECT * FROM T_SCORE_1 UNPIVOT(SCORE FOR COURSE IN(语文,数学,英语))MINUS A-BNAME COURSE SCORE张三 语文 98张三 数学 99张三 英语 97李四 语文 88李四 数学 89李四 英语 92--使用UNION ALLUNION ALL 表示合并多个结果集--示例SELECT NAME,语文 COURSE,语文 SCORE FROM T_SCORE_1UNION ALLSELECT NAME,数学 COURSE,数学 SCORE FROM T_SCORE_1UNION ALLSELECT NAME,英语 COURSE,英语 SCORE FROM T_SCORE_1SELECT NAME,语文 COURSE,语文 SCORE FROM T_SCORE_1UNION ALLSELECT NAME,数学,数学 FROM T_SCORE_1UNION ALLSELECT NAME,英语,英语 FROM T_SCORE_1--使用UNION ALL做列转行--连续登录问题有张登录表T_LOG,有USER_NAME和LOGIN_DATE字段: CREATE TABLE T_LOG(USER_NAME VARCHAR2(10),LOGIN_DATE DATE)先要求查出每个用户的最大连续登录天数输出结果USER_NAME LOGIN_DATE张三 4李四 3SELECT USER_NAME,MAX(CT)FROM (SELECT USER_NAME,COUNT(*) CTFROM (SELECT USER_NAME,LOGIN_DATE,LOGIN_DATE - ROW_NUMBER()OVER(PARTITION BY USER_NAME ORDER BY LOGIN_DATE) R1FROM T_LOG)GROUP BY USER_NAME,R1)GROUP BY USER_NAME;--连续登录的思路将表中日期字段减去一个连续的数字如果日期连续减出来的值相等然后再按照--减出来的字段进行分组计数此时就能够得到每个用户所有连续登录天数最大连续登录天数最后按照用户--分组求最大值即可一般连续数字用ROW_NUMBER()来构造;--使用WITH AS语法WITH T1 AS(SELECT USER_NAME,LOGIN_DATE,LOGIN_DATE - ROW_NUMBER()OVER(PARTITION BY USER_NAME ORDER BY LOGIN_DATE) RN1FROM T_LOG),T2 AS (SELECT USER_NAME,COUNT(*) CNFROM T1GROUP BY USER_NAME,RN1)SELECT USER_NAME,MAX(CN)FROM T2GROUP BY USER_NAME--ORACLE伪列--伪表DUAL --虚拟表只有一行一列一般用于满足SELECT语法规则伪列ROWNUM、ROWID--①ROWNUM:返回查询结果的行号一般用于分页查询SELECT E.*,ROWNUMFROM EMP E--示例查询EMP表前五行数据SELECT E.*,ROWNUMFROM EMP EWHERE ROWNUM 5--示例查询EMP表5-10条数据SELECT E.*,ROWNUMFROM EMP EWHERE ROWNUM BETWEEN 5 AND 10--ROWNUM不能用进行比较只能用小于或者小于等于--小练习查询薪资排名在第5-10的员工信息;SELECT *FROM(SELECT T.*,ROWNUM RNFROM (SELECT E.*FROM EMP EORDER BY SAL DESC)T)WHERE RN BETWEEN 5 AND 10--②ROWID 返回表中每一行数据的物理地址每一行是唯一的一般用于去重或者删除重复数据CREATE TABLE EMP_BAK6 AS SELECT * FROM EMPINSERT INTO EMP_BAK6 SELECT * FROM EMP_BAK6--使用ROWID去重TRUNCATE TABLE EMP_BAK6;--删除重复数据DELETE FROM EMP_BAK6WHERE ROWID NOT IN (SELECT MIN(ROWID) --MAXFROM EMP_BAK6GROUP BY EMPNO --主键)------ 递归查询 /树状查询--------------递归从顶层到下一层级,一层一层递归去找。递归里面有一个很重要的关键字,LEVEL -- 伪列关键字代表树形结构中的层级编号SELECT level,a.*FROM 表名 aWHERE 条件1START WITH 条件2 --设置起点用来限制第一层的数据或者叫根节点数据CONNECT BY PRIOR 条件3 --用来指明在查找数据时以怎样的一种关系去查找SELECT * FROM EMPprior 表示上一层级的标识符。经常用来对下一层级的数据进行限制。不可以接伪列。PRIOR在等号前面和后面查询的数据是不一样的SELECT * FROM emp---PRIOR 侧的字段是 EMPNO就是往下属寻找------往 子项 找---------------------------示例我要找KING这个员工的所有下属SELECT e.*,LEVELFROM emp eSTART WITH enameKINGCONNECT BY PRIOR empnomgr;------------往 父项 找---------------------示例查找7876员工的所有领导SELECT e.*,LEVELFROM emp eSTART WITH empno7876CONNECT BY empno PRIOR mgr;---练习1找出 BLAKE 的 直系 下属有哪些SELECT *FROM(SELECT e.*,LEVEL LVFROM emp eSTART WITH ENAMEJONESCONNECT BY PRIOR empno mgr)WHERE LV 2;-----练习:生成一组连续的日期输出最近10天的日期SELECT SYSDATE,LEVEL,TO_CHAR(SYSDATE,YYYYMMDD)-LEVEL1FROM DUALCONNECT BY LEVEL 10 Oracle数据库3（结构搭配）
DATE1981-12-03TO_DATE(1981-12-03,YYYY-MM-DD)TO_DATE(2005-01-01 13:30:00,YYYY-MM-DD HH24:MI:SS) 24 小时制COMM IS NULLCOMM IS NOT NULLSALNVL(COMM,0)SELECT * FROM EMP WHERE HIREDATE TO_DATE(12031981,MMDDYYYY)SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,YYYYMMDD) 19811203SELECT EMPNO,ENAME,HIREDATE,TO_CHAR(HIREDATE,YYYY年MM月DD日) FROM EMPSELECT * FROM EMPWHERE JOB IN(ANALYST,MANAGER,PRESIDENT)-- 简单建表语法CREATE TABLE 表名 (字段1 数据类型1(长度),字段2 数据类型2(长度),字段3.............);-- EMP表的建表语句案例重点无论表名或字段名不能以数字开头且不能带有除下划线以外的特殊符号CREATE TABLE EMP(EMPNO NUMBER(4) , 默认为最长38位ENAME VARCHAR2(10), 必须指定长度,且最长为4000自动长度JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE, 不能指定长度SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2),PHONE CHAR(13) 必须指定长度且最长为2000少于指定长度的字符使用空格代替定长长度。);CHAR 类型的查询效率比 VARCHAR2 类型要高INSERT INTO USER20260707(U_ID,UNAME) VALUES(1002,李四);DROP TABLE USER20260707;SELECT JOB,AVG(SAL),COUNT(*)FROM EMPGROUP BY JOB -- 先分组HAVING AVG(SAL) 1500; -- 后过滤复制表(查询已有的表结构和数据来建表) ASCREATE TABLE EMP_TEST1027A ASSELECT * FROM EMP;复制表结构不含数据CREATE TABLE EMP_TEST1027B ASSELECT * FROM EMP WHERE 12; -- 设置了没有任何数据符合的条件复制表的部分字段和数据,复制EMP表中10和20的员工号、员工姓名、部门编号CREATE TABLE EMP_TEST1027C ASSELECT EMPNO,ENAME,DEPTNOFROM EMPWHERE DEPTNO 10 OR DEPTNO 20;使用查询结果建表时可以给字段重新起名CREATE TABLE EMP_TEST1027D ASSELECT EMPNO AS 员工号,ENAME AS 姓名,DEPTNO AS 部门编号FROM EMPWHERE DEPTNO 10 OR DEPTNO 20;添加列ALTER TABLE 表名 ADD 列名 数据类型[长度]--示例ALTER TABLE USER221 ADD AGE NUMBER(3);修改列类型ALTER TABLE 表名 MODIFY 列名 数据类型[长度]修改列名ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名ALTER TABLE USER221 RENAME COLUMN UNAME TO 用户名;删除列ALTER TABLE 表名 DROP COLUMN 列名--注意如果列中有数据会一并删除且不可回滚ALTER TABLE USER221 DROP COLUMN BDATE;修改表名ALTER TABLE 表名 RENAME TO 新表名ALTER TABLE USER221 RENAME TO 用户表221;插入数据语句INSERT INTO 表名(指定字段) VALUES(所有字段对应的值);INSERT INTO USER221 VALUES(1,李秀光,NULL,NULL,18);INSERT INTO USER221(UNAME,U_ID) VALUES(何仙姑,5);插入指定的表INSERT INTO 表名 SELECT 查询语句INSERT INTO EMP_TEST1027BSELECT * FROM EMP WHERE DEPTNO 10;INSERT INTO EMP_TEST1027B(EMPNO,ENAME,SAL)SELECT EMPNO AS AAA,JOB,SAL FROM EMP WHERE DEPTNO10;修改数据语句UPDATEUPDATE 表名 SET 修改的字段 值 --修改某字段的所有值UPDATE 表名 SET 修改的字段 值 WHERE 修改数据的条件 --按条件修改某个字段的值UPDATE 表名 SET 修改的字段1 值1,修改的字段2 值2 --同时修改多个字段的值关联SELECT 查询的字段FROM 表1 AINNER JOIN 表2 B --INNER可以省略ON A.关联字段B.关联字段AND/WHERE 过滤;SELECT ENAME,SAL,E.DEPTNO,D.DEPTNO,DNAMEFROM EMP EINNER JOIN DEPT DON E.DEPTNO D.DEPTNOSELECT ENAME,SAL,E.DEPTNO,DNAME,LOCFROM EMP EINNER JOIN DEPT DON E.DEPTNO D.DEPTNO --14AND D.DEPTNO 10 --3--内关联使用AND和WHERE过滤结果是一致的左外关联/左关联 保留主表所有数据以及从表能够关联上的数据从表关联不上补空标准SQL语法SELECT 查询字段FROM 表1 A --主表LEFT JOIN 表2 B --从表ON A.关联字段B.关联字段AND/WHERE 过滤;--示例使用左关联关联EMP和DEPT表SELECT *FROM EMP ELEFT JOIN DEPT DON E.DEPTNO D.DEPTNO --14AND只过滤从表数据WHERE是过滤主从表所有数据假设有两张表A表和B表A表有个ID字段值是 1~6B表有个ID字段值是3~9问题A JOIN B ON A.IDB.ID 有_4_条数据3 4 5 6A LEFT JOIN B ON A.IDB.ID 有_6_条数据1 2 3 4 5 6A LEFT JOIN B ON A.IDB.ID AND A.ID3 有_6_条数据1 2 3 4 5 6A LEFT JOIN B ON A.IDB.ID WHERE A.ID3 有_3_条数据4 5 6A RIGHT JOIN B ON A.IDB.ID 有_7_条数据3 4 5 6 7 8 9A RIGHT JOIN B ON A.IDB.ID AND B.ID6 有_7_条数据3 4 5 6 7 8 9A FULL JOIN B ON A.IDB.ID 有_9_条数据1 2 3 4 5 6 7 8 9A FULL JOIN B ON A.IDB.ID WHERE B.ID5 有_4_条数据6 7 8 9②右外关联/右关联标准SQL语法SELECT 查询字段FROM 表1 A --从表RIGHT JOIN 表2 B --主表ON A.关联字段B.关联字段AND/WHERE 过滤;SELECT *FROM EMP ERIGHT JOIN DEPT DON E.DEPTNO D.DEPTNO --15全外关联保留两表的所有数据关联不上互相补空值标准SQL语法SELECT 查询字段FROM 表1 AFULL JOIN 表2 BON A.关联字段B.关联字段WHERE 过滤;SELECT NVL(A.USER_NAME,B.USER_NAME) USER_NAME,NVL(A.AMOUNT,0)NVL(B.AMOUNT,0) AMOUNTFROM T_CUNKUAN_A AFULL JOIN T_CUNKUAN_B BON A.USER_NAME B.USER_NAME集合UNION ALL(并集)返回各个查询的所有记录包括重复记录。UNION(并集)返回各个查询的所有记录不包括重复记录。INTERSECT(交集)返回两个查询共有的记录。MINUS(补集)返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。--ORACLE场景判断----CASE WHEN语法CASE WHEN 条件1THEN 输出值1WHEN 条件2THEN 输出值2...ELSE 输出值N --ELSE不写其他情况默认补空值END 别名--输出部门代号10号代号120号部门代号230号部门代号3SELECT DEPTNO,CASE WHEN DEPTNO 10 THEN 1WHEN DEPTNO 20 THEN 2-- WHEN DEPTNO 30 THEN 3-- ELSE 4END 部门代号FROM EMP--DECODE()语法DECODE(字段,判断值1,输出值1,判断值2,输出值2,...,输出值N)--输出值N表示其他条件下的输出值不写默认输出空值--示例使用DECODE()将EMP表英文岗位转换成中文岗位SELECT ENAME,JOB,CASE WHEN JOB CLERK THEN 文员WHEN JOB SALESMAN THEN 销售WHEN JOB MANAGER THEN 经理ELSE END 中文工作FROM EMPSELECT ENAME,JOB,DECODE(JOB,CLERK,文员,SALESMAN,销售,MANAGER,经理,) 中文工作FROM EMP--注意DECODE只能适用于等值判断--小练习查询EMP表每一年的入职人数--输出格式如下1980 1981 1982 19871 10 1 2--使用CASE WHENSELECT SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1980 THEN 1 END) 1980,SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1981 THEN 1 END) 1981,SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1982 THEN 1 END) 1982,SUM(CASE WHEN TO_CHAR(HIREDATE,YYYY)1987 THEN 1 END) 1987FROM EMP--使用DECODESELECT SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1980,1)) 1980,SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1981,1)) 1981,SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1982,1)) 1982,SUM(DECODE(TO_CHAR(HIREDATE,YYYY),1987,1)) 1987FROM EMP--别名不能以数字开头不能使用特殊符号*-,以数字开头或者有特殊符号可以加双引号--分析函数ORACLE函数分为系统函数和自定义函数系统函数又分为单行函数、聚合函数、分析函数/开窗函数单行函数只能输出明细聚合函数只能输出聚合后的值分析函数也叫开窗函数保留明细数据的同时还可以输出聚合后的值--聚合型分析函数①SUM()OVER([PARTITION BY ...][ORDER BY ...]) --PARTITION BY 表示分组--SUM()OVER(ORDER BY ...) 三级形态 --加了ORDER BY,结果会累计求和SELECT ENAME,SAL,SUM(SAL)OVER(ORDER BY SAL)FROM EMP--SUM()OVER(PARTITION BY ... ORDER BY ...) 终极形态SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) 求和FROM EMP②AVG()OVER([PARTITION BY ... ORDER BY ...])--小练习查询每个员工跟部门平均薪资的差值--输出员工姓名、薪资、部门平均薪资、差值SELECT ENAME,SAL,AVG(SAL)OVER(PARTITION BY DEPTNO) 部门平均薪资,SAL-AVG(SAL)OVER(PARTITION BY DEPTNO) 差值FROM EMP③MAX/MIN()OVER([PARTITION BY ... ORDER BY...])--小练习查询每个员工跟部门最高薪资和最低薪资的差值--输出员工姓名、薪资、部门最高薪资、与最高薪资差值、部门最低薪资、与最低薪资差值SELECT ENAME,SAL,MAX(SAL)OVER(PARTITION BY DEPTNO) 部门最高薪资,SAL-MAX(SAL)OVER(PARTITION BY DEPTNO) 与最高薪资差值,MIN(SAL)OVER(PARTITION BY DEPTNO) 部门最低薪资,SAL-MIN(SAL)OVER(PARTITION BY DEPTNO) 与最低薪资差值FROM EMP④COUNT()OVER([PARTITION BY ... ORDER BY ...])--查询每个部门的人数以及按照入职时间的累计人数SELECT DEPTNO,ENAME,HIREDATE,COUNT(*)OVER(PARTITION BY DEPTNO) 每个部门的人数,COUNT(*)OVER(ORDER BY HIREDATE) 入职时间的累计人数FROM EMP--查询员工姓名、入职日期、每一年的累计人数SELECT ENAME,HIREDATE,COUNT(*)OVER(PARTITION BY TO_CHAR(HIREDATE,YYYY) ORDER BY HIREDATE) 每一年的累计人数FROM EMP--一般使用了分析函数的分组PARTITION BY ,就不会再使用GROUP BY--非聚合型分析函数①RATIO_TO_REPORT()OVER([PARTITION BY ...]) --占比函数--示例查询员工占对应部门的薪资占比SELECT ENAME,DEPTNO,SAL,CASE WHEN SUM(SAL)OVER(PARTITION BY DEPTNO) 0 THEN 0ELSE SAL/SUM(SAL)OVER(PARTITION BY DEPTNO)END 占比函数1 --报错除数为0,RATIO_TO_REPORT(SAL)OVER(PARTITION BY DEPTNO) 占比函数2FROM EMP--占比函数可以避免除数为0的报错情况②FIRST_VALUE()OVER([PARTITION BY ...][ORDER BY ...]) --取第一个值--示例(不用分析函数)查询每个部门薪资最高的员工姓名SELECT ENAME,SAL,MAX_SALFROM EMP ELEFT JOIN (SELECT DEPTNO,MAX(SAL) MAX_SALFROM EMPGROUP BY DEPTNO)FON E.DEPTNO F.DEPTNOWHERE SAL MAX_SALSELECT DISTINCT FIRST_VALUE(ENAME)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 员工姓名FROM EMP--分析函数结果往往会有重复所以一般会对结果再次去重③NTILE()OVER([PARTITION BY ...][ORDER BY ...]) --切片函数现有一张手机型号价格表手机型号、价格请根据手机的价格排名来定位手机的级别若手机价格排前30%则是高端机、若手机价格排在40%-70%则是中端机后30%输出低端机;输出字段手机型号、级别SELECT 手机型号,CASE WHEN NT 3 THEN 高端机WHEN NT 7 THEN 中端机ELSE 低端机END 级别FROM (SELECT 手机型号,价格,NTILE(10)OVER(ORDER BY 价格 DESC) NTFROM 手机型号价格表)④WM_CONCAT()OVER([PARTITION BY ...][ORDER BY ...])SELECT WM_CONCAT(ENAME)OVER() FROM EMP; --返回14行拼接结果SELECT WM_CONCAT(ENAME)OVER(ORDER BY SAL) FROM EMP; --累计拼接SELECT E.*,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO) FROM EMP E; --按照部门分组拼接SELECT E.*,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP E; --按部门分组累计⑤排名函数重点ROW_NUMBER()OVER() --不考虑并列 1 2 3 4RANK()OVER() --考虑并列并空出排名 1 2 2 4DENSE_RANK()OVER --考虑并列不空出排名 1 2 2 3--查询每个部门每个工作薪资排名前2的员工信息SELECT *FROM(SELECT ENAME,SAL,DEPTNO,JOB,ROW_NUMBER()OVER(PARTITION BY DEPTNO,JOB ORDER BY SAL DESC) RN1FROM emp)WHERE RN1 3⑥偏移函数LAG(X,Y,Z)OVER([PARTITION BY ...][ORDER BY ...]) --向上偏移,X表示偏移的目标字段Y表示偏移量Z表示取不到时的默认值LEAD(X,Y,Z)OVER([PARTITION BY ...][ORDER BY ...]) --向下偏移--查询每个员工对应的上一个入职的员工SELECT ENAME,HIREDATE,LAG(ENAME,2,)OVER(ORDER BY HIREDATE) 上一个入职的员工FROM EMP--查询每个部门每个员工对应的下一个入职的员工SELECT ENAME,HIREDATE,LEAD(ENAME,1,)OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) 下一个入职的员工FROM EMP--行列转换--①行转列将多行少列的数据转成少行多列CREATE TABLE T_SCORE(NAME VARCHAR2(10),COURSE VARCHAR2(10),SCORE NUMBER);SELECT * FROM T_SCORE;--使用CASE WHEN进行行转列SELECT NAME,SUM(CASE WHEN COURSE 语文 THEN SCORE END) 语文,SUM(CASE WHEN COURSE 数学 THEN SCORE END) 数学,SUM(CASE WHEN COURSE 英语 THEN SCORE END) 英语FROM T_SCOREGROUP BY NAME--使用PIVOT函数语法SELECT * FROM 表 PIVOT(SUM(指标字段) FOR 待拆解的列 IN(值1 字段1,值2 字段2,...));SELECT * FROM T_SCORE PIVOT(SUM(SCORE) FOR COURSE IN(语文 语文,数学 数学,英语 英语));②列转行将少行多列的数据转换成多行少列CREATE TABLE T_SCORE_1 ASSELECT NAME,SUM(CASE WHEN COURSE 语文 THEN SCORE END) 语文,SUM(CASE WHEN COURSE 数学 THEN SCORE END) 数学,SUM(CASE WHEN COURSE 英语 THEN SCORE END) 英语FROM T_SCOREGROUP BY NAMESELECT * FROM T_SCORE_1--使用UNPIVOT语法SELECT * FROM 表 UNPIVOT(指标字段 FOR 待合并的列 IN(列1,列2,...));SELECT * FROM T_SCORE_1 UNPIVOT(SCORE FOR COURSE IN(语文,数学,英语))MINUS A-BNAME COURSE SCORE张三 语文 98张三 数学 99张三 英语 97李四 语文 88李四 数学 89李四 英语 92--使用UNION ALLUNION ALL 表示合并多个结果集--示例SELECT NAME,语文 COURSE,语文 SCORE FROM T_SCORE_1UNION ALLSELECT NAME,数学 COURSE,数学 SCORE FROM T_SCORE_1UNION ALLSELECT NAME,英语 COURSE,英语 SCORE FROM T_SCORE_1SELECT NAME,语文 COURSE,语文 SCORE FROM T_SCORE_1UNION ALLSELECT NAME,数学,数学 FROM T_SCORE_1UNION ALLSELECT NAME,英语,英语 FROM T_SCORE_1--使用UNION ALL做列转行--连续登录问题有张登录表T_LOG,有USER_NAME和LOGIN_DATE字段: CREATE TABLE T_LOG(USER_NAME VARCHAR2(10),LOGIN_DATE DATE)先要求查出每个用户的最大连续登录天数输出结果USER_NAME LOGIN_DATE张三 4李四 3SELECT USER_NAME,MAX(CT)FROM (SELECT USER_NAME,COUNT(*) CTFROM (SELECT USER_NAME,LOGIN_DATE,LOGIN_DATE - ROW_NUMBER()OVER(PARTITION BY USER_NAME ORDER BY LOGIN_DATE) R1FROM T_LOG)GROUP BY USER_NAME,R1)GROUP BY USER_NAME;--连续登录的思路将表中日期字段减去一个连续的数字如果日期连续减出来的值相等然后再按照--减出来的字段进行分组计数此时就能够得到每个用户所有连续登录天数最大连续登录天数最后按照用户--分组求最大值即可一般连续数字用ROW_NUMBER()来构造;--使用WITH AS语法WITH T1 AS(SELECT USER_NAME,LOGIN_DATE,LOGIN_DATE - ROW_NUMBER()OVER(PARTITION BY USER_NAME ORDER BY LOGIN_DATE) RN1FROM T_LOG),T2 AS (SELECT USER_NAME,COUNT(*) CNFROM T1GROUP BY USER_NAME,RN1)SELECT USER_NAME,MAX(CN)FROM T2GROUP BY USER_NAME--ORACLE伪列--伪表DUAL --虚拟表只有一行一列一般用于满足SELECT语法规则伪列ROWNUM、ROWID--①ROWNUM:返回查询结果的行号一般用于分页查询SELECT E.*,ROWNUMFROM EMP E--示例查询EMP表前五行数据SELECT E.*,ROWNUMFROM EMP EWHERE ROWNUM 5--示例查询EMP表5-10条数据SELECT E.*,ROWNUMFROM EMP EWHERE ROWNUM BETWEEN 5 AND 10--ROWNUM不能用进行比较只能用小于或者小于等于--小练习查询薪资排名在第5-10的员工信息;SELECT *FROM(SELECT T.*,ROWNUM RNFROM (SELECT E.*FROM EMP EORDER BY SAL DESC)T)WHERE RN BETWEEN 5 AND 10--②ROWID 返回表中每一行数据的物理地址每一行是唯一的一般用于去重或者删除重复数据CREATE TABLE EMP_BAK6 AS SELECT * FROM EMPINSERT INTO EMP_BAK6 SELECT * FROM EMP_BAK6--使用ROWID去重TRUNCATE TABLE EMP_BAK6;--删除重复数据DELETE FROM EMP_BAK6WHERE ROWID NOT IN (SELECT MIN(ROWID) --MAXFROM EMP_BAK6GROUP BY EMPNO --主键)------ 递归查询 /树状查询--------------递归从顶层到下一层级,一层一层递归去找。递归里面有一个很重要的关键字,LEVEL -- 伪列关键字代表树形结构中的层级编号SELECT level,a.*FROM 表名 aWHERE 条件1START WITH 条件2 --设置起点用来限制第一层的数据或者叫根节点数据CONNECT BY PRIOR 条件3 --用来指明在查找数据时以怎样的一种关系去查找SELECT * FROM EMPprior 表示上一层级的标识符。经常用来对下一层级的数据进行限制。不可以接伪列。PRIOR在等号前面和后面查询的数据是不一样的SELECT * FROM emp---PRIOR 侧的字段是 EMPNO就是往下属寻找------往 子项 找---------------------------示例我要找KING这个员工的所有下属SELECT e.*,LEVELFROM emp eSTART WITH enameKINGCONNECT BY PRIOR empnomgr;------------往 父项 找---------------------示例查找7876员工的所有领导SELECT e.*,LEVELFROM emp eSTART WITH empno7876CONNECT BY empno PRIOR mgr;---练习1找出 BLAKE 的 直系 下属有哪些SELECT *FROM(SELECT e.*,LEVEL LVFROM emp eSTART WITH ENAMEJONESCONNECT BY PRIOR empno mgr)WHERE LV 2;-----练习:生成一组连续的日期输出最近10天的日期SELECT SYSDATE,LEVEL,TO_CHAR(SYSDATE,YYYYMMDD)-LEVEL1FROM DUALCONNECT BY LEVEL 10