Home About Me

MySQL Schema for a PHP Product Management Website

2026-10-10

A small PHP-based product management website can be supported by a straightforward relational database with separate tables for products and users. The shops table stores product information, including names, prices, inventory, descriptions, and creation timestamps. The users table contains login and account details for system users.

The schema uses an auto-incrementing numeric ID as the primary key for each table. Product names and usernames are also unique, preventing duplicate records in either area. The sample data includes beverages, confectionery, personal-care products, consumer electronics, and flowers, while the user table contains an administrator account.

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- 主机： localhost
-- 生成日期： 2023-12-04 01:05:11
-- 服务器版本： 8.0.12
-- PHP 版本： 7.3.4

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 数据库： `www_ymx_com`
--

CREATE DATABASE IF NOT EXISTS `www_ymx_com` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `www_ymx_com`;

-- --------------------------------------------------------

--
-- 表的结构 `shops`
--

CREATE TABLE `shops` (
  `id` int(11) NOT NULL,
  `shopname` varchar(100) DEFAULT NULL,
  `shopprice` int(11) DEFAULT NULL,
  `shoptotal` int(11) DEFAULT NULL,
  `shoptext` text,
  `create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- 转存表中的数据 `shops`
--

INSERT INTO `shops` (`id`, `shopname`, `shopprice`, `shoptotal`, `shoptext`, `create_time`) VALUES
(4, '柠檬茶', 3, 34, '这酸爽', '2023-11-23 06:05:17'),
(5, '冰绿茶', 3, 22, '好喝好喝', '2023-11-23 06:05:47'),
(7, '棒棒糖', 1, 12, '好吃好吃', '2023-11-23 06:10:26'),
(8, '洗发水', 23, 53, '去油去屑', '2023-11-23 06:15:04'),
(9, '沐浴露', 32, 26, '干净清爽', '2023-11-23 06:16:13'),
(10, '笔记本电脑', 2599, 5, 'i7级游戏本', '2023-11-23 06:16:50'),
(11, '华为手机P60', 5999, 1, '遥遥领先', '2023-11-23 06:17:45'),
(12, '玫瑰花', 99, 999, '浪漫主义', '2023-11-25 06:44:44');

-- --------------------------------------------------------

--
-- 表的结构 `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `usertype` varchar(50) DEFAULT NULL,
  `usertell` bigint(11) DEFAULT NULL,
  `usermail` varchar(50) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- 转存表中的数据 `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `usertype`, `usertell`, `usermail`, `create_time`) VALUES
(1, 'admin', 'admin', '管理员', 13888156156, '[email protected]', '2023-11-23 05:04:33');

--
-- 转储表的索引
--

--
-- 表的索引 `shops`
--
ALTER TABLE `shops`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `shopname` (`shopname`);

--
-- 表的索引 `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- 在导出的表使用AUTO_INCREMENT
--

--
-- 使用表AUTO_INCREMENT `shops`
--
ALTER TABLE `shops`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- 使用表AUTO_INCREMENT `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Related Posts