MySQL Operators

OverviewPrecedence of operators and type conversionArithmetic operatorsBitwise operatorsLogical operatorsComparison operatorsMathematical functionsString functionsDate functions

Precedence of operators1.Interval2.binary,collate3.!4.-(unary minus),~(unary bit inversion)5.^6.*,/, div, %, mod7.+,-

8.<<, >>9.&10.|11. =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN12.between,case,when,then,else13.Not14.&&,and15.Xor16.||, or17. :=

Ex:Mysql>select 3-2%1;3Mysql>select 4+5-1/2*2;8.0000Mysql>select 23+13&&13;1

All arithmetic operations are calculated with BIGINT(64-bit) precision.Ex:Mysql>select 4+55;59Mysql>select 4-55;-51;Mysql>select -5;-5 (unary minus)

Mysql>select 122%45;32Mysql>select 100/0;NULLMysql>select 10/3;3.3333Mysql>select 10 div 3;3 (integer division)

All the operands should be numerals, but they are internally represented as binary.Left shift(<<)Mysql>select 4<<2;16Right shift(>>)Mysql>select 4>>2;1

Bitwise OR(|)Mysql>select 23|12;31Bitwise AND(&)Mysql>select 23&12;4Bitwise XOR(^)Mysql>select 1^1;0

Mysql>select 0^1;1Mysql>select 0^0;0Bitwise NOTMysql>select 5&~1;4Mysql>select 6&~6;0

Ex:Mysql>create table employee(Ssn INT NOT NULL,Salary INT,Name VARCHAR(20),Department VARCHAR(20),Primary key(ssn));Mysql>insert into employeeValues(100,10000,’sneha’,’finance’);

Mysql>insert into employeeValues(200,20000,’shalini’,’hr’);Mysql>insert into employeeValues(300,30000,’john’,’finance’);Mysql>insert into employeeValues(400,40000,’jack’,’hr’);

Mysql>select * from employee;

Logical ANDMysql>select ssn, name from employee where salary>10000 && salary<40000;

Logical ORmysql>select name, salary, ssn from employee whereName like ‘%s’ || department like ‘%f’;

Logical NOTmysql>select name, department from employee where ssn!=100;

Comparison operatorsThese operators return 1 if true else 0 if the condition is false

Returns 1 if the condition is TRUE and returns 0 if the condition is FALSEMysql>select 2.34<=2.34;1Mysql>select 2.34<=1.24;0Mysql>select 4.2>=4;1

Mysql>select 10!=10;oMysql>select 5!=10;1Mysql> select 3 between 1 and 4;1Mysql>select greatest(10,20,99);99

Mysql>select cos(89);0.510177044941669Mysql>select atan(90);1.55968567289729Mysql>select floor(1.34);1Mysql>select ceil(1.34);2Mysql>select format(234567.34356,3)234,567.344

Mysql>select pi();3.141593Mysql>select radians(90);1.5707963267949Mysql>select degrees(1.57);89.9543738355392Mysql>select truncate(4.34,1);4.3Mysql>select truncate(23.22,-1);20

Mysql>select concat(“winners”, ”attitude”);winnersattitudeMysql>select concat_ws(‘$’,’pope’,’ john’, ’paul’);pope$john$paul(concats the strings by placing the separator in between the strings)Mysql>select char_length(‘mysql’);5

Mysql>select lcase(‘OPERATORS’);operatorsMysql>select length(‘personalexcellence’);18Mysql>select locate(‘point’, ’powerpoint’);6(returns the position of the substring in the mainstring)

Mysql>select repeat(‘win’,3);winwinwinMysql>select replace(‘myindia’, ’my’, ’our’);ourindiaMysql>select reverse(‘apple’);elppa

Mysql>select substring(‘mysql’,3);sql(returns the substring at the given position from the main string)Mysql>select format(233444.564678,3);233,444.565(formats the given string and rounds to the given digits after the decimal point)

Ex:Mysql>select curdate();2009-12-29Mysql>select curtime();13:04:21Mysql>select now();2009-12-29 13:05:12

Mysql>select monthname(‘2009-10-09’);octoberMysql>select month(‘2009-10-09’);10 (returns the month in numeric format)Mysql>select minute(‘13:05:12’);5Mysql>select hour(‘13:05:12’);13

Mysql>select dayname(‘2010-01-01’);FridayMysql>select dayofmonth(‘2010-01-01’);1 (returns the day in the range 1 to 31)Mysql>select dayofweek(‘2010-21-01’):5 (returns the day in the range 1 to 7)

Mysql>select datediff(‘2009-1-1’,’2009-10-10’);83 (returns the difference between the dates in days)Mysql>select date_add(‘2009-12-29’,interval 3 day);2010-01-01(returns the new date formed after adding the number of days mentioned in the interval)

SOME OF THE APPLICATIONS OF MYSQL OPERATORS;1.operators and functions are used for performing calculations on stored values.2.High performance text search, field search and text editing applications.3.NAND,NOR AND XOR operators are are used in electrical applications.

Visit more self help tutorialsPick a tutorial of your choice and browse through it at your own pace.The tutorials section is free, self-guiding and will not involve any additional support.Visit us at www.dataminingtools.net

MySQL Operators

More Related Content

MySQL Operators