_002_

====================================================

由于我们按照的是oracle12,所以学习中用到的scott用户没有自带,这对于新手来说是个很重要的实践,所以参考下面把scott用户弄出来

https://blog.****.net/cfchengfei002/article/details/45582259

注意创建账号的时候,我们创建的是c##scott,所以账号应该是c##scott,而不是scott

 

注意事项

　　1 如果想分行写,比如select * from  后面跟着表名,但是我们不想写在一行,就可以直接按回车键,输入表面,然后输入;

　　2 sql语句不区别大小写,但是字符串里的内容是区分大小写的

 

 

 

一些简单的指令

　　show user;    查看目前在哪个用户下

　　qiut               退出当前账号

　　desc 　　　　查看表结构

　　col ename for a20   设置列宽 ,ename是列,a表示字符串

　　col sal for 9999         设置数字宽度,sal是列,一个9表示一位数字的宽度

　　/　　　　　　　　　可以再执行一次上一次执行过的sql

　　c /form/from           修改sql语句,c是change , form是错误的关键字,from是正确的,

　　　　　　　　　　     然后用/来执行上一次执行过的sql(已经修改)

　　conn 账号/密码       登录账户

　　ed　　　　　　　　这个也是修改sql语句的,ed是edit的缩写,这是会弹出一个记事本

 　　

登录

　　1 在cmd里输入sqlplus 账号/密码 ,如果是内置的管理员账号sys,则在密码后面要加上as sysdba

　　2 如果已经在sqlplus里了,那么输入 conn 账号/密码   这里的conn代表着connect(连接)

　　   conn命令一般在cmd下使用,一般不在sql developer上用

 

查看当前用户的所有表,*是匹配所有数据,tab是一个数据字典(后面会讲)

select * from tab;    

 

如果显示出来的表不规则(看起来不舒服),可以输入以下命令

set linesize 100;    设置一行显示多少个字符

set pagesize 100;   设置一页多少行

 

如果不想每次登陆sql都设置,可以写到 登陆初始化的配置文件里

D:\app\1\product\12.1.0\dbhome_1\sqlplus\admin\glogin.sql  在这里写入上面2行

 

修改密码.以管理员权限登录后可修改指定用户密码,这个后面会具体说明,可以先用

alter user scott identified by 新密码;  

 

查看表内的所有数据

select * from 表;   　

select "test" from 表;     　　表内有多少数据,那么就会出现多少test,因为"test"已经相当于一个列了,不过列的名字叫tes,最后一个字符会省略

　　　　　　　　　　　 　　且这个tes列所有的数据都是test

 

查看指定列的数据　　　　　　　　　　　

select 列名1,列名2 form 表;     

 

四则运算表达式,比如月薪*12就代表年薪,但是oracle的四则运算中如果有一个是null,那么整个式子都为null,这时候就需要滤空函数了　　　　　　　  

select salary*12 from 表;

select salary *12+nvl(comm,0) from emp;             滤空函数nvl,如果comm为null,那么就替换成0

 

as起到别名的作用,写和不写没区别,但是建议写下, 别名可以不用加""(不加就不能有特殊字符),但是为了区别关键字,还是加下,或者用单引号　　 

select (salary*12) as "年薪" from 表;   　

　　　　 

加了distinct(不同的),查找出来的salary数据不会有重复,且distinct作用于后面所有指定了的列　　　　　　　　　　　　　　　　　　　  

select distinct salary ename form 表;　　　　　

 

过滤数据,关键字是where,用了where后oracle依然是查询了,只不过不显示出来了,

select * from 职工表 where 奖金列 is null;  　　　　查找职工里面没有奖金的人,null比较特殊,所以用is,而不是用=

select * from  职工表 where 部门列=10;                   查找10号部门的员工,!=代表不等于

select * from 职员工表 where 薪水列=5000;             这会把所有人的薪水都改成5000

 

日期操作,v$nls_parameters也是一种数据字典,用于获取系统当前的日期格式

select * from v$nls_parameters;    

select  svsdate from dual;              查看系统的时间

 

修改日期格式,如果m是mon,那么会显示中文的几月

alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'     

 

比较查询,一般求一个范围,日期也可比较,oracle在解析where的时候,会从右到左解析,所以用and的时候应该将易假的数值放在右侧(短路与)以提高效率

select * from emp where sal>=1000 and sal<=2000;      查询薪水在1000-2000的人

select * from emp where sal between 1000 and 2000;    查询薪水在1000-2000的人

select * from emp where dept=10 or dept=20;　　　　  查询10或者20号部门的人

select * from emp where in(10,20);　　　　　　 可以用in(集合中)代替上面的,比较方便,如果是not in ,那么就是集合之外的,但是not in中不能有null

 

模糊查询

select * from emp where ename like '张%';       查询表中姓张的人,%代表匹配任意多个字符, _ 匹配任意一个字符,改成%天%就是找出名字中带有天的人

select * from emp where ename like '%\_%';     查询表中名字包含下划线的人, \是转移字符,

 

排序,是对查询出来的结果进行排序,注意是已经出来的结果

select * from emp where sal>1000 order by sal;    查出薪水大于1000的人,并按薪水排序

select ename,sal from emp order by 2;                  查出的名字和薪水,并且按第二个我们写的列排序,我们第二个写的列是sal,是按我们写的,不是按表中的列

select * from emp order by sal,dept; 　　　　　 查看所有人,并且按照部门排序,排序了的部门再按薪水进行排序,具体看下图

先按部门排序,排完之后有重复的部门,那么重复的部门中便再按薪水排序

select * from emp order by sal,dept desc;                 查看所有人,并按部门从大到小排序,desc代表指定从大到小,且只作用dept,就近原则

select * from emp order by comm desc nulls last;     查看所有人,并按奖金从大到小排序, 后面的nulls last代表空值置后,因为如果不写nulls last,会成下左图

=

单行函数

　　

=

字符函数

lower转小写 upper转大写 initcap首字母大写

select lower("Hello,Word") as 转小写,upper('hello,woRld') as 转大写,initcap(hELLO) as 首字母大写 from emp;

=

concat 字符串连接   substr 截取字符串 lengthb 字节数(length是字符数) instr 是否有指定字符在母串中 lpad 左填充  rpad右填充  trim 去除首位  replace替换

 

select concat('123_','234') from emp;   

select substr('qwe123',3,2) from emp;  从第3位开始取(第三位是e),取2位,第三个参数不填表示一直取到最后

select lengthb('hello word') from emp;    计算hello word的长度

select instr('hello world','orl') from emp;   ord是否在hellowordd里面,找到返回下标(从1开始计数),没有则返回0

select lpad( 'abcd', 10, '*') 左 ,rpad('abcd',10,'#')from emp;      填充字符串,lpad是左填充,第二个参数是填充后的字符串,第三个参数是用什么填充

select trim('H' from 'Hello') from emp;    一般用这个去空格,

select replace('hello world,'h','*') from emp;     将指定的字符替换

select replace('hello world,'h','') from emp;      删除一个字符串,直接替换成空

=

数值函数

round 四舍五入 

round(45.926,2)  四舍五入到第二位,就是看第三位如何,如果>=5,就是45.93,如果第二个参数是负数,就是小数点前面的了

trunc 截断

trunc(45.926,2)   截断到2位

mod求余

mod(1600,300)  3*5=15,余下100

 

 

select sysdate-1 as 昨天,sysdate+1 as 明天 from empl;            时间可以直接和数字相加,但不能日期+日期,但却可以日期-日期得出天数

 

日期函数

months_between   2个日期相差的月数   add_months 在某个日期值上,加上多少的月,正数向后计算,负数向前计算

last_day   日期所在月的最后一天    next_day  指定日期的下一个日期   

select next_day(sysdatem,'星期一') from emp;   下个星期一是多少号

 

months_between可以精确计算2个日期之间相差多少天,上面的计算不精确,因为没有考虑有的月有28天和天31天

select months_between(sysdate,我们指定的日期) from emp;    

 

可以用round,trunc 对日期型进行四舍五入和截断

select round(sysdate,'month'),round(sysdate,'year') from emp;  如果取的月,那么就看日,取的年就看月,因为是精确到月和精确到年

 

上面的隐式转换,是将字符串隐式的转换到日期型

 

 

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day' ) from emp;   在固定的格式里加入自定义字符串

select to_date('上面输出的内容','yyyy-mm-dd hh24:mi:ss "今天是" day' )   第二个参数是按什么格式解析 

 

通用函数适用于任何数据类型,同时也适用于空值

nvl(表达式1,表达式2)     滤空函数,如果表达式1为空值(null),那么替换成参数2

nvl2(表达式1,表达式2,表达式3)  滤空函数升级版,如果表达式1为空值(null),那么替换成参数3,否则替换成参数2

nullif(表达式1,表达式2)     用来判断2个值是否相等,如果相等返回null,如果不相等会返回第一个参数

coalesce(表达式1,表达式n)    聚合函数  从左到右找,返回第一个不为空的数值

 

select sal*12+nvl2(comm,comm,0) from emp;     

select comm,mgr ,sal,coalesce(comm,mgr,sal) 结果值 from emp;   

 

条件表达式

=

select ename,job,sal as 涨前薪水 , case job when 'PRESIDENT' then sal+1000

　　　　　　　　　　　　　　　　　　　     when 'MANAGER' then sal+800

　　　　　　　　　　　　　　　　　　　　  else sal+400

　　　　　　　　　　　　            end 涨后薪水

from emp

 

 

 

=

 

 

=