This post aims to provide you with a basic understanding of MySQL so that you can get up and running with one of the most extensively used database systems in the world fast and effortlessly.

Learning objectives

  • Writing queries
  • Creating and updating databases and tables
  • Data types in MySQL
  • Using MySQL built-in functions
  • Working with string and mathematical functions
  • Differences between MySQL and standard SQLTable of Contents

A brief overview of SQL

SQL stands for structured query language and is used to query and manipulate data in relational database management systems. Every system has its own SQL dialect, and MySQL is no exception. The fundamentals are the same, but the specifics might vary considerably.

A keyword begins a SQL statement, which is followed by a semicolon. In SQL, the semicolon is technically a statement terminator, which means it is always necessary.

In some situations, some systems, such as MySQL, employ the semicolon as a separator. This indicates that if there is only one statement, it is not necessarily needed. Because the semicolon is always permitted, it never hurts to use it, and we strongly advise you to do so.

SQL statements are not case-sensitive. This means that capital and lowercase letters are treated the same. By default, most symbols in MySQL are also not case sensitive, but there are exceptions.

select * from table;
SELECT * FROM table;

SELECT * FROM users; -- this is a comment

Remember that MySQL has configuration options that can alter this behavior. Your server can be set up so that all symbols are case-sensitive. So, we recommend that you write your SQL in a consistent manner, including case and symbols, to make your code as portable as possible.

A double dash precedes the comment, which is followed by at least one space and finishes at the end of the line. Two hyphen characters are used to start the comment, with no space between them and at least one space character following the second hyphen. The comment content follows, and the comment text is concluded with a new line.

Depending on the syntax of the statement, it may include one or more clauses. This SELECT statement, for example, contains a FROM clause and a WHERE clause.

SELECT * FROM users WHERE first_name LIKE 'Victor';

The WHERE clause defines a condition that must be met for each of the rows picked, while the FROM clause identifies the table.

Functions are used to carry out certain data operations. The COUNT function is used in this example to discover the number of rows that fit the condition in the WHERE clause.

SELECT COUNT(*) FROM users WHERE first_name LIKE 'Victor';

In SQL, expressions are used to extract values from data; for instance, this statement has two expressions. To represent the population in millions, this statement divides the population column by one million.

SELECT name, population / 1000000 AS populationM FROM countries WHERE population >= 1000000 ORDER BY population;

And only those rows with a population column larger than or equal to one million are selected using this logical expression.

The structure of SQL syntax is straightforward, but the rules might be complicated depending on the statements and their implementations.