Skip to main content

SQL Basic Queries using Linux terminal

0) open mysql on terminal
open terminal and type command
sudo mysql
entre password

1) Show Database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new                |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2) Change database



mysql> use new;
Database changed

3) Create Table 
mysql> create table person(sno varchar(5) primary key, name varchar(20), age int(3), sex varchar(1));
Query OK, 0 rows affected (0.01 sec)

4) show Table
mysql> show tables;
+---------------+
| Tables_in_new |
+---------------+
| person        |
+---------------+
1 row in set (0.00 sec)

 5)Describe table

mysql> describe person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(5)  | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| sex   | varchar(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)


6) insert value into table

mysql> insert into person (sno,name,age,sex) values ('1', 'subham', 25, 'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert into person(sno,name,age,sex) values ('2', 'nunu', 30, 'F');
Qmysql> insert into person(sno,name,age,sex) values ('3', 'chayan', 20, 'M');
Query OK, 1 row affected (0.01 sec)
uery OK, 1 row affected (0.02 sec)



7) Show whole table

mysql> select *from person
    -> ;
+-----+--------+------+------+
| sno | name   | age  | sex  |
+-----+--------+------+------+
| 1   | subham |   25 | M    |
| 2   | nunu   |   30 | F    |
| 3   | chayan |   20 | M    |
+-----+--------+------+------+
3 rows in set (0.00 sec)

8) insert again values into table 


mysql> insert into person(sno,name,age,sex) values ('4', 'debo', 15, 'M');
Query OK, 1 row affected (0.00 sec)

9) show table
mysql> select *from person;
+-----+--------+------+------+
| sno | name   | age  | sex  |
+-----+--------+------+------+
| 1   | subham |   25 | M    |
| 2   | nunu   |   30 | F    |
| 3   | chayan |   20 | M    |
| 4   | debo   |   15 | M    |
+-----+--------+------+------+
4 rows in set (0.00 sec)

10) perform select operation
 mysql> select sno from person where sno='2';
+-----+
| sno |
+-----+
| 2   |
+-----+
1 row in set (0.00 sec)

mysql> select sno from person;
+-----+
| sno |
+-----+
| 1   |
| 2   |
| 3   |
| 4   |
+-----+
4 rows in set (0.00 sec)


mysql> select name from person;
+--------+
| name   |
+--------+
| subham |
| nunu   |
| chayan |
| debo   |
+--------+
4 rows in set (0.00 sec)

mysql> select *from person;
+-----+--------+------+------+
| sno | name   | age  | sex  |
+-----+--------+------+------+
| 1   | subham |   25 | M    |
| 2   | nunu   |   30 | F    |
| 3   | chayan |   20 | M    |
| 4   | debo   |   15 | M    |
+-----+--------+------+------+
4 rows in set (0.01 sec)

mysql> select sno,age,sex from person where name='subham';
+-----+------+------+
| sno | age  | sex  |
+-----+------+------+
| 1   |   25 | M    |
+-----+------+------+
1 row in set (0.01 sec)


mysql> select age from person;
+------+
| age  |
+------+
|   25 |
|   30 |
|   20 |
|   15 |
+------+
4 rows in set (0.00 sec)

mysql> select name from person where age=30;
+------+
| name |
+------+
| nunu |
+------+
1 row in set (0.00 sec)

11) alter table 


mysql> alter table person add phone varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table person add email varchar(20) after age;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table person add adress varchar(20) after phone;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

12) show table after alter

mysql> describe person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sno    | varchar(5)  | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| email  | varchar(20) | YES  |     | NULL    |       |
| sex    | varchar(1)  | YES  |     | NULL    |       |
| phone  | varchar(10) | YES  |     | NULL    |       |
| adress | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

13) show person 
 mysql> select *from person;
+-----+--------+------+-------+------+-------+--------+
| sno | name   | age  | email | sex  | phone | adress |
+-----+--------+------+-------+------+-------+--------+
| 1   | subham |   25 | NULL  | M    | NULL  | NULL   |
| 2   | nunu   |   30 | NULL  | F    | NULL  | NULL   |
| 3   | chayan |   20 | NULL  | M    | NULL  | NULL   |
| 4   | debo   |   15 | NULL  | M    | NULL  | NULL   |
+-----+--------+------+-------+------+-------+--------+
4 rows in set (0.00 sec)

 14) modify table 
 mysql> alter table person modify email varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

15) describe table after modify

mysql> describe person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sno    | varchar(5)  | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| email  | varchar(30) | YES  |     | NULL    |       |
| sex    | varchar(1)  | YES  |     | NULL    |       |
| phone  | varchar(10) | YES  |     | NULL    |       |
| adress | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

15) perform modify again
mysql> alter table person modify adress varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

16) describe person
 mysql> describe person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sno    | varchar(5)  | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| email  | varchar(30) | YES  |     | NULL    |       |
| sex    | varchar(1)  | YES  |     | NULL    |       |
| phone  | varchar(10) | YES  |     | NULL    |       |
| adress | varchar(30) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

17) drop one attributes

mysql> alter table person drop adress;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

18) describe person after drop one attribute
mysql> describe person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(5)  | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| email | varchar(30) | YES  |     | NULL    |       |
| sex   | varchar(1)  | YES  |     | NULL    |       |
| phone | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

19)update table
mysql> update person set age=25;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 5  Changed: 4  Warnings: 0

mysql> update person set sex='M';
Query OK, 1 row affected (0.02 sec)
Rows matched: 5  Changed: 1  Warnings: 0

20) show table 
 mysql> select *from person;
+-----+--------+------+----------------------+------+------------+
| sno | name   | age  | email                | sex  | phone      |
+-----+--------+------+----------------------+------+------------+
| 1   | subham |   25 | NULL                 | M    | NULL       |
| 2   | nunu   |   25 | NULL                 | M    | NULL       |
| 3   | chayan |   25 | NULL                 | M    | NULL       |
| 4   | debo   |   25 | NULL                 | M    | NULL       |
| 5   | joy    |   25 | iamsubham1@gmail.com | M    | 8981887507 |
+-----+--------+------+----------------------+------+------------+
5 rows in set (0.00 sec

21) perform delete operation

mysql> delete from person where sno='4';
Query OK, 1 row affected (0.01 sec)

mysql> select *from person;
+-----+--------+------+----------------------+------+------------+
| sno | name   | age  | email                | sex  | phone      |
+-----+--------+------+----------------------+------+------------+
| 1   | subham |   25 | NULL                 | M    | NULL       |
| 2   | nunu   |   25 | NULL                 | M    | NULL       |
| 3   | chayan |   25 | NULL                 | M    | NULL       |
| 5   | joy    |   25 | iamsubham1@gmail.com | M    | 8981887507 |
+-----+--------+------+----------------------+------+------------+
4 rows in set (0.01 sec)

mysql> describe person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | varchar(5)  | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| email | varchar(30) | YES  |     | NULL    |       |
| sex   | varchar(1)  | YES  |     | NULL    |       |
| phone | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

22) delete whole table  values

mysql> delete from person;
Query OK, 4 rows affected (0.01 sec)

mysql> select *from person;
Empty set (0.00 sec)

 23) drop whole table
mysql> drop table person;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql> describe person;
ERROR 1146 (42S02): Table 'new.person' doesn't exist
mysql> select *from person;
ERROR 1146 (42S02): Table 'new.person' doesn't exist

24) exit mysql on terminal
mysql> exit
Bye
Labels:

Comments

Post a Comment

Popular posts from this blog

how to Install Numpy, Pandas and matplotlib on ubuntu 18.04 and Linux Mint

Install Python, NumPy,Matplotlib for Python 3 on Ubuntu 18.04, Linux Mint, Debian Linux. This is a short article about installing Numpy, Pandas , Matplotlib, Python3 on the latest Ubuntu 18.04 LTS, Linux Mint, Debian Linux which comes with Python 3.6.5. Let’s start by making sure we have an updated system: 1 sudo apt update 2 sudo apt upgrade Now, let’s install NumPy, Pandas,Matplotlib : sudo apt-get install python-pip sudo pip install numpy sudo pip install pandas sudo pip install matplotlib Test numpy : Open up a Terminal in Your Linux Operating System by running the following: python3 At the Terminal, type the following: >>> import numpy as np >>> np.__version__ '1.13.3'   Test Pandas : Open up a Terminal in Your Linux Operating System by running the following: python3 At the Terminal, type the following: >>> import pandas as pd >>> pd.__version__ '0.22.0'   Test Matpl...
2 comments
Read more

Introduction to SQL and Database with example

            Introduction to SQL and Database What is SQL? SQL is a language used to retrieve and manipulate data in a RDMS. SQL stands for S tructured Q uery L anguage. What is a Database? A database is a place to store data. A relational database system (RDBMS) stores data in tables. Relational Database Tables A relational database stores data in tables. Each table has a number of rows and columns. The table below has 4 rows and 3 columns. SQL and Relational Databases A relational database contains tables which store data that is related in some way. SQL is the language that allows retrieval and manipulation of table data in a relational database.
Post a Comment
Read more

Library Management System DataFlow Diagram

Library Management System DataFlow Diagram 1) Zero Level DFD 2) 1st level DFD and 2nd level DFD
Post a Comment
Read more