[go: up one dir, main page]
Scribd
Upload
6 views20 pages

FDB Lecture06

Uploaded by

jsherzad733
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views20 pages

FDB Lecture06

Uploaded by

jsherzad733
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 20

Jawzjan University

Computer Learning Department

Fundamentals of
Database
Prepared by: Khudaydad MAHMOODI
Overview
 MySQL Functions
 String Functions
 Date Functions
 Math Functions
String Functions
Name Description
CONCAT Concatenate two or more strings into a single string
FORMAT Format a number with a specific locale, rounded to the number of decimals
LENGTH Get the length of a string in bytes and in characters
REPLACE Search and replace a substring in a string
SUBSTRING Extract a substring starting from a position with a specific length
LEFT Get a specified number of leftmost characters from a string
RIGHT Get a specified number of rightmost characters from a string
TRIM Remove unwanted characters from a string
LTRIM Remove all leading spaces from a string
RTRIM Remove all trailing spaces from a string
LOWER Convert a string to lowercase
UPPER Convert a string to uppercase
String Functions – CONCAT()
Concatenate two or more strings into a single string.
Example:
SELECT CONCAT('MySQL', ' ', 'Database');

MySQL Database
String Functions – FORMAT()
Format a number with a specific locale, rounded to the number of
decimals
Example:
SELECT FORMAT(12500.2015, 2);

12,500.20

Example:
SELECT FORMAT(12500.2015, 0);

12,500
String Functions – LENGTH()
Get the length of a string in bytes and in characters.
Example:
SELECT LENGTH('MySQL');

5
String Functions – REPLACE()
Search and replace a substring in a string.
Example:
SELECT REPLACE('www.mysql.com', 'www', 'https://www');

https://www.mysql.com
String Functions – SUBSTRING()
Search and replace a substring in a string.
Example:
SELECT SUBSTRING('MySQL Database', 7);

Database

SELECT SUBSTRING('MySQL Database', -8);

Database

SELECT SUBSTRING('MySQL Database', 3, 3);

SQL
String Functions – LEFT()
Get a specified number of leftmost characters from a string.
Example:
SELECT LEFT('MySQL Database', 5);

MySQL
String Functions – RIGHT()
Get a specified number of rightmost characters from a string.
Example:
SELECT RIGHT('MySQL Database', 8);

Database
String Functions – TRIM()
Removes both leading and trailing spaces from a string.
Example:
SELECT TRIM(' MySQL ');

'MySQL'

SELECT TRIM(LEADING FROM ' MySQL ');

'MySQL '

SELECT TRIM(TRAILING FROM ' MySQL ');

' MySQL'
String Functions – LTRIM()
Remove all leading spaces from a string.
Example:
SELECT LTRIM(' MySQL Database');

'MySQL Database'
String Functions – RTRIM()
Remove all trailing spaces from a string.
Example:
SELECT RTRIM('MySQL Database ');

'MySQL Database'
String Functions – LOWER()
Convert a string to lowercase.
Example:
SELECT LOWER('MySQL');

mysql
String Functions – UPPER()
Convert a string to uppercase.
Example:
SELECT UPPER('MySQL');

MYSQL
Date Functions
Name Description
NOW() Returns the current date and time.
CURDATE() Returns the current date.
CURTIME() Returns the current time.
DATE() Extracts the date part of a date or date/time expression.
EXTRACT() Returns a single part of a date/time.
DATE_ADD() Adds a specified time interval to a date.
DATE_SUB() Subtracts a specified time interval from a date.
DATEDIFF() Returns the number of days between two dates.
DATE_FORMAT() Displays date/time data in different formats.
Date Functions – NOW()
Returns the current date and time.
Example:
SELECT NOW();

2019-05-19 23:02:35
Date Functions – CURDATE()
Returns the current date.
Example:
SELECT CURDATE();

2019-05-19
Date Functions – CURTIME()
Returns the current time.
Example:
SELECT CURTIME();

23:02:35
Math Functions
ABS() Returns the absolute value of a number.
CEIL() Returns the smallest integer value greater than or equal to the input number.
FLOOR() Returns the largest integer value not greater than the argument.
MOD() Returns the remainder of a number divided by another.
ROUND() Rounds a number to a specified number of decimal places.
TRUNCATE() Truncates a number to a specified number of decimal places.
POW() Returns the argument raised to the specified power.
SQRT(n) Returns the square root of n.
SIN(n) Returns the sine of n.
COS(n) Returns the cosine of n, where n is in radians.
TAN(n) Returns the tangent of n.

You might also like