第2讲     DDL语句和DML语句
              其中有数据库最重要的部分——查询

1,Java程序和数据库表的对应关系:
成员字段
对象记录

2,创建表的基本语法
create table table_name (
     列名  列的数据类型
     ……
)
***Oracle数据类型,见《Oracle Database 10g SQL》的第9页,或者看附录A。这是重点记忆和理解的地方。

3,删除表的基本语法
drop table table_name;

4,表的管理——在一个表中,有时候需要添加,修改,或删除列。它们的基本语法如下:
     ①添加     alter table table_name
          add (column_name datatype);
     ②修改     alter table table_name
                    modify (column_name datatype);
                    注意这里修改的是数据类型,而不是数据名称。
     ③删除     alter table table_name
                    drop column column_name;
                    注意删除的语法和添加修改不同,不能有括号,一次只能删除一条。
     ④修改表的名字     rename old_name to new_name;

5,查看一个表的详情
desc table_name;

以上是数据定义语言DDL,下面是数据操纵语言DML,体会其中的差别。

6,Oracle的crud操作
     ①增     insert into table_name (columns...)
                 values (values...);
                注意:数据类型,数据大小,数据顺序必须要相同。如果columns...省略不写,values...一定要写全,顺序也一定要正确。
                插入空值可以直接添加null值,也可以不定义。
     ②改     update table_name
                set column_name1=expr1,...
                where 条件
                注意:千万不能忘了where语句。任何值如果是空值都是“is null” 而不能“=null”。
     ③删     delete from table_name 
                where 条件
                注意:Ⅰ如果不使用where条件,将删除表中的所有数据。
                          Ⅱ只删除数据,不删除结构(drop table table_name是把数据和结构一起删除)。
                          Ⅲ和insert和update一样,删除表的数据时,将引起其他表的“参照完整性”问题。
                          Ⅳ用delete删除数据以后,如果事先设置了保存点,是可以进行回滚找回数据的。用法如下:
                             ⑴savepoint point_name;
                             ⑵执行delete删除操作
                             ⑶rollback to point_name;
                             ***truncate table table_name;这条语句会删除表中所有的记录,但也不会删除表结构,数据删除后不可回滚,删除速度快。
                             truncate属于DDL语句。
     ④查     select [distinct] columns,... from table_name
                where 条件
                注意:增删改查中查是难点也是重点,所以具体操作放在下一个知识点中。如果加了distinct,那么重复数据将被忽略。

7,Oracle基本查询
     ①     SQL语句本身是不区分大小写的,但是数据库对象的内容是区分大小写的。
     ②     任何数乘以null等于null,任何数和null执行了任何运算得到的结果也为空。解决办法:用nvl(x,value)函数。
     ③     在查询时,“||”连接符可以把多个列合并成一个列进行输出,但是它只连接字符类型的字段或字符串或表达式,不连接数字类型。
     ④     灵活应用to_char()函数,见《Oracle Database 10g SQL》的第70页。
     ⑤     like。模糊查询。%:表示任意0到多个字符。_:表示任意单个字符。
             比如:S%表示首字母为S的字符串,__O%表示第三个字母为大写O的字符串。
     ⑥     in。在where语句中使用in。比如:select * from emp where empno in(123,456,789);查询括号中三个员工号的员工信息。
     ⑦     order by column_name [asc];默认,升序排列。ascend
             order by column_name desc;指定降序排列。descend
             order by 后面可以接多个字段,以逗号隔开,即可按多个字段排序。
             order by 对别名排序:select ename,sal*12+nvl(comm,0) 年薪 order by 年薪;这样可以减少运算量。

8,Oracle复杂查询
     ①     聚合函数(组合函数)会忽略空值,也就是不加入运算。常用的聚合函数有:avg(),max(),min(),count(),median(),stddev(),variance().
     ②     子查询。select ename,job,sal from emp where sal=(select max(sal) from emp);有的要求不能一步就能查询出来时用。
     ③     group by。select avg(sal),max(sal),deptno from emp group by deptno;被查询的字段或表达式的返回值个数相同,并且能一一对应起来就可以放在一起。
     ④     having。用于过滤行分组。必须和group by 连用,但是group by 不一定要和having连用。
             select avg(sal),max(sal),deptno from emp group by deptno;
     ⑤     分组函数只能出现在选择列表,having子句,order by子句中。也就是说不能出现在where子句中。
     ⑥     多表查询。
             Ⅰ笛卡尔积。要避免笛卡尔积,那么就必须满足:条件的个数>=表的个数-1。
             Ⅱselect ename,sal,dname from emp,dept where emp.deptno=dept.deptno;说明如果在多个表中,字段名是唯一的,可以不用指明是哪个表的字段,但是如果一个字段名在多个表中出现,那么一定要特别指明。
             Ⅲ select ename,sal,grade from emp,salgrade where sal between losal and hisal;虽然说表名有时候可以省略,但是为了增加可读性,我们一般习惯加上表名。有些表名非常长,这时可以使用别名来替代,从而解决这个问题。
             Ⅳ select t1.ename,t1.sal,t2.grade from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal;
             自连接。
             在同一个表中,有时候需要通过一个记录的信息,得到另一个相关记录的信息。这时候我们就要把一张表看成两张表,构成多表查询。
             select t1.ename,t2.ename from emp t1,emp t2 where t1.mgr=t2.empno;查询某个员工的上级信息。

9,子查询
     ①     定义:子查询是指嵌入在其他sql语句中的select语句,也叫嵌套语句。
     ②     单行子查询:子查询语句返回一条记录。
             如:显示与SMITH同一部门的所有员工姓名。
             Ⅰ查询出SMITH所在部门号:select deptno from emp where ename='SMITH';
             Ⅱ查询出在这个部门中的所有员工信息:select ename from emp where deptno=(select deptno from emp where ename='SMITH');
     ③     多行子查询:子查询语句返回多条记录。
             如:显示具有与10号部门中任意岗位相同岗位的职员信息。
             Ⅰ查询出10号部门有哪些岗位:select distinct job from emp where deptno = 10;
             Ⅱ查询出在以上任意岗位的员工信息:select * from emp where job in (select distinct job from emp where deptno = 10);
             多行子查询中有时候会用到all(所有),any(任意一个)操作符。跟in用法差不多,意义很直观。
     ④     多列子查询:单行子查询和多行子查询都只返回表的一个字段值,多列子查询将会同时返回多个字段的值。
             如:查询与SMITH的部门,岗位完全相同的职员信息。
             Ⅰ查询出SMITH所在部门和工作岗位:select deptno from emp where ename='SMITH';select job from emp where ename='SMITH' ;
             Ⅱ查出与上述部门岗位都相同的员工信息:select * from emp where deptno=(select deptno from emp where ename='SMITH') and job=(select job from emp where ename='SMITH');(不好的写法)
             select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');注意字段顺序要对应!
     ⑤     在from子句中使用子查询。(精髓:把子查询的结果看成一张表,然后进行多表查询
             如:显示高于自己部门平均工资的职员信息。       
             Ⅰselect avg(sal),deptno from emp group by deptno;
             Ⅱselect * from emp t1,(select avg(sal) a,deptno from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal > t2.a;
     ⑥     分页查询。这是重点。学习任何数据库都必须要掌握。分页查询是子查询的一个应用,应该作为一个独立的知识点。

10,分页查询。
     Oracle使用三层过滤机制。
     第一层:select * from emp;
     第二层:select t1.*,rownum  rn from (select * from emp) t1 where rownum<=6;
     第三层:select t2.* from (select t1.*,rownum  rn from (select * from emp) t1 where rownum<=6) t2 where rn>=4;

     注意:rownum<=6中的rownum是t1表中的rownum,rn>=4是t2表中的rownum。
     这是一个分页模版。只要改变数字就行了。
     如果在分页查询中还涉及到多表查询或子查询之类的复杂查询,那么先把这些复杂查询“压缩”在第一层。

     这只是三种方法之一,据说效率最高。还有按照rowid和分析函数来做。

11,合并查询。
     ①     union:用于取得两个结果集的并集。把两个结果中完全相同的行去掉一行。
             select ename,job,sal from emp where sal>2000 union select ename,job,sal from emp where job='MANAGER';
     ②     union all:和union相似,不过不会去掉重复行,并且不会自动排序。其他三个都会自动排序。
     ③     intersect:取交集。也会去掉重复行。
     ④     minus:取差集。

以上是对表的查询进行详细的探讨,下面探讨的是表的内连接和外连接。

12,内连接。
     基本语法:select 列名... from 表1 inner join 表2 on 条件...
     它的执行效果跟:select 列名... from 表1,表2 where 条件...     是一样的。
     前面讲的都是内连接,内连接是我们用的最多的连接。

13,外连接。
     ①     外连接分三种:左外连,右外连,完全外连。
     ②     创建两张表做测试。
           ID NAME             
--- -------
  1 jack
  2 tom
  3 kity
  4 nono

 ID      GRADE
--- ----------
  1         56
  2         76
11         80

     ③     左外接测试:显示所有人的成绩,如果没有成绩,也要显示该人的姓名和id号,成绩显示为空。
             如果左侧的表,完全显示,就是左外连。两种写法:
             select stu.name,stu.id,exam.grade from stu left join exam on stu.id=exam.id;左侧的表全部显示
             select stu.name,stu.id,exam.grade from stu,exam where stu.id=exam.id(+);用null填补右侧的表
     ④     右外连测试:显示所有的成绩,如果没有名字匹配,显示空。
             select stu.name,stu.id,exam.grade from stu right join exam on stu.id=exam.id;右侧的表全部显示
             select stu.name,stu.id,exam.grade from stu,exam where stu.id(+)=exam.id;用null填补左侧的表
     ⑤     完全外连测试:显示所有成绩和所有人的名字,如果没有相应的匹配值,则显示空。
             select stu.name,stu.id,exam.grade from stu full outer join exam on stu.id=exam.id;

数据库实例的创建

14,数据库实例的创建
     ①     使用向导工具来创建。
             Database Configuraton Assistant\创建数据库\一般用途\全局数据库名\直接下一步\设置口令\选择存储机制——文件系统\指定存储位置——默认\直接下一步\下一步\下一步\下一步\完成
     ②     手工创建。没有资料,略。
     注意:当我们创建完一个新的数据库实例的时候,在服务中就有两个服务被创建,这时你根据实际需要去启动相应的数据库实例。
               在同一台机器,可以同时启动多个数据库实例,在登录或连接的时候,需要指定主机字符串。
               两个不同的数据库实例中,即使是同名方案,也互不相关。

第2讲完