记录自己的Oracle之旅(二)

废话不多说，直接开始建表，

create table s_dept(
       did integer primary key,--主键
       dname varchar2(20),  --部门名称
       manager integer,  --外键，部门经理
       tel varchar2(30),  --部门电话
       master integer    --外键，上级部门
); 
create table s_emp(
       eid integer primary key,--主键
       ename varchar2(20),  --员工名称
       sex varchar2(1),    --性别
       hire date,      --入职日期
       sar number(8,2),    --工资 
       did integer    --外键，所属部门
);
create table s_history(
       hid integer primary key,--主键
       startdate date,    --入职时间
       enddate date,    --离职时间
       place varchar2(50),  --公司名称
       job varchar2(20),  --职位
       eid integer    --外键，所属员工
);

建表之后插入几条测试数据

insert into s_dept(did,dname,manager,tel,master) values(1,'董事会',5,'1111111',0);
insert into s_dept(did,dname,manager,tel,master) values(2,'市场部',6,'2222222',1);
insert into s_dept(did,dname,manager,tel,master) values(3,'财务部',7,'3333333',1);
insert into s_dept(did,dname,manager,tel,master) values(4,'采购部',8,'4444444',1);
insert into s_dept(did,dname,manager,tel,master) values(5,'人事部',9,'5555555',1);
insert into s_emp(eid,ename,sex,hire,sar,did) values(5,'tom','m',sysdate-234,4500.50,1);
insert into s_emp(eid,ename,sex,hire,sar,did) values(6,'jacky','m',sysdate-543,2982.12,2);
insert into s_emp(eid,ename,sex,hire,sar,did) values(7,'kelly','f',sysdate-445,3491.32,3);
insert into s_emp(eid,ename,sex,hire,sar,did) values(8,'red','f',sysdate-143,3700.00,4);
insert into s_emp(eid,ename,sex,hire,sar,did) values(9,'blue','m',sysdate-423,3800.00,2);
insert into s_emp(eid,ename,sex,hire,sar,did) values(10,'green','m',sysdate-413,5100.00,3);
insert into s_emp(eid,ename,sex,hire,sar,did) values(11,'peter','m',sysdate-243,6700.00,4);

然后是老规矩，写几个方便操作的查询语句

select * from s_dept;
select * from s_emp for update;
select * from s_emp_n;
select * from s_history;
drop table s_emp_n;

？？嫑在意那个drop语句

接下来开始表的操作

--向s_dept表中增加一个新的字段remark
alter table s_dept add remark varchar2(50);
--修改字段sex的类型
alter table s_emp modify sex varchar2(2);
--添加名为sex的约束
alter table s_emp add constraint sex check(sex in('m','f'));
--删除名为sex的约束
alter table s_emp drop constraint sex;
--复制表(包括表中的数据，需写出需要复制的列)
create table s_emp_n as select eid,ename,sex,hire,sar,did from s_emp;
--复制表(不包括表中的数据)
create table s_emp_n as select eid,ename,sex,hire,sar,did from s_emp where 1=2;

表操作完了，开始数据的操作

--插入来自表的记录
insert into s_emp_n select * from s_emp;
--从指定表s_emp中插入指定列eid,ename的记录
insert into s_empt_n(eid,ename) select eid,ename from s_emp;

--给工资低于6000的人加工资
update s_emp set sar=sar+100 where sar<6000;
--修改kelly的入职日期
update s_emp set hire=to_date('2020-03-23','yyyy-mm-dd') where ename = 'kelly';
--in和not in
select * from s_emp where did in(2,3);
select * from s_emp where did not in(2,3);
--查询所有部门的所有员工
select s_emp.eid as 员工编号,s_emp.ename as 员工姓名,decode(s_emp.sex,'m','男','f','女') as 性别,s_emp.hire as 入职日期,s_emp.sar as 目前薪资,s_dept.dname as 所在部门 from s_emp left join s_dept on s_dept.did = s_emp.did;
--查询工资比本部门平均工资高的员工
select * from s_emp where eid in(select a.eid from (select eid,sar,did from s_emp) a,--查询员工工资
    (select did,avg(sar) sar from s_emp group by did) b--查询每个部门的平均工资--
    where a.did=b.did and a.sar >= b.sar);
--查询没有员工的部门
select d.* from s_dept d where not exists (select e.* from s_emp e where e.did=d.did);

--UNION合并，去除重复(加all可以不去重复)
SELECT eid, ename, sar FROM s_emp WHERE sar>2000 UNION all SELECT eid, ename, sar FROM s_emp WHERE did=2;
SELECT eid, ename, sar FROM s_emp WHERE sar>2000 UNION SELECT eid, ename, sar FROM s_emp WHERE did=2;
--intersect取交集
SELECT eid, ename, sar FROM s_emp WHERE sar>3500 INTERSECT SELECT eid, ename, sar FROM s_emp WHERE did=2;
--minus从第一个结果中减去相同的行
SELECT eid, ename, sar FROM s_emp WHERE sar>2000 MINUS SELECT eid, ename, sar FROM s_emp WHERE did=2;
--||可以连接两个字段，放在一起显示，还可以在两个列中间加入修饰性的文字(比如说下面的例子)
select  (dname || '的电话是:' || tel) as dtel from s_dept;

对数据的操作就结束了，接下来是一些函数

Select abs(-15) from dual;
--四舍五入
Select ceil(44.778) from dual;
--三角函数cos
Select cos(180) from dual;
--向下取整
Select floor(100.2) from dual;
--平方
Select power(4,2) from dual;
--余数
Select mod(10,3) from dual;
--round(x,y)取小数点后y位数字，并四舍五入
Select round(100.256,2) from dual;
--trunc(x,y)取小数点后y位数字
Select trunc(100.256,2) from dual;
--平方根
Select sqrt(6) from dual;
--大于0返回1，小于0返回-1，等于0返回0
Select sign(-30) from dual;

另一些函数

--输出字符串(首字母大写)
Select initcap('hello') from dual;
--输出字符串(全小写)
Select lower('FUN') from dual;
--输出字符串(全大写)
Select upper('sun') from dual;
--获取字符串的长度
Select Length('sun') from dual;
--ltrim('x','y')从左开始删除字符串x中包含在y中的字符，直到删除完毕或者遇到的下一个字符不在y中
Select ltrim('xyzadams','xyz') from dual;
--rtrim('x','y')从右开始删除字符串x中包含在y中的字符，直到删除完毕或者遇到的下一个字符不在y中
Select rtrim('xyzadams','ams') from dual;
--修改字符串中指定的字符(可以指定多个不连续的)
Select translate('jack','j c','b n') from dual;
--修改字符串中指定的字符(必须完全匹配)
Select replace('jack and jue','j','bl') from dual;
--instr('x','y')返回y字符在x中的位置(从1开始)
Select instr('worldwide','d') from dual;
--substr('x',y,z)从第y位字符开始，输出z位字符(如果省略了z，那么会输出到字符串的末尾)
Select substr('abcdefg',3,2) from dual;
--字符拼接(只支持两个字符串的拼接)
Select concat ('Hello','world') from dual;
--获取当前时间
select sysdate from dual;
--获取两个日期之间的月份间隔(新日期在前，旧日期在后)
select months_between(to_date('2020-01-01','yyyy-mm-dd'),to_date('2010-01-01','yyyy-mm-dd')) from dual;
--next_day(date,x)获取指定日期所在周的第x天的日期
select next_day() from dual;
--查询月的最后一天
select last_day(to_date('2020-2','yyyy-mm')) from dual;