Built in Functions -MySQL

MySQL built-in functions are pre-defined functions provided by MySQL to perform various operations on data. These functions are categorized into different types based on their functionality:

String Functions

  • CONCAT(): Concatenates two or more strings.
  • SUBSTRING(): Extracts a substring from a string.
  • LENGTH(): Returns the length of a string.
  • UPPER(): Converts a string to uppercase.
  • LOWER(): Converts a string to lowercase.
  • TRIM(): Removes leading and trailing spaces from a string.
  • REPLACE(): Replaces occurrences of a specified string with another string.

Numeric Functions

  • ABS(): Returns the absolute value of a number.
  • CEIL() or CEILING(): Rounds a number up to the nearest integer.
  • FLOOR(): Rounds a number down to the nearest integer.
  • ROUND(): Rounds a number to a specified number of decimal places.
  • MOD(): Returns the remainder of a division operation.
  • POWER(): Raises a number to the power of another number.

Date and Time Functions

  • CURDATE(): Returns the current date.
  • CURTIME(): Returns the current time.
  • NOW(): Returns the current date and time.
  • DATE(): Extracts the date part of a date or datetime expression.
  • TIME(): Extracts the time part of a date or datetime expression.
  • YEAR(): Returns the year from a date.
  • MONTH(): Returns the month from a date.
  • DAY(): Returns the day of the month from a date.
  • DATE_ADD(): Adds a specified time interval to a date.
  • DATEDIFF(): Returns the difference in days between two dates.

Aggregate Functions

  • COUNT(): Returns the number of rows that match a specified condition.
  • SUM(): Returns the sum of a numeric column.
  • AVG(): Returns the average value of a numeric column.
  • MIN(): Returns the minimum value in a set.
  • MAX(): Returns the maximum value in a set.

Control Flow Functions

  • IF(): Returns one value if a condition is true, and another value if it is false.
  • CASE: Performs conditional logic similar to a switch statement.

JSON Functions

  • JSON_OBJECT(): Creates a JSON object from a list of key-value pairs.
  • JSON_ARRAY(): Creates a JSON array from a list of values.
  • JSON_EXTRACT(): Extracts data from a JSON document.

Full-Text Search Functions

  • MATCH()...AGAINST(): Performs full-text search operations.

Miscellaneous Functions

  • VERSION(): Returns the MySQL version.
  • DATABASE(): Returns the name of the current database.
  • USER(): Returns the current user name and host name.

These built-in functions are essential tools in MySQL for data manipulation, calculation, and retrieval. For a comprehensive list and detailed descriptions, you can refer to the MySQL documentation.

Example

SELECT CONCAT('Hello', ' ', 'World');

+---------+
| bin(12) |
+---------+
| 1100    |
+---------+

SELECT SUBSTRING('Hello World', 1, 5);

+-------------------------------+
| CONCAT('Hello', ' ', 'World') |
+-------------------------------+
| Hello World                   |
+-------------------------------+

SELECT LENGTH('Hello');

+-----------------+
| LENGTH('Hello') |
+-----------------+
|               5 |
+-----------------+

SELECT REPLACE('hello','o','p')

+--------------------------+
| REPLACE('hello','o','p') |
+--------------------------+
| hellp                    |
+--------------------------+
SELECT BIN(12)  ;
+---------+
| bin(12) |
+---------+
| 1100    |
+---------+

SELECT ABS(-10);
+----------+
| ABS(-10) |
+----------+
|       10 |
+----------+

SELECT CEIL(1.5);
+-----------+
| CEIL(1.5) |
+-----------+
|         2 |
+-----------+

SELECT FLOOR(1.5);
+------------+
| FLOOR(1.5) |
+------------+
|          1 |
+------------+

SELECT CURDATE(); 

+------------+
| CURDATE()  |
+------------+
| 2024-07-10 |
+------------+

SELECT NOW(); 
+---------------------+
| NOW()               |
+---------------------+
| 2024-07-10 10:43:31 |
+---------------------+

SELECT DATE_ADD('2020-01-01', INTERVAL 1 DAY); 

+----------------------------------------+
| DATE_ADD('2020-01-01', INTERVAL 1 DAY) |
+----------------------------------------+
| 2020-01-02                             |
+----------------------------------------+
SELECT DAY('2024-07-10'); 
+-------------------+
| DAY('2024-07-10') |
+-------------------+
|                10 |
+-------------------+

SELECT MONTH('2024-07-10'); 
+---------------------+
| MONTH('2024-07-10') |
+---------------------+
|                   7 |
+---------------------+

SELECT YEAR('2024-07-10');

+--------------------+
|YEAR('2024-07-10')  |
+--------------------+
|               2024 |
+--------------------+

SELECT IF(1 < 2, 'True', 'False');
+----------------------------+
| IF(1 < 2, 'True', 'False') |
+----------------------------+
| True                       |
+----------------------------+

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE
);

INSERT INTO orders (order_date) VALUES
('2024-01-15'),
('2024-05-22'),
('2024-07-10'),
('2024-12-30');
SELECT 
    order_id,
    order_date,
    CASE
        WHEN MONTH(order_date) BETWEEN 1 AND 3 THEN 'Winter'
        WHEN MONTH(order_date) BETWEEN 4 AND 6 THEN 'Spring'
        WHEN MONTH(order_date) BETWEEN 7 AND 9 THEN 'Summer'
        WHEN MONTH(order_date) BETWEEN 10 AND 12 THEN 'Fall'
        ELSE 'Unknown'
    END AS season
FROM 
    orders;

+----------+------------+--------+
| order_id | order_date | season |
+----------+------------+--------+
|        1 | 2024-01-15 | Winter |
|        2 | 2024-05-22 | Spring |
|        3 | 2024-07-10 | Summer |
|        4 | 2024-12-30 | Fall   |
+----------+------------+--------+

SELECT VERSION(); 
SELECT DATABASE(); 
SELECT USER()

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more