【数据库】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，数值型函数

3，字符串函数

示例：

统计dept表中d_name的长度

select length(d_name) from dept;

4，日期和时间函数

示例

查看当前时间：

select CURRENT_DATE;

5，流程控制函数

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 THEN

WHEN 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,case when 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;