卷王D6--数据库（从入门到跑路）

最新推荐文章于 2024-11-14 18:36:14 发布

Jeffrey.Yin

最新推荐文章于 2024-11-14 18:36:14 发布

阅读量56

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/wq71765312/article/details/131800130

版权

SQL sever学习-基础篇1（day6）

12、分组查询

--分组查询
--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--方法一
SELECT '北京' 地区, COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,
ROUND(AVG(PEOPLESALARY),2) 平均工资 
FROM PEOPLE
WHERE PEOPLEADRESS = '北京'
UNION
SELECT '中国' 地区, COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,
ROUND(AVG(PEOPLESALARY),2) 平均工资 
FROM PEOPLE
WHERE PEOPLEADRESS = '中国'
--方法二
SELECT PEOPLEADRESS 地区, COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,
ROUND(AVG(PEOPLESALARY),2) 平均工资 
FROM PEOPLE
GROUP BY PEOPLEADRESS
--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--1985年及以后出身的员工不参与统计
SELECT PEOPLEADRESS 地区, COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,
ROUND(AVG(PEOPLESALARY),2) 平均工资 
FROM PEOPLE
WHERE PEOPLEBIRTH < '1985-1-1'--where只能使用在group by前面，否则会报错
GROUP BY PEOPLEADRESS
--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--要求筛选出员工人数至少在2人及以上的记录，并且1985年及以后出身的员工不参与统计
SELECT PEOPLEADRESS 地区, COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,
ROUND(AVG(PEOPLESALARY),2) 平均工资 
FROM PEOPLE
WHERE PEOPLEBIRTH < '1985-1-1' 
GROUP BY PEOPLEADRESS
HAVING COUNT(*) >= 2  --聚合函数作为条件时不能放到普通条件where后面，只能使用having函数放到group by后面

13、多表查询（一）

--笛卡尔乘积
SELECT * FROM PEOPLE,DEPARTMENT

--简单多表查询
--查询员工信息，显示部门名称
SELECT * FROM PEOPLE,DEPARTMENT
WHERE PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
--查询员工信息，显示职级名称
SELECT * FROM [RANK]
SELECT * FROM PEOPLE
SELECT * FROM PEOPLE,[RANK]
WHERE PEOPLE.RANKID = [RANK].RANKID
--查询员工信息，显示部门名称和职级名称
SELECT * FROM PEOPLE,[RANK],DEPARTMENT
WHERE PEOPLE.RANKID = [RANK].RANKID AND PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID

--内连接查询
--查询员工信息，显示部门名称
SELECT * FROM PEOPLE
INNER JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
--查询员工信息，显示职级名称
SELECT * FROM PEOPLE
INNER JOIN [RANK] ON  PEOPLE.RANKID = [RANK].RANKID
--查询员工信息，显示部门名称和职级名称
SELECT * FROM PEOPLE
INNER JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
INNER JOIN [RANK] ON  PEOPLE.RANKID = [RANK].RANKID

--外连接（左外连，右外连，全外连）
--左外连：以左表为主表进行数据显示，主外键关系找不到的数据null取代

--查询员工信息，显示部门名称
SELECT * FROM PEOPLE
LEFT JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
SELECT * FROM DEPARTMENT
LEFT JOIN PEOPLE ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID

--右连：A LEFT JOIN B = B RIGHT JOIN A
--查询员工信息，显示部门名称
SELECT * FROM PEOPLE
LEFT JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
SELECT * FROM DEPARTMENT
RIGHT JOIN PEOPLE ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID

--全外连：两张表的数据，无论是否符合关系，都要显示
SELECT * FROM PEOPLE
FULL JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID

14、多表查询（二）

--查询武汉地区所有员工信息，要求显示部门名称以及员工的详细资料（显示中文别名）
SELECT PEOPLEID 员工编号,PEOPLENAME 姓名,PEOPLEPHONE 电话,PEOPLEADRESS 地址,PEOPLESALARY 工资 
FROM PEOPLE
LEFT JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID

--查询出武汉地区所有员工信息，要求显示部门名称，职级名称，以及员工的详细资料（显示中文别名）
SELECT  PEOPLEID 员工编号,DEPARTMENTIDNAME 部门名称,RANKNAME 职级名称,PEOPLENAME 姓名,
PEOPLESEX 性别,PEOPLEBIRTH 生日,PEOPLESALARY 工资,PEOPLEPHONE 电话,PEOPLEADRESS 地址
FROM PEOPLE
LEFT JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
LEFT JOIN [RANK] ON  PEOPLE.RANKID = [RANK].RANKID
WHERE PEOPLEADRESS = '中国'

--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资。
SELECT DEPARTMENT.DEPARTMENTID 部门编号,DEPARTMENTIDNAME 部门名称,COUNT(*) 员工人数, SUM(PEOPLESALARY) 工资总和,AVG(PEOPLESALARY) 平均工资,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资
FROM PEOPLE
INNER JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
GROUP BY DEPARTMENT.DEPARTMENTID,DEPARTMENTIDNAME

--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资。
--平均工资在10000以下的不参与统计，并且根据平均工资降序排序
SELECT DEPARTMENT.DEPARTMENTID 部门编号,DEPARTMENTIDNAME 部门名称,COUNT(*) 员工人数, SUM(PEOPLESALARY) 工资总和,AVG(PEOPLESALARY) 平均工资,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资
FROM PEOPLE
INNER JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
GROUP BY DEPARTMENT.DEPARTMENTID,DEPARTMENTIDNAME
HAVING AVG(PEOPLESALARY) >= 10000
ORDER BY AVG(PEOPLESALARY) DESC

--根据部门名称，然后根据职位名称，分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
SELECT DEPARTMENT.DEPARTMENTID 部门编号,DEPARTMENTIDNAME 部门名称,COUNT(*) 员工人数, SUM(PEOPLESALARY) 工资总和,AVG(PEOPLESALARY) 平均工资,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资
FROM PEOPLE
INNER JOIN DEPARTMENT ON  PEOPLE.DepartmentID = DEPARTMENT.DEPARTMENTID
INNER JOIN [RANK] ON  PEOPLE.RANKID = [RANK].RANKID
GROUP BY DEPARTMENT.DEPARTMENTID,DEPARTMENTIDNAME,[RANK].RANKID,RANKNAME