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

important question on Entity Relationship Model(ER Model)

5)A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints. Answer:   In the answer given here, the main entity sets are student, course, course-offering, and instructor. The entity set course-offering is a weak entity set dependent on course. The assumptions made are : a class meets only at one particular place and time. This E - R diagram cannot model a class meet
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

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