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.
- CREATE DATABASE mydatabase;
- SHOW DATABASES;
- USE mydatabase;
- 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) );
- DESCRIBE bookstore;
- SELECT * FROM bookstore;
- INSERT INTO bookstore ( title, author, publisher, year ) VALUES ( "MY BOOK", "SOMEONE AUTHORING", "ANY PUBLISHER", "2000" );
- UPDATE `bookstore` SET `year` = "1987" WHERE `bookstore`.`title` = "MY BOOK";
- DROP TABLE bookstore;
- DROP DATABASE mydatabase;