12.29笔记及作业

笔记

—插入数据
—日期使用当前系统时间函数sysdate
insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(7500, ‘ALLENN’, ‘SALESMAN’, 7900, sysdate, 1900.00, 300.00, 30);
—日期格式转换
insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(8500,’YUCHEN’,’SALESMAN’,7900,to_date(‘2020/12/29’, ‘yyyy-mm-dd’),1900.00,300.00,30);
—复制emp表为emp_backup，将emp表中所有数据插入到emp_backup表中
—如果需要某些条件的数据，则可以通过指出列名和列位
insert into emp_backup —所有的列
select —所有的列
from emp;
—更新emp_backup表中的数据
update emp_backup set ename=’chen’ where ename=’HUAMINI’;
—对emp_backup操作 rollback 后得到的只有表结构，emp全部被拷入的数据被清除，无法撤回
delete from emp_backup;
—drop删除表的数据及表的空间,无法撤会
drop table emp_backup;
—创建STUDENT表
create table STUDENT
(SNO VARCHAR2(10) NOT NULL,
SNAME varchar2(10) NOT NULL,
SSEX VARCHAR2(10) NOT NULL,
SBIRTHDAY DATE,
CLASS NUMBER NOT NULL);
常用的数据对象，SEQUENCE队列，PROCDURE存储过程，TIGGER触发器，TABLES表，INDEX索引
create table dept30 —通过子查询来创建表，并且表中包含子查询的数据
as
select empno,ename,sal12 annsal,hiredate
from emp
where deptno=30;
—alter修改表
—add在表中添加列
alter table dept30 —修改表，增加一列使用alter table 表名 add 列名 类型(大小),多列时，使用（）
add (notes1 varchar2(10),
comments varchar2(10));
—modify修改列的类型
alter table dept30
modify (notes number(10),
comments varchar(10));
—rename修改表的字段名，column是关键字，不能省
alter table dept30 rename column notes1 to note;
—rename修改表名
rename dept30 to department;

作业

—1、对emp表中sal、comm进行加计算，并使用别名命令为员工的月总收入，同时展示出员工部门编号、员工姓名信息。
select ename,sal+nvl(comm,0) as monthlySalary ,deptno from emp;
—2、使用连接符查询emp表中员工的姓名和工资，并以如下格式列出且字段名展示为 TOTAL INCOME：
select ename||’ TOTAL INCOME ‘||sal from emp;
—3、使用distinct排重查询emp中的job类型
select distinct job from emp;
—4、从emp表中找出奖金高于薪水60%的员工
select from emp where nvl(comm,0)>(sal+nvl(comm,0))0.6;
—5、找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。
select from emp where (deptno=10 and job=’MANAGER’) or (deptno=20 and job=’CLERK’);
—6、从emp和dept中联合查询，并将员工编号、姓名、职位、地址信息列出。
select e.empno,e.ename,e.job,d.loc from emp e,dept d ;
—7、统计各部门的薪水总和。
select deptno,sum(sal+nvl(comm,0)) from emp group by deptno;
—8、找出部门10中所有理（MANAGER），部门20中所有办事员（CLERK）以及既不是经理又不是办事员但其薪水大于或等2000的所有员工的详细资料。
select from emp where (deptno=10 and job=’MANAGER’) or (deptno=20 and job=’CLERK’) or (job not in(‘MANAGER’,’CLERK’) and (sal+nvl(comm,0))>=2000);
—9、列出各种工作的最低工资。
select job,min(sal) from emp group by job;
—10、列出各个部门的MANAGER（经理）的最低薪水。
select deptno,min(sal+nvl(comm,0)) from emp where job=’MANAGER’ group by deptno;
—11、找出收取奖金的员工的不同工作。
select job from emp where comm is not null;
—12、找出无奖金或奖金低于300的员工。
select from emp where comm<300 or comm is null;
—13、显示所有员工的姓名,并使姓名首字母大写。
select initcap(ename) from emp;
—14、显示正好为5个字符的员工的姓名。
select ename from emp where length(ename)=5;
—15、显示不带有“R”的员工姓名。
select ename from emp where ename not like ‘%R%’;
—16、列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水。
select ename,sal+nvl(comm,0) from emp where sal+nvl(comm,0)>all(select sal+nvl(comm,0) from emp where deptno=30);
—17、列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(empno),avg(sal),avg((sysdate - hiredate)/365) from emp group by deptno;
—18、列出从事同一种工作但属于不同部门的员工的一种组合。
select e.ename,e.job, e.deptno ,d.job, d.deptno from emp e, emp d where e.job=d.job and e.deptno<>d.deptno;
—19、列出薪水比“SMITH”多的所有员工。
select from emp where sal+nvl(comm,0) >(select sal+nvl(comm,0) from emp where ename=’SMITH’);
—20、列出至少有一个员工的所有部门。
select distinct deptno from emp where deptno in (select deptno from emp );
—21、对于每个员工，显示其加入公司的天数、月数、年数。
select ename , trunc(sysdate - hiredate) “天数” ,
trunc(sysdate - hiredate)/30 “月数” , trunc(sysdate - hiredate)/365 “年”
from emp;
—22、对21中的天数、月数、年数取整显示。
select ename , trunc(sysdate - hiredate) “天数” ,
round(trunc(sysdate - hiredate)/30) “月数” , round(trunc(sysdate - hiredate)/365) “年”
from emp;
—23、找出在每年5月受聘的所有员工。
select ename from emp where to_char(hiredate,’mm’) = 5 ;
—24、显示在一个月为30天的情况下所有员工的日薪水，取整。
select ename , round(sal/30) daysal from emp;
—25、显示所有员工的姓名和加入公司的年份和月份，并将员工入职年月从低到高排序。
select ename , to_char(hiredate,’yyyy-mm’)
from emp
order by hiredate ;
—26、从emp表中复制一个表名为empcopy，并增加字段名“备注”，修改列“COMM”为奖金
create table empcopy
as
select *
from emp;
alter table empcopy add (“备注” varchar2(10));
alter table empcopy rename column COMM to “奖金”;
—27、重命名表empcopy为empbak
rename empcopy to empbak;
—28、新员工入职，请新增一个用户
insert into empcopy(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, “奖金”, DEPTNO)values
(7505,’YUCHE’,’SALESMAN’,7900,to_date(‘2020/12/29’, ‘yyyy-mm-dd’),1900.00,300.00,40);
—29、SMITH的职位变更为SCOTT的职位
update empcopy set job=(select job from empcopy where ename=’SCOTT’) where ename=’SMITH’;
—30、SMITH离职，请删除该用户
delete from empcopy where ename=’SMITH’;