Mysql

Introduction of Mysql:;
       Mysql is a type of database. Its a opensource relational database it means free of cost , used around the world.Its a smaller part of RDBMS.It is fast and easy to use being used for smaller and large organization.


⟻






There are several command used in mysql


(Q)How to Create Database?
Ans :- We create database by using Create command on mySQL prompt in console environment.


Syntax:-  Create database database name;↩
Ex-          Create database students;↩

                    (Here students is a name of database)
After press enter key Message show as :-
   Query ok, 1 row affected (0.00 sec).
(Q) How to open any Created database?
Ans:-with the help of USE command user can open any created database.
             
Syntax:- USE  database name;↩
             USE students;↩
       Then , a message shown as "Database changed"
(Q)How to create table in Database?
Ans:-we can create database table by using CREATE command.

   Syntax:- CREATE Table Table name(Field1  datatype1,Field2 datatype2, Field n datatype n);
Ex:-  CREATE Table Students(Roll int ,name char(20), marks int);
  
Then, a message shown as:- "Query OK, 0 rows affected (0.16 sec)"

(Q)How to display Structure of Created Database Table?
Ans:-To display the structure of any created database table use desc cmd

Syntax:- desc table name;↩
Ex;-        desc students;↩

Then Structure of table open with fields name and its types:-

Field                Type                 Null           Key               Default              Extra

Roll                   int                   yes                                    NULL

Name                 char(20)        Yes                                    NULL

marks                   int                  yes                                   NULL

3 rows in set.

(Q) How to Insert record in table:

Ans:- By using Insert command user insert records in database table.

Syntax:-  Insert into table name  values (data 1, data2 ,data ....n);

Ex:-         Insert  into students values(101,'sonu',55);↩

                Insert  into students values(102,'Monu',66);↩

                Insert  into  students  values(103,'Ramu',77);↩


Then, message shown as:-

                                 1 row affected.
                   (By using upper three lines user insert total three records in students table.)

(Q) How to display record of any open file?

Ans:-  By using SELECT command User display the record of any created file.

Syntax:-  SELECT  *  from table name  ↩

Ex:-          SELECT * from Students ↩

Then ,total row of table display as:-



Roll                Name                         Marks

  101                 sonu                              55

 102                 Monu                             66

 103                Ramu                              77

*** Insert command provides several alternatives with SELECT command.

  (i) If user wants to display any specific fields of table then:-

        Syntax:-  SELECT field1, field2, field....n  ↩


       Ex:-
        SELECT roll, Name ,Marks from students;  ↩

Then records of Student table shown as:-

Roll                Name                         Marks

  101                 sonu                              55

 102                 Monu                             66


 103                Ramu                              77

(ii)  If user wants to display records according to any field then:-

Mysql> SELECT roll ,marks from students ;  ↩

Then records of Student table shown as:-

Roll                                      Marks

  101                                        55

  102                                        66

  103                                        77

(iii) Relational operator (>,< )used with SELECT command .

    mysql> SELECT  name , roll , village from student where marks > 60 ;  ↩


    Then records of Student table shown as:-

Roll                Name                         Marks

 102                 Monu                             66

 103                Ramu                              77

(iv) Arithmatic operator used with SELECT command .

mysql> select roll, name,  marks + 5  from  students  ;  ↩

Then records of student table shown as:-

Roll                Name                         Marks

  101               sonu                              60

 102                Monu                            71

 104                Mohit                            72

 103                Ramu                            82

 105                sohan                            40

 (V) Logical operator used with SELECT command .

            Use of OR operator.


             Ex:-  SELECT name,class from students where roll =101  or  roll = 103   ;  ↩

              Use of AND operator.

            mysql> Select * from students where roll =101 and marks = 60.  ;  ↩

           Use of NOT Operator.

             Ex:-  SELECT * from students where class !=10 ;  ↩








*** Use of  'IN'  and  'NOT IN ' Predicates:-

         The arithmatic operator (=) Compares a single value to another single value. In case a value needs to be compared to a list of values then the 'IN' predicate is used. One can check a single value against multiple  values by using the 'IN' predicate.

   (Q) Find the list of students whose roll is 101,103,104,102.

                mysql> Select * from students where roll in (101,103,104,102) ;  ↩

   NOT IN:-
             The 'NOT IN ' predicate is the opposite of the 'IN '  predicate. This will select all the                      rows where values donot match all of the values int the list.

 (Q) Find the list of students whose name is  not 'sonu','monu','mohit' .

mysql> Select * from students where name NOT IN ('sonu','monu','mohit') ;  ↩




RANGE SEARCHING by using  (BETWEEN & NOT BETWEEN)

BETWEEN:- 
                    In order to select data that is within a range of values, the "BETWEEN" operator is used. In 'BETWEEN' operators allows the selection or rows that contain values within a specified lower and upper limit..The range coded after the word 'BETWEEN' is inclusive .

NOT BETWEEN is vice versa of BETWEEN operator..

                                                                                        The lower must be code first. The two values in between the range must be linked with the keyword 'AND'. The BETWEEN operator can be used with both character column and the other from a numeric value. Eg:-

(Q) Find the list of students whose fee is greater than and equal to 2500 and less than and equal to 4000.

              mysql> Select * from students where fees between 2500 and 4000 ;  ↩


 (Q) Find the list of students whose fee is NOT BETWEEN 2500 and 4000 .

              mysql> Select * from students where fees not between 2500 and 4000 ;  ↩




                                            ****PATTERN MATCHING*****

                                                                                        (LIKE and NOT LIKE predicate)

The  Comprision operaor discussed so far have compared on e value, exacute to one another value. Such precision may not always be desired or necessary . For this purpose oracle provides the 'Like' predicate.
                 The LIKE predicate allows for a comparison of one string value with another string value,which is not identical. This is achived by using wildcard characters. Two wildcard characters that are available are:-

For character datatypes:-

(i) The percent sign (%) match any String.
(ii) The underscore(_) matches any single character.


(Q) Find the list of students whose name end with kumar.

Ans:-

mysql> Select *from students where name like '%kumar' ;  ↩


(Q) Find the list of students whose name starts with Dr.

Ans:-
mysql> Select * from students where name like 'DR%'  ; ↩


(Q)Find the list of students whose name contains 'a' at second and second last positions.

                Mysql> Select *from student where name like '_a%a_'; ↩

(Q) Find the list of studetnts whose name contains  'Kumar' .

   mysql> Select * from students where name like '%kumar%'; ↩

(Q) Find the list of students whose name contains 'I' at the forth positions.

    Musql>Select *from students  where name like '___I%'

(Q)Find the list of students whose name contains 'b' at second postition & 'i' at forth position.

    mysql> Select * from students where name like  '_B_I%' ; ↩


(Q) Find the list of students with student_id & name whose fee is not greater than 26000.

   mysql>   Select  student_id  "students id" name "studetn name" from student where fee                          <=26000;  ↩

(Q) Find the list of studetns of area 'Ara' ,'balia' and 'chhapara' whose name contains kumar in the last.

Mysql> Select * from students where city in('ara','balia','chapra') and name like '%kumar' ; ↩


(Q) Find the list of Employee  whose designation is 'manager' ,'assistance' or 'poune '   but the emp of gaya must not be included.

mysql> Select *from students where city not in ('gaya') ; ↩


(Q) Find the list of employee where City must not be 'patna','Gaya', 'Ranchi' and 'Ara'  and name of emp contain 'sinha' and basic pay must be >=2000  and <= 30,000.

mysql> Select *from employee where city not in ('patna','gaya','ranchi','ara') and name like '%sinha' and basic_pay between 20,000 and 30,000 ; ↩

(Q) Find the list of manager and assistant of bhagalpur and patna whose name start with 'Dr' and end with 'Singh%'

mysql> Select * from employee where designation in('manage','assistant' ) and city in ('bhagalpur' ,'jamalpur') and name like 'Dr%' and name like '%singh';   ↩


                                    ********** ORDER By STATEMENT***********

                               It is used to retrieve data in ascending or descending order of attributes                                values:-

                     *  Ascending order - 'asc'
                                                   
                                                              * Descending order - 'desc'

                                                                                                        *By default order-  'asc'

(Q) find the list of employee in ascending order of emp no.

Ans:-
             Select * from employee order by emp_no  asc;  ↩


                                    ***    Here asc is optional. ****

(Q) Find the list of employee in descending order of basic pay.

Ans:- Select *from  employee order by basic_pay desc;  ↩

(Q) Find the list of employee in asceding order of empyee name & basic pay   .

Ans:-
 Select * from  employee order by name, basic asc;  ↩

(Q) Find the list of employee in descending order of basic pay.

Ans:- Select *from employee order by basic_pay desc; ↩
  
(Q) find the list of employee in ascending order of employee name and basic pay.

Ans:- Select *from employee order by name,basic_pay asc; ↩

(Q)  Find the list of employee whose designation is manager & basic pay exist between 10,000 and 20,000 but the list must be displayed in ascending order of name, city.

Ans:- Select *from employee where desiganation='mgr' and basic_pay between 10000 and 20000 order by name asc, city asc;





     

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
(Q) How to DELETE any particular record from table.?

Ans:- DELETE  command is used for delete any record from table.


 Syntax:-

          DELETE  FROM  students where name= 'monu'  ;  ↩
           
                   (Here , students is a table name and  name is a field name of that table.)

(Q) What is Primary key ? Why it requird in table ? How to create a primary key?

Ans:- Primary key is a key which used in table with field ,which appily with that field in               table , which  all record field value is different / unique to each other. In the case of duplicate value its show error message on screen or prevent to enter duplicate primary key field values.
   PRIMARY  key used in table to prevent duplicate value and also used for relate any two database table , where both table has minimum one common field  required.


                         In  mysql primary key appily with ALTER  command.

(Q) ALTER Command:-

            ALTER Command is used to  apply primary key with unique field of table.

EX:- ALTER table Students add primary key (roll) ;  ↩

                  Then message shown as:-

                                        (1 row afftcted.)

                  **To display its type DESC students ;  ↩

Then Structure open as:-

Field                Type                 Null           Key               Default          _    Extra

Roll                   int                    yes             PRI                NULL

Name               char(20)           Yes                                    NULL

marks               int                    yes                                    NULL
___________________________________________________________________

Note:- Roll Field पर primary key लगाने के बाद अगर user कोई  duplicate  Roll enter  करेगा  तो                   एक  error messaage  show  होगा और duplicate entry को prevent करेगा !

(Q ) प्राइमरी Key   को  कैसे  हटते  हैं ?

Ans :- Primary Key  को Alter  command  के साथ Drop keyword लगा  कर  हटाते हैं !

For ex :-

                            

                            ALTER Table student drop primary key.  ;  ↩

                             ** देखने  के  लिए  desc students टाइप करें !**

(ii) Alter command का उपयोग किसी  बनाए हुऐ Table में  नया field जोड़ने के लिए करते हैं !

                              Ex:- ALTER table students add fees int ;  ↩

                                ** देखने  के  लिए  desc students टाइप करें !**

Then Structure open as:-

Field                Type                 Null           Key               Default          _    Extra

Roll                   int                    yes             PRI                NULL

Name               char(20)           Yes                                    NULL

marks               int                    yes                                    NULL

fees                   int                    yes                                    NULL

___________________________________________________________________

** देखने  के  लिए  Select command टाइप करें !**

mysql> select *from  students ;  ↩

Roll                Name                         Marks                     Fees

  101                 sonu                              55                        NULL

 102                 Monu                             66                       NULL

 103                 Ramu                             77                      NULL

____________________________________________________

(Q) किसी  field के data type ko change करने के लिए :-

                          ALTER Command के साथ modify column का use  करते हैं !

Syntax :- ALTER table Table name modify column field name datatype  ;  ↩

Ex:-         ALTER table students  modify column fees character(15)  ;  ↩

                          ** देखने  के  लिए  desc students टाइप करें !**

Then Structure open as:-

Field                Type                 Null           Key               Default          _    Extra

Roll                   int                    yes             PRI                NULL

Name               char(20)           Yes                                    NULL

marks               int                    yes                                    NULL

fees                  char(15)            yes                                    NULL

___________________________________________________________________

(iii) Alter command का उपयोग किसी  बनाए हुऐ Table में column को Drop करने के  लिए करते हैं !

               Syntax:- Alter table table name drop column field name ;  ↩

                 Ex:- Alter table students drop column fees;

           ** देखने  के  लिए  desc students टाइप करें !**

Then Structure open as:-

Field                Type                 Null           Key               Default          _    Extra

Roll                   int                    yes             PRI                NULL

Name               char(20)           Yes                                    NULL

marks               int                    yes                                    NULL

(iv)Alter command का उपयोग किसी  बनाए हुऐ Table में column को Add करने के  लिए करते हैं !

               Syntax:- Alter table table name add column field name ;  ↩

                 Ex:- Alter table students add column fees;

           ** देखने  के  लिए  desc students टाइप करें !**

Then Structure open as:-

Field                Type                 Null           Key               Default          _    Extra

Roll                   int                    yes             PRI                NULL

Name               char(20)           Yes                                    NULL

marks               int                    yes                                    NULL

fees                   int                    yes                                    NULL

                                                        
(Q) UPDATE Command:- 

Update command का उपयोग किसी record को change करने  के  लिए और table में कोई field खाली हो उसको fill करने के लिए करते हैं !

Syntax:- Update table name set field name= value  where field name= value;

Ex:-         Update Students set fees= 2500 where name= 'ramu'  ;  ↩

** देखने  के  लिए  Select command टाइप करें !**

mysql> select *from  students ;  ↩

Roll                Name                         Marks                     Fees

  101                 sonu                              55                        NULL

 102                 Monu                             66                       NULL

 103                 Ramu                             77                      NULL