Data Manipulation Language (DML)

Thebestindonesia.com – Data Manipulation Language (DML) can be used if DDL has occurred. The function of this DML Query is to manipulate data in the database, for example, for retrieving, inserting, modifying, and deleting data. Basic commands in DML include:

1. INSERT: used to insert data into a table
2. DELETE: used to delete data from the table
3. UPDATE: used to change the data in the table
4. SELECT: used to select and display data

A. INSERT
The INSERT command adds rows or enters data in a table. The requirement to enter data is that a table has been created in a database beforehand. There are three ways to enter data, namely:

INSERT INTO tablename VALUES (value1, value2, value-n); 

The command above functions to enter data into the table sequentially from the leftmost column to the very end. The values given must be the same as the table’s available columns.

INSERT INTO tablename (column1, column2, column-n) VALUES (value1, value2, value-n);

The command above doesn’t care whether the column that should be NOT NULL is filled. This method is used as needed because it can cause errors like the Primary key column is not filled in.

INSERT INTO tablename SET column1 = value1, column2 = value2, Column-n = value-n;

The command above must write the column names correctly. Otherwise, it will cause an error.
Information:

If the data type is string, date, or time (example: gita, database modeling, 1993-03-20) then the value is enclosed in single quotation marks (‘gita’) or double quotation marks (“gita”). If the data is of numeric type (example: 11, 9), then the value is not enclosed in single or double quotation marks.

Read also : Data Definition Language (DDL)

B. DELETE
The DELETE command is used to delete records from a table. Whereas to delete records in a specific column, use the UPDATE command.

DELETE FROM tablename [WHERE condition];

Commands in [] are optional to delete a line with certain conditions.

C. UPDATES
The UPDATE command is used to change the contents of data in one or several columns in a table.

UPDATE tablename SET column-n = value-n [WHERE condition]; 

Commands in the [] sign are optional to change a line with certain conditions.

D. SELECT
The SELECT command displays the contents of a table that can be connected to other tables. In its simplest form, the SELECT function only has the word pair FROM, which means selecting the table to SELECT. There are three ways to display the contents of a table, namely:

Display data for all columns using an asterisk (*)

SELECT * FROM tablename;

Display data for a specific column

SELECT column1, column2, column-n FROM tablename;

Display data with specific data conditions with the WHERE

SELECT clause * FROM table name WHERE conditions;

Some comparison operators that can be used in the WHERE clause are “=” (equal), > (more than), < (less than), < > (not equal to), >=  (more than or equal to), <= (less than or equal to). The other operators are AND, OR, NOT, BETWEEN-AND, IN, and LIKE.

SELECT operation
Select is used to retrieve several data rows that meet the given predicate. The predicate refers to the conditions to be fulfilled in the selection operation. Some of the commands in the Select operation include 1. Give another name to the column

SELECT oldcolumnname AS newcolumnname FROM tablename; 

Examples:

SELECT type AS book_title FROM master_book;

Use aliases for table names

SELECT aliasname.columnname1, aliasname.columnname2 FROM tablenamealiasname;

Examples:

SELECT B.book_title, B.author FROM master_book B;

Display data from more than two tables

SELECT * FROM tablename1, tablename2, tablename-n; 

Examples:

SELECT * FROM master_book;

Query Using Multiple Conditional Parameters

We can select data using several combinations of conditional parameters associated with AND or OR statements. The AND statement can also be written as ‘&&’, while the OR statement can also be written as ‘||’. The AND statement has higher precedence than the OR statement.

Read also : MySQL

Read also : MySQL Query

Examples:

SELECT first_name, last_name, salary, city FROM employee WHERE city='New York' AND salary > 4000;

Text Type Data Query with Pattern Matching

Pattern matching can be used to select text-type data with specific characteristics. The commands used for matching are LIKE and NOT LIKE. Here are some of the pattern-matching facilities provided by MySQL.

Symbol	Function
_	Match any single character
%	Match an arbitrary number of character (including non  character)

Example 1:

SELECT CONCAT(first_name," ",last_name)
FROM employees
WHERE first_name LIKE 'J_ _ _ _';

The example above shows how to choose an employee whose first name starts with the letter ‘J’ and is followed by exactly four characters.

Usage example 2:

SELECT CONCAT(first_name," ",last_name)
FROM employees
WHERE first_name NOT LIKE '%n';

The example above shows how to select employees whose first name does not end with the character ‘n’.

Query Unique Data Using DISTINCT

They eliminate duplication of the data sought to obtain unique data (appears only once).
Usage examples:

SELECT DISTINCT city FROM employee;

In the example above, when the DISTINCT statement is not used, all city names from the employee table will be displayed. There will be a description that comes out more than once, for example, the city “New York”.

Limit Query Results with LIMIT

We can limit the data generated from the queries we enter using the LIMIT statement.
Example 1:

SELECT * FROM employee LIMIT 5;

In the example above, we limit the amount of data displayed, namely, 5 data records.
Example 2:

SELECT * FROM employee LIMIT 2,3;

In the example above, we limit the data displayed from the 2nd to the 4th (3 data in total). The thing to note is that the data sequence starts from 0th.

Read also : Fundamentals of Object Oriented Programming

Grouping Query Results Using GROUP BY

Query results can be grouped by field/column using the GROUP BY statement.
Examples:

SELECT * FROM employee GROUP BY city;

The example above shows a grouping by ‘city’. The query results will show that the data displayed is the first for each ‘city’ group. The thing to note is that the data displayed is sorted based on the data in the ‘city’ column.

Getting the Number of Members of Each Group Using COUNT()

Query using GROUP BY only shows the first data that appears. If we want to get the number of members for each group, we can use the COUNT() function.
Example 1:

SELECT city, COUNT() FROM employee GROUP BY city; 

The query results above show the number of employees in each city. We can also do a GROUP BY combination with the condition parameters as follows:

SELECT city, COUNT() FROM employee WHERE description='Programmer' GROUP BY city;

The query results above show the number of “Programmers” in each city.

Conditional Parameters with HAVING

The HAVING statement is a conditional parameter like WHERE which acts as a secondary delimiter of the query results. The HAVING statement is usually used as a secondary delimiter after the GROUP BY statement, although it can also be used without using GROUP BY.
Example 1:

SELECT first_name, last_name, salary FROM employee HAVING  salary > 3000;

The query results above show that the HAVING parameter can be used like the WHERE parameter.
Example 2:

SELECT city, COUNT(), salary FROM employee WHERE salary  > 3000 GROUP BY city; SELECT city, COUNT(), salary FROM employee GROUP BY city HAVING salary > 3000;

The query results above show the difference in the order in which filtering is executed, so different results are obtained. Query (a) selects salary> 3000 before grouping by city. Query (b) categorizes the towns first. This grouping causes only the first data for each group to be selected. When executing the HAVING parameter, the Query will only display the first data for each city group with a salary> 3000.

Sort Query Results Using ORDER BY

We can sort the query results based on a specific field/column using ORDER BY. We can add ASC and DESC statements to get ascending or descending order.
Examples:

SELECT CONCAT(first_name,” “,last_name) AS name FROM employee ORDER BY name;
SELECT CONCAT(first_name,” “,last_name) AS name FROM employee ORDER BY name DESC;

Query (a) sorts employees by name. By default, ORDER BY uses ascending order. We can use DESC to get descending order as shown by Query (b).

Sort Query Results Based on More Than One Column

Sorting query results can be done based on more than one column. The sequence statements (ASC and DESC) are attached to the preceding column.

Examples:

SELECT first_name, last_name, city FROM employee ORDER BY first_name, city;

The query example above shows sorting by first_name first before sorting by city. If there is data with the same first_name, then the data will be sorted by city.

Operator BETWEEN

The BETWEEN operator filters data that has a value between the two specified values. Meanwhile, the NOT BETWEEN operator is the negation of BETWEEN, which is used to filter data that has no significance between the two specified values
Examples:

SELECT first_name, last_name, salary FROM employee WHERE  salary BETWEEN 1000 AND 3000;

The query results above show a list of employees with an income between 1000 and 3000. The BETWEEN operator value1 AND value2 has an equivalent value with value1 ≤ data < value2. The BETWEEN operator can also filter alphanumeric values (letter characters).

Sub-Query

A sub-query is a SELECT statement inside a SELECT statement. A sub-query is declared inside brackets (). The statements outside the sub-query statements are either SELECT, INSERT, UPDATE, DELETE, SET or DO statements. Examples:

SELECT first_name, last_name, salary FROM employee WHERE  salary = (SELECT MAX(salary) FROM employee);

The link operator ‘=’ can only be used to receive one value, so when the result of a sub-query contains more than one data, an error message will occur.

Sub-queries with ALL

The ALL command is followed by a comparison operator and has the meaning Displays a value if the comparison is true for all data. Examples:

SELECT first_name, last_name, salary FROM employee WHERE  salary > ALL(SELECT salary FROM employee WHERE  salary<2000);

The query above produces a list of employees with a salary greater than all employees with a salary <2000.

Sub-queries with ANY

The ANY command is followed by a comparison operator, which means. Returns the value that matches whatever is produced by the sub-query. The alias of ANY is SOME.
Examples:

SELECT first_name, last_name, salary FROM employee WHERE  salary > ANY(SELECT salary FROM employee WHERE  salary<2000);

The results of the example sub-query above are all employees whose salary is less than 2000. The overall effect displays all employees whose salary is greater than the results of the sub-query. For example, in the entire employee data, if there is employee A with the lowest pay, namely 100, then employee A will not be displayed because there is no other salary smaller than A.

Sub-queries with EXISTS

A sub-query with EXISTS returns TRUE if it has a result. If the sub-query is TRUE, then the main query will be executed. The country of the EXISTS sub-query is NOT EXISTS.
Examples:

SELECT first_name, last_name, city FROM employee WHERE EXISTS(SELECT * FROM employee WHERE city='Toronto');

Sub-queries with IN

As mentioned before, the ‘=’ operator can only be used when the result of the sub-query has precisely one consequence. If the results of the sub-query amount to more than one data, then we can use the IN command.
Examples:

SELECT first_name, last_name, description FROM employee WHERE description IN(SELECT title FROM job);