首页 / 更多教程 / 面向对象数据库设计与应用 04 - 数据表操作与查询

面向对象数据库设计与应用 04 - 数据表操作与查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了面向对象数据库设计与应用 04 - 数据表操作与查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4910字，纯文字阅读大概需要8分钟。

内容图文

0003‘,‘推荐‘,‘服装‘); Insert into C##RD.RD_GOODS (GOOD_ID,DISCOUNT,GOODS,UNIT_PRICE,PURCHASE_DATE,GOOD_TYPE) values (‘0003‘,‘0.85‘,‘劲霸西服‘,165,‘2017-32-23‘, (select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0003‘));select GOOD_ID,GOODS,deref(GOOD_TYPE),(select STATUS from RD_COMMODITY_TYPES where RD_COMMODITY_TYPES.COMMODITY_TYPE_ID=‘0003‘) from RD_GOODS where GOOD_TYPE=(select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0003‘); select GOOD_ID,GOODS,deref(GOOD_TYPE),(select STATUS from RD_COMMODITY_TYPES where RD_COMMODITY_TYPES.COMMODITY_TYPE_ID=‘0003‘) from RD_GOODS where GOOD_TYPE=(select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0003‘); update RD_COMMODITY_TYPES set STATUS=‘热点‘ where COMMODITY_TYPE_ID=‘0003‘; select GOOD_ID,GOODS,deref(GOOD_TYPE),(select STATUS from RD_COMMODITY_TYPES where RD_COMMODITY_TYPES.COMMODITY_TYPE_ID=‘0003‘) from RD_GOODS where GOOD_TYPE=(select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0003‘);

②更新多条记录。将所有商品的折扣调整为0.8,将所有的进货日期调整2010年10月1日

select * from RD_GOODS;

update RD_GOODS set  DISCOUNT=‘0.80‘,PURCHASE_DATE=‘2010-10-01‘;

select * from RD_GOODS;

③删除单条记录。商品号为040002的商品已售完,在商品表中清除该商品的信息。

update RD_GOODS set  GOOD_ID=‘040002‘ where GOODS=‘劲霸西服‘;

select * from RD_GOODS;

 

delete from RD_GOODS where GOOD_ID=‘040002‘;

select * from RD_GOODS;

④删除所有记录。删除用户表中的所有信息。

select * from RD_MEMBERS;

 

delete from RD_MEMBERS;

select * from RD_MEMBERS;

⑤使用复合条件查询。需要了解商品类别为01，并且商品单价在2500元以上，的商品信息，要求以汉字标题显示商品号，商品名称，商品类别和价格。

update RD_COMMODITY_TYPES set COMMODITY_TYPE=‘02‘;

Insert into RD_COMMODITY_TYPES (COMMODITY_TYPE_ID,STATUS,COMMODITY_TYPE) values (‘0004‘,‘推荐‘,‘01‘);

Insert into C##RD.RD_GOODS (GOOD_ID,DISCOUNT,GOODS,UNIT_PRICE,PURCHASE_DATE,GOOD_TYPE) values (‘0004‘,‘0.8‘,‘直升飞机‘,165000,‘2017-32-23‘, (select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0004‘));

Insert into C##RD.RD_GOODS (GOOD_ID,DISCOUNT,GOODS,UNIT_PRICE,PURCHASE_DATE,GOOD_TYPE) values (‘0005‘,‘0.95‘,‘520钢铁胶水‘,165,‘2017-32-23‘, (select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0004‘));

Insert into C##RD.RD_GOODS (GOOD_ID,DISCOUNT,GOODS,UNIT_PRICE,PURCHASE_DATE,GOOD_TYPE) values (‘0006‘,‘0.45‘,‘华人牌手机‘,23450,‘2017-32-23‘, (select ref(a) from RD_COMMODITY_TYPES a where COMMODITY_TYPE_ID=‘0004‘));

select * from RD_GOODS;

 

select GOOD_ID "商品号", GOODS "商品名称",(select COMMODITY_TYPE from RD_COMMODITY_TYPES where RD_COMMODITY_TYPES.COMMODITY_TYPE=‘01‘) "商品类别", UNIT_PRICE "价格" from RD_GOODS where (select COMMODITY_TYPE from RD_COMMODITY_TYPES where RD_COMMODITY_TYPES.COMMODITY_TYPE=‘01‘)=‘01‘ and UNIT_PRICE > 2500;

⑥模糊查询。知道一个商品的商品名称中包含有“520”字样，要求查询该商品的商品号、商品名称、商品单价和商品折扣。

select * from RD_GOODS;

 

select GOOD_ID,GOODS,UNIT_PRICE,DISCOUNT from RD_GOODS where GOODS like‘%520%‘;