/* 时间:2020/09/13 功能: 一 identity 二 视图 三 分页 */
一 identity
-- identity create table student2 ( student_id int primary key, student_name nvarchar(200) not null ) insert into student2 values(1, '张三') insert into student2 values(2, '李四') insert into student2 values(3, '王五') select * from student2 insert into student2(student_name) values('赵六') -- error create table student3 ( student_id int primary key identity(100, 5), student_name nvarchar(200) not null ) insert into student3 values('李四') insert into student3 values('王五') select * from student3 insert into student3 values(1, '张三') -- error insert into student3(student_name) values('张三') delete from student3 where student_name = '张三' insert into student3 values('赵六')
二 视图
-- 求平均工资最高部门编号和部门平均工资 -- SQL Server select top 1 deptno, avg(sal) from emp group by deptno order by avg(sal) desc -- 通用SQL语句 select * from ( select deptno, avg(sal) "avg_sal" from emp group by deptno )"E" -- 小表全部信息 where "E"."avg_sal" = ( select max("T"."avg_dept") from( select deptno, avg(sal) "avg_dept" from emp group by deptno ) "T" ) -- 部门最高平均工资 -- 视图用法 create view v$_emp_1 as select deptno, avg(sal) "avg_sal" from emp group by deptno select * from v$_emp_1 where avg_sal = ( select max(avg_sal) from v$_emp_1 )
三 分页
-- 分页查询 -- 查询工资最高的员工信息,排序前三 select top 3 * from emp order by sal desc -- 查询工资最高的员工信息,排序前4-6 select top 3 * from emp where empno not in ( select top 3 empno from emp order by sal desc ) order by sal desc -- 查询工资最高的员工信息,排序前7-9 select top 3 * from emp where empno not in ( select top 6 empno from emp order by sal desc ) order by sal desc -- 查询工资最高的员工信息,排序前10-12 select top 3 * from emp where empno not in ( select top 9 empno from emp order by sal desc ) order by sal desc 假设每页显示n条记录, 当前要显示第m页。 表名是A, 主键是A_id。 m n 1 0 2 3 3 6 4 9 select top n * from A where A_id not in ( select top (m-1)*n A_id from emp )