MySQL is the most popular open source database. If you are planning to start learning DBMS, start with MySQL. Here is quick-start guide to MySQL database administration.

What's a database anyway?

Wikipedia's entry under database says,
A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model.
Simply put, database is a set of tables which contain data. The tables in turn are made up of fields and rows where rows contain the data and fields are the identifiers of the data.

Installation

You can get the latest version of MySQL at the official website. Installation is pretty simple, if you are on Windows. If you are trying to integrate MySQL with PHP, you have to make a simple copy-paste. I have tutorial on installing PHP in Windows. For Debian based Linux distributions, this article may come handy for you.

Basic Syntax

MySQL (or SQL in general) commands are generally UPPERCASE with a semicolon at the end. The names of the database, tables, rows or fields need not be uppercase.

Create a database

Creating a database is as simple as this:
CREATE DATABASE name_of_the_database;
Once you have created a database, you must use it. To select the database for use, use the following:
USE name_of_the_database;

Create a table

Creating a table is a little different business and you need to plan a little about the types of data which are going to be contained in the fields. For that you need to know the data types. Here is a quick overview of the most commonly used data types:
VARCHAR — "A variable length string." It can contain numbers letters signs etc.
INT — Integers. Its length can be upto 11 digits.
TEXT — Strings. Max length 216-1
TIMESTAMP — A timestamp in 'YYYY-MM-DD HH:MM:SS' format.
Now, back to the business, while creating a table you must remember the following syntax:
CREATE TABLE name_of_table (
field1_name field1_definition,
field2_name field2_definition,
field3_name field3_definition,
...);
For example, to create the above table (in the image), I would use:
CREATE TABLE users (
userid INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
username VARCHAR(255),
password VARCHAR(32)
);
Now, you ask me, what is PRIMARY KEY, AUTO_INCREMENT, NOT NULL, VARCHAR(255) and so on.
When we use PRIMARY KEY we mean that it should contain a unique index by which we can identify each row of information. That is to say, in the above example, there won't be two rows having the same userid.
AUTO_INCREMENT would increase the next row by 1.
NOT NULL, as it says, would force userid to have an integral value.
About VARCHAR(255) and VARCHAR(32), these are all varchars with the values in the parentheses as their lengths. Got it?

Insert data into the table

Once you are done preparing the table, it's time you insert some values to the table. The basic syntax is:
INSERT INTO table_name (field1, field2, field3, ...) VALUES (val1, val2, val3...);
where val1 is the value of field1, val2 is value of field2 and so on. To insert a value in the above example, we would use:
INSERT INTO users (userid, username, password) VALUES(1, 'new_user', '82ff8f59bdb4cf94c95559a592720464');
Since we have AUTO_INCREMENT on the userid field we need not worry about userid column. Thus if we leave out the userid field, mysql will automatically increase the value and put the value in the table. i.e. the following syntax would also work:
INSERT INTO users (username, password) VALUES('new_user2', 'gh548f59bdb4cf94c95559a59284754');
This comes handy as we may not know what would be the next value of the index field.

Get data from the table

So, you have a database, a table and some value inside the table. Now, fetch some data from the table. To show all the values of the table, use the following command:
SELECT * FROM table_name;
The asterisk (*) selects all the fields of table. Let's say you need the usernames and the passwords (of the table in the example) only, then you should use:
SELECT username, password FROM users;
Or say, you want to find out the password of the user with username 'someuser', you need:
SELECT password FROM users WHERE username = 'someuser';
The WHERE clause in the above statement sets the condition that not all but only the row that has the username field with someuser will be selected. SELECT is probably the most powerful SQL statement. It can be used wisely to do complicated tasks quite easily.

Delete data

So, you are not happy with a user and wanna ban him? Just use the following:
DELETE FROM users WHERE username = 'baduser';
This will delete the row with the username 'baduser'. Pretty easy, huh?

Update data

To update data on a specific row, the following is used:
UPDATE table_name SET field1 = 'some_data' WHERE field2 = 'some_data';
In the above example we would use:
UPDATE users SET username = 'new_username' WHERE username = 'old_username';

Ok, is that all?

No, it's not. There are a lot of things and lot of possibilities. This is just an introduction to using MySQL. Do read the online reference of MySQL, it's got all your answers. :)

Labels: ,

blog comments powered by Disqus