Oracle

最新推荐文章于 2023-10-24 17:25:47 发布

闻舟已渡

最新推荐文章于 2023-10-24 17:25:47 发布

阅读量196

点赞数

分类专栏：课程笔记

本文链接：https://blog.csdn.net/qq_43759694/article/details/115519234

版权

课程笔记专栏收录该内容

12 篇文章 1 订阅

订阅专栏

基本命令

clear screen 清屏
@ d:/1.sql 执行外部文件
dbms_output.put_line(v_name); 打印输出
如果无效：set serveroutput on size 100000; / set server put on

用户/数据库

用户

创建用户
create user username identified by passwordInfo;

修改密码
alter user 用户名 identifid by 密码;

查看当前数据库连接用户
Show user

链接用户
conn username/userpwd

编辑用户状态
alter user hr account unlock

赋予用户权限
grant privilege [,privilege] to user [user,user | role, PUBLIC];
	   【create session  	创建会话
		create table		创建表
		create sequence		创建序列
		create view			创建视图
		create procedure	创建过程 】

创建用户表空间
alter user [用户名 | username] quota [表空间大小 5M| unlimited]
on [表空间 | user]

查看所有用户
select username,account_status from dba_users order by username;

权限

角色

1.创建角色
create role manager ;
2.赋予角色权限
GRANT create table,create view To manager
3.赋予用户角色
GRANT manager TO USer1， User2

分配对象权限
grant [select,update,...] on tablename to user1,user2;`

分配对象权限的权限
....
with grant option

public关键字
某个表权限给所有人
to public

收回对象权限
revoke [权限] on tablename from [username|rolename]

数据库


查看表结构：
desc all_tables;

查看当前库的所有数据表 
select TABLE_NAME from all_tables;

查询数据库名：
select name,dbid from v$database;

查看当前数据库： 
select name from v$database;

查询SQL

创建表
SQL> CREATE TABLE USERS(
  2  ID NUMBER(8) PRIMARY KEY,
  3  NAME VARCHAR(10),
  4  SEX CHAR(2),
  5  ADDR VARCHAR2(20),
  6  BIRTHDAY DATE,
  7  SALARY NUMBER(8,2)
  8  );

表已创建。

删除表
DROP TABLE xxx;

查询表结构（只能在命令行模式下）
DESC XXXTABLE

查询表数据
SELECT * FROM XXXTABLE;

SQL语句分类

DML语句
数据操作语句增删该查
DDL语句
数据定义语句 create /Alter /Drop /Truncate
DCL语句
Grant /Revoke
事务控制语句
Commit /Rollback /Savepoint

SQL



从表中查询部分字段
SELECT [* | column1 ，column2] FROM [table];
在查询过程中 对数值型可以执行加减乘除等运算


起别名
SELECT NAME AS "姓名" FROM [TABLE];
NAME 姓名     	NAME "姓名"


合并多行查询
SELECT NAME||SALARY FROM [TABLE];
SELECT NAME||'的工资是'||SALARY AS "工资" FROM [TABLE];
 
日期和文本型字符串必须用单引号
日期型数据也可以使用加减数值型 得到新的日期数据
日期型数据可以相减得到差


去除单列重复的数据
SELECT DISTINCT [column] FROM [TABLE];
去除多列重复数据
SELECT DISTINCT [column],[column] FROM [TABLE];


使用where对数据进行过滤
SELECT *
FROM [TABLE]
WHERE ID=1001;


查询字符型数据必须用单引号 对大小写敏感
SELECT *
FROM [TABLE]
WHERE NAME='张三';


使用日期型数据对格式敏感
SELECT *
FROM [TABLE]
WHERE DATE='20-2-1999';

查询默认日期格式
SELECT SYSDATE FROM DUAL;

在当前连接会话改变当前日期格式
ALTER SESSION SET nls_date_format="YYYY-MM-DD HH:MI:SS"

不等于
!=   <>


BETWEEN ... AND        NOT BETWEEN  ......AND

SELECT *
FROM [TABLE]
WHERE ID BETWEEN 1001 AND 1005;

SELECT *
FROM [TABLE]
WHERE ID NOT BETWEEN 1001 AND 1005;

AND
SELECT *
FROM [TABLE]
WHERE ID〉=1000 AND ID>=1005 ;


使用IN    NOT IN

SELECT *
FROM [TABLE]
WHERE JOB IN （'manager','clerk'）;

SELECT *
FROM [TABLE]
WHERE JOB NOT IN （'manager','clerk'）;

SELECT *
FROM [TABLE]
WHERE JOB='manager'[|| ,OR]JOB='clerk';


使用like进行模糊查询

（1）%
字段 like '%关键字%'字段包含"关键字"的记录 
字段 like '关键字%'字段以"关键字"开始的记录
字段 like '%关键字'字段以"关键字"结束的记录
SELECT *
FROM [TABLE]
WHERE NAME LIKE '%aa%';
（2）_
 单一任何字符（下划线）常用来限制表达式的字符长度语句
 
SELECT * FROM [user] WHERE uname     LIKE '_三_'
搜索结果：“猫三脚”这样uname为三个字符且中间一个是“三”的；
（3）[]
在某一范围内的字符，表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
SELECT * FROM [user] WHERE u_name     LIKE '老[1-9]';
SELECT * FROM [user] WHERE u_name LIKE     '[张李王]三';


空值
SELECT *
FROM TABLE
WHERE ID IS NULL;


对查询数据进行排序
默认按升序排列

SELECT *
FROM table
WHERE ID〉100
ORDER BY SALARY；
升序
SELECT *
FROM table
WHERE ID〉100
ORDER BY SALARY ASC；
降序
SELECT *
FROM table
WHERE ID〉100
ORDER BY SALARY DESC;

按照多个字段排序 
SELECT *
FROM table
WHERE ID〉100
ORDER BY SALARY,ID;
先按SALARY  后按ID排序

按照别名排序
SELECT SALARY "薪水",ID
FROM table
WHERE ID〉100
ORDER BY "薪水";

函数

用于执行处理和复杂运算

最大值
max()

最小值
min()

计数
count() 

日期
to_date(原数据,'格式')
        yyyy  		四位年  
        mm          两位月
        mon    		简化字符集表示        
        month 		完整字符集表示 
        dd          当月第几天 
        ddd         当年第几天 
        dy          当周第几天简写 
        day         当周第几天全写         
        hh          12小时进制 
        hh24        24小时进制               
        mi          60进制    
        ss          60进制 
        Q           季度                  
        WW          当年第几周            
        W           当月第几周            

列出指定表的列定义，视图或同义词，或指定函数或存储过程的详述。
desc 对象

其他函数
算式中出现null结果为空需要转换为0

/

CASE 实现IF 	ELSE IF
SELECT JOB,SALARY
	CASE JOB
		WHEN 'MANAGER'THEN
			SALARY+100
		WHEN 'CLERCK'THEN	
			SALARY-100
		ELSE
			SALARY=100
	END AS "修订后工资"
FROM TABLE
WHERE ID>100;


分组函数 对数据进行分组
SELECT ID，MAX(SALARAY)
FROM TABLE
GROUP BY ID;
按照ID进行分组  求每个分组的最高工资

按照多个字段进行分组
SELECT ID，MAX(SALARAY)
FROM TABLE
GROUP BY ID,SALARY;


HAVING
对分组的数据进行过滤不能使用where 而是要使用having
SELECT ID，MAX(SALARAY)
FROM TABLE
GROUP BY ID
HAVING MAX(SALARY)>=3000
ORDER BY ID DESC; 

首先使用where进行过滤 对过滤后的数据进行分组 分组后使用having进行过滤
SELECT ID，MAX(SALARAY)
FROM TABLE
WHERE ID IS NOT NULL
GROUP BY ID
HAVING MAX(SALARY)>=3000
ORDER BY ID DESC; 

组函数可以嵌套
组函数嵌套时候必须使用GROUP BY
组函数最多可以嵌套两层

多表查询查询

/

多表查询
SELECT NAME,NUMBER,SALARY
FROM TABLE1,TABLE2


如果有相同的列
SELECT NAME,TABLE1.NUMBER,TABLE2.NUMBER,SALARY
FROM TABLE1,TABLE2
产生笛卡尔乘积


去除笛卡尔乘积
SELECT NAME,TABLE1.NUMBER,TABLE2.NUMBER,SALARY
FROM TABLE1 AS T1,TABLE2 AS T2
WHERE T1.ID = T2.ID;
AND NAME LIKE 'XXX';


多表联合查询
uid cid sid
SELECT U.*，C.*,S.*
FROM USERS U,CLASS C,SCHOOL S
WHERE U.UID = C.CID AND U.UID = S.SID;


自查询
自连接 通过别名虚拟成两个表 然后从这两个表找等值查询
FROM USERS S,USERS U


外连接
在等值查询的基础上 可以查询不满足等质条件的数据
特殊的等值连接
--左外连接
可以把右边不满足的添加进来
 SELECT U.*，C.*,S.*
FROM USERS U,CLASS C
WHERE U.UID(+) = C.CID
+不能同时出现在两边
--交叉连结
SELECT 
FROM TABLE1 T1
CROSS JOIN TABLE2 T2;
--自然连接
在父子表关系上自动匹配两个表中列明完全相同的字段 在这些相同名称的字段上做等值查询
SELECT T1.NAME T2.NAME NATURAL_COLUMN 
FROM TABLE1 T1
NATURAL JOIN TABLE2 T2
--改进自然连接
JOIN ...USING 在自然连接的基础上 使用指定的列进行连接
SELECT
FROM TABLE1
JOIN TABLE2 USING(ID);


内连接
使用 JOIN ON 做N个表的等值查询 
需要n-1个join on语句
SELECT E.*,L.*
FROM TABLE1 T1
JOIN TABLE2 T2 ON (T1.ID = T2.ID)
JOIN TABLE3 T3 ON (T2.TID = T3.ID)
WHERE 1=1;


外连接
OUTER JOIN ... ON
LEFT OUTER JOIN ... ON (...)
可以把左边不满足等值条件的数据查询出来
RIGHT OUTER JOIN ... ON (...)
可以把左边不满足等值条件的数据查询出来
FULL OUTER JOIN ... ON (...) 
可以把两边的不满足条件的全部查询出来


通过查询结果创建表
SELECT *
from users
FOR CREAT_TABLE;


多个表合并为一个表
字段类型顺序必须一样才可以合并结果集 
去除重复
SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2
不去重复
SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2

子查询


子查询
select salary 
from employees
where uid = (select uid 
			from user 
			where uname = 'aaa');


多行子查询

any 任意一个
返回其他部门中比IT部门任一员工工资低的员工信息
select employInfo 
from employees
where job_id <> 'IT' and salary < any (select salary
										from employees
										where job_id = 'IT');

all 所有
返回其他部门中比IT部门所有员工工资低的员工信息
select employInfo 
from employees
where job_id <> 'IT' and salary < all (select salary
										from employees
										where job_id = 'IT');
in 等于列表中的任意一个
返回其他部门中等于IT部门员工工资低的员工信息
select employInfo 
from employees
where job_id <> 'IT' and salary in (select salary
										from employees
										where job_id = 'IT');

创建&管理表

–DDL–
常见的数据库对象
在这里插入图片描述

创建表


/

查看用户创建所有表
select table_name from user_tables;							

查看属于用户数据对象
select * from user_catalog;


创建表

(1)直接创建空表
create table 表名称
(
id  varchar2(50) primary key ,
name char(200） not null,
phone number(11) unique,
class carchar(10)， 
);

(2)从其他表导入数据建表
create table 表名
as 
select uid, uname ,salary
from employees;

(3)从其他表导入列表建空表
create table 表名
as 
select *
from employees where 1 = 2;

-------------------------------------------------------------
varcha2(size) 	----0-4000,可变长度
char(size) 		----0-2000,固定长度,用空格在数据的右边补到固定长度
long 			----可变长字符数据	2G
number(6,2) 	----6位整数、2位小数
number(3) 		----3位整数
clob 			----txt文本	4G
blob 			----图片、视频、声音等转换过来的二进制对象  4G
date 			----sysdate
bfile  			----存储外部文件数据 4G
rowid			----行地址

管理表


/

添加一列
alter table 表名 add (email varchar2(20))

修改列
alter table 表名 modify (id number(15) default 2000)    --默认值

删除列
alter table 表名 drop column 列名

重命名列
alter table 表名 rename column 列名1 to 列名2

删除表
drop table 表名

清空表
truncate table 表名

改变对象（表，视图，序列，同义词等）名称
rename name1 to name2

rollback;
增删改可以回滚 DDL不可以回滚

数据处理

增删改

insert 插入数据 
插入数据 （列名与数据一一对应）
insert into 表名 values (数据1, 数据2, 数据3)    

insert into 表名 (列3, 列2, 列1)values (数据3, 数据2, 数据1)

insert into 表名 (列3, 列2, 列1) select 列3, 列2, 列1 from employees


update 更新数据

update 表名
set name = ‘zhangsan’ where id= 123;


delete 删除记录
delete from 表名 where id=123

事务

在这里插入图片描述

约束

概念
约束是表级的强制规定
有以下五种约束:
– NOT NULL 不为空
– UNIQUE 唯一
– PRIMARY KEY 主键
– FOREIGN KEY 外键
– CHECK 检查条件
创建时机
创建和修改约束:
— 建表的同时
— 建表之后

可以在表级或列级定义约束
可以通过数据字典视图-查看约束

作用范围:
列级约束：职能作用在一个列
表及约束：可以作用在多个列上

列的约束必须定义在列的后边，表及约束可以独立定义为空约束职能作用在列上.

CREATE 时创建约束

not null约束
约束名定义： constraint emp_id_nn 

create table emp(
	id number(10) constraint emp_id_nn not null,
	name varchar(20) not null
)

unique约束

create table emp(
	id number(10) constraint emp_id_uk unique,
	name varchar(20) not null
)

表级约束
create table emp(
	id number(10) constraint emp_id_uk unique,
	name varchar(20) not null，
	email varchar(20),
	constraint emp_email_uk unique(email)
)

primary key

特点：a.主键不可为空  b.唯一确定

create table emp(
	id number(10) constraint emp_id_pk primary key,
	name varchar(20) not null
)

表级别
create table emp(
	id number(10) 
	name varchar(20) not null，
	email varchar(20),
	constraint emp_email_uk unique(email),
	constraint emp_email_pk unique(id),
)

foreign key 

create table emp(
	id number(10) 
	name varchar(20) not null，
	email varchar(20),
	department_id number(10)
	constraint emp_email_uk unique(email),
	constraint emp_email_pk unique(id),
	constraint emp_dept_id_fk foreign key(department_id) references [departments | 表名] (department_id)
)

级联删除
当父表中的列被删除后 子表中的相对应的列也被删除

级联置空
子表中相应的列置空

create table emp(
	id number(10) 
	name varchar(20) not null，
	email varchar(20),
	constraint emp_email_uk unique(email),
	constraint emp_email_pk unique(id),
	constraint emp_dept_id_fk foreign key(department_id) references [departments | 表名] (department_id) on delete set null 
)

check 约束

create table emp(
	id number(10) 
	name varchar(20) not null，
	email varchar(20),
	slary number(10.3) check (salary > 1500 and salary <3000),
)

ALTER添加删除约束

alter table语句：
可以添加或者删除约束 凡是不可以修改约束
有效化 无效化约束
添加 not null 约束要用MODIFY

ALTER TABLE [TABLENAME] 
ADD CONSTRAINT [CONSTRAINTNAME] [TYPE] (COLUMN);

删除约束
ALTER TABLE emp
DROP CONSTRAINT EMP5_NAME_NN

添加唯一约束
alter table emp
add constraint emp_name_uk unique(name)

有效化&无效化 约束

alter table emp 
disable constraint emp_name_uk

alter table emp 
enable constraint emp_name_uk

视图

表：基本的数据存储集合由行和列组成
视图：从表中抽出的逻辑上的相关的数据集合
序列：提供有鬼路的数值
索引：提供高效率的查询
同义词：给对象起别名

属性概念

不同权限的人查看的同在一张表的信息不同
通过子表的方式实现功能, 但是修改时要全部修改
不能分为多张子表表修改时过于麻烦
通过视图的方式来修改可以直接修改原表
视图是一个虚表试图依赖的表称为基表
视图会继承基表的约束

操作

创建视图

create view employview
as
select id,name,salary
from employment
where department_id = 10;

修改视图

create or replace view employview
as
select id,name,salary
from employment
where department_id = 10;

屏蔽DML操作

create or replace view employview
as
select id,name,salary
from employment
where department_id = 10
with read only;

简单视图和复杂视图区别：
在创建时候使用到了分组函数

特性	简单视图	复杂视图
表的数量	1	1-N
函数	无	有
分组	无	有
DML操作	可以	有时可以

删除视图
drop view viewname

TOP-N分析

概念

分析一个列中最大或者最小的n个值

添加伪列
rownum 只能使用 < <=

select rownum, name, id,salary
from (
	select name, id,salary
	from tablename
	order by salary
)
where rownun <= 10

将其转化为真正的列
select rn,name, id,salary
from(
	select rownum rn, name, id,salary
	from (
		select name, id,salary
		from tablename
		order by salary
	)
)
where rn <= 10 and rn >5

其他数据库对象

序列

可以让用户产生唯一数值的数据库对象

自动提供唯一值
共享对象
只要提供主键值
将序列装入内存可以提高访问效率

创建序列

CREATE SEQUENCE SEQUENCENAME
[INCREMENT BY N] --每次增长的数值
[START WITH N]   --从那个值开始
[MAXVALUE N | NOMAXVALUE]
[MINVALUE N | NOMINVALUE]
[CYCLE | NOCYCLE] --是否需要循环
[CACHE N | NOCACHE --是否需要缓存登录

-----------------------------------
为一个数据库对象   其包含方法
insert into table1 values (sequencename.nextval,"aa",200);

修改序列初始值需要 删除序列并
重新建立序列

--------------------------------------
裂缝
多个表使用统一个序列  会产生裂缝

删除序列
drop sequence seq

索引

索引是一种独立于表外的数据独对象
索引被删除或者损坏不会影响表
索引一旦建立有系统维护并使用不用再查询sql中指定使用哪个索引
通过快速定位数据的方法减少磁盘I/O

创建索引

1.自动创建索引
在定义主键约束或者唯一约束的时候后自动创建唯一性索引
2.手动创建
用户可以在其他列上创建非唯一的索引

create index indexname
on tablename ([column1,column2])

删除索引
drop index indexname

创建索引的时机：

列中数据分布范围比较广
经常被where子句或者连接条件
经常访问并且数据量大

同义词

命别名

create synonym em for employments;

select * from em;


删除同义词
drop synonym [name]

Set操作符

参考

以A集合中有元素2，3；B集合中有元素1，3，4为例解释以下操作符
UNION 取A集合与B集合的并集，结果为1，2，3，4
UNION ALL 两集合的所有元素，结果为1，2，3，3，4
INTERSECT 取两集合的交集，结果为3
MINUS 如为A MINUS B ，在A中与B求差，结果为2

set操作符表达的是两个集合间的操作

select  *  from  ...
union all
select  *  from  ...

PL/SQL

PL/SQL数据路程序设计语言专门用于对oracle数据库进行访问

基本语法

块结构

	DECLARE
		//声明部分 声明用到的变量类型有标局部的存储过程和函数 
	BEGIN
		//执行部分 过程及其sql语句 程序的主体
	EXCEPTION
		//异常处理和分析
		--when 。。。 then 。。。
	END；
	
执行部分是必须的

标识符	命名规则	例子
程序变量	V_name	V_name
程序常量	C_name	C_name
游标变量	Name_cursor	Emp_ cursor
异常标识	E_name	E_roo_many
表类型	Name_table_type	Emp_record_type
表	Name_table	Emp
记录类型	Name_record	Emp_record
SQL*plus替代变量	P_name	P_sal
绑定变量	G_name	G_year_sal

记录类型，把逻辑相关的类型数据作为一个单元存储起来，称作PL/SQL recoed的域（FIELD），来存放不同类型但逻辑相关的信息类似于类
声明变量
[value_name] [type];
%type 取其变量类型
%rowtype 根据行结构定义类型
声明记录类型
type name_record is record( [value_name] [type], ... )

声明变量   
V_name date;
v_aal employee.salary%type

声明记录类型  
type emp_record is record(
	v_aal employee.salary%type,
	v_aal employee.salary%type,
	v_aal employee.salary%type
)
//定义一个记录类型的成员变量
v_emp_record emp_record;
---------------------------------------------
declare 
	v_name emp.name%type;
begin
	select name into v_name from emp where id =1;	
	dbms_output.put_line(v_name);
end;
/

基础语句
判断
1. if<exp>then SQL elif<EXP> then SQL else SQL end if;
2. case - when - then - end;
  循环
3. loop - exit when - end loop;
4. while - loop - end loop;
5. for i in …
6. goto exit
运算符

:= 赋值
/ 除号
=> 关系号
… 范围运算符
|| 字符连接符
IS NULL 空
BETWEEN AND 介于两者之间
IN 在一系列值中
AND 与
OR 或
NOT 取反（is not null）

判断语句
（1） if语句
declare 
	v_name emp.name%type;
begin
	select name into v_name from emp where id =2;
	if v_name = 'zhangsan' then
		dbms_output.put_line(v_name);
	else 
		dbms_output.put_line('is not zhangsan');
	end if;
end;
/
（2）case语句
declare 
	coun number :=3;
	v_employ emp%rowtype;
begin
	while coun >=1 loop
		case coun
			when 3 then
				dbms_output.put_line(333);
			when 2 then
				dbms_output.put_line(222);
			when 1 then
				dbms_output.put_line(111);
		end case;
		coun := coun-1;
	end loop;
end;
/

循环语句
（1）loop语句
declare 
	coun number :=3;
	v_employ emp%rowtype;
begin
	loop
		select * into v_employ from emp where id = coun;
		dbms_output.put(v_employ.id);
		dbms_output.put(v_employ.name);
		coun := coun-1;
		exit when coun = 0;
	end loop;
end;
/
（2）where语句
declare 
	coun number :=3;
	v_employ emp%rowtype;
begin
	while coun >=1 loop
		dbms_output.put_line(111);
		coun := coun-1;
	end loop;
end;
/
（3）for语句
declare 
	coun number :=1;

	v_employ emp%rowtype;
begin
	for i in 1..100 loop
		dbms_output.put_line(coun);
		coun := coun + 1;
	end loop;

end;
/

// reverse 表示递减  不加默认递增
begin
	for coun in reverse 1..10 loop
		dbms_output.put_line(coun);
	end loop;

end;
/

游标

在执行一个sql语句时 Oracle会创建一个隐式的游标游标是内存中最处理该语句的工作区域，其中存储了sql执行的结果

通过游标可获知结果和游标状态

类似于java中的迭代器

显示的斥力游标
a.定义游标
cursor \[cursorname] \[parameter1,...] is select_statement;
游标参数只能作为输入参数
parametername [in] datatype [{:=| default}expression]
不能使用长度约束
b.打开游标
open cursorname [parameter=>value,...]
不能重复打开i
c.提取游标数据
detch cursorname into { variable_list | record_variable }
关闭游标
处理完毕及时关闭释放资源 fetch无效可以用open打开
close cursorname

declare 
	name emp.name%type;
	cursor emp_name_cursor is select name from emp;
begin
	--打开游标
	open emp_name_cursor;
	--提取游标
	fetch emp_name_cursor into name;
	while emp_name_cursor%found loop		
		dbms_output.put_line(name);
		fetch emp_name_cursor into name;
	end loop;
end;
/

闻舟已渡

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle

Oracle简单SQLSQL语句分类SQL函数简单SQL创建表SQL> CREATE TABLE USERS( 2 ID NUMBER(8) PRIMARY KEY, 3 NAME VARCHAR(10), 4 SEX CHAR(2), 5 ADDR VARCHAR2(20), 6 BIRTHDAY DATE, 7 SALARY NUMBER(8,2) 8 );表已创建。删除表DROP TABLE xxx;查询表结构（只能在命令行模式下）
复制链接

扫一扫

专栏目录