SQL Notes for basic to advance.

First of all if i open the sql command or Workbench then need to password.

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 8.0.37 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#Data base ko dekhne ke liye quary:-----------------------------------------

mysql> SHOW DATABASES;

#then by default eske under jentna bhi rhega sb at a time execute hoga.

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.01 sec)

mysql> SHOW DATABASES

-> SHOW DATABASES;

ERROR 2013 (HY000): Lost connection to MySQL server during query

No connection. Trying to reconnect...

Connection id: 14

Current database: *** NONE ***

Note : Esme hum quary ko capital aur small do letters me lekh skte hai.

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.01 sec)

mysql> create database kishan ka databasr;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ka databasr' at line 1

mysql> create databases kishan ka database;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases kishan ka database' at line 1

Yah best method hai quary lekhne ka..............................................................

mysql> CREATE DATABASE Kishan_db;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| information_schema |

| kishan_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

10 rows in set (0.00 sec)

mysql> create database rahul_db

-> ;

Query OK, 1 row affected (0.01 sec)

mysql> show database;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| information_schema |

| kishan_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

11 rows in set (0.00 sec)

mysql> create database Aktu_ka_ database;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

mysql> CREATE DATABASE Aktu_ka_database;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| aktu_ka_database |

| information_schema |

| kishan_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

12 rows in set (0.00 sec)

yaha tk hum data base banana seekhe hai aur unko show kra kra ke bhi cheak kiye hai.

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| aktu_ka_database |

| information_schema |

| kishan_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

12 rows in set (0.00 sec)

mysql> USE rahul_db();

ERROR 1049 (42000): Unknown database 'rahul_db()'

mysql> USE<rahul_db>

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<rahul_db>' at line 1

mysql> use rahul_db;

Database changed

mysql> select database();

+------------+

| database() |

+------------+

| rahul_db |

+------------+

1 row in set (0.00 sec)

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| aktu_ka_database |

| information_schema |

| kishan_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

12 rows in set (0.00 sec)

mysql> use aktu_ka_database;

Database changed

mysql> select database();

+------------------+

| database() |

+------------------+

| aktu_ka_database |

+------------------+

1 row in set (0.00 sec)

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| aktu_ka_database |

| information_schema |

| kishan_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

12 rows in set (0.00 sec)

#Data base ko delete krne k liye quary..........................

mysql> DROP DATABASE kishan_db;

Query OK, 0 rows affected (0.01 sec)

mysql> show Databases;

+---------------------+

| Database |

+---------------------+

| aktu_ka_database |

| information_schema |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| school_db |

| student_db |

| sys |

+---------------------+

11 rows in set (0.00 sec)

mysql> DROP DATABASE school_db;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

+---------------------+

| Database |

+---------------------+

| aktu_ka_database |

| information_schema |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| sys |

+---------------------+

10 rows in set (0.00 sec)

mysql> CREATE DATABASE jai_Bajgrang_Bali_ka_db;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| mysql |

| performance_schema |

| rahul_db |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| sys |

+-------------------------+

11 rows in set (0.00 sec)

#Kesi data base ko lakr uspr use use krne k liye.............

mysql> USE jai_Bajgrang_Bali_ka_db;

Database changed

# Data base ko select krne k liye.

mysql> SELECT DATABASE();

+-------------------------+

| DATABASE() |

+-------------------------+

| jai_bajgrang_bali_ka_db |

+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT DATABASE();

+-------------------------+

| DATABASE() |

+-------------------------+

| jai_bajgrang_bali_ka_db |

+-------------------------+

1 row in set (0.00 sec)

Table ko describe krne k liye...........................................

DESC lekh kr hum us table ka name likh rhe jese hume describe krna hai......................jase family_member table ko..........

mysql> DESC family_members;

ERROR 1146 (42S02): Table 'jai_bajgrang_bali_ka_db.family_members' doesn't exist

mysql> DESC family_members;

ERROR 1146 (42S02): Table 'jai_bajgrang_bali_ka_db.family_members' doesn't exist

mysql> DESC customers;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(100) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> use table family_members;

ERROR 1049 (42000): Unknown database 'table'

mysql> USE aktu_ka_database;

Database changed

mysql> DESC family_members;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

Note: for clear the screen

system.cls; yahi syntex hai.........................................................

mysql> use aktu_ka_database; # yaha aktu ka data use krke changing kr rhe hai.

Database changed

mysql> desc family_members;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> INSERT INTO family_members(id,name,age)

-> VALUES (100,"rAVI KUMAR SINGH",23),

-> (101,"KISHAN KUMAR YADAV",25),

-> (102,"mUKESH KUMMAR CHAURESIA",24)

-> ;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> DESC family_members;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> CLS;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLS' at line 1

mysql> cls

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cls' at line 1

mysql> desc family_members;INSERT INTO family_membersVALUES(101,"RAVI SINGH",22),(102,"KISHAN KUMAR YADAV",24),(103,"PRIYA SINGH",24),(104,"ROSHNI SINGH",26);

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '101,"RAVI SINGH",22),(102,"KISHAN KUMAR YADAV",24),(103,"PRIYA SINGH",24),(104,"' at line 1

mysql> desc family_members;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> INSERT INTO family_members

-> VALUES

-> (101,"RAJEEV",28),

-> (102,"KISHAN",24),

-> (103,"RAVI KUMAR SINGH",21),

-> (104,"NIDHI SINGH",18),

-> (105,"KARAN SINGH",25);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

#Kesi particular collumn ko delete krne k liye.............

mysql> SELECT id,name FROM family_members;

+------+-------------------------+

| id | name |

+------+-------------------------+

| 100 | rAVI KUMAR SINGH |

| 101 | KISHAN KUMAR YADAV |

| 102 | mUKESH KUMMAR CHAURESIA |

| 101 | RAVI SINGH |

| 102 | KISHAN KUMAR YADAV |

| 103 | PRIYA SINGH |

| 104 | ROSHNI SINGH |

| 101 | RAJEEV |

| 102 | KISHAN |

| 103 | RAVI KUMAR SINGH |

| 104 | NIDHI SINGH |

| 105 | KARAN SINGH |

+------+-------------------------+

12 rows in set (0.00 sec)

mysql> SELECT name FROM family_members;

+-------------------------+

| name |

+-------------------------+

| rAVI KUMAR SINGH |

| KISHAN KUMAR YADAV |

| mUKESH KUMMAR CHAURESIA |

| RAVI SINGH |

| KISHAN KUMAR YADAV |

| PRIYA SINGH |

| ROSHNI SINGH |

| RAJEEV |

| KISHAN |

| RAVI KUMAR SINGH |

| NIDHI SINGH |

| KARAN SINGH |

+-------------------------+

12 rows in set (0.00 sec)

#yadi 10000 student ke data me ek ka details nekalna ho to unkiid k througn diya jata hai unki id ke through hum kr skte hai..........

mysql>

PRATICS...................................................................................................

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 38

Server version: 8.0.37 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college_ka_database |

| dosti |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

| sys |

+-------------------------+

16 rows in set (0.02 sec)

mysql> DROP dosti;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dosti' at line 1

mysql> drop database dosti;

Query OK, 1 row affected (0.10 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college_ka_database |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

| sys |

+-------------------------+

15 rows in set (0.00 sec)

mysql> drop databases sys,mysql;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases sys,mysql' at line 1

mysql> drop databases dosti;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases dosti' at line 1

mysql> drop databases sys;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases sys' at line 1

mysql> DROP DATABASE sys;

Query OK, 101 rows affected (0.41 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college_ka_database |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

+-------------------------+

14 rows in set (0.00 sec)

mysql> CREATE DATABASE Data_Science;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college_ka_database |

| data_science |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

+-------------------------+

15 rows in set (0.00 sec)

mysql> use database data_science;

ERROR 1049 (42000): Unknown database 'database'

mysql> use data_science;

Database changed

mysql> create table data_science_student_table;

ERROR 4028 (HY000): A table must have at least one visible column.

mysql> create table datasciencestudent;

ERROR 4028 (HY000): A table must have at least one visible column.

mysql> use data_science;

Database changed

mysql> create table data;

ERROR 4028 (HY000): A table must have at least one visible column.

mysql> CREATE TABLE data(

-> id INT UNIQUE

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO data VALUES(101);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO data VALUES(102);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM data;

+------+

| id |

+------+

| 101 |

| 102 |

+------+

2 rows in set (0.00 sec)

mysql> INSERT INTO data VALUES

-> (103);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO data VALUES(103);

ERROR 1062 (23000): Duplicate entry '103' for key 'data.id'

mysql> INSERT INTO data VALUES(104);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM data;

+------+

| id |

+------+

| 101 |

| 102 |

| 103 |

| 104 |

+------+

4 rows in set (0.00 sec)

mysql> CREATE TABLE data(

-> name VARCHAR(50),

-> rollno INT NOT NULL);

ERROR 1050 (42S01): Table 'data' already exists

mysql> SELECT * TABLE FROM data_science;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE FROM data_science' at line 1

mysql> alter table data add column name varchar(50) after id;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select*from data;

+------+------+

| id | name |

+------+------+

| 101 | NULL |

| 102 | NULL |

| 103 | NULL |

| 104 | NULL |

+------+------+

4 rows in set (0.00 sec)

mysql>

mysql> alter table data add column rollno int before name;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before name' at line 1

mysql> alter table data add column rollno int after name;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select*from data;

+------+------+--------+

| id | name | rollno |

+------+------+--------+

| 101 | NULL | NULL |

| 102 | NULL | NULL |

| 103 | NULL | NULL |

| 104 | NULL | NULL |

+------+------+--------+

4 rows in set (0.00 sec)

mysql> select rollno from data;

+--------+

| rollno |

+--------+

| NULL |

+--------+

4 rows in set (0.00 sec)

mysql> select name from data values

-> ("Ravi singh");

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values

("Ravi singh")' at line 1

mysql> INSERT INTO data (name, rollno) VALUES (value1, value2, value3, etc);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> INSERT INTO data (name, rollno)

-> VALUES("RAVI KUMAR SINGH",67),

-> VALUES("KISHAN KUMAR PRAJAPATI",43),

-> VALUES("RAHUL SINGH",22);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES("KISHAN KUMAR PRAJAPATI",43),

VALUES("RAHUL SINGH",22)' at line 3

mysql> INSERT INTO data (name, rollno)

-> VALUES("RAVI KUMAR SINGH",67);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT*FROM data;

+------+------------------+--------+

| id | name | rollno |

+------+------------------+--------+

| 101 | NULL | NULL |

| 102 | NULL | NULL |

| 103 | NULL | NULL |

| 104 | NULL | NULL |

| NULL | RAVI KUMAR SINGH | 67 |

+------+------------------+--------+

5 rows in set (0.00 sec)

mysql> create database if not exist college;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exist college' at line 1

mysql> CREATE DATABASE IF NOT EXISTS college;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college |

| college_ka_database |

| data_science |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

+-------------------------+

16 rows in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS college;

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college |

| college_ka_database |

| data_science |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

+-------------------------+

16 rows in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS prema;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college |

| college_ka_database |

| data_science |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| mysql |

| performance_schema |

| prema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

+-------------------------+

17 rows in set (0.00 sec)

mysql> create database if not exists madhubala;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+-------------------------+

| Database |

+-------------------------+

| aktu_ka_database |

| college |

| college_ka_database |

| data_science |

| information_schema |

| jai_bajgrang_bali_ka_db |

| kishan_ka_database |

| library |

| library_ka_database |

| madhubala |

| mysql |

| performance_schema |

| prema |

| ravi_kumar_singh_db |

| ravi_singh db |

| student_db |

| student_ka_database |

| students_details_db |

+-------------------------+

18 rows in set (0.00 sec)

mysql> create table emp(

-> id int ,

-> salery int default 25000);

Query OK, 0 rows affected (0.04 sec)

mysql> insert into emp(id) values(101);

Query OK, 1 row affected (0.01 sec)

mysql> select *from emp;

+------+--------+

| id | salery |

+------+--------+

| 101 | 25000 |

+------+--------+

1 row in set (0.00 sec)

mysql>

Search This Blog

Ravi Kumar Singh Data Scientest

SQL Notes for basic to advance.

Popular posts from this blog

Python Complete notes with code and explanation in hindi english both language.

Overall company coding pratics with full concepts in python language.

Find the largest three distinct elements in an array