数据库-内容的查询 二

-- > any 比最小的值要高


select *
  from products
 where unitprice > any
 (select unitprice from products where categoryid = 2)
 order by unitprice asc
           select min(unitprice) from products where categoryid = 2




-- < any 比最大的值要小
select *
  from products
 where unitprice < any
 (select unitprice from products where categoryid = 2)
 order by unitprice desc
           select max(unitprice) from products where categoryid = 2


-- > all 比最大的值还要高
select *
  from products
 where unitprice > all
 (select unitprice from products where categoryid = 2)
  select max(unitprice) from products where categoryid = 2


-- < all 比最小的值还要小
select *
  from products
 where unitprice < all
 (select unitprice from products where categoryid = 2)
  select min(unitprice) from products where categoryid = 2
  
  




--case when


select productname,
       case
         when unitprice between 0 and 10 then
          '便宜'
         when unitprice between 10 and 50 then
          '可以接受的'
         when unitprice > 50 then
          '太贵了'
         else
          '该商品没有正确价格'
       end 商品接受程度,
       unitprice
  from products
  
  
  
  create table person
  (
  pid int,
  pname varchar(20),
  gender int
  )
  
  insert into person(pid,pname,gender) values(1,'小明',1);
    insert into person(pid,pname,gender) values(2,'小红',2);
      insert into person(pid,pname,gender) values(3,'小王',1);
     
    
   select pid,
          pname,
          case
            when gender = 1 then
             '雄'
            when gender = 2 then
             '雌'
          end 性别
     from person
  
  
  
  
  
  
  
  
  --填充函数
--dual是一个虚拟表,用来匹配SQL语句
--第一个参数指要去填充的字符串
--第二个参数指填充后的长度
--第三个参数指用来填充的内容
  select lpad('a',10,'*') from dual;
  select rpad('a',10,'*') from dual;
  
  --拼接函数
  select concat('学生的名字是','朱一帆') from dual
  
  --截取函数
  --第一个参数指要截取的字符串
  --第二个参数指从字符串的什么位置开始
  --第三个参数指一共截取多少位
  select substr('asdfqwer',3,3) from dual
  
--  13247174155
select from dual
select substr('13247174155',8,4) from dual




  
select concat(rpad(substr('13247174155',1,3),7,'*'),substr('13247174155',8,4)) from dual






create table "user"(phone number(11,0))
insert into "user"(phone) values(13247174155);
insert into "user"(phone) values(13247174555);
insert into "user"(phone) values(18647174157);
insert into "user"(phone) values(15947174795);








create table "dictionary"(dcon varchar(5),dtype varchar(20))
insert into "dictionary"(dcon,dtype) values('1','lstart');
insert into "dictionary"(dcon,dtype) values('3','lend');
insert into "dictionary"(dcon,dtype) values('7','filllength');
insert into "dictionary"(dcon,dtype) values('*','fillcontent');
insert into "dictionary"(dcon,dtype) values('8','rstart');
insert into "dictionary"(dcon,dtype) values('4','rend');








select phone from "user"




select concat(rpad((substr(phone,
                           (select dcon
                              from "dictionary"
                             where dtype = 'lstart'),
                           (select dcon
                              from "dictionary"
                             where dtype = 'lend'))),
                   (select dcon from "dictionary" where dtype = 'filllength'),
                   (select dcon from "dictionary" where dtype = 'fillcontent')),
              (substr(phone,
                      (select dcon from "dictionary" where dtype = 'rstart'),
                      (select dcon from "dictionary" where dtype = 'rend'))))
  from "user"








  update "dictionary" set lend='2',rend='2',rstart='10' 
  update "dictionary" set dcon='2' where dtype='lend';
    update "dictionary" set dcon='2' where dtype='rend';
      update "dictionary" set dcon='10' where dtype='rstart';












 --length
  select length('asdf')     from dual
  --instr
  select instr('asdfss','s') from dual
  --round 取整
  select round(2.456,2) from dual
  --trunc 截断
    select trunc(2.456,2) from dual  
   --mod
   select mod(10,3)     from dual


select trunc(dbms_random.value*10,0) from dual


       0<=dbms_random.value<1








--to_date 转换成日期
--to_number 转换成数字
--to_char 转换成字符串


select to_date('2017-06-09','yyyy-MM-dd') from dual


select to_number('1') from dual
select to_char('1') from dual




select sysdate from dual
--日期的加减以天位单位
select sysdate-1 from dual


select sysdate-1/24 from dual


select sysdate-1/24/60 from dual


--months_between


select months_between(sysdate,to_date('2017-06-01','yyyy-MM-dd')) from dual