PHP/SQL 1: Introduction to Database Programming

(1)

PHP/SQL 1: Introduction to Database Programming

Lesson 1: Introduction

Understanding the Learning Sandbox

Experimenting with Examples

MySQL Syntax

Databases and Permissions

Creating Databases

The Command Line

Lesson 2: Creating Tables

Reconnecting to MySQL

Creating and Checking Tables

Creating a Table

Checking Tables

Inserting Values into Tables

Inserting Specific Column Values

Retrieving Data from Tables

Lesson 3: Searching Through Tables

Exploring the 'where' Keyword

The 'like' Selector

Updating and Deleting Data

Updating Entries

Deleting Entries

Lesson 4: Managing Tables

Altering Tables

Deleting and Reordering Columns

Changing Column Type

Renaming Tables

Deleting Tables

Lesson 5: Managing Table Entries

Optimizing with Keys/Indexes

Auto_increment and Primary Keys

The Primary Key

Lesson 6: Relational Databasing

What is a Relational Database?

Inner Joins

Outer Joins

Aliases

Lesson 7: Managing Query Results

Group By

Using Distinct to Prevent Duplicate Results

Searching and Counting within Groups

Searching Within Groups

Renaming Results

Ordering Results

Descending Results

Nesting Groups and Orders

Limiting Results

Lesson 8: Database Programming with PHP

The PHP/MySQL Relationship

Connecting to a Database in PHP

Executing SQL Commands

(2)

Delimiting Queries

Lesson 9: Project Address/Phone Book, Part 1

Project Description

Table Layout

Table Creation

Lesson 10: Project Address/Phone Book, Part 2

Starting with the HTML Form

Storing the Data into SQL through PHP

Breaking Down addentry.php

Lesson 11: Project Address/Phone Book, Part 3

Construction of viewbook.php

Lesson 12: Advanced SQL Syntax, Part 1

Date and Time Functions

DAYOFWEEK(date)

WEEKDAY(date)

DAYOFMONTH(date)

DAYOFYEAR(date)

YEAR(date)

MONTH(date)

DAYNAME(date)

MONTHNAME(date)

QUARTER(date)

WEEK(date)

WEEK(date,weekday)

TO_DAYS(date)

FROM_DAYS(x)

CURDATE(), CURRENT_DATE

PERIOD_ADD(period,x)

PERIOD_DIFF(period1,period2)

DATEDIFF(date1,date2)

DATE_FORMAT(date,format)

Date/Time Mathematical Functions

DATE_ADD(date/time,INTERVAL expr type), ADDDATE(date/time,INTERVAL expr

type)

DATE_SUB(date/time,INTERVAL expr type), SUBDATE(date/time,INTERVAL expr

type)

EXTRACT(expr type FROM date/time

CURRENT_TIME()

HOUR(time)

MINUTE(time)

SECOND(time)

TIME_TO_SEC(time)

SEC_TO_TIME(sec)

TIME_FORMAT(time,format)

NOW(), SYSDATE(), CURRENT_TIMESTAMP()

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp, format)

Lesson 13: Advanced SQL Syntax, Part 2

Math Functions

MOD(x,y)

ABS(x)

SIGN(x)

LEAST(x,y,...)

(3)

GREATEST(x,y,...)

TRUNCATE(x,y)

FLOOR(x)

CEILING(x)

ROUND(x), ROUND(x,y)

SIN(x)

COS(x)

TAN(x)

ASIN(x)

ACOS(x)

ATAN(x)

ATAN2(x,y)

COT(x)

PI()

DEGREES(x)

RADIANS(x)

EXP(x)

LOG(x)

LOG10(x)

SQRT(x)

POW(x,y), POWER(x,y)

Lesson 14: Advanced SQL Syntax, Part 3

String and Character Functions

ASCII(str)

CONV(x,y,z)

BIN(x)

OCT(x)

HEX(x)

CHAR(x,y,z,...)

LENGTH(str), CHAR_LENGTH(str), OCTET_LENGTH(str),

CHARACTER_LENGTH(str)

LOCATE(str1,str2), POSITION(str1 IN str2)

LOCATE(str1,str2,x)

INSTR(str1, str2)

SOUNDEX(str)

CONCAT(str1,str2,...)

LPAD(str1,x,str2)

RPAD(str1,x,str2)

LEFT(str,x)

RIGHT(str,x)

LTRIM(str)

RTRIM(str)

LOAD_FILE(filen)

SPACE(x)

REPLACE(str1,str2,str3)

REPEAT(str,x)

REVERSE(str)

INSERT(str1,x,y,str2)

ELT(x,str1,str2,str3,...)

FIELD(str1,str2,str3,str4,...)

TRIM([[BOTH | LEADING | TRAILING] [str2] FROM] str1)

SUBSTRING(str,x), SUBSTRING(str FROM x)

(4)

SUBSTRING_INDEX(str1,str2,x)

LCASE(str), LOWER(str)

UCASE(str), UPPER(str)

MAKE_SET(bit,str1,str2,...)

EXPORT_SET(bit,str1,str2,[str3,[x]])

FIND_IN_SET(str1,strlist)

Lesson 15: Final Project

Final Project, Part 1

Final Project, Part 2

Copyright © 1998-2014 O'Reilly Media, Inc.

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.

(5)

Introduction

Welco me to the O'Reilly Scho o l o f Techno lo gy's PHP/SQL 1: Int ro duct io n t o Dat abase Pro gram m ing co urse!

Course Objectives

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

create, search, and manage SQL tables and entries.

perfo rm basic SQL queries and jo ins, and manage the results.

demo nstrate understanding o f advanced SQL syntax fo r date, time, math, and string functio ns. implement SQL database pro gramming into a PHP-based web applicatio n.

build a full-fledged o nline address/pho ne bo o k using PHP and MySQL.

SQL(Structured Query Language) is the language fo r databases such as Oracle, MySQL, MS Access, etc. Kno wing SQL is paramo unt to using these databases. In this co urse, yo u will learn basic SQL database creatio n and manipulatio n, as well as ho w to search databases and ho w to inco rpo rate them into PHP-based pro grams and applicatio ns.

Fro m beginning to end, yo u will learn by do ing yo ur o wn SQL-based pro jects using PHP, and then handing them in fo r instructo r feedback. These pro jects, as well as the final pro ject, 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, 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.

(6)

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:

(7)

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

Co de Edito r Demo Co ursewo rk Demo

Understanding the Learning Sandbox

Co deRunner is a multi-purpo se edito r that allo ws yo u to create applicatio ns in many techno lo gies. One o f these techno lo gies is MySQL.

Experimenting with Examples

All o f the co mmunicatio n to o ls and learning co ntent will be in the upper part o f the screen. We'll ask yo u frequently to type co de into the MySQL shell that we've set up fo r yo u, and to experiment by making yo ur o wn changes. To access yo ur MySQL shell, click the Ne w T e rm inal butto n to co nnect to Unix and lo g in fro m there, o r click the Co nne ct t o MySQL butto n to co nnect directly to the MySQL she ll. Fo r this co urse, yo u can use either metho d to get to yo ur MySQL she ll, altho ugh yo u sho uld be familiar with bo th metho ds. Click the Ne w T e rm inal butto n to co nnect to Unix no w. Yo u will see pro mpts fo r the lo gin and passwo rd yo u 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 lo gin 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.

Yo u sho uld see so mething like this: Login: certjosh

Password:

Last login: Thu Aug 5 20:47:25 from 63.171.219.116 cold:~$

Then, to start yo ur MySQL she ll, type the fo llo wing at the Unix pro mpt. (Do n't wo rry if yo u're no t familiar with Linux, UNIX, o r SSH.)

(8)

Type the fo llo wing co mmand at yo ur Unix pro mpt:

cold:~$ mysql -h sql.oreillyschool.com -p -u username username

Note

Make sure that yo u type yo ur username twice, just as it appears abo ve! Of co urse yo u sho uld_{use yo ur actual Sandbo x lo gin, which is listed fo r yo u o n yo ur Student Start Page.}

Yo u're pro mpted fo r yo ur passwo rd. Enter it and yo u're set to start MySQL. No w yo u sho uld see a MySQL pro mpt that lo o ks like this:

OBSERVE:

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

Server version: 5.0.62-log Source distribution

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

mysql>

Since yo ur SQL acco unt is sto red o n o ur servers, yo u can access it fro m anywhere, thro ugh any SSH pro gram yo u may have - no t just the o ne we pro vided fo r yo u in Co deRunner. If yo u like, just SSH into yo ur do main(ie. yo urdo m ain.o re illyst ude nt .co m) that we set up fo r yo u (ask yo ur instructo r if yo u're no t sure what yo urs is), then fo llo w the steps abo ve to lo g in to yo ur MySQL she ll.

MySQL Syntax

No w that we're in an interface enviro nment, let's dive into MySQL and start learning a few keywo rds and syntax. First up is the se le ct keywo rd. Yo u use se le ct to retrieve data fro m yo ur database, query info rmatio n (such as the date in the example belo w), inquire abo ut the MySQL versio n, and view o ther database info . Try this:

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select current_date(), current_time(); +---+---+

| current_date() | current_time() | +---+---+ | 2010-09-21 | 15:53:59 | +---+---+ 1 row in set (0.00 sec)

Yo u may have no ticed that MySQL tried to put the info rmatio n yo u requested into a nice, neat little table. Well, as nice and neat as tables get when they're built o ut o f plus and minus signs, anyway.

See ho w fast the retrieval time was? 0 .0 0 seco nds! With a larger database o r a mo re co mplicated query, yo u'd have a slightly lo nger respo nse time.

In MySQL, co mmands such as se le ct are case insensitive.

Note

This applies to keywo rds o nly. The fo llo wing lines will all return the same result: Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> SELECT CURRENT_TIME(), CURRENT_DATE(); mysql> select current_time(), current_date(); mysql> SeLeCt CuRrEnT_tImE(), cUrReNt_DaTe();

(9)

Did all three lines return identical results? If no t, spell check yo ur co mmand lines and make sure the o nly discrepancies are in the cases (upper o r lo wer) o f the letters. No w type in the fo llo wing co mmand and hit e nt e r:

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select current_time()

What happened? No thing sho wed up except fo r this weird -> pro mpt.

Mo st MySQL keywo rds require a semico lo n (;) at the end in o rder fo r the co mmand to be executed. If MySQL do esn't find a semico lo n, it'll simply mo ve o n to the next line with a new pro mpt ->. The MySQL pro gram will repeat this until it finds the next semico lo n entered. This feature is useful when yo ur co mmand line is really, really lo ng. It allo ws yo u to break it up o ver several different pro mpt lines. Pro grammers in Java, C/C++, PERL, and o ther languages sho uld have no pro blem understanding this co ncept, since it ho lds true in tho se languages as well. So ho w do yo u execute that co mmand yo u entered o n the last line? Yo u can either enter the semico lo n and press Ent e r to execute yo ur co mmand and retrieve the date, o r yo u can enter \c at the pro mpt to cancel the previo us co mmand and start with a fresh pro mpt.

To retrieve the versio n o f MySQL yo u're using, simply use the ve rsio n() functio n. Let's find o ut abo ut yo ur MySQL versio n by trying this:

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select current_date(),

-> version(); +---+---+ | current_date() | version() | +---+---+ | 2010-09-21 | 5.0.62-log | +---+---+ 1 row in set (0.00 sec)

To speed up yo ur MySQL experience, here's a quick tip yo u may have figured o ut already. Yo u can use the Up arro w key to recall the previo us co mmand lines yo u've typed. The mo re times yo u press the up key, the deeper into histo ry o f co mmand lines it go es.

Yo u can use semico lo ns to co mbine multiple co mmand lines to gether. Fo r example: Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> select current_time(); select version(); +---+

| current_time() | +---+ | 15:55:41 | +---+ 1 row in set (0.00 sec) +---+

| version() | +---+ | 5.0.62-log | +---+

1 row in set (0.00 sec)

Databases and Permissions

When wo rking with SQL, yo u're always o perating within a database enviro nment. In yo ur case, at OST, the name o f yo ur database will be identical to yo ur lo gin. Let's go ahead and pull it up.

(10)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> show databases;

Do yo u see a list o f databases? These are the names o f the databases o n the OST server to which yo u have access. Do yo u see yo ur database? Let's access it.

Type the fo llo wing blue and green text at yo ur MySQL pro mpt: mysql> use username;

Database changed mysql>

What wo uld happen if yo u tried to access a database to which yo u didn't have permissio n? Try it: Type the fo llo wing blue and green text at yo ur MySQL pro mpt:

mysql> use zyla;

ERROR 1044: Access denied for user: '[email protected]' to databa se 'zyla'

mysql>

Turns o ut yo u can't access any o f them except yo urs—by the same to ken, no bo dy but yo u can lo o k at yo ur database. Why is this? When we created a database fo r yo u o n o ur OST servers, we were able to grant and deny privileges o n yo ur database, so that o nly yo ur lo gin (and yo ur instructo r's) co uld access it. Permissio ns and privileges are co vered in mo re depth in the PHP/SQL 2 co urse.

Creating Databases

Who co uld have guessed that in o rder to sho w a list o f databases, yo u had to type sho w dat abase s;? Can yo u predict which co mmand we use to create databases? Well, if yo u said cre at e , yo u're right. To create a database we do the fo llo wing:

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> create database yourdatabasename;

Did yo u get an erro r message? Just like befo re, that's because o n the OST servers, yo u o nly have access to yo ur o wn database; yo u do n't have permissio n to create new o nes o n o ur servers. But, o n yo ur o wn SQL server (fo r instance, in PHP/SQL 2) yo u wo uld be able to use the cre at e co mmand to build new databases. When yo u're finished with yo ur MySQL sessio n, type in quit to exit o ut o f the MySQL sessio n. This is o ne o f the few keywo rds that do n't require a semico lo n. Yo u sho uld see so mething like this:

INTERACTIVE SESSION: mysql> quit

Bye cold:~$

Yo u can also use the keywo rd e xit .

The Command Line

(11)

Since yo u've exited the MySQL shell, it's a go o d time to revisit the co mmand yo u typed to enter MySQL in the first place:

OBSERVE:

cold:~$ mysql -h sql.oreillyschool.com -p -u username username

Mo st o f this co urse will be spent within the MySQL she ll itself; ho wever, because the command line is used o ften in advanced SQL to pics, it's go o d to kno w a bit abo ut that as well.

The co mmand line is a way to enter co mmands into a co mputer thro ugh the keybo ard. The co mmand line is used mo stly in open source o perating systems like Linux o r UNIX, but yo u can find ways to o pen a co mmand line terminal in Windo ws, Mac, o r any o ther o perating system. m ysql is the co mmand name used to launch the MySQL pro gram that's been installed by the system administrato r. Everything written after that co mmand are param e t e rs that are passed into the co mmand to give the applicatio n instructio ns to execute upo n launch.

The -h flag tells MySQL to read all the databases o ff a separate server—in yo ur case, instead o f

co ld.o reillyscho o l.co m, yo u use the OST database server, sql.o re illyscho o l.co m, because that's where yo ur database resides.

The -p flag tells mysql to pro mpt fo r a passwo rd, so yo u can lo g in safely. The -u flag tells mysql to lo g in the user specified by username.

The last username parameter specifies the default database to use—in yo ur case, we created yo ur

database name to be the same as yo ur Sandbo x lo gin. In the later co urse, yo u might create a database with any name yo u wish.

Just fo r kicks, let's try to access that pesky 'zyla' database again, using the co mmand line: Type this into yo ur UNIX pro mpt:

cold:~$ mysql -h sql.oreillyschool.com -p -u username zyla Password:

ERROR 1044: Access denied for user: '[email protected]' to databas e 'zyla'

mysql>

Even tho ugh it still asked fo r yo ur passwo rd, MySQL did no t allo w yo u to lo g in, because yo u attempted to default to a database yo u co uldn't access.

We've co vered a lo t o f gro und already, but we've barely started the fun. See yo u in the next lesso 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.

(12)

Creating Tables

Reconnecting to MySQL

Welco me to the seco nd lesso n. Do yo u remember ho w to lo g into yo ur MySQL shell? Just to refresh yo ur memo ry, click the Ne w T e rm inal butto n ( to o pen a new terminal sessio n and type in yo ur Sandbo x username and passwo rd to lo g in. Once yo u're in, type the fo llo wing to launch MySQL:

Type the fo llo wing co mmand at yo ur Unix pro mpt:

cold:~$ mysql -h sql.useractive.com -p -u username username Password:

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

Server version: 5.0.62-log Source distribution

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

mysql>

Creating and Checking Tables

By no w, yo u kno w that yo u're using a database named after yo ur Sandbo x lo gin, that we created especially fo r yo u. But what's in that database?

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> show tables;

Empty set (0.00 sec) mysql>

Creating a T able

OK, yo u're sitting in an empty database. It's time to add a table. No w, we need to think o f a purpo se fo r this table. Fo r no w, let's make a table that will ho ld a list o f yo ur friends and so me o f their statistics. Let's think o f so me info rmatio n abo ut yo ur friends that yo u might want in a database.

Let's try first name, last name, birthday, gender, height, and weight.

Note

When yo u create tables, it's best to be careful fro m the start. Plan everything in advance befo re_{yo u actually begin creating anything.}

Any idea what the co mmand wo uld be to create a table? If yo u tho ught o f cre at e , yo u're catching o n quickly. Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> create table friends (first_name varchar(20),

-> last_name varchar(20), birthday date, gender char(1), -> height int(3) unsigned, weight int(4) unsigned);

Check o ut the table that yo u just created by using the sho w t able s; co mmand. The table f rie nds sho uld be in the list.

(13)

INTERACTIVE SESSION: mysql> show tables; +---+ | Tables_in_certjosh | +---+ | friends | +---+ 1 row in set (0.00 sec)

Phew! That was a lo t o f text just to create a table! Let's go o ver what we just did. First, we cre at e d a table named f rie nds. Then, in a set o f parentheses, we put in all the data and the co rrespo nding data types. We have first_name as a varchar data type, which stands fo r varying characters. The number 20 (in parentheses) fo llo wing varchar indicates that the first_name data entries can be up to 20 characters lo ng. The char is very similar to varchar in that it also takes ASCII characters such as "a","b","c","1","2","\",">", and so o n. The char data type can be set to take in a maximum o f 255 characters, while varchar can take up to 6 5,535 characters. The key difference between the two lies in the fact that they allo cate a different amo unt o f memo ry space.

To make a memo ry-efficient table, try to fo llo w these guidelines:

If yo u do n't kno w exactly ho w many characters the data will be, o r if it might be mo re than 255 characters, use varchar.

If yo u kno w the data's exact size and it's less than 255 characters, use char.

To illustrate, it's wise to use varchar fo r names since yo u do n't kno w exactly ho w many characters will be invo lved fro m entry to entry. Fo r gender (gender) (m/f), yes/no fields, and o ther sho rt data fields, it's go o d to use char.

Mo ving right alo ng, the birthday is o f the dat e data type. A date in MySQL is in the fo rmat year-mo nth-day, such as 20 15-0 9 -21. It's mo re advantageo us to use the dat e data type fo r dates than to use the char type— we'll see why in a bit.

We can represent gender using a single character (m o r f—and yo u can use o ther co des like "d" fo r decline to answer), so we'll use the char data type. Height and weight are integers. Integers refer to all who le numbers, including negative who le numbers (52, 5, 0 , -1, -40 ). Ho wever, no tice the wo rd unsigne d that is within the co de. Unsigne d disables negative numbers. This is useful here since we wo n't need negative numbers fo r height and weight. Cho o sing the right type o f variables will save a lo t o f disk space (especially when yo ur database is huge) and minimize the data access time.

Oo ps, I almo st fo rgo t o ne o f the data types!

The t e xt data type is very similar to the varchar and char data types, except that we do no t specify the maximum length in parentheses after the wo rd t e xt . The text data type might be useful if yo u need a data co lumn to sto re a lengthy descriptio n o f an item o r so me o ther lo ng piece o f text. Be careful with lo ng text entries; the who le structure o f a database is such that entries are stable and so mewhat permanent. So , say yo u make a spelling erro r in that lo ng text variable that yo u sto red and no w yo u want to change it. It wo uld be do -able, but co rrecting such erro rs is difficult. (We co uld use the alt e r co mmand to do so , but let's wo rry abo ut that later.)

Checking T ables

(14)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> describe friends;

Fo r no w, do n't wo rry abo ut the Null, Ke y, and Ext ra co lumns. The De f ault co lumn simply states that if no data is entered in that particular field, a NULL value is assigned (basically, it's left empty). The useful co lumns here are Fie ld and T ype . So metimes database pro grammers can get lo st and fo rget the types o f data and what names they've created. To find o ut the names o f the fields and the data types, we simply use de scribe . Pretty handy, eh?

Okay, no w that the table is set up, we'll need to enter so me data. But befo re we do that, let me just give yo u a quick reference table o f all data types o ut there that yo u can use. We wo n't co ver all o f them here, but the data types we will co ver can handle just abo ut any no rmal database.

Num e ric Dat a T ype s Space Re quire d

TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT 4 bytes INTEGER 4 bytes BIGINT 8 bytes FLOAT(4) 4 bytes FLOAT(8 ) 8 bytes FLOAT 4 bytes DOUBLE 8 bytes

DOUBLE PRECISION 8 bytes

REAL 8 bytes

DECIMAL(N,D) N bytes (D+2, if N<D) NUMERIC(N,D) N bytes (D+2, if N<D)

Dat e and T im e Dat a

T ype s Space Re quire d

DATE 3 bytes

DATETIME 8 bytes TIMESTAMP 4 bytes

TIME 3 bytes

YEAR 1 byte

St ring Dat a T ype s Space Re quire d CHAR(X) X bytes, where X <= 2^8 (255)

VARCHAR(X) L+1 o r 2 bytes, where X <= 2^16 (6 5,535) TINYBLOB, TINYTEXT L+1 bytes, where L < 2^8 (255)

(15)

BLOB, TEXT L+2 bytes, where L < 2^16 (6 5,535) MEDIUMBLOB,

MEDIUMTEXT L+3 bytes, where L < 2^24 (16 ,777,215) LONGBLOB, LONGTEXT L+4 bytes, where L < 2^32 (4,29 4,9 6 7,29 5)

ENUM('value1','value2',...) 1 o r 2 bytes, depending o n the number o f enumeratio n values (6 5,535 values_maximum) SET('value1','value2',...) 1, 2, 3, 4 o r 8 bytes, depending o n the number o f set members (6 4 members_maximum)

Inserting Values into Tables

No w, let's enter so me values into the table "friends" that we've already set up. Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> insert into friends values ('Hoyoul', 'Kang', -> '1979-08-08', 'm', 165, 145);

Query OK, 1 row affected (0.00 sec)

If so mething went wro ng (fo r example, if yo u fo rgo t to type a character), o ften MySQL will tell yo u the appro ximate lo catio n o f the erro r.

We've successfully inserted o ne entry into the friends table, using the co mmand inse rt int o . The table name fo llo ws the co mmand, fo llo wed by the co lumn values within a set o f parentheses.

Note

Within the parentheses, yo u must enter the values in the exact o rder yo u specified when yo u first createdthe table fo rmat. In o ther wo rds, "Ho yo ul" and "Kang" are the first_name and the last_name co lumns, respectively, that yo u created previo usly.

Values that fit into the varchar, char, t e xt , and dat e data types need to be within either single o r do uble quo tes (if the values co ntain quo tes, there are o ther rules fo r entry; fo r example, using backticks (`) instead o f quo tes). When we get into PHP interfacing, we'll need to use single quo tes due to int e rpo lat io n, so yo u might want to make a habit o f using single quo tes to define values fro m no w o n.

Inserting Specific Column Values

Yo u can also cho o se which specific fields yo u want to enter using the Inse rt int o statement. Let's say we want to enter ano ther ro w into o ur f rie nds table with first name = 'Jo e', birthday = '19 55-12-12', gender = 'm', weight = '16 0 '. Here's ho w yo u wo uld go abo ut do ing it.

Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> insert into friends (first_name, birthday, gender, weight) -> values ('Joe', '1955-12-12', 'm', '160');

Right after the table name, yo u type the names o f the fields yo u want to insert values into and then enter the co rrespo nding values, in the same order, into the table. So 'Jo e' co rrespo nds to f irst _nam e , '19 55-12-12' co rrespo nds to birt hday, and so o n. The fields fo r which yo u didn't supply a value wo uld simply co ntain the default NULL (an empty value).

Retrieving Data from Tables

Let's retrieve the data we've just entered.

(16)

Type the fo llo wing into yo ur MySQL pro mpt: mysql> select * from friends;

+---+---+---+---+---+---+ | first_name | last_name | birthday | gender | height | weight | +---+---+---+---+---+---+ | Hoyoul | Kang | 1979-08-08 | m | 165 | 145 | | Joe | NULL | 1955-12-12 | m | NULL | 160 | +---+---+---+---+---+---+ 2 rows in set (0.00 sec)

Here we have the keywo rd se le ct , which retrieves the co lumns and their values in a certain table—in o ur case, f rie nds. The star (*) is a wildcard—a sho rtcut—that in o ur case means all column values.

Take so me time to insert so me mo re o f yo ur friends and their statistics into yo ur table. Try to add at least five mo re. That sho uld get yo u really co mfo rtable with using the Insert syntax. Try to enter different values fo r all the different fields so yo u have a go o d amo unt o f variety in yo ur data—the mo re friends yo u enter at this po int, the mo re fun yo u can have later when we start to play aro und with querying.

Be sure to play aro und with cre at e , se le ct , and inse rt , and to hand in yo ur assignm e nt s fro m the syllabus. Please make sure to read all the co mments o n the Graded links o nce yo ur co ursewo rk has been returned. See yo u in the next lesso n!

(17)

Searching Through Tables

Exploring the 'where' Keyword

We to o k a quick lo o k at the se le ct keywo rd in o ur previo us lab. Do yo u remember the syntax o f that co mmand? Let's lo o k at it again.

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select * from friends;

Here we're selecting to view all the co lumns (the asterisk * symbo l o r "wildcard" represents "all") fro m the table f rie nds.

Let's try a mo re co mplicated query. Say we want to get a list o f the first names o f all yo ur female friends. We can do this in o ne co mmand:

3 rows in set (0.00 sec) mysql>

This statement is very similar to an English sentence, isn't it? The previo us co mmand line co uld be read, "se le ct the f irst _nam e co lumn f ro m table f rie nds whe re ge nde r is equal to 'f '." The part after the keywo rd whe re is called the co ndit io nal st at e m e nt , and it's used just like yo u wo uld use it in any pro gramming language—fo r instance, a PHP if /e lse statement.

mysql> select first_name from friends where gender = 'f' or gender = 'm';

The o perato r o r is used here to expand o ur o ptio ns when retrieving data. Basically, this means that if the gender matches 'f' or 'm', then MySQL returns the first_name value fo r the entry to be displayed.

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select first_name, last_name, birthday

-> from friends where height > 175 and weight < 160;

The three names o f the fields (first_name, last_name, and birthday) are given (separated by co mmas) to specify that yo u want tho se three fields to be displayed. Since we have two co nditio ns, and we need to satisfy bo th o f them, we use the and o perato r. The first co nditio n was that the height must be greater than 175 centimeters, which was indicated with he ight > 17 5 . The seco nd co nditio n was that weight was under 16 0 po unds, represented by we ight < 16 0 .

(18)

Ope rat o r Me aning

= equal

< less than

<= less than o r equal to > greater than

>= greater than o r equal to != no t equal

Try playing aro und with the and, o r, and o ther o perato rs using the se le ct statements befo re mo ving o n.

The 'like' Selector

If yo u've wo rked with pro gramming languages, yo u pro bably reco gnized the co nditio nal statement o perato rs. Ho wever, yo u may no t have seen the special SQL like selecto r yet.

mysql> select first_name,birthday from friends where first_name like ("Des%"); +---+---+

In MySQL, the keywo rd like is used as an operator, to search fo r co lumn values that match a certain string pattern—in o ur case, we wanted to find Dessie's birthday, but co uldn't remember ho w her name was spelled. Because we kno w it starts with "Des," we use the percent sign (% ) wildcard to represent the rest o f her name, ho wever it may be spelled. Of co urse, if yo u have friends named "Destiny" o r "Deshawn," their birthdays will be listed as well.

Updating and Deleting Data

Being able to search a table is great, but tables aren't much go o d if yo u can't update o r delete entries. Luckily, do ing these tasks is just as simple as searching.

Updating Entries

When yo u retrieved yo ur friends' data using the se le ct keywo rd, did yo u no tice so mething strange abo ut Ho yo ul's height? His weight lo o ks no rmal in po unds—145 po unds is a reaso nable human weight—but if we take his height in inches, 16 5 inches wo uld make him o ver 13 feet tall!

To make o ur data co nsistent, we sho uld pick either the Imperial o r the Metric system fo r bo th co lumns. Let's go with Imperial, which wo uld make his height 6 9 inches. Update it as sho wn:

Type the fo llo wing into yo ur MySQL pro mpt:

mysql> update friends set height=69 where first_name='Hoyoul'; Query OK, 1 rows affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0 mysql>

(19)

mysql> select * from friends where first_name='hoyoul';

And if yo u want to update multiple co lumn values: Type the fo llo wing into yo ur MySQL pro mpt:

mysql> update friends set birthday='1975-09-30',gender='f' where last_name='Parm elee';

Deleting Entries

We've decided that since we do n't kno w Jo e's last name, we can't really co nsider him a friend—mo re o f an acquaintance, really. So we need to remo ve him fro m o ur table, alo ng with anyo ne who se last name was never mentio ned:

mysql> delete from friends where last_name is null; Query OK, 1 row affected (0.00 sec)

mysql>

And no w check o ur table, using the se le ct keywo rd: Type the fo llo wing into yo ur MySQL pro mpt: mysql> select * from friends;

+---+---+---+---+---+---+ | first_name | last_name | birthday | gender | height | weight | +---+---+---+---+---+---+ | Hoyoul | Kang | 1979-08-08 | m | 69 | 145 | | Trish | Gray | 0000-00-00 | f | 63 | 116 | | Kerry | Beck | 0000-00-00 | f | 72 | 135 | | Dessie | Coale | 1975-08-01 | f | 68 | 125 | | Kerry | Parmelee | 1975-09-30 | f | 0 | 0 | +---+---+---+---+---+---+ 5 rows in set (0.00 sec)

Here, the de le t e keywo rd was used to delete ro ws o f data, cho sen by the co nditio nal statement last _nam e is null. There's no need to specify a wildcard (*) o r any co lumn names, since the entire ro w will always be deleted.

But why was the wo rd is used instead o f the equality o perato r (=)? Since null is a special keywo rd indicating an empty cell, MySQL requires the o perato r is in o rder to prevent null fro m being co nfused with a string, such

(20)

as 'hello ' o r, incidentally, 'null.'

Get in yo ur fill o f practice, and be sure to hand in yo ur assignments. See yo u in the next lesso n!

(21)

Managing Tables

Altering Tables

The o verall idea o f having a database is to allo w many entries o ver a lo ng perio d o f time. The database co lumns sho uld therefo re be well tho ught-o ut befo rehand, and we sho uld try to anticipate po ssible future changes. Ho wever, o nce in a while, so mething co mpletely unexpected happens and yo u have to alt e r the table co lumns to add new catego ries o r change existing o nes.

Befo re we discuss the alt e r co mmand, let's create ano ther table to play with: Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> create table officeproducts (product_name varchar(20), -> price decimal(6,2));

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

Yo u no w have a table called o f f ice pro duct s with two co lumns o f info rmatio n—pro duct _nam e and price . As it turns o ut, we fo rgo t to add a de script io n co lumn. Luckily, SQL co ntains many co mmands to deal with human erro r, and o ne o f tho se is alt e r t able . Let's alter o ur o f f ice pro duct s table to add a de script io n co lumn.

mysql> alter table officeproducts add description text; Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0 mysql>

No w let's check the table to make sure it was changed co rrectly. Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> describe officeproducts;

mysql>

We simply used the alt e r t able and add co mmands to add a de script io n co lumn with type t e xt.

Deleting and Reordering Columns

If we decide that we no lo nger want a co lumn in o ur table, we can dro p it. Ho wever, if we have data in that co lumn, we will lo se it, so we must be very careful when we use dro p. Suppo se we want to dro p the de script io n co lumn.

(22)

mysql> alter table officeproducts drop description; Query OK, 0 rows affected (0.00 sec)

Note

Be careful using the dro p co mmand! When yo u dro p the co lumn, yo u dro p all o f its data. We used the dro p co mmand to delete the co lumn called de script io n fro m the table o f f ice pro duct s. No tice that, unlike adding co lumns, when dro pping co lumns we do n't need to specify the co lumn type. No w, suppo se we really do want that descriptio n co lumn, but we want it to co me after pro duct _nam e . Try this:

Type the fo llo wing co mmands at yo ur MySQL pro mpt:

mysql> alter table officeproducts add description text after product_name; Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0 mysql> describe officeproducts;

mysql>

This time, we added af t e r co lum n_nam e to o ur co mmand line. When yo u insert a co lumn using the alt e r co mmand, the new co lumn is appended after the last co lumn by default. Using af t e r enables yo u to specify where the new co lumn go es. To put yo ur new co lumn up fro nt as the first co lumn o f the table, yo u can use f irst instead o f af t e r:

OBSERVE:

alter table officeproducts add description text first;

To reo rder a table witho ut lo sing any data, use alt e r t able with m o dif y. Suppo se we decide after all this that we'd like to mo ve de script io n after price again. Try this:

mysql> alter table officeproducts modify description text after price; Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

(23)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> describe officeproducts;

mysql>

Changing Column T ype

What if yo u want to change the data type o f an existing co lumn? Suppo se we want to change the data type o f co lumn de script io n fro m t e xt to varchar(25 5 ). Here's ho w we wo uld do it:

Type the fo llo wing co mmands at yo ur MySQL pro mpt:

mysql> alter table officeproducts modify description varchar(255); Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0 mysql> describe officeproducts;

mysql>

Syntax-wise, the m o dif y co mmand abo ve is almo st exactly like the add co mmand. Ho wever, rather than adding a new co lumn, it takes an existing co lumn and alters o nly its data type, to whatever yo u specify. In o ur case, it takes the de script io n co lumn and changing its data type fro m t e xt to varchar(25 5 ).

Renaming Tables

So metimes, yo u might need to change the name o f a table after creating it. Instead o f recreating the table and typing all o f the co lumn names and co lumn types all o ver again, yo u can just rename the table.

mysql> alter table officeproducts rename as office_prod; Query OK, 0 rows affected (0.00 sec)

mysql> describe officeproducts;

ERROR 1146: Table 'certjosh.officeproducts' doesn't exist mysql>

What just happened? By using the co mmand re nam e as, we've taken the table o f f ice pro duct s and renamed it o f f ice _pro d. As a result, the table o f f ice pro duct s no longer exists. Let's try this:

(24)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> describe office_prod;

mysql>

No w that lo o ks better.

Deleting Tables

We kno w ho w to create, alter, and rename tables. But what if we had an o ld table we no lo nger needed and wanted to remo ve? We'll need to dro p the table, meaning we'll remo ve the table and all the entries inside o f it. Let's go ahead and drop o f f ice _pro d alto gether.

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> drop table office_prod;

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

ERROR 1146: Table 'certjosh.office_prod' doesn't exist mysql>

By simply typing dro p t able , then the table name o f f ice _pro d, we have deleted the entire table fro m o ur SQL database.

WARNING

When yo u dro p a table, it is go ne fo rever! If yo u have an essential table with lo ts o f entries in it, all_{will be lo st. Use extreme cautio n with the dro p co mmand!}

Befo re mo ving o n, be sure to hand in yo ur assignm e nt s fo r this lesso n. See yo u in the next o ne!

(25)

Managing Table Entries

Yo u've learned ho w to create, alter, search, update, and delete tables in SQL. Theo retically, yo u co uld get by in SQL with these skills alo ne—especially with the limited data we've been using so far.

Ho wever, the reality is that an SQL table will pro bably end up with to ns and to ns o f data. That's really the reaso n fo r creating an auto mated database system, isn't it? And since that's the case, we need to take mo re care with o ur tables to ensure their integrity and efficiency.

Optimizing with Keys/Indexes

Are yo u co nnected to yo ur SQL database? Go o d. Let's create a new table, a t able _o f _co nt e nt s, that co ntains the chapter names, descriptio ns, and to pics o f a bo o k we want to po st o nline:

mysql> create table table_of_contents (chapter_name varchar(250), chapter_description text, chapter_topic varchar(25));

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

No w let's fill the table with so me bo o k chapters.

mysql> insert into table_of_contents values ('The Client Program', mysql> 'Recipes for the mysql interface itself.','queries'); Query OK, 0 rows affected (0.01 sec)

mysql> insert into table_of_contents values ('Writing External Programs', mysql> 'Recipes for MySQL-based programs.','DBI');

Query OK, 0 rows affected (0.01 sec)

mysql> insert into table_of_contents values ('Record Selection', mysql> 'Recipes for formatting MySQL output.','SELECT'); Query OK, 0 rows affected (0.01 sec)

mysql>

(26)

INTERACTIVE SESSION:

mysql> select * from table_of_contents;

+---+---+---+ | chapter_name | chapter_description | chapter_topic | +---+---+---+

| The Client Program | Recipes for the mysql interface itself. | queries |

| Writing External Programs | Recipes for MySQL-based programs. | DBI |

| Record Selection | Recipes for formatting MySQL output. | SELECT |

| Working with Strings | Recipes for controlling strings in output. | pattern matching |

| Working with Dates and Times | Recipes for controlling date/time in output. | TIMESTAMP |

| Sorting Query Results | Recipes for sorting SELECT results. | ORDER BY |

| Generating Summaries | Recipes for using groupings, count(), min and max. | GROUP BY |

| Introduction to MySQL on the Web | Formatting MySQL output into web pages. | DBI |

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

8 rows in set (0.00 sec)

This table lo o ks pretty o rganized and intuitive fo r a bo o k, right? In fact, yo u can use the chapt e r_t o pic co lumn to find the chapter that co ntains it.

mysql> select chapter_name,chapter_description from table_of_contents where chapter_top ic='GROUP BY';

mysql>

Pretty handy, huh? Think abo ut it. If yo u wanted to find a specific to pic in a physical bo o k, unless it had the rare, really great index, yo u'd pro bably have to flip thro ugh many pages in that bo o k in o rder to find what yo u're lo o king fo r. And, o nce yo u fo und yo ur to pic, there'd be no guarantee that the to pic didn't sho w up again o ther chapters. If that bo o k happened to be several hundred pages lo ng, it wo uld take way to o lo ng to find all the instances o f yo ur to pic. But wait—is SQL searching any differently fro m ho w yo u wo uld search?

(27)

mysql> EXPLAIN select chapter_name,chapter_description from table_of_contents where cha pter_topic='GROUP BY';

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

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

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

1 row in set (0.00 sec) mysql>

Note

The EXPLAIN keywo rd can be used in fro nt o f any SELECT statement, to analyze the o ptimizatio n o f that_search.

Wo w. Turns o ut SQL searches exactly like yo u do —lo o king at all eight rows in a table to find the to pic in questio n. No big deal, tho ugh, right? I mean, it's o nly eight ro ws, after all.

But what if we were lo o king fo r "GROUP BY" in an entire o nline library, like Safari Online Bo o ks? What if we were lo o king fo r "GROUP BY" in 550 BILLION web pages wo rth o f data, like Go o gle? Imagine waiting ho urs and ho urs fo r yo ur Go o gle search to co mplete—witho ut o ptimizatio n, even super-fast SQL wo uld get unacceptably bo gged do wn. Let's get real here. When yo u are lo o king up a to pic in a physical bo o k, yo u do n't flip thro ugh indiscriminately. Yo u lo o k in the inde x in the back o f the bo o k to find it:

As it turns o ut, SQL can also create an inde x o r ke y to help with searches. Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> alter table table_of_contents add key(chapter_topic); Query OK, 8 rows affected (0.01 sec)

In the statement abo ve, we've added a ke y fo r the co lumn chapt e r_t o pic to let SQL kno w that we plan to search o n that co lumn quite a bit. SQL respo nded by creating a new, invisible table, specially designed to speed up any search we do o n chapt e r_t o pic.

(28)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> describe table_of_contents;

mysql>

As yo u can see, the chapt e r_t o pic co lumn is no w flagged as a multiple key, meaning that a value can be allo wed mo re than o nce in this co lumn (like the SELECT to pic in o ur table).

So ho w do es this help o ur search? Let's find o ut: Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> EXPLAIN select chapter_name,chapter_description from table_of_contents where cha pter_topic='GROUP BY';

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

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

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

1 row in set (0.00 sec) mysql>

And there yo u have it—suddenly, SQL go es fro m having to lo o k at every single entry in the table, to finding "GROUP BY" in o ne sho t. That's a po werful thing, especially as the size and co mplexity o f yo ur tables increases.

Auto_increment and Primary Keys

Yo u may have already no ticed that we have left o ut a very impo rtant co lumn in o ur t able _o f _co nt e nt s table: the chapter number itself! The o rder o f chapters is incredibly impo rtant in a bo o k, so there has to be a way to specify which go es where.

Let's add the chapter number:

mysql> alter table table_of_contents add chapter int unsigned NOT NULL first; Query OK, 8 rows affected (0.00 sec)

Here we've added a new co lumn named chapt e r, with type int (fo r "integer"). unsigne d indicates that it canno t be a negative number, and NOT NULL tells SQL no t to allo w any NULL values fo r chapt e r. f irst causes the co lumn to be inserted, well, first—befo re all the o ther co lumns.

(29)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select * from table_of_contents;

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

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

| 0 | Writing External Programs | Recipes for MySQL-based programs. | DBI |

| 0 | Introduction to MySQL on the Web | Formatting MySQL output into web pages. | DBI |

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

At this po int, we co uld simply updat e the table with all the chapter numbers by hand. Ho wever, because we kno w that each chapter will have o ne unique, incremental number assigned to it, it makes sense to auto mate this pro cess to prevent human erro r.

In o rder to do that, first we need to tell MySQL (like we did befo re with chapt e r_t o pic) that chapt e r will be a ke y co lumn.

mysql> alter table table_of_contents add key(chapter); Query OK, 8 rows affected (0.01 sec)

No w, use alt e r t able to m o dif y the type o f chapt e r: Type the fo llo wing co mmand at yo ur MySQL pro mpt:

mysql> alter table table_of_contents modify chapter int unsigned NOT NULL auto_incremen t;

Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql>

When we mo dified the type o f chapt e r, we left everything intact and simply added aut o _incre m e nt to the end. Here's what SQL did:

(30)

Type the fo llo wing co mmand at yo ur MySQL pro mpt: mysql> select * from table_of_contents;

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

Vo ila! SQL has filled in the chapter o rder, just ho w we wanted. No w, when we want to add a chapter, we can simply leave chapt e r blank, and it will be filled in fo r us. Like this:

(31)

mysql> insert into table_of_contents values ('','Modifying Tables', -> 'Dropping, adding, and changing columns','ALTER TABLE'); Query OK, 1 row affected (0.00 sec)

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

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

T he Primary Key

Because o ur chapt e r co lumn is co nsidered a unique ide nt if ie r, we can go o ne step further than simply naming it as a key. In this case, the o ptimal thing to do wo uld be to name chapt e r as the table's primary key.

(32)

Type this co mmand at yo ur MySQL pro mpt:

mysql> alter table table_of_contents add primary key(chapter); Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0 mysql> describe table_of_contents;

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

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

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

Only o ne prim ary ke y can be named fo r a table, because this indicates to SQL that this co lumn is indeed the table's unique ide nt if ie r. Fo r this reaso n, o f co urse, every ro w in that co lumn abso lutely must be unique.

No w that we've learned ho w to keep o ur tables o ptimized, we're ready to increase the co mplexity o f o ur database. Be sure to hand in yo ur assignm e nt s, and see yo u in the next lesso n!

(33)

Relational Databasing

So far, we've wo rked with simple tables in SQL, and they have wo rked well fo r o ur simple examples. Ho wever, databases can quickly beco me eno rmo us and incredibly co mplex. Ho w do we deal with info rmatio n when it o utgro ws o ne table?

What is a Relational Database?

Let's go back to o ur o nline bo o k example. Are yo u co nnected to yo ur SQL Database? Go o d. Type the fo llo wing co mmand at yo ur MySQL pro mpt:

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

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

9 rows in set (0.00 sec)

mysql>

There's a big pro blem with this table: the to pic co lumn. If yo u think abo ut it, a bo o k chapter's almo st always go ing to co ver lo ts o f to pics, no t just o ne. But ho w do we include all the chapter to pics witho ut inserting the chapter name and descriptio n o ver and o ver again? The chapt e r co lumn is auto -increment, so it wo n't even allo w duplicate chapters anyway.

There's a simple so lutio n to this dilemma: Let's create a new table, just fo r the chapter to pics. First, we need to dro p the chapter_to pic co lumn fro m table_o f_co ntents:

mysql> alter table table_of_contents drop chapter_topic; Query OK, 9 rows affected (0.02 sec)

(34)

No w, create the new table, called chapter_to pics:

mysql> create table chapter_topics (topic_id int unsigned NOT NULL auto_increment, -> chapter int unsigned NOT NULL, topic varchar(25) NOT NULL,

-> primary key(topic_id), key(chapter), key(topic)); Query OK, 0 rows affected (0.00 sec)

mysql> describe chapter_topics;

mysql>

Note

Did yo u see ho w we were able to do all the o ptimizatio n fro m the last lesso n within one cre at e t able_{statement? Pretty handy!}

By splitting o ur data between two tables, we've created a perfect example o f a relational database. The two tables are

related because o f the chapt e r co lumn, mirro red in bo th tables as a to uch po int. This can be do ne with any number o f

tables. Fo r so me definitio ns o f relatio nal databases, see this answers.co m article.

In fact, MySQL is co nsidered a relational database management system (RDBMS), and SQL as a language was built specifically fo r many RDBMS, no t just MySQL. Here's what Wikipedia has to say.

Befo re mo ving o n, we need to fill the chapt e r_t o pics table with so me values. Use the inse rt int o statement to get so mething like the fo llo wing:

OBSERVE:

mysql> select * from chapter_topics; +---+---+---+ | topic_id | chapter | topic | +---+---+---+ | 1 | 1 | queries | | 2 | 2 | DBI | | 3 | 2 | PHP | | 4 | 3 | SELECT | | 5 | 3 | WHERE | | 6 | 3 | LIMIT | | 7 | 3 | temporary tables | | 8 | 4 | pattern matching | | 9 | 5 | TIMESTAMP | | 10 | 5 | date_format | | 11 | 6 | ORDER BY | | 12 | 7 | GROUP BY | | 13 | 8 | DBI | | 14 | 8 | HTML | +---+---+---+ 14 rows in set (0.00 sec)

mysql>

Inner Joins

No w that we have o ur tables ready, let's get into so me basic relatio nal databasing. To co mbine the two tables we just created abo ve, we wo uld perfo rm an inne r jo in o n the two tables, using o ne o f the fo llo wing co mmands:

(35)

Type any o f the fo llo wing co mmands at yo ur MySQL pro mpt:

mysql> select * from table_of_contents, chapter_topics; mysql> select * from table_of_contents join chapter_topics; mysql> select * from table_of_contents cross join chapter_topics;