22 SQL in MySQL – I

Hiren Joshi

 

Objectives:

  • Use of select statement retrieve data from single table
  • Use of insert, update and delete statement

    Insert:

  • Insert statement is used to add new record into the table.
  • The general syntax for insert is:

Insert into <tableName>  [(column list)]

Values(value for column1 [, value for column2]…)

  • In case column list is not specified, the column values must be specified in the same order as they appeared in the table and must provide value for each column.
  • If column list is specified then the column values must be specified in the same order as they appeared in column list. The column has default value and allows null value can be omitted from the column list. You can also omit an auto increment column.
  • DEFAULT key word is used to insert default value into column. Default key word is used if a column is defined with default value.
  • Default key word is also used to a column which is defined as auto increment value. Then default value take the auto generated value for that column.
  • NULL keyword is used to insert null value into column(s). Null value can only be inserted if a column is defined so that it allows null value.
  • Example:

Consider we have create table customer as follow.

create table customer

(cid int auto_increment unique,

cname varchar(50),

ccity varchar(50) default ‘Ahmedabad’,

cemail varchar(60),

cmobile varchar(11));

  • To insert a record into customer table for all columns,

insert into customer values(1,’Mahesh’,DEFAULT,’mp123@yahoo.com’,’9876543210′);

  • To insert a record which has value for few columns

Insert into customer(cid,cname) values (2,’Rajesh’);

  • Then Ccity is Ahmedabad, cemail is null and cmobile is null.
  • If you write  insert into customer(cname,ccity,cmobile) values(‘Vimal’,’Rajkot’, ‘1234567890’);
  • Then it is considered cid value as 3 because it is defined as auto_increment and cemail is null.

   Select:

  • The select statement is used to retrieve the data stored in the specified table.
  • The select statement shows data of columns specified in column-list of the table specified in from clause.
  • The simplified syntax of select statement is:

 

Select <column-list separated by ,>

From <tableName>

[where searchCondition]

[Order by <order by list>]

[limit  <row limit>]

  • The code must follow the clauses order shown in simplified syntax for select statement.
  • To view, all the records from the table customer, code

select * from customer;

  •  To view only few columns of customer table, code

select cid, cname, ccity from customer;

  • You can code column name in any order you would like to view it.

select cname, cemail, cid from customer;

  • Where clause with select statement
  • The where clause is used to list selected records. The general syntax of where clause is used with comparison operator is

 

   Where <expression1> operator  <expression2>

o   The comparison operators:

§    =  (Equal)

§   <

§    >

§    <=

§    >=

§    < > (Not equal)

§    !=  (Not equal)

  •  To compare string literal or date literal, string literal or date literal is enclosed between quotes.
  • To list selected records, search criteria is used with where clause. For example, to display all the student whose city is Ahmedabad code

select * from customer  where ccity = ‘Ahmedabad’;

  • To display specified columns with specific search criteria, list required column name and write search criteria

select cname,cmobile from customer where ccity = ‘Ahmedabad’;

  • Character comparisons performed on data of MySQL database are not case sensitive.  So, ‘Ahmedabad’ and ‘ahmedabad’ are considered equivalent. For example, following both queries return the same result set.

 

select * from customer where ccity = ‘Ahmedabad’;

select * from customer where ccity = ‘ahmedabad’;

  • Numerical literal is compared without quotes.

         select *  from customer where cid > 1;

  • If the result of comparison is true value then the row(s) being tested is included in the result set.
  • If the result of comparison is false value or null value then the row(s) being tested is not included in the result set.

              select * from customer  where ccity = ‘Valsad’;

  • The search criteria can be done by any column of the base table irrespective of whether it is included in the select clause or not.

select cid, cname from customer where ccity = ‘Ahmedabad’;

  • If null value is comparing using one of these comparison operators, the result is always null. A null value is a value which is either unknown, unavailable or not applicable. It is not same as zero for numeric value or an empty string (‘’) for string value.

select * from customer where cemail = NULL;

  • Is null clause is used to test for null values.

    select * from customer where cemail is null;

  • Where clause can also be used with logical operators. The logical operator is used to create compound conditions which consist of two or more conditions.
  • The logical operators are:
  • And
  • Or
  • Not
  • And operator returns true when all the condition is satisfied.
  • OP operator returns true when any one condition is satisfied.
  • Not operator returns negate value means it makes true to false and false to true.
  • The order of precedence for logical operators are:  1. Not  2. And 3. Or
  • Parentheses are used to override the order of precedence for any expression or clarify the sequence in which the operations are evaluated.
  • AND operator example:

       select *  from customer where cname = ‘Vimal’ and ccity = ‘Ahmedabad’;

   OR operator example

    select *  from customer Where cname = ‘Vimal’ or ccity = ‘Ahmedabad’;

  • Not operator

    select * from customer where ccity <> ‘Ahmedabad’;

  • Compound statement example

      select * from customer where ccity = ‘Rajkot’ OR  cname != ‘Vimal’;

  • In keyword
  • In keyword is used to test whether an expression is equal to a value in the list of expressions.
  • The order of expression is insignificant in the list of expression when used with in keyword.

select *  from customer  where ccity in (‘Rajkot’,’Surat’);

  • Not operator can be used to test for an expression which are not in the list of expressions.

        select *  from customer  where cid not in (1,3,5);

  • The same expression can be written using or operator which is written by using in operator.

select * from customer where ccity = ‘rajkot’ or ccity = ‘surat’;

select * from customer where cid <> 1 and cid <> 3 and cid <> 5;

  • Between keyword
  • The between keyword is used to test whether an expression falls within a range of values.
  • The lower limit must be coded as lower value and the upper limit must be coded as the second expression. In case, the lower and upper value order is not followed, MySQL will return an empty result set.
  • The lower limit and upper limit are inclusive in between clause.

           select *  from customer where cid between 2 and 4;

  • Not operator can be used with between keyword.

         select *   from customer  where cid not between 2 and 4;

  • Like and Regexp (Regular Expression) operators
  • Like and regexp operators are used to retrieve rows that match a string pattern. The string pattern is known as mask. The mask determines which values in the column satisfy the condition.
  • The mask used with Like keyword can contain special symbol. The special symbol is called wildcard.
  • Wild cards used with like:
  • The mask used for regexp operator can have special characters and constructs. The mask used in regexp is not case sensitive.

 

Character/Construct Description
^ Matches the pattern to the starting of the string
$ Matches the pattern to the ending of the string
. Matches any single character
[characterlist]

 

 Matches any single character listed within the

character list mentioned in brackets.
[char1-char2]

 

 Matches any single character  within the range

specified between char1 and char2.
| Matches either left side or right side pattern

    select *  from customer where ccity regexp ‘Ah’;

Select * from customer  where cemail regexp ‘^vh’;

Select * From customer where cmobile regexp ‘987[246]’;

Select *  from customer where cemail regexp ‘v[a-h]’;

Select * from customer where cname regexp ‘ah|aj’;

Not operator can be used with like or regexp keyword.

As the like and regexp operator significantly degrade the performance compared to other types of searches, use them only when necessary.

     Order by clause

  • To display records in particular record, order by clause is used.

              select * from customers order by cmobile;

  • Order can be ascending or descending. By default it is ascending. To display in descending order type desc after column name.
  • In ascending order sort, special characters appear first followed by numbers and then characters. This sort order is determined by the character set used by MySQL server.

      select *  from customer  order by cname desc;

  • You can code ascending and descending code simultaneously for different column

        select *  from customer  order by ccity , cname desc ;

  •  The order by clause can be used with expression also. For example, the below code display records based on combination of student name and email.
  • Null values appears first in sort order irrespective of ascending or descending order.

 

select cid, concat(cname,’ ‘,cemail) as “Customer EMail”

from customer

order by “Customer Email”;

 

select cid, cname, ccity,concat(cemail,’ ‘,cmobile) as “Contact”

from customer

order by Contact;

 

  • The sorting can be done by any column of the base table irrespective of whether it is included in the select clause or not.

select cid, cname, cemail from customer

order by ccity;

  • Number can be used to specify the columns in order by clause to use for sorting. In that case, 1 represent the first column of the result set, 2 represents the second column of the result set and so on.

 

select cid, cname, ccity

from customer

order by 2,3;

 

  • Limit clause
  • The limit clause is used to limit specified number of rows of return by result set.
  • Limit clause takes one or two integer arguments. The general syntax for limit clause is
  • Limit  [offset , ]  row_count
  • In case, the limit clause takes single argument, it specifies the maximum row count starting from first row.
  • Limit To restrict first two rows, code

 

select *

from customer

limit 2;

  • If limit clause takes 2 arguments, it specifies from starting to end rows. The offset specifies the first rows and its offset value is 0. Following code display record number 3,4,5

 

select *

  from customer

limit 2,3;

  • Generally, order by clause is used whenever the limit clause is used.

select *

from customer

order by cmobile

limit 3 offset 2

An alias for column

  • By default, the result set display the same column name which is given in base table. Alias is used to display different column name from base table.
  • To specify an alias for column name as keyword is used. Although as keyword is optional, it is recommended to use to increase readability.
  • If an alias is not specified for a column which has calculated expression/value, MySQL is used the expression for the calculated value as the column name.
  • To use space(s) or special character(s) in an alias, the alias is enclosed between double quotes or single quotes.
  • For exam, to display column cname as customer name following code need to write:
  • Select cid, cname as “Customer Name”  from customer;
  • In case, an column alias is not included space, As keyword is excluded :
  • select cid, cname as “Customer Name”, ccity CustomerCity from customer;
  • Following code shows that as keyword is not included for cid.
  • select cid ‘customer id’, cname as “Customer Name”, ccity as CustomerCity from customer;

       Test expression without from clause

  • In MySQL, to code from clause is not compulsory to test expression that include arithmetic operators and functions.

select 1000*(1+.1) as “10% More than 1000”;

  • The CURRENT_DATE( ) function returns the current date. The parentheses are optional for it. select CURRENT_DATE() as “Current Date”;
  • DATE_FORMAT function is used to format display date in specific format. The DATE_FORMAT function is used % as prefix with format code. For example, %m is used to represent month in numeric format.

select CURRENT_DATE as “Current Date”,

 

DATE_FORMAT(CURRENT_DATE,’%d-%m-%Y’) as “Formatted Date in dd-mm-YYYY

 

format”;

  • CONCAT function is used to concatenate two or more strings.

 

select cid, cname, CONCAT(cid,cname) as “Concated Student ID and Name” from customer;

 

Eliminate duplicate records

  • The distinct keyword is used to prevent identical (duplicate) records from being included in the result set.
  • The distinct key word must be place immediately after the select keyword.

       select distinct ccity  from customer;

 

Update:

  • Update statement is used to update record(s) of the table.
  • The syntax to update is:

Update <table_name>

Set columnName1 = value1 [,

columnName2 = value2 , …]

[where searchCondition]

  • The value for a column can be literal or an expression.
  • In the where clause, you can specify the conditions that must be met for a row to be updated.
  • In case, where clause is not used, the update statement will update all the records of the table.
  • To update city for all customer as surat code

 

update customer

set ccity = ‘Surat’;

  • To update specified row i.e. update city for cid 3,

update customer

set ccity = ‘Rajkot’

where cid = 3;

 

  • Now cid 1 and 2 have ccity is surat and cid is Rajkot.

    Delete:

  • Delete statement is used to delete record(s) from the table.
  • The general syntax to delete is:

Delete from <table_name>

[Where searchCondition]

  • In case, where clause is not used, the delete statement will delete all the records of the table.
  • To delete all records whose city is surat

 

delete from customer

where ccity = ‘surat’;

 

  • Above delete statement, delete 2 records from customer table
  • In case where criteria are not used, then the delete statement will delete all the records.

 

References:

 

1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,

2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress

3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,

4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O’Reilly Media

5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox

6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible

7. Joel Murach, Ray Harris: Murach’s PHP and MySQL, Shroff/Murach

8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML/Javascript/DHTML/PHP , BPB Publications

9. Joel Murach, “Murach’s MySQL”, Shroff/Murach

10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams

11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education

12. http://www.php.net/

13. http://www.w3schools.com/

14. http://www.tutorialspoint.com/