Creating Tables and Inserting Data with MySQL
Now that we have a database, we're ready to create our table. Generally, you can imagine a MySQL table like a spreadsheet. A Database holds many of these tables. Let's access one of our Databases now:
USE dbname;
Still not forgetting our semicolons, replace dbname with your database name that you chose from the previous tutorial. This will put is into our chosen database, and you should see "Database changed"
Now, let's create an actual table. Again, this table is more likely what you consider a database, or at least that's how it was for me. So again, a Database just simply houses the tables. The tables actually house the data. To create a new table:
CREATE TABLE taula (time INT(13), username VARCHAR(20), tweet VARCHAR(140));
Press enter, and you're done. Now let's explain this entire query:
CREATE TABLE
MySQL syntax for creating tables, in all-caps to designate this as a MySQL command.
This is the name I have chosen for my table. Call yours what you want, just remember that when you see "taula" that you need to change it to your own name.
We begin the rules for this new table. Do not forget to close them at the end. It would be wise at this point to just close them off and add the semi-colon.
This is our first column's name. Call it something that makes sense. Many people make the first column "id" and do an auto-increment. We are not doing this here.
This is our datatype, and then the length of that datatype. In our case, we're calling this an INT for integer, and saying it will be 13 characters long. Integers are whole numbers. If you need decimals, then you would use a FLOAT or DECIMAL depending on your needs.
Another column, our second one, and we're calling it "username."
This datatype is "VARCHAR," which is variable characters. The variable is in reference to the length. This means you can put in 5 characters, or 19, and it will not take up any more space. If you put CHAR(20), then it would not be variable. Now, what if we try to insert 21 characters into our VARCHAR(20) column? The sky wont fall, but the last character will be chopped off. Thus, you want to be careful when choosing limits. If you have a website that has a login, for example, and you intend to limit usernames to 20 characters, you need to make sure this limit is in the database as well as in the registration form.
This is our last column name, tweet, of the datatype of VARCHAR, which will be up to 140 characters in length.
You should have added the closing parenthesis and the final semi-colon already, but if you have not, do this!
There are many other datatypes to consider like TEXT, FLOAT, DOUBLE, DECIMAL, BLOB, TINYINT, SMALLINT, MEDIUMINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, YEAR, TINYBLOB, TINYTEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM.
Why so many types? Doesn't VARCHAR pretty much cover it all?Most of those you will probably never use. You can really get by with pretty much all varchars, some int, some float.
So then why might there be this many datatypes? Especially considering all these "tiny, small, medium, and large" examples? For the typical database, using these data types wont really yield much benefit, but what if you're running a truly massive database that is terabytes in size and millions of rows?
Let's consider FLOAT vs DECIMAL vs DOUBLE for a moment. Many people question why these even exist, as they are built to contain exactly the same looking data.
It all depends what you're doing with the data. Are you just storing it and you want to access it quickly? Are you going to attempt to do calculations on the data or run other types of logic?
Different datatypes are going to act slightly different in various scenarios, and, depending on your uses, you may find one is superior to the others. As your database grows, and your needs change, you will likely want to revisit data-types and test performance with different ones.
Now that you've created this table, let's look at it real quick:
DESCRIBE taula;
This is how you look at the structure of your table. You will see the field names (like your columns), the datatype, and some more information.
Now let's insert some data. Eventually, our goal is to have our program insert data for us, but it is a good idea to do it yourself to better understand how it works before we get into doing it via a program.
INSERT INTO taula VALUES (1385419969, "sentdex", "welcome to my tutorial");
Above is a typical insertion query. Here's each step explained:
INSERT INTOThis is a MySQL command, notifying MySQL that we're about to insert some data.
This is our table name, so we're telling MySQL where we're going to be doing this inserting.
This is another MySQL command, which notifies MySQL what we're planning to insert.
Here is the list of inserted values. Notice how we are inserting the values in this specific order, these are the values in order of our columns (time, username, tweet)
Now feel free to insert a few more rows, increasing the time slightly. Here's a few I used in the video.
INSERT INTO taula VALUES (1385419970, "sentdex", "wow thanks @Obama, you are right these tutorials rock");
INSERT INTO taula VALUES (1385419971, "sentdex", "@BillGates, thanks Bill for the job offer as VP, but i wanna keep opts open");
Now we have the data in the table, how do we access it?
SELECT * FROM taula;
This SELECTs all (which is denoted with a *) FROM the table named taula.
Now you should see each row organized like a spreadsheet.
