DML (DATA MANIPULATION LANGUAGE)
Support various types of commands, such as
I.
Insert
II.
Update
III.
Delete
IV.
Select
1. INSERT
Used to store or add data in a MySQL table
within the database in two ways using a single query:
- Insert a record in a single row
- Insert a record in multiple rows
Syntax:-
INSERT INTO tableName VALUES (firstColumnValue, ..., lastColumnValue) -- All columns
For inserting multiple rows in one INSERT INTO statement:
INSERT INTO tableName VALUES
(row1FirstColumnValue, ..., row1lastColumnValue),
(row2FirstColumnValue, ..., row2lastColumnValue),
...
To insert a row with values on selected columns only, use:
-- Insert a single record with selected columns
INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, ..., columnNValue)
-- Alternately, use SET to set the values
INSERT INTO tableName SET column1=value1, column2=value2, ...
-- Insert multiple records
INSERT INTO tableName
(column1Name, ..., columnNName)
VALUES
(row1column1Value, ..., row2ColumnNValue),
(row2column1Value, ..., row2ColumnNValue),
...
Example:
1. INSERT INTO People (id, name, occupation, age) VALUES (101, 'Peter', 'Engineer', 32);
2. INSERT INTO People VALUES (102, 'Joseph', 'Developer', 30),
(103, 'Mike', 'Leader', 28), (104, 'Stephen', 'Scientist', 45);
3. INSERT INTO People (name, occupation) VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');
Inserting Date in MySQL Table:
Provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME, and YEAR.
The default format of the date is YYYY-MM-DD.
This format has the following descriptions:
YYYY: four-digit year, like 2020.
MM: two-digit month, like 01, 02, 03, and 12.
DD: two-digit day, like 01, 02, 03, and 31.
Basic syntax to insert a date in a MySQL table:
INSERT INTO table_name (column_name, column_date) VALUES ('DATE: Manual Date', '2008-7-04');
INSERT INTO Employee (emp_id, name, birthdate, gender, hire_date)
VALUES (101, 'Bryan', '1988-08-12', 'M', '2015-08-26'), (102, 'Joseph', '1978-05-12', 'M', '2014-10-21'), (103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'), (105, 'Marie', '1990-02- 11', 'F', '2018-10-12');
If you want to insert a date in the mm/dd/yyyy format, then use :
INSERT INTO table_name VALUES (STR_TO_DATE(date_value, format_specifier));
If you want to copy all data from one table into another table, use the statement:
1. INSERT INTO table_name2 SELECT * FROM table_name1 WHERE condition;
MySQL version 8.0.19, use a TABLE statement in place of SELECT query to get the same output, as shown below:
1. INSERT INTO table2 TABLE table1;
=============================================
UPDATE
Modifying Data in the table:-
To modify existing data, use the UPDATE ... SET command, with the following
syntax:
UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria
1. UPDATE table_name SET column_name1 = new-value1, column_name2=new-value2, ... [WHERE Clause]
For example,
UPDATE trainer SET email = 'mike@tutorialandexamples.com'
WHERE course_name = 'Java'; UPDATE People SET name = 'Mary',
occupation = 'Content Writer' WHERE id = 105;
-- Increase the price by 10% for all products
UPDATE products SET price = price * 1.1;
SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.35 |
| 1002 | PEN | Pen Blue | 8000 | 1.38 |
| 1003 | PEN | Pen Black | 2000 | 1.38 |
| 1004 | PEC | Pencil 2B | 10000 | 0.53 |
| 1005 | PEC | Pencil 2H | 8000 | 0.54 |
+-----------+-------------+-----------+----------+-------+
-- Modify selected rows
UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';
SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+---------+----------+-------+
| 1001 | PEN | Pen Red | 4900 | 1.35 |
+-----------+-------------+---------+----------+-------+
-- You can modify more than one value
UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen Red';
SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+---------+----------+-------+
| 1001 | PEN | Pen Red | 4950 | 1.23 |
+-----------+-------------+---------+----------+-------+
=============================================================
DELETE
Deleting Rows from the table
syntax:
-- Delete all rows from the table. Records are NOT recoverable!!!
DELETE FROM tableName
-- Delete only row(s) that meets the criteria
DELETE FROM tableName WHERE criteria
For example,
DELETE FROM Employees WHERE emp_id=107;
DELETE FROM products WHERE name LIKE 'Pencil%';
SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001 | PEN | Pen Red | 4950 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.38 |
| 1003 | PEN | Pen Black | 2000 | 1.38 |
+-----------+-------------+-----------+----------+-------+
DELETE FROM products;
SELECT * FROM products;
Empty set (0.00 sec)
====================================================
SELECT -Querying the Database
The SELECT command uses the following syntax:
-- List all the rows of the specified columns
SELECT column1Name, column2Name, ... FROM tableName
-- List all the rows of ALL columns, * is a wildcard denoting all columns
SELECT * FROM tableName
-- List rows that meet the specified criteria in the WHERE clause
SELECT column1Name, column2Name,... FROM tableName WHERE criteria
SELECT * FROM tableName WHERE criteria
The SELECT statement uses the following parameters:
|
Parameter Name |
Descriptions |
|
field_name(s)
or * |
Used
to specify one or more columns to return in the result set. The asterisk (*)
returns all fields of a table. |
|
table_name(s) |
Name
of the tables from which you want to fetch data. |
|
WHERE |
Optional
clause. Specifies the condition that returned the matched records in the
result set. |
|
GROUP
BY |
Optional
clause. Collects data from multiple records and groups them by one or more
columns. |
|
HAVING |
Optional
clause. Works with the GROUP BY clause and returns only those rows whose
condition is TRUE. |
|
ORDER
BY |
Optional
clause. Used for sorting the records in the result set. |
|
OFFSET |
Optional
clause. Specifies which row returns first. By default, it starts with zero. |
|
LIMIT |
Optional
clause. Used to limit the number of returned records in the result set. |
===========================================================
0 Comments