1.基本查询
-基本的查询语句
select [distinct] column_name1,.../* from table_name [where conditions];(distinct会去掉重复的行)
-设置查询结果的显示格式
COLUMN column_name HEADING new_name;(更改查询后结果的字段名,COLUMN可以简写为col)
COLUMN column_name FORMAT dataformat;(显示的格式,如字符的长度,数值的格式,正确会显示,不正确会用#代替)
-清除格式
COLUMN column_name CLEAR;
-给字段设置别名
select column_name AS new_name,... from table_name;(as可用空格代替)
示例:select id as 编号,username 用户名,salary as 工资 from userinfo_c;
2. 运算符和表达式
-算术运算符(+,-,*,/)
示例:select id,username,salary+200 from userinfo_c;(结果的salary会增加200,而原始表中的数值不变)
-比较运算符(>,>=,<,<=,=,<>)(得到的结果为bool值)
示例:select username from users where salary>800;
-逻辑运算符(and,or,not)(结果也为bool值)
示例:select username from users where salary>800 and salary<1800;
3. 带条件的查询
-单一条件的查询
示例:select salary from userinfo_c where username=‘aaa’;
-多条件的查询
示例:select username from users where username=‘aaa’ or (salary between 500 and 8000);
-运算符优先级
括号优先级最大,比较运算符优先级(not>and>or)>逻辑运算符>算术运算符
4. 模糊查询
-like运算符(查询的结果也是bool值)
通配符的使用(_,%)(一个_只能代表一个字符,%可以代表0到多个任意字符)
示例:select * from users where username like ‘a%’;(查询用户名为a开头的用户信息)
select * from users where username like ‘a_ _’;(查询用户名为三位数且为a开头的用户信息)
select * from users where username like ‘_a%’;(查询用户名第二位为a的用户信息)
select * from users where username like ‘%a%’;(查询用户名中包含a的用户信息)
5. 范围查询
-between ... and ...(可以在between前加not)
-in/not in
示例:select * from users where username in (‘aaa’,‘bbb’);
6. 查询结果排序
基本语句:select ... from ...[where ...] order by column1 desc/asc,...;(desc是降序,asc是升序)
示例:select * from users order by id desc,username asc;
7. case...when语句的使用
基本语句1:case column_name when value1 then result1,...[else result] END(as name)
示例:select username,case username when ‘aaa’ then ‘计算机部门’
when ‘bbb’ then ‘市场部门’ else ‘其他部门’ end as 部门 from users;
基本语句2:case when column_name=value1 then result1,...[else result] END(as name)
示例:select username,case when username=‘aaa’ then ‘计算机部门’
when username=‘bbb’ then ‘市场部门’ else ‘其他部门’ end as 部门 from users;
8. decode函数的使用
基本语句:decode(column_name,value1,result1,...,[defaultvalue])(不写defaultvalue则为空值)
示例:select username,decode(username,’aaa’,’计算机部门’,’bbb’,‘市场部门’,’其他’)as 部门
from users;