April 25, 2013
MySQL is a database language, its pretty straightforward, almost to the point of being confusing. Admittedly some of the commands will be confusing and not as straight forward since they rely on a shorthand version of what the command would originally be. For example the "describe" command which is used for displaying information in "Tables" is "DESC". Usually you input MySQL commands through a Linux command line, either through the bash ($ or #) or MySQL prompt (mysql>) after logging into MySQL.
First off let's explain what databases are: Imagine an excel spreadsheet if you will; now the Sheet itself will be the Table and the Window in which the spreadsheet is in is the Database. Databases are just containers for Tables, tables are the containers for the information you will be using for your website, program, etc. Databases may contain multiple tables and each table can have an excessive amount of columns and rows depending on what you need stored in those tables. (I will be using HTML tables to demonstrate what I'm trying to explain to give you some visuals).
Imagine you have a shop that sells fruit. You would probably need two (2) separate databases to handle your transactions. One database for storing your fruit inventory and another for handling your cash register. This is because each database will require a different engine, one that handles queries faster and one that is more suited for cash transactions because it's more secure. We'll go more into database engines in later tutorials.
For now we'll focus on the inventory. For this example we'll make a database and call it "inventory". We'll stick to lower case naming since most often commands are input as all capital letters. It is not required that commands be entered in all capitals but it does help sort out what is data or a table name, and what is a command or condition.
You're going to need a server and be able to SFTP into the aforementioned server. You won't have to work in the same directory as you website, however if you are doing some MySQL commands with documents (In specialized cases) you will need to be in the same directory as the document(s) you are working with.
After you log into your server you input the following to log into MySQL using the same credentials as your server account.
$ mysql -u username -p
You will be prompted for your password,
If you log in successfully you should be prompted by the following message and a MySQL bash.
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is ##### Server version: 5.1.67-0ubuntu0.10.04.1 (Ubuntu) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
From here you input MySQL commands, of which, all need to end with a semicolon ";". If you fail to put a semicolon the program will assume there is more to the command. If you forget it and simply type a semicolon and hit Enter. The command will execute as if there was no white space between the semicolon in the command when you hit Enter. Not having the command execute if there is no semicolon is really helpful because as you'll see later, some of the commands (especially for creating the column headers for your tables) can get really messy. Being able to return adds some much needed white space to separate things so you don't get lost and it is easier to read.
Depending on how your MySQL was set up and your access rights there may be some databases already visible such as "information_schema", "mysql", and "phpmyadmin". I suggest you just leave those alone for now.
As stated before we're going to create a database called "inventory" for your fruit stand. To do this we input the following commands:
mysql> CREATE DATABASE inventory
You should see the following line under your command:
Query OK, 1 row affected (0.01 sec)
To see your databases you simply input the following:
mysql> SHOW DATABASES;
The following will appear:
+--------------------+ | Database | +--------------------+ | information_schema | | inventory | | mysql | | phpmyadmin | +--------------------+ 4 rows in set (0.00 sec)
The above should appear, as you can see our database "inventory" is listed second. Right now it's empty but we will populate it with tables shortly.