MySQL operator and function (Chubby teacher)

Source: Internet
Author: User
Tags abs mathematical functions rand time and seconds


Use test;
CREATE TABLE ' employee ' (
emp_no int unsigned,
Emp_name varchar (30),
Emp_sex varchar (3),
Emp_age tinyint unsigned,
Sal Double,
History datetime
);
INSERT into employee values (1, ' Zhang San ', ' Male ', 18, 5000, ' 2012-04-23 '),
(2, ' John Doe ', ' Male ', 27, 4500, ' 2013-05-23 '),
(3, ' Harry ', ' Male ', 23, 4700, ' 2012-04-21 '),
(4, ' zilong ', ' Male ', 19, 3800, ' 2011-03-04 '),
(5, ' Li Bai ', ' Male ', 15, 6200, ' 2015-09-09 '),
(6, ' Liu Bei ', ' Male ', 28, 2500, ' 2016-02-11 '),
(7, ' LV bu ', ' male ', 21, 6000, ' 2010-10-18 '),
(8, ' Shang Xiang ', ' female ', 16, 4500, ' 2011-09-26 '),
(9, ' Little Joe ', ' Woman ', ', ', ' 2013-07-05 '),
(10, ' Big Joe ', ' female ', 16, 5000, ' 2017-09-01 ');


Commonly used operators:
1: Equals (=)
SELECT * FROM employee where sal = 3800;
SELECT * FROM employee where sal = null; --No null data is queried here

2: Equals (<=>)
SELECT * FROM employee where Sal <=> 3800;
SELECT * FROM employee where Sal <=> null; --The null data can be queried here

3:is Judgment (NULL)
SELECT * from-employee where Sal is null;
SELECT * FROM employee where SAL is not null;

4:null value can also be used IsNull ();
SELECT * FROM Employee where ISNULL (SAL);
SELECT * FROM Employee where!isnull (SAL);

5: Within the interval (between) between Min and Max PS: Here is a closed interval
SELECT * FROM employee where Sal between 4500 and 5000;

6: Not within the interval
SELECT * FROM employee where Sal is not between 4500 and 5000; --null not included in the

7:and and OR
SELECT * FROM employee where Sal isn't between 4500 and or Sal is null;
SELECT * FROM employee where Sal = 4500 and emp_sex = ' female ';

8: Less than (<), greater than (>), less than or equal (<=), greater than or equal to (>=)
SELECT * FROM employee where Sal >= 4500;
***************************************************************************************************************

Mathematical functions
1:rand ();
Select Rand () from dual; --dual is a pseudo-table
Select from dual;
Select rand (); --Can be shortened

2:least (value1, value2, ...) returns the minimum value
Select least (54,76,4,65,76,87,87,56,65,654,45,23,1,76);
Select Least (54,76,4,65,76,87,87,56,65,654,45,23,1,76) as Min_value; --The column name can be an alias

3:greatest (value1, value2, ...) returns the maximum value
Select Greatest (54,76,4,65,76,87,87,56,65,654,45,23,1,76);

4:round (M, D); Returns the rounded value of M, where D means to keep a few decimals, the default value is 0
Select round (1.69);
Select Round (1.69, 1);

5:abs () absolute value
Select 5-10;
Select ABS (5-10);
***************************************************************************************************************

Summary functions
1:avg ();
SELECT * FROM employee where SAL >= 6000;
Select AVG (SAL) from employee where Sal >= 6000;

2:count ()
Select COUNT (*) from employee;
Select COUNT (emp_name) from employee;
Select count (SAL) from employee; --Print 9 ignores null values here
Select COUNT (*) from employee where Sal >= 4000;
Select COUNT (*) from the employee where Sal <= 4000 or sal is null;

3:sum ()
Select sum (SAL) from employee where Sal >= 6000;

4:min ()
Select min (sal) from employee;

5:max ()
Select Max (SAL) from employee;
***************************************************************************************************************

Date function
1: Get the current date time
Select Now (), Sysdate (), Current_timestamp ();
Select Now (6), sysdate (6), Current_timestamp (6);
Ps:now (), Current_timestamp (), no difference, indicates the time when SQL started executing
Sysdate () indicates the start time of this function

2: Get the current date
Select Curdate (); --Only month and day

3: Get the current time
Select Curtime (); --only time and seconds

4: Addition of the date Date_add
Select History, Date_add (History, Interval ' 1 12:10 ' Day_minute) from employee; --date_add (History, Interval ' 1 12:10 ' Day_minute)
Select History, Date_add (History, Interval ' 1-1 ' Year_month) from employee; --date_add (History, Interval ' 1-1 ' Year_month)
Select History, Date_add (History, Interval ' 1 ' second) from employee; --date_add (History, Interval ' 1 ' second)

5: Subtraction of the date data_sub
Select History, Date_sub (History, Interval ' 1-1 ' Year_month) from employee;

6: Calculated date Difference
Select History, Sysdate (), DateDiff (Sysdate (), history) from employee; --expressed in days

7: Gets the specified part of the date (converts the date to the specified format) date_format ()
Select History, Date_format (History, '%y year%m%d ') from employee;
Select History, Date_format (History, '%d ') from employee;
Select History, Date_format (History, '%y%m month%d%h%i minutes%s seconds ') from employee;

8: Calculate the day of the week
Select History, Dayname (history) from employee;

9: Chinese Date string conversion date str_to_date ()
INSERT into employee values (11, ' Zhang Fei ', ' Male ', 22, 3000, ' February 01, 2017 '); --Error
INSERT into employee values (11, ' Zhang Fei ', ' Male ', ', ', ' str_to_date ', ' February 01, 2017 ', '%y '%m '%d '%h '%i '%s seconds ');

INSERT into employee values (12, ' two elder brother ', ' male ', $, str_to_date (' February 01, 2017 23:02 02 sec ', '%y year%m month%d%h%i minutes '%s seconds '));
INSERT into employee values (12, ' two elder brother ', ' male ', $, str_to_date (' February 01, 2017 11:02 02 sec ', '%y year%m month%d%h%i minutes '%s seconds '));
PS: If the H is 12 small system, if the large h is 24 small Ming system;



String functions
1:left (str, len) returns the left-side Len character of the string str
Select Left (' ABCDEFG ', 5);

2:length ()
Select Length (' ABCDEFG ');

3:lower (str) returns the lowercase string str
Select lower (' HELLO ');

4:substring () takes a substring, the second argument is the starting position of the intercept, and the third argument is the length to intercept.
Select substring (' HelloWorld ', 2, 3);

5:concat () string concatenation
Select Concat (emp_name, ' employees ') from employee;

6:replace (replace
Select Replace (Emp_name, ' Lee ', ' old ') from employee where emp_name = ' John Doe ';


MySQL operator and function (Chubby teacher)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.