学习笔记6.9

select prod_name,prod_price
FROM products
where prod_price =3.49;

select prod_name,prod_price
FROM products
where prod_price <> 3.49;

select prod_name,prod_price
from products
where prod_price between 4 and 10;


--1   where  筛选关键字;
 
 
    select * from products where prod_name >'DZZZZZZZ';
 
 --   从emp表中列出 job值大于'CLERK'的项 (由于比较的是字符串,所以加单引号),小写大于大写
    

 
/*2  between  and
 
 
   select ename,sal from emp where sal between 800 and 1500 
 
 
    从emp表中找到 sal值在800和1500之间的项(包括800和1500) 列出他们的ename 和sal*/
 
 
--3 空值
 
   select prod_id,prod_name from products where prod_price is null  ;
    
 
  --列出非空值
 
  select prod_id,prod_name from products where prod_price is  not  null  ;

 
--4 模糊查询
 
 --  查询prod_name中包含inch的项
 
  select  prod_name from products where prod_name like '%inch%' ;
 
 --  %代表0个或多个字符,  次查询目的为了找到ename 值为 ALL 左边和右边具有0个或多个字符的项
 

 --  查询ename第二个字母为A 的项
 
 select prod_name from products where prod_name like '_8%' ;
 
 
--5  转义字符 
 
   -- 查找prod_name中带有 % 的项
 
select  prod_name from products where prod_name like '%$%%'  escape '$' ;
 -- 指定'$'为转义字符查找% .
 
 
  --转义字符默认为'/'  使用/ 为转义字符时不用写后面的escape ..
 
 
--6 排序order by
 
   
 
--     select * from dept  order by deptno ; (默认升序)
 
--    select * from dept  order by deptno desc ;(按deptno的降序排序)
 
  --组合使用:
 
-- select  empno,ename from emp where empno <> 10 order by empno;   (先过滤 再排序)
 
 
-- select  empno,ename from emp order by empno asc,ename desc; 按(empno升序排序)  empno相同时按ename 降序排序*/

select prod_id,prod_name,prod_price
FROM Products
where vend_id ='DLL01' and prod_price <=4;

select prod_id,prod_name,prod_price
FROM Products
where vend_id ='DLL01' or prod_price <=10;

--and优先 --brs01制造的价格大于10美元的产品和dll01制造的所有产品，不管价格
SELECT prod_name,prod_price
from products
where vend_id ='DLL01' or vend_id='BRS01'
     and prod_price >=10;
     
     
SELECT prod_name,prod_price
from products
where (vend_id ='DLL01' or vend_id='BRS01')
     and prod_price >=10;
     
select prod_name,prod_price
from products
where vend_id in('DLL01','BRS01')
order by prod_name;

select prod_name
from products
where not vend_id ='DLL01'
order by prod_name;