题-MySQL数据库
题-MySQL数据库
橙子🍊以下是关于数据库中表格的一份简要笔记:
1. account 表格
字段:
userid
:用户IDfullname
:全名password
:密码sex
:性别address
:地址email
:电子邮件phone
:电话号码
约束:
- 主键:
userid
- 性别检查约束:只能是’男’或’女’
- 主键:
记录:
- 包含了一些用户的信息,如刘晓和、张嘉庆等。
2. category 表格
字段:
catid
:分类IDcatname
:分类名称
约束:
- 主键:
catid
- 主键:
记录:
- 包含了一些宠物分类,如鸟类、猫、狗等。
3. lineitem 表格
字段:
orderid
:订单IDitemid
:商品IDquantity
:数量unitprice
:单价
约束:
- 主键:(
orderid
,itemid
) - 外键:
itemid
关联到product
表的productid
- 外键:
orderid
关联到orders
表的orderid
- 主键:(
记录:
- 包含了一些订单明细,如商品ID、数量、单价等。
4. orders 表格
字段:
orderid
:订单IDuserid
:用户IDorderdate
:订单日期totalprice
:总价status
:状态
约束:
- 主键:
orderid
- 外键:
userid
关联到account
表的userid
- 主键:
记录:
- 包含了一些订单信息,如用户ID、订单日期、总价等。
5. product 表格
字段:
productid
:商品IDcatid
:分类IDname
:商品名称descn
:商品描述listprice
:标价unitcost
:成本qty
:库存数量
约束:
- 主键:
productid
- 外键:
catid
关联到category
表的catid
- 主键:
记录:
- 包含了一些商品信息,如商品名称、描述、价格等。
这是对数据库中主要表格结构和内容的简要概述。
当创建数据库时,通常按照以下步骤执行SQL指令。下面是每个指令的详细解析:
1. SET NAMES utf8mb4;
和 SET FOREIGN_KEY_CHECKS = 0;
这两个指令用于设置字符集和禁用外键检查。utf8mb4
是一种Unicode字符集,通常用于支持各种语言的字符。禁用外键检查可以使表的创建和插入数据的过程更顺利。
2. 创建 account
表格
1 | -- 创建 account 表格 |
- 解析:
- 创建了一个名为
account
的表格,包含了用户信息的字段。 - 使用了
char
、varchar
等字段类型,设置了字符集为utf8mb3
。 - 定义了主键 (
userid
) 和性别检查约束 (sex
只能为’男’或’女’)。
- 创建了一个名为
3. 插入 account
表格数据
1 | -- 插入 account 表格数据 |
- 解析:
- 向
account
表格插入了一些示例数据,包括用户ID、全名、密码等。
- 向
4. 创建 category
表格
1 | -- 创建 category 表格 |
- 解析:
- 创建了一个名为
category
的表格,用于存储宠物分类信息。 - 设置了
char
和varchar
字段,并定义了主键 (catid
)。
- 创建了一个名为
5. 插入 category
表格数据
1 | -- 插入 category 表格数据 |
- 解析:
- 向
category
表格插入了一些示例数据,包括分类ID和分类名称。
- 向
6. 创建 lineitem
表格
1 | -- 创建 lineitem 表格 |
- 解析:
- 创建了一个名为
lineitem
的表格,用于存储订单明细信息。 - 包含了外键,将
itemid
关联到product
表的productid
,将orderid
关联到orders
表的orderid
。
- 创建了一个名为
7. 插入 lineitem
表格数据
1 | -- 插入 lineitem 表格数据 |
- 解析:
- 向
lineitem
表格插入了一些示例数据,包括订单ID、商品ID、数量、单价等。
- 向
8. 创建 orders
表格
1 | -- 创建 orders 表格 |
- 解析:
- 创建了一个名为
orders
的表格,用于存储订单信息。 - 包含了外键,将
userid
关联到account
表的userid
。
- 创建了一个名为
9. 插入 orders
表格数据
1 | -- 插入 orders 表格数据 |
- 解析:
- 向
orders
表格插入了一些示例数据,包括订单ID、用户ID、订单日期、总价等。
- 向
10. 创建 product
表格
1 | -- 创建 product 表格 |
- 解析:
- 创建了一个名为
product
的表格,用于存储商品信息。 - 包含了外键,将
catid
关联到category
表的catid
。
- 创建了一个名为
11. 插入 product
表格数据
1 | -- 插入 product 表格数据 |
- 解析:
- 向
product
表格插入了一些示例数据,包括商品ID、分类ID、商品名称、描述、价格等。
- 向
12. SET FOREIGN_KEY_CHECKS = 1;
用于启用外键检查,保证数据的完整性。
这样,整个数据库的创建、表格定义和数据插入过程就完成了。这份报告详细解析了每个指令的作用以及表格的结构和数据。如果你有其他问题或需要进一步的解释,请告诉我。
下面是数据库文件中的全部指令:
按照指令的执行顺序和优先级,下面是按照优先级排序的表格:
优先级 | 指令 | 说明 |
---|---|---|
1 | SET NAMES utf8mb4; | 设置字符集为 utf8mb4,通常用于支持各种语言的字符。 |
2 | SET FOREIGN_KEY_CHECKS = 0; | 禁用外键检查,允许在导入数据时不考虑外键关联的完整性。 |
3 | DROP TABLE IF EXISTS account; | 如果存在表格 account ,则删除它。 |
4 | CREATE TABLE account ... | 创建名为 account 的表格,用于存储用户账户信息。包括字段 userid , fullname , password , sex , address , email , phone 等。 |
5 | INSERT INTO account VALUES ... | 向 account 表格插入一些示例数据,包括用户ID、全名、密码、性别等。 |
6 | DROP TABLE IF EXISTS category; | 如果存在表格 category ,则删除它。 |
7 | CREATE TABLE category ... | 创建名为 category 的表格,用于存储宠物分类信息。包括字段 catid , catname 。 |
8 | INSERT INTO category VALUES ... | 向 category 表格插入一些示例数据,包括分类ID和分类名称。 |
9 | DROP TABLE IF EXISTS lineitem; | 如果存在表格 lineitem ,则删除它。 |
10 | CREATE TABLE lineitem ... | 创建名为 lineitem 的表格,用于存储订单明细信息。包括字段 orderid , itemid , quantity , unitprice 等。同时,定义了两个外键关联到 product 表的 productid 和 orders 表的 orderid 。 |
11 | INSERT INTO lineitem VALUES ... | 向 lineitem 表格插入一些示例数据,包括订单ID、商品ID、数量、单价等。 |
12 | DROP TABLE IF EXISTS orders; | 如果存在表格 orders ,则删除它。 |
13 | CREATE TABLE orders ... | 创建名为 orders 的表格,用于存储订单信息。包括字段 orderid , userid , orderdate , totalprice , status 等。同时,定义了一个外键关联到 account 表的 userid 。 |
14 | INSERT INTO orders VALUES ... | 向 orders 表格插入一些示例数据,包括订单ID、用户ID、订单日期、总价等。 |
15 | DROP TABLE IF EXISTS product; | 如果存在表格 product ,则删除它。 |
16 | CREATE TABLE product ... | 创建名为 product 的表格,用于存储商品信息。包括字段 productid , catid , name , descn , listprice , unitcost , qty 等。同时,定义了一个外键关联到 category 表的 catid 。 |
17 | INSERT INTO product VALUES ... | 向 product 表格插入一些示例数据,包括商品ID、分类ID、商品名称、描述、价格等。 |
18 | SET FOREIGN_KEY_CHECKS = 1; | 启用外键检查,确保数据的完整性。 |
这份表格提供了对每个指令的简要说明
4.4.3 Petstore数据修改与删除操作
1.修改数据
(1).设新从澳大利亚购进一批天使鱼,数量为50尾,进价为15元/尾,请按库存与新进商品的平均值调整商品的成本价格。该商品将以高出成本价格20%的市场价格卖出,请调整商品的市场价格和数量
调整商品的成本价格:成本价格=(库存数量*成本价格+50*15)/(库存量+50)
1 | update product |
调整商品的市场价格和数量
1 | update product |
或一次性调整所有数据
1 | update product |
(2)订单号为“20130411”的订单已经发货,请将该订单的状态修改为“1”,同时根据该订单的订单明细表修改商品表的库存。
修改订单状态
1 | update orders |
修改商品表的库存
1 | update lineitem,product |
如果想要一次修改所有数据,则涉及三表操作,可将上面两条update语句合并为一条update语句。
1 | update orders,lineitem,product |
2.删除数据
(1).请将用户号为“u0004”的所有订购信息删除,并删除其用户记录。
(2).删除其所有订购信息,包括订单表和订单明细表的信息。
1 | delete orders,lineitem |
(3).删除其所有订购信息,包括用户表、订单表和订单明细表的信息。
1 | delete account,orders,lineitem |
3.5.3 建立数据完整性约束
product表中的“catid”列引用了category表中的“catid”。请为product表中的“catid”列创建外键,以保证当要删除category表中的“catid”列的值时,如果product表中的“catid”列还有该值的记录,则拒绝对category表的删除操作。(提示:使用了外键约束restrict)
为
product
表中的catid
列创建外键,并使用RESTRICT
约束,可以通过以下 SQL 语句实现:1
2
3
4
5
6-- 在 product 表中添加外键约束
ALTER TABLE product
ADD CONSTRAINT fk_catid -- 外键约束的名称,这里为 fk_catid
FOREIGN KEY (catid) -- 外键列,即 product 表中的 catid 列
REFERENCES category(catid) -- 关联表和关联列,这里为 category 表的 catid 列
ON DELETE RESTRICT; -- 定义删除操作时的行为,即拒绝删除 category 表中仍有关联值的记录解析细节:
ALTER TABLE product
:指定要修改的表是product
表。ADD CONSTRAINT fk_catid
:给外键约束起一个名称,这里取名为fk_catid
。FOREIGN KEY (catid)
:指定product
表中的列catid
作为外键列。REFERENCES category(catid)
:指定关联的表是category
表,关联的列是catid
列。ON DELETE RESTRICT
:定义删除操作时的行为。这里是RESTRICT
,表示如果在category
表中删除某个catid
,但在product
表中还有引用了这个catid
值的记录,将会拒绝删除。
这样的外键约束将确保
product
表中的catid
列的值必须是category
表中的catid
列的存在值,并且在删除category
表中的匹配行时,如果在product
表中仍有引用这个catid
的记录,将会拒绝删除。orders表中的“userid”列引用了account表中的“userid”。请为orders表中的“userid”列创建外键,以保证当要删除和更新account表中的数据时,只要orders表中还有该客户的订单,就拒绝对account表进行删除和更新操作。(提示:使用了外键约束restrict)
1
2
3
4
5alter table orders
add foreign key (userid)
references account(userid)
on delete restrict
on update restrict;这条 SQL 指令是用于在
orders
表中添加一个外键约束,将userid
列与account
表中的userid
列进行关联。下面是详细的解析:1
2
3
4
5
6
7-- 在 orders 表中添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_userid -- 指定外键约束的名称,这里为 fk_userid
FOREIGN KEY (userid) -- 指定 orders 表中的列,即外键列,这里为 userid
REFERENCES account(userid) -- 指定关联表和关联列,这里为 account 表的 userid 列
ON DELETE RESTRICT -- 定义删除操作时的行为,这里是 RESTRICT,表示拒绝删除关联表中的 userid 值
ON UPDATE RESTRICT; -- 定义更新操作时的行为,这里是 RESTRICT,表示拒绝更新关联表中的 userid 值解析细节:
ALTER TABLE orders
:指定要修改的表是orders
表。ADD CONSTRAINT fk_userid
:给外键约束起一个名称,这里取名为fk_userid
,方便后续管理和识别。FOREIGN KEY (userid)
:指定orders
表中的列userid
作为外键列。REFERENCES account(userid)
:指定关联的表是account
表,关联的列是userid
列。ON DELETE RESTRICT
:定义在删除操作时的行为。这里是 RESTRICT,表示如果在account
表中要删除某个userid
,但在orders
表中有关联的记录存在,将会拒绝删除。即,要删除account
表中的userid
值,必须先删除在orders
表中引用了这个userid
值的记录。ON UPDATE RESTRICT
:定义在更新操作时的行为。这里是 RESTRICT,表示如果在account
表中更新某个userid
,但在orders
表中有关联的记录存在,将会拒绝更新。即,要更新account
表中的userid
值,必须先更新在orders
表中引用了这个userid
值的记录。
总的来说,这个外键约束确保了在
orders
表中的userid
列的值必须是account
表中的userid
列的存在值,同时定义了在删除或更新关联表中的userid
值时的行为。lineitem表中的“itemid”列引用了product表中的“productid”。请为lineitem表中的“itemid”列创建外键,以保证当要删除和更新product表中的商品号时,自动删除或更新lineitem表中匹配的行。(提示:使用了级联约束cascade)
为
lineitem
表中的itemid
列创建外键,并使用级联约束CASCADE
,可以通过以下 SQL 语句实现:1
2
3
4
5
6
7-- 在 lineitem 表中添加外键约束
ALTER TABLE lineitem
ADD CONSTRAINT fk_itemid -- 外键约束的名称,这里为 fk_itemid
FOREIGN KEY (itemid) -- 外键列,即 lineitem 表中的 itemid 列
REFERENCES product(productid) -- 关联表和关联列,这里为 product 表的 productid 列
ON DELETE CASCADE -- 定义删除操作时的级联行为,即删除关联表中的记录时,同时删除 lineitem 表中的匹配行
ON UPDATE CASCADE; -- 定义更新操作时的级联行为,即更新关联表中的记录时,同时更新 lineitem 表中的匹配行解析细节:
ALTER TABLE lineitem
:指定要修改的表是lineitem
表。ADD CONSTRAINT fk_itemid
:给外键约束起一个名称,这里取名为fk_itemid
。FOREIGN KEY (itemid)
:指定lineitem
表中的列itemid
作为外键列。REFERENCES product(productid)
:指定关联的表是product
表,关联的列是productid
列。ON DELETE CASCADE
:定义在删除操作时的行为。这里是CASCADE
,表示如果在product
表中删除某个productid
,将会自动删除在lineitem
表中引用了这个productid
值的记录。ON UPDATE CASCADE
:定义在更新操作时的行为。这里是CASCADE
,表示如果在product
表中更新某个productid
,将会自动更新在lineitem
表中引用了这个productid
值的记录。
这样,创建的外键约束将确保
lineitem
表中的itemid
列的值必须是product
表中的productid
列的存在值,并且在删除或更新product
表中的匹配行时,会自动进行相应的级联操作。lineitem表中的“orderid”列引用了orders表中的“orderid”。请为lineitem表中的“orderid”列创建外键,以保证当要删除orders表中的订单号时,自动删除lineitem表中匹配的行。(提示:使用了级联约束cascade)
为
lineitem
表中的orderid
列创建外键,并使用级联约束CASCADE
,可以通过以下 SQL 语句实现:1
2
3
4
5
6-- 在 lineitem 表中添加外键约束
ALTER TABLE lineitem
ADD CONSTRAINT fk_orderid -- 外键约束的名称,这里为 fk_orderid
FOREIGN KEY (orderid) -- 外键列,即 lineitem 表中的 orderid 列
REFERENCES orders(orderid) -- 关联表和关联列,这里为 orders 表的 orderid 列
ON DELETE CASCADE; -- 定义删除操作时的行为,即删除 orders 表中的订单时,自动删除 lineitem 表中匹配的行解析细节:
ALTER TABLE lineitem
:指定要修改的表是lineitem
表。ADD CONSTRAINT fk_orderid
:给外键约束起一个名称,这里取名为fk_orderid
。FOREIGN KEY (orderid)
:指定lineitem
表中的列orderid
作为外键列。REFERENCES orders(orderid)
:指定关联的表是orders
表,关联的列是orderid
列。ON DELETE CASCADE
:定义删除操作时的行为。这里是CASCADE
,表示如果在orders
表中删除某个订单号,将会自动删除在lineitem
表中引用了这个订单号的记录。
这样的外键约束将确保
lineitem
表中的orderid
列的值必须是orders
表中的orderid
列的存在值,并且在删除orders
表中的匹配行时,会自动进行相应的级联操作。为account表中的“sex”列添加check完整性约束,以保证性别只能包含“男”或“女”。
要为
account
表中的sex
列添加 CHECK 完整性约束,以保证性别只能包含“男”或“女”,可以使用以下 SQL 语句:1
2
3
4-- 在 account 表中添加 CHECK 约束
ALTER TABLE account
ADD CONSTRAINT chk_sex -- 约束的名称,这里为 chk_sex
CHECK (sex IN ('男', '女')); -- CHECK 约束,确保 sex 列的值只能是 '男' 或 '女'解析细节:
ALTER TABLE account
:指定要修改的表是account
表。ADD CONSTRAINT chk_sex
:给 CHECK 约束起一个名称,这里取名为chk_sex
。CHECK (sex IN ('男', '女'))
:定义 CHECK 约束的条件,确保sex
列的值只能是 ‘男’ 或 ‘女’。
这样的 CHECK 约束将阻止在
account
表中插入或更新sex
列的值,如果不是 ‘男’ 或 ‘女’ 将会触发约束错误。这有助于维护数据的一致性和完整性。