oracle基本的增删改查、用户权限设置

一、常见字段类型

字符串：

        char 定长字符类型（未达到指定长度时，自动在末尾用空格补全）；默认值为1；最大2000字节；非unicode。
        varchar2 变长字符类型（未达到指定长度时，不自动补全空格）；定义时需指定长度；最大为4000字节；非unicode。

数字类型：

        number(p,s)类型，p表示整数的最大位数在1-38之间，s表示精确到小数后几位。s可省略

        float(b)，b在1-126位之间，浮点数类型，小数点精确多少位

日期类型：

        date 存储以下信息：世纪，年，月，日，时，分，秒。占用七个字节存储空间，每个部分占用一个字节。书写格式
（2024-6-12 11:23:01','yyyy-mm-dd hh24:mi:ss'）
（'2024612112301', 'yyyymmddhh24miss'）

二、增删改查

1、增

（1）创建用户/模式，用户授权（如果一开始没有建好测试的模式，可以先在命令行用系统管理员身份去创建该模式，如果在dbeaver里19c测试创建后权限有问题比较麻烦，这样创建完后自测没有问题，初学者可以照着搞，另外有一个文，是写中间碰到各种问题的可以参考坎坷的oracle安装学习-CSDN博客）

-- 创建c##scott用户

CREATE USER c##scott IDENTIFIED BY 123456 ;

-- 为用户授权

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO c##scott CONTAINER=ALL ;

-- 设置用户使用的表空间

ALTER USER c##scott DEFAULT TABLESPACE USERS;

ALTER USER c##scott TEMPORARY TABLESPACE TEMP;

（2）创建一个测试表test88

create table test88
(
  id       VARCHAR2(30),
  name     VARCHAR2(30),
  sex      NUMBER(1),
  birthday date
)

（3）插入测试数据

insert  into  表名   values(值,...);
例：
insert  into  test88   values(1,'张三',1,to_date('2024-6-12 11:23:01','yyyy-mm-dd hh24:mi:ss'));
insert   into  test88  (id,name,birthday)   values(2,'张四',to_date('2024-6-12 11:23:01','yyyy-mm-dd hh24:mi:ss'));

将a表的数据快速插入到b表中
INSERT  INTO avg_all  SELECT  SDEPT,AVG(SAGE)  FROM STUDENT   GROUP	BY	SDEPT;

2、删

（1）删除表中数据，表

###这些操作其实在生产环境一般都比较少用，谨慎操作，都应该是从其他数据库过来的吧。

删除某行数据
DELETE FROM 表名称 WHERE 列名称 = 值

例：
DELETE FROM test88 WHERE name = '张三';

删除所有数据（如果还有世间牵挂，千万谨慎使用，让熟悉业务的帮忙确认一下）
delete from 表名；

例：
delete from test88；

删除表(谨慎)
drop table 表名;

例：
drop  table  test88;

3、改

（1）修改表中数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例：
UPDATE  test88 SET  id = 3 WHERE birthday = to_date('2024-9-12 1:23:01','yyyy-mm-dd hh24:mi:ss');

UPDATE  test88 SET  id = 4,name='李三',sex=2 WHERE birthday = to_date('2024-9-12 1:23:01','yyyy-mm-dd hh24:mi:ss');

（2）增减字段

增：ALTER   TABLE  表名 ADD  字段名  字段类型;

例：
ALTER   TABLE  test88 ADD   num  DATE;


删：ALTER  TABLE	表名  DROP  column  列名称;

例：
ALTER  TABLE	STUDENT  DROP  column  S_ENTRANCE;

（3）修改字段名

ALTER TABLE 表名  RENAME COLUMN	原字段名  TO 新字段名;


例：
ALTER TABLE test88  RENAME COLUMN  num  TO numm;

（4）修改字段类型

ALTER TABLE 表名  modify 字段名 字段类型;

例：
ALTER TABLE test88  modify num INT;

（5）给字段增加约束条件

ALTER TABLE 表名 ADD 约束类型(字段名); 

例：给test88表的num字段加取唯一值的约束条件
ALTER TABLE test88 ADD UNIQUE(num); 

4、查

###常用查询条件

注：AND和 OR来连接多个查询条件 AND的优先级高于OR 可以用括号改变优先级

（1）基础查询

SELECT   字段名,...  FROM  表名  where  字段名= ;  

例：大写name，小写id进行显示
SELECT   UPPER(name),LOWER(id)  FROM   test88  WHERE id=2;

例：DISTINCT去重
SELECT  DISTINCT  ssex  FROM student;

例：泛匹配%（匹配所有字符）
SELECT * FROM test88 WHERE NAME LIKE '刘%'；


例：ORDER BY，可以按一个或多个属性列排序，升序：ASC;降序：DESC;缺省值为升序。
SELECT * FROM test88  ORDER BY num DESC , name ASC;

例：count(*)计算有多少行数据， group by 以哪列数据为目标进行分组。
SELECT  name,count(*)  FROM  test88 GROUP  BY name;

例：having，类似于where的用法，但是where是在分组之前，having是在分组后的基础上进一步筛选。
SELECT  name,count(*)  FROM  test88 GROUP  BY name having count(*)> 2;

（2）连接查询

        1）内连接查询

内连接,test88 别名t8，test99别名t9，当然也可以不用别名，这里省略了as
例1：自身连接
SELECT  t8.*,t9.* FROM test88 t8,test99  t9   WHERE t8.num=t9.num; 

例2：内连接
SELECT  t8.*,t9.* FROM test88 t8 inner join test99  t9   ON t8.num=t9.num; 

例1和例2效果一样

        2）外连接查询

1、左连接left，右连接right，会以相应侧表内的所有内容为主题去保留另外一侧相应的内容，outer可以省略
SELECT test88.name,sex,age,num,Grade  FROM test88 LEFT OUTER JOIN test99 ON 
 test88.name=test99.name;

2、三表查询：
SELECT  *  FROM  test88 t8  LEFT  JOIN  test99 t9  ON  t8.name =t9.name  LEFT JOIN test111  t1  ON t9.num =t1.num; 

        3）子查询,性能不如join查询

子查询和父查询不相关
SELECT  *  FROM test88 WHERE test88.num   IN (SELECT num FROM test99  );

子查询和父查询相关
SELECT name,num,sex  FROM test88 x WHERE Grade >=(SELECT AVG（Grade） FROM test99 y WHERE y.id=x.id);

（3）集合式查询

集合操作的种类
1、并操作UNION,union去重显示，union  all全部显示不去重
例：
SELECT   *  FROM test88 s WHERE s.age =21	
UNION 
SELECT   *  FROM test99 s2 WHERE s2.age=29
2、交操作INTERSECT
例：
SELECT   *  FROM test88 s WHERE s.AGE =19
INTERSECT
SELECT   *  FROM test99 s2 WHERE s2.id=201215122
3、差操作MINUS
例：
SELECT   *  FROM test88 s WHERE s.age =19
MINUS 
SELECT   *  FROM test99 s2 WHERE s2.id=201215122









####注意：集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同

（4）派生表的查询

例：
SELECT  name FROM STUDENT,(SELECT  sno FROM SC WHERE  cno=1) test	WHERE student.SNO =test.sno;


1、SELECT  name FROM STUDENT
#你的最终查询出现的列

2、(SELECT  sno FROM SC WHERE  cno=1) test
#引用了另外一张表，或者对表进行了改造，test是新表的名字。

3、WHERE student.SNO =test.sno;
#最终的查询条件。这里引用了新表里的字段作为条件与初始的表进行比较筛选，然后去生成1中所指明的列。

（5）补充

补充：括号（）里的会优先执行,and优先级高于or。

统计元组个数 COUNT(*)

统计一列中值的个数 COUNT([DISTINCT|ALL] )

计算一列值的总和（此列必须为数值型） SUM([DISTINCT|ALL] )

计算一列值的平均值（此列必须为数值型） AVG([DISTINCT|ALL] )

求一列中的最大值和最小值 MAX([DISTINCT|ALL] ) MIN([DISTINCT|ALL] )



使用ANY或ALL谓词时必须同时使用比较运算
语义为：
> ANY 大于子查询结果中的某个值 
> ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值 
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值 
>= ALL 大于等于子查询结果中的所有值

使用ANY或ALL谓词时必须同时使用比较运算
语义为（续）
<= ANY 小于等于子查询结果中的某个值 
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值



属性定义（或者域定义）中
• 有NOT NULL约束条件的不能取空值
• 加了UNIQUE限制的属性不能取空值
• 码属性不能取空值
• 空值与另一个值（包括另一个空值）的算术运算的结果为空值
• 空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。
• 有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑

三、用户相关权限设置

创建用户	
create user 用户名 identified by 密码;

解锁用户	
alter user 用户名 account unlock;

锁定用户	
alter user 用户名 account lock;

修改密码	
alter user 用户名 identified by 新密码;

修改管理员密码	
alter user sys identified by 新密码;

给用户授权	
grant 权限 to 用户名;

收回用户的权限	
revoke 权限 from 用户名;

删除用户	
drop user 用户名;

创建角色	
create role 角色名;

删除角色	
drop role 角色名;

把权限授予给角色	
grant create session to 角色名;

角色继承	
grant create table,角色名1 to 角色名2;

给用户分配空间配额	
alter user 用户名 quota unlimited on users;