Uploaded byDataminingTools Inc
Mysql:Operators
This document discusses MySQL operators including: - Precedence of operators and type conversion - Arithmetic, bitwise, logical, and comparison operators - Mathematical, string, and date functions It provides examples of using various operators and functions to perform calculations on values, with applications including calculations on stored data, text search/editing, and electrical applications.
Mysql:Operators
- 1.
- 2. OverviewPrecedence of operatorsand type conversionArithmetic operatorsBitwise operatorsLogical operatorsComparison operatorsMathematical functionsString functionsDate functions
- 3. Precedence of operators1.Interval2.binary,collate3.!4.-(unaryminus),~(unary bit inversion)5.^6.*,/, div, %, mod7.+,-
- 4. 8.<<, >>9.&10.|11. =,<=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN12.between,case,when,then,else13.Not14.&&,and15.Xor16.||, or17. :=
- 5. Ex:Mysql>select 3-2%1;3Mysql>select 4+5-1/2*2;8.0000Mysql>select 23+13&&13;1
- 6.
- 7. All arithmetic operationsare calculated with BIGINT(64-bit) precision.Ex:Mysql>select 4+55;59Mysql>select 4-55;-51;Mysql>select -5;-5 (unary minus)
- 8. Mysql>select 122%45;32Mysql>select 100/0;NULLMysql>select10/3;3.3333Mysql>select 10 div 3;3 (integer division)
- 9.
- 10. All the operandsshould be numerals, but they are internally represented as binary.Left shift(<<)Mysql>select 4<<2;16Right shift(>>)Mysql>select 4>>2;1
- 11. Bitwise OR(|)Mysql>select 23|12;31BitwiseAND(&)Mysql>select 23&12;4Bitwise XOR(^)Mysql>select 1^1;0
- 12. Mysql>select 0^1;1Mysql>select 0^0;0BitwiseNOTMysql>select 5&~1;4Mysql>select 6&~6;0
- 13.
- 14. Ex:Mysql>create tableemployee(Ssn INT NOT NULL,Salary INT,Name VARCHAR(20),Department VARCHAR(20),Primary key(ssn));Mysql>insert into employeeValues(100,10000,’sneha’,’finance’);
- 15. Mysql>insert into employeeValues(200,20000,’shalini’,’hr’);Mysql>insertinto employeeValues(300,30000,’john’,’finance’);Mysql>insert into employeeValues(400,40000,’jack’,’hr’);
- 16. Mysql>select * fromemployee;
- 17. Logical ANDMysql>select ssn,name from employee where salary>10000 && salary<40000;
- 18. Logical ORmysql>select name,salary, ssn from employee whereName like ‘%s’ || department like ‘%f’;
- 19. Logical NOTmysql>select name,department from employee where ssn!=100;
- 20. Comparison operatorsThese operatorsreturn 1 if true else 0 if the condition is false
- 21. Returns 1if 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
- 22. Mysql>select 10!=10;oMysql>select 5!=10;1Mysql>select 3 between 1 and 4;1Mysql>select greatest(10,20,99);99
- 23.
- 26. 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
- 27. Mysql>select pi();3.141593Mysql>select radians(90);1.5707963267949Mysql>selectdegrees(1.57);89.9543738355392Mysql>select truncate(4.34,1);4.3Mysql>select truncate(23.22,-1);20
- 28.
- 29. Mysql>select concat(“winners”, ”attitude”);winnersattitudeMysql>selectconcat_ws(‘$’,’pope’,’ john’, ’paul’);pope$john$paul(concats the strings by placing the separator in between the strings)Mysql>select char_length(‘mysql’);5
- 30. Mysql>select lcase(‘OPERATORS’);operatorsMysql>select length(‘personalexcellence’);18Mysql>select locate(‘point’, ’powerpoint’);6(returns the position of the substring in the mainstring)
- 31. Mysql>select repeat(‘win’,3);winwinwinMysql>select replace(‘myindia’,’my’, ’our’);ourindiaMysql>select reverse(‘apple’);elppa
- 32. Mysql>select substring(‘mysql’,3);sql(returns thesubstring 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)
- 33.
- 34. Ex:Mysql>select curdate();2009-12-29Mysql>select curtime();13:04:21Mysql>selectnow();2009-12-29 13:05:12
- 35. 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
- 36. 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)
- 37. 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)
- 38. SOME OF THEAPPLICATIONS 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.
- 39. Visit more selfhelp 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