大三寒假实训 第一课SQL 较为复杂的sql语句学习

##表A   left join  表B  on 条件   A.id=B.id

SELECT * FROM `t_emp_project`
## 项目表 与project 表进行关联
SELECT * FROM t_project AS a LEFT JOIN 
t_emp_project AS b on a.id = b.projectid

 SELECT *  FROM t_dept AS d RIGHT JOIN
 t_emp e on d.id = e.id
 
 ## 在原来基础上  得到员工id，要关联员工表
 
 SELECT a.*,b.* ,temp.* FROM t_project AS a LEFT JOIN 
t_emp_project AS b on a.id = b.projectid 
 LEFT JOIN
t_emp AS temp on b.empid =temp.id
## 多表查询格式 SQL
## 思路1：查询项目表 关联 中间表 得到员工表 id

SELECT 
a.*,
b.empid
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表

##思路2： 在上面基础上 去关联员工id

SELECT 
a.id,projectname,
b.empid,   ##中间表 员工id
E.empname,E.AGE,E.sex,E.phone,E.deptid,E.birthday
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id

##我们可以帮任意查询的结果当成一张表 前提不能有重复的列 

SELECT
a.*
 FROM 
 (
SELECT 
a.id,projectname,
b.empid,   ##中间表 员工id
E.empname,E.AGE,E.sex,E.phone,E.deptid,E.birthday
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
 ) AS a ##这张新表别名叫a
 
 
 
 
 ##单行函数  count总条数  min max avg SUM
SELECT
COUNT(*) total,
MAX(a.age) maxage,
MIN(a.age) minage,
ROUND(AVG(a.age)) avgage,##ROUND(X)四舍五入取整数
SUM(a.age) sumage## 它的查询结果别名 
FROM 
(
SELECT 
a.id,projectname,
b.empid,   ##中间表 员工id
E.empname,E.AGE,E.sex,E.phone,E.deptid,E.birthday
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
 ) AS a ##这张新表别名叫a
 
 
 ##排序   order by  列1 desc/asc
SELECT
a.*
 FROM 
(
 SELECT 
a.id,projectname,
b.empid,   ##中间表 员工id
E.empname,E.AGE,E.sex,E.phone,E.deptid,E.birthday
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
 ) AS a ##这张新表别名叫a
 ORDER BY a.empid DESC,a.id  DESC
 
 ##动态创建一个列   根据sex 判断男女 0 男   
SELECT
a.*,
CASE a.sex=0 when 1 THEN '男' else '女' 
end as csex
FROM 
 (
SELECT 
a.id,projectname,
b.empid,   ##中间表 员工id
E.empname,E.AGE,E.sex,E.phone,E.deptid,E.birthday
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
 ) AS a ##这张新表别名叫a
 ORDER BY a.empid DESC,a.id  DESC
 
 
 
 
 
 
 
 ## 分组函数 GROUP BY 以什么什么列分组 按照男女分组 统计人数 和平均年龄 
 
##使用口诀 1.使用了group by 列，那麽查询select 的列 不能用* 只能用groupby 后面的列 这里可以看看a.sex
SELECT
a.sex,
COUNT(*) num,ROUND(AVG(a.age)) avgage
 FROM 
 (
SELECT 
a.id,projectname,
b.empid,   ##中间表 员工id
E.empname,E.AGE,E.sex,E.phone,E.deptid,E.birthday
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
 
 ) AS a ##这张新表别名叫a

GROUP BY a.sex

###  如何统计一个项目 男女个有多少人 (动态的列不能影响这一行数据的准确性)
SELECT
a.projectname,
a.sex,
COUNT(a.sex) sexnum,
case a.sex=1 when 1  then COUNT(a.sex)  else '0' end as   男生人数,
case a.sex=0  when 1  then COUNT(a.sex)  else '0' end as   女生人数
FROM(
SELECT 
a.id,projectname,
E.sex ##中间表 员工id
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
)
AS a GROUP BY a.projectname,a.sex

##纵变横  

SELECT
 a.projectname,
 SUM(a.男生人数)  as 男生人数,
 SUM(a.女生人数)  as 女生人数
FROM
(
SELECT
a.projectname,
case a.sex=1 when 1  then COUNT(a.sex)  else '0' end as   男生人数,
case a.sex=0  when 1  then COUNT(a.sex)  else '0' end as   女生人数
FROM
(
SELECT 
a.id,projectname,
E.sex ##中间表 员工id
FROM t_project a 
LEFT JOIN t_emp_project b on a.id=b.projectid  ##关联中间表
LEFT JOIN t_emp E            on b.empid=E.id
)
AS a GROUP BY a.projectname,a.sex
) a GROUP BY a.projectname

SELECT * FROM t_teamball
SELECT a.teamballid,a.teamname FROM t_teamball a

SELECT * FROM  t_player
SELECT b.playerid,b.teamballid,b.playername FROM t_player b

SELECT * FROM t_schedule
SELECT  c.scheduleid,c.matchdate,c.hometeamid,c.visitingteamid FROM t_schedule c

SELECT * FROM t_detail
SELECT d.detailid,d.scheduleid,d.playerid,d.score  FROM t_detail d where scheduleid ='1712365749483474944'

##目标 
赛程id   比赛日期   主队名字  主队总分  客队名字   客队总分

##如果要重复用到一张表 你可以重复给表起名字哟
SELECT 
a.scheduleid,
a.matchdate,
a.hometeamid,
a.visitingteamid,
b.name AS hometeamname,
c.name AS visitingteaname
FROM t_schedule a 
LEFT JOIN t_teamball b on a.hometeamid=b.teamballid  ##主队id关联球队表
LEFT JOIN t_teamball c on a.visitingteamid=c.teamballid  ##客队id关联客队的球队表

## 查询赛程id 1712365749483474944 查询各个球员得分 并显示球员名字
SELECT 
d.detailid,
d.scheduleid,
d.playerid,
d.score, 
c.playername,
c.teamballid 
FROM t_detail d
LEFT JOIN t_player c on d.playerid =c.playerid
where scheduleid ='1712365749483474944'

###把结果当成一个表 并且求和

SELECT 
new.teamballid,
SUM(new.score) 总分
from
(
SELECT 
d.detailid,
d.scheduleid,
d.playerid,
d.score, 
c.playername,
c.teamballid 
FROM t_detail d
LEFT JOIN t_player c on d.playerid =c.playerid
where scheduleid ='1712365749483474944'
 ) as new
 GROUP BY  new.teamballid