ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 深入浅出学Oracle&JDBC 之 第二部分:SQL【私塾在线原创】

深入浅出学Oracle&JDBC 之 第二部分:SQL【私塾在线原创】

原创 Linux操作系统 作者:裂痕010 时间:2012-02-29 11:23:20 0 删除 编辑

SQL

SQL 是什么?
SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。(同时也是数据库脚本文件的扩展名。 )
 
SQL 能干什么?
   SQL 可创建新数据库;                  SQL 可在数据库中创建新表;
   SQL 可在数据库中创建视图;        SQL 可在数据库中创建存储过程;
   SQL 可以设置表、存储过程和视图的权限。
 
   SQL 面向数据库执行查询;           SQL 可从数据库取回数据;
   SQL 可在数据库中插入新的记录; SQL 可更新数据库中的数据;
   SQL 可从数据库删除记录;
  
 
   一句话:SQL是一种数据库语言,用于完成所有数据库操作。
SQL 有什么?
   SQL语言包含4个部分:
        数据定义语言 (DDL),如:CREATE、DROP、ALTER等语句。
    数据操作语言 (DML),如:INSERT、UPDATE、 DELETE等语句。
   数据查询语言 (DQL),如:SELECT等。
   数据控制语言 (DCL),如:GRANT、REVOKE等语句。
 
SQL 标准:
 1 SQL-86 ANSI (即美国国家标准) 1986年10月
 2 SQL-89 ANSI 1989年
 3 SQL-92 ANSI 1992年 即SQL2
 4 SQL-99 ANSI 1999年 即SQL3
 5 SQL:2003 、SQL:2006、SQL:2008
 
 6 SQL Server的SQL扩展叫T-SQL,其遵循ANSI制定的 SQL-92标准。
 7 Oracle 的SQL 扩展叫PL-SQL,其遵循ANSI制定的 SQL-92标准。
 8 MySQL同样也遵循ANSI制定的SQL-92标准。

SQL-3 HelloWorld


 

1.2、使用SQL创建数据库表

--学生表

java代码:
create table TBL_STUDENT
(SNUM  NUMBER not null ,  SNAME VARCHAR2(100)   ,
  AGE   NUMBER ,  SEX   CHAR(1) ,
  constraint PK_STUDENT primary key (SNUM) );
 
--课程表

java代码:
create table TBL_COURSE
(CNUM  NUMBER not null,  CNAME VARCHAR2(100) ,
  constraint PK_COURSE primary key (CNUM));
--成绩表

java代码:
create table TBL_GRADE
(SNUM  NUMBER not null  ,  CNUM  NUMBER not null  ,
  SCORE NUMBER     ,
  constraint PK_GRADE primary key (SNUM, CNUM) );
 Oracle  SQL 语句不区分大小写

2、CURD(增删改查)

新增

java代码:
insert into TBL_STUDENT(Snum,Sname,AGE,SEX) values (1,'zhang',1,'1');
Commit;
 
修改

java代码:
update TBL_STUDENT set AGE=2 where SNUM=1
Commit;
删除

java代码:
delete from TBL_STUDENT where snum=1
Commit;
 
查询全部

java代码:
select * from TBL_STUDENT
按条件查询

java代码:
select * from TBL_STUDENT where snum=1
 

SQL-4 数据类型

1 、字符类型
 
• CHAR 一个定长字符串,当位数不足自动用空格填充来达到其最大长度。如非NULL的 CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。
 
• VARCHAR2 目前这也是 VARCHAR 的同义词。这是一个变长字符串,与 CHAR 类型不同,它不会用空格填充至最大长度。VARCHAR2(12)可能包含 0~12字节的信息 。VARCHAR2最多可以存储4,000 字节的信息。
CHAR和VARCHAR2的比较
         CHAR(4)          “A“ 实际在数据库中存储为 "A   “
                                  “ABCDE”超长报错
         VARCHAR2(4)   “A” 存储的还是“A”
                                  “ABCDE”超长报错
         汉字:每个汉字占多少字节,要看具体的编码方式,如UTF-8(1-3字节)、GB2312(2字节)、GBK(2字节)、GB18030(1、2、4字节)
2 、数字类型
 
• NUMBER:该数据类型能存储精度最多达38位的数字。每个数存储在一个变长字段中,其长度在0~22字节之间。 Oracle的NUMBER类型精度很高, 远远高于许多编程语言中常规的FLOAT和DOUBLE类型。
 NUMBER( p,s )   p 表示精度(总长度)   s 表示小数位置   且四舍五入
 
      
java代码:
 NUMBER(10,3) 10是总长度,3是小数位数的长度
123.456
123.4567 :将存储为 123.457
12345679.899 :精度超长了,10是总长度,3是小数位, 整数位为10-3=7位
 
     NUMBER(10)==NUMBER(10,0)     java.lang.Integer
     NUMBER(19)==NUMBER(19,0)     java.lang.Long
3 、日期类型
 
•  DATE 一个7字节的定宽日期/时间数据类型。其中总包含 7个属性,包括:世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。
• TIMESTAMP 一个 7 字节或 12.字节的定宽日期/时间数据类型。它与 DATE 数据类型不同,因为 TIMESTAMP 可以包含小数秒(fractional second);带小数秒的 TIMESTAMP 在小数点右边最多可以保留9位。
4 、二进制及大文本数据
• BLOB    (binary large object)在Oracle9i及以前的版本中, 这种数据类型允许存储最多4GB的数据, 在Oracle 10g及以后的版本中允许存储最多(4GB)×(数据库块大小)字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据,如果要存储电子表格、字处理文档、图像文件等就很适合采用
• CLOB  Character Large Objec t 在Oracle9i及以前的版本中, 这种数据类型允许存储最多4GB的数据, 在Oracle 10g及以后的版本中允许存储最多(4GB)×(数据库块大小)字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。

SQL-5 DDL

是什么:数据库模式定义语言 DDL(Data Definition Language) 是用于描述数据库中要存储的现实世界实体的语言。
 
能干什么:创建 / 删除 / 修改表、索引 ……
 
有什么  (不同数据库 DDL 可能不一样,需要参考各个数据库的 SQL 手册)
 
    CREATE TABLE          创建表   (主键、外键、约束、级联)
    DROP TABLE               删除表
    ALTER TABLE             修改表
    CREATE VIEW             创建视图
    DROP VIEW                  删除视图
    CREATE INDEX            创建索引
    DROP INDEX                删除索引
    CREATE SEQUENCE  创建序列
   ……
1 、创建表
    语法
 create table 表名 (
           字段名 类型 [not null] [default 默认值 ],
       字段名 类型 [not null],
     [constraint pk_ 表名   primary key( 主键名 ),]         
           [constraint unique_ 表名 _ 字段名   unique( 字段名 ),]
           [constraint check_ 表名 _ 字段名   check( 表达式 ),]
           [constraint check_ 表名 _ 字段名   check( 表达式 ),]
     [constraint  fk_ 表名 _ 参照表名  
                               foreign key( 当前表外键名 )   references 参照表 ( 参照表主键名 )
                               on delete [cascade|no action|set null]]
      )
1.1 、关系数据结构
     字段名                    不允许是 Oracle 关键字
     类型                       Oracle 数据类型
     not null                不允许为 null ,默认允许
     default  默认值     指定默认值,不知道默认就是 null
 
1.2 、关系约束(可选)
  constraint pk_ 表名   primary key( 字段名 :指定主键字段
 
  constraint unique_ 表名 _ 字段名   unique( 字段名 :唯一约束,即该列不允许重复
 
 constraint check_ 表名 _ 字段名   check( 表达式 :检查约束,如(age<100)
 
 [constraint  fk_ 表名 _ 参照表名  
                      foreign key( 当前表外键名 )     references 参照表 ( 参照表主键名 )
                      on delete [cascade|no action|set null]]   :指定关联关系
2 、修改表
      2.1 、重命名表
      alter table 表名 rename to 新表名;
      2.2 、新增一列
      alter table  表名
              add (字段名 类型 [not null]  [default 默认值],
                      字段名 类型 [not null]  [default 默认值],……);
      2.3 、重命名一列
      alter table  表名  rename column 原来字段名 to 新字段名;
      2.4 、修改一列
      alter table  表名
               modify (字段名  新类型  [not null]  [default 默认值],
                            字段名  新类型  [not null]  [default 默认值],……);            
       2.5 、删除一列
       alter table 表名   drop  column  字段名;
             
 
      
 

SQL-5 DDL-索引


 
 
4.5 、创建索引语法
 
     创建索引的标准语法
          CREATE INDEX 索引名 ON 表名 (字段名)
     创建唯一索引
          CREATE unique INDEX 索引名 ON 表名 (字段名)
     创建组合索引
               CREATE INDEX 索引名 ON 表名 (字段名1,字段名2)
     创建反向索引
       CREATE INDEX 索引名 ON 表名 (字段名) reverse
4.6 、使用索引时注意事项:
 
           索引能改善查询性能,但同时也降低了插入、删除和修改的性能,因为执行这些 操作需要更新索引。
       
           不要所有列都建索引,那些经常查询的数据应该建立索引。
          
      可以索引多个列,如(性别,姓名) 但根据之前介绍的只有在前缀查找时才能 利用索引,因此如果您执行( select * from tbl where 姓名 =  aaa  )将无法利用索 引,而( select * from tbl where 性别 =  1  )将能利用索引
       
            如果在索引列上使用函数也无法利用索引,如 (select * from tbl where upper( 姓名 _=  AAA  ) 也无法利用索引。
5 、创建序列
               序列号生成器,可以为表中的行自动生成序列号,产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值,可以在插入语句中引用,也可以通过查询检查当前值,或使序列增至下一个值。
 
一般用于生成流水号、如用户编号、订单流水号等等

SQL-5 DDL-序列

 
5.1 、创建序列
  
java代码:
   CREATE SEQUENCE 序列名
        [INCREMENT BY n]
        [START WITH n];
 
5.2 、删除序列
DROP SEQUENCE 序列名;

SQL-6 DML

DML 是什么:Data Manipulation Language,数据操纵语言,命令使用户能够操作已有数据库中的数据的计算机语言。
 
DML 能干什么:增删改
 
 
DML 有什么  不同数据库DML可能不一样,需要参考各个数据库的SQL手册,但一般具有如下语句
 
    insert      新增
    update    修改
    delete     删除

SQL-6 DML-update

Update (更新数据)
    语法: Update 表名 set 列名 =  (, 列名 =  )*  (where 条件 )*
 
1 、更新表中特定行

java代码:
update tbl_student set age=2,sex=‘0’ where snum=1;
 
2 、更新表中所有行:初始化时可能用用

java代码:
Update tbl_student set age=2,sex=‘0’;
Delete (删除数据)
     语法: Delete from 表名 (where 条件 )*
 
1、从表中删除特定行数据

java代码:
Delete from tbl_student where snum=1;
 
2、从表中删除所有行
Delete from tbl_student;
 
3、截断,更快的从表中删除所有行 -不保留日志,1、不能回滚;2、快
Truncate table TBL_Student;

SQL-7 DQL

是什么:Data Query Language 数据查询语言
 
能干什么:数据检索
 
有什么:
SELECT 查询字段
FROM 表
[ WHERE 查询条件]
[ GROUP BY 分组字段]
[ HAVING   分组条件]
[ ORDER BY 排序字段 [ ASC | DESC ] ] 
                                         ASC 升序,默认 ; DESC 降序
一、最简单查询
 
1 、检索单列
     
java代码:
select snum from tbl_student;
2 、检索多列
    
java代码:
 select snum,sname from tbl_student;
3 、检索所有列
    
java代码:
 select * from tbl_student;
二、排序检索数据
 
1 、按单列排序(查询所有学生,按学号降序排序)
      select * from tbl_student order by snum desc;
 
2 、按多列排序(查询所有学生,首先按年龄升序排序,然后再按性别降序排序)
      select * from tbl_student order by age, sex desc;
 
 
 
注:多列排序时,是先按第一个字段排序,排好后再按照第二个字段对已排好序的记录 再排序。
 
 uuid  name    
      2      3
      2      2
      1      1

  
三、基本数据过滤
 
3 、检索及格的学生且小于等于 80 分的;

java代码:
Select * from tbl_grade where score >=60 and score <=80;
Select * from tbl_grade where score between 60 and 80; (检索包括开始和结束)
4 、检索没填性别的所有学生;无值不是 0 或空字符串 ’’ ,而是 NULL

java代码:
Select * from tbl_student where sex is null;
 
四、高级数据过滤
1  not and or
检索及格的学生且小于80分的;

java代码:
Select * from tbl_grade where score >=60 and score <80;
检索不及格的或缺考的

java代码:
Select * from tbl_grade where score <60 or score is null;
检索及格的

java代码:
Select * from tbl_grade where not(score <60 or score is null);
2  not and or
    优先级顺序(从高到低) not and or
    混用时最好加括号 ()
 
3  in 运算符
 
查询学号为 1  2 的学生信息
Select * from tbl_student where snum=1 or snum=2
Select * from tbl_student where snum in(1,2)
五、用通配符进行过滤
 LIKE 操作符
用于模糊匹配,可使用的通配符如下: 
%  0 个或多个字符;
:一个字符。
    
 
1.1  % 通配符(匹配 0 个或多个)(查询姓张的所有学生)
Select * from tbl_student where sname like   % 
 
1.2  % 通配符(查询姓名中包含 a 的所有学生)
Select * from tbl_student where sname like  %a% 
 
1.3  _ 通配符(查询姓张的且姓名是两个字符的的所有学生)
Select * from tbl_student where sname like   _ 
六、创建计算字段
 
计算字段:或计算临时字段,如在查询学生时需要查询学生的总成绩、平均成绩,这些 而外的数据需要有个字段来存放,这个字段就是计算字段
 
1 、拼接字段 使用 || 连接字符串
检索所有学生,学生学号和姓名放一列显示;
select snum || '--' || sname,age,sex from tbl_student;
 
2 、使用别名
select snum || '--' || sname as sname,age,sex from tbl_student;
 
  
 
 
 
3 、统计某行不同值的数量 ( 去除重复行 )
查询学生的年龄,要求不重复
     
java代码:
select distinct age from student;
统计不同年龄的数量
     
java代码:
select count(distinct age) from student;
 
九、分组数据
1 、统计每个学生的总分、平均分、最高分、最低分
     
java代码:
select snum, sum(score),avg(score),max(score),min(score) from tbl_grade
           group by snum;
 
2 、多分组字段类似于排序,先按第一个分组,再对已分好的组按第二个字段分组
    统计不同年龄、不同性别的人数
    
java代码:
 select age,sex,count(*) from tbl_student group by age,sex
3 、过滤分组
WHERE是过滤字段,而无法对分组进行过滤,如过滤掉[最低分小于60]的学生WHERE无法做到,需要使用having 来完成
 
过滤掉最低分小于60的学生

java代码:
select snum, sum(score),avg(score),max(score),min(score) from tbl_grade
           group by snum having min(score) >=60;
 
检索至少学过两门课的学生

java代码:
select snum from tbl_grade group by snum having count(*)>=2
 
4、分组和排序
按照学生总分降序排序

java代码:
Select snum, sum(score) ,avg(score),max(score),min(score) from tbl_grade
           group by snum order by  sum(score) desc;
 
6  group by 规则
 
  select 分组字段/聚合函数 from 表
             where条件 group by 分组字段 having 分组条件
 
 1、where条件运行于分组之前,where条件中不允许出现聚合函数
 2、如果分组字段中含有NULL值,则NULL值作为一个分组
 3、having是对组进行过滤
十、使用子查询(嵌套查询)
4、查询有一门及格的学生

java代码:
select * from tbl_student s
     where 60 < any(select score from tbl_grade g where s.snum =g.snum);
或
select * from tbl_student s
     where 60 < some(select score from tbl_grade g where s.snum = g.snum);
 
any  some  all 区别
any表示子查询返回值中的任意一个值(any是等于N个or语句)
    expr>any(单列多行子查询),expr大于子查询中任一返回值,   也就是只要大于子查询最小返回值就可以了
some在此表示满足其中一个的意义,是用or串起来的比较从句。 some和any用法意义是一样的。
all表示子查询返回值中的所有值 (all是等于N个And语句)
    expr>all(单列多行子查询),expr大于子查询中所有返回值,也就是必须要大于子查询中最大返回值才行,
5  in 子查询
in 按子查询返回值分为:单行单列、多行单列、单行多列、多行多列
     in 按是否与主表连接分为:相关子查询、无关子查询
 
     5.1 、单行单列子查询
  查询学生的成绩,要求显示学生的姓名

java代码:
       select  (select sname from tbl_student s where s.snum=g.snum) as sname,
                   (select cname from tbl_course c where c.cnum=g.cnum) as cname,
                   score
      from tbl_grade g


    5.2 、多行单列子查询
       查询男同学的成绩
        
java代码:
select * from tbl_grade g
        where snum in (select snum from tbl_student where sex=‘0’)
    5.3 、单行多列或多行多列子查询
      查询男同学的成绩
        
java代码:
select * from tbl_grade g   where (snum,’0’) in (select snum,sex from tbl_student)
6  exists 子查询
只要子查询找到一条记录,结果就是true
 
6.1 、查询选修过课程的学生
   select * from tbl_student s
        where exists (select * from tbl_grade where s.snum=g.snum)
 
6.2 、查询没有选修过 1 号课程的学生的信息
    
java代码:
select *  from tbl_student s
      where not exists
(select * from tbl_grade g where g.cnum=1 and s.snum=g.snum)
 
6.3 、查询男同学的成绩
    
java代码:
select * from tbl_grade g
      where exists (select * from tbl_student s where s.snum=g.snum and sex=‘0’)
 
6.4 、检索学全了课程的学生
分析:不可能【找不到一门课,这么课找不到成绩】

java代码:
select s.* from tbl_student s where not exists(
    select * from tbl_course c where not exists(
        select * from tbl_grade g where g.snum=s.snum and c.cnum=g.cnum
    ))
 
6.5 、检索没学全课程的学生
分析:可能【找到  一门课 找不到 成绩】

java代码:
select s.* from tbl_student s where exists(
    select * from tbl_course c where not exists(
        select * from tbl_grade g where g.snum=s.snum and c.cnum=g.cnum
    ))
7 、并操作(查询小于 60  大于 90 的)

java代码:
select * from tbl_grade where score <60
union
select * from tbl_grade where score>90
8 、联接表
1 、为什么使用联接:一 x` 张表不足以返回你需要的所有数据
2 、笛卡尔积(没有条件,结果记录数为 A 表记录数× B 表记录数)
select s.*,g.* from tbl_student s,tbl_grade g
      笛卡尔积禁止使用,因此不要忘了加Where条件
3 、外部联接
3.1 、左外联接:左边的全部显示,右边没有显示为 null
查询所有学生及成绩,即使学生没选课也显示出来

java代码:
select s.snum,s.sname,g.cnum,g.score from tbl_student s
 left outer join tbl_grade g on s.snum=g.snum
或
select s.snum,s.sname,g.cnum,g.score from tbl_student s, tbl_grade g
where  s.snum=g.snum(+)
3.2 、右外联接:右边的全部显示,左边没有显示为 null

java代码:
select s.snum,s.sname,g.cnum,g.score from tbl_grade g
 right outer join tbl_student s on s.snum=g.snum
或
select s.snum,s.sname,g.cnum,g.score from tbl_student s, tbl_grade g
where  g.snum(+)=s.snum
3.3 、全外联接

java代码:
select s.snum,s.sname,g.cnum,g.score from tbl_grade g
 full outer join tbl_student s on s.snum=g.snum
补充(表达式及Oracle常用函数)
1、case表达式:case … when … then … when … then … else … end
    select snum,sname,
              case sex when '1'  then '男' when '0' then '女' else '未知' end
     from tbl_student;
 
2、NVL(c1, c2):逻辑等价于 IF c1 is null THEN c2 ELSE c1 END
3、ROWID:ROWID就是唯一标志记录物理位置的一个id,可理解为磁盘地址
4、ROWNUM:Oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,

事务

事务是什么
        用来维护数据库完整性,它保证成批的 SQL 要么全做,要么全不做。
 
1 、事务特性( ACID )事务必需满足 ACID ,缺一不可
 
    原子性( Atomicity ):即事务是不可分割的最小工作单元,事务内的操作要么全做,要么全不做;
     一致性( Consistency ):在事务执行前数据库的数据处于正确的状态,而事务执行完成后数据库的数据还是处于正确的状态,即数据完整性约束没有被破坏;如银行转帐,A转帐给B,必须保证A的钱一定转给B,一定不会出现A的钱转了但B没收到,否则数据库的数据就处于不一致(不正确)的状态;
    隔离性( Isolation ):并发事务执行之间无影响,在一个事务内部的操作对其他 事务是不产生影响,这需要事务隔离级别来指定隔离性;
    持久性( Durability ):事务一旦执行成功,它对数据库的数据的改变必须是永久的,不会因为如遇到系统故障或断电造成数据不一致或丢失。
事务处理
  事务( transaction ):一组 SQL 语句;
  回滚( rollback       :撤销指定 SQL 语句的过程;
  提交( commit        :执行指定 SQL 语句;
  保留点( savepoint ):事务处理中设置的临时占位符,可以对保留点执行回滚
 
示例如下:
delete from tbl_student;
commit;-- 提交事务,全部删除了
 
 
delete from tbl_student;
rollback;-- 回滚了事务,删除被撤销了

SQL实战

1 、不连续数谜题 : 找到表中没有的最小的 TBL_NUM  num 
Num
1
3
4
7
7
 select  min(num1.num+1) from TBL_NUM num1
     where num1.num+1 not in (select num2.num from TBL_NUM num2);
 
 
 
2 、删除重复行 , 删除最先插入的
delete from tbl_num
  where rowid not in (select max(rowid) from tbl_num group by num)
3 、分页
 
--3 、分页
select s2.* from
  --2 、为检索出的数据加行号
  (select s1.*, rownum r from
      ( --1 、最内层检索真正的数据
        select * from tbl_student  where 1=1
       ) s1
   ) s2
-- 分页条件
where s2.r>=1 and s2.r<3
 
分页三步曲:
1、检索记录
2、加行号
3、根据行号对结果分页
 
 
1 、检索张三所有孩子
Select * from tbl_tree where puuid=(select uuid from tbl_tree where name ='张三')
2 、检索张四的兄弟
Select * from tbl_tree where puuid=(select puuid from tbl_tree where name ='张四')
3 、检索张三的孙子
Select * from tbl_tree where puuid in (
         select uuid from tbl_tree where puuid =(
                                                          select uuid from tbl_tree where name ='张三'))
4 、检索张三的孩子和孙子
Select * from tbl_tree where puuid=(select uuid from tbl_tree where name ='张三')
union
Select * from tbl_tree where puuid in (
         select uuid from tbl_tree where puuid =(
                                                          select uuid from tbl_tree where name ='张三'))
 
 
5 、找张三的子子孙孙(包括重孙等)(递归查询)
select * from tbl_tree
             where name!='张三‘
             start with name='张三'
             connect by prior uuid = puuid
--1 、递归开始:根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。  start with name=  张三  表示从张三开始
--2 、连接条件:[connect by] [prior] uuid = puuid,其中用prior表示上一条记录,比如 connect by prior uuid = puuid就是说上一条记录的uuid是本条记录的puuid,即前一条记录是当前记录的父亲。
--3 、递归方向: prior运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是自底向上。
--4 、过滤条件:where子句,用于对返回的所有记录进行过滤
 
-- 加层次 level
select lpad(name, level*2+2,' ') from tbl_tree
            connect by prior uuid = puuid
            start with name='张三'
5 、前边递归做法是 oracle 特有的,再介绍个通用的 code  ( 无递归 )
地区表

java代码:
create table TBL_AREA2
(UUID NUMBER not null,
  NAME VARCHAR2(100),
  CODE VARCHAR2(100),
  constraint PK_AREA primary key (UUID),
  constraint INDEX_AREA_CODE unique (CODE) using index);
 
insert into tbl_area2 values(1,'中国','01');
insert into tbl_area2 values(2,'上海','0101');
insert into tbl_area2 values(3,'深圳','0102');
insert into tbl_area2 values(4,'北京','0103');
insert into tbl_area2 values(5,'海淀','010301');
insert into tbl_area2 values(6,'朝阳','010302');
 
Code组成:由父的code+自己在兄弟里的大排名,每进入一层code长度加2
                    code列是唯一索引
6 、前边递归做法是 oracle 特有的,再介绍个通用的 code 
 
1 、检索中国的孩子
Select * from tbl_area where code like '01__'
2 、检索中国的孙子
Select * from tbl_area where code like '01____'
3 、检索中国的子和孙(不包括重孙)
Select * from tbl_area where code like '01__' or code like '01____'
4 、检索中国的子孙(包括重孙)
Select * from tbl_area where code like '01__%'
5 、查询厂洼属于的城市 ( 不好向上递归,在 java 里做 )
Select * from tbl_area
where code =(select substr(code,0, 4) from tbl_area where name='厂洼')
6、查询厂洼所属城市的所有区
Select * from tbl_area
where code like (select substr(code,0, 4) || '__' from tbl_area where name='厂洼')
Oracle 递归支持和 Code 法比较
--1、puuid只有在Oracle里才好用,从通用性上来看code更好
--2.1、puuid number(10)
--2.2、code varchar2(100) 层数和一个父最多有多少个子有限制
--2.3、code列的规则一旦定下来很难改变
--3、移动节点(包括子节点)
   puuid简单,只需更新父亲即可;
   code麻烦,不仅需要更新父亲,连孩子都得修改;
  oracle 内部支持法:把张四移到张五
    update tbl_tree set puuid=(select uuid from tbl_tree where name='张五')
    where name='张四';
  code 法:如把北京移动到上海下边 ( 首先把北京移动到上海下,然后更新北京的孩子 )
update tbl_area set code='010101'||substr(code,5) where code like '0103%';
数据库设计优化

 

JDBC

是什么?
    JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
     JDBC为工具/数据库开发人员提供了一个标准的API,据此可以构建更高级的工具和接口,使数据库开发人员能够用纯 Java API 编写数据库应用程序。
    
能干什么?
       标准的数据访问接口,可以连到不同的数据库;  
  发SQL查询字符串到数据库;  
  在Java应用程序中进行数据库的增删改查
 
有什么?
    驱动程序 Driver
    连接         Connection
    语句         Statement/PreparedStatement

 
HelloWorld
 
1 、开发环境和运行环境准备
     开发环境: java 工程 +oracle 数据库
     运行环境: java SE+oracle 数据库
2 、准备 jar 包: ojdbc14.jar  Oracle 驱动包)
 
3 、写客户端测试
 

java代码:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class ClientTest {
   
    public static void main(String[] args) throws ClassNotFoundException {
       //1. 注册加载一个 driver 驱动
        Class. forName ("oracle.jdbc.driver.OracleDriver");
        Connection conn = null;
        try {
//2 创建数据库连接
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String username = "test";
String password = "test";
conn = DriverManager. getConnection (url, username, password);
//3 创建一个 Statement (发送 sql )
Statement stat = conn.createStatement();
//4 执行 SQL
stat.execute("insert into tbl_student values(11,'zhang', 1, '1')");
//5 处理结果(无)
//5 关闭 Statement
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {//6 关闭连接
    conn.close();
} catch (SQLException e) { e.printStackTrace(); }
        }       
    }
}



Statement  PreparedStatement 区别
 
1  PreparedStatement 能防止 SQL 注入问题,而 Statement 是动态拼出SQL ,因此不 能解决;
 
2  PreparedStatement  SQL 进行预编译,因此如果我们采用绑定变量的SQL (语句 类似,数据不同)数据库只需解析一次(生成执行计划),因此效率要高;
 
3  Statement 由于没有采用绑定变量,因此可能每次都需要编译(生成执行计划),因 此对于那种语句类似但数据不同的 SQL 每次都需要编译,效率低;
 
4  PreparedStatement 开销要比 Statement 高, 因此如果 SQL 只执行一次,应该使用 Statement 
 
5 、在大多数情况下都应该使用 PreparedStatement 

java代码:
–Driver
–DriverManager
–Connection
–Statement
–PreparedStatement
–CallableStatement
–ResultSet
–DatabaseMetadata
–ResultSetMetadata
–Types
数据库元数据
    数据库元数据和结果集元数据
 
   1  DatabaseMetadata
使用conn.getMetadata方法获得;
提供了获取数据库相关数据信息的能力(如数据库对象)。
 
   2  ResultSetMetadata
使用 ResultSet.getMetaData方法获得;
        提供了各种方法去获得结果集元数据的相关数据信息,包括结果集的总行数
批量更新
     批量更新,即一次提交多条同构更新操作语句给数据库
     JDBC API 在批量更新之后只产生一个结果集对象
    以下均支持批量更新
         Statement
         PreparedStatement
         CallableStatement
Statement 批量更新
 
语法
    
java代码:
Statement  stmt = con.createStatement();
   stmt.addBatch(sqlStr);
示例

java代码:
    con.setAutoCommit(false);
    Statement stmt = con.createStatement();
    stmt.addBatch(“insert into tbl_users values(1, ‘z1’, 1, ‘1’)”);
    stmt.addBatch(“insert into tbl_users values(2, ‘z2’, 1, ‘1’)”);
     int[] results = stm.executeBatch();
 
     con.commit();
PreparedStatement 批量更新
     PreparedStatement 中使用批量更新时,一定要先设置好参数后,再使用   addBatch() 方法加入缓存,最后一起发送到数据库。
 
语法
    
java代码:
PreparedStatement pstmt = con.prepareStatement(sqlStr);
    pstmt.setXXX(..); 
    pstmt.addBatch(); 
    pstmt.excuteBatch();

示例
    
java代码:
String sql = “ insert into tbl_student values(?,?,?,?) ” ;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setInt(1, 1); pstm.setString(2, “zhang1”);
    pstm.setInt(3,1); pstm.setString(4,’1’);
    pstm.addBatch();
     int[] counts = pstm.excuteBatch(); con.commit();
Blob 和 Clob

 
1 、需要存储如图片、 word 文件等字节类型数据,请使用 Blob
2 、需要存储大文本数据,请使用 clob

java代码:
//1 、插入(通过 empty_clob() 来初始化 resourceValue )
String insertSql = "insert into tbl_resource" +
"(uuid, title, resourceValue) values(?,?, empty_blob() )";
 
PreparedStatement pstat = conn.prepareStatement(insertSql);
pstat.setInt(1, 1);
pstat.setString(2, "test");
pstat.executeUpdate();
pstat.close();
//2 、锁定要修改的数据行进行更新,注意 "for update" 语句
String selectForUpdateSql = "select resourceValue " +
         "from tbl_resource where uuid=? for update";
pstat = conn.prepareStatement(selectForUpdateSql);
pstat.setInt(1, 1);
 
ResultSet rs = pstat.executeQuery();
// 读取 blob 数据
Blob blob = null;
if(rs.next()) {
    blob = rs.getBlob("resourceValue");
}
// 将图片字节数据输出到 Blob 对象里
 
String fileName = "D:/Blue hills.jpg";
FileInputStream fis = new FileInputStream(fileName);
byte[] bytes = new byte[fis.available()];
fis.read(bytes);
fis.close();
 
OutputStream s = blob.setBinaryStream(0);
os.write(bytes);
os.close();
 
pstat.close();
String selectSql = "select * from tbl_resource where uuid=?";
PreparedStatement pstat = conn.prepareStatement(selectSql);
pstat.setInt(1, 1);
ResultSet rs = pstat.executeQuery();
// 读取 blob 数据
if(rs.next()) {
    String title = rs.getString("title");
    Blob blob = rs.getBlob("resourceValue");     
    byte[] bytes = blob.getBytes(1, (int) blob.length());
 
    File file = new File("D:\\Backup\\" + title + ".jpg");
    FileOutputStream fos = new FileOutputStream(file);
    fos.write(bytes);
    fos.close();
}
pstat.close();


Clob 新增 & 修改

 

java代码:
//1 、插入(通过 empty_clob() 来初始化 resourceValue )
String insertSql = "insert into tbl_content" +
     "(uuid, title, content) values(?,?, empty_clob() )";
PreparedStatement pstat = conn.prepareStatement(insertSql);
pstat.setInt(1, 1);
pstat.setString(2, "test");
pstat.executeUpdate();
pstat.close();
Clob 新增 & 修改
 
//2 、锁定要修改的数据行进行更新,注意 "for update" 语句
String selectForUpdateSql = "select content " +
    "from tbl_content where uuid=? for update";
pstat = conn.prepareStatement(selectForUpdateSql);
pstat.setInt(1, 1);
 
ResultSet rs = pstat.executeQuery();
// 读取 blob 数据
Clob clob = null;
if(rs.next()) {
      clob = rs.getClob("content");
}
//3 、更新 Clob 内容
clob.setString(1, "test 中文 ");
pstat.close();
Clob 查询
 
String selectSql = "select content from tbl_content where uuid=?";
PreparedStatement pstat = conn.prepareStatement(selectSql);
pstat.setInt(1, 1);           
 
ResultSet rs = pstat.executeQuery();
// 读取 C lob 数据
Clob clob = null;
if(rs.next()) {
    clob = rs.getClob("content");
}
           
System. out .println(clob.getSubString(1, (int) clob.length()));
 
pstat.close();


视频配套PPT,视频地址【Oracle数据库开发及SQL基础视频 】

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26715421/viewspace-717348/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-02-29

  • 博文量
    2
  • 访问量
    1748