Mysql All join-create-table-foreign-key-primary-key

In This post we will discuss:

1. How to connect mysql using command prompt (CMD)
2. Show Databases
3. How to select database
4. How to Create tables
5. How to show structure of table
6. How to insert multiple Records at a time
7. How to Create to create auto_increment and primary key
8. How to Create to create Foreign Key
9. How to Create to create inner join
10. How to Create to create Leftjoin
11. How to Create to create right join

Microsoft Windows [Version 6.2.9200] (c) 2012 Microsoft Corporation. All rights reserved. ============[ connect mysql using command prompt ]================== C:\Users\Nandan>cd C:\xampp\mysql\bin C:\xampp\mysql\bin>mysql -u root -p1234; ===================================================================== Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.1.22-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. =====================[Show database ] =============================== MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | shoppingdb | | test | +--------------------+ 17 rows in set (0.00 sec) =======================[ Change dasebase ]=========================== MariaDB [(none)]> use test; Database changed ============================[ Show Tables ]=========================== MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | abc1 | | abc2 | +----------------+ 4 rows in set (0.00 sec) =========================[ Create table ]============================= MariaDB [test]> create table empinfo( -> id int auto_increment primary key, -> eid int not null, -> salary int, -> address varchar(100), -> foreign key(eid) references emp(id) -> ); Query OK, 0 rows affected (0.21 sec) ==============[ Create emp Table ]=============================== MariaDB [test]> create table emp( -> id int auto_increment primary key, -> name varchar(30), -> mobile varchar(12), -> branch varchar(100) -> ); Query OK, 0 rows affected (0.21 sec) ==============[ Show Tables ]================================ MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | abc1 | | abc2 | | emp | | empinfo | +----------------+ 4 rows in set (0.00 sec) ==============[ Show structure of Tables ]=================== MariaDB [test]> desc emp; +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | mobile | varchar(12) | YES | | NULL | | | branch | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+----------------+ 4 rows in set (0.04 sec) ==============[ Create empinfo primary key Table ]=============== MariaDB [test]> create table empinfo( -> id int auto_increment primary key, -> eid int not null, -> salary int, -> address varchar(100), -> foreign key(eid) references emp(id) -> ); Query OK, 0 rows affected (0.21 sec) ==============[ Show structure of Tables ]=================== MariaDB [test]> desc empinfo; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | eid | int(11) | NO | MUL | NULL | | | salary | int(11) | YES | | NULL | | | address | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 4 rows in set (0.04 sec) ==============[ Create Multiple Records in Tables ]=================== MariaDB [test]> insert into emp(name,mobile,branch)values("Mohan","90938392","MC A"),("Geeta","928282822","BCA"),("Rakhi","8735362222","Btech"); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> select *from emp; +-----+-------+------------+--------+ | id | name | mobile | branch | +-----+-------+------------+--------+ | 101 | Mohan | 90938392 | MCA | | 102 | Geeta | 928282822 | BCA | | 103 | Rakhi | 8735362222 | Btech | +-----+-------+------------+--------+ 3 rows in set (0.00 sec) MariaDB [test]> select *from empinfo; +----+-----+--------+---------+ | id | eid | salary | address | +----+-----+--------+---------+ | 13 | 101 | 4000 | Noida | | 15 | 103 | 9000 | Delhi | +----+-----+--------+---------+ 2 rows in set (0.00 sec) =================================[ Inner Join of Tables ]=================== MariaDB [test]> select emp.id,emp.name,empinfo.salary from emp inner join empinf o on emp.id=empinfo.eid; +-----+-------+--------+ | id | name | salary | +-----+-------+--------+ | 101 | Mohan | 4000 | | 103 | Rakhi | 9000 | +-----+-------+--------+ 2 rows in set (0.00 sec) ===================================[ Left Join of Tables ]=================== MariaDB [test]> select emp.id,emp.name,empinfo.salary from emp left join empinfo on emp.id=empinfo.eid; +-----+-------+--------+ | id | name | salary | +-----+-------+--------+ | 101 | Mohan | 4000 | | 103 | Rakhi | 9000 | | 102 | Geeta | NULL | +-----+-------+--------+ 3 rows in set (0.00 sec) ===================================[ right Join of Tables ]=================== MariaDB [test]> select emp.id,emp.name,empinfo.salary from emp right join empinf o on emp.id=empinfo.eid; +------+-------+--------+ | id | name | salary | +------+-------+--------+ | 101 | Mohan | 4000 | | 103 | Rakhi | 9000 | +------+-------+--------+ 2 rows in set (0.00 sec)