DML Commands in MYSQL
Data Manipulation Language (DML) commands are used in MySQL to handle the actual data within the schema objects once the database structure has been established using Data Definition Language (DDL) commands. The component of SQL called DML gives users the ability to view and work with the data that is kept in the database. The table’s stored records can be retrieved, inserted, deleted, and modified using the main DML methods.
SELECT, INSERT, UPDATE, and DELETE are the basic DML commands.
Inserting Data (INSERT)
To add new records (rows) to a table, use the INSERT statement.
When inserting a single row with specified columns and values, the basic syntax is:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);You can skip the column names if you are giving values for each column in the specified order:
INSERT INTO table_name VALUES (value1, value2, ...);It’s crucial to remember that MySQL requires single (‘ ‘) or double (” “) quotes around text and date type values. You can enter numbers without using quotations.
By using a comma to separate the sets of values, bulk data can be captured in a single query:
INSERT INTO table_name (columnA, columnB) VALUES (valueA1, valueB1), (valueA2, valueB2), ...;Example INSERT statements:
Inserting into an Employee table, specifying columns:
INSERT INTO Employee (empID, ename, city, pay) VALUES (1,'Amitabh','Allahabad',15000); Inserting into a towns table, providing values for all columns:
INSERT INTO towns VALUES (33, 'Paris'); Inserting multiple records into a cats table:
INSERT INTO cats ( name, owner, birth) VALUES ('Sandy', 'Lennon', '2015-01-03'), ('Cookie', 'Casey', '2013-11-13'), ('Charlie', 'River', '2016-05-21'); Retrieving Data (SELECT)
Information can be retrieved from a table using the SELECT query. It enables you to submit requests, or queries, to retrieve records that are stored in the database.
The basic syntax of the SELECT statement is:
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;- what_to_select can be an asterisk (*) to indicate all columns.
- Alternatively, you can specify a list of specific columns separated by commas (e.g., column1, column2).
- The FROM clause indicates the table from which you want to retrieve data.
- The WHERE clause is optional.
Example SELECT statements:
Selecting all columns from an employees table:
SELECT * FROM employees; Selecting specific columns (first_name, last_name, job_title) from an employees table:
SELECT first_name, last_name, job_title FROM employees; Selecting specific columns (FirstName, LastName) from a tblReceptionist table:
SELECT FirstName, LastName FROM tblReceptionist; CASE statements
CASE statements in MySQL work like if-then-else statements. It checks conditions and returns a value when the first one is met. The statement returns the result after THEN when a condition is true and ends reading conditions.
If no WHEN conditions are met, the ELSE clause value is returned. No ELSE portion and no conditions make the CASE statement return NULL. The statement block must end with END.
CASE statements can be used in SELECT lists to display multiple outputs based on column values or UPDATE statements to set distinct row values.
Here’s a simple code example using CASE in a SELECT statement:
SELECT column_name,
CASE
WHEN column_name > 100 THEN 'Value is Large'
WHEN column_name > 50 THEN 'Value is Medium'
ELSE 'Value is Small'
END AS value_size
FROM your_table_name;This query returns column_name and a new column value_size based on column_name’s value.
MYSQL Data Manipulation
Filtering Data (WHERE Clause)
When using DML statements, particularly SELECT, UPDATE, and DELETE, the WHERE clause is used to filter records according to a predetermined condition. In programming languages, it functions similarly to a ‘if’ condition. The procedure will only contain rows that meet the criteria or conditions.
The basic syntax when used with SELECT is:
SELECT column1, column2, ... FROM table_name WHERE condition;Conditions typically use Comparison Operators:
- = (Equal to)
- != or <> (Not equal to).
- > (Greater than)
- < (Less than)
- >= (Greater than or equal to).
- <= (Less than or equal to).
Conditions can be combined using Logical Operators:
- AND (&&): All conditions separated by AND must be TRUE.
- OR (||): Any of the conditions separated by OR must be TRUE.
- NOT (!): Negates the condition.
Example WHERE clauses:
Selecting records from projects where start_date is ‘2003-06-01’:
SELECT * FROM projects WHERE start_date='2003-06-01'; Selecting records from tblPatient where Gender is ‘Male’ AND PatientID is >= 5:
SELECT * FROM tblPatient WHERE Gender='Male' AND PatientID>=5; Selecting records from customers where Title is ‘Mr’ OR ‘Mrs’:
SELECT * FROM customers WHERE Title='Mr' OR Title='Mrs'; Selecting records where the condition is NOT TRUE:
SELECT column1, column2, ... FROM table_name WHERE NOT condition; Pattern Matching with LIKE
In a WHERE clause, the LIKE operator is used to look for a certain pattern in a column. Wildcards are used in it:
%: Represents zero, one, or multiple characters.- _: Represents a single character.
Example LIKE statements:
Selecting students whose Name starts with ‘R’:
SELECT Name, Fname, City FROM Student Where Name LIKE ‘R%’ ORDER BY Class; Selecting customers with a CustomerName starting with “a”:
SELECT * FROM Student WHERE name LIKE 'a%'; Range Selection with BETWEEN
Values inside a certain range are chosen by the BETWEEN operator. The values may take the form of dates, text, or numbers. Because the BETWEEN operator is inclusive, the result includes both the beginning and ending values.
Example BETWEEN statements:
Selecting employees whose salary is between 10000 and 20000 (inclusive):
SELECT * FROM Employee WHERE salary BETWEEN 10000 AND 20000; Selecting receptionists whose Salary is between 9000 and 12000:
SELECT LastName, Salary FROM tblReceptionist WHERE Salary BETWEEN 9000 AND 12000; You can also use NOT BETWEEN to select values outside a range.
Checking for NULL Values
A field that has no value is called a NULL value. It is not the same as zero or a field with spaces in it. When creating a record, a field with a NULL value was left empty.
To check for NULL values, you use the IS NULL operator:
SELECT column_names FROM table_name WHERE column_name IS NULL; To check for values that are not NULL, you use IS NOT NULL:
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Example IS NULL/IS NOT NULL statements:
Selecting students where City is NULL:
SELECT * FROM Student WHERE City IS NULL ; Selecting students where City is NOT NULL:
SELECT * FROM Student WHERE City IS NOT NULL; Updating Data (UPDATE)
A table’s existing records can be changed using the UPDATE command.
The basic syntax is:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;- SET specifies the column(s) to update and their new value(s).
- The WHERE clause specifies which rows to update5866. It is very important to include a WHERE clause, otherwise, all rows in the table will be updated.
Example UPDATE statements:
Updating the name for a student with id = 20:
update student set name=”XYZ” where id=20; Updating the end_date for projects that started on ‘2006-01-01’:
UPDATE projects SET end_date = '2006-08-31' WHERE start_date = '2006-01-01';Updating the Salary for a receptionist named ‘Peters’:
UPDATE tblReceptionist SET Salary=10000 WHERE LastName='Peters'; Updating multiple columns for employees in department 3:
UPDATE `employees` SET `salary` = `salary` * 1.10, `job_title` = CONCAT('Senior',' ', `job_title`) WHERE `department_id` = 3; Deleting Data (DELETE)
Existing records can be deleted from a table using the DELETE statement.
The basic syntax is:
DELETE FROM table_name WHERE condition;The rows to be deleted are specified by the WHERE clause. As with UPDATE, all records in the table will be deleted if the WHERE clause is left off. Use caution while using delete statements.
Example DELETE statements:
Deleting a student record with id = 20:
delete from student where id=20; Deleting records from Student where City is ‘Mumbai’:
DELETE FROM Student WHERE City=‘Mumbai’ ;
Deleting records from employees where employee_id is 1:
DELETE FROM `employees` WHERE `employee_id` = 1; Deleting all records from a table (Caution: use with care!):
DELETE FROM table_name;Difference between TRUNCATE (DDL) and DELETE (DML)
It’s critical to differentiate DELETE from other operations that eliminate structures or data.
- To eliminate rows from a table, use the DML command DELETE. A WHERE clause can be used to eliminate particular rows.
- To eliminate every record from a table, use the DDL command TRUNCATE TABLE. It retains the table structure but eliminates the allotted space. Importantly, a WHERE clause cannot be used by TRUNCATE TABLE to exclude only particular rows. For deleting every row, TRUNCATE TABLE frequently works faster than DELETE.
Example TRUNCATE TABLE statement:
TRUNCATE TABLE users; Sorting Data (ORDER BY Clause)
The result set is sorted using the SELECT command and the ORDER BY clause.
The basic syntax is:
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;- One or more designated columns are used as the basis for sorting.
- The ascending (ASC) sort order is the default.
- The DESC keyword is used after the column name to sort in descending order.
- Sorting by more than one column is possible if you list them after ORDER BY. For rows that have the same value in the first column, the result is sorted by the first column listed, followed by the second, and so on.
Example ORDER BY statements:
Sorting the Student table by City in ascending order (default):
SELECT * FROM Student ORDER BY City; Sorting the Student table by City in descending order:
SELECT * FROM Student ORDER BY City DESC; Sorting the emp table by salary in descending order:
SELECT * FROM emp ORDER BY salary DESC; Sorting by alias column:
SELECT Name, Basic+DA AS ‘PAY’ FROM Student ORDER BY PAY; Limiting Results (LIMIT Clause)
To limit how many rows a SELECT statement can return, use the LIMIT clause.
The syntax includes choosing how many rows to return and, if desired, an offset:
SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
SELECT column_name(s) FROM table_name LIMIT offset, row_count; - number or row_count specifies the maximum number of rows to return.
- offset (optional) specifies the starting row (the first row is offset 0).
Example LIMIT statements:
Choosing the top five entries from the table of employees:
SELECT first_name, last_name FROM employees LIMIT 5;Starting with the third row (offset 3) of the customers database, four records are chosen:
SELECT * FROM customers LIMIT 3,4; The fundamental tools for working with and modifying the data in your MySQL database tables are provided by these simple DML commands and the clauses that go with them.