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- What is SQL?
- Why MySQL?
- Installing MySQL.
- Using the MySQL command line interface
- Creating databases
- Creating Tables
- Data types
- The CREATE TABLE command
- The Primary Key
- Column parameters
- Insert data
- Select data
- Update data
- Delete data
- Delete (Drop) tables and databases
- User creation
- CREATE USER method
- GRANT method
- 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.
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 has become very popular, mostly because it offers a number of additional, ‘non-standardized’, features that can save developers a lot of time and energy. I will cover a few of them during this article.
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.
Having never tried this on 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, a term Linux users may be familiar with.
As with Linux, 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, and do not even think about ever using it as a user for any external API application, such PHP web pages. The root user haves access to EVERYTHING and you should avoid using it wherever possible.
(See chapter 12 for info on how to create users) 5. Creating databases.
Databases are basically collections of tables.
Each table contains a set number of columns, also referred to as fields, which represent each piece of data we want stored.
The actual data is stored in rows, each row having a value for each of the columns.
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:
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 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 the MySQL engine.
The third one, 'test', should, however, be messed with. That is its purpose.
We will not be using any of those. We are going to create our own database. This is done by a very simple command:
Where ‘newDB’ is the name of our Database.
We are going to want to use our brand new database, so we issue possibly the shortest MySQL Command known to man! The ‘USE’ command lets us manipulate tables within a database, without having to actually reference the database in every query. So:
6. Creating Tables
As with the databases, we can issue a command to see all tables we have access to. This is essentially the same command, only the parameter changes:
Which would output nothing, as we have not yet created any tables. Let’s fix that.
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.
- 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.
- CHAR[n] – This is a string of characters. The [n] represents the number of characters the field will contain. Even if you provide fewer character than the [n] specifies, the field will always be [n] number of chars, and will subsequently occupy the amount of disk space [n] characters will take.
- 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 255. 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.
- LongText – This field is used when you want to store a large amount of text.
- 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.
- 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).
- Blob – This one expects binary data. Ideal to store image data. It comes in four different sizes: ‘TinyBlob’,Blob ‘, ‘MediumBlob’and ‘LargeBlob’.
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 tblName
-
(
-
Col1Name Col1Type <parameters>,
-
Col2Name Col2Type <parameters>,
-
…
-
ColNName ColNType <parameters>,
-
PRIMARY KEY(col1, col2, …, col16)
-
);
-
Here we have the command ‘CREATE TABLE tblName’, 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 must 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.
- Not Null – Indicates that the column does not accept NULL values.
- 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 an such.
- Default value – This specifies a default value for the column. Note that some fields cannot have a default value (binary fields and such).
- 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.
- References tbl(col) – This creates a Foreign Key constraint between the specified column and the new column. This is however 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 username 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, 'UserContact', 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 'UserContact' table.
We can create these two tables using these two commands:
Each command ends with a semi-colon (;) -
CREATE TABLE User
-
(
-
UserID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
-
UserName VARCHAR(255) NOT NULL UNIQUE,
-
Joined DATETIME NOT NULL,
-
Password CHAR(40) NOT NULL,
-
FirstName VARCHAR(50) NOT NULL,
-
LastName VARCHAR(50) NOT NULL,
-
DateOfBirth DATE NotNull,
-
PRIMARY KEY (UserID)
-
);
-
-
CREATE TABLE UserContact
-
(
-
ContactID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
-
UserID_FK BIGINT UNSIGNED NOT NULL REFERENCES User(UserID),
-
ContactLable VARCHAR(255) NOT NULL DEFAULT 'Email',
-
ContactValue VARCHAR(255) NOT NULL,
-
PRIMARY KEY (ContactID)
-
);
-
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 tblName
-
(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
-
(UserName,Password,Joined,FirstName,LastName,DateO fBirth)
-
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 UserContact
-
(UserID, ContactLabel, ContactValue)
-
VALUES
-
(1, 'Email', 'me@myself.i');
-
-
/* Phone for user 'Johnny'*/
-
INSERT INTO UserContact
-
(UserID, ContactLabel, ContactValue)
-
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 such a ridiculously small part of the possible parameters for this command, it’s not even funny! 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 FirstName, LastName
-
FROM user
-
WHERE UserName = 'Atli';
-
-
/* Show the last user */
-
SELECT *
-
FROM User
-
ORDER BY UserID DESC
-
LIMIT 1;
-
And to view User Contact info, we could do this:
-
/* Show for UserID=1 */
-
SELECT ContactLable AS 'Lable', ContactValue AS 'Value'
-
FROM UserContact
-
WHERE UserID = 1;
-
-
/* This is a more complex way
-
to do the same, by joining the
-
two tables */
-
SELECT ContactLable AS 'Lable', ContactValue AS 'Value'
-
FROM UserContact
-
INNER JOIN User
-
ON User.UserID = UserContact.UserID_FK
-
WHERE User.UserName = '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 UserName = '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
-
WHERE UserID = 2
-
LIMIT 1;
-
-
DELETE FROM UserContact
-
WHERE UserID = 2;
-
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’ and is as simple as they come.
So to finish our database off, we would do this:
-
DROP newDB;
-
/* or if you want to delete the tables first */
-
DROP newDB.User;
-
DROP newDB.UserContact;
-
DROP NewDB;
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 'Username'@'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
