Transcript MySQL+Part+II - Negative Karma Engine

MySQL Part II
IDIA 618
Fall 2014
Bridget M. Blodgett
Indexing
• For very small relational databases looking up
information directly is quick and easy
– But production level databases can quickly
become large enough to create delays while
searching
• An index uses a small amount of memory and
HD space in order to speed searching
Index Types
• There are three different index types in MySQL:
– INDEX
– PRIMARY KEY
– FULLTEXT
• Determining what type to use and where to apply
it
– ALTER TABLE classics ADD INDEX
(author(20));
– CREATE INDEX author ON classics
(author(20));
Primary Keys
• Primary keys are unique identifiers for each
row in a table
• They can be added after a table is created
– ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;
• But this only works for an unpopulated table
– Use another column (or create a new one) that
will have unique data for each entry and feed it
data for the key
FULLTEXT
• Saves each words of the data string in an
index that can be searched using natural
language
– Still only works for small tables
– Only works for Char, Varchar, Text columns
• ALTER TABLE classics ADD FULLTEXT(author,title);
Querying
• After creating and deleting databases the
most important thing you will need to do is
query them
• Basic format is:
– SELECT author, title FROM classics;
• This can then be expanded to refine the
information you are receiving back
SELECT Modifications
• SELECT COUNT
– Returns the number of rows that match that result
– SELECT COUNT(*) FROM classics;
• SELECT DISTINCT
– Will only display rows that match the search
criteria just once
– SELECT author FROM classics;
– SELECT DISTINCT author FROM classics;
WHERE & LIKE
• Where allows you to narrow down the results
that you get based upon certain qualifiers
– SELECT author,title FROM classics WHERE
author="Mark Twain";
• This requires specific knowledge of what you
need to search for
• Like allows for vague searches using a wildcard
keyword
– SELECT author,title FROM classics WHERE author
LIKE "Charles%";
Activity
Sample Table: empinfo
first
last
id
age
city
state
John
Jones
99980
45
Payson
Arizona
Mary
Jones
99982
25
Payson
Arizona
Eric
Edwards
88232
32
San Diego
California
Mary Ann
Edwards
88233
32
Phoenix
Arizona
Ginger
Howell
98002
42
Cottonwood Arizona
Sebastian
Smith
92001
23
Gila Bend
Arizona
Gus
Gray
22322
35
Bagdad
Arizona
Mary Ann
May
32326
52
Tucson
Arizona
Erica
Williams
32327
60
Show Low
Arizona
Leroy
Brown
32380
22
Pinetop
Arizona
Elroy
Cleaver
32382
22
Globe
Arizona
Activity
• Display all columns for everyone that is over
40 years old.
• Create a select query that finds all people with
the last name “Jones”
• Create a select query that finds people with
first names that start with “Er”
• Create a select query that finds people with
last names ending in “s”
LIMIT
• Limit can be used to make sure that only a
certain number of rows are returned in
response to a query
– Using just one identifier means that it will start at
the beginning and return that number. Giving it
two will skip the first number of responses
– Offsets begin like arrays (at 0)
• SELECT author,title FROM classics LIMIT 3;
• SELECT author,title FROM classics LIMIT 1,2;
MATCH…AGAINST
• Only usable on columns with a FULLTEXT index
• Allows you to do searches like in an Internet
search engine (using normal language)
– There are certain words that will be ignored by this
modifier (and, or, not) and return an empty set
• WHERE MATCH(author,title) AGAINST('old shop');
SELECT author,title FROM classics
• In Boolean mode adding a + requires a word and
– bars it from being in the result
• SELECT author,title FROM classics WHERE
MATCH(author,title) AGAINST('"origin of"' IN
BOOLEAN MODE);
UPDATE…SET
• Update set allows for the contents within a
search set to be updated
• Makes sure that only the rows you want
changed will be
• UPDATE classics SET author='Mark Twain (Samuel
Langhorne Clemens)' WHERE author='Mark Twain';
ORDER BY
• Order by is a sort feature that you can apply to
one or more columns
• Allows for both ascending and descending
with ascending being the default
• SELECT author,title FROM classics ORDER BY
author;
• SELECT author,title FROM classics ORDER BY
title DESC;
• SELECT author,title,year FROM classics ORDER
BY author,year DESC;
GROUP BY
• Also orders the results of a search by groups
them according to another category in the
table
• Useful when you want to see how the data
relates to other information in the table
• SELECT category,COUNT(author) FROM classics GROUP
BY category;
customers
customerid
firstname
lastname
city
state
10101
10298
10299
10315
10325
10329
10330
10338
10339
10408
10410
10413
10419
10429
10438
10439
10449
John
Leroy
Elroy
Lisa
Ginger
Kelly
Shawn
Michael
Anthony
Elroy
Mary Ann
Donald
Linda
Sarah
Kevin
Conrad
Isabela
Gray
Brown
Keller
Jones
Schultz
Mendoza
Dalton
Howell
Sanchez
Cleaver
Howell
Davids
Sakahara
Graham
Smith
Giles
Moore
Lynden
Pinetop
Snoqualmie
Oshkosh
Pocatello
Kailua
Cannon Beach
Tillamook
Winslow
Globe
Charleston
Gila Bend
Nogales
Greensboro
Durango
Telluride
Yuma
Washington
Arizona
Washington
Wisconsin
Idaho
Hawaii
Oregon
Oregon
Arizona
Arizona
South Carolina
Arizona
Arizona
North Carolina
Colorado
Colorado
Arizona
items_ordered
customerid
10330
10101
10298
10101
10299
10339
10449
10439
10101
10449
10439
10298
10410
10438
10438
10298
10449
10449
10101
10330
order_date
30-Jun-1999
30-Jun-1999
01-Jul-1999
01-Jul-1999
06-Jul-1999
27-Jul-1999
13-Aug-1999
14-Aug-1999
18-Aug-1999
01-Sep-1999
18-Sep-1999
19-Sep-1999
28-Oct-1999
01-Nov-1999
02-Nov-1999
01-Dec-1999
15-Dec-1999
22-Dec-1999
30-Dec-1999
01-Jan-2000
item
Pogo stick
Raft
Skateboard
Life Vest
Parachute
Umbrella
Unicycle
Ski Poles
Rain Coat
Snow Shoes
Tent
Lantern
Sleeping Bag
Umbrella
Pillow
Helmet
Bicycle
Canoe
Hoola Hoop
Flashlight
quantity
1
1
1
4
1
1
1
2
1
1
1
2
1
1
1
1
1
1
3
4
price
28.00
58.00
33.00
125.00
1250.00
4.50
180.79
25.50
18.30
45.00
88.00
29.00
89.22
6.75
8.50
22.00
380.50
280.00
14.75
28.00
Activity
• Select the lastname, firstname, and city for all
customers in the customers table. Display the
results in Ascending Order based on the
lastname.
• How many people are in each unique state in the
customers table? Select the state and display the
number of people in each. Hint: count is used to
count rows in a column, sum works on numeric
data only.
• How many orders did each customer make?
Joining Tables
• Sometimes the information you need is
spread across several tables
• A JOIN allows for that information to be
combined in the results table (reducing the
amount of information you need to handle)
• Simple joins are very easy to perform, simply
list both tables after the SELECT
• SELECT name,author,title from customers,classics
WHERE customers.isbn=classics.isbn;
Types of Joins
• Joining can be altered to create more specific
results
• Natural Join – automatically joins columns
that have the same column name
• Join…On – allows you to specify the column to
join the two tables
• AS – allows you to create aliases to shorten
table names when used as reference
Activity
• Using the previous two tables:
• Write a query using a join to determine which
items were ordered by each of the customers
in the customers table. Select the customerid,
firstname, lastname, order_date, item, and
price for everything each customer purchased
in the items_ordered table.
Logical Operators
• Used with WHERE queries to narrow down the
results
• Useful when data may be saved in a couple
different ways:
• SELECT author,title FROM classics WHERE author
LIKE "%Mark Twain%" OR author LIKE "%Samuel
Langhorne Clemens%";
Database Design
• Databases thrive on good and efficient design
• The correct layout will improve the efficiency,
speed, and usefulness of your site
• Begin with trying to anticipate what types of
queries will be commonly needed on your site
– What could they be for the project site?
– What types of information is needed to answer
these questions?
– What seem like some naturally occurring groups?
Primary Keys
• One of the most important parts of good
database design
– Have a quick unique identifier makes storing and
retrieving information much easier
– Keys should be truly unique and not repeatable
for different objects
– Auto increment works great for this
– Unfortunately its not the most natural of keys
Normalization
• You want to avoid duplication of information
in the database
– Redundancy increases the size of the database
and how long it takes for results to be returned
• Duplicates also make consistency (one of
those key principles!) hard to maintain since
making sure all instances of an entry are
updated (or deleted)
Normalization Schemas
• There are three separate schemas for
normalization (yes I know the list has 4)
– First
– Second
– Third
– Normal Form
• Normalizing for each of these forms will make
sure your database stays in that sweet spot
Purch.
Author 1 Author 2 Title
ISBN
Price U.S. Cust. name Cust. address date
David Sklar AdamTrach PHPCook 059610 44.99
Emma
1565 Rainbow Mar 03
tenberg
book
1015
Brown
Road, Los
2009
Angeles, CA
90014
DannyGoo
dman
Hugh E.
Williams
Dynamic 0596
59.99
HTML
527403
David Lane PHP and
MySQL
059600 44.95
5436
David Sklar AdamTrach PHPCook 059610 44.99
tenberg
book
1015
Rasmus
Lerdorf
Kevin Tatro Program 059600 39.99
e&
mingPHP 6815
Peter MacI
ntyre
Darren
Ryder
4758 Emily
Drive,
Richmond, VA
23219
Earl
862 Gregory
B.Thurston Lane,
Frankfort, KY
40601
Darren
4758 Emily
Ryder
Drive,
Richmond, VA
23219
David Miller 3647 Cedar
Lane,
Waltham, MA
02154
Table 9-1. A highly inefficient design for a database table
Dec 19
2008
Jun 22
2009
Dec 19
2008
Jan 16
2009
First Normal Form
• For a database to satisfy the First Normal Form, it
must fulfill three requirements:
1. There should be no repeating columns containing
the same kind of data.
2. All columns should contain a single value.
3. There should be a primary key to uniquely identify
each row.
• Columns which are needed but not fitting this
form can (and should) be spun off to another
table
ISBN
0596101015
Author
David Sklar
0596101015
Adam Trachtenberg
0596527403
Danny Goodman
0596005436
Hugh E Williams
0596005436
David Lane
0596006815
Rasmus Lerdorf
0596006815
Kevin Tatroe
0596006815
Peter MacIntyre
Table 9-3. The new Authors table
Second Normal Form
• Only after achieving First Normal Form can
Second Normal Form be evaluated
• Second Normal Form is achieved by
identifying columns whose data repeats in
different places and then removing them to
their own tables.
CustNo
1
Name
Emma Brown
Address
City
State Zip
1565 Rainbow Road Los Angeles CA
90014
2
Darren Ryder
4758 Emily Drive
Richmond
VA
23219
3
Earl B.
Thurston
862 Gregory Lane
Frankfort
KY
40601
4
David Miller
3647 Cedar Lane
Waltham
MA
02154
Table 9-6. The new Customers table
CustNo
ISBN
Date
1
0596101015
Mar 03 2009
2
0596527403
Dec 19 2008
2
0596101015
Dec 19 2008
3
0596005436
Jun 22 2009
4
0596006815
Jan 16 2009
Table 9-7. The new Purchases table
Third Normal Form
• Third normal form is considered to be the
strictest rule to follow and isn’t always needed
to have a productive database
• Data that is not directly dependent on the
primary key but that is dependent on another
value in the table should also be moved into
separate tables, according to the dependence.
• In this example it would require making 3 new
tables for ZIP, State, and CITY
When to Use Third Normal Form
• When additional information may be needed
– Eg. The 2 letter state abbreviation
• Book suggests that if you answer “yes” to
either of these questions then you should
follow third normal form:
1. Is it likely that many new columns will need
to be added to this table?
2. Could any of this table’s fields require a
global update at any point?
When Not to Normalize
• Spreading info across so many tables can
make MySQL work hard to return your results
• On a very popular site, if you have normalized
tables, your database access will slow down
considerably once you get above a few dozen
concurrent users
Databases and Anonymity
• A lot of information about people gets stored
in databases of dynamic websites
• This can be a benefit or a danger
– How Target Figured Out A Teen Girl Was Pregnany
Before Her Father Did