MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations.

It is the world's second most popular and widely-used open-source database.

Formerly MySQL was initially owned by a for-profit firm MySQL AB, then Sun Microsystems bought it, and then Oracle bought Sun Microsystems, so Oracle currently owns MySQL.

Using MySQL is free of cost for the developer, but enterprises have to pay a license fee to Oracle.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) based on structured query language. MySQL supports a wide range of operating systems, most famous of those include Windows, Linux & UNIX.

• Flexibility
• Power
• Enterprise-Level SQL Features
• Full-Text Indexing and Searching
• Configuration and Security
• Query Caching
• Replication

MySQL is written in C and C++, and its SQL parser is written in yacc.

1. ISAM
2. MyISAM
3. MERGE
4. InnoDB
5. MEMORY (HEAP)
6. ARCHIVE
7. BDB
8. CSV
9. FEDERATED

MyISAM is the default database engine used in MySQL.

• Multithreaded SQL server supporting various client programs and libraries
• Different backend
• Wide range of application programming interfaces
• Administrative tools.

The default port number of MySQL is 3306.

• MySQL is not very efficient in handling very large databases.
• MySQL versions less than 5.0 do not support COMMIT, stored procedure and ROLEBACK.
• MySQL doesn’t have as good a developing and debugging tool as compared to paid databases.
• Transactions are not handled very efficiently.
• Developers may find some of its limitations very frustrating.

Truncate	Delete
TRUNCATE is a DDL command.	DELETE is a DML command.
It is not possible to use Where command with TRUNCATE.	You can use it with DELETE command.
TRUNCATE cannot be used with indexed views.	DELETE can be used with indexed views.
Truncate is a very dangerous command and should be used carefully because it deletes every row permanently from a table.	The DELETE command is used to delete data from a table. It only deletes the rows of data from the table.

Mysql_connect	Mysql_pconnect
It opens a new connection to the database.	it opens the persistent connection.
Every time you need to open and close the database connection.	The database connection cannot be closed.
The mysql_close() method is used to close the connection with the database..	The mysql_close() does not close the connection with the database..
Due to its complex use, it is not much user-friendly.	Due to its simplicity, it is more user-friendly..

• UNION – This operation returns all the distinct rows selected by a query
• UNION ALL – This operation returns all the rows selected by a query and also includes all duplicate rows.
• MINUS – This operation returns all the distinct rows selected by the first query but does not select the rows selected by the second query.
• INTERSECT – This operation returns all the distinct rows selected by both queries.

A database is a structured collection of data stored in a computer system and organized in a way to be quickly searched. With databases, information can be rapidly retrieved.

Data is made up of small facts and information without context. If you give data context, then you have information. Knowledge is gained when information is consumed and used for decision making.

A database is an organized collection of related information.

SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

SQL is a computer language, whereas MySQL is a software or an application

SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data

MySQL database server is reliable, fast and very easy to use.  This software can be downloaded as freeware and can be downloaded from the internet.

A MySQL database contains one or more tables, each of which contains records or rows.

Within these rows are various columns or fields that contain the data itself.

There is a major difference between a database and a table.
 

• Tables are used to group the data in relation to each other and create a dataset. This dataset will be used in the database. The data stored in the table in any form is a part of the database, but the reverse is not true.
• Tables are a way to represent the division of data in a database while the database is a collection of tables and data.
• A database is a collection of organized data and features used to access them, whereas the table is a collection of rows and columns used to store the data.

HEAP tables are present in memory and they are used for high speed storage on temporary.

Some key points as follows:

• BLOB or TEXT fields are not allowed
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL
• Only comparison operators can be used =, <,>, = >,=<

First of all, the MYSQL server is free to use for developers and small enterprises..

MySQL's community is tremendous and supportive; hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market for a long time. All bugs arising in the previous builds have been continuously removed, and a very stable version is provided after every update.

MySQL server is open source

The MySQL database server is very fast, reliable, and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

MySQL is open source software which is available at any time and has no cost involved.

MySQL is portable

GUI with command prompt.

Administration is supported using MySQL Query Browser

myisamchk is a useful database utility tool that is used to get information about MyISAM database tables.

It is also used for checking, debugging, repairing and optimizing database tables. It is better to use this command when the server is down or when the required tables are not in use by the server.

Advantages:

It is well-known for its reliable and secure database management system. Transactional tasks of the website can be done more securely by using this software.

It supports different types of storage engines to store the data and it works faster for this feature.

It can handle millions of queries with a high-speed transactional process.

It supports many advanced level database features, such as multi-level transactions, data integrity, deadlock identification, etc.

Maintenance and debugging processes are easier for this software.


Disadvantages:

It is hard to make MySQL scalable.

It is not suitable for a very large type of database.

The uses of stored routines and triggers are limited to MySQL.

The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.

There are three main ways you can interact with MySQ
 

• using a command line
• via a web interface
• through a programming language

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL.
 

• MyISAM
• Heap
• Merge
• INNODB
• ISAM

• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

• Alter
• Delete
• Drop
• Insert
• Show
• Truncate
• Update
• use

Char	Varchar
CHAR column length is fixed.	VARCHAR length is variable.
The maximum no. of character CHAR data types can hold is 255 characters.	VARCHAR can hold up to 4000 characters..
CHAR is 50% faster than VARCHAR.	Slower than CHAR.
CHAR uses static memory allocation.	VARCHAR uses dynamic memory allocation.
CHAR stands for Character.	VARCHAR stands for Variable Character.

Primary Key	Candidate key
Primary Key is a unique and non-null key which identify a record uniquely in table. A table can have only one primary key.	Candidate key is also a unique key to identify a record uniquely in a table but a table can have multiple candidate keys.
Primary key column value can not be null.	Candidate key column can have null value.
Primary key is most important part of any relation or table.	Candidate key refers to which key can be used as the primary key.
Primary Key is a candidate key.	Candidate key may or may not be a primary key.

• TINYINT
• SMALLINT
• MEDIUMINT
• INT
• BIGINT
• DECIMAL
• FLOAT
• DOUBLE
• BIT

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR

• CHAR
• VARCHAR
• BINARY
• BLOB
• TINYTEXT
• TEXT
• ENUM
• SET
• NULL

A query is a specific request or a question. One can query a database for specific information and have a record returned.

CHAR_LENGTH  is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

ENUM data type is used in the MySQL database table to select any one value from the predefined list.

The value of a particular field can be restricted by defining the predefined list as the field which is declared as ENUM will not accept any value outside the list.

The SET data type is used to select one or more or all values from the predefined list. This data type can also be used to restrict the field for inserting only the predefined list of values like ENUM.

ENUMs and SETs are used to represent powers of two because of storage optimizations

Database Black Box Testing deals with data mapping, data storing and retrieving. The Database Black Box Testing is used for techniques such as Equivalence Partitioning and Boundary Value Analysis. 

Using the Drop Table statement We can delete a table in MySQL.

• Using the CREATE TABLE Statement
• Using the ALTER TABLE Statement

The foreign key is used to link one or more tables together. It matches the primary key field of another table to link the two tables. It allows us to create a parent-child relationship with the tables.

ENUM is a string object used to specify set of predefined values and that can be used during table creation.

REGEXP is a pattern match in which  matches pattern anywhere in the search value.

The database table uses a primary key to identify each row uniquely.

When the primary key of any table is used in another table as the primary key or another field for making a database relation, then it is called a foreign key.

• The primary key uniquely identifies a record, whereas foreign key refers to the primary key of another table.
• When a record is inserted in a table that contains the primary key then it is not necessary to insert the value on the table that contains this primary key field as the foreign key.
• The primary key can never accept a NULL value but foreign key accepts a NULL value.
• When a record is deleted from the table that contains the primary key then the corresponding record must be deleted from the table containing the foreign key for data consistency. But any record can be deleted from the table that contains a foreign key without deleting a related record of another table.

An expression which consists of a temporary set of results defined in a SQL statement is said to be a Common Table Expression(CTE).

You can remove a column by using the DROP keyword -

In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index.

You can achieve fast searches with the help of an index, telling the SQL engine where to go to find your data.

Indexes can be created using one or more columns.

Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

• SET
• BLOB
• CHAR
• VARCHAR
• ENUM
• TEXT

A TIMESTAMP data type is used to store the combination of date and time value which is 19 characters long.

A Cursor is nothing but a control which enables traversal over the records in the table. So, the cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.

A NULL value is a field with no value present in that particular field. Since the NULL value cannot be compared to any other NULL values, you cannot use the comparison operators such as =, <, or <>. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.

BLOB is an acronym that stands for a binary large object. It is used to hold a variable amount of data.

It can hold a very large amount of data. For example - documents, images, and even videos. You could store your complete novel as a file in a BLOB if needed.

There are four types of BLOB -
 

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

• Storage mechanism
• Locking levels
• Indexing
• Capabilities and functions

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver

Both mysql_fetch_array() and mysql_fetch_object() are built-in methods of PHP to retrieve records from MySQL database table.

The difference between these methods is that mysql_fetch_array() returns the result set as an array and mysql_fetch_object() returns the result set as an object.

The NVL function, IFNULL function, and the ISNULL function all of them are used to replace the NULL value with another value.

The ORACLE users use the NVL function, MySQL users use the IFNULL function and the SQL servers use the ISNULL function

TIMESTAMP column is updated with Zero when the table is created.

UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to  current time whenever there is a change in other fields of the table.

The reason behind selecting Lamp stack is very simple.

Linux, Apache, MySQL, PHP are open source software. The security of the Linux operating system is much more than Windows.

The Apache server is a better server than others in the perspective of functionalities and security.

MySQL is one of the most popular open source databases is used with PHP to perform various functionalities.

A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.

Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.

In MySQL, a view consists of a set of rows that is returned if a particular query is executed. This is also known as a ‘virtual table’. Views make it easy to retrieve the way of making the query available via an alias.

The advantages of views are - 
 

• Simplicity
• Security
• Maintainability

A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event.
 

• Audit Trails
• Validation
• Referential integrity enforcement

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

Use the strtotime function to subtract both the dates and find the differences between the days in seconds

Both NOW() and CURRENT_DATE() are built-in MySQL methods.

NOW() is used to show the current date and time of the server and CURRENT_DATE() is used to show only the date of the server.

To rename the database name, we need first to create a new database into the MySQL server.

Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database.

The server, mysqld, is the hub of a MySQL installation.

It performs all manipulation of databases and tables.

Using mysqldump command we can import mysql database.

Using the ALTER TABLE statement we can follow this task.

One-to-One - Usually, when two items have a one-to-one relationship, you just include them as columns in the same table.

One-to-Many - One-to-many (or many-to-one) relationships occur when one row in one table is linked to many rows in another table.

Many-to-Many - In a many-to-many relationship, many rows in one table are linked to many rows in another table. To create this relationship, add a third table containing the same key column from each of the other tables

Scaling capacity is actually the ability to handle the load, and it’s useful to think of load from several different angles.
 

• Quantity of data
• Number of users
• User activity
• Size of related datasets

CONCAT() function is used to combine two or more string data.

The function used to encrypt the data is AES_ENCRYPT() and the function used to decrypt the data is AES_DECRYPT().

Heap Table	Temporary Table
Heap Table exists in the memory.	A temporary table is valid only during the session.
Heap Tables are shared among a various number of clients.	Temporary tables are not shared among the clients.
Temporary tables need a special privilege to create tables.	Heap Tables are storage engines which do not need special privileges.

A decimal data type is used in MySQL to store the fractional data.

The meaning of decimal (5,2) means that the total length of the fractional value is 5.
The field can contain 3 digits before the decimal point and 2 digits after the decimal point.
If a user adds any value larger than the defined length then it will insert 999.99 in the field.

We can connect two or more tables in MySQL using the JOIN clause. MySQL allows various types of JOIN clauses. These clauses connect multiple tables and return only those records that match the same value and property in all tables. The following are the four easy ways to join two or more tables in MySQL.
 

• Inner Join
• Left Join
• Right Join
• Cross Join

A stored procedure is a group of SQL statements that we save in the database. The SQL queries, including INSERT, UPDATE, DELETE, etc. can be a part of the stored procedure.

A procedure allows us to use the same code over and over again by executing a single statement. It stores in the database data dictionary.

We can execute a stored procedure in MySQL by simply CALL query. This query takes the name of the stored procedure and any parameters we need to pass to it.

The process of breaking up large tables into smaller chunks (called shards) that are spread across multiple servers is called Sharding.

Sharding is a very important concept which helps the system to keep data into different resources according to the sharding process.

The advantage of Sharding is that since the sharded database is generally much smaller than the original queries, maintenance, and all other tasks are much faster.

1. Sharding makes the Database smaller
2. Sharding makes the Database faster
3. Sharding makes the Database much more easily manageable
4. Sharding can be a complex operation sometimes
5. Sharding reduces the transaction cost of the Database.

Indexing is a process to find an unordered list into an ordered list. It helps in maximizing the query's efficiency while searching on tables in MySQL. The working of MySQL indexing is similar to the student index.

Suppose we have a student and want to get information about, say, searching.

Without indexing, it is required to go through all pages one by one, until the specific topic was not found.

On the other hand, an index contains a list of keywords to find the topic mentioned on pages. Then, we can flip to those pages directly without going through all pages.

Federated tables which allow access to the tables located on other databases on other servers.

mysqldump is a useful utility tool of MySQL that is used to dump one or more or all databases from the server for backup or transfer to another database server.

MySQL is the most popular free and open-source database software which comes under the GNU General Public License.

In the beginning, it was owned and sponsored by the Swedish company MySQL AB. Now, it is bought by Sun Microsystems (now Oracle Corporation), who is responsible for managing and developing the database.

Both UNIX TIMESTAMP and MySQL TIMESTAMP are used to represent the date and time value.

The main difference between these values is that UNIX TIMESTAMP represents the value by using 32-bits integers and MySQL TIMESTAMP represents the value in the human-readable format.

Auto Increment is a constraint that automatically generates a unique number while inserting a new record into the table. Generally, it is used for the primary key field in a table.

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

The "i_am_a_dummy flag" enables the MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

MySQL table that uses the CSV storage engine is called a CSV table. Data are stored as comma-separated values in the CSV table. MySQL server creates a data file with an extension .csv to store the content of the CSV table.

LIKE and REGEXP operators are used to express with ^ and %

Install antivirus and configure the operating system's firewall.

Never use the MySQL Server as the UNIX root user.

Change the root username and password Restrict or disable remote access.

SUM() function is used to calculate the sum of any column.

COUNT() function is used to count the total number of records of any table.

ISAM  is abbreviated as Indexed Sequential Access Method

It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

• LONGTEXT
• TEXT
• TINYTEXT
• MEDIUMTEXT

SQLyog program is the most popular GUI tool for admin.

It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin, and others. MySQL front ends and MySQL GUI tools.

When a group of database operations is done as a single unit then it is called a transaction.

If any task of the transactional tasks remains incomplete then the transaction will not succeed. Hence, it is mandatory to complete all the tasks of a transaction to make the transaction successful.

A transaction has four properties which are known as ACID property.
 

• Atomicity
• Consistency
• Isolation
• Durability

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compressed if required.

On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.

Commit is a transaction command that executes when all the tasks of a transaction are completed successfully. It will modify the database permanently to confirm the transaction.

Rollback is another transactional command that executes when any of the transactional tasks become unsuccessful and undoes all the changes that are made by any transactional task to make the transaction unsuccessful.

Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().

MyISAM Static and MyISAM dynamic are the variations of the MyISAM storage engine.
 

• All the fields of MyISAM static table are of a fixed length and MyISAM dynamic table accepts variable length fields such as BLOB, TEXT, etc.
• After data corruption, it is easier to restore the MyISAM static table than MyISAM dynamic table.

MySQL data directory is a place where MySQL stores its data.

Each subdirectory under this data dictionary represents a MySQL database.