Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
MySQL is by far the most popular database management system for small- to medium-sized web projects. In this course, Bill Weinman provides clear, concise tutorials that guide you through creating and maintaining a MySQL database of your own. Bill explores the basic syntax, using SQL statements to create, insert, update, and delete data from your tables. He also covers creating a new database from scratch, as well as data types, transactions, subselects, views, and stored routines. Plus, learn about the multi-platform PHP PDO interface that will help you connect your database to web applications.
My database is effectively a container for tables and related metadata. In MySQL, a database is implemented as a file system directory or folder that contains the files that represent the tables in the database. These files will be stored in a different place on different installations. Here in our Zamp installation, you look under Applications. And ZAMP and ZAMP files VAR, VAR and MySQL, these are the folders that represent the databases.
There's our scratched album and world databases. And you notice these red marks here mean that the permissions are not going to allow us to open those folders. We'll get around that in a moment. First, I want to show you on a PC where you can also find these files. So, here on a PC you'll find it under your local c disk. Assuming you have the default ZAMP installation and then the ZAMP folder. And then MySQL and Data. And there's those same folders.
There's our album database, our scratch database, and our world database. And you'll want to keep this open, you're going to look inside of there, you can actually open these folders on a PC. On a Mac, it's a little more complicated, and I'll show you how to do that. You're going to want to keep this folder open, to look in there as we create at database. And likewise here on your Mac, you're going to want to keep this folder open in your finder, so that you can look inside of it as we create a database. Now, we're going to go, and create a database. So, you're not going to have any database selected here, and we're just going to type CREATE DATABASE.
And I'm just going to name it foo. f o o. And when I press go, it says one rows affected, that's talking about the table in MySQL's own internal structure that keeps track of databases. So it's affected a row in there. But if we look out here in our file structure, you'll see there's now a folder called foo. And you'll see that on the PC as well. Here on the map, we have to go through a little bit of rigmarole to open that folder up. You can just open it your PC and see what's inside it.
But here we're going to right-click or Ctrl-click and select Get Info. And when we come down here and hit this lock to unlock it, and I'll need to type my system password. And now I'm going to want to add myself here now because the only thing that has read write access here is my SQL. So these permissions are very strict and so I'm going to press the plus sign here and I'm going to add myself. So there's my Bill Weinman you'll just add your own. And under my privilege there I'm going to switch to read write.
And now you'll notice that foo no longer has that little red mark, and I can close this. And now I can look actually look inside of that folder and we see the DB.opt. So that's the metadata for the database. Is in that file there. So now when I come back out here to SID, because I don't have this in my database selector. In order to use this database I just have to type use foo and then whenever commands I put after that are going to be relative to the database foo. So, I'm going to say, create table, we'll just call the table foo, give it a couple of columns, doesn't really matter what they are.
And I'll go ahead and insert into foo so we can see that it works. Values one text and select asterisk from foo. Now, when I press go here it will create that table, it will insert a row and it'll select and we'll see the data, so there's the data right there. And when we come back out here to our file system, we see there's now a couple of files in there for that foo table. And if I come back out here and I drop the table, say drop table foo.
Then you see that those files are gone from that file system. And, in fact, just going to jump up a level here. If I drop the database, and this is of course how you delete a database, is with drop database. So drop database foo and press go and now you see our foo folder is gone entirely. So MySQL implements a database as a direct reviewer folder, that contains files that represent the tables in the database.
There are currently no FAQs about MySQL Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.