SQL-学习笔记

exists

查询数据库表goods中第6个商品的信息

SELECT id,name,price,sales FROM(SELECT * from goods LIMIT 6)AA 
WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM goods LIMIT 5) 
BB WHERE AA.id=BB.id);                                                             

 随机查询某行数据

SQL server-order by NEWID()

SELECT TOP 1
id,name,price,sales 
from goods order by NEWID();

MYSQL-order by RAND()

SELECT id,name,price,sales FROM goods ORDER BY RAND() LIMIT 1;

ORACLE-dbms_random.random()/dbms_random.value()

先随机排序，再选一个，这个函数效率低

select * from( select * from goods oeder by dbms_random.random)where rownum=1;

 同样是先随机排序，再选一个，这个函数效率高一些

select * from( select * from goods oeder by dbms_random.value)where rownum<=1;

在结果中添加编号

SELECT (select COUNT(id) FROM goods A WHERE A.id>=B.id) 
添加的编号,id,name,price  FROM goods  B order  BY 1;