奶茶管理系统数据库sql server
时间: 2023-06-29 21:08:26 浏览: 113
以下是一个简单的奶茶管理系统数据库 SQL Server 的示例:
创建表格:
```sql
CREATE TABLE [dbo].[Order] (
[Order_ID] INT IDENTITY(1,1) NOT NULL,
[Customer_Name] VARCHAR(50) NOT NULL,
[Order_Date] DATE NOT NULL,
[Total_Price] DECIMAL(10,2) NOT NULL,
PRIMARY KEY CLUSTERED ([Order_ID] ASC)
);
CREATE TABLE [dbo].[Order_Item] (
[Item_ID] INT IDENTITY(1,1) NOT NULL,
[Order_ID] INT NOT NULL,
[Item_Name] VARCHAR(50) NOT NULL,
[Item_Price] DECIMAL(10,2) NOT NULL,
[Quantity] INT NOT NULL,
[Subtotal] DECIMAL(10,2) NOT NULL,
PRIMARY KEY CLUSTERED ([Item_ID] ASC),
CONSTRAINT [FK_Order_Item_Order] FOREIGN KEY ([Order_ID]) REFERENCES [dbo].[Order]([Order_ID])
);
```
插入数据:
```sql
INSERT INTO [dbo].[Order] ([Customer_Name], [Order_Date], [Total_Price])
VALUES ('John Smith', '2021-08-01', 25.00);
INSERT INTO [dbo].[Order_Item] ([Order_ID], [Item_Name], [Item_Price], [Quantity], [Subtotal])
VALUES (1, 'Mango Bubble Tea', 5.50, 2, 11.00),
(1, 'Taro Milk Tea', 6.50, 1, 6.50);
```
查询数据:
```sql
SELECT [Order_ID], [Customer_Name], [Order_Date], [Total_Price]
FROM [dbo].[Order];
SELECT [Item_ID], [Order_ID], [Item_Name], [Item_Price], [Quantity], [Subtotal]
FROM [dbo].[Order_Item];
```
更新数据:
```sql
UPDATE [dbo].[Order]
SET [Total_Price] = 30.00
WHERE [Order_ID] = 1;
UPDATE [dbo].[Order_Item]
SET [Quantity] = 3, [Subtotal] = 16.50
WHERE [Item_ID] = 1;
```
删除数据:
```sql
DELETE FROM [dbo].[Order_Item]
WHERE [Item_ID] = 2;
DELETE FROM [dbo].[Order]
WHERE [Order_ID] = 1;
```
相关推荐
![sql](https://img-home.csdnimg.cn/images/20210720083646.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)