Table of contents
In this blog, we are going to learn and recall about MySQL Database fundamentals and commands
What is MySQL?
MySQL is the most popular Open Source Relational SQL database management system.
MySQL is one of the best RDBMS being used for developing web-based software applications.
RDBMS Terminology
Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.
Database − A database is a collection of tables, with related data.
Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet.
Column − One column (data element) contains data of one and the same kind, for example the column postcode.
Row − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.
Redundancy − Storing data twice, redundantly to make the system faster.
Primary Key − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.
Foreign Key − A foreign key is the linking pin between two tables.
Compound Key − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
Index − An index in a database resembles an index at the back of a book.
Referential Integrity − Referential Integrity makes sure that a foreign key value always points to an existing row.
MySQL Database
MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −
MySQL is released under an open-source license. So you have nothing to pay to use it.
MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
MySQL uses a standard form of the well-known SQL data language.
MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
MySQL works very quickly and works well even with large data sets.
MySQL is very friendly to PHP, the most appreciated language for web development.
MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.
Hope you can get the basic knowledge about MySQL
Let's go further Hands-on part
How to install MySQL ?
you can install MySql shell (CLI) or you can install MySQL workbench(GUI)
check the below links
MySQL Database
A database is used to store the collection of records in an organized form. It allows us to hold the data into tables, rows, columns, and indexes to find the relevant information frequently. We can access and manage the records through the database very easily
Create Database
CREATE DATABASE cloudnloud-db;
Select Database
USE cloudnloud-db;
Drop Database
We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE command
DROP DATABASE cloudnloud-db;
List All Database
SHOW DATABASES;
Table & Views
CREATE Table
A table creation command requires three things:
- Name of the table
- Names of fields
- Definitions for each field
CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
........,
table_constraints
);
CREATE TABLE members(
id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
age int NOT NULL,
PRIMARY KEY (id)
);
ALTER Table
ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];
ALTER TABLE members
ADD mem_age varchar(40) NOT NULL;
TRUNCATE Table
The TRUNCATE statement in MySQL removes the complete data without removing its structure.
TRUNCATE TABLE members;
Drop Table
This statement removes the complete data of a table along with the whole structure or definition permanently from the database
DROP TABLE members;
MySQL Views
the View is a virtual table created by a query by joining one or more tables
CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
CREATE VIEW trainer AS
SELECT course_name, trainer
FROM courses;
MySQL Queries
INSERT Record
insert into members values(101,'rahul','delhi');
UPDATE Record
update members set name='bob', city='london' where id=101;
DELETE Record
delete from members where id=101;
SELECT Record
SELECT * from members;
Are you able to understand?
then read further
MySQL Clauses
MySQL WHERE
WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause to filter the results
SELECT *
FROM members
WHERE address = 'Mau';
MySQL DISTINCT
DISTINCT clause is used to remove duplicate records from the table and fetch only the unique records
SELECT DISTINCT expressions
FROM tables
[WHERE conditions];
SELECT DISTINCT address
FROM members;
MySQL ORDER BY
ORDER BY Clause is used to sort the records in ascending or descending order
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];
SELECT *
FROM members
WHERE address = 'Lucknow'
ORDER BY mem_name;
MySQL GROUP BY
GROUP BY Clause is used to collect data from multiple records and group the result by one or more columns.
SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;
SELECT address, COUNT(*)
FROM members
GROUP BY address;
MySQL HAVING
HAVING Clause is used with GROUP BY clause. It always returns the rows where the condition is TRUE.
SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
SELECT mem_name, SUM(working_hours) AS "Total working hours"
FROM members
GROUP BY mem_name
HAVING SUM(working_hours) > 5;
MySQL Conditions
MySQL AND
AND condition is used with SELECT, INSERT, UPDATE or DELETE statements to test two or more conditions in an individual query
WHERE condition1
AND condition2
...
AND condition_n;
SELECT *
FROM members
WHERE mem_firstname = 'Ajeet'
AND mem_id > 3;
MySQL OR
SELECT *
FROM members
WHERE mem_firstname = 'Ajeet'
OR mem_id > 100;
MySQL AND OR
SELECT *
FROM members
WHERE (course_name = 'Java' AND mem_name = 'Aryan')
OR (mem_id < 2);
MySQL LIKE
SELECT mem_name
FROM members
WHERE address LIKE 'Luck%';
MySQL IN
SELECT *
FROM members
WHERE mem_name IN ('Ajeet', 'Vimal', 'Deepika');
MySQL NOT
SELECT *
FROM members
WHERE mem_name NOT IN ('Ajeet','Vimal','Deepika');
MySQL IS NULL
SELECT *
FROM members
WHERE mem_name IS NULL;
MySQL IS NOT NULL
SELECT *
FROM members
WHERE mem_name IS NOT NULL;
MySQL BETWEEN
SELECT *
FROM members
WHERE mem_id BETWEEN 1 AND 3;
MySQL JOIN
It is used to retrieve data from multiple tables
There are three types of MySQL joins:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
Inner JOIN
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Left Join
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Right Join
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Aggregate Functions
MySQL count()
SELECT COUNT (aggregate_expression)
FROM table_name
[WHERE conditions];
SELECT COUNT(mem_name) FROM members;
MySQL sum()
SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];
SELECT SUM(working_hours) AS "Total working hours" FROM members;
MySQL avg()
SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];
SELECT AVG(working_hours) Avg_working_hours FROM members;
MySQL min()
SELECT MIN ( DISTINCT aggregate_expression)
FROM table_name(s)
[WHERE conditions];
SELECT MIN(income) AS Minimum_Income FROM members;
MySQL max()
SELECT MAX(income) AS "Maximum Income" FROM members;
MySQL first()
SELECT column_name
FROM table_name
LIMIT 1;
SELECT mem_name
FROM members
LIMIT 1;
MySQL last()
SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT 1;
SELECT mem_name
FROM members
ORDER BY mem_id DESC
LIMIT 1;
Hope you got some good insights after reading this blog
Keep Learning, Keep Growing