This tutorial is intended for Ubuntu Power Users and Programmers
I. Theoritical Background
What is Database?
A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching, and replicating the data it holds.
Other kinds of data stores can be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those type of systems.
So now a days we use relational database management systems (RDBMS) to store and manager huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as foreign keys.
A Relational DataBase Management System (RDBMS) is a software that:
Enables you to implement a database with tables, columns, and indexes.
Guarantees the Referential Integrity between rows of various tables.
Updates the indexes automatically.
Interprets an SQL query and combines information from various tables.
RDBMS Terminology:
Before we proceed to explain MySQL database system, lets revise few definitions related to database.
Database: A database is a collection of tables, with related data.
Table: A table is a matrix with data. A table in a database looks like a simple spreadsheet.
Column: One column (data element) contains data of one and the same kind, for example the column postcode.
Row: A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.
Redundancy: Storing data twice, redundantly to make the system faster.
Primary Key: A primary key is unique. A key value can not occur twice in one table. With a key you can find at most one row.
Foreign Key: A foreign key is the linking pin between two tables.
Compound Key: A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
Index: An index in a database resembles an index at the back of a book.
Referential Integrity: Referential Integrity makes sure that a foreign key value always points to an existing row.
MySQL Database:
MySQL is a fast, lightweight, easy-to-use RDBMS being used by many small and big businesses. MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. MySQL is so popular because of many good reasons.
MySQL is released under an open-source license and it's free so you have nothing to pay to use it.
MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
MySQL uses a standard form of the well-known SQL data language.
MySQL works well on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, Microsoft .NET languages, etc.
MySQL works very quickly and works well even with large data sets.
MySQL is very friendly to PHP, the most appreciated language for web development.
MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
MySQL is customizable. The open source GPL license allows programmers to modify the MySQL software to fit their own specific environments.
MySQL Packages:MySQL - The MySQL database server, which manages databases and tables, controls user access, and processes SQL queries.
MySQL-client - MySQL client programs, which makes it possible to connect to, and interact with, the server.
MySQL-devel - Libraries and header files that come in handy when compiling other programs that use MySQL.
MySQL-shared - Shared libraries for the MySQL client
MySQL-bench - Benchmark and performance testing tools for the MySQL database server.
II. The Tutorial
Installation Steps : MySQL server and client packages:
The first thing to do is install the server and client packages. Open the terminal and type the following line.
sudo apt-get install mysql-server mysql-client
When you're done, you have a MySQL database ready to use. However, there’s more to do. Now, you probably don’t want just the MySQL Server. Most likely you have Apache and PHP already installed, and want MySQL to go with that. You need to install support library to make MySQL available to PHP:
sudo apt-get install php5-mysql
Or for Ruby:
sudo apt-get install libmysql-ruby
Post-Installation Steps:
MySQL ships with a blank password for the root MySQL user. You need to set a root password, for starters. MySQL has it’s own user accounts, which are not related to the user accounts on your Linux machine. By default, the root account of the MySQL Server is empty. You need to set it. Please replace ‘mypassword’ with your actual password and myhostname with your actual hostname.
sudo mysqladmin -u root -h localhost password 'mypassword'
sudo mysqladmin -u root -h myhostname password 'mypassword'
Connecting to a MYSQL server using the mysql command
To connect to the MySQL server on the local system, issue the following command:
mysql -u username -p
Enter password:
Where username is the name of a user authorized to use the database (see Create a MySQL user account). The -p option will prompt for the user’s password as shown below the command.
If the MySQL server resides on a remote host, the mysql command, if present on the local system, can be used to connect to the MySQL server accross the network. To connect to a MySQL server on a host named ‘elephant’ use the following command:
mysql -h elephant -u username -p
Typical output screen when succesfully connected:
| genes@genesServer:~$ mysql -u genes -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.1.49-1ubuntu8.1 (Ubuntu) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
It should be rewarded with a mysql> prompt. Now you are connected to the MySQL server and you can execute all the SQL command at mysql> prompt as follows.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec) |
Create Database using mysqladmin:
You would need special privilege to create or to delete a MySQL database. So assuming you have access to root user, you can create any database using mysql mysqladmin binary.
Example:
Here is a simple example to create database called PAYROLL:
genes@genesServer:~$ mysqladmin -u root -p create PAYROLL
Enter password: |
This will create a MySQL database PAYROLL.
Once you get connection with MySQL server, it is required to select a particular database to work with. This is because there may be more than one database available with MySQL Server.
Setting Up a MySQL User Accounts:
For adding a new user to MySQL you just need to add a new entry to user table in database mysql.
Below is an example of adding new user genesacenassr with SELECT, INSERT and UPDATE privileges with the password dragonfly the SQL query is :
genes@genesServer:~$ mysql -u root -p
Enter password:
mysql> use mysql;
Database changed
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'genesacenassr',
PASSWORD('dragonfly'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'genesacenassr';
+-----------+--------------+------------------+
| host | user | password |
+-----------+--------------+------------------+
| localhost | genesacenassr| 6f8c114b58f2ce9e |
+-----------+--------------+------------------+
1 row in set (0.00 sec) |
|
When adding a new user remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.
Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don't use it then you won't be able to connect to mysql using the new user account at least until the server is rebooted.
You can also specify other privileges to a new user by setting the values of following columns in user table to 'Y' when executing the INSERT query or you can update them later using UPDATE query.
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Another way of adding user account is by using GRANT SQL command; Following example will add user genesacenassr with password dragonfly for a particular database called PAYROLL.
genes@genesServer:~$ mysql -u root -p
Enter password:
mysql> use mysql;
Database changed
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON PAYROLL.*
-> TO 'genesacenassr'@'localhost'
-> IDENTIFIED BY 'dragonfly'; |
This will also create an entry in mysql database table called user.
NOTE: MySQL does not terminate a command until you give a semi colon (;) at the end of SQL command.
Selecting MySQL Database from Command Prompt:
This is very simple to select a particular database from mysql> prompt. You can use SQL command use to select a particular database.
Example:
Here is an example to select database called PAYROLL:
genes@genesServer:~$ mysql -u root -p
Enter password:
mysql> use PAYROLL;
Database changed
mysql> |
Now you have selected PAYROLL database and all the subsequent operations will be performed on PAYROLL database.Create MySQL Tables
The table creation command requires:
Syntax:
Here is generic SQL syntax to create a MySQL table:
CREATE TABLE table_name (column_name column_type); |
Now we will create following table in PAYROLL database.
employee_tbl(
empid INT NOT NULL AUTO_INCREMENT,
empname VARCHAR(100) NOT NULL,
empaddress VARCHAR(100) NOT NULL,
employment_date DATE,
PRIMARY KEY ( empid )
); |
Here few items need explanation:
Field Attribute NOT NULL is being used because we do not want this field to be NULL. SO if user will try to create a record with NULL value then MySQL will raise an error.
Field Attribute AUTO_INCREMENT tells to MySQL to go ahead and add the next available number to the id field.
Keyword PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by comma to define a primary key.
Creating Tables from Command Prompt:
This is easy to create a MySQL table from mysql> prompt. You will use SQL command CREATE TABLE to create a table.
Example:
Here is an example which creates employee_tbl:
genes@genesServer:~$ mysql -u root -p
Enter password:
mysql> use PAYROLL;
Database changed
mysql> CREATE TABLE employee_tbl(
-> empid INT NOT NULL AUTO_INCREMENT,
-> empname VARCHAR(100) NOT NULL,
-> empaddress VARCHAR(100) NOT NULL,
-> employment_date DATE,
-> PRIMARY KEY ( empid )
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> |
