JAVA八股文面试必会-基础篇-3.4 SQL语句练习

于 2024-06-10 11:03:36 发布
阅读量949 收藏 27
点赞数 33
文章标签： 面试 职场和发展 sql 数据库 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lky20011016/article/details/139574514
版权

一. SQL增删改语句语法

创建表

create table 表名(
    字段名称 类型 长度 约束,
    字段名称 类型 长度 约束,
    字段名称 类型 长度 约束
)

修改表

-- 修改表添加字段
alter table 表名 add 字段名称 类型 长度 约束
-- 修改表修改字段
alter table 表名 modify 字段名称 类型 长度 约束

删除表

drop table 表名

查看表结构

desc 表名

插入语句

-- 插入一条数据
insert into 表名(列1,列2....) value (值1,值2,值3...) 
    
-- 批量插入
insert into 表名(列1,列2....) values (值1,值2,值3...),  (值1,值2,值3...)

-- 想把一张表的数据查询出来, 直接导入到另一张表
select 字段1,字段2,字段3 from 表名1 into 表名2

修改语句

update 表名 set 字段名1= 字段值1 , 字段名2= 字段值2, 字段名3= 字段值3 where 条件

删除语句

delete from 表名 where 条件

查询语句

select 字段1,字段2,字段3... from 表名 where 条件 group by 分组字段 having 筛选后条件 order by 排序字段1, 排序字段1 limit 起始索引, 查询条数

二. 多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多(多对一)
  • 多对多
  • 一对一

2.1 一对多

  • 案例: 部门 与 员工的关系
  • 关系: 一个部门对应多个员工，一个员工对应一个部门
  • 实现: 在多的一方建立外键，指向一的一方的主键

互联网项目数据库表设计的过程中一般而言是不需要添加外键约束(逻辑外键) , 原因是外键约束会影响SQL语句的执行性能

传统项目一般都会加外键约束

2.2 多对多

  • 案例: 学生与课程的关系
  • 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

2.3 一对一

  • 案例: 用户 与 用户详情的关系
  • 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

唯一外键对应 : 在任意一方添加外键指向另一方的主键, 并且设置外键唯一

主键对应 : 让两个表的主键进行一一对应

三. 多表查询

3.1 数据准备

create table dept
(
    id   int auto_increment comment 'ID'
        primary key,
    name varchar(50) not null comment '部门名称'
)
comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部');
INSERT INTO dept (id, name) VALUES (2, '市场部');
INSERT INTO dept (id, name) VALUES (3, '财务部');
INSERT INTO dept (id, name) VALUES (4, '销售部');
INSERT INTO dept (id, name) VALUES (5, '总经办');
INSERT INTO dept (id, name) VALUES (6, '人事部');

create table emp
(
    id          int auto_increment comment 'ID'
        primary key,
    name        varchar(50) not null comment '姓名',
    age         int         null comment '年龄',
    job         varchar(20) null comment '职位',
    salary      int         null comment '薪资',
    entrydate   date        null comment '入职时间',
    managerid   int         null comment '直属领导ID',
    dept_id     int         null comment '部门ID',
    gender      char        null comment '性别',
    workaddress varchar(50) null comment '工作城市',
    constraint fk_emp_dept_id foreign key (dept_id) references dept (id)
) comment '员工表';

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5, '男', '北京');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1, '男', '上海');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1, '男', '长沙');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1, '男', '北京');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1, '男', '长沙');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1, '女', '武汉');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3, '女', '北京');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3, '女', '北京');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3, '女', '北京');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2, '女', '武汉');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2, '男', '武汉');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2, '男', '武汉');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2, '男', '上海');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4, '男', '杭州');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4, '男', '杭州');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4, '男', '上海');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id, gender, workaddress) VALUES (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null, '男', '杭州');

dept表共6条记录，emp表共17条记录。

概述

多表查询就是指从多张表中查询数据。

原来查询单表数据，执行的SQL形式为 ：select * from emp;

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ; 具体的执行结果如下:

此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录(17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单介绍下笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。

而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。

select * from emp , dept where emp.dept_id = dept.id;

而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。

分类

  • 连接查询
    • 内连接：相当于查询A、B交集部分数据
    • 外连接：
      • 左外连接：查询左表所有数据，以及两张表交集部分数据
      • 右外连接：查询右表所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名
  • 子查询

3.2 内连接

内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)

内连接查询的是满足条件的二张表的数据组合

内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。

1. 隐式内连接

SELECT  字段列表   FROM   表1 , 表2   WHERE   条件 ... ;

2. 显式内连接

SELECT  字段列表   FROM   表1  [ INNER ]  JOIN 表2  ON  连接条件 ... ;

案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)  --- INNER JOIN ... ON ...

select e.name, d.name from emp e inner join dept d  on e.dept_id = d.id;

-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d  on e.dept_id = d.id;

注意事项 : 一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

3.3 外连接

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

1. 左外连接

SELECT  字段列表   FROM   表1  LEFT  [ OUTER ]  JOIN 表2  ON  条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

查询左表所有数据以及满足连接条件的二张表数据组合

2. 右外连接

SELECT  字段列表   FROM   表1  RIGHT  [ OUTER ]  JOIN 表2  ON  条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

右表所有数据以及满足连接条件的二张表数据组合

案例:

A. 查询emp表的所有数据, 和对应的部门信息

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

B. 查询dept表的所有数据, 和对应的员工信息(右外连接)

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

如何选择使用内连接还是外连接  ?

  • 分析是否需要查询一个表的所有数据, 如果需要查询一个表的所有数据, 不管满足条件与否, 这个时候就需要使用外连接

内连接和外连接的区别 ?

  • 语法不同 , 内连接语法为 inner join , 也可以省略写成隐式内连接 ,  外连接语法是 outer join
  • 结果集不同
    • 内连接查询  : 内连接查询的是满足条件的二张表的数据组合
    • 左外连接 :  左外连接查询左表所有数据以及满足连接条件的二张表数据组合
    • 右外连接 :  右外连接右表所有数据以及满足连接条件的二张表数据组合
  • 使用场景不同
    • 内连接只能查询二张表交集数据, 外连接可以查询一张表的所有数据, 如果需要查询一张表所有数据, 不管满足条件与否, 就必须使用外连接

3.4 自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：

SELECT  字段列表   FROM   表A   别名A   JOIN  表A    别名B   ON  条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例：

A. 查询员工 及其 所属领导的名字

select a.name , b.name from emp a , emp b where a.managerid = b.id;

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

注意事项: 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

3.5 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT  字段列表   FROM   表A  ...  
UNION [ ALL ]
SELECT  字段列表  FROM   表B  ....;
  • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

案例:

A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们也可以通过union/union all来联合查询.

select * from emp where salary < 5000
union all
select * from emp where age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

select * from emp where salary < 5000
union
select * from emp where age > 50;

union 联合查询，会对查询出来的结果进行去重处理。

注意：

如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。如：

3.6 子查询

3.6.1 概述

1. 概念

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

2. 分类

根据子查询结果不同，分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

A. WHERE 之后

B. FROM 之后

C. SELECT 之后

3.6.2 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：=  <>  >   >=   <  <=

案例:

A. 查询 "销售部" 的所有员工信息

完成这个需求时，我们可以将需求分解为两步：

①. 查询 "销售部" 部门ID

select id from dept where name = '销售部';

②. 根据 "销售部" 部门ID, 查询员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');

B. 查询在 "方东白" 入职之后的员工信息

完成这个需求时，我们可以将需求分解为两步：

①. 查询 方东白 的入职日期

select entrydate from emp where name = '方东白';

②. 查询指定入职日期之后入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

3.6.3 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符

描述

IN

在指定的集合范围之内，多选一

NOT IN

不在指定的集合范围之内

ANY

子查询返回列表中，有任意一个满足即可

SOME

与ANY等同，使用SOME的地方都可以使用ANY

ALL

子查询返回列表的所有值都必须满足

案例:

A. 查询 "销售部" 和 "市场部" 的所有员工信息

分解为以下两步:

①. 查询 "销售部" 和 "市场部" 的部门ID

select id from dept where name = '销售部' or name = '市场部';

②. 根据部门ID, 查询员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

B. 查询比 财务部 所有人工资都高的员工信息

分解为以下两步:

①. 查询所有 财务部 人员工资

select id from dept where name = '财务部';

select salary from emp where dept_id = (select id from dept where name = '财务部');

②. 比 财务部 所有人工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );

C. 查询比研发部其中任意一人工资高的员工信息

分解为以下两步:

①. 查询研发部所有人工资

select salary from emp where dept_id = (select id from dept where name = '研发部');

②. 比研发部其中任意一人工资高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

3.6.4 行子查询

行子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、!= / <> 、IN 、NOT IN

案例:

A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

这个需求同样可以拆解为两步进行:

①. 查询 "张无忌" 的薪资及直属领导

select salary, managerid from emp where name = '张无忌';

②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

3.6.5 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

案例:

A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

分解为两步执行:

①. 查询 "鹿杖客" , "宋远桥" 的职位和薪资

select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

②. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

B. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

分解为两步执行:

①. 入职日期是 "2006-01-01" 之后的员工信息

select * from emp where entrydate > '2006-01-01';

②. 查询这部分员工, 对应的部门信息;

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

3.7 多表查询案例

数据环境准备:

create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

在这个案例中，我们主要运用上面所讲解的多表查询的语法，完成以下的12个需求即可，而这里主要涉及到的表就三张：emp员工表、dept部门表、salgrade薪资等级表 。

1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）

select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;

2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

3. 查询拥有员工的部门ID、部门名称

select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;

4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;

5. 查询所有员工的工资等级

-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;

6. 查询 "研发部" 所有员工的信息及 工资等级

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部';

7. 查询 "研发部" 员工的平均工资

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

8. 查询工资比 "灭绝" 高的员工信息。
①. 查询 "灭绝" 的薪资

select salary from emp where name = '灭绝';

②. 查询比她工资高的员工数据

select * from emp where salary > ( select salary from emp where name = '灭绝' );

9. 查询比平均薪资高的员工信息
①. 查询员工的平均薪资

select avg(salary) from emp;

②. 查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp );

10. 查询低于本部门平均工资的员工信息

①. 查询指定部门平均薪资

select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;

②. 查询低于本部门平均工资的员工信息

-- 方式一 : 
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );

-- 方式二 : 
select emp.* from emp left join (select dept_id ,avg(salary) avg from emp group by dept_id) t on emp.dept_id = t.dept_id where emp.salary < t.avg;

11. 查询所有的部门信息, 并统计部门的员工人数

select
    dept.id,dept.name ,sum(if(emp.id is null ,0,1)) 人数
from
    dept left join emp on dept.id = emp.dept_id
group by dept.id,dept.name ;

备注: 以上需求的实现方式可能会很多, SQL写法也有很多，只要能满足我们的需求，查询出符合条件的记录即可。

3.8 行列转行查询

1. 查询每个城市男女员工的数量

select  workaddress , sum(case gender when '男' then 1 else 0 end ) '男', sum(case gender when '女' then 1 else 0 end )  '女'  from emp group by workaddress

2. 假如 (0-3000] 为低收入  ,(3000-10000]为中收入 , (10000~]为高收入 , 统计每个部门中各种收入群体的人数

select
    dept.id ,
    dept.name ,
    sum(case  when salary between 0 and 3000 then 1 else 0 end) '低收入' ,
    sum(case  when salary between 3000 and 10000 then 1 else 0 end) '中收入',
    sum(case  when salary > 10000 then 1 else 0 end) '高收入'
from
    emp , dept
where
    emp.dept_id = dept.id
group by dept.id , dept.name

3. 统计每个部门男女员工的平均薪资

select
    dept.id,
    dept.name,
    floor(avg(case gender when '男' then salary else null end)) 男,
    floor(avg(case gender when '女' then salary else null end)) 女
from
    emp , dept
where
    emp.dept_id = dept.id
group by
    dept.id,dept.name

四. MYSQL常用开窗函数

窗口函数也称为分析函数，意思是对数据库数据进行实时分析处理的函数 , 开窗函数大体可以分为以下两种：

  1. 能够作为开窗函数的聚合函数（sum，avg，count，max，min）
  2. rank，dense_rank，row_number 等专用开窗函数。
  • row_number : 为组内每一条数据生成行号 , 按行号进行排序 , 相同的数据也按顺序排

10 20 30 30 40 50

1 2 3 4 5 6

  • rank : 为组内每一条数据生成排名(数据相同并列) , 下一跳数据不连续(1,2,2,4)

10 20 30 30 40 50

1 2 3 3 5 6

  • dense_rank : 为组内每一条数据生成排名(数据相同并列) ,下一跳数据连续(1,2,2,3)

10 20 30 30 40 50

1 2 3 3 4 5

有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。

开窗函数和聚合函数的区别

1. 聚合函数每组只返回一个值，开窗函数每组可返回多个值

2. SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。

注：常见主流数据库目前都支持开窗函数，MYSQL数据库8.0版本开始支持开窗函数

开窗函数的基本语法 :

<开窗函数> over ([partition by <列清单>]  order by <排序用列清单>)

开窗函数示例 : 

CREATE TABLE `student_score` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` char(10) DEFAULT NULL,
    `course` char(10) DEFAULT NULL,
    `score`  int (2) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ;

INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小张','语文',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小李','语文',90);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小王','语文',70);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小赵','语文',56);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小杨','语文',70);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小郭','语文',99);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小钱','语文',86);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小谭','语文',87);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小何','语文',80);

INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小张','数学',70);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小李','数学',100);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小王','数学',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小赵','数学',76);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小杨','数学',55);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小郭','数学',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小钱','数学',79);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小谭','数学',54);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小何','数学',30);


INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小张','英语',90);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小李','英语',85);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小王','英语',99);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小赵','英语',86);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小杨','英语',95);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小郭','英语',60);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小钱','英语',89);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小谭','英语',84);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小何','英语',70);



# 用户登录表
create table user_login
(
    user_id varchar(100),
    login_time datetime
);
# 插入用户登录数据
insert into user_login values
(1,'2022-11-25 13:21:12'),
(1,'2022-11-24 13:15:22'),
(1,'2022-11-24 10:30:15'),
(1,'2022-11-24 09:18:27'),
(1,'2022-11-23 07:43:54'),
(1,'2022-11-10 09:48:36'),
(1,'2022-11-09 03:30:22'),
(1,'2022-11-01 15:28:29'),
(1,'2022-10-31 09:37:45'),
(2,'2022-11-25 13:54:40'),
(2,'2022-11-24 13:22:32'),
(2,'2022-11-23 10:55:52'),
(2,'2022-11-22 06:30:09'),
(2,'2022-11-21 08:33:15'),
(2,'2022-11-20 05:38:18'),
(2,'2022-11-19 09:21:42'),
(2,'2022-11-02 00:19:38'),
(2,'2022-11-01 09:03:11'),
(2,'2022-10-31 07:44:55'),
(2,'2022-10-30 08:56:33'),
(2,'2022-10-29 09:30:28');

需求一 : 查询每门课的学生考试成绩排名

-- 需求一 : 查询每门课的学生考试成绩排名
-- 无法生成排名
select  * from student_score order by course , score desc ;

-- row_number : 组内按照顺序生成行号
SELECT
    `name`,
    `course`,
    `score`,
    rank()   over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    student_score ;

需求二 : 查询每门课 , 考试成绩最好的学生信息

select  * from (
SELECT
    `name`,
    `course`,
    `score`,
    rank()   over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    student_score
) t where t.score_rank = 1

需求三 : 查询每门课 , 考试成绩前三名的学生信息

select  * from (
SELECT
    `name`,
    `course`,
    `score`,
    rank()   over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    student_score
) t where t.score_rank <=3

需求四 : 查询所有学生各科学生的总分排名

select * ,row_number() over () r  from (
    select name , sum(score) s  from student_score group by name order by s desc
) t ;

需求五 : 查询所有学生各科学生的总分前3名

-- 需求五 : 查询所有学生各科学生的总分排名
select * from (
  select t.* , row_number() over () 排名 from (
      select name,sum(score) 总分  from student_score group by name order by sum(score) desc
    ) t
) t2 where t2.排名 <=3 ;

需求六 : 查询连续登录超过5天的用户信息

select distinct user_id from (
  -- 登录日期减去排名得到最早登录日期的前一天 , 如果剪完之后日期相同代表是连续的
  select t2.user_id, login_date, date_add(login_date,interval -irank day ) idate from (
      -- 根据登录时间对用户登录日期进行排序 , 加排名
    	select t.user_id, t.login_date ,rank() over (partition by user_id order by login_date) irank from (
          -- 将登录时间转化为 日期格式(年月日)
          select distinct user_id,date(login_time) login_date from user_login
        ) t
    ) t2
) t3 group by user_id , idate having count(idate) >= 5 ;
  1. 对用户登录时间去重
  2. 根据用户登录日期进行排序, 使用rank()开窗函数加上排名
  3. 使用登录日期减去排名会得到连续最早登录的前一天的时间 , 如果减完之后时间相同的数据就代表连续的数据
  4. 对用户和时间进行分组, 获取组内数量 >=5 的用户信息

需求七 : 查询每个用户连续登陆的最大天数

select t4.user_id,max(c) maxDay from (
  select distinct user_id , count(idate) c from (
      select t2.user_id, login_date, date_add(login_date,interval -irank day ) idate from (
          select t.user_id, t.login_date ,rank() over (partition by user_id order by login_date) irank from (
              select distinct user_id,date(login_time) login_date from user_login
            ) t
        ) t2
    ) t3 group by user_id , idate
) t4 group by t4.user_id
  1. 对用户登录时间去重
  2. 根据用户登录日期进行排序, 使用rank()开窗函数加上排名
  3. 使用登录日期减去排名会得到连续最早登录的前一天的时间 , 如果减完之后时间相同的数据就代表连续的数据
  4. 对用户和时间进行分组, 获取组内数量
  5. 再对用户进行分组 , 获取组内数量最大值, 就是连续登录的最大天数

五. SQL练习

5.1 需求说明

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

根据以上信息按照下面要求写出对应的SQL语句

四张表的表关系如下图所示

执行SQL语句导入数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `tid` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `score` float(4, 1) NOT NULL,
  PRIMARY KEY (`sid`, `cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 80.0);
INSERT INTO `score` VALUES (1, 2, 90.0);
INSERT INTO `score` VALUES (1, 3, 99.0);
INSERT INTO `score` VALUES (2, 2, 60.0);
INSERT INTO `score` VALUES (2, 3, 80.0);
INSERT INTO `score` VALUES (3, 1, 80.0);
INSERT INTO `score` VALUES (3, 2, 80.0);
INSERT INTO `score` VALUES (3, 3, 80.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `birthday` date NOT NULL,
  `sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '猴子', '1989-01-01', '男');
INSERT INTO `student` VALUES (2, '猴子', '1990-12-21', '女');
INSERT INTO `student` VALUES (3, '马云', '1991-12-21', '男');
INSERT INTO `student` VALUES (4, '王思聪', '1990-05-20', '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '孟扎扎');
INSERT INTO `teacher` VALUES (2, '马化腾');
INSERT INTO `teacher` VALUES (3, NULL);
INSERT INTO `teacher` VALUES (4, '');

SET FOREIGN_KEY_CHECKS = 1;

5.2 练习题目

  1. 查询姓"猴"的学生名单
  2. 查询名字中最后一个字是"猴"的学生名单
  3. 查询名字中带"猴"的学生名单
  4. 查询姓“孟”老师的个数
  5. 查询课程编号为“2”的总成绩和课程名称
  6. 查询选了课程的学生人数
  7. 查询各科成绩最高和最低的分， 以如下的形式显示：课程号,课程名称，最高分，最低分
  8. 查询每门课程被选修的学生数 , 展示课程号,课程名称 , 学生数
  9. 查询男生、女生人数
  10. 查询平均成绩大于60分学生的学号和平均成绩
  11. 查询至少选修两门课程的学生信息
  12. 查询同名同姓学生名单并统计同名人数
  13. 查询不及格的课程并按课程号从大到小排列
  14. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
  15. 检索课程编号为“0004”且分数小于60的学生学号和分数，结果按按分数降序排列
  16. 统计每门课程的学生选修人数(超过2人的课程才统计)要求输出课程号,课程名称和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
  17. 查询两门以上不及格课程的同学的学号,姓名及其平均成绩
  18. 查询学生的总成绩并进行排名
  19. 查询所有课程成绩均小于60分学生的学号、姓名
  20. 查询没有学全所有课的学生的学号、姓名
  21. 查询出只选修了两门课程的全部学生的学号和姓名
  22. 查询1990年出生的学生名单
  • 需要用到日期函数

  1. 查询各学生的年龄（精确到月份）
  2. 查询本月过生日的学生
  3. 查询所有学生的学号、姓名、选课数、总成绩
  4. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
  5. 查询学生的选课情况：学号，姓名，课程号，课程名称
  6. 查询出每门课程的及格人数和不及格人数
  7. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
  8. 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
  9. 查询每个学生的各科成绩, 展示效果如下图所示

  1. 检索"0001"课程分数小于60，按分数降序排列的学生信息
  2. 查询不同老师所教不同课程平均分从高到低显示
  3. 查询课程名称为"数学"，且分数低于60的学生姓名和分数
  4. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
  5. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
  6. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
  7. 查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号
  8. 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
  9. 查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
  10. 查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名
  11. 查询没学过“孟扎扎”老师课的学生的学号、姓名
  12. 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩
  13. 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
  14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
  15. 查询每门课程排名前三的学生信息
  16. 查询每位学生各科成绩的排名

5.3 练习答案

# 1. 查询姓"猴"的学生名单
SELECT * FROM student WHERE `name` like '猴%' ;

# 2. 查询名字中最后一个字是"猴"的学生名单
SELECT * FROM student WHERE `name` like '%猴' ;

# 3. 查询名字中带"猴"的学生名单
SELECT * FROM student WHERE `name` like '%猴%' ;

# 4. 查询姓“孟”老师的个数
SELECT count(1) FROM teacher WHERE `name` like '孟%' ;

# 5. 查询课程编号为“2”的总成绩和课程名称
SELECT c.`name` , SUM(s.score) FROM course c , score s WHERE c.id = s.cid AND c.id = 2

# 6. 查询选了课程的学生人数
SELECT COUNT(DISTINCT student.id) FROM student LEFT JOIN score ON student.id = score.sid WHERE score.cid IS NOT NULL ; 
# 7. 查询各科成绩最高和最低的分， 以如下的形式显示：课程号,课程名称，最高分，最低分
SELECT  c.id ,c.`name` , MAX(score) 最高分 , MIN(score) 最低分 FROM course c , score s WHERE c.id = s.cid GROUP BY c.id ,c.`name`;

# 8. 查询每门课程被选修的学生数 , 展示课程号,课程名称 , 学生数
SELECT c.id , c.`name`,COUNT(1) FROM course c LEFT JOIN score s ON c.id = s.cid GROUP BY c.id , c.`name`;

# 9. 查询男生、女生人数
SELECT sex , count(1) 人数 FROM student GROUP BY  sex ;

# 10. 查询平均成绩大于60分学生的学号和平均成绩

SELECT
	student.id, student.`name`, AVG( score ) 平均成绩 
FROM
	student,
	score 
WHERE
	student.id = score.sid 
GROUP BY
	student.id 
HAVING
	平均成绩 > 60;

# 11. 查询至少选修两门课程的学生信息
SELECT student.* FROM student , score WHERE student.id = score.sid GROUP BY student.id HAVING count(1)>=2

# 12. 查询同名同姓学生名单并统计同名人数
SELECT
	s1.`name` 姓名, COUNT( 1 ) 数量 
FROM
	student s1, student s2 
WHERE
	s1.`name` = s2.`name` 
	AND s1.id != s2.id 
GROUP BY
	s1.`name`,
	s2.`name`;
	
# 13. 查询存在不及格成绩的课程并按课程号从大到小排列
SELECT * from score WHERE score < 60 ORDER BY score DESC ;

# 14. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT
	c.id, c.`name`, AVG( s.score ) 平均成绩 
FROM
	course c, score s 
WHERE
	c.id = s.cid 
GROUP BY
	c.id,
	c.`name` 
ORDER BY
	平均成绩 ASC,
	c.id DESC ;

# 15. 检索课程编号为“4”且分数小于60的学生学号和分数，结果按按分数降序排列
SELECT sid , score FROM score WHERE cid = 4 AND score < 60 ORDER BY score DESC ;

# 16. 统计每门课程的学生选修人数(超过2人的课程才统计)
#     要求输出课程号,课程名称和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT
	c.id, c.`name`, COUNT( DISTINCT s.sid ) 选课人数 
FROM
	course c, score s 
WHERE
	c.id = s.cid 
GROUP BY
	c.id,
	c.`name` 
HAVING
	选课人数 > 2 
ORDER BY
	选课人数 DESC, 	c.id ASC ;
	
# 17. 查询两门以上不及格课程的同学的学号,姓名及其平均成绩
SELECT
	student.id, student.`name`, COUNT( DISTINCT score.cid ) 数量, AVG( score.score ) 
FROM
	student,
	score 
WHERE
	student.id = score.sid  AND score.score < 60 
GROUP BY 
	student.id, student.`name` 
HAVING 数量 >2

# 18. 查询学生的总成绩并进行排名

SELECT
	student.id, student.NAME, SUM( score.score ) 总成绩 
FROM
	student, score 
WHERE
	student.id = score.sid 
GROUP BY
	student.id,
	student.NAME 
ORDER BY
	总成绩 DESC;

# 19. 查询所有课程成绩均小于60分学生的学号、姓名
SELECT * from student WHERE id not in (
	SELECT s.sid from course c ,score s WHERE c.id = s.cid AND s.score >=60 
) ;

# 20. 查询没有学全所有课的学生的学号、姓名
SELECT DISTINCT
	t.sid, t.sname 
FROM
	( SELECT student.id sid, student.NAME sname, course.id cid, course.NAME cname FROM student, course ) t
	LEFT JOIN score s ON t.sid = s.sid 
	AND t.cid = s.cid 
WHERE
	s.sid IS NULL;

# 21. 查询出只选修了两门课程的全部学生的学号和姓名
SELECT
	student.id, student.`name` 
FROM
	student,
	score 
WHERE
	student.id = score.sid 
GROUP BY
	score.sid 
HAVING
	COUNT( DISTINCT score.cid )= 2
# 22. 查询1990年出生的学生名单
SELECT * FROM student WHERE YEAR(birthday)= '1990' ;

# 23. 查询各学生的年龄
SELECT * ,IF(MONTH(birthday)-MONTH(NOW())>0,YEAR(now())-YEAR(birthday)-1,YEAR(now())-YEAR(birthday)) FROM student ; 

# 24. 查询本月过生日的学生
SELECT * FROM student WHERE MONTH(birthday)= MONTH(CURRENT_DATE) ;

 
# 25. 查询所有学生的学号、姓名、选课数、总成绩
SELECT
	student.id, student.`name`, COUNT( DISTINCT score.cid )选课数, SUM( score.score ) 总成绩
FROM
	student LEFT JOIN score ON student.id = score.sid 
GROUP BY
	student.id,
	student.`name`

# 26. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT
	student.id, student.`name`, AVG(score.score) 平均成绩
FROM
	student LEFT JOIN score ON student.id = score.sid 
GROUP BY
	student.id,
	student.`name`
HAVING 
	平均成绩 > 85 ;
 
# 27. 查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT
	student.id 学号,
	student.`name` 姓名,
	course.id 课程号,
	course.`name` 课程名称 
FROM
	student
	LEFT JOIN score ON student.id = score.sid
	LEFT JOIN course ON score.cid = course.id ;
 
# 28. 查询出每门课程的及格人数和不及格人数
SELECT
	c.id, c.`name`,
	SUM( IF ( s.score < 60, 1, 0 )) 不及格人数,
	SUM( IF ( s.score >= 60, 1, 0 )) 及格人数 
FROM
	course c, score s 
WHERE
	c.id = s.cid 
GROUP BY
	c.id, c.`name`
 
# 29. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
SELECT
	c.id, c.`name`,
	SUM( IF ( s.score >= 85 AND s.score <= 100 , 1, 0 )) '[100-85]',
	SUM( IF ( s.score >= 70 AND s.score < 85 , 1, 0 )) '[85-70]',
	SUM( IF ( s.score >= 60 AND s.score < 70 , 1, 0 )) '[70-60]',
	SUM( IF ( s.score < 60 , 1, 0 )) '[<60]'
FROM
	course c, score s 
WHERE
	c.id = s.cid 
GROUP BY
	c.id, c.`name`

# 30. 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
SELECT * from student WHERE id in (
	SELECT sid from score WHERE cid = 3 AND score >80
)

# 31. 查询每个学生的各科成绩
SELECT student.id , student.name, 
SUM(CASE course.id
	WHEN 1 THEN
		score.score
	ELSE
		0
END ) '语文',
SUM(CASE course.id
	WHEN 2 THEN
		score.score
	ELSE
		0
END )'数学',
SUM(CASE course.id
	WHEN 3 THEN
		score.score
	ELSE
		0
END )'英语',
SUM(CASE course.id
	WHEN 4 THEN
		score.score
	ELSE
		0
END )'物理'
FROM student , score ,course WHERE student.id = score.sid AND score.cid = course.id GROUP BY student.id , student.name ;

# 32. 检索"0001"课程分数小于60，按分数降序排列的学生信息
SELECT * FROM student , score WHERE student.id = score.sid AND score.cid = 1 AND score.score <60 ORDER BY score.score desc ;


# 33. 查询不同老师所教不同课程平均分从高到低显示
SELECT
	t.`name` 教师, c.`name` 课程, 	AVG( s.score ) 平均分 
FROM
	teacher t,
	score s,
	course c 
WHERE
	t.id = c.tid 
	AND c.id = s.cid 
GROUP BY
	t.id,
	t.`name`,
	c.id,
	c.`name` 
ORDER BY
	平均分 DESC

# 34. 查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT
	st.NAME, 	s.score 
FROM
	course c,
	score s,
	student st 
WHERE
	c.id = s.cid 
	AND s.sid = st.id 
	AND c.`name` = '数学' 
	AND s.score < 60

# 35. 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
SELECT
	st.NAME,c.`name`, 	s.score 
FROM
	course c,
	score s,
	student st 
WHERE
	c.id = s.cid 
	AND s.sid = st.id 
	AND s.score > 70
	
 
# 36. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
	student.id,
	student.NAME,
	AVG( score.score ) 平均成绩 
FROM
	student,
	score 
WHERE
	student.id = score.sid 
	AND score.score < 60 GROUP BY student.id, student.NAME HAVING count( DISTINCT score.cid ) >=2 ;

# 37. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT s1.sid,s1.cid,s1.score FROM score s1 , score s2 WHERE s1.sid = s2.sid AND s1.score = s2.score AND s1.cid !=s2.cid 

# 38. 查询课程编号为“1”的课程比“2”的课程成绩高的所有学生的学号 

SELECT t1.sid FROM 
	(SELECT * from score WHERE cid =1 ) t1,
	(SELECT * from score WHERE cid =2)  t2
WHERE
	t1.sid = t2.sid and t1.score > t2.score ;

 
# 39. 查询学过编号为“1”的课程并且也学过编号为“2”的课程的学生的学号、姓名
SELECT s.id , s.`name` FROM 
	student s ,
	(SELECT * from score WHERE cid =1 ) t1,
	(SELECT * from score WHERE cid =2)  t2
WHERE
	s.id = t1.sid and t1.sid = t2.sid ;
	
	

# 40. 查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
SELECT
	student.* 
FROM
	student, score 
WHERE
	student.id = score.sid 
	AND score.cid IN ( SELECT c.id FROM teacher t, course c WHERE t.id = c.tid AND t.`name` = '孟扎扎' ) 
GROUP BY
	score.sid 
HAVING
	COUNT( DISTINCT score.cid ) = ( SELECT count( 1 ) FROM teacher t, course c WHERE t.id = c.tid AND t.`name` = '孟扎扎' );

# 41. 查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名
SELECT * from student WHERE id not in 
(
	SELECT sc.sid  FROM teacher t, course c , score sc WHERE t.id = c.tid AND sc.cid = c.id  AND t.`name` = '孟扎扎' 
)


# 42. 查询没学过“孟扎扎”老师课的学生的学号、姓名
SELECT * from student WHERE id not in 
(
	SELECT sc.sid  FROM teacher t, course c , score sc WHERE t.id = c.tid AND sc.cid = c.id  AND t.`name` = '孟扎扎' 
);


# 43. 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT st.id , st.name , sc.score  FROM teacher t, course c , score sc , student st WHERE t.id = c.tid AND sc.cid = c.id and sc.sid = st.id  AND t.`name` = '孟扎扎' ORDER BY sc.score DESC limit 1 ;

# 44. 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
SELECT * from student WHERE id in 
(SELECT s.sid from score s INNER JOIN (SELECT * from score WHERE sid = 1) t ON s.cid = t.cid WHERE s.sid !=t.sid
);

# 45. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
	s.sid,
	avg( s.score ) 平均成绩,
	max( CASE WHEN c.NAME = '数学' THEN s.score ELSE NULL END ) AS '数学',
	max( CASE WHEN c.NAME = '语文' THEN s.score ELSE NULL END ) AS '语文',
	max( CASE WHEN c.NAME = '英语' THEN s.score ELSE NULL END ) AS '英语' 
FROM
	score s
	INNER JOIN course c ON s.cid = c.id 
GROUP BY
	s.sid;
吉迪恩
关注
Java面试SQL查询语句突击练习
07-02
Java面试SQL查询语句突击练习突击，对你面试绝对有帮助。请放心下载学习
练习用基础SQL语句
weixin_34413065的博客
05-14 105
本文语句大部分SQL语句来自《数据库系统概论》（第四版）王珊&amp;萨师煊 ，是我们上课用的教材，感觉很不错，总结了其中第三章的SQL语句，比较实用，希望对大家有帮助。总结如下，可以用来学习基础的SQL语句。   建立数据库 CREATE DATABASE DB_Student   建立表 CREATE TABLE Student     (Sno CHAR(9) PRIMARY ...
面试宝典----数据库(总结来自知乎路人甲)
Sinkiang的博客
12-26 4136
一）什么是存储过程？有哪些优缺点？ 存储过程是一些预编译的SQL语句。 更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。 存储过程是一个预编译的代码块，执行效率比较高 一个存储过程替代大量T_SQL语句
java面试（SQL篇）
qq_33146819的博客
04-09 1075
一、数据库的事务性 1.数据库事务的四大原则ACID A:原子性。数据库事务的操作是一个不可以分割的工作单位，要么全部发生，要么全不发生 C:一致性。事务开始以前和结束以后，数据库的完整性不会被破坏。完整性指的是关系数据的完整性和业务逻辑上的一致性。例如经典的转账案例，转账前后账户内的存款总额不能改变 I:隔离性。两个事务之间互相不受影响。 D：持久性，事务一旦执行完毕，生成的结果会被...
java mysql语句练习_【Java】java.sql数据库操作简单练习
weixin_32384503的博客
01-19 145
1 packageDatabaseTest;23 import java.awt.*;4 import java.awt.event.*;5 import javax.swing.*;6 import java.sql.*;78 public class DatabaseConnect extends JFrame implementsActionListener {910 String ...
Java面试八股文001-Java-Java基础
最新发布
07-05
### Java基础知识点详解 #### 1. Java中的几种基本数据类型及其对应的包装类型与占用空间 Java中的基本数据类型共有8种，它们分别是： - `byte`：字节类型，占用1字节（8位）。 - `short`：短整型，占用2字节（16...
Java面试宝典合集-八股文-自我介绍-案例
09-11
多年积攒的Java面试资源，非常全面，毕业生如果静下心啃透这些资料，找找工作如探囊取物：愿每一位java学弟，毕业就找到自己心仪的工作！加油！！！ [精选视频教学]直击面试系列_破解Java面试题wmv 10万字总结.pdf ...
《JAVA面试题》--新版Java面试专题视频教程，java八股文面试全套真题+深度详解（含大厂高频面试真题）.zip
04-02
十余年JAVA从业经验，精通JAVA技术体系，有志于做JAVA技能提升的朋友可与我联系，交个朋友 十余年JAVA从业经验，精通JAVA技术体系，有志于做JAVA技能提升的朋友可与我联系，交个朋友 十余年JAVA从业经验，精通JAVA...
前端面试八股文面试必备题-zhaowa
10-06
适合人群：将要进行Web前端开发岗位面试的小伙伴，具备一定编程基础，学习过Web前端 注意：本资源为面试题而非教程。 阅读建议：一遍过，建立起知识体系结构，反复温习加深记忆，最后手动验证，也可以参看其他文章，...
《JAVA面试题》--Java面试八股文.zip
04-02
十余年JAVA从业经验，精通JAVA技术体系，有志于做JAVA技能提升的朋友可与我联系，交个朋友 十余年JAVA从业经验，精通JAVA技术体系，有志于做JAVA技能提升的朋友可与我联系，交个朋友 十余年JAVA从业经验，精通JAVA...
java 面试题基础题，sql 面试题 带答案
07-18
java 面试题基础题，sql 面试题 带答案
JAVA——SQL练习
Li_1_234的博客
08-28 435
首先创建一个表单student 【成绩表sc】 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(...
java sql 练习
weixin_48322694的博客
05-07 583
UserDao 代码 //根据用户名查询用户信息 public User selectUser(String username) throws SQLException { String sql = "select * from user where username = ?"; User user = qr.query(sql,new BeanHandler<User>(User.class),username); return
java面试题 sql_SQL 面试题目及答案 | java面试题
weixin_34835470的博客
02-12 727
学生成绩表(stuscore)：姓名：name 课程：subject 分数：score 学号：stuid张三 数学 89 1张三 语文 80 1张三 英语 70 1李四 数学 90 2李四 语文 70 2李四 英语 80 21.计算...
java初级面试题（sql方面）
The_flying_pig的博客
12-18 4766
下面我给大家带来一些数据库方面面试题。 下列的链接是我看到非常好的java基础面试题的博文推荐给大家。 http://blog.csdn.net/jackfrued/article/details/44921941/ 关系数据库这一块是非常重要的，对于我们初级程序员，数据库考察的很大一部分知识其实就是数据库语言的掌握了，至于使用的是mysql、sqlserver、oracle等其中的哪种
Java面试需要知道的SQL语句语法（二）
Wz1135640的博客
11-09 665
12、使用外连接  A、left outer join：  左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。  sql: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B：right outer join:  右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括
SQL练习和Java作业
zwyoozwz的博客
05-19 641
1、 有一个员工表dept_emp简况如下: emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1995-12-03 9999-01-01 第一行表示为员工编号为10001的部门是d001部门。 有一个部门经理表dept_manager简况如下:
Java面试需要知道的SQL语句语法（一）
Wz1135640的博客
11-09 510
DDL—数据定义语言(Create，Alter，Drop，DECLARE) DML—数据操纵语言(Select，Delete，Update，Insert) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、创建数据库 Create DATABASE database-name  2、删除数据库 dr
java小记03_常用SQL语句
qq_43175961的博客
11-05 153
数据库 查所有数据库 show databases; 创建数据库 create database db1; 查看数据库 show create database db1; 创建数据库指定字符集 create database db1 character set utf8/gbk 删除数据库 drop database db1; 使用数据库 use db1; 创建表 创建表 create tabl...
Java八股文面试详解：基础、与C++区别、面向对象解析
"Java八股文面试基础涵盖了Java的特点、Java与C++的区别、面向对象与面向过程的差异以及JDK和JRE的区别，旨在帮助求职者深入理解Java编程的基础知识，以应对面试中的常见问题。" Java作为一门广泛使用的编程语言，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

吉迪恩

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值