oracle中table表操作

1. scott用户示例表

  1) 部门表：dept

1	DEPTNO	NUMBER(2)	表示部门编号，由两位数字构成
2	DNAME	VARCHAR2(14)	部门名称，最多由14个字符所组成
3	LOC	VARCHAR2(13)	部门所在的位置

  2) 雇员表：emp

1	EMPNO	NUMBER(4)	雇员的编号，由四位数字所组成
2	ENAME	VARCHAR2(10)	雇员的姓名，由10位字符所组成
3	JOB	VARCHAR2(9)	雇员的职位
4	MGR	NUMBER(4)	雇员对用的领导编号，领导也是雇员
5	HIREDATE	DATE	雇员的工作日期
6	SAL	NUMBER(7,2)	基本工资，其中有两位小数，五位整数，一共七位
7	COMM	NUMBER(7,2)	奖金，佣金
8	DEPTNO	NUMBER(2)	雇员所在的部门编号

  3) 部门表：dept

1	GRADE	NUMBER	工资的等级
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

  4) 部门表：dept

1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	雇员的工资
4	COMM	NUMBER	雇员的奖金

2. 表的操作

2.1 查询表结构

1) 查询表结构：
SQL> desc dba_users;

2) 使用user_tab_cols表查询表结构：
select column_name, data_type, data_length from user_tab_cols where table_name='DBA_USERS';

2.2 删除表

------------------------------ 
create table t_user(
  id number,
  username char(20),
  password varchar2(20)
);
------------------------------ 
1) delete命令
SQL> delete t_user;

  1.用delete删除表数据，只是删除表中的数据，表结构不会被删除！
  2.虽然也是删除整个表的数据，但是过程是系统一行一行地删，效率比truncate低。
  3.delete删除是不释放空间的。

2) truncate命令
SQL> truncate table t_user;

  1.用truncate删除表数据，只是删除表中的数据，表结构不会被删除！
  2.删除整个表的数据时，过程是系统一次性删除数据，效率比较高。
  3.truncate删除释放空间。

3) drop命令
SQL> drop table t_user;

  1.用drop删除表数据，不但会删除表中的数据，连表结构也被删除了！

3. 表的增删改查

3.1 插入insert语句

给指定列插入数据：
SQL> insert into dept(deptno,dname) values(50,'xx');

插入全部列数据：
SQL> insert into dept(deptno,dname,loc) values(60,'xx','lll'); 

插入全部列数据简写:
SQL> insert into dept values(70,'xxx','llll');

3.2 更新update语句

更新指定数据：
SQL> update dept set dname='司法部' where deptno=50;
SQL> update dept set dname='律政司', loc='china' where deptno=60;

3.3 删除delete语句

删除指定数据：
SQL> delete from dept where deptno=70;

删除指定条件的数据：
SQL> delete from dept where deptno>40;

3.4 查询select语句

------------------------------ 
create table t_user1(
  id number,
  username varchar2(20),
  password varchar2(20)
);
insert into t_user1(id, username, password) values(1, 'bill', 'bill123');
insert into t_user1(id, username, password) values(2, 'adam', 'adam123');

create table t_user2(
  id number,
  username varchar2(20),
  password varchar2(20)
);
insert into t_user2(id, username, password) values(1, 'bill', 'bill123');
insert into t_user2(id, username, password) values(2, 'bake', 'bake123');
------------------------------ 

查询所有
SQL> select * from emp;

指定字段查询
SQL> select ename, sal from emp;

加where条件
SQL> select * from emp where sal>=800;
SQL> select * from emp where sal>=1500 and job='SALESMAN';

distinct去重复记录
SQL> select distinct * from dept;

group by分组查询
SQL> select job,count(ename) as num from emp t group by job;

having过滤分组
SQL> select job,count(ename) as num from emp t group by job having count(ename)>=2;

order by排序
SQL> select * from emp order by sal desc;

子查询：查询出基本工资大于平均工资的员工
SQL> select * from emp where sal>(select avg(sal) from emp); 

联合查询、并集(去重复)
SQL> select * from t_user1 union select * from t_user2;

联合查询、并集(不重复)
SQL> select * from t_user1 union all select * from t_user2;

交集
SQL> select * from t_user1 intersect select * from t_user2;

差集(在第一集合t_user1中且不在第二个集合t_user2中)
SQL> select * from t_user1 minus select * from t_user2;

内连接
SQL> select * from emp t, dept d where t.deptno=d.deptno;
SQL> select * from emp e inner join dept d on e.deptno=d.deptno;

外连接
左外连接
SQL> select * from emp e left join dept d on e.deptno=d.deptno;
右外连接
SQL> select * from emp e right join dept d on e.deptno=d.deptno;