数据库MySQL学习笔记（七）

分页查询

 

当查询的记录数较大时，一页显示不下，可以进行分页查询。关键字limit

用法： limit begin,size;

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

            size -- 每页的最大记录数

也可以在limit之后只加一个参数 size，表示每页的最大记录数，默认从0开始

第n页，每页m条数据的分页写法：

       select * from 表名 limit (n-1)*m,m

分页查询的例子：

--练习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:使用子查询，先排序，再查询每页的数据。
--     按照员工工资降序排序，查询第二页的数据，每页6条。
select e.* from (select * from emp order by sal desc) e limit 6,6;

 

视图

 

1、视图是表的一部分投影，也是数据库里的一个对象，是表的虚拟部分。

2、视图的存在是为了安全方便，视图中含有表的部分数据，不在视图中的字段有隐藏效果，相对来说是安全的。

3、视图和表一样，也可以进行增删改查。

      对表进行DML操作，可能会对视图产生影响，但对视图进行DML进行操作，一定会对视图产生影响。

4、创建视图的sql语句：

       create view 视图名 as 子查询

5、视图的with check option 约束：如果在创建视图有此选项时，表示只能对视图DML操作可见数据，反之，对视图不可见的数据，是不可以通过视图进行上述操作的。

6、视图的分类：

       简单视图：子查询是一个表中的普通查询语句

       复杂视图：子查询是一个带有分组函数的，或者关联查询的语句

                           复杂视图不能对视图进行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;

 

索引

 

目的为了提高查询速度，相当于一本书的目录，索引也是数据库的对象，是占空间的，每张表的每个字段都可以设置相应的索引。

优点是提高了查询速度，缺点是占空间，每次进行DML操作时，数据库都要自动维护索引，降低了效率。

适当使用索引是可以提高效率的，但不是索引越多越好。

创建索引相关的例子：

--给emp表中的empno创建索引。
create index index_emp_empno on emp(empno);
--查询empno为9003的数据。
select * from emp where empno=9003;

--删除索引，删除视图都是用drop
--drop index  indexName
--drop view   viewName

 

相关例子

/*练习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:使用子查询，先排序，再查询每页的数据。
按照员工工资降序排序，查询第二页的数据，每页6条。
select * from (
    select * from emp order by sal desc
) a limit 6,6*/

/*6.使用emp表中deptno=20的数据做成一个视图
CREATE VIEW emp_20_view AS 
select * from emp where deptno=20*/

/*7.查询视图中所有的数据
select * from emp_20_view*/

/*8.向视图中插入数据
INSERT INTO emp_20_view VALUES
(8110,'ZHANGSAN','MANAGER',7369,
'2010-10-10',5000,null,10)*/

/*9.查询视图中所有的数据
select * from emp_20_view*/

/*10.删除emp表中empno=8010
delete from emp where empno = 8010*/

/*11.创建具有检查约束的视图
CREATE VIEW emp_20_view AS 
select empno,ename,sal,deptno from emp 
where deptno=20 
WITH CHECK OPTION*/

/*12.查询视图数据
select * from emp*/

/*13.向视图中插入数据
INSERT INTO emp_20_view(empno,ename,sal,deptno) VALUES
(8110,'ZHANGSAN',7000,10)*/

/*14.练习:创建一个复杂视图view_emp_100，是每个部门的平均工资，最高工资
，最低工资，及其部门号。
CREATE VIEW emp_view AS 
select avg(sal),max(sal),min(sal),deptno from 
emp group by deptno

select * from emp_view*/

/*15.为emp表deptno创建索引
CREATE INDEX emp_dept_index ON emp(deptno)
*/