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 MySQL table
within the database in two ways using a single query:
- Insert
record in a single row
- Insert
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 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 below 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 date in 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 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 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 values
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 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 returns in the result set. The asterisk (*)
returns all fields of a table. |
|
table_name(s) |
Name
of tables, which 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 grouped 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 to which row returns first. By default, starts with zero. |
|
LIMIT |
Optional
clause. Used to limit the number of returned records in the result set. |
For examples:-- List all rows for the specified columns
SELECT name, price FROM products;
+-----------+-------+
| name | price |
+-----------+-------+
| Pen Red | 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
5 rows in set (0.00 sec)
-- List all rows of ALL the columns. The wildcard * denotes ALL columns
SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
+-----------+-------------+-----------+----------+-------+
5 rows in set (0.00 sec)
Comparison Operators
For numbers (INT, DECIMAL, FLOAT), you could use comparison operators:
'=' (equal to), '<>' or '!=' (not equal to), '>' (greater than), '<' (less than),
'>=' (greater than or equal to), '<=' (less than or equal to), to compare two numbers.
For example, price > 1.0, quantity <= 500.
SELECT name, price FROM products WHERE price < 1.0;
+-----------+-------+
| name | price |
+-----------+-------+
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
2 rows in set (0.00 sec)
SELECT name, quantity FROM products WHERE quantity <= 2000;
+-----------+----------+
| name | quantity |
+-----------+----------+
| Pen Black | 2000 |
+-----------+----------+
1 row in set (0.00 sec)
CAUTION: Do not compare FLOATs (real numbers) for equality ('=' or '<>'),
as they are not precise. On the other hand, DECIMAL are precise.
For strings, you could also use '=', '<>', '>', '<', '>=', '<=' to compare two strings
(e.g., productCode = 'PEC'). The ordering of string depends on the so-called collation chosen.
For example,
SELECT name, price FROM products WHERE productCode = 'PEN';
-- String values are quoted
+-----------+-------+
| name | price |
+-----------+-------+
| Pen Red | 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
+-----------+-------+
3 rows in set (0.00 sec)
String Pattern Matching - LIKE and NOT LIKE LIKE (or NOT LIKE) is wildcard characters.
The wildcard '_' matches any single character; '%' matches any number of characters (including zero).
For example,
· 'abc%' matches strings beginning with 'abc';
· '%xyz' matches strings ending with 'xyz';
· '%aaa%' matches strings containing 'aaa';
· '___' matches strings containing exactly three characters; and
· 'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more characters.
-- "name" begins with 'PENCIL'
SELECT name, price FROM products WHERE name LIKE 'PENCIL%';
+-----------+-------+
| name | price |
+-----------+-------+
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
-- "name" begins with 'P', followed by any two characters,
-- followed by space, followed by zero or more characters
SELECT name, price FROM products WHERE name LIKE 'P__ %';
+-----------+-------+
| name | price |
+-----------+-------+
| Pen Red | 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
+-----------+-------+
Logical Operators - AND, OR, NOT, XOR
Examples:-
SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %';
+-----------+-------------+----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+----------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
+-----------+-------------+----------+----------+-------+
SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+---------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
+-----------+-------------+---------+----------+-------+
SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
+-----------+-------------+-----------+----------+-------+
IN, NOT IN
SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
+-----------+-------------+-----------+----------+-------+
BETWEEN, NOT BETWEEN
SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1003 | PEN | Pen Black | 2000 | 1.25 |
+-----------+-------------+-----------+----------+-------+
IS NULL, IS NOT NULL
NULL is a special value, which represent "no value", "missing value" or "unknown value".
For example,
SELECT * FROM products WHERE productCode IS NULL;
Empty set (0.00 sec)
Using comparison operator (such as = or <>) to check for NULL is a mistake - a very common mistake.
For example,
SELECT * FROM products WHERE productCode = NULL;
-- This is a common mistake. NULL cannot be compared.
ORDER BY Clause
syntax:
SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...
The selected row will be ordered according to the values in columnA, in either ascending (ASC) (default) or descending (DESC) order.
If several rows have the same value in columnA, it will be ordered according to columnB, and so on.
For examples,
-- Order the results by price in descending order
SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1001 | PEN | Pen Red | 5000 | 1.23 |
+-----------+-------------+-----------+----------+-------+
-- Order by price in descending order, followed by quantity in ascending (default) order
SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1001 | PEN | Pen Red | 5000 | 1.23 |
+-----------+-------------+-----------+----------+-------+
randomize the returned records via function RAND(), e.g.,
SELECT * FROM products ORDER BY RAND();
LIMIT Clause
A SELECT query on a large database may produce many rows. So use the LIMIT clause to limit the number of rows displayed, e.g.,
-- Display the first two rows
SELECT * FROM products ORDER BY price LIMIT 2;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
+-----------+-------------+-----------+----------+-------+
the number of rows to be displayed in the LIMIT clause, as follows:
--Skip the first two rows and display the next 1 row
SELECT * FROM products ORDER BY price LIMIT 2, 1;
+-----------+-------------+---------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+---------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
+-----------+-------------+---------+----------+-------+
AS - Alias
Keyword AS is define an alias for an identifier (such as column name, table name).
The alias will be used in displaying the name. It can also be used as reference.
For example,
SELECT productID AS ID, productCode AS Code, name AS Description, price AS `Unit Price` FROM products ORDER BY ID;
-- Define aliases to be used as display names and alias ID as reference
+------+------+-------------+------------+
| ID | Code | Description | Unit Price |
+------+------+-------------+------------+
| 1001 | PEN | Pen Red | 1.23 |
| 1002 | PEN | Pen Blue | 1.25 |
| 1003 | PEN | Pen Black | 1.25 |
| 1004 | PEC | Pencil 2B | 0.48 |
| 1005 | PEC | Pencil 2H | 0.49 |
+------+------+-------------+------------+
Take note that the identifier "Unit Price" contains a blank and must be back-quoted.
Function CONCAT()
Concatenate function used to add a few columns as one (e.g., joining the last name and first name) using function CONCAT().
For example,
SELECT CONCAT(productCode, ' - ', name) AS `Product Description`, price FROM products;
+---------------------+-------+
| Product Description | price |
+---------------------+-------+
| PEN - Pen Red | 1.23 |
| PEN - Pen Blue | 1.25 |
| PEN - Pen Black | 1.25 |
| PEC - Pencil 2B | 0.48 |
| PEC - Pencil 2H | 0.49 |
+---------------------+-------+
DISTINCT
keyword DISTINCT is use to select only distinct values. For examples,
-- Without DISTINCT
SELECT price FROM products;
+-------+
| price |
+-------+
| 1.23 |
| 1.25 |
| 1.25 |
| 0.48 |
| 0.49 |
+-------+
--DISTINCT on price
SELECT DISTINCT price AS `Distinct Price` FROM products;
+----------------+
| Distinct Price |
+----------------+
| 1.23 |
| 1.25 |
| 0.48 |
| 0.49 |
+----------------+
-- DISTINCT combination of price and name
SELECT DISTINCT price, name FROM products;
+-------+-----------+
| price | name |
+-------+-----------+
| 1.23 | Pen Red |
| 1.25 | Pen Blue |
| 1.25 | Pen Black |
| 0.48 | Pencil 2B |
| 0.49 | Pencil 2H |
+-------+-----------+
GROUP BY Clause
The GROUP BY the clause allows collapsing multiple records with a common value into groups.
For example,
SELECT * FROM products ORDER BY productCode, productID;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
+-----------+-------------+-----------+----------+-------+
SELECT * FROM products GROUP BY productCode;
-- Only first record in each group is shown
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1001 | PEN | Pen Red | 5000 | 1.23 |
+-----------+-------------+-----------+----------+-------+
GROUP BY aggregate functions (such as COUNT(), AVG(), SUM()) to produce group summary.
GROUP BY Aggregate Functions:
COUNT, MAX, MIN, AVG, SUM, STD, GROUP_CONCAT
The function COUNT(*) returns the rows selected;
COUNT(columnName) counts only the non-NULL values of the given column. For example,
-- Function COUNT(*) returns the number of rows selected
SELECT COUNT(*) AS `Count` FROM products;
-- All rows without GROUP BY clause
+-------+
| Count |
+-------+
| 5 |
+-------+
SELECT productCode, COUNT(*) FROM products GROUP BY productCode;
+-------------+----------+
| productCode | COUNT(*) |
+-------------+----------+
| PEC | 2 |
| PEN | 3 |
+-------------+----------+
-- Order by COUNT - need to define an alias to be used as reference
SELECT productCode, COUNT(*) AS count FROM products GROUP BY productCode
ORDER BY count DESC;
+-------------+-------+
| productCode | count |
+-------------+-------+
| PEN | 3 |
| PEC | 2 |
+-------------+-------+
Besides COUNT(), there are many other GROUP BY aggregate functions such as AVG(), MAX(), MIN() and SUM().
For example,
SELECT MAX(price), MIN(price), AVG(price), STD(price), SUM(quantity) FROM products;
-- Without GROUP BY - All rows
+------------+------------+------------+------------+---------------+
| MAX(price) | MIN(price) | AVG(price) | STD(price) | SUM(quantity) |
+------------+------------+------------+------------+---------------+
| 1.25 | 0.48 | 0.940000 | 0.371591 | 33000 |
+------------+------------+------------+------------+---------------+
SELECT productCode, MAX(price) AS `Highest Price`, MIN(price) AS `Lowest Price`
FROM products GROUP BY productCode;
+-------------+---------------+--------------+
| productCode | Highest Price | Lowest Price |
+-------------+---------------+--------------+
| PEC | 0.49 | 0.48 |
| PEN | 1.25 | 1.23 |
+-------------+---------------+--------------+
SELECT productCode, MAX(price), MIN(price), CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`, CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`, SUM(quantity)
FROM products GROUP BY productCode;
-- Use CAST(... AS ...) function to format floating-point numbers
+-------------+------------+------------+---------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | Std Dev | SUM(quantity) |
+-------------+------------+------------+---------+---------+---------------+
| PEC | 0.49 | 0.48 | 0.49 | 0.01 | 18000 |
| PEN | 1.25 | 1.23 | 1.24 | 0.01 | 15000 |
+-------------+------------+------------+---------+---------+---------------+
HAVING clause
HAVING is similar to WHERE, but it can operate on the GROUP BY aggregate functions; whereas WHERE operates only on columns.
SELECT productCode AS `Product Code`, COUNT(*) AS `Count`, CAST(AVG(price) AS DECIMAL(7,2)) AS `Average` FROM products GROUP BY productCode HAVING Count>=3;
-- CANNOT use WHERE count >= 3
+--------------+-------+---------+
| Product Code | Count | Average |
+--------------+-------+---------+
| PEN | 3 | 1.24 |
+--------------+-------+---------+
WITH ROLLUP
The WITH ROLLUP clause shows the summary of group summary, e.g.,
SELECT productCode, MAX(price), MIN(price), CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`, SUM(quantity) FROM products GROUP BY productCode WITH ROLLUP;
-- Apply aggregate functions to all groups
+-------------+------------+------------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | SUM(quantity) |
+-------------+------------+------------+---------+---------------+
| PEC | 0.49 | 0.48 | 0.49 | 18000 |
| PEN | 1.25 | 1.23 | 1.24 | 15000 |
| NULL | 1.25 | 0.48 | 0.94 | 33000 |
+-------------+------------+------------+---------+---------------+
Query data from multiple tables using inner join:
SELECT select_list FROM table1 INNER JOIN table2 ON condition;
Query data from multiple tables using left join:
SELECT select_list FROM table1 LEFT JOIN table2 ON condition;
Query data from multiple tables using right join:
SELECT select_list FROM table1 RIGHT JOIN table2 ON condition;
Make a Cartesian product of rows:
SELECT select_list FROM table1 CROSS JOIN table2;
Counting rows in a table.
SELECT COUNT(*) FROM table_name;
Sorting a result set:
SELECT select_list FROM table_name ORDER BY column1 ASC [DESC], column2 ASC [DESC];
Group rows using the GROUP BY clause.
SELECT select_list FROM table_name GROUP BY column_1, column_2, ...;
Filter group using the HAVING clause:
SELECT select_list FROM table_name GROUP BY column1 HAVING condition;
Searching
Search for data using the LIKE operator:
SELECT select_list FROM table_name WHERE column LIKE '%pattern%';
Text search using a regular expression with RLIKE operator.
SELECT select_list FROM table_name WHERE column RLIKE 'regular_expression';
WHERE Clause
MySQL WHERE Clause is used with SELECT, INSERT, UPDATE, and DELETE clause to filter the results. It specifies a specific position for performing operations.
Syntax:
1. WHERE conditions;
MySQL WHERE Clause with a single condition
Ex. SELECT * FROM officers WHERE address = 'Mau';
MySQL WHERE Clause with AND condition
SELECT * FROM officers WHERE address = 'Lucknow' AND officer_id < 5;
WHERE Clause with OR condition
SELECT * FROM officers WHERE address = 'Lucknow' OR address = 'Mau';
MySQL WHERE Clause with the combination of AND & OR conditions
SELECT * FROM officers WHERE (address = 'Mau' AND officer_name = 'Ajeet')
OR (officer_id < 5);
Distinct Clause
MySQL DISTINCT clause is used to remove duplicate records from the table and fetch only the unique records. The DISTINCT clause is only used with the SELECT statement.
Syntax:
1. SELECT DISTINCT expressions FROM tables [WHERE conditions];
1. SELECT DISTINCT address FROM officers;
MySQL DISTINCT Clause with multiple expressions
Use multiple expressions with the DISTINCT Clause then the MySQL DISTINCT clause will remove duplicates from more than one field in your SELECT statement.
SELECT DISTINCT officer_name, address FROM officers;
FROM Clause
The MySQL FROM Clause is used to select some records from a table. It can also be used to retrieve records from multiple tables using the JOIN condition.
Syntax:
FROM table1 [ { INNER JOIN | LEFT [OUTER] JOIN| RIGHT [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]
Note:
If you are using the FROM clause in a MySQL statement then at least one table must have been selected.
If you are using two or more tables in the MySQL FROM clause, these tables are generally joined using INNER or OUTER joins.
MySQL FROM Clause: Retrieve data from one table
SELECT * FROM officers WHERE officer_id <= 3;
MySQL FROM Clause: Retrieve data from two tables with inner join
retrieve data from two tables using INNER JOIN.
SELECT officers.officer_id, students.student_name FROM students INNER JOIN officers ON students.student_id = officers.officer_id;
Retrieve data from two tables using an outer join
SELECT officers.officer_id, students.student_name FROM officers LEFT OUTER JOIN
students ON officers.officer_id = students.student_id;
ORDER BY Clause
used to sort the records in ascending or descending order.
Syntax:
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ ASC | DESC ];
Note:
ORDER BY clause USED in a SELECT statement, SELECT LIMIT statement, and DELETE LIMIT statement.
ORDER BY: without using ASC/DESC attribute
by default result in ascending order.
SELECT * FROM officers WHERE address = 'Lucknow' ORDER BY officer_name;
Using both ASC and DESC attributes
SELECT officer_name, address FROM officers WHERE officer_id < 5 ORDER BY officer_name DESC, address ASC;
GROUP BY Clause
Used to collect data from multiple records and group the result by one or more columns.
Generally used in a SELECT statement and also used some aggregate functions like COUNT, SUM, MIN, MAX, AVG, etc. on the grouped column.
Syntax:
SELECT expression1, expression2, ... expression_n, aggregate_function (expression)
FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
(i) MySQL GROUP BY Clause with the COUNT function
SELECT address, COUNT(*) FROM officers GROUP BY address;
(ii) MySQL GROUP BY Clause with SUM function
Using the SUM function and return total value.
Example:-
SELECT emp_name, SUM(working_hours) AS "Total working hours"
FROM employees
GROUP BY emp_name;
(iii) MySQL GROUP BY Clause with MIN function
Specifies the minimum value.
SELECT emp_name, MIN(working_hours) AS "Minimum working hour"
FROM employees group BY emp_name;
(iv) MySQL GROUP BY Clause with MAX function
Specifies the maximum value.
SELECT emp_name, MAX (working_hours) AS "Minimum working hour"
FROM employees GROUP BY emp_name;
(v) MySQL GROUP BY Clause with AVG function
specifies the average value.
SELECT emp_name, AVG(working_hours) AS "Average working hour"
FROM employees GROUP BY emp_name;
(VI) HAVING Clause
HAVING Clause is used with GROUP BY clause. It always returns the rows where the condition is TRUE.
Syntax:
1. SELECT expression1, expression2, ... expression_n, aggregate_function (expression)
2. FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING condition;
example:
1. SELECT emp_name, SUM(working_hours) AS "Total working hours"
FROM employees GROUP BY emp_name HAVING SUM(working_hours) > 5;
SQL - EXCEPT Clause
EXCEPT clause/operator is used to combine two SELECT statements and returns only rows, which are not presented in the second SELECT statement.
Just like the UNION operator, the same rules apply using the EXCEPT operator. MySQL does not support the EXCEPT operator.
Except Query
Syntax:-
===========================================================
0 Comments