select 语句

 

1\DML:CRUD
  INSERT
  SELECT --
  UPDATE
  DELETE
2\DDL
  CREATE
  DROP
  ALTER
  
  TRUNCATE TABLE;
  
3\事务控制
  COMMIT
  ROLLBACK
  
  
  insert into test02
  select 100,'aaa' from dual;
  
  select * from (
  select * from all_tables)b;
  
  
  select id 客户编号,name as 客户姓名 from test01;
  
  
  select CERT_CODE as 身份证号,name as 客户姓名 from test01;
  
  
  select DISTINCT id 客户编号,name as 客户姓名 from test01;
  
    select id 客户编号,name as 客户姓名 from test01
    union
    select * from test02;
  
    
    select id 客户编号,name as 客户姓名 from test01
    union all
    select * from test02;
  
    select id 客户编号,name as 客户姓名 from test01
    intersect
    select * from test02;
  
    
    select distinct id 客户编号,name as 客户姓名 from test01 a
           where exists(select 1 from test02 b where a.id=b.id and a.name=b.name);
  
  
    select id 客户编号,name as 客户姓名 from test01
    minus
    select * from test02;
    
    
    select distinct id 客户编号,name as 客户姓名 from test01 a
           where not exists(select 1 from test02 b where a.id=b.id and a.name=b.name)
        order by 客户编号 asc ,客户姓名 desc
        
        group by
        
    --记录数
    select count(*) from test01;
    
    select id,count(*) as rec_count from test01
       group by id
       order by rec_count;
      
    SELECT COUNT(1) FROM TEST01;
     select id,count(1) as rec_count from test01
       group by id
       order by rec_count;
      
    select count(0) from test01;
    
    --
    
    
    create table student (stu_id number(5),stu_name varchar2(30));
    
    create table course (id number(5),course_name varchar2(30));
    
    create table student_course (stu_id number(5),id number(5))
    
    insert into student
          select 3,'王五' from dual;
    
    
    insert into course
        select 1,'oracle' from dual
        union all
        select 2,'javaee' from dual;
        
    insert into student_course
       select 5,1 from dual
       union all
       select 1,2 from dual
       union all
       select 2,2 from dual
      
      
       commit;
      
       select a.stu_id,stu_name,b.id,b.course_name
          from student a, course b,student_course c
          where a.stu_id=c.stu_id and b.id=c.id;
          
      
       SELECT e1.last_name||' works for '||e2.last_name
        "Employees and Their Managers"
        FROM hr.employees e1, hr.employees e2
        WHERE e1.manager_id = e2.employee_id
        AND e1.last_name LIKE 'R%';
        
      select a.* from test01 a,all_tables b
      
      
     select * from student a,student_course b
            where a.stu_id=b.stu_id;
    
     select A.STU_NAME,COUNT(*)
            from student a inner join student_course b on a.stu_id=b.stu_id
        GROUP BY A.STU_NAME;
    
     select A.STU_NAME,COUNT(b.id)
            from student a left join student_course b on a.stu_id=b.stu_id
        GROUP BY A.STU_NAME;
    
    select * from student
    
    select * from student_course
    
    select A.*,b.*
        from student a left join student_course b on a.stu_id=b.stu_id
        --GROUP BY A.STU_NAME;
        
    select A.STU_NAME,COUNT(b.id)
       from student_course b right join student a on a.stu_id=b.stu_id
       GROUP BY A.STU_NAME;
      
    select a.course_name,count(b.id)
       from course a right join student_course b on a.id=b.id
       group by a.course_name
      
      
    select A.*,b.*
        from student a full join student_course b on a.stu_id=b.stu_id;
        
        
    create table a(id number(5),amount number(10,2));
        create table b(id number(5),amount number(10,2))
        
        delete a;
        insert into a
        select 1,500 from dual
        union all
        select 2,100 from dual        
                
        insert into b
        select 1,500 from dual
        union all
        select 2,50 from dual
        union all
        select 3,100 from dual
    
    select b.*,
       case
         when a_id is null then 'A表不存在'
         when b_id is null then 'B表不存在'
         when a_amount<>b_amount then '余额不相等'
       end as 原因
     from (
      select a.id as a_id,a.amount as a_amount,
             b.id as b_id,b.amount as b_amount
         from a full join b on a.id=b.id) b
      where a_id is null or b_id is null or a_amount<>b_amount;
      
      
      select * from hr.employees where not( salary>10000 )
         order by salary desc
    
    
      select * from hr.employees where salary>=10000 and manager_id=148
         order by salary desc
        
      select * from hr.employees where (salary>=20000 or salary<2400) and manager_id=148
         order by salary desc;
        
      select * from users where username=? and password=?;
      
      pass='8888';
      
      
      select * from users where username='001' and password='888' or '1'='1';
      
      
      select * from test02 where name like '张%';
      
      
      select * from hr.employees where salary>=10000 and salary<=20000;
      select * from hr.employees where and salary<=20000 and salary>=10000 ;
      
      
      select * from hr.employees where salary not between 10000 and 20000;
      
      select * from hr.employees where not(salary  between 10000 and 20000);
      
     -- select * from hr.employees where salary in (1000,);
    
    
insert into test02
select 101,'' from dual;
commit;
select * from test02 where name=null;--''
select * from test02 where name is null;
select * from test02 where name is NOT null;
select * from test02 where NOT(name is null);

--IN\NOT IN\EXISTS\NOT EXISTS\COUNT;
SELECT * FROM STUDENT;
SELECT * FROM COURSE;
SELECT * FROM STUDENT_COURSE;
--查有学课程
select * from student a
   where stu_id in(select stu_id from student_course);
--查没有学课程
select * from student a
   where stu_id NOT in(select stu_id from student_course);

--查有学课程
select * from student a
   where exists (select stu_id from student_course b where a.stu_id=b.stu_id);

--查没有学课程
select * from student a
   where not exists (select stu_id from student_course b where a.stu_id=b.stu_id);
  
--查有学课程
select * from student a
   where exists (select 1 from student_course b where a.stu_id=b.stu_id);
--查没有学课程
select * from student a
   where not exists (select 1 from student_course b where a.stu_id=b.stu_id);
  
  
--查有学课程
select * from student a
   where stu_id in (select stu_id from student_course b where b.id=1);
--查有学课程(oracle)
select * from student a
   where stu_id in (select stu_id from student_course b ,course c where b.id=c.id and c.course_name='oracle');
  
--查有学课程
select * from student a
   where exists (select 1 from student_course b where a.stu_id=b.stu_id and b.id=1);
--查有学课程(oracle)
select * from student a
   where exists (select 1 from student_course b ,course c where  a.stu_id=b.stu_id and b.id=c.id and c.course_name='javaee');
--查有学课程
select * from student a
   where (select count(1) from student_course b where a.stu_id=b.stu_id and b.id=1)=0;
--查有学课程(oracle)
select * from student a
   where (select count(1) from student_course b ,course c where  a.stu_id=b.stu_id and b.id=c.id and c.course_name='javaee')=0;
  
  
select * from hr.employees where abs(salary)>2000;

select * from hr.employees where salary>2000 or salary<-2000;
select * from hr.employees where salary-2000>5000;
select * from hr.employees where salary>5000+2000;

insert into test01(id,name)
values(2,'test');
insert into test01(id,name)
select 2,'test' from dual;

--oracle HINT
insert into test01(id,name)
select * from test02;
alter table test01 add (x varchar2(30));
select * from test01
DML-insert \delete\update

CREATE TABLE TEST03(ID NUMBER(3),NAME VARCHAR2(20));

select * from test03
insert into test03
values(1,'test1')
commit;
rollback;

insert into test03
values(1,'test1')
update test03 set name='张' ;

create table test04 as
select * from test03;

select * into test04 from test03;
truncate table test04;