Page - Learn MySQL in Plain English: A Beginners Guide to MySQL

Table of Contents

The sole purpose of a database is to store information in an organized way.

Thats really as simple as it gets, and it is something that you should always come back to throughout this book.

We want to store our information in an organized way, such that we can retrieve the data quickly at some later date and time. We not only want to be able to retrieve our data at some point in the future, but we also want to get it VERY quickly.

Im talking split second speed here!

In a test that I literally JUST did on my own computer as I was writing this sentence, my laptop was able to return over 110,000 rows of data in just 1 second.

Think about that

Im talking about the ability for my simple laptop computer to get the equivalent of ALL of the transactions youve ever made with your credit card and debit card combined over the course of your entire life plus ALL of the emails youve ever sent in the past 5 years of your life, in under 1 second.

Thats a lot of speed!

So I guess its fair to say that modern databases are pretty darn efficient when it comes to storing and retrieving data.

And thats exactly what youre going to learn about in this book. Youre going to learn how to harness the power, speed, efficiency and sheer coolness of databases.

First things first, lets make sure you can visualize what a database is.

Im all about visualization to aid in learning new concepts, so lets start with a simple spreadsheet. You know, with rows and columns with boring figures and data points?

Simple spreadsheets arent far off from what modern databases are all about. Its actually why some companies choose to store their precious information in a spreadsheet instead of relational databases. This, however, is a huge mistake if you hope to store any larger amounts of data.

In any case, just like spreadsheets have rows and columns, so do relational databases. Rows make up the unique data amount something in particular, and columns give us the ability to store more and more information about each unique row of data.

For example, lets take a look at a simple spreadsheet that outlines information about a User. Lets assume we want to store some information about Users so that we can have them login to an application.

We keep track of many things like their username, password, account type, and so on. Each one of these unique things we keep track of is a column. It tells us something of interest with regards to this particular spreadsheet.

Each row of data makes up the unique users stored in our spreadsheet. Each row is unique, as we never want to have a duplicate user. Imagine the chaos of storing two users with the same username if one of the two people logged into the system, which data would you want to show them? You could accidentally show them the data that belongs to the OTHER user that shares the same username.

Maintaining unique rows of data is of paramount importance in databases. But well dive into that later.

For now I want you to concentrate on the concept of rows and columns and tables.

Well a table is where we stored the rows and columns. You can think of a table like an individual spreadsheet. A table is where all of our data is actually stored in our database. Its kind of like a folder on your desktop. It helps us organize our data.

In our example of users, we would likely have our information stored in a Users table. We would name it that, because that name just makes a whole lot of sense. Were storing user information in the table, so we might as well name it the Users table.

Tables are typically named after nouns. A noun in the English language means a person, place or thing. If you have any object oriented programming experience, then you can think of a table as an Object. If you dont have any programming background, then no worries, I wont make too many nerdy programming references.

Alright, so youve just learned that these things called Tables are used to store information by following the format of rows and columns. And believe it or not, thats pretty much as complex as a database really gets in terms of how to visualize it. So just be sure to have those terms memorized before continuing, as Ill be using them a lot throughout this book.

Now lets shift our discussion into the actual operations that a database carries out during the course of its life. Obviously databases were created to actually DO stuff right? We call the work that a database does an operation. Its not brain surgery, but it does require some detailed knowledge of how to talk in the language of databases.

What the heck is the language of databases? Why its SQL of course! SQL is an acronym that stands for Structured Query Language.

I usually pronounce SQL as sequel (like the sequel to a movie). Some people dont agree that it should be pronounced this way and insist on just saying the letters Ess -Queue-Elle. Im a man who likes efficiency, and saying the letters in SQL takes longer than saying sequel, so you can guess how I pronounce it. In any case, knowing how to say the name of the language of databases isnt why you have this book I presume.

So lets learn how to speak in our database tongue shall we?

C reate

R ead

U pdate

D elete

This is the stuff at the heart of all databases and SQL. A database essentially carries out these four operations over and over again for the duration of its existence.

In this initial discussion, I talk about these four database operations in detail and the actual syntax that is used in a flavor of SQL known as MySQL.

You can think of SQL as a kind of specification that needs implementation. In Java terms, you can think of SQL like an interface and MySQL like the class that implements it.

I chose to go into depth with MySQL as its a free implementation of SQL, it is popular and widely adopted with plenty of HOW-TO articles around the net.

If you wish to learn how to install the MySQL RDBMS and a GUI for manipulation your database, then please check out this video: link required here

Lets talk syntax!

The first step in our CRUD operations is the Create operation. This is used to create data in the database. This can be accomplished using the insert keyword.

Lets assume you would like to insert a new User into your database. How would you accomplish this?

I like to approach these questions with the mindset of What would I need to know if I was the database management system? I would need to know where to insert the data, and specifically what data to insert.

Lets see what that looks like in the MySQL syntax:

insert into users (username, password ) values ( 'tpage' , 'password123' );

In the SQL code above, we have satisfied the questions of where and what . The where is satisfied by specifying the name of the table in which the data is to be inserted, and also the columns we wish to populate. The what is satisfied by the actual data we are providing in the single quotes.

This is by far the most commonly used operation in any database. The ability to retrieve/read data in a database is crucial to a properly functioning application. In order to read information from a database using MySQL youll use the select keyword. Im not sure why they didnt choose to go with a keyword like read, but hey, what can you do!