通用:ROW_NUMBER() OVER()方式
select * from (
select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels
) as b
where RowId between 10 and 20
备注:---where RowId BETWEEN 当前页数-1*条数 and 页数*条数---
SQL2012以上的版本才支持:offset fetch next方式(:推荐使用 )
select * from (
select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels
) as b
where RowId between 10 and 20
备注:--order by ArtistId offset 页数 rows fetch next 条数 rows only ----
适应于数据库2012以下的版本:top not in方式
select top 3 * from ArtistModels
where ArtistId not in (select top 15 ArtistId from ArtistModels)
备注:-where Id not in (select top 条数*页数 ArtistId from ArtistModels)
Oracle:
方式一(效率高):
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
方式二:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
MySql:
语法:SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
案例:select * from orders_history where type=8 order by id limit 10000,10;
PostgreSql:
语法:select * from persons limit A offset B;
案例:select * from persons limit 5 offset 0 ;
解释:A就是你需要多少行;B就是查询的起点位置
SqlLite:
案例:SELECT * FROM Table ORDER BY ID DESC Limit 10,9
解释:limit语义:跳过10行,取9行
6、按日期范围查询
Sqlserver:
select * from table1 where t1>='2017-6-1' and t1<='2017-6-5'
Oracle:
SELECT * FROM TB_NAME t WHERE to_date(,'yyyy-MM-dd HH24:MI:SS')
between to_date('2011-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS') and to_date('20110702 12:00:00','yyyymmdd HH24:MI:SS') ;
MySql:
select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00' and addedTime < '2018-1-1 00:00:00';
PostgreSql:
select * from user_info where create_date >= '2015-07-01' and create_date < '2015-08-15';
SqlLite:
select * from table1 where t1>='2017-6-1' and t1<='2017-6-5'
1、%:
SELECT * FROM [user] WHERE uname LIKE ‘%三%’ ;
2、_:单一任何字符(下划线)常用来限制表达式的字符长度语句
SELECT * FROM [user] WHERE uname LIKE ‘三__’;
3、[]:在某一范围内的字符,表示括号内所列字符中的一个(类似正则表达式)
SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’ ;
4、[^]: 不在某范围内的字符,用法与[ ]相反
5、在Oracle中提供了instr(strSource,strTarget)函数,比使用’%关键字%’的模式效率高很多。
instr函数也有三种情况:
instr(字段,’关键字’)>0相当于 字段like ‘%关键字%’ ;
instr(字段,’关键字’)=1相当于 字段like ‘关键字%’ ;
instr(字段,’关键字’)=0相当于 字段not like ‘%关键字%’ 。
特殊用法:
select id, namefrom user where instr(‘101914, 104703’, id) > 0;
它等价于
select id, namefrom user where id = 101914 or id = 104703;
MySql:
1、同Sqlserver和Oracle前4点
2、LOCATE('substr',str,pos)方法
SELECT LOCATE('bar',`foobarbar`); ###返回4
3、POSITION('substr' IN `field`)方法
SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
4、INSTR(`str`,'substr')方法
SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0
5、FIND_IN_SET(str1,str2):
SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);
PostgreSql:
1、前缀+模糊查询。(可以使用b-tree索引)
select * from tbl where col like 'ab%';
或
select * from tbl where col ~ '^ab';
2、后缀+模糊查询。(可以使用reverse(col)表达式b-tree索引
select * from tbl where col like '%ab';
或
select * from tbl where col ~ 'ab$';
写法
select * from tbl where reverse(col) like 'ba%';
或
select * from tbl where reverse(col) ~ '^ba';
3、前后模糊查询。(可以使用pg_trgm和gin索引)
select * from tbl where col like '%ab%';
或
select * from tbl where col ~ 'ab';
4、全文检索。(可以使用全文检索类型以及gin或rum索引)
select * from tbl where tsvector_col @@ 'postgres & china | digoal:A' order by ts_rank(tsvector_col, 'postgres & china | digoal:A') limit xx;
5、正则查询。(可以使用pg_trgm和gin索引)
select * from tbl where col ~ '^a[0-9]{1,5}\ +digoal$';
6、相似查询。(可以使用pg_trgm和gin索引)
select * from tbl order by similarity(col, 'postgre') desc limit 10;
7、ADHOC查询,任意字段组合查询
select * from tbl where a=? and b=? or c=? and d=? or e between ? and ? and f in (?);
8、关键字
SELECT * FROM 表名 WHERE 字段 ILIKE regexp_replace(concat('%','关键字','%'),'\\','\\\','g')
SqlLite:
1、同Sqlserver和Oracle前4点
"select * from zipcode where (address like'%" & zipcode_key & "%') or (city like'%" & zipcode_key & "%') or (province like'%" & zipcode_key & "%') order by province,city,address
8、字符串连接符
Sqlserver:
1、使用 +
select 'Post'+'gresql'+' good!';
2、使用 CONCAT
SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))
FROM user
Oracle:
1、使用 ||
select 'Post'||'gresql'||' good!';
2、使用 CONCAT
SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))
FROM user
MySql:
1、使用 ||,如果是纯数字拼接,不能使用,得出结果为 1||2=3
select 'Post'||'gresql'||' good!';
2、使用 CONCAT
SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))
FROM user
PostgreSql:
1、使用 ||
select 'Post'||'gresql'||' good!';
2、使用 CONCAT
SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))
FROM user
1、创建
创建表
Create table t_user(Id number(6),userid varchar2(20),loginpassword varchar2(20),isdisable number(6) );
创建序列
create sequence user_seq increment by 1 start with 1 nomaxvalue nominvalue nocache
创建触发器
create or replace trigger tr_user
before insert on t_user
for each row
begin
select user_seq.nextval into :new.id from dual;
end;
测试
insert into t_user(userid,loginpassword, isdisable) values('ffll','liudddyujj', 0);
insert into t_user(userid,loginpassword, isdisable) values('dddd','zhang', 0) ;
select * from t_user;
2、返回值 select user_seq.CURRVAL from dual
MySql:
1、创建:id int primary key auto_increment
2、返回值:SELECT LAST_INSERT_ID()
PostgreSql:
1、创建:新建字段 并且将字段属性设置为serial
2、新增返回:insert into point(pointtype,pointx,pointy,pointval)values(1,2,3,4) RETURNING id;
SqlLite:
1、创建:id INTEGER PRIMARY KEY AUTOINCREMENT
2、返回值:select last_insert_rowid() from person
3、自增自增归零:DELETE FROM sqlite_sequence WHERE name='TableName';
4、特定字段自增归零:UPDATE sqlite_sequence SET seq = 0 WHERE name='TableName';