黑马程序员--Oracle学习_day02

----------  android培训 、java培训、期待与您交流！ ----------

1，上节回顾

2，oracle的表的管理

1），表名的命名规则：

a,必须以字母开头

b,长度不能超过30字符

c,不能使用oracle的保留字

d,只能使用如下字符  A-Z,a-z,0-9,$,#。

        2），oracle支持的数据类型

a,字符型  

   char  定长  最大2000字符。 （查询速度快）

      例子：char(10) ‘小韩’前四个字符放’小韩’，后添6个空格补全。

   varchar2(20)  变长  最大4000字符。（节省空间）

       例子：varchar2(10)’小向’ oracle 分配四个字符，这样可以节省空间。

  clob 字符型大对象，最大4G   

           b,数字类型

                   number 范围 -10的38次方到10的38次方，可以表示整数，也可以表示小数。

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

               number(5)  表示一个5位整数  范围-99999 ~ 99999  

              c,日期类型

                  date   包含年月日和时分秒

                 timestamp  这是oracle9i对date数据类型的扩展。

 

           d, 图片，声音

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

               面试题：什么情况把图片存本身存放到数据库中呢？

              答：出于安全考虑的时候把图片本身存放到数据库中。一般情况下是把图片存放到一个文件夹中，而数据库存储图片的路径。

      3），修改表：

·  添加一个字段

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

  ·修改字段的长度

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

· 修改字段的类型（不能有数据）

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

· 修改字段名

sql>alter  table  student  rename column  旧字段名 to  新字段名;

   ·删除一个字段

  sql>alter  table  student drop column sal;

· 修改表的名字

    sql>reanme  student  to stu;

删除表

  sql>drop  table  student;

    4)，添加数据insert

· 所有字段都插入

sql>insert  into  student  values(‘A001’,’张三’,’男’,’01-5月-05’,’10’);

  ·      修改日期默认格式：orable中默认的日期格式‘DD-MON-YY’ 日-月-年。

     alter  session  set  nls_date_format = ‘yyyy-mm-dd’;

   插入部分字段

 sql>insert  into  student(xh,xm,sex) values (‘A003’, ‘JOHN’,’女’);

·插入空值

sql>insert  into  student(xh,xm,sex,birthday)   values(‘A004’,’MARTIN’,’男’,null);

       查询空值：sql>select  * from  student  where  birthday  is  null;

        

     查询非空 ：sql>select  * from  student  where  birthday  is  not  null;

 

    5)，修改数据 update

·修改一个字段

sql>update  student  set  sex=’女’  where  xh=1;

      ·修改多个字段

sql>update   student  set  sex=’男’ ,birthday=’1980-04-01’  where  xl=’1’;

     ·修改含有null值的数据

sql>update   student  sex=’女’  where  sex is null;

    6)，删除数据

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

·delete  from  student  where  xh=’1’;   删除一条记录

·drop  table  student ; 删除表的结构和数据

·truncate  from  student; （慎用） 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

       7),回滚

savepoint  aa; 创建保存点。

rollback  to  aa; 回退到保存点。

 

 3，基本查询

  ·查看表结构

sql>desc  dept;

      ·查询所有列 （不要轻意使用*，查询速度慢）

sql>select  * from  dept;

     ·查询指定列

sql>select  ename,al,job,deptno  from  emp;

    ·如何取消重复行

   sql>select  distinct  deptno,job, from  emp;

      显示操作时间

set  timing  on;  打开显示操作时间开头。

 

    查询SMITH的薪水，工作，所在的部门？

           sql>select  sal,job,deptno from emp  where ename='SMITH';

   ·使用算术表达式

   ？显示每个雇员的年工资

sql>select  ename,sal*12+nvl(comm,0)*12  YearSal  from emp ;

  ·使用列的别名

 sql>select  ename  “姓名”，sal*12  as  “年收入”  from emp;

注意：别名要使用双引号括起来。

 

 ·如何处理null值？使用nvl函数来处理

  sql>select  sal*13+nvl(comm,0)*12  “年收入”  ,ename  from  emp; 

      ·如何连接字符串（||）

sql>select  ename  ||  ‘is  a ’  ||  job  from  emp;

 

     ·如何使用like操作符，模糊查询

         a，% 表示任意0到多个字符    _ 表示任意单个字符

 ？，如何显示首字符为S的员工姓名和工资？

sql>select  ename,sal  from  emp  where  ename  like  ‘S%’;

      ？，如何显示第三个字符为大写的o的所有员工的姓名和工资？

  sql>select ename,sal  from  emp  where  ename  like  ‘__O%’;

 ·如果只知道员工姓名(Ename)中含有‘李’字符，写查询语句，从员工信息表中查询出 所有符合该条件的结果。

           Select *  from  emp  where  enmae  like ‘%李%’;

 ·在where条件中使用in

？如何显示empno为7788,7782,9999的雇员情况

sql> select  * from  emp  where  empno  in(7788,9999);

  ·使用is  null 的操作符

？如何显示没有上级的雇员情况

sql>select * from emp  where  mgr is null;

  ·使用逻辑操作符号

？查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名 首富字母为大写的J。

     ·使用order  by 字句

    升序：order  by XX  asc;  asc可以不写，默认的。

       降序：order  by  XX  desc;

   ？如何按照工资的从低到高的顺序显示雇员的信息

    sql>select * from  emp  oreder  by  sal;

          ？按照部门号升序而雇员的工资降序排序

sql>select  * from  emp  order  by  deptno,sal desc;

      ·使用列的别名排序

sql>select  ename,sal*12  “年薪”  from  emp  order  by  “年薪” asc; 

4，复杂查询

在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，必须学习好较为复杂的select语句。

·group  by  和having子句：

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

？1，如何显示每个部门的平均工资和最高工资

sql>select  avg(sal),max(sal),deptno  from  emp group  by deptno; 

？2，显示每个部门的每种岗位的平均工资和最低工资

sql>select  avg(sal),min(sal),deptno,job  from  emp  group  by  deptno,job;

？3，显示平均工资低于2000的部门号和它的平均工资

sql>select  avg(sal),deptno  from  emp  group  by  deptno  having  avg(sal) <2000;

 ·分组函数包括：max、min、avg、sum、count。

      ·对数据分组的总结

    1），分组函数只能出现在选择列表、having、order by 子句中。

  2），如果在select 语句中同时包含有group by，having，oreder by 那么他们的顺序是group by, having ,order  by。 

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

例如：select  avg(sal),deptno  from  emp  group  by  deptno  having   avg(sal) <2000;  中的deptno就一定要出现在group by中。

      ·多表查询

    多表查询是指基于两个和两个以上的表或视图的查询，在实际应用中，查询单 个表可能不能满足你的需求，（如显示sales部门位置和其员工的姓名），这种情况 下需要使用到（dept表和emp表）。

  ？1，显示雇员名，雇员工资及所在部门的名字。（笛卡尔集）

sql> select  a.ename,a.sal,b.dname  from  emp  a,dept  b  where   a.deptno=b.deptno; 

   笛卡尔集规定：查询的条件是，不能少于表的个数-1。

？2，如何显示部门号为10的部门名、员工名和工资。

sql>select  b.dname,a.ename,a.sal  from  emp  a,dept b  where  a.deptno=b.deptno  and  a.deptno=10; 

？3，显示各个员工的姓名、工资、及其工资的级别。

sql>select  a1.ename,a1.sal,a2.grade  from  emp  a1,salgrade  a2  where  a1.sal  between  a2.losal  and  a2.hisal;

扩展要求：

 ？显示雇员名，雇员工资及所在部门的名字，并按部门号排序。

sql>select  a1.ename,a1.sal,a2.dname  from  emp a1,dept a2     a1.deptno=a2.deptno  order  by  a1.deptno;

     ·自连接

  自连接是指在同一张表的连接查询。

         ？显示某个员工的姓名和他的上级领导的姓名 比如：显示‘FORD’的上级

sql>select  worker.eanme,boss.ename  from  emp worker,emp boss  where  worker.mgr=boss.empno  and  worker.ename=’FORD’;

    ·子查询

子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询。

a,单行子查询是返回一行数据的子查询语句，请思考：如何显示与SMITH同  一部门的所有员工的信息？

sql>select  *  from  emp  where  deptno=(select  deptno  from  emp  where  ename=’SMITH’);

b,多行子查询是指返回多行数据的子查询。请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号？

sql>select  ename,job,sal,deptno  from  emp  where  job  in (select  job  from  emp  where  deptno=10);

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

  请思考：如何显示工资比部门30的所有员工的工资高的员工姓名、工资、部门号？

方法一：

sql>select  ename,sal,deptno  from  emp  where  sal>all(select  sal  from  emp  where  deptno=30);

方法二：(这种方法效率较高)

          sql>select  ename,sal,deptno  from  emp  where  sal>(select  max(sal)    from  emp  where  deptno=30);

·多列子查询

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

请思考：如何查询与SMITH的部门和岗位完全相同的所有雇员？

 方法一：

sql>select  * from  emp  where  job=(select  job  from  emp where  ename='SMITH')  and  deptno=(select  deptno  from  emp where  ename='SMITH');

   方法二：(这种方法效率较高)

sql>select  * from  emp  where  (deptno,job)=(select  deptno,job  from  emp  where  ename='SMITH');

        ·在from 子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工信息？

sql>select  * from  emp  where  sal>(select  avg(sal)  from  where  deptno=deptno);

//1,查询出各个部门的平均工资和部门号

     sql>select  deptno,avg(sal) mysal  from  emp  group  by  deptno;  

//2,把上面的查询看做是一张子表

sql>select  a2.ename,a2.sal,a2.deptno,a1.mysal  from  emp a2 (select  deptno,avg(sal) mysal  from  emp  group  by  deptno) a1  where  a2.deptno=a1.deptno  and  a2.sal>a1.mysal;

        这里需要说明的是当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫作内嵌视图，当在from子句中使用子查询时，必须给子查询指定  别名。

--内嵌视图就是把子查询当作一个表使用。

    ·分页查询

 oracle的分页一共有3种方式

1，根据ROWID来分

2，按分析函数来分

3，按ROWNUM来分

a, rownum分页

sql>(select * from emp )

  b, 显示rownum(oracle分配的)

sql>select a1.*, rownum  rn  from  (select * from emp ) a1;

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

 

  ·几个查询变化

  a, 指定查询列，只需修改最里层的子查询

       b, 如何排序，只需要修改最里层的子查询

 

  ·用查询结果创建新表，这是一种快捷建表的方法。

sql>create  table  mytable (id,name,sal,job,deptno) as  

   select  empno,eanme,sal,job,deptno  from  emp;

 

 ·合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 nuion, union all,  intersect,  minus

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

sql>select  ename,sal,job  from  emp  where  sal>2500  union  

    select  ename,sal,job  from  emp  where  job='MANAGER';

        2)union  all, 该操作赋于union相似，但是它不会取消重复行，而且不会排序。直接合并。

sql>select  ename,sal,job, from  emp  where  sal>2500  union  all

select  ename,sal,job  from  emp  where  job=’MANAGER’;

     3）minus ，取差集

sql>select  ename,sal,job  from  emp  where  sal>2500  minus

      select  ename,sal,job  from  emp  where  job='MANAGER';

  4）intersect，取交集

             sql>select  ename,sal,job  from  emp  where  sal>2500  intersect

     select  ename,sal,job  from  emp  where  job='MANAGER';

 

5，oracle数据库的创建

1)，通过oracle提供的向导工具。

   ·创建数据库使用工具dbcp，数据库配置助手。

2)，我们也可以通过手工步骤直接创建。