Basic MySQL

A short tutorial on installation, and basic MySQL commands to get you started with Database Management.

Blue Light Tech

MySQL is an open-source database management software which offers assistance in retrieving, storing and organizing data.

  1. How to Install MySQL on Ubuntu and CentOS
  2. How to Access the MySQL shell
  3. How to Create and Delete a MySQL Database
  4. How to Access a MySQL Database
  5. How to Create a MySQL Table
  6. How to Add Information to a MySQL Table
  7. How to Update Information in the Table
  8. How to Add and Delete a Column
  9. How to Delete a Row

How to Install MySQL on Ubuntu and CentOS

Download MySQL if you have not installed it on your server as explained below;

  • For Ubuntu –
    sudo apt-get install mysql-server
  • For Centos –
    sudo yum install mysql-server 
    /etc/init.d/mysqld start

How to Access the MySQL shell

Type the command below into the terminal to access MySQL shell after installation.

mysql -u  root -p

Once you’ve entered your password, you can start building your database for the MySQL. You also need to remember that:

  • Though the MySQL command line is not case sensitive, best practice tends to be to create a clear distinction between commands and names, therefore the text, databases, usernames or tables should be in lowercase while MySQL commands appear in uppercase. This is purely a suggestion, and not required.
  • All MySQL commands must end with a semicolon to be run.

How to Create and Delete a MySQL Database

Each database in MySQL consists of tables which have specific kind of data and these databases are the form in which it organizes its information. To find out what databases exist you just need to type:

SHOW DATABASES;

which returns:

Database
information_schema
mysql
performance_schema

3 rows in set (0.01 sec)

For you to create a database such as test_db:

CREATE DATABASE test_db;
SHOW DATABASES;

which returns:

Database
information_schema
test_db
mysql
performance_schema

4 rows in set (0.00 sec)

For you to delete a MySQL database you use the phrase DROP. An example in this case would be;

DROP DATABASE test_db;

How to Access a MySQL Database

When a new database is created, the first step will be to access the database in order to add tables etc;

USE test_db;

To check the table available in the database simply type;

SHOW tables;

The “Empty set” message appears in this case since the database is new.

How to Create a MySQL Table

If you are planning a friends get together for instance details of the event can be as follows on a new MySQL table;

CREATE TABLE customer_details (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
email VARCHAR(320),
phone VARCHAR(15),
signup_date DATE);

How to Add Information to a MySQL Table

More details of the event can be added on each row as follows;

INSERT INTO `customer_details` (`id`,`name`,`email`,`phone`,`signup_date`) VALUES (NULL, 'Steve', 'Steve@email.com','0123456789', '2012-04-11');

How to Update Information in the Table

If Steve then needs to change his phone number;

UPDATE `customer_details`
SET
`phone` = '01783637323'
WHERE `customer_details`.`name` ='Steve';

How to Add and Delete a Column

Add emails as shown below;

ALTER TABLE customer_details ADD address VARCHAR(40);

You can also delete a column as shown below;

ALTER TABLE customer_details DROP address;

How to Delete a Row

Use the command below for this;

DELETE from [table name] where [column name]=[field text];

If Eve needs to be removed from the table;

DELETE from customer_details  where name='Eve';

Query OK, 1 row affected (0.00 sec)

SELECT * FROM customer_details;
id name email phone signup_date
1 Steve steve@email.com 0123456789 2012-04-11 
3 Tom tom@email.com 0453541224         2012-04-18 
4 Jane Jane.doe@email.com 0523558652         2012-04-10 

3 rows in set (0.00 sec)


0 Comments