[数据库]--DQL语言之常见函数

常见函数

在java里面将一组逻辑语言封装在方法体中

好处：1.隐藏代码运行细节 2.提高代码的重用性

在数据库中调用：select 函数名(实参列表)

特点：

1.函数名

2.函数功能

分类：

单行函数（一个函数返回一个值）

1.字符函数：

length\concat\ifnull...

##LENGTH获取参数值的字节个数

SELECT LENGTH('Ellen');

SELECT LENGTH('乔巴xixixi');

SHOW VARIABLES LIKE '%char%'

##CONCAT拼接字符串

SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;

##UPPER,LOWER大小写转换

SELECT UPPER('ellen');

SELECT LOWER('LOVE');

##将员工表里的姓大写，名小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

##SUBSTR(str FROM pos FOR len)截取数据

SELECT SUBSTR('今晚我想吃大盘鸡',7) 结果;

SELECT SUBSTR('今晚我想吃大盘鸡',5,4) 结果;

##姓名中首字母大写，其他字符小写然后用_拼接，显示为结果

SELECT CONCAT(UPPER(SUBSTR(first_name, 1, 1)),'_',LOWER(SUBSTR(first_name, 2)))

AS 结果 FROM employees;

##INSTR通过子串的下标索引获取

SELECT INSTR('路飞一行人踏上了征程','征程') AS 结果;

SELECT INSTR('这征程艰险路飞一行人踏上了征程','征程') AS 结果;

##TRIM截取

SELECT LENGTH(TRIM(' 乔巴 '));

SELECT TRIM('a' FROM 'aaaaaa乌aaaa索普aaaaaaaaaaaaaa');

##LPAD用指定的字符实现左填充指定长度

SELECT LPAD('山治',指定长度,'指定字符');

SELECT LPAD('山治',指定长度,'指定字符');

##RPAD用指定的字符实现右填充指定长度

SELECT RPAD('山治',10,'*');

##REPLACE 替换

SELECT REPLACE('我最近爱上了学习','爱上了','酷爱') AS 结果;

 

2.数学函数：

##ROUND 四舍五入

SELECT ROUND(1.55);

SELECT ROUND(-1.55);

##ceil 向上取整，返回值>=该参数的最小整数

SELECT CEIL(0.01);

SELECT CEIL(-1.02);

SELECT CEIL(1.00);

##floor 向下取整，返回值<=该参数的最大整数

SELECT FLOOR(9.99);

SELECT FLOOR(-9.99);

##TRUNCATE截断

SELECT TRUNCATE(1.65,1);

SELECT TRUNCATE(1.69999999999,2);

##MOD取余

SELECT MOD(10,3);

SELECT MOD(-10,-3);

 

3.日期函数

##now 当前日期+时间

SELECT NOW();

##curdate 当前日期

SELECT CURDATE();

##curtime 当前时间

SELECT CURTIME();

SELECT YEAR(NOW());

SELECT YEAR('2020-5-3');

SELECT YEAR(hiredate) 年份 FROM employees;

SELECT MONTH(NOW());

SELECT MONTHNAME(NOW());

##STR_TO_DATE将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('2000-5-01','%Y-%m-%d');

##查询入职日期是1992-4-3的员工

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('1992-4-3','%Y-%m-%d');

##DATE_FORMAT将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 结果;

##查询有奖金的员工名和入职日期(xx月xx日xx年)

SELECT first_name,DATE_FORMAT(hiredate,'%m月%d日%y年') FROM employees WHERE commission_pct IS NOT NULL;

 

4.其他函数

SELECT VERSION();

SELECT DATABASE();

SELECT USER();

 

5.流程控制函数

##if函数：if else的效果

SELECT IF('10>5','大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻 嘻') FROM employees;

##case函数：switch case的效果

##switch(变量或者表达式){

## case常量1：语句1；break；

## ......

## DEFAULT:语句n；break;

## }

 

/*MySQL里：

case要判断的字段或表达式

when 常量1 then 要显示的值1(语句1)

when 常量2 then 要显示的值2(语句2)

...

end*/

 

##查询员工工资，要求：

##部门号是10的，显示的工资是1.1倍

##部门号是40的，显示的工资是1.2倍

##部门号是70的，显示的工资是1.3倍

##其他的部门，显示的工资是原工资

SELECT salary AS 原工资,department_id,

CASE department_id

WHEN 10 THEN salary*1.1

WHEN 40 THEN salary*1.2

WHEN 70 THEN salary*1.3

ELSE salary

END AS 新的工资

FROM employees;

 

##查询员工工资情况，要求：

##如果工资大于20000，显示A

##如果工资大于15000，显示B

##如果工资大于10000，显示C

##其他的都显示为D

SELECT salary,

CASE

WHEN salary>20000 THEN 'A'

WHEN salary>15000 THEN 'B'

WHEN salary>10000 THEN 'C'

ELSE 'D'

END AS 工资级别

FROM employees;

 

6.分组函数

统计函数、聚合函数、组函数

分类：

sum求和、avg平均数、max最大值、min最小值、count计算个数

特点：

1.sum、avg一般用于处理数值型

2.max、min、count可以处理任何类型

3.所有分组函数都忽略null值

4.和distinct去重搭配使用

5.count

例：

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均值,MAX(salary) 最大值,MIN(salary) 最小值,COUNT (salary) 个数 FROM employees;

SELECT SUM(salary),AVG(last_name) FROM employees;

SELECT MAX(hiredate) FROM employees;

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

 

7.分组查询

语法：

SELECT 查询列表

FROM 表

WHERE 筛选条件

GROUP BY 分组字段;

注意：查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段

分组前筛选和分组后筛选：

针对的表 位置 关键字

分组前筛选 原始表 group by前 where关键字

分组后筛选 group by的结果集 group by后 having

 

where和having区别：

1.分组前筛选用where

2.分组后筛选用having

3.where不能使用列别名和聚合函数(sum,avg,count)

 

8.连接查询

笛卡尔乘积现象：表1有n行，表2有m行，结果是m*n行

发生原因：没有添加有效的连接条件

如何避免：添加有效的连接条件

分类：

1.按照年代区分：

sql92标准：仅仅支持内连接

sql99标准：支持内连接+外连接+交叉连接

2.按照功能区分：

内连接：

  等值连接

  非等值连接

  自连接

外连接：

  右外连接

  左外连接

  全外连接

  交叉连接

sql92标准：内连接

1.等值连接

（1）多表等值连接的结果是多表的交集部分

（2）n表连接，至少需要n-1个条件

（3）多表的顺序没有要求

（4）一般要为表起别名

（5）可以搭配前面使用的所有子句使用，比如排序、分组、筛选

2.非等值连接

不完全是不等值连接，还可以是其他关系连接(大于、小于或者其他关系)

3.自连接

相当于等值连接

sql99标准：

内连接   inner

外连接

右外连接 right(outer)

左外连接 left(outer)

全外连接 full(outer)

交叉连接 cross

语法：

select 查询列表 from 表1 别名 连接类型 表2 别名

on 连接条件

where 筛选条件

group by分组

having 筛选条件语句

order by 排序

 

内连接：inner(比较重要)

语法：

select 查询列表 from 表1 别名 inner join 表2 别名

on 连接条件;

1.等值连接

（1）添加排序、分组、筛选

（2）inner可以省略

（3）筛选条件可以写在where后面，连接条件写在on后面

（4）查询内容是多表的交集部分

2.非等值连接

3.自连接

外连接:用来查询一个表中有而另一个表中没有的数据

特点：

1.外连接的查询结果是主表中的所有结果

如果从表中有和主表信息匹配的，则会显示匹配的值

如果从表中有和主表信息匹配的，则会显示null

外连接的查询结果=内连接的结果+主表中有而从表中没有的数据

2.右外连接 right(outer）右边的是主表

左外连接 left(outer) 左边的是主表

3.左外和右外调换两个表的顺序，可以实现同样的效果

4.全外连接 full(outer)

 

交叉连接 cross

九九乘积

sql92标准和sql99标准：

1.sql99标准支持的连接类型较多

2.可读性高，实现了连接条件和筛选条件的分离

 

9.子查询

含义：出现在其他查询语句中的select语句，又称为内查询语句

（子查询语句的）外部查询语句称为主查询或者外查询

分类：

按照子查询语句出现的位置：

select后面

标量子查询

from后面

表子查询

where或having后面（重要）

标量子查询

列子查询

行子查询

exists后面

表子查询，相关子查询

按照结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集只有一行多列）

表子查询（结果集只有多行多列）

（1）where或having后面（重要）

标量子查询（单行）

列子查询（多行）

行子查询（多行多列）

特点：

（1）子查询语句放在小括号

（2）子查询一般放在条件的右侧

（3）标量子查询，一般情况下搭配单行操作符使用

> < >= <= <>

列子查询，一般情况下搭配多行操作符使用

in/not in：等于列表中的任何一个

any/some：任一，和子查询返回的某一个值作比较

all：和子查询返回的每一个值作比较

（2）select后面（仅仅支持标量子查询） 一般不常用

（3）from后面（表子查询）将子查询结果看成表来用，要求必须起别名

（4）exists后面，相关子查询

语法：exists（完整的查询语句）

结果：1和0

 

10.分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

select 查询列表

from 表

连表类型（join type）

on 连接条件

where 筛选条件

group by 分组条件

having 筛选条件

order by 排序的字段

limit [offset，size]分页关键字

offset:要显示条目的起始索引（从0开始）

size:要显示的具体的条目个数

特点：

1.limit语句放在查询语句的最后

2.公式:limit [offset，size]

要显示的页数page

每页的条目数size

select 查询列表

from 表名

limit (page-1)*size,size；

size=10;

page size

1    0

2    10

3    20

 

11.union联合查询

概念：将多条查询语句的结果集合并成一个结果

语法：查询语句1 union 查询语句2 union ...

特点：

1.要查询的结果来自于多个表，而且多个表之间没有

直接连接关系，但是查询的信息一致

2.联合查询的结果集列数必须一致