1.条件查询
select ... from person where ....。
字符串和日期需要用单引号引起来;字符串大小写敏感;日期格式敏感;
create table person(
name varchar2(50),
age number(3)
);
insert into person values('ONE',1);
insert into person values('two',2);
commit;
select name,age from person where name = 'ONE';
获取系统当前时间:
select sysdate from dual;
2.比较运算符
= 等于,>大于,>=大于等于,,!=,~不等于(注意,oracle的不等于有三种表示形式);
between ...and ...介余两者之间,包括边界;
in 在集合中,特别注意,in(...)中极限1000个,即in(表达式1,表达式2,...,表达式1000);
like 模糊查询;
is null 或is not null 判断是否为空值或是否不为空值;
重点讲后面四个,其他跟数学一样:
create table person(
name varchar2(50),
age number(3)
);
insert into person values('ONE',1);
insert into person values('two',2);
commit;
select name,age from person where age between 1 and 10;--between and
select name,age from person where age in (1,2) ;--in
select name,age from person where name like '%O%';--like
select name,age from person where age is null;--is null
select name,age from person where age is not null;--is not null
3.模糊查询
在上面能够看到,like就是像的意思,把像的东西查出来。
select name,age from person where name like '%O%';--like
--%代表一个,或多个字符,该语句表示O前可以为多个字符,O后也可以为多个字符的名字。也就是包含有O的就在查询内容中。
_下划线表示一个字符。
4.逻辑运算
AND(与):全真才真,一假必须假
OR(或):一真必须真,全假才假
NOT(非):真假相对
create table person(
name varchar2(50),
age number(3)
);
insert into person values('ONE',1);
insert into person values('two',2);
commit;
select name,age from person where name='ONE' and age = 1;--and
select name,age from person where name='ONE' or age = 2 ;--or
select name,age from person where age not in(1,2);--not
select name,age from person where age is null;--is null
select name,age from person where age is not null;--is not null