分页查询和视图的含义及小案例-向日葵屋

分页查询

需求：

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

关键字：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));