book

Learning SQL

by Alan Beaulieu

August 2005

Beginner

312 pages

8h 23m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Why Learn SQL?Why Use This Book to Do It?Structure of This BookConventions Used in This BookHow to Contact UsUsing Code ExamplesSafari EnabledAcknowledgments
1.1. Introduction to Databases1.1.1. Nonrelational Database Systems1.1.2. The Relational Model1.1.3. Some Terminology1.2. What Is SQL?1.2.1. SQL Statement Classes1.2.2. SQL: A Nonprocedural Language1.2.3. SQL Examples1.3. What Is MySQL?1.4. What's in Store
2.1. Creating a MySQL Database2.2. Using the mysql Command-Line Tool2.3. MySQL Data Types2.3.1. Character Data2.3.1.1. Character sets2.3.1.2. Text data2.3.2. Numeric Data2.3.3. Temporal Data2.4. Table Creation2.4.1. Step 1: Design2.4.2. Step 2: Refinement2.4.3. Step 3: Building SQL Schema Statements2.5. Populating and Modifying Tables2.5.1. Inserting Data2.5.1.1. Generating numeric key data2.5.1.2. The insert statement2.5.2. Updating Data2.5.3. Deleting Data2.6. When Good Statements Go Bad2.6.1. Nonunique Primary Key2.6.2. Nonexistent Foreign Key2.6.3. Column Value Violations2.6.4. Invalid Date Conversions2.7. The Bank Schema
3.1. Query Mechanics3.2. Query Clauses3.3. The select Clause3.3.1. Column Aliases3.3.2. Removing Duplicates3.4. The from Clause3.4.1. Tables3.4.1.1. Subquery-generated tables3.4.1.2. Views3.4.2. Table Links3.4.3. Defining Table Aliases3.5. The where Clause3.6. The group by and having Clauses3.7. The order by Clause3.7.1. Ascending Versus Descending Sort Order3.7.2. Sorting via Expressions3.7.3. Sorting via Numeric Placeholders3.8. Exercises3.8.1. 3-13.8.2. 3-23.8.3. 3-33.8.4. 3-4
4.1. Condition Evaluation4.1.1. Using Parentheses4.1.2. Using the not Operator4.2. Building a Condition4.3. Condition Types4.3.1. Equality Conditions4.3.1.1. Inequality conditions4.3.1.2. Data modification using equality conditions4.3.2. Range Conditions4.3.2.1. The between operator4.3.2.2. String ranges4.3.3. Membership Conditions4.3.3.1. Using subqueries4.3.3.2. Using not in4.3.4. Matching Conditions4.3.4.1. Using wildcards4.3.4.2. Using regular expressions4.4. NULL: That Four-Letter Word4.5. Exercises4.5.1. 4-14.5.2. 4-24.5.3. 4-34.5.4. 4-4
5.1. What Is a Join?5.1.1. Cartesian Product5.1.2. Inner Joins5.1.3. The ANSI Join Syntax5.2. Joining Three or More Tables5.2.1. Using Subqueries as Tables5.2.2. Using the Same Table Twice5.3. Self-Joins5.4. Equi-Joins Versus Non-Equi-Joins5.5. Join Conditions Versus Filter Conditions5.6. Exercises5.6.1. 5-15.6.2. 5-25.6.3. 5-3
6.1. Set Theory Primer6.2. Set Theory in Practice6.3. Set Operators6.3.1. The union Operator6.3.2. The intersect Operator6.3.3. The except Operator6.4. Set Operation Rules6.4.1. Sorting Compound Query Results6.4.2. Set Operation Precedence6.5. Exercises6.5.1. 6-16.5.2. 6-26.5.3. 6-3

7.1. Working with String Data7.1.1. String Generation7.1.1.1. Including single quotes7.1.1.2. Including special characters7.1.2. String Manipulation7.1.2.1. String functions that return numbers7.1.2.2. String functions that return strings7.2. Working with Numeric Data7.2.1. Performing Arithmetic Functions7.2.2. Controlling Number Precision7.2.3. Handling Signed Data7.3. Working with Temporal Data7.3.1. Dealing with Time Zones7.3.2. Temporal Data Generation7.3.2.1. String representations of temporal data7.3.2.2. String-to-date conversions7.3.2.3. Functions for generating dates7.3.3. Temporal Data Manipulation7.3.3.1. Temporal functions that return dates7.3.3.2. Temporal functions that return strings7.3.3.3. Temporal functions that return numbers7.4. Conversion Functions7.5. Exercises7.5.1. 7-17.5.2. 7-27.5.3. 7-3
8.1. Grouping Concepts8.2. Aggregate Functions8.2.1. Implicit Versus Explicit Groups8.2.2. Counting Distinct Values8.2.3. Using Expressions8.2.4. How Nulls Are Handled8.3. Generating Groups8.3.1. Single-Column Grouping8.3.2. Multi-Column Grouping8.3.3. Grouping via Expressions8.3.4. Generating Rollups8.4. Group Filter Conditions8.5. Exercises8.5.1. 8-18.5.2. 8-28.5.3. 8-38.6. 8-4 (Extra Credit)
9.1. What Is a Subquery?9.2. Subquery Types9.3. Noncorrelated Subqueries9.3.1. Multiple-Row, Single-Column Subqueries9.3.1.1. The in operator9.3.1.2. The all operator9.3.1.3. The any operator9.3.2. Multicolumn Subqueries9.4. Correlated Subqueries9.4.1. The Exists Operator9.4.2. Data Manipulation Using Correlated Subqueries9.5. When to Use Subqueries9.5.1. Subqueries as Data Sources9.5.1.1. Table fabrication9.5.1.2. Task-oriented subqueries9.5.2. Subqueries in Filter Conditions9.5.3. Subqueries as Expression Generators9.6. Subquery Wrap-up9.7. Exercises9.7.1. 9-19.7.2. 9-29.7.3. 9-39.7.4. 9-4
10.1. Outer Joins10.1.1. Left Versus Right Outer Joins10.1.2. Three-Way Outer Joins10.1.3. Self Outer Joins10.2. Cross Joins10.3. Natural Joins10.4. Exercises10.4.1. 10-110.4.2. 10-210.4.3. 10-310.4.4. 10-4 (Extra Credit)
11.1. What Is Conditional Logic?11.2. The Case Expression11.2.1. Searched Case Expressions11.2.2. Simple Case Expressions11.3. Case Expression Examples11.3.1. Result Set Transformations11.3.2. Selective Aggregation11.3.3. Checking for Existence11.3.4. Division by Zero Errors11.3.5. Conditional Updates11.3.6. Handling Null Values11.4. Exercises11.4.1. 11-111.4.2. 11-2
12.1. Multiuser Databases12.2. What Is a Transaction?12.2.1. Starting a Transaction12.2.2. Ending a Transaction12.2.3. Transaction Savepoints
13.1. Indexes13.1.1. Index Creation13.1.1.1. Unique indexes13.1.1.2. Multi-column indexes13.1.2. Types of Indexes13.1.2.1. B-tree indexes13.1.2.2. Bitmap indexes13.1.2.3. Text indexes13.1.3. How Indexes Are Used13.1.4. The Downside of Indexes13.2. Constraints13.2.1. Constraint Creation13.2.2. Constraints and Indexes13.2.3. Cascading Constraints
B.1. Select ExtensionsB.1.1. The limit ClauseB.1.1.1. Combining the limit clause with the order by clauseB.1.1.2. limit's optional second parameterB.1.1.3. Ranking queriesB.1.2. The into outfile ClauseB.2. Combination Insert/Update StatementsB.3. Ordered Updates and DeletesB.4. Multitable Updates and Deletes
C.1. Chapter 3C.1.1. 3-1C.1.2. 3-2C.1.3. 3-3C.1.4. 3-4C.2. Chapter 4C.2.1. 4-1C.2.2. 4-2C.2.3. 4-3C.2.4. 4-4C.3. Chapter 5C.3.1. 5-1C.3.2. 5-2C.3.3. 5-3C.4. Chapter 6C.4.1. 6-1C.4.2. 6-2C.4.3. 6-3C.5. Chapter 7C.5.1. 7-1C.5.2. 7-2C.5.3. 7-3C.6. Chapter 8C.6.1. 8-1C.6.2. 8-2C.6.3. 8-3C.7. 8-4 (Extra Credit)C.8. Chapter 9C.8.1. 9-1C.8.2. 9-2C.8.3. 9-3C.8.4. 9-4C.9. Chapter 10C.9.1. 10-1C.9.2. 10-2C.9.3. 10-3C.9.4. 10-4 (Extra Credit)C.10. Chapter 11C.10.1. 11-1C.10.2. 11-2
D.1. Advanced SQLD.2. Database ProgrammingD.3. Database DesignD.4. Database TuningD.5. Database AdministrationD.6. Report Generation

Content preview from Learning SQL

Chapter 2. Creating and Populating a Database

This chapter will provide you with the information you need to create your first database and to create the tables and associated data used for the examples in this book. You will also learn about various data types and see how to create tables using them. Because the examples in this book are executed against a MySQL database, this chapter is somewhat skewed toward MySQL's features and syntax, but most concepts are applicable to any server.

Creating a MySQL Database

If you already have a MySQL database server available for your use, you can start with item number 8 in the instructions below. Keep in mind, however, that this book assumes that you are using MySQL Version 4.1.11 or later, so you may want to consider upgrading your server or installing another server if you are using an earlier release.

The following instructions will show you the minimum steps required to install a MySQL server on a Windows computer, create a database, and load the sample data for this book:

Download the MySQL Database Server (Version 4.1.7 or later) from http://dev.mysql.com. Unless you are planning to use the server for more than just a training tool, you should download the Essentials Package, which includes only the commonly used tools, instead of the Complete Package.
Launch the installation by double-clicking on the downloaded file.
Install the server using the "typical install." The installation should be quick and painless, but feel free to consult the ...