OReilly.Head.First.SQL.pdf

(49029 KB) Pobierz
O'Reilly - Head First SQL
373600099.004.png
Head First SQL
by Lynn Beighley
Copyright © 2007 O’Reilly Media, Inc. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly Media books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles ( safari.oreilly.com ). For more information, contact our corporate/institutional sales
department: (800) 998-9938 or corporate@oreilly.com .
Series Creators:
Kathy Sierra, Bert Bates
Series Editor:
Brett D. McLaughlin
Editor:
Catherine Nolan
Design Editor:
Louise Barr
Cover Designers:
Louise Barr, Karen Montgomery
Production Editor:
Sanders Kleinfeld
Indexer:
Julie Hawks
Page Viewer:
Andrew Fader
Printing History:
August 2007: First Edition.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. The Head First series designations,
Head First SQL , and related trade dress are trademarks of O’Reilly Media, Inc.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. The Head First series designations,
Head First series designations,
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a trademark
claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher and the authors assume no
responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.
No clowns, doughnuts, or Girl Sprouts were harmed in the making of this book. Just my car, but it’s been fixed.
TTM
This book uses RepKover ™ , a durable and fl exible lay-fl at binding.
ISBN-10: 0-596-52684-9
ISBN-13: 978-0-596-52684-9
[M]
373600099.005.png 373600099.006.png 373600099.007.png 373600099.001.png
table of contents
Table of Contents (Summary)
Intro
xxv
1
Data and Tables: A place for everything
1
2
The SELECT Statement: Gifted data retrieval
53
3
DELETE and UPDATE: A change will do you good
119
4
Smart Table Design: Why be normal?
159
5
ALTER: Rewriting the past
197
6
Advanced SELECT: Seeing your data with new eyes
235
7
Multi-table Database Design: Outgrowing your table
281
8
Joins and Multi-table Operations: Can’t we all just get along?
343
9
Subqueries: Queries Within Queries
379
10
Outer Joins, Self Joins, and Unions: New maneuvers
417
11
Constraints, Views, and Transactions: Too many cooks spoil the database
455
12
Security: Protecting your assets
493
Table of Contents (the real thing)
Intro
Your brain on SQL. Here you are trying to learn something, while here
your brain is doing you a favor by making sure the learning doesn’t stick . Your
brain’s thinking, “Better leave room for more important things, like which wild
animals to avoid and whether naked snowboarding is a bad idea.” So how do you
trick your brain into thinking that your life depends on knowing SQL?
Who is this book for?
xxvi
We know what you’re thinking
xxvii
Metacognition
xxix
Bend your brain into submission
xxxi
Read me
xxxii
The technical review team
xxxiv
Acknowledgments
xxxv
ix
table of contents
data and tables
1 Don’t you just hate losing things Whether it’s your car
keys, that 25% off coupon for Urban Outfitters, or your application’s
data, there’s nothing worse than not being able to keep up with what
you need ... when you need it. And when it comes to your applications,
there’s no better place to store your important information than in a
table . So turn the page, come on in, and take a walk through the world
of relational databases .
Defining your data
2
Look at your data in categories
7
What’s in a database?
8
Your database viewed through x-ray specs...
10
Databases contain connected data
12
Tables Up Close
13
Take command!
17
Setting the table: the CREATE TABLE statement
19
Creating a more complicated table
20
Look how easy it is to write SQL
21
Create the my_contacts table, finally
22
Your table is ready
23
Take a meeting with some data types
24
Your table, DESCribed
28
You can’t recreate an existing table or database!
30
Out with the old table, in with the new
32
To add data to your table, you’ll use the INSERT statement
34
Your database viewed
through x-ray specs...
Create the INSERT statement
37
Variations on an INSERT statement
41
Columns without values
42
Peek at your table with the SELECT statement
43
SQL Exposed: Confessions of a NULL
44
Controlling your inner NULL
45
NOT NULL appears in DESC
47
Fill in the blanks with DEFAULT
48
Your SQL Toolbox
50
x
A place for everything
373600099.002.png
table of contents
the SELECT statement
2
Gifted data retrieval
Is it really better to give than retrieve When it comes to
databases, chances are you’ll need to retrieve your data as often than
you’ll need to insert it. That’s where this chapter comes in: you’ll meet the
powerful SELECT statement and learn how to gain access to that important
information you’ve been putting in your tables. You’ll even learn how to
use WHERE , AND , and OR to selectively get to your data and even avoid
displaying the data that you don’t need.
Date or no date?
54
A better SELECT
57
What the * is that?
58
How to query your data types
64
More punctuation problems
65
Unmatched single quotes
66
Single quotes are special characters
67
INSERT data with single quotes in it
68
SELECT specific columns to limit results
73
SELECT specific columns for faster results
73
Combining your queries
80
Finding numeric values
83
Smooth Comparison Operators
86
Finding numeric data with Comparison Operators
88
Text data roping with Comparison Operators
91
To be OR not to be
93
The difference between AND and OR
96
I’m a star!
Use IS NULL to find NULLs
99
Saving time with a single keyword: LIKE
101
The call of the Wild(card)
101
Selecting ranges using AND and comparison operators
105
Just BETWEEN us… there’s a better way
106
After the dates, you are either IN...
109
... or you are NOT IN
110
More NOT
111
Your SQL Toolbox
116
xi
373600099.003.png

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin