MySQL Tutorial: Install, Create DB and Table, Insert and Select Records

by Ramesh Natarajan on October 10, 2011

This MySQL jumpstart guide will get you running quickly on the basics. This explains how to install MySQL, create a sample database, create a table, insert records into the table, and select records from the table.

1. Install and Configure MySQL

Go to the MySQL download page, and select your appropriate platform to download the latest version of MySQL community server.

2. Create MySQL Database

Once MySQL is installed, connect to it using MySQL root user and create the database as shown below.

# mysql -u root -p
Enter password:
mysql>

After connecting as MySQL root user, execute the following command from the “mysql> ” prompt to create a database.

The following command will create the database called “thegeekstuff”.

mysql> create database thegeekstuff

Use “show databases” command to verify that the database was created successfully.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| thegeekstuff       |
+--------------------+
3 rows in set (0.00 sec)

3. Create MySQL Table

Connect to the newly created database using “use” command before you can perform any operation in the database.

To connect to the database, do the following.

mysql> use thegeekstuff;
Database changed

The following example creates a employee table.

create table employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
dept varchar(10),
salary int(10)
);

When you copy-paste the above create table command in the “mysql> ” prompt, it will display the continuation prompt “->” starting from the 2nd line, which indicates that the command is still not over. The end of a SQL command is identified by a semi-colon.

mysql> create table employee (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(20),
    -> dept varchar(10),
    -> salary int(10)
    -> );
Query OK, 0 rows affected (0.00 sec)

You can also use any one of the following data types.

  • dcode SMALLINT
  • mcode MEDIUMIN
  • project_start DATE
  • loggedon TIME

Do the following to view all the tables available in the database.

mysql> show tables;
+------------------------+
| Tables_in_thegeekstuff |
+------------------------+
| employee               |
+------------------------+
1 row in set (0.00 sec)

To view the table description, do the following.

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| dept   | varchar(10) | YES  |     | NULL    |                |
| salary | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

4. Insert Records into a Table

Use the following sample insert commands to insert some records to the employee table. While inserting values to all the columns in the table, you don’t need to specify the column name. Just specify the values in the same sequence as the column names.

insert into employee values(100,'Thomas','Sales',5000);
insert into employee values(200,'Jason','Technology',5500);
insert into employee values(300,'Mayla','Technology',7000);
insert into employee values(400,'Nisha','Marketing',9500);
insert into employee values(500,'Randy','Technology',6000);

To insert values only to specific columns, you should specify the column names as shown below.

mysql> insert into employee(name,dept) values('Ritu', 'Accounting');
Query OK, 1 row affected (0.01 sec)

Note: You can also upload data from a text file to MySQL database using mysqlimport command.

5. Query Records from a Table

To view all the records from a table, use the following select statement.

mysql> select * from employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
| 501 | Ritu   | Accounting |   NULL |
+-----+--------+------------+--------+
6 rows in set (0.00 sec)

To view only a specific columns from a table, specify the column names in the select command.

mysql>  select name, dept from employee;

The following select statement has a where condition which displays the employee records who belong to Technology department and getting a salary >= 7000.

mysql> select * from employee where dept = 'Technology' and salary >= 6000;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 300 | Mayla  | Technology |   7000 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+
2 rows in set (0.00 sec)

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 26 comments… read them below or add one }

1 shanil October 10, 2011 at 6:48 pm

great tutorial…many thanks

2 ajmal October 11, 2011 at 6:32 am

mysql> create database thegeekstuff should be followed by ;
mysql> create database thegeekstuff;

3 Gaurav Chatterjee October 11, 2011 at 3:16 pm

Your articles are always the best place to refer for anyone who’s beginning.
Thanks a lot

4 chamo1075 October 11, 2011 at 7:12 pm

nice tutorial!

@ajmal: good eye, i missed that one.

5 DanielH October 30, 2011 at 10:47 am

All query commands should be followed by a “;”. But thanks allot for this great tutorial

6 ayala February 16, 2012 at 4:11 am

Realy nice tuto.Thx Ramesh.

7 nitesh singh October 19, 2012 at 9:31 am

really good ,,,,,,,,,,,,,nice and superb,,,,,,,,,,,,
thanx

8 deepak December 18, 2012 at 10:12 am

I’m a newbie to sql, and found your article just what I needed. Many thanks!!

9 shimmar February 16, 2013 at 3:09 pm

thanks …. exactly what i needed………..

10 shanila.j February 19, 2013 at 3:11 am

thank u……………………

11 Desi in DC April 16, 2013 at 7:56 pm

Thank you !

12 Muhammed shafi May 30, 2013 at 7:30 pm

thanks brother,

Very useful for the poeple who don’t know about php.

As a beginner i need u r help always.,.
thank u………………..

13 algates July 23, 2013 at 1:25 pm

good article .crystal clear

14 uche August 27, 2013 at 3:23 am

Thanks so much. I m a beginner. Pls I want to save the tables and also link them up.

15 check September 4, 2013 at 5:02 am

Thank you very much

16 Alireza September 12, 2013 at 2:01 am

Thank you very much. Very useful .

17 Rahul Patadiya October 8, 2013 at 12:23 pm

Very Useful sir,

Also Add Backup and Restore Command:
backup: # mysqldump -u root -p[root_password] [database_name] > filename.sql

restore:# mysql -u root -p[root_password] [database_name] < filename.sql

18 madhawa October 17, 2013 at 8:57 am

it’s really really useful for me.god bless you for teaching us without salary

19 Dhiraj December 25, 2013 at 11:41 pm

thanx for guiding me..

20 Satinder January 3, 2014 at 4:10 pm

I need help with creating/maintaining a MY SQL Database in AWS

21 Tiger January 14, 2014 at 2:44 pm

You’re the best! Keep up the good work!

22 Chris January 15, 2014 at 6:46 pm

Really nice tutorial for a beginner, helped me out allot .

23 biagio February 3, 2014 at 9:52 am

good tutorial, useful for me

24 a February 15, 2014 at 4:10 am

good….

25 wasiq March 11, 2014 at 12:33 am

good explanation, thxs

26 Kumar Nayan March 30, 2014 at 11:33 pm

Really good tutorial…..

Many Thanks……

Leave a Comment

Previous post:

Next post: