mysql - 自动更新Mysql数据库条目

Question

我有一家小企业，我制作了一个简单的 mysql 数据库，用于输入客户名称、日期时间、详细信息、发票、签名者、账单总额、本月迄今、年初至今、购买的零件、年初至今购买的零件。我想自动更新美元金额，这可能吗？例如:下个月至今为 1300.00 美元入场费为 100.00 美元，总计 1400.00 美元。任何帮助将不胜感激。

Answer 1

您需要以准确且高效的方式存储您的信息。然后使用查询来提取您要查找的本月至今和年初至今的信息。

让我们创建一个测试数据库和一些表。您可以根据需要添加列。

测试数据库

CREATE DATABASE stackoverflow;
USE stackoverflow;

为客户创建表格

这将存储您的所有客户

CREATE TABLE clients ( 
  client_id       INT NOT NULL PRIMARY KEY auto_increment,
  client_name     VARCHAR( 70 ) NOT NULL,
  client_details  TEXT
) engine = myisam DEFAULT charset=latin1;

创建零件表

这将存储您希望向客户开具发票的所有零件

CREATE TABLE parts ( 
  part_id         INT NOT NULL PRIMARY KEY auto_increment,
  part_name       VARCHAR( 70 ) NOT NULL,
  part_cost       NUMERIC( 15,2 ),
  description     TEXT
) engine = myisam DEFAULT charset=latin1;

创建发票表

这将存储创建的每张发票。请注意，这里没有美元值(value)或零件。这样做将减少相同信息必须存储的次数。

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. Wikipedia

CREATE TABLE invoices ( 
  invoice_id      INT NOT NULL PRIMARY KEY auto_increment,
  client_id       INT NOT NULL,
  invoice_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  details         TEXT,
  signer          VARCHAR ( 70 ) NOT NULL
) engine = myisam DEFAULT charset=latin1;

创建表以将零件连接到发票

现在，让我们创建一个表，将零件表与发票连接起来。在这里，我们可以添加每张发票的每个项目特有的注释和数量等信息。我们将使用查询中的数量来计算总计、MTD、YTD 等。

CREATE TABLE invoice_parts (
  id              INT NOT NULL PRIMARY KEY auto_increment,
  part_id         INT NOT NULL,
  invoice_id      INT NOT NULL,
  quantity        INT NOT NULL,
  notes           TEXT
) engine = myisam DEFAULT charset=latin1;

让我们添加一些测试信息

-- add some clients
INSERT INTO `clients` (`client_id`, `client_name`, `client_details`) VALUES (NULL, 'chad barker', 'some guy named chad'), (NULL, 'leon tester', 'some guy named leon');

-- add some parts
INSERT INTO `parts` (`part_id`, `part_name`, `part_cost`, `description`) VALUES (NULL, 'can of awesome', '1.99', 'awesome can of awesome'), (NULL, 'can of fail', '.25', 'can filled with failure'), (NULL, 'box of blocks', '24.99', 'box full of cube shaped items called blocks'), (NULL, 'bag of air', '3.99', 'reusable bag filled with useless air');

-- add some invoices
INSERT INTO `invoices` (`invoice_id`, `client_id`, `invoice_date`, `details`, `signer`) VALUES (NULL, '1', CURRENT_TIMESTAMP, 'there was an order for some stuff', ''), (NULL, '1', CURRENT_TIMESTAMP, 'more stuff needed now', ''), (NULL, '2', CURRENT_TIMESTAMP, 'need some stuff quick', ''), (NULL, '2', CURRENT_TIMESTAMP, 'don''t ship, just charge me', '');

-- add some invoice/part connections
INSERT INTO `invoice_parts` (`part_id`, `invoice_id`, `quantity`, `notes`) VALUES ('3', '1', '3', 'nothing special'), ('2', '1', '2', 'wants blue'), ('4', '2', '32', 'wants every color'), ('3', '2', '31', 'wants clear'), ('2', '3', '12', 'wants blue'), ('1', '4', '2', 'wants every color'), ('1', '1', '3', 'nothing special'), ('2', '1', '2', 'wants blue'), ('3', '2', '22', 'wants every color'), ('4', '2', '3', 'wants clear'), ('2', '3', '12', 'wants blue'), ('3', '4', '2', 'wants every color');

查询发票信息

SELECT
  c.client_name,
  i.invoice_date,
  i.details,
  i.signer
FROM invoices i
INNER JOIN clients c
ON i.client_id = c.client_id
WHERE i.invoice_id = 1
;

查询零件列表

SELECT
  ip.invoice_id,
  p.part_id,
  p.part_name,
  p.part_cost,
  ip.quantity,
  p.part_cost * ip.quantity as total
FROM invoice_parts ip
INNER JOIN parts p
ON ip.part_id = p.part_id
WHERE ip.invoice_id = 1;

查询发票总费用

SELECT
  ip.invoice_id,
  SUM( p.part_cost * ip.quantity ) as invoice_total
FROM invoice_parts ip
INNER JOIN parts p
ON ip.part_id = p.part_id
WHERE ip.invoice_id = 1;

按选定月份查询

SELECT
  month( i.invoice_date ) as month,
  count( distinct ip.invoice_id ) as invoices,
  count( * ) as parts,
  p.part_cost * ip.quantity as total
FROM invoices i
INNER JOIN invoice_parts ip
ON ip.invoice_id = i.invoice_id
INNER JOIN parts p
ON ip.part_id = p.part_id
WHERE i.client_id = 2
AND month( i.invoice_date ) = 5;

按选定年份查询

SELECT
  year( i.invoice_date ) as year,
  count( distinct ip.invoice_id ) as invoices,
  count( * ) as parts,
  p.part_cost * ip.quantity as total
FROM invoices i
INNER JOIN invoice_parts ip
ON ip.invoice_id = i.invoice_id
INNER JOIN parts p
ON ip.part_id = p.part_id
WHERE i.client_id = 1
AND year( i.invoice_date ) = 2014;