Manipulate tables in the database is essential to develop any activity related to development. Add, delete, and query records are essential functions in any application, whether or not WEB.
In MySQL you can manipulate the records in a table quickly and very simply through a series of commands. Next, we show how to query, add, update delete records from a table in MySQL
Query for records in MySQL
To query the records of a table in MySQL SELECT command is used, as the following syntax:
As a practical example we will see all the products in a table.
You can use the character (*) to return all fields from the table or select which fields you want to display in the query.
SELECT cod_prod, name
Insert records into a table in MySQL
To perform the insertion of a record in a table in MySQL just use the following syntax:
INSERT [table] (fields) VALUES (values)
As a practical example we will use again the product table.
INSERT tbl_product (cod_prod, Name) VALUES (1, ‘Tv’)
Update the record of a table in MySQL
When you need to update a field the following syntax is used.
UPDATE [Table] SET [Column name] = [value] WHERE [conditional]
It is very important that the conditional is inserted when there is update a particular record in the table, if it does not happen all the records are updated with the same information and this can create a problem of inconsistency in the data base.
Using the Products table, we go to a practical example:
UPDATE tbl_product SET Name = ‘Tv 2’ WHERE cod_prod = 1
Note that only the record that has the product code 1 will have your updated name, with that other records are maintained and there is no possibility of errors in the database.
Deleting records in a table in MySQL
To delete a particular record in MySQL you must use the DELETE command, as the following syntax:
DELETE FROM [table] WHERE [conditional]
As in the UPDATE command, it is essential to use the conditional. If it is not used all records of the table will be deleted.
Let us use the products table as an example to illustrate the operation of this command.
DELETE FROM tbl_product WHERE cod_prod = 1
MySQL is now one of the databases most cost-effective and therefore is widely used in development projects, with that understanding its operation ends up being essential for anyone working on projects for the internet.
Discover the advantages of combining work with MySQL in PHP projects, visit the link above and find out.