第5节表的管理.pptVIP

5.3.2 修改数据 修改数据 语法格式： UPDATE {table_name|view_name} SET {{column_name|@variable}=expression }[,…n] [FROM {table_source} [,…n]] [WHERE search_condition] 5.3.2 修改数据 例5-14 使用简单的UPDATE语句。 UPDATE Customer2 SET linkman_name=佚名, address=NULL, telephone= 例5-15 在UPDATE语句中使用WHERE子句。 UPDATE Customer2 SET telephone=0731-+telephone WHERE LEN(telephone)=7 5.3.2 修改数据 例5-16 在UPDATE语句中使用来自另一个表的信息 UPDATE Sell_Order SET cost= Sell_Order.Order_Num*Goods.Unit_Price*(1- Sell_Order.Discount) FROM Sell_Order,Goods WHERE Sell_Order.Goods_id=Goods.Goods_id 5.3.2 修改数据 例5-16 在UPDATE语句中使用来自另一个表的信息 UPDATE Sell_Order SET cost=SO.Order_Num*G.Unit_Price*(1-SO。Discount) FROM Sell_Order SO,Goods G WHERE SO.Goods_id=G.Goods_id 5.3.2 修改数据 例5-17 在UPDATE 语句中使用SELECT…TOP语句。 UPDATE Goods SET Unit_Price=Unit_Price*0.9 FROM Goods, (SELECT TOP 10 Goods_ID, SUM(Order_Num) AS Total_Num FROM Sell_Order GROUP BY Goods_ID ORDER BY Total_Num DESC ) AS Total_Sum WHERE Goods.Goods_id=Total_Sum.Goods_id 课堂练习 (1)将学生200215121的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； (2)将所有学生的年龄增加1岁 UPDATE Student SET Sage= Sage+1； 5.3.3 删除数据 删除数据 1、DELETE 语句 DELETE 语句可删除表或视图中的一行或多行,每一行的删除都将被记入日志。DELETE语句的语法格式如下： DELETE [FROM] { table_name|view_name} [FROM {table_source} [,…n]] [WHERE {search_condition}] 5.3.3 删除数据 例5-18 不带参数使用DELETE命令删除所有行。 USE Sales GO DELETE Customer2 本例从Customer2表中删除所有行。 例5-19 带WHERE子句的DELETE语句,有条件地删除行。 DELETE FROM Sell_Order WHERE Custom_ID=C0003 5.3.3 删除数据 例5-20 在DELETE中使用联接或子查询。 DELETE Sell_Order FROM Sell_Order SO INNER JOIN customer2 C ON SO.customer_id=C.customer_id WHERE C.address LIKE 东方市% -- 等同于下列命令 DELETE Sell_Order FROM Sell_Order SO, customer2 C WHERE SO.customer_id=C.customer_id AND C.address LIKE 东方市% 课堂练习 (1)删除学号为200215128的学生记录 (2)删除所有的学生选课记录 DELETE FROM Student WHERE Sno= 2