原标题:Oracle数据库与SQL语言总结【面试考点】
Mysql基于数据库的管理机制
Oracle基于用户的数据库管理机制
创建一个信息系统,给你分配一个用户,创建空间、创建表
Oracle版本:
Oracle10g(10.21)官方文档:支持xp不支持win7/win8 linux unbutu
Oracle11g(11.2)支持以上
10.21版本是经典版本,两个差别很多
Oracle服务器时实例和数据库组成
一个实例对应一个oracle数据库
将物理文件的集合映射到内存
集群 : web tomcat6.0
数据库是物理磁盘文件的集合,实例访问数据库
Sql基本命令:
Select * from database
Set linesize 140
Set pagesize 140
140行另一页显示
Host cls 清屏
查找有哪些表:
Select * from table
(table 超级字典 超级管理员分配给普通用户的资源,系统创建的时候已经创建好)
查看字段:
Desc dept
字段的格式:
第一个字段是所在部门编号,第二个字段是名称,第三个字段是所在城市
查看表结构:
Desc data_emp
以员工表为例 data_emp
1、查询所有列:select * from data_emp;
2、查询部分的列:select name , age, salary form data_emp;
3、查询别名:
Select dpartNum as “员工编号”, name “姓名”, salary 月薪,daptName “部门 名称” from data_emp;
如果有些如错误,内容比较对,重复输入比较麻烦,这时使用命令:ed (edit)编辑,默认打开记事本,并且将上次的命令在记事本中显示,在记事本中完成修改之后,回到命令框中会有反斜线出现:表示重复执行上次命令。
查询别名带表达式:
Select dpartNum as “员工编号”, name “姓名”, salary*12 年薪,daptName “部门 名称” from data_emp;
4、查询重复列 检索部门编号 distinct去掉重复列
Select distinct depart from data_emp;
SQL大小写不敏感。可以在一行或者多行写,关键字不能缩写也不能分行,个句子一般要分行写,使用缩进提高语句的可读性。
控制定义: NULL 捣蛋鬼
控制不是空也不是0,是无效的,未指定的,未知的不可预知的值 NULL != NULL
注意:(conn表示奖金)
Select empNum,name,salary*12 年薪,(salary*12 + conn) 年收入 from data_emp;
这时候,会有人显示没有年收入,但有年薪。这是因为这里的部分人没有奖金,但是任何值NULL与运算都是NULL所以这里没有年收入。
所以此处需要进行NULL修正,也就是遇空修正。
应用一个修正函数:nvl(conn,0) 意思就是将conn 修正为0;
Select empNum,name,salary*12 年薪,(salary*12 + nvl(conn))即可完成需求。
在看一个NULL != NULL的案例:
查询奖金为空的员工:
Select * form date_emp where conn is NULL; 正常显示
Select * form date_emp where conn is not NULL; 正常显示
Select的解锁列可以来自任何列
连接:
将任何两个列可以通过|| 连接起来显示
例如:select “111’’ || ‘’222” “连接”,name, id from data_emp;
虚表(伪表)概念:
查询连接的不依赖于任何一个业务表 关键字 dual
Select “111” || “222” from dual;
Select sysData from dual;
SQL与sqlplus
SqlPlus通sql语言访问oracle服务器,服务器将查询结果通过sqlplus命令显示给salplus浏览器,最后将格式化报表反馈给客户端
Sqlplus与SQL命令:
Sql是一种语言,ANSI标准99,关键字不能缩写,使用语句控制数据库表中的定义信息与表中的数据;
SQLplus是一种环境,oracle特性之一,关键字可以缩写,命令不能够改变数据库中的值,用于集中运行。
Select/update/delete
Ed/set/col(sqlplus 工具命令)
Help topic将查看所有的sqlplus命令
使用: ? edit (命令) 查看如何使用
条件查询:
Select ….form data_emp where colname = 20; 查看行名为20的…..所有数据
Select …. From data_emp where colname between 100 and 200; 100到200之间的数据。
Select …fron data_emp where colname in (10,20); 查看colname是10和20 的两种的
再将 = 变换为 > < 即可 所有语法!
获取日期格式:
Select sysdate from dual;
获取系统配置参数:
Select * from v$nls parmaters; 获取系统配置参数
修改:
Alter session set MLS_DATE_FORMAT = “DD_Mon_RR”;
Alter session set MLS_DATE_FORMAT = “yyyy_mm_dd”;
查找的时候:必须按照特定格式,非中国人习惯的格式
Select * from emp where WIREDATE < ’08-09月-01’;检索名中日期是格式名敏感的。
Sql语句中字符串是使用单引号
薪水在1000到2000的之间的员工
Select * from data_emp where salary >1000 and salary <2000;
Select * from data_emp where salary between 1000 and 2000;
查询是10和20号部门的员工信息:
Select * from data_emp where dapartNum in (10 ,20);
查询不是10和20号部门的员工信息:
Select * from data_emp where dapartNum not in (10 ,20);
再看:查询一个员工信息在10 20 或者不在任何部门的员工信息:
Select * from data_emp where dapartNum in (10,20,null);
Select * from data_emp where dapartNum not in (10,20,null) ; 不是10 20 也不是任何部门
Null第三点:如果集合里面有空值,不能使用not in 但可以使用 in查询
NULL前两点:NULL不等于0 NULL不能使用 != 操作,而是使用 is not NLL
LIKE模糊查询:
使用like运算查询类似的值,选择条件可以包括字符或者数字;
%代表零个或者多个任意个字符
—代表一个字符
查询名字以S开通的员工信息
Select * from data_emp where name like ‘S%’;
查询员工名字是四个字符的Select * from data_emp where name like ‘-----’;
查询名字中带有转义字符的员工信息:
查询名字中带有下划线的员工信息
Select * from data_emp where name = ‘%\_%’
有关排序:
使用order by 默认是从小到大,使用order by desc 将是降序 从大到小
Select * from data_emp order by salary;
Select * from data_emp order by salary desc, nameNum desc;
2、order by 后面可以跟列名 表达式 别名 序号
3、order by 后面可以跟多个列
4、NULL :如何将NULL排在最后面
按照奖金进行排序查询,员工信息
Select * from emp order by comm desc nulls last;
Sql函数
函数分为单行函数和多行函数
单行函数:字符、通用、数值、转换、日期五类函数
字符函数:
大小写控制函数:
LOWER UPER INITCAP(首字母大写)
Select lower(‘hEllo wOrld’) as 转小写,upper(’hello world’) 转大写,initcap(‘hello’) from dual
字符控制函数:
CONCAT
Select concat(‘aaa’ ,‘bbb’)from dual; 三个不行 只能连接两个 可以嵌套进行三个
Select concat(‘aaa’ ,concat(‘bbb’,‘vvv’)) from dual;
SUBSTR
Substr从a字符串的第b位开始取 Substr从a字符串的第b位开始取c个
Select substr from dual; 结果为defg
Select substr from dual; 结果为de
LENGTH/LENGTHB 求 字符和字节 数 一个中文字符是两个字节
Select length(‘中国abc’),字符数,lengthb(‘中国abc’) 字节数 from dual;
结果是5、7
INSTR(‘母串’,‘子串’); 从母串中查找子串的位置
Select instr(‘abcdefghhhh’,’hhhh’) from dual;
LPAD | RPAD 填充
Lpad(‘abcd’,10,’*’) 给左边填充*使够十个 rpad(‘abcd’,10,’*’)给右边补*
Select Lpad(‘abcd’,10,’*’),左填充 ,rpad(‘abcd’,10,’*’) 右填充 from dual;
TRIM
Select trim(‘H’, from ‘HellHoeHHHHHH’) from dual;去掉前面的H和后面的H 中间的不管,可以是字符也可以是空格,一般就是为了去掉空格而设计的。
REPLACE 替换
Replace(‘abcde’,b,m)将字符b替换为m;
数字函数:
Round(45.926,2) 四舍五入保留两位有效数字 45.93
Trunc(45.926,2) 截取,保留两位 45.92
Mod(1600,300) 求余 100;
oracle日期
select to_char(sysdate,’yyyy-mm—dd hh24:mi-ss’ from dual;
强转为字符串并且按照中国人的格式显示日期和时间
Mysql日期
Date datetime……..多种类型
日期的运算
计算多少天多少周多少年
Select name, (sysdate-hrdate) 天,(sysdate-hrdate)/7 周,(sysdate-hrdate)/30 月,(sysdate-hrdate)/365 年from data_emp ;员工来了….年月日的时间
两个日期之间可以相减,返回值是天数.
求昨天 今天和 明天
Select sysdate-1 昨天, sysdate 今天,sysdate+1 明天 from dual;
使用to_char(sysdate-1,’yyyy—mm—dd’)改为中国习惯
精确的计算员工的入职月数 两个日期相差的月数
Select name 姓名, (sysdate-hrdate)/30 粗算月,Month_between(hiredate,sysdate) 入职月精确数 from date_emp;
在sql语句中使用if_then_else逻辑
两种方法:
1、 Case表达式,sql99的语法,类似于basic,比较繁琐
2、 Decode函数,oracle自己的函数,类似于java,比较简介
涨工资:总裁涨1000 经理涨800 员工涨500
Select last_name ,job_id 工种,salary 涨前工资 ,
(Case job_id when ‘president’ then salary+1000
When ‘manager; then salary+800
When ‘ordinary’ then salary+500’
Else salary end) 涨后工资,from data_emp;
使用decode函数:
Select last_name,job_id,salary 涨前工资,
Decode(job_id, ‘president’, salary+1000,
‘manager; then salary+800,
‘ordinary’ then salary+500’
Salary) 涨后工资,
From data_emp;
多行函数:分组函数不是难,而是很多情况下不知道使用分组函数,或者说想不到使用分组函数
求平均、平均奖金……(avg count max min sum)
分组数据
分组过滤
使用group by进行分组,使用having子句进行过滤分组结果集
求员工表中最高工资、最低工资、平均工资 所有工资:
Select max 最高工资,min 最低工资,avg 平均工资,salary 工资 from dat_emp;
使用nvl(comm,0)修正函数,将comm奖金修正为零
求出员工表中各个部门的平均工资(分组数据)
Select---
From----
Where colum >20
Order by--------
Group by-----------
Group by是检验数据库实力的一个标准
Select deaprtNum, avg(salary) from data_emp group by departNum;
按照系别分组求从员工信息表中求取每个组的平均工资
Select deaprtNum, avg(salary) from data_emp order by name_id group by departNum;
检索的数据列即紧跟select后面的数据,必须是分组group byz分组后的,如果没有在分组中,语法就错误。
Group by 后面可以跟多个列
这个问题很多人都出错
分组过滤案例:
查询平均工资在2000以上的部门
分析:最终落脚点是部门,所以需要用到分组,外加大于两千的条件,即分组过滤
Select departNum, avg(salary) from emp group by departNum having avg(salary) > 2000;
Where子句与having 子句区别:
案例:
求10号部门的平均工资
先分组后过滤
Select departNum , avg(salary) from data_emp group by departNum having departNum = 10;
先过滤,后分组
Select departNum, avg(salary) from data_emp where departNum =10 group by departNum;
致谢:传智播客
本文是在B站学习数据时期的笔记,部分内容为了求做笔记速度,比较仓促,基本根据上下文及字面意思加上理解,不会影响阅读!返回搜狐,查看更多
责任编辑: