--实验八
--实验八,数据更新
--(1)在s表中插入元组“s6、华誉、40、广州,02085268888”
alter table S add
phone nchar(11)
insert into
S values('S6','华誉','40','广州','02085268888')
--(2)向表中插入元组'J8,传感器'
insert into J
(JNO,JNAME) values('J8','传感器')
--(3)对每一个供应商,求它为各种工程供应零件的总数量,并将此结果存入数据库
create table SJQ
(
sno char(10),
sumsjo int
)
insert
into SJQ
select sno,sum(qty)
from spj
group by sno
--(4)将p表中pno值为p6的元组的color属性值改为绿,weight属性值改为60
update P set
COLOR='绿',
WEIGHT='60'
where p.PNO='p6'
select * from p
--(5)将spj表中前4个元组的qty属性值统一改为300
update temp
set qty='300'
from (
select top 4*
from spj
}as temp
--(6)将s表中的CITY属性名含有'京'或'津'的相应status属性值增加100.insert into t
values('天使是')
insert into t
values('哈师大')
update t
set age='jiasdas'
where age like '%使%' or age like '%i%'
update s
set STATUS=STATUS+100
WHERE city LIKE '%京%' or city like '%津%'
--(7)将供应商s2为’一汽'工程项目所提供的零件数量修改为800
update spj
set qty='800'
where jno in(
select jno
from j
where j.jname='一汽'
)
and sno='s2'
--(8)将全部红色零件的颜色改为浅红色
update p
set color='浅红'
where color='红'
--(9)由s5供给j4的零件p6改为s3供应,请在数据库中作为必要的数据修改
update spj
set sno='s3'
where sno='s5' and jno='j4' and pno='p6'
--(10)在spj表中新增一列属性名为SDATE的属性列,对该表中的每一元组在SDATE属性列上填上实验当时的日期和时间
alter table spj
add SDATE datetime
update spj
set SDATE=GETDATE()
--(11)*删除所在城市为‘广州‘的供应商记录
delete spj
where spj.sno in
(
select sno
from s
where s.city='广州'
)
delete from s
where s.city='广州'
--(12)*删除所有零件名称中第一个字为’螺‘字的零件记录,并在供应情况表中删除相应的记录
delete from spj
where spj.pno in
(
select pno
from p
where p.pname like '螺%'
)
delete from p
where p.pname like '螺%'
--(13)*删除s3和s4两供应商为’三建‘工程供应商’螺母‘或’螺丝刀‘零件的相应供应情况数据信息