Data
Manipulation
DIT201 Database System
Data Manipulation Language (DML)
DML is a subset of SQL that focuses on the
manipulation of data stored in a Relational Database
Management System (RDBMS). DML commands are used
to perform various operations on the data within the
database, including querying, inserting, updating, and
deleting records. DML commands allow users and
applications to interact with the data and modify it
according to their needs.
2
SELECT statement
SELECT DataName, DataName, [DISTINCT DataName ], [ function(DataName) ]
FROM TableName
[WHERE DataName Condition Value [AND/OR DataName Condition DataValue] ]
[GROUP BY DataName ]
[ORDER BY DataName [ASC/DESC] ]
[LIMIT number] ;
SELECT * : Select all DataName
[] : Optional statement
3
Start Database Front-End and Database
Management System
1 2
4
Download and Import Data
1 Import Result
Select the file
from which there
was a previous
backup.
.
.
3
5
1
6
7
8
2
9
3 Write your SQL statement
and run with Ctrl+Enter
10
11
12
Calculations statement in query language
SELECT DataName, DataName as DisplayName, (DataName operator DataName) as
DisplayName
FROM TableName ;
DataName as DisplayName
Change DataName to DisplayName.
(DataName operator DataName) as
DisplayName
operator : + - * / %
DataType must be a number for calculation.
as DisplayName stores the result of the calculation 13
14
15
16
MySQL functions
MySQL functions are built-in functions to manipulate
and operate on data stored in the database.
https://www.w3schools.com/sql/sql_ref_mysql.asp 17
MIN/MAX Function
SELECT MAX(DataName) MAX(DataName)
FROM TableName ;
DataType must be a number.
18
SUBSTR() Function
SELECT DataName, SUBSTR(DataName, start, length)
FROM TableName ;
19
DATE_FORMAT() Function
SELECT DataName, DATE_FORMAT(DataName, "%d/%M/%Y")
FROM TableName ;
20
NOW() Function : Return current date and time
SELECT NOW() ;
21
DATEDIFF() Function : Return the number of days between two date
SELECT DataName, DATEDIFF( NOW(), DateTimeData )
FROM TableName ;
22
TIMESTAMPDIFF() Function : Return the number of years between two
SELECT
date DataName, TIMESTAMPDIFF(YEAR, DateTimeData , NOW())
FROM TableName ;
23
ADDDATE() Function : Return the number of years between two date
SELECT DataName, ADDDATE(DataName, INTERVAL Number DAY/YEAR )
FROM TableName ;
24
Function statement in query language
25
Condition Operators https://
www.w3schools.com/
SELECT *
FROM TableName
WHERE DataName Condition Value ;
26
Condition Operators https://
www.w3schools.com/
SELECT *
FROM TableName
WHERE DataName Condition Value ;
27
LIKE operator https://
www.w3schools.com/
SELECT *
FROM TableName
WHERE DataName LIKE Pattern ;
28
Combining the AND and OR Conditions
SELECT *
FROM TableName
[WHERE DataName Condition Value AND DataName Condition Value ;
29
Combining the AND and OR Conditions
SELECT *
FROM TableName
[WHERE DataName Condition Value OR DataName Condition Value ;
30
Condition statement in query language
31
ORDER BY statement
SELECT *
FROM TableName
ORDER BY DataName ;
32
LIMIT statement
SELECT *
FROM TableName
LIMIT number ;
33
Limit statement in query language
34
GROUP BY
SELECT DataName, DataName SELECT *
FROM TableName FROM TableName
GROUP BY DataName ; GROUP BY DataName ;
35
GROUP BY with COUNT() function
SELECT DataName , COUNT(DataName)
FROM TableName
GROUP BY DataName ;
36
GROUP BY with SUM() function
SELECT DataName , SUM(DataName) SUM(DataName)
FROM TableName
GROUP BY DataName ; DataType must be a number.
37
GROUP BY with AVG() function
SELECT DataName , AVG(DataName) AVG(DataName)
FROM TableName
GROUP BY DataName ; DataType must be a number.
38
Group By statement in query language
39
End.
DIT201
