Summary of Mysql operators and functions

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');

Commonly used operators:
1: equal to (=)

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

2: equal to ( <=> )

##

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

3: is judge(null)

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

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

  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 not between 4500 and 5000;
  --null不为包括进去

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

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

select * from employee where sal >= 4500;

**** *************************************************** *************************************************** *******<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif;"></span><br/>

Mathematical functions

1: rand();

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

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

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 how many decimal places to retain. 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 function

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;   --打印9 这里会忽略null值
  select count(*) 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 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()表示这个函数开始时间

2: Get the current date

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

3: Get the current time

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

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)

5: Date subtraction operation data_sub

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

6: Calculate date difference

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

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;

8: Calculate the day of the week for a date

select history, dayname(history) from employee;

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秒'));

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);

2: length()

select length('abcdefg');

3: lower(str) returns the lowercase string str

 select lower('HELLO');

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);

5: concat() string splicing

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

6: replace(replace

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

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!