Understanding SQL

Structured Query Language, or SQL (pronounced see-quel), is the language that is used for querying and manipulating data and defining structures in databases. Initially developed at IBM in the early 1970s, SQL became an ANSI and ISO standard in 1986.

SQL is a powerful, yet simple language, and can do many things, such as execute queries, retrieve, insert, update, and delete data, create databases and tables, and much more.

These types of activities can be grouped into different subdivisions of SQL: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL):

• Use DDL commands to specify database schema:
• CREATE: This is used to create a new database or objects in a database.
• ALTER: This is used to alter a database or objects in a database.
• DROP: This is used to delete a database or objects in a database.
• TRUNCATE: This is used to remove all data from a table instantaneously.
• Use DML commands to query and modify data:
• SELECT: This is used to retrieve data from a database.
• INSERT: This is used to insert data into a database.
• UPDATE: This is used to update data in a database.
• DELETE: This is used to remove data from a database.
• Use DCL commands to control permissions and translations:
• GRANT: This is used to give access to a user.
• REVOKE: This is used to take access away from a user.
• COMMIT: This is used to save changes in a transaction.
• ROLLBACK: This is used to remove the saved changes in a transaction.

You won't learn about GRANT and REVOKE in this book. To get more information on granting and denying permissions, please visit https://dev.mysql.com/doc/refman/8.0/en/grant.html and https://dev.mysql.com/doc/refman/8.0/en/revoke.html.

Elements of SQL

The SQL language comprises several elements that will be explained in more depth in subsequent chapters. These elements include the following:

• Queries that retrieve data based on specific criteria.
• Clauses that are components of statements or queries.
• Predicates that are logical conditions that evaluate to true or false. These help you to narrow down the results of your queries.
• Expressions that produce either scalar values or tables of columns and rows. Expressions are a part of predicates.
• Statements that are queries run against a database, comprised of clauses and, optionally, expressions and predicates.
• White space that is generally ignored in SQL statements and queries, making it easier to format for readability because you don't have to worry so much about particular spacing for the SQL to run correctly.

The following diagram shows you the components of a SQL statement, which is also called a SQL query:

In the preceding diagram, you can see the different elements of a SQL statement. Each line in the preceding statement is considered a clause. Clauses use SQL keywords. Keywords are reserved words that have special significance in the SQL language—SELECT, FROM, and WHERE are just some of the keywords that are used. More information on keywords is provided in Chapter 4, Designing and Creating a Database. The preceding diagram also shows an expression and predicate. A predicate helps you to narrow down your query results. The expression is a piece of a predicate that sets the value. The diagram also helps to illustrate the use of white space. You could write out your entire query on one line, but it's much easier to read when you add carriage returns and spaces. The details of the different elements of queries will be covered more in future chapters of this book.