book

Head First SQL

by Lynn Beighley

August 2007

Beginner to intermediate

608 pages

13h 12m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Who is this book for?Who should probably back away from this book?We know what you’re thinking.And we know what your brain is thinking.Metacognition: thinking about thinkingHere’s what WE didHere’s what YOU can do to bend your brain into submissionRead meThe technical review teamAcknowledgments
Defining your dataLook at your data in categoriesWhat’s in a database?Your database viewed through x-ray specs...Databases contain connected dataTake command!Setting the table: the CREATE TABLE statementCreating a more complicated tableLook how easy it is to write SQLCreate the my_contacts table, finallyYour table is readyTake a meeting with some data typesYour table, DESCribedYou can’t recreate an existing table or database!Out with the old table, in with the newTo add data to your table, you’ll use the INSERT statementCreate the INSERT statementVariations on an INSERT statementColumns without valuesPeek at your table with the SELECT statementControlling your inner NULLNOT NULL appears in DESCFill in the blanks with DEFAULTYour SQL Toolbox
Date or no date?Making contactA better SELECTWhat the * is that?How to query your data typesMore punctuation problemsUnmatched single quotesSingle quotes are special charactersINSERT data with single quotes in itHandle quotes with a backslashHandle quotes with an extra single quoteSELECT specific dataThe old waySELECT specific columns to limit resultsSELECT specific columns for faster resultsDoughnut ask what your table can do for you...Ask what you can do for your doughnutCombining your queriesFinding numeric valuesOnce is enoughSmooth Comparison OperatorsFinding numeric data with Comparison OperatorsText data roping with Comparison OperatorsSelecting your ingredientsTo be OR not to beThe difference between AND and ORUse IS NULL to find NULLsMeanwhile, back at Greg’s place...Saving time with a single keyword: LIKEThe call of the Wild(card)That’s more LIKE itSelecting ranges using AND and comparison operatorsJust BETWEEN us... there’s a better wayAfter the dates, you are either IN...... or you are NOT INMore NOTYour SQL Toolbox

Clowns are scaryClown trackingHow our clown data gets enteredBonzo, we’ve got a problemGetting rid of a record with DELETEUsing our new DELETE statementDELETE rulesThe INSERT-DELETE two stepBe careful with your DELETEThe trouble with imprecise DELETEChange your data with UPDATEUPDATE rulesUPDATE is the new INSERT-DELETEUPDATE in actionUPDATE your pricesAll we need is one UPDATEYour SQL Toolbox
Two fishy tablesA table is all about relationshipsAtomic data30 minutes or it’s freeLocation, location, locationAtomic data and your tablesReasons to be normalThe benefits of normal tablesClowns aren’t normalHalfway to 1NFPRIMARY KEY rulesGetting to NORMALFixing Greg’s tableThe CREATE TABLE we wroteShow me theTime-saving commandThe CREATE TABLE with a PRIMARY KEY1, 2, 3... auto incrementallyAdding a PRIMARY KEY to an existing tableALTER TABLE and add a PRIMARY KEYYour SQL Toolbox
We need to make some changesTable alteringExtreme table makeoverRenaming the tableWe need to make some plansRetooling our columnsStructural changesALTER and CHANGEChange two columns with one SQL statementQuick! DROP that columnA closer look at the non-atomic location columnLook for patternsA few handy string functionsTo SELECT the last two charactersTo SELECT everything in front of the commaUse a current column to fill a new columnHow our UPDATE and SET combo worksYour SQL Toolbox
Dataville Video is reorganizingProblems with our current tableMatching up existing dataPopulating the new columnThe order does matterUPDATE with a CASE expressionLooks like we have a problemTables can get messyWe need a way to organize the data we SELECTTry a little ORDER BYORDER a single columnORDER with two columnsORDER with multiple columnsAn orderly movie_tableand the ordered results from our query:Reverse the ORDER with DESCThe Girl Sprout® cookie sales leader problemSUM can add them for usSUM all of them at once with GROUP BYAVG with GROUP BYMIN and MAXCOUNT the daysSELECT DISTINCT valuesLIMIT the number of resultsLIMIT to just second placeYour SQL Toolbox
Finding Nigel a dateWhy change anything?The query worked really wellIt worked too wellIgnoring the problem isn’t the answerToo many bad matchesUse only the first interestA possible matchMis-matchedAdd more interest columnsStarting overAll is lost...... But waitThink outside of the single tableThe multi-table clown tracking databaseThe clown_tracking database schemaAn easier way to diagram your tablesHow to go from one table to twoLinking your tables in a diagramConnecting your tablesForeign key factsConstraining your foreign keyWhy bother with foreign keys?CREATE a table with a FOREIGN KEYRelationships between tablesPatterns of data: one-to-onePatterns of data: when to use one-to-one tablesWhen to use one-to-one tablesPatterns of data: one-to-manyPatterns of data: getting to many-to-manyPatterns of data: we need a junction tablePatterns of data: many-to-manyPatterns of data: fixing gregs_listNot in first normal formFinally in 1NFComposite keys use multiple columnsEven superheros can be dependentShorthand notationsSuperhero dependenciesPartial functional dependencyTransitive functional dependencySecond normal formWe might be 2NF already...Third normal form (at last)And so, Regis (and gregs_list) lived happily ever afterThe EndYour SQL Toolbox
Still repeating ourselves, still repeating...Prepopulate your tablesWe got the “table ain’t easy to normalize” bluesThe special interests (column)Keeping interestedUPDATE all your interestsGetting all the interestsMany paths to one placeCREATE, SELECT and INSERT at (nearly) the same timeCREATE, SELECT and INSERT at the same timeWhat’s up with that AS?Column aliasesTable aliases, who needs ’em?Everything you wanted to know about inner joinsCartesian joinReleasing your inner joinThe inner join in action: the equijoinThe inner join in action: the non-equijoinThe last inner join: the natural joinJoined-up queries?Your SQL Toolbox
Greg gets into the job recruiting businessGreg’s list gets more tablesGreg uses an inner joinBut he wants to try some other queriesSubqueriesWe combine the two into a query with a subquerySubquery rulesSubquery rulesA subquery construction walkthroughA subquery as a SELECT columnAnother example: Subquery with a natural joinA noncorrelated subqueryA noncorrelated subquery with multiple values: IN, NOT INCorrelated subqueriesA (useful) correlated subquery with NOT EXISTSEXISTS and NOT EXISTSGreg’s Recruiting Service is open for businessOn the way to the partyYour SQL Toolbox
Cleaning up old dataIt’s about left and rightHere’s a left outer joinThe results of the left outer joinOuter joins and multiple matchesThe right outer joinWhile you were outer joining...We could create a new tableHow the new table fits inA self-referencing foreign keyJoin the same table to itselfWe need a self-joinAnother way to get multi-table informationYou can use a UNIONUNION is limitedUNION rules in actionUNION ALLCreate a table from your unionINTERSECT and EXCEPTWe’re done with joins, time to move on to...Subqueries and joins comparedTurning a subquery into a joinA self-join as a subqueryGreg’s company is growingYour SQL Toolbox
Greg’s hired some helpJim’s first day: Inserting a new clientJim avoids a NULLFlash forward three monthsCHECK, please: Adding a CHECK CONSTRAINTCHECKing the genderFrank’s job gets tediousCreating a viewViewing your viewsWhat your view is actually doingWhat a view isInserting, updating, and deleting with viewsThe secret is to pretend a view is a real tableView with CHECK OPTIONYour view may be updatable if...When you’re finished with your viewWhen bad things happen to good databasesWhat happened inside the ATMMore trouble at the ATMIt’s not a dream, it’s a transactionThe classic ACID testSQL helps you manage your transactionsWhat should have happened inside the ATMHow to make transactions work with MySQLNow try it yourselfYour SQL Toolbox
User problemsAvoiding errors in the clown tracking databaseProtect the root user accountAdd a new userDecide exactly what the user needsA simple GRANT statementGRANT variationsREVOKE privilegesREVOKING a used GRANT OPTIONREVOKING with precisionThe problem with shared accountsUsing your roleRole droppingUsing your role WITH ADMIN OPTIONREVOKE role with CASCADEREVOKE role with RESTRICTCombining CREATE USER and GRANTGreg’s List has gone global!Your SQL ToolboxHow about a Greg’s List in your city?Use SQL on your own projects, and you too could be like Greg!
#1. Get a GUI for your RDBMSMySQL GUI toolsOther GUI tools#2. Reserved Words and Special CharactersSpecial CharactersReserved Words#3. ALL, ANY, and SOMEUsing ALLUsing ANYUsing SOME#4. More on Data TypesBOOLEANINTOther INT typesDATE and TIME types#5. Temporary tablesReasons you might want a temporary table:Create a temporary tableA temporary table shortcut#6. Cast your dataSome situations where you might want to use CAST()You can’t 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/MySQLA closer look at each line
Get started, fast!Instructions and TroubleshootingSteps to Install MySQL on WindowsDownload your installerPick a destination folderClick “Install” and you’re done!Steps to Install MySQL on Mac OS X
SymbolsABCDEFGILMNOPSTUVW

Content preview from Head First SQL

Chapter 12. Security: Protecting your assets

You’ve put an enormous amount of time and energy into creating your database. And you’d be devastated if anything happened to it. You’ve also had to give other people access to your data, and you’re worried that they might insert or update something incorrectly, or even worse, delete the wrong data. You’re about to learn how databases and the objects in them can be made more secure, and how you can have complete control over who can do what with your data.

User problems

Clown tracking took off in such a big way that the Dataville City Council had to employ a whole team of people to track clowns and add the data to the clown_tracking database.

Unfortunately the team was infiltrated by a clown disguised in ordinary clothes who went by the codename of “George.” He caused a number of problems in the database, including lost data, modified data, and nearly duplicate records that only exist because of his deliberate misspellings. Here are a few of the problems with the clown tracking database:

Snuggles, Snugles, and Snuggels all have rows in the clown_info table. We’re pretty sure they are all the same clown because the gender and description columns are the same (except for misspellings).

With those multiple entries ...