Intro to MySQL

About MySQL

MySQL, a free and open-source database, is one of the most popular databases in the world.

This tutorial will discuss a basic MySQL installation on a Linux virtual server, and some of the basic MySQL commands.
Full documentation can be found on the MySQL.com website here

 

1)  Installing MySQL

cPanel/WHM will come with MySQL pre-installed.  However if you are not using cPanel you will need to install MySQL from the command line.

For users who are unfamiliar with the Linux command line we highly recommend using cPanel/WHM, which will install Apache, PHP and MySQL by default, and allow you to manage your databases, tables and mysql users from a webpage with ease.

For advanced users:

SSH In to your server as root or a user with sudo capabilities.

Then run the appropriate command below for your version of Linux.

CentOS Linux

yum install mysql-server

or 

sudo yum install mysql-server

 

Debian/Ubuntu Linux

apt-get install mysql-server

or

sudo apt-get install mysql-server

 

2) Log into the MySQL Command Line

mysql -u root -p

mysql is the command for accessing the mysql shell.
The -u flag allows you to specify which mysql user you are logging in as
And, the -p flag tells mysql you are going to provide a password (even if it's a blank password).

If this is your first time logging in after you installed MySQL the root user's password should be blank, simply hit the enter key at the password prompt.


3) Change the root user's password

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new password');


new password will be the new password you want for the user.

4) Basic MySQL Commands

The commands below are a brief list that will get you started with creating a database, adding tables to your database, and inserting rows.
For a more comprehensive list of commands please visit the main MySQL documentation at http://dev.mysql.com/doc/

Creating a MySQL database

CREATE DATABASE test_db;


Selecting a MySQL database to use

USE test_db;


Creating a new table

For our example we are going to create a table for tracking books we've lent out to friends.

CREATE TABLE books (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(32), lent_to VARCHAR(32), lent_on INT);

Let's break that command down into smaller pieces:

CREATE TABLE books

CREATE TABLE is the command for creating the table, and books is the name of the table.

(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT

  • Add a column named "id" to this table, with data type of INT (integer)
  • Its value cannot be NULL
  • It is this table's PRIMARY KEY column
  • It automatically increments every time a new row is inserted into the table.
A row's id will always be unique, and you will use it often for interacting directly with a specific row.

title VARCHAR(32), lent_to VARCHAR(32), lent_on INT

The remaining columns for the table are;

  • title of the book, which has a data type of VARCHAR, or Variable Character. and a character length of 32
  • lent_to, also a VARCHAR with a length of 32
  • lent_on with a type of INT -- for storing unix timestamps.


Inserting a row into your MySQL table

INSERT INTO books (title, lent_to, lent_on) VALUES ('Learning MySQL','Monty Widenius','1412886424');

This will insert a new row of data into the books table.
You will notice that we did not specify an id for the row. This is because we set that column up to be our auto_incrementing primary key -- so MySQL will automatically populate this column with the next integer.
If we were to select this row from our table later on we would see that we lent the book Learning MySQL to Monty Widenius, on October 9th, 2014 at 8:27PM UTC


Suggested Reading

Now that you have some basic knowledge of how to use the MySQL command line you will want to read up on some more common commands. We suggest starting with the list below;


Was this article helpful?

mood_bad Dislike 0
mood Like 0
visibility Views: 6124