从表中查询N条数据:
利用一条SQL语句从数据库Table表中随机获取N条记录,各数据库的SQL语句略有不同,如下:
1、MySql
Select * From TABLE Order By Rand() Limit N
以上代码效率不高,自己对1000条数据表随机取10条的测试结果为耗时0.125s,有网友推荐改为以下代码效率有大幅提高,耗时为0.005s,不过结果是随机从某个位置开始取N条记录,而不是随机取N条记录,略有差异。
SELECT * FROM `TABLE` a join (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `TABLE`)-(SELECTMIN(id) FROM `TABLE`))+(SELECT MIN(id) FROM `TABLE`)) AS id) AS b WHERE a.id >= b.id ORDER BYa.id LIMIT N;
2、SQL Server
NewID()函数将创建一个 uniqueidentifier 类型的唯一值。
3、Access
Select TOP N * From TABLE Order By Rnd(ID)
Rnd(ID) 其中的ID是自动编号字段,可以利用其他任何数值来完成,比如用姓名字段(UserName)
Select TOP N * From TABLE Order BY Rnd(Len(UserName))
4、Oracle
1)dbms_random包
select * from (select * from Table order by dbms_random.value) where rownum < N;
注:dbms_random包需要手工安装,位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)
可以产生100到200范围的随机数
2)按概率抽取:
例:
取表Table中记录条数的10%条记录
3)sys_guid
select * from (select * from Table orderby sys_guid()) where rownum<N;
5、postgreSQL
select * from Table order by random() limit N
判断表名/字段名是否存在:
1.MSSQL Server
表:select COUNT(*) from dbo.sysobjectsWHEREname = 'table_name';
字段:select COUNT(*) fromsyscolumnsWHEREid = object_id(‘table_name’) and name= 'column_name';
2.My SQL
表:select COUNT(*) from information_schema.tables WHERE table_name = 'table_name';
字段:select COUNT(*) from information_schema.columns WHERE table_name = 'table_name' and column_name ='column_name';
3.Oracle
表:SELECT count(*) FROM USER_OBJECTS WHERE OBJECT_NAME = 'table_name';
字段:SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME ='table_name'andcolumn_name ='column_name';
4.PostgreSql
表:注意,表名需为小写的
1.selectcount(*) from information_schema.tables WHERE table_schema = 'table_schema' and table_name ='table_name';
2.select count(*) from pg_class where relname = 'qwbddict';
3.select count(*) from pg_statio_user_tables where relname='qwbddict';
字段:select count(*) from information_schema.columns WHERE table_schema = 'table_schema' and table_name ='table_name' and column_name = 'column_name';