SQL Appendix

In this chapter, the SQL commands that are mentioned in this book will be outlined for quick reference. This includes the syntax for querying data, modifying data, and designing databases and tables. This chapter will help you by providing a quick reference guide so that you won't have to go back through all the chapters to check the syntax, but if you require more details regarding how the syntax works, you can refer to the specific chapter for that information.

In this chapter, we will cover the following topics:

• SQL for designing databases
• SQL for selecting data
• SQL for modifying data
• SQL expressions
• Advanced query techniques
• Programmable objects

SQL for designing databases

This section takes you through some example syntax for creating and altering databases in MySQL. For more details on this syntax, visit Chapter 4, Designing and Creating a Database, and Chapter 8, Modifying Data and Table Structures. These chapters will also outline the differences in syntax for Oracle, PostgreSQL, and SQL Server.

Syntax for creating a database

In order to create a database, you can use the following sample syntax:

CREATE DATABASE yourschema; 

Syntax for creating and altering tables

In order to create a table, you can use the...

SQL for selecting data

This section takes you through some example syntax for selecting data in MySQL. For more details on this syntax, visit Chapter 6, Querying a Single Table; Chapter 7, Querying Multiple Tables; and Chapter 10, Grouping and Summarizing Data. These chapters will also outline the differences in syntax for Oracle, PostgreSQL, and SQL Server.

Syntax for selecting data

To select all columns from one table, you can use the following sample syntax:

SELECT * FROM tablename; 

To select one or more columns from one table, you can use the following sample syntax:

SELECT col1, col2, col3 FROM tablename; 

To select from a specified database, you can use the following sample syntax:

USE databasename;
SELECT col1,...

SQL for modifying data

This section takes you through some example syntax for modifying data in MySQL. For more details on this syntax, visit Chapter 8, Modifying Data and Table Structures. This chapter will also outline the differences in syntax for Oracle, PostgreSQL, and SQL Server.

Syntax for inserting data

To INSERT a single row, you can use the following sample syntax:

INSERT INTO tablename (col1, col2, col3)
VALUES ('value1','value2',value3);

To INSERT multiple rows, you can use the following sample syntax:

INSERT INTO tablename (col1, col2, col3)
VALUES ('value1','value2',value3),
       ('value5','value6',value7),
       ('value8','value9&apos...

SQL expressions

This section takes you through some example syntax for using expressions in MySQL. For more details on this syntax, visit Chapter 9, Working with Expressions. That chapter also outlines the differences in syntax for Oracle, PostgreSQL, and SQL Server.

Types of expressions

To use literal values in expressions, you can use the following sample syntax:

SELECT 'string', 1, 1.23, NULL;

To use comparison operators, you can use the following examples:

• SELECT column FROM table WHERE column = 100: This is an example of equal to.
• SELECT column FROM table WHERE column = 'value': This is an example of equal to.
• SELECT column FROM table WHERE column != 1000: This is an example of not equal to.
• SELECT...

Advanced query techniques

This section takes you through some example syntax for advanced query techniques in MySQL. For more details on this syntax, visit Chapter 11, Advanced Query Techniques. This chapter will also outline the differences in syntax for Oracle, PostgreSQL, and SQL Server.

Syntax for subqueries

In the following code, the query inside parentheses is the inner query, while the query outside parentheses is the outer query.

To use a subquery, you can follow the following sample syntax:

SELECT col1
FROM table1
WHERE col1 IN 
     (SELECT col1 FROM table 2 WHERE col1 = 'test')

For a non-correlated subquery in WHERE with multiple values being returned, you can follow the following sample syntax. The square...

Programmable objects

This section takes you through some example syntax for modifying data in MySQL. For more details on this syntax, visit Chapter 12, Programmable Objects. This chapter will also outline the differences in syntax for Oracle, PostgreSQL, and SQL Server.

Syntax for views

To create a view, you can use the following sample syntax:

CREATE VIEW nameofview AS
SELECT col1, col2, co1n
FROM tablename
WHERE condition(s);

To create a view that will be used to modify data, you can use the following sample syntax:

CREATE VIEW nameofview AS
SELECT col1, col2, co1n
FROM tablename
WHERE condition(s)
WITH CHECK OPTION;

Syntax...

Summary

In this book, you learned many things about the SQL programming language and how to use it in MySQL Workbench. This included creating databases, creating and alerting tables, and creating and altering indexes.

You learned how to select data with SQL syntax using the SELECT, WHERE, ORDER BY, JOIN, GROUP BY, and HAVING clauses. We also provided information about the aggregate functions you can use in the GROUP BY clause. You also learned how to modify data with SQL syntax including INSERT, UPDATE, and DELETE. This included how to use SQL transactions to commit and rollback SQL statements.

Then, you learned about advanced query techniques, including how to create and use subqueries, common table expressions, query hints, and transaction isolation level syntax. You learned about programmable objects, including creating and using views, variables, stored procedures, functions...