MySQL Basic Tutorial

[Atli]

This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.

Anyone should be able to get through this without much trouble. Programming knowledge is not required.

Index

1. What is SQL?
2. Why MySQL?
3. Installing MySQL.
4. Using the MySQL command line interface
5. Creating databases
6. Creating Tables
   1. Data types
   2. The CREATE TABLE command
   3. The Primary Key
   4. Column parameters
7. Insert data
8. Select data
9. Update data
10. Delete data
11. Delete (Drop) tables and databases
12. User creation
    1. CREATE USER method
    2. GRANT method
    3. Privileges

1. What is SQL?
SQL, or Structured Query Language, is the mechanism which we use to manipulate data within our databases. This mechanism is based upon issuing commands (or statements), which are often referred to as SQL Queries.

The following example is perhaps (and sadly) the most used SQL query ever written. It simply commands the system to return every column of every row in a given table.

SELECT * FROM tbl_name

2. Why MySQL?
No database management system, such as MySQL, is essentially better than any other. They are all based on the same standard and most basic queries, such as the one I showed you earlier, can be executed on all of them. So by learning MySQL, you are also learning the basics of all the other SQL database systems.

There are, however, slight differences between them. MySQL, being open-source, has become very popular over the years, especially among developers who work on other open-source platforms, such as PHP. It has been known for being one of the fastest RDBMS (relational database management system) available. - The accuracy of that claim does of course vary as newer versions of MySQL and it's competing products are released.

MySQL is used to power the back-end of some of the most high-profile internet services in the world, such as Google, Wikipedia, Facebook, YouTube, Twitter, and many more, with a total of over eleven million active installations.

3. Installing MySQL.
Installing MySQL is simple enough. It is open source so you have the option of downloading the source and compiling it yourself. I would, however, recommend against this, as precompiled binary versions exists for most major operating system.

For windows users, you can simply download the MS installer package, which will pretty much take care of everything for you. The only thing you have to do really is choose a password for the root user.

Linux users can also in many cases find binary packages, but I will leave it up to you to find out how to install it on your distribution. MySQL is often provided with the OS installation, so you might want to look out for that. - Users of popular distros such as Debian, Ubuntu, Fedora and so on, can often find MySQL in their software repositories. If you are unsure of whether or not that applies to you, try either of:

$ apt-get install mysql
$ yum install mysql

That will often get you started.

Having never tried this on a Mac, I would assume it is as simple as on Windows.

If all else fails, you can always download the source and build it yourself.

4. Using the MySQL command line interface.
Now, once MySQL has been installed on you system, you will need to find your way to the MySQL CLI (Command Line Interface), which you can use to execute your SQL queries.

On windows, this would be located under 'MySQL' in the All Programs menu.
On Linux, you should be able to simply type 'mysql' in the terminal.
The same goes for Mac.

Once the CLI is open you will need to enter the root password. This is the password you specified when you configured MySQL during the installation. That is; this is the password given to the root user.

As on Unix, the root user has access to all areas of the system. It should never be used to do anything that you can do with a regular user. Using the root user in situations where a normal user can be used is a major seciryt risk, and a sure way to get a slap on the back of the head from your boss!
(See chapter 12 for info on how to create users)

5. Creating databases.
Databases are basically collections of data tables. - Each table is made up of one or more columns, which define the data it is meant to store. - The actual data is stored as rows in the table, each row containing a data field for each column in the table. (Much like you see in spread-sheet applications such as Excel.)

MySQL comes by default with a set of databases. We can issue a command that shows us all databases that the user has access to. As we are logged in as root, we will see all databases.
The command is simple:

SHOW DATABASES;

This should output something like this:
(This can vary based on your OS and MySQL setup method)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

These three databases are - usually - created by default by the MySQL installer. The first two, 'information_scheme' and 'mysql', you should not mess with, unless you know what you're doing. They are used to store information used by MySQL, such as user information and table layouts, which is generated by MySQL itself.

The third one, 'test', on the other hand, should be messed with. That is it's purpose.

We will not be using any of those, though. We are going to create our own database. This is done by a very simple command:

CREATE DATABASE new_db;

Where 'new_db' is the name of our Database.

Now, to use our new database, we would have to include it's name in all our SQL queries, so that MySQL will be avare of what database we are using.

SELECT * FROM new_db.some_table;

However, as this can get very tiresome, very fast, the MySQL CLI provides us with the USE command. This command tells the MySQL CLI client that we want to use the given database, so we can just leave it out of our SQL queries. (Sort of like relocating to a different country to avoid having to dial the country-code first when calling there.)

USE new_db;

And we can rewrite the previous query like this:

SELECT * FROM some_table;

6. Creating Tables
Like I mentioned earlier, each database contains a number of tables. And as with the databases, we can issue a command to see all tables we have access to in a given table. This is essentially the same command, only the parameter changes:

SHOW TABLES;

Which would output nothing, as we have not yet created any tables.
Keep in mind that by using the USE command from before, we avoid having to specify which database we want to be using. If you did not use that command, or want to view the tables for another database, you can add the database name to the SHOW TABLES command:

SHOW TABLES FROM new_db;

6(a) Data types
Before you start creating tables, you must understand the basic data types each column can have. This here is in no way a complete list, which incidentally can be found here.

• Bool – This field can only contain one of two values, 1 or 0. They can also be referred to as TRUE or FALSE (0 being FALSE).
• INT – This is a integer type, which contains non-fractional numbers. There are several different sizes, ranging from 'TinyInt' to 'BigInt'.
• Double – This is also a numeric value, except this one is designed to allow fractional numbers.
• Decimal - Due to mathematical limitations, the DOUBLE type's fraction part is not always completely accurate. The DECIMAL type, however, is made to store 100% accurate fractional numbers. Ideal for storring monetery values.
• DateTime – This is a special field for date and time. It is common to use the NOW() function to populate a field of this type. Note that fields can also be created to contain either Date or Time, by simply separating the two words and leaving the one you want.
• Char(n) – This is a string of characters. The n represents the number of characters the field will contain. The max number of characters you can specify is 255. Even if you provide fewer character than the n specifies, the field will always store n number of chars, and will subsequently occupy the amount of disk space n characters will take. In those cases, the unfilled character slots are filled with empty spaces. (MySQL automatically takes care of adding and removing them for you, so no worries about that.)
• VarChar(n) – Like the Char type, this is a string. The difference is between them is that the n here represent the max number of characters allowed. The max number of chars you can specify is 65,535. If you provide fewer characters than n specifies, MySQL will automatically decrease the size of that field to match the number of characters provided, thus saving disk space.
• Text and Blob – These are meant for large ammounts of either binary (Blob) or string (Text) data. The difference between the two is that Blob is treated as raw binary data, while Text is treated as a string with a set character-set. - There are four sizes. By default the fields can hold 64 KiB of data. You can prefix them with Tiny, Medium or Large to modify that to 256 bytes, 16 MiB and 4 GiB, respectively. (Note that your system may limit the actual amount that can be stored, like if you are using 32bit Windows or the FAT file-system.)

6(b) The CREATE TABLE command
The command to create a table is a little more complex than the simple commands we have seen so far. (Click here to see a complete definition)

CREATE TABLE tbl_name
(
  Col1Name Col1Type <parameters>,
  Col2Name Col2Type <parameters>,
  …
  ColNName ColNType <parameters>,
  PRIMARY KEY(col1, col2, …, col16)
);

Here we have the command 'CREATE TABLE tbl_name', followed by a list of columns we want created in our table encapsulated, separated by a comma.
Each of the columns has a list of parameters. The first being it's name, the second its data type followed by a list of additional options.
The options are used to for various reasons. Such as to indicate a column is a Primary Key, or that it will not accept NULL values, or define a Default value. The list goes on.

6(c) The Primary Key
The last line in our column list brings us to a very important concept; the primary key. Every table should have one or more columns that are defined as a primary key.

The primary key must have an unique value for each row, as its whole purpose is to identify a single row out of the rest, even if every other field in the table contains identical data. It is best to define a Primary Key column as an integer, as they can be automatically incremented and have been known to work faster than other types. It is, however, possible to define other data types as Primary Keys, but this should only be done under special circumstances.

6(d) Column parameters
These are a few common and much used column parameters:

• Primary Key – Identifies the Primary Key of the table. Note that this parameter can not be used on more than one column! Use the syntax suggested in the CREATE TABLE definition above to define multiple primary keys.
• Unique – Makes sure this column has a unique value for each row. This is in many ways like the Primary Key, but is not limited to a single column per table. - The difference between the two is mostly symbolic, as they behave almost identically.
• Not Null – Indicates that the column does not accept NULL. - A NULL simply means: "no value". - It is often confused with an "empty value", like that of an empty string or the number 0, but even an empty value is, be definition, a value. - The practical difference is that a NULL can not be compared as a value (using operators like = and <>). To test for a NULL you must use IS NULL or IS NOT NULL.
• Auto_Increment – Used with numeric columns. This basically means that the columns Default value is the largest value previously used +1. Note that even if you remove every row in the table, this value will not be reset.
• Unsigned – This will effectively double the max size of an integer field, but as a result it cannot accept negative values. Perfect for ID's and such.
• Default value – This specifies a default value for a column. Note that some fields (Blob, Text, and the DateTime types) can not have a default value. It's a good idea to include this for most columns, unless you have a reason not to. By doing so you allow INSERT queries to skip those columns if needed. (See chapter #7.)
• References tbl(col) – This creates a Foreign Key constraint between the specified column and the new column. That is; it links this column to a column in another table, so that a value that is not present in the target table can not be inserted into this one. - Note, this is currently ignored by MyISAM tables.

Example
Now, let's create a couple of tables. Say we want to store information about an imaginary group of users on an imaginary web that we are supposedly creating.
We would want to store basic info on the user, such as user_name and password. It would also be good to store more personalized info, such as first and last names and date of birth.
For that we create a 'User' table that contains columns for every piece of data we want to collect.

We would also want some contact info. And for that, we create a seperate table, 'user_contact', which contains it's own Primary Key and a Foreign Key that references the Primary Key of the 'User' table. This is typically called a one-to-many relation (1:N), which here means that each User can be linked to an unlimited (or untill you run out of diskspace) number of rows in the 'user_contact' table.

We can create these two tables using these two commands:
Each command ends with a semi-colon (;)

CREATE TABLE user
(
  user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(255) NOT NULL UNIQUE,
  joined DATETIME NOT NULL,
  password CHAR(40) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE Not Null,
  PRIMARY KEY (user_id)
);
 
CREATE TABLE user_contact
(
  contact_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id_fk BIGINT UNSIGNED NOT NULL REFERENCES user(user_id),
  contact_label VARCHAR(255) NOT NULL DEFAULT 'Email',
  contact_value VARCHAR(255) NOT NULL,
  PRIMARY KEY (contact_id)
);

7. Inserting data.
Now that we have our database and a couple of tables, let's insert some data.

To do that, we use the "INSERT INTO" command.
(Click here for a complete definition)

INSERT INTO tbl_name
  (col1, col2,…, colN)
VALUES
  (v11, v12, …, v1N),
  (v21, v22, …, v2N),
  …
  (vN1, vN2, …, vNN);

As you can see, we specify the table name and each column from that table we want to insert data into. Then we encapsulate the data we want to enter and put it after the VALUES clause.
Note that you can insert multiple rows at a time, by simply adding them, one after another and separating them by a comma.

So, to insert some data into our imaginary user tables, we could do something like this:

INSERT INTO user
  (user_name, password, joined, first_name, last_name, date_of_birth)
VALUES
  ('Atli', SHA('MyPass'), NOW(), 'Atli', 'Jónsson', '1986-09-05'),
  ('Johnny', SHA('Insane'), NOW(), 'John', 'Doe', '200-07-07');
 
/* Email for user 'Atli' */
INSERT INTO user_contact
  (user_id_fk, contact_label, contact_value)
VALUES
  (1, 'Email', 'me@myself.i');
 
/* Phone for user 'Johnny'*/
INSERT INTO user_contact
  (user_id_fk, contact_label, contact_value)
VALUES
  (2, 'Phone', '5885566');

Note, that the SHA() function creates a 40 character long encrypted string that can not be decrypted (a "hash"). For security reasons, passwords should always be stored as hashes.

8. Selecting data.
At last we have some data in our database!
If only there was a command that could show us that data. But wait… there is!

The SELECT command does precisely that. It selects rows, based on the parameters it is passed with, and returns all rows it deems worthy.

The following is an just a small portion of the possible SELECT syntax. Optional clauses are encapsulated in [] brackets.
(Click here for a complete definition)

SELECT colums
FROM table
[WHERE (boolean expressions)]
[ORDER BY colums]
[GROUP BY colums]
[LIMIT start [, count]]

So, to view all our imaginary users from our imaginary user table, we could do this:

/* Show all users */
SELECT *
FROM user;
 
/* Show specific data for a specific user */
SELECT  first_name, 
        last_name
FROM    user
WHERE   user_name = 'Atli';
 
/* Show the last user */
SELECT  *
FROM    user
ORDER BY user_id DESC
LIMIT 1;

And to view User Contact info, we could do this:

/* Show for user_id=1 */
SELECT  contact_label AS 'Lable', 
        contact_value AS 'Value'
FROM    user_contact
WHERE   user_id_fk = 1;
 
/* This is a more complex way
 * to do the same, by joining the
 * two tables */
SELECT  contact_label AS 'Lable', 
        contact_value AS 'Value'
FROM    user_contact
INNER JOIN user
    ON  user.user_id = user_contact.user_id_fk
WHERE   user.user_name = 'Atli';

9. Updating data.
Let's say our friend Johnny got drunk and accidentally tattooed his password on his forehead. Now this would pose somewhat of a security thread, wouldn't you think?
What we need to do, is change his password!
But wait… Does that mean we need to delete our database and start all over?

No! Luckily for Johnny we have the 'UPDATE' command, which we can use to update his password.
(Click here for a complete definition)

UPDATE table
SET
field1 = value1,
field2 = value2,
…
fieldN = valueN
[WHERE (boolean expressions)]
[LIMIT amount]

So to change Johnny's password we would do this:

UPDATE user
SET password = SHA('NewPassword')
WHERE user_name = 'Johnny'
LIMIT 1;

Note that I use the LIMIT clause so I don't accidentally update every single password in the table. (Honestly, those kinds of mistakes have actually killed people!)

10. Deleting data.
Now we enter a dangerous topic; Deleting stuff!
Like say, Johnny. He has just become annoying and we decide he has to go.

Then we will need the 'DELETE' command.
(Complete definition here)

DELETE FROM table
[WHERE (boolean expressions)]
[LIMIT amount]

As this is not a command you want to mess up with, if it is at all possible, use the LIMIT CLAUSE.

For our friend Johnny, the commands would look something like this:

DELETE FROM user_contact
WHERE user_id_fk = 2;
 
DELETE FROM user
WHERE user_id = 2
LIMIT 1;

Now if we want to clean house and just delete everything, there are two options.
We could just use the 'DELETE' command stripped down to its very basics or we could use the specially made 'TRUNCATE' command, which is the preferred way.

/* Using the DELETE command */
DELETE FROM user;
 
/* Using the TRUNCATE command */
TRUNCATE user;

Both leave the table completely empty, but the 'TRUNCATE' command resets all 'Auto_Increment' fields, which the 'DELETE' command does not.

11. Deleting Tables and Databases
Now that our imaginary tables have served their purpose, why don't we just delete them?

Unlike when we delete data, deleting tables and databases is referred to as 'Dropping' them.
Intuitively, the command we need for this is called 'DROP'. There are two versions, one to drop databases (DROP DATABASE) and one to drop tables (DROP TABLE).

DROP DATABASE dbName;
DROP TABLE dbName.tbl_name;

So to finish our database off, we would do this:

DROP DATABASE new_db; 
 
/* or if you want to delete the tables first */
DROP TABLE new_db.user;
DROP TABLE new_db.user_contact;
DROP DATABASE new_db;

12. User creation.
Now that we know how to manipulate our databases, we need to start thinking about who we allow access to it.

So far we have been using the root user to execute our queries. As I mentioned before, this is not a very safe thing to do, especially when we start using these queries in other applications, such as websites.
The root user has access to the entire database system and is capable of doing anything he wants to do to it.

Now what if somebody got a hold of the code for your application and stole your password?
That thief would have complete access to do whatever he wanted to you data.

To prevent this, we create limited users, that are only allowed access to specific databases or tables, and can only execute specific commands.
We could create a user that only has permission to execute SELECT, INSERT and UPDATE commands on a specific database, so that if the user password is compromised, the entire database is not at risk.

There are two commands that can be used to create a user: GRANT and CREATE USER.

12(a) GRANT
This is the preferred method of creating a user. It will work on all MySQL versions.

It allows us to specify which commands the user will be allowed to execute, and it also allows us to specify which database, or even which tables, he has access to.

GRANT <privileges>
ON database.table
TO 'User'@'location'
IDENTIFIED BY 'Password';

The <privileges> part of the query is where you would specify which commands the user has access to. Any command that is not specified can not be used by the user.
(See chapter 12(c) for a list of privileges)

The next part, the ON clause, specifies which databases or tables the user has access to.
You can use the wildcard char (*) to give the user access to a range of tables. Doing '*.*', for example, will give the user access to all tables in all databases (past, present and future).
Doing 'dbName.*' will grant the user privileges on all tables (past, present and future) on the database named 'dbName'.

The TO clause specifies the user name and the location from where the user can connect.
The location can be either a domain name or an IP address, and it can contain wildcard characters (%).
For example, ('John'@'192.168.%.%') would allow a user named 'John' access from any computer on a typical local network. If you specify only the wildcard character ('John'@'%') it will allow the user to connect from anywhere.

12(b) CREATE USER
Creates a user without access to anything. This user will only be able to log in, and nothing else.
Note, this method will not work on MySQL version 4 and earlier.

CREATE USER 'user_name'@'location' IDENTIFIED BY 'Password';

7(c)Privileges
These are the commands we allow the user to perform on the tables we specified.
Any command we do not specify, the user is unable to execute.
This is far shy of a complete set of commands: (You can find a complete list here)

• SELECT – Used to SELECT data (See part 8)
• INSERT – Used to INSERT data (See part 7)
• UPDATE – Used to UPDATE data (See part 9)
• DELETE – Used to DELETE data (See part 10)
• CREATE – Used to create tables, databases and more.
• TRUNCATE – Used to completely clear all data from a table.
• ALL – Used to grant the user ALL privileges. This will give the user 'root-like' privileges on the specified tables.

Example
So, let us create a user that has the ability to SELECT and INSERT data into all tables of every database.
And lets allow him to connect from anywhere.

GRANT SELECT, INSERT
ON *.*
TO 'John'@'%'
IDENTIFIED BY 'MyPassword';

This would be a rather safe user. He would not be able to delete or edit any existing data or tables, but only view it and add new data.

Finally!
Well, to those who made it this far. GOOD JOB!
I hope this has helped you and I wish you all the best.
See you around!

- Atli Þór

[gobblegob]

Greatest MySql Tutorial i've found ,Great job !!!

GobbleGob.

[vhm]

Thank you for the nice and quick tutorial.

[uptown]

Undoubtedly the most precise and helpful tutorial to MySql that I know of.

[havemercy]

This helped me a lot,thanks!

[Markus]

Good work, Atli :D

[itsraghz]

This will be a very precise and need of the hour tutorial for the beginners. Appreciate your efforts Atli :)