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.
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 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
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 cloudnloud-db;
We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE command
DROP DATABASE cloudnloud-db;
List All Database
Table & Views
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 table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];
ALTER TABLE members ADD mem_age varchar(40) NOT NULL;
The TRUNCATE statement in MySQL removes the complete data without removing its structure.
TRUNCATE TABLE members;
This statement removes the complete data of a table along with the whole structure or definition permanently from the database
DROP TABLE members;
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;
insert into members values(101,'rahul','delhi');
update members set name='bob', city='london' where id=101;
delete from members where id=101;
SELECT * from members;
Are you able to understand?
then read further
WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause to filter the results
SELECT * FROM members WHERE address = 'Mau';
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;
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;
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;
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);
SELECT mem_name FROM members WHERE address LIKE 'Luck%';
SELECT * FROM members WHERE mem_name IN ('Ajeet', 'Vimal', 'Deepika');
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;
SELECT * FROM members WHERE mem_id BETWEEN 1 AND 3;
It is used to retrieve data from multiple tables
There are three types of MySQL joins:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;
SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;
SELECT COUNT (aggregate_expression) FROM table_name [WHERE conditions]; SELECT COUNT(mem_name) FROM members;
SELECT SUM(aggregate_expression) FROM tables [WHERE conditions]; SELECT SUM(working_hours) AS "Total working hours" FROM members;
SELECT AVG(aggregate_expression) FROM tables [WHERE conditions]; SELECT AVG(working_hours) Avg_working_hours FROM members;
SELECT MIN ( DISTINCT aggregate_expression) FROM table_name(s) [WHERE conditions]; SELECT MIN(income) AS Minimum_Income FROM members;
SELECT MAX(income) AS "Maximum Income" FROM members;
SELECT column_name FROM table_name LIMIT 1; SELECT mem_name FROM members LIMIT 1;
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
Community and Social Footprints :
Did you find this article valuable?
Support Cloudnloud Tech Community by becoming a sponsor. Any amount is appreciated!