Oracle day02

Oracle的数据类型:

字符型:
varchar2(20)        可变类型        0-4000字节

char(2)                固定类型        0-2000字节

数值型:
number(5)

number(5,1)

日期型:

date

timestamp        包含毫秒数

创建表:

create table student{

id number(5),

name varchar2(20),

birthday date,

sal number(5,1)

};

建表以后,修改表名:

alter table student rename to stu;

建表以后,新增字段:

alter table student add email varchar2(20);

建表以后,修改列名:

alter table student rename column email to youxiang;

建表以后,修改数据类型:

alter table student modify youxiang varchar2(30);

建表以后,删除字段:
alter table student modify drop column youxiang;

建表以后,删除表:

drop table student2;        12(表结构,表内数据)

建表以后,截断表:

truncate table student;     23(表数据,表空间)

1.表结构        2.表数据        3.表空间

查看表结构:
desc student;

查看当前用户下有哪些表:
select table_name from user_tables;

增删改查:

新增数据:insert

insert into student values(1,'zs',sysdate,5000);

insert into student (id,name,sal) values(2,'ls',6000);

删除数据:delete

update student set sal = sal + 500;

update student set sal = sal + 500 where id = 2;

update student set sal = sal + 500,name = 'zsf' where iid = 1;

查询数据:select

select * from student;

select name,sal from student;

select name,sal from student where name = 'zs';

select name,sal from student where name = 'zs' and sal = 5000;

别名:

select name,sal * 1.3 as sal from student where name = 'zsf' or name = 'ls';

select name,sal * 1.3 sal from student where name = 'zsf' or name = 'ls';

select s.name,s.sal from student s;

like:模糊查询

%:代表任意位的任意字符

_:代表一位上的任意字符

select name from student where name like 'ET%';

select name from student where name like 'ET_';

select name from student where name like '%s%';

not like:

select name from student where name not like '%s%';

逃离符:escape

通过指定一个字符位进行逃离,来保证like之后的特殊字符看作是普通字符

select name from student where name like 'ET,_%' escape',';

select name from student where name like 'ET._.%' escape'.';

计算:

+ - * /

比较:

>        <         >=        <=        !=        <>不等于

条件:

and                并且        两个条件必须都成立

or                   或者        有一个条件成立即可

between        and        闭合区间

select name,sal from student where sal between 5000 and 5500;

select name,sal from student where sal >= 5000 and sal <=5500;

is null:是空

is not null:非空

select * from student where birthday is null;

函数:
*:通过函数修饰的列一定要起别名

dual:假表,伪表,测试表

聚组函数:聚簇函数:多行函数:组函数                       (和分组一起使用 : group by)

max()求最大值        min()求最小值        avg()求平均值        sum()求和        count()

求记录数

select count(*) num from student;

select count(birthday) num from student;

select count(0) num from student; count(0)所有非空的行数

单行函数:

ceil():向上取整

floor():向下取整

select ceil(12.3) num from dual;

abs():求绝对值

select abs(-666) num from dual;

sign():求符号位        正数返回1,负数返回-1,零返回0

select sign(-666) num from dual;

power(a,b):求a的b次方

select power(2,3) num from dual;

sqrt():求正平方根

select sqrt(16) num from dual;

round():求四舍五入

select round(3.141592654) num from dual;

select round(3.141592654,3) num from dual;

trunc():求直接截断

select trunc(3.141592654) num from dual;

select trunc(3.141592654,3) num from dual;

字符函数:

upper():转换成大写

lower():转换成小写

initcap():首字母大写

length():求长度

select name,upper(name) uname,lower(name) lname,initcap(name) iname, length(name) lenname from student;

substr(a1,a2,a3):截取字符串

a1:原字符串

a2:从哪开始截取

a3:截取长度

select substr('woshizhizhuxia',3) str from dual;

select substr('woshizhizhuxia',3,9)str from dual;

replace(a1,a2,a3):替换字符串

a1:原字符串

a2:要替换的字符

a3:替换成的字符

select replace('woshizhizhuxia','h') str from dual;

select replace(name,'s','000') name from student;

instr(a1,a2,a3,a4):索引字符串

a1:原字符串

a2:想要找到的字符

a3:从哪开始找

a4:第几次出现

select instr('woshizhizhuxia','h') str from dual;

select instr('woshizhizhuxia','h',5) str from dual;

select instr('woshizhizhuxia','h',5,2) str from dual;

concat(a1,a2):拼接字符串

select concat(name,sal) str from student;

select concat(concat(name,birthday),sal) str from student;

||:拼接

select '123' || '456'  || '789' str from dual;

转换函数:

to_number():将一个字符类型的数值转换成数值类型

select * from student where to_number(phone) = 123;

to_char():

1.将数值类型转换成字符类型

select * from student where to_char(sal) = '5000';

2.将日期类型转换成字符类型

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff3') time from dual;

3.格式化字符串,常用在货币单位

select to_char('10000000','999,9999,999,999.99') money from dual;

to_date():将一个字符类型的日期转换为日期类型

select to_date('19961212111111','yyyy-mm-dd hh24:mi:ss' )time from dual;

日期函数:

两个日期之间可以相减,单位是天

sqlplus中修改日期展示格式:

一次性修改方法:
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

永久修改方法:详见环境变量

yyyy        年        year

mm        月        month        带'月'的月份

ddd        日        年中的日

dd          日        月中的日

d            日        周中的日

hh24      24小时制

hh12     12小时制

mi        分

ss        秒

xff        毫秒

ff3        毫秒保留sanwei

add_months():在某个日期上添加多少个月

select add_mouths(sysdate,3) time from dual;

months_between():两个日期之间存在多少个月

select months_between(sysdate,to_date('20230131','yyyy-mm-dd'))time from dual;

next_day():下一个周几是哪天

select next_day(sysdate,'星期六') time from dual;

last_day():给定日期所在月份的最后一天

select last_day(sysdate) time from emp;

通用函数:

nvl(原字符串,是空展示什么):空值处理

select birthday,nvl(birthday,sysdate) time from student;

nul2(原字符串,不是空展示什么,是空展示什么):空值处理二代

select birthday,nvl2(birthday,birthday,sysdate) time from student;

decode(c1,c2,c3,c4,c5...Cx,Cx+1):

c1是原字符串,从c1开始每两个参数看作是一组,拿每组的第一个参数和c1进行比较,如果相同则返回该组的第二个参数

相当于:
第一次比较:c2 == c1 ? c3 :

第二次比较:c4 == c1 ? c5 :

如果参数个数是奇数个,并且最终判断没有相同的值,则返回空

如果参数个数是偶数个,并且最终判断没有相同的值,则返回最后一个参数的值

*:如果部门编号是10,则工资涨二百,如果部门编号是20,则工资减二百,其他部门加一百

select ename,deptno,sal,decode(deptno,10,sal + 200,20,sal - 200,sal +100) salary from emp;

*:如果部门编号是10,则工资涨二百,如果部门编号是20,则工资减二百

select ename,deptno,sal,decode(deptno,10,sal + 200,20,sal-200) salary from emp;

条件取值语句:

(case        -- 拿来作比较的值

when        -- 如果..

then        -- 则..

else        --否则..

end)       --结束

*:如果部门编号是10,则工资涨二百,如果部门编号是20,则工资减二百,其他部门加一百

select ename,deptno,sal,(case deptno when 10 then sal + 200 when 20 then sal - 200 else sal + 100 end) salary from emp;

如果部门编号是10,则工资涨二百,如果部门编号是20,则工资减二百

select ename,deptno,sal,(case deptno when 10 then sal + 200 when 20 then sal - 200 end)salary from emp;

分组:group by

在一张表中,将某列或者多个列上相同的值划分为一个组,那么这张表就被分为多个组

*:如果根据字段A分组那么只能查询字段A,其他字段需要以组函数的形式出现

select deptno from emp group by deptno;

select deptno,job from emp group by deptno,job;

select deptno,count(empno) num from emp group by deptno;

条件:having

select feptno,count(empno) num from emp group by deptno having count(empno) >=5;

分组:聚合统计

去重:distinct

*:支持单列多列的去重

select distinct deptno from emp;

select distinct deptno,job from emp;

select count(distinct deptno) num from emp;

排序:order by

升序:asc        降序:desc        默认升序

select deptno from emp order by deptno desc;

select deptno,sal from emp order by deptno desc,sal asc;

select deptno,sal from emp order by deptno desc,sal;

查询关键字的优先级:

from        表名        --        优先级最高

where        条件        --        优先级次高

group by        分组        --        优先级在where之后

having        条件        --        优先级在group by之后

select        列名        --        优先级高于order by

order by        排序        --        优先级最低

 

select deptno,count(deptno) num

from emp

where deptno = 10

group by deptno

having count(deptno) >=2       

order by num desc;