CONTENTS IN DETAIL
Introduction..................................................................................................1
SQL Overview...........................................................................................1
About This Book.......................................................................................2
Code Conventions......................................................................................3
Code Download.........................................................................................3
Chapter 1: Storing and Maintaining Data.................................................5
Creating and Selecting A Default Database..............................................5
Creating a Table.........................................................................................6
Adding Data...............................................................................................7
Updating Data............................................................................................8
Deleting Data...........................................................................................10
Summary..................................................................................................11
Chapter 2: Basic Queries...........................................................................13
The SELECT statement...........................................................................13
Querying All Data...................................................................................13
Selecting Specific Columns.....................................................................14
Selecting Rows with WHERE.................................................................15
Compound Conditions.............................................................................16
Evaluation Precedence and the Use of Parentheses.................................18
The NOT logical operator........................................................................19
The BETWEEN Operator........................................................................20
The IN Operator.......................................................................................20
The LIKE Operator..................................................................................21
Escaping the Wildcard Character............................................................22
Combining the NOT operator..................................................................22
Summary..................................................................................................24
Chapter 3: Full-Text Search......................................................................25
Natural Language Searches.....................................................................25
Boolean Searches.....................................................................................29
Summary..................................................................................................31
Chapter 4: Query Output..........................................................................33
Column Aliases........................................................................................33
Expressions..............................................................................................34
Limiting the Number of Rows.................................................................35
The DISTINCT Keyword........................................................................36
Aggregate Functions................................................................................37
The CASE Expression.............................................................................38
Ordering Output Rows.............................................................................42
Storing Query Output..............................................................................46
Summary..................................................................................................48
Chapter 5: Grouping..................................................................................49
The GROUP BY Clause..........................................................................49
The HAVING Keyword..........................................................................51
Summary..................................................................................................53
Chapter 6: Joins.........................................................................................55
Primary Keys and Foreign Keys..............................................................55
Querying Multiple Tables........................................................................56
Using Table Aliases.................................................................................58
Joining More than Two Tables................................................................59
The Outer Join.........................................................................................62
Self-Joins.................................................................................................64
Multiple Uses of A Table........................................................................65
Natural Joins............................................................................................67
Summary..................................................................................................69
Chapter 7: Subqueries...............................................................................71
Single-Row Subqueries...........................................................................71
Multiple-Row Subqueries........................................................................73
Multiple Nested Subqueries.....................................................................77
Correlated Subqueries..............................................................................78
Summary..................................................................................................79
Chapter 8: Compound Queries.................................................................81
UNION ALL............................................................................................81
UNION....................................................................................................83
Summary..................................................................................................84
Chapter 9: Views........................................................................................85
Creating and Using Views.......................................................................85
Nested Views...........................................................................................89
Managing Views......................................................................................89
Summary..................................................................................................90
Chapter 10: Built-in Functions.................................................................91
Numeric Functions..................................................................................91
Character Functions.................................................................................94
Datetime Functions..................................................................................98
NULL-related functions..........................................................................99
Summary................................................................................................102
Chapter 11: Transactions........................................................................103
Overview...............................................................................................103
SAVEPOINT.........................................................................................104
AutoCommit..........................................................................................105
Summary................................................................................................106
Chapter 12: Stored Routines...................................................................107
Row-by-row Processing........................................................................107
If-Then-Else Decision Logic.................................................................110
Exception Handling...............................................................................112
User-defined Functions..........................................................................112
Transactions...........................................................................................113
Summary................................................................................................114
Chapter 13: The Data Dictionary...........................................................115
The Schemata Table..............................................................................115
The Tables Table...................................................................................116
The Columns Table...............................................................................117
The Routines Table................................................................................118
Summary................................................................................................118
Appendix A: Installing MySQL Community Edition...........................119
Downloading MySQL Community Edition..........................................119
Installing MySQL Community Edition.................................................120
Appendix B: MySQL Built-in Data Types.............................................127
Appendix C: Indexes................................................................................129
Creating an Index...................................................................................129
Multi-Column Indexes...........................................................................130
Unique Indexes......................................................................................130
Partial Indexes.......................................................................................131
Primary Key Indexes ............................................................................131
Deleting An Index.................................................................................131
Index..........................................................................................................133
Introduction
Welcome to SQL for MySQL: A Beginner’s Tutorial. This book is for you if you want to learn SQL the easy way. SQL, which stands for Structured Query Language and is pronounced es-cue-el, is the standard language you use to interact with a relational database management system (RDBMS). This book uses the free edition of the MySQL database to show how SQL works.
SQL Overview
Initially developed at IBM in the early 1970s, SQL was formalized by the American National Standards Institute (ANSI) in 1986. Since then the SQL standard has been revised seven times. The examples in this book were tested using MySQL 5.6, which conforms to the SQL:2008 standard. This standard is one revision earlier than SQL:2011, the latest standard.
SQL consists of a data definition language (DDL) and a data manipulation language (DML). The DDL is used to create, delete, and alter the structure of a table and other database objects. The DML is used to insert, retrieve, and update data in a table or tables. Many database vendors implement a version of SQL that is not 100% compliant with the standard. They often add unique features to their SQL, resulting in an SQL dialect. For example, the following are some of the differences between MySQL and Oracle.
▪ The AS reserved word in the CREATE TABLE AS INSERT statement is optional in MySQL but mandatory in Oracle.
▪ A MySQL INSERT statement can insert multiple rows; an Oracle INSERT statement can only insert one row.
▪ MySQL only supports UNION set operator, whereas Oracle supports UNION, INTERSECT and MINUS set operators.
▪ MySQL’s stored routine equivalent in Oracle is its stored routine that you create using Oracle’s PL/SQL (Procedural Language extension to SQL). MySQL does not give a name to its procedural language extension that you use to create its stored routines. PL/SQL has much more features than for creating stored routines. Because of these dialects, SQL statements written for one RDBMS may not necessarily work in other RDBMS’s.
About This Book
This book consists of thirteen chapters and three appendixes. This section gives you an overview of each chapter and appendix.
Chapter 1, “Storing and Maintaining Data” starts the book by discussing how data is stored and maintained in a relational database. In this chapter you learn how to use SQL INSERT, UPDATE, and DELETE statements.
Chapter 2, “Basic Queries” explains how to construct queries using the SELECT statement.
Chapter 3, “Full-Text Search” explores the distinct MySQL full-text search features.
Chapter 4, “Query Output” shows how you can format query outputs beyond simply displaying columns of data from a database.
Chapter 5, “Grouping” explains what a group is, how to create a group, and how to apply aggregate functions to a group.
Chapter 6, “Joins” talks about the JOIN clause for querying data from multiple tables.
Chapter 7, “Subqueries” discusses the subquery. A subquery is a query that can be nested in another query.
Chapter 8, “Compound Queries” talks about set operators for combining the outputs of multiple queries.
create and store in a database
Chapter 10, “Built-in Functions” discusses some of the most commonly used built-in functions in the MySQL database.
Chapter 11, “Transaction” shows how to treat multiple SQL statements as a transaction set.
Chapter 12, “Stored Routines” introduces MySQL’s stored routines. Stored routines extend SQL.
Chapter 13, “The Data Dictionary” shows how to use the data dictionary, the metadata of a database, to find information about the database.
Appendix A, “Installing MySQL Community Edition” is a guide to installing MySQL Database Community Edition and making preparations for trying out the book examples.
Appendix B, “MySQL Built-in Data Types” provides a list of MySQL built-in data types.
Finally, Appendix C, “Indexing” covers the various indexing techniques available in the MySQL database.
Chapter 1
Storing and Maintaining Data
Data in a relational database (such as MySQL) is stored in tables. A very simple sales database, for example, might have four tables to store data on products, customers, suppliers, and customer orders.
When you add a record of data into a table, the record is stored as a row of the table. A record has fields. A product record, for example, might have four fields: product_code, name, price, and launch_date. All records you store in the product table must have the same fields. Each of the fields is a column of the table.
This chapter shows you how to use SQL statements to store and maintain data. The main objective of this chapter is to give you a taste of working with SQL. To test the book examples you need a working MySQL database.
Appendix A, “Installing MySQL Community Edition” shows how you can install MySQL Community Edition and make it ready for use with the examples. This appendix also shows you how to use the mysql commandline tool to interactively execute your SQL statements. If you do not have a working MySQL, you should read this appendix first.
