MySQL operator and function (Chubby teacher)

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)