当前位置: 必赢官网 > 计算机编程 > 正文

MYSQL 计数 最大值 最小值 平均数 求和

时间:2019-12-26 05:58来源:计算机编程
createtableTeachingData--教学数据表(Tidchar(10)primarykey,--教师编号Tetimechar(100),--教学时间Teplacechar(10),--教学地点TeDenamechar(100),--教学系名Tenumint,--教学人数Tecoursechar(100),--教学课程Curriculum_1floa

createtableTeachingData--教学数据表(Tidchar(10)primarykey,--教师编号Tetimechar(100),--教学时间Teplacechar(10),--教学地点TeDenamechar(100),--教学系名Tenumint,--教学人数Tecoursechar(100),--教学课程Curriculum_1float,--课程系数);insertintoTeachingDatavalues(101,'8:00--9:50','227','物联网工程',60,'数据结构','3');insertintoTeachingDatavalues(102,'10:10--12:00','422','软件工程',40,'c语言','3');insertintoTeachingDatavalues(103,'14:00--15:50','202','服务外包',54,'高等数学','3');insertintoTeachingDatavalues(104,'16:10--18:00','402','电子工程',48,'复变函数','3');insertintoTeachingDatavalues(108,'16:10--18:00','202','计算机国际',62,'单片机','3');insertintoTeachingDatavalues(107,'0:10--12:00','303','软件工程',40,'嵌入式','3');insertintoTeachingDatavalues(106,'14:00--15:50','412','计算机国际',62,'无线传感系统','3');insertintoTeachingDatavalues(105,'16:10--18:00','102','电子工程',48,'射频技术','3');createtableElseWorkData--其他工作数据表(Tidchar(10)primarykey,--教师编号Tnamechar(10),--姓名JKDnamechar(50),--监考系名JKplacechar(10),--监考地点JKtimechar(50),--监考时间JKnumint,--监考次数Curriculum_2float,--监考系数);insertintoElseWorkDatavalues(101,'张三','物联网工程','202','8:00--10:30','1','1');insertintoElseWorkDatavalues(102,'李四','服务外包','227','14:00--16:00','1','1');insertintoElseWorkDatavalues(103,'王五','软件工程','201','8:00--10:00','1','1');insertintoElseWorkDatavalues(104,'赵六','电子工程','203','9:00--10:00','1','1');insertintoElseWorkDatavalues(105,'钱七','计算机国际','505','8:30--10:00','1','1');insertintoElseWorkDatavalues(106,'孙八','物联网工程','600','8:00--10:00','1','1');insertintoElseWorkDatavalues(107,'周九','软件工程','102','10:00--12:00','1','1');insertintoElseWorkDatavalues(108,'郑十','物联网工程','401','15:00--17:00','1','1');createtableAllWorkData--总工作量数据表(Tidchar(10)primarykey,--教师编号Tnamechar(10),--教师姓名Tework_Tfloat,--教学工作量Ework_Efloat,--其他工作量Aworkfloat,--总工作量);createtableTework--教学工作量表(Tidchar(10)primarykey,--教师编号Tenumint,--教学人数Tecoursechar(100),--教学课程Curriculum_1float,--课程系数Tework_Tfloat,--教学工作量);createtableEwork--其他工作量表(Tidchar(10)primarykey,--教师编号Tnamechar(10),--教师姓名Curriculum_2float,--监考系数JKnumint,--监考次数Ework_Efloat,--其他工作量);----------------------CREATETRIGGERbox_E_1--其他工作量增加触发器ONElseWorkDataAFTERINSERTASBEGININSERTINTOEwork(Tid,Tname,JKnum,Curriculum_2)SELECTTid,Tname,JKnum,Curriculum_2FROMINSERTEDENDCREATETRIGGERbox_E_2--其他工作量删除触发器ONElseWorkDataAFTERDELETEASBEGINDELETEEworkWHERETidIN(SELECTTidFROMDELETED)ENDcreatetriggerbox_E_3_1--其他工作量同步触发器onElseWorkDataafterupdateasbeginupdateEworksetTid=i.TidfromEworkasbr,deletedd,insertedasiwherebr.Tid=d.Tidendcreatetriggerbox_E_3_2--其他工作量同步触发器2onElseWorkDataafterupdateasbeginupdateEworksetTname=i.TnamefromEworkasbr,deletedd,insertedasiwherebr.Tname=d.Tnameendcreatetriggerbox_E_3_3--其他工作量同步触发器3onElseWorkDataafterupdateasbeginupdateEworksetCurriculum_2=i.Curriculum_2fromEworkasbr,deletedd,insertedasiwherebr.Curriculum_2=d.Curriculum_2endcreatetriggerbox_E_3_4--其他工作量同步触发器4onElseWorkDataafterupdateasbeginupdateEworksetJKnum=i.JKnumfromEworkasbr,deletedd,insertedasiwherebr.JKnum=d.JKnumendcreatetriggerbox_E_4--其他工作量计算触发器onEworkforinsert,update,deleteASupdateEworksetEwork_E=JKnum*Curriculum_2CREATETRIGGERbox_T_1--教学工作量增加触发器ONTeachingDataAFTERINSERTASBEGININSERTINTOTework(Tid,Tenum,Tecourse,Curriculum_1)SELECTTid,Tenum,Tecourse,Curriculum_1FROMINSERTEDENDCREATETRIGGERbox_T_2--教学工作量删除触发器ONTeachingDataAFTERDELETEASBEGINDELETETeworkWHERETidIN(SELECTTidFROMDELETED)ENDcreatetriggerbox_T_3_1--教学工作量同步触发器1onTeachingDataafterupdateasbeginupdateTeworksetTid=i.TidfromTeworkasbr,deletedd,insertedasiwherebr.Tid=d.Tidendcreatetriggerbox_T_3_2--教学工作量同步触发器2onTeachingDataafterupdateasbeginupdateTeworksetTenum=i.TenumfromTeworkasbr,deletedd,insertedasiwherebr.Tenum=d.Tenumendcreatetriggerbox_T_3_3--教学工作量同步触发器3onTeachingDataafterupdateasbeginupdateTeworksetCurriculum_1=i.Curriculum_1fromTeworkasbr,deletedd,insertedasiwherebr.Curriculum_1=d.Curriculum_1endcreatetriggerbox_T_3_4--教学工作量同步触发器3onTeachingDataafterupdateasbeginupdateTeworksetTecourse=i.TecoursefromTeworkasbr,deletedd,insertedasiwherebr.Tecourse=d.Tecourseendcreatetriggerbox_T_4--教学工作量计算触发器onTeworkforinsert,update,deleteASupdateTeworksetTework_T=Curriculum_1*Tenum------------------------------------------------------------------------------------------------------------以上是部分命令想实现输入俩个表的数据后,能自动将剩下三个表填好现在其他工作量表和教学工作量表都已经没问题了但是自动填总工作表的时候填不上去,各位大佬,救救孩子吧,怎么才能让老师编号,老师姓名,其他工作量和教学工作量自动填上去啊???

 笔记:

为什么要使用触发器
     触发器的主要作用就是其能够实现由主键和外所不保证的复杂的参照完整性和数据的一致性。除此之外触发器还有其它许多不同的功能 

一、聚合函数:计数 最大值  最小值  平均数  求和

 触发器定义
      触发器实际上是一种特殊的存储的过程。触发器的主要功能是监视用户对数据的修改。如果针对一个数据表建了触发器,则 当数据表被修改(插入、更新或者删除)时,SQL Server便使触发器自动执行,以便对数据的修改采用一些适当的措施,从而保证数据的完整性和安全性。

1.计数 COUNT()  忽略NULL值

触发器分类
      After 触发器在触发操作(Insert、Update 或Delete)后和处理完任何约束后激发。可通过指定 After 或 for 关键字来请求 After 触发器。因为 For 关键字与 After 的效果相同,所以具有 For 关键字的触发器也归类为 After 触发器。
Instead Of 触发器代替触发动作进行激发,并在处理约束之前激发。

方式1:COUNT(*)  *代表所有字段 一般用于查询表中共有多少条记录(实体)

注意:对于每个触发操作(Update、Delete和Insert),每个表或视图只能有一个 Instead Of 触发器。而一个表对于每个触发操作可以有多个 After触发器。

SELECT COUNT(*) zongshu FROM emp;

Instead Of 触发器
      INSTEAD OF触发器指定执行触发器而不是执行触发 的SQL 语句,从而替代触发语句的操作。
在表或视图上,每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而,可以在每个具有 INSTEAD OF 触发器的视图上定义视图。

方式2:计算某个字段中非NULL值的个数

Instead Of 触发器举例
Instead Of 触发器的主要优点是可以使不能更新的视图支持更新。包含多个基表的视图必须使用 Instead Of 触发器来支持引用表中数据的插入、更新和删除操作。Instead Of触发器的另一个优点是使您得以编写这样的逻辑代码:可以拒绝批处理中的某些部分同时允许批处理的其它部分成功。
 Instead Of 触发器可以进行以下操作:

SELECT COUNT(ALL comm) FROM emp;-- all 可以省略

  •  忽略批处理中的某些部分。
  • 不处理批处理中的某些部分并记录有问题的行。
  • 如果遇到错误情况则采取备用操作。

方式3:去重计数  计算某列中不重复非NULL值的个数

       将此逻辑作为 Instead Of 触发器的一部分进行编码,可避免所有访问数据的应用程序必须重新执行该逻辑。在下列 Transact-SQL 语句序列中Instead Of触发器更新视图中的两个基表。另外,显示两种处理错误的方法: 
忽略对 People 表的重复插入,并且插入的信息将记录在 PeopleRepeat 表中。
表Teacher的重复插入转变为 Update 语句,该语句将当前信息检索至 Teacher,而不会产生重复键侵犯。用Transact-SQL 语句创建两个基表、一个视图、一个记录错误表和视图上的 INSTEAD OF 触发器。下面的这些表将个人数据和业务数据分开并且是视图的基表:下面的视图使用某个人的两个表中的所有相关数据立报表: 

SELECT COUNT( DISTINCT job) FROM emp;

Instead Of 触发器举例代码如下

2.最大值  MAX() 求某一列中的最大值,不分组的情况下不可与其他字段一起使用

--身份证表
Create Table People
   (
    PeopleID Int Primary Key,--身份证号
    PeopleName Varchar(20) not null,--姓名
    PeopleSex Char(2) Default('男'),--性别
    Constraint Check_Sex Check(PeopleSex in ('男','女')),
   )
--教师表
Create Table Teacher
   (
    TeacherID int Primary Key,--教师编号
    PeopleID int References People(PeopleID),--身份证号
    Salary Money,--工资
   )

SELECT MAX(sal) FROM emp ;

--记录重复身份证号
Create Table PeopleRepeat
   (
    PeopleID Int,--身份证号
    PeopleName Varchar(20) not null,--姓名
    PeopleSex Char(2) Default('男'),--性别
    OperateName Char(50),--操作员
    OperateTime DateTime,--操作时间
   )

3.最小值  MIN()  不分组的情况下不可与其他字段一起使用

--创建视图    
Alter View View_Teacher As
Select P.PeopleID,PeopleName,PeopleSex,TeacherID,Salary
From People P Inner Join Teacher T
   On P.PeopleID = T.PeopleID

SELECT  MIN(sal) FROM emp ;

 

4.平均值  AVG()  不分组的情况下不可与其他字段一起使用

--Instead Of 触发器在单独视图的多个基表中插入行。
--将对插入具有重复社会安全号的行的尝试记录在 PersonDuplicates 表中。
--将Teacher 中的重复行更改为更新语句。

SELECT AVG(sal) FROM emp;

If Exists (Select Name From Sysobjects
      Where name = Ltrim('Trig_Insert_ViewTeacher') And type = 'TR')
   Drop Trigger Trig_Insert_ViewTeacher
Go

5.求和  SUM() 不分组的情况下不可与其他字段一起使用

Create Trigger Trig_Insert_ViewTeacher ON View_Teacher
Instead Of Insert
As
Begin
Set Nocount On
-- 检查PeopleID是否重复,不重复则插入记录
If (Not Exists (Select P.PeopleID
      From People P, Inserted I
      Where P.PeopleID = I.PeopleID))
   Insert Into People
      Select PeopleID,PeopleName,PeopleSex
      FROM inserted
Else
-- 记录重复插入的信息(错误处理方法一:忽略对 People 表的重复插入,并且插入的信息将记录在 PeopleRepeat 表中。)
   Insert into PeopleRepeat                                                          
      Select PeopleID,PeopleName,PeopleSex,Suser_Sname(),Getdate()
      From Inserted
--检查是否存在TeacherID,如果不存在则插入记录
If (Not Exists (Select T.TeacherID
      From Teacher T, inserted I
      Where T.TeacherID = I.TeacherID))
   Insert Into Teacher
      Select TeacherID,PeopleID,Salary 
      From inserted
else
--如果重复了,则改为Upate,则不会出现重复插入的错误 即前面所讲的遇 到错误备用操作
--(错误处理方法二:表Teacher的重复插入转变为 Update 语句,该语句将当前信息检索至 Teacher,而不会产生重复键侵犯。)
   Update Teacher
      Set TeacherID = I.TeacherID,
          Salary = I.Salary
   From Teacher T, Inserted I
   Where T.PeopleID = I.PeopleID
End

SELECT SUM(sal) FROM emp;

Go

练习:

Insert Into View_Teacher
       Values(0001,'小敏','女',2,5000)

1.查询员工的总人数;

Select * From People
Select * From Teacher
Select * From PeopleRepeat
Select * From View_Teacher

SELECT COUNT(*) FROM emp;

总结要点

2.查询员工的平均工资;

  • Instead Of 触发器并不(Insert,Update,Delete)操作,这些触发器在执行任何约束前执行,因此可执行预处理以补充约束操作
  • Instead Of 触发器的主要优点是可以使不能更新的视图支持更新
  • 在含有用 DELETE 或 UPDATE 操作定义的外键的表上,不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器

SELECT AVG(sal) FROM emp;

3.查询文员的总人数;

SELECT COUNT(*) FROM emp WHERE job='文员';

  1. 查询30号部门中文员的总工资;

SELECT SUM(sal IFNULL(comm,0)) FROM emp WHERE deptno=30 AND job ='文员';

5.查询10号部门中员工的最低工资;

SELECT MIN(sal) FROM emp WHERE deptno=10;

6.查询入职时间在2001-2005的员工的最高工资

SELECT MAX(sal) FROM emp WHERE hiredate BETWEEN '2001' AND '2005';

7.查询2005年之前入职的员工的平均工资

SELECT AVG(sal) FROM emp WHERE hiredate <'2005';

8.查询2002年之后入职的员工的最高工资和最低工资和平均工资。

SELECT MAX(sal) ,MIN(sal),AVG(sal) FROM emp  WHERE hiredate >'2002';

9.查询30号部门的最高工资、最低工资、平均工资;

SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE deptno =30;

10.查询10号或20号部门的文员的最高工资、最低工资、平均工资;

SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE (deptno =10 OR deptno =20 ) AND job ='文员';

二、分组  GROUP BY

分组计算时SELECT 语句中可以有分组字段和聚合函数,但不要放其他字段;

SELECT deptno, AVG(sal),MAX(sal) ,MIN(sal) ,SUM(sal)FROM  emp WHERE sal >20000 GROUP BY deptno;

HAVING 用户过滤聚合函数的值 只能用在分组后面

SELECT deptno ,MIN(sal),MAX(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>8000 AND MAX()<50000;

SELECT deptno ,MIN(sal) minSal ,MAX(sal) b FROM emp GROUP BY deptno HAVING minSal>8000 AND b<50000;

使用多个字段分组:

计算每个部门中每个岗位的平均工资:

SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno ,job ;

分组练习:

-- 1.查询各个部门的最高工资、最低工资、平均工资。

www.437.com,SELECT deptno,MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno;

-- 2.查询各个职位的平均工资 降序排列

必赢的网址登录,SELECT job,AVG(sal) avgSal FROM emp GROUP BY job ORDER BY avgSal DESC ;

-- 3.查询平均工资大于10000的岗位

SELECT job ,AVG(sal) avgSal FROM emp GROUP BY job HAVING avgSal >10000 ;

-- 4.查询每个部门的人数

SELECT deptno ,COUNT(ename) FROM emp GROUP BY deptno;

-- 5.查询人数大于5的部门

SELECT deptno ,COUNT(ename) qty FROM emp GROUP BY deptno HAVING qty>5;

-- 6.查询部门人数小于3的部门的平均工资、最高工资、最低工资

SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM emp  GROUP BY deptno HAVING COUNT(*)<=3;

-- 7.查询各个部门中工资大于10000的人数 降序显示

SELECT deptno ,COUNT(empno) qty FROM emp WHERE sal>10000 GROUP BY deptno ORDER BY qty DESC ;

-- 8.查询各个岗位中工资大于10000的人数 升序显示

-- 9.查询平均工资大于10000的前两个部门。

SELECT AVG(sal) ,deptno FROM emp  GROUP BY deptno HAVING AVG(sal)>10000 LIMIT 2;

必赢官网,-- 10.查询每个部门中没有津贴的人数。

SELECT deptno,COUNT(*) FROM emp WHERE comm IS NULL GROUP BY deptno

三、组合查询 - 子查询

1.单行单列  跟在WHERE子句后,用于判断的条件

-- 比李世民工资高的所有员工的信息

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='李世民')

2.单行多列  跟在WHERE子句后

-- 查询和李世民工资及职位都一样的人的所有信息

SELECT * FROM emp WHERE (job,sal) IN (SELECT job ,sal FROM emp WHERE ename='李世民')

AND ename !='李世民';

3.多行单列

-- 查询大于30号部门中任意一个员工工资的人的所有信息

-- any:任意的意思

SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);

SELECT * FROM emp WHERE sal >  (SELECT MIN(sal) FROM emp WHERE deptno=30);

-- 查询大于30号部门中所有人工资的员工信息

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

4.多行多列 用在FROM子句后面 作为虚表使用

SELECT e.ename FROM (SELECT * FROM emp WHERE deptno =30) e WHERE e.comm IS NULL;

组合查询-- 合并结果集  上下两条查询语句的字段个数要一样;

UNION --去除重复数据的合并;

SELECT ename,sal FROM emp WHERE deptno=10 UNION SELECT  ename,sal FROM emp WHERE deptno=20;

UNION ALL  -- 不去除重复记录

SELECT ename,sal  FROM emp WHERE deptno=10 UNION ALL SELECT  ename,sal FROM emp WHERE deptno=20;

四、链接

  1. 内连接 去除笛卡尔积:去除匹配错误的数据;

1.1 mysql方言方式:

SELECT * FROM emp ,dept  WHERE emp.deptno= dept.deptno AND dept.dname='学工部' ORDER BY emp.sal DESC ;

1.2 标准SQL方式:SELECT * FROM emp INNER JOIN dept  ON emp.deptno= dept.deptno;

1.3自然连接:SELECT * FROM emp NATURAL JOIN dept;

2.外连接

2.1 左外连接 LEFT OUTER JOIN  -- out可以省略,以左边表的行数为准,若右边表没有与之匹配的数据,那么用null值填充

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno =d.deptno;

SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno =d.deptno;

2.2 右外连接  RIGHT OUTER JOIN  与左外连接意思相反

SELECT * FROM emp e RIGHT  JOIN dept d ON e.deptno =d.deptno;

二。1:查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

2:薪水大于12000的雇员,按照部门编号进行分组,分组后的平均薪水必须大于15000,查询各分组的平均工资,按照工资的倒序进行排列

3:查询每个雇员和其所在的部门名

4.查询每个雇员姓名及其工资所在的等级

5:查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。

6:查询每个雇员和其经理的姓名

-- 7:查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))

SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- 8:查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)

SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

-- 9:查询每个部门中工资最高的人的姓名、薪水和部门编号

SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

-- 10:查询每个部门平均工资所在的等级

SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno

) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

-- 11:查询每个部门内平均的薪水等级

SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

SELECT deptno,AVG(sal IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno

) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

-- 12:查询雇员中有哪些人是经理人

-- 13:不准用max函数,求雇员表中薪水的最高值。

SELECT * FROM emp ORDER BY sal IFNULL(comm,0)  DESC LIMIT 0,1;

-- 14:平均薪水最高的部门的部门编号

SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

-- 15:求平均薪水最高的部门的部门名称

SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

-- 16:查询手下有员工的领导的信息

SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- mysql联表查询练习:

-- 1.查询销售部工资大于20000的所有人;

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;

-- 2.查询每个部门的人数,要求显示部门名称;

SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;

-- 3.查询每个部门的最高工资,平均工资,最低工资 ,要求显示部门名称;

SELECT * FROM dept LEFT JOIN (

SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno

-- 4.查询教研部中入职时间最早的员工信息,要显示部门名称,姓名,入职时间;

SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;

-- 5.查询当前没有员工的部门信息;

SELECT * FROM (

SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

三。课后作业:

-1.学生表

Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--创建测试数据

create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--2.课程表

Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--3.教师表

Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名

create table Teacher(Tid varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--4.成绩表

SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

课后作业:

-1.学生表

Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--创建测试数据

create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--2.课程表

Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--3.教师表

Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名

create table Teacher(Tid varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--4.成绩表

SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT st.sid,st.sname,AVG(sc.score) avgScore FROM student st ,sc WHERE st.sid=sc.Sid GROUP BY sid  HAVING avgScore>60;

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT st.sid,st.sname ,COUNT(sc.cid),SUM(sc.score) FROM student st ,sc WHERE st.sid=sc.sid GROUP BY st.sid;

--6、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

SELECT * FROM student st,sc,course co,teacher  te WHERE

st.sid=sc.sid AND sc.cid=co.cid AND co.tid=te.tid AND te.tname='张三' ORDER BY sc.score DESC LIMIT 1;

--7、查询本周过生日的学生

SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) ;

--8、查询下周过生日的学生

SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) 1 ;

--9、查询本月过生日的学生

SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW());

--10、查询下月过生日的学生

SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW()) 1;

--11、查询每门功成绩最好的前两名

SELECT  sid,cid,score

FROM sc r1

WHERE  (SELECT COUNT(*) FROM sc r2 WHERE r2.cid=r1.cid AND r1.score <= r2.score) <=2 ORDER BY cid ;

--12、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

SELECT  cid,COUNT(*) qty FROM sc GROUP BY cid HAVING qty>5 ORDER BY qty DESC ;

--13、检索至少选修两门课程的学生学号

SELECT sid ,COUNT(*) FROM sc GROUP BY sid HAVING COUNT(*) >=2;

--14、查询选修了全部课程的学生信息

SELECT sid ,COUNT(*) aa  FROM sc GROUP BY sid HAVING aa=(SELECT COUNT(*) FROM course);

--15、查询各学生的年龄

SELECT * ,YEAR(NOW())-YEAR(sage) FROM student

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT  a.sid,a.score,b.score FROM (

SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.Cid=01 ) a,

(

SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.cid=02) b  WHERE a.sid=b.sid  AND a.score >b.score;

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN

(SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs>=60)s2 ON s1.Sid=s2.Sid ;

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN

(SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs<60)s2 ON s1.Sid=s2.Sid ;

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT DISTINCT s1.Sid,s1.Sname,s2.cc,s2.ss FROM student s1 INNER JOIN

(SELECT sid,COUNT(cid)cc,SUM(score)ss FROM sc GROUP BY sid) s2 ON s1.Sid=s2.Sid;

--6、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

SELECT DISTINCT * FROM student s1 INNER JOIN (SELECT * FROM sc WHERE cid=01 ORDER BY score DESC LIMIT 2) s2 ON s1.sid=s2.sid;

--7、查询本周过生日的学生

--8、查询下周过生日的学生

--9、查询本月过生日的学生

--10、查询下月过生日的学生

--11、查询每门功成绩最好的前两名

(SELECT *FROM sc WHERE cid =01 ORDER BY score DESC LIMIT 2)UNION

(SELECT *FROM sc WHERE cid =02 ORDER BY score DESC LIMIT 2)UNION

(SELECT *FROM sc WHERE cid =03 ORDER BY score DESC LIMIT 2);

--12、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

SELECT cid,COUNT(sid) cs FROM sc GROUP BY cid HAVING cs>5 ORDER BY cs DESC;

--13、检索至少选修两门课程的学生学号

SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=2;

--14、查询选修了全部课程的学生信息

SELECT * FROM Student s1 INNER JOIN (SELECT sid,COUNT(cid) cc FROM sc GROUP BY sid HAVING cc=3) s2 ON s1.sid=s2.sid

--15、查询各学生的年龄

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

四。1:查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

2:薪水大于12000的雇员,按照部门编号进行分组,分组后的平均薪水必须大于15000,查询各分组的平均工资,按照工资的倒序进行排列

3:查询每个雇员和其所在的部门名

4.查询每个雇员姓名及其工资所在的等级

5:查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。

6:查询每个雇员和其经理的姓名

-- 7:查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))

    SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- 8:查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)

  SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

-- 9:查询每个部门中工资最高的人的姓名、薪水和部门编号

    SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

    SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

-- 10:查询每个部门平均工资所在的等级

SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno

) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

-- 11:查询每个部门内平均的薪水等级

  SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

SELECT deptno,AVG(sal IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno

) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

-- 12:查询雇员中有哪些人是经理人

-- 13:不准用max函数,求雇员表中薪水的最高值。

  SELECT * FROM emp ORDER BY sal IFNULL(comm,0)  DESC LIMIT 0,1;

-- 14:平均薪水最高的部门的部门编号

    SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

-- 15:求平均薪水最高的部门的部门名称

    SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

-- 16:查询手下有员工的领导的信息

        SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- mysql联表查询练习:

-- 1.查询销售部工资大于20000的所有人;

    SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;

-- 2.查询每个部门的人数,要求显示部门名称;

SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

  SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;

-- 3.查询每个部门的最高工资,平均工资,最低工资 ,要求显示部门名称;

SELECT * FROM dept LEFT JOIN (

  SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno

-- 4.查询教研部中入职时间最早的员工信息,要显示部门名称,姓名,入职时间;

    SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;

-- 5.查询当前没有员工的部门信息;

SELECT * FROM (

  SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

编辑:计算机编程 本文来源:MYSQL 计数 最大值 最小值 平均数 求和

关键词: 必赢官网