Basic MySQL Commands On Linux MySQL

MySQL

MySQL  is the most popular open source database management system, it is widely used in many development environments such as  Windows ,  Linux , Mac OS X, Unix, FreeBSD, NetBSD, Novell NetWare, SGI Irix, Solaris , SunOS, ..

This article will guide you through some basic mysql commands in Linux environment.

Start / Stop / Restart MySQL

To start MySQL, you execute the following command:

# systemctl start mysqld.service # for systems using systemd 
# /etc/init.d/mysqld start # for systems using init

Stop the service:

# systemctl stop mysqld.service # for systems using systemd
# /etc/init.d/mysqld stop # for systems using init

Restart:

# systemctl restart mysqld.service # for system using systemd
# /etc/init.d/mysqld restart # for systems using init

Login with root user

# mysql -u root -p

You can also log in with other users by replacing root with a username, then typing a password to login. If successful, you will have a console similar to the following:

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 195 

Server version: 5.5.31-0 + wheezy1 (Debian) 

Copyright (c) 2000, 2013, Oracle and / or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and / or its affiliates. 
Other names may be trademarks of their respective owners. 

Type 'help;' or '\ h' for help. Type '\ c' to clear the current input statement.

mysql>

Summary of basic MySQL statements

Create the itfromzero database

mysql> create database itfromzero;
Query OK, 1 row affected (0.02 sec) 

mysql>

After creating the database, use the command show databases; to list available databases

mysql> show databases; 
+ -------------------- +
| Database |
+ -------------------- + 
| information_schema |
| mysql |
| performance_schema |
| itfromzero |
| test |
+ -------------------- + 
9 rows in set (0.00 sec) 
mysql>

Select Database

To select database, use the following command:

mysql> use itfromzero ;
Database changed
mysql>

Create Tables

I will create member names table with the following command:

mysql> CREATE TABLE member (
    -> id Int (3), 
    -> first_name Varchar (15), 
    -> email Varchar (20) 
    ->); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

Check the table just created with the command show tables;

mysql> show tables; 
+ ---------------------- + 
| Tables_in_itfromzero |
+ ---------------------- + 
| member |
+ ---------------------- + 

1 row in set (0.00 sec) 

mysql>

You can view the created columns with the command:

mysql> show columns from member; 

+ ------------ + ------------- + ------ + ----- + --------- + ------- + 
| Field | Type | Null | Key | Default | Extra |
+ ------------ + ------------- + ------ + ----- + --------- + ------- + 
| id | int (3) | YES | | NULL | |
| first_name | varchar (15) | YES | | NULL | |
| email | varchar (20) | YES | | NULL | |
+ ------------ + ------------- + ------ + ----- + --------- + ------- + 
3 rows in set (0.00 sec)

mysql>

Add columns to the database

You could add the last_name column between first_name and email as follows:

mysql> ALTER TABLE member ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

Review:

mysql> show columns from member; 

+ ------------ + ------------- + ------ + ----- + --------- + ------- + 
| Field | Type | Null | Key | Default | Extra |
+ ------------ + ------------- + ------ + ----- + --------- + ------- + 
| id | int (3) | YES | | NULL | |
| first_name | varchar (15) | YES | | NULL | |
| last_name | varchar (20) | YES | | NULL | |
| email | varchar (20) | YES | | NULL | |
+ ------------ + ------------- + ------ + ----- + --------- + ------- + 

4 rows in set (0.00 sec) 

mysql>

Or you can add country column after email:

mysql> ALTER TABLE member ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (0.16 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

mysql>

Review:

mysql> show columns from minttec; 

+ ------------ + ------------- + ------ + ----- + --------- + ------- + 
| Field | Type | Null | Key | Default | Extra |
+ ------------ + ------------- + ------ + ----- + --------- + ------- +
| id | int (3) | YES | | NULL | |
| first_name | varchar (15) | YES | | NULL | |
| last_name | varchar (20) | YES | | NULL | |
| email | varchar (20) | YES | | NULL | |
| country | varchar (15) | YES | | NULL | |
+ ------------ + ------------- + ------ + ----- + --------- + ------- +
5 rows in set (0.00 sec) 

mysql>

Insert values ​​into data fields

For example insert into the member table with information 1 member as follows:

mysql> INSERT INTO member VALUES ('1', 'Ravi', 'Saive', ' [email protected] ', 'India');
Query OK, 1 row affected (0.02 sec) 

mysql>

Or you can insert many members at the same time:

mysql> INSERT INTO member VALUES ('2', 'Narad', 'Shrestha', ' [email protected] ', 'India'), ('3', 'user', 'singh', ' user @ xyz. com ',' Aus'), ('4', 'itfromzero', '[dot] com', ' [email protected] ', 'VN');
Query OK, 3 rows affected (0.05 sec) 
Records: 3 Duplicates: 0 Warnings: 0

Check that the table has just inserted:

mysql> select * from member; 
+ ------ + --------------- + ----------- + -------------- -------- + --------- + 
| id | first_name | last_name | email | country |
+ ------ + --------------- + ----------- + -------------- -------- + --------- + 
| 1 | Ravi | Saive | [email protected]     | India |
| 2 | Narad | Shrestha | [email protected]         | India |
| 3 | user | singh | [email protected]          | Aus |
| 4 | itfromzero | [dot] com | [email protected] | VN |
+ ------ + --------------- + ----------- + -------------- -------- + --------- + 

4 rows in set (0.00 sec)

mysql>

Delete data in MySQL

Example delete from member table with id = 3

mysql> DELETE FROM member WHERE id = 3;

Query OK, 1 row affected (0.02 sec)

Check the board again

mysql> select * from member;

+ ------ + ------------ + ----------- + ----------------- ----- + --------- + 
| id | first_name | last_name | email | country |
+ ------ + ------------ + ----------- + ----------------- ----- + --------- +
| 1 | Ravi | Saive | [email protected]     | India |
| 2 | Narad | Shrestha | [email protected]         | India |
| 4 | itfromzero | [dot] com | [email protected] | VN |
+ ------ + ------------ + ----------- + ----------------- ----- + --------- +
3 rows in set (0.00 sec)

Value update

Member’s information editing example has id = 4

mysql> UPDATE member SET id = 3 WHERE first_name = 'itfromzero'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>

Note : You should use multiple conditions with the following statement:

mysql> UPDATE member SET id = 6 WHERE first_name = itfromzero 'AND last_name =' [dot] com '; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>

Delete columns in MySQL

Example of deleting country column:

mysql> ALTER TABLE member drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql>

Check the table again:

mysql> select * from member; 

+ ------ + --------------- + ----------- + -------------- -------- + 
| id | first_name | last_name | email |
+ ------ + --------------- + ----------- + -------------- -------- + 
| 1 | Ravi | Saive | [email protected]     |
| 2 | Narad | Shrestha | [email protected]         |
| 6 | itfromzero | [dot] com | [email protected] |
+ ------ + --------------- + ----------- + -------------- -------- +
3 rows in set (0.00 sec) 

mysql>

Rename the table

Example renaming table member to member_tbl

mysql> RENAME TABLE member TO member_tbl; 
Query OK, 0 rows affected (0.03 sec)

mysql>

List all tables

Use the show tables command to list:

mysql> show tables; 

+ ---------------------- + 
| Tables_in_itfromzero |
+ ---------------------- + 
| member_tbl |
+ ---------------------- +
1 row in set (0.00 sec) 

mysql>

Delete Database

Example of deleting Database itfromzero:

mysql> drop database itfromzero; 
Query OK, 1 row affected (0.02 sec)

List of database:

mysql> show databases; 

+ -------------------- + 
| Database |
+ -------------------- + 
| information_schema |  
| mysql |
| performance_schema |
| phpmyadmin |  
| test |
+ -------------------- +

7 rows in set (0.00 sec) 
mysql>

Above are some basic mysql commands on Linux. Good luck.