关系型数据库常用总结

关系型数据库常用总结

西安聚合软件 作者：libajian

 

这篇指南是针对数据库比较薄弱的同事进行岗前培训的一个参考手册。全部是实战经验。

 

单表操作

  

普通查询：select classcode,classname from bookclass

查询总数：select count(classcode) from bookclass

          这里的count(classcode)要比count(*)的效率高一些

 

主从表

-

联合带分类的书本信息：

Select a.classcode,

c.classname,

a.bookcode,

a.bookname

from  book a

left join bookclass c on a.classcode=c.classcode

 

联合查询某个分类共有多少种书

Select  a.classcode as classcode,

c.classname as classname,

count(a.bookcode)

from  book a

left join bookclass c on a.classcode=c.classcode

group by classcode,classname

 

联合查询某个分类的书的总价钱（本数*单价）

Select 
       a.classcode as classcode,

c.classname as classname,     

sum(a.amount*a.price)  
  From book a
  Left Join bookclass c On a.classcode = b.classcode
  Group By a.classcode,c.classname

 

多表联合查询

   

Select a.classcode,(select classname from bookclass where classcode=a.classcode),a.bookcode,a.bookname  from book

严禁以上语法，这个效率很慢

    

     下面说说左右连接的用法。

     例如存在下面两张表

    

会员卡：card

Manid(会员id)

Cardcode（卡编号）

Codemoney（卡余额）

001

Card001

100

002

Card002

200

003

Card003

300

005

Card005

500

    

    

会员借书总价：book

Manid(会员id)

bookamount本数

bookmoney（书价合计）

001

1

1000

002

2

2000

004

4

4000

006

6

6000

    

     现在要查某会员的卡余额和借书总价的对比

1， 使用左连接

Selecta.manid,a.codecode,a.codemoney,b.bookamount,bookmoney

From card a

Left join book bon a.manid=b.manid

结果是

001

Card001

100

1

1000

002

Card002

200

2

2000

003

Card003

300

 

 

005

Card005

500

 

 

 

所有的会员中，办过会员卡的记录。其中bookamount和bookmoney为空表示没有借书。

2， 使用右连接

  Selecta.manid,a.codecode,a.codemoney,b.bookamount,bookmoney

From card a

Right join bookb on a.manid=b.manid

结果是

001

Card001

100

1

1000

002

Card002

200

2

2000

004

 

 

4

4000

006

 

 

6

6000

 

所有的会员中，借过书的记录。其中codecode和codemoney为空表示没有办会员卡。

3， 使用内连接

   Selecta.manid,a.codecode,a.codemoney,b.bookamount,bookmoney

From card a

Inner join bookb on a.manid=b.manid

结果是

001

Card001

100

1

1000

002

Card002

200

2

2000

 

所有的会员中，办过卡并且借过书的记录。

4， 使用外连接（全连接）

 Selecta.manid,a.codecode,a.codemoney,b.bookamount,bookmoney

From card a

All join book bon a.manid=b.manid

结果是

001

Card001

100

1

1000

002

Card002

200

2

2000

003

Card003

300

 

 

005

Card005

500

 

 

004

 

 

4

4000

006

 

 

6

6000

     显示所有的会员。

    

     相信大家对连接有了比较深刻的认识了。

    

     下面说说union 和union all

        经常我们需要将两个表的数据合并到一起进行处理，将业务问题简单化一些。

       比如有入库和出库记录表

       

物料

入库数

入库日期

苹果

100

2008-07-01

李子

20

2008-09-09

    

    

物料

出库数

出库日期

李子

300

2008-07-12

苹果

40

2008-08-09

    

     我们使用

     Select 物料 as 物料，入库数 as 数目，入库日期 as 日期，‘入库’ as 出入库类型 from 入库表

     Unionall

     Select物料 as 物料，出库数as 数目，出库日期as 日期，‘出库’ as 出入库类型

from 出库表

结果就是

物料

数目

日期

出入库类型

苹果

100

2008-07-01

入库

李子

20

2008-09-09

入库

香蕉

300

2008-07-12

出库

苹果

40

2008-08-09

出库

 

Union  是将两个表中重复的记录去掉。大家可以试试

Select 1 From dual
Union All
Select 1 From dual

Select 1 From dual
Union
Select 1 From dual

区别。一般在数据量较小的情况下，使用union可以，数据量大的情况下，切不可使用，因为其对每行每个字段都要比较，速度会超级慢。Union all 则不检查，只是一个合并的动作。

有了上面的结果，可能我们就需要将结果按照日期排序，就是一个出入库的台帐流水记录了。我们借用临时表比较好用一些。

下面说说临时表的用法

临时表（withtablename as ）

先看下sql

With temptalble as (

Select 物料 as 物料，入库数 as 数目，入库日期 as 日期，‘入库’ as 出入库类型 from 入库表

     Union all

     Select 物料 as 物料，出库数as 数目，出库日期as 日期，‘出库’ as 出入库类型

from 出库表

)

Select 物料，数目，日期，出入库类型 from temptalbe order by 日期

蓝色斜体部分就是union章节的代码，红色的是临时表的。相信大家看到这些就基本明白意思了。临时表可以同时定义多个，然后进行一些操作。像上面的代码也可以这样写

With temp_1 as (

Select 物料 as 物料，入库数 as 数目，入库日期 as 日期，‘入库’ as 出入库类型 from 入库表

),

Temp_2 as (

Select 物料 as 物料，出库数as 数目，出库日期as 日期，‘出库’ as 出入库类型

from 出库表

)

Select a.物料，（a.数目-b.数目） as 库存数目 from

temp_1

all join temp_2  b on a.物料=b.物料

又成了查询各个物料的实际目前库存数目了。

 

In、not in的用法

In的效率一般也比较慢，但一般数据量和一般的业务还是没有影响的。

In的意思就是涵盖在这个里面的意思。

比如：

Select * from 入库表 where 物料 in (‘苹果’，‘例子’)

 

可以这样使用，比如我们查询在连接章节的例子，我们只查询会员卡表中借过书的会员

Select 会员编码，卡号，卡余额 from 会员卡

Where 会员编码 in (select 会员编码 from  借书表 )

或者没有借过书的

Select 会员编码，卡号，卡余额 from 会员卡

Where 会员编码 not in (select 会员编码 from  借书表 )

 

Ibatis的技巧

1，$ 和 # 的区别

   $表示传入的数值按照原来的样子替换。传入什么就是什么。

   #和传入数值的类型有关系,ibatis会解析替换。

   例如：

   Select1 from dual where aa = $value$

   如果传入的是一个String s = “cccc”;

   那么上述sql就是 Select 1 from dual where aa = cccc

   如果传入的是 int s = 22;

   那么上述sql就是 Select 1 fromdual where aa = 22

  

   Select1 from dual where aa = #value#

   如果传入的是一个String s = “cccc”;

   那么上述sql就是 Select 1 from dual where aa = ‘cccc’  //ibatis根据类型解析过来了。是根据传入的hashmap的值类型进行解析的。

   如果传入的是 int s = 22;

   那么上述sql就是 Select 1from dual where aa = 22

   

 2，在sql中，我们可能为了工作需要多次用到同一块sql串。比如

Select

A1,a2,a3,a4,………………………………………

………….

From dual

 

其中红色部分超级长，但是好多地方引用，如果发生修改，就要到处修改。及其麻烦。

不用着急,ibatis有sql块定义，帮咱们解决了这个问题。

如：

<sql id="testSql">

   A1,a2,a3,a4,………………………………………

………….

</sql>

<select id=”xxx”>

Select

<include refid=" testSql "/>

From dual

 

</select>

3,

<isNotNull>和isPropertyAvailable的区别

<isNotNull>用于判断给这个map，进行了push值，但值是否为空。

isPropertyAvailable判断map或者类是否存在这个key，如果没有push，就是无效。

这两个都有反义用法 isnull  isnotPropertyAvailable。

还有isNotEmpty ，这些有效性验证的用法，大家可以查看ibatis的说明文档。

4， 迭代用法

     这个用法一般用于复杂一些的业务，比如 in 或者同一个字段多个or的用法

 

     <iterateprepend="and" open="(" close=")" property="m_code_list" conjunction="or">

                  char(b.CODE) = #m_code_list[]#

        </iterate>

 

     比如 m_code_list= {[“1”],[“2”],[“3”]}

     那么这句话ibatis解析为

     

     （

         char(b.CODE) = ‘1’ or char(b.CODE)= ‘2’ or char(b.CODE) = ‘3’

）

 

如果是in的用法就是

Cc in

<iterate prepend="and" open="("close=")" property="m_code_list"conjunction=",">

                  char(b.CODE) = #m_code_list[]#

        </iterate>

解析为

Cc in (‘1’,’2’,’3’)