node学习—数据库函数和分组

一、数据库函数和分组

1.函数

1.内置函数

select AVG(salary) from employee;
--薪水平均值

select count(id) from employee;
--查询员工数量

select count(id) as 员工数量,
avg(salary) as 平均薪资,
sum(salary) as 总薪资,
min(salary) as 最小薪资
from employee;

--拼接
select CONCAT(`name`,salary) as 信息 from employee;
select CONCAT(`name`,'_',salary) as 信息 from employee;

二、视图

新建视图

select c.id as cid,c.`name` cname,
d.id as did,d.`name` dname,
e.id as eid,e.`name` ename,e.salary,e.birthday,e.ismale,e.location,e.joinDate
from company as c inner join department as d on c.id = d.companyId
inner join employee as e on d.id = e.deptId

点击保存

select `c`.`id` AS `cid`,`c`.`name` AS `cname`,`d`.`id` AS `did`,`d`.`name` AS `dname`,`e`.`id` AS `eid`,`e`.`name` AS `ename`,`e`.`salary` AS `salary`,`e`.`birthday` AS `birthday`,`e`.`ismale` AS `ismale`,`e`.`location` AS `location`,`e`.`joinDate` AS `joinDate` from ((`company` `c` join `department` `d` on((`c`.`id` = `d`.`companyId`))) join `employee` `e` on((`d`.`id` = `e`.`deptId`)))

打开后就是这样了