{"id":223,"date":"2018-07-13T05:20:14","date_gmt":"2018-07-13T05:20:14","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&#038;p=223"},"modified":"2018-08-10T10:22:20","modified_gmt":"2018-08-10T10:22:20","slug":"sql-in-mysql-i","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/sql-in-mysql-i\/","title":{"rendered":"SQL in MySQL \u2013 I"},"content":{"raw":"<div>\r\n\r\n&nbsp;\r\n\r\n<strong>Objectives:<\/strong>\r\n<ul>\r\n \t<li><strong>Use of select statement retrieve data from single table<\/strong><\/li>\r\n \t<li><strong>Use of insert, update and delete statement<\/strong><\/li>\r\n<\/ul>\r\n<strong>\u00a0 \u00a0 Insert:<\/strong>\r\n<ul>\r\n \t<li>Insert statement is used to add new record into the table.<\/li>\r\n \t<li>The general syntax for insert is:<\/li>\r\n<\/ul>\r\nInsert into &lt;tableName&gt;\u00a0 [(column list)]\r\n\r\nValues(value for column1 [, value for column2]\u2026)\r\n<ul>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">DEFAULT key word is used to insert default value into column. Default key word is used if a column is defined with default value.<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">Example:<\/li>\r\n<\/ul>\r\nConsider we have create table customer as follow.\r\n\r\ncreate table customer\r\n\r\n(cid int auto_increment unique,\r\n\r\ncname varchar(50),\r\n\r\nccity varchar(50) default 'Ahmedabad',\r\n\r\ncemail varchar(60),\r\n\r\ncmobile varchar(11));\r\n<ul>\r\n \t<li>To insert a record into customer table for all columns,<\/li>\r\n<\/ul>\r\ninsert into customer values(1,'Mahesh',DEFAULT,'mp123@yahoo.com','9876543210');\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li>To insert a record which has value for few columns<\/li>\r\n<\/ul>\r\nInsert into customer(cid,cname) values (2,'Rajesh');\r\n<ul>\r\n \t<li>Then Ccity is Ahmedabad, cemail is null and cmobile is null.<\/li>\r\n \t<li>If you write\u00a0 insert into customer(cname,ccity,cmobile) values('Vimal','Rajkot', '1234567890');<\/li>\r\n \t<li>Then it is considered cid value as 3 because it is defined as auto_increment and cemail is null.<\/li>\r\n<\/ul>\r\n<strong>\u00a0 \u00a0Select:<\/strong>\r\n<ul>\r\n \t<li>The select statement is used to retrieve the data stored in the specified table.<\/li>\r\n \t<li>The select statement shows data of columns specified in column-list of the table specified in from clause.<\/li>\r\n \t<li>The simplified syntax of select statement is:<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\nSelect &lt;column-list separated by ,&gt;\r\n\r\nFrom &lt;tableName&gt;\r\n\r\n[where searchCondition]\r\n\r\n[Order by &lt;order by list&gt;]\r\n\r\n[limit\u00a0 &lt;row limit&gt;]\r\n<ul>\r\n \t<li style=\"text-align: justify\">The code must follow the clauses order shown in simplified syntax for select statement.<\/li>\r\n \t<li style=\"text-align: justify\">To view, all the records from the table customer, code<\/li>\r\n<\/ul>\r\nselect * from customer;\r\n<ul>\r\n \t<li>\u00a0To view only few columns of customer table, code<\/li>\r\n<\/ul>\r\nselect cid, cname, ccity from customer;\r\n<ul>\r\n \t<li>You can code column name in any order you would like to view it.<\/li>\r\n<\/ul>\r\nselect cname, cemail, cid from customer;\r\n<ul>\r\n \t<li><strong>Where clause with select statement<\/strong><\/li>\r\n \t<li style=\"text-align: justify\">The where clause is used to list selected records. The general syntax of where clause is used with comparison operator is<\/li>\r\n<\/ul>\r\n<\/div>\r\n&nbsp;\r\n<div>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0Where &lt;expression1&gt; operator\u00a0 &lt;expression2&gt;<\/p>\r\n<p style=\"text-align: justify\">o\u00a0\u00a0 The comparison operators:<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 =\u00a0 (Equal)<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0 &lt;<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &gt;<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &lt;=<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &gt;=<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &lt; &gt; (Not equal)<\/p>\r\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 !=\u00a0 (Not equal)<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">\u00a0To compare string literal or date literal, string literal or date literal is enclosed between quotes.<\/li>\r\n \t<li style=\"text-align: justify\">To list selected records, search criteria is used with where clause. For example, to display all the student whose city is Ahmedabad code<\/li>\r\n<\/ul>\r\nselect * from customer\u00a0 where ccity = 'Ahmedabad';\r\n<ul>\r\n \t<li>To display specified columns with specific search criteria, list required column name and write search criteria<\/li>\r\n<\/ul>\r\nselect cname,cmobile from customer where ccity = 'Ahmedabad';\r\n<ul>\r\n \t<li style=\"text-align: justify\">Character comparisons performed on data of MySQL database are not case sensitive.\u00a0 So, \u2018Ahmedabad\u2019 and \u2018ahmedabad\u2019 are considered equivalent. For example, following both queries return the same result set.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\nselect * from customer where ccity = 'Ahmedabad';\r\n\r\nselect * from customer where ccity = 'ahmedabad';\r\n\r\n<\/div>\r\n<ul>\r\n \t<li><span style=\"text-align: initial;font-size: 1em\">Numerical literal is compared without quotes.<\/span><\/li>\r\n<\/ul>\r\n<div>\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0select *\u00a0 from customer where cid &gt; 1;\r\n<ul>\r\n \t<li style=\"text-align: justify\">If the result of comparison is true value then the row(s) being tested is included in the result set.<\/li>\r\n \t<li style=\"text-align: justify\">If the result of comparison is false value or null value then the row(s) being tested is not included in the result set.<\/li>\r\n<\/ul>\r\n<\/div>\r\n<div>\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 select * from customer\u00a0 where ccity = 'Valsad';\r\n<ul>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n<\/ul>\r\nselect cid, cname from customer where ccity = 'Ahmedabad';\r\n<ul>\r\n \t<li style=\"text-align: justify\">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 (\u2018\u2019) for string value.<\/li>\r\n<\/ul>\r\nselect * from customer where cemail = NULL;\r\n<ul>\r\n \t<li style=\"text-align: justify\">Is null clause is used to test for null values.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 select *\u00a0from customer where cemail is null;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">The logical operators are:<\/li>\r\n \t<li style=\"text-align: justify\">And<\/li>\r\n \t<li>Or<\/li>\r\n \t<li>Not<\/li>\r\n \t<li style=\"text-align: justify\">And operator returns true when all the condition is satisfied.<\/li>\r\n \t<li style=\"text-align: justify\">OP operator returns true when any one condition is satisfied.<\/li>\r\n \t<li style=\"text-align: justify\">Not operator returns negate value means it makes true to false and false to true.<\/li>\r\n \t<li style=\"text-align: justify\">The order of precedence for logical operators are:\u00a0 1. Not\u00a0 2. And 3. Or<\/li>\r\n \t<li style=\"text-align: justify\">Parentheses are used to override the order of precedence for any expression or clarify the sequence in which the operations are evaluated.<\/li>\r\n \t<li style=\"text-align: justify\">AND operator example:<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 \u00a0select *\u00a0 from customer where cname = 'Vimal' and ccity = 'Ahmedabad';<\/p>\r\n\u00a0 \u00a0OR operator example\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 select *\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer W<\/span><span style=\"text-align: initial;font-size: 1em\">here cname = 'Vimal' or ccity = 'Ahmedabad';<\/span><\/p>\r\n\r\n<ul style=\"text-align: justify\">\r\n \t<li><span style=\"text-align: initial;font-size: 1em\">Not operator<\/span><\/li>\r\n<\/ul>\r\n<\/div>\r\n<div>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 select * from customer where ccity &lt;&gt; 'Ahmedabad';<\/p>\r\n\r\n<ul style=\"text-align: justify\">\r\n \t<li>Compound statement example<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 select * from customer where ccity = 'Rajkot' OR\u00a0 cname != 'Vimal';<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\"><strong>In keyword<\/strong><\/li>\r\n \t<li style=\"text-align: justify\">In keyword is used to test whether an expression is equal to a value in the list of expressions.<\/li>\r\n \t<li style=\"text-align: justify\">The order of expression is insignificant in the list of expression when used with in keyword.<\/li>\r\n<\/ul>\r\nselect *\u00a0 from customer\u00a0 where ccity in ('Rajkot','Surat');\r\n<ul>\r\n \t<li style=\"text-align: justify\">Not operator can be used to test for an expression which are not in the list of expressions.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 \u00a0 select *\u00a0 from customer\u00a0 where cid not in (1,3,5);<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">The same expression can be written using or operator which is written by using in operator.<\/li>\r\n<\/ul>\r\nselect * from customer where ccity = 'rajkot' or ccity = 'surat';\r\n\r\nselect * from customer where cid &lt;&gt; 1 and cid &lt;&gt; 3 and cid &lt;&gt; 5;\r\n<ul>\r\n \t<li><strong>Between keyword<\/strong><\/li>\r\n \t<li style=\"text-align: justify\">The between keyword is used to test whether an expression falls within a range of values.<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">The lower limit and upper limit are inclusive in between clause.<\/li>\r\n<\/ul>\r\n<\/div>\r\n<div>\r\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0select *\u00a0\u00a0from customer\u00a0where cid between 2 and 4;<\/p>\r\n\r\n<ul>\r\n \t<li>Not operator can be used with between keyword.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0select *\u00a0 \u00a0from customer\u00a0 where cid not between 2 and 4;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: left\"><strong>Like and Regexp (Regular Expression) operators<\/strong><\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">The mask used with Like keyword can contain special symbol. The special symbol is called wildcard.<\/li>\r\n \t<li style=\"text-align: justify\">Wild cards used with like:<\/li>\r\n<\/ul>\r\n<img class=\"alignnone size-full wp-image-226 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110.png\" alt=\"\" width=\"665\" height=\"221\" \/>\r\n<ul>\r\n \t<li style=\"text-align: justify\">The mask used for regexp operator can have special characters and constructs. The mask used in regexp is not case sensitive.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<table class=\"aligncenter\" style=\"height: 253px\" border=\"1\">\r\n<tbody>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 147.063px\"><strong>Character\/Construct<\/strong><\/td>\r\n<td style=\"height: 28px;width: 322.063px\"><strong>Description<\/strong><\/td>\r\n<\/tr>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 147.063px\">^<\/td>\r\n<td style=\"height: 28px;width: 322.063px\">Matches the pattern to the starting of the string<\/td>\r\n<\/tr>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 147.063px\">$<\/td>\r\n<td style=\"height: 28px;width: 322.063px\">Matches the pattern to the ending of the string<\/td>\r\n<\/tr>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 147.063px\">.<\/td>\r\n<td style=\"height: 28px;width: 322.063px\">Matches any single character<\/td>\r\n<\/tr>\r\n<tr style=\"height: 0px\">\r\n<td style=\"height: 57px;width: 147.063px\" rowspan=\"2\">[characterlist]\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 57px;width: 322.063px\" rowspan=\"2\">Matches any single character listed within the\r\n\r\ncharacter list mentioned in brackets.<\/td>\r\n<\/tr>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 147.063px\" rowspan=\"2\">[char1-char2]\r\n\r\n&nbsp;<\/td>\r\n<td style=\"height: 28px;width: 322.063px\" rowspan=\"2\">Matches any single character\u00a0 within the range\r\n\r\nspecified between char1 and char2.<\/td>\r\n<\/tr>\r\n<tr style=\"height: 28px\">\r\n<td style=\"height: 28px;width: 147.063px\">|<\/td>\r\n<td style=\"height: 28px;width: 322.063px\">Matches either left side or right side pattern<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 select *\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where ccity regexp 'Ah';<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select *\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cemail regexp '^vh';<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select * F<\/span><span style=\"text-align: initial;font-size: 1em\">rom customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cmobile regexp '987[246]';<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select *\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cemail regexp 'v[a-h]';<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select *\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cname regexp 'ah|aj';<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Not operator can be used with like or regexp keyword.<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">As the like and regexp operator significantly degrade the performance compared to other types of searches, use them only when necessary.<\/span><\/p>\r\n\r\n<\/div>\r\n<div>\r\n\r\n<strong>\u00a0 \u00a0 \u00a0Order by clause<\/strong>\r\n<ul>\r\n \t<li>To display records in particular record, order by clause is used.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 select *\u00a0from customers\u00a0order by cmobile;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">Order can be ascending or descending. By default it is ascending. To display in descending order type desc after column name.<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 select *\u00a0 from customer\u00a0 order by cname desc;<\/p>\r\n\r\n<ul>\r\n \t<li>You can code ascending and descending code simultaneously for different column<\/li>\r\n<\/ul>\r\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 select *\u00a0 from customer<span style=\"font-size: 1em;text-align: initial\">\u00a0 order by ccity , cname desc ;<\/span><\/p>\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li style=\"text-align: justify\">\u00a0The order by clause can be used with expression also. For example, the below code display records based on combination of student name and email.<\/li>\r\n \t<li style=\"text-align: justify\">Null values appears first in sort order irrespective of ascending or descending order.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">select cid, concat(cname,' ',cemail) as \"Customer EMail\"<\/p>\r\n<p style=\"text-align: center\">from customer<\/p>\r\n<p style=\"text-align: center\">order by \"Customer Email\";<\/p>\r\n&nbsp;\r\n<p style=\"text-align: center\">select cid, cname, ccity,concat(cemail,' ',cmobile) as \"Contact\"<\/p>\r\n<p style=\"text-align: center\">from customer<\/p>\r\n<p style=\"text-align: center\">order by Contact;<\/p>\r\n&nbsp;\r\n<ul>\r\n \t<li style=\"text-align: justify\">The sorting can be done by any column of the base table irrespective of whether it is included in the select clause or not.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: center\">select cid, cname, cemail from customer<\/p>\r\n<p style=\"text-align: center\">order by ccity;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">select cid, cname, ccity<\/p>\r\n<p style=\"text-align: center\">from customer<\/p>\r\n<p style=\"text-align: center\">order by 2,3;<\/p>\r\n&nbsp;\r\n<ul>\r\n \t<li><strong>Limit clause<\/strong><\/li>\r\n \t<li>The limit clause is used to limit specified number of rows of return by result set.<\/li>\r\n \t<li>Limit clause takes one or two integer arguments. The general syntax for limit clause is<\/li>\r\n \t<li>Limit\u00a0 [offset , ]\u00a0 row_count<\/li>\r\n \t<li style=\"text-align: justify\">In case, the limit clause takes single argument, it specifies the maximum row count starting from first row.<\/li>\r\n \t<li>Limit To restrict first two rows, code<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">select *<\/p>\r\n<p style=\"text-align: center\">from customer<\/p>\r\n<p style=\"text-align: center\">limit 2;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">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<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">select *<\/p>\r\n<p style=\"text-align: center\"><span style=\"font-size: 1em\">\u00a0 from customer<\/span><\/p>\r\n<p style=\"text-align: center\"><span style=\"font-size: 1em\">limit 2,3;<\/span><\/p>\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li>Generally, order by clause is used whenever the limit clause is used.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: center\">select *<\/p>\r\n<p style=\"text-align: center\">from customer<\/p>\r\n<p style=\"text-align: center\">order by cmobile<\/p>\r\n<p style=\"text-align: center\">limit 3 offset 2<\/p>\r\n<strong>An alias for column<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">To specify an alias for column name as keyword is used. Although as keyword is optional, it is recommended to use to increase readability.<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n \t<li style=\"text-align: justify\">To use space(s) or special character(s) in an alias, the alias is enclosed between double quotes or single quotes.<\/li>\r\n \t<li style=\"text-align: justify\">For exam, to display column cname as customer name following code need to write:<\/li>\r\n \t<li style=\"text-align: justify\">Select cid, cname as \"Customer Name\"\u00a0 from customer;<\/li>\r\n \t<li style=\"text-align: justify\">In case, an column alias is not included space, As keyword is excluded :<\/li>\r\n \t<li style=\"text-align: justify\">select cid, cname as \"Customer Name\", ccity CustomerCity from customer;<\/li>\r\n \t<li style=\"text-align: justify\">Following code shows that as keyword is not included for cid.<\/li>\r\n \t<li style=\"text-align: justify\">select cid 'customer id', cname as \"Customer Name\", ccity as CustomerCity from customer;<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\"><strong>\u00a0 \u00a0 \u00a0 \u00a0Test expression without from clause<\/strong><\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">In MySQL, to code from clause is not compulsory to test expression that include arithmetic operators and functions.<\/li>\r\n<\/ul>\r\nselect 1000*(1+.1) as \"10% More than 1000\";\r\n<ul>\r\n \t<li style=\"text-align: justify\">The CURRENT_DATE( ) function returns the current date. The parentheses are optional for it. select CURRENT_DATE() as \"Current Date\";<\/li>\r\n \t<li style=\"text-align: justify\">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.<\/li>\r\n<\/ul>\r\n<\/div>\r\n<div>\r\n<p style=\"text-align: center\">select CURRENT_DATE as \"Current Date\",<\/p>\r\n&nbsp;\r\n\r\nDATE_FORMAT(CURRENT_DATE,'%d-%m-%Y') as \"Formatted Date in dd-mm-YYYY\r\n\r\n&nbsp;\r\n\r\nformat\";\r\n<ul>\r\n \t<li>CONCAT function is used to concatenate two or more strings.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: justify\">select cid, cname, CONCAT(cid,cname) as \"Concated Student ID and Name\" from customer;<\/p>\r\n&nbsp;\r\n\r\n<strong>Eliminate duplicate records<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">The distinct keyword is used to prevent identical (duplicate) records from being included in the result set.<\/li>\r\n \t<li style=\"text-align: justify\">The distinct key word must be place immediately after the select keyword.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 \u00a0select distinct ccity\u00a0 from customer;<\/p>\r\n&nbsp;\r\n\r\n<strong>Update:<\/strong>\r\n<ul>\r\n \t<li>Update statement is used to update record(s) of the table.<\/li>\r\n \t<li>The syntax to update is:<\/li>\r\n<\/ul>\r\n<p style=\"text-align: center\">Update &lt;table_name&gt;<\/p>\r\n<p style=\"text-align: center\">Set columnName1 = value1 [,<\/p>\r\n<p style=\"text-align: center\">columnName2 = value2 , \u2026]<\/p>\r\n<p style=\"text-align: center\">[where searchCondition]<\/p>\r\n\r\n<ul>\r\n \t<li>The value for a column can be literal or an expression.<\/li>\r\n \t<li>In the where clause, you can specify the conditions that must be met for a row to be updated.<\/li>\r\n \t<li>In case, where clause is not used, the update statement will update all the records of the table.<\/li>\r\n \t<li>To update city for all customer as surat code<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">update customer<\/p>\r\n<p style=\"text-align: center\">set ccity = 'Surat';<\/p>\r\n\r\n<ul>\r\n \t<li>To update specified row i.e. update city for cid 3,<\/li>\r\n<\/ul>\r\n<\/div>\r\n<p style=\"text-align: center\">update customer<\/p>\r\n<p style=\"text-align: center\">set ccity = 'Rajkot'<\/p>\r\n<p style=\"text-align: center\">where cid = 3;<\/p>\r\n&nbsp;\r\n<ul>\r\n \t<li>Now cid 1 and 2 have ccity is surat and cid is Rajkot.<\/li>\r\n<\/ul>\r\n<strong>\u00a0 \u00a0 Delete:<\/strong>\r\n<ul>\r\n \t<li>Delete statement is used to delete record(s) from the table.<\/li>\r\n \t<li>The general syntax to delete is:<\/li>\r\n<\/ul>\r\n<p style=\"text-align: center\">Delete from &lt;table_name&gt;<\/p>\r\n<p style=\"text-align: center\">[Where searchCondition]<\/p>\r\n\r\n<ul>\r\n \t<li>In case, where clause is not used, the delete statement will delete all the records of the table.<\/li>\r\n \t<li>To delete all records whose city is surat<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">delete from customer<\/p>\r\n<p style=\"text-align: center\">where ccity = 'surat';<\/p>\r\n&nbsp;\r\n<ul>\r\n \t<li>Above delete statement, delete 2 records from customer table<\/li>\r\n \t<li>In case where criteria are not used, then the delete statement will delete all the records.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\n<strong>References:<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\r\n<p style=\"text-align: justify\">2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\r\n<p style=\"text-align: justify\">3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\r\n<p style=\"text-align: justify\">4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O'Reilly Media<\/p>\r\n<p style=\"text-align: justify\">5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\r\n<p style=\"text-align: justify\">6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\r\n<p style=\"text-align: justify\">7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\r\n<p style=\"text-align: justify\">8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\r\n<p style=\"text-align: justify\">9. Joel Murach, \u201cMurach\u2019s MySQL\u201d, Shroff\/Murach<\/p>\r\n<p style=\"text-align: justify\">10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\r\n<p style=\"text-align: justify\">11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\r\n<p style=\"text-align: justify\">12. http:\/\/www.php.net\/<\/p>\r\n<p style=\"text-align: justify\">13. http:\/\/www.w3schools.com\/<\/p>\r\n<p style=\"text-align: justify\">14. http:\/\/www.tutorialspoint.com\/<\/p>","rendered":"<div>\n<p>&nbsp;<\/p>\n<p><strong>Objectives:<\/strong><\/p>\n<ul>\n<li><strong>Use of select statement retrieve data from single table<\/strong><\/li>\n<li><strong>Use of insert, update and delete statement<\/strong><\/li>\n<\/ul>\n<p><strong>\u00a0 \u00a0 Insert:<\/strong><\/p>\n<ul>\n<li>Insert statement is used to add new record into the table.<\/li>\n<li>The general syntax for insert is:<\/li>\n<\/ul>\n<p>Insert into &lt;tableName&gt;\u00a0 [(column list)]<\/p>\n<p>Values(value for column1 [, value for column2]\u2026)<\/p>\n<ul>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">DEFAULT key word is used to insert default value into column. Default key word is used if a column is defined with default value.<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">Example:<\/li>\n<\/ul>\n<p>Consider we have create table customer as follow.<\/p>\n<p>create table customer<\/p>\n<p>(cid int auto_increment unique,<\/p>\n<p>cname varchar(50),<\/p>\n<p>ccity varchar(50) default &#8216;Ahmedabad&#8217;,<\/p>\n<p>cemail varchar(60),<\/p>\n<p>cmobile varchar(11));<\/p>\n<ul>\n<li>To insert a record into customer table for all columns,<\/li>\n<\/ul>\n<p>insert into customer values(1,&#8217;Mahesh&#8217;,DEFAULT,&#8217;mp123@yahoo.com&#8217;,&#8217;9876543210&#8242;);<\/p>\n<\/div>\n<div>\n<ul>\n<li>To insert a record which has value for few columns<\/li>\n<\/ul>\n<p>Insert into customer(cid,cname) values (2,&#8217;Rajesh&#8217;);<\/p>\n<ul>\n<li>Then Ccity is Ahmedabad, cemail is null and cmobile is null.<\/li>\n<li>If you write\u00a0 insert into customer(cname,ccity,cmobile) values(&#8216;Vimal&#8217;,&#8217;Rajkot&#8217;, &#8216;1234567890&#8217;);<\/li>\n<li>Then it is considered cid value as 3 because it is defined as auto_increment and cemail is null.<\/li>\n<\/ul>\n<p><strong>\u00a0 \u00a0Select:<\/strong><\/p>\n<ul>\n<li>The select statement is used to retrieve the data stored in the specified table.<\/li>\n<li>The select statement shows data of columns specified in column-list of the table specified in from clause.<\/li>\n<li>The simplified syntax of select statement is:<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Select &lt;column-list separated by ,&gt;<\/p>\n<p>From &lt;tableName&gt;<\/p>\n<p>[where searchCondition]<\/p>\n<p>[Order by &lt;order by list&gt;]<\/p>\n<p>[limit\u00a0 &lt;row limit&gt;]<\/p>\n<ul>\n<li style=\"text-align: justify\">The code must follow the clauses order shown in simplified syntax for select statement.<\/li>\n<li style=\"text-align: justify\">To view, all the records from the table customer, code<\/li>\n<\/ul>\n<p>select * from customer;<\/p>\n<ul>\n<li>\u00a0To view only few columns of customer table, code<\/li>\n<\/ul>\n<p>select cid, cname, ccity from customer;<\/p>\n<ul>\n<li>You can code column name in any order you would like to view it.<\/li>\n<\/ul>\n<p>select cname, cemail, cid from customer;<\/p>\n<ul>\n<li><strong>Where clause with select statement<\/strong><\/li>\n<li style=\"text-align: justify\">The where clause is used to list selected records. The general syntax of where clause is used with comparison operator is<\/li>\n<\/ul>\n<\/div>\n<p>&nbsp;<\/p>\n<div>\n<p style=\"text-align: justify\">\u00a0 \u00a0Where &lt;expression1&gt; operator\u00a0 &lt;expression2&gt;<\/p>\n<p style=\"text-align: justify\">o\u00a0\u00a0 The comparison operators:<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 =\u00a0 (Equal)<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0 &lt;<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &gt;<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &lt;=<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &gt;=<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 &lt; &gt; (Not equal)<\/p>\n<p style=\"text-align: justify\">\u00a7\u00a0\u00a0\u00a0 !=\u00a0 (Not equal)<\/p>\n<ul>\n<li style=\"text-align: justify\">\u00a0To compare string literal or date literal, string literal or date literal is enclosed between quotes.<\/li>\n<li style=\"text-align: justify\">To list selected records, search criteria is used with where clause. For example, to display all the student whose city is Ahmedabad code<\/li>\n<\/ul>\n<p>select * from customer\u00a0 where ccity = &#8216;Ahmedabad&#8217;;<\/p>\n<ul>\n<li>To display specified columns with specific search criteria, list required column name and write search criteria<\/li>\n<\/ul>\n<p>select cname,cmobile from customer where ccity = &#8216;Ahmedabad&#8217;;<\/p>\n<ul>\n<li style=\"text-align: justify\">Character comparisons performed on data of MySQL database are not case sensitive.\u00a0 So, \u2018Ahmedabad\u2019 and \u2018ahmedabad\u2019 are considered equivalent. For example, following both queries return the same result set.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>select * from customer where ccity = &#8216;Ahmedabad&#8217;;<\/p>\n<p>select * from customer where ccity = &#8216;ahmedabad&#8217;;<\/p>\n<\/div>\n<ul>\n<li><span style=\"text-align: initial;font-size: 1em\">Numerical literal is compared without quotes.<\/span><\/li>\n<\/ul>\n<div>\n<p>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0select *\u00a0 from customer where cid &gt; 1;<\/p>\n<ul>\n<li style=\"text-align: justify\">If the result of comparison is true value then the row(s) being tested is included in the result set.<\/li>\n<li style=\"text-align: justify\">If the result of comparison is false value or null value then the row(s) being tested is not included in the result set.<\/li>\n<\/ul>\n<\/div>\n<div>\n<p>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 select * from customer\u00a0 where ccity = &#8216;Valsad&#8217;;<\/p>\n<ul>\n<li style=\"text-align: justify\">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.<\/li>\n<\/ul>\n<p>select cid, cname from customer where ccity = &#8216;Ahmedabad&#8217;;<\/p>\n<ul>\n<li style=\"text-align: justify\">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 (\u2018\u2019) for string value.<\/li>\n<\/ul>\n<p>select * from customer where cemail = NULL;<\/p>\n<ul>\n<li style=\"text-align: justify\">Is null clause is used to test for null values.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0 select *\u00a0from customer where cemail is null;<\/p>\n<ul>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">The logical operators are:<\/li>\n<li style=\"text-align: justify\">And<\/li>\n<li>Or<\/li>\n<li>Not<\/li>\n<li style=\"text-align: justify\">And operator returns true when all the condition is satisfied.<\/li>\n<li style=\"text-align: justify\">OP operator returns true when any one condition is satisfied.<\/li>\n<li style=\"text-align: justify\">Not operator returns negate value means it makes true to false and false to true.<\/li>\n<li style=\"text-align: justify\">The order of precedence for logical operators are:\u00a0 1. Not\u00a0 2. And 3. Or<\/li>\n<li style=\"text-align: justify\">Parentheses are used to override the order of precedence for any expression or clarify the sequence in which the operations are evaluated.<\/li>\n<li style=\"text-align: justify\">AND operator example:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 \u00a0select *\u00a0 from customer where cname = &#8216;Vimal&#8217; and ccity = &#8216;Ahmedabad&#8217;;<\/p>\n<p>\u00a0 \u00a0OR operator example<\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 select *\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer W<\/span><span style=\"text-align: initial;font-size: 1em\">here cname = &#8216;Vimal&#8217; or ccity = &#8216;Ahmedabad&#8217;;<\/span><\/p>\n<ul style=\"text-align: justify\">\n<li><span style=\"text-align: initial;font-size: 1em\">Not operator<\/span><\/li>\n<\/ul>\n<\/div>\n<div>\n<p style=\"text-align: justify\">\u00a0 \u00a0 select * from customer where ccity &lt;&gt; &#8216;Ahmedabad&#8217;;<\/p>\n<ul style=\"text-align: justify\">\n<li>Compound statement example<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 select * from customer where ccity = &#8216;Rajkot&#8217; OR\u00a0 cname != &#8216;Vimal&#8217;;<\/p>\n<ul>\n<li style=\"text-align: justify\"><strong>In keyword<\/strong><\/li>\n<li style=\"text-align: justify\">In keyword is used to test whether an expression is equal to a value in the list of expressions.<\/li>\n<li style=\"text-align: justify\">The order of expression is insignificant in the list of expression when used with in keyword.<\/li>\n<\/ul>\n<p>select *\u00a0 from customer\u00a0 where ccity in (&#8216;Rajkot&#8217;,&#8217;Surat&#8217;);<\/p>\n<ul>\n<li style=\"text-align: justify\">Not operator can be used to test for an expression which are not in the list of expressions.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 \u00a0 select *\u00a0 from customer\u00a0 where cid not in (1,3,5);<\/p>\n<ul>\n<li style=\"text-align: justify\">The same expression can be written using or operator which is written by using in operator.<\/li>\n<\/ul>\n<p>select * from customer where ccity = &#8216;rajkot&#8217; or ccity = &#8216;surat&#8217;;<\/p>\n<p>select * from customer where cid &lt;&gt; 1 and cid &lt;&gt; 3 and cid &lt;&gt; 5;<\/p>\n<ul>\n<li><strong>Between keyword<\/strong><\/li>\n<li style=\"text-align: justify\">The between keyword is used to test whether an expression falls within a range of values.<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">The lower limit and upper limit are inclusive in between clause.<\/li>\n<\/ul>\n<\/div>\n<div>\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0select *\u00a0\u00a0from customer\u00a0where cid between 2 and 4;<\/p>\n<ul>\n<li>Not operator can be used with between keyword.<\/li>\n<\/ul>\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0select *\u00a0 \u00a0from customer\u00a0 where cid not between 2 and 4;<\/p>\n<ul>\n<li style=\"text-align: left\"><strong>Like and Regexp (Regular Expression) operators<\/strong><\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">The mask used with Like keyword can contain special symbol. The special symbol is called wildcard.<\/li>\n<li style=\"text-align: justify\">Wild cards used with like:<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-226 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110.png\" alt=\"\" width=\"665\" height=\"221\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110.png 665w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110-300x100.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110-65x22.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110-225x75.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-110-350x116.png 350w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/p>\n<ul>\n<li style=\"text-align: justify\">The mask used for regexp operator can have special characters and constructs. The mask used in regexp is not case sensitive.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<table class=\"aligncenter\" style=\"height: 253px\">\n<tbody>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 147.063px\"><strong>Character\/Construct<\/strong><\/td>\n<td style=\"height: 28px;width: 322.063px\"><strong>Description<\/strong><\/td>\n<\/tr>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 147.063px\">^<\/td>\n<td style=\"height: 28px;width: 322.063px\">Matches the pattern to the starting of the string<\/td>\n<\/tr>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 147.063px\">$<\/td>\n<td style=\"height: 28px;width: 322.063px\">Matches the pattern to the ending of the string<\/td>\n<\/tr>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 147.063px\">.<\/td>\n<td style=\"height: 28px;width: 322.063px\">Matches any single character<\/td>\n<\/tr>\n<tr style=\"height: 0px\">\n<td style=\"height: 57px;width: 147.063px\" rowspan=\"2\">[characterlist]<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 57px;width: 322.063px\" rowspan=\"2\">Matches any single character listed within the<\/p>\n<p>character list mentioned in brackets.<\/td>\n<\/tr>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 147.063px\" rowspan=\"2\">[char1-char2]<\/p>\n<p>&nbsp;<\/td>\n<td style=\"height: 28px;width: 322.063px\" rowspan=\"2\">Matches any single character\u00a0 within the range<\/p>\n<p>specified between char1 and char2.<\/td>\n<\/tr>\n<tr style=\"height: 28px\">\n<td style=\"height: 28px;width: 147.063px\">|<\/td>\n<td style=\"height: 28px;width: 322.063px\">Matches either left side or right side pattern<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">\u00a0 \u00a0 select *\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where ccity regexp &#8216;Ah&#8217;;<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select *\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cemail regexp &#8216;^vh&#8217;;<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select * F<\/span><span style=\"text-align: initial;font-size: 1em\">rom customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cmobile regexp &#8216;987[246]&#8217;;<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select *\u00a0\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cemail regexp &#8216;v[a-h]&#8217;;<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Select *\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">from customer\u00a0<\/span><span style=\"text-align: initial;font-size: 1em\">where cname regexp &#8216;ah|aj&#8217;;<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Not operator can be used with like or regexp keyword.<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: justify;font-size: 1em\">As the like and regexp operator significantly degrade the performance compared to other types of searches, use them only when necessary.<\/span><\/p>\n<\/div>\n<div>\n<p><strong>\u00a0 \u00a0 \u00a0Order by clause<\/strong><\/p>\n<ul>\n<li>To display records in particular record, order by clause is used.<\/li>\n<\/ul>\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 select *\u00a0from customers\u00a0order by cmobile;<\/p>\n<ul>\n<li style=\"text-align: justify\">Order can be ascending or descending. By default it is ascending. To display in descending order type desc after column name.<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<\/ul>\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 select *\u00a0 from customer\u00a0 order by cname desc;<\/p>\n<ul>\n<li>You can code ascending and descending code simultaneously for different column<\/li>\n<\/ul>\n<p style=\"text-align: left\">\u00a0 \u00a0 \u00a0 \u00a0 select *\u00a0 from customer<span style=\"font-size: 1em;text-align: initial\">\u00a0 order by ccity , cname desc ;<\/span><\/p>\n<\/div>\n<div>\n<ul>\n<li style=\"text-align: justify\">\u00a0The order by clause can be used with expression also. For example, the below code display records based on combination of student name and email.<\/li>\n<li style=\"text-align: justify\">Null values appears first in sort order irrespective of ascending or descending order.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">select cid, concat(cname,&#8217; &#8216;,cemail) as &#8220;Customer EMail&#8221;<\/p>\n<p style=\"text-align: center\">from customer<\/p>\n<p style=\"text-align: center\">order by &#8220;Customer Email&#8221;;<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">select cid, cname, ccity,concat(cemail,&#8217; &#8216;,cmobile) as &#8220;Contact&#8221;<\/p>\n<p style=\"text-align: center\">from customer<\/p>\n<p style=\"text-align: center\">order by Contact;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li style=\"text-align: justify\">The sorting can be done by any column of the base table irrespective of whether it is included in the select clause or not.<\/li>\n<\/ul>\n<p style=\"text-align: center\">select cid, cname, cemail from customer<\/p>\n<p style=\"text-align: center\">order by ccity;<\/p>\n<ul>\n<li style=\"text-align: justify\">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.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">select cid, cname, ccity<\/p>\n<p style=\"text-align: center\">from customer<\/p>\n<p style=\"text-align: center\">order by 2,3;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li><strong>Limit clause<\/strong><\/li>\n<li>The limit clause is used to limit specified number of rows of return by result set.<\/li>\n<li>Limit clause takes one or two integer arguments. The general syntax for limit clause is<\/li>\n<li>Limit\u00a0 [offset , ]\u00a0 row_count<\/li>\n<li style=\"text-align: justify\">In case, the limit clause takes single argument, it specifies the maximum row count starting from first row.<\/li>\n<li>Limit To restrict first two rows, code<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">select *<\/p>\n<p style=\"text-align: center\">from customer<\/p>\n<p style=\"text-align: center\">limit 2;<\/p>\n<ul>\n<li style=\"text-align: justify\">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<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">select *<\/p>\n<p style=\"text-align: center\"><span style=\"font-size: 1em\">\u00a0 from customer<\/span><\/p>\n<p style=\"text-align: center\"><span style=\"font-size: 1em\">limit 2,3;<\/span><\/p>\n<\/div>\n<div>\n<ul>\n<li>Generally, order by clause is used whenever the limit clause is used.<\/li>\n<\/ul>\n<p style=\"text-align: center\">select *<\/p>\n<p style=\"text-align: center\">from customer<\/p>\n<p style=\"text-align: center\">order by cmobile<\/p>\n<p style=\"text-align: center\">limit 3 offset 2<\/p>\n<p><strong>An alias for column<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">To specify an alias for column name as keyword is used. Although as keyword is optional, it is recommended to use to increase readability.<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<li style=\"text-align: justify\">To use space(s) or special character(s) in an alias, the alias is enclosed between double quotes or single quotes.<\/li>\n<li style=\"text-align: justify\">For exam, to display column cname as customer name following code need to write:<\/li>\n<li style=\"text-align: justify\">Select cid, cname as &#8220;Customer Name&#8221;\u00a0 from customer;<\/li>\n<li style=\"text-align: justify\">In case, an column alias is not included space, As keyword is excluded :<\/li>\n<li style=\"text-align: justify\">select cid, cname as &#8220;Customer Name&#8221;, ccity CustomerCity from customer;<\/li>\n<li style=\"text-align: justify\">Following code shows that as keyword is not included for cid.<\/li>\n<li style=\"text-align: justify\">select cid &#8216;customer id&#8217;, cname as &#8220;Customer Name&#8221;, ccity as CustomerCity from customer;<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>\u00a0 \u00a0 \u00a0 \u00a0Test expression without from clause<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">In MySQL, to code from clause is not compulsory to test expression that include arithmetic operators and functions.<\/li>\n<\/ul>\n<p>select 1000*(1+.1) as &#8220;10% More than 1000&#8221;;<\/p>\n<ul>\n<li style=\"text-align: justify\">The CURRENT_DATE( ) function returns the current date. The parentheses are optional for it. select CURRENT_DATE() as &#8220;Current Date&#8221;;<\/li>\n<li style=\"text-align: justify\">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.<\/li>\n<\/ul>\n<\/div>\n<div>\n<p style=\"text-align: center\">select CURRENT_DATE as &#8220;Current Date&#8221;,<\/p>\n<p>&nbsp;<\/p>\n<p>DATE_FORMAT(CURRENT_DATE,&#8217;%d-%m-%Y&#8217;) as &#8220;Formatted Date in dd-mm-YYYY<\/p>\n<p>&nbsp;<\/p>\n<p>format&#8221;;<\/p>\n<ul>\n<li>CONCAT function is used to concatenate two or more strings.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">select cid, cname, CONCAT(cid,cname) as &#8220;Concated Student ID and Name&#8221; from customer;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Eliminate duplicate records<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">The distinct keyword is used to prevent identical (duplicate) records from being included in the result set.<\/li>\n<li style=\"text-align: justify\">The distinct key word must be place immediately after the select keyword.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 \u00a0select distinct ccity\u00a0 from customer;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Update:<\/strong><\/p>\n<ul>\n<li>Update statement is used to update record(s) of the table.<\/li>\n<li>The syntax to update is:<\/li>\n<\/ul>\n<p style=\"text-align: center\">Update &lt;table_name&gt;<\/p>\n<p style=\"text-align: center\">Set columnName1 = value1 [,<\/p>\n<p style=\"text-align: center\">columnName2 = value2 , \u2026]<\/p>\n<p style=\"text-align: center\">[where searchCondition]<\/p>\n<ul>\n<li>The value for a column can be literal or an expression.<\/li>\n<li>In the where clause, you can specify the conditions that must be met for a row to be updated.<\/li>\n<li>In case, where clause is not used, the update statement will update all the records of the table.<\/li>\n<li>To update city for all customer as surat code<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">update customer<\/p>\n<p style=\"text-align: center\">set ccity = &#8216;Surat&#8217;;<\/p>\n<ul>\n<li>To update specified row i.e. update city for cid 3,<\/li>\n<\/ul>\n<\/div>\n<p style=\"text-align: center\">update customer<\/p>\n<p style=\"text-align: center\">set ccity = &#8216;Rajkot&#8217;<\/p>\n<p style=\"text-align: center\">where cid = 3;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Now cid 1 and 2 have ccity is surat and cid is Rajkot.<\/li>\n<\/ul>\n<p><strong>\u00a0 \u00a0 Delete:<\/strong><\/p>\n<ul>\n<li>Delete statement is used to delete record(s) from the table.<\/li>\n<li>The general syntax to delete is:<\/li>\n<\/ul>\n<p style=\"text-align: center\">Delete from &lt;table_name&gt;<\/p>\n<p style=\"text-align: center\">[Where searchCondition]<\/p>\n<ul>\n<li>In case, where clause is not used, the delete statement will delete all the records of the table.<\/li>\n<li>To delete all records whose city is surat<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">delete from customer<\/p>\n<p style=\"text-align: center\">where ccity = &#8216;surat&#8217;;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Above delete statement, delete 2 records from customer table<\/li>\n<li>In case where criteria are not used, then the delete statement will delete all the records.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>References:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\n<p style=\"text-align: justify\">2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\n<p style=\"text-align: justify\">3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\n<p style=\"text-align: justify\">4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O&#8217;Reilly Media<\/p>\n<p style=\"text-align: justify\">5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\n<p style=\"text-align: justify\">6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\n<p style=\"text-align: justify\">7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\n<p style=\"text-align: justify\">8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\n<p style=\"text-align: justify\">9. Joel Murach, \u201cMurach\u2019s MySQL\u201d, Shroff\/Murach<\/p>\n<p style=\"text-align: justify\">10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\n<p style=\"text-align: justify\">11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\n<p style=\"text-align: justify\">12. http:\/\/www.php.net\/<\/p>\n<p style=\"text-align: justify\">13. http:\/\/www.w3schools.com\/<\/p>\n<p style=\"text-align: justify\">14. http:\/\/www.tutorialspoint.com\/<\/p>\n","protected":false},"author":3,"menu_order":22,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-hiren-joshi"],"pb_section_license":""},"chapter-type":[],"contributor":[59],"license":[],"class_list":["post-223","chapter","type-chapter","status-publish","hentry","contributor-dr-hiren-joshi"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/223","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":7,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/223\/revisions"}],"predecessor-version":[{"id":419,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/223\/revisions\/419"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/223\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=223"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=223"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=223"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}