Head First SQL. Your Brain on SQL -- A Learner's Guide

Spis treści książki

• Head First SQL
• Dedication
• SPECIAL OFFER: Upgrade this ebook with OReilly
• A Note Regarding Supplemental Files
• Advance Praise for Head First SQL
• Praise for other Head First books
• Praise for the Head First Approach
• Author of Head First SQL
• How to use this Book: Intro
  • Who is this book for?
  • Who should probably back away from this book?
  • We know what youre thinking.
  • And we know what your brain is thinking.
  • Metacognition: thinking about thinking
  • Heres what WE did
  • Heres what YOU can do to bend your brain into submission
  • Read me
  • The technical review team
  • Acknowledgments
• 1. Data and Tables: A place for everything
  • Defining your data
  • Look at your data in categories
  • Whats in a database?
  • Your database viewed through x-ray specs...
  • Databases contain connected data
  • Take command!
  • Setting the table: the CREATE TABLE statement
  • Creating a more complicated table
  • Look how easy it is to write SQL
  • Create the my_contacts table, finally
  • Your table is ready
  • Take a meeting with some data types
  • Your table, DESCribed
  • You cant recreate an existing table or database!
  • Out with the old table, in with the new
  • To add data to your table, youll use the INSERT statement
  • Create the INSERT statement
  • Variations on an INSERT statement
  • Columns without values
  • Peek at your table with the SELECT statement
  • Controlling your inner NULL
  • NOT NULL appears in DESC
  • Fill in the blanks with DEFAULT
  • Your SQL Toolbox
• 2. The SELECT Statement: Gifted data retrieval
  • Date or no date?
  • Making contact
  • A better SELECT
  • What the * is that?
  • How to query your data types
  • More punctuation problems
  • Unmatched single quotes
  • Single quotes are special characters
  • INSERT data with single quotes in it
    • Handle quotes with a backslash
    • Handle quotes with an extra single quote
  • SELECT specific data
  • The old way
  • SELECT specific columns to limit results
  • SELECT specific columns for faster results
  • Doughnut ask what your table can do for you...
  • Ask what you can do for your doughnut
  • Combining your queries
  • Finding numeric values
  • Once is enough
  • Smooth Comparison Operators
  • Finding numeric data with Comparison Operators
  • Text data roping with Comparison Operators
  • Selecting your ingredients
  • To be OR not to be
  • The difference between AND and OR
  • Use IS NULL to find NULLs
  • Meanwhile, back at Gregs place...
  • Saving time with a single keyword: LIKE
  • The call of the Wild(card)
  • Thats more LIKE it
    • Selecting ranges using AND and comparison operators
  • Just BETWEEN us... theres a better way
  • After the dates, you are either IN...
  • ... or you are NOT IN
  • More NOT
  • Your SQL Toolbox
• 3. DELETE and UPDATE: A change will do you good
  • Clowns are scary
  • Clown tracking
  • How our clown data gets entered
  • Bonzo, weve got a problem
  • Getting rid of a record with DELETE
  • Using our new DELETE statement
  • DELETE rules
  • The INSERT-DELETE two step
  • Be careful with your DELETE
  • The trouble with imprecise DELETE
  • Change your data with UPDATE
  • UPDATE rules
  • UPDATE is the new INSERT-DELETE
  • UPDATE in action
  • UPDATE your prices
  • All we need is one UPDATE
  • Your SQL Toolbox
• 4. Smart Table Design: Why be normal?
  • Two fishy tables
  • A table is all about relationships
  • Atomic data
    • 30 minutes or its free
    • Location, location, location
  • Atomic data and your tables
  • Reasons to be normal
  • The benefits of normal tables
  • Clowns arent normal
  • Halfway to 1NF
  • PRIMARY KEY rules
  • Getting to NORMAL
  • Fixing Gregs table
  • The CREATE TABLE we wrote
  • Show me the
  • Time-saving command
  • The CREATE TABLE with a PRIMARY KEY
  • 1, 2, 3... auto incrementally
  • Adding a PRIMARY KEY to an existing table
  • ALTER TABLE and add a PRIMARY KEY
  • Your SQL Toolbox
• 5. Alter: Rewriting the Past
  • We need to make some changes
  • Table altering
  • Extreme table makeover
  • Renaming the table
  • We need to make some plans
  • Retooling our columns
  • Structural changes
  • ALTER and CHANGE
  • Change two columns with one SQL statement
  • Quick! DROP that column
  • A closer look at the non-atomic location column
  • Look for patterns
  • A few handy string functions
    • To SELECT the last two characters
    • To SELECT everything in front of the comma
  • Use a current column to fill a new column
  • How our UPDATE and SET combo works
  • Your SQL Toolbox
• 6. Advanced Select: Seeing your data with new eyes
  • Dataville Video is reorganizing
  • Problems with our current table
  • Matching up existing data
  • Populating the new column
    • The order does matter
  • UPDATE with a CASE expression
  • Looks like we have a problem
  • Tables can get messy
  • We need a way to organize the data we SELECT
  • Try a little ORDER BY
  • ORDER a single column
  • ORDER with two columns
  • ORDER with multiple columns
  • An orderly movie_table
    • and the ordered results from our query:
  • Reverse the ORDER with DESC
  • The Girl Sprout cookie sales leader problem
  • SUM can add them for us
  • SUM all of them at once with GROUP BY
  • AVG with GROUP BY
  • MIN and MAX
  • COUNT the days
  • SELECT DISTINCT values
  • LIMIT the number of results
  • LIMIT to just second place
  • Your SQL Toolbox
• 7. Multi-Table Database Design: Outgrowing your table
  • Finding Nigel a date
  • Why change anything?
  • The query worked really well
  • It worked too well
  • Ignoring the problem isnt the answer
  • Too many bad matches
  • Use only the first interest
  • A possible match
  • Mis-matched
  • Add more interest columns
  • Starting over
  • All is lost...
  • ... But wait
  • Think outside of the single table
  • The multi-table clown tracking database
  • The clown_tracking database schema
  • An easier way to diagram your tables
  • How to go from one table to two
  • Linking your tables in a diagram
  • Connecting your tables
  • Foreign key facts
  • Constraining your foreign key
  • Why bother with foreign keys?
  • CREATE a table with a FOREIGN KEY
  • Relationships between tables
  • Patterns of data: one-to-one
  • Patterns of data: when to use one-to-one tables
    • When to use one-to-one tables
  • Patterns of data: one-to-many
  • Patterns of data: getting to many-to-many
  • Patterns of data: we need a junction table
  • Patterns of data: many-to-many
  • Patterns of data: fixing gregs_list
  • Not in first normal form
  • Finally in 1NF
  • Composite keys use multiple columns
  • Even superheros can be dependent
  • Shorthand notations
  • Superhero dependencies
  • Partial functional dependency
  • Transitive functional dependency
  • Second normal form
  • We might be 2NF already...
  • Third normal form (at last)
  • And so, Regis (and gregs_list) lived happily ever after
  • The End
  • Your SQL Toolbox
• 8. Joins and Multi-Table Operations: Cant we all just get along?
  • Still repeating ourselves, still repeating...
  • Prepopulate your tables
  • We got the table aint easy to normalize blues
  • The special interests (column)
  • Keeping interested
  • UPDATE all your interests
  • Getting all the interests
  • Many paths to one place
  • CREATE, SELECT and INSERT at (nearly) the same time
  • CREATE, SELECT and INSERT at the same time
  • Whats up with that AS?
  • Column aliases
  • Table aliases, who needs em?
  • Everything you wanted to know about inner joins
  • Cartesian join
  • Releasing your inner join
  • The inner join in action: the equijoin
  • The inner join in action: the non-equijoin
  • The last inner join: the natural join
  • Joined-up queries?
  • Your SQL Toolbox
• 9. Subqueries: Queries within queries
  • Greg gets into the job recruiting business
  • Gregs list gets more tables
  • Greg uses an inner join
  • But he wants to try some other queries
  • Subqueries
  • We combine the two into a query with a subquery
  • Subquery rules
  • Subquery rules
  • A subquery construction walkthrough
  • A subquery as a SELECT column
  • Another example: Subquery with a natural join
  • A noncorrelated subquery
  • A noncorrelated subquery with multiple values: IN, NOT IN
  • Correlated subqueries
  • A (useful) correlated subquery with NOT EXISTS
  • EXISTS and NOT EXISTS
  • Gregs Recruiting Service is open for business
  • On the way to the party
  • Your SQL Toolbox
• 10. Outer Joins, Self-Joins, and Unions: New maneuvers
  • Cleaning up old data
  • Its about left and right
  • Heres a left outer join
    • The results of the left outer join
  • Outer joins and multiple matches
  • The right outer join
  • While you were outer joining...
  • We could create a new table
  • How the new table fits in
  • A self-referencing foreign key
  • Join the same table to itself
  • We need a self-join
  • Another way to get multi-table information
  • You can use a UNION
  • UNION is limited
  • UNION rules in action
  • UNION ALL
  • Create a table from your union
  • INTERSECT and EXCEPT
  • Were done with joins, time to move on to...
  • Subqueries and joins compared
  • Turning a subquery into a join
  • A self-join as a subquery
  • Gregs company is growing
  • Your SQL Toolbox
• 11. Constraints, Views, and Transactions: Too many cooks spoil the database
  • Gregs hired some help
  • Jims first day: Inserting a new client
  • Jim avoids a NULL
  • Flash forward three months
  • CHECK, please: Adding a CHECK CONSTRAINT
  • CHECKing the gender
  • Franks job gets tedious
  • Creating a view
  • Viewing your views
  • What your view is actually doing
  • What a view is
  • Inserting, updating, and deleting with views
  • The secret is to pretend a view is a real table
  • View with CHECK OPTION
  • Your view may be updatable if...
  • When youre finished with your view
  • When bad things happen to good databases
  • What happened inside the ATM
  • More trouble at the ATM
  • Its not a dream, its a transaction
  • The classic ACID test
  • SQL helps you manage your transactions
  • What should have happened inside the ATM
  • How to make transactions work with MySQL
  • Now try it yourself
  • Your SQL Toolbox
• 12. Security: Protecting your assets
  • User problems
  • Avoiding errors in the clown tracking database
  • Protect the root user account
  • Add a new user
  • Decide exactly what the user needs
  • A simple GRANT statement
  • GRANT variations
  • REVOKE privileges
  • REVOKING a used GRANT OPTION
  • REVOKING with precision
  • The problem with shared accounts
  • Using your role
  • Role dropping
  • Using your role WITH ADMIN OPTION
    • REVOKE role with CASCADE
    • REVOKE role with RESTRICT
  • Combining CREATE USER and GRANT
  • Gregs List has gone global!
  • Your SQL Toolbox
  • How about a Gregs List in your city?
  • Use SQL on your own projects, and you too could be like Greg!
• A. Leftovers: The Top Ten Topics (we didnt cover)
  • #1. Get a GUI for your RDBMS
    • MySQL GUI tools
    • Other GUI tools
  • #2. Reserved Words and Special Characters
    • Special Characters
    • Reserved Words
  • #3. ALL, ANY, and SOME
    • Using ALL
    • Using ANY
    • Using SOME
  • #4. More on Data Types
    • BOOLEAN
    • INT
    • Other INT types
    • DATE and TIME types
  • #5. Temporary tables
    • Reasons you might want a temporary table:
    • Create a temporary table
    • A temporary table shortcut
  • #6. Cast your data
    • Some situations where you might want to use CAST()
    • You cant use CAST() in these situations
  • #7. Who are you? What time is it?
  • #8. Useful numeric functions
  • #9. Indexing to speed things up
  • #10. 2-minute PHP/MySQL
    • A closer look at each line
• B. MySQL Installation: Try it out for yourself
  • Get started, fast!
  • Instructions and Troubleshooting
  • Steps to Install MySQL on Windows
    • Download your installer
    • Pick a destination folder
    • Click Install and youre done!
  • Steps to Install MySQL on Mac OS X
• C. Tools Roundup: All your new SQL tools
• D.  
  • Symbols
  • A
  • B
  • C
  • D
  • E
  • F
  • G
  • I
  • L
  • M
  • N
  • O
  • P
  • S
  • T
  • U
  • V
  • W
• Index
• About the Author
• SPECIAL OFFER: Upgrade this ebook with OReilly
• Copyright

pokaż cały spis treści

ukryj recenzje