分页查询和视图的含义及小案例

ゝ一世哀愁。 2022-05-11 09:28 152阅读 0赞

分页查询

需求：

每次查询的记录较大，通常一页显示不下，此时我们可以进行分页查询。

关键字：limit

用法：limit begin,size;

begin:记录的开始行数，偏移量

size：每一页最大记录数

注意：limit后面可以是一个参数，一个参数时是指每次最大的查询记录数limit size；

默认记录数从0开始，而不是1

\--练习1:查询员工表，查询第一页的数据，每页10条。

select * from emp limit 0,10;

\--练习2:查询员工表，查询第二页的数据，每页5条。

select * from emp limit 5,5;

\--练习3:按照部门号升序排序，查询第二页的数据，每页5条

select * from emp order by deptno  limit 5,5;

\--练习4:按照员工编号降序排序，查询第二页的数据，每页5条。

select * from emp order by empno desc limit 5,5;

\--练习5:使用子查询，先排序，再查询每页的数据。

select e.* from (select * from emp order by sal desc) e limit 6,6;

\-- 执行效率与优化

\--- 创建了一张表temp\_001,插入了10w条的数据。

\- 需求: 每页100条记录，查询第1页的数据

select * from temp_001 limit 100;

\-- 查询第11页的数据

--select * from temp_001 limit 1000,100;

\-- 查询第101页的数据

--select * from temp_001 limit 10000,100;

\-- 查询第1001页的数据

--select * from temp_001 limit 100000,100;

当我们在做分页查询的时候，查询的效率与偏移量有关系，偏移量越大， 效率越低，消耗的时间就会越多。 如上述分页查询

如何优化分页查询

(1)我们可以使用子查询先确定作为tid偏移量的位置。

\-- (2)再进行分页查询

select * from where tid>=(查出id的值) limit 100;

\--练习:

select * from temp_001 where tid>=
    (select tid from temp_001 limit 144000,1) limit 100;

\-- 使用 between and

select * from temp_001 
    where 
    tid between 145000 and 145200 limit 100;

**视图**:VIEW，表的一部分投影。也是数据库里一个对象。(表的虚拟部分)

视图的作用: 因为视图中含有某表中的部分数据，不在视图中的字段有隐藏效果。相对来说，安全。

**--如何操作视图呢？**视图可以像表一样，进行增删改查。

**--如何创建视图呢？**

\--格式： create view ViewName as subQuery

\--练习1: 将员工表中的10部门的数据封装到一个视图view\_emp\_deptno\_10

create view view_emp_deptno_10 as select * from emp where deptno=10;

\-- 通过视图进行DML操作，一定会对表有影响。

\-- 通过表进行DML操作，可能会对视图有影响。

\--练习2:通过视图，插入数据 empno:9001,ename:'zs',部门号:10

\-- 插入数据 empno:9002,ename:'ls',部门号:20

\-- 查询表emp,查询视图view\_emp\_deptno\_10

insert into view_emp_deptno_10 
    values (9001,'zs',null,null,null,null,null,10);
    insert into view_emp_deptno_10 (empno,ename,deptno)
    values (9002,'ls',20);
    select * from view_emp_deptno_10;

\--with check option:对视图的一种检查约束选项

如果在创建视图有此选项时，表示，只能对视图DML操作可见数据。

反之，对视图不可见的数据，是不可以通过视图进行DML操作的。

\--练习2:创建一个视图view\_emp\_deptno\_20

create view view_emp_deptno_20 as select empno,ename,job,sal,deptno
    from emp where deptno = 20 with check option;
    select * from view_emp_deptno_20;

\--练习3:通过视图view\_emp\_deptno\_20插入数据

\-- empno:9003,ename:'ww',job:manager,sal:5000,20

\-- empno:9004,ename:'zl',job:manager,comm:500,20

\-- empno:9005,ename:'qq',job:manager,sal:500,10

insert into  view_emp_deptno_20 values(9003,'ww','manager',5000.0,20);
    insert into  
    view_emp_deptno_20(empno,ename,job,comm,deptno)values
    (9004,'zl','manager',500.0,20);
    insert into  view_emp_deptno_20 values(9005,'qq','manager',5000.0,10);

\--视图的分类：

\-- 简单视图:subQuery是一个表中的普通查询语句

\-- 复杂视图:subQuery是一个可以带有分组函数的，或者关联查询的语句。

\-- 复杂视图不能对视图进行DML操作，只能进行DQL操作

\--练习:创建一个复杂视图view\_emp\_100，是每个部门的平均工资，最高工资，最低工资，及其部门号。

create view view_emp_100 as select avg(ifnull(sal,0)) avg_sal,max(sal) max_sal,min(sal) min_sal,deptno from emp group by deptno;
    select * from view_emp_100;

索引(Index):目的是为了提高查询速度。相当于一本书的目录。

索引也是数据库的对象，占空间。每张表每个字段都可以设置相应索引。

优点: 提高查询速度。

缺点: 占空间，每次进行DML操作时，数据库都要(自动)重新维护索引，降低效率.

总体来说:表中有索引可以提高效率，但不是索引越多越好。当表中的数据量比较小时，无需索引(因为直接查询可以比使用索引更快)，当某个字段的值比较少时，也不需要索引，如性别字段只有'f','m'.只有当数据量比较大，和字段值多时，可以使用索引。

\--练习1:给emp表中的empno创建索引。

\--练习2:查询empno为9003的数据

\--练习1:给emp表中的empno创建索引。

create index index_emp_empno on emp(empno);

\--练习2:查询empno为9003的数据。

select * from emp where empno=9003;

\--删除索引，删除视图都是用drop

--drop index  indexName
    --drop view   viewName

\--练习：数据库中有以下学生信息表：

\--Student学生表（SNO姓名，SEX性别，BIRTHDAY出生日期，DEP院系

\--Course课程表（CNO编号，CNAME课程名称，CVAL学分）

\--SC选课表（SNO学号，CNO课程编号，SCORE成绩）

create table student(sno int,sname varchar(20),dep varchar(20));
    
    create table Course(cno int,cname varchar(20),cval varchar(20));
    
    create table SC(sno int,cno varchar(20),score varchar(20));

\--1.写一个SQL语句，查询选修了“数据结构”的总人数

select count(*) from sc where cno=(select cno from Course where cname='数据结构');

\--2.写一个SQL语句，查询“张宇”同学选修了的课程和成绩

\--法1

select cname,score from Student s,Sc ,Course c where s.sno=sc.sno and c.cno =sc.cno and sname='张宇';

\--法2

select cno,score from Course c join (select cno,score from sc where sno=(select sno from student where sname='张宇'))on c.cno=sc.cno;

\--3.写一个SQL语句，查询挂科学分最多的同学姓名和院系

\--先统计每个人都挂了多少学分

\--然后用sum(cval)作为新的表 先计算总分--按照学号分组，分数排序筛选出成绩下小于60的学分--学号匹配题目要求的sum(cval)--从student表中选择到符合要求的学号和院系

select sname,dep from student where sno in(
    
    select sno from Course,SC where Course.cno = sc.cno and sc.score<60 group by sc.sno having sum(cval)=
    
    (select sum(cval) sum_ from Course,SC where Course.cno = sc.cno and sc.score<60 group by sc.sno  order by sum_ desc limit 1));