Mysql Basic Commands

 ============Mysql Basic Commands=================

1)create database sample; ----> creating new database
2)use sample
3)show tables;   -----> show the all tables
4)create table student(id integer primary key auto_increment,name char(20),phone_no int(10),address varchar(100),dob date);
5)desc student --> see the all column in table
6)insert into student (name,phone_no,address) values ("koti",66666666666,"bng"), ("swathi",777777777,"karntaka"), ("anushuka",000000,"banagalore"); --->inserting data to table
7)select * from student; ---> see all the records in single table
8)select * from student where name = "koti"; -- > where condition
9)alter table student rename course --- > Rename the table
10)alter table student add column dob date;   --- > Add column in Mysql
11)alter table course change dob dateofbirth date;  ---> Rename the column
12)alter table course modify city char(20); ---> Change column Type
13)update course SET name = "rao" where id = 1;
14)Joins

select * from course;
+----+----------+------------+------------+-------------+-----------+
| id | name     | phone_no   | address    | dateofbirth | city      |
+----+----------+------------+------------+-------------+-----------+
|  1 | rao      | 2147483647 | mlg        | NULL        | Bangalore |
|  2 | sam      | 2147483647 | madras     | NULL        | Bangalore |
|  3 | anjali   | 2147483647 | bng        | NULL        | Bangalore |
|  4 | swathi   |  777777777 | karntaka   | NULL        | Bangalore |
|  5 | anushuka |          0 | banagalore | NULL        | Bangalore |
+----+----------+------------+------------+-------------+-----------+
 select * from student;
+----+------+
| id | name |
+----+------+
|  1 | koti |
|  2 | rao  |
+----+------+
2 rows in set (0.00 sec)

Innnerjoin -> select s.name,c.address from student as s join course as c where c.st_id = s.id;
+------+---------+
| name | address |
+------+---------+
| koti | mlg     |
| koti | madras  |
+------+---------+
2 rows in set (0.00 sec)


Left outerjoin ---> select s.name,c.address from student as s left join course as c on c.st_id = s.id;
+------+---------+
| name | address |
+------+---------+
| koti | mlg     |
| koti | madras  |
| rao  | NULL    |
+------+---------+
3 rows in set (0.00 sec)

 Right  outerjoin --->select s.name,c.address from student as s right  join course as c on c.st_id = s.id;
+------+------------+
| name | address    |
+------+------------+
| koti | mlg        |
| koti | madras     |
| NULL | bng        |
| NULL | karntaka   |
| NULL | banagalore |
+------+------------+
5 rows in set (0.00 sec)