2021-05-03

目录

一、前言

二、使用步骤

       1.创建表（基本语句）

       2.修改表

       3.crud(增加、更新、删除、查找数据)    

          3.1 增加数据  

          3.2 修改数据 

          3.3 删除数据 

          3.4 查询数据

                3.4.1 简单单表查询

                3.4.2 复杂单表查询 

                3.4.3 多表查询 

                         3.4.3.1 自连接：在同一张表的连接查询

                         3.4.3.2 子查询 ：嵌入在其他sql语句中的select语句，也叫嵌套查询

                         3.4.3.3 单行子查询：只返回一行数据的子查询语句【"="]

       3.4.3.4 多行子查询：返回多行数据的子数据 【"in"】

3.4.5 Oracle表内连接和外连接 

3.4.6 分页查询

总结

---

一、前言

此文主要是整理了Oracle中表的管理

二、使用步骤

1.创建表（基本语句）

 create table 表名(field1 datatype, field2 datatype, field3 datatype,....);

field:指定列名；datatype:指定类型名

示例：

为用户AAA创建一个表user1

表名和列名的命名规则：

       *必须以字母开头

       *长度不能超过30字符

       *不能使用Oracle保留字

       *只能使用以下字符A-Z，a-z，0-9，$，#等

Oracle常用数据类型 

字符型

1、char 定长，最大2000个字符

     例：char(10)：’小红‘ 前四个字符放小红，剩下六个字符将用空格补全，即'小红      '

2、varchar2(20)   变长，最大4000个字符。

      例：varchar2(10)：'小红' oracle只会分配4个字符，节省空间。

3、clob(character large object) 字符型大对象，最大4G

4、blob 二进制数据，可以存放图片/声音 4G

数值型

number[(precision[,scale])]  number(p,s)   其中：1<=p<=38,-84<=s<=127

保存数据范围：-1.0e-130<=number value<1.0e+126

保存在机器内部的范围：1~22bytes

有效位：从左边第一个不为0的数算起的位数。

s>0：精确到小数点右边s位，并四舍五入。然后检验有效位是否<=p;

s<0：精确到小数点左边s位，并四舍五入。然后检验有效位是否<=p+|s|;

s=0：等价于number(p),此时number为整数。

示例：

number(5,2)：表示一个小数有5位有效位，2位小数。范围：-999.99~999.99

如果数值超出了位数限制就会被截取多余的位数。但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值时575.32.

number(5)<==>number(5,0)：表示一个五位整数，范围-99999~99999。

输入57523.32，真正保存的数据是57523

number(6,-2)：如果输入123.89则结果应该是100

2.修改表

     使用alter table语句添加、修改或删除列的语法。

alter table 表名 add (列名 datatype);   //为一个表增加列属性

alter table 表名 modify(列名 datatype);  //为一个表修改列属性

alter table 表名 drop column(列名);   //删除一个表的某个列属性

修改表的名称：rename 表名 to 新表名;

**新建一个表student 

SQL> create table student(xh number(4),xm varchar2(20),sex char(2),birthday date,sal number(7,2),zhaopian blob);

**添加一个字段classid，类型为数值型2位有效位

SQL> alter table student add(classid number(2));

**修改字段xm的长度为30

SQL> alter table student modify(xm varchar2(30));

**修改字段的类型/名字(不能有数据)

SQL> alter table student modify(xm char(30));

**删除字段sal

SQL> alter table student drop column sal;

**修改表的名字为stu

SQL> rename student to stu;

**删除表stu

SQL> drop table stu;

 3.crud(增加、更新、删除、查找数据)    

3.1 增加数据  

             使用insert语句向表中插入数据。

             insert into 表名 [(column [, column...])]   values (value [, value...]);

                 * 插入的数据应与字段的数据类型相同。

                 * 数据的大小应在列的指定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

                 * 在values中列出的数据位置必须与被加入的列的排列位置相对应。

                 * 字符和日期型数据应包含在单引号中。             

                 * 插入空值，不指定或insert into 表名 values(null);

 小提示：oracle中默认的日期格式为：’DD-MON-YY'  dd--日子(天）mon--月  yy--2位的年

                ‘03-5月-21’  2021年5月3号，改日期的默认格式：

                alter session set nls_date_format = 'yyyy-mm-dd';

3.2 修改数据 

             使用update语句修改表中数据。

              update 表名 set 列名1=表达式1 [ , 列名2=表达式2...]  [ where 条件] ;

                   * update语法可以用新值更新原有表行中的各列。

                   * set子句指示要修改哪些列和要赋予哪些值。

                   * where子句指定应更新哪些行。如没有where子句，则更新所有的行。

3.3 删除数据 

             使用delete语句删除表中数据。

              delete from 表名 [where 条件语句] ;

                   * 如果不使用where子句，将删除表中所有数据。

                   * delete语句不能删除某一列的值(可使用update)

                   * 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

                   * 同insert和update一样，从一个表中删除记录将引起其他表的参照完整性问题，再修改数据库数据时，头脑应该始终不要忘记这个问题。

                   *删除表中数据也可使用truncate table语句，他和delete有所不同。

 删除数据：

delete from 表名;  //删除所有记录，表结构还在，写日志，可以恢复的，速度慢

drop table 表名;   //删除表的结构和数据   delete from student where xh='A001'；删除一条记录

truncate table 表名;   //删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

3.4 查询数据

              基本select语句： select [distinct] *|{column1,column2,...}  from 表名 ;

                 * select指定查询哪些列的数据。

                 * column指定列名

                 * *号代表所有列

                 * from指定查询哪张表

                 * distinct可选，指显示结果时，是否剔除重复数据

查看表的结构： SQL> desc 表名;

查询所有列： SQL> select * from 表名;

查询指定列：SQL> 列1,列2... from表名;

如何取消重复行：SQL> select distinct 列1,列2 from 表名；

3.4.1 简单单表查询

普通简单查询

查询SMITH的姓名，薪水，工作，所在部门： SQL> select ename,sal,job,dname from emp1,dept where emp1.deptno=dept.deptno and ename='SMITH';

显示每个雇员的年工资，并且使用别名来表示：SQL> select ename,sal*12 年工资 from emp1; / select ename,sal*12 as "年工资" from emp1;

显示每个雇员年工资加奖金总额：由于奖金comm可能有的雇员没有为null，所以需要使用nvl来处理null值：SQL> select sal*12+nvl(comm,0)*12 "年工资",ename,comm from emp1;

连接字符串(||)：SQL> select ename || 'is a' || job from emp1;

小总结提示：

        1）起别名时若中间有空格必须加引号；

        2）nvl(comm,0)：表示comm为空值，则函数值为0，否则函数值为comm；

        3）Oracle中任何值和空值相加为空值。

使用where子句

显示工资高于3000的员工：SQL> select * from emp1 where sal>3000;

查找1982.1.1后入职的员工：SQL> select * from emp1 where hiredate>to_date('1982/1/1','yyyy/mm/dd');

显示工资在2000到2500的员工情况：SQL> select * from emp1 where sal between 2000 and 2500;

使用like操作符

%：表示任意0到多个字符

_：表示任意单个字符

显示首字符为S的员工姓名和工资：SQL> select ename,sal from emp1 where ename like 'S%';

显示第三个字符为大写O的所有员工的姓名和工资：
SQL> select ename,sal from emp1 where ename like '__O%';

在where条件中使用in

显示empno为123，345，800的雇员情况：SQL> select * from emp1 where empno in (123,345,800,7934);

使用is null的操作符

显示没有上级的雇员情况：SQL> select * from emp1 where mgr is null;

使用逻辑操作符号

查询工资高于500或时岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J：SQL> select * from emp1 where (sal>500 or job='MANAGER') and ename like 'J%';

使用order by子句

按照工资的从低到高的顺序显示雇员的信息：SQL> select * from emp1 order by sal desc;

按照部门号升序而雇员的入职时间降序排列：SQL> select * from emp1 order by deptno asc,hiredate desc;

使用列的别名排序

按照雇员的年薪的别名升序排序：SQL> select ename,sal*12 "年薪" from emp1 order by "年薪" asc;

3.4.2 复杂单表查询 

数据分组-max，min，avg，sum，count

显示所有员工中最高工资和最低工资：SQL> select max(sal) 最高工资,min(sal) 最低工资 from emp1;

显示所有员工中的平均工资和工资总和：SQL> select avg(sal) 平均工资,count(sal) 工资总和 from emp1;

计算共有多少员工：SQL> select count(*) from emp1;

请显示工资最高的员工的姓名和工作岗位：SQL> select ename,job from emp1 where sal=(select max(sal) from emp1);

请显示工资高于平均工资的员工信息：SQL> select * from emp1 where sal>(select avg(sal）from emp1);

小提示：where语句中不能有函数哦！

group by和having子句

group by用于对查询的结果分组统计；having子句用于限制分组显示结果。

显示每个部门的平均工资和最高工资：SQL> select avg(sal) "部门平均工资",max(sal) "部门最高工资" from emp1 group by deptno;

显示每个部门的每种岗位的平均工资和最低工资：SQL> select min(sal),avg(sal),max(sal),deptno,job from emp1 group by deptno,job;

显示平均工资低于2000的部门号和它的平均工资：SQL> select deptno,avg(sal) from emp1 group by deptno having avg(sal)<2000;

对分组数据的总结：

        ①分组函数只能出现在选择列表、having、order by语句中；

        ②如果在select语句中同时包含group by、having、order by，那么他们的顺序是group by、having、order by

        ③在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错。

            例如：SQL> select deptno,avg(sal),max(sal) from emp1 group by deptno having avg(sal)<2000;这里的deptno一定出现在group by中。

3.4.3 多表查询 

 定义：多表查询指基于两个或两个以上的表或视图的查询。

1）显示雇员名，雇员工资及所在部门的名字：SQL> select ename,sal,dname from emp1,dept where emp1.deptno=dept.deptno;

规定：多表查询的条件是至少不能少于表的个数-1

2）显示部门号为10的部门名、员工们和工资：SQL> select ename,sal,dname from emp1,dept where emp1.deptno=dept.deptno and dept.deptno=10;

3）显示各个员工的姓名，工资，及其工资级别：SQL> select ename,sal,salgrade.grade from emp1,salgrade where emp1.sal between salgrade.losal and salgrade.hisal;

4）显示雇员名，雇员工资及所在部门的名字，并按部门排序：SQL> select ename,sal,dname from emp1,dept where emp1.deptno=dept.deptno order by emp1.deptno desc;

3.4.3.1 自连接：在同一张表的连接查询

显示员工的上级领导的名字：SQL> select e1.ename,e2.ename from emp1 e1,emp1 e2 where e1.mgr=e2.empno;

显示各员工的姓名和他的上级领导姓名：SQL> select e1.ename,e2.ename from emp1 e1,emp1 e2 where e1.mgr=e2.empno;

3.4.3.2 子查询 ：嵌入在其他sql语句中的select语句，也叫嵌套查询

3.4.3.3 单行子查询：只返回一行数据的子查询语句【"="]

显示与SMITH同一部门的所有员工：SQL> select * from emp1 where deptno=(select deptno from emp1 where ename='SMITH');

3.4.3.4 多行子查询：返回多行数据的子数据 【"in"】

查询和部门10的工作相同的雇员的名字、岗位、工资、部门号：SQL> select ename,job,sal,deptno from emp1 where job in (select job from emp1 where deptno=10);

多行子查询中使用all操作符

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号：SQL> select ename,sal,deptno from emp1 where sal>all(select sal from emp1 where deptno=30); /  SQL> select ename,sal,deptno from emp1 where sal>(select max(sal) from emp1 where deptno=30);

多行子查询中使用any操作符

显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号：SQL> select ename,sal,deptno from emp1 where sal>any(select sal from emp1 where deptno=30);  /  SQL> select ename,sal,deptno from emp1 where sal>(select min(sal) from emp1 where deptno=30);

多列子查询

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言，而多列子查询是指查询多列数据的子查询语句。

查询与SMITH的部门和岗位完全相同的所有雇员：SQL> select * from emp1 where (deptno,job)=(select deptno,job from emp1 where ename='SMITH');

在from子句中使用子查询

显示高于自己所在部门平均工资的员工的信息：SQL> select e1.*,e2.myavg from emp1 e1,(select avg(sal) myavg,deptno from emp1 group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.myavg;

SQL> select e1.* from emp1 e1 where e1.sal>(select avg(sal) from emp1 where deptno=e1.deptno);

查找每个部门工资最高的人的详细资料：SQL> select e1.*,e2.m from emp1 e1,(select deptno,max(sal) m from emp1 group by deptno) e2 where e1.deptno=e2.deptno and e1.sal=e2.m;  /  SQL> select * from emp1 e where sal=(select max(sal) from emp1 where deptno=e.deptno);

显示每个部门的信息和人员数量：SQL> select d.*,e1.c from dept d,(select deptno,count(*) c from emp1 group by deptno) e1 where e1.deptno=d.deptno;  /  SQL> select d.*,(select count(*) from emp1 where deptno=d.deptno) allnum from dept d;

说明：挡在from子句中使用子查询时，该子查询会被作为一个临时表来对待，该子查询叫做内嵌视图。

           当在from子句中使用子查询时，必须给子查询指定别名。

用查询结果创建新表：这个命令时一种快捷的建表方法。

create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

自我复制数据(蠕虫复制)：可以使用此法为表创建海量数据

insert into mytable (id,name,sal,job,deptno)  select empno,ename,sal,job,deptno from emp;

合并查询【集合操作符号 union , union all,intersect , minus】

1）union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

SQL> select ename,sal,job from emp1 where sal>2500 union select ename,sal,job from emp1 where job='MANAGER';

2）union all：该操作赋予union相似，但是它不会取消重复行，而且不会排序。

SQL> select ename,sal,job from emp1 where sal>2500 union all select ename,sal,job from emp1 where job ='MANAGER';

3）intersect：此操作符用于取得两个结果集的交集。

SQL> select ename,sal,job from emp1 where sal>2500 intersect select ename,sal,job from emp1 where job='MANAGER';

4）minus：此操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。

3.4.5 Oracle表内连接和外连接 

内连接：利用where子句对两张表形成的笛卡尔积进行筛选。

              案例：显示两表id匹配的

外连接：①左外连接：如果左侧的表完全显示就是左外连接

                  例：显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空。

                         SQL> select stu.*,exam from stu left join exam on stu.id=exam.id;

                         SQL> select stu.id,stu.name,exam.exam from stu,exam where stu.id=exam.id(+);

              ②右外连接：如果右侧的表完全显示就是右外连接

                   例：显示所有成绩，如果没有名字匹配，显示为空。

                          SQL> select stu.*,exam from stu right join exam on stu.id=exam.id;

                          SQL> select stu.*,exam from stu,exam where stu.id(+)=exam.id;

              ③完全外连接：完全显示两个表，没有匹配的记录置为空。

                  例：显示所有人成绩和所有人姓名，如果没有相应的匹配值，则显示为空。

                          SQL> select * from stu s full outer join exam e on s.id=e.id;

小练习：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。     
              SQL> select emp.*,dept.* from emp right join dept on emp.deptno=dept.deptno;  

              SQL> select emp.*,dept.* from emp,dept where emp.deptno(+)=dept.deptno; 

3.4.6 分页查询

分页步骤：

                1）select a1.*,rownum rn from (select * from emp) a1;

                2）select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;

                3）select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;

---

总结

关于Oracle中表的管理知识就这么多了！