【Oracle】第3天 数据补充

友情链接：Mysql建表规范

如何在PL/SQL中修改一个表的列的数据类型

右击表名→单击修改(edit)→选择“Columns”选项卡→直接修改想修改的地方→提交

数据类型

备份一张表：

CREATE TABLE TABLE_NAME AS SELECT * FROM TABLE_NAME;

查看自己备份的表格：

SELECT * FROM EMP2;

修改表格中的数据:

SELECT * FROM EMP2 FOR UPDATE;

然后手动修改数据最后提交

COMMIT:提交你对表中数据所做的修改
ROLLBACK:回滚，回退到前一步

删除一个表：

DROP TABLE TABLE_NAME;

字符串类型：

VARCHAR2 (N):N是指字符长度，剩余空间可释放，N一般为4000，变长
CHAR(N)：N是指字符长度，剩余空间不可释放，N一般为1~2000，定长，不足部分以空格填充

• 这里给ENAME列一个VARCHAR2（10），给JOB列一个CHAR（10）
  ↓
  

剩余空间可释放：当储存数据不足所设长度时，剩余空间可以用于存放其他数据

在oracle数据库中，‘’(空字符串)和NULL(空值)是等价的

数值类型：

NUMBER(总数字长度（可变），小数长度)

日期类型：

DATE()
YYYY 年 MM 月 DD 日

年月日缺省默认当年首月首日

TIMESTAMP()时间戳

聚合函数(分组函数)

SUM(COLUMN1)
AVG(COLUMN1)
MIN(COLUMN1)——可以处理字符型
MAX(COLUMN1)——可以处理字符型

应用：

COUNT():统计数据条数 
COUNT(1):扫描第1列，统计包含NULL的值的所有符合条件的字段的条数
COUNT(*):扫描所有的列，统计包含NULL的值的所有符合条件的字段的条数
COUNT(列名)：统计表格中除去NULL以外的所有符合条件的行的总数

练习：

查询EMP表中工资的最大值、最小值、平均值和总和，和部门编号
→

SELECT DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL) FROM EMP;--报错，因为DEPTNO不是单组分组

↑错误示范

注意： 单纯的列不能和分组函数 联合使用

数据处理函数

ROUND(参数一，精度)：将参数一四舍五入
TRUNC(参数一，精度)：将参数一截取

精度默认为0

应用:

SELECT ROUND(123.456,1) FROM DUAL;

→

SELECT ROUND(123.456,2) FROM DUAL;

→

SELECT ROUND(123.456,-1) FROM DUAL;

→

去重函数

DISTINCT

应用：
单列去重：

SELECT DISTINCT JOB FROM EMP;

多列去重：

SELECT DISTINCT JOB,SAL FROM EMP;

.

判断函数

语法：

CASE WHEN COMDITION1 THEN VALUE1 
WHEN COMDITION2 THEN VALUE2
WHEN COMDITION3 THEN VALUE3
ELSE VALUE4
END;

满足条件1，赋值VALUE1，不满足条件1却满足条件2的话，赋值VALUE 2，不满足条件2却满足条件3的话，赋值VALUE3，否则赋值VALUE4
——另起一列，并不是在原有的字段上做修改，一般写在SELECT后面作为一个字段（=列）展示

应用：

SELECT DEPTNO,CASE WHEN DEPTNO=10 THEN '10号部门'
WHEN DEPTNO=20 THEN '20号部门'
ELSE '30号部门' END "部门名称"
FROM EMP;

↓以下语句的效果同上↑

SELECT DEPTNO,CASE DEPTNO WHEN 10 THEN '10号部门'	
WHEN 20 THEN '20号部门'
ELSE '30号部门' END "部门名称" FROM EMP;

应用：给经理加薪其工资的20％，给董事长分红为其工资两倍，给销售加薪10％，给普通雇员降薪30％查询各个工位的员工的薪资以及员工编号，姓名
→

SELECT EMPNO,ENAME,CASE WHEN JOB='MANAGER' THEN 1.2*SAL
WHEN JOB='PRESIDENT' THEN 3*SAL
WHEN JOB='SALESMAN' THEN 1.1*SAL
WHEN JOB='CLERK' THEN 0.7*SAL
END
FROM EMP;

注意： 这里有一个逻辑上的易错点，给董事长分红为工资的两倍，所以这个地方SAL3而不是SAL2

应用:将1981入职的员工标注为老员工，1981年以前的员工标注为资深员工，其他标注为新员工查询员工 EMPNO, ENAME,SAL，新老员工情况
→

SELECT EMPNO,ENAME,SAL,CASE WHEN HIREDATE<TO_DATE('1981/01/01','YYYY/MM/DD') THEN '资深员工' 
WHEN HIREDATE>TO_DATE('1981/12/31','YYYY/MM/DD') THEN '新员工'
ELSE '老员工' END AS "新老员工情况"
FROM EMP;

↓以下语句的效果同上↑

SELECT EMPNO,ENAME,SAL,CASE WHEN TO_CHAR(HIREDATE,'YYYY')=1981 THEN '老员工' 
WHEN TO_CHAR(HIREDATE,'YYYY')>1981 THEN '新员工'
ELSE '资深员工' END AS "新老员工情况"
FROM EMP;

练习：
1.分别将10号部门中员工工资在1000以下的标注为低薪，1000-2000的标注为中等薪资，2000以上标注为高薪
→

SELECT ENAME,CASE WHEN SAL<1000 THEN '低薪'
WHEN SAL<=2000 THEN '中等薪资'
WHEN 2000<SAL THEN '高薪'
END FROM EMP WHERE DEPTNO=10;

↓以下语句的效果同上↑

SELECT ENAME,CASE WHEN SAL<1000 THEN '低薪'
WHEN SAL BETWEEN 1000 AND 2000 THEN '中等薪资'
WHEN 2000<=SAL THEN '高薪'
END FROM EMP WHERE DEPTNO=10;

2.将10号部门的员工标注为1,20号部门的员工标注为2,30号部门的员工标注为3
→

SELECT ENAME,CASE WHEN DEPTNO=10 THEN '1' 
WHEN DEPTNO=20 THEN '2' 
WHEN DEPTNO=30 THEN '3' 
END FROM EMP;

3.统计10,20,30号部门的员工人数
→

SELECT SUM(CASE WHEN DEPTNO=10 THEN 1 ELSE 0 END) "10号部门员工人数",SUM(CASE WHEN DEPTNO=20 THEN 1 ELSE 0 END) "20号部门员工人数",SUM(CASE WHEN DEPTNO=30 THEN 1 ELSE 0 END) "30号部门员工人数" FROM EMP;

字符函数

UPPER(字符串)：将括号内的字符大写
LOWER(字符串)：将括号内的字符小写

应用：将EMP表中S开头的员工姓名小写，并查询EMPNO, ENAME（小写以后的名字），SAL,JOB列信息
→

SELECT EMPNO,LOWER(ENAME),SAL,JOB FROM EMP WHERE ENAME LIKE 'S%';

练习：

1.分别查询emp表的工资列的最大最小平均值和工资和，dept表的最大最小部门编号以及 SALGRADE表的最高等级和最低等级
→

SELECT MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL) FROM EMP;
SELECT MAX(DEPTNO),MIN(DEPTNO) FROM DEPT;
SELECT MAX(GRADE),MIN(GRADE) FROM SALGRADE;

2.查询姓名以A开头的员工的工资和，以及最高最低工资，员工人数
→

SELECT SUM(SAL),MAX(SAL),MIN(SAL),COUNT(ENAME) FROM EMP WHERE ENAME LIKE 'A%';

3.统计姓名以S开头的员工的员工人数，工资和，以及最高最低平均工资
→

SELECT COUNT(ENAME),SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP WHERE ENAME LIKE 'S%';

4.统计姓名以K开头的员工的员工人数，工资和，以及最高最低平均工资
→

SELECT COUNT(ENAME),SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP WHERE ENAME LIKE 'K%';

5.查询姓名以A开头并且不包含%和_等特殊字符的员工的工资和，以及最高最低工资，员工人数
→

SELECT SUM(SAL),MAX(SAL),MIN(SAL),COUNT(ENAME) FROM EMP WHERE ENAME LIKE 'A%' AND ENAME NOT LIKE '%/%%' ESCAPE '/' AND ENAME NOT LIKE '%/_%' ESCAPE '/';

6.统计姓名以S开头的并且包含%和_或者工资高于1000的员工的员工人数，工资和，以及最高最低平均工资
→

SELECT COUNT(ENAME),SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP WHERE (ENAME LIKE 'S%' AND ENAME LIKE  '%/%%' ESCAPE '/' AND ENAME LIKE '%/_%' ESCAPE '/') OR SAL>1000;

7.统计姓名以K开头不以A结尾且是10或者20号部门的员工的员工人数，工资和，以及最高最低平均工资
→

SELECT COUNT(ENAME),SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP WHERE ENAME LIKE 'K%' AND ENAME NOT LIKE '%A' AND (DEPTNO=10 OR DEPTNO=20);