setup.sql

-- make sure the websiteuser account is set up and has the correct privileges
CREATE USER IF NOT EXISTS websiteuser IDENTIFIED BY 'websitepassword';
GRANT INSERT, SELECT, UPDATE, DELETE ON website.* TO websiteuser;

DROP TABLE IF EXISTS accounts;

CREATE TABLE IF NOT EXISTS accounts (
  id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user VARCHAR(25) NOT NULL,
  pass VARCHAR(70) NOT NULL,
  phone VARCHAR(25) NOT NULL,
  email VARCHAR(50) NOT NULL
);

INSERT INTO accounts(user, pass)
	VALUES("doej", "$2b$10$gL33obKAFUT5DK3pEbh72OIHztsWBniBBh.PdeKOrF1yr5KFAsdZO");


-- Commodities应该跟Accounts外键关联
CREATE TABLE IF NOT EXISTS commodities (
  id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(25) NOT NULL,
  picture VARCHAR(50) NOT NULL,
  price FLOAT NOT NULL,
  `describe` VARCHAR(70) NOT NULL,
  onSaleTime datetime NOT NULL,
  status VARCHAR(10) NOT NULL,
  userId MEDIUMINT UNSIGNED NOT NULL,
  foreign key (userId) references accounts (id)
);


CREATE TABLE IF NOT EXISTS offers (
  id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bid FLOAT NOT NULL,
  commodityId MEDIUMINT UNSIGNED NOT NULL,
  foreign key (commodityId) references commodities (id),
  buyerId MEDIUMINT UNSIGNED NOT NULL,
  foreign key (buyerId) references accounts (id)
);

常用sql
select * from commodities;

注意事项：
describe是mysql关键字，作为表字段一定要用``引起来，不能是''

INSERT INTO commodities (name, picture, price, `describe`, onSaleTime, status, userId) VALUES ('dd', '/uploads/2-1645623389571.webp', 123, 'jaflkj', '2022-2-23', 'on sale', 2);
INSERT INTO commodities (name, picture, price, `describe`, onSaleTime, status, userId) VALUES ('dd', '/uploads/2-1645623389571.webp', 123, 'jaflkj', '2022/2/23', 'on sale', 2);