Mysql operator and function (fat instructor), mysql operator Function

Mysql operator and function (fat instructor), mysql operator Function

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', 'mal', 18,500 0, '2017-04-23 '),
(2, 'lily', 'male', 27,450 0, '2017-05-23 '),
(3, 'wang 5', 'male', 23,470 0, '2017-04-21 '),
(4, 'zilong', 'male', 19,380 0, '2017-03-04 '),
(5, 'Li Bai ', 'male', 15,620 0, '2017-09-09 '),
(6, 'Liu bei ', 'male', 28,250 0, '2017-02-11 '),
(7, 'lup', 'male', 21,600 0, '2017-10-18 '),
(8, 'shangxiang ', 'female', 16,450 0, '2017-09-26 '),
(9, 'xiaocho', 'female ', 15, null, '2017-07-05 '),
(10, 'dacho', 'female ', 16,500 0, '2017-09-01 ');


Common operators:
1: equal to (=)
Select * from employee where sal = 3800;
Select * from employee where sal = null; -- no null data is found here.

2: equal to (<=>)
Select * from Maid where sal <=> 3800;
Select * from employee where sal <=> null; -- 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: You can also use isnull () to determine the null value ();
Select * from employee where isnull (sal );
Select * from employee where! Isnull (sal );

5: between min and max ps in the interval (between): Here is a closed interval
Select * from employee where sal between 4500 and 5000;

6: Not in the interval
Select * from employee where sal not between 4500 and 5000; -- null is not included

7: and or
Select * from employee where sal not between 4500 and 5000 or sal is null;
Select * from employee where sal = 4500 and emp_sex = 'femal ';

8: less than (<), greater than (>), less than or equal to (<=), 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 1 + 1 from dual;
Select rand (); -- can be abbreviated

2: least (value1, value2,...) returns the minimum value.
Select least (54,76, 65,654 );
Select least (54,76, 65,654,) as min_value; -- the column name can be an alias

3: greatest (value1, value2,...) returns the maximum value.
Select greatest (54,76, 65,654 );

4: round (M, D); returns the rounded value of M. D indicates the number of decimal places to be retained. 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 );
**************************************** **************************************** *******************************

Aggregate 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 the null value is ignored here
Select count (*) from employee where sal> = 4000;
Select count (*) from Maid 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 Functions
1: Get the current date and time
Select now (), sysdate (), current_timestamp ();
Select now (6), sysdate (6), current_timestamp (6 );
Ps: now (), current_timestamp (); no difference, indicating the time when the SQL statement starts to be executed
Sysdate () indicates the start time of the function.

2: Get the current date
Select curdate (); -- only year, month, and day

3: Get the current time
Select curtime (); -- only hours, minutes, And seconds

4: date_add
Select history, date_add (history, interval '1 'day_minute) from employee; -- date_add (history, interval '1' 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: Date subtraction data_sub
Select history, date_sub (history, interval '1-1 'year_month) from employee;

6: Calculate the date difference
Select history, sysdate (), datediff (sysdate (), history) from employee; -- expressed in days

7: Get the specified part of the date (convert the date to the specified format) date_format ()
Select history, date_format (history, '% Y % m month % D') from employee;
Select history, date_format (history, '% d') from employee;
Select history, date_format (history, '% Y % m month % d % H % I minute % s s') 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 fee', 'male', 22,300 0, 'August 1'); -- Error
Insert into employee values (11, 'zhang fee', 'male', 22,300 0, str_to_date ('August 1 ', '% Y % m % d % H % I % s s '));

Insert into employee values (12, 'dige', 'male', 22,300 0, str_to_date ('August 23, February 01, 2017, 02 seconds ', '% Y % m % d % H % I % s s '));
Insert into employee values (12, 'dige', 'male', 22,300 0, str_to_date ('february 11, February 01, 2017, 02 minutes, 02 seconds ', '% Y % m % d % h % I % s s '));
Ps: If it is h, it indicates 12 smaller units; if it is H, it indicates 24 smaller units;



String Functions
1: left (str, len) returns the left len characters of the str string.
Select left ('abcdefg', 5 );
 
2: length ()
Select length ('abcdefg ');

3: lower (str) returns a lower-case string str
Select lower ('hello ');

4: substring () is used to obtain the substring. The second parameter is the starting position of the substring and the third parameter is the length of the substring to be truncated.
Select substring ('helloworld', 2, 3 );

5: concat () String concatenation
Select concat (emp_name, 'employee ') from employee;

6: replace (replace
Select replace (emp_name, 'lil', 'old') from employee where emp_name = 'lily ';