[MySQL光速入门]010 运算符与函数-CSDN博客

必会单词

regexp 正则表达式
trim 修剪
replace 替换
ceiling 最高限度
year 年
month 月
day 日
hour 小时
minute 分钟
second 秒
current 当前的
date 日期
time 时间
now 现在
week 星期
version 版本

运算符

算术运算符

加 +
- select int类型的字段,int类型的字段+10 from 表;
```
select bookprice,bookprice+10 from book;
复制代码
```
减 -
- select int类型的字段,int类型的字段-10 from 表;
```
select bookprice,bookprice-10 from book;
复制代码
```
乘 *
- select int类型的字段,int类型的字段*10 from 表;
```
select bookprice,bookprice*10 from book;
复制代码
```
除 /
- select int类型的字段,int类型的字段/10 from 表;
```
select bookprice,bookprice/10 from book;
复制代码
```
求余/取模 %
- select int类型的字段,int类型的字段%10 from 表;
```
select bookprice,bookprice%10 from book;
复制代码
```

比较运算符

大于/小于

SELECT 字段1, 字段2 FROM 表 WHERE 字段2 > 值;

SELECT bookname, bookprice FROM book WHERE bookprice > 20;
复制代码

SELECT 字段1, 字段2 FROM 表 WHERE 字段2 < 值;

SELECT bookname, bookprice FROM book WHERE bookprice < 20;
复制代码

不等

SELECT 字段1, 字段2 FROM 表 WHERE 字段2 <> 值;

SELECT bookname, bookprice FROM book WHERE bookprice <> 20;
复制代码

SELECT 字段1, 字段2 FROM 表 WHERE 字段2 != 值;

SELECT bookname, bookprice FROM book WHERE bookprice != 20;
复制代码

正则

select 字段1,字段1 REGEXP '正则表达式1',字段2,字段2 REGEXP '正则表达式2',字段3,字段3 REGEXP '正则表达式3' from 表;

select bookname,bookname REGEXP '^j',bookauthor,bookauthor REGEXP '红$',bookpublisher,bookpublisher REGEXP '.+出版社' from book;
复制代码

select * from 表 where 字段 REGEXP '正则表达式';

select * from book where bookpublisher REGEXP '.+[0-9]$';
复制代码

逻辑运算符

&& and

select * from 表名 where 字段 > 值1 and 字段 < 值2;

select * from book where borrowsum > 5 and borrowsum < 30;
复制代码

select * from 表名 where 字段 > 值1 && 字段 < 值2;

select * from book where borrowsum > 5 && borrowsum < 30;
复制代码

! not

select * from 表名 where not 字段名 = 值;

select * from book where not borrowsum = 30;
复制代码

select * from 表名 where 字段名 != 值;

select * from book where borrowsum != 30;
复制代码

select * from 表名 where 字段名 <> 值;

select * from book where borrowsum <> 30;
复制代码

|| or

select * from 表名 where 字段 <= 值1 || 字段 >= 值2;

select * from book where borrowsum <= 5 or borrowsum >= 30;
复制代码

select * from 表名 where 字段 <= 值1 or 字段 >= 值2;

select * from book where borrowsum <= 5 || borrowsum >= 30;
复制代码

这四个语句, 结果一样

select * from book where borrowsum > 5 and borrowsum < 30;
复制代码

select * from book where borrowsum > 5 && borrowsum < 30;
复制代码

select * from book where not( borrowsum <= 5 or borrowsum >= 30);
复制代码

select * from book where not( borrowsum <= 5 || borrowsum >= 30);
复制代码

内置函数

字符串函数

left(s,n)/right(s,n)

select 字段,left(字段,2) from 表;

select bookname,left(bookname,2) from book;
复制代码

select 字段,right(字段,2) from 表;

select bookname,right(bookname,2) from book;
复制代码

concat()/concat_ws()

select 字段1,字段2,字段3,字段4,字段5,CONCAT(字段1,字段2,字段3,字段4,字段5) as 别名1, CONCAT_WS('分隔符',字段1,字段2,字段3,字段4,字段5) as 别名2 from 表;

select bookid,bookname,bookauthor,bookpublisher,bookprice,CONCAT(bookid,bookname,bookauthor,bookpublisher,bookprice) as 详情1,CONCAT_WS('_',bookid,bookname,bookauthor,bookpublisher,bookprice) as 详情2 from book;
复制代码

trim()/ltrim(s)/rtrim(s)

select 字段, trim(字段) 别名1, ltrim(字段) 别名2, rtrim(字段) 别名3 from 表名;

select bookpublisher,trim(bookpublisher) 删除左右空格,ltrim(bookpublisher) 删除左空格,rtrim(bookpublisher) 删除右空格 from book;
复制代码

replace()
- select 字段别名,replace(字段,'匹配的字段','替换的字段') 替换后 from 表名;
```
select bookname 替换前,replace(bookname,'设计','崩溃') 替换后 from book;
复制代码
```
substring()
- select 字段, SUBSTRING(字段,开始位置从一开始,长度) from 表名;
```
select bookname, SUBSTRING(bookname,2,3) from book;
复制代码
```

日期函数

now()
```
select now();
复制代码
```

curdate()/curtime()

select now(),CURRENT_DATE(),CURRENT_TIME(),curdate(),curtime();
复制代码

dayofweek(d)/dayofmonth(d)/dayofyear(d)

select now(),DAYOFWEEK(now()),DAYOFMONTH(now()),DAYOFYEAR(now()),WEEKDAY(now());
复制代码

hour(t)/minute(t)/second(t)

select now(),HOUR(now()),MINUTE(now()),SECOND(now());
复制代码

date_add()/date_sub()

select DATE_ADD(now(),interval 3 day);
复制代码

select DATE_SUB(now(),INTERVAL 7 MINUTE);
复制代码

datediff()

select DATEDIFF('2020-10-1',now());
复制代码

数学函数

abs(x)

select abs(-789),abs(-123.666);
复制代码

floor(x)/ceiling(x)

select FLOOR(-2.3),CEILING(-2.3),FLOOR(9.9),CEILING(9.9);
复制代码

greatest()/least()

select GREATEST(1,2,3,4),LEAST(1,2,3,4);
复制代码

round(x)/truncate(x,y)

select round(3.4567),round(4.567),TRUNCATE(3.4567,3);
复制代码

rand()
```
select rand(),rand();
复制代码
```

sqrt(x)/mod(x,y)

select sqrt(64),sqrt(2),TRUNCATE(sqrt(2),3),mod(10,4);
复制代码

系统函数

database()/user()/version()

select DATABASE(),user(),version();
复制代码

charset(str)/collation(str)

select charset('123'),COLLATION('123');
复制代码