MySQL实训作业2（一）

#删除数据库
DROP DATABASE  IF EXISTS StoreDB;
#创建数据库StoreDB
CREATE DATABASE IF NOT EXISTS StoreDB;
#使用数据库
USE StoreDB;
#创建Products数据表
CREATE TABLE Products(
    ProductID  INT auto_increment PRIMARY KEY,
    ProductName VARCHAR(255),
    Price DECIMAL(10,2),
    Category VARCHAR(255)
)ENGINE = INNODB CHARSET=utf8;
#创建Orders数据表
CREATE TABLE Orders(
    OrdersID int auto_increment PRIMARY KEY,
    ProductID INT,
    Quantity  INT,
    OrderDate Date,
    FOREIGN KEY(ProductID) REFERENCES Products(roductID)
);
###插入数据
INSERT INTO Products(ProductName,Price,Category)VALUES('衬衫',10,'衣服');
INSERT INTO Products(ProductName,Price,Category)VALUES('面包',15,'食品');
INSERT INTO Products(ProductName,Price,Category)VALUES('洗衣机',50,'电器');
INSERT INTO Orders(ProductID,Quantity,OrderDate)VALUES('衬衫',5,'2022.01.13');
INSERT INTO Orders(ProductID,Quantity,OrderDate)VALUES('面包',2,'2022.04.15');
INSERT INTO Orders(ProductID,Quantity,OrderDate)VALUES('洗衣机',3,'2022.11.11');

select orders.orderid,orders.orderdate,products.productname,orders.quantity
from orders inner join products on orders.productid = products.productid;
###找出每个类别中价格最高的商品
select category,max(price) as highesprice 
from products group by category;
###找出过去一个月内所有订单
select * from orders where orderdate between date_sub(curdate(),interval 1 month) and curdate();
###找出购买数量最多的商品的名称
select category,max(price) as maxprice
from products group by category;