【数据库】sql函数和多表关联查询

目录

一，SQL函数

1，聚合函数

1， count函数

2， AVG函数

3， SUM函数

4， MAX函数

5， MIN函数

6，数据分组——GROUP BY

7，限定组的结果，HAVING

8， HAVING与WHERE的区别

9，对结果集排序：order by

 10，MySQL多行数据合并：GROUP_CONCAT

11，查询结果限定

2，数值型函数

3，字符串函数

 示例：

4，日期和时间函数

 示例

5，流程控制函数

1，IF(expr,v1,v2)

2，IFNULL(v1,v2) 

3，CASE value WHEN

4，CASE WHEN

二，多表关联查询

 1，inner join：内连接

 2，leftjoin：左连接

 3，rightjoin：右连接

 4，自连接

 5，​交叉连接

---

一，SQL函数

1，聚合函数

聚合函数对一组值进行运算，并返回单个值。也叫分组函数。

COUNT(*|列名) 统计行数，*表示所有记录都不忽略，指定列名时会忽略null

AVG(数值类型列名) 平均值，忽略null

SUM (数值类型列名) 求和，忽略null

MAX(列名) 最大值，忽略null

MIN(列名) 最小值，忽略null

  

表t1的所有记录如下图所示：

 

1， count函数

COUNT(*|列名) 统计行数，*表示所有记录都不忽略，指定列名时会忽略null

（1）统计t1表有多少行

select count(*) from t1;

 

 

（2）统计t1表中的performance 列有多少行，不统计null

select count(performance) from t1;

 

 

2， AVG函数

AVG(数值类型列名) 平均值，忽略null

统计t1表中salary的平均值

select avg(salary) from t1;

 

3， SUM函数

SUM(数值类型列名) 求和，忽略null

统计t1表中salary的和

select sum(salary) from t1;

 

 

4， MAX函数

MAX(列名) 最大值，忽略null

MIN统计t1表中salary的最大值

select max(salary) from t1;

 

 

 

5， MIN函数

 MIN(列名) 最小值，忽略null

统计t1表中salary的最小值

select min(salary) from t1;

 

 

6，数据分组——GROUP BY

GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。

分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。

 

 数据分组--GROUP BY

语法：SELECT column,group_function FROM table[WHEREcondition]

 [GROUP BY group_by_expression]

查看dept表下的所有信息：

select * from dept;

 

  查看表employee的所有信息：

select * from employee;

 

 （1）每个部门的平均工资

select dept_id,avg(salary) from employee group by dept_id;

 

（2）出现在select后的字段都，它必须出现在GROUP BY子句中

select dept_id,d_name,avg(salary) from employee group by dept_id,d_name;

（3）出现在group by子句的字段可以不出现在select的列表当中

select dept_id,avg(salary) from employee group by dept_id,d_name;

7，限定组的结果，HAVING

限定组的结果：HAVING子句，HAVING子句用来对分组后的结果再进行条件过滤。

语法：SELECT column, group_function FROM table [WHEREcondition] [GROUP BY group_by_expression]  [HAVING group_condition] [ORDER BY column];

（1）查询部门平均工资大于400的部门

select d_name,avg(salary) from employee group by d_name having avg(salary) >400;

 

 

8， HAVING与WHERE的区别

HAVING与WHERE的区别：WHERE是在分组前进行条件过滤， HAVING子句是在分组后进行条件过滤，WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数。

 

（1）查找employee表中dept_id>1的d_name和avg(salary)

方法一：where

select d_name,avg(salary) from employee where dept_id>1 group by d_name;

方法二：having

select d_name,avg(salary) from employee group by d_name,dept_id having dept_id>1;

​

9，对结果集排序：order by

对结果集排序：查询语句执行的查询结果默认是按数据插入顺序排列，实际上可能需要按某列的值大小排列，按某列排序采用order by 列名[desc],列名…;设定排序列的时候可采用列名、列别名。

（1）查找employee表中dept_id>1的d_name和avg(salary),并对平均工资进行升序排序

select d_name,avg(salary) asalary from employee group by d_name,dept_id having deept_id>1 order by asalary;

（2）查找employee表中按照d_name分组的dept_id>1，avg(salary)>=300的d_name和avg(salary)并按升序排序

select d_name,avg(salary) asalary from employee where dept_id>1 group by d_name having asalary >=300 order by asalary;

 

 

 10，MySQL多行数据合并：GROUP_CONCAT

 （1）查找employee表中按照组dept_id，d_name分组的dept_id，d_name,avg(salary)，e_id并把属于一组的e_id合并输出在一行

select dept_id,d_name,avg(salary),group_concat(e_id) from employee group by dept_id,d_name;

（2）查找employee表中的dept_id，d_name,avg(salary)，e_id,name,并把属于一组的e_id与name合并输出在一行

select dept_id,d_name,avg(salary),group_concat(e_id),group_concat(name) from emplloyee

注意：使用 GROUP_CONCAT()函数必须对源数据进行分组，否则所有数据会被合并成一行

（3）查找dept表中的部门名称并将查到的多行结果合并一行

select group_concat(d_name) from dept;

​

11，查询结果限定

查询结果限定，在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。

语法：SELECT … LIMIToffset_start,row_count;

offset_start:第一个返回记录行的偏移量。默认为0。

row_count:要返回记录行的最大数目。

dept表有5行数据

select * from dept;

 

 

（1）显示表dep前t三行数据

select * from dept limit 3;

 

 

（2）从第3行开始，显示3行内容（n,m从n+1行开始，显示m行内容）

select * from dept limit 2,3;

 

 

 

 

 

 

2，数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求平方根
POW 和 POWER	两个函数的功能相同，返回参数的幂次方
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数时，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号

 

 

3，字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH	计算字符串长度函数，返回字符串的字符长度，注意两者的区别
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
INSERT(str,pos,len,newstr)	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT(str,len)	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE(s，s1，s2)	字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len)	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)	比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
LOCATE(substr,str [,pos])	返回第一次出现子串的位置
INSTR(str,substr)	返回第一次出现子串的位置

 

 示例：

统计dept表中d_name的长度

select length(d_name) from dept;

 

 

 

4，日期和时间函数

函数名称	作用
CURDATE() CURRENT_DATE() CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME CURRENT_TIME() CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW()	返回当前系统的日期和时间值
SYSDATE	返回当前系统的日期和时间值
DATE	获取指定日期时间的日期部分
TIME	获取指定日期时间的时间部分
MONTH	获取指定日期中的月份
MONTHNAME	获取指定曰期对应的月份的英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
YEAR	获取年份，返回值范围是 1970〜2069
DAYOFWEEK	获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1 〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 和 DAY	两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31
DATEDIFF(expr1,expr2)	返回两个日期之间的相差天数，如 SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
TIME_TO_SEC	将时间参数转换为秒数，是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

 

 示例

查看当前时间：

select CURRENT_DATE;

 

 

 

 

5，流程控制函数

函数名称	作用
IF(expr,v1,v2)	判断，流程控制，当expr = true时，或者为1时返回 v1，当expr = false、null 、0时返回v2
IFNULL(v1,v2)	判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE	搜索语句

 

 

1，IF(expr,v1,v2)

判断，流程控制，当expr = true时，或者为1时返回 v1，当expr = false、null 、0, ' '时返回v2

（1）使用IF()函数进行条件判断

如果表达式12>3成立，返回yes，否则返回no;

select if(12>3,'yes','no');

（2）分别显示emp表有奖金和没奖金的员工信息。

在数据库chap04下创建emp表，包含id int primary,name char(30) nut null,comm int三个字段

create table emp(id int primary key,name char(30) not null,comm int);

给表emp中插入数据

insert into emp values (1,'xioaming',2000),(2,'xiaohong',300),(3,'xiaohei',3200);

insert into emp (id,name) values (4,'xiaobai'),(5,'xiaolan');

查看表emp的所有信息

select * from emp;

分别显示emp表有奖金和没奖金的员工信息。

select id,name,if(comm is null,'没奖金','有奖金') '备注' from emp;

2，IFNULL(v1,v2) 

IFNULL(v1,v2)  判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2​

IFNULL() 函数语法格式为：IFNULL(expression, alt_value)

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为NULL 则返回第一个参数的值。

select ifnull(null,2);

​

3，CASE value WHEN

使用CASE value WHEN语句执行分支操作

语法：CASE  <表达式>

   WHEN<值1> THEN<操作>

   WHEN<值2> THEN<操作>

   ...

   ELSE<操作>

END

 

将 <表达式> 的值逐一和每个 when的 <值> 进行比较，如果跟某个<值>相等，则执行它后面的 <操作> ，如果所有 when的值都不匹配，则执行 else的操作，如果 when的值都不匹配，且没写 else，则会报错。

（1）判断输入的值是否为3，如果是3,输出three;

select case 3 when 1 then 'one' when 2 then 'two' when 3 then 'three' else 'other' end;

（2）

employee的所有信息如下

​

查找employee表下的dept_id,name,部门，当dept_id等于1，部门为boss，当dept_id等于2，部门为'finamce' ，3 ，'sale'； 4， 'tech'

select dept_id,name,case dept_id when 1 then 'boss' when 2 then 'finamce' when 3 then 'sale' when 4 then 'tech' end '部门' from employee;

​

4，CASE WHEN

使用CASE WHEN语句执行分支操作

判断条件放到when后面

select name,salary,casewhen salary<=300 then '[0,300]'when salary<=500 then '(300,500]'when salary<=1000 then '(500,1000]'end'salary level'from employee;

 

 

 

二，多表关联查询

1，inner join：内连接

inner join：内连接，只取得键值一致的部分，代表选择的是两个表键值交叉的部分。

语法如下：

SELECT列名1,列名2... FROM表1 INNER JOIN 表2 ON表1.列=表2.列 [and条件表达式 ][ WhERE条件语句 ] [ orderby列 [desc] ];

查看dept表的所有信息

select * from dept;

查看employee表的所有信息

select * from employee;

employee表中的dept_id是dept表中d_id的外键

（1）查找employee表dept_id中和dept表中d_id相等的所有记录

select * from employee e inner join dept d on e.dept_id=d.d_id;

（2）查找职员的部门

select e.name,d_name from employee e inner join dept d on e.dept_id=d.d_id;

（3）查找销售部门的职员姓名

select e.name,d_name from employee e inner join dept d on e.dept_id=d.d_id where d.d_name='sale';

（4）查找工资大于等于300的职员信息

方法一：

select e.name,d_name,e.salary from employee e inner join dept d on e.dept_id=d.d_id and salary>=300;

方法二：

select e.name,d_name,e.salary from employee e inner join dept d on e.dept_id=d.d_id where salary>=300;

2，leftjoin：左连接

leftjoin：左连接，代表选择的是前面一个表的全部。左连接是以左表为标准，只查询在左边表中存在的数据。语法如下：

SELECT列名1 FROM表1 LEFTOUTERJOIN表2 ON表1.列=表2.列 WhERE条件语句;

employee表中的所有记录如下所示：

select * from employee;

dept表中的所有记录如下所示

select * from dept;

显示所有员工的信息，包括没有部门的员工

select * from employee e left join dept d on e.dept_id=d.d_id;

显示所有部门的信息，包括没有员工的部门

select * from dept d left join employee e on e.dept_id=d.d_id;

工资大于等于300的信息

select * from employee e left join dept d on e.dept_id=d.d_id where salary>=300;

工资大于等于300的信息按升序排序

select * from employee e left join dept d on e.dept_id=d.d_id where salary>=300 order by salary;

 

 

3，rightjoin：右连接

3.rightjoin：右连接，代表选择的是后面一个表的全部。右连接将会以右边作为基准，进行检索。语法如下：

SELECT列名1 FROM表1 RIGHTOUTERJOIN表2 ON表1.列=表2.列 WhERE条件语句;

显示所有部门的信息，包括没有员工的部门

select * from employee e right join dept d on e.dept_id=d.d_id;​

 

 

显示所有员工的信息，包括没有部门的员工

select * from dept d right join employee e on e.dept_id=d.d_id;

 

 4，自连接

自连接，自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)

查找比自己mgr岁数大的员工和mgr的姓名和年龄

（1）创建表t4

create table t4(name char(30),age int,mgr char(20));

 

（2）向表t4中插入数据：

insert into t4 values ('xiaoming',29,'xiao'),('xiaohong',26,'xiao'),('zhouyi',19,'zhou'),('xiao',28,'booss'),('zhou',30,'boss');

 

 （3）查看t4表的所有信息

select * from t4;

 

 （4）查找比自己mgr岁数大的员工和mgr的姓名和年龄（两张表之间的逗号也可以换为join）

select e.name,e.age,m.name mgr,m.age mgr_age from t4 e,t4 m where e.mgr=m.name and e.age > m.age;

​

5，​交叉连接

交叉连接：不适用任何匹配条件。生成笛卡尔积

表t1的信息如下：

select * from t1;

表dept的信息如下：

select * from dept;

查询表t1与表table的交叉连接

select * from dept,t1;