Oracle数据库浅浅笔记

目录

一、数据库语言分类

1、 创建

2、修改表：alter table

3、插入

4、删除表：drop table 表名 --》整个表就不存在了

5、查询

6、修改操作   update

7、commit提交

二、主外键

三、操作符

1、算术操作符

2、连接操作符

3、函数

4、条件取值（判断列名是条件1，显示T1,条件2显示T2，否则显示F）

5、 排序

四、表连接  .... join  on  联结条件

一、数据库语言分类

DDL：数据库定义语言：create、drop

DML：数据库的操作语言：insert、update、delete

DQL：数据库的查询语言：select

DCL：数据库的控制语言：grant、revoke

1. 创建

①创建表：create table

  create table  表名(

     字段名   数据类型   [primary key],  --主键

     字段名2  数据类型  [约束],

     ......,

     字段名N  数据类型  [约束]

  );

②新创建表A复制表B的内容或个别列 as

--表不存在：create table 新表名  as select 列名...  from 原表    -- * 代表所有列

--create table  A表名 as select *  from  B表名;

③只需要复制表结构不需要数据（各个列名） where 条件不成立

--create table 新表名 as select 列名...  from 原表  where 1=2

④创建序列 模拟序列自动增长

create sequence  自定义序列名 start with 自定义序列号  increment by n;

/*

create sequence 自定义序列名  increment  by  n   start  with  n

[maxvalue n] [minvalue n]

[cycle|nocycle]

[cache  n|nocache] ;

increment  by  n  --表示序列每次增长的幅度;默认值为1.

START WITH n  --表示序列开始时的序列号。默认值为1.

maxvalue  n  --表示序列可以生成的最大值(升序).

minvalue  n  --表示序列可以生成的最小值(降序).

cycle  --表示序列到达最大值后，在重新开始生成序列,默认值为 nocycle。

cache  --允许更快的生成序列.

 在设计表的时候需要一个不需要明确意义的列来主键，这时可以使用序列

 序列用于生成唯一、连续的序号的对象； 序列可以升序也可以是降序 */

⑤--如何访问序列对象中生成序列编号

 --生成序号

 select 自定义序列名.nextval  from dual;  --借助daul

 --获取当前序列对象的序号

 select 自定义序列名.currval  from  dual;  --currval 就是当前序列的值

2、修改表：alter table

–添加字段、列

alter table 表名 add 字段名 数据类型

–修改字段的数据类型

alter table 表名 modify 字段名 新的数据类型

–修改字段的名字

alter table 表名 rename column 旧名 to 新名

–删除字段

–alter table 表名 drop column 要删除的字段名

--删除约束：  alter table 表名  drop constraint 约束名;

–重命名表名

–alter table 表名 rename to 新表名

--添加约束（在创建表外）

       --第一：主键约束：主键不能重复   唯一标识该列   

       -- alter  table  表名  add  constraint  主键约束名称   primary  key(列名1,列名2...);

       

       --第二：非空约束： alter  table 表名  modify 列名 not null;

  

       --第三：唯一约束：不能相同  unique

       -- alter table  表名  add constraint 唯一约束名 unique(列名1,列名2...);

--第四：要在表中将一个字段设置外键列

-- alter table 表名 add conatraint 外键约束名称 foreign key(列名) references 被引用表名(列名);

--第五：检查约束和默认约束

--例子：性别是男是女  默认男

     --我们需要在属性上添加---------------检查约束和默认约束

     --第①：检查约束语法：alter table 表名 add constraint 检查约束名 check(检查条件);

     --检查条件in、or、and、length

--alter table 表名 add constraint 自定义检查约束名 check(sex='男' or sex='女');

      --alter table 表名 add constraint 自定义检查约束名 check(sex in('男','女'));

--检查条件在n到m之间：......check(列名between n and m);

--alter table 表名 add constraint 自定义名 check(列名>=n  and 列名<=m);

--......check(  length(列名)=n );

--第②：默认约束语法：alter table 表名 modify 列名 default 值;

     alter table 表名 modify sex default  '男';

--添加约束（在创建表内）

    --第一：重新创建带-------行级约束--------的表

    create table 表名(

    cid number(4)  primary key ,  --班级编号 主键

    cname varchar2(20) unique not null ,  --班级名称  唯一，不能为空

    cyear number(4),  --入学年份

    clean number(1)   --学制

    );

    --第二：重建创建------带表级约束-----的表

    create table 表名(

    cid number(4) ,  --班级编号

    cname varchar2(20)  not null ,  --班级名称  不能为空

    constraint  pk_100_cid  primary key(cid),

    constraint  uq_100_cname  unique(cname)

    );

--一般创建表一步到位（表级约束）

--字段名  数据类型[(n)] constraint 自定义约束名 check(检查条件)

--和主键约束一样：字段名 数据类型 primary key

--禁用某个属性的约束： alter table 表名  disable constraint 约束名;

--启用：alter table 表名  enable constraint 约束名;

3、插入

①插入数据：insert into 表名 (字段名1,字段名2,....,字段名N) values(值1,值2,......,值N);

--插入值的顺序必须和建表时列的顺序一致;

②将表B中列名1,列名2 两列的值插入到表A中的列名1，列名2

--语法： insert into  表名A (列名1,列名2,.....,列名N)  select 来源表B中列名  from 来源表B

③同时向表中添加多行数据  dual 是 oracle中的一张临时表，默认一行一列；union 联合

insert into 表名

   select 列1的值,列2值,...列n的值  from daul union   --union 联合的意思 ，把他们拼在一起

   select 列1的值,列2值,...列n的值 from daul; --表示插入表，两行值。

④插入系统时间 insert into  表名 (birthday)  values(sysdate);

 插入指定时间  to_date('  ' , 'yyyy-mm-dd' )

insert into 表名(字段名)  values(  to_date('2000-09-09','yyyy-mm-dd')  );

4、删除表：drop table 表名 --》整个表就不存在了

删除数据   delete  from  表名  [where 删除条件]

5、查询

①查询表中数据select   字段名1,字段名2,....,字段名n  from 表名 where 条件;

--查询字符类型（char/varchar2）字段的长度

  select length(字段名)  from 表名;

--查看学生表的结构   user_tab_columns

select * from  user_tab_columns  where table_name ='表名';

②--alias  column name: 列重命名   只在本次查询中（本次sql语句里有效）有效，并没有改变表：

--用法：列名 空格 别名 或者 列名 as 别名

select 属性名 别名 from 表名;  或者：  select 属性名  as  别名 from 表名;  

③--alias table name : 表重命名

--用法  表名 取个别名c       访问字段：别名.字段名

 select  别名.*  from 表名  别名 ;   --表名 空格 别名

④--数据类型  

--number(n,m)  有效位 ，小数位数

字段名 number(有效位数)  --将小数点后面的数，四舍五入化为整数输出

字段名 number(n,m)      -- n  >= 小数点后有效位数m + 整数位数  否则报错

--字符类型

--char  定长度的   2000

--varchar2  可变长的字符串    4000字节

字段名  char(n)

字段名  varchar(n)  -- 允许最大n个字符

--日期类型 date

字段名 date

6、修改操作   update 

语法：update 表名  set  字段名=值[,字段名2=值2,....] [where 修改条件]

7、commit提交

--DDL 事务create、drop 会自动提交到永久表空间中

--DML  事务insert、update、delete  必须执行  commit； 手动提交   rollback; --可以反悔并恢复数据

--特别注意： 使用JAVA访问PL/SQL插入的数据时，在访问前PL/SQL必须执行commit，否则访问不到新修改或插入的结果

 --delete  删除

  delete * from 表名;  --删除所有记录   事务手动提交，需要commit； rollback; --反悔了 恢复数据

 --truncate  删除表中所有记录   事务自动提交  反悔不了。truncate table 表名;

二、主外键

  /*

    主键：

        *********非空唯一************

        特点：确保每一行数据的唯一性

        注意：一张表中只能有一个主键约束，但是主键约束可以由多个字段构成（组合主键）    

    外键：

        唯一涉及到表与表主键的关系的约束

        主表和从表

          * 删除主表前需要删除从表中相关的数据记录才可以删除

          * 不建议修改主表中主键列  如果需要修改 那么对应的从表中外键列也必须修改

     唯一性：

          保证字段值的唯一性

     检查约束：

          表中的值  更具有实际的意义

  */

三、操作符

/*

oracle中sql的操作符分为以下几类：

  算术操作符    +  -  *  /  针对数值操作

  比较操作符    =  >  <  <=  >=  !=和<>意义相同  between...and、 in、not in、 like、 is null

  逻辑操作符    and  or  not

  集合操作符    union

  连接操作符    ||    union、 union all、 intersect、minus

查询的语法：select <列名列表> from <表名>[,表名]... [where <查询条件>]

*/

1、算术操作符

①查询数据某些属性出现null时：可以使用------函数nvl(expr1,expr2)------来解决。

--若expr1为NULL，返回expr2；expr1不为NULL返回expr1；

-- select  列1,列2，nvl(列1名,自定义数值)  from 表名;  --nvl(列1名,自定义数值)会在查询结果中自成一列。

②+

--select  列1+数值或列2  别名  from 表名; --查询出表格中列1与列2相加的（别名）结果列。

③in 和 or 只能少量几个明确的值    not

where 属性列名  in(值1,值2...值n)   或者where  属性列名=值1  or 属性列名=值2 or...

where 属性列名 not  in(值1,值2...值n)  --不在这个范围的

④--模糊查询  like    _表示一个字符  %表示零个或多个

-- select  *  from 表名 where 属性名 like '张%';

-- ...where  属性名  like '张_'

-- ...where 列名1  is  null; --查询列名1为空的记录

2、连接操作符 

①--union 俩个表结合（去重）

select 列1,列2  from  表1  union  select  列x,列y   from  表2;

 --union  all（所有记录结合不去重）

select 列1,列2  from  表1   union  all  select  列x,列y   from  表2;

--union会自动剔除重复的信息（要求是所有的列的值都相同时才会剔除），而union all不会

②intersect 交集（获取这两个表中的相同的数据（交集））

 select  列1,列2   from 表1   intersect   select 列x,列y   from 表2;

③minus（第一个结果集减去第一个结果集与第二个结果集的交集）

select 列1,列2   from 表1   minus   select  列x,列y  from 表2;

--返回第一个查询语句中的所有数据，但是如果这些数据在第二个查询结果中出现，则这条数据在结果中不显示

④exists 存在 ， not exists不存在

 select  *  from 表名 表别名 where  exists(select查询条件语句);

 select  *  from 表名 表别名 where  not  exists(select查询条件语句);

⑤--any、all、some

select * from 表 where 列  <  all(select 列1  from  表); --找<查询条件里的max(列1)

select * from 表 where 列  <  any(select 列1  from  表); 相当于找<查询条件里的最小min(列1)；

select * from 表 where 列  <  some(select 列1  from  表); 一般用于=

⑥--连接操作符  ||    ----在一列里面显示两列的值

--例子：获取学生信息格式为学号_姓名

 select  sid||'_'||sname  学号_姓名  from student;

3、函数

①数值函数

--忽略小数部分floor(计算式子或列名)

 select  floor(列的计算操作)  别名  from 表;

--保留两位小数返回 trunc(计算式子,n) n是保留小数位数

 select  trunc(sal/30 ,  2)  别名  from 表;

--向上取整返回 ceil(列的操作)   >=该值的最小整数

select  ceil(列的操作)   别名  from 表;

--四舍五入后返回 round(列的操作)

select  round(列的操作)   别名  from 表;

②字符函数

--转换成小写字母显示  lower(列名)

--转换成大写字母显示  upper(列名)

--正好为n个字符      length(列名)=n;

--substr(列名,n1,n2) 从n1开始截取n2个，没有n2就接到末尾

--字符串替换  replace(列名, 被替换的值1 , 新值 )

③转换函数

--修改员工日期  to_date('具体日期','日期格式')  

如to_date('2022-04-29','yyyy-mm-dd')

--显示某列年份和月份     to_char(列名,'yyyy-mm')

④日期函数

--sysdate系统的今天

--last_day(列名)  --该列最后一天

--获取月份extract(month from 列名)

--求23天之后的日期

 select trunc(sysdate)+(interval '23' day) 日期 from dual;

⑤聚合函数   max(属性名) 最高   min(..)最低   avg(..)平均   sum(..)总和   count(..)个数

--distinct 过滤重复数据  select distinct 列名 from 表

4、条件取值（判断列名是条件1，显示T1,条件2显示T2，否则显示F）

①decode(列名,'条件1','T1','条件2','T2','F')

--例子：如果是男则显示M，如果是女，则显示F

--select decode(sex,'男','M','F') 性别 from 表;

②select case 列

           when '条件1'  then  'T1'

           when '条件2'  then  'T2'

           else 'F'  end [列别名]  from 表;

③分组group by ... having...

select ... where ... group by ... having 需要满足的条件 order by(排序，在分组后进行) 列 asc/desc

--group by 字段名    在select语句中只能出现分组字段名  或  聚合函数

1. 排序

--order by  列名1 [desc/asc], 列名2 [desc/asc]  --desc降序，asc升序

①row_number()    排序:   如果排序字段的值相等，序号也不中断  1、2、3、4、5、6、7

 select  *，row_number() over (order by 某列  desc/asc)  [别名]  from 表名;

②rank( )   具有相等值的行排位相同，序数随后跳跃 1、2、2、4、5、5、7

  Select  *, rank() over (order by 某列  desc/asc)  [别名]  from 表名;

③dense_rank( )  具有相等值的行排位相同，序号是连续的 1、2、2、3、4、5、5、6、7

  select  *, dense_rank( )  over (order by 某列  desc/asc)  [别名]  from 表名;

④伪序列 rownum的使用 

select  rownum,  表名.*  from 表  where  rownum<=数值;  --查询出数值个数据

四、表连接  .... join  on  联结条件 

/*

种类:

1.内联结(inner join): 仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行. 内联接消除与另一个表中任何不匹配的行.

2.外联结：

  外联接会返回from子句中提到的至少一个表的所有行，只要这些行符合任何where或having条件。

  左外联接 left join:左边表显示所有的行，右边表中没有的字段用null代替。   -----左表占主动地位，右表空也是有左表对应null

  右外联接 right join:右边表中所有的行, 左边表中没有的字段用null代替。

  完整外联结 full join:两表数据都返回，没有的地方用null代替。

3.交叉联结(cross join)左表中的每一行与右表中的每行都组合成。笛卡尔乘积=左表数据行*右表数据行

*/

例;

select *  from 表A  right  join  表B  on  A.列1 =B.列2 ；