oracle教程08 oracle中的常用基本查询举例

左手的ㄟ右手 2022-05-27 04:26 161阅读 0赞

基本查询语句

--去除重复行
    --select distinct deptno, job from emp;
    
    select * from emp;
    
    --设置简体中文的日期格式
    alter session set nls_language = 'SIMPLIFIED CHINESE';
    select empno,ename,hiredate from emp;
    
    --设置美国英语的日期格式
    alter session set nls_language = 'AMERICAN';
    select empno,ename,hiredate from emp;
    
    --设置特定格式
    alter session set nls_date_format='YYYY/MM/DD';
    select empno,ename,hiredate from emp;

查询语句中算术运算符的使用

--select * from emp;
    -- 算术运算符的演示
    --select empno,ename,sal,sal*12 from emp;
    --
    --select empno, ename, sal*12+sal/2 from emp;
    --
    --select empno, ename, 200+sal*12  from emp;
    --
    --select empno, ename, (sal+100)*12  from emp;
    --
    --select empno, ename, sal, comm, sal+comm from emp;
    
    --默认值的演示
    --create table student(
    --    sid number(8,0) primary key,
    --    name varchar2(10),
    --    sex char(2) default '男',
    --    age number(2,0) default 20,
    --    address varchar2(50)
    --);
    
    
    --insert into student(sid,name) values (20010001,'张三');
    select * from student;
    
    insert into student values(20010002,'王五',default,21,'北京市昌平区');
    commit;

查询语句中列的别名、连接符、字符串

--列的别名
    select empno "雇员编号", ename "雇员名", sal*12 "年收入" from emp;
    
    select empno "雇员编号", ename "雇员名", sal*12  年收入 from emp;
    
    select empno "雇员编号", ename "雇员名", sal*12 as 年收入 from emp;
    
    --连接符
    select  ename||'的月工资是：'||sal||'岗位是：'||job as 雇员职位信息 from emp;
    
    select ename||5 from emp;

过滤数据（比较运算）

--数字值的情况
    select * from emp where deptno=20;
    
    --字符值
    select * from emp where job='MANAGER';
    
    --日期值
    select * from emp where hiredate = '02-4月-81';
    
    --比较运算
    select * from emp where sal = 3000;
    
    select * from emp where sal >= 3000;
    
    select * from emp where sal < 3000;
    
    select * from emp where sal <> 3000;
    select * from emp where sal != 3000;
    
    select * from emp where sal>1000 and sal<3000;
    
    
    select * from emp where sal<1000 or sal>3000;

过滤数据(范围模糊查询等)

--范围查询between..and
    select empno, ename, sal from emp where sal between 1500 and 3000;
    
    --in
    select empno,ename, job from emp where job in ('SALESMAN','MANAGER','CLERK');
    
    --模糊查询like
    select * from emp where ename like 'J%';
    select * from emp where ename like '_AR%';
    
    select * from emp;
    
    select * from emp where ename like 'G\_%' escape '\';
    
    --is null
    select empno, ename, sal, comm from emp where comm is not null;

过滤数据(逻辑运算)

--and
    select empno, ename, job, deptno from emp where job='MANAGER' and deptno=10;
    
    --or
    select empno, ename, job, sal from emp where job='MANAGER' or sal>2000;
    
    --not
    select empno, ename,job from emp where job not in ('CLERK','SALESMAN','MANAGER');
    
    --混合
    select empno,ename, job, sal from emp where (sal>2000 or deptno=30) and job not in ('PRESIDENT','MANAGER');

排序数据

--单列排序
    select ename, comm from emp order by comm asc;
    
    --使用列的别名排序
    select  empno, ename, sal*12 年收入 from emp order by 年收入 desc;
    
    --多列排序
    select empno,ename, deptno, sal from emp order by deptno asc, sal desc;