BLOB or Binary Large Object can be used to store binary data in MySQL. Sometimes binary data like images need to be stored in SQL databases. For example, you might want to store user photos along with other user details in the database table. Binary data of the user photo can be saved as a BLOB. By using BLOB, we will not require separate storage for images. BLOB helps in removing complexity and providing portability in such cases.

DEFAULT constraint provides a default value to a column. In case a row is inserted into the table and no value is provided to the column, the default value is taken.

Consider a table Customer created using the statement below.

CREATE TABLE `Customer`(
	`customerid` CHAR(5) NOT NULL,  
	`name` VARCHAR(25) NOT NULL, 
	`country` VARCHAR(25) NOT NULL, 
	PRIMARY KEY(`customerid`)); 

If we run the INSERT command as below, we will get an error as the country name is not provided.

INSERT INTO `Customer` (`customerid`, `name`) VALUES ('12345','William Jones'); 

To fix this problem you can add a DEFAULT constraint using the command below. Default value USA will be taken on running the INSERT command above.

ALTER TABLE Customer ALTER country SET DEFAULT 'USA';

• Data definition language (DDL) commands are the commands which are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.

• Data manipulation language (DML) commands are commands which are used for manipulation or modification of data. INSERT, UPDATE and DELETE are some common DML commands.

Views are virtual tables that are stored in SQL queries with a certain given name. VIEW replaces a SELECT query with a given named view. The SELECT query could be a complex SELECT query across different database tables or a simple SELECT query. Once invoked, a view gives results using the query stored in the view.

A view can be created using the below syntax.

CREATE VIEW `name_of_view` 
AS SELECT select_statement;

A query can be made on a view similar to the database table below.

SELECT * FROM  name_of_view;

• DELETE is a Data Manipulation Language(DML) command. It can be used for deleting some specified rows from a table. DELETE command can be used with the WHERE clause.

• TRUNCATE is a Data Definition Language(DDL) command. It deletes all the records of a particular table. The TRUNCATE command is faster in comparison to DELETE. While the DELETE command can be rolled back, TRUNCATE can not be rolled back in MySQL.

Both CHAR and VARCHAR data types store characters up to a specified length.

1. CHAR stores characters of fixed length while VARCHAR can store characters of variable length.

2. Storage and retrieval of data are different in CHAR and VARCHAR.

3. CHAR internally takes fixed space, and if the stored character length is small, it is padded by trailing space characters. VARCHAR has 1 or 2-byte prefixes along with stored characters.

4. CHAR has slightly better performance.

5. CHAR has memory allocation equivalent to the maximum size specified while VARCHAR has variable length memory allocation.

Aggregate functions are functions that are used to get a single summary value like the minimum, maximum, or average of a group of values.

COUNT, SUM, AVG, MIN, MAX are examples of MySQL aggregate functions.

COUNT function is used to count the number of entries returned by the SELECT query. Similarly, SUM, AVG, MIN, and MAX can be used to calculate the sum, average, minimum, and maximum of a set of values returned by the database query.