mysql select statement

the MySQL select statement is used to select date from a database.

the mysql select statement allows you to retrieve zero or more rows from tables or views.

the MySQL select command returns a result that is a combination of columns and rows, which is also known as a result-set.

mysql select syntax

select     column_name1,    column_name2, ...

                from       table_name1      [ inner | left | right ]  
                
                join   table_name2   on   conditions
                             
                where      conditions
                             
                group by   group
                             
                having     group_conditions
                             
                order by   column_1        [asc | desc]
                             
                limit      offset,         row_count

the select statement is composed of several clauses:

  • select chooses which columns of the table you want to get the data
  • from specifies the table from which you get the data
  • join gets data from multiple table based on certain join conditions
  • where filters rows to select
  • group by group rows to apply aggregate functions on each group
  • having filters group based on groups defined by group by clause
  • order by specifies the order of the returned result set
  • limit constrains number of returned rows
        select    *   from     table_name;

the asterisk (*) notation is a shorthand of selecting all columns in the table.


use this table in below mysql select examples

below is a selection from the "user" table:

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india
2ramkumar9549293212usa
3shyamdhaka2323234545jaipur
4ankitnehra3423567845uae
5radhakumari2323121212jaipur

MySQL select example

the following mysql statement selects the "firstname" and "mobileno" columns from the "user" table:

 
      select    firstname,   mobileno       from        user  ;

output :-

firstnamemobileno
sandeep9549293245
ram9549293212
shyam2323234545
ankit3423567845
radha2323121212

select and where

the following mysql statement selects the desired record from the "user" table :

 
   select   userid, firstname, lastname, mobileno, city       from        user  
      
   where     firstname = 'sandeep' ;     

output :-

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india

select * example

how select all columns from a table using select command

the following mysql statement selects all the columns from the "user" table :

 
      select      *      from        user  ;

output :-

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india
2ramkumar9549293212usa
3shyamdhaka2323234545jaipur
4ankitnehra3423567845uae
5radhakumari2323121212jaipur

mysql  select into  statement

the select into statement copies data from one table and inserts it into a new table.

mysql  select into  syntax

                select      column_name(s)

                    into      newtable   [in  externaldb]
                   
                    from    table_name;

the select ...into form of select enables a query result to be stored in variables or written to a file.

note : an into clause should not be used in a nested select because such a select must return its result to the outer context.


mysql select into example

create a data copy of user:

                   select  *

                     into     data
                     
                     from     user;

use the in clause to copy the table into another database:

                   select  *

                     into     data     in   'my.mdp'
                     
                     from     user;

mysql   select   distinct statement

the MySQL select distinct statement is used to return only distinct (different) values.

when querying data from a table, you may get duplicate rows.in order to remove the duplicate rows,you use the distinct operator in the select statement.

the distinct keyword can be used to return only distinct (different) values.

mysql   select   distinct syntax

             select    distinct      column_name,    column_name,   ....
 
             from     table_name
                                  
             where    where_conditions

below is a selection from the "user" table:

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india
2ramkumar9549293212usa
3shyamdhaka2323234545jaipur
4ankitnehra3423567845uae
5radhakumari2323121212jaipur

MySQL SELECT DISTINCT example

the following mysql statement only the distinct values from the "city" columns from the "user" table :

          
              selelct   distinct   city     from    user ;
              

output :-

city
india
usa
jaipur
uae