ETL复习--2020年3月24日--数据类型、数据增删改

--2020年3月24日 AM

1、DDL 数据定义语言
创建对象

 

1.1
Oracle数据类型主要有：字符串、数值、日期

mssql：float、medical、int（bight，int，smallint，tinyint）

char(18)：固定字长,例如性别、身份证号

varchar(10)：可变字长，按实际存储长度计算字长，可存空字符串

varchar2(10)：可变字长，不可存储空字符串，Oracle中常用

SQL中每个汉字占2-3个字节，英文、数值占1个字节，空格占空间，但不占字节

varchar2 和 nvarchar2 的区别：
varchar2 最多存放4000字节的数据，最多可以可以存入4000个字母，或最多存入2000个汉字
（数据库字符集编码是GBK时，varchar2最多能存放2000个汉字，数据库字符集编码是UTF-8时，那就最多只能存放1333个汉字）
nvarchar2（size），size最大值为2000，单位是字符，而且不管是汉字还是字母，每个字符的长度都是2个字节。
所以nvarchar2类型的数据最多能存放2000个汉字，也最多只能存放2000个字母。并且NVARCHAR2不受数据库字符集的影响。

1.2
字符串转换日期

select to_date('2020-02-02','yyyy-mm-dd') from dual;
select to_date('2020-02-02152526','yyyy-mm-ddhh24miss') from dual;

sqlsever对于时间分类较多:date smalldate time smalltime timestamp

day 也可表示星期
select to_char(hiredate,'day') from emp;

1.3 大对象类型

lob：用于存储大对象类型。例如文本信息长度超过4000、二进制文件等，
最大容量为4GB，
lob 分类：
clob：用于存储大型文本数据。(例如：备注信息)；
blob：用于存储二进制数据。(例如：图片文件)；
bfile：作为独立文件存在的二进制数据。

1.4
null：字符串与 is null、is not null 做判断

Oracle中没有布尔型，可利用字符串或数值 (1/0) 进行替代表示

1.5
建表

create table t_1 (
id number,
numid number,
name varchar2(30),
"sex" char(2),
--sqlsever中使用 [ ] 进行强制转换
fdate date
);

1.6
约束
1) 主键约束 (primary key [PK]) 不能重复，不能为 null。
联合主键 多个字段一同控制，不完全相同即不触发联合主键约束
2) 外键约束 (foreign key [FK]) 可以为 null。
3) 唯一约束 (unique [UK]) 不能重复的，但是可以为null。
4) 非空约束 (not null ) 只能在列级定义。
5) 检查约束 (check [CK]) (condition) ) 。

create table t_1 (
id number primary key,
numid number not null,
name varchar2(30) unique,
sex char(2) check (sex in ('男','女')),
fdate date
);

insert into t_1 values (1,1,1,'男',null);

1.7
修改表结构

alter table t_1 modify name not null;

drop table t_1;

练习题：
1.创建student表,要求列与emp表相同，EMPNO主键约束，ENAME非空约束，SAL检查约束大于零。

create table student (
EMPNO number primary key,
ENAME varchar2(30) not null,
JOB varchar2(30),
MGR varchar2(30),
HIREDATE date,
SAL number check (sal>0),
COMM number,
DEPTNO number
);

2.修改deptno列属性为字符串类型。

alter table student modify deptno varchar2(10);

3.删除emp表。

drop table emp;

--PM

2、DML 数据操作语言
行级处理

create table emp1 as select * from emp;

2.1 insert

insert into emp1 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7799, mike, uiou, 3211, null, 12320, null, 80);

insert into emp1 (EMPNO, ENAME)
values (7799, mike);

insert into emp1
values (7799, mike, uiou, 3211, null, 12320, null, 80);

insert into emp1 select * from emp;--插入结果集，Oracle中如此写

2.2 update

update emp1 set ename = 'xxx' where empno=7369;
--update基于select，select无结果，update也无法执行

update emp1 set ename = 'xxx',comm=950 where deptno=20;
--一条update语句可对一条数据的多个字段同时更新

2.3 delete

delete from emp1;--有事务控制，可回滚找回

delete from emp1 where comm is null;

truncate table emp1;--较delete更为直接，无法回滚找回，但耗时有优势

练习题：
1.列出至少有一个员工的部门编号和员工人数。

select deptno,count(ename) from emp group by deptno having count(ename)>=1;

2.列出最低薪金大于1500的所有工作。

select job from emp where sal>1500;

5.列出在每个部门工作的员工数量、平均工资。

select deptno,count(ename),avg(sal) from emp group by deptno;

6.列出各个部门的MANAGER（经理）的最低薪金。

select deptno,min(sal) from emp where job='MANAGER' group by deptno;

7.列出所有员工的年工资,按年薪从低到高排序。

select ename,sal*12 from emp order by 2;

8.查询出emp表中职位是经理（MANAGER），部门平均薪水在2000以上的部门编号和员工人数。

select deptno,count(ename) from emp where job='MANAGER' group by deptno having avg(sal)>2000;

9.查询部门总工资在4000以上的部门最高和最低薪水。

select deptno,max(sal),min(sal),sum(sal) from emp group by deptno having sum(sal)>4000;

10.查询部门20的员工，每个月的工资总和及平均工资。

select ename,sum(sal),avg(sal) from emp where deptno=20 group by ename;

11.查询每个部门每个岗位的工资总和。

select deptno,job,sum(sal) from emp group by deptno,job;

12.查询部门人数大于2的部门编号，最低工资、最高工资,部门人数。

select deptno,min(sal),max(sal),count(ename) from emp group by deptno having count(ename)>2

13.查询部门平均工资大于2000，且人数大于2的部门编号，部门人数，部门平均工资，并按照部门人数升序排序。

select deptno,count(ename),avg(sal) from emp group by deptno having count(ename)>2 and avg(sal)>2000 order by count(ename);

14.查询部门平均工资在2500元以上的部门编号及平均工资。

select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;

15.查询员工岗位中平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select job,avg(sal) from emp group by job having avg(sal)>2500 order by avg(sal) desc;

16.查询岗位不为SALESMAN，工资和大于等于2500的岗位及 每种岗位的工资和。

select job,sum(sal) from emp where job!='SALESMAN' group by job having sum(sal)>=2500;

17.写一个查询，显示每个部门最高工资和最低工资的差额。

select max(sal)-min(sal) from emp group by deptno;

18.求出每个部门中相同职位的员工人数 和平均工资。

select deptno,count(job),avg(sal) from emp group by deptno,job having count(job)>1;