1、建表

1建表

方法一

create table EMP
(empno NUMBER(4)
 ,ename VARCHAR2(10)
 ,job VARCHAR2(9)
 ,mgr NUMBER(4)
 ,hiredate DATE
 ,sal NUMBER(7,2)
 ,comm NUMBER(7,2)
 ,deptno NUMBER(2)
);

方法二

-- 第二种建表
-- 把一个查询结果集建成一张表
-- 备份emp表
create table emp_bak as select * from emp;
-- 验证
select * from emp_bak;
-- 只要表结构不要数据    临时 temporary  缩写  tmp 
create table emp_tmp as select * from emp where 1 = 2;

2.插入字段注释

-- 员工表的 表注释
comment on table EMP is '员工表';
-- 字段注释
comment on column EMP.empno       is '员工编号';
comment on column EMP.ename       is '姓名';
comment on column EMP.job         is '工作';
comment on column EMP.mgr         is '上级编号';
comment on column EMP.hiredate    is '入职日期';
comment on column EMP.sal         is '薪水';
comment on column EMP.comm         is '奖金';
comment on column EMP.deptno        is '部门编号';

3.删除表

-- 删除表
drop table emp;

4.对表结构操作

--DDL 是对表结构的操作 CREATE（创建）命令、ALTER（修改）命令、DROP（删除）
--DML 是对表数据的操作 INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）
--ALTER    修改
--ADD      添加
--MODIFY   修改
--DROP     删除
--RENAME   重命名
-- 删除表
drop table emp_test1;
-- 创建表
create table emp_test1 as select * from emp;
-- 增加一个字段  默认空值 
ALTER TABLE emp_test1 ADD aaa VARCHAR2(20);
-- 修改一个字段的长度
ALTER TABLE emp_test1 modify aaa VARCHAR2(40);
-- 修改一个字段的数据类型  
ALTER TABLE emp_test1 modify aaa NUMBER;
-- 如果字段里面已经有数据了 那就不能修改数据类型 但是可以修改字段的长度
ALTER TABLE emp_test1 modify job NUMBER;
ALTER TABLE emp_test1 modify empno VARCHAR2(20);
ALTER TABLE emp_test1 modify job VARCHAR2(20);
ALTER TABLE emp_test1 modify job VARCHAR2(5);
-- 删除一个字段
ALTER TABLE emp_test1 drop column aaa ;
-- 字段重命名
ALTER TABLE emp_test1 RENAME column aaa TO bbb ;
-- 表重命名
ALTER TABLE emp_test1 RENAME TO emp_test2;
ALTER TABLE emp_test2 RENAME TO emp_test1;

5.约束 constraint

-- 对表数据的约束 
--  not null：非空约束
 alter table emp_constraint modify job not null;
 insert into emp_constraint (job) values('teacher');
 insert into emp_constraint (job) values(' ');  -- 空格也是一个字符
 -- unique：唯一约束       可以插入空值 
alter table emp_constraint add constraint uq_emp_constraint_empno unique(empno);
insert into emp_constraint (empno) values(7499); -- 无法将null插入 job
insert into emp_constraint (empno,job) values(7499,'aaa'); -- 违反唯一约束条件
insert into emp_constraint (empno,job) values(1111,'aaa');
-- 添加主键约束
ALTER TABLE emp_constraint ADD constraint PK_emp_constraint_empno PRIMARY KEY(empno) ;
-- 外键
-- emp表本身就设了外键 -- 先删除子表再删父表 -- 添加数据 先添加父表再子表
SELECT * FROM emp;
SELECT * FROM dept;
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references dept (DEPTNO);
-- CHECK：检查约束 到时候数据插入sal不是这个范围插不进
ALTER TABLE emp_constraint ADD constraint CK_emp_constraint_sal check(sal >=0 AND sal<=10000);
-- 删除约束
ALTER TABLE emp_constraint DROP constraint uq_emp_constraint_empno;
-- default 默认值  你不往里面写数据的时候就会自动给到默认值
alter table emp_constraint modify comm default 888 ; 
-- 在建表的时候就把约束写好
create table emp_test
(
  empno    NUMBER(4)    primary key,
  ename    VARCHAR2(10) unique,
  job      VARCHAR2(9)  not null,
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2) check(sal >=0 AND sal<=10000),
  comm     NUMBER(7,2) default 888,
  deptno   NUMBER(2),
  constraint fk_emp_test_deptno foreign key(deptno) references dept_constraint(deptno)
)

6.关联查询join

-- 内关联 inner join   (inner可省略)
-- 只显示关联的上的数据

-- 外关联有三种

-- 左外  left join -- 主表数据不丢失(关键字左边的表)
-- 右外  right join   主表数据不丢失(关键字右边的表)
-- 全外  full join    主表数据不丢失(左右都是主表)

-- 关联字段的字段名可以不一样么
-- 你是工具字段名去关联的还是工具字段的值
alter table dept_join rename column deptno to deptno_new ;
alter table dept_join rename column deptno_new to deptno ;

-- 关联字段的名字可以不一样 它是根据里面的值去做关联的 -- 关联字段的数据类型要一致
select e.*,d.* from emp_join e join dept_join d on e.deptno = d.deptno_new;

7.子查询

8.oracle的 （+）

oracle中的（+）是一种特殊的用法，（+）表示外连接，并且总是放在非主表的一方。 

例如

左外连接：select A.a,B.a from A LEFT JOIN B ON A.b=B.b;

等价于select A.a,B.a from A,B where A.b = B.b(+);

再举个例子，这次是右外连接：select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;

等价于select A.a,B.a from A,B where A.b (+) = B.b;

个人补充：

数据表的连接有:
1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现
2、外连接: 包括
（1）左外连接(左边的表不加限制)
（2）右外连接(右边的表不加限制)
（3）全外连接(左右两表都不加限制)
3、自连接(连接发生在一张基表内)

有 (+) 的一方代表有可以为空，即副表

2、增删改

1.插入数据

-- 新增数据  提交

-- 没有指定字段插入数据 字段可以省略 但是不建议省略

insert into student_info values(10001,'小明','男','小学','深圳','城市户口',sysdate);



-- 没有足够的值 字段有几个 就需要几个对应的值 否则报错

insert into student_info values(10001,'小明','男','小学','深圳','城市户口');



-- number字段不能插入字符或者日期数据

-- 字段的数据类型要看清楚 不要插入其他类型的数据  字段类型要一致

insert into student_info values('BRUCE','小明','男','小学','深圳','城市户口',sysdate); 



-- 隐式转换

insert into student_info values(10002,'小明',2,'小学','深圳','城市户口',sysdate); 

insert into student_info values('10003','小明','男','小学','深圳','城市户口',sysdate);

2.修改数据

-- 无where条件修改数据 修改这个表的所有行 只修改了一个字段

update student_info s set s.home_address = '广西';

commit;

-- 修改多个字段

update student_info s set s.home_address = '湖南',s.household_type = '农村户口';

commit;



-- 条件修改数据  只修改一条或者几条数据

-- WHERE 跟你查询的那个where是一样的逻辑

-- 工作中需要修改数据 通常是先查询出来 再去修改 避免改了不该改的数据

update student_info s set s.home_address = '广东' where s.stu_name = '小三';

commit;


-- 来自湖南的同学 学历改成 本科

select * from student_info s where s.home_address = '湖南';

update student_info s set s.education = '本科' where s.home_address = '湖南';

commit;

3、删除数据

-- 条件删除数据

delete from student_info s where s.home_address = '广东';

commit;

-- 删除整个表的数据

delete from student_info ;

commit;

rollback;

-- from 可以省略 不建议省略

delete  student_info ;



-- truncate 删除  不需要提交 commit  工作中慎用

truncate table  student_info;

4、查

1. 聚合函数

• sum 求和
• avg 求平均
• max 最大
• min 最小
• count 统计

2.排序

order by 默认正序 asc 倒序 desc

-- 空值排序  默认空值最大

-- 指定空值的位置  

-- nulls first  指定在前

-- nulls last   指定在后

3. 虚表dual

-- dual表的介绍
select * from dual;

4.高级查询

一 去重distinct

-- 去重 distinct
select distinct e.job from emp e;

-- group by 分组 去重

二 null

-- 空值不能被统计

select count(comm) from emp;

-- 空值排序  默认空值最大
select * from emp e order by e.comm;

select * from emp e order by e.comm desc;

-- 指定空值的位置  
-- nulls first  指定在前
-- nulls last   指定在后

-- null  真的是啥都没有 不存在
-- ''    一个字符串 但是里面没有任何东西
-- ' '   空格  是一个字符

-- 空值不能做运算
-- 任何数据跟空值做算数运算 的结果都是空值
-- 这么解决这个问题  空值转换函数  nvl
select e.* ,nvl(e.comm,0) as new_comm,e.sal + nvl(e.comm,0) as sal_comm from emp e;
-- nvl 只能空值转成0么
-- 如果不是空值就返回字段本身 如果是空值 就返回第二个参数

三 in

-- in操作  在什么里面
-- not in  不在什么里面

select * from emp e where e.deptno in (10,20);

四 between

-- BETWEEN…AND…  在什么之间
-- 包括两头  闭区间
select * from emp e where e.sal between 1500 and 3000;

-- 等价于
select * from emp e where e.sal >= 1500 and e.sal <= 3000;

五 LIKE模糊查询

--  %  表示零个或者多个任意字符
-- _  代表一个任意字符

-- 名字以S开头
select * from emp e where e.ename like 'S%';

-- 名字以SM开头
select * from emp e where e.ename like 'SM%';

-- 名字以R结尾
select * from emp e where e.ename like '%R';

-- 名字里面包含L
select * from emp e where e.ename like '%L%';

-- 第二个字符是 L
select * from emp e where e.ename like '_L%';

-- 倒数第二个是 K
select * from emp e where e.ename like '%K_';

六 集合运算

--INTERSECT    交集 
--UNION ALL    并集不去重 
--UNION        并集去重   
--MINUS        补集 

-- intersect    交集 
select * from emp_test1
intersect
select * from emp_test2;

-- union all    并集不去重 
-- union        并集去重  
select * from emp_test1
--union all
union
select * from emp_test2;

-- minus  补集  
-- 除掉共有的部分 看左边的结果集还剩下什么
select * from emp_test1
minus
select * from emp_test2;

七 伪列rowid

-- 伪列 ROWID 物理地址
-- 使用ROWID可以快速的定位表中的某一行
-- ROWID值唯一  用来删除重复数据

select rowid,d.* from dept_test d;

八 伪列 ROWNUM

-- 基本用法 给查询的数据一个行号
select rownum as rn,e.* from emp e;

5、关系运算

-- 字符串连接操作符 ||  可以多个
select 'aaa'||'bbb' from dual; 

-- 拼接函数 concat  只能两个
select  concat('aaa','bbb')  from dual; 

-- 三个逻辑运算符优先级 NOT>AND>OR