这篇用的文件都是软件里自带的表Tables——EMP、DEPT、SALGRADE、BONUS
目录
怎么导出表
右键表——Export data
简单查询
语句:select * from emp
--试用期的工资是多少(3个月,80%开支),3个月之后转正,问,这一年 每名员工,一共赚了多少钱?
select ename 名字,(sal*0.8)*3+sal*9 as 工资 from emp
空值的运算
-- 计算 奖金和工资的和
-- comm字段中 有null,这个null不能参加计算,如果参加计算,那么最终结果也是null
-- select sal+comm from emp
--nvl(字段,转成什么) 判断这个字段是否为null值,如果是null那么就把这个字段内容转成0或者其他
select sal+nvl(comm,0) from emp
|| 用来连接列或字符串
--可以使用||来将多个列或字符串连接在一起
select ename ||'=='|| sal as 信息 from emp
distinct去掉重复行数据
-- 所有员工中有几种岗位
-- 去掉重复行数据
select distinct job from emp
-- 两个都重复才能去掉
select distinct ename,job from emp
where条件语句
where语句后接条件表达式。返回值:真、假、空。只显示经过条件判断结果为真的记录
-- 条件
-- 查询 部门号是30的所有员工信息
-- where表示条件
select * from emp where deptno=30
-- 用 is null的方式,来判断空值,不能使用字段=hull的方式进行判断
select * from emp where comm is null
-- 不是null的所有信息显示出来
select * from emp where comm is not null
字符串或日期都需要加单引号
-- 字符串或日期都需要加单引号
select * from emp where ename='WARD'
比较运算符
-- = > >= < <= <> 常用的比较运算符
select * from emp where sal>=2000
-- 不等于
select * from emp where sal<>3000
日期的表示
dd-MM-YYYY
-- oracle 默认日期格式 dd-MM-YYYY
select * from emp where hiredate > '20-2月-1981'
练习1
1.查询职位为SALESMAN的员工编号、职位、入职日期
2.查询1985年12月31日之前入职的员工姓名及入职日期
3.查询部门编号不在10部门的员工姓名、部门编号
-- 1.查询职位为SALESMAN的员工编号、职位、入职日期
select empno,job,hiredate from emp where job='SALESMAN'
-- 2.查询1985年12月31日之前入职的员工姓名及入职日期
select ename,hiredate from emp where hiredate < '31-12月-1985'
-- 3.查询部门编号不在10部门的员工姓名、部门编号
select ename,deptno from emp where deptno<>10
范围表示
1.可以用比较的方法写,and连接
2.between...and...
-- 查询出工资在1500到2500之间的所有员工人员信息
select * from emp where sal>=1500 and sal<=2500
-- between 开始 and 结束 表示在开始和结束这个区间范围的所有信息
-- 注意:1.是包含边界值 2.不要写反!!
select * from emp where sal between 1500 and 2500
in限制条件
in(信息1,信息2,信息3……)表示在in这个范围内,如果有符合要求的就显示出来
-- 查询出员工编号是7369或者7499或者7654这几个人的信息
select * from emp where empno=7369 or empno=7499 or empno=7654
-- 可以用 in 来简化使用,in(信息1,信息2,信息3……)表示在in这个范围内,如果有符合要求的就显示出来
select * from emp where empno in(7369,7499,7654)
like模糊查询
模糊查询 会有两个通配符:% _
% 表示任何信息(任何长度,任何字符)
_ 表示一个字符
模糊查询的语法:select * from 表名 where 列名 like 'A%'
not like 表示不符合要求
-- %A% 这种模式是 无论A在什么位置,只要字段里有A就显示出来
select * from emp where ename not like '%A%'
-- W开头,长度2
select * from emp where ename like 'W_'
练习2
1.查询入职日期在82年至85年的员工姓名,入职日期。
2.查询月薪在3000到5000的员工姓名,月薪。
3.查询部门编号为10或者20的员工姓名,部门编号。
4.查询经理编号为7902, 7566, 7788的员工姓名,经理编号。
5.查询员工姓名以W开头的员工姓名。
6.查询员工姓名倒数第2个字符为T的员工姓名。
7.查询奖金为空的员工姓名,奖金。
----------------------练习--------------------------------
--1.查询入职日期在82年至85年的员工姓名,入职日期。
select ename,hiredate from emp where hiredate between '1-1月-1982' and '31-12月-1985'
--2.查询月薪在3000到5000的员工姓名,月薪。
select ename,sal from emp where sal between 3000 and 5000
--3.查询部门编号为10或者20的员工姓名,部门编号。
select ename,deptno from emp where deptno in(10,20)
--4.查询经理编号为7902, 7566, 7788的员工姓名,经理编号。
select ename,mgr from emp where mgr in(7902,7566,7788)
--5.查询员工姓名以W开头的员工姓名。
select ename from emp where ename like 'W%'
--6.查询员工姓名倒数第2个字符为T的员工姓名。
select ename from emp where ename like '%T_'
--7.查询奖金为空的员工姓名,奖金。
select ename,comm from emp where comm is null
优先级
and的优先级要高于or
注意:在写or和and的同时一定要注意优先级,那就把or用括号括起来
-- 查找职位为ANALYST或者CLERK,并且工资大于1000的人
select ename,job,sal
from emp
where job = 'ANALYST' or job = 'CLERK' and sal>1000
-- 相当于
select ename,job,sal
from emp
where job = 'ANALYST' or (job = 'CLERK' and sal>1000)
-- 正确写法
select ename,job,sal
from emp
where (job = 'ANALYST' or job = 'CLERK') and sal>1000
ORDER BY排序
ASC:升序,默认
DESC:降序
-- 排序
-- order by 要写在sql语句的最后面
-- 升序和降序
-- 升序:从小到大
-- 升序关键字:ASC。order by 默认情况下就是升序,所以ASC可以省略不写
select * from emp order by sal ASC
-- 降序:从大到小
-- 降序关键字:desc。降序的时候必须写这个关键字
select * from emp where deptno=20 order by sal desc
-- 组合排序
select * from emp order by sal desc,hiredate asc
-- 排序 还可以根据别名进行排序
select ename 名字,sal 工资 from emp order by 工资 desc
-- 排序 还可以根据列的位置来进行排序 2表示第二列
select ename 名字,sal 工资 from emp order by 2 desc
练习3
1.查询部门在20或30的员工姓名,部门编号,并按照工资升序排序。
2.查询工资在2000-3000之间,部门不在10号的员工姓名,部门编号,工资,并按照部门升序,工资降序排序。
3.查询入职日期在82年至83年之间,职位以SALES或者MAN开头的员工姓名,入职日期,职位,并按照入职日期降序排序。
4.查询入职时间在1982-7-9之后,并且不从事SALESMAN工作的员工姓名、入职时间、职位。
5.查询员工姓名的第三个字母是a的员工姓名。
6.查询除了10、20号部门以外的员工姓名、部门编号。
7.查询部门号为30号员工的信息,先按工资降序排序,再按姓名升序排序。
8.查询没有上级的员工(经理号为空)的员工姓名。
9.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
-----------------练习--------------------------------
--1.查询部门在20或30的员工姓名,部门编号,并按照工资升序排序。
select ename,deptno,sal from emp where deptno in(20,30) order by sal asc
--2.查询工资在2000-3000之间,部门不在10号的员工姓名,部门编号,工资,并按照部门升序,工资降序排序。
select ename,deptno,sal from emp where (sal between 2000 and 3000) and deptno<>10 order by deptno asc,sal desc
--3.查询入职日期在81年至83年之间,职位以SALES或者MAN开头的员工姓名,入职日期,职位,并按照入职日期降序排序。
select ename,hiredate,job from emp
where hiredate between '1-1月-1981' and '31-12月-1983'
and job like 'SALES%' or job like 'MAN%'
order by hiredate desc
--4.查询入职时间在1982-7-9之后,并且不从事SALESMAN工作的员工姓名、入职时间、职位。
select ename,hiredate,job from emp where hiredate>'9-7月-1982' and job<>'SALESMAN'
--5.查询员工姓名的第三个字母是A的员工姓名。
select ename from emp where ename like '__A%'
--6.查询除了10、20号部门以外的员工姓名、部门编号。
select ename,deptno from emp where deptno not in(10,20)
--7.查询部门号为30号员工的信息,先按工资降序排序,再按姓名升序排序。
select * from emp where deptno=30 order by sal desc,ename asc
--8.查询没有上级的员工(经理号为空)的员工姓名。
select ename,mgr from emp where mgr is null
--9.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
select * from emp where sal>=4500 and deptno in(10,20)
字符处理函数
函数 | 功能 |
upper(‘字符串’) | 把字符变成大写 |
lower(‘字符串’) | 把字符变成小写 |
initcap(‘字符串’) | 把每个单词的第一个字母变成大写 |
concat(字符串1,字符串2) | 拼接字符串 |
substr(字符串,开始位置,结束) | 对字符串进行分割 |
length(字符串) | 返回字符的长度 |
instr(字符串,字符) | 返回指定字符所在的位置 |
lpad(字符串,一共几位,填充字符) | 对字符串进行左填充 |
rpad(字符串,一共几位,填充字符) | 对字符串进行右填充 |
trim(字符串) | 去掉字符串两端的空格 |
---------------------字符处理函数--------------------------------------
-- 把字符变成大写 upper
select upper('ddkien') from emp --DDKIEN
-- 把字符变成小写 lower
select lower('JKDddF') from dual --jkdddf
-- initcap 可以把每个单词的第一个字母变成大写
select initcap('sql hello') from dual --Sql Hello
-- concat 表示连接字符串拼接的意思
select concat('db','hello') from dual --dbhello
select concat(ename,sal) from emp
-- substr 表示对某个字符串进行分割截取
-- substr(ename,1,3) ename 表示字符,1表示从哪里开始,3表示截取多少位
select substr(ename,1,3) from emp
-- 返回字符的长度,是一个数字
select length(ename) from emp
-- 返回指定的字符所在的位置
select instr(ename,'A'),ename from emp
-- 对字符串进行填充
-- lpad 左填充
-- rpad 右填充
select lpad(ename,10,'*'),lpad(sal,10,'$'),rpad(job,10,'#') from emp
-- 去掉字符串两端的空格
select trim(' hello ') from dual
dual是个‘伪表’,可以用来测试函数和表达式
数字函数
函数 | 功能 |
round(数,保留几位小数) | 四舍五入 |
trunc(数,截到第几位) | 截取 |
mod(数,数) | 取余 |
------------------------数字函数----------------------
-- round 四舍五入,2表示保留2位小数
select round(123.456,2) from dual --123.46
-- 120 后面的数变成0
select round(123.456,-1) from dual --120
-- trunc 截断,表示后面的信息不会再进位了
select trunc(123.456,1) from dual --123.4
-- mod 取余 7/5
select mod(7,5) from dual --2
练习4
1.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
2.使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’分别处理得到下列字符串ello、Hello、ll、hello。
3.将员工工资按如下格式显示:123,234.00 RMB 。
4.查询员工的姓名及其经理编号,要求对于没有经理的显示“No Manager”字符串。
--------------------练习-------------------
-- 1.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
select * from emp where ename like '__A%'
select * from emp where substr(ename,3,1)='A' --从第三位截取,截一个
-- 2.使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’
-- 分别处理得到下列字符串ello、Hello、ll、hello。
-- ltrim 表示去掉左边的空格 rtrim表示去掉右边的空格
-- ltrim('hello','h') 表示 可以去掉 字符中左边的某个字符
-- trim()不支持两个参数
select ltrim('hello','h') from dual
select trim(' Hello ') from dual
select ltrim(rtrim('bllb','b'),'b') from dual
select trim(' hello ') from dual
-- 3.将员工工资按如下格式显示:123,234.00 RMB 。
-- to_char(sal)转成字符型
select to_char(sal,'999,999.00') || 'RMB' from emp
-- 4.查询员工的姓名及其经理编号,要求对于没有经理的显示“No Manager”字符串。
select ename,nvl(to_char(mgr),'No Manager') from emp