SQL 语句
基本SQL 查询语句
语法:
select 属性列表
from 表名(视图)列表
[where 条件表达式1]
[group by 属性名1 [having 表达式2 ]]
[order by属性2[asc | desc]]
asc 升序
desc 降序
eg:
select id,name,age,sex,address from employee;
条件+排序:
select id,e_no,name,age,sex,address from emplyee where age<26 order by e_no desc;
字段查询
查询所有字段
eg:
select * from employee;
注:为了提高查询效率,尽可能地使用字段来查询,而不用* 号
- 查询指定字段
可根据需要指定字段,同事字段的顺序也可以改变
eg:
select name,address,age from employee;
- 查询指定记录
eg:
select * from employee where e_no = 1001;
查询条件:符号或关键字
比较:=、<、<=、>、>=、!=、<>、!>、!<
指定范围:between and 、 not between and
指定集合:in、not in
匹配字符:like、not like
是否为空:is null、 is not null
多个查询条件: and、or
带in关键字的查询
语法:[ not ] in (元素1,元素2,……,元素n)
eg:
select * from employee where e_no in (1002,1004);
select * from employee where name not in ('张三','李四');
注:字符串类型一定要加上引号
带between and的范围查询
eg:
select * from employee where age between 15 and 25;
select * from employee where age not between 15 and 25;
带like的字符匹配查询
- 通配符:
%:代表任意长度的字符串,长度可以为0.
eg:
a%c --> 表示以字母a开头,以字母c结尾的任意长度的字符串,可以代表ac、abc、ashghdac
_:智能表示单个字符。
eg:
a_c --> 表示以字母a开头,以字母c结尾的3个字符,中间的“_”可以表示任意一个字符,比如:abd、adc、azc
(oracle) [ˆ]:表示非此字符范围
eg:
a[ˆbc]
eg:
select * from employee where address like '%吴';
select * from employee where address not like '_吴';
- 查询空值
空值:一种是从未填写过数据,二是填写过数据后删除掉的
eg:
select * from employee where address is null;
select * from employee where address =' ';
select * from employee where address is not null;
- 带and的多条件查询
可以N个,必须同时满足条件,只要有一个不满足,则如此记录将会被删除。
eg:
select * from employee where age between 15 and 25 and address like '%东吴';
select * from employee where age between 12 and 25 and address like '%东吴' and sex= '女';
- 带or的多条件查询
可以N个,无需同时满足条件,只要其中一个条件满足,则记录会被查到,只有条件都不满足时如此记录才会被删除掉
eg:
select * from employee where age between 15 and 25 or address