第10课 运算符与函数

第五章-思维导图.png

必会单词

1. regexp 正则表达式
2. trim 修剪
3. replace 替换
4. ceiling 最高限度
5. year 年
6. month 月
7. day 日
8. hour 小时
9. minute 分钟
10. second 秒
11. current 当前的
12. date 日期
13. time 时间
14. now 现在
15. week 星期
16. 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');