Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Throughout the remainder of this video series, I'm going to be using a database that's built and stored in the MySQL database engine. I'll show you how to define a database from scratch first in phpMyAdmin, a web-based application that's included with both WAMP server on Windows and MAMP on Mac. Then in another video, I'll show you how to import an existing database from a script. To start up phpMyAdmin on Windows, go to the system tray icon, and then go to phpMyAdmin.
If you're working on MAMP with Mac, go to the MAMP homepage, and then click on phpMyAdmin. On the phpMyAdmin homepage, click into Create new database, and then type in the name of the database you want to create. Your database name should not have any spaces in it. If you want to represent a space, use an underscore character, type the name of the database, and click Create. You're taken to a screen to create tables. You can have as many tables as you want in your database.
Each table can have as many fields or columns as you need. In this initial database, I'm only going to have one table. It'll be named people. Set the initial number of fields. If you don't get this exactly right, it's no problem. You'll be able to add fields later on. I'll set my initial number of fields to 3, and click Go. On this screen, you're asked to define the fields or columns in your database. The first field should always be a primary key.
I typically set the name of this column with the name of the entity that's being stored in the table, such as person for the people table, and then I append Id to the end of it. So the name of my primary key column will be personId. In MySQL databases, it's very common to set the primary key column as an integer, and then to add the AUTO INCREMENT property. That means that each time a new row or record is added to the table, the next available numeric value will be assigned to the table automatically.
I first have to say that this is a primary key. You do that by setting the Index property to PRIMARY. Then I'll check the AUTO INCREMENT property. The other two columns will be named firstName and lastName. They'll both be set to variable character, or VARCHAR columns. VARCHAR columns can take text values. I'll set them each to VARCHAR. Then I'll set the available length for the columns to 50 characters each.
That means that that's the longest available value that you can put into the field. Finally, I'll set the Storage Engine for this table. The Storage Engine indicates how the table will be physically stored on disk, and also what its capabilities will be. The default is a storage engine named MyISAM. MyISAM is an incredibly fast storage engine. It's the default. When you set a table up as MyISAM, you don't get transactional capability - I'll give you an explanation of what that means in a moment - but you do get very fast storage and retrieval.
If you need transactional capability, meaning the ability to commit and roll back transactions, you can instead select the InnoDB engine. As with other settings, if you don't set this exactly the way you want it initially, you can change it later. I'll accept the default of MyIASM. Then, down at the bottom of the screen, I can click Save, or if I click Go, I can add additional fields or columns. I'll click Save, and that creates my table. On this screen, you're shown the actual SQL code that was used to create the table.
Also in the table structure, you can see what the columns are, their data types, and their properties. If you need to edit any of these columns, you can click on the Edit icon, represented by a pencil, and you'll be taken to this data entry form. If you click Save, you'll see the resulting SQL statement that was used to make the change. If you need to add fields, you can use the form down in the middle. You can add fields to the end of the table, or after any of the existing fields. If you need to completely drop the table, or delete it, you can click the Drop icon.
Be careful with this. If you drop a table or a database, there is no backup. You'll need to create it from scratch later. Once you've created the table structure, you're ready to add data to the table. I'll show you how to do that in a separate video.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91401 Viewers
80 Video lessons · 138269 Viewers
59 Video lessons · 57104 Viewers
52 Video lessons · 70759 Viewers
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.