MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminBuilt in functions in MySQL
String Functions
String functions can manipulate a text string LEFT(string,length) – It is used to get the specified portion of the string from left side. For example – below query will show only first 3 letters of the name.
SELECT LEFT(FirstName,3) Name FROM CUSTOMER;
RIGHT(string,length) – It is used to get the specified portion of the string from right side. For example – below query will show only last 3 letters of the name.
SELECT RIGHT(FirstName,3) Name FROM CUSTOMER;
MID(string,start_position,length) – It is used to get the specified portion of the string from any position. For example – below query will show 3 letters of the name starting at position 2.
SELECT MID(FirstName,2,3) Name FROM CUSTOMER;
LENGTH(string) – It is used to get the length of the string. For example – below query will show length of the first name of the customer.
SELECT LENGTH(FirstName) NameLength FROM CUSTOMER;
LCASE(string) – It is used to convert the string to lower case.
SELECT LCASE(FirstName) Name FROM CUSTOMER;
UCASE(string) – It is used to convert the string to upper case.
SELECT UCASE(FirstName) Name FROM CUSTOMER;
REVERSE(string) – It is used to reverse the string.
SELECT REVERSE(FirstName) Name FROM CUSTOMER;
SUBSTRING(string,position) – It is used to get the SUBSTRING of the string from starting from specific position. For example – below query will show substring starting at position 2.
SELECT SUBSTRING(FirstName,2) Name FROM CUSTOMER;
CONCAT(string1,string2,…) – It is used to concatinate the strings together in one string.
SELECT CONCAT(FirstName, ” ” , LastName) Name FROM CUSTOMER;
REPLACE(whole_string,to_be_replaced,replacement) – It is used to replace the portion of the string. For example – below query will replace Sh by sa in name. Replacement is case sensitive.
SELECT REPLACE(FirstName,”Sh”,”sa”) Name FROM CUSTOMER;
INSERT(string,start_position,lengthToBeReplaced,newstring) – It is used to insert the substring in another string at specific position. For example – below query will insert Brett in name starting at position 2 and one letter from original string will be removed at position 2 as well.
SELECT INSERT(FirstName,2,1,”Brett”) Name FROM CUSTOMER;
LOCATE(substring,string) – It is used to find the substring in another string. If the substring is found, it’s starting position is returned. If substring is not found, 0 is returned. For example – below query will find the Sh in First Name.
SELECT LOCATE(“Sh”,FirstName) Position FROM CUSTOMER;
Numeric Functions
Numeric functions are used to manipulate numbers FlOOR function returns the nearest integer number that is less than given number SELECT FLOOR(number)
SELECT FLOOR(3.1);
-- Output will be 3.
CEILING function returns the nearest integer number that is larger than given number. SELECT CEILING(number)
SELECT CEILING(4.8);
-- Output will be 5.
ROUND function rounds the number upto given decimals. By default, it rounds the number to 0 decimal places. ROUND(number,[Decimal Places])
SELECT ROUND(12.812,2);
-- Output will be 12.81
SELECT ROUND(12.816,2);
-- Output will be 12.82
TRUNCATE function simply truncates the number upto given decimals. TRUNCATE(number,decimal places)
SELECT TRUNCATE(12.812,1);
Summarizing Functions
COUNT function returns the total number of records.
COUNT(ColumnName)
select count(*) from CUSTOMER.
-- It will print total number of records from the table CUSTOMER.
AVG function returns the average of all values in a given column.
SELECT AVG(ColumnName)
MIN function returns the minimum of all values in a given column.
MIN(ColumnName)
MAX function returns the maximum of all values in a given column.
MAX(ColumnName)
SUM function returns the sum of all values in a given column.
SUM(ColumnName)
Functions to manage Null values
COALESCE Function This function returns first Non-Null value from given parameters. If there is no non-null value, it returns null.
SELECT COALESCE(NULL, NULL, 5);
-- It will return 5.
SELECT COALESCE(NULL, NULL, NULL, NULL);
-- It will return NULL.
IFNULL function If first expression is null, it returns second expression. If first expression is not null, it returns first expression. IFNULL(original_value, new_value)
SELECT IFNULL(1,4);
-- It will return 1.
SELECT IFNULL(NULL,2);
-- It will return 2.
Date and Time Functions CURDATE method returns current date.
SELECT CURDATE();
CURTIME method returns current time.
SELECT CURTIME();
NOW method returns current date as well as time.
SELECT NOW();
DAYOFMONTH(date) – Returns numeric value of day (1-31)
SELECT DAYOFMONTH(STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’));
-- Output will be 09.
DAYOFYEAR(date) – Returns numeric value of day in a year (1-365)
SELECT DAYOFYEAR(STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’));
-- Output will be 09.
MONTH(date) – Returns numeric value of month (1-12)
SELECT MONTH(STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’));
-- Output will be 01.
- DAYNAME(date) – Returns the name of weekday (sunday, monday etc.)
- MONTHNAME(date) – Returns the name of the month (January, Feb etc)
- YEAR(date) – Returns Year in 4 digits
- HOUR(time) – returns hour in 24 hour format
- MINUTE(time) – returns Minutes portion of the time
- SECOND(time) – returns seconds portion of the time
- DATE_FORMAT() – It is used to format the DATE, DATETIME and TIMESTAMP.
DATE_FORMAT(NOW(),’%d-%m-%Y’)
To get the date in dd-mmm-yyyy format, use below syntax
DATE_FORMAT(NOW(),’%d-%b-%Y’)
-- hour:min:sec format
DATE_FORMAT(NOW(),’%h:%i:%s %p’)
To get the date in 24 hour format, use below syntax
DATE_FORMAT(NOW(),’%T’)
TIME_FORMAT() – It is used to format the TIME, DATETIME and TIMESTAMP.
TIME_FORMAT(NOW(),’%h:%i:%s %p’)
DATE_ADD – It is used to add the interval to given date
DATE_ADD(date,INTERVAL expr type)
SELECT DATE_ADD(now(), INTERVAL 5 DAY)
SELECT DATE_ADD(now(), INTERVAL 5 HOUR)
SELECT DATE_ADD(now(), INTERVAL 5 MONTH)
SELECT DATE_ADD(now(), INTERVAL 5 YEAR)
DATE_SUB(date,INTERVAL expr type) – It is used to subtract the interval from given date
SELECT DATE_SUB(now(), INTERVAL 5 MINUTE)
SELECT DATE_SUB(now(), INTERVAL 5 WEEK)
SELECT DATE_SUB(now(), INTERVAL 5 QUARTER)
Web development and Automation testing
solutions delivered!!