Ade Malsasa Akbar contact
Senior author, Open Source enthusiast.
Wednesday, October 28, 2015 at 22:42

We find many MySQL tutorials worldwide. But this tutorial is intended for beginners in Ubuntu. This covers installation of MySQL itself and then some basic MySQL queries such as creating and updating table. This works with Kubuntu, Xubuntu, and another official flavors of Ubuntu because they use the same repository. We hope this helps any programmer comes into Ubuntu to deal with MySQL.


Installing MySQL

 

To install MySQL in Ubuntu, connect to the internet and type this command on Terminal:

sudo apt-get install mysql-server

Login into MySQL Shell

 

Open your Terminal (Ctrl+Alt+T) and type command below. This command will invoke mysql binary program with root user. You will be prompted to enter password afterwards.

/usr/bin/mysql -u root

You will always see prompt shell like this while working with MySQL:

mysql>

Some MySQL Rules

 

  • MySQL is semi-colon ended language. You must end every a complete line with a semi-colon ( ; ) character like in C++.
  • MySQL is case insensitive, on the contrary of C++.
  • While the MySQL keywords are case insensitive, but in Linux, database name or table name (created by user) will be case sensitive. It is important.
  • Drop term in MySQL is basically same with delete.
  • Record term in MySQL is row (table).
  • Field term in MySQL is column (table).

Creating A Database

 

CREATE DATABASE mydatabase;

These command will create a database with the name mydatabase.

Showing All Databases

 

SHOW DATABASES;

This command will show all databases you have.

Enter A Database

USE mydatabase;

To see tables inside a database, you must enter the database first. MySQL implements 'USE' statement to do that.

Creating Table

 

CREATE TABLE bookstore ( id int unsigned NOT NULL AUTO_INCREMENT, title varchar(255) NOT NULL, author varchar(255), publisher varchar(255) NOT NULL, year varchar(4) NOT NULL, PRIMARY KEY (id) );

  • This query will create a table with name bookstore, then
  • create 5 columns by name id, title, author, publisher, and year,
  • the first column will automatically numbered because it is integer (int) and has command AUTO_INCREMENT,
  • varchar(255) is a limiter so every column will be limited into 255 characters.

See Table Properties

DESCRIBE bookstore;

Output from this command will be:

mysql> DESCRIBE bookstore;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title     | varchar(255)     | NO   |     | NULL    |                |
| author    | varchar(255)     | NO   |     | NULL    |                |
| publisher | varchar(255)     | NO   |     | NULL    |                |
| year      | varchar(255)     | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 

See Table Contents

SELECT * FROM bookstore;

This query (SELECT FROM)  will show complete form of a table. in this case, bookstore table. Output from this query will be like this:

mysql> SELECT * FROM bookstore;
+----+---------+-------------------+---------------+------+
| id | title   | author            | publisher     | year |
+----+---------+-------------------+---------------+------+
|  1 | MY BOOK | SOMEONE AUTHORING | ANY PUBLISHER | 2000 |
+----+---------+-------------------+---------------+------+
1 row in set (0.00 sec)

 

Insert Data Into Table

INSERT INTO bookstore ( title, author, publisher, year ) VALUES ( "MY BOOK", "SOMEONE AUTHORING", "ANY PUBLISHER", "2000" );

Output will be like this:

mysql> SELECT * FROM bookstore;
+----+------------+-------------------+---------------+------+
| id | title      | author            | publisher     | year |
+----+------------+-------------------+---------------+------+
|  1 | MY BOOK    | SOMEONE AUTHORING | ANY PUBLISHER | 2000 |
|  2 | THEIR BOOK | THEY              | ANY PUBLISHER | 2001 |
+----+------------+-------------------+---------------+------+
2 rows in set (0.00 sec)

 

Edit A Table

 

UPDATE `bookstore` SET `year` = "1987" WHERE `bookstore`.`title` = "MY BOOK";

This query will edit data in the table. It will UPDATE bookstore table, by SET value 1987 into a position. The position is column year and row MY BOOK. Important: notice the apostrophe and quote characters used above. Output will be like this:

Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM bookstore;
+----+------------+-------------------+---------------+------+
| id | title      | author            | publisher     | year |
+----+------------+-------------------+---------------+------+
|  1 | MY BOOK    | SOMEONE AUTHORING | ANY PUBLISHER | 1987 |
|  2 | THEIR BOOK | THEY              | ANY PUBLISHER | 2001 |
+----+------------+-------------------+---------------+------+
2 rows in set (0.00 sec)

 

Delete A Table


DROP TABLE bookstore;

DROP TABLE query will delete a table specified. In this case, it will delete bookstore. You will not be able to SELECT a table which has deleted. Notice that DROP TABLE will delete all data inside your table. Output will be like this:

mysql> DROP TABLE bookstore;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM bookstore;
ERROR 1146 (42S02): Table 'mydatabase.bookstore' doesn't exist

 

Delete A Database


DROP DATABASE mydatabase;

This query will delete a database. In this case, mydatabase. Notice that this will also delete all tables and all data inside. Output will be like this:

mysql> DROP DATABASE mydatabase;
Query OK, 1 row affected (0.11 sec)

mysql> USE mydatabase;
ERROR 1049 (42000): Unknown database 'mydatabase'

 

Summary


To help anyone learn MySQL quickly, we sum up all queries above here. Of course, these don't cover all you need with MySQL. It is just a beginning.
  1. CREATE DATABASE mydatabase;
  2. SHOW DATABASES;
  3. USE mydatabase;
  4. CREATE TABLE bookstore ( id int unsigned NOT NULL AUTO_INCREMENT, title varchar(255) NOT NULL, author varchar(255), publisher varchar(255) NOT NULL, year varchar(4) NOT NULL, PRIMARY KEY (id) );
  5. DESCRIBE bookstore;
  6. SELECT * FROM bookstore;
  7. INSERT INTO bookstore ( title, author, publisher, year ) VALUES ( "MY BOOK", "SOMEONE AUTHORING", "ANY PUBLISHER", "2000" );
  8. UPDATE `bookstore` SET `year` = "1987" WHERE `bookstore`.`title` = "MY BOOK";
  9. DROP TABLE bookstore;
  10. DROP DATABASE mydatabase;
References