Mysql common operators and functions and mysql Operators

Mysql common operators and functions and mysql Operators

Create a data table first.

Use test; create table 'employe' (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,500 0, '2014-04-23 '), (2, 'Li si', 'male', 2012 0, '1970-05-23 '), (3, 'wang 5', 'male', 2013 0, '1970-04-21'), (4, 'zilong ', 'male ', 19,380 0, '2017-03-04 '), (5, 'Li Bai', 'male', 2011 0, '2017-09-09 '), (6, 'Liu bei ', 'male', 28,250 0, '2017-02-11 '), (7, 'lup', 'male', 2016 0, '2017-10-18'), (8, 'shangxiang ', 'female', 16,450 0, '1970-09-26 '), (9, 'xiaocho', 'female', 15, null, '1970-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; -- null data cannot be found here.

2: equal to (<=>)

Select * from employee 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, 65,654,); select least (54,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. 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 here the null value select count (*) is ignored (*) from employee where sal> = 4000; select count (*) from 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 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. sysdate () indicates the start time of the function when SQL is started.

2: Get the current date

select curdate(); -- Only the year, month, and day

3: Get the current time

select curtime(); -- Only time, minute, and second

4: 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: 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 % 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 11'); -- insert into employee values (11, 'zhang fee', 'male ', 22,300 0, str_to_date ('july 11', '% Y, % m, % d, % H, % I, % 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 ';