SQL by Abhilash
SQL:-
sql is structured query language.
All SQL queries are as below:-
show databases:-
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| amit |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
create database:-
mysql> create database demo;
Query OK, 1 row affected (0.04 sec)
use database demo;
mysql> use demo;
Database changed
create table student:-
create table student(name varchar(30),id int not null primary key,address varchar(50),marks int);
mysql> create table student(name varchar(30),id int not null primary key,address varchar(50),marks int);
Query OK, 0 rows affected (0.09 sec)
use of desc:-
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| id | int | NO | PRI | NULL | |
| address | varchar(50) | YES | | NULL | |
| marks | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
use of insert(secure way):-
mysql> insert into student(name,id,address,marks) values('kailash',12,'Haridwar',78);
Query OK, 1 row affected (0.01 sec)
use of insert(insecure way i.e. here we should know the sequence of column field):-
mysql> insert into student(name,id,address,marks) values('kailash',12,'Haridwar',78);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values('ram',45,'haridar',79),('ravi',17,'delhi',90);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
use of select:-
mysql> select * from student;
+---------+----+-----------+-------+
| name | id | address | marks |
+---------+----+-----------+-------+
| kailash | 12 | Haridwar | 78 |
| ravi | 17 | delhi | 90 |
| kamal | 23 | rishikesh | 56 |
| ram | 45 | haridar | 79 |
+---------+----+-----------+-------+
4 rows in set (0.00 sec)
mysql> select name,id from student;
+---------+----+
| name | id |
+---------+----+
| kailash | 12 |
| ravi | 17 |
| kamal | 23 |
| ram | 45 |
+---------+----+
4 rows in set (0.00 sec)
mysql> select * from student where id=12;
+---------+----+----------+-------+
| name | id | address | marks |
+---------+----+----------+-------+
| kailash | 12 | Haridwar | 78 |
+---------+----+----------+-------+
1 row in set (0.00 sec)
here we have set the key 'id' as 'primary' hence we can't give duplicate values to it,also we can't give null values as values is set to be not null
mysql> insert into student values('ram',12,'haldwani',67);
ERROR 1062 (23000): Duplicate entry '12' for key 'student.PRIMARY'
use of update:-
mysql> update student set address='dehradun' where id=45;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
use of alter:-
mysql> alter table student add phoneno int;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+---------+----+-----------+-------+---------+
| name | id | address | marks | phoneno |
+---------+----+-----------+-------+---------+
| kailash | 12 | Haridwar | 78 | NULL |
| ravi | 17 | delhi | 90 | NULL |
| kamal | 23 | rishikesh | 56 | NULL |
| ram | 45 | dehradun | 79 | NULL |
+---------+----+-----------+-------+---------+
4 rows in set (0.00 sec)
mysql> alter table student drop column phoneno;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select *from student;
+---------+----+-----------+-------+
| name | id | address | marks |
+---------+----+-----------+-------+
| kailash | 12 | Haridwar | 78 |
| ravi | 17 | delhi | 90 |
| kamal | 23 | rishikesh | 56 |
| ram | 45 | dehradun | 79 |
+---------+----+-----------+-------+
4 rows in set (0.00 sec)
use of delete(where the structure of table has changed is considered to be DDL and where data of table has been changes is considered to be as DML) :-
mysql> delete from student where name='kamal';
Query OK, 1 row affected (0.02 sec)
mysql> select * from student;
+---------+----+----------+-------+
| name | id | address | marks |
+---------+----+----------+-------+
| kailash | 12 | Haridwar | 78 |
| ravi | 17 | delhi | 90 |
| ram | 45 | dehradun | 79 |
+---------+----+----------+-------+
3 rows in set (0.02 sec)
use of aggregate functions:-
mysql> select sum(marks) from student;
+------------+
| sum(marks) |
+------------+
| 247 |
+------------+
1 row in set (0.00 sec)
mysql> select avg(marks) from student;
+------------+
| avg(marks) |
+------------+
| 82.3333 |
+------------+
1 row in set (0.01 sec)
mysql> select count(name) from student;
+-------------+
| count(name) |
+-------------+
| 3 |
+-------------+
1 row in set (0.01 sec)
mysql> select max(marks) from student;
+------------+
| max(marks) |
+------------+
| 90 |
+------------+
1 row in set (0.00 sec)
mysql> select min(marks) from student;
+------------+
| min(marks) |
+------------+
| 78 |
+------------+
1 row in set (0.00 sec)
(use of like):-
mysql> select * from student where name like 'r%'; (for first element searching)
+------+----+----------+-------+
| name | id | address | marks |
+------+----+----------+-------+
| ravi | 17 | delhi | 90 |
| ram | 45 | dehradun | 79 |
+------+----+----------+-------+
2 rows in set (0.00 sec)
mysql> select *from student where name like '%h';(for last element searching)
+---------+----+----------+-------+
| name | id | address | marks |
+---------+----+----------+-------+
| kailash | 12 | Haridwar | 78 |
+---------+----+----------+-------+
1 row in set (0.00 sec)
use to search second element where _ defines the single letter:-
mysql> select *from student where name like '_a%';
+---------+----+----------+-------+
| name | id | address | marks |
+---------+----+----------+-------+
| kailash | 12 | Haridwar | 78 |
| ravi | 17 | delhi | 90 |
| ram | 45 | dehradun | 79 |
+---------+----+----------+-------+
3 rows in set (0.00 sec)
use to search second last element where _ defines the single letter:-
mysql> select *from student where name like '%a_';
+------+----+----------+-------+
| name | id | address | marks |
+------+----+----------+-------+
| ram | 45 | dehradun | 79 |
+------+----+----------+-------+
1 row in set (0.00 sec)
Joins:-
inner join:
mysql> select * from student inner join employee on student.id=employee.id;
+---------+----+----------+-------+----+--------+---------+-------+
| name | id | address | marks | id | salary | empcode | name |
+---------+----+----------+-------+----+--------+---------+-------+
| kailash | 12 | Haridwar | 78 | 12 | 20000 | 102 | aman |
| ram | 45 | dehradun | 79 | 45 | 30000 | 202 | kamal |
+---------+----+----------+-------+----+--------+---------+-------+
2 rows in set (0.00 sec)
left join:
mysql> select * from student left join employee on student.id=employee.id;
+---------+----+----------+-------+------+--------+---------+-------+
| name | id | address | marks | id | salary | empcode | name |
+---------+----+----------+-------+------+--------+---------+-------+
| kailash | 12 | Haridwar | 78 | 12 | 20000 | 102 | aman |
| ravi | 17 | delhi | 90 | NULL | NULL | NULL | NULL |
| ram | 45 | dehradun | 79 | 45 | 30000 | 202 | kamal |
+---------+----+----------+-------+------+--------+---------+-------+
3 rows in set (0.00 sec)
Comments
Post a Comment