数据库 - 第二次实验

哔哩哔哩

CREATE TABLE user (
	uid INT PRIMARY KEY NOT NULL,
	nickname VARCHAR(255) NOT NULL,
	gender VARCHAR(255) NOT NULL,
	dateofbirth DATE NOT NULL,
	idnumber VARCHAR(255) NOT NULL,
	profile VARCHAR(255) NOT NULL,
	userlevel INT NOT NULL
);

CREATE TABLE connect (
	connect_time TIMESTAMP PRIMARY KEY NOT NULL,
	uid INT NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid)
);

CREATE TABLE video (
	vid INT PRIMARY KEY NOT NULL,
	name VARCHAR(255) NOT NULL,
	description VARCHAR(255),
	channel VARCHAR(255),
	content TEXT
);

CREATE TABLE post (
	uid INT NOT NULL,
	vid INT NOT NULL,
	post_time TIMESTAMP NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid),
	FOREIGN KEY (vid) REFERENCES video(vid)
);

CREATE TABLE watch (
	uid INT NOT NULL,
	vid INT NOT NULL,
	watch_time TIMESTAMP NOT NULL,
	duration INT NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid),
	FOREIGN KEY (vid) REFERENCES video(vid)
);

CREATE TABLE like (
	uid INT NOT NULL,
	vid INT NOT NULL,
	like_time TIMESTAMP NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid),
	FOREIGN KEY (vid) REFERENCES video(vid)
);

CREATE TABLE coin (
	uid INT NOT NULL,
	vid INT NOT NULL,
	coin_time TIMESTAMP NOT NULL,
	amount INT NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid),
	FOREIGN KEY (vid) REFERENCES video(vid)
);

CREATE TABLE collect (
	uid INT NOT NULL,
	vid INT NOT NULL,
	collect_time TIMESTAMP NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid),
	FOREIGN KEY (vid) REFERENCES video(vid)
);

CREATE TABLE comment (
	uid INT NOT NULL,
	vid INT NOT NULL,
	post_time TIMESTAMP NOT NULL,
	video_time TIMESTAMP NOT NULL,
	content TEXT NOT NULL,
	color VARCHAR(255) NOT NULL,
	FOREIGN KEY (uid) REFERENCES user(uid),
	FOREIGN KEY (vid) REFERENCES video(vid)
);

淘宝

CREATE TABLE commodity (  
  cid INT PRIMARY KEY NOT NULL,  
  name VARCHAR(255) NOT NULL,  
  type VARCHAR(255) NOT NULL,  
  brand VARCHAR(255),  
  guarantee_period VARCHAR(255) NOT NULL,
  bid INT NOT NULL,
  FOREIGN KEY (bid) REFERENCES brand (bid)
);

CREATE TABLE batch (  
  batchid INT PRIMARY KEY NOT NULL,  
  cid INT NOT NULL,  
  man_date DATE NOT NULL,  
  ref_price DECIMAL(10,2),  
  FOREIGN KEY (cid) REFERENCES commodity(cid) 
);

CREATE TABLE brand (  
  bid INT PRIMARY KEY NOT NULL,  
  name VARCHAR(255) NOT NULL,  
  address VARCHAR(255) NOT NULL,  
  telephone VARCHAR(20) NOT NULL  
);

CREATE TABLE stores (  
  sid INT PRIMARY KEY NOT NULL, 
  name VARCHAR(255) NOT NULL,  
  level VARCHAR(255)  
);

CREATE TABLE user (  
  uid INT PRIMARY KEY NOT NULL,  
  name VARCHAR(255) NOT NULL,  
  address VARCHAR(255),  
  telephone VARCHAR(20),  
  gender VARCHAR(10)  
);

CREATE TABLE onsell (  
  cid INT NOT NULL,  
  batchid INT NOT NULL,  
  sid INT NOT NULL,  
  price DECIMAL(10,2) NOT NULL,  
  onsell_date DATE NOT NULL,  
  status VARCHAR(20) NOT NULL,  
  rem_amount INT NOT NULL,  
  FOREIGN KEY (cid) REFERENCES commodity(cid),  
  FOREIGN KEY (batchid) REFERENCES batch(batchid),  
  FOREIGN KEY (sid) REFERENCES stores(sid)  
);

CREATE TABLE orders (  
  oid INT AUTO_INCREMENT,  
  uid INT NOT NULL,  
  sid INT NOT NULL,  
  batchid INT NOT NULL,  
  cid INT NOT NULL,  
  sold_time TIMESTAMP NOT NULL,  
  status VARCHAR(20) NOT NULL,  
  buyer_rate INT CHECK (buyer_rate IN (1,2,3,4,5)),  
  PRIMARY KEY (oid),
  FOREIGN KEY (uid) REFERENCES user(uid),  
  FOREIGN KEY (sid) REFERENCES stores(sid),  
  FOREIGN KEY (batchid) REFERENCES batch(batchid),  
  FOREIGN KEY (cid) REFERENCES commodity(cid)  
);