How to Learn MySQL

Structured Query Language (SQL, pronounced sequel) is a programming language used for querying databases. MySQL (pronounced My Ess Que El) is considered the standard query language for relational database systems.

MySQL is run by Swedish company MySQL AB. It was founded in 1995 and acquired in 2008 by Sun Microsystems, which was acquired by Oracle in 2010. It’s now the world’s leading open-source database and one of the world’s largest open-source companies, handling all the engineering, training and selling internally. The programming language has found its way into thousands of other closed-source software programs.

MySQL helps in computing large amounts of data. Relational databases rely on the use of tables rather than large stores of data. As such, they are very portable, safe and stable. They can also be manipulated or organized to maximize speed and flexibility. A user organizes relationships between data so one never has orphan, duplicate or missing data.

The MySQL system relies on relational algebra, a mathematical query language that manipulates data in a database. There are eight types of statements in MySQL: Data Definition Language (DDL), Data Manipulation Language (DML), Transactional and Locking Statements, Replication Statements, Prepared Statements, Compound Statement Syntax, Database Administration Statements, and Utility Statements.

DDL (Data Definition Statements) are used to define the database’s structure, known as a schema. Some commands include:

• CREATE: These statements create something, for instance, in the case of CREATE TABLE, a table within a database. Other common create statements include CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, and CREATE PROCEDURE.
• DROP: These statements remove something, for instance, in the case of DROP DATABASE, the command removes a database. Other drop statements include DROP EVENT, DROP FUNCTION, DROP INDEX, DROP VIEW, DROP TABLE.
• ALTER: These statements change what already exists. Common alter statements include ALTER DATABASE, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER AND ALTER TABLE.

DML (Data Manipulation Statements) allow a user to view and modify data with these commands:

• SELECT: These statements are used to retrieve rows from one or more tables.
• INSERT: These statements add rows to an existing table.
• UPDATE: These statements modify rows in a table.
• DELETE: These statements remove rows from a table.

By default MySQL automatically stores updates. You can override this default with Transactional and Locking Statements like:

• SET autocommit: By using this statement, a session always has a transaction open. If a user doesn’t commit the final transaction, MySQL automatically rolls back the transaction to the previous version.
• COMMIT: This statement makes changes made to the current transaction permanent. It also makes the changes visible to other users.
• ROLLBACK: Like an “edit” or “back” button, this statement cancels all changes made in the current transaction.

Replication Statements enable data from the master database server to be replicated to more database servers. Some statements include:

• SHOW BINARY LOGS: This statement lists the binary logs on the server.
• CHANGE REPLICATION SOURCE TO: This statement modifies the parameters used by the replica server for connecting to and reading data from the source.
• START GROUP_REPLICATION: This statement enables recovery from multiple places or users. It requires the GROUP_REPLICATION_ADMIN privilege to be turned on.

Prepared Statements are templates that do not use values, but placeholders. This method provides security against SQL injection attacks, in which the code is manipulated by an attacker to gain access to sensitive data in the database. Prepared statements use these commands:

• PREPARE: This statement assigns a name and parameter to an SQL statement for future reference.
• EXECUTE: Once a prepared statement exists, EXECUTE is used to re-invoke the parameter.
• DEALLOCATE PREPARE: This statement is used once a prepared statement no longer needs the assigned parameter.

Compound Statement Syntax are statements that contain other statements. They command declarations for variables, condition handlers, and cursors. They also are used for flow control constructs such as loops and conditional tests. An example of a compound statement is:

• BEGIN … END: These statements can include multiple statements enclosed within them. They can include stored procedures for functions, triggers, events and lists.

Database Administration Statements enable programs to perform operations including configuring servers, administering users, exporting, importing and viewing data. Some of these statements include:

• Account Management Statements: These statements deal with users and permissions. They are atomic and crash safe.
• Resource Group Management Statements: MySQL allows the creation of resource groups and enables assigning threads within the server to various groups.
• Table Maintenance Statements: These statements allow for quality assurance on tables. They include ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE, OPTIMIZE TABLE and REPAIR TABLE.
• Component, Plugin, and User-Defined Function Statements: A user-defined statement extends MySQL by making a new function act like a native or built-in MySQL function. Components and Plugins extend server capabilities. These statements are used to manipulate the server.
• CLONE Statement: This statement is used to clone data in SQL locally or from a remote server.
• SET Statements: These statements come in a variety of forms. SET CHARACTER SET and SET NAMES assign values related to the current connection to the server.
• SHOW Statements: These statements show up in many forms and offer information about databases, columns, tables or server status.

Utility Statements enable users to obtain data and information and include the following statements:

• DESCRIBE/EXPLAIN: These synonymous statements retrieve information on table structure or query execution plans.
• HELP: This statement searches help tables for the given search and displays the results. It returns online information from the MySQL Reference Manual.
• USE: This statement communicates to MySQL to use this named version as the default database for future statements.