Page 1 of 1

MySQL Basics Everything you need to know to start using MySQL databases.

#1 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Posted 19 June 2010 - 07:57 AM

*
POPULAR

Introduction
This article describes all that you need to know to start working with MySQL database. Everything from the basic info on SQL, how to set MySQL up and how to use the basic commands is covered. Advanced topics, such as JOINS or proper table design, are not covered in this article.

This does NOT require any knowledge of computer programming, although it may help.

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?
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. I will cover the basic commands needed to operate a MySQL server in the following chapters.

Even though there are a number of graphical user interfaces avaiable for most SQL servers, the server itself accepts only commands written in plain text. It is important, even though you will probably end up using a GUI tool, to be aware of how the commands are used in their plain text form.

The following example is one of the most used SQL queries of all times. It simply commands the system to return every column of every row in a given table.
SELECT * FROM tbl_name

(Note that in 99% of cases, you will want to add a WHERE clause to that, to specify the data you need. This will be explained further in chapter 8.)

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 previous exapmle, 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 on 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.
  • Most Linux distributions either come with, or allow easy access to MySQL. Users of popular distros such as Debian, Ubuntu, Fedora and so on, can often find MySQL in their software repositories. For Debian based distros, you can often do $apt-get install mysql. On RedHat based distros, the equivalent would be $yum install mysql.
  • If you are running Mac OS X server, MySQL is already installed. Just open Applications/Server/MySQL Manager. For the client version, see 2.12. Installing MySQL on Mac OS X for instructions.


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

4. Using the MySQL command line interface.
MySQL comes standard with a basic command line interface, which allows you to execute your queries on the server. There are many GUI tools available, like MySQL's own MySQL Worbench, or the popular PHP tool phpMyAdmin. But for the purposes of this tutorial, I will be sticking to the standard CLI tool. In many cases, servers do not allow for GUI access, so you may not be able to use anything else.

On Windows, this would be located under 'MySQL' in the All Programs menu.
On Linux and Mac, you should be able to simply type 'mysql' in the terminal.

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. (Some Linux distributions do not require you to enter this password.)

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', is created for your benefit only. It's just there for you to play with.

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.1 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.2 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.3 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.4 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, by 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 a group of users.
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 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 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.
To have MySQL return the data in the tables back to us, we use the SELECT command. 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 users from our 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.
To edit the data already inside the database, we use the UPDATE command.

It's important to note that the UPDATE command, as well as the DELETE command (see next chapted), are “greedy” just like the SELECT command. By that I mean, they will use the widest possible range of data based on the filters we give it. So if you do not properly specify which data you want to update, the update command will update more than you want it to. Without a WHERE clause, the UPDATE command will update all the data in the table.

This is how we use the UPDATE command. It is in many ways similar to the SELECT command, as you can see:
(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 in our User table 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.
The DELETE command is fairly self-explanitory. It deletes data. As I noted in the chapter about the UPDATE command, you need to be very careful about specifying what you want deleted. These commands will delete as much as they can. If you don't add a proper WHERE clause, you may end up losing data you did not want to be deleted.

(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.

To delete Johnny from the Users table, we would to 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
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.1 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.

(Clicke here for a complete defenition.)
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.3 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.2 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.

(Clicke here for a complete defenition.)
CREATE USER 'user_name'@'location' IDENTIFIED BY 'Password';


12.3 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.

Epilogue
That's it for the basic use of MySQL!

Those of you new to database design might want to follow this up by reading up on the actual design of databases. An important concept in relational database design is Normalization. It describes how to properly design your tables, to maximize flexibility and minimaize the chance of data corruption. It's essential to at least be aware of this concept if when designing in a RDBMS system, such as MySQL.

But I will leave you with that, for now.

All the best,
- Atli

Is This A Good Question/Topic? 15
  • +

Replies To: MySQL Basics

#2 vigoloubev  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 16
  • Joined: 17-February 11

Posted 22 February 2011 - 03:10 AM

Short and helpful :^:
Was This Post Helpful? 0
  • +
  • -

#3 antifreez  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 27-March 11

Posted 28 March 2011 - 05:48 AM

Nice tut for going in MySQL tnx man. :)
Was This Post Helpful? 0
  • +
  • -

#4 bjantho  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 25-June 11

Posted 06 August 2011 - 05:40 PM

Very nice, I learned a lot.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1