Oracle之基础篇

最新推荐文章于 2024-05-23 17:45:07 发布

微卡Chou

最新推荐文章于 2024-05-23 17:45:07 发布

阅读量72

点赞数

分类专栏：数据库文章标签： java

本BLOG上原创文章未经本人许可，不得用于商业用途。转载请注明出处，否则保留追究法律责任的权利。

本文链接：https://blog.csdn.net/xuanxxxxxx/article/details/115426366

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、数据库安装

地址

二、 windows下cmd进入oracle

sqlplus 用户名/密码

三、数据库管理器

菜单-Oracle文件夹-Database Controller orcl
输入system/123456 进入

四、DML【数据操纵语言】

insert、update、delete、select

-- 1. 列的别名
-- 紧跟别名，也可以在列名和别名之间加关键字'AS'，别名使用双引号，以便在别名中包含空格和特殊的字符并区分大小写。
-- 2. 连接符
-- 把列与列，列与字符连接在一起，用'||'表示，可以用来合成列。
   select name || id AS "Employees" from employees;
-- 3. 字符串
-- 字符串可以实select 列表中的一个字符、数字、日期，日期和字符只能在单引号中出现。
-- 4. 删除重复行
   select 'DISTINCT'
-- 5. 比较运算（WHERE 后面）
-- BETWEEN...AND...、IN(...)、LIKE(% 任意个字符，_ 一个字符)、IS NULL
-- 6. 单行函数
-- LOWER('SQL Course') sql course
-- UPPER('SQL Course') SQL COURSE
-- INITCAP('SQL Course') Sql Course
-- CONCAT('Hello', 'World') HelloWorld
-- SUBSTR('HelloWorld',1,5) Hello
-- LENGTH('HelloWorld') 10
-- INSTR('HelloWorld', 'W') 6
-- LPAD(salary,10,'*') *****24000 --从左边对字符串使用指定的字符进行填充，10 是填充之后的字符串长度。
-- RPAD(salary, 10, '*') 24000*****
-- TRIM('H' FROM 'HelloWorld') elloWorld
-- 		如 trim('字符1' from '字符串2') ，字符1只能是单个字符。
--		1. trim()删除字符串两边的空格。
--		2. ltrim()删除字符串左边的空格。
--		3. rtrim()删除字符串右边的空格。
--		4. trim('字符1' from '字符串2') 分别从字符2串的两边开始，删除指定的字符1。
--		5. trim([leading | trailing | both] trim_char from string)　从字符串String中删除指定的字符trim_char。
--			leading:从字符串的头开始删除。
--			trailing:从字符串的尾部开始删除。
--			borth:从字符串的两边删除。
--		6. tim()只能删除半角空格。
--		For example:
--		trim(' tech ') would return 'tech'；
--		trim(' ' from ' tech ') would return 'tech'；
--		trim(leading '0' from '000123') would return '123'；
--		trim(trailing '1' from 'Tech1') would return 'Tech'；
--		trim(both '1' from '123Tech111') would return '23Tech'；
-- REPLACE(‘abcd’,’b’,’m’) amcd
-- ROUND() 四舍五入 ROUND(45.926,2) 45.93
-- TRUNC() 截断 ROUND(45.926,2) 45.92
-- MOD() 求余 MOD(1600,300) 100
-- SYSDATE() 日期、时间
--		在日期上加上或减去一个数字结果仍为日期。  
--		两个日期相减返回日期之间相差的天数。 
--		日期不允许做加法运算，无意义。可以用数字除24来向日期中加上或减去天数
-- MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 两个日期相差月数
-- ADD_MONTHS ('11-JAN-94',6) 向指定日期中加上若干月数
-- NEXT_DAY ('01-SEP-95','FRIDAY') 指定日期的下一个星期 * 对应的日期
-- LAST_DAY('01-FEB-95') 本月的最后一天
-- ROUND('25-JUL-95','MONTH') 01-AUG-95 日期四舍五入 
-- TRUNC('25-JUL-95','MONTH') 01-JUL-95 日期截断
-- 转换函数
-- 		隐式数据类型转换
--			(varchar2 or char) to number
--			(varchar2 or char) to date
--			(number) to varchar2
--			(date) to varchar2
--		显式数据类型转换
--			(char) to number -- to_number
--			(char) to date -- to_date
--			(number or date) to char -- to_char
-- to_char(date,'format_model')
--		日期格式：
--		必须包含在单引号中而且大小写敏感。
--		可以包含任意的有效的日期格式。
--		日期之间用逗号隔开
-- to_date(char,'format_model')
-- to_char(number,'format_model')
--		数字格式：
--		9 数字
--		0 零
--		$ 美元符
--		L 本地货币符号
--		. 小数点
--		, 千位符
-- 通用函数(适用于任何数据类型，同时也适用于空值)
--		NVL(commission_pct,0) 如果commission_pct是空值就转换为一个已知的值
--		NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3
--		NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1 
--		COALESCE(expr1, expr2,...) : 依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
-- IF-THEN-ELSE 逻辑
--		第一种：
--		CASE expr WHEN comparison_expr1 THEN return_expr1
--         [WHEN comparison_expr2 THEN return_expr2
--          WHEN comparison_exprn THEN return_exprn
--          ELSE else_expr]
--		END
--		第二种：
--		DECODE(col|expression, search1, result1 ,
--      		[, search2, result2,...,]
--      		[, default])

-- 多表查询
--	SQL语句的多表查询方式：
--	例如：按照department_id查询employees(员工表)和departments(部门表)的信息。
--		方式一(通用型):SELECT ... FROM ... WHERE
  		SELECT e.last_name,e.department_id,d.department_name FROM employees e,departments d where e.department_id = d.department_id
--		方式二：SELECT ... FROM ... NATURAL JOIN ...
--		有局限性：会自动连接两个表中相同的列(可能有多个:department_id和manager_id)
  		SELECT last_name,department_id,department_name FROM employees NATURAL JOIN departments
--		方式三：SELECT ... JOIN ... USING ...
--		有局限性：好于方式二，但若多表的连接列列名不同，此法不合适
  		SELECT last_name,department_id,department_name FROM employees JOIN departments USING(department_id)
--		方式四：SELECT ... FROM ... JOIN ... ON ...
--		常用方式，较方式一，更易实现外联接(左、右、满)
  		SELECT last_name,e.department_id,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id
--		内连接
--		    1）
--		    	等值连接
--		    	不等值连接
--		    2）
--		    	非自连接
--		   		自连接
--		外连接
--			左外连接、右外连接、满外连接

为什么where不能跟聚合函数？
因为where执行完还不确定结果集有多少组，而聚合函数是对每个组进行聚合运算。因为having是对组进行过滤，所以having后面的跟着的是已经分组的行或者聚合函数。

-- 子查询
--	查询 (内查询) 在主查询之前一次执行完成。
--	子查询的结果被主查询(外查询)使用 。
--	子查询要包含在括号内。
-- 	将子查询放在比较条件的右侧。
--	单行操作符对应单行子查询，多行操作符对应多行子查询

-- 一次只能向表中插入一条数据
INSERT INTO	table [(column [, column...])]VALUES(value [, value...]);
-- 在 INSERT 语句中加入子查询
INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90;
-- 更新数据
UPDATE   employees
SET      job_id  = (SELECT  job_id 
                    FROM    employees 
                    WHERE   employee_id = 205), 
         salary  = (SELECT  salary 
                    FROM    employees 
                    WHERE   employee_id = 205) 
WHERE    employee_id    =  114;
-- 删除数据
DELETE FROM departments WHERE  department_name = 'Finance';

五、DDL【数据定义语言】

create table、alter table、drop table、create index、drop index

# 创建表
CREATE TABLE dept(deptno 	NUMBER(2),
		dname 	VARCHAR2(14),
		loc 	VARCHAR2(13));
# 查看表结构
DESCRIBE dept;
# 通过子查询创建表
create table emp1 as select * from employees;
# 使用 ALTER TABLE 语句可以:
#	追加新的列
ALTER TABLE table
ADD	(column datatype [DEFAULT expr]
	[, column datatype]...);
#	修改现有的列
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
	[, column datatype]...);
#	删除一个列
ALTER TABLE table
DROP COLUMN  column_name;
#	重命名表的一个列名
ALTER TABLE table_name RENAME COLUMN old_column_name 
TO new_column_name；
# 清空表
TRUNCATE TABLE detail_dept;
# 执行RENAME语句改变表, 视图, 序列, 或同义词的名称
RENAME dept TO detail_dept;

六、DCL【数据控制语言】

commit、rollback、savepoint、lock

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
数据库事务组成:

一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句

数据库事务开始结束:

以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
2.1 COMMIT 或 ROLLBACK 语句
2.2 DDL 语句（自动提交）
2.3 用户会话正常结束
2.4 系统异常终止

回滚到保留点

使用 SAVEPOINT 语句在当前事务中创建保存点。
使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
UPDATE…
SAVEPOINT update_done;
INSERT…
ROLLBACK TO update_done;

自动提交在以下情况中执行:

DDL 语句。
DCL 语句。
不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。

自动回滚

会话异常结束或系统异常会导致自动回滚。

七、SQL*PLUS

https://blog.csdn.net/u012060033/article/details/90575037

八、约束constraint

# 什么是约束？
#	约束是表级的强制规定
# 有五种约束
	NOT NULL 
	UNIQUE 唯一
	PRIMARY KEY
	FOREIGN KEY
#		FOREIGN KEY: 在表级指定子表中的列
#		REFERENCES: 标示在父表中的列
#		ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
#		ON DELETE SET NULL(级联置空): 子表中相应的列置空
	CHECK
# 表级约束和列级约束
#	作用范围
#	①列级约束只能作用在一个列上
#	②表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）
# 	定义方式
#	列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义,特别的：非空(not null) 约束只能定义在列上
# 定义约束
CREATE TABLE employees(
  	     employee_id NUMBER(6),
    	 first_name VARCHAR2(20),
  	     ...
  	     job_id VARCHAR2(10) NOT NULL, # 列级
	     CONSTRAINT emp_emp_id_pk 
		 PRIMARY KEY (EMPLOYEE_ID)); # 表级
#	列级
		column [CONSTRAINT constraint_name] constraint_type
#	表级
		column,...
  		[CONSTRAINT constraint_name] constraint_type (column, ...),
# 添加约束
#	使用 ALTER TABLE 语句:
#		添加或删除约束,但是不能修改约束
#		有效化或无效化约束
#		添加 NOT NULL 约束要使用 MODIFY 语句
ALTER TABLE	table ADD [CONSTRAINT constraint] type (column);
# 删除约束
ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
# 无效化约束
ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk;
# 激活约束
ALTER TABLE employees ENABLE CONSTRAINT	emp_emp_id_pk;
# 查询约束
SELECT	constraint_name, constraint_type, search_condition
FROM	user_constraints
WHERE	table_name = 'EMPLOYEES';
# 查询定义约束的列
SELECT	constraint_name, column_name
FROM	user_cons_columns
WHERE	table_name = 'EMPLOYEES';

九、视图

从表中抽出的逻辑上相关的数据集合

视图是一种虚表。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
视图向用户提供基表数据的另一种表现形式

# 创建视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
  [(alias[, alias]...)]
 AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

CREATE VIEW empvu80 AS SELECT  employee_id, last_name, salary
FROM employees WHERE department_id = 80;
# 修改视图
CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id)
AS SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees WHERE department_id = 80;
# 视图使用DML规定
#	可以在简单视图中执行 DML 操作
#	当视图定义中包含以下元素之一时不能使用delete:
#		组函数
#		GROUP BY 子句
#		DISTINCT 关键字
#		ROWNUM 伪列
# 	当视图定义中包含以下元素之一时不能使用update:
#		组函数
#		GROUP BY子句
#		DISTINCT 关键字
#		ROWNUM 伪列
#		列的定义为表达式
#	当视图定义中包含以下元素之一时不能使insert:
#		组函数
#		GROUP BY 子句
#		DISTINCT 关键字
#		ROWNUM 伪列
#		列的定义为表达式
#		表中非空的列在视图定义中未包括
#	可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作
#		任何 DML 操作都会返回一个Oracle server 错误
CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title)
AS SELECT	employee_id, last_name, job_id
   FROM     employees
   WHERE    department_id = 10
   WITH READ ONLY;
# 删除视图
#	删除视图只是删除视图的定义，并不会删除基表的数据
DROP VIEW view;
# Top-N
#	Top-N 分析查询一个列中最大或最小的n个值。
#		查询最大的几个值的 Top-N 分析
		SELECT [column_list], ROWNUM  
		FROM   (SELECT [column_list] 
		        FROM table
		        ORDER  BY Top-N_column)
		WHERE  ROWNUM <=  N;
#		注意: 对 ROWNUM（行号） 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。
#		查询工资最高的三名员工：
		SELECT ROWNUM as RANK, last_name, salary 
		FROM  (SELECT last_name,salary FROM employees
       			ORDER BY salary DESC)
		WHERE ROWNUM <= 3;

十、运算符

将多个查询用 SET 操作符连接组成一个新的查询

UNION（并集）/UNION ALL（并集不去重）
INTERSECT（交集）
MINUS（差集）

除 UNION ALL之外，系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

十一、高级子查询

# 非相关子查询
#	执行过程：
#		（1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
#		（2）执行外部查询，并显示整个结果。
	SELECT	select_list
	FROM	table
	WHERE	expr operator (SELECT select_list FROM table);
	# 多列子查询
		# 主查询与子查询返回的多个列进行比较
		# 多列子查询中的比较分为两种:
	 		# 成对比较
			# 不成对比较
		SELECT	employee_id, manager_id, department_id
		FROM	employees
		WHERE  (manager_id, department_id) IN
                      (SELECT manager_id, department_id
                       FROM   employees
                       WHERE  employee_id IN (141,174))
		AND	employee_id NOT IN (141,174);
	# 在 FROM 子句中使用子查询（必须起别名）
	SELECT  a.last_name, a.salary, a.department_id, b.salavg
	FROM    employees a, (SELECT   department_id, 
	                      AVG(salary) salavg
	                      FROM     employees
	                      GROUP BY department_id) b
	WHERE   a.department_id = b.department_id
	AND     a.salary > b.salavg;
# 相关子查询（子查询中使用主查询中的列）
#	相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
#	执行过程：
#		（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
#		（2）执行内层查询，得到子查询操作的值。
#		（3）外查询根据子查询返回的结果或结果集得到满足条件的行。
#		（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。
# EXISTS 操作符
#	EXISTS 操作符检查在子查询中是否存在满足条件的行
#		如果在子查询中存在满足条件的行:
#			不在子查询中继续查找
#			条件返回 TRUE
#		如果在子查询中不存在满足条件的行:
#			条件返回 FALSE
#			继续在子查询中查找
SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X' FROM   employees WHERE  manager_id = 
                      outer.employee_id);
# NOT EXISTS 操作符
# 同上相反

# 相关更新
#	使用相关子查询依据一个表中的数据更新另一个表的数据
UPDATE table1 alias1 SET column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column =    
                        alias2.column);

# 相关删除
#	使用相关子查询依据一个表中的数据删除另一个表的数据
DELETE FROM table1 alias1 WHERE  column operator 
	(SELECT expression
 	 FROM   table2 alias2
 	 WHERE  alias1.column = alias2.column);

微卡Chou

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle之基础篇

一、数据库安装地址二、 windows下cmd进入oraclesqlplus 用户名/密码三、数据库管理器菜单-Oracle文件夹-Database Controller orcl输入system/123456 进入
复制链接

扫一扫