MySQL (CBSE Class XI & XII IP)

Select Command in MySQL

Students

S_IDNAME GENDERSUBJECTCLASSSECTIONBIRTH_DATESchool_Name
1001BOBBYMCOMPUTERXIIZ1987-01-27BHOPAL
1002SUNNYMCOMPUTERXIA1986-04-11BANGLORE
1003AYUSHMMATHXIA2000-10-09KV HARDA
1004AMITMCOMMERCEXIIB2000-09-21KV HARDA
1005VISHALMCOMMERCEXIIB2001-08-23KV HARDA
1006RASHMIFMATHXIIA2000-01-06KV HARDA
1007BOBBY SONIMARTXIIZ1986-01-31HARDA
1008TAPTIFCOMMERCEXIIB2000-09-12KV HARDA
1009ADITIFMATHXIIA2000-04-06KV HARDA
1010DEVENDRAMCOMMERCEXIIB2000-07-15KV HARDA
1011HARDIKMCOMMERCEXIIB2000-05-18KV HARDA
1012UDDHAVMCOMMERCEXIIB2001-12-10KV HARDA
1013AASHIFMATHXIIA2000-10-19KV HARDA
1014PRANJALIFMATHXIIA2001-10-18KV HARDA
1015AARTIFBIOLOGYXIIC2001-05-19KV HARDA
1016LEKHAFBIOLOGYXIC2003-01-21KV HARDA

Select all columns – 

Command (select all column and all data from table using * ):

    SELECT * FROM table-name;

Example:

     SELECT * FROM students;

Select particular Columns –  

Command(select particular columns from table using column name):

                     SELECT column1, column2, column3 

                     FROM table-name;

Example:

                     SELECT name, s_id, class, section FROM students;

 

DISTINCT keyword – Use for eliminating Redundant Data or duplicate row.

 

 

      SELECT DISTINCT CLASS FROM students ;

  (Display the name of all classes without duplicate value)

 

      SELECT DISTINCT School_Name FROM students ;

  (Display the all School Names without duplicate value)

 

ALL keyword – it show the all records with duplicate value.

It is just same as when you specify neither DISTINCT nor ALL keyword.

      SELECT ALL CLASS FROM students ;

  (Display the name of all classes)

      SELECT DISTINCT School_Name FROM students ;

  (Display the all School Names from table)

Using Column Alias –  

Command(view selected column with new name):

        SELECT column-name AS “column-alias’

        FROM table-name;

Example:

       SELECT name AS “student_name”, S_ID, DOB 

       FROM students;

  (Display three column student_name, s_id and DOB” )

       SELECT name AS “student_name”, S_ID AS “Student_ID” DOB 

       FROM students;

  (Display three column student_name, Student_id and DOB” )

Select perticular Rows(record) –

WHERE Clause

Command:

     SELECT what_to_select 

      FROM table-name 

      WHERE condition_to_satisfy_with_using_operator;

 

Examples:

      SELECT * FROM students

      WHERE name = ‘bobby’;

 

      SELECT * FROM students

      WHERE Birth_date = ‘2000-04-06’;

 

      SELECT * FROM students

      WHERE id >= 1005;

 

AND operator – all condition are true or satisfy.

      SELECT * FROM students

      WHERE id >= 1005 AND Section = ‘B’;

 

OR operator – any one condition is true.

 

      SELECT * FROM students

      WHERE name = ‘bobby’ OR subject = ‘computer’ ;

 

 LIKE (String- matching operator)   – 

condition based on Pattern matches.

Pattern are described using two special wildcard characters:

1. Percent sign (%) – for any substring or part of string.

2. Underscore sign ( _ ) – for any single charater. 

 

      SELECT * FROM students

      WHERE name LIKE%A%’;

(all record are select and view which have character ‘A’)

 

      SELECT * FROM students

      WHERE subject LIKE ‘com%’;

  (all record are select and view that subject start from ‘COM’)

      

      SELECT * FROM students

      WHERE name LIKE ‘Vi_h_ _’;

(name VISHAL select)

 

      SELECT * FROM students

      WHERE ID LIKE  10_ _;

  (select all record from table that ID’s start from 10)

 

BETWEEN operator – condition based on a Range.

 

      SELECT * FROM students

      WHERE ID BETWEEN 1005 AND 1010 ;

IN operator – condition based on a LIST.

 

      SELECT * FROM students

      WHERE CLASS IN (‘ART’, ‘BIOLOGY’) ;

NOT IN operator – condition based on a LIST.

 

      SELECT * FROM students

      WHERE CLASS NOT IN (‘ART’, ‘BIOLOGY’) ;

 

IS NULL  – Searching NULL value in a column.

 

      SELECT * FROM students

      WHERE CLASS IS NULL ;

 

Performing Simple Calculation

using Select command you can perform simple calculation

and also use MySQL function i.e.

String Functions, Numeric Functions or Date & time functions.

Examples: 

    SELECT 5*6;

     SELECT 6.1919 + 3.11;

     SELECT 9.870 * 22 + 110 – 133;

     SELECT curdate();

     SELECT Now();

 

Leave a Reply

Your email address will not be published. Required fields are marked *