1)建表

create table test
(
    test_id int,
    test_price decimal,
    test_desc varchar(255)
);

也可以建立查询结果的子表。但是子表在可视化视图中是看不到的。

create table hehe
as
select *  from emp;

2)排序

查询结果为默认升序,可以指定降序,ORDER BY子句可以有多个关键字(先排前)

SELECT * FROM emp ORDER BY empno DESC;

注意ORDER BY子句总放在SELECT子句的最后面。可以在查询中使用函数。

SELECT * FROM emp ORDER BY UPPER(ename);

3)过滤查询

SELECT DISTINCT ename FROM emp;

关键字UNIQUE与DISTINCT作用相同,但是UNIQUE为ORACLE特有语法,不易于移植。

4)where子句

select * from emp where sal between 2000 and 3000;
select * from emp where job in('SALESMAN','CLERK');

上面第2列代码指定字段job为'SALESMAN','CLERK'的职员。

SELECT 
  empno,
  ename,
  job,
  sal,
  TO_CHAR(hiredate, 'YY-MM-DD') as hiredate
from 
  emp
where 
  job='CLERK' and EXTRACT(MONTH FROM hiredate)=12;

5)Fetch子句

fetch子句可以返回限定条数的数据。下列代码放回工资最高的五个员工(如果最后一个名额并列则全部显示,把with ties换成only则不会拓展显示数据)。

--以下代码只有在Oracle12c以上的版本才可以执行
select
  empno,
  ename,
  sal
from 
  emp 
order by
  sal desc
fetch next 5 rows with ties;

6)In子句

select
  empno,
  ename,
  job,
  sal
from 
  emp 
where 
  job not in('CLERK')
order by
  sal desc;

7)Between And子句

select
  empno,
  ename,
  job,
  hiredate
from
 emp
where
 hiredate between date '1980-01-01' and date '1981-01-01'
order by
  empno;

8)like子句

like子句可以使用通配符匹配查询。下面查询姓名首字母为A的,至少有两个字母的员工,不区分大小写。

 select
  empno,
  ename,
  job
from
  emp
where
  upper(ename) like 'A_%'
ORDER BY
  empno;

9)插入数据

INSERT INTO emp(empno,ename,job)
VALUES(8003,'sg','CLERK');

10)更新数据

UPDATE  emp
SET ename='pp'
WHERE empno='8003';

11)删除数据

DELETE FROM emp
WHERE empno='8003';

12)返回指定行数数据

select * from emp where rownum<=5;

该语句在数据量特别大的数据库中很有用。

13)别名

select empno as id,ename as name from emp;



select 
e.empno,e.ename,d.dname,e.deptno,d.deptno
from 
emp e,dept d
where e.deptno=d.deptno;

14)连接

内连接两张表的全部内容都会显示,外连接分为左连接和右连接,左连接左表全部显示。

select 
e.empno,e.ename,e.job,d.deptno,d.dname
from 
emp e
inner join
dept d
on e.deptno=d.deptno;

15)Union和unionAll

合并多个select子句,其中unionAll允许重复值,而Union则不允许。

select deptno from emp
union all
select deptno from dept
order by deptno;

16)约束

常用primarary key,foreign key not null,check,default.

create table teacher(
  tid VARCHAR(20) PRIMARY key,
  tname varchar(30),
  tsex VARCHAR(10)
);
alter table teacher
add constraint ck_teacher check(tsex='M' or tsex='W');

17)创建索引

create index index_emp
on emp(empno);

18)drop与truncate

drop index index_test;

truncate可以截断表中数据但是不删除表。

truncate table hehe;

19)函数

select avg(sal) from emp;
select count( distinct ename) from emp;

20)group by子句

使用聚合函数group by可以分组查询,比如查各部门员工的平均工资。

select deptno,avg(sal) as avg_sal from emp group by deptno;

20)having子句

使用having关键字是因为where子句与聚合函数无法同时使用。

select 
deptno,avg(sal) as avg_sal 
from
emp 
group by deptno
having avg(sal)>1000;