A simple question on MySQL?

Question

MySQL

Good afternoon, dear,
I strongly ask you not to downvote, as I am just trying to get to know mysql better. I have a question about adding a column to an existing table.
There is such a table:

mysql> describe 37A;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(10) unsigned | NO   | PRI | NULL    | auto_increment |
| part        | varchar(20)         | NO   | PRI | NULL    |                |
| description | varchar(150)        | NO   |     | NULL    |                |
| unit        | varchar(5)          | NO   |     | NULL    |                |
| quantity    | decimal(3,0)        | NO   |     | NULL    |                |
| USD         | decimal(10,2)       | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

One more column needs to be added to this table, the Total_in_USD column should be quantity multiplied by USD.
I know that the question is quite simple, I read a little, unfortunately it did not work out.
Thank you in advance.

Answer 1

About SELECT and ALTER you have already been written. And here is an example of the triggers you need:

CREATE DEFINER = 'root'@'localhost' TRIGGER `tab_before_ins_tr` BEFORE INSERT ON `tab`  
FOR EACH ROW
BEGIN
  SET NEW.Total_in_USD = NEW.quantity * NEW.USD;
END;

CREATE DEFINER = 'root'@'localhost' TRIGGER `tab_before_upd_tr` BEFORE UPDATE ON `tab`
FOR EACH ROW
BEGIN
  SET NEW.Total_in_USD = NEW.quantity * NEW.USD;
END;

Answer 2

They don't do it this way, it's redundant information, you can just pull it out when requesting:
SELECT *, (quantity*USD as Total_in_USD) FROM 37A;
But if you really want to pervert, then dig in the direction of triggers.
On events it is possible to hang up updating of a field.

Answer 3

As it was said, you can not add a column, but calculate it when selecting data using mysql or already when receiving data using apparently php (I don’t know what you use),
but if you need to add it, then:
ALTER TABLE `37A` ADD COLUMN Total_in_USD FLOAT(9,2); - creates a column
then you need to set it to the value
UPDATE `37A` SET Total_in_USD = quantity * USD; - sets the required value for the column,
but in general the question is simple, the network is full of examples, use google / yandex

Answer 4

There is also an option with view
Something like
CREATE VIEW tablemultiple AS SELECT id, part, usd, quantity, quantity*usd as Total_in_USD from a37a

Answer 5

Perhaps the car came from another BV (for example, ORACLE or MsSQL) where there are calculated fields. Due to them, saving resources during the selection, but a heavier insert (update). This is very similar to the example with triggers - only it is done without triggers. As a matter of fact leads to denormalization for which it is not necessary to watch.
It is very useful if, for example, a selection is based on the result of performing a complex mathematical function on several fields.