Oracle日常学习笔记01

Oracle日常学习笔记01

登陆账户Scott，密码tiger

首先是别名学习

Select * From Emp;
SELECT ENAME,EMPNO,JOB FROM EMP;
select EMPNO 员工编号,ename 员工姓名 from emp;

–去重复

select job from emp;
select DISTINCT job from emp;–只有所有的列都相同才被认为是相同的行可以合并

–字符串连接查询

员工编号是什么姓名是什么工作是什么
select ‘员工编号是’ || empno || ‘姓名是’ || ename || ‘的工作是’ || job from emp;

–查询中的四则运算

查每个人的年薪

select ename,sal*12 from emp;

–限定查询

查询员工工资大于1500的雇员

select ename,sal from emp where sal>1500;

–查询出每个月能得到奖金的雇员

select * from emp;
select * from emp where comm is not null;
select * from emp where comm is null;

–查询出工资大于1500并且还有奖金的员工

select * from emp where sal>1500 and COMM is not null;

–查询出工资大于1500或者还有奖金的员工

select * from emp where sal>1500 or COMM is not null;

–查询出工资不大于1500并且没有奖金的员工

select * from emp where sal <= 1500 and COMM is null;
select * from emp where not(sal > 1500 or COMM is not null);

–工资在1500到3000之间的人

SELECT * FROM emp where sal BETWEEN 1500 and 3000;–两边同时取等于，包含边界
select * from emp where sal>=1500 and sal<=3000;

–查询1981-1-1到1982-12-31号入职的员工

select * from emp where HIREDATE BETWEEN TO_DATE(‘1981/1/1’,‘yyyy/mm/dd’) and TO_DATE(‘1981/12/31’,‘yyyy/mm/dd’);

–注意：SQL语句不区分大小写但是表中的值区分大小写

select * from emp where ENAME = ‘smith’;
select * from emp where ENAME = ‘SMITH’;

–查询雇员编号是7369、7499、7521的员工编号和具体信息

select * from emp where EMPNO = 7369 or EMPNO = 7499 or EMPNO = 7521;
select * from emp where empno in(7369,7499,7521);

–模糊查询

"%"可以匹配任意长度的内容
–“_”可以匹配一个长度的内容

–查询中所有雇员姓名中第二个字母是M的雇员

select * from emp where ename like ‘_M%’;

–最后一个字符数M

select * from emp where ename like ‘%M’;

–查询中员工编号不是7369的员工

SELECT * FROM emp where EMPNO != 7369;
SELECT * FROM emp where EMPNO <> 7369;