sql 语句复习

1.select distinct name from students关键词 DISTINCT 用于返回唯一不同的值。

2.select * from wher name ="Jeson"

运算符：=  ------------->等于

           <> ------------>不等于

           >  ------------->大于

          <   ------------->小于

          >=  ------------->大于等于

          <= -------------->小于等于

          between   -------->在某个范围类

          like -------------->搜索某种模式（模糊查询）

 

   AND 和 OR  -------------->且 与 或

3.SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

降序用desc 升序用asc

4.INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

一般插入语句

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

列名和值对应的插入语句

5.UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

修改语句

6.DELETE FROM Person WHERE LastName = 'Wilson'

删除语句

7.SELECT TOP 2 * FROM Persons

8.SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

IN 操作符允许我们在 WHERE 子句中规定多个值。

9.SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po 
WHERE p.LastName='Adams'
WHERE p.FirstName='John'


　CRUD是指在做计算处理时的增加(Create)、查询(Retrieve)（重新得到数据）、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。

 

分页：

 

select *
  from (select a.*, rownum rn
          from WZ_JHTMQZTBR a
         where rownum < 11 order by a.jhtmqztbr_id) t
 where rn > 5 order by t.jhtmqztbr_id



           select *
             from WZ_JHTMQZTBR
            where rowid in (select rid
                              from (select rownum rn, rid
                                      from (select rowid rid, jhtmqztbr_id
                                              from WZ_JHTMQZTBR
                                             order by jhtmqztbr_id)
                                     where rownum < 11)
                             where rn > 5)
            order by jhtmqztbr_id;

 

 

 

select * from table_name limit 5,10 

 

select top n * from (select top m * from table_name order by column_name ) a order by column_name desc