数据库-数据操作-增删查改

任务描述

本关任务：修改数据

编程要求

我们已经为你建好了数据库与数据表，并添加了相应的数据内容。 你只需根据右侧提示，完成以下任务：
修改数据
① 更新所有代理商的提成比例为20%。（即更新提成比例字段值为20）
② 为产品表格增加一个新字段—-缺货量，数据类型smallint。
③ 将编号为0011的产品单价更新为1000。

相关知识

update语句使用

update语句一次只能操作一个表。
格式1： update 表名 [别名] set 列名 = 表达式, … [where 条件];

格式2： update 表名 [别名] set (列名, …) = (子查询) [where 条件]; 例：工种为SALESMEN的职工的工资改为工种平均工资的110%

update EMPLOYEEset Salary = (select 1.1 * avg(Salary) from EMPLOYEE where JOB="SALESMEN")where JOB="SALESMEN";

例：将所有学生的年龄增加1岁

update student set Sage=Sage+1;

ALTER TABLE 语句使用

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。 SQL ALTER TABLE 语法 如需在表中添加列，请使用下列语法: ALTER TABLE table_name ADD column_name datatype 要删除表中的列，请使用下列语法： ALTER TABLE table_name DROP COLUMN column_name 要改变表中列的数据类型，请使用下列语法： ALTER TABLE table_name ALTER COLUMN column_name datatype 例：表 “Persons” 中添加一个名为 “Birthday” 的新列，数据类型为date。

ALTER TABLE PersonsADD Birthday date

例：从表stuinfo中删除名为name的列

alter table stuinfodrop column name

订单数据库

订单数据库中的关系模型

订单数据库，里面有5张数据表，关系模型如下：
1、代理商（代理商编号、姓名、地址、邮政编码、提成金额、提成比例） 其中代理商编号为primary key（主码）

字段名	数据类型	备注
代理商编号	char(4)	primary key
姓名	nvarchar(10)
地址	nvarchar(20)
邮政编码	char(6)
提成金额	smallmoney
提成比例	tinyint

2、客户（客户编号、姓名、地址、邮政编码、收支差额、贷款限额、代理商编号） 其中客户编号为primary key（主码）

字段名	数据类型	备注
客户编号	char(4)	primary key
姓名	nvarchar(10)
地址	nvarchar(20)
邮政编码	char(6)
收支差额	smallmoney
贷款限额	smallmoney
代理商编号	char(4)	foreign key

3．产品（产品编号、描述信息、库存量、类别、仓库的编号、价格） 其中产品的编号为primary key（主码）

字段名	数据类型	备注
产品编号	char(4)	primary key
描述信息	nvarchar(20)
库存量	int
类别	tinyint
仓库编号	char(4)
价格	smallmoney

4．订单（订单编号、订货日期、客户编号）

字段名	数据类型	备注
订单编号	char(4)	primary key
订货日期	Smalldatetime
客户编号	char(4)	foreign key

5．订货项目（订单编号、产品编号、订购数量、订购单价）

字段名	数据类型	备注
订单编号	char(4)	primary key ， foreign key
产品编号	char(4)	primary key， foreign key
订购数量	smallint
订购单价	smallmoney

注意：这里不是有两个主键，没有数据表会有两个主键。
这里是联合主键，也就是说通过字段订单编号和产品编号来确认一条记录的唯一性。
primary key(订单编号,产品编号)

订单数据库中原始数据

1．对于代理商（代理商编号、姓名、地址、邮政编码、提成金额、提成比例）

01、联邦、东环路1号、541001、30000.00、40

02、惠普、东环路2号、541002、4000.00、10

03、三洋、东环路3号、541003、10000.00、30

04、联想、东环路4号、541004、100000.00、60

2．客户（客户编号、姓名、地址、邮政编码、收支差额、贷款限额、代理商编号）

100、张三、西环路1号、100001、10.00、100.00、02

200、李四、西环路2号、100001、-10.00、10.00、04

300、王五、西环路3号、100001、100.00、1000.00、02

400、赵六、西环路4号、100001、600.00、2000.00、01

600、李明、西环路6号、100001、20.00、300.00、03

700、张进、西环路7号、100001、400.00、1000.00、03

3．产品（产品的编号、描述信息、库存量、类别、仓库的编号、价格）

0011、药物,单位(瓶)、1000、12、1001、40.00

0022、机器,单位(件)、300、3、1002、50000.00

0033、中药,单位(包)、800、12、1001、300.00

0044、软件,单位(套)、1500、10、1003、2000.00

0055、家具,单位(件)、6000、3、1002、1000.00

0066、小型机,单位(台)、10000、3、1002、200000.00

4．订单（订单编号、订货日期、客户编号）

111、2000-10-01、200

222、2000-09-01、200

444、2002-02-02、300

555、2003-03-03、100

5．订货项目（订单编号、产品编号、订购数量、订购单价）

111、0033、200、280.00

222、0066、6000、150000.00

444、0011、300、39.00

555、0055、5500、950.00

444、0044、1000、1900.00

sql语句

数据修改

USE 订单数据库
GO
SET NOCOUNT ON

/*
① 更新所有代理商的提成比例为20%。（即更新提成比例字段值为20）
*/
--   第一题SQL语句开始   --

UPDATE 代理商 SET 提成比例=20;


--   第一题结束  --
GO


/*
② 为产品表格增加一个新字段----缺货量，数据类型smallint。
*/
--  第二题SQL语句开始  --

ALTER TABLE 产品 ADD 缺货量 SMALLINT;

--  第二题结束  --
GO


/*
③ 将编号为0011的产品单价更新为1000。
 */
--  第三题SQL语句开始 --
 
 UPDATE 产品 SET 价格=1000 WHERE 产品编号='0011';
 
--  第三题结束   --
GO

数据插入

USE 订单数据库
GO
SET NOCOUNT ON

--使用insert语句插入数据--
/*
  ①在产品表格中插入一种新产品。
  需要插入的数据为：0077、瓷器，单位（件）、8000、3、1002、10000.00
*/
--   第一题SQL语句开始   --
INSERT INTO 产品 
VALUES
('0077', '瓷器、单位（件', 8000, 3, '1002', 10000.00);



--   第一题结束  --
GO

/*
②在订单表中插入新订单。
  需要插入的数据为： 666、2003-06-06、300
*/
--  第二题SQL语句开始  --

INSERT INTO 订单 
VALUES
('666', '2003-06-06', '300');

--  第二题结束  --
GO


/*
③在代理商表中插入新代理商。
 需要插入的数据为：05、海信、中山路9号、541000、200000.00、60
 */
--  第三题SQL语句开始 --
 
 INSERT INTO 代理商
 VALUES
 ('05', '海信', '中山路9号', '541000', 200000.00,60);
 
--  第三题结束   --
GO

数据删除

USE 订单数据库
GO
SET NOCOUNT ON

/*
① 从客户表中删除客户编号为700的客户。
*/
--   第一题SQL语句开始   --

DELETE FROM 客户 WHERE 客户编号='700';


--   第一题结束  --
GO


/*
② 从代理商表中删除代理商编号为05的代理商。
*/
--  第二题SQL语句开始  --

DELETE FROM 代理商 WHERE 代理商编号='05';

--  第二题结束  --
GO


/*
③ 从订货项目表中删除订单编号为444的所有订货项目。
 */
--  第三题SQL语句开始 --
 
 DELETE FROM 订货项目 WHERE 订单编号='444';
 
--  第三题结束   --
GO

建立与删除索引

USE 订单数据库
GO
SET NOCOUNT ON

/*
（1）建立索引
在各表中，分别按代理商编号、客户编号、产品编号及订单编号建立唯一索引。
*/
/*
1. 在代理商表中，按代理商编号升序建立名为***代理商编号索引***的唯一索引。
*/
--   第一题SQL语句开始   --
CREATE UNIQUE INDEX 代理商编号索引 ON 代理商(代理商编号);

--   第一题结束  --
GO


/*
2. 在客户表中，按客户编号升序建立名为***客户编号索引***的唯一索引。
*/
--  第二题SQL语句开始  --

CREATE UNIQUE INDEX 客户编号索引 ON 客户(客户编号);

--  第二题结束  --
GO


/*
3. 在产品表中，按照产品编号升序建立名为***产品编号索引***的唯一索引。
 */
--  第三题SQL语句开始 --
 
CREATE UNIQUE INDEX 产品编号索引 ON 产品(产品编号);
 
--  第三题结束   --
GO

/*
4. 在订单表中，按照订单编号升序建立名为***订单编号索引***的唯一索引。
 */
--  第四题SQL语句开始 --
CREATE UNIQUE INDEX 订单编号索引 ON 订单(订单编号);
--  第四题结束   --
GO