oracle 基本的sql总结

╰半夏微凉° 2022-05-27 12:59 183阅读 0赞

目标

\--1、基本的sql语句  
\--2、oracle的单值、分组函数

\--3、oracle的多表查询、集合查询

\--1.1 基本的sql分类

\--数据库定义语言（DDL）  
  --create alter drop  
\--数据库操作语言(DML)  
  --insert delete update select  
\--事务控制语言（TCL）  
  --commit savepoint rollback    
\--数据库控制语言(DCL)

--grant revoke

\--1.2 oracle的基本数据类型

\--字符 数值 日期 RAW/LONG RAW LOB  
\--1.2.1字符  
\--char不可变长度  varchar2可变长度   long大2G  
\--1.2.2数值  
\--number(3,2)    后两位是小数部分精度  第一个是整数部分     
\--1.2.3日期类型  
\--Date  日期和时间部分  精确到秒  
\--timestamp  存储日期时间时区  精确到秒的小数点后6位  
    
  -- select to\_char(sysdate,'yyyy-MM-dd hh24:mi:ss')from dual;  
  -- select to\_char(systimestamp,'yyyy-MM-dd hh24:mi:ssxff6')from dual;

1.2.4 RAW/LONG  
 RAW数据类型用于存储二进制数据 2000字节

Long RAW数据可以存储2G

1.2.5LOB  
 --clob 字符型的lob   
 --blob 二进制的lob 如图形、音频、视频

--bfile 二进制文件 将二进制数据储存在数据库的外部的操作系统文件中 数据库保存指针

1.2.6  rowid（快速查询）  rownum(分页)

1.3 oracle基本查询

\--create table stu(id int ,age int  );  
  --添加字段  
  --alter table stu add (name\_C varchar2(255));  
  --修改字段长度  
  --alter table stu modify(name\_C varchar2(256));  
  --修改字段的类型/或名称 --别这样干  
  -- alter table stu modify(name\_C char(20));  
  --修改字段的名称  
    --alter table stu rename column  name\_C to name\_T;  
  --删除字段  
  --alter table stu drop column name\_T;  
  --查询表结构  
  -- desc  stu;  
  --修改表的名称  
  -- rename stu to student;  
  --删除表  
  --drop table student;  
  
  
  --创建新表  
  --create table tercher(id int ,name varchar2(255),comingDate date);  
    
  --所有的字段都插入(date 默认是dd-mon-yy)  
  --insert into tercher values(1,'evio','01-5月-18');  
  --插入部分  
  --insert into tercher values(2,null,TO\_DATE('19960413','yyyyMMdd'));  
  --insert into tercher(id,comingDate) values(3,TO\_DATE('1993-09-13','yyyy-MM-dd'));  
  --select \* from tercher;  
  --查询姓名为空的学生  
  -- select \* from TERCHER where name is null;--错误写法 name =null name=''  
  --用一个表创建一个新表 包含数 据  
 -- create table TERCHER2 as select \* from TERCHER;  
\--用一个表创建一个新表 不包含数据  
   create TABLE tercher4 as SELECT \* from TERCHER where 1>2;  
\--truncate的用法  
   truncate table tercher;--1 删除不用commit  2 不进入日志（truncate比较危险）  
     
\--把一个表中的若干数据添加到另一个表  
  insert into tercher select \*from tercher2;  
\--更新表的数据  
  update tercher set name='evior' where id=2;   
\--删除表的数据  
  delete from tercher where id=3;  
\--查询数据总数  
  select count(\*) from tercher;   
\--插入数据是包含  ' 用''  
  update tercher set name='evio''r' where id=2;  
   select \*from tercher;  
 --模糊匹配  
 select \* from tercher where name like 'e%';  
 select \* from tercher where name like 'evi\_';  
 --length()   
 select \* from tercher where length(name)>4;  
 --排序  
 select \* from tercher order by COMINGDATE;  
 --别名 as 可省略 "会添加空格  
 select id as "编号", name as "名称" from tercher;  
   
   
 create table cj(sno int,sname varchar2(255),score int);  
   
select \* from cj;  
  
  
\--每个学生的总分  id分组  
select sno, sum(score) from cj GROUP by sno;  
  
  
\--每门课程的平均成绩  
select sname, AVG(score) from cj group by sname;  
   
\--查询平均成绩大于60的学生  
select sno,avg(score) from cj group by sno having (avg(score)>60);  
  
  
\--查询 平均成绩 大于 所有学生的 平均成绩  的 学生的 学号和平均成绩  
select sno,avg(score) from cj group by sno having (avg(score)>(select avg(SCORE) from cj));

\--1.4 sql 的操作符  
\--||

select '学号是:'||sno||'成绩:'||score from cj;

\--1.5 oracle函数  
\--1.5.1 单行函数  
    
     --首字母大写

select initcap('hello') from dual;

--全部大写

select upper('hello') from dual;

--全部小写

select lower('hello') from dual;

--求长度

select length('hello') from dual;

--字符串截取 substr 从第2位置开始 截取1个

select substr('hello',2,1) from  dual;

--replace l -> x  
     select replace('hello','l','x') from dual;  
     --concat

select concat('hello','world') from dual;

--字符函数  
     --chr 得到整数的ascii  ascii->char  
     select chr(100) from dual;

select ascii('B') from dual;

--填充 lpad 增长到10位 不够用 'x' 填充  
     select lpad('he',10,'x') FROM dual;--前填充

select rpad('he',10,'x') FROM dual;--后填充

--日期时间函数  
     --add\_months  
     select add\_months(sysdate,5) from dual;

select months\_between(sysdate,to\_date('2015-09-13','yyyy-mi-dd')) from dual;

--extract 提取  
     select extract(month from sysdate) from dual;  
     select extract(year from sysdate) from dual;

select extract(day from sysdate) from dual;

--所在月的最后一天  
     select last\_day(sysdate) from dual;  
       
     --eg:  
     -- select \* from student where add\_months(birthday,27\*12)<sysdate;  
     -- select floor(sysdate-birdate) from student;--floor 取整  
       
   --数字函数  
     --abs() 取绝对值   ceil()向上取整  round() 四舍五入 trunc() 直接截断  
   --转换函数  
     --to\_char() to\_number  ()  
   --混合函数  
     --nvl() nvl2()  
   --分组函数

--min  max avg sum count

\--1.6 oracle的连接方式

\--创建表  
create table stu(sid int ,sname varchar2(255));  
insert into stu values(1,'evio');  
insert into stu values(2,'evior');  
insert into stu values(3,'yan');  
insert into stu values(4,'yanwenpin');  
  
  
  
  
create table address(aid int ,adress varchar(256));  
insert into address values(1,'湖北');  
insert into address values(2,'江西');  
insert into address values(3,'湖南');

insert into address values(5,'广东');

\--等值连接   
select s.SID,s.SNAME,a.ADRESS,a.AID  
from stu s,address a  
where s.SID=a.AID;

![70][]

select s.SID,s.sname,a.ADRESS,a.aid   
from stu s  
inner join address a

on a.aid=s.sid;

![70 1][]

\--左外连接  
select s.sid,s.sname,a.ADRESS,a.AID  
from stu s,address a

where s.SID=a.AID(+);

![70 2][]

select s.SID,s.SNAME,a.ADRESS,a.aid  
from stu s  
LEFT OUTER join address a  
on s.SID=a.AID;  
![70 3][]  
  
\--右外连接  
select s.SID,s.SNAME,a.AID,a.ADRESS  
from stu s,address a  
where s.SID(+)=a.AID;  
![70 4][]  
  
select s.sid,s.sname,a.aid,a.ADRESS   
from stu s  
right outer join address a  
on s.sid=a.aid;   
![70 5][]  
  
  
  
\--全连接  
select s.SID,s.SNAME,a.AID,a.ADRESS  
from stu s  
full outer join address a  
on s.sid=a.AID;

![70 6][]

\--集合的操作符  
\--union 并集 相加   
select \* from stu where sid=2  
UNION   
select \* from stu where sid=1;

![70 7][]

\--union 并集显示重复  
select \* from stu  
union all

select \* from stu where sid=1;

![70 8][]

\--minus 差集 相减  
select \* from stu  
minus

select \* from stu where sid=1;

![70 9][]

\--intersect 交集  
select \* from stu  
intersect

select \* from stu where sid=1;

![70 10][]

[70]: /images/20220527/031c7948fee64118bc8b5158b5a86744.png
[70 1]: /images/20220527/60cccc48349143328b2b5c08d0794427.png
[70 2]: /images/20220527/bb41089ea76a4713ba86e83fd90501bd.png
[70 3]: /images/20220527/5e474d4e67cb44f5905aeec327337a4d.png
[70 4]: /images/20220527/5ecd469b53ab419980bf03a09e652001.png
[70 5]: /images/20220527/7804d622658c46d9874b150e50117c85.png
[70 6]: /images/20220527/82d62ca07e7d41f3a46efeff317d8bc9.png
[70 7]: /images/20220527/41efc4e8b3ad406abb98c4dd8d03957f.png
[70 8]: /images/20220527/23dea0d1f12c45a28242e4c049d4680e.png
[70 9]: /images/20220527/acc86a95ed964a908183e18b014fad8a.png
[70 10]: /images/20220527/795eb9aee7aa441fb33b530f6a1e09ea.png