DBA 1: Introduction to Database Administration

(1)

DBA 1: Introduction to Database Administration

Lesso n 1: Int ro duct io n

Getting Started Lesso n 2: St o ring Dat a

Sto ring Data

Data and Data Types

What is NULL and NOT NULL, Anyway? Our First Table

Lesso n 3: Re lat io nships Be t we e n T able s Other Tables

Lesso n 4: INSERT , SELECT , UPDAT E, DELET E DESCRIBING Yo ur Tables Insert SELECT Update Delete Lesso n 5: T ransact io ns

Making Sure Yo ur Co mmands Behave ACID

Transactio n Iso latio n Levels Using Transactio ns

Co mmitting and ro lling back Lesso n 6 : J o ins

Co mbining Tables with a Jo in Other Jo ins

Left Jo ins

Full Outer Jo ins / Unio n

Lesso n 7: Aggre gat e s, Funct io ns, and Co ndit io nals Aggregating Data

Functio ns Co nditio nals

Lesso n 8 : Subque rie s and Vie ws Querying Queries

Views

Creating a View Restrictio ns o n Views Dro pping a View Lesso n 9 : St o re d Pro ce dure s

Using Sto red Pro cedures Mo tivatio ns

(2)

Creating Sto red Pro cedures Setup

Duplicating o ur View Parameters

Variables

Lesso n 10 : PIVOT and UNPIVOT PIVOTing Data

UNPIVOTing Data Lesso n 11: Full T e xt

Creating Full-Text Indexes Querying Full-Text Indexes

Lesso n 12: Inf o rm at io n Abo ut t he Dat abase INFORMATION_SCHEMA

TABLES COLUMNS VIEWS ROUTINES Lesso n 13: Final Pro je ct

Final Pro ject Specificatio n

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. See http://creativecommons.org/licenses/by-sa/3.0/legalcode for more information.

(3)

Introduction

Welco me to the O'Reilly DBA Series!

In this co urse, yo u will learn the basics to create a well-designed database using basic database co mmands. Yo u will also learn ho w to manipulate the data sto red in these tables and to return meaningful results to help analyze the data sto red.

Course Objectives

When yo u co mplete this co urse, yo u will be able to :

create, read, update, and delete data using basic SQL co mmands. perfo rm relatio nal jo ins and transactio ns o n SQL tables.

aggregate data thro ugh functio ns and co nditio nals. perfo rm subqueries and manipulate views.

create sto red pro cedures within SQL. summarize data using PIVOT and UNPIVOT.

demo nstrate kno wledge o f data indices and info rmatio n schemas. create a full-fledged blo g database.

Fro m beginning to end, yo u will learn by do ing pro jects in yo ur o wn Unix and MySQL enviro nments, and then handing them in fo r instructo r feedback. These pro jects, as well as the final pro ject—develo ping a co mplete database and demo nstrating administrative tasks—will add to yo ur po rtfo lio and will co ntribute to certificate co mpletio n. Besides a bro wser and internet co nnectio n, all so ftware is pro vided o nline by the O'Reilly Scho o l o f Techno lo gy.

Learning with O'Reilly School of Technology Courses

As with every O'Reilly Scho o l o f Techno lo gy co urse, we'll take a user-active appro ach to learning. This means that yo u (the user) will be active! Yo u'll learn by do ing, building live pro grams, testing them and experimenting with them— hands-o n!

To learn a new skill o r techno lo gy, yo u have to experiment. The mo re yo u experiment, the mo re yo u learn. Our system is designed to maximize experimentatio n and help yo u learn to learn a new skill.

We'll pro gram as much as po ssible to be sure that the principles sink in and stay with yo u.

Each time we discuss a new co ncept, yo u'll put it into co de and see what YOU can do with it. On o ccasio n we'll even give yo u co de that do esn't wo rk, so yo u can see co mmo n mistakes and ho w to reco ver fro m them. Making mistakes is actually ano ther go o d way to learn.

Abo ve all, we want to help yo u to learn to learn. We give yo u the to o ls to take co ntro l o f yo ur o wn learning experience. When yo u co mplete an OST co urse, yo u kno w the subject matter, and yo u kno w ho w to expand yo ur kno wledge, so yo u can handle changes like so ftware and o perating system updates.

Here are so me tips fo r using O'Reilly Scho o l o f Techno lo gy co urses effectively:

T ype t he co de . Resist the temptatio n to cut and paste the example co de we give yo u. Typing the co de actually gives yo u a feel fo r the pro gramming task. Then play aro und with the examples to find o ut what else yo u can make them do , and to check yo ur understanding. It's highly unlikely yo u'll break anything by

experimentatio n. If yo u do break so mething, that's an indicatio n to us that we need to impro ve o ur system! T ake yo ur t im e . Learning takes time. Rushing can have negative effects o n yo ur pro gress. Slo w do wn and let yo ur brain abso rb the new info rmatio n tho ro ughly. Taking yo ur time helps to maintain a relaxed, po sitive appro ach. It also gives yo u the chance to try new things and learn mo re than yo u o therwise wo uld if yo u blew thro ugh all o f the co ursewo rk to o quickly.

Expe rim e nt . Wander fro m the path o ften and explo re the po ssibilities. We can't anticipate all o f yo ur questio ns and ideas, so it's up to yo u to experiment and create o n yo ur o wn. Yo ur instructo r will help if yo u go co mpletely o ff the rails.

Acce pt guidance , but do n't de pe nd o n it . Try to so lve pro blems o n yo ur o wn. Go ing fro m misunderstanding to understanding is the best way to acquire a new skill. Part o f what yo u're learning is pro blem so lving. Of co urse, yo u can always co ntact yo ur instructo r fo r hints when yo u need them. Use all available re so urce s! In real-life pro blem-so lving, yo u aren't bo und by false limitatio ns; in OST co urses, yo u are free to use any reso urces at yo ur dispo sal to so lve pro blems yo u enco unter: the Internet,

(4)

co urses, yo u are free to use any reso urces at yo ur dispo sal to so lve pro blems yo u enco unter: the Internet, reference bo o ks, and o nline help are all fair game.

Have f un! Relax, keep practicing, and do n't be afraid to make mistakes! Yo ur instructo r will keep yo u at it until yo u've mastered the skill. We want yo u to get that satisfied, "I'm so co o l! I did it!" feeling. And yo u'll have so me pro jects to sho w o ff when yo u're do ne.

Lesson Format

We'll try o ut lo ts o f examples in each lesso n. We'll have yo u write co de, lo o k at co de, and edit existing co de. The co de will be presented in bo xes that will indicate what needs to be do ne to the co de inside.

Whenever yo u see white bo xes like the o ne belo w, yo u'll type the co ntents into the edito r windo w to try the example yo urself. The CODE TO TYPE bar o n to p o f the white bo x co ntains directio ns fo r yo u to fo llo w:

CODE TO TYPE:

White boxes like this contain code for you to try out (type into a file to run).

If you have already written some of the code, new code for you to add looks like this.

If we want you to remove existing code, the code to remove will look like this.

We may also include instructive comments that you don't need to type.

We may run pro grams and do so me o ther activities in a terminal sessio n in the o perating system o r o ther co mmand-line enviro nment. These will be sho wn like this:

INTERACTIVE SESSION:

The plain black text that we present in these INTERACTIVE boxes is

provided by the system (not for you to type). The commands we want you to type look lik e this.

Co de and info rmatio n presented in a gray OBSERVE bo x is fo r yo u to inspect and absorb. This info rmatio n is o ften co lo r-co ded, and fo llo wed by text explaining the co de in detail:

OBSERVE:

Gray "Observe" boxes like this contain information (usually code specifics) for you to observe.

The paragraph(s) that fo llo w may pro vide additio n details o n inf o rm at io n that was highlighted in the Observe bo x. We'll also set especially pertinent info rmatio n apart in "No te" bo xes:

Note

No tes pro vide info rmatio n that is useful, but no t abso lutely necessary fo r perfo rming the tasks at hand.

T ip

Tips pro vide info rmatio n that might help make the to o ls easier fo r yo u to use, such as sho rtcut keys.

WARNING

Warnings pro vide info rmatio n that can help prevent pro gram crashes and data lo ss.

The CodeRunner Screen

This co urse is presented in Co deRunner, OST's self-co ntained enviro nment. We'll discuss the details later, but here's a quick o verview o f the vario us areas o f the screen:

(5)

These video s explain ho w to use Co deRunner: File Management Demo

Co de Edito r Demo Co ursewo rk Demo

Getting Started

In this co urse, yo u will be creating SQL files. SQL files are made up o f SQL statements and have an .sql extensio n. Click the Ne w T e rm inal butto n to co nnect to Unix o r click the Co nne ct t o MySQL butto n to co nnect directly to the MySQL server. In this co urse we'll primarily use the Unix Terminal and no t the MySQL server directly. By do ing so we have so me additio nal co ntro l o ver ho w we co nnect to MySQL. Many o f the pro jects will be turned in as SQL files.

(6)

were given when yo u registered with OST, altho ugh the system may fill o ne o r bo th o f these in fo r yo u. If the system do esn't auto matically lo g yo u in, type yo ur username and passwo rd when pro mpted. (Yo u may need to click inside the Unix Terminal windo w to be able to type. When typing yo ur passwo rd, yo u will no t see any characters reflected back to yo u as yo u type.) Yo u will then be lo gged in to o ne o f the OST servers.

Once yo u see the UNIX pro mpt, it's time to co nnect to MySQL! Be sure to replace bo th o ccurrences o f username with yo ur o wn username.

Type the fo llo wing at the UNIX pro mpt:

cold1:~$ mysql -h sql -p -u username username

Yo ur screen will lo o k like this:

OBSERVE:

cold1:~$ mysql -h sql -p -u username username Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 135535

Server version: 5.1.69-log Source distribution

affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

To co nnect to a mysql database, we run the pro gram m ysql. To specify which database server we want to co nnect to , we type -h sql. We want to be pro mpted to pro vide a passwo rd, so we use the -p o ptio n. To specify o ur username, we use the -u username o ptio n. Finally, the database we want to use has the same name as o ur username, so we pro vide it again: username.

Befo re we are ready to use MySQL, let's type in a co mmand that tells MySQL to act in a "traditio nal" way. The co mmand will cause MySQL to act mo re like Po stgreSQL, Micro so ft SQL Server, and Oracle.

(7)

Type the fo llo wing at the MySQL pro mpt:

mysql> set sql_mode='traditional';

Query OK, 0 rows affected (0.00 sec) mysql>

When yo u are ready to leave mysql and return to the Unix pro mpt, type e xit as sho wn.

mysql> exit

Bye cold1:~$

Fo r mo st pro jects, yo u will select SQL fro m the Syntax menu in Co deRunner. If yo u are in the Unix Terminal, click o n the "untitled" tab next to the Terminal tab. No w, click the Ne w File butto n in the menu bar to create a new "untitled" tab.

Enter the fo llo wing co de as sho wn:

Type the fo llo wing into Co deRunner:

set sql_mode='traditional'; select now();

select 'hello world' as Message;

(8)

Note

When naming files and creating tables, please be sure to name them exactly as requested. The .sql extensio n is impo rtant fo r MySQL files, and in a Unix system, case is significant: dba1lesso n1pro ject1.sql and DBA1Lesso n1Pro ject1.sql wo uld be entirely different files, and Artists and artists wo uld be different tables. While file and table names are case sensitive, MySQL keywo rds, such as SET and SELECT , are no t, and can be entered in either upper o r lo wer case.

To o pen a file yo u have previo usly saved, click the Lo ad butto n and select the file fro m the Lo ad File windo w, o r do uble-click the file in the File Bro wser. No te that when yo u save a file, the wo rd "untitled" is replaced by the file name in the co de edito r tab. When yo u o pen a file, the file o pens in a new tab.

Test it in the Unix Terminal using the fo llo wing co mmand:

cold1:~$ mysql -t -h sql -p -u username username < dba1lesson1project1.sql

Enter password: +---+ | now() | +---+ | 2013-10-16 15:56:48 | +---+ +---+ | Message | +---+ | hello world | +---+ cold1:~$ OBSERVE:

(9)

The -t o ptio n tells MySQL to o utput yo ur queries in a nice table fo rmat. Be sure to replace username with yo ur specific username, and dba1le sso n1pro je ct 1.sql with yo ur pro ject filename.

Yo u've finished this lesso n! When yo u finish a lesso n, co mplete the ho mewo rk o bjectives and quizzes. To get started o n the first o bjective, click Obje ct ive 1 in the curriculum area in the upper left. Fo r this o bjective, yo u'll submit the dba1le sso n1pro je ct 1.sql file we created here.

(10)

Storing Data

Befo re we get started, make sure yo u are in the Unix Terminal and then co nnect to MySQL. Click the Ne w T e rm inal butto n and lo g in. Be sure to click in the windo w befo re yo u start typing. When yo u have lo gged in, co nnect to the MySQL server. Remember to replace username with yo ur actual username, and be sure to type yo ur username twice!

cold1:~$ mysql -h sql -p -u username username

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 778597

Server version: 5.0.41-community-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

Do n't fo rget—yo u also need to set the 'TRADITIONAL' mo de.

mysql> set sql_mode='traditional';

No w yo u're ready to go !

Databases excel at fo ur o peratio ns: Creating data, Reading data, Updating data, and Deleting data; we refer to these o peratio ns co llectively as CRUD. The mo st po pular databases use Structured Query Language (SQL) to perfo rm tho se o peratio ns.

Yo u canno t build a skyscraper witho ut blueprints, and yo u canno t sto re data in a database witho ut defining what type o f info rmatio n yo u wish to sto re, and ho w yo u want to sto re it. The subset o f SQL to create yo ur blueprints is called Data Definition Language (DDL).

Data and Data T ypes

Befo re yo u can build the walls o f a skyscraper, yo u must decide which material yo u will use to build yo ur walls. Database administrato rs must also cho o se the material with which to co nstruct their database. Let's wo rk with an example.

Suppo se yo u've just been hired by a used bo o k sto re. Yo ur jo b is to build a new database system fo r tracking bo o ks, amo ng o ther things.

Since we are interested in keeping track o f bo o ks, we need to determine the data that co mprises a bo o k. Mo st bo o ks have a title, an autho r, the date the bo o k was published, a co unt o f the number o f pages, and a price. So me bo o ks co ntain a brief descriptio n o f their co ntent, o thers do no t. Fo r example, Database Nation was written by Simso n Garfinkel in December 20 0 0 and currently co sts $16 .9 5. The descriptio n o f the bo o k reads:

"As the 21st century begins, advances in techno lo gy endanger o ur privacy in ways never befo re imagined. This newly revised update o f the po pular hardco ver editio n, 'Database Natio n: The Death o f Privacy in the 21st

Century,' is the co mpelling acco unt o f ho w invasive techno lo gies will affect o ur lives in the co ming years. It's a timely, far-reaching, entertaining, and tho ught-pro vo king lo o k at the serio us threats to privacy facing us

to day."

(11)

Database tables sto re info rmatio n in ro ws and co lumns. The co lumns are the definitio n (o r template) that each ro w must fo llo w. Fo r a table that sto res bo o k info rmatio n, we might have co lumns fo r T it le , Aut ho r, Dat e Publishe d, Num be r o f Page s, Price , and De script io n.

Each co lumn o f info rmatio n is o f a different type. Fo r example, the title and autho r are textual. The date published is exactly that: a date. The number o f pages is a no n-negative integer. The co st is a decimal, with two decimal places. The descriptio n, if o ne exists, is also textual.

As a database administrato r (DBA), yo ur jo b is to pick the smallest and mo st appro priate data type fo r the info rmatio n we want to sto re. Yo u co uld just sto re everything as a bunch o f wo rds. Fo r example, yo u co uld sto re the Date Published as 0 5 /10 /20 0 7 . If yo u saw this data, wo uld yo u be certain it was May 10 th, 20 0 7, o r co uld it be Octo ber 5th, 20 0 7? If yo u sto re it as a date, there wo uld be no questio n, because the database co uld return the date to yo u in any fo rm yo u like, even "May 10 th, 20 0 7." It co uld also do so me calculatio ns fo r yo u, like tell yo u that "o ne mo nth prio r to the publish date" was "April 10 th, 20 0 7."

Data types can be classified into three gro ups: text data types, numeric data types, and date/time data types. Here are so me o f the data types MySQL suppo rts:

Gro up Dat a T ype De script io n Sam ple

Text CHAR A fixed length o f zero to 255 characters. Useful fo r data like state_{co des, which are always two characters lo ng.} IL

Text VARCHAR

A variable length o f zero to 6 5535 characters. Useful fo r data who se length varies, but is always within a certain size range. Co mmo n examples are first o r last names.

ILLINOIS

Text TEXT A variable length o f up to 6 5535 characters. Illino is is a_{great state.}

Text MEDIUMTEXT A variable length o f up to 16 777215 characters.

This histo ry o f Illino is...

Text LONGTEXT A variable length o f up to 429 49 6 729 5 characters.

The lo nger histo ry o f Illino is...

Numeric INT Integer (who le) numbers. 5

(12)

Numeric DOUBLE An appro ximate decimal po int value sto red in 8 bytes. 7.25 Numeric DECIMAL An exact decimal po int value. The preferred data type fo r sto ring_{mo netary values such as prices o r salaries.} 2.25

Date DATE Year, mo nth and day. 20 0 9

-12-31

Date DATETIME Year, mo nth, day and time.

20 0 9 -12-31 14:25:0 0

Date TIME Time o nly. 14:25:0 0

Date YEAR Year o nly. Year(4) has a range o f 19 0 1-2155, year(2) has a range o f_{19 70 -20 6 9 .} 20 12

What is NULL and NOT NULL, Anyway?

In a database, yo u might have a co lumn that is o ptio nal, such as a seco nd address line fo r yo ur custo mers. Elsewhere yo u might want to sto re so me bit o f info rmatio n, like the list price (a decimal), which is also o ptio nal. Yo u co uld set the list price to be $-9 9 9 9 .9 9 and define that value as the "no list price" value, but there is a better way.

NULL is no thing. It is no value.

WARNING

Be careful with NULL—NULL is no t equal to "" o r 0 . It is not an empty string. It cannot beco mpared to so mething else. Ho wever, even tho ugh is no thing, yo u can tell if so mething IS NULL o r IS NOT NULL.

Our First Table

In o rder to interact quickly with data in o ur tables, we need to define the primary key co lumns o n o ur table. A primary key is the minimum set o f info rmatio n needed to uniquely identify a ro w in a database table. At first glance, the primary key fo r o ur bo o k table co uld be the title.

Dat abase Nat io n is o ne title; ho wever, the bo o k might co me in hardco ver and paperback editio ns, and there might so meday be ano ther bo o k with the same title. If o ur bo o k sto re sto cks bo th editio ns, o r the o ther bo o k with the same title, the title "Database Natio n" alo ne wo n't be eno ugh fo r us to distinguish between the different bo o ks. Thus we wo n't be able to use the title as o ur primary key.

Amo ng o ther aspects o f a bo o k that we might sto re in a database, the price, the autho r, the descriptio n, and the catego ry are all po tentially no t unique.

Yo u might decide to sho eho rn so me extra info rmatio n to the bo o k's title, perhaps creating two bo o ks: Database Natio n - Hard Co py

Database Natio n - Paperback

This type o f co mpro mise isn't a go o d idea. The title is Dat abase Nat io n, no t Dat abase Nat io n - Hard Co py. Our manager might step in at this po int, and tell us that everything in the sto re has a unique Pro duct Co de . This pro duct co de sho uld be o ur primary key, since it can be used to uniquely identify a ro w in o ur table.

We will use the Pro duct Co de as o ur primary key, add a co lumn called Cat e go ry, and fo rget abo ut the Autho r, the Date Published, and Number o f Pages co lumns fo r no w.

(13)

mysql> CREATE TABLE Products --> (

--> ProductCode char(20) NOT NULL, --> Title varchar(50) NOT NULL, --> Category varchar(30) NOT NULL, --> Description text,

--> Price DECIMAL(9,2) NOT NULL, --> PRIMARY KEY(ProductCode) --> ) ENGINE=INNODB;

Let's take a clo ser lo o k:

OBSERVE:

mysql> CREATE TABLE Products --> (

--> ProductCode char(20) NOT NULL, --> Title varchar(50) NOT NULL, --> Category varchar(30) NOT NULL, --> Description text,

--> Price DECIMAL(9,2) NOT NULL, --> PRIMARY KEY(ProductCode) --> ) ENGINE=INNODB;

The Pro duct s table is fairly small; it co ntains a Pro duct Co de used to uniquely identify a ro w, the Title o f the item, a Catego ry, an o ptio nal Descriptio n, and the Price. The NOT NULL keywo rds indicate which co lumns are required (canno t be null). We didn't specify "NOT NULL" fo r De script io n, so we do n't require a value in that co lumn. Finally, we specify o ur PRIMARY KEY as Pro duct Co de.

Can a primary key be null? Create a little test table to find o ut.

Note

In this co urse, we use the INNODB engine (ENGINE=INNODB) fo r mo st o f the tables we create. This_{engine allo ws us to use mo re standard SQL than the no rmal MySQL tables.}

Believe it o r no t, yo u just created a new table! In the next lesso n, yo u'll create additio nal tables, and see ho w all o f the tables relate to each o ther. See yo u there!

(14)

Relationships Between Tables

Other Tables

In the last lesso n, we learned ho w to create tables. One table is o kay, but we'll need a few additio nal tables to keep track o f custo mers, invento ry, and o rders.

In the last lesso n we decided to sto re the Pro ductCo de, Title, Catego ry, Descriptio n, and Price fo r all o f o ur bo o ks. Being a bo o k sto re, it wo uld be nice to track o ur custo mers and o rders so we kno w which bo o ks are selling well. We co uld add a new co lumn called "Custo mer" and a co lumn called "DatePurchased" to o ur bo o k table, but then we wo uld have to co py the Pro ductCo de, Title, Catego ry, Descriptio n and Price each time a bo o k was so ld! Also , we designed the Pro ducts table to co ntain pro ducts, and a custo mer isn't a pro duct!

A better so lutio n is to use a separate table fo r each gro up o f info rmatio n—a table fo r bo o ks, a table fo r custo mers, and a table fo r o rders. This way we do n't have a lo t o f duplicate info rmatio n in o ur tables, wasting space and po tentially causing co nfusio n.

MySQL, Po stgreSQL, Oracle, and SQL Server are all relational databases. They efficiently sto re info rmatio n in multiple tables that are linked to gether by relatio nships.

The first table we'll create will keep track o f custo mers. The data used to describe a custo mer sho uld include their first and last name, address, and email address. But suppo se yo u had two custo mers with the name "Jo hn Smith," o r a custo mer who shares his email address with his wife? Ho w wo uld yo u keep these individual entries separate? We need a primary key fo r o ur custo mer table, but we really do n't have any unique info rmatio n. Fo rtunately we can have the database create a unique co lumn fo r us. MySQL pro vides an AUT O_INCREMENT that will auto matically po pulate a co lumn with an increasing integer number. The value fo r the first ro w is 1, then the next ro w is 2, and so o n. It do es no t repeat numbers.

This type o f key is also kno wn as a surrogate key. We'll use a surro gate key fo r o ur custo mer table, and call it Cust o m e rID.

mysql> CREATE TABLE Customers -> (

-> CustomerID int AUTO_INCREMENT NOT NULL, -> FirstName varchar(50) NOT NULL,

-> LastName varchar(50) NOT NULL, -> AddressLine1 varchar(50) NOT NULL, -> AddressLine2 varchar(50),

-> City varchar(50) NOT NULL, -> State char(2),

-> PostalCode varchar(10), -> EmailAddress varchar(100), -> DateAdded DATETIME NOT NULL, -> PRIMARY KEY(CustomerID) -> ) ENGINE=INNODB;

Query OK, 0 rows affected (0.01 sec)

mysql>

(15)

Let's discuss so me impo rtant parts o f this co mmand:

OBSERVE:

mysql> CREATE TABLE Customers -> (

-> CustomerID int AUTO_INCREMENT NOT NULL, -> FirstName varchar(50) NOT NULL,

-> LastName varchar(50) NOT NULL, -> AddressLine1 varchar(50) NOT NULL, -> AddressLine2 varchar(50),

-> City varchar(50) NOT NULL, -> State char(2),

-> PostalCode varchar(10), -> EmailAddress varchar(100), -> DateAdded DATETIME NOT NULL, -> PRIMARY KEY(CustomerID) -> ) ENGINE=INNODB;

The f irst line t e lls t he dat abase se rve r t o cre at e a ne w t able nam e d Cust o m e rs.

This table co ntains o ur surro gate key co lumn named Cust o m e rID. No tice the use o f the mySQL-specific AUT O_INCREMENT keywo rd.

Note

Auto -Increment co lumns so metimes have gaps. Fo r instance, a table may have a 1 in the first ro w, then 5_{in the next ro w, depending o n whether ro ws are added o r remo ved fro m the table.}

Next, we defined the name co lumn. First Nam e will be a varchar with a maximum length o f 5 0 characters. This sho uld be a required value fo r all custo mers, so it is defined as NOT NULL. The co lumns fo r LastName and AddressLine1 are defined similarly.

Addre ssLine 2 is also a varchar o f maximum length 5 0 characters, but since so me peo ple do n't have a seco nd address line, yo u do n't need to include the NOT NULL keywo rds.

Dat e Adde d will sto re the date and time the custo mer is added to the table.

Finally, yo u let the database engine kno w that the PRIMARY KEY is the surro gate key, Cust o m e rID.

No w that yo u have the Custo mers table in place, let's create so me additio nal tables to help o rganize the bo o ksto re. We'll need to create tables to sto re Inve nt o ry, track Orde rs, and tell us what pro ducts make up an o rder

(16)

In the diagram, arro ws po int to the so urce o f the info rmatio n. In o ther wo rds, the Cust o m e rID in Orders has a co rrespo nding Cust o m e rID in Custo mers.

Here are the o ther tables we'll create:

T able Nam e Co lum ns

Invento ry Pro ductCo de, QuantityInSto ck

Orders OrderNumber, Custo merID, Invo iceNumber, DatePlaced, OrderStatus Order Line OrderNumber, Pro ductCo de, Quantity, ExtendedAmo unt

The Inve nt o ry table will keep track o f the number o f bo o ks in sto ck. No te that we uniquely identify the quantity in sto ck by Pro duct Co de , which relates back to the Pro ducts table. We need to include the Pro duct Co de in bo th tables in o rder to make this link. A Pro ductCo de o f "ABC123" in Pro ducts will co rrespo nd exactly to a Pro ductCo de o f "ABC123" in Invento ry.

(17)

mysql> CREATE TABLE Inventory (

ProductCode char(10) NOT NULL, QuantityInStock int NOT NULL, PRIMARY KEY(ProductCode) ) ENGINE=INNODB;

Next, create a table to ho ld Orde rs. This table will co ntain an auto -increment co lumn to uniquely identify each o rder.

mysql> CREATE TABLE Orders (

OrderNumber int AUTO_INCREMENT NOT NULL, CustomerID int NOT NULL,

InvoiceNumber varchar(15), DatePlaced DATETIME, OrderStatus char(10), PRIMARY KEY(OrderNumber) ) ENGINE=INNODB;

mysql>

Finally, the Orde rLine table will be used to reco rd the items purchased fo r each unique o rder.

mysql> CREATE TABLE OrderLine (

OrderNumber int NOT NULL, ProductCode char(10) NOT NULL, Quantity int NOT NULL,

ExtendedAmount DECIMAL(9, 2) NOT NULL, PRIMARY KEY(OrderNumber, ProductCode) ) ENGINE=INNODB;

mysql>

This table has so mething new:

OBSERVE:

PRIMARY KEY(OrderNumber, ProductCode)

The PRIMARY KEY is defined o n two co lumns. A PRIMARY KEY uniquely identifies a ro w in a table. Often the primary key is just o ne co lumn, such as a userid o r UPC co de. It can be multiple co lumns, ho wever.

(18)

Note

A key (primary o r o ther) that spans multiple co lumns is also kno wn as a composite key.

In o ur table the Orde rNum be r and Pro duct Co de to gether will uniquely identify a ro w.

Believe it o r no t... yo u just made fo ur new tables! In the next lesso n yo u will learn ho w to sto re data in these tables. See yo u there!

(19)

INSERT, SELECT, UPDATE, DELETE

DESCRIBING Your Tables

In the last lesso n yo u created tables that will sto re data fo r yo ur bo o ksto re. No w it's time to actually sto re so me data! Befo re we get started, let's examine the tables created in the last lesso n. MySQL has a DESCRIBE keywo rd that can be used to do just that.

Note

Befo re yo u get started, make sure yo u are co nnected to MySQL, and have set the 'traditio nal' mo de.

mysql> DESCRIBE Customers;

DESCRIBE returns a lo t o f useful info rmatio n: the co lumn names, the data types (and sizes), whether the co lumn can be NULL, any default values, and whether the co lumn is auto _increment.

Insert

To add data to a table, use the INSERT keywo rd.

mysql> INSERT INTO Customers

-> (FirstName, LastName, AddressLine1, City, State, PostalCode, DateAdded, EmailAdd ress)

-> VALUES ('John', 'Smith', '123 4th Street', 'Chicago', 'IL', '60606', NOW(), 'joh [email protected]');

Query OK, 1 row affected (0.01 sec)

Co ngratulatio ns, yo u just added a ro w o f data into yo ur table! Let's lo o k clo ser.

OBSERVE:

->(FirstName, LastName, AddressLine1, City, State, PostalCode, DateAdded, EmailAddr ess)

->VALUES ('John', 'Smith', '123 4th Street', 'Chicago', 'IL', '60606', NOW(), 'john @hotmail.com');

The syntax fo r INSERT is fairly co mpact. First, yo u specify the target table fo r yo ur data—in this case, Cust o m e rs. Next, yo u list the specific co lumns fo r which yo u will pro vide data. There are co mmas between each co lumn name, and

(20)

they are enclo sed within pare nt he se s ().

Note

Yo u do n't have to pro vide data fo r all co lumns—o nly the required (no n-null) co lumns.

Next, yo u specify the VALUES fo r each co lumn. Values that will be sto red in text co lumns must have single quo tatio n marks (') aro und them.

The DateAdded co lumn in the Custo mers table must be the date, right now. Fo rtunately, mySQL pro vides a NOW() functio n that returns the date and time in the pro per fo rmat fo r use in o ur table.

Note

It's o kay to change the o rder o f yo ur co lumns—see ho w DateAdded is befo re EmailAddress? Just_{remember to pro vide yo ur VALUES}_{in the same o rder yo u specified the co lum ns.}

No w let's insert ano ther reco rd.

-> (FirstName, LastName, DateAdded, EmailAddress)

-> VALUES ('Jane', 'Adams', NOW(), '[email protected]');

ERROR 1364 (HY000): Field 'AddressLine1' doesn't have a default value mysql>

While this erro r message may seem strange, everything is wo rking perfectly. When we created the table, we defined the co lumn "AddressLine1" to be NOT NULL. We didn't pro vide a value fo r AddressLine1 in o ur insert statement, the database set it to NULL by default, resulting in this erro r.

WARNING

If yo u see so mething like Que ry OK, 1 ro w af f e ct e d, 2 warnings (0 .0 0 se c), yo u'll need to go back to the start o f the lesso n and make sure yo u typed the SET sql_m o de ='T RADIT IONAL'; co mmand. Yo u sho uld also remo ve everything fro m Custo mers and start again—to do this, type DELET E FROM Cust o m e rs; at the MySQL pro mpt.

Fix yo ur INSERT statement by pro viding the AddressLine1 and City:

-> (FirstName, LastName, DateAdded, EmailAddress, AddressLine1, City)

-> VALUES ('Jane', 'Adams', NOW(), '[email protected]', '300 N. Michigan', 'Chicago' );

Query OK, 1 row affected (0.01 sec) mysql>

Add mo re custo mers—try adding o ne with an AddressLine2.

SELECT

INSERTing data into tables is fine, but chances are yo u want to retrieve info rmatio n as well. In SQL, the keywo rd to get info rmatio n o ut o f a table is SELECT .

(21)

mysql> SELECT * FROM Customers;

+---+---+---+---+---+---+---+---+---+---+

+---+---+---+---+---+---+---+---+---+---+

+---+---+---+---+---+---+---+---+---+---+

2 rows in set (0.00 sec) mysql>

Note

The Custo merID values yo u see pro bably wo n't match yo ur o wn Custo merIDs, because the co lumn is_{an auto increment co lumn.}

Yo ur o utput may no t lo o k to o nice—in fact it might be pretty ugly! That's because MySQL may have to wrap the ro ws to fit yo ur screen. As yo u learn mo re abo ut MySQL, yo u'll disco ver o ther ways to present the info rmatio n. Fo r example, try SELECT * FROM Cust o m e rs\G. We wo n't get into this in detail here, but fo r an idea o f what's available, see the MySQL develo per do cumentatio n.

In a SELECT statement (as in many co mputer co mmands), the asterisk (*) means "all co lumns." Thus the SQL statement yo u entered co uld be translated to English as "retrieve data fro m all co lumns, fro m the table called Custo mers."

Of co urse, the list returned fro m this SELECT statement is pretty lo ng, so let's o mit the address info rmatio n fo r no w.

mysql> SELECT CustomerID, FirstName, LastName, EmailAddress FROM Customers;

mysql>

OBSERVE:

This time, MySQL o nly retrieves the co lumns yo u specified (Cust o m e rID, First Nam e , Last Nam e , and Em ailAddre ss).

What if yo u o nly want to retrieve custo mers fro m Illino is? The SELECT statement can include a WHERE clause that enables yo u to filter yo ur ro ws.

(22)

mysql> SELECT CustomerID, FirstName, LastName, EmailAddress, State FROM Customers

-> WHERE State='IL';

mysql>

OBSERVE:

mysql> SELECT CustomerID, FirstName, LastName, EmailAddress, State FROM Customers -> WHERE State='IL';

The results o nly include ro ws WHERE the St at e is e qual t o 'IL.'

This time Ms. Adams isn't included because we didn't specify a state fo r her. We kno w Chicago is in Illino is, but SQL do esn't!

What if yo u o nly want to see yo ur custo mers fro m Illino is who have a gmail address? Remember that email addresses at gmail end with gmail.co m.

mysql> SELECT CustomerID, FirstName, LastName, EmailAddress, State FROM Customers

-> WHERE State='IL' AND EmailAddress LIKE '%gmail.com'; Empty set (0.00 sec)

mysql>

We do n't have any custo mers in Illino is with gmail acco unts, so no ro ws were returned. This query sho ws so mething new.

OBSERVE:

mysql> SELECT CustomerID, FirstName, LastName, EmailAddress, State FROM Customers WHERE State='IL' AND EmailAddress LIKE '%gmail.com';

AND is a co njunctio n that co nnects the two queries, to return o nly custo mers in Illino is AND with email addresses at gmail.

Note

Yo u can also use OR in the WHERE clause. Other co mpariso ns yo u can do include IS NULL, IS NOT_{NULL, != (no t equals), and < o r >.}

LIKE do esn't do an exact match o n a co lumn. The special character % means "anything" to SQL, so Em ailAddre ss LIKE '% gm ail.co m ' co uld be translated to English as "EmailAddress that ends with gmail.co m."

Update

No w yo u have data in yo ur tables, but at so me po int this data might change. Suppo se, fo r example, that yo ur custo mer Jo hn Smith tells yo u that his email address has changed. It used to be 'jo hn@ho tmail.co m,' but no w it's

'jo [email protected] m.' Let's UPDAT E his ro w in the Custo mers table.

In the fo llo wing example, be sure to change the Custo merID fro m id to whatever Custo merID yo ur individual Jo hn Smith was given.

(23)

mysql> UPDATE Customers

-> SET EmailAddress='[email protected]'

-> WHERE CustomerID=id;

Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>

OBSERVE:

-> SET EmailAddress='[email protected]' -> WHERE CustomerID=id;

UPDATE statements have two impo rtant parts: the co lum n updat e s, and the WHERE clause. The co lum n updat e s specify the new values fo r the given co lumns. The WHERE clause tells the database which ro ws in the database yo u want to update.

Let's check to make sure the data was updated.

mysql>

Yes, it has been updated.

The WHERE clause is no t required, but if yo u do n't pro vide o ne, every ro w in the database will be updated. If yo u want to update o nly o ne ro w, yo u'll need to specify eno ugh co nditio ns in yo ur WHERE clause to limit yo ur change to that o ne ro w.

T ip

If yo u're no t sure ho w to limit yo ur UPDATE statement co rrectly, write it as a SELECT statement first. If yo urSELECT statement returns the intended ro ws, chances are yo ur UPDATE statement will o nly change the intended ro ws as well.

Yo u can change multiple co lumns as well. Befo re we do , let's write a SELECT statement to make sure o ur WHERE clause is co rrect. Once again, be sure to change the Custo merID fro m id to whatever Custo merID yo ur individual Jo hn Smith was given.

(24)

Type the fo llo wing at the MySQL pro mpt: mysql> SELECT * -> FROM Customers -> WHERE CustomerID=21; +---+---+---+---+---+---+---+ ---+---+---+

+---+---+---+---+---+---+---+ ---+---+---+

+---+---+---+---+---+---+---+ ---+---+---+

1 row in set (0.00 sec) mysql>

One ro w was returned, with the co rrect custo mer.

No w that we're co nfident that we've written the co rrect WHERE clause, let's rewrite the SELECT statement as an UPDATE statement.

-> SET EmailAddress='[email protected]', City='Evanston'

Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>

Yo ur results sho w that o ne ro w was matched, and o ne ro w was changed.

If yo u need additio nal co nfirmatio n that the change to o k place, write ano ther SELECT query to check fo r yo urself!

Delete

No w that yo u have data in yo ur tables and have altered that data, ho w do yo u get rid o f it? Perhaps yo ur custo mer Ms. Adams wants to be remo ved fro m yo ur system. o f 31. Do uble-check her Custo merID with SELECT and replace id in the co mmand with her Custo merID fro m yo ur system.

mysql> DELETE FROM Customers

(25)

mysql>

Yep, it's go ne!

Take a mo ment to reflect o n the wo rk yo u've just do ne. Yo u added data to yo ur tables, retrieved it, filtered it, updated so me ro ws, and deleted so me o ld reco rds. In the next lab yo u'll learn ho w to keep yo ur data secure and co nsistent thro ugh the use o f transactio ns. See yo u there!

(26)

Transactions

Making Sure Your Commands Behave

Welco me back! In the last lesso n yo u learned ho w to sto re and retrieve data fro m yo ur database. Yo u also learned to create tables in yo ur database to track custo mers, invento ry, and sales. In this lesso n we'll discuss the steps yo u can take to make sure the data in yo ur database is always co rrect and up-to -date, even if many peo ple are wo rking with the data at the same time.

Fo r this lesso n, yo u'll need to have two Unix terminals o pen at the same time. The two sessio ns will be o pen in different tabs. To tell them apart, use the po sitio n o f the tabs, and the tabs' names ("Terminal1" and "Terminal2," o r similar). Lo g into MySQL and be sure to set the 'traditio nal' mo de in each Unix terminal! When yo u switch to a new terminal yo u will need to click in it befo re yo u can type in it.

Let's go back to o ur bo o ksto re. When a bo o k is so ld, several things need to happen: 1. Check pro duct invento ry to make sure the item is in sto ck.

2. Create an o rder.

3. Add the pro duct to the o rder item table.

4. Update invento ry to reflect the new quantity in sto ck.

5. Check the invento ry level again so the sales staff is alerted when sto ck gets lo w.

But what happens if two peo ple try to purchase the same bo o k at the same time? Who gets the bo o k? What happens if yo u add invento ry to the database when so meo ne purchases a bo o k?

In the database wo rld, transactions are used to gro up related co mmands so all co mmands execute o r all co mmands do no t execute. There is no in-between.

ACID

Databases must maintain fo ur pro perties: At o m icit y, Co nsist e ncy, Iso lat io n, and Durabilit y. To help yo u remember this, use the acro nym ACID.

(27)

Co nsist e ncy: Databases o nly sto re data that is co nsistent with the rules defined fo r sto ring data. Fo r instance, if an invento ry table isn't allo wed to have negative quantities, then no ro ws will be allo wed to have negative quantities.

Iso lat io n: Transactio ns are independent o f the o utside wo rld. No o utside change can cause an unexpected change within o ur transactio n. When making a sale, no o ther sales can change the quantity in invento ry until this sale has been co mpleted.

(28)

(29)

All databases handle Ato micity, Co nsistency, and Durability internally and auto matically. The Iso latio n pro perty depends o n the user—it's usually set to a secure level by default, but depending o n yo ur requirements, yo u can raise o r lo wer it.

Note

MySQL is a unique database because it do esn't always suppo rt ACID pro perties. Depending o nthe versio n o f MySQL being used and the way yo ur database is set up, transactio ns may no t secure yo ur data. To ensure MySQL will secure yo ur data, always use the Inno DB engine.

Transaction Isolation Levels

There are fo ur primary transactio n iso latio n levels suppo rted by databases.

The first is called read uncommitted. It is the fastest, but least safe level. Transactio ns run with this iso latio n level are no t guaranteed to o ccur independently o f o ther transactio ns. This means that o ne sale transactio n is allo wed to "dirty read" the results o f o ther inco mplete sale transactio ns. (A "dirty read" is when the database gives yo u data that has no t yet been co mmitted to disk.) This iso latio n level is o nly used in certain circumstances where yo u do n't necessarily care if the data is inco rrect.

The next transactio n level, called read committed, is slightly safer than read unco mmitted. Dirty reads are no t po ssible under this iso latio n level. If yo u run a query at the beginning o f yo ur transactio n, and run the same query at the end o f the transactio n, the results wo n't be the same if ano ther transactio n begins and co mmits in the middle o f yo ur transactio n.

Repeatable read is usually the default transactio n iso latio n level. In this iso latio n level, yo u canno t get dirty reads, and no o ther transactio ns can change ro ws during yo ur transactio n.

The highest level o f safety, and the slo west transactio n level, is serializable. Under this iso latio n level, each 'read' and 'write' to the database o ccurs in sequence. This iso latio n level can cause perfo rmance issues, since yo ur transactio n might have to wait fo r so meo ne else's transactio n to co mplete.

Using Transactions

Befo re yo u query the database, yo u sho uld specify yo ur transactio n iso latio n level. In MySQL this is do ne via the SET co mmand.

(30)

Type the fo llo wing at the MySQL pro mpt in the first Unix terminal:

mysql> SET transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)

mysql>

This co mmand do esn't return any ro ws.

Mo st database servers allo w yo u to pro cess many SQL statements to gether as a single batch. In MySQL yo u begin a batch with the ST ART T RANSACT ION statement, and end it with COMMIT , o r undo it with ROLLBACK. As always, yo u must end each individual SQL statement with a semico lo n (;).

Let's create a simple transactio n.

Type the fo llo wing at the MySQL pro mpt in first Unix terminal:

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec) mysql> SELECT LastName FROM Customers;

+---+ | LastName | +---+ | Smith | +---+

1 row in set (0.01 sec) mysql> COMMIT;

Note

It's o kay if the results returned fro m yo ur query are different fro m this example.

OBSERVE:

mysql> SELECT LastName FROM Customers; mysql> COMMIT;

First, yo u ST ART T RANSACT ION. In this batch yo u are o nly executing o ne statement, to return the co unt fro m the Cust o m e rs table. Ho wever, yo u co uld do additio nal wo rk in yo ur batch by adding mo re SQL statements. Finally, yo u end yo ur batch with COMMIT ;.

Of co urse, this example o nly selected data fro m the database—it didn't update o r add data. What happens when yo u use a transactio n with an INSERT statement? To find o ut, yo u'll use bo th Unix terminals.

(31)

Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Customers

-> VALUES ('John', 'Doe', '123 4th Street', 'Chicago', 'IL', '60606', NOW(), 'johnd [email protected]');

Next, o pen a seco nd Unix terminal sessio n, start mysql, and set sql_mo de='traditio nal'.

Type the fo llo wing at the MySQL pro mpt in the seco nd Unix terminal:

mysql> SELECT FirstName, LastName FROM Customers;

Yo ur table may have mo re ro ws than the table sho wn abo ve, but yo u sho uld see that J o hn Do e is no t there. If yo ur transactio n iso latio n level was set co rrectly, yo u won't see any ro ws fo r Jo hn Do e. This is the co rrect result, since we set the re ad co m m it t e d iso latio n level befo re we started the batch.

Note

If yo u do see Jo hn Do e, yo u pro bably missed the step where yo u entered SET t ransact io n iso lat io nle ve l re ad co m m it t e d; o n the first terminal. Switch back to the first terminal, delete Jo hn Do e, and try again fro m the beginning.

No w switch back to the first Unix terminal, where yo u typed in the INSERT statement.

mysql> COMMIT;

Switch back to the seco nd Unix terminal, where yo u typed in the SELECT statement and run it again (yo u can use the up arro w o n yo ur keybo ard to repeat the co mmand).

(32)

There is o ne o ther way to end yo ur batch—yo u can undo yo ur changes with the ROLLBACK statement. Make sure yo u are in the first Unix terminal.

Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Customers

-> VALUES ('Becky', 'Stein', '123 4th Street', 'Chicago', 'IL', '60606', NOW(), 'bs [email protected]');

Query OK, 1 row affected (0.01 sec) mysql> ROLLBACK;

No w, switch to the seco nd Unix terminal and repeat the SELECT:

Yo ur o utput may be different fro m the abo ve, but since yo u rolled back the transactio n, yo u do n't see any ro ws fo r Becky Stein.

Committing and rolling back

Yo u might be asking, "so when do I use COMMIT and ROLLBACK?"

Transactio ns are usually used in applicatio ns o r in sto red pro cedures. Yo ur applicatio n will begin a

transactio n, then execute a query. If that query was successful, the applicatio n executes the next query. If any query fails, the transactio n is ro lled back. If all queries succeed, the transactio n is co mmitted.

(33)

pro blem. The database do es the hard wo rk fo r yo u!

Yo u've learned a very po werful way to make sure yo ur data stays secure. In the next lesso n yo u'll learn ho w to co mbine data fro m all o f yo ur tables in po werful and meaningful ways. Se e yo u t he n!

(34)

Joins

Combining Tables with a Join

In this lesso n, yo u'll learn ho w to co mbine data fro m many tables into a single query.

Wo uldn't it be nice to be able to write a query that wo uld allo w yo u to see the current invento ry o f the bo o k sto re? Until no w the o nly way yo u had to find this info rmatio n was by writing two queries: o ne to list the pro ducts in the sto re, and ano ther to list the quantities in the sto re.

Befo re we begin this lesso n, let's clear the pro ducts and invento ry table and start fresh. We haven't "o fficially" added any reco rds to these tables, but if yo u've added any, yo u can delete them using the DELET E keywo rd yo u learned abo ut earlier.

mysql> DELETE FROM Inventory;

Query OK, 15 rows affected (0.07 sec) mysql> DELETE FROM Products;

No w that o ur tables are empty, let's create so me new data. O'Reilly has a very nice bo o k catalo g, so let's bo rro w a few descriptio ns fro m that. We'll do wnlo ad an SQL file to save us so me time. This text file co ntains many INSERT

statements that we'll submit to the database server, instead o f typing each and every INSERT co mmand o urselves. First, get back to the Unix pro mpt.

mysql> exit;

Bye cold1:~$

Next, we'll grab the file fro m O'Reilly's servers using the curl co mmand.

Type the fo llo wing at the Unix pro mpt:

cold1:~$ curl -L https://courses.oreillyschool.com/dba1/downloads/products.sql > produc ts.sql

% Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 9520 100 9520 0 0 53816 0 --:--:-- --:--:-- --:--:-- 178k cold1:~$

Once that file has do wnlo aded, we'll use the m ysql co mmand to impo rt the pro ducts (replace username with yo ur username). If the data is impo rted successfully, yo u'll see no results.

Type the fo llo wing at the Unix pro mpt:

cold1:~$ mysql -h sql -p -u username username < products.sql

Enter password: cold1:~$

(35)

mysql> INSERT INTO Inventory values ('artofsql', 52);

mysql> INSERT INTO Inventory values ('databaseid', 0);

mysql> INSERT INTO Inventory values ('mysqlspp', 5);

mysql> INSERT INTO Inventory values ('sqlhks', 32);

mysql> INSERT INTO Inventory values ('sqltuning', 105);

To co mbine two tables in o ne query, yo u use a J OIN. A jo in between the Pro ducts table and Invento ry table will match every ro w in Pro ducts with every ro w in Invento ry. Let's try it o ut!

(36)

mysql> SELECT * FROM Products JOIN Inventory;

(37)

---+---+---+---+ | databaseid | Database in Depth | Database Theory |This book sheds l ight on the principles behind the relational model, which is fundamental to all databas e-backed applications--and, consequently, most of the work that goes on in the computin g world today. Database in Depth: The Relational Model for Practitioners goes beyond th e hype and gets to the heart of how relational databases actually work. Ideal for exper ienced database developers and designers, this concise guide gives you a clear view of the technology--a view that's not influenced by any vendor or product. Featuring an ext ensive set of exercises, it will help you: * understand why and how the relational mode l is still directly relevant to modern database technology (and will remain so for the foreseeable future) * see why and how the SQL standard is seriously deficient * use the best current theoretical knowledge in the design of their databases and database appli cations * make informed decisions in their daily database professional activities Datab ase in Depth will appeal not only to database developers and designers, but also to a d iverse field of professionals and academics, including database administrators (DBAs), information modelers, database consultants, and more. Virtually everyone who deals with relational databases should have at least a passing understanding of the fundamentals of working with relational models. Author C.J. Date has been involved with the relation al model from its earliest days. An exceptionally clear-thinking writer, Date lays out principle and theory in a manner that is easily understood. Few others can speak as aut horitatively the topic of relational databases as Date can. | 29.95 | artofsql | 52 |

| sqlhks | SQL Hacks | SQL |Whether you're ru nning Access, MySQL, SQL Server, Oracle, or PostgreSQL, this book will help you push th e limits of traditional SQL to squeeze data effectively from your database. The book of fers 100 hacks -- unique tips and tools -- that bring you the knowledge of experts who apply what they know in the real world to help you take full advantage of the expressiv e power of SQL. You'll find practical techniques to address complex data manipulation p roblems. Learn how to: * Wrangle data in the most efficient way possible * Aggregate an d organize your data for meaningful and accurate reporting * Make the most of subquerie s, joins, and unions * Stay on top of the performance of your queries and the server th at runs them * Avoid common SQL security pitfalls, including the dreaded SQL injection attack Let SQL Hacks serve as your toolbox for digging up and manipulating data. If you love to tinker and optimize, SQL is the perfect technology and SQL Hacks is the must-h ave book for you. | 29.99 | artofsql | 52 |

| artofsql | The Art of SQL | Database Theory |For all the buzz about trendy IT techniques, data processing is still at the core of our systems, especi ally now that enterprises all over the world are confronted with exploding volumes of d ata. Database performance has become a major headache, and most IT departments believe that developers should provide simple SQL code to solve immediate problems and let DBAs tune any "bad SQL" later. In The Art of SQL, author and SQL expert Stephane Faroult ar gues that this "safe approach" only leads to disaster. His insightful book, named after Art of War by Sun Tzu, contends that writing quick inefficient code is sweeping the di rt under the rug. SQL code may run for 5 to 10 years, surviving several major releases of the database management system and on several generations of hardware. The code must be fast and sound from the start, and that requires a firm understanding of SQL and re lational theory. The Art of SQL offers best practices that teach experienced SQL users to focus on strategy rather than specifics. Faroult's approach takes a page from Sun Tz u's classic treatise by viewing database design as a military campaign. You need knowle dge, skills, and talent. Talent can't be taught, but every strategist from Sun Tzu to m odern-day generals believed that it can be nurtured through the experience of others. T hey passed on their experience acquired in the field through basic principles that serv ed as guiding stars amid the sound and fury of battle. This is what Faroult does with S QL. Like a successful battle plan, good architectural choices are based on contingencie s. What if the volume of this or that table increases unexpectedly? What if, following a merger, the number of users doubles? What if you want to keep several years of data o nline? Faroult's way of looking at SQL performance may be unconventional and unique, bu

(38)

t he's deadly serious about writing good SQL and using SQL well. The Art of SQL is not a cookbook, listing problems and giving recipes. The aim is to get you-and your manager -to raise good questions. | 44.99 | artofsql | 52 |

| sqltuning | SQL Tuning | Database Theory |A poorly performi ng database application not only costs users time, but also has an impact on other appl ications running on the same computer or the same network. SQL Tuning provides an essen tial next step for SQL developers and database administrators who want to extend their SQL tuning expertise and get the most from their database applications. There are two b asic issues to focus on when tuning SQL: how to find and interpret the execution plan o f an SQL statement and how to change SQL to get a specific alternate execution plan. SQ L Tuning provides answers to these questions and addresses a third issue that's even mo re important: how to find the optimal execution plan for the query to use. Author Dan T ow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database pla tform being used. You'll learn how to understand and control SQL execution plans and ho w to diagram SQL queries to deduce the best execution plan for a query. Key chapters in the book include exercises to reinforce the concepts you've learned. SQL Tuning conclu des by addressing special concerns and unique solutions to "unsolvable problems." Wheth er you are a programmer who develops SQL-based applications or a database administrator or other who troubleshoots poorly tuned applications, SQL Tuning will arm you with a r eliable and deterministic method for tuning your SQL queries to gain optimal performanc e. | 39.95 | artofsql | 52 |

| mysqlspp | MySQL Stored Procedure Programming | MySQL |The implementatio n of stored procedures in MySQL 5.0 a huge milestone -- one that is expected to lead to widespread enterprise adoption of the already extremely popular MySQL database. If you are serious about building the web-based database applications of the future, you need to get up to speed quickly on how stored procedures work -- and how to build them the right way. This book, destined to be the bible of stored procedure development, is a re source that no real MySQL programmer can afford to do without. In the decade since MySQ L burst on the scene, it has become the dominant open source database, with capabilitie s and performance rivaling those of commercial RDBMS offerings like Oracle and SQL Serv er. Along with Linux and PHP, MySQL is at the heart of millions of applications. And no w, with support for stored procedures, functions, and triggers in MySQL 5.0, MySQL offe rs the programming power needed for true enterprise use. MySQL's new procedural languag e has a straightforward syntax, making it easy to write simple programs. But it's not s o easy to write secure, easily maintained, high-performance, and bug-free programs. Few in the MySQL world have substantial experience yet with stored procedures, but Guy Har rison and Steven Feuerstein have decades of combined expertise. In MySQL Stored Procedu re Programming, they put that hard-won experience to good use. Packed with code example s and covering everything from language basics to application building to advanced tuni ng and best practices, this highly readable book is the one-stop guide to MySQL develop ment. It consists of four major sections: * MySQL stored programming fundamentals -- tu torial, basic statements, SQL in stored programs, and error handling * Building MySQL s tored programs -- transaction handling, built-in functions, stored functions, and trigg ers * MySQL stored programs in applications -- using stored programs with PHP, Java, Pe rl, Python, and .NET (C# and VB.NET) * Optimizing MySQL stored programs -- security, ba sic and advanced SQL tuning, optimizing stored program code, and programming best pract ices A companion web site contains many thousands of lines of code, that you can put to use immediately. Guy Harrison is Chief Architect of Database Solutions at Quest Softwa re and a frequent speaker and writer on MySQL topics. Steven Feuerstein is the author o f Oracle PL/SQL Programming, the classic reference for Oracle stored programming for mo re than ten years. Both have decades of experience as database developers, and between them they have authored a dozen books. | 44.99 | artofsql | 52 |

| databaseid | Database in Depth | Database Theory |This book sheds l ight on the principles behind the relational model, which is fundamental to all databas e-backed applications--and, consequently, most of the work that goes on in the computin g world today. Database in Depth: The Relational Model for Practitioners goes beyond th e hype and gets to the heart of how relational databases actually work. Ideal for exper ienced database developers and designers, this concise guide gives you a clear view of the technology--a view that's not influenced by any vendor or product. Featuring an ext ensive set of exercises, it will help you: * understand why and how the relational mode l is still directly relevant to modern database technology (and will remain so for the foreseeable future) * see why and how the SQL standard is seriously deficient * use the best current theoretical knowledge in the design of their databases and database appli cations * make informed decisions in their daily database professional activities Datab ase in Depth will appeal not only to database developers and designers, but also to a d iverse field of professionals and academics, including database administrators (DBAs), information modelers, database consultants, and more. Virtually everyone who deals with