Summary of Mysql operators and functions-Mysql Tutorial-php.cn

This article gives you a summary of the commonly used operators and the usage and examples of common functions in mysql. It is very comprehensive. Friends in need can refer to it

Let’s look at an example first

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, '张三', '男', 18, 5000, '2012-04-23'), (2, '李四', '男', 27, 4500, '2013-05-23'), (3, '王五', '男', 23, 4700, '2012-04-21'), (4, '子龙', '男', 19, 3800, '2011-03-04'), (5, '李白', '男', 15, 6200, '2015-09-09'), (6, '刘备', '男', 28, 2500, '2016-02-11'), (7, '吕布', '男', 21, 6000, '2010-10-18'), (8, '尚香', '女', 16, 4500, '2011-09-26'), (9, '小乔', '女', 15, null, '2013-07-05'), (10, '大乔', '女', 16, 5000, '2017-09-01');

Copy after login

Commonly used operators:
1: equal to (=)

select * from employee where sal = 3800; select * from employee where sal = null; --这里查询不到为null的数据

Copy after login

2: equal to ( <=> )

select * from employee where sal <=> 3800; select * from employee where sal <=> null; --这里可以查询到为null的数据

Copy after login

3: is judge(null)

select * from employee where sal is null; select * from employee where sal is not null;

Copy after login

4: You can also use isnull() for null value judgment;

select * from employee where isnull(sal); select * from employee where !isnull(sal);

Copy after login

5: Within the interval (between) between min and max ps: Here is a Closed interval

select * from employee where sal between 4500 and 5000;

Copy after login

6: Not within the interval

select * from employee where sal not between 4500 and 5000; --null不为包括进去

Copy after login

7: and 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 = '女';

Copy after login

8: Less than (<), Greater than (>), Less than or equal to (<=), Greater than or equal to (>=)

select * from employee where sal >= 4500;

Copy after login

**** *************************************************** *************************************************** *******

Mathematical functions

1: rand();

select rand() from dual; --dual是一个伪表 select 1+1 from dual; select rand(); --可以简写

Copy after login

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; --列名可以起一个别名

Copy after login

3: greatest(value1, value2, ...) returns the maximum Value

select greatest(54,76,4,65,76,87,87,56,65,654,45,23,1,76);

Copy after login

4: round(M, D); Returns the rounded value of M. D indicates how many decimal places to retain. The default value is 0

select round(1.69); select round(1.69, 1);

Copy after login

5: abs() absolute value

select 5-10; select abs(5-10);

Copy after login

**************** *************************************************** *********************************************

Summary function

1: avg();

select * from employee where sal >= 6000; select avg(sal) from employee where sal >= 6000;

Copy after login

2: count()

select count(*) from employee; select count(emp_name) from employee; select count(sal) from employee; --打印9 这里会忽略null值 select count(*) from employee where sal >= 4000; select count(*) from employee where sal <= 4000 or sal is null;

Copy after login

3: sum()

select sum(sal) from employee where sal >= 6000;

Copy after login

4: min()

select min(sal) from employee;

Copy after login

5: max ()

select max(sal) from employee;

Copy after login

****************************************** *************************************************** ***********************

Date function

1: Get the current The date and time of

select now(), sysdate(), current_timestamp(); select now(6), sysdate(6), current_timestamp(6); ps: now(), current_timestamp();没有区别, 表示sql开始执行时的时间 sysdate()表示这个函数开始时间

Copy after login

2: Get the current date

select curdate(); --只有年月日

Copy after login

3: Get the current time

select curtime(); --只有时分秒

Copy after login

4: Date addition operation 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)

Copy after login

5: Date subtraction operation data_sub

select history, date_sub(history, interval '1-1' year_month) from employee;

Copy after login

6: Calculate date difference

select history, sysdate(), datediff(sysdate(), history) from employee; --以天数来表示

Copy after login

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月%d号 %H时%i分%s秒') from employee;

Copy after login

8: Calculate the day of the week for a date

select history, dayname(history) from employee;

Copy after login

9: Chinese date string conversion date str_to_date()

insert into employee values(11, '张飞', '男', 22, 3000, '2017年02月01号'); --报错 insert into employee values(11, '张飞', '男', 22, 3000, str_to_date('2017年02月01号', '%Y年%m月%d号 %H时%i分%s秒'));

Copy after login

insert into employee values(12, 'Second brother', 'Male', 22, 3000, str_to_date('February 01, 2017, 23:02:02', '%Y year %m month %d number %H hour %i minute %s seconds'));

insert into employee values(12, 'Second brother', 'Male', 22, 3000, str_to_date('February 01, 2017 11:02:02', '%Y Year %m month %d %h hour %i minute %s second'));
ps: If it is h, it means 12 hours, if it is a big H, it means 24 hours;

String function

1: left(str, len) Returns len characters from the left end of string str

select left('abcdefg', 5);

Copy after login

2: length()

select length('abcdefg');

Copy after login

3: lower(str) returns the lowercase string str

select lower('HELLO');

Copy after login

4: substring() takes the substring, the second parameter is the intercepted starting point The starting position, the third parameter is the length to be intercepted

select substring('helloworld',2,3);

Copy after login

5: concat() string splicing

select concat(emp_name, '员工') from employee;

Copy after login

6: replace(replace

select replace(emp_name, '李', '老') from employee where emp_name = '李四';

Copy after login

The above is the detailed content of Summary of Mysql operators and functions. For more information, please follow other related articles on the PHP Chinese website!