花点时间整理下sql基础,温故而知新。文章的demo来自oracle自带的dept,emp,salgrade三张表。解锁scott用户,使用scott用户登录就可以看到自带的表。
#使用oracle用户登录linux
[oracle@localhost ~]$ sqlplus / as sysdba;
......
SQL> alter user scott account unlock;
四大语句
DQL语句——select
DML语句——insert,upate,delete等(关键字后带表名)
DDL语句——create,alter,drop等(关键字后带table关键字再跟表名)
事务控制语句——commit,rollback
基础select语句
简单select语句
select * from emp;
包含算术表达式的语句
--查询出来的sal乘以5
select ename , sal*5 from emp;
--表中取出2*3的结果
select 2*3 from emp;在emp;
--当我们需要显示一个结果的时候就用系统提供的dual虚表
select 2*3 form dual;
获取系统当前的时间
--sysdate在Oracle中表示当前系统时间
select sysdate from dual;
含有别名的语句
--显示出来的是salary字段名而不是sal
select ename, sal*12 salary from emp;
含有空值
空值表达式
--含有任何null值的数学表达式最后的结果都为null
select ename, sal*5+comm from emp;
空字符串(字符串、字符串连接符)
--含有任何null值的字符串表达式中,null被当作空字符串处理
select empno, ename || 'abc' || mgr from emp;
--字符串连接符:||。select语句中用单引号表示字符串
distinct消除重复
--用distinct消除结果集中deptno相同的记录
select distinct deptno from emp;
--消除结果集中job和deptno都相同的记录
select distinct job, deptno from emp;
where 条件过滤
等值判断
select * from emp where empno = 7369;
非等值判断
(1)>,<,>=, <= ,<>
--取出所有部门号不是10的雇员的名字和薪水
select ename, sal from emp where deptno <> 10;
(2)between..and..
--包含最大值和最小值
select ename, sal from emp where sal between 800 and 1500;
(3)字符串比较
比较字符串ASCII码值的比较,先比较第一字母,依次比较每个字母
select ename, sal from emp where ename > ‘DBA’;
空值处理
--为空
select ename from emp where comm is null;
--不为空
select ename from emp where comm is not null;
注意,select ename from emp where comm = null;这种写法表示comm字段的值等于null,is null 或者is not null返回的boolean值,而=null返回的是null。null是一种特有的数据类型,其等价于没有任何值、是未知数。因此通常会获取不到结果却不提示语法错误。
in语句
--在选定范围
select ename, sal from emp where sal in (800, 1250, 1500, 2000);
--不在选定范围
select ename, sal from emp where sal not in (800, 1500, 2000);
日期处理
--Oracle默认的日期格式为:DD-MON-RR
select ename, hiredate from emp where hiredate > ’20-2月-1981’;
--to_char(),把日期类型字段转为指定格式的字符串
--输出2008-04-04
select to_char(sysdate, ‘YYYY-MM-DD-HH24:MI:SS’) from dual;
--to_date(),将日期字符串转为指定格式的日期
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);
and,or,not语句
--and
select deptno, ename, sal from emp where deptno = 10 and sal > 1000;
--or
select deptno, ename, sal from emp where deptno = 10 or sal > 1000;
--not
select ename, sal from emp where sal not in (800, 1500, 2000);
like模糊查询(通配符匹配、单字符匹配、转义字符)
(1)通配符%,代表0个或者多个字符
select ename from emp where ename like ‘%ALL%’;
(2)单个字符-,代表一个字符
select ename from emp where ename like ‘_A%’;
(3)转义字符\,统默认的转义字符是 ‘\’
--中间的%不表示通配符,而是表示姓名包含%
select ename from emp where ename like ‘%\%%’;
order by排序
asc升序
select ename, sal from emp order by ename asc;
desc降序
select ename, deptno from emp order by ename desc;
常用sql函数
lower()——将字符串全部转成小写
select lower(ename),sal from emp where lower(ename) like '%a%';
upper()——将字符串全部转成大写
select upper(ename),sal from emp where upper(ename) like '%a%';
substr()——截取字符串
--从第2个字符开始截取ename
select substr(ename, 2) from emp;
--substr('hello',1,3),输出hel,第三个参数可以省略
select substr('hello', 1,3) from dual;
chr()——求一个与某个ASCII码值对应的字符
select chr(65) from dual;
ascii()——求一个字符的ASCII码值
select ascii(‘A’) from dual;
round()——四舍五入
对参数值进行四舍五入,可以指定四舍五入到小数点后几位,可以用负数指定小数点前面几位
--输出24
select round(23.652) from dual;
--输出23.65
select round(23.652,2) from dual;
--输出20,-1表示对个位数进行四舍五入
select round(23.652,-1) from dual;
--输出0,-2表示对十位数进行四舍五入
select round(23.652,-2) from dual;
to_char()——日期类型转字符串
把日期类型字段转为指定格式的字符串,可以精确到时分秒
--输出2008-04-04
select to_char(sysdate, ‘YYYY-MM-DD’) from dual;
--2008-04-04 01:10:21
select to_char(sysdate,'YYYY-MM-DD hh:mm:ss') from dual;
to_date()——字符串转为日期类型
将日期字符串转为指定格式的日期
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);
to_number()——字符串转数字
将指定的字符串转换成数字格式,第一个参数为要转为数字的字符串(一定要确保所转换字段是可转换为数字的,否则报“invalid number”),第二个参数为指定的格式
select * from emp where sal > to_number(‘$1,200.00’, ‘$9,999.99’);
nvl()——空值处理
这个函数有两个参数:
第一参数:字段名或表达式,如果这个参数值为null,就返回第二参数值,否则返回第一参数值。
select ename, sal*12+nvl(comm, 0) from emp;
组函数
min()——求最小值
max()——求最大值
avg()——求平均值
sum()——求总和
select sum(sal), avg(sal), max(sal), min(sal) from emp;
count()——求数量
--求记录数量
select count(*) from emp;
--求comm字段中所有非空记录的数量
select count(comm) from emp;
--和distinct一起使用,得到所有唯一值记录的数量
select count(distinct deptno) from emp;
分组函数
对组函数的查询结果进行分组
group by
使用group by 的规律:出现在select列表中的字段,如果没有出现在组函数中,则必须出现在group by子句中。
select deptno, avg(sal) from emp group by deptno;
--典型错误,group by缺少ename
select ename, deptno, max(sal) from emp group by deptno;
--求出每个部门中最赚钱的那个人的名字
select ename, deptno, sal from emp where sal in (
select sal from (
select deptno, max(sal) sal from emp group by deptno
))
having
having语句的作用是对分组之后的数据进行过滤数据,where 语句的作用是分组之前过滤数据。
--注意不能使用where
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
综合练习
从emp这张表里把平均工资和部门编号列出来,并且过滤掉大写是A的名字 把剩下的数据按照部门编号进行分组,分组之后的平均薪水必须大于2000,按照部门编号的倒序排列。
分析:分组,过滤,排序;过滤又包含分组前过滤(where)和分组后过滤(having)。编写顺序:分组前过滤(where) > 分组(group by) > 分组后过滤(having) > 排序(order by)
select avg(sal),deptno,ename from emp where ename not like '%A%' group by deptno,ename having avg(sal) > 2000 order by deptno desc;
本文就到此,下一篇我们继续整理多表连接查询、子查询、视图相关内容。