一.DDL (Data Definition Language) 不可回滚
1.创建表方式一 (直接造)
create table a1 (id number,name varchar2(20),hire_date date,salary number(10,2))
--约束(种类:not null /unique/primary key/foreign key/check)
constraint pk primary key(id)
2.创建表方式二(基于现有表)
create table a2 as select id ,c1,c2 from table_k;----包含数据
create table a2 as select id ,c1,c2 from table_k where 1=2;----不包含数据
3.修改表 :alter table
3.1添加列:
alter table emp1 add email varchar2(20);
3.2 修改现有列:
alter table emp1 modify email varchar2(30);
3.3重命名:
alter table emp1 rename column email to myemail;
3.4 删列:
alter table emp1 drop column email;
4. 清空表: truncate table (不可回滚) ( delete from (可回滚 ))
Truncate table emp1
5. 重命名表:
Rename emp1 to emp2
6. 删除表:
Drop table emp1;
二.DML(Data Manipulation Language)
1. 添加:
1.1:(一条一条添加)
Insert into emp(,,) values(,,)
1.2:(批量导入)
Insert into emp(,,) select , , from emp2 where 1=1
2. 删除:
Delete from emp where ..
3. 修改:
Update emp set .... where ..
4. 查询:
4.1内连接:(仅查出满足连接条件的)
Select a.c1,a.c2,b.c2 from t1 a,t2 b where a.c1 = b.c1
4.2 外链接
4.3左外链接:
Select a.c1,a.c2,b.c2 from t1 a,t2 b where a.c1 = b.c1(+)
Select a.c1,a.c2,b.c2 from t1 a left outer join
t2 b on a.c1 = b.c1
4.右外链接:
Select a.c1,a.c2,b.c2 from t1 a,t2 b where a.c1(+) = b.c1
Select a.c1,a.c2,b.c2 from t1 a left outer join t2 b on a.c1 = b.c1
5.满外链接:
Select a.c1,a.c2,b.c2 from t1 a full outer join t2 b on a.c1 = b.c1
三.子查询:
1.多列子查询
Q1:查询与141与174号员工的md 与 dd 一样的其他全功信息。
Select * from emp where (md,dd) in (select md,dd from emp where emp.id in(141,174)) where emp.id not in (141,174)
2. 扩展(除了group by不能使用子查询,其他地方均可用)
Q2:查询公司中比自己部门平均工资高的员工信息
Select name,salary from emp a,(select department,avg(salary) sv form emp b group by department ) where a.department = b.department and a.salary > b.sv