My name is Mason Ellwood, and I’m currently working on Flatiron School’s Online Full Stack Web Development Program. Each week, I’ll be writing about my experience, what I’m learning, and tips on learning to code.
Back at it, SQL! Last post we created a database and learned how to add information to the database using a .txt file. Let’s learn a little more about SQL and a few rules to follow.
So when creating a table you need to make sure to include a name and at least one column. Remember from last post, we use a create statement by including a name and a data type. This allows SQL to know the kind of data that will be stored there. This allows you to have control over your data. Because the main purpose of SQL is to store a large amount of data, you should be very concerned with storing, accessing, and acting upon the data that you are storing. This makes defining your data very important.
Defining your data, or typing gives the ability to perform all kinds of operations with predictable results. Without typing, our data would be a mess or confusing; so we don’t want to do that haha.
Because we are using SQLite we will continue to explicitly talk about rules directly related to it. So anyways, SQLite only has four basic categories for defining data
- TEXT: Any alphanumerical character which we want to represent as plain text. And example of this is the body of a paragraph, this would be considered TEXT.
- INTEGER: Anything we want to represent as a whole number. Keep in mind, if it is a number and contains no letter or special characters or decimal points, then we should use an INTEGER.
- REAL: Anything that is a decimal point. Example being 1.3, this would be considered REAL. In terms of decimal point, SQLite will only hold numbers that are 13 characters in length.
- BLOB: This is generally used to hold binary code, so far in the lessons, we have not used this, so I am unsure at an example i can give you all.
The above creates a database. Then creates a cats table with an id, name, age, and breed column. Running .schema will display your database table structure to make sure you did this correctly,
As you review this, take notice that we use the INSERT INTO command, followed by the name of the table to which we want to add data to. In the parentheses, we put column names that we will fill with data.
Once your database if setup correctly, we can then add information to it!
This adds information about the cat Maru to your table. To retrieve your data to make sure that you have added it correctly, run the code below.
The * character is equivalent to all. Basically saying select and display all from the cats table.
Cool! Now you know how to create a database, add information to your database, and display that added data to the use (being you). Please post a comment and let me know if you have any questions regarding the material covered.