Creating and using databases in MySQL

In this post I am going to show simple examples which should help to understand how to create database,table,how to populate,update data in table and how to delete data from table and how to delete database.
I will use MySQL database server which you can download from mysql.com.
If you installed MySQL we can start.

Creating database and assigning permissions to user

Run console. Type:

mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ;or \g.Your MySQL connection id is 37Server version:5.1.37-1ubuntu5.1 (Ubuntu)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database employee;Query OK,1 row affected (0.00 sec)mysql>grant all privileges on employee.* to 'oleg'@'localhost' identified by 'pswd';Query OK,0 rows affected (0.00 sec)mysql>quitBye

Here we are logged in as user root and created a database employee.
Also here we assigned all permissions to employee database to the user oleg@localhost with password pswd.

mysql -u oleg -p;Enter password:Welcome to the MySQL monitor. Commands end with ;or \g.Your MySQL connection id is 42Server version:5.1.37-1ubuntu5.1 (Ubuntu)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>use employee;Database changed

In this snippet of code we are logged to the server as user oleg and changed database to employee.
Note:if you would like to start with selected database in this case employee,you can run this command:

mysql -u oleg -p employee;

It uses employee database by default.

Creating table

Our table is simple.

mysql>create table person  ->(person_id smallint unsigned, ->fname varchar(20), ->lname varchar(20), ->gender enum('M','F'), ->birth_date date, ->city varchar(20), ->country varchar(20), ->constraint pk_person primary key (person_id)  ->);Query OK,0 rows affected (0.15 sec)

If you would like to check if table person exists you can run command describe (in MySQL you can also use desc command):

mysql>desc person;

or

mysql>show tables;

Inserting data

For inserting data we will use insert SQL data statement. But at first we need to turn on the auto-increment feature for our primary key column.
We will use alter table for this modification:

mysql>alter table person modify person_id smallint unsigned auto_increment;

And now we can insert our data:

mysql>insert into person (person_id,fname,lname,gender,birth_date) values (null,'Oleg','Mazurashu','M','1986-04-07');

After this we can view this data by using select:

mysql>select * from person;

This snippet of code will get all data from table. If you would like to get some data you need to specify fields:

mysql>select person_id,fname,lname from person;

or if you need specify row then use where with select

mysql>select fname from person where lname = 'Mazurashu';

Updating data

mysql>update person set city = 'Chernivtsi',country = 'Ukraine' where person_id = 1;

This snippet of code adds city and country data to Oleg‘s data in table.

Deleting data

mysql>delete from person where fname = 'Oleg';

This snippet of code deletes all data related to user with first name Oleg.
Now we need to remove table person

mysql>drop table person;

For deleting database run

mysql>drop database employee;

That’s all. It is very basic tutorial which should help to people who don’t know how to start with SQL and MySQL.

If you’d like to get the latest posts as soon as they’re published,subscribe to our feed!