About
MySQL is an open source database management software which allows users to manage the databases on their servers. Simple command lines with the help of MySQL can serve you very effectively in storing, organizing and retrieving data on your server.
In this tutorial, let's discuss some major operations that you can perform while managing your database.
Installation
If you don't have MySQL already installed on your server, enter the following command based on the OS of your server:
For CentOs
sudo yum install mysql-server
/etc/init.d/mysqld start
For Ubuntu
sudo apt-get install mysql-server
Accessing MySQL Shell
MySQL shell is the interface platform for performing all the database management actions. Once you have installed MySQL, you can access it by following the below-mentioned command:
mysql -u root -p
After entering the root MySQL password into the prompt (not to be confused with the root password of your server), you will be able to start building your MySQL database.
- [ht_message mstyle="alert" title="Note" " show_icon="" id="" class="" style="" ]
- All MySQL commands ends with a semicolon (;). MySQL will not read the execution if the command does not end with a semicolon.
- Though MySQL shell is not case sensitive, for the sake of distinguishment MySQL commands are usually written in uppercase and databases, tables, usernames, or text are in lowercas
[/ht_message]
Create/ Delete a Database
MySQL saves the information fed, in the form of databases with tables holding specific data.
For checking the types of databases available:
SHOW DATABASES;
For the above command, the terminal should respond with a prompt similar to this
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
For creating a new database, enter the following command:
CREATE DATABASE database name;
Replace 'database name' with the name you want to give to the new database in the above command. (eg: Function)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Function |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Delete a database
You can use the Drop command to delete a database. (eg: Function)
DROP DATABASE database Function;
Replace 'database Function' in the above command with the name of the database you want to delete.
Accessing a MySQL database
Now that you have a new database created, you can start feeding the data, but first, you need to create a table within the larger database.
Let's access the database you want to use (eg: function)
USE function;
Replace 'Function' in the above command with the name of the database you want to access.
In the same way that you could check the available databases, you can also see an overview of the tables that the database contains.
SHOW tables;
Since this one is a new database created, MySQL will respond as “Empty set” as there won't be any data to show.
Creating a MySQL Table
Now let's learn some basic table creating procedures. In this example, lets create a new table "Wedding" for a wedding plan where we can use MySQL to track the details.
Let's create a new table named Wedding, under the database "function".
CREATE TABLE Wedding (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(6),
confirmed CHAR(1),
signup_date DATE);
The above command will accomplish the following objectives:
- It has created a table called "Wedding" within the directory, "function".
- 5 columns are created with the terms name, food, confirmed, and signup date in the table_id
- "INT NOT NULL PRIMARY KEY AUTO_INCREMENT" in the table Id column will number the rows automatically
- VARCHAR (20) limits the number of characters that can be used for the name column to 20 characters
- The food column represents the food preference of the attendee. VARCHAR(6) limits the number of characters here to 6.(eg: Veg or Nonveg)
- The confirmed column can be responded with an acknowledgement as, either Y for yes or N for No.
- The “date” column will show when they signed up for the event. MySQL requires that dates be written as yyyy-mm-dd.
Now, let's have a look at the table using the "SHOW TABLE" command:
mysql> SHOW TABLES;
+--------------------+
| Tables_in_function |
+--------------------+
| Wedding |
+--------------------+
1 row in set (0.01 sec)
You can check the table format using the DESCRIBE command:
To check the table format of the table Wedding:
mysql>DESCRIBE Wedding;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| food | varchar(30) | YES | | NULL | |
| confirmed | char(1) | YES | | NULL | |
| signup_date | date | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
[ht_message mstyle="alert" title="Note" " show_icon="" id="" class="" style="" ]Though MySQL commands are not case sensitive, the names proposed to the tables and databases are case sensitive. (eg: Wedding, WEDDING and wedding are not the same)[/ht_message]
Adding information to a MySQL table
Now we have an organized table for the wedding, and its time for the relevant information to be entered in the table Wedding.
For filling information in the row with the preferred data:
INSERT INTO `Wedding` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Ami", "Nonveg","Y", '2012-04-16');
Let's add more people with different preferences:
INSERT INTO `Wedding` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Amir", "Nonveg","Y", '2012-03-16');
INSERT INTO `Wedding` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Amit", "Veg","Y", '2012-04-16');
INSERT INTO `Wedding` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Anas", "Nonveg","Y", '2012-09-16');
After the above entries are made, the table Wedding will look like this:
mysql> SELECT * FROM Wedding;
+----+------+--------+-----------+-------------+
| id | name | food | confirmed | signup_date |
+----+------+--------+-----------+-------------+
| 1 | Ami | Nonveg | Y | 2016-04-11 |
| 2 | Amir | Nonveg | N | 2016-04-14 |
| 3 | Amit | veg | Y | 2016-04-18 |
| 4 | Anas | Nonveg | Y | 2016-04-10 |
+----+------+--------+-----------+-------------+
4 rows in set (0.00 sec)
Updating information in a table
You can address any change you want to make in the table by simple command lines.
eg: Amir needs to change his food preference from Nonveg to veg:
UPDATE `Wedding`
SET
`food` = 'Veg'
WHERE `Wedding`.`name` ='Amir';
The above syntax can be used to edit, modify or add information to the table.
Adding/Deleting a Column
You can Add/delete columns present in the table at any given point.
(eg: Add a column to the table Wedding named "Contact")
ALTER TABLE Wedding ADD contact VARCHAR(10);
This command will add a new column called "contact" at the end of the table Wedding by default, and the VARCHAR command limits it to 10 characters.
You can also mention the spot you want to add column in the table.
(eg: Add the column "contact" after "name" in the table wedding)
ALTER TABLE Wedding ADD contact VARCHAR(10) AFTER name;
The above command will place the contact column next to the column name.
To delete a column
(eg: Delete the column contact in the table Wedding)
ALTER TABLE Wedding DROP contact;
The above command will delete the column contact from the table wedding.
Deleting a Row
Like columns, you can always delete the rows as well:
DELETE from [table name] where [column name]=[field text];
(eg: Delete the column with the details of "Anas", as he won't be able to make it)
mysql> DELETE from Wedding where name='Anas';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM potluck;
+----+------+--------+-----------+-------------+
| id | name | food | confirmed | signup_date |
+----+------+--------+-----------+-------------+
| 1 | Ami | Nonveg | Y | 2016-04-11 |
| 2 | Amir | Nonveg | N | 2016-04-14 |
| 3 | Amit | veg | Y | 2016-04-18 |
+----+------+--------+-----------+-------------+
3 rows in set (0.00 sec)