MySQL Basic queries tutorial 1

on
Open mysql command line
Enter password : password

CREATE A DATABASE OF EMP

mysql> create database emp;
mysql> \c emp;
mysql> use emp;
Database changed

ALSO LEARN  What is Data Definition Language and Data Manipulation Language ?

CREATE A TABLE OF COMPANY

mysql> create table company (cid int not null, cname varchar(15) not null, ccity varchar(20) not null, primary key (cid))

mysql> insert into company values('1','TCS','Mumbai');
Query OK, 1 row affected (0.58 sec)

mysql> insert into company values('2','Cisco','Bangalore');
Query OK, 1 row affected (0.21 sec)

mysql> insert into company values('3','Accenture','Kolkata');
Query OK, 1 row affected (0.17 sec)

mysql> insert into company values('4','Infosys','Pune');
Query OK, 1 row affected (0.65 sec)
mysql> select * from company;
+-----+-----------+-----------+
| cid | cname     | ccity     |
+-----+-----------+-----------+
|   1 | TCS       | Mumbai    |
|   2 | Cisco     | Bangalore |
|   3 | Accenture | Kolkata   |
|   4 | Infosys   | Pune      |
+-----+-----------+-----------+
4 rows in set (0.00 sec)

IN SIMILAR WAYS CREATE A TABLE OF EMPLOYEE

mysql> create table employee (eid int not null, ename varchar(15) not null,edesignation varchar(20) not null, ecity varchar(20) not null,esalary int not null, eompany varchar(20) not null , primary key (cid))

HOW TO DISPLAY THE ENTIRE TABLE?

mysql> select * from employee;
+-----+--------+--------------+-----------+---------+-----------+
| eid | ename  | edesignation | ecity     | esalary | ecompany  |
+-----+--------+--------------+-----------+---------+-----------+
|   1 | Sumi   | Analyst      | Mumbai    | 50000   | TCS       |
|   2 | Sakhi  | Analyst      | Bangalore | 40000   | Cisco     |
|   3 | Suzi   | Executive    | Kolkata   | 60000   | Accenture |
|   4 | hina   | Analyst      | Pune      | 45000   | Infosys   |
|   5 | Hiten  | Developor    | Mumbai    | 90000   | TCS       |
|   6 | Deepak | Tester       | Bangalore | 70000   | Cisco     |
|   7 | Dina   | Marketer     | Mumbai    | 20000   | Accenture |
|   8 | Aina   | Manager      | Pune      | 50000   | Infosys   |
+-----+--------+--------------+-----------+---------+-----------+
8 rows in set (0.00 sec)

HOW TO DISPLAY PARTICULAR COLUMNS FROM ONE TABLE?

mysql> select ename,ecompany from employee;
+--------+-----------+
| ename  | ecompany  |
+--------+-----------+
| Sumi   | TCS       |
| Sakhi  | Cisco     |
| Suzi   | Accenture |
| hina   | Infosys   |
| Hiten  | TCS       |
| Deepak | Cisco     |
| Dina   | Accenture |
| Aina   | Infosys   |
+--------+-----------+
8 rows in set (0.00 sec)
HOW TO DISPLAY PARTICULAR COLUMNS FROM ONE TABLE AFTER RENAMING THE COLUMN HEADINGS?

mysql> select ename as employee_name , ecompany as employee_company from employee;
+---------------+------------------+
| employee_name | employee_company |
+---------------+------------------+
| Sumi          | TCS              |
| Sakhi         | Cisco            |
| Suzi          | Accenture        |
| hina          | Infosys          |
| Hiten         | TCS              |
| Deepak        | Cisco            |
| Dina          | Accenture        |
| Aina          | Infosys          |
+---------------+------------------+
8 rows in set (0.00 sec)

HOW TO DISPLAY PARTICULAR COLUMNS FROM ONE TABLE GIVEN A CERTAIN CONDITION?

mysql> select * from employee where ecity="Mumbai";
+-----+-------+--------------+--------+---------+-----------+
| eid | ename | edesignation | ecity  | esalary | ecompany  |
+-----+-------+--------------+--------+---------+-----------+
|   1 | Sumi  | Analyst      | Mumbai | 50000   | TCS       |
|   5 | Hiten | Developor    | Mumbai | 90000   | TCS       |
|   7 | Dina  | Marketer     | Mumbai | 20000   | Accenture |
+-----+-------+--------------+--------+---------+-----------+
3 rows in set (0.16 sec)

mysql> select * from employee where ecity="Mumbai" and ecompany="TCS";
+-----+-------+--------------+--------+---------+----------+
| eid | ename | edesignation | ecity  | esalary | ecompany |
+-----+-------+--------------+--------+---------+----------+
|   1 | Sumi  | Analyst      | Mumbai | 50000   | TCS      |
|   5 | Hiten | Developor    | Mumbai | 90000   | TCS      |
+-----+-------+--------------+--------+---------+----------+
2 rows in set (0.10 sec)

mysql> select * from employee where ecity="Mumbai" or ecompany="TCS";
+-----+-------+--------------+--------+---------+-----------+
| eid | ename | edesignation | ecity  | esalary | ecompany  |
+-----+-------+--------------+--------+---------+-----------+
|   1 | Sumi  | Analyst      | Mumbai | 50000   | TCS       |
|   5 | Hiten | Developor    | Mumbai | 90000   | TCS       |
|   7 | Dina  | Marketer     | Mumbai | 20000   | Accenture |
+-----+-------+--------------+--------+---------+-----------+
3 rows in set (0.00 sec)

HOW TO UPDATE VALUES IN A PARTICULAR TABLE?

mysql> update employee set esalary=25000 where eid=7;
Query OK, 1 row affected (0.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select eid,esalary from employee;
+-----+---------+
| eid | esalary |
+-----+---------+
|   1 | 50000   |
|   2 | 40000   |
|   3 | 60000   |
|   4 | 45000   |
|   5 | 90000   |
|   6 | 70000   |
|   7 | 25000   |
|   8 | 50000   |
+-----+---------+
8 rows in set (0.00 sec)

AGGREGATE FUNCTIONS

mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.09 sec)
mysql> select max(esalary), ename from employee;
+--------------+-------+
| max(esalary) | ename |
+--------------+-------+
| 90000        | Sumi  |
+--------------+-------+
1 row in set (0.00 sec)
mysql> select min(esalary) as minimum, ename from employee;
+---------+-------+
| minimum | ename |
+---------+-------+
| 25000   | Sumi  |
+---------+-------+
1 row in set (0.00 sec)

 Get complete MySQL /PostgreSQL cheat sheet / formulas 
MySQL Basic queries tutorial 1