Oracle基本语法

一. DDL数据定义语言

1.1 创建表空间

create tablespace tablespace_name
	datafile '/home/app/oracle/oradata/orcl/waterboss.dbf'
	size 100m
	autoextend on
	next 10m;

1.2 删除表空间

	--删除空的表空间，但是不包含物理文件
    	drop tablespace tablespace_name;
        --删除非空表空间，但是不包含物理文件
        drop tablespace tablespace_name including contents;
        --删除空表空间，包含物理文件
        drop tablespace tablespace_name including datafiles;
        --删除非空表空间，包含物理文件
        drop tablespace tablespace_name including contents and datafiles;
        --如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
        drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

1.3 用户

  -- 查看当前用户
    select username from all_users;
    -- 创建用户
    create user user_name   -- user_name 为创建的用户名。
    identified by 'password' -- identified by  用于设置用户的密码
	default tablespace table_name; -- default tablesapce 用于指定默认表空间名称
    -- 用户赋权
    grant dba to user_name; -- 给用户赋予dba权限

1.4 创建表

  -- 创建表
    CREATE TABLE table_name (       
    column1 datatype [DEFAULT expression] [constraint],
    column2 datatype [DEFAULT expression] [constraint],
    ...
);       -- table_name：表的名称。
         -- column1, column2, ...：列的名称。
		 -- datatype：列的数据类型，如 VARCHAR2, NUMBER, DATE 等。
		 -- DEFAULT expression：为列指定默认值。
		 -- constraint：为列添加约束，如 PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK 等

1.5 修改表

   -- 修改表
    	-- 修改表中字段
    	ALTER TABLE table_name MODIFY(
    	column1  datatype [DEFAULT expression] [constraint]，
    	column2  datatype [DEFAULT expression] [constraint]...
		);
		-- 修改表中字段名
		ALTER TABLE table_name RENAME COLUMN old_column TO new_column;

1.6 删除表

    -- 删除表
    	--删除一个字段 
    	alter table table_name drop column column_name;
    	--删除多个字段 
    	alter table table_name drop (column1_name, column2_name ...);
    -- 清空表
    truncate table table_name;

二. DML数据操作语言

2.1 插入数据

  -- 插入数据
    	-- 插入单条数据
    	insert into table_name values (value1，value2，...);
    	-- 插入多条数据
    	insert into table_name(column1，column2 ，...) values(value1，value2，...),(value1，value2，...);

2.2 更新,修改数据

    -- 更新,修改数据
    update table_name set column1 = value1, column2 = value2 ，.... where condition;

2.3 删除数据

   -- 删除数据
    delete from table_name where condition;

三. DQL数据查询语言

3.1 单表查询

	    -- where精确查询
    	select * from table_name 
    	where condition;
    	-- like模糊查询
    	select * from table_name 
    	where column like pattern;
    	-- in 查询
    	SELECT column1, column2, ... 
    	FROM table_name 
    	WHERE column IN (value1, value2, ...);
    	-- between 查询
    	SELECT column1, column2, ... 
    	FROM table_name 
    	WHERE column BETWEEN value1 AND value2;
    	-- 分组去重
    		--distinct去重
    		SELECT DISTINCT column1, column2, ... 
    		FROM table_name;
    		--group by去重
    		SELECT column_name, aggregate_function(column_name) 
    		FROM table_name 
    		WHERE column_name operator value 
    		GROUP BY column_name;
    		--row_number去重
    		select * from (select t1.*, rownumber（）over（partitiion by column）
                           as rn from table_name as t1) where rn > 1;
    	-- 伪列查询
    		-- rowid语法
    			-- 查询rowid
    				select rowid, t.* 
    				from table_name t;
    			-- 指定rowid来查询记录
    				select rowID,t.* 
    				from table_name t 
    				where ROWID= 'AAAR+DAAHAAAAFfAAA';
    		-- rownum语法
    			-- 查询rownum
    			select rownum,t.* from table_name t;
    			-- 返回查询的前五行
    			select *
				from table_name
				where rownum <= 5;
    			-- 查询虚拟表中虚拟列中的前五列
    			select ROWNUM
				from dual 
				connect by rownum <= 10;
    	-- 聚合查询
    		-- 聚合函数
    			-- 求和函数
    			SELECT SUM(column_name) FROM table_name;
    			-- 求平均值函数 
    			SELECT AVG(column_name) FROM table_name
    			-- 求最大值函数
    			SELECT MAX(column_name) FROM table_name;
    			-- 求最小值函数
    			SELECT MIN(column_name) FROM table_name;
    			-- 统计记录个数函数
    			SELECT COUNT(column_name) FROM table_name;
    		-- 分组聚合
    		select column1,sum(column) 
    		from table_name 
    		group by column1;

3.2 多表查询

        -- 内连接
    	SELECT column_name(s)
   		FROM table1
		INNER JOIN/JOIN table2
		ON table1.column_name=table2.column_name;
    	-- 左连接
    	SELECT column_name(s)
		FROM table1
		LEFT JOIN/LEFT OUTER JOIN table2
		ON table1.column_name=table2.column_name;
    	-- 右连接
    	SELECT column_name(s)
		FROM table1
		RIGHT JOIN/RIGHT OUTER JOIN table2
		ON table1.column_name=table2.column_name;
    	-- 全连接
    	SELECT column_name(s)
		FROM table1
		FULL JOIN/FULL OUTER JOIN table2
		ON table1.column_name=table2.column_name;
    	-- 联合查询
    	SELECT column1, column2, ...
		FROM table1
		UNION
		SELECT column1, column2, ...
		FROM table2;
    	-- 子查询
    		-- 单行子查询
    			-- where子查询
    			select * from table_name2
   				where column in (select * from table_name1 where condition1);
   				-- select子查询
   				select column1, column2,
       			(select t2.column from table_name2 t2 where condition)
				from table_name1 t1;
    		-- 多行子查询(from)
    		select column1,column2,...
			from table_name t3, (select column1,... from table_name1 t1
            where condition1) t2
			where condition2;
    	-- cte表达式
    	WITH Expression_Name [ ( ColumnName [1,...n] ) ]
    	AS ( CTE query definition )
    	subquery;

3.3 分页查询

        -- 简单分页
    	select rownum, t.* from table_name t where rownum<=n; -- 查询前n条记录
    	-- 基于排序的分页
    	select *
		from (select rownum as rnum, t.*
    			from table_name t
    			where rownum <= value2)
		where rnum >= value1; -- 查询value1到value2之间的记录

3.4 递归查询

	        -- connect by语法
    		-- 语法1
    		select * from table_name
			[start with condition1]
			connect by [prior] father_column=son_column;
    		-- 语法2
    		select * from table 
			[start with condition1]
			connect by son_column= [prior] father_column;
			-- SYS_CONNECT_BY_PATH查询根节点到叶子的路径
			SELECT SYS_CONNECT_BY_PATH(column_name, 'connect_symbol')
			-- connect_by_isleaf 查询当前节点是否为叶子节点
			SELECT connect_by_isleaf

3.5 行列转换

    	--行转列
    	select
       	(select ... ) column0,
 		sum/count ( case when condition1 then value1 end) column1,
 		sum/count ( case when condition2 then value2 end) column2,
  		sum/count ( case when condition3 then value3 end) column3,
  		......
		from table_name
		where condition
		group by column;
    	--列转行
    	SELECT 'value1' as column1,column2 as column3 FROM table_name
		union
		SELECT 'value2' as column1,column2 as column3 FROM table_name
		union
		SELECT 'value3' as column1,column2 as column3 FROM table_name;
    -- 集合运算
    	--并集运算
    		-- 去重
    		select * from table_name where condition
			union
			select * from table_name where condition;
    		-- 不去重
			select * from table_name where condition
			union all
			select * from table_name where condition;
    	--交集运算
    	select * from table_name where condition
		intersect
		select * from table_name where condition;
    	--差集运算
    	select * from table_name where condition
		minus
		select * from table_name where condition;

四. DCL数据控制语言

4.1 权限

        --系统权限
			--显示系统权限
			select * from system_privilege_map order by name;
            --授予系统权限
            		grant system_privilege_map to user_name [with admin option];
			--回收系统权限
			revoke system_privilege_map from user_name;
		--对象权限
			--显示对象权限
			select distinct privilege from dba_tab_privs;
			--授予对象权限
			grant privilege(alter/update/delete...) on object(table/index/view...)
            to user_name [with grant option];
			--回收对象权限
			revoke privilege(alter/update/delete...) on object(table/index/view...)
            to user_name [with grant option];
			--说明赠予回收系统权限和对象权限的区别
			系统权限不会被级联收回,对象权限会被级联收回

4.2 角色管理

-- connect角色权限
		connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。
		拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)。
		-- resource角色权限
		更可靠和正式的数据库用户可以授予resource role。
      	resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
		-- dba角色权限
		dba role拥有所有的系统权限。
	-- 自定义角色
		-- 创建角色
			--建立角色(数据库验证)
			create role role_name identified by ...;
			--建立角色(不验证)
			create role role_name not identified;
			--给角色授权
			grant system_privilege_map to role_name;-- 授予系统权限
			grant privilege on object to role_name;-- 授予对象权限
		-- 分配角色给某个用户
		grant role_name to user_name [with admin option];
		-- 删除角色
		drop role role_name;
		-- 显示角色信息
			-- 显示所有角色
			select * from dba_roles;
			-- 显示用户具有的角色,及其默认角色(用户名大写)
			select granted_role from dba_role_privs where grantee='USER_NAME';

五. 函数&窗口函数

5.1函数

5.1.1 字符串函数

	        --1.返回对应字符的十进制值
			select ASCII(str) from dual;
			--2.给出十进制返回字符
			select char(str) from dual;
			--3.拼接两个字符串,与||相同
			select concat(str1,str2) from dual;
			--4.将字符串的第一个字母变为大写
			select initcap(str) from dual;
			--5.找出某个字符串的位置
			select instr(sourceString, destString, start, appearPosition) from dual;
			--6.找出某个字符串的位置和字节数
			select instrb(sourceString, destString, start, appearPosition) from dual;
			--7.以字符给出字符串的长度
			select length(str) from dual;
			--8.以字节给出字符串的长度
			select lengthb(str) from dual;
			--9.将字符串转换成小写
			select lower(str) from dual;
			--10.使用指定的字符在字符的左边填充
			select lpad(string, length, lpad_string) from dual;
			--11.在左边裁剪掉指定的字符
			select ltrim(string, trimstring) from dual;
			--12.使用指定的字符在字符的右边填充
			select rpad(string, length, rpad_string) from dual;
			--13.在右边裁剪掉指定的字符
			select rtrim(string, trimstring) from dual;
			--14.执行字符串搜索和替换
			select replace(string_expression , string_pattern , [string_replacement]) 				from dual;
			--15.取字符串的子串
			select substr(string_expression , start , length) from dual;
			--16.取字符串的子串 (以字节)
			select substrb(string_expression , start , length) from dual;
			--17.返回一个同音字符串
			select substrb(string) from dual;
			--18.执行字符串搜索和替换
			select translate(string_expression , string_pattern , string_replacement) 				from dual;
			--19.裁剪掉前面或后面的字符串
			select trim(trim_character FROM str) from dual;
			--20.将字符串变为大写
			select upper(str) from dual;

5.1.2 数值函数

            --1.绝对值 
			select abs(value) from dual;
			--2.大于或小于value的最小整数(向上取整)
			select ceil(value) from dual;
			--3.余弦
			select cos(value) from dual;
			--4.反余弦
			select acos(value) from dual;
			--5.e的value次幂
			select exp(value)from dual;
			--6.小于或等于value的最大整数(向下取整)
			select floor(value) from dual;
			--7.value的自然对数
			select ln(value) from dual;
			--8.value的以n为底的对数
			select log(n,value) from dual;
			--9.求模(取余,返回 value/n 的余数)
			select mod(value,n) from dual;
			--10.value的exponent次幂
			select power(value,exponent) from dual;
			--11.按procision精度四舍五入
			select round(value,procision) from dual;
			--12.value 为正返回 1;为负返回-1;为 0 返回 0(返回正负值)
			select sign(value) from dual;
			--13.正弦
			select sin(value) from dual;
			--14.反正弦
			select asin(value) from dual;
			--15.value 的平方根
			select sqrt(value) from dual;
			--16.正切
			select tan(value) from dual;
			--17.反正切
			select atan(value) from dual;
			--18.按照 precision 截取 value
			select trunc(value,precision) from dual;
			--19.返回 value 在 ORACLE 的存储空间大小
			select vsize(value) from dual;

5.1.3 日期函数

            --1.获取当前日期
			select sysdate from dual;
			--2.在当前日期基础上加指定的月
			select add_months(date, value) from dual;
			--3.求所在月最后一天
			select last_day(date) from dual;
			--4.日期截取:截掉时分秒,留下年月日
			select trunc(sysdate) from dual;-- 截掉时分秒。留下年月日
			select trunc(sysdate, 'yyyy') from dual;
			select trunc(sysdate, 'mm') from dual;
			SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
			SELECT TO_NUMBER(TO_CHAR(sysdate, 'SSSSS')) AS seconds FROM dual;
			--5.获取日期中的年月日
			EXTRACT ( 
        	{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } 
            | { TIMEZONE_HOUR | TIMEZONE_MINUTE } 
        	| { TIMEZONE_REGION | TIMEZONE_ABBR } 
			FROM { date_value | interval_value } ) 
				--获取当前日期的年数
				select extract(year from sysdate)　from dual;
				--获取当前日期的月数
				select extract(month from sysdate) from dual;
				--获取当前日期的天数
				select extract(day from sysdate)　from dual;

5.1.4 转换函数

            --1.将字符转换到rowid 类型
		    select chartorowid(value)　from dual;
			--2.转换一个字符节到另外一个字符节
			select convert(str,set1,set2) from dual;
			--3.转换十六进制到二进制类型
			select hextoraw(value) from dual;
			--4.转换二进制到十六进制
			select rawtohex(value) from dual;
			--5.转换 ROWID 到字符
			select rowidtochar(rowid) from dual;
			--6.转换日期格式到字符串
			select to_char(sysdate, ['yyyy-mm-dd']) from dual;
			--7.按照指定的格式将字符串转换到日期型
			select to_date(sysdate, ['yyyy-mm-dd']) from dual;
			--8.把单字节字符转换到多字节
			select TO_MULTI_BYTE(str) from dual;
			--9.将数字字串转换到数字
			select to_number(str) from dual;
			--10.转换多字节到单字节
			select TO_SINGLE_byte(str) from dual;

5.1.5 其他函数

	        --1.空值处理函数
				--NVL语法
				select NVL(NULL, returnvalue) from dual;
				--NVL2语法
				select NVL2(value,ifnull_returnvalue,nullvalue) from dual;
			--2.条件取值
			select decode(expression, value, result1, result2) from dual;

5.2 窗口函数

Function (arg1, ...) OVER (PARTITION BY column ORDER BY column Window_clause)
		-- 序号函数
			-- 排序,不记录重复值
			row_number(): 按照 1,2,3,4 标记序号
			-- 排序,记录重复值,重复时不保留名次
			rank(): 按照 1,2,2,4 标记序号
			-- 排序,记录重复值,重复时保留名次
			dense_rank():按照 1,2,2,3 标记序号
		-- 分布函数
			-- (rank - 1)/(rows - 1)
			CUME_DIST:用途：分组内小于、等于当前rank值的行数 / 分组内总行数
 			应用场景：查询小于等于当前薪资（salary）的比例
			-- <= 当前rank值的行数/总行数
			PERCENT_RANK():用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数			产生的序号，rows为当前窗口的记录总行数
			应用场景：不常用
		-- 前后函数
			-- 返回当前行的前n行的expr的值
			lag(column,n,expr)
			-- 返回当前行的后n行的expr的值
			lead(column,n,expr)
		-- 头尾函数
			-- 返回第一个expr的值
			first_value(column):默认窗口是:分组内当前行到往上无边界
			-- 返回最后一个expr的值
			last_value(column)默认窗口是:分组内当前行到往上无边界
		-- 其他函数
			-- 返回第n个expr的值
			NTH_VALUE(expr,n): 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
			-- 将有序数据分为n个桶,记录等级数
			NTILE(n):用途：将分区中的有序数据分为n个等级，记录等级数
		-- 调整窗口大小
		rows/range between unbound preceding/ n preceding/ current row/ n following/ unbound following and ...

六.视图,物化视图,索引,序列,同义词

6.1 视图

-- 创建视图
			-- 创建简单视图
			CREATE  VIEW view_name AS subquery;
			-- 创建带检查约束的视图
			CREATE  VIEW view_name AS subquery WITH CHECK OPTI;ON
			-- 创建只读视图
			CREATE  VIEW view_name AS subquery WITH READ ONLY;
			-- 创建带错误的视图
			CREATE  VIEW view_name AS subquery WITH READ ONLY;
			-- 创建复杂视图
			CREATE OR REPLACE FORCE VIEW view_name;
			AS 
			subquery
			[WITH CHECK OPTION ] 
			[WITH READ ONLY] 
		-- 删除视图
		DROP VIEW view_name;

6.2 物化视图

-- 创建物化视图
		create materialized view view_name
		[build immediate  |  build deferred ]
			--1.创建手动刷新的物化视图
			create materialized view view_name as subquery;
			begin
  				dbms_mview.refresh('view_name','C'); -- 'C'代表全量刷新
			end;
			--2.创建自动刷新的物化视图(全量刷新)
			create materialized view view_name
			refresh
			on commit
			as subquery;
			--3.创建不生成数据的物化视图
			create materialized view view_name
			build deferred
			refresh
			on commit
			as subquery;
			begin
  				dbms_mview.refresh('view_name','C');
			end;
			--4.创建增量刷新的物化视图
				-- 创建物化视图日志
				create materialized view log on table_name with rowid；
				-- 创建物化视图
				create materialized view view_name
				refresh fast
				as subquery;
				begin
        			DBMS_MVIEW.refresh('table_name','F');
				end;

6.3 索引

	-- 创建索引
    	create index index_name  on table_name(column);
    	-- 创建唯一索引
    	create unique index index_name  on table_name(column);
    	-- 创建组合索引
    	create index inedx_name  on table_name(column1,column2.....);
    	-- 删除索引
    	 ALTER TABLE table_name DROP INDEX index_name;
    	 DROP INDEX index_name ON table_name;

6.4 序列

		--创建序列
			--创建简单序列(默认)
			create sequence sequence_name;
				-- 提取下一个值
				select sequence_name.nextval from dual;
				-- 提取当前值
				select sequence_name.currval from dual;
			--创建带有参数的复杂序列
			CREATE SEQUENCE sequence_name  //创建序列名称
			[INCREMENT BY n]  //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
			[START WITH n]     //开始的值,递增默认是 minvalue 递减是 maxvalue
			[{MAXVALUE n | NOMAXVALUE}] //最大值
			[{MINVALUE n | NOMINVALUE}] //最小值
			[{CYCLE | NOCYCLE}] //循环/不循环
			[{CACHE n | NOCACHE}];//分配并存入到内存中
		--修改序列
		alter sequence sequence_name [maxvalue 5000  -- 不能修改start with参数
                                      cycle ... ];
		--删除序列
		DROP SEQUENCE sequence_name;

6.5 同义词

--创建同义词
			--创建私有同义词
			create synonym synonym_name for table_name;
			--创建公有同义词
			create public synonym synonym_name for table_name;
			--删除同义词

七. PL/SQL

7.1 PL/SQL基础语法

	[declare]
	begin
	[exception]
	end;
		-- 变量
		-- 记录型变量
			-- 列变量
			 variable_name    table_name.column_name%type;
			-- 行变量
			variable_name    table_name%rowtype;

7.2 异常

           -- 预定义异常
			exception
			when exception_name  then
				SQL statements;
			-- 用户自定义异常
			declare
   			 exception_name EXCEPTION; 
   			 PRAGMA EXCEPTION_INIT (exception_name,exception_number);
   			 	...
			exception
   			 when exception_name THEN -- 捕获自定义异常,捕获成功则打印下方语句
        		SQL statements;
			end;

7.3 条件判断

			-- 单分支
			if condition then
				SQL statements
			end if;
			-- 双分支
			if condition then
				SQL statements 1;
			else
				SQL statements 2;
			end if;
			-- 多分支
			if condition then
				SQL statements 1;
			elsif
				SQL statements 2;
				...
			else
				SQL statements N;
			end if;
		-- 循环
			-- 无条件loop循环
			loop
			[exit when condition]
			end loop;
			-- 条件循环while
			while condition 
			loop
 				 SQL statements;
			end loop;
			-- 序列循环for
			for variable in state_number..end_number
			loop
					SQL statements;
			end loop;

7.4 游标

	-- 基本语法
			declare
				cursor cursor_name(parameter_list)  is  subquery;
			begin
				open cursor_name(condition);
				loop
					fetch cursor_name into variable;
					exit when cursor_name%notfound;
				end loop;
				close cursor_name;
			end;
			-- for循环遍历游标
			declare
				cursor cursor_name(parameter_list)  is  subquery;
			begin
				for variable in cursorname(condition) loop
					SQL subquery;
				end loop;
			end;

7.5 存储函数

-- 创建存储函数
		CREATE FUNCTION function_name (parameter_list)  
		RETURNS return_datatype  
		is
		[characteristic ...]  
		BEGIN  
         	SQL statements
   		RETURN value;  
   		-- 调用存储函数
   		select function_name(parameter_list) from dual;

7.6 存储过程

-- 创建存储过程
		CREATE OR REPLACE PROCEDURE procedure_name(parameter_list)
		AS/IS
		[characteristic ...]  
		BEGIN
			 SQL statements
		END;
		-- 调用存储过程
		call procedure_name(parameter_list);

7.7 触发器

-- 基本语法
			-- 创建日志
			create table table_log_name
			(
                ...
			);
			-- 创建触发器
			create or replace trigger trigger_name
    		after
        	insert
   			 on table_name -- 记录T-AREA表中的插入
   			 for each row
			declare
			begin
    		insert into table_log_name values (...(:new/:old));
			end;

7.8 定时器

		-- 提交作业
			declare
				job_id number;
			begin
				DBMS_JOB.SUBMIT (
                    job_id        OUT BINARY_INTEGER,-- 任务编号
  					what          IN VARCHAR2,-- 任务内容
   					next_date     IN DATE DEFAULT SYSDATE,-- 任务下一次执行
   					interval      IN VARCHAR2 DEFAULT NULL-- 间隔时间
                );
				end;
		-- 开启定时器
			-- 运行定时器
			dbms_job.run(job_id);
			-- 记录任务编号(用于以后删除和修改)
			dbms_output.put_line(job_id);
			end;
		-- 查看作业
		select
    	job,log_user,
    	last_date,
   		next_date,
    	interval,
   		what
		from user_jobs;
		-- 清空作业
		truncate table user_jobs;
		-- 删除作业
		declare
		id number := n;
		begin
		DBMS_JOB.remove(id);
		end;

八. 备份,导入和导出

		-- 整库导出命令
		exp system/Aa123456 full=y
		-- 按用户导出与导入
		exp system/Aa123456 owner=c##wateruser file=wateruser.dmp
		-- 按用户导入
		imp system/Aa123456  file=wateruser.dmp  fromuser=wateruser
		-- 按表导出与导入
		-- 按表导出
		exp wateruser/itcast file=a.dmp tables=t_account,a_area
		--用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可
		-- 按表导入
		imp wateruser/itcast file=a.dmp tables=t_account,a_area