数据库基础(二)

四、更新数据

  插入数据后，我们需要对插入的数据进行更改，这时可以使用UPDATE或MERGE语句来修改。使用UPDATE语句时，可以使用表达式更新列值，也可以使用子查询更新一列或多列的数据。

1.      用UPDATE来更新数据时，需要注意：

1)       如果要更新数字列，则可以直接提供数字值；如果要更新字符列霍日期列，则数据必须用单引号引住。

2)       当更新数据时，数据必须要满足约束规则。

3)       当更新数据时，数据必须要与列的数据类型匹配。

2.      使用表达式更新数据

1)       具体语法

  UPDATE TABLE SET <column>=<value> [,<column>=<value>…] [WHERE <condition>];

2)       将basetab的msisdn是13913896206的更新为13815427202且paytype为0

update basetabset paytype=0,msisdn=’13815427202’where msisdn=’13913896206’;

建议：大家可以养成一个好的习惯，在update和delete语句时就算是不想加where条件，也希望写成 where1=1；这样就不是忘了些where 而导致全部更新或删除了。

3.      使用子查询更新数据

   当使用update语句更新数据时，不仅可以使用表达式或数值直接更新数据。也可以使用子查询更新数据。

 如：update basetab set paytype=(select paytype from basetrab where msisdn=’8613913896206’)where msisdn=’8613815427202’;

 

4.      使用MERGE语句来进行更新数据。

1)       具体例子

有两张表，basetab和 basetab_exp表，要如果msisdn在basetab中存在，这用basetab_exp对basetab中更新；如果msisdn不在basetab则用basetab_exp数据插入basetab中。

merger into basetab b using basetab_exp be on (b.msisdn = be.msisdn)

when matched then

update set b.paytype = be.paytype,b.imsi = be.imsi

when not matched then

insert (b.msisdn,b.imsi,b.paytype)values(be.msisn,be.imsi,b.paytype);

2)       例子说明

merger into 子句指名了合并操作的目标表（即行要合并到的表）。上面的例子为basetab表。

using……on 子句指定了一个表连接。上面的例子为basetab_exp表，并通过msisdn来连接。

when matched then 子句指定了当一行满足using……..on子句的条件时要执行的操作。上面的例子执行的操作为update.

when not matched then 子句指定了不满足using……on 子句的条件时要执行的操作。上面的例子执行的操作为insert.

 

五、检索数据

当表中已经有数据了，我们需要查看数据的时候，就需要用到select语句了。和select相关的主要关键字有：from、where和order by。

select关键字告诉数据库你想要那些列。

from告诉数据库这些列所在的表。

where告诉数据库想为所选择的信息做出怎样的限制。

order by 告诉Oracle按所给出的顺序返回信息。

下面说明一下简单表查询以及一些和查询有关的知识点。

1.      间单表查询

1)       查询手机号为8613913896206的用户信息

  select msisdn, paytype frombasetab where msisdn=’8613913896206’;

2)       查询所有paytype为0的用户的定购关系信息，按照msisdn降序序排序

select msisdn,serviceid,nextchargetime from subscribeinfo s，basetab b

where s.msisdn=b.msisdn and paytype=0 order by msisdn desc,

说明：关键字desc代表降序(descending) asc代表升序，不加asc默认是升序。

      s代表subscribeinfo的别名，b代表basetab的别名。

2.      单值的逻辑说明

paytye = 0  paytype 等于0

paytye >0  paytype  大于 0

paytye >=0  paytype 大于等于0

paytye <0  paytype 小于0

paytye <=0 paytype 小于等于0

paytye !=0 paytype 不等于 0

paytye ^=0 paytype  不等于 0

paytye <> paytype  不等于 0

说明：因为有些键盘没有惊叹号(!)或加字记号(^),所以Oracle允许使用三种输入不等运算符的方式。

注意：当使用大于和小于运算符比较存储在字符数据类型列中的数字时，要小心。所有的varchar2和char列的值在比较中都被看做是字符。因此，存储在这些类型列中的数字将按字符串而不是数字进行比较。如果列数据类型为NUMBER，那么12大于9。如果为字符列，则0大于12，因为字符’9’大于字符’1’。

3.      运算符like说明

Sql的最强大的功能之一是提供了称为LIKE的模式匹配运算符，该运算符能搜索数据库列的每一行来查找你所描述的模式值。它使用两个特殊的字符来指示要进行哪种匹配。一个为百分号，称为通配符(wildcard)，还有一个是下划线，称为位置标示符(position marker)。

1)       百分号(%)表示此处可接受任何内容，包括1个字符、100个字符，或无字符。

2)       下划线( _ )表示此处可以接受一个字符，不为无字符。

       如：select * from basetab where msisdn like ‘861381542__%’;

3)       转义符说明

　　　如：create tabletest1(test varchar2(10));

insert intotest1 values('_nihao');

insert intotest1 values('n_ihao');

　　　想查出’_nihao’这条记录

　　　select * from test1where test like ‘t_%’ escape ‘t’; t为转义符’_’被转义。

　　　想查出’n_ihao’这条记录

　　　select * from test1where test like ‘nl_%’ escape ‘l’; l 为转义符。　　　

4)       有时需要将字符串连接起来，连接字符串是使用”||”操作符来完成的。当连接字符串时，要在字符串中加入数字值，”||”后面可以直接加入数字，如果要加入字符或日期，则需要用单引号引住。

4.      NULL 与 NOT NULL

IS NULL 指示Oracle识别数据为空的列，IS NOT NULL指示Oracle识别数据不为空的列　为NULL不能就认为是０，NULL表示未知，

Oracle允许将关系运算符(=、!=等等)与NULL连用，但这种比较不返回有意义的结果。与NULL值比较要使用IS 和 IS NOT。

1)       使用NVL函数处理NULL

NVL函数用于将NULL转变为实际值，其语法格式为NVL(expr1,expr2).如果expr1是NULL，则返回expr２，如果不是NULL则返回expr1；

2)       使用NLV2函数处理NULL值

NVL2，该函数也用于处理NULL，其语法格式为NVL2(expr1, expr2, expr3). 如果expr1不是NULL,则返回expr２；如果expr1是NULL,则返回expr３。

5.      值列表的简单测试

1)       对数字的逻辑测试

　　  paytye IN(1,2,3)     paytype在列表(1,2,3)中

　　　paytye NOTIN(1,2,3)  paytye不在列表（１，２，３）中

　　　paytye BETWEEN 6 AND 10paytye等于６、１０或在６到１０之间的任何数

paytye NOTBETWEEN 6 AND 10 paytye小于６或大于１０

2)       对字母（或字符）的测试

　　　msisdn IN (‘A’,’C’,’F’)   msisdn在列表(‘A’,’C’,’F’)中

　　　msisdn NOT IN(‘A’,’C’,’F’)     msisdn不在列表(‘A’,’C’,’F’)中

　　　msisdn BETWEEN ‘B’AND ‘D’ msisdn等于’B’、’D’，或在’B’、’D’之间

　　　msisdn NOT BETWEEN ‘B’AND ‘D’  msisdn为小于’B’或大于’D’的任何字符

6.       子查询

1)       返回单值

如：select * from parentserviceinfo  p where p.serviceid = ( select pserviceidfrom serviceinfo  where serviceid=’10111’);

2)       返回列表

　　    如：select * from basetab where msisdn in (select msisdn fromsubscribeinfo);

7.       group by 和having的用法

1)       group by子句用于对查询结果进行分组统计。

　　　　如：selectpaytype,count(*) from basetab group by paytype;

2)       Having子句的功能和where子句很相似，只是它的逻辑仅和分组函数的结果有关，而不与单个行的列和表达式有关，单个行仍然可被where子句选择。

如：select paytype,count(*) from basetab groupby paytype having count(*)>6;

说明：上述查询语句中有几个子句。下面列出Ｏracle用来执行这些子句的规则以及执行的顺序：

a)       根据where子句选择行。

b)       根据group by子句组合行

c)       为每个组计算分组函数的结果

d)       根据having子句选择和排除组

e)       根据order by子句中的分组函数的结果对组进行排序，order by 子句必须使用分组函数，或者使用在groupby子句中指定的列。

8.      EXISTS及其相关子查询的使用

　EXISTS用来测试存在状态，它以子查询可能放置IN的方式放置，不同之处在于，它是对从一个查询返回的行的逻辑测试，而不是对行本身的逻辑测试。

　如：select msisdn,serviceid from subscribeinfoA where EXISTS (select * from subscribeinfo B where A.msisdn=B.msisdn group byB.msisdn having count(B.serviceid)>1) order by msisdn,serviceid;

用IN替换

Selectmsisdn,serviceid from subscribeinfo where msisdn in (select msisdn fromsubscribeinfo group by msisdn having count(serviceid)>1) order bymsisdn,serviceid;

9.      外部连接

1)       right outer join

如：select su.msisdn,su.nextchargetime,s.serviceid from subscribeinfo suright outer join serviceinfo s on su.seriviceid=s.serviceid;

select su.msisdn,su.nextchargetime,s.serviceid from subscribeinfo su,serviceinfo s where su. serviceid (+)=s. serviceid;

作为外部连接语法的一部分使用了on子句。可以使用using子句及表共同的拥有的列名来替换on子句，不要用表名或表别名限制此列名。

select msisdn,nextchargetime,serviceid from subscribeinfo  right outer join serviceinfo  using(serviceid);

2)       left outer join

如：select su.msisdn,su.nextchargetime,s.serviceid from serviceinf sleft outer join subscribeinfo su on s.seriviceid=su.serviceid;

select su.msisdn,su.nextchargetime,s.serviceid from subscribeinfosu,serviceinfo s where su.serviceid =s.serviceid (+);

3)       full outer join

如：select su.msisdn,su.nextchargetime,s.serviceid from serviceinf sfull outer join subscribeinfo su on s.seriviceid=su.serviceid;

10. 合并查询

1)       UNION

UNION操作符用于获取两个结果集的并集。当使用该操作符时，会自动　　　　　　　　　　　　　　　　　　　　　　　　　　去掉结果集中的重复行，并且会以第一列的结果进行排序。

　　  select msisdn,paytype from basetab wheremsisdn=’86131%’

union

select msisdn,paytype from basetab where paytpe=0;

2)       UNION ALL

UNION ALL 操作符用于获取两个结果集的并集。但与UNION操作符不同，该操作符不会取消重复值；也不会按照任何列进行排序。

3)       INTERSECT

      INTERSECT操作符是取两个结果集的交集

select msisdn,paytype from basetab where msisdn=’86131%’

      intersect

      select msisdn,paytype from basetab where paytpe=0;

4)       MINUS

       MINUS操作符是取两个结果集的差

select msisdn,paytype from basetab where msisdn=’86131%’

       minus

        select msisdn,paytype from basetab wherepaytpe=0;

 

六、删除数据

  当要删除表中某行的数据时，可以使用DELETE语句。使用该语句既可以删除一行数据，也可以删除多行数据。

 DELETE FROM T <table> [WHERE <condition>];

table 表示表名，condition用于指定条件子句。

1.      删除满足条件的数据

   如：delete from baset where msisdn=’8613112345%’;

2.      使用子查询删除数据

   如：delete from subscribeinfo where serviceid in (select serviceid fromserviceinfo );

注意：在主从表中，要删除主表数据是，要确保从表不存在相关记录。