1. Introducing MySQLWelcome| 00:00 | (Music playing.)
| | 00:03 | Hi! I'm Bill Weinman and welcome
to MySQL Essential Training.
| | 00:07 | In this course, I aim to give you the
knowledge to perform the basic functions
| | 00:11 | that will allow you to use MySQL
effectively, and to take advantage of its
| | 00:15 | powerful and unique features.
| | 00:17 | After I walk you through the
installation of the program, we get right into
| | 00:20 | creating and using your own databases.
| | 00:23 | We'll look at the different data types
MySQL supports and the functions that are
| | 00:27 | provided to get the most out of them.
| | 00:29 | We'll take a good look at the command-line
interface, and how to use it to
| | 00:33 | access MySQL's powerful security model.
| | 00:36 | I'll show you the MySQL PHP
interfaces to help you create web applications.
| | 00:41 | Whatever the application, calendar,
email, Websites, bookkeeping, sales,
| | 00:46 | inventory, even the lowly To Do list,
it's all about the data, and that usually
| | 00:51 | mean some sort of a database.
| | 00:53 | This course will give you the tools to
create and maintain the database you need.
| | 00:57 | Now let's get started with
MySQL Essential Training.
| | Collapse this transcript |
| What is MySQL?| 00:00 | As we talk about MySQL, it's important to
understand what MySQL is and what it isn't.
| | 00:06 | MySQL is not an application.
| | 00:08 | It is a database management system.
| | 00:10 | It's not going to manage your database for
you without another application to support it.
| | 00:14 | MySQL is a database management system
consisting of a database server, which
| | 00:19 | manages the data and
performs the basic CRUD operations.
| | 00:22 | That's Create, Read, Update, and Delete.
| | 00:26 | It's a set of client libraries.
| | 00:28 | These are the interfaces for
applications to connect with the database server.
| | 00:32 | It includes a full command line
client, a full-featured client capable of
| | 00:37 | performing all the operations necessary
to manage and operate the database server.
| | 00:41 | MySQL is a full-featured
database management system.
| | 00:45 | It has all the components you
need to incorporate it into a
| | 00:48 | full-featured database application that will
scale well, and serve your needs for the long-term.
| | Collapse this transcript |
| Important terminology| 00:00 | MySQL is a technical topic.
| | 00:02 | Whenever you're dealing with a technical
topic, it's a good idea to agree on the
| | 00:06 | terminology involved in the discussion.
| | 00:08 | I'll cover some of the more general
terms here in this movie, and I'll cover
| | 00:11 | more specific terms as they come up.
| | 00:13 | Also at the end of each movie you'll find a
slight recapping terms covered in that movie.
| | 00:18 | DBMS is Database Management System.
| | 00:21 | This is the software package that manages
the data in the database. MySQL is a DBMS.
| | 00:28 | The DBMS stores, retrieves, searches,
updates, and deletes data in the database.
| | 00:33 | It also typically controls access to the data.
| | 00:36 | The DBMS may be used directly by
users, or may be used programatically by
| | 00:40 | another computer program that
requires the use of a database.
| | 00:44 | A storage engine is the part of the
DBMS that stores and accesses the data.
| | 00:50 | The storage engine includes mechanisms
for indexing, searching, and selectively
| | 00:54 | locking portions of the data in the database.
| | 00:57 | Different storage engines may be optimized
for different types and purposes of data.
| | 01:03 | SQL, Structured Query Language is a
language for interacting with a DBMS.
| | 01:08 | SQL is by far, the most
popular language for this purpose.
| | 01:12 | SQL is used by both users and
programs to interface with the DBMS.
| | 01:17 | A database is the store of data,
usually stored on disc, but may be stored in
| | 01:21 | memory or somewhere else.
| | 01:23 | It's often stored in a system of files and
the file system, sometimes in a single file.
| | 01:28 | The term is used to refer
generically to the place where the data lives.
| | 01:33 | So the file system itself is
actually a form of the database.
| | 01:35 | A database is typically segregated into tables.
| | 01:40 | Each table has its own configuration,
and usually carries its own class of data,
| | 01:45 | like a country table or an album table.
| | 01:49 | Tables are typically
organized in rows and columns.
| | 01:52 | A row usually represents all the
information in a given record of data.
| | 01:57 | For a given country in the country
table, a single row would have the name,
| | 02:00 | continent, region, etcetera.
| | 02:02 | For the single country, or for an album,
the album table, the title artist and
| | 02:07 | label for a given album.
| | 02:08 | A column represents all like
items across multiple rows.
| | 02:13 | The names of all the countries would
be in the name column of the country
| | 02:16 | table, or the artist for all the albums
would be in the artist column of the album table.
| | 02:22 | Columns are sometimes called fields, a
more general term that applies also to
| | 02:26 | non-relational databases.
| | 02:27 | Likewise, rows are sometimes called records.
| | 02:30 | An individual item of data
is sometimes called a cell.
| | 02:34 | A cell is one place in a
table that can hold one value.
| | 02:38 | It is one column in one row.
| | 02:41 | These are the major terms that
will be used during this course.
| | 02:43 | More specific terms would be covered in
individual lessons as they come up along the way.
| | 02:48 | Also at the end of each movie,
you'll find a slight recapping of terms
| | 02:51 | covered in that movie.
| | Collapse this transcript |
| Using the exercise files| 00:00 | So, let's talk a little bit about the
Exercise Files folder that comes with his course.
| | 00:04 | There are three items in the Exercise Files
folder, and they are SID and CRUD and SQL.
| | 00:10 | In the SQL folder, you'll find the
databases that we're using in the examples,
| | 00:14 | the album database, the test
database and the world database.
| | 00:17 | We'll import these into MYSQL in one of
the lessons using the command line client.
| | 00:22 | The SID and CRUD
applications, we'll install later.
| | 00:25 | But let's just take a quick look at them.
| | 00:27 | The SID application is how we'll be
demonstrating the exercises, type the SQL in
| | 00:32 | here, press Go and it executes and
it gives you the results down here.
| | 00:37 | It gives you the information that
comes back from MYSQL including how long it
| | 00:40 | took and how many rows were returned.
| | 00:43 | And the CRUD application is a simple
Create, Read, Update, Delete, which is
| | 00:48 | what stands for CRUD.
| | 00:49 | And it's an Album database using a couple of
joint tables, and it's got Albums and Tracks.
| | 00:54 | Here is the Albums and if we look in
one of the albums, we'll see the Tracks
| | 00:59 | and really that's it.
| | 01:00 | The CRUD application will be used for
explaining how to write code in PHP that
| | 01:05 | uses MYSQL and the SID application of
course, will be used to demonstrate the
| | 01:10 | various examples throughout the course.
| | 01:13 | So that's really it.
| | 01:14 | There is nothing vital in the Exercise Files.
| | 01:17 | If you don't have access to
the Exercise Files, that's fine.
| | 01:19 | You can still follow along in
the command line client in MYSQL.
| | 01:22 | Then really you should be able to do
all of the examples and all of the course
| | 01:27 | using your own data and using
the MYSQL command line client.
| | 01:30 | That's it, those are the Exercise Files,
enjoy and let's get started with the course now.
| | Collapse this transcript |
|
|
2. Installing the Test EnvironmentInstallation overview| 00:00 | Let's take a look at the
process of installing MYSQL.
| | 00:03 | When you're installing your database
management system, the first thing you want
| | 00:06 | to think about is how will
this installation be used?
| | 00:09 | It will be used in a testing
environment, where the priority to use on
| | 00:12 | flexibility and power, and your
activities are basically going to be concerned
| | 00:16 | with the learning about the
environment that you're going to be installing.
| | 00:19 | Or it will be used in a production environment.
| | 00:21 | Where it will be concerned with
security and flexibility and reliability.
| | 00:26 | And you also want to think about what
applications will you be using with the database?
| | 00:31 | Most applications that use a database
will include their own instructions, for
| | 00:34 | how it gets installed and will often
includes scripts for installing the
| | 00:37 | database itself or for installing the data.
| | 00:40 | There are two major options when
installing a Database Management System,
| | 00:43 | in particular MYSQL.
| | 00:45 | One is to install it as a part of a package
and this is how we'll be doing in this course.
| | 00:50 | In this case, we'll be installing
MYSQL as part of the package called XAMPP,
| | 00:56 | which includes Apache, it
includes MYSQL, it includes PHP and Perl.
| | 01:01 | It's cross-platform, it's
simple to use and you can download it
| | 01:04 | from apachefriends.org.
| | 01:07 | The other option is to install
MYSQL as a standalone package.
| | 01:10 | In other words, you have your Web
Server already installed separately, you have
| | 01:14 | your languages installed separately,
and you're going to be installing the
| | 01:17 | database separately.
| | 01:19 | If you choose to go this route, you
can either download your MYSQL from the
| | 01:23 | Download URL here on the screen, or you
can buy one of their support packages.
| | 01:28 | When you look for support, you either
have the support from MYSQL because you
| | 01:31 | bought it, or you can look at their
forums, or my personal favorite to go to
| | 01:34 | Google and type in my question and find
the many other smart people who have run
| | 01:38 | into the same issue and
have already resolved it.
| | 01:41 | If you're installing MYSQL standalone,
you may also want to install their GUI
| | 01:44 | tools, which are available at this URL.
| | 01:47 | For the purposes of the course,
we're going to be using the Command-Line
| | 01:50 | client because it accesses all of the
power of MYSQL and all of the features
| | 01:54 | in MYSQL and I'm going to be covering
some of those features that are not part
| | 01:57 | of their GUI tools.
| | 01:59 | You're also going to need a Text Editor
to edit some of the configuration files
| | 02:03 | and it's very important to realize that
a text editor is not a word processor.
| | 02:07 | Do not use Microsoft Word or any of
the other word processors that are
| | 02:10 | available out here.
| | 02:11 | The Text Editor, when you edit the text and
save it in a file, it saves only the text.
| | 02:17 | A word processor saves other what we
call meta-information about the formatting
| | 02:21 | and the fonts, and the layout and the
things like this, which do not work with
| | 02:25 | the configuration files.
| | 02:26 | So you'll need to have a plain
text editor and I have a couple of
| | 02:30 | recommendations here for you.
| | 02:31 | On the Mac, I'll use BBEdit
and I recommend it highly.
| | 02:34 | I do a lot of text editing on my Mac,
and I find BBEdit to be a very powerful,
| | 02:39 | flexible and easy to use tool.
| | 02:41 | Text Wrangler is also by the
Bare Bones Company who makes BBEdit.
| | 02:45 | It's a smaller free version of
BBEdit and that'll work just fine for all
| | 02:49 | purposes here in this course.
| | 02:51 | On the PC, I'll use Notepad++.
| | 02:54 | I have used other text editors on the
PC, some of which I paid good money for
| | 02:58 | and I end up using Notepad++,
which is free, highly recommended.
| | 03:03 | It's a good powerful, easy to use,
easy to install Text Editor for the PC.
| | 03:08 | You're also going to need a way to
access the Command-Line client in MYSQL
| | 03:12 | and on the PC this facility is built-in with
the cmd.exe application included with Windows.
| | 03:20 | This is how you access
the Command-Line on the PC.
| | 03:23 | On the Mac, you can use either the
Terminal application that's included with OS
| | 03:27 | X and it's heart of the operating system.
| | 03:30 | Or you can use iTerm,
which is the one that I use.
| | 03:33 | It's also free from iterm.sourceforge.net.
| | 03:36 | It's much more powerful, easier to use, more
flexible and in my opinion it just works better.
| | 03:42 | So that's the overview of
installing MYSQL on both the Mac and the PC.
| | 03:47 | And now we'll get into the actual installation.
| | Collapse this transcript |
| Installing XAMPP on Windows| 00:00 | So let's go ahead and install
MySQL on a PC running Windows Vista.
| | 00:06 | We're going to be using the XAMPP for
Windows package, and this is the webpage
| | 00:10 | where you can download XAMPP for Windows.
| | 00:12 | The URL is up here in the location bar.
| | 00:14 | The XAMPP for Windows package includes
all of the pieces that we're going to be
| | 00:18 | using plus a lot more.
| | 00:20 | Apache web server, MySQL latest version,
the latest version of PHP and a lot
| | 00:26 | of other pieces that we won't be using here,
but you may find useful at another time.
| | 00:30 | Go ahead and download the
Installer by clicking on this link.
| | 00:33 | I've already downloaded it
and saved it to the desktop.
| | 00:38 | We have it right here.
| | 00:40 | Before we actually run the Installer
and install XAMPP, we need to make sure
| | 00:44 | that there is an important
setting in Windows is set correctly.
| | 00:50 | Windows Vista has a feature called UAC,
which we can access in User Accounts.
| | 00:59 | Windows Vista has a feature called User
Accounts Control, which needs to be off.
| | 01:04 | The User Account Control prevents
programs from gaining administration level
| | 01:08 | access without user intervention.
| | 01:10 | XAMPP requires administration level access.
| | 01:13 | User Account Control was turned on by
default in some early versions of Vista.
| | 01:17 | It is turned off by default in current versions.
| | 01:20 | We just want to make sure
that it is actually off.
| | 01:22 | We can see here that's it's not checked.
| | 01:25 | So this feature is off in this installation.
| | 01:29 | So you want to check that
and make sure that it's off.
| | 01:32 | It needs to be off in order to run XAMPP.
| | 01:38 | Close the Control Panel and go ahead
and install XAMPP now. Select a language.
| | 01:44 | Notice this alert that User Control is
deactivated and that's what you expect. So that's correct.
| | 01:52 | Go ahead and press OK.
| | 01:53 | It's really important.
| | 01:54 | XAMPP won't run without it.
| | 01:55 | Next, the destination folder C:\xampp is okay.
| | 02:01 | That's an unusual place to
install an application, but this isn't a
| | 02:04 | normal application.
| | 02:05 | This actually is a system level service.
| | 02:07 | So that's a common way to do that.
| | 02:09 | We'll go ahead and click Next.
| | 02:11 | These are the checkboxes.
| | 02:12 | Exactly how they need to be.
| | 02:14 | You want a desktop icon.
| | 02:16 | You want it in the Start menu.
| | 02:18 | You do not want anything installed as a service.
| | 02:20 | We're not running this in a production
environment, and really if your running a
| | 02:24 | production environment you probably
don't want to run a package like this.
| | 02:27 | Go ahead and click Install,
and now it's installing.
| | 02:33 | XAMPP is installed. Click Finish.
| | 02:38 | Now, we want to start the XAMPP Control Panel.
| | 02:40 | So we'll go ahead and click Yes and
this will start the XAMPP Control Panel.
| | 02:44 | You can see we have an
icon for it on the desktop.
| | 02:46 | We'll start MySQL first and then start Apache.
| | 02:51 | You don't need to start anything else.
| | 02:54 | Close the Control Panel.
| | 02:56 | Notice that it stays down here in our
Notification Area so that we can launch
| | 03:00 | it again we need to.
| | 03:03 | Now let's just bring up Firefox and make
sure that it's well working. Type localhost.
| | 03:12 | Localhost is a special address that
means on my computer. There it is.
| | 03:17 | So the Apache web server is
working and PHP is working.
| | 03:21 | We can click on Status here.
| | 03:24 | These green lights are perfect.
| | 03:26 | These red lights are just right, because
we're not running SMTP. We're not running FTP.
| | 03:31 | We don't need it for our purposes.
| | 03:33 | And XAMPP is now installed.
| | 03:35 | There's one more piece of
configuration that we need to do.
| | 03:40 | We run our command line on
Windows and type in mysql.
| | 03:44 | This will not work.
| | 03:47 | The reason for that is that the
MySQL command line client is not in
| | 03:52 | the execution path.
| | 03:53 | So we need to edit the execution
path on Windows and then this will work.
| | 03:58 | So we'll go ahead and exit our command
line, and bring up the Control Panel again.
| | 04:04 | What the execution path is the path of
directories that Windows looks in, in
| | 04:09 | order to execute an executable
program from the command line.
| | 04:14 | So go ahead and edit it.
| | 04:15 | We'll click on System and Maintenance,
click on System, and Advanced system
| | 04:21 | settings, and down here Environment Variables.
| | 04:23 | The execution path is set in
environment variable called Path, and there it is.
| | 04:29 | We'll click on Edit.
| | 04:31 | This is a list of directories or folders in
Windows that are separated by semi-colons.
| | 04:38 | So I want to put one at the end of the list.
| | 04:40 | Semincolon, c:\xampp\mysql\bin, and click OK,
click OK, and click OK. There we have it.
| | 04:56 | We should be able to run
MySQL now from command line.
| | 05:02 | And there we have it.
| | 05:03 | Now MySQL is installed, XAMPP is
installed, and it's all tested and working.
| | Collapse this transcript |
| Installing XAMPP on a Mac| 00:00 | So now let's go ahead and
install on MySQL on a Mac.
| | 00:04 | We're using the XAMPP for Mac OS X package,
which includes a number of useful things.
| | 00:08 | This package includes Apache, MySQL 5.1.33,
the latest versions of PHP and Perl
| | 00:15 | and a number of other useful things
that you might use in another time.
| | 00:17 | So we'll simply click on the download link here.
| | 00:21 | I've already downloaded this to my desktop.
| | 00:24 | We'll go ahead and click on the DMG package
here, which is how you install stuff on a Mac.
| | 00:33 | Like many Mac installers we simple drag
the package into the Applications folder
| | 00:38 | and it copies thousands of things for us.
| | 00:42 | Now we can unmount the package by right-clicking
or Ctrl-clicking either here or
| | 00:48 | on the desktop icon.
| | 00:50 | Now XAMPP is actually installed.
| | 00:54 | Let me go ahead and open
the Applications folder.
| | 00:57 | You see the XAMPP Control Panel right here.
| | 01:03 | I'm going to drag that
into the dock. There it is.
| | 01:09 | Close that. Now I'll go ahead and run the
Control Panel, get the standard warning about
| | 01:16 | downloading stuff from the Internet.
| | 01:17 | Now I'm going to go ahead and
start MySQL, I type my password.
| | 01:24 | We'll also start Apache and put
that back in the dock and close this.
| | 01:32 | So now XAMPP is installed
and running and it's tested.
| | 01:35 | Bring up Firefox and type
localhost into the location bar.
| | 01:42 | There is XAMPP running our local
computer, so the Apache sever is working.
| | 01:48 | Then if I click on English, you can see
the PHP running because I'm running PHP URL.
| | 01:54 | Status, everything is activated and working.
| | 01:57 | So I'm going to go ahead and close Firefox.
| | 02:03 | We have one more bit of configuration
that we need to do here in order to run
| | 02:07 | the MySQL command line client.
| | 02:09 | We need to set the
execution path. I'll show you why.
| | 02:14 | Type MySQL.
| | 02:16 | We'll see it says command not found.
| | 02:19 | That's because it is installed, but the
path to the MySQL executable file is not
| | 02:26 | set in the operating system.
| | 02:29 | So OS X is based on UNIX, so this is done
in a .profile file in your home directory.
| | 02:36 | On this computer there is no .profile file.
| | 02:41 | You may already have one.
| | 02:42 | You may have set it up yourself,
something else that you installed may have set
| | 02:46 | one and your path may already be
getting set to something other than default.
| | 02:51 | In my case it is set to the default and
I'm going to show you how to look at that.
| | 02:55 | You type echo $PATH and you can see the
path, and this is the standard path that
| | 03:01 | MAC OS X and most UNIX
operating system come with.
| | 03:05 | You notice there are a number of
folders or directories as they are called in
| | 03:08 | UNIX separated by a colon.
| | 03:10 | In each case you have a usr/bin,
you have bin:, usr/sbin:, etcetera.
| | 03:16 | Each of these is separated by a colon.
| | 03:18 | It's not a semi-colon. It's a colon.
| | 03:20 | And you don't need one at the end of the list.
| | 03:23 | So we're going to adding one more
directory to the end of the list with another
| | 03:28 | colon, and here's how we do it.
| | 03:30 | The file that we want to edit and in
this case it doesn't exist, we're going to
| | 03:34 | be creating it, is called .profile.
| | 03:36 | So I'm going to type bbedit, which is the
name of my editor, and .profile, like that.
| | 03:43 | I can't simply open BBedit and go to
the File menu and select Open, and open
| | 03:48 | that file, because dot files
don't show up in those listings.
| | 03:52 | So you need to do this from command line.
| | 03:54 | So when I press Enter, it will launch
BBedit and because the .profile file is
| | 03:59 | not there already, it will launch
it with a blank file. Here we go.
| | 04:05 | It says untitled text, because
there's not already a file with that name.
| | 04:10 | So we're going to go ahead and we're
going to put in a command that's going to
| | 04:13 | assign a new directory to the end
of the path, and it looks like this.
| | 04:34 | So the path is assigned an environment variable.
| | 04:37 | The environment variable is called PATH.
| | 04:40 | This is the way you add something to
the end of an environment variable.
| | 04:43 | Type the name of the environment
variable with a $ sign, that's PATH=$PATH and
| | 04:49 | then whatever you're going to add after it.
| | 04:51 | In this case, it's a colon followed by
the path to the MySQL binary executable
| | 04:57 | which is /Applications/XAMPP/xamppfiles/bin.
| | 05:03 | Then you need to export the path.
| | 05:05 | You can simply say export PATH.
| | 05:07 | Occasionally you'll see this written
in a different way where the code export
| | 05:10 | will be before the line above it.
| | 05:13 | Either way if you already have a
.profile file, and it already has an assignment
| | 05:17 | like this in it, simply add the path
to the end of that line with a colon,
| | 05:23 | :Applications, blah, blah, blah like that.
| | 05:26 | So now we're going to go ahead and save this.
| | 05:28 | I'm going to choose Save As.
| | 05:31 | It's going in my home directory billw and
it's going to be called .profile. Like that.
| | 05:40 | No when I say Save, I get this
warning that Names that begin with a dot are
| | 05:44 | reserved for the system.
| | 05:45 | I go ahead and say okay, I
Use dot, and now it's saved.
| | 05:49 | So I'll quit BBEdit.
| | 05:51 | I want to quit my terminal program,
because the next time I start it, it'll
| | 05:58 | start a new shell and it
will read variable correctly.
| | 06:02 | Now I should be able to run MySQL. There it is.
| | 06:07 | This is the MySQL command line client and
we'll be using this later in the lesson. That's it.
| | 06:12 | XAMPP is installed, MySQL
is installed and working.
| | Collapse this transcript |
|
|
3. Configuring MySQLUsing the MySQL Command Line Interface on Windows| 00:00 | So now we're going to talk about the
MySQL command line client from the Windows
| | 00:04 | side and I want to show you how to
invoke the MySQL command line, because we're
| | 00:09 | going the to be using it
for the next few lessons.
| | 00:11 | Now the reason we're using the command
line client instead of any graphic tools
| | 00:17 | is because the command line client
that comes with my MySQL is able to access
| | 00:21 | all of the features of MySQL in all
the variations with all other options.
| | 00:26 | There is not a graphical tool that
allows you to access all of the options of
| | 00:32 | all of the features of MySQL.
| | 00:33 | So we're going to be using the command
line client to be able to tap into its
| | 00:37 | power and to show you how to
administer MySQL from the command line.
| | 00:41 | In order to do this, we need to
run the Windows cmd.exe application.
| | 00:48 | Cmd.exe has one particular quirk that
we are going to need to get around and
| | 00:52 | that is once you invoke it, it starts
up in the directory where cmd.exe lives
| | 00:57 | rather than your home directory or a
directory where you might want to use it.
| | 01:01 | So I want to show you a way to get around that.
| | 01:03 | I'll cmd in this search field here and
that brings it up and I'll right-click on
| | 01:10 | cmd and select Copy.
| | 01:12 | Right-click on the desktop, say Paste
Shortcut and this puts a shortcut on the
| | 01:17 | desktop to the cmd application.
| | 01:20 | Now if I double-click on this now,
you'll see that it starts up in
| | 01:23 | Windows\System32 directory, which is not
at all where we want it to be, and it's
| | 01:27 | actually a very dangerous place to be.
| | 01:29 | So we don't wan it to do that.
| | 01:31 | I'm going to type exit here.
| | 01:32 | Instead, we're going to tell it
to start in a different directory.
| | 01:36 | So I'll right-click on it and select the
Properties and then this starting field
| | 01:42 | is what we want to change.
| | 01:43 | You see it has the System32 directory.
| | 01:46 | I'm going to put the directory where
the SQL files are because we're going to
| | 01:49 | need that at some point soon.
| | 01:51 | So type in C:\Users\William Weinman and
start typing my name and then I'm going
| | 01:58 | to use my arrow keys so
that we don't get any typos.
| | 02:01 | Down arrow gets my name and then \
Desktop\Exercise Files\, I can't see the end
| | 02:13 | of that so I'm going to type SQL, there we go.
| | 02:18 | Say OK and now this command processor will
start in the directory that we want it to be.
| | 02:24 | There is the directory
where all my SQL files are.
| | 02:26 | I'll just type dir and there they are.
| | 02:29 | If I type mysql at this point, now
we're in the MySQL command line client, we
| | 02:36 | can type our SQL statements
here just type show databases.
| | 02:41 | Because in its default configuration,
there is not a whole lot else we can do.
| | 02:44 | But that shows that it is working.
| | 02:46 | Type exit and exit from this now.
| | 02:49 | When we want to run the MySQL command
line client in Windows just double-click
| | 02:52 | on this shortcut and
type mysql and there we are.
| | 02:58 | Just one more quick trick here
about the command line shortcut.
| | 03:02 | You'll notice when we bring up this
window, it's 80 characters wide and 24
| | 03:05 | characters high and those numbers
actually have some historical significance,
| | 03:09 | but it's been so long ago that it's
become hysterical significance instead of
| | 03:12 | historical and we want to change it.
| | 03:14 | The problem is that when you just drag
the corner of this window, in Windows, it
| | 03:18 | doesn't actually change
the dimensions of the window.
| | 03:22 | It just changes the dimensions of the
screen part of it and that doesn't work.
| | 03:26 | So what you really need to do is you
need to change it in the properties of the
| | 03:29 | shortcut, which makes no sense
whatsoever, which is why I'm showing you this
| | 03:32 | trick, because it's hard
to figure out on your own.
| | 03:35 | So I'm going to right-click on the
shortcut, which brings up little context menu
| | 03:39 | and select Properties and select
Layout and this is where you change it.
| | 03:44 | You see it's kind of buried in there
and I'm going to make it 100X50 and you
| | 03:46 | have to change it in both places,
which also makes lots of sense.
| | 03:52 | You'll notice that this little
preview shows me how much of the screen
| | 03:55 | real estate is taken up.
| | 03:56 | Your screen is probably bigger than mine.
| | 03:58 | Mine is this size, because we are making a
movie that you can watch on your desktop.
| | 04:02 | Your screen is probably bigger.
| | 04:03 | So play with these numbers and have
it take up most of the screen real
| | 04:06 | estate like that, because that's
going to come in handy as we use this
| | 04:10 | window in the later lessons.
| | 04:12 | So I'm going to go ahead and
show you what that looks like.
| | 04:14 | You just double-click on it and there it is.
| | 04:16 | You've now resized this window.
| | 04:18 | Wasn't that intuitive?
| | Collapse this transcript |
| Using the MySQL Command Line Interface on a Mac| 00:00 | We're going to be using the MySQL
command line client in the next few lessons.
| | 00:05 | So let's take a look at how to run
this MySQL command line client on the Mac.
| | 00:10 | We'll start by bringing up
our terminal application.
| | 00:12 | In this case I'm using iTerm, but you
can use Terminal that comes with OS X.
| | 00:18 | At the command line, I simply type mysql and
that brings up the MySQL command line client.
| | 00:24 | There are other options that we'll be
using sometimes and we'll talk about those
| | 00:28 | when we get to them, but that's really it.
| | 00:30 | Once you're in the MySQL command line
client, you can type your SQL right here.
| | 00:34 | Like for instance, I'll
just type show databases.
| | 00:38 | You always need to use a semi-colon at
the end of your SQL commands in the MySQL
| | 00:44 | command line client.
| | 00:46 | There we have the databases that are
available at this time in the MySQL command
| | 00:51 | line client and that's really it to
exit the client you type exit and then you
| | 00:55 | exit again to exit the command
line shell, and there you have it.
| | 00:59 | That's how you run the MySQL
command line client on the Mac.
| | Collapse this transcript |
| Setting up the root user| 00:00 | Now we're going to set up
the root user in the MySQL.
| | 00:03 | Users in MySQL are different
than users in your operating system.
| | 00:07 | You may login to your computer using a
particular user name and password, but
| | 00:11 | MySQL requires a separate login for its
purposes with a separate user name and a password.
| | 00:15 | MySQL has a special super user called
root that has privileges for all actions
| | 00:20 | including creating other user.
| | 00:22 | This is a necessary
starting point to set up a system.
| | 00:26 | By default, the root user is
installed without a password.
| | 00:30 | So anyone would access to the system would
be able to create and destroy users and data.
| | 00:35 | If your installation is being set up
on a private system on a private network
| | 00:39 | only for the purpose of testing
and debugging, this may be okay.
| | 00:42 | But for production system, you
must correct this before proceeding.
| | 00:46 | To make matters just a little more
confusing, MySQL is often installed with
| | 00:50 | more than one root user.
| | 00:52 | The reason for this is also confusing.
| | 00:54 | It has to do with how MySQL
maintains its access privilege system.
| | 00:58 | But the point is important.
| | 00:59 | There are usually two root users and
there may be more and you'll have to
| | 01:03 | update all of them.
| | 01:06 | So let's start up the command line MySQL client.
| | 01:11 | Double-click on the shortcut that we
created earlier and type mysql -u root and
| | 01:19 | what this does is it starts the MySQL
command line client with the root user and
| | 01:24 | this allows you to change
things like the user database.
| | 01:28 | MySQL keeps all of its user credentials in a
database called MySQL and a table called user.
| | 01:37 | So we're going to use SQL to update the
user table in the MySQL database and set
| | 01:43 | the password in all the
rows where the user is root.
| | 01:47 | So here is the SQL to do
that. UPDATE mysql.user.
| | 01:54 | That means the user table in the MySQL database.
| | 01:58 | SET Password, that's the password column
in the user table in the MySQL database
| | 02:08 | = PASSWORD, and this is the password
function which encrypts a password for the
| | 02:14 | purpose of storing it in this table.
| | 02:17 | We're going to put our password in quotes here.
| | 02:19 | I'm just going to call it foo.bar.
| | 02:21 | It's a simple to remember, temporary
password for the purpose of this lesson.
| | 02:26 | Please don't make your root password to that.
| | 02:28 | Come up with something unique for
your installation WHERE User=root.
| | 02:36 | That will update this table only for the
rows where the user column is equal to root.
| | 02:46 | So how ever many root users are in
this system, and I happen to know that
| | 02:49 | there are two here.
| | 02:50 | We enter a semi-colon and press Enter.
| | 02:54 | We'll see that this has affected two
rows and it changed two values and two rows.
| | 03:00 | Now that we're updated the user table
and inserted these passwords, we need to
| | 03:06 | get MySQL to reread it's database so
that it can apply these changes to the
| | 03:14 | system that's being used and we do
this with the special FLUSH command.
| | 03:18 | FLUSH PRIVILEGES and what this does is
it tells MySQL to reread these tables
| | 03:26 | so that it will update the passwords and
memory and be able to start using them right now.
| | 03:30 | So press Enter and we'll
exit and start up again.
| | 03:35 | If I type mysql -u root, you'll see that
we can't get in because we're not using
| | 03:40 | the password now and so I put a -p at
the end and this tells it to ask for a
| | 03:45 | password and we'll type in the
password foo.bar, and there we are.
| | 03:50 | So we've updated the password for the
root users and we've tested it and we know
| | 03:55 | that it works and that's how we
set up the root user in MySQL.
| | Collapse this transcript |
| Setting up a regular user| 00:00 | Now we are going to talk about
setting up regular users in MySQL.
| | 00:03 | Remember users in MySQL are different
than users in your operating system.
| | 00:08 | You may login to your computer using
a particular username and password.
| | 00:12 | MySQL requires a separate login for its
purposes with a separate username and password.
| | 00:17 | MySQL uses a complex and
flexible access privilege system.
| | 00:22 | It's possible to grant very
specific privileges to particular users on
| | 00:26 | individual databases and on
individual systems or across the system.
| | 00:31 | Because the privilege system is so
powerful, it's important that you setup users
| | 00:35 | carefully so that they have the
privileges that they need to do their job, but
| | 00:39 | you are still able to
prevent costly abuse and accidents.
| | 00:43 | Keep in mind the users
are not necessarily people.
| | 00:46 | They can be programs or other processes as well.
| | 00:49 | I think of them as roles so that I
may have a web user that's for web based
| | 00:53 | applications like the SID application
or an admin account that I'll use from my
| | 00:58 | day-to-day administrative work.
| | 01:00 | Users are distinguished by
both username and host name.
| | 01:03 | So a user at local host is
different than user at some domain.com.
| | 01:08 | So let's go ahead and start up the
MySQL command line client, double-click on
| | 01:13 | the shortcut that we created earlier
and type mysql -u root -p, so that will
| | 01:20 | use the root user and it will ask for the
password which we setup in the last lesson.
| | 01:24 | It's foo.bar.
| | 01:26 | Now we are in the MySQL command line
client and we are as the root users, so we
| | 01:32 | can do dangerous things.
| | 01:34 | Now this is probably last time we are
going to use the root user, because one of
| | 01:37 | the things we'll do here is setup
the admin user, which we'll use for
| | 01:41 | administrative work in the future.
| | 01:43 | I want to go ahead and create two users.
| | 01:45 | One of them for web work and
one for administrative work.
| | 01:50 | CREATE USER web@localhost;
| | 01:54 | and CREATE USER admin@localhost;
| | 02:00 | Now by using the @localhost syntax,
that means that these users are restricted
| | 02:05 | to operating from the local host.
| | 02:08 | If they connect into this computer
from another computer either on the local
| | 02:11 | network or out on the
Internet, this login will not work.
| | 02:15 | You will need a separate user
account for outside of this computer.
| | 02:19 | Now the users have been created, but they
have no privileges so you can't use them.
| | 02:23 | We need to create privileges for these users.
| | 02:26 | Once a user is created, you grant
privileges using the Grant statement.
| | 02:31 | For the web user, you need all the
basic data permissions plus, permissions
| | 02:35 | necessary for managing a database.
| | 02:37 | So this is everything except
the administrative purposes.
| | 02:40 | Now these privileges get specified individually.
| | 02:43 | So there is a bit of typing here.
| | 02:45 | Let's go ahead and type the command.
| | 02:47 | GRANT SELECT, INSERT, UPDATE, DELETE,
CREATE, DROP, FILE, INDEX, ALTER, CREATE
| | 03:01 | TEMPORARY TABLES, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE,
| | 03:17 | EXECUTE, ON *.* TO web@localhost;
| | 03:26 | So these are a number of privileges
listed individually each one separated by a
| | 03:31 | comma and a space and these privileges
are granted on *.* which means that the
| | 03:37 | user can use these privileges on
all the tables in all the databases to
| | 03:42 | web@localhost which grants these
privileges to the web user on local host that
| | 03:47 | we setup a view minutes ago.
| | 03:49 | So I'll hit Enter, and we'll go ahead
now and grant privileges to the admin user.
| | 03:54 | Now this is a bit easier because the
admin user gets all privileges and there
| | 03:59 | is shortcut for that. GRANT ALL ON *.*.
| | 04:02 | That grants all privileges on all
tables in all databases to admin@localhost
| | 04:10 | with Grant option and that means that
not only our all of these privileges
| | 04:15 | granted to this user, but this user
also has the privilege of granting
| | 04:19 | privileges to other users.
| | 04:21 | Go ahead and hit Enter and now we'll
issue the FLUSH PRIVILEGES command.
| | 04:26 | Now remember what this does is it tells
the MySQL database management system to
| | 04:35 | go ahead and read the privileges from
the database again so these privileges
| | 04:39 | can be used right away.
| | 04:42 | Now we'll go ahead and assign passwords
to these two users and we do that in the
| | 04:48 | user table the same way that
we did it for the root user.
| | 04:51 | UPDATE mysql.user SET PASSWORD =
PASSWORD <' foo.baz'> WHERE user = 'web ' ; and
| | 05:15 | we'll do the same thing for the admin.
| | 05:19 | I'm just going to hit the Up
arrow and go ahead and admin.
| | 05:24 | You wouldn't do this in the real world,
but for our purposes for demonstration,
| | 05:28 | I'm just setting the password to the
same for both of them and there we go.
| | 05:32 | Now I need to FLUSH PRIVILEGES again.
| | 05:38 | We FLUSH PRIVILEGES and now
let's go ahead and test them.
| | 05:47 | Try the web user first and you see
it needs a password. So we'll -p.
| | 05:51 | I like to test things without
the password just to make sure.
| | 05:55 | Let me go ahead and type the password,
foo.baz, and there we are. We are in.
| | 06:02 | If I say show databases so we can
see that it's working and it has
| | 06:05 | authorization to the system.
| | 06:07 | Go ahead and exit and try the admin user.
| | 06:11 | Again, it doesn't work without the password, -p.
| | 06:19 | Type the password and
there we are. Show databases.
| | 06:24 | We can see that that works.
| | 06:25 | All right, so that's how we setup the
users and you would go through that same
| | 06:29 | process for any other users that you may have.
| | Collapse this transcript |
| Importing databases on Windows| 00:00 | Sometimes you may need to import some
data into a database as a starting point.
| | 00:05 | For example, when you are installing
an application that uses MySQL, like the
| | 00:08 | example applications provided with this course.
| | 00:11 | Here is how you do that.
| | 00:12 | Let's go ahead and start
up the command line client.
| | 00:16 | We'll double-click on the shortcut
that we created earlier and you'll notice
| | 00:21 | that this drops us into the SQL
directory in the Exercises Folder.
| | 00:26 | If I type dir here, it will give
us a listing of this directory.
| | 00:30 | We'll see that there is three files,
album-mysql.sql, test-mysql.sql,
| | 00:38 | and world-mysql.sql.
| | 00:40 | What these are, are files with SQL
commands that create and populate databases,
| | 00:48 | album database, test database,
and world database respectively.
| | 00:52 | The way these are used is by
piping them into the MySQL command line
| | 00:56 | client using a special pipe
character that will read the input to a
| | 01:01 | program from another source.
| | 01:03 | In this case, a file
rather than from the keyboard.
| | 01:06 | So let's see how this works.
| | 01:08 | So you type in the command line here,
mysql -u admin -p, so that invokes the
| | 01:23 | MySQL command line client with the
admin user and asks for a password.
| | 01:28 | Then you see the left angel bracket is
the pipe character that reads input from
| | 01:33 | a file instead of the keyboard and
then the name of the file album-mysql.sql.
| | 01:40 | So when I hit the Enter key, this will
invoke the MySQL program with the admin user.
| | 01:47 | It will ask me for the password and
then it will read all of the SQL commands
| | 01:51 | from this file and use them instead of
the keyboard and it will just give me
| | 01:55 | back the command prompt here.
| | 01:56 | I am going to type the
password and there we are.
| | 02:03 | It has now created the album database
and populated that database with the
| | 02:07 | data from that file.
| | 02:08 | Let's go ahead and do the other two
and then we'll go into MySQL command line
| | 02:12 | client and look at what it did.
| | 02:14 | I am going to go ahead and hit the Up
arrow and then edit the command line so I
| | 02:18 | don't have to type the
whole thing again. Here we go.
| | 02:27 | Type the password and do the
same thing with the world database.
| | 02:42 | Now we have imported all three
of these databases into our MySQL.
| | 02:46 | I'm going to go ahead and run MySQL
and we'll take a look at what we have.
| | 02:58 | First, we'll use the test database. use test;
| | 03:03 | What this does is it tells MySQL that
the commands that I'm going to give it
| | 03:06 | from here on forward are relative to
the test database and I'll take a look at
| | 03:13 | the tables with the show
tables command, show tables, semicolon.
| | 03:18 | Let's take a look at what's
in item, select * from item;
| | 03:23 | Here we have four items in our
item table in the test database.
| | 03:30 | So we have imported this data
from the test-mysql.sql file.
| | 03:36 | Let's take a look at the
album database. use album;
| | 03:41 | Remember the semicolon
terminates a command in SQL. show tables;
| | 03:47 | I have two tables in the
album database, album and track.
| | 03:52 | The album table has information that's relative
to an album like the artist and what have you.
| | 03:59 | And the track table has the
data that's relative to the track.
| | 04:02 | So let's take a look at the album table first.
| | 04:05 | select * from album;
| | 04:06 | Here are the albums in our album table.
| | 04:12 | This is actually the database that's used
for the CRUD application that we'll see later.
| | 04:16 | You will see that there are a number of albums.
| | 04:19 | It looks like seven albums in that
table and the tracks will list each of the
| | 04:23 | tracks for all of those albums.
| | 04:25 | select * from track;
| | 04:29 | and that scrolls on by.
| | 04:30 | That's got all of those tracks in it.
| | 04:33 | Now also take a look at the
world database. use world;
| | 04:37 | Now the world database has bigger tables.
| | 04:41 | Show tables, city, country, language and
you will notice select count * from city;
| | 04:53 | There are 4,000 rows in the city table.
| | 04:59 | select count * from country and there are
likewise 239 rows in the country database.
| | 05:08 | So we have successfully imported data
for three different databases using the
| | 05:13 | command line very easy, very little
typing and now we have gone in and we've
| | 05:17 | confirmed that the data is there.
| | 05:19 | So now you know how to import data.
| | 05:22 | This is a common way for the data to
be distributed within an application in
| | 05:27 | SQL files like that.
| | 05:29 | So this is how you import those SQL
files into MySQL from the command line.
| | Collapse this transcript |
| Importing databases on a Mac| 00:00 | Sometimes you may need to import some
data into a database as a starting point,
| | 00:04 | for example, when you are installing
an application that uses MySQL like the
| | 00:08 | example applications provided with this course.
| | 00:11 | Here is how we do that on a Mac.
| | 00:12 | I am going to start up my terminal application.
| | 00:16 | In this case, I'm using iTerm.
| | 00:17 | You can use the Terminal application
that comes with Mac OS X if you prefer.
| | 00:22 | Now before I invoke the MySQL
Command-Line client, I'm going to need to
| | 00:26 | change directories into the
directory that has the SQL files that we are
| | 00:30 | going to be importing.
| | 00:31 | So we use the cd command for that.
| | 00:34 | cd stands for change directory.
| | 00:36 | And then, you type the
directory path from where you are.
| | 00:39 | So in this case, type Desktop and you
will notice that I just typed the first
| | 00:43 | couple of letters and then I pressed
the Tab key, which completes the typing.
| | 00:48 | So I don't have to type it myself and
the computer figures it out from what I
| | 00:52 | have started with, and it
spells it correctly and everything.
| | 00:55 | So for example, after Desktop, I want
the Exercise Files directory, so I type
| | 01:02 | Ex and that's enough, and I press the Tab key
and it goes ahead and completes that for me.
| | 01:06 | You will notice also, I'm going to
bring the mouse cursor over here to point.
| | 01:09 | You'll see this Backslash before the
space between the word Exercise and Files.
| | 01:14 | That's actually necessary.
| | 01:15 | That tells the operating system that
that's a literal space and it's not a space
| | 01:20 | separating two commands.
| | 01:22 | Then after Exercise Files, I want the
SQL folder inside the Exercise Files
| | 01:27 | folder, and I press Enter, and
now we are at the correct folder.
| | 01:33 | So I'm going to type another UNIX command here.
| | 01:35 | It's called pwd, which stands
for print working directory.
| | 01:39 | This will give us the directory path
where we are, and we can confirm that where
| | 01:42 | we want to be and that's correct.
| | 01:45 | Users/billw/Desktop/Exercise Files/SQL.
| | 01:48 | That's the path to the
directory where we want to be.
| | 01:51 | I am gong to type another UNIX command,
which is ls, which is for listing the
| | 01:56 | files in a directory.
| | 01:57 | We can see we have three files in
this directory, album-mysql.sql,
| | 02:02 | test-mysql.sql, and world-mysql.sql.
| | 02:10 | These are the files that contain the
SQL commands for creating and populating
| | 02:16 | the databases that we want to
import into our installation.
| | 02:20 | We do this by using what's called a pipe.
| | 02:23 | What we are going to do is we are
going to invoke the MySQL command-line
| | 02:27 | program with a pipe.
| | 02:29 | What a pipe does is it reads commands
into a program from something other than a
| | 02:34 | keyboard and we are going to use
the pipe that reads from a file.
| | 02:37 | This is really a lot more simple than it sounds.
| | 02:39 | So let's take a look at how this works.
| | 02:42 | I am going to type now, mysql -u admin -p.
| | 02:50 | So that MySQL will ask for the
password and then the pipe character and I'm
| | 02:56 | going to use the test database first,
and I can use command completion again.
| | 03:00 | I can just type the first few letters
and press the Tab key and the computer
| | 03:05 | figures out from those first few
letters what it is that I meant to type and it
| | 03:09 | completes it for me.
| | 03:10 | And so that's the test-mysql.sql file.
| | 03:14 | So what this command does is it invokes
the MySQL command-line program with the
| | 03:18 | user admin, it will ask for the
password, and then it will read the data from
| | 03:23 | the file as if I was typing
that into the interactive program.
| | 03:28 | And then it will finish. So here we go.
| | 03:32 | So there we have it.
| | 03:33 | That command completed.
| | 03:34 | We'll take a look, in a minute, inside
the MySQL command-line application to see
| | 03:41 | the data that we have imported.
| | 03:42 | But let's go ahead first and
import these other databases first.
| | 03:47 | So we have the same command, all we
need to do is to change the file name.
| | 03:51 | So I'm just going to press the Up arrow
on my keyboard, and that will bring up
| | 03:55 | the command that I last entered.
| | 03:57 | Let me use the Backspace key
here, and type the album database.
| | 04:02 | al should be enough, I press Tab, and
it completes and I type in the password
| | 04:09 | and finally the world database.
| | 04:12 | We'll do the same thing.
| | 04:14 | Press the Up arrow and Backspace and
type world, and Tab, and type the password,
| | 04:26 | and there we have it.
| | 04:27 | Now we have imported the three
databases into the MySQL database application.
| | 04:34 | Now let's go into the command-line
application and take a look at the data.
| | 04:39 | mysql -u admin -p foo.baz and there we are.
| | 04:47 | We are in the MySQL command line
application, and we can take a look at our deal.
| | 04:53 | Let's start with the test data.
| | 04:54 | We'll say use test;
| | 04:56 | Remember we always have to press the
semicolon at the end of our SQL inside the
| | 05:01 | command-line application.
| | 05:03 | Use test means to use the test database for
the command that follow and let's take a look.
| | 05:11 | We'll show tables;.
| | 05:14 | We have there tables.
| | 05:16 | Let's take a look at some of the data
in one of those tables, select * from
| | 05:20 | item;, and that will show all
the data in the item, table.
| | 05:26 | There it is and let's use the album database.
| | 05:34 | The album database is what's used in the CRUD
application that we'll be taking a look at later.
| | 05:38 | So tables.
| | 05:40 | Here is the tables in the album database.
| | 05:43 | Let's take a look at the data
inside the album table, there it is.
| | 05:53 | And finally, use world.
| | 05:56 | The world database is huge.
| | 05:58 | It's got a lot of data in it, show tables.
| | 06:01 | For example, if you take
a look at the City table,
| | 06:06 | Well, there we are, we have successfully
imported three databases into our MySQL
| | 06:20 | installation, and we have looked and
seen that the data got there safely.
| | 06:24 | Now we can just exit and type exit again to
get out of our terminal app and we are done.
| | Collapse this transcript |
|
|
4. Using the SQL Interactive Demonstrator (SID)Using SID| 00:00 | I have created a simple interface for
showing examples and for use by those of
| | 00:04 | you who need a simple way of typing
in some SQL and viewing the results.
| | 00:08 | I call this app SID for
SQL Interactive Demonstrator.
| | 00:12 | You don't need to use it.
| | 00:13 | It's included in the Exercise Files
and it's a great way to follow along, as
| | 00:17 | I'll be demonstrating the
examples in MySQL using SID.
| | 00:20 | SID requires MySQL, PHP, and a web
server, typically Apache, which are all
| | 00:26 | available for Windows, Mac OS X,
Linux and most other operating systems.
| | 00:30 | I suggest the XAMPP package for
Windows and OS X because it's easy to install
| | 00:35 | and it includes all the necessary components.
| | 00:37 | Most Linux applications already have
the required packages already installed.
| | 00:42 | So this is what SID is.
| | 00:43 | You select your database over here
that you are going to be operating on, and
| | 00:46 | you type sql in right here.
| | 00:52 | You don't need to put a semicolon at the
end of the sql in SID, and you just hit
| | 00:56 | Go, and there is the results.
| | 01:00 | Like I said it's easy to use, and that
was the reason that I designed and built
| | 01:03 | this is to make it easy and quick to
demonstrate and try out SQL statements.
| | 01:09 | I'll be using this to demonstrate the
SQL throughout the remainder of this
| | 01:12 | course and the lessons that immediately
follow this one show you how to install
| | 01:17 | SID on Windows and Mac OS X.
| | Collapse this transcript |
| Installing SID on Windows| 00:00 | Now that we have imported the databases,
let's go ahead and install the SID application.
| | 00:05 | SID stands for SQL Interactive
Demonstrator, and it's a program that I created
| | 00:11 | for the purpose of demonstrating SQL
on these courses here on Lynda.com.
| | 00:16 | We'll be using the SID application for
a lot of the remainder of this course
| | 00:20 | to demonstrate the various features that are
specific to the MySQL Database Management system.
| | 00:25 | You will find SID in the Exercise Files folder.
| | 00:28 | So, we'll go ahead and open that up.
| | 00:32 | SID is in that folder.
| | 00:34 | They are in the SID folder inside of it.
| | 00:36 | I'll bring this down a little bit.
| | 00:37 | What we are going to do is we are going
to copy this whole SID folder into the
| | 00:42 | htdocs folder under the xampp directory.
| | 00:46 | So, go ahead and find xampp.
| | 00:48 | It's under Computer and Local C Disk and xampp.
| | 00:52 | You remember we installed it there.
| | 00:53 | If I open this up, you will see
there is a folder called htdocs.
| | 00:57 | This is where the files go that
are used for the Apache Web Server.
| | 01:03 | So, we simply drag this folder, and
I'm going to hold down the Ctrl key which
| | 01:06 | makes it Copy instead of moving, so
that we are installing a Copy of it, and we
| | 01:10 | have the original still available there.
| | 01:13 | I'm going to drag that into the htdocs
folder, so it says Copy to htdocs and
| | 01:18 | I'll let go, and there it is.
| | 01:20 | Now, we'll go into this SID folder that
we just copied over and you see there is
| | 01:26 | a file that are called sid.
| | 01:27 | When I hover the mouse over it,
it says Type is PHP Script.
| | 01:31 | It's actually called sid.php and
Windows, by default, hides the file name
| | 01:37 | extensions of files.
| | 01:39 | You can turn this back on in an option
in Windows and make it show them, but we
| | 01:43 | don't need to do that.
| | 01:44 | We are just going to edit the file.
| | 01:45 | I'm going to right-click on it with my
mouse, right-click with the mouse and
| | 01:49 | select Edit with Notepad ++.
| | 01:51 | When we install Notepad ++, it put
this context menu for the explorer.
| | 01:57 | So, I'll press on that and it opens up.
| | 02:00 | So, the reason we are editing this
file is to enter the correct username and
| | 02:04 | password for accessing the MySQL database.
| | 02:08 | So, here we have towards the top of
the program and whenever you have to edit
| | 02:11 | a program for this, it's usually going to
be towards the top, some place easy to find.
| | 02:14 | Here we have the place where you enter
the user name and password for MySQL.
| | 02:19 | And in this case it's easy to see
because it says MYSQLUSER and MYSQLPASS
| | 02:24 | and sometimes there will be comments that say
edit this here or instructions will come with it.
| | 02:29 | In this case, this is where we do it.
| | 02:31 | So, we have Web already as the user and
that's the username that we want, so we
| | 02:35 | don't need to change that.
| | 02:37 | And over here in PASS I'm going to put
my cursor between these two quote marks
| | 02:40 | and I'm going to type the password
that we entered in the MySQL Command-Line
| | 02:44 | client when we set up the web at
local host user, I'll put in foo.baz.
| | 02:48 | Now we'll notice that it
doesn't say web at local host.
| | 02:52 | It just says web, the out local host
apart means that it will be connecting
| | 02:57 | with the username web from the local host
and so, this just needs to say web right here.
| | 03:03 | So, we are actually done editing
this file so we'll go ahead and save it.
| | 03:07 | I'll save and we'll close out text
editor and we'll close this Explorer window
| | 03:16 | and go ahead and start up the web
browser, Firefox, and go ahead and type in
| | 03:21 | here locahost/SID/sid.php, which
is the name of the application.
| | 03:31 | Press Enter and this
brings up the SID application.
| | 03:35 | There it is SQL Interactive Demonstrator.
| | 03:39 | Now, how this works is you select a
database and we have the choice of the
| | 03:42 | three databases that we imported already, and you
type the SQL in this box over here and press Go.
| | 03:48 | So, let's go ahead and try it out, we'll
select the test database and we'll type
| | 03:53 | in some SQL, SELECT * FROM
item and go ahead and press Go.
| | 04:01 | The semicolon is not necessary in this
context, go ahead and press Go and there
| | 04:06 | we have the contents of the item
table in the test database, so it works.
| | 04:12 | We've successfully imported the
databases and we successfully installed SID.
| | 04:17 | That's pretty easy to do.
| | Collapse this transcript |
| Installing SID on a Mac| 00:00 | Now that we have imported our databases,
let's go ahead and install the SID application.
| | 00:05 | SID stands for SQL Interactive Demonstrator.
| | 00:08 | It's a program that I created for
these SQL courses here on Lynda.com for
| | 00:12 | demonstrating SQL commands, and we'll
be using this application for most of the
| | 00:17 | rest of the this course.
| | 00:19 | The SID application is in the Exercise
Files folder in the SID folder within
| | 00:26 | there and we are going to be installing
it in the Apache web tree in the XAMPP
| | 00:32 | Applications folder.
| | 00:33 | So go down here in Applications and
find xampp and you see this htdocs folder.
| | 00:42 | It's actually a shortcut
that's where you put the files.
| | 00:45 | It will show up in the Apache web tree.
| | 00:47 | So, files that you want to be
available to Apache for displaying on a web
| | 00:51 | page, you put in there.
| | 00:52 | So, all we need to do is to drag, this
SID folder and I'm going to hold down
| | 00:57 | the Option key, so that it copies instead of
moving and drop it in the htdocs folder over here.
| | 01:05 | And then we are going to need to come
into the htdocs folder and go into SID and
| | 01:10 | we are going to want to edit the sid.php
file, so that we can enter the username
| | 01:16 | and password so that SID is
able to access the database.
| | 01:21 | So, I'm going to right-click or you can
Ctrl-click if you have a single button
| | 01:25 | mouse and Open With and I'm going to
select my text editor which in this case
| | 01:31 | is BBEdit, you can use whatever text
editor you are using, you should be able
| | 01:35 | to access it this way.
| | 01:36 | I am going to click on that, and that
will open the sid.php file and over here
| | 01:44 | in the first few lines is where you find
the username and password for accessing MySQL.
| | 01:50 | Username is already correct, it's web,
and we want to put in the password that
| | 01:53 | we entered in our database, and that was
foo.baz for the web at local host user.
| | 02:00 | Now, you don't need to put web at local
host over here in the USER, you just put
| | 02:04 | web and local host is the name of
the host that is accessing from.
| | 02:08 | So, this is the way that it needs to
be, we'll go ahead and Save and close
| | 02:15 | BBEdit and we can close these folders
here and go ahead and run our web browser
| | 02:27 | and we should be able to access it.
| | 02:30 | I'm going to type localhost/SID/sid.php
and there is SID and the way that
| | 02:42 | SID works is you select a database and here
is the databases that we already imported.
| | 02:46 | You type in your SQL over here
and press Go. So, let's try it.
| | 02:51 | Test database and we'll type
SELECT * FROM item, just like we tested it in
| | 02:59 | the command line client and there is the data.
| | 03:02 | We have got 4 rows.
| | 03:03 | It tells you how quick it happened and
there's the rows of data, so that's it.
| | 03:09 | We have installed SID successfully and we
are ready to go with the rest of the course.
| | Collapse this transcript |
|
|
5. Creating a DatabaseCreating a database| 00:00 | In MySQL's model, as with most
relational database management systems, tables
| | 00:04 | are contained within a database.
| | 00:06 | So before you can create a
table, you must create a database.
| | 00:10 | MySQL has a simple
statement for creating a database.
| | 00:16 | The Create Database statement creates
a database and you follow that with the
| | 00:20 | name of the database you want to create.
| | 00:23 | Press Go and it's done.
| | 00:24 | You can use Show Databasea to see
the databases that are available.
| | 00:33 | Here we see the sales database.
| | 00:35 | It's right in there that we just created.
| | 00:37 | Then you can use DROP Database to
delete a database that already exists.
| | 00:42 | This is very dangerous, you don't want to
do this with the database that you need.
| | 00:46 | It's not recoverable.
| | 00:47 | It doesn't put it a
trashcan someplace. It erases it.
| | 00:56 | Drop Database sales deletes the sales
database, the entire thing with all of its
| | 01:01 | tables and all of its data.
| | 01:03 | It doesn't ask you for confirmation.
| | 01:05 | Drop Database sales deletes the sales database.
| | 01:07 | So now if we do Show DatabaseS again,
you'll see that the sales database is now gone.
| | 01:16 | So that's how you create and
destroy a database in MySQL.
| | Collapse this transcript |
| Creating a table| 00:00 | As a database contains tables, a table
contains all the rows and columns for
| | 00:04 | a given set of data.
| | 00:05 | A table definition contains
all the details of a data set.
| | 00:09 | Because of this, the CREATE TABLE
statement is a little more complex than the
| | 00:13 | CREATE DATABASE statement.
| | 00:15 | The CREATE TABLE statement is how you
define a table including its columns,
| | 00:19 | storage options, auto-
increments, keys and much more.
| | 00:23 | Here's an example of a CREATE
TABLE statement from the test database.
| | 00:26 | It's included with this course.
| | 00:28 | So this is the CREATE TABLE statement
and the name of the table is after the
| | 01:18 | CREATE TABLE keywords.
| | 01:19 | You'll notice I named it test_customer,
because there is already a customer
| | 01:23 | in the test database.
| | 01:25 | The id column, name, address, city,
state and zip, these are the columns.
| | 01:30 | The details of these various data
types and options, we're going to cover
| | 01:35 | some of those later.
| | 01:36 | But let's just briefly
take a look at how this works.
| | 01:39 | The id column is integer and has these options:
| | 01:43 | NOT NULL, AUTO_INCREMENT, PRIMARY KEY.
| | 01:46 | The AUTO_INCREMENT option is actually
very useful and I'm going to just tell you
| | 01:49 | a little bit about that right here.
| | 01:51 | It's common for each row that's inserted.
| | 01:53 | MySQL will automatically place the
next incremental value in this column.
| | 01:57 | It's very useful for id rows.
| | 01:59 | The name is a variable number of
characters between 0 and 255 characters.
| | 02:05 | Address, city are the same, state is a
fix number of characters, two characters,
| | 02:09 | zip is up to ten characters.
| | 02:11 | We'll go ahead and hit Go.
| | 02:13 | That will create the table.
| | 02:15 | You notice it didn't work because
we haven't selected a database yet.
| | 02:18 | A table is contained within a database,
so we need to select a database in order
| | 02:23 | to create the table inside of that database.
| | 02:25 | I'll go ahead and press Go, and there we go.
| | 02:28 | The table got created and now we can go
and show tables and we'll see that the
| | 02:35 | table is in our list of tables.
| | 02:38 | There it is, test_customer.
| | 02:39 | Just like SHOW DATABASES
shows you a list of databases.
| | 02:43 | SHOW TABLES shows a list of
tables from within a given database.
| | 02:48 | When I'm done with that table, I can use
DROP TABLE, just like the DROP DATABASE
| | 02:53 | will delete a database.
| | 02:55 | DROP TABLE test_customer will delete
the table and all the data within it.
| | 03:00 | Again, this does not put it in a
trashcan someplace where you can recover it.
| | 03:03 | This is destructive.
| | 03:05 | When you drop a table, you
lose all the data in that table.
| | 03:10 | So now the table is gone, we'll go ahead
and show tables and you'll see that the
| | 03:17 | table is no longer in our list there.
| | 03:20 | So that's the CREATE TABLE statement.
| | 03:22 | It's generally a good idea to know how
this works, even if you're not going to
| | 03:25 | be creating your table definitions yourself.
| | Collapse this transcript |
|
|
6. MySQL Data TypesWhat are data types?| 00:00 | Let's take a moment and talk about data
types in general before we get into the
| | 00:03 | specifics of how MySQL
represents data with its data types.
| | 00:08 | Data types are important because
they're how the database stores and
| | 00:12 | represents its data.
| | 00:13 | If you understand the data types, you'll
have a much easier time working with the data.
| | 00:17 | MySQL has data types for numeric data,
for representing numbers, string types
| | 00:23 | for storing words and text, large
storage types for storing documents and files,
| | 00:29 | data and time types for storing date
and time values, bit values for storing
| | 00:35 | logical values in flags and
enumeration types for mnemonic values.
| | 00:40 | MySQL has a rich set of data types.
| | 00:43 | This chapter covers the
most common and useful types.
| | 00:46 | If you're interested in knowing the
full breadth and depth of what MySQL has
| | 00:50 | available, please see the
documentation on the MySQL website.
| | Collapse this transcript |
| Numeric types| 00:00 | There are three major kinds of
numeric types, integer types, floating point
| | 00:04 | types and fixed point types.
| | 00:07 | Integer types are for storing whole
numbers, i.e., numbers that don't have a
| | 00:11 | decimal point or a fractional
part, numbers like 1, 2, 3 and 4.
| | 00:16 | Floating point types are for storing real
numbers, numbers like 3.14159 or 147.39.
| | 00:24 | Use floating point storage for
applications where the range of mathematical
| | 00:28 | results is more important in
the accuracy of a calculation.
| | 00:32 | Fixed point types are for storing
decimal numbers that have a fixed precision.
| | 00:36 | This is a special type that is
useful in situations like financial
| | 00:40 | applications, where arithmetic
functions must return accurate results rounded
| | 00:45 | to a specific number of digits.
| | 00:47 | The distinction between floating point
and fixed point types is very important.
| | 00:51 | So let's take a look at an example.
| | 00:53 | In the test database, you'll
find a table called numerics.
| | 00:56 | I'll type DESCRIBE numerics and that
will give us the structure of the table.
| | 01:05 | It has an id field and it has four numeric
fields, two decimal and two floating point.
| | 01:12 | The decimal types are da and db for
decimal a and decimal b and the floating
| | 01:18 | point columns are fa and fb for
floating point a and floating point b.
| | 01:24 | There's one row of data in the table and
you'll see it has four values, 0.10 and
| | 01:36 | 0.20 in decimal, and 0.1
and 0.2 in floating point.
| | 01:41 | The decimal values have the decimal
point in a fixed place and a fixed number of
| | 01:45 | digits after the decimal point.
| | 01:47 | The floating point numbers do not have a
fixed number of places after the decimal point.
| | 01:52 | So they just show 0.1 and 0.2.
| | 01:54 | Now if we take the decimal values, da
and db, and add them together, say da+db,
| | 02:04 | you'll see we get the expected result,
da is 0.10, db is 0.20 and da+db is 0.30.
| | 02:11 | Now if we test this for equality with
.3, in other words, we're testing the
| | 02:16 | result of da+db against a literal
value of .3, we should get a true answer.
| | 02:23 | True would be 1 and false would be 0.
| | 02:26 | So we expect to get a 1 here.
| | 02:28 | So this is what we expected, da+db = .3 is true.
| | 02:35 | Now let's try the same little
experiment with the floating point values.
| | 02:39 | Take fa and fb, which are 0.1 and 0.2,
and we'll take the sum of fa and fb,
| | 02:46 | which is 0.3, and we'll test it
against .3 and see if that comes out as true.
| | 02:53 | We notice in this case it
is false, 0 means false.
| | 02:57 | We might say well, if fa+fb is not
equal to .3, what is it equal to?
| | 03:03 | Let's just take out the = .3
part here and see what fa+fb is.
| | 03:11 | That's not really what we expected, is it?
| | 03:14 | It's 0.3, which is accurate to about, I
don't know, 6 or 7 decimal places, and
| | 03:21 | then a bunch of junk.
| | 03:22 | This is not equal to 0.3,
and that's the problem.
| | 03:27 | Floating point numbers in all
computer systems, not just in SQL, but in all
| | 03:32 | computer systems, floating point numbers
sacrifice accuracy for what's called precision.
| | 03:38 | In the mathematical rule, precision mean
something different than what you might
| | 03:41 | think it means in your
average colloquial speech.
| | 03:44 | In the mathematical world, precision
refers to the number of digits of accuracy.
| | 03:49 | So it's accurate to 6 or so decimal places,
but beyond that, it is not accurate anymore.
| | 03:55 | Floating point numbers sacrifice
accuracy for what they call precision.
| | 04:01 | Decimal number systems in computers
are specifically designed to limit the
| | 04:07 | precision for accuracy.
| | 04:09 | In other words, they will always be
accurate to the number of decimal places
| | 04:12 | that you defined them to be, that
you'll use specific rules for doing rounding.
| | 04:17 | This works best and was designed
specifically for monetary applications, for
| | 04:22 | applications where money is involved.
| | 04:25 | If you're a designer and you know the
difference between JPEG and GIF files,
| | 04:28 | think of this like the
difference between JPEG and GIF.
| | 04:31 | JPEG files are great for photographic data,
but they sacrifice the precision of the colors.
| | 04:36 | They give you the access to millions of
colors, but they sacrifice the accuracy
| | 04:41 | of those colors for the
amount of colors that you get.
| | 04:44 | GIF files on the other hand, great
for flat artwork, where you've got a
| | 04:47 | limited number of colors, because
they will very accurately represent up to
| | 04:51 | 255 colors and no more.
| | 04:54 | That's the difference
between floating point and decimal.
| | 04:57 | Fixed point types are like the GIFs.
| | 05:00 | They have a limited amount of
precision and they are very, very accurate.
| | 05:04 | Floating point types are like the JPEGs.
| | 05:06 | They're able to represent a wide
range of numbers and they sacrifice a bit
| | 05:11 | of accuracy for that.
| | 05:13 | This distinction is very important and
the point is, of course, if you're doing
| | 05:16 | monetary applications, like banking or
accounting or anything like that, make
| | 05:20 | sure that you use the fixed
point types, in this case, decimal.
| | Collapse this transcript |
| String types| 00:00 | String Types are for storing
strings of data, often text.
| | 00:05 | Fixed string types are for storing
text that's always the same size like two
| | 00:10 | letter state code or something like that.
| | 00:13 | Variable Length string types are for
storing strings of values that might have
| | 00:18 | different sizes, like a name or an address.
| | 00:23 | Character string types are
for storing textual data.
| | 00:26 | And I pronounce CHAR, some people say CHAR.
| | 00:30 | CHAR is the fixed length type and
VARCHAR is the Variable length type.
| | 00:37 | BINARY types are for non-textual data.
| | 00:40 | BINARY is the fixed length type and
VARBINARY is the variable length type.
| | 00:46 | This is the Create Table statement for
the Album table in the album database.
| | 00:53 | You'll see we have an Integer type
for the id, and we have VARCHAR for
| | 00:58 | title, artist and label.
| | 01:00 | In parenthesis, you put the maximum
length of the variable character, field.
| | 01:06 | So, we have 255 for all of these and
then we have a DATE type that we'll
| | 01:10 | talk about these later.
| | 01:11 | So, this is an example of this in use
and this is usually how you'll see it used.
| | 01:16 | Everyone once in a while, you'll see
CHAR for fixed length ones, very, very
| | 01:19 | rarely, you see the BINARY
type or the VARBINARY type.
| | 01:23 | So, String Types are for
storing strings of data, usually text.
| | Collapse this transcript |
| Large storage types| 00:00 | Blob and Text are Large Objects
Storage Types, Binary and Text respectively.
| | 00:06 | Blob stands for Binary Large Object.
| | 00:08 | It's used for storing data that is not text.
| | 00:11 | There are four types of Blobs in MYSQL.
| | 00:14 | TinyBlob, for storage up to 256 bytes,
Blob for storage up to 64K bytes,
| | 00:22 | MediumBlob for storage up to 16M bytes
and LongBlob are for storage up to 4G bytes.
| | 00:29 | And these all are for non-textual data.
| | 00:32 | For textual data, we have the Text types,
and likewise, they come in four sizes.
| | 00:38 | TinyText for up to 256 bytes, Text
for up to 65535 bytes which is 64K,
| | 00:48 | MediumText for up to 16M bytes
and LongText for up to 4G bytes.
| | 00:54 | The Blob types are useful for
storing binary objects like images.
| | 00:58 | The Text types are useful for storing
text objects like articles or web pages.
| | 01:03 | The Text types can be used
with MYSQL's full-text searches.
| | Collapse this transcript |
| Date and time types| 00:00 | The DATE and TIME types are useful
for storing date and time related
| | 00:04 | information, it may include the DATE
type, which is good for dates only, and
| | 00:09 | dates in the range of
year 1000 to the year 9999.
| | 00:12 | And the TIME type which is good for times
and time related values, not just time of day.
| | 00:19 | Give or Take for times in
the range of +/- 840 hours.
| | 00:23 | And the DATETIME type for
date and time combinations.
| | 00:28 | The TIMESTAMP type is useful for
event logging and it's automatic.
| | 00:33 | So, every time you insert a row, it
automatically gets updated with the time and
| | 00:38 | date that the row was inserted.
| | 00:40 | Let's take a look at some examples.
| | 00:42 | We'll go ahead and create a
table in the test database.
| | 00:45 | This will create a table called datetest,
with a DATETIME type for the date, and
| | 01:03 | a TIMESTAMP type for a timestamp called stamp.
| | 01:06 | We'll go ahead and insert some data into it.
| | 01:25 | This will INSERT INTO the table
datetest, the column date with the value
| | 01:31 | 2009-05-04, so that's May 4th,
2009, a 3:31 in the afternoon.
| | 01:37 | Now, let's take a look at what we've done.
| | 01:54 | So, this goes ahead and it shows
our two columns here Date and Stamp.
| | 01:58 | And it calculates the difference
between these two values using the DATEDIFF
| | 02:03 | function that we'll cover later. There we go.
| | 02:08 | They are 16 days apart.
| | 02:10 | The DATEDIFF function gives
the result in number of days.
| | 02:13 | So, that's just a little example of how
you can use these values and how they work.
| | 02:18 | The DATE and TIME types are useful
for handling date and time related
| | 02:22 | information and storing that in your database.
| | Collapse this transcript |
| Bit type| 00:00 | The BIT type in MySQL is
used for storing bit fields.
| | 00:04 | So let's describe that way of example.
| | 00:06 | We'll create a table in our test database.
| | 00:12 | We call it bittest.
| | 00:16 | We'll give it two columns b1 and b2.
| | 00:18 | b1 is a bit field with 8 bits
and b2 is a bit field with 10 bits.
| | 00:32 | I'll go ahead and create this
table and insert some data into it.
| | 00:56 | What this does is it inserts into the
table bittest, values for two columns b1 andb2.
| | 01:01 | And the values are four On bits followed
by four Off bits, and Off On Off Off On.
| | 01:09 | The b here before the quoted value
indicates that it's a binary value.
| | 01:15 | And so it'll be just be ones and zeros.
| | 01:16 | I'll go ahead and insert
that row, and there we go.
| | 01:20 | Now, let's take a look at it.
| | 01:22 | Now we're not going to expect this
to be displayable data, because we
| | 01:29 | entered binary data.
| | 01:30 | So what we'll see will be odd and there we go.
| | 01:33 | We have this odd little character
there, and it couldn't figure out a way
| | 01:36 | to display b2 at all.
| | 01:38 | And so, we insert a binary data, how
can we new look at it as binary data.
| | 01:43 | Well, one way to do that is to tell
MySQL that this is a number and you do that
| | 01:49 | by adding zero to it.
| | 01:51 | Any kind of math, will
tell it that it's a number.
| | 01:55 | So, you can just add zero to tell it that
it's number without modifying the value.
| | 02:03 | And there you see we've got 240 and 9.
| | 02:05 | But we entered binary values.
| | 02:07 | Can we see them as binary values?
| | 02:09 | And in fact, we can.
| | 02:11 | We can use the BIN function to see them
as binary values, and there we have the
| | 02:21 | values that we entered.
| | 02:22 | You'll notice that the leading zero
is gone from b2, so there is ten bits
| | 02:26 | available there and we've
really only used four of them.
| | 02:29 | The rest of them are zeros, which is
significant, but it doesn't need to display them.
| | 02:34 | There are other number systems that are
used for binary values, and just briefly
| | 02:37 | I'm going to show them to you.
| | 02:38 | So there is BIN, I'm just
going to show them with b1.
| | 02:42 | There is Octal, which is base 8.
| | 02:44 | There is Hexadecimal, which is base 16.
| | 02:54 | And so there is those values in Binary,
Octal and Hexadecimal, that's all the same value.
| | 03:00 | So that's the bit field.
| | 03:01 | It's useful for storing flags.
| | 03:04 | If you want to store the state of a
number of things in a very small space, it's
| | 03:07 | really useful for that.
| | 03:09 | You won't actually see it that often.
| | 03:11 | It's something that might be used by
programmer types, but you're not going
| | 03:14 | to see it a lot in web work or in most of
the database applications that you run across.
| | 03:18 | And that's the bit field.
| | Collapse this transcript |
| Boolean values| 00:00 | MySQL does not actually have a Boolean type.
| | 00:04 | It uses integers for Boolean values.
| | 00:07 | So, if you declare a column in your
table, as type BOOL, it will actually
| | 00:11 | store it as TINYINT.
| | 00:13 | The word BOOL is an alias for the word TINYINT.
| | 00:17 | True is represented by an integer 1.
| | 00:20 | False is represented by an integer 0.
| | 00:23 | And in fact, the BIT type is an
excellent way to store Boolean values.
| | 00:29 | Let's take a brief look at what
integer values look like in MySQL.
| | 00:34 | We can do this by simply testing
for equality. If we ask is 5=5?
| | 00:42 | We should get 1 for True.
| | 00:44 | Yes, 5 is equal to 5.
| | 00:46 | And try something that we
know is not equal. Is 5=7?
| | 00:49 | No, 5 is not equal to 7, so we get a 0.
| | 00:54 | So we can see that 1 is True and 0 is False.
| | 00:58 | And that's really all you need to
know about Boolean values in MySQL.
| | 01:02 | You can store them in a bit field
or you can store them in an INT,
| | 01:05 | and 1 is True and 0 is False.
| | Collapse this transcript |
| Enumeration types| 00:00 | There are two different enumeration
types in MySQL, enum and set and they
| | 00:05 | work very differently.
| | 00:07 | So, let's look at a couple of examples so
that you can see how it is that they work.
| | 00:12 | I am going to create a table in
the test database to show you enum.
| | 00:31 | This will create a table called enumtest
with one column color of type ENUM with
| | 00:38 | three possible values, red, blue and green.
| | 00:42 | Go ahead and create the table,
and now let's insert a value.
| | 00:57 | This will insert 1 row into the
enumtest table, in the test database with the
| | 01:02 | value red in the color column.
| | 01:04 | There we go and let's take a look.
| | 01:14 | And there we have the one column with
the value red, let's insert a couple more
| | 01:33 | and then another one for green.
| | 01:34 | And so, what we have now is we have 3 rows,
one is red, one is blue and one is green.
| | 01:51 | The value of this is that, these different
values are actually stored in just 1 byte each.
| | 01:58 | The ENUM type is actually just an
integer and it stores a number called an index.
| | 02:03 | And when you define it, you have
defined three possible values red, blue and
| | 02:08 | green and so it will store a one if
it's red and a two if it's blue and a
| | 02:13 | three if it's green.
| | 02:14 | So it takes up very little space, if
you have a limited number of possible
| | 02:18 | values for a column and this is a
great way to be able to still see them
| | 02:24 | mnemonically, to be able to still see
red, blue and green and to not have to
| | 02:27 | translate it in your code.
| | 02:29 | That one is a red, because the database
engine will do that for you and it does
| | 02:32 | it very, very efficiently.
| | 02:34 | So, this is a very useful column type.
| | 02:37 | I want to show you one other
little quirky aspect of the ENUM type.
| | 02:50 | If I try to put a value in here that is
not defined in the declaration, it's not
| | 02:56 | really going to know what to do with it.
| | 02:58 | It can't store a 1 or a 2 or a 3.
| | 03:01 | It can't store a 4 because it doesn't
have it defined, and it won't know what
| | 03:04 | that is when it tries to read it.
| | 03:06 | So, what it does is it just silently
stores nothing, and it's not even a null value.
| | 03:13 | It's just empty.
| | 03:16 | So, when we go and look at
this, we only see three values.
| | 03:25 | There's actually a fourth row down here
and just because HTML, doesn't display
| | 03:30 | an empty table cell there, we are not
seeing it but we if we do count, we'll see
| | 03:34 | that there are 4 rows, there are 4 rows.
| | 03:42 | So it has stored an empty
value there for the unknown color.
| | 03:46 | So, this is just an important aspect
to understand about how ENUMS work.
| | 03:50 | It will silently fail effectively.
| | 03:52 | I would have preferred that it gave an
error and didn't allow us to insert a
| | 03:56 | value that's not in the
enumeration list, when we declared it.
| | 03:59 | But this is what it does, and it's
important to know that that's how it operates.
| | 04:03 | Now, let's look at the SET column type.
| | 04:06 | We'll create another test table.
| | 04:24 | This creates a table called settest.
| | 04:26 | We have one column attrib, which is
a SET type and it has three possible
| | 04:32 | values, bold, italic and underline,
and these are attributes that can be
| | 04:37 | applied in combinations.
| | 04:39 | And so what SET does is it allows you to
have multiple values in one column, in one row.
| | 04:47 | So, one value can be bold or it can be
italic or it can be bold and italic or it
| | 04:53 | can be italic and underline.
| | 04:55 | It can be any combination of these
three values, and the way that MySQL does
| | 05:00 | this is it stores them in a bit field.
| | 05:02 | This is really just another way to
use a bit field with mnemonic values and
| | 05:07 | it's very, very flexible and powerful
and it allows you to use your bit fields
| | 05:12 | and not have to do a lot of
computation to figure out which bits are set and
| | 05:15 | which bits are not set.
| | 05:16 | Instead you get these lovely
mnemonic values for the bits.
| | 05:20 | So, let's go ahead and create this
table and let's assign some values to it.
| | 05:26 | I'm going to go ahead and do several of these.
| | 05:39 | I'm just going to Cut and Paste, so
that we can -- let's say we'll do bold and
| | 05:49 | italic and we'll do another one with
say bold and underline, and another one
| | 06:04 | with say underline and italic and
let's do one more with underline and bold.
| | 06:15 | We did bold and underline before, let's do
underline and bold, so we can see how that works.
| | 06:20 | The truth is that MySQL will always
display them in the order that they were defined.
| | 06:25 | So, for underline, italic, it will say
italic, underline and underline, bold it
| | 06:30 | will say bold, underline,
when we read them back.
| | 06:33 | Let's go ahead and store this.
| | 06:34 | We should have 5 rows in our
table and take a look. There we go.
| | 06:45 | We have bold; bold, italic;
| | 06:47 | bold, underline;
| | 06:48 | italic, underline and bold, underline.
| | 06:50 | You see underline is always at the end
because that's the way that we defined it.
| | 06:54 | If we go and look at our declaration
DESCRIBE settest, we see that that's the
| | 07:02 | order that they are defined, set, bold,
italic and underline, in that order so
| | 07:06 | it will always read them back in that order.
| | 07:08 | So that's the SET type.
| | 07:10 | SET is very useful for flags.
| | 07:12 | It makes the bit field usable by
somebody who is not even terribly technical and
| | 07:17 | those are the two enumeration
types that are available in MySQL.
| | 07:20 | There is ENUM for one value per row and
they are stored as indexes in an integer
| | 07:27 | field and then there is settest for
multiple combinations of possible values and
| | 07:33 | they are stored in a bit field.
| | Collapse this transcript |
|
|
7. MySQL FunctionsMySQL functions| 00:00 | In SQL functions are used to provide
derived values, values that are derived
| | 00:05 | from other values, and from other data.
| | 00:07 | For example, this is a SELECT
statement from the world database, selecting a
| | 00:21 | COUNT of all the rows from the Country table.
| | 00:23 | This is a syntax that's familiar to most
people who have ever seen any SQL, this
| | 00:28 | is one of the most common functions
used in SQL and it's an aggregate function
| | 00:33 | that basically counts all the
rows from a select statement.
| | 00:37 | So, COUNT is the function and
the asterisk is the argument.
| | 00:42 | The general syntax of a function call
is the name of the function and an open
| | 00:48 | parentheses and then the argument to
the function or more than one argument if
| | 00:53 | there are, and they are separated by
commas, and the close parentheses and this
| | 00:58 | gives you the result of the
function call, rather than the raw data.
| | 01:02 | So, in this case COUNT with the asterisk,
means to count all the rows from the table.
| | 01:09 | So, this gives you the count of all
the rows from the country and we put
| | 01:12 | another table name here, you will see we get a
different number, 4079 rows in the city table.
| | 01:19 | So, in a nutshell, that's how a function works.
| | 01:22 | Let's go ahead and look at some
specific functions that are available in MySQL.
| | Collapse this transcript |
| String functions| 00:00 | MySQL has a number of useful String functions
and we're going to look at some of them here.
| | 00:04 | But first let's talk about what a String is?
| | 00:13 | This is a Literal String, SELECT 'Hello, World'.
| | 00:17 | So Hello World is a literal string.
| | 00:19 | You can see it's a number of
characters enclosed in single quote marks and
| | 00:22 | that's how you do a string in SQL.
| | 00:25 | Of course, there is also strings
that are stored in the database.
| | 00:27 | So, this will select the title column,
from the Album table in the album database.
| | 00:41 | There we go.
| | 00:41 | These are strings also and string
functions applied to these as well.
| | 00:46 | Let's go back to our literal
string, and there it is Hello World.
| | 00:56 | And let's say that these two words
were coming from different sources.
| | 01:01 | So, we'll use a function called CONCAT,
to concatenate two literal strings.
| | 01:09 | So, remember our function syntax,
we have the name of the function, the
| | 01:15 | parenthesis and inside the
parenthesis we have arguments.
| | 01:19 | In this case, there is two
arguments separated by a comma. We run this.
| | 01:22 | It will concatenate Hello with World
and run them together. There we go.
| | 01:28 | CONCAT ('Hello', 'World') comes out.
| | 01:30 | HelloWorld run together without the
comma and the space, because the comma and
| | 01:34 | the space in this case are simply
separating the arguments to the function and
| | 01:39 | they're not actually part of the string.
| | 01:41 | If we want the comma and the space,
we could add that as a separate string
| | 01:46 | inside here and now, we have three strings.
| | 01:51 | We have Hello, we have comma,
space and we have World.
| | 01:56 | It's a little hard to look at because
we're also using a comma and a space
| | 02:00 | to separate the arguments and when
we run it, we'll see we get the result
| | 02:04 | that we're looking for. Hello, World.
| | 02:07 | Like I said, that's a little hard to look at.
| | 02:09 | MySQL has another function called
Concatenate with Separator, which is spelled
| | 02:14 | CONCAT_WS and you give the separators
the first arguments, so I'm going to give
| | 02:19 | a colon as the separator and then I
use a comma to separate the arguments.
| | 02:26 | So, I'm going to take out this comma,
space string and now we have three arguments.
| | 02:31 | We've the first argument is the
Separator that we're going to use and the second
| | 02:35 | argument is the literal sting, Hello,
the third argument is the literal string,
| | 02:38 | World and so what this will do is it
will use this separator, to separate all
| | 02:43 | the arguments after it.
| | 02:44 | So, we have Hello:World and if we were
to have more arguments, those will all
| | 02:57 | get separated with colons as well.
| | 03:00 | I'll show you a practical example of
this little bit later in the lesson, but
| | 03:03 | right now, I want to talk about LPAD and RPAD.
| | 03:07 | In order to do that, we're going to
close our browser, we're going to open up
| | 03:10 | the command line client, because
spaces are hard to see in a web browser.
| | 03:15 | Web browsers tend to fold
numbers of spaces all into one space.
| | 03:18 | So, it's hard to illustrate something that
actually uses the spaces in a web browser.
| | 03:23 | So, go ahead and run the MySQL
command line client and we'll take a look at
| | 03:33 | the Title column from the Album Database
again, and Use Album, SELECT title FROM album.
| | 03:44 | Remember, you have to use a semi-colon
to terminate all of your SQL Commands in
| | 03:48 | the command line client.
| | 03:50 | And there we have the Title column from
the Album table in the album database.
| | 03:56 | Now, what if we wanted all
of these to be Right Flush.
| | 03:59 | This is something we can use LPAD for.
| | 04:12 | SELECT LPAD title, 30, FROM album;
| | 04:19 | and what this does is it
calls the LPAD function.
| | 04:23 | This first argument is what the string is.
| | 04:26 | So, in this case it's the Title
column from the album database.
| | 04:30 | The second argument is the number of
characters that you want to have in the
| | 04:34 | final string, and you will see what
that looks like in a moment and the third
| | 04:38 | argument is what you are padding it
with to make the string that long.
| | 04:42 | And then we have our SQL from Album
telling it what table to take the column
| | 04:47 | from and this is what we get.
| | 04:49 | We get a title padded out to 30 characters.
| | 04:53 | So all these strings are exactly 30
characters long and if the data wasn't long
| | 04:57 | enough, it pads the left-hand side.
| | 04:59 | That's what's LPAD with spaces.
| | 05:02 | If we use RPAD instead, then we get them
padded out on the right-hand side to 30 characters.
| | 05:18 | It's good to know also what happens
when the padding is less than the length
| | 05:23 | of the string and what happens is
that the string will be truncated to the
| | 05:26 | length that you specify.
| | 05:27 | So if I say SELECT LPAD title 15
FROM album, then we get strings that are
| | 05:44 | exactly 15 characters long and
they are truncated at the end.
| | 05:48 | If the string is short enough, in the
case of Apostrophe or Kind of Blue, or
| | 05:53 | Birds of Fire then, that's fine.
| | 05:55 | If they are too long, in case of Two Men with
the Blues then it's truncated to 15 characters.
| | 06:01 | If they are too short they're
padded on the left-hand side.
| | 06:03 | This is what you'd expect if your strings
are longer than the amount of the padding.
| | 06:09 | So, this is really useful for
reports that are going to be printed in a
| | 06:13 | fixed-width typeface, which reports
typically were until just the last few years
| | 06:18 | and in a lot of places they still are.
| | 06:20 | So, that's the LPAD and RPAD functions.
| | 06:23 | And we'll go ahead and Exit the command
line client and go back to the web browser.
| | 06:27 | And I want to show you a
practical example of how these are used.
| | 06:35 | This is an example from the CRUD
application and we'll actually see it in place
| | 06:40 | in the CRUD application later on in the course.
| | 07:01 | So, here is an SQL statement that's
going to show us a few columns from the
| | 07:04 | Track table in the Album database.
| | 07:10 | There we have it, id, album_id,
title, track_number, and duration.
| | 07:14 | You'll notice the
duration is in number of seconds.
| | 07:17 | That's the length of the
track in the number of seconds.
| | 07:18 | Now we see, we don't want to display it
that way and I decided to store it that
| | 07:22 | way in the database for certain reasons.
| | 07:24 | But I want to display it as minutes and seconds.
| | 07:28 | Now, there is a function built into
MySQL that will display it as hours, minutes
| | 07:32 | and seconds, which is not what I
want, and that looks like this.
| | 07:41 | So you have hours, minutes, and
seconds and few tracks are long enough for
| | 07:45 | that, and when they are I'm happy to display
90 minutes instead of 1 hour and 30 minutes.
| | 07:51 | And so what I really wanted was to
display this in minutes and seconds, and so I
| | 07:55 | came up with a way to do it using CONCAT_WS
and LPAD and that looks like this.
| | 08:03 | Go ahead and indent it on another
line, so that it's obvious and visible.
| | 08:08 | You'll remember from the SQL course
that whitespace is ignored in SQL and it's
| | 08:16 | all just folded into one space and so
this allows us to indent the code, so that
| | 08:20 | it looks readable on page.
| | 08:22 | So, here we have a rather long
expression and it uses two functions, CONCAT_WS
| | 08:45 | and LPAD and when you're looking at a
long expression like this, it's best to
| | 08:49 | start in the inside when you read it.
| | 08:51 | This makes it easier to read.
| | 08:52 | So if we look at this part of the
expression here, we see we have an LPAD
| | 08:56 | function, with duration MOD 60 and
we'll be explaining that in the section
| | 09:01 | on Numeric functions but in a nutshell that
does a division and gives you the remainder.
| | 09:06 | So, we have duration MOD 60 as the
string and 2 is the length of the string and
| | 09:13 | the literal 0 is what to pad it with.
| | 09:17 | And so what this will do is it will
take this duration MOD 60 and if it's less
| | 09:21 | than two characters long, it will pad
it on the left-hand side with zeros and
| | 09:25 | that gives you things like this here,
this duration is 3 minutes and 5 seconds
| | 09:30 | and you'll see that it display as
just 3 or 5 and we're all done with this.
| | 09:35 | And then we have CONCAT_WS,
which has three arguments.
| | 09:39 | It has the separator.
| | 09:42 | It has the duration DIV 60 which is
the number of minutes and this LPAD
| | 09:48 | expression for the number of seconds.
| | 09:50 | So it takes those two values and it
concatenates them together with a colon,
| | 09:54 | which is exactly what we want.
| | 09:56 | So, that's how this line works.
| | 09:58 | When we press Go here, we'll see.
| | 10:00 | We now get the duration in minutes and seconds.
| | 10:02 | So we have 5 minutes here, down here,
we have one that's 13 minutes long.
| | 10:06 | We'll notice that it shows the whole 13.
| | 10:08 | It also shows this 06, with a leading 0.
| | 10:12 | We have that in a few places there.
| | 10:14 | So it's padding the left-hand side of
that value with a 0, so we have a leading
| | 10:19 | 0 on the seconds, we don't have a
leading 0 on the minutes which is exactly how
| | 10:23 | I think it should be displayed and so
that's why I have created it this way.
| | 10:27 | So, there is a practical
example of these String functions.
| | 10:31 | Of course MySQL has many String
functions available, most of them are not as
| | 10:36 | commonly used as the concatenation and
the padding and they're all documented on
| | 10:40 | the MySQL website, if you're interested in them.
| | Collapse this transcript |
| Numeric functions| 00:00 | MySQL has a number of useful
numeric functions and we are going to talk
| | 00:03 | about those in a moment.
| | 00:05 | But first, I want to talk
about numbers a little bit.
| | 00:11 | SELECT 320, so 320 here is a literal number
and when I press Go, you see the result is 320.
| | 00:17 | I can also get numbers from a database.
| | 00:27 | So this will select the duration column
from the track table in the album database.
| | 00:32 | So there we have a bunch of
numbers and those come from a database.
| | 00:37 | So for our demonstrations here we'll be
using mostly literal numbers and we'll
| | 00:42 | also use some numbers from database.
| | 00:44 | So let's start with this 320 number.
| | 00:49 | And let's say I want to know how
many minutes there are in 320 seconds.
| | 00:53 | I can just divide by 60.
| | 00:57 | I see there is 5 minutes in change.
| | 00:58 | Now, what if I'm not interested in the
change, I just want to know the whole minutes.
| | 01:04 | So I can use the DIV
instead of the Slash operator.
| | 01:09 | This is actually a function call,
320 DIV 60 and that will give me 5.
| | 01:15 | So there is 5 minutes and we are
not worried about the change here.
| | 01:19 | If we are worried about the change, we
can just say MOD instead of DIV and that
| | 01:22 | will give us the modulus which is the
remainder of a division and that will tell
| | 01:27 | us that there is 20 seconds
after the 5 minutes are taken away.
| | 01:32 | And this is really useful.
| | 01:33 | An example from the CRUD
application, do something like this.
| | 01:59 | And this will give us all of those
durations from the track table in the album
| | 02:03 | database formatted as minutes and seconds.
| | 02:07 | So if we look at this and when we nest
functions like this, we want to look from
| | 02:10 | the inside out, we use
LPAD to format this function.
| | 02:15 | So this is the function here, and
duration, MOD 60 that gives us the remainder.
| | 02:19 | It gives us the number of seconds
after the minutes are taken away.
| | 02:21 | I want to format that to be 2 characters
long and padded with 0s on the left and
| | 02:26 | so the LPAD function does that
and that's from here to there.
| | 02:31 | And that is one argument to this
CONCAT_WS and we are using the colon as a
| | 02:38 | separator, CONCAT_WS is covered in our
string functions section and we are using
| | 02:43 | the duration DIV 60 which is the
number of minutes and the padded number of
| | 02:47 | seconds, padded on the left with 0s, FROM track.
| | 02:51 | So this will give us the minutes and
the seconds and the seconds will be padded
| | 02:55 | with 0s on the left, separated by a colon.
| | 02:59 | And there we have it.
| | 03:01 | So 320 seconds is 5 minutes and 20
seconds, 5:44, 4:56 and you will see that the
| | 03:09 | numbers that have a number of seconds
less than 10 are padded on the left with a
| | 03:13 | 0, this one and there is another one.
| | 03:16 | And we'll see that the numbers for the
minutes are not padded and yet when there
| | 03:20 | is more than 10 minutes, it shows that way.
| | 03:27 | So this is a useful example and
this is actually how it's done in the
| | 03:31 | CRUD application we'll see that later on when
we look at the code of the CRUD application.
| | 03:36 | There are other useful numeric
functions that you will see commonly used in
| | 03:38 | MySQL and let's pick another literal
number to use for this example. Select 125.
| | 03:46 | That number is in base 10 which is
commonly how we use numbers in our day-to-day
| | 03:50 | life but let's say that we are a
computer geek and we want to see it in base 16,
| | 03:54 | you can say CONVERT 125, 10, 16.
| | 04:03 | Base 16 is also called hexadecimal and
so we are converting from base 10, which
| | 04:08 | is also called decimal into base 16,
and the number in base 10 is 125, so it
| | 04:14 | will give us the result in hexadecimal.
| | 04:17 | You can do the same thing,
converting to base 2 and you can convert the
| | 04:23 | other way around as well.
| | 04:33 | There are some shortcuts for
some of the more common conversions.
| | 04:36 | If you want to see, 125 in HEX, you can
just say HEX (125) or if you want to see
| | 04:46 | it in binary, you can say BIN, that's
base 2 and if you want to see it in octal,
| | 04:53 | there is a shortcut for that
which is OCT, which is base 8.
| | 04:59 | So those are the conversion functions.
| | 05:01 | Another common numeric function that you
will see is the CRC32 function and this
| | 05:13 | does what's called a Cyclic Redundancy Check.
| | 05:15 | It's a form of a check sum.
| | 05:17 | It will come up with a unique number
that represents this string and it's CRC32,
| | 05:22 | which is a common form of CRC,
a common form of hashing.
| | 05:26 | There is different words that are
used to describe it and you will see it a
| | 05:30 | lot and then there is the number and
it comes up in decimal by default and if
| | 05:33 | you want it in text, which is also common, you
will see this very often, this construction here.
| | 05:38 | All right and you will also
commonly see it, used from that table.
| | 05:46 | Say SELECT title and the
CRC from the track table.
| | 05:52 | And even though it's just a 32 bit number,
you will see these numbers are almost
| | 06:01 | entirely unique for most lists of less
than a few hundred thousand of things.
| | 06:06 | So it's often used for hashing and
for indexing into data structures.
| | 06:10 | So you will see that now and then.
| | 06:12 | There is also a complete set of
trigonometric functions and we are just going
| | 06:16 | to look at a couple of them because I
wouldn't want to get into too much heavy math here.
| | 06:21 | Like for instance, DEGREES from
radians, PI is half a circle and radians.
| | 06:28 | So we have that PI function inside as
the argument to DEGREES function and this
| | 06:32 | will give us PI and DEGREES which will
come out to 180 degrees or if you want
| | 06:37 | radians from degrees,
you can say RADIANS of 180.
| | 06:41 | And that will give you a 180 RADIANS, which
is more digits of PI than I have memorized.
| | 06:50 | There is of course many
more trigonometry functions.
| | 06:52 | You have got sines and cosines
and tans and things like that.
| | 06:57 | There is the common formatting
function, you will see this one a lot.
| | 07:07 | And what this will do is it will
format this large number with two decimal
| | 07:11 | places and it will put commas in
between the digits and the number.
| | 07:16 | So there we have easy to read number
and you will see this function, we used a
| | 07:20 | lot, and it's internationalized.
| | 07:22 | So if you are using a different locale
in your installation of MySQL, it will
| | 07:26 | use periods instead of the commas and commas
instead of the periods if you are in Europe.
| | 07:30 | Or however it's set up on your computer in your
| | 07:33 | internationalization, localization.
| | 07:36 | Another common function that you
will see is power, spelled POW.
| | 07:43 | So this will give us 16 squared or if we
want a larger power, 16 raised to the 8.
| | 07:53 | It's a big number and the
Random function is also very common.
| | 07:58 | It gives you a random number and
every time you call it, you get a
| | 08:02 | different random number.
| | 08:04 | Numbers are all less than one.
| | 08:07 | And this is actually very, very useful.
| | 08:10 | For example, you can select let's say
I want a random list of countries from
| | 08:29 | the world database.
| | 08:30 | This is a very common way to get that, so
this will give us random records from a database.
| | 08:36 | Every time I click on it, I get
10 different random countries.
| | 08:41 | And if we want, you can seed the
random number generator, this is interesting
| | 08:47 | and it will give you the same random
list every time because it's starting from
| | 08:50 | the same place in its randomization.
| | 08:52 | I guess a different number.
| | 08:55 | It will give you a different...
| | 08:56 | But each time I click at it, it's the same.
| | 09:00 | So those are the most common numeric
functions that you will see in MySQL, of
| | 09:02 | course there are a lot more of them.
| | 09:05 | If you go to the MySQL website, you can
find a complete list but these are the
| | 09:09 | ones that you will see the most common
and that you will use the most commonly.
| | Collapse this transcript |
| Date and time functions| 00:00 | MySQL has a number of useful
time and date related functions.
| | 00:04 | Most common of which is the NOW function.
| | 00:09 | The NOW function shows you what the
time and date is right now. There it is.
| | 00:16 | That's the time and date that I'm
recording this, in the booth of Lynda.com.
| | 00:20 | You can also just get the date part
of that, using the CURDATE function for
| | 00:25 | current date, there is just the date
part of when it is right now and you can
| | 00:31 | get the time part with
CURTIME, for current time.
| | 00:38 | You can get the current time and
date in the UTC Time Zone, which is the
| | 00:43 | Universal Time Zone, also
called Greenwich Mean Time.
| | 00:49 | Using UTC TIMESTAMP, UTC_TIMESTAMP.
| | 00:54 | There is the current date
and time in the UTC time zone.
| | 01:00 | So I can say NOW, UTC_TIMESTAMP
and see both of them together.
| | 01:06 | And if I want to know the
difference between them, I can say NOW -
| | 01:13 | UTC_TIMESTAMP showing that I can do
math, I can do simple arithmetic anyway
| | 01:20 | directly on time values.
| | 01:23 | Now, it gives me the
difference in this internal time format.
| | 01:26 | If I want it formatted for display, I
can say TIME and use the TIME function
| | 01:33 | wrapped all the way around that
expression and it will give me just the time,
| | 01:39 | seven hours difference between here and UTC.
| | 01:43 | If you want to know the number of days
between two dates, you can do something
| | 01:48 | like this, DATEDIFF, NOW,
there's what we were looking for.
| | 02:02 | And we can see that that's a seven
days difference, like we expected.
| | 02:06 | And finally a common function that
you will see used a lot is DATE_FORMAT.
| | 02:24 | What DATE_FORMAT does is it formats a
date for being readable and it uses these
| | 02:29 | various tokens for specifying how the
date is going to be formatted and these
| | 02:34 | are all explained to the MySQL documentation.
| | 02:37 | But in short, these are common ones.
| | 02:39 | Capital W is the for the day of the
week, so we see Thursday and that comes
| | 02:44 | from the capital W. Capital D is for the day
of the month with the English ordinal there.
| | 02:52 | If I do that in a lower case d, you
will see I'll get something different.
| | 02:57 | It's just the number like that.
| | 03:00 | The capital M is for the month spelled
out, capital Y is for the four digit year
| | 03:06 | and capital T is for the time
formatted thusly in a common way.
| | 03:11 | So that's DATE_FORMAT and
you will see that a lot.
| | 03:15 | So those are the date and
time related functions in MySQL.
| | 03:17 | Of course, like all of the other
categories, there is a lot more functions
| | 03:21 | available and if you look on the MySQL
website, you can find a complete list.
| | Collapse this transcript |
| Time zones in MySQL| 00:00 | So let's take a look at
how MySQL handles timestamps.
| | 00:03 | I'm going to bring up the command line
client for this and we're going to create
| | 00:11 | a table in the Test database.
| | 00:13 | So we'll use Test, remember the semi-
colon because this the command line client.
| | 00:40 | So this creates a table with
three columns ID, Name, and Stamp.
| | 00:45 | ID is an auto-increment column, Name is a
string, and Stamp is a timestamp column.
| | 00:52 | So timestamps would be
inserted automatically in that column.
| | 00:55 | Let's go ahead and insert a row.
| | 01:10 | So we've inserted a row into our table
and we set a value for the Name column,
| | 01:15 | Fred Flintstone, and we did not set values for
Stamp and ID because those areautomatically inserted.
| | 01:20 | Let's take a look.
| | 01:26 | So there's our row.
| | 01:27 | We have the ID of 1, the name Fred
Flintstone, and the timestamp of the
| | 01:32 | time when I inserted it.
| | 01:34 | Now, you notice that it displays this time
in my local time zone, which is Pacific time.
| | 01:39 | So that's the same time as the time in my watch.
| | 01:42 | It's stored internally in the universal
time zone, which is Greenwich Mean Time.
| | 01:48 | It's the universal time zone.
| | 01:50 | So in order to display the time in my
local time zone, it needs to know what
| | 01:55 | that time zone is and that's done
with a system variable called Time Zone.
| | 02:03 | These two @ signs are to tell me that
I'm looking at a system variable and
| | 02:09 | time zone is right there.
| | 02:11 | That's set to System, which means that
it's using the time zone of my system.
| | 02:16 | Now, this is normal and this is
the way that MySQL is normally setup.
| | 02:20 | So it's not actually
storing the time zone internally.
| | 02:24 | It's using the time zone from the host system.
| | 02:27 | And then it does its calculations based on that.
| | 02:30 | MySQL is capable of using
multiple time zones in a session.
| | 02:34 | And if you have an application that
needs that, then this can be very useful.
| | 02:39 | You can actually change the time zone for
a given session and then change it back.
| | 02:44 | This is useful especially for web
applications where your users can set their
| | 02:49 | time zones and save them.
| | 02:50 | Unfortunately, this feature only works
if the time zone tables are installed.
| | 02:54 | Now, I'm going to go ahead and set the
time zone to Eastern time, and as you
| | 02:59 | follow along, this may not work for you.
| | 03:01 | Now, if you get an error when you
type that, that means that your time zone
| | 03:13 | tables are not installed.
| | 03:15 | There is another lesson in this
chapter to tell you how to install them.
| | 03:19 | So if this is important to you, then
you can pause here and go and install the
| | 03:23 | time zone tables, and
come back, and follow along.
| | 03:26 | Now, I've set my time zone to
Eastern time, which is three hours later
| | 03:30 | than Pacific time.
| | 03:31 | So when I query my table, we can see
that the time is now three hours later
| | 03:40 | than it was before.
| | 03:42 | We can set it back by setting it to US
Pacific or we can simply set it to System.
| | 03:49 | Now, I recommend if you're using
this feature in production then at the
| | 03:58 | beginning of a session you set the
time zone, and then at the end of the
| | 04:01 | session, you always set it back to system.
| | 04:03 | So that when a different client
connects to that connection, the time zone is
| | 04:07 | set in a known place.
| | 04:10 | Now, we query again.
| | 04:11 | You'll see that the time is back
to what it was, in Pacific time.
| | 04:18 | Now, you'll notice a
couple of little quirks here.
| | 04:21 | One is that when the time is
set to a different time zone.
| | 04:24 | It's not always exactly the same time.
| | 04:27 | I've noticed this too, and I'm
not sure exactly why this is.
| | 04:30 | I've tried to find out,
and I've not found an answer.
| | 04:32 | If I do find an answer, I'll
post it on my website at bw.org.
| | 04:36 | But it's just something worth noting,
if you are setting your time zones, it's
| | 04:39 | going to be consistent, and
that's what really matters.
| | 04:43 | So this is how MySQL handles time
zones, and it stores its timestamps in
| | 04:49 | universal time, which makes it convenient
for it to do the calculations when it reads it.
| | 04:53 | So that way, if different people are
reading the same value in different time
| | 04:57 | zones, it will always display it in their
local time zone and the time will be correct.
| | 05:01 | So that's how MySQL handles time zones,
and if you need to install your time
| | 05:05 | zone tables, be sure to take a look at
the lessons for installing your time zone
| | 05:09 | tables on Mac and on Linux.
| | Collapse this transcript |
| Installing time zone support in MySQL in Windows| 00:00 | For those of you who need to install
the time zone support files in MySQL, this
| | 00:06 | is a lesson on how to install those
files in the Windows operating system.
| | 00:11 | First, let's take a look at MySQL and
make sure that we need to install the files.
| | 00:15 | So I'm going to start up
the MySQL command line client.
| | 00:23 | Let's start by taking a look at the
system variable that tells us the time
| | 00:28 | zone of this process.
| | 00:34 | So this is a simple select statement
that's looking at a system variable and you
| | 00:37 | can tell it's got the
two at signs and time_zone;
| | 00:43 | and that value is SYSTEM.
| | 00:45 | This is the normal value.
| | 00:47 | You'll see this whether or not
you need to install these files.
| | 00:50 | Now I'm going to try to change the value.
| | 00:52 | So I say SET time_zone = 'US/Eastern',
to make it a string literal and
| | 01:11 | semicolon, and I get this error.
| | 01:13 | This error says unknown or incorrect time zone.
| | 01:15 | This is because MySQL does not
recognize the name of the time zone that I've
| | 01:20 | entered, and it doesn't recognize that
name because it's missing its time zone files.
| | 01:25 | So we're going to go ahead and install them now.
| | 01:27 | If you don't get an error here, then
you can check the time zone variable and
| | 01:32 | see if it's been changed.
| | 01:37 | Now, in this case, it has not been
changed, because we need the files.
| | 01:41 | After we install the files, we'd go ahead and
do this again, and see that it's been changed.
| | 01:45 | I'm going to exit the command line
client, and exit the command line window.
| | 01:50 | Now, I'm going to come down here and run
the XAMPP Control Panel and stop MySQL.
| | 01:57 | You must stop MySQL before
installing these files, so I press Stop there.
| | 02:01 | I'm waiting for it to stop. There we go.
| | 02:05 | See down here it says Stopped.
| | 02:07 | I'll minimize that.
| | 02:10 | I'll bring up my web browser.
| | 02:12 | Now I've already navigated to this page.
| | 02:14 | This is the URL,
dev.mysql.com/downloads/timezones.html,
| | 02:20 | and this is where you find
the files that you need to download.
| | 02:24 | Now, there is two links here, one for an
older version and one for a newer version.
| | 02:28 | We're going to download the newer version.
| | 02:30 | I'm going to save this file.
| | 02:41 | I have my Firefox set to
save files on the desktop.
| | 02:44 | I'll go ahead and close this and close Firefox.
| | 02:51 | There is the time zone ZIP file.
| | 02:53 | I'll go ahead, and Extract All, to my desktop.
| | 02:57 | I'll just say Extract, and there is
this directory, and here is the files.
| | 03:02 | So these are the time zone
files that I'm going to install.
| | 03:06 | These are actually database files.
| | 03:08 | This is what database files look
like in MySQL, in the default format.
| | 03:13 | So now we're going to come over
here, I have my local C disc up.
| | 03:16 | We're going to navigate to XAMPP
folder, and the MySQL folder, and then
| | 03:22 | Data folder, and then inside this
these are the different databases that we
| | 03:25 | have installed here.
| | 03:27 | I'm going to go to the MySQL database,
which is where the system files are kept,
| | 03:30 | and you see here there are no time zone files.
| | 03:34 | Now, you may see time zone files and
still have this where it's not working,
| | 03:38 | and that's okay, you can replace those
files, because those are default files
| | 03:43 | that just have a minimal amount of information
in them, but they don't have the full database.
| | 03:47 | See here where the name database is 106 K,
the transition database is 600 K for each file.
| | 03:53 | So these are the files that we want.
| | 03:54 | I'm going to go ahead and select
all of these, and copy these over.
| | 03:58 | I'm going to move them over, and that's
okay, because we have them archived in
| | 04:03 | the ZIP file already. We'll close that.
| | 04:06 | We'll start the database up again,
Start MySQL, there it says Running.
| | 04:12 | We'll go ahead and open the command line client.
| | 04:16 | We'll take a look at that variable.
| | 04:20 | Now, the value hasn't changed.
| | 04:26 | It still says SYSTEM, but I'm going
to go ahead and set it to something
| | 04:28 | else, and it worked.
| | 04:38 | Now, when I take a look at the variable,
you'll see that it's been changed, and
| | 04:45 | we're now in the Eastern time zone.
| | 04:48 | If I use the test database, I've
still got my time zone test table.
| | 04:57 | I see that value is the Eastern value,
and just to make sure, I'll go ahead and
| | 05:01 | set my variable back to the default.
| | 05:07 | SYSTEM is the default like that,
and select from the database again.
| | 05:17 | This is working as expected.
| | 05:18 | I'm now in my default time zone, which
is the US Pacific, and when I had it set
| | 05:23 | to US Eastern it gave me that value.
| | 05:25 | We've successfully installed and
tested the time zone database in MySQL on
| | 05:29 | the Windows operating system.
| | Collapse this transcript |
| Installing time zone support in MySQL on a Mac| 00:00 | Okay, so let's install the Time Zone
Database in MySQL on the Mackintosh side.
| | 00:05 | The first thing we want to do is to
confirm that we actually need to do this.
| | 00:09 | So let's open our terminal and start
up the MySQL command-line interface.
| | 00:13 | So, we'll start by taking a look at
the time zone variable and this says
| | 00:31 | SELECT @@time_zone.
| | 00:33 | The double @ sign means it's a system
variable, and don't forget to put in the
| | 00:36 | semicolon because this is
the command-line interface.
| | 00:39 | We see that it's set to
system, which is as it should be.
| | 00:43 | So, let's try to change it.
| | 00:44 | So, I'm setting time zone to US/Eastern
which is a value that I know should be
| | 00:57 | there for time zone databases intact,
and we get unknown or incorrect time zone.
| | 01:02 | So, we know that we have a problem.
| | 01:04 | We know that the timezone
database is not as it should be.
| | 01:08 | Let's just take a look and see if
the database exists and is empty, or it
| | 01:13 | doesn't exist at all.
| | 01:14 | And really, either way, we are going to
need to replace it but let's just take a look.
| | 01:26 | So the database is named time_zone_name and
we are just going to take a count from it.
| | 01:30 | It should be several hundred entries,
although sometimes it comes installed with
| | 01:35 | no entries, empty, not existing
at all, or just a few entries.
| | 01:39 | And in any of these cases,
we'll need to update it.
| | 01:41 | I need to select the MySQL database, use
MySQL and then we'll do that command again.
| | 01:48 | I'm just pressing the Up arrow a couple
of times to get that, and there we have it.
| | 01:52 | We have a database installed but it's empty.
| | 01:54 | And this is common and it just means
that we need to go ahead and install the
| | 01:58 | time zone databases.
| | 01:59 | So let's exit from here and exit our terminal.
| | 02:02 | Bring up the web browser and
go ahead and download the file.
| | 02:06 | So we are going to http://dev.
mysql.com/downloads/timezones.html.
| | 02:17 | All right, so here is the MySQL page
where you can download the time zone
| | 02:24 | description tables and you will
see there is two links down here.
| | 02:27 | One of them says preferred.
| | 02:29 | So, I'm going to click on that one.
| | 02:30 | I will go ahead and save the file.
| | 02:33 | I have my Firefox set to
save files on my Desktop.
| | 02:37 | Go ahead and quit Firefox and there is the file.
| | 02:40 | It's a zip file, which means that on a
Mac I can just double-click on it and it
| | 02:44 | will expand the zip file into this
nice handy folder, and I'll go ahead and
| | 02:48 | double-click on the folder and
there is the time zone files.
| | 02:51 | These are what database
files look like in MySQL.
| | 02:55 | So, now we want to go
and install these in MySQL.
| | 02:57 | Now, this is going to be a two-step process.
| | 03:00 | The first step is to find the
place where they go, and this is in
| | 03:03 | Applications/XAMPP/xamppfiles/var/
mysql and this is where the MySQL databases
| | 03:16 | are, and you will see we
have world, test, mysql.
| | 03:20 | The mysql database is where MySQL's
system tables are and you will see, go down
| | 03:26 | here, we'll see there is the
time zone files, all right.
| | 03:28 | So, I'm just going to take these
files here and select them all.
| | 03:33 | I pressed Command+A to select them all.
| | 03:36 | I'm going to drag those over.
| | 03:40 | This copy dialog comes up and it
says that the files already exist.
| | 03:43 | So, I'm going to Apply to all and Replace. Oops!
| | 03:48 | So, before I copy these
files over, I have to stop MySQL.
| | 03:55 | Bring up the XAMPP Controls and press Stop.
| | 03:57 | MySQL is not running and now I
can simply drag these files over.
| | 04:05 | And I get this Copy dialog that
says that the files already exist.
| | 04:11 | So Apply to all and press Replace.
| | 04:14 | And there we have it.
| | 04:16 | Files have been copied.
| | 04:18 | Unfortunately, the way they come from
the factory, these files have the wrong
| | 04:22 | permissions and they will not work.
| | 04:23 | So, we are going to need to
do a little bit of surgery.
| | 04:26 | It looks a little bit complicated.
| | 04:28 | It's not that hard if you just follow
along, and you only need to do it once.
| | 04:32 | So, there is the good news.
| | 04:34 | First, we are going to use the cd command.
| | 04:36 | I brought up the terminal and we are
using the command-line now in the UNIX side
| | 04:40 | of OS X. So, I'm using the cd command,
which changes directory, and I'm going to
| | 04:45 | cd to this directory over here
where all these time zone files are.
| | 04:50 | So that's in Applications.
| | 04:52 | I just need to type a few letters and
I can press the Tab key, and it will
| | 04:56 | complete that for me.
| | 04:57 | And the XAMPP, press the X and
press Tab, xamppfiles/var/mysql/mysql.
| | 05:07 | So, that's the cd command and I press
Enter, and now I'm in this directory where
| | 05:11 | those time zone files are.
| | 05:12 | I can say ls time*, like that,
and I can see those files.
| | 05:17 | So I need to use the chmod command.
| | 05:21 | It's a unixism for changing
the permissions on the files.
| | 05:26 | I want to see what the permissions are.
| | 05:27 | I can use ls -l and I see that
these permissions over here are not
| | 05:35 | sufficient for what we want.
| | 05:36 | So, we are going to say chmod 775 time*.
| | 05:43 | So, that will change all of
these files to the permissions 775.
| | 05:48 | Essentially, it means that more of
these bits will be turned on and we would
| | 05:51 | be able to use them.
| | 05:53 | Press Enter, and we notice that the
icon shifted over here when I did that.
| | 05:57 | And now if I just use my Up arrow a
couple of time and get this ls command,
| | 06:02 | press Enter, we'll see that now a lot
more of these bits are turned on and these
| | 06:06 | files are now actually usable by MySQL.
| | 06:10 | So, we can just type exit and we can close this.
| | 06:14 | And then we can close this and then we
can turn On MySQL again, almost there.
| | 06:20 | That's running and let's go and test it.
| | 06:25 | So, we'll start up the MySQL command-
line client, and I'm going to use mysql and
| | 06:38 | SELECT COUNT(*) FROM time_zone_name;
| | 06:44 | and there we have it,
546 rows. This should work.
| | 06:49 | Let's go ahead and test it. use test;
| | 06:52 | SELECT * FROM tztest;
| | 06:57 | There we have it with our local time zone,
and I'm going to change my time zone.
| | 07:03 | SET time_zone = US/Eastern;. Oh!
| | 07:11 | Look at that. It worked.
| | 07:12 | Now, I'll just Up arrow a couple of
times and do my SELECT and look at that.
| | 07:17 | The time zone is different.
| | 07:18 | Now, this time stamp is stored
in the database in universal time.
| | 07:23 | So, when I change my local time,
it's able to translate that into a
| | 07:27 | different time zone.
| | 07:28 | When I change my local time
zone back, SET time_zone = SYSTEM;.
| | 07:37 | Now, I select from the tztest and we'll
see that the time zone has changed back.
| | 07:41 | Our time zone support is now working.
| | 07:43 | We have successfully undated the time
zone database for MySQL on the Mac.
| | Collapse this transcript |
| Aggregate functions| 00:00 | MySQL has a few unique aggregate
functions or aggregate function usages.
| | 00:05 | Most of the aggregate functions that
are available in MySQL are also available
| | 00:09 | on other platforms and are
actually part of the standard.
| | 00:12 | So we are going to focus in this
lesson on those differences of MySQL.
| | 00:15 | If you are interested in knowing
more about the rest of the aggregate
| | 00:19 | functions, take a look on my MySQL
Essential Training course here on Lynda.com
| | 00:23 | for a more complete reference.
| | 00:30 | The most common aggregate function
is the Count function, I'm sure you've
| | 00:33 | seen it many times.
| | 00:35 | I'm going to go ahead here and select
the world database, and what we'll have
| | 00:38 | here is a count of all the rows in
the country table in the world database.
| | 00:43 | Of course, you've seen this before many times.
| | 00:47 | The Count function in MySQL also
accepts the DISTINCT keyword, and what this
| | 00:54 | will do is will count the number of
distinct values in the Region column in the
| | 00:59 | country table in the world database.
| | 01:03 | So we see that there are 25 distinct
values in the Region column, and of course
| | 01:07 | that means that there are 25
distinct regions in the country table.
| | 01:11 | Now you may want to know how many
countries are there in each region.
| | 01:15 | You can do this like this.
| | 01:18 | SELECT Region, COUNT (Region), FROM
Country, GROUP BY Region, and what this will
| | 01:30 | give you is a count of all of the
different values in the Region column for each
| | 01:37 | grouping of region by different region values.
| | 01:41 | So for each value of region, it will
count the number of rows in that group, and
| | 01:48 | that will give us the number of
different countries in each region.
| | 01:52 | And so again the way this does is by
grouping by region, what it does is it
| | 01:56 | groups all of the different rows that
have a single value of region and counts
| | 02:00 | those, and so there are five rows that
have the value of Antarctica in region,
| | 02:06 | and there are 24 rows that have
the value of Caribbean in region.
| | 02:11 | And so by grouping them together this
way, we can get a count within each group
| | 02:16 | of how many rows there are with that value.
| | 02:20 | And so that tells us how many
countries there are in each region.
| | 02:23 | Now that we have this data, maybe we'd
like to know the names of all of those
| | 02:27 | countries in each region.
| | 02:29 | And for this we have the GROUP_CONCAT
function, and that looks like this, SELECT
| | 02:40 | Region, GROUP_CONCAT.
| | 02:42 | That's the name of the function with
the argument of (Name) FROM Country and
| | 02:46 | GROUP BY Region, and what this will
do for each region, it will list in one
| | 02:51 | value the names of all the
countries separated by commas.
| | 02:55 | Now this is a very powerful
grouping aggregate function.
| | 02:59 | You see, Antarctica we have these countries,
the Caribbean we have all of these countries.
| | 03:05 | Now there is a couple of
things about this that I don't like.
| | 03:08 | I don't like the uses of single comma
and no spaces to separate them, because
| | 03:12 | that makes things like this look strange.
| | 03:14 | Virgin Islands, U.S., Turks.
| | 03:17 | You don't even realize that Virgin
Islands, U.S. is the name of a country.
| | 03:22 | We can see because it's got this space
after the comma that it's not that kind
| | 03:26 | of a comma, and so we'd like to change
that, and also the names of the countries
| | 03:31 | are not in any order.
| | 03:33 | So we'd like to order those as well.
| | 03:34 | The GROUP_CONCAT function is
very flexible in these ways.
| | 03:38 | We can put in here ORDER BY and
we can also specify a Separator.
| | 03:47 | And what I'm going to do for a
Separator is I'm going to use (space) / (slash)
| | 03:50 | (space), so that it doesn't get
confused with the commas that may already exist
| | 03:55 | in some of the names.
| | 03:56 | This will order the list
within the group by name.
| | 04:02 | So it will be in alphabetical order,
and it will change the Separator from this
| | 04:05 | bare comma to something that's a
little bit more readable. Here we go.
| | 04:10 | So now if we look at the Caribbean,
it starts with Anguilla, and Antigua
| | 04:15 | and Barbuda, and Aruba.
| | 04:17 | So it starts with the As and it
ends with Virgin Islands, U.S. Virgin
| | 04:20 | Islands, British as well.
| | 04:24 | So this is a list that we can actually use.
| | 04:27 | Those are the aggregate functions
that are different or unique in MySQL.
| | 04:32 | Most of the aggregate functions that
MySQL supports are available in other
| | 04:36 | platforms and are documented in SQL
Essential Training course here on Lynda.com.
| | 04:41 | What we are focusing on here are the
ones that are different or unique to MySQL.
| | Collapse this transcript |
| Full-text search| 00:00 | MySQL has a very simple, and very
powerful full text search feature.
| | 00:05 | So let's take a look at how that works.
| | 00:07 | I'm going to create a
table in the test database.
| | 00:38 | So this will create a table with
three columns in it, id, title and body.
| | 00:43 | The id column is auto increment, the
title column is a string, and the body
| | 00:49 | column is a text column.
| | 00:51 | The word FULLTEXT specifies a type of index,
which is used with the FULLTEXT searches.
| | 00:57 | So this creates two full text
indexes, one on title, and one on body.
| | 01:01 | Let's go ahead and create the table.
| | 01:05 | So our table is created, and now
let's go ahead and insert some data.
| | 01:11 | So we're going to insert a few rows of
data and there are these little articles
| | 01:45 | with just a little line of text in them.
| | 01:50 | And we just modify this
for each one. There we go.
| | 03:25 | So we've got six articles in our table now,
let's take a look at them, and there they are.
| | 03:36 | Now let's do some searches on them.
| | 03:50 | So we have a WHERE clause here for the
FULLTEXT search, and we are using two
| | 03:54 | keywords we haven't seen
before, MATCH and AGAINST.
| | 03:58 | MATCH specifies the columns to use
for the FULLTEXT search, and AGAINST
| | 04:03 | specifies the search term, in
this case the word database.
| | 04:08 | So let's go ahead and see what happens.
| | 04:11 | So we see we have the
word database in two results.
| | 04:15 | One has exactly the same case as the
one that we've searched for, and the other
| | 04:21 | one has different capitalization.
| | 04:24 | It has a capital D and a capital B.
And so we can see that we've found both
| | 04:28 | of those results, and this is called
a natural language search, and it's a
| | 04:33 | very simple search.
| | 04:34 | It's interesting if we had another word
here, say MySQL, this doesn't change our
| | 04:42 | results at all, because the word MySQL
appears in every single article, and so
| | 04:48 | the natural language search is
specifically designed to not return results for
| | 04:54 | search terms that exist in more
than 50% of the rows in the database.
| | 05:00 | And this is actually a very important
feature, because if you were to choose
| | 05:04 | search for a bunch of words, and one
of those words occurs in say 80% of the
| | 05:10 | possible results, you are
going to have two new results.
| | 05:13 | So you are not really going to be able
to find what it is you are looking for.
| | 05:17 | So the natural language search ignores
those results for search terms that occur
| | 05:23 | in more than 50% of the rows in the database.
| | 05:27 | On the other hand, if we look for the
word tutorial with database, this will
| | 05:32 | actually expand our results a little
bit, because the word tutorial occurs in
| | 05:38 | this row here, as well as in this one
that has word database in it as well.
| | 05:42 | If we look for the word management,
database management, we'll see one of our
| | 05:50 | results has both of those words
in it, and it comes up at the top.
| | 05:54 | And one of them also just has the word
database, and so it finds both of those results.
| | 05:59 | If we want to find results that
only have both the words database and
| | 06:03 | management, we need to do a
different type of search and it's called a
| | 06:06 | BOOLEAN MODE search.
| | 06:07 | We put the words here IN BOOLEAN MODE,
and then we have to put symbols on the
| | 06:13 | words that we want to, make sure that we match.
| | 06:15 | This symbol is called the add symbol,
and it make sure that both of these terms
| | 06:21 | exist in any returned results.
| | 06:23 | So this should just return this one
or with the id one, this first row.
| | 06:29 | And there we have it.
| | 06:31 | On the other hand, we can look for
results that do not have the word
| | 06:35 | management in them.
| | 06:37 | Just the word database but specifically
not the word management, and that will
| | 06:43 | return just this row, which has
database but does not have management.
| | 06:47 | Finally, there is a third type of
search that is supported by the MySQL
| | 06:51 | FULLTEXT search engine, and this is
called WITH QUERY EXPANSION, and this is
| | 07:02 | actually very interesting.
| | 07:04 | You'll notice that we have three
results and one of them does not have the word
| | 07:09 | database in it at all.
| | 07:09 | We're searching for the word database,
but we're searching for a WITH QUERY
| | 07:13 | EXPANSION, and it has given us three
results and it thinks we might actually be
| | 07:17 | interested in this third result, as well as
the ones they have the word database in them.
| | 07:22 | And the reason for that is
and how it does this search.
| | 07:26 | So the QUERY EXPANSION search works like this.
| | 07:29 | It goes two passes through the database.
| | 07:31 | The first pass, it looks for the search
terms that you've provided and then it
| | 07:36 | takes the results from those search
terms, and finds words that are common in
| | 07:41 | those results, and then does
another search for those terms.
| | 07:46 | So in our first results
it found some other words.
| | 07:48 | It found Tutorial, and it also found
Management and System and following and
| | 07:53 | comparison, and it didn't find any of those
elements, but it did find the word Tutorial.
| | 07:58 | And so it thought that
result just may be relevant.
| | 08:00 | We'll throw that in at the bottom, and see
if you are interested in that result as well.
| | 08:06 | So this is called QUERY EXPANSION.
| | 08:08 | It's actually a very useful tool, and
it will help to find results that maybe
| | 08:12 | you might not have thought of when you
are searching a large database of text.
| | 08:17 | So in a nutshell, that's
how FULLTEXT search works.
| | 08:20 | I encourage you to create some tables
and fill them with large amounts of text,
| | 08:24 | and experiment with this feature because it's
actually very powerful, and very simple to implement.
| | Collapse this transcript |
|
|
8. PHP's MySQLi InterfacePHP programming interfaces| 00:00 | PHP provides several interfaces for using MySQL.
| | 00:04 | The original interface will simply called MySQL.
| | 00:07 | It is modeled closely after MySQL's
basic C language interface, and is still
| | 00:11 | widely used, especially by
programmers with the background in C.
| | 00:15 | The MySQLLi interface was
introduced in PHP Version 5.0.
| | 00:19 | It's designed for a newer
versions of the MySQL database engine.
| | 00:23 | It's also object-oriented and
it's being actively developed.
| | 00:27 | If you're doing new development
using modern versions of MySQL in PHP, I
| | 00:32 | strongly recommend that you
use MySQLi instead of MySQL.
| | 00:37 | The PDO interface is a newer, more
modern object-oriented multi-platform
| | 00:42 | interface that supports more than just MySQL.
| | 00:44 | That supports other database engines as well.
| | 00:48 | This is the interface that I prefer
and the interface that I do my new
| | 00:52 | development, when I'm using MySQL with PHP.
| | Collapse this transcript |
| Introducing CRUD| 00:00 | As we talk about the MySQL interface in
PHP, I want to show you some code, and
| | 00:06 | use this code as an
example to explain how it works.
| | 00:09 | And this code is in a program called
CRUD, which does the Create, Read, Update
| | 00:14 | and Delete functions in a database.
| | 00:17 | It's the most basic kind of a database
program, and I created it for an example,
| | 00:23 | so that we can illustrate these basic functions.
| | 00:25 | So, we are going to go ahead and install
CRUD first, so you can see how it works.
| | 00:28 | And so that you can use it and test
it, and as you play with the code.
| | 00:32 | So, this is the XAMPP
directory in the local C drive.
| | 00:34 | I'm going to double-click there, and put it
in htdocs, and just copy the program over.
| | 00:44 | This is exactly how we did the SID
installation, and for those of you who are on
| | 00:49 | a Mac, you can just do
exactly what you do with SID.
| | 00:51 | I am going to go into the CRUD
directory, and I'm going to edit the CRUD
| | 00:56 | program, and make sure we put in our
web password, foo.baz, just like we did
| | 01:03 | with SID and with CRUD there is
actually a second copy crud-mysqli which is for
| | 01:11 | the MySQLi interface.
| | 01:12 | I'll right-click on it, click on Edit
with Notepad++, and put in the password.
| | 01:21 | Now we can go ahead and run it in the
web browser, and it's in the same place.
| | 01:29 | It's called CRUD, and crud.php.
| | 01:33 | So this is the CRUD application.
| | 01:40 | It uses the album database, and you
can see it's got a list of albums, and
| | 01:44 | inside each of these albums
there is a list of tracks.
| | 01:50 | And so, this is basically how it works,
you can add a new album, and album
| | 01:58 | must have an artist, so new artist,
and you can add tracks, track number 1,
| | 02:07 | this is track one, and it's in 12
minutes and 32 seconds, and a track two,
| | 02:17 | this is another track.
| | 02:20 | It's 3 minutes and 16 seconds, and so
on, and say Done, and there it is new
| | 02:34 | album, new artist etcetera. Here it is.
| | 02:36 | You can delete tracks.
| | 02:38 | Here is a little Confirmation page.
| | 02:43 | You can delete whole albums.
| | 02:45 | See that this deletes the
album and all of its tracks.
| | 02:49 | Now, what we are going to do is we
are going to look at the code that makes
| | 02:52 | all of this work, and see how that code
is written, and how it interfaces with
| | 02:57 | the MySQL database.
| | Collapse this transcript |
| Connecting to the database| 00:00 | So, let's take a look under
the hood in the CRUD Application.
| | 00:04 | This is a special version of
CRUD that uses the MySQLi interface.
| | 00:08 | The version of CRUD as I originally
wrote it, uses the PDO interface and that's
| | 00:13 | the interface that I prefer, and
that's the version that I'll maintain.
| | 00:15 | I created this separate version to
demonstrate how the MySQLi interface works.
| | 00:21 | So, this is the MySQLi version of CRUD.
| | 00:25 | This is the line here where
the database is initialized.
| | 00:28 | This is where we set up the connection to MySQL.
| | 00:32 | So, it creates a MySQLi object.
| | 00:35 | We are using the Object Interface of MySQLi.
| | 00:37 | MySQLi has both an Object
Interface and a Procedural Interface or a
| | 00:43 | non-object interface.
| | 00:44 | We are using the Object Interface to MySQLi.
| | 00:47 | The code looks a little bit different,
but it works pretty much the same, and if
| | 00:51 | you can follow along with this, if you
see code that's Procedural using MySQLi,
| | 00:54 | it should be obvious what it does.
| | 00:57 | So, this sets up the MySQL connection.
| | 01:00 | It provides the hostname, the
username, the password and the database.
| | 01:07 | The hostname is a variable,
and that's setup here.
| | 01:10 | It set to be localhost.
| | 01:13 | It set to literal value localhost
because CRUD is not designed for,
| | 01:17 | production environment.
| | 01:19 | It's only designed for a
personal testing environment.
| | 01:22 | So, we really only want to run our localhost.
| | 01:25 | We are not going to run
it on a remote host ever.
| | 01:26 | It sets up the database,
is also setup to album here.
| | 01:33 | The username, and the password, and the
database are all set up at the top of the program.
| | 01:40 | And here they are, and those are
constants, they are not variables.
| | 01:45 | So, a couple of things to note
about the MySQLi interface here.
| | 01:48 | One is that MySQLi does not
support persistent connections with the
| | 01:53 | Default MySQL driver.
| | 01:55 | Persistent connections are
very useful in a web environment.
| | 01:58 | What a persistent connection is, is a
connection between the web server and the
| | 02:02 | database server that stays active,
and stays alive and does not need to be
| | 02:07 | created every time there is a web connection.
| | 02:09 | Without persistent connections, every
time somebody brings up a webpage, even if
| | 02:13 | it's the same user in the same session.
| | 02:16 | From page to page, there is a whole new
instance of the web server, there is a
| | 02:20 | whole new instance of the program.
| | 02:22 | And that new instance actually has to
create its own connection to the database server.
| | 02:27 | And this takes time and resources.
| | 02:30 | Persistent connections reuse the
connection, so they don't have to be
| | 02:33 | setup every time, and that every time can be
a lot, especially in a busy web environment.
| | 02:38 | So, I consider persistent connections
viable, and they are not supported by
| | 02:42 | MySQLi in the default configuration.
| | 02:44 | And there is actually very few
configurations where it will work.
| | 02:48 | Another thing to keep in mind is that
there is a bug in versions of MySQLi
| | 02:53 | before PHP Version 5.3 that do not
allow the use of the Connect_error function.
| | 03:03 | So instead we have to do our errors this way.
| | 03:05 | So this is just the buggy nature of the code.
| | 03:08 | MySQLi is still being actively
supported and developed, but not very actively.
| | 03:15 | The developers are really paying more
of attention to PDO and so that's the
| | 03:19 | more reliable code.
| | 03:20 | So, we have to do a few little
things like this to work around some of
| | 03:23 | the problems in MySQLi.
| | 03:25 | So, this is the code
that sets up the connection.
| | 03:28 | It's actually very simple.
| | 03:29 | You create an object using the
standard new object syntax in MySQL.
| | 03:35 | And it puts the object in
the variable called dbh.
| | 03:38 | It's called dbh for traditional reasons.
| | 03:41 | That's just the traditional
name of a database handle.
| | 03:43 | So I like to use traditional names for
things, because it's easier to read the
| | 03:47 | code later when I come back to it, and I
haven't seen it for a while, I can know
| | 03:52 | pretty much instantly
what it is that it's doing.
| | 03:54 | So this is how the connection is set up.
| | 03:57 | If the connection setup fails it
calls the Error function, which is an
| | 04:00 | internal function that exists in the
CRUD Application, for displaying errors
| | 04:05 | in red on the screen.
| | 04:06 | And it uses this @ sign to prevent a
PHP from throwing an error to the screen,
| | 04:11 | and then it allows us to use
our own error reporting mechanism.
| | 04:14 | So, that's pretty much it.
| | 04:15 | That's initialization code for setting up
the database connection using MySQLi in PHP.
| | Collapse this transcript |
| Prepared statements| 00:00 | This is the function update_album_SQL.
| | 00:04 | This is where the SQL lives for
updating the Album table in the album database
| | 00:10 | for the CRUD Application.
| | 00:12 | One of the significances of this
piece of code is that it introduces us to
| | 00:18 | prepared statements and bound parameters.
| | 00:19 | We are going to talk a little about that here.
| | 00:21 | And talk about how this works.
| | 00:23 | So, here is the query.
| | 00:25 | You can see it's standard SQL, UPDATE
album, SET title, artist, label etcetera.
| | 00:31 | You'll notice that the values that
are being set to these column names are
| | 00:35 | question marks, and those question
marks correspond to this bind parameter
| | 00:41 | function call and each of these
variables title, artist, label, released and
| | 00:47 | id which is hiding off to the side there.
| | 00:50 | These question marks get
replaced with these values.
| | 00:54 | The bind parameter function in the
MySQL drivers actually go to the trouble of
| | 01:00 | properly quoting these for inclusion in the SQL.
| | 01:04 | So, that you don't have to worry
about that, and that's a big convenience.
| | 01:07 | It also prevents errors, and makes it
work more reliably in runtime, to have the
| | 01:14 | system doing that, rather than you
having to do that manually in the code.
| | 01:18 | So, that's what prepared
statements are, and that's why they exist.
| | 01:21 | They also allow some other efficiencies.
| | 01:23 | There are some cases where you might
be able to prepare a statement and then
| | 01:27 | loop bind different parameters, to the
same statement without having to prepare
| | 01:31 | the statement, over and over and
that can create some efficiencies.
| | 01:34 | But the major advantage here is in the
reliability and scalability of having
| | 01:39 | these parameters automatically
bound to the placeholders in the code.
| | 01:43 | So, I like prepared statements.
| | 01:45 | One of the things that disappointed me
about the MySQLi interface is that you
| | 01:49 | cannot use prepared statement
in all of the different contexts.
| | 01:53 | So that's one of the reasons why I
choose PDO over MySQLi for my work.
| | 01:57 | But this is how it works in
MySQLi and it's very useful.
| | 02:01 | You'll notice in the bind parameter
the first argument of the bind parameter
| | 02:05 | function specifies which types
these different variables are.
| | 02:09 | So, we have ssssi, which means
string, string, string, string, integer.
| | 02:14 | And that tells it, how to do the coding.
| | 02:16 | So, there you have it.
| | 02:17 | That's how the bound parameter and the
prepared statements work in MySQLi in PHP.
| | Collapse this transcript |
| Fetching results| 00:00 | So, let's take a look at how you
execute a query and fetch the results.
| | 00:05 | This is get_albums_sql and it's a
simple function that just assigns a query.
| | 00:10 | SELECT * (everything) from the album
ORDER BY title and executes that query, and
| | 00:14 | returns a handle to the results, so it
does not actually return the results, and
| | 00:19 | this is an important distinction.
| | 00:21 | Query Result Handle.
| | 00:22 | qrh stands for Query Result Handle.
| | 00:24 | There in the comments is a handle to be
executed query and allows you to fetch
| | 00:32 | the results one by one.
| | 00:34 | The importance of this is that if your
table is very, very large, and you don't
| | 00:39 | know how many albums somebody might
have in their albums database, there may be
| | 00:42 | more data that it would even
fit in memory in the computer.
| | 00:47 | If you go and load up a variable with
more data then it will fit in memory,
| | 00:50 | well, something is going to give,
and it could create security problems.
| | 00:53 | It could create stack overflows.
| | 00:55 | It could crash your computer.
| | 00:57 | So, instead what we do here.
| | 00:58 | As we perform this query and it gets
all set up and then the results are simply
| | 01:03 | grabbed one by one off of the disk.
| | 01:05 | And that happens up here
in the list_albums function.
| | 01:10 | It calls get_albums_sql, so it gets the
query result handle and then down here,
| | 01:15 | it grabs the first row and that way it
can test if there is no first row, then
| | 01:19 | we know that there is no albums in the database.
| | 01:21 | And otherwise, we eventually get
down here, and we set a row_count.
| | 01:27 | We go ahead and build the
results, album_result_row.
| | 01:31 | That's a function that builds the
HTML for the result from the row, and it
| | 01:35 | increments the count, and then goes
ahead and fetches more using this Fetch
| | 01:39 | Associative Function.
| | 01:40 | The Fetch Associative Function is
great because it assigns an associative
| | 01:45 | array to the result.
| | 01:46 | That associative array has all
of the column names in the keys.
| | 01:49 | So that's really, really useful.
| | 01:52 | So, this is how you do a
query and get the results.
| | 01:55 | You don't want to call some fetchAll
function that's going to load up your
| | 01:59 | memory with some unknown amount of data.
| | 02:01 | That way lies tragedy.
| | 02:04 | Instead, what you want to do is be
smart about it, execute the query and then
| | 02:07 | fetch the results one by one.
| | 02:09 | So, that you can deal with them in bite-size
chunks, and they will fit in memory
| | 02:12 | one at time and this is how
that's done in MySQLi in PHP.
| | Collapse this transcript |
| Auto-increment IDs| 00:00 | So, this is how we insert
a row into the database.
| | 00:03 | This is insert_album_sql.
| | 00:05 | This is for creating a new
album record in the database.
| | 00:08 | Here is our query, INSERT INTO album,
and we got title, artist, label,
| | 00:12 | released and the values are replacement
parameters that are given in this bind
| | 00:17 | parameter function.
| | 00:18 | So, what we have here is a prepared statement.
| | 00:20 | Now, the interesting thing is at this
particular table has an Auto Increment ID
| | 00:27 | Field, so you notice that
we don't insert an ID number.
| | 00:29 | The ID number is automatically generated.
| | 00:32 | And yet when we are done with this
function, we want to be able to pass the ID
| | 00:36 | number back, so that it can be used in
other context because that's important.
| | 00:40 | That's how we join our tables together.
| | 00:42 | So we need to keep track of that.
| | 00:44 | So, there is a special function in
MySQL called insert_id that returns the last
| | 00:50 | created Auto Increment ID.
| | 00:53 | So, this is generates an Auto Increment ID.
| | 00:55 | MySQL keeps track of it, and allows us
to grab it here, and return it as the
| | 00:59 | return value of this function.
| | Collapse this transcript |
|
|
9. PHP's PDO Interface with MySQLPDO is multi-platform| 00:00 | In this chapter we are going to look at the
code that is used to create the CRUD Application.
| | 00:05 | And this is the PDO
version of the CRUD Application.
| | 00:08 | This is the version that I intended
to write, this is the version that I'll
| | 00:12 | continue to maintain and this
uses the PDO Interface in PHP.
| | 00:16 | If you want a little tour around the
CRUD Application, look at the Intro movie
| | 00:19 | for the MySQLi Version in the last chapter.
| | 00:22 | This is the CRUD Application,
and this is the CRUD code.
| | 00:29 | It's written in PHP, and it uses the
PDO interface through the MySQL database.
| | 00:34 | So let's go ahead and get
started and start looking at the code.
| | Collapse this transcript |
| Connecting to the database| 00:00 | So, this is the initialization code in the
CRUD application, using the PDO interface.
| | 00:05 | The PDO interface is the one that I
chose for this application, so this is the
| | 00:09 | code that I actually use and maintain.
| | 00:12 | Here is where the connection to the
database happens and it's a little bit,
| | 00:16 | more complicated and little bit different
than the interface from the MySQLi interface.
| | 00:22 | It creates a new PDO object using the
new and it names that object dbh, which is
| | 00:28 | a traditional name for a database
handle and it uses this string for
| | 00:32 | initializing the database.
| | 00:33 | This is a carry over from the Perl
interface that PDO is based upon.
| | 00:37 | It's called DBI and Perl.
| | 00:39 | It's very common, very popular and it
works with multiple different databases here.
| | 00:43 | So, you specify which database you are using.
| | 00:45 | In this case it's MySQL.
| | 00:46 | This will work with Postgres or with
Oracle or Microsoft SQL server or all kinds
| | 00:52 | of different databases, using the same code.
| | 00:54 | You specify your host, you specify
your database name and we use the
| | 00:58 | constant MYSQLDB from that.
| | 01:00 | That's initialized at the top of the
code and username and password they are
| | 01:04 | also constant and initialized at the
top of the code, and here we specify that
| | 01:08 | we are going to use the Persistent Connections.
| | 01:12 | So, Persistent Connections are a
valuable feature and here what a
| | 01:15 | Persistent Connection is.
| | 01:17 | Whenever somebody clicks on a web page
or they move from one page to another
| | 01:20 | within the same site and within the same
session, that click creates a whole new
| | 01:24 | connection between the web
browser and the web server.
| | 01:26 | Now, under normal circumstances, that
would create a new instance over your
| | 01:30 | program, your program would have to
set up it's database connections and then
| | 01:34 | destroy the database connections at
the end of the program which is just when
| | 01:38 | they click on another page.
| | 01:39 | Now, this is resource intensive.
| | 01:42 | It uses a lot of resources to
create and destroy all of these database
| | 01:46 | connections, which slows things
down and makes things seem sluggish.
| | 01:49 | So, instead with a persistent connection
what happens is that web server manages
| | 01:54 | a pool of connections and every time
somebody clicks on a page, one of those
| | 01:59 | connections can be reused.
| | 02:01 | It's handed off to the program, they
can use that existing connection and then
| | 02:04 | the connection goes back in the pool
when they are done with it. This saves time.
| | 02:07 | It saves resources.
| | 02:08 | It makes your application feel snappier to
the user and it improves the user experience.
| | 02:13 | So, we are using these persistent
connections and PDO makes these persistent
| | 02:17 | connections readily available without
depending upon a particular driver for
| | 02:21 | MySQL or for whatever database.
| | 02:24 | So, this is how the database
connection is created and it's very simple, this
| | 02:29 | is the simple code in the initialization
section of the CRUD application using PHP and MySQL.
| | Collapse this transcript |
| Prepared statements| 00:00 | This is the update_album_sql
function, which updates the album table in
| | 00:04 | the album database.
| | 00:06 | You notice the SQL here UPDATE album,
SET title = ?, artist = ? etcetera.
| | 00:13 | The question marks are
replaced with actual values later on.
| | 00:17 | This is called a prepared statement
and it uses these tokens and allows the
| | 00:21 | execute statement later to replace
the tokens with the actual values.
| | 00:26 | That way the system can quote them
properly and it can format them properly for
| | 00:30 | the SQL without you having to decipher
is this an integer? Is this a string?
| | 00:36 | And it allows this all to happen automatically.
| | 00:38 | So, it's less prone to error, it's less
prone to crashing and it's less prone to
| | 00:42 | problems in general.
| | 00:44 | It also allows you under different
circumstances to prepare one statement once
| | 00:49 | and to execute it with a different set
of values over and over and over again,
| | 00:53 | which could be another type of efficiency.
| | 00:55 | So, this function uses prepared
statements and we use prepared statements
| | 00:59 | wherever possible throughout this code.
| | Collapse this transcript |
| Fetching results| 00:00 | So, let's talk about how we fetch data
from the database using PDO in PHP with MySQL.
| | 00:06 | This is the get_album_sql
function. It's very simple.
| | 00:09 | All it does is it executes a query
and it returns a handle to that query
| | 00:14 | statement, the statement handle.
| | 00:16 | The query is simple, SELECT * FROM
album ORDER by title and that gets prepared
| | 00:22 | and executed and what gets
returned is called a statement handle.
| | 00:26 | This statement handle represents the data,
but it doesn't actually contain the data.
| | 00:31 | With a statement like this SELECT *
FROM, you could end up with a lot of data
| | 00:36 | depending on how many albums are in the
database or how many tracks are in the
| | 00:39 | database, if we were doing this with the tracks.
| | 00:41 | You could easily fill up memory in the
server and you don't want to do that.
| | 00:45 | Instead what you want to do is you
want to be able to execute that query and
| | 00:50 | then take the results one by one.
| | 00:52 | That's what this does.
| | 00:53 | This returns a statement handle, which
represents a pointer into those result
| | 00:57 | sets, and then way up here where this
gets called into list albums, the first
| | 01:02 | thing it does is it calls that get_albums
_sql and it takes that statement handle
| | 01:06 | and it's starts fetching rows.
| | 01:08 | It fetches the first row
separately and it fetches it using this
| | 01:11 | FETCH_ASSOCIATIVE flag so that the row
variable is in an associative array where
| | 01:17 | each of the column names are key
names in the associative array.
| | 01:22 | So now you know the names of the
columns and then down here in this block of
| | 01:28 | code we actually loop through
grabbing the result rows one at a time and
| | 01:34 | building them onto the screen using
this album_result_row function which
| | 01:38 | actually builds the HTML
to put it up on the screen.
| | 01:41 | So this is the efficient way to do this
and it's the safe way to do this where
| | 01:45 | you execute the query once that sets up
the interface to start returning result
| | 01:52 | rows and then you grab the result rows
one at a time out of the database rather
| | 01:57 | than collecting them all in memory
and potentially having a problem with
| | 02:00 | crashing the program.
| | 02:02 | So this is how you fetch results from
a database using PDO and PHP in MySQL.
| | Collapse this transcript |
| Auto-increment IDs| 00:00 | Let's take a look at how you insert
data into a table that has an auto
| | 00:04 | increment id field.
| | 00:06 | This is the insert_album_sql function,
which inserts data into the album table
| | 00:11 | and here is the SQL INSERT INTO album,
title, artist label, released and it's a
| | 00:16 | prepared statement like we've seen
before with the replacement parameters.
| | 00:19 | But the interesting thing here
is how the id field is handled.
| | 00:22 | You will notice that we don't specify it.
| | 00:24 | We don't insert a value and that's
correct, because it's automatically generated
| | 00:28 | by MySQL when the data is
inserted into the table.
| | 00:31 | But it becomes available when
this function called LastInsertId.
| | 00:33 | So we are able to access it because we
actually need it to be able to tie other
| | 00:39 | data together using that id field.
| | 00:43 | That's an important piece of our drawing tables.
| | 00:45 | So we are able to access that and
return the value in this id variable.
| | 00:49 | So it's as simple as that.
| | 00:50 | This is how you insert data into a
table that has an auto increment id.
| | 00:55 | You are able to access that value
with the last insert id function and this
| | 01:00 | function here returns the
value in the id variable.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | In this course my goal was to give
you a good overview of MySQL's unique
| | 00:04 | features and tools so that you can get
the most out of this freely available and
| | 00:08 | valuable database management system.
| | 00:10 | I have shown you how to install XAMPP,
a complete package with MySQL, Apache, and
| | 00:14 | PHP, how to use MySQL's powerful command
line interface, how to use SQL features
| | 00:19 | that are unique to MySQL, and
how to code from MySQL and PHP.
| | 00:23 | I have also provided you with some
useful tools in the SID and CRUD applications
| | 00:28 | and you have the source code for both of them.
| | 00:30 | So you can use them to learn how to
create your own useful tools and applications.
| | 00:34 | I have covered the most common and
useful features here, but there is a lot of
| | 00:38 | interesting ground left for you to
explore, if you are inclined to do so.
| | 00:41 | I strongly encourage you to play around
with what you have learned, experiment
| | 00:44 | to try different things and
different combinations of things.
| | 00:47 | MySQL is a powerful and complex
system with lots of interesting nooks and
| | 00:51 | crannies to keep you engaged in the
process of learning for many interesting
| | 00:55 | hours of experimentation.
| | 00:56 | I have really enjoyed creating this
course for you and I sincerely hope all of
| | 01:00 | this is as useful for you
as it has been fun for me.
| | Collapse this transcript |
|
|
