UPDATE Statement :
The UPDATE query is used to change or modify the existing values in a table.
The Update Syntax is
UPDATE tbl_name SET
col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition];
The UPDATE query updates the columns of existing rows in a table with the new values. The SET clause is used to indicate which columns to be modified. The WHERE clause is used to specify the conditions that identify which rows to be updated.
The following example will set the address of the student to a new address.
mysql> update student set address='welling street' where
address='victoria street';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
But this will set all the address of the students who ever lives in victoria street will be changed to welling street.
Suppose if we want to set the address of a single student to a new address then we can choose the below option.
mysql> update student set address='welling street' where name='jack';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
If we want to change a students mark we can use the below statement.
mysql> update student set marks=100 where name='david';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
This can also be rewritten as the following.
mysql> update student set marks=marks+2 where name='david';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
In UPDATE statement we can also use the arithmetic operations
Operator Precedence:
Operators are used to operate with two operands. Wide collection of Operators are available in MySQL. The operator precedences are shown below in the table.
Highest Precedence :=
1 , OR, XOR
2 &&, AND
3 BETWEEN, CASE, WHEN, THEN, ELSE
4 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
5
6 &
7 <<, >>
8 -, +
9 *, /, DIV, %, MOD
10 ^
11 - (unary minus), ~ (unary bit inversion)
12 !, NOT
Lowest BINARY, COLLATE
If a statement contains paranthesis, then the operations inside the paranthesis are performed first. These operators will be explained in the following sections.
Type Conversion :
Type conversion takes place when operators are used with different types of operands in an expression. Some conversions are done implicitly and some need explicit conversions.
In MySQL the numbers are converted to strings and sometimes strings to numbers depending upon the condition.
Let us consider an example for converting a string to an integer.
mysql> Select 1+'11';
--> 12
Here the string '11' is converted to a number and the result of the expression is also a number.
Lets see another example for converting an integer to a string.
mysql> select concat(1, ' HIOX');
--> '1 HIOX'
We can convert or casting a number to a string explicitly. Here we use CAST() or CONCAT() function.
mysql> select 12, cast(12 as char);
--> 12, '12'
mysql> select 12, concat(12);
--> 12, '12'
Logical Operator :
MySQL supports the following logical operations :
AND(&&) Operator
OR() Operator
NOT(!) Operator
AND(&&) Operator :
The logical AND(&&) operator indicates whether the both operands are true. Lets see a statement using AND operator.
mysql> select studid, name from student where marks > 80
and marks <> select studid, name from student where marks > 80
&& marks <> select name, marks, address from student where
name like 'a%' or name like 's%';(or)
mysql> select name, marks, address from student where
name like 'a%' name like 's%';
+-------+-------+------------------+
name marks address
+-------+-------+------------------+
steve 100 5th cross street
anne 100 downing street
steve 75 downing street
anne 80 edinburgh
+-------+-------+------------------+
4 rows in set (0.00 sec)
In the above statement it will list the name, marks and address of the student whose name starts with the letter A and S.
NOT(!) Operator :
The logical NOT(!) operator have only one operand and it returns the inverse of the value.
mysql> select * from student where not (studid=1);(or)
mysql> select * from student where ! (studid=1);
+--------+-------+-------+-----------------+---------+
studid name marks address phone
+--------+-------+-------+-----------------+---------+
2 david 100 welling street 547896
4 jack 82 welling street 2436821
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
8 mille 98 victoria street 1236547
+--------+-------+-------+-----------------+---------+
6 rows in set (0.00 sec)
It will list all the student details except the studid 1.
Comparison Operator :
Comparison operator is used to compare expressions or values. The result of the comparison will be either True(1) or False(0). MySQL supports the following comparison operators :
EQUAL(=)
LESS THAN(<) LESS THAN OR EQUAL(<=) GREATER THAN(>)
GREATER THAN OR EQUAL(>=)
NOT EQUAL(<>,!=)
BETWEEN
GREATEST
= :
Equal.
mysql> select 1 = 0;
--> 0
mysql> select 0.0 = 0;
--> 1
< : Less than. mysql> select 4.5 <> 1
mysql> select 1.1 <> 0
<= : Less than or equal. mysql> select 2.2 <= 2.2; --> 1
mysql> select 2.2 <= 2.1; --> 0
> :
Greater than.
mysql> select 7 > 2;
--> 1
mysql> select 4 > 4.1;
--> 0
>= :
Greater than or equal.
mysql> select 10 >= 10;
--> 1
mysql> select 4.4 >= 4.5;
--> 0
<>, != :
Not equal.
mysql> select 8 <> 8;
--> 0
mysql> select 7 != 7.7;
--> 1
expr BETWEEN min AND max :
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0.
mysql> select 5 between 5 and 6;
--> 1
mysql> select 'N' between 'M' and 'O';
--> 1
This is same for the expr NOT BETWEEN min AND max, but Not.
GREATEST(value1,value2,...) :
This operator returns the largest argument, compared with two or more arguments.
mysql> select greatest('N', 'M', 'O');
--> O
mysql> select greatest(1, 2);
--> 2
The same rule is applied in finding the LEAST().
Numeric Functions :
Numeric function consists of two main sections. They are :
Arithmetic Operations
Mathematical Functions
Now, lets first discuss about Arithmetic operations.
Arithmetic Operations :
In MySQL, we have the usual Arithmetic operations. Lets see the arithmetic operators one by one with an example.
Addition (+) :
mysql> select 5+5;
--> 10
Subtraction (-) :
mysql> select 25-18;
--> 7
Multiplication (*) :
mysql> select 4*4;
--> 16
Division (/) :
mysql> select 5/3;
--> 1.67
Next lets move to the Mathematical functions
Mathematical Functions :
ABS ACOS ASIN ATAN CEIL
COS COT DEGREES EXP FLOOR
FORMAT
LN LOG LOG(B,X) LOG2
LOG10 MOD PI POWER RADIANS
RAND ROUND SIGN SIN SQRT
TAN TRUNCATE
Now lets see some Mathematical functions with an example.
ABS(X) :
This function returns the Absolute value of the given value.
mysql> select abs(5);
--> 5
mysql> select abs(-25);
--> 25
ACOS(X) :
Returns the arc Cosine value of the given number.
mysql> select acos(0);
--> 1.5707963267949
mysql> select acos(1);
--> 0
ASIN(X) :
Returns the arc Sine value of the given number.
mysql> select asin(1);
--> 1.5707963267949
mysql> select asin(0);
--> 0
ATAN(X) :
Returns the arc Tangent value of the given number.
mysql> select atan(1);
--> 0.78539816339745
mysql> select atan(2);
--> 1.1071487177941
CEIL(X) or CEILING(X) :
Returns the smallest integer nearest to the given value but not less than that.
mysql> select ceil(1.14);
--> 2
mysql> select ceiling(-1.14);
--> -1
COS(X) :
Returns the Cosine of X, where X is given in radians.
mysql> select cos(pi());
--> -1
mysql> select cos(0);
--> 1
COT(X) :
Returns the cotangent value of X.
mysql> select cot(1);
--> 0.64209261593433
mysql> select cot(45);
--> 0.61736962378356
Mathematical Functions :
Now lets see some Mathematical functions with an example.
DEGREES(X) :
This function returns the argument X, converted from radians to degrees.
mysql> select degrees(pi());
--> 180
mysql> select degrees(0.78539816339745);
--> 45
EXP(X) :
Returns the value of the base of natural logarithms(e) raised to the power of X.
mysql> select exp(-1);
--> 0.36787944117144
mysql> select exp(1);
--> 2.718281828459
FLOOR(X) :
Returns the largest integer value not greater than X.
mysql> select floor(5.68);
--> 5
mysql> select floor(-1.26);
--> 2
FORMAT(X,D) :
Formats the given number in a ##,###.## format,rounded to D decimal places.
mysql> select format(7895423.23478,2);
--> 7,895,423.23
mysql> select format(1287654.458,0);
--> 1,287,654
LN(X) :
Returns the natural logarithm of X, the base-e logarithm of X.
mysql> select ln(3);
--> 1.0986122886681
mysql> select ln(-1);
--> NULL
LOG(X) :
This function returns the natural logarithm of X, if it contains a single parameter.
mysql> select log(1);
--> 0
mysql> select log(-2);
--> NULL
LOG(B,X) :
If it contains two parameters, then returns the natural logarithm of X for an arbitrary base B .
mysql> select log(3,4512);
--> 7.659204143237
mysql> select log(1,250);
--> NULL
Mathematical Functions :
Now lets see some Mathematical functions with an example.
LOG2(X) :
Returns the base-2 logarithm of X.
mysql> select log2(7215);
--> 12.816783679379
mysql> select log2(-10);
--> NULL
LOG10(X) :
Returns the base-10 logarithm of X.
mysql> select log10(5);
--> 0.69897000433602
mysql> select log10(-5);
--> NULL
MOD(N,M), N % M, N MOD M :
This function returns the remainder of N divided by M.
mysql> select mod(123, 10);
--> 3
mysql> select 111 % 8;
--> 7
mysql> select 45 mod 4;
--> 1
PI() :
Returns the value of ?(pi).
mysql> select pi();
--> 3.141593
POW(X,Y), POWER(X,Y) :
This function returns the value of XY.
mysql> select pow(3,4);
--> 81
mysql> select pow(3,-4);
--> 0.012345679012346
RADIANS(X) :
This function returns the argument X, converted from degrees to radians.
mysql> select radians(30);
--> 0.5235987755983
mysql> select radians(45);
--> 0.78539816339745
RAND(X) :
This function returns the random floating point number between 0 & 1. If a number is given within this function ,it produces repeatable sequence of values.
mysql> select rand();
--> 0.13388662052703
mysql> select rand(10);
--> 0.65705152196535
mysql> select rand(10);
--> 0.65705152196535
mysql> select rand();
--> 0.82382530888899
mysql> select rand();
--> 0.7174667135975
mysql> select rand(10);
--> 0.65705152196535
Mathematical Functions :
Now lets see some Math functions with an example.
ROUND(X), ROUND(X,D) :
Returns the argument X, rounded to the nearest integer. If it contains two arguments, then returns X rounded to D decimal places.
mysql> select round(12.589);
--> 13
mysql> select round(-12.23);
--> -12
mysql> select round(5.258, 1);
--> 5.3
mysql> select round(5.258, -1);
--> 10
SIGN(X) :
Returns the sign of the given number as -1 if negative,0 if zero, or 1 if positive.
mysql> select sign(-89);
--> -1
mysql> select sign(0);
--> 0
mysql> select sign(123);
--> 1
SIN(X) :
Returns the sine value of X,X given in radians.
mysql> select sin(45);
--> 0.85090352453412
mysql> select sin(pi());
--> 1.2246063538224e-016
SQRT(X) :
Returns the square root of the number,the number should be a positive number.
mysql> select sqrt(144);
--> 12
mysql> select sqrt(741);
--> 27.221315177632
TAN(X) :
Returns the tangent of X, where X is given in radians.
mysql> select tan(45);
--> 1.6197751905439
mysql> select sqrt(90);
--> -1.9952004122082
TRUNCATE(X,D) :
The number is truncated to D digits after decimal point. If D is negative, the D digits before the decimal point are converted to 0.
mysql> select truncate(4.556,1);
--> 4.5
mysql> select truncate(444.556,-1);
--> 440
String Function :
Functions are predefined set of instructions that returns a value. Functions which involves strings are called as String functions. There are different types of functions availble in MySQL.
The important string functions are,
CHAR_LENGTH
CONCAT CONCAT_WS FORMAT
LCASE
LENGTH LOCATE REPEAT
REPLACE REVERSE SUBSTRING
CHAR_LENGTH(str) or CHARACTER_LENGTH(str) :
This string function returns the length of the string.
mysql> select char_length("hioxindia");
--> 9
mysql> select character_length("easycalculation");
--> 15
CONCAT(str1,str2,...) :
Returns the concatenated string of the given arguments.
mysql> select concat('hiox','india');
--> 'hioxindia'
CONCAT_WS() :
It stands for Concatenate With Separator and is a special form of CONCAT function. Returns the concatenated string of the given arguments seperated by given seperator.
mysql> SELECT CONCAT_WS('!','One','Two','Three');
--> 'One!Two!Three'
( Here '!' is the seperator)
FORMAT() :
Formats the given no and rounds to the given digits after decimal point.
mysql> SELECT FORMAT(12332.123456, 4);
--> '12,332.1235'
LCASE(str) or LOWER() :
Returns the lowercase of the given string.
mysql> select lcase('HIOX');
--> 'hiox'
mysql> select lower('EASYCALCULATION');
--> easycalculation
Like wise UPPER or UCASE returns the uppercase of the given string.
LENGTH(str) :
Returns the length of the given string in bytes. If there is a 2 byte character the length is calculated as 2. Whereas the CHAR_LENGTH calculates only the character length.
mysql> select length('HIOXINDIA');
--> 9
LOCATE(substr,str) or POSITION(substr IN Str) :
Returns the position of the first occurance of the substring in the string.
mysql> select locate('ind','hioxindia');
--> 5
mysql> select position('cul' in 'easycalculation');
--> 8
REPEAT(str,count) :
The given string is repeated for the given count.
mysql> select repeat('HIOX',2);
--> 'HIOXHIOX'
REPLACE(str,from_str,to_str) :
In the given string 'str' the 'from_str' is replaced by the 'to_str' string.
mysql> select replace('MyMYSql','My','you');
--> youMYSql
The given 'from_str' is case sensitive. Here in the above example the first 'My' is changed but not the second('MY').
REVERSE(str) :
The given string is reversed and returned.
mysql> select reverse('HIOX');
--> 'XOIH'
SUBSTRING(str,pos) :
The function returns a substring from the string 'str' starting at position 'pos'.
mysql> select substring('EASYCALCULATION', 5);
--> 'CALCULATION'
Date and Time Functions :
This function is used to manipulate the display format of a date and time. Lets see some basic functions for date and time.
CURDATE CURTIME DATEDIFF DATE_ADD
DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR
HOUR MINUTE MONTH MONTHNAME
NOW CURDATE() :
This date function returns the current date in the format 'YYYY-MM-DD' or 'YYYYMMDD'.
mysql> select curdate();
--> 2007-01-03
CURTIME() :
Returns the current time in the format 'HH:MM:SS' or 'HHMMSS'.
mysql> select curtime();
--> 17:33:07
DATEDIFF(expression1,expression2) :
expression1 and expression2 are date or date-and-time expressions. This function returns expression1 – expression2 expressed as a value in days from one date to the other. Here only the date parts will be considered for calculation.
mysql> select datediff('2007-2-6 17:33:25','2007-1-1');
--> 36
DATE_ADD(datetime, INTERVAL expression datetimetype) :
This date function adds the expression to the datetime supplied.
mysql> select date_add('2007-1-14', interval 15 day);
--> 2007-01-29
This function is same for DATE_SUB, but subtracting will take place instead of adding.
DAYNAME(date) :
Returns the name of the day for the specified date.
mysql> select dayname('2007-01-04');
--> Thursday
DAYOFMONTH(date) or DAY(date) :
Returns the date for the day of the month in the range of 1 to 31. DAY() is a synonym for DAYOFMONTH().
mysql> select dayofmonth('2007-01-04');
--> 4
DAYOFWEEK(date) :
Returns the day of the week in the numeric format as 1 for Sunday to 7 for Saturday.
mysql> select dayofweek('2007-01-04');
--> 5
DAYOFYEAR(date) :
Returns the day of the year for given date in the numeric format, in the range 1 to 366.
mysql> select dayofyear('2007-07-09');
--> 190
HOUR(time) :
Returns the hour of the specified time in the numeric format from 0 to 23.
mysql> select hour('14:46:12');
--> 14
MINUTE(time) :
Returns the minute of the specified time in the numeric format from 0 to 59.
mysql> select minute('14:46:12');
--> 46
MONTH(date) :
Returns the month for the given date in the numeric format, in the range 0 to 12. mysql> select month('2007-07-09');
--> 7
MONTHNAME(date) :
Returns the name of the month for the specified date.
mysql> select monthname('2007-07-09');
--> July
NOW() :
This date time function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.
mysql> select now();
--> 2007-01-04 14:56:15
