sql 语句

本文记录了我在面试过程中感觉有用的问题，方便日后参考。

---

常用数据库的分页查询语句

SQLServer 分页

// 方法一:
// pages = pageSize * (pageNumber - 1)

select top pageSize * from 表名 where id  not in (select top pages id from 表名 order by id) order by id;

// 方法二:
// pages = pageSize * (pageNumber - 1)

select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t );

MySQL 分页

// pages = pageSize * (pageNumber - 1)

select * from 表名 limit pages, pageSize; 

Oracle 分页

select a.* from   
 (  
   select rownum num ,t.* from  表名 t where 某列=某值 order by id asc   
 )a  
 where a.num>=startPage and a.num<endPage  

---

用 sql 语句删除表中完全重复的记录，只保留一项

以 MySQL 数据库为例:

一、准备数据

create table student (
  id int primary key auto_increment,
  stu_id varchar(10) null,
  stu_name varchar(20) null  
);

insert into student(stu_id, stu_name) values ('1001', 'johnny');
insert into student(stu_id, stu_name) values ('1001', 'johnny');
insert into student(stu_id, stu_name) values ('1001', 'johnny');
insert into student(stu_id, stu_name) values ('1001', 'johnny11');
insert into student(stu_id, stu_name) values ('1002', 'karva');
insert into student(stu_id, stu_name) values ('1003', 'ketty');
insert into student(stu_id, stu_name) values ('1003', 'ketty');

二、删除重复数据

delete m from student as m,
(select min(id) as min_id, stu_id, stu_name from student group by stu_id, stu_name having count(stu_id) > 1 and count(stu_name) > 1) as n
where m.stu_id = n.stu_id and m.stu_name = n.stu_name and m.id <> n.min_id;

简述数据库的事务和锁

悲观锁和乐观锁的理解

分布式事务的理解

数据库事务死锁的避免

sql 语句的优化

转载于:https://www.cnblogs.com/dwarcheng/p/5883780.html