Oracle SQL基本操作总结（知识点）

SQL基本操作

一、 创建用户

以管理员身份登录：sys system 密码root
Create user 用户名 identified by 密码
界面操作：在其它用户上击右键，创建用户后，要先关闭，再编辑。
修改用户：alter user 用户名 identified by 新密码
删除用户：drop user 用户名
界面操作：在用户名上击右键，选择“编辑”或“删除”

创建用户的页面操作：
管理员下==》其他用户==>创建用户==〉用户名前面一定要加C##》应用〉关闭对话框


在其他用户下找到刚才创建的用户

二、 给用户授权或角色

1. 直接授角色：connect resource

Grant connect, resource to 用户名

2. 授系统权限和对象权限

系统权限主要包括create\alter\drop命令。对象权限主要指增删改查操作。系统权限由管理员授权，对象权限由对象自己授权。
权限名称就是操作命令。如创建表的权限，就是create table
Grant 系统权限 to 用户名
Grant 对象权限 on 对象 to 用户名

对象权限是不能通过页面操作的，只能通过命令来操作。

connect------连接数据库的权限
resource------------创建表空间等权限。
grant connect,resource to 用户名

对象权限由对象本身来授权。


如：在scott下：
grant select on emp to c##rose;

3. 角色的创建和管理（角色是权限的集合）

角色的创建和管理使用管理员身份

角色的创建和管理使用管理员身份。
（1）创建角色Create role 角色名 例：create role c##cad
(2) 给角色授权 grant 权限 to 角色 例：grant create table to c##cad
(3) 将角色授给用户 grant 角色名 to 用户名 例：grant c##cad to c##rr

提醒：用户被授予了角色后，需要重新连接数据库，才能获得此角色

删除角色：drop role 角色名

4. 回收权限

  Revoke  权限  from 用户（角色）

三、 创建表空间

Create tablespace 表空间名 datafile ‘d:\u1.dbf’ size 20m

四、 创建和管理表：

创建表、修改表推荐页面的方式。

Create table 表名（字段1 数据类型……）表空间表
界面操作：左侧列表中选择表，击右键。
修改表：界面操作（添加、删除、改名和改类型）
Alter table 表名 add 列名 数据类型
Alter table 表名 Rename column列名 to 新名
Alter table 表名 Modify 列名 新数据类型
Alter table 表名 Drop column 列名

复制表：create table 新表名 as select * from 已存在的表名 where 1=2
加上条件，只复制结构；没有条件表结构和记录一起复制

页面上创建表，char类型的默认值要加上一对单引号

create table aa as select * from emp;
这样创建表约束是不会复制进来的

五、 创建约束

主键：primary key
非空not null
唯一unique
检查check （表达式）
外键：references 表名
以上内容可以在建表时，直接加在列名后面
Create table tt(tid number primary key,tname varchar2(10) constraint nn not null,cj number(4,1) check (cj<=100));
提醒：检查表达式必须加括号
在各项约束前，可以加上constraint 约束名。为约束建立名称

修改表时创建约束
Alter table 表名 add constraint 约束名 约束类型（字段名称）(非空约束不能使用add.要使用modify)
Alter table tt add constraint uk_tt unique ( tname );

删除约束
Alter table 表名 drop constraint约束名

界面操作：同创建表

六、 表数据操作（增删改）

Insert into 表（字段名列表）values(数据列表);

Insert into 表 select语句 –注意：此命令必须保证两个表的字段数目和类型、顺序一致

Delete from emp where子句

Update emp set 字段名＝值 where子句

七、 表查询

简单查询：

Select子句：
字段别名 as可以省略

去掉重复值 distinct

数值运算 sal+comm

各种函数

例1：计算每个员工的年薪（包括奖金）

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

例2：查询共有多少个工种

select count(distinct job) from emp;  

where子句

Between and

In

Like % _

escape


当名字中含有%号的时候，要使用escape转义符

insert into dept values(50,'a%aa','weihai');--正常插入，插入的时候可以插入%
--找dept表中名字包含%号的记录:
select * from dept where dname like '%\%%' escape '\';--查找的时候查找含有%字符的那就必须使用escape转义符啦。

And or not

空值查询 is null

例1：查询工资在2000到3000之间的员工信息

select * from emp where sal between 2000 and 3000;

例2：查询姓名的第二个字符是A的员工信息

select ename from emp where ename like '_A%';

排序：
order by desc降序
可以多字段排序，当第一个排序字段值相同时，按第二个字段排序
可以使用别名排序

分组：
group by having
select子句中除了统计函数（sum avg max min count）外，只能出现分组字段
where子句中不能有统计函数，但having子句可以有。

例1：查询平均工资最高的部门编号及平均工资

select deptno,max(avg(sal)) from emp group by deptno;❌

上面为错误做法，❌❌❌，函数的嵌套的select后面的字段除了函数嵌套字段不能写其他字段

select max(avg(sal)) from emp group by deptno;✅

select deptno,avg(sal) from emp group by deptno having avg(sal)=(select max(avg(sal)) from emp group by deptno);✅

例2：查询同年雇佣的员工人数

select extract(year from hiredate),count(*) from emp group by extract(year from hiredate);

连接查询: 有相关性的两个表连接才有意义。

语法：表1 join 表2 on 表1…字段＝表2…相同字段 内连接
表1 left join 表2 on 表1…字段＝表2…相同字段 左外连接
表1 right join 表2 on 表1…字段＝表2…相同字段 右外连接
表1 full join 表2 on 表1…字段＝表2…相同字段 内连接

表1 join 表2 on 表1…字段＝表2…相同字段 join 表3 on 表.1(2).字段＝表3.相同字段
也可以使用where语句表示连接条件。

自连接：同一张表的两个字段具有层次关系。 自连接必须给表起别名
表1 别名1 join 表1 别名2 on 别名1.字段＝别名2.字段

子查询:
通常是在where子句中包含一条select语句

单行子查询：
表示where子句包含的select语句，查询结果只有一条记录
使用到的符号为 where 字段名＝ > < !=

例1：查询和SMITH同一个工种的员工信息

select * from emp where job=(select job from emp where ename='SMITH');

多行子查询：
表示where子句包含的select语句，查询结果含有多条记录
使用到的符号为where 字段名 in 、 >all、 >any 、<all、 <any

例1：查询不是领导的员工

select empno,ename from emp where empno not in(select mgr from emp);❌

如果是空值的话，not in永远为假

a not in(20,10,null) =====> a<>10 and a<>20 and a<>null
a in(20,10,null) =====> a=10 or a=20 or a=null;


所以使用not in是错的，因为mgr中有一个人的mgr是空值。

select empno,ename from emp where empno not in(select mgr from emp where mgr is not null);✅

关联子查询：子查询的执行需要外查询提供值，外查询的执行依赖于子查询。子查询会被多次执行。

例1：查询比同部门的平均工资高的员工信息

select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);

伪列：rownum和rowid
rowid表示记录的地址，即使是重复的记录，也有不同的值
rownum表示结果集中记录的累加
查询的思路：
第一， 先确定表，通过结果判断
第二， 确定条件，如果有各个、每个这样的内容，需要分组。
第三， 确定结果字段

八、 创建和管理视图(视图操作的时候不推荐使用页面的方式)

视图的本质是一条查询语句，视图是一个虚表

Create or replace view 视图名 as select语句

提醒：创建视图需要权限。

关键点在select语句。如果包含了计算，或多张表，就属于复杂视图。这时视图不能随意修改。

可以定义自己的字段名称。

删除视图：drop view 视图名

九、 创建和管理索引(创建索引的时候也不推荐使用页面的方式)

为提高查询速度而建立，当表有主键或唯一约束时，系统会自动在该字段上建立索引

Create index 索引名 on 表名（字段）

重命名索引：alter index索引名 rename to 新索引名
删除索引：drop index 索引名
提醒：删除自动索引，需要删除约束。

删除自动索引不能使用 drop index 索引名.，通过删除约束就可以

十、 创建和管理序列(创建序列的方式推荐使用页面的方式)

界面操作更方便

Create sequence 序列名 start with  
                      Increment by
                      Maxvalue      minvalue
					  Cycle  nocycle
					  Nocache  cache 20

序列的应用：nextval、 currval

可以在创建表时使用序列值，也可以在插入记录时使用序列值

create table t_test01(id number default seq_1.nextval,name varchar2(50));
insert into xs values(myseq.nextval,'aba');

建表时创建序列：generated by default as identity自动增长约束。

Create table 表名(字段名1 数据类型 generated by default as identity,字段名2 数据类型……)

可以实现字段值的自动生成，默认情况下，初始值为1，每新增一条记录，字段值自动加1。

一个数据表只能有一个字段使用自增约束，且该字段必须为主键的一部分。

修改序列：alter sequence 所有要修改的内容，除了初始值

删除序列：drop sequence 序列名

十一、同义词

简化对表或其他对象的访问

创建同义词也需要权限

界面操作更方便
Create [pulbic] synonym 名称 for 方案.对象名
删除同义词：drop synonym 名称

十二、事务操作

事务开启：dml操作

结束：隐式结束和显示结束

隐式结束：做了ddl操作或权限操作

显示结束：
Commit、savepoint 名称、rollback [<to 名称>]

十三、数据字典

主要用于查看信息
分为三类：dba_ user_ all_

常用
User_objects
User_tables user_views
User_对象名称复数

十四、函数

数学函数：mod() round()
字符串函数：lower() upper() initcap() initcap() substr() instr () replace()
日期函数：add_months months_between() extract() next_day() last_day()
转换函数：to_char to_date to_number ascii cast()
空值函数：nvl nvl2

注意：在PL/SQL语句块中没有nvl2这个函数哦

十五、cdb转pdb

进入并打开pdb

Alter session set container=pdborcl;
Alter pluggable database pdborcl open;

退出并关闭pdb

Alter pluggable database pdborcl close [immediate];
Alter session set container=cdb$root;

在sqlplus中连接，需要加上数据库名。例：conn hr/hr@pdborcl
在sqldeveloper中连接，其模式为tns，网络别名为pdborcl

十六、创建hr用户的连接：

1、创建一个超级管理员用户（sys as sysdba）

2、在超级管理员下：

Alter session set container=pdborcl;
Alter pluggable database pdborcl open;

3、在HR用户下只做查询的话：

select * from hr.jobs;

4、如果要求连接hr的话：

先改监听文件：
目录：C:\app\product\12.1.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\12.1.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

5、打开上述文件之后：

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
pdbORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

将内容改成上面的内容

6、在超级管理员下：

alter user hr account unlock;
alter user hr  identified by hr;