Queries, Databases and Tables

Video loading...

  • 0:01
    PHP for Beginners Part 3 Queries, Databases and Tables
  • 0:05
    with Leanna Pelham
  • 0:09
    On this screen, we're now talking directly to the MySQL
  • 0:13
    database software that's running on our computer.
  • 0:16
    It's kind of like talking to a dumb robot, like Siri.
  • 0:20
    We can ask it questions and give it commands,
  • 0:22
    as long as we use a simple syntax it understands.
  • 0:26
    Let's try saying hi.
  • 0:28
    Each command should end with a semicolon.
  • 0:32
    OK, that didn't work.
  • 0:34
    Try this instead.
  • 0:39
    [DING]
  • 0:42
    Awesome.
  • 0:42
    Select is the command we use to get data out of MySQL.
  • 0:49
    And even though this isn't very impressive,
  • 0:51
    we'll do crazy things with it later.
  • 0:54
    And hey, this is our first query.
  • 0:57
    The MySQL program we're inside of sent this message out
  • 1:00
    of our computer and back in through port 3306, where the MySQL server
  • 1:05
    software read it and responded back with this little table.
  • 1:10
    A query isn't a question.
  • 1:12
    It's a human-like language of commands that
  • 1:14
    say, show me this data, update that data, or delete these things.
  • 1:20
    Try another query.
  • 1:21
    I know, using sentences to do things is just weird.
  • 1:26
    It'll feel better over time.
  • 1:28
    And no, my cat didn't accidentally step on the caps lock.
  • 1:32
    The shouting is on purpose.
  • 1:34
    MySQL commands are usually written in all caps like this,
  • 1:37
    but it's totally unnecessary.
  • 1:39
    The same command works in lowercase.
  • 1:42
    MySQL is the database software we're talking to.
  • 1:45
    And actually, it can hold many different databases, which
  • 1:49
    are like separate dividers, or folders, for data.
  • 1:52
    If you were building 10 different sites,
  • 1:54
    you'd have 10 different databases.
  • 1:57
    I already have several others on my machine.
  • 2:00
    We're building a site, so let's create a database called air_pup.
  • 2:04
    The name air_pup isn't important.
  • 2:07
    We could use anything.
  • 2:09
    Send a query to list the databases.
  • 2:14
    There it is.
  • 2:15
    This fancy new database is like an empty directory.
  • 2:19
    There's nothing in it yet, but it's ready to go.
  • 2:22
    Hey, we already know three MySQL commands or queries, Select,
  • 2:28
    Create Database, and Show Databases.
  • 2:31
    And there really aren't that many more to learn.
  • 2:33
    Let's move into our database.
  • 2:38
    Any queries we make now will be run against the air_pup database.
  • 2:42
    A directory on your computer holds files.
  • 2:45
    So what lives inside a database?
  • 2:48
    Tables.
  • 2:49
    If you think of a database like a spreadsheet,
  • 2:51
    then each sheet or page is like a table.
  • 2:54
    One sheet might hold pet data and have fields
  • 2:56
    for name, breed, bio, and other stuff.
  • 2:59
    We might also have a second sheet that holds user data with fields
  • 3:02
    like name, address, and favorite ice cream.
  • 3:05
    In the database world, each of these is a table.
  • 3:08
    So before we start tossing pets into the database,
  • 3:11
    we need to create a table and tell MySQL
  • 3:13
    exactly what fields this will have.
  • 3:17
    We're going to do this the hard way first.
  • 3:19
    You'll thank me later when you really understand this stuff.
  • 3:23
    Like all things, a table is created by asking the database
  • 3:26
    politely, in other words, a query.
  • 3:29
    This is a long and ugly.
  • 3:32
    First, we say we want to create a table with the name pet.
  • 3:36
    Next, like a spreadsheet, we give the table some columns.
  • 3:41
    The big difference is that each column also has a data type, which
  • 3:45
    says if it should hold numbers, text, or something different.
  • 3:48
    The ID column is an int type, so it holds numbers.
  • 3:52
    The varchar type means that this column
  • 3:54
    can store up to 255 characters of text.
  • 3:59
    If we try to put more in it, the 256th character
  • 4:02
    will get chopped off.
  • 4:05
    There are other details that I don't want you to worry about yet.
  • 4:08
    The important parts are that we're calling the table pet,
  • 4:12
    giving it three columns, and setting a data type on each column.
  • 4:16
    Besides int and varchar, MySQL has a lot of other types.
  • 4:21
    But honestly, you'll use these and just a few others most of the time.
  • 4:26
    I used multiple lines to make this one long query.
  • 4:30
    MySQL is totally OK with this.
  • 4:33
    It just waits for a semicolon before actually sending the query.
  • 4:37
    OK, run it.
  • 4:39
    The message says, query OK, 0 rows affected.
  • 4:43
    That's not very exciting, considering how much typing we did.
  • 4:47
    But this is good.
  • 4:49
    Try another query to see all the tables in the database.
  • 4:53
    OK, only one table so far, but great start.
  • 4:57
    We've created our database and a table.
  • 5:00
    To celebrate, let's give it a treat by putting some data in.
Loading ...

Queries, Databases and Tables

Loading...

In this tutorial, now that we've connected to our database, we're going to learn the basics of SQL queries. You'll learn how to create databases, the tables that live in them, and how to do a basic SELECT query to retrieve information.

Downloads: 
Log in or sign up to download companion files.

Additional resources:
There are no resources for this video. If you believe there should be, please contact us.