Select专题
一、基础
1.基本的Select 查询语句
语法:
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
(1)查询所有的列:
select * from dept;
select dname,deptno,loc from dept;
*的效率低
(2) Sql语句的书写规范:
1.大小写不敏感
2.可以分行写
3.关键字不能分行和和缩写
4.条件一般分行写
select * FROM EMP;
SELECT *
FROM emp;
(3)数学表达式 :+ - * /
select ename,sal+300 from emp;
运算符的优先级:
*、/ > + - 同级从左到右进行运算,( )优先级最高
select ename,sal,12*sal+100 from emp;
select ename,sal,12*(sal+100) from emp;
(4) NULL 是一个不可用的值,不同于0或空行
select ename,sal,comm from emp;
select ename,sal,12*sal*comm from emp;
(5)定义字段column别名 alias
可以直接在字段column名后,也可以是column名 as 别名alias
需要双引号如果包含空格或特殊字符,再者是为了大小写敏感
select ename name,sal,12*sal*comm from emp;
select ename name,sal,12*sal*comm as "Annual Salary" from emp;
(6)连接字符 ||
select ename||job as "Employees" from emp;
select ename||' is a '||job as "Employees" from emp;
(7)常量 :字符, 数字,日期等(在Select中)
日期和字符必须要单一行单引号''包围起来
select ename ||' is a'||job as "Employee Details" from emp;
(8)处理重复的行 distinct
select deptno from dept;
select distinct deptno from emp;
二、约束和排序数据
语法:
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY];
(1) WHERE 条件
日期和字符必须要单一行单引号''包围起来,
字符串的值是大小写敏感的,日期date的值是格式敏感的(默认:DD-MON-RR)
1. 约束列
select * from emp where deptno=30;
select * from emp where ename='KING';
2. 比较:=, >,>=, <,<=,<>
select * from emp where sal>2000;
3. between ... and ...,in(set),like, is null
select * from emp where sal between 2000 and 2500;
select * from emp where mgr in (7698,7839,7788);
//%>=0 _=1
select * from emp where ename like 'A%';
select * from emp where ename like '_A%';
//转义字符 ESCAPE
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%SA\_%' ESCAPE '\';
select * from emp where mgr is null;
4. 逻辑条件: and ,or,not
select * from emp where sal>1000 and job like '%MAN%';
select * from emp where sal>1000 or job like '%MAN%';
select * from emp where job not in ('SALESMAN','CLERK');
5.约束的优先级
1 Arithmetic operators 算术运算符
2 Concatenation operator 链接运算符
3 Comparison conditions 比较条件
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT logical condition
7 AND logical condition
8 OR logical condition
select * from emp where job='SALESMAN' or job='CLERK' and sal >=1300;
select * from emp where (job='SALESMAN' or job='CLERK') and sal >=1300;
(二) ORDER BY 排序 ,放在SELECT语句的最后
ASC: ascending order, default 升序
DESC: descending order 降序
1. select * from emp order by hiredate;
2. select * from emp order by hiredate desc;
3. 按照别名排序
select ename,sal,12*sal annsal from emp order by annsal;
4. 利用多列进行排序
select ename,deptno,sal annsal from emp order by deptno,sal desc;
三 、函数
(1)字符串函数
select ename,lower(ename) from emp; //转化为小写
select upper('Sql course') from dual;//转化为大写
select initcap('sql course') from dual;//转为驼峰写法
dual table :是和数据字典以前生成的一张表,dual是在SYS的Schema中,但能被所有的用户使用
dual表有一个DUMMY的字段,有一X的值
select * from emp where ename='smith'; //没记录
select * from emp where lower(ename)='smith';//有记录
select concat('Hello','World') from dual; // 字符串连接
select substr('HelloWorld',1,5) from dual;
select length('HelloWorld') from dual;
select instr('HelloWorld','W') from dual;
select lpad(sal,10,'*') from emp;
select rpad(sal,10,'*') from emp;
select trim('H' from 'HelloWorld') from dual;
(2)数值函数
select round(45.926,2),round(45.923,0),round(45.923,-1) from dual;
select trunc(45.926,2),trunc(45.923,0),trunc(45.923,-1) from dual;
select mod(1600,300) from dual;
(3)日期函数
可以+ - 一个日期;
两个日期相减得到两日期间的间隔天数;两日期不能相加
select sysdate from dual;
select sysdate+1 from dual;
select sysdate-1 from dual;
select ename,hiredate, (sysdate-hiredate)/365 from emp;
select sysdate+24/24,sysdate+33/24 from dual;
months_between(date1,date2)= (date1-date2)
select months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.
29','yyyy.mm.dd')) from dual;
add_months(date,number)=(date.month+number)
select sysdate as today ,add_months(sysdate,1) as next_month,
add_months(sysdate,-1) as last_month from dual;
next_day(date,'day')
select next_day(sysdate,5) from dual;
select next_day(sysdate,'星期五') from dual;
last_day(date)
select last_day('01-4月-95') from dual;
round(date,'format')
select round(sysdate,'month') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'year') from dual;
(4) 转化函数
a) to_char(date,'format_model')
format_model: 必须加单引号'' ,大小写敏感
YYYY(Full year in numbers)
YEAR(Year spelled out)
MM(Two-digit value for month)
MONTH(Full name of the month)
MON(Three-letter abbreviation of the month)
DY(Three-letter abbreviation of the day of the week)
DAY(Full name of the day of the week)
DD(Numeric day of the month)
HH or HH12 or HH24
MI
SS
select ename,to_char(hiredate,'fmDD Month YYYY') from emp;
b) to_char(number,'format_model')
9 代表数字
0
$ 美元符号
L 本地货币符号
. 小数点
, 千分割符
select ename,to_char(sal,'L999,999.00') from emp;
c) to_number(char[,'format_model'])
select to_number('1234') from dual;
select to_number('1234','9999') from dual;
d) to_date(char[,'format_model'])
select to_date('020908','ddmmyyyy') from dual;
(5) 通用函数
a) nvl(expr1,expr2) ,如果expr1为null,则用expr2代替
select ename,sal,nvl(comm,0) from emp;
b) nvl2(expr1,expr2,expr3),如果expr1不为null,则用expr2代替,否则用expr3代替
select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp;
c) nullif(expr1,expr2)
case when expr1=expr2 then null else expr1 end
d) case表达式
语法:
case expr when comparison_expr1 then return_expr1
[when comparison_expr2 then return_expr2
[when comparison_exprn then return_exprn
else else_expr]
end
select ename,job,sal,case job when 'CLERK' then 1.10*sal
when 'MANAGER' then 1.20*sal
else sal end "revised_salary"
from emp;
select ename,job,sal,case when job='CLERK' then 1.10*sal
when job='MANAGER' then 1.20*sal
else sal end "revised_salary"
from emp;
四、多表连接
连接类型: 等值连接,不等值连接,外连接,自连接
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
a) 等值连接 equijoins
select ename,dname
from emp,dept
where emp.deptno=dept.deptno;
select ename,dname
from emp,dept
where emp.deptno=dept.deptno
and emp.deptno=30;
select e.ename,d.ename
from emp e,dept d
where e.deptno=d.deptno;
select ename,dname
from emp e,dept d,
where e.deptno=d.deptno
Joining More than Two Tables
hr>select e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
b) 不等值连接 nonequijoins
select ename,sal,grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal
c)外连接 outer joins
展示两表不满足条件的column,+在那边表示多展示此表不满足的column
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
select e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;
select ename,dname
from emp,dept
where emp.deptno(+)=dept.deptno
and emp.deptno(+)=30;