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;