UNIT II OPEN SOURCE DATABASE

Introduction - Setting up an account - Starting, Terminating and writing your own

MySQL Programs - Record Selection Technology - Working with Strings - Date and
Time - Sorting Query Results module - Generating Summary- Working with Metadata
- Using Sequences - MySQL and Web.

Introduction – MySQL

MySQL is a very popular open-source relational database management system

(RDBMS).

What is MySQL?

 MySQL is a relational database management system

 MySQL is open-source
 MySQL is free
 MySQL is ideal for both small and large applications
 MySQL is very fast, reliable, scalable, and easy to use
 MySQL is cross-platform
 MySQL is compliant with the ANSI SQL standard
 MySQL was first released in 1995
 MySQL is developed, distributed, and supported by Oracle Corporation
 MySQL is named after co-founder Ulf Michael "Monty" Widenius's daughter:
My

1
Page
Who Uses MySQL?

 Huge websites like Facebook, Twitter, Airbnb, [Link], Uber, GitHub,

YouTube, etc.
 Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc.
 A very large number of web developers around the world

Key Features in MySQL

MySQL is a popular choice for managing relational databases for several reasons:
 Open-Source: MySQL is free and open-source, allowing modification and
distribution.
 High Performance: It offers fast data retrieval and processing for large datasets.
 ACID Compliance: Ensures data integrity and reliability, especially with
InnoDB storage.
 Scalability: Supports large databases and high traffic with features like
partitioning and clustering.
 Multiple Storage Engines: Offers different storage engines (e.g., InnoDB,
MyISAM) for flexible use.
 Replication: Supports master-slave replication for data redundancy and high
availability.
 Security Features: Provides user authentication, SSL encryption, and secure
data storage options.

Who Uses MySQL

MySQL is a widely-used relational database management system (RDBMS) that
caters to various user groups, from small businesses to large enterprises. Here's a
look at who uses MySQL:
 Small to Medium-Sized Businesses (SMBs): MySQL is popular among SMBs
due to its cost-effectiveness, ease of use, and flexibility. These businesses
leverage MySQL for managing their customer data, sales transactions, and other
operational databases.
 Large Enterprises: Many large organizations use MySQL for its scalability and
reliability. Companies like Facebook, Google, and Adobe rely on MySQL to
handle large-scale databases and high-traffic applications.
 Web Developers: MySQL is a favorite among web developers because it
integrates seamlessly with popular web development technologies such as PHP
and JavaScript. It powers many websites and web applications, from blogs to e-
commerce platforms.
 Educational Institutions: MySQL is frequently used in academic settings for
teaching database management and SQL skills. Its open-source nature makes it a
cost-effective choice for educational purposes.

Applications of MySQL
MySQL has used in various applications across a wide range of industries and
domains, because of to its versatility, reliability, and performance. Here are some
2
Page

common applications :
 E-commerce: MySQL is extensively used in e-commerce platforms for
managing product catalogs, customer data, orders, and transactions.
 Content Management Systems (CMS): Many popular CMS platforms rely on
MySQL as their backend database to store website content, user profiles,
comments, and configuration settings.
 Financial Services: MySQL is employed in financial applications, including
banking systems, payment processing platforms, and accounting software,
to manage transactional data, customer accounts, and financial records.
 Healthcare: MySQL is used in healthcare applications for storing and
managing patient records, medical histories, treatment plans, and diagnostic
information.

Difference Between MySQL and SQL

MySQL SQL

SQL (Structured Query Language) is

MySQL is a Relational Database
a standard language used for
Management System (RDBMS) that
communicating with relational
uses SQL (Structured Query Language).
databases.

It is open source and accessible to any and

It is not an open-source language.
everyone for free.

It supports basic programming languages It is in itself a Query language used

like C, C++, Python, Ruby, etc. for database systems.

It available only in the English language. It is available in different languages.

It doesn't support user-defined functions It supports user-defined functions and

and XML. XML.

Setting up an account

How to Install MySQL on Windows?

Hardware and Software Requirements to Install MySQL
 Operating System: Windows 10, 8, 7, or Windows Server 2016/2019.
 Hardware: At least 4 GB of RAM and 2 GB of free disk space (preferably more
3

for optimal performance).

Page
 Software: Windows must be up-to-date. Make sure to uninstall any older
versions of MySQL to avoid conflicts.
 Network: An active internet connection to download the MySQL installer.

Download and Install MySQL for Windows Steps:

Now, Let' 's break down MySQL software downloading steps for a better
understanding and see install MySQL on Windows 10 step by step.

Step 1: Visit the Official MySQL Website

Open your preferred web browser and navigate to the official MySQL website. Now,
Simple click on first download button.

Step 2: Go to the Downloads Section

On the MySQL homepage, Click on the " No thanks, just start my download" link
to proceed MySql downloading.

Step 3: Run the Installer

After MySQL downloading [Link] file , go to your Downloads folder, find the
file, and double-click to run the installer.
Step 4: Choose Setup Type
The installer will instruct you to choose the setup type. For most users, the
"Developer Default" is suitable. Click "Next" to proceed.
Step 5: Check Requirements
You might be prompted to install necessary MySQL software, typically Visual
Code. The installer can auto-resolve some issues, but not in this case.
Step 6: MySQL Downloading
Now that you're in the download section, click "Execute" to start downloading the
components you selected. Wait a few minutes until all items show tick marks,
indicating completion, before moving forward.
Step 7: MySqL Installation
Now the downloaded components will be installed. Click "Execute" to start the
installation process. MySQL will be installed on your Windows system. Then
click Next to proceed
Step 8: Navigate to Few Configuration Pages
Proceed to "Product Configuration" > "Type and Networking" >
"Authentication Method" Pages by clicking the "Next" button.
Step 9: Create MySQL Accounts
Create a password for the MySQL root user. Ensure it's strong and memorable. Click
"Next" to proceed.
Step 10: Connect To Server
Enter the root password, click Check. If it says "Connection succeed," you've
successfully connected to the server.
4
Page
Step 11: Complete Installation
Once the installation is complete, click "Finish." Congratulations! MySQL is now
installed on your Windows system.
Step 12: Verify Installation
To ensure a successful installation of MySQL, open the MySQL Command Line
Client or MySQL Workbench, both available in your Start Menu. Log in using the
root user credentials you set during installation.

Steps to Install MySQL on Linux:

The installation process of MySQL on Linux involves using the terminal and
running a series of commands. This articles covers the installation on Ubuntu and
other Debian-based distributions, but the steps are similar for other Linux
distributions as well.
Step 1: Update Your Package List
Before installing any software, it’s always a good idea to update your package list to
ensure you’re getting the latest version available from the repository.

sudo apt update

Step 2: Install MySQL Server

Now, let’s install the MySQL server using the following command. Open terminal
using Ctrl+Alt+T then copy and paste the following command in the terminal to
install MySQL in Linux.

sudo apt install mysql-server

Once you run the command, it will prompt you for your password. Enter it and
press Enter then Press "y" to continue.

It will take some time to download and install MySQL in Linux.

Step 3: Verify MySQL Installation

To verify that MySQL is installed correctly or to know the version enter the
following commands in your Terminal.

mysql --version

This confirms that MySQL is installed and ready to use.

Securing Your MySQL Installation
By default, MySQL is installed with some security vulnerabilities, so it's important
5

to secure your installation. MySQL provides a command to set up basic security

Page
settings, such as configuring a password for the root user and removing insecure
default settings.
Step 4: Run the MySQL Secure Installation Script
Now we will set the VALIDATE PASSWORD component to improve Security
sudo mysql_secure_installation

Then press "y" to set the password. Next press "0" for the low-level password or
choose as you want to set the password.

Create a password. Then Re-enter the password, then to continue press "y".

Now the whole setup is done. Hence, MySQL installaion is successfully done!
Step 5: Test the MySQL Server
Once the security configuration is complete, test whether MySQL is running
properly:

sudo systemctl status mysql

If MySQL is running correctly, you should see output indicating that the service is
active and running. If not, you can start the MySQL service using:

sudo systemctl start mysql

Getting Started with MySQL
Once MySQL is installed and secured, let’s log into the MySQL shell to start using
it.

Step 6: Log into MySQL

To get started with MySQL, type the following command to go to the root
directory.

sudo mysql -u root

You’ll be prompted to enter the root password you set earlier. Once entered
correctly, you’ll be logged into the MySQL shell.

Starting, Terminating and writing your own MySQL Programs

CREATE TABLE
Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
6

);
Page
PersonID LastName FirstName Address City

MySQL DROP TABLE Example

The following SQL statement drops the existing table "Shippers":

Example

DROP TABLE Shippers;

MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not
the table itself.

Syntax
TRUNCATE TABLE table_name;
ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database
systems don't allow deleting a column):

Example

ALTER TABLE Customers

DROP COLUMN Email;
ID LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

ALTER TABLE Persons

MODIFY COLUMN Date of Birth year;
MySQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the
accuracy and reliability of the data in the table. If there is any violation between the
constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a
7

column, and table level constraints apply to the whole table.

Page
The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 CREATE INDEX - Used to create and retrieve data from the database very
quickly
 DEFAULT - Sets a default value for a column if no value is specified

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
MySQL FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary
key is called the referenced or parent table.

Look at the following two tables:

Persons Table
PersonID LastName FirstName Age
1 Hansen Ola 30
8

2 Svendson Tove 23
Page

3 Pettersen Kari 20
Orders Table
OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2

Notice that the "PersonID" column in the "Orders" table points to the "PersonID"
column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons"
table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table.

DESCRIBE Statement

CREATE TABLE users (

id INT PRIMARY KEY,

name VARCHAR(50),

email VARCHAR(100),

age INT

);

DESC users;

Record Selection Technology

Page

The MySQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT * FROM table_name;

SELECT * FROM Customers;

Custom CustomerNa ContactNam Address City Post Count

erID me e alCo ry
de

1 Alfreds Maria Anders Obere Str. Berlin 1220 Germa

Futterkiste 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222

3 Antonio Antonio Mataderos México 0502 Mexico

Moreno Moreno 2312 D.F. 3
Taquería

4 Around the Thomas 120 London WA UK

Horn Hardy Hanover 1
Sq. 1DP

5 Berglunds Christina Berguvsvä Luleå S- Swede

snabbköp Berglund gen 8 958 n
22

SELECT CustomerName, City, Country FROM Customers;

CustomerName City Country

10

Alfreds Futterkiste Berlin Germany

Page
 Ana Trujillo México D.F. Mexico
Emparedados y helados

Antonio Moreno México D.F. Mexico

Taquería

Around the Horn London UK

Berglunds snabbköp Luleå Sweden

SELECT Country FROM Customers;

Country

Germany

Mexico

Mexico

UK

Sweden

SELECT DISTINCT Country FROM Customers;

SELECT COUNT(DISTINCT Country) FROM Customers;

5
11

SELECT * FROM Customers WHERE Country='Mexico';

Page
2 Ana Trujillo Ana Trujillo Avda. de la Méxi 05021 Mexico
Emparedados y Constitució co
helados n 2222 D.F.

SELECT * FROM Customers WHERE CustomerID = 1;

1 Alfreds Maria Obere Str. Berli 12209 Germa

Futterkiste Anders 57 n ny

SELECT * FROM Customers

WHERE Country = 'Germany' AND City = 'Berlin';

1 Alfreds Maria Obere Str. Berli 12209 Germa

Futterkiste Anders 57 n ny

SELECT * FROM Customers

WHERE City='Berlin' OR City='London';

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222

4 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

SELECT * FROM Customers

WHERE NOT Country='Germany';

12

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució
Page
 helados n 2222 D.F. 1

3 Antonio Moreno Antonio Mataderos México 0502 Mexico

Taquería Moreno 2312 D.F. 3

4 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

5 Berglunds Christina Berguvsvä Luleå S- Swede

snabbköp Berglund gen 8 958 n
22

SELECT * FROM Customers

WHERE Country='Germany' AND (City='Berlin' OR City='London');

SELECT * FROM Customers

WHERE NOT Country='Mexico' AND NOT Country='UK';

1 Alfreds Maria Obere Str. Berlin 1220 Germa

Futterkiste Anders 57 9 ny

5 Berglunds Christina Berguvsvä Luleå S- Swede

snabbköp Berglund gen 8 958 n
22

SELECT * FROM Customers

ORDER BY Country DESC;

Custom CustomerName ContactNa Address City Post Count

erID me alCo ry
13

de
Page
1 Alfreds Maria Obere Str. Berlin 1220 Germa
Futterkiste Anders 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222

3 Antonio Moreno Antonio Mataderos México 0502 Mexico

Taquería Moreno 2312 D.F. 3

4 Berglunds Christina Berguvsvä Luleå S- Swede

snabbköp Berglund gen 8 958 n
22

5 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address,

City,PostalCode,Country)VALUES (6,'Cardinal', '[Link]', 'Skagen21', 'Stava
nger', '4006', 'Norway');

Custom CustomerName ContactNa Address City Post Count

erID me alCo ry
de

1 Alfreds Maria Obere Str. Berlin 1220 Germa

Futterkiste Anders 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222
14

3 Antonio Moreno Antonio Mataderos México 0502 Mexico

Page
 Taquería Moreno 2312 D.F. 3

4 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

5 Berglunds Christina Berguvsvä Luleå S- Swede

snabbköp Berglund gen 8 958 n
22

6, 'Cardinal', '[Link] 'Skagen21', 'Stavanger' 4006 'Norwa

hsen', , ,' ' y'

SELECT CustomerName,ContactName,Address
FROM Customers
WHERE Address IS NULL;

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

1 Alfred Schmidt Maria Obere Str. Frankfurt 1220 Germa

Anders 57 9 ny

SELECT * FROM Customers LIMIT 2;

1 Alfreds Maria Obere Str. Berlin 1220 Germa

Futterkiste Anders 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222
15
Page
SELECT COUNT(CustomerID)

FROM CUSTOMERS;

Produc ProductName SupplierID ORDER Unit Price

tID

1 Chais 1 100 10 boxes x 20 18

bags

2 Chang 1 50 24 - 12 oz 19
bottles

3 Aniseed Syrup 1 200 12 - 550 ml 10

bottles

4 Chef Anton's 2 300 48 - 6 oz jars 22

Cajun Seasoning

SELECT AVG(Price)
FROM Products;

162.5

SELECT SUM()
FROM Products;

650

SELECT * FROM Customers

WHERE CustomerName LIKE 'a%';

16
Page
3 Antonio Moreno Antonio Mataderos México 0502 Mexico
Taquería Moreno 2312 D.F. 3

1 Alfreds Maria Obere Str. Berlin 1220 Germa

Futterkiste Anders 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222

3 Antonio Moreno Antonio Mataderos México 0502 Mexico

Taquería Moreno 2312 D.F. 3

4 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

SELECT * FROM Customers

WHERE CustomerName LIKE '%a';

SELECT * FROM Customers

WHERE CustomerName LIKE '_r%';

4 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

SELECT * FROM Customers

17

WHERE CustomerName LIKE 'a%s';

Page
2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico
Emparedados y Constitució D.F. 1
helados n 2222

SELECT * FROM Customers

WHERE Country IN ('Germany', 'Mexico', 'UK');

1 Alfreds Maria Obere Str. Berlin 1220 Germa

Futterkiste Anders 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222

3 Antonio Moreno Antonio Mataderos México 0502 Mexico

Taquería Moreno 2312 D.F. 3

4 Around the Horn Thomas 120 London WA UK

Hardy Hanover 1
Sq. 1DP

SELECT * FROM Products

WHERE Order BETWEEN 100 AND 200;

1 Chais 1 100 10 boxes x 20 18

bags

2 Chang 1 50 24 - 12 oz 19
bottles

3 Aniseed Syrup 1 200 12 - 550 ml 10

18

bottles
Page
SELECT * FROM Products
WHERE Order BETWEEN 100 AND 200

AND SupplierID NOT IN (2);

1 Chais 1 100 10 boxes x 20 18

bags

2 Chang 1 50 24 - 12 oz 19
bottles

3 Aniseed Syrup 1 200 12 - 550 ml 10

bottles

SELECT ContactName AS Name, CustomerName AS Customer

FROM Customers;

Custom Customer Name Address City Post Count

erID alCo ry
de

1 Alfreds Maria Obere Str. Berlin 1220 Germa

Futterkiste Anders 57 9 ny

2 Ana Trujillo Ana Trujillo Avda. de la México 0502 Mexico

Emparedados y Constitució D.F. 1
helados n 2222

Working with Strings

MySQL - String Functions
MySQL is one of the most widespread relational database management
systems that covers many functions to manipulate the strings precisely. Processing
text data in MySQL can go from a simple concatenation to pattern matching/substring
19

extraction.
Page
Function Description

CONCAT_WS() It Concatenates strings with a specified separator.

CONCAT() It Concatenates two or more strings.

CHARACTER_LENGTH() It Returns the number of characters in a string.

It Returns the string at the specified index from a list

ELT() of strings.

It Returns a string where each bit of a bitmap value

EXPORT_SET() corresponds to a value in a set.

It Returns the index (position) of a string in a list of

FIELD() strings.

It Returns the position of a string within a comma-

FIND_IN_SET() separated list of strings.

It Formats a number to a format like '#,###,###.##',

FORMAT() rounded to a specified number of decimal places.

FROM_BASE64() It Decodes a base64-encoded string.

It Returns a string representation of a hexadecimal

HEX() value.

It Inserts a substring into a string at a specified

INSERT() position and for a certain number of characters

It Returns the position of the first occurrence of a

INSTR() substring in a string.

The LENGTH() function returns the length of a

LENGTH() string in bytes.
20

The LIKE() function is used for pattern matching in

LIKE() SQL queries. It allows you to search for a specified
Page
Function Description

pattern in a string.

The LOAD_FILE() function reads the content of a

LOAD_FILE() file on the server and returns it as a string.

The LOCATE() function returns the position of the

LOCATE() first occurrence of a substring within a string.

The LOWER() function converts all characters in a

LOWER() string to lowercase.

The LPAD() function pads a string to the left with

LPAD() another string to a certain length.

1. CONCAT_WS()
SELECT CONCAT_WS(', ', 'apple', 'banana', 'orange') AS Concatenated_String;
Output:
+----------------------+
| Concatenated_String |
+----------------------+
| apple, banana, orange |
+----------------------+

2. CONCAT()
SELECT CONCAT('Hello', ' ', 'World') AS Concatenated_String;
Output:
+-------------------+
| Concatenated_String |
+-------------------+
| Hello World |
+-------------------+

3. CHARACTER_LENGTH()
SELECT CHARACTER_LENGTH('Hello World') AS String_Length;
Output:
+--------------+
| String_Length |
+--------------+
21

| 11 |
Page

+--------------+
4. ELT()
SELECT ELT(3, 'apple', 'banana', 'orange', 'grape') AS Selected_String;
Output:
+----------------+
| Selected_String |
+----------------+
| orange |
+----------------+

5. EXPORT_SET()
SELECT EXPORT_SET(5, 2, '0', ',', '1') AS Binary_Set;
Output:
+------------+
| Binary_Set |
+------------+
| 1,0,1 |
+------------+

6. FIELD()
SELECT FIELD('banana', 'apple', 'banana', 'orange') AS Position;
Output:
+----------+
| Position |
+----------+
|2 |
+----------+

7. FIND_IN_SET()
SELECT FIND_IN_SET('banana', 'apple,banana,orange') AS Position;
Output:
+----------+
| Position |
+----------+
|2 |
+----------+

8. FORMAT()
SELECT FORMAT(1234567.89, 2) AS Formatted_Number;
Output:
+-------------------+
| Formatted_Number |
+-------------------+
| 1,234,567.89 |
22

+-------------------+
Page
9. POSITION()
SELECT POSITION('bar' IN 'foobarbar') AS Position;
Output:
+----------+
| Position |
+----------+
| 4|
+----------+

10. QUOTE()
SELECT QUOTE('It\'s a beautiful day!') AS Quoted_String;
Output:
+--------------------------+
| Quoted_String |
+--------------------------+
| 'It\'s a beautiful day!' |
+--------------------------+

11. REPLACE()
SELECT REPLACE('Hello World', 'World', 'Universe') AS Modified_String;
Output:
+------------------+
| Modified_String |
+------------------+
| Hello Universe |
+------------------+

12. REPEAT()
SELECT REPEAT('Hello ', 3) AS Repeated_String;
Output:
+------------------+
| Repeated_String |
+------------------+
| Hello Hello Hello |

13. RPAD()
SELECT RPAD('apple', 10, '*') AS Padded_String;
Output:
+---------------+
| Padded_String |
+---------------+
| apple***** |
+---------------+
23

14. RTRIM()
Page

SELECT RTRIM(' Hello World ') AS Trimmed_String;

Output:
+----------------+
| Trimmed_String |
+----------------+
| Hello World |
+----------------+

15.MAKE_SET()
SELECT MAKE_SET(1, 'a', 'b', 'c') AS Set_Values;
Output:
+------------+
| Set_Values |
+------------+
|a |
+------------+

Date and Time Functions

Name Description
Add time values (intervals) to a
ADDDATE() date value
ADDTIME() Add time
Convert from one time zone to
CONVERT_TZ() another
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMEST
AMP Synonyms for NOW()
CURTIME() Return the current time
Extract the date part of a date or
DATE() datetime expression
Add time values (intervals) to a
DATE_ADD() date value
DATE_FORMAT() Format date as specified
Subtract a time value (interval)
DATE_SUB() from a date
24

DATEDIFF() Subtract two dates

Page
Name Description
Synonym for
DAY() DAYOFMONTH()

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');

-> 0, 0

mysql> SELECT ADDDATE('2008-01-02', 31);

-> '2008-02-02'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

-> '03:00:01.999997'

mysql> SELECT CURDATE();

-> '2008-06-13'

mysql> SELECT CURDATE() + 0;

-> 20080613

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);

-> '2018-05-02'

mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);

-> '2017-05-01'

mysql> SELECT DATE_ADD('2020-12-31 23:59:59',

-> INTERVAL 1 SECOND);

-> '2021-01-01 00:00:00'

mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');

-> 2019

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');

-> 201907
25

SELECT DATE("2017-06-15 09:34:21");

Page
 2017-06-15

Sorting Query Results module

The ORDER BY clause in SQL is used to sort the results of a query, either in
ascending or descending order. It's placed after the WHERE clause (if present) and
allows you to specify which column(s) to sort by and the direction (ascending or
descending) for each.
Here's a breakdown:

1. Basic Syntax:
Code
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC | DESC];

 SELECT: Specifies the columns to retrieve.

 FROM: Specifies the table to retrieve data from.
 WHERE: Filters the data based on a condition.
 ORDER BY: Sorts the results.
 column_name: The column to sort by.
 ASC: Sorts in ascending order (default, can be omitted).
 DESC: Sorts in descending order.

Product Product Supplier Category Unit Price

ID Name ID ID
1 Chais 1 1 10 boxes x 18
20 bags

2 Chang 1 1 24 - 12 oz 19
bottles

3 Aniseed Syrup 1 2 12 - 550 10

ml bottles

4 Chef Anton's Cajun 2 2 48 - 6 oz 22

Seasoning jars
26

5 Chef Anton's Gumbo 2 2 36 boxes 21.35

Page

Mix
SELECT * FROM Products

ORDER BY Price;

ProductID ProductName SupplierID Catego Unit Price

ryID

3 Aniseed Syrup 1 2 12 - 550 ml 10

bottles

1 Chais 1 1 10 boxes x 18
20 bags

2 Chang 1 1 24 - 12 oz 19
bottles

5 Chef Anton's 2 2 36 boxes 21.35

Gumbo Mix

4 Chef Anton's 2 2 48 - 6 oz 22
Cajun jars
Seasoning

SELECT * FROM Products

ORDER BY Price DESC;

Sort the products by ProductName in reverse order:

SELECT * FROM Products

ORDER BY ProductName DESC;

ProductID ProductName SupplierID CategoryID Unit Price

27

4 Chef Anton's Cajun 2 2 48 - 6 oz 22

Seasoning jars
Page
5 Chef Anton's Gumbo 2 2 36 boxes 21.35
Mix

2 Chang 1 1 24 - 12 oz 19
bottles

1 Chais 1 1 10 boxes x 18
20 bags

3 Aniseed Syrup 1 2 12 - 550 ml 10

bottles

SELECT * FROM Products

ORDER BY ProductName;

ProductID ProductName SupplierID CategoryID Unit Price

3 Aniseed 1 2 12 - 550 ml 10
Syrup bottles

1 Chais 1 1 10 boxes x 18
20 bags

2 Chang 1 1 24 - 12 oz 19
bottles

4 Chef Anton's 2 2 48 - 6 oz 22
Cajun jars
Seasoning

5 Chef Anton's 2 2 36 boxes 21.35

28

Gumbo Mix
Page
SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

Generating Summary

filtering your data

Let's start by examining an example payments table. Our goal is to roll up old months
and put them into a summary table. To begin, we need to filter down to just older
months:

SELECT

amount,

YEAR(payment_date),

MONTH(payment_date)

FROM

payments

WHERE

payment_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

Grouping your data

Once we have our filtered data, we need to group it by year and month so we can roll
it up into the summary table:

SELECT

sum(amount) as amount,
29

YEAR(payment_date) as `year`,
Page
 MONTH(payment_date) as `month`

FROM

payments

WHERE

payment_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

GROUP BY

`year`, `month`

Using Aggregate Functions

These are the basics for summarization:

SELECT

COUNT(*) AS total_rows,

AVG(price) AS avg_price,

SUM(quantity) AS total_quantity,

MAX(price) AS max_price,

MIN(price) AS min_price

FROM Products

CREATE TABLE employees (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES

('Amit', 'Sales', 50000.00),
('Neha', 'HR', 60000.00),
('Ravi', 'IT', 70000.00),
30

('Priya', 'Sales', 55000.00),

('Sandeep', 'IT', 65000.00);
Page
SELECT COUNT(*) AS total_employees FROM employees;
Output:
+-----------------+
| total_employees |
+-----------------+
| 5|
+-----------------+

SUM()
The SUM() function returns the total sum of a numeric column.
Example:
SELECT SUM(salary) AS total_sales FROM employees;
Output:
+-------------+
| total_sales |
+-------------+
| 300000.00 |

The AVG() function returns the average value of a numeric column.

Example:
SELECT AVG(salary) AS average_salary FROM employees;
Output:
+----------------+
| average_salary |
+----------------+
| 60000.000000

The MAX() function returns the maximum value in a set of values.

Example:
SELECT MAX(salary) AS highest_salary FROM employees;
Output:
+----------------+
| highest_salary |
+----------------+
| 70000.00 |
+----------------+

MIN()
The MIN() function returns the minimum value in a set of values.
Example:
31

SELECT MIN(salary) AS lowest_salary FROM employees;

Output:
Page
 +---------------+
| lowest_salary |
+---------------+
| 50000.00 |

2. Grouping Data (GROUP BY)

To summarize data by category or group:

SELECT

category,

COUNT(*) AS total_products,

AVG(price) AS avg_price

FROM products

GROUP BY category;

Working with Metadata

Working with metadata in MySQL involves accessing information about the structure
and characteristics of your databases, tables, columns, and other database
objects. MySQL provides several mechanisms to retrieve this metadata:

1. INFORMATION_SCHEMA Database:
The INFORMATION_SCHEMA is a virtual database within each MySQL instance
that acts as a central repository for metadata. It contains several read-only tables
(views) that provide detailed information about:
 Databases: SCHEMATA table.
 Tables: TABLES table (including table names, engine, row format, etc.).
 Columns: COLUMNS table (including column names, data types, nullability,
character sets, etc.).
 Indexes: STATISTICS table.
 Constraints: TABLE_CONSTRAINTS and KEY_COLUMN_USAGE tables (for
primary keys, foreign keys, unique constraints).
 Stored Procedures and Functions: ROUTINES table.
32

 Triggers: TRIGGERS table.

Page

 Events: EVENTS table.

What is MySQL Metadata?

MySQL stores metadata in system databases, mainly:

 information_schema — the main one, with views for tables, columns,

constraints, etc.
 performance_schema — low-level monitoring (performance-focused).
 mysql — internal user and privilege info (you rarely query this directly).

1. List All Databases

SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA;

2. List All Tables in a Database

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

3. List All Columns in a Table

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE,
COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

4. List Indexes on a Table

SELECT *
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

Using Sequences - MySQL and Web.

Sequences in MySQL

MySQL does not have a built-in sequence feature but provides an alternative in
the form of the AUTO_INCREMENT column, which serves a similar purpose.

In MySQL, the AUTO_INCREMENT attribute is used to automatically

generate unique integer values (sequences) for a column. By default, this
33

sequence begins with an initial value of 1 and increments by 1 for each new
Page

row that is added.

 Syntax
Following is the syntax of AUTO_INCREMENT attribute in MySQL –
CREATE TABLE table_name (
column1 datatype AUTO_INCREMENT,
column2 datatype,
column3 datatype,
...
columnN datatype
);

Example
In the following example, we are creating a table named "CUSTOMERS" and,
in addition, defining the AUTO_INCREMENT attribute for the "ID" column of
the table –
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

Now, let us insert some records into the above-created table –

INSERT INTO CUSTOMERS VALUES

(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00);

Output

We can see in the table displayed below that the values in the "ID" column are
automatically incremented −
34
Page
ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00

2 Khilan 25 Delhi 1500.00

3 Kaushik 23 Kota 2000.00

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

6 Komal 22 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

35
Page