Oracle学习笔记附SQL练习上（持续更新）

Oracle

Oracle概述

Oracle介绍

Oracle简介

维基百科
Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。

Oracle数据特点

完整的数据管理功能:

• 数据的大量性
• 数据保存的持久性
• 数据的共享性
• 数据的可靠性
• 信息准则一关系型DBMS的所有信息都应在逻辑上用一种方法,即表中的值显示地表示
• 保证访问的准则
• 视图更新准则一只要形成视图的表中的数据变化了，相应的视图中的数据同时变化
• 数据物理性和逻辑性独立准则

Oracle体系结构

Oracle Server(服务器)
Oracle服务器是最大的概念,包含多种文件的结构,进程结构和内存结构;但是,处理SQL语句是,并非所有这些结构都会用到.某些结构用于提高数据库的性能,确保数据库在遇到软件或硬件错误时可以恢复,或者执行维护改数据库所需的其他任务.
Oracle服务器包含两个部分:Oracle Instance(实例)和Oracle Database(数据库)

Oracle Instance(实例)
定义:访问Oracle Database的一个手段;一个instance对应一个切也只能对应一个database,一个database可以有多个instance来访问它,也就是说instance和database是多对一个的关系,在典型的去年高考下,一台机器上装的Oracle,他们俩是一对一的关系,对应集群放是(RAK)是多对一;instance也是由两个部分组成:内存(memory)和后台进程(background process)

memory有两部分组成:SGA(system global area,系统全局区)和PGA(program global area,程序全局区);一个实例就是一个SGA,SGA实例的基本组成部分;一个server process启动的时候,就有一个PGA;oracle是一个吃内存的软件,内存主要消耗在SGA上

instance是活的东西,易于消失,oracle没有启动的话,也就没有instance

SYS用户和SYSTEM用户

Oracle安装会自动的生产sys用户和system用户

• sys用户是超级用户,具有最高权限,具有sysdba,有create database权限,改用户默认的密码change_on_install(安装时设定.用户手动给的)
• system用户是管理操作员,权限也很大.具有sysoper角色,没有create database,默认密码是manager
• 一般讲,对数据库维护,使用system永固登录就可以登录就可以
• 普通用户:scott 密码:安装时内置好,系统设定,默认tiger,默认锁定的
  注意：也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。

C:> sqlplus
请输入用户名：sys
输入口令：sys as sysdba //注意：在口令这里输入的密码后面必须要跟上 as sysdba 才可以。
SQL> alter user scott account unlock;
     用户已更改.
SQL> commit;
     提交完成.
SQL> conn scott/tiger
更改scott口令
新口令：tiger
重新键入新口令：tiger
口令已更改
已连接。
//完成。

创建一个用户
创建一个用户dog,密码 :1
create user dog identified by 1
给dog分配两个角色 Connect 和Resource
grant Connect, Resource to dog

数据字典解释

1.数据字典是指对数据的数据项,数据结构,数据流,数据存储,逻辑处理,外部实体等进行定义和描述,其目的是对数据流程图中的各个元素做出详细的说明.
2.数据字典是一种用户可以访问的记录数据库和应用程序源数据的目录.主动数据字典是指对数据库或应用程序进行修改时,其内容可以由DBMS自动更新的数据字典.
3.数据字典的主要作用:数据字典和数据流图共同构成系统的逻辑模型.没有流通数据字典难以发挥作用.没有数据字典,数据流图就不严格.

Oracle Database

Connection(链接)和session(会话).一个Connection主要指oracle客户端与后台服务器建立的链接,即客户端与服务器之间的通道.只有当链接建立起来以后,才能建立session会话.

从物理结构分析,Oracle Database主要包括三种文件类型.Data files(数据文件), Control files(控制文件)和Redo Log files(归档日志文件)以及password file (口令文件)等

Data files（数据文件）
用于存储数据库数据，它包括数据字典，用户数据，UNDO数据，临时数据等。数据库逻辑上由一个或多个表空间Tablespace组成，而表空间在物理上由一个或多个数据文件组成。

Control files（控制文件）
用于记录和维护数据库的物理结构

Redo Log files(重做日志文件)
用于记录数据库的变化,当里程出现失败或介质失败时可以使用它进行恢复数据库.当DDL或DML时,事物会写到日志缓冲区,在特定时刻会由LGWR将这些日志写入重做日志文件.数据库中至少有两个日志文件,他们是循环使用

Archived Log files（归档日志文件）
它是重做日志的备份，它会保存全部的重做历史记录。

Password file(口令文件) 和Parameter file(参数文件)
口令文件主要用于存放超级用户的口令及其他特权用户的用户名和口令。两个文件都保存在<安装路径>/database文件夹中。

Oracle表空间

简单来说,表空间就是一个或数据文件(物理文件)的集合,所有数据对象都被逻辑的存放在指定的表空间中.
表空间的类型

• 系统表结构: 系统自动创建的,一般存储Oracle的数据字典表和相应的数据
• 永久表空间:用来存放永久性数据,例如系统数据,应用程序数据
• 临时表 空间:例如Group by , order by 灯算法会用到一些临时空间,用完释放
• 撤销表空间:用于数据恢复

1.操作永久的表空间:
创建（表空间文件为1个）

create tablespace tablesName(
    logger       -- 可将表空间的创建信息记录到Oracle的日志中
    datafile `tablespaceName.dbf`     --保存文件路径,习惯上讲表空间的名称用于文件名称
    (可设置绝对路径,相对路径的话会保存到默认目录下)
    size 64m     -- 最初的数据文件大小
    autoextend on    -- 开启自增长
    next management local --extend management 有两种方式  local:本地   dictionary(数据字典管理) 
)12345678

创建（表空间文件为多个）

create tablespace tablespaceName 
    logger 
    datafile 
    'tablespaceName01.dbf' size 64m autoextend on next 64m maxsize unlimited, --设置自增长且无上限
    'tablespaceName02.dbf' size 64m autoextend on next 64m maxsize unlimited,
    'tablespaceName03.dbf' size 64m autoextend on next 64m maxsize unlimited
    extent management local;1234567

表空间增加文件

1 alter tablespace tablespaceName
2add datafile 'tablespaceName04.dbf'
3     size 64m autoextend on
4next 64m maxsize unlimited;1234

删除表空间

1 drop tablespace tablespaceName including contents and datafiles cascade constraints;1

2.创建临时表空间

create tablespace tablespaceName 
　　logger 
　　tempfile 
　　'tablespaceName01.dbf' size 64m autoextend on next 64m maxsize unlimited, --设置自增长且不设置上线
　　'tablespaceName02.dbf' size 64m autoextend on next 64m maxsize unlimited,
　　'tablespaceName03.dbf' size 64m autoextend on next 64m maxsize unlimited
　　extent management local;1234567

简单的命令

• 设置每页显示的数据条数: SET PAGESIZE 30;
• 设置每行显示的字符数: SET LINESIZE 100;
• 执行文件: @文件路径
• 显示当前用户: SHOW USER
• 切换登录用户: CONNECT 用户/密码(SYS用户需要) AS SYSDBA
• 拷贝文件: HOST COPY d:hello.sql d:data.sql

Oracle数据类型

1）字符类型

数据类型	长度	说明
CHAR(n BYTE/CHAR)	默认1字节，n值最大为2000	末尾填充空格以达到指定长度，超过最大长度报错。默认指定长度为字节数，字符长度可以从1字节到四字节。
NCHAR(n)	默认1字符，最大存储内容2000字节	末尾填充空格以达到指定长度，n为Unicode字符数。默认为1字节。
NVARCHAR2(n)	最大长度必须指定，最大存储内容4000字节	变长类型。n为Unicode字符数
VARCHAR2(n BYTE/CHAR)	最大长度必须指定，至少为1字节或者1字符，n值最大为4000	变长类型。超过最大长度报错。默认存储的是长度为0的字符串。
VARCHAR	同VARCHAR2	不建议使用

2）数字类型

数据类型	长度	说明
NUMBER(p[,s])	1-22字节。P取值范围1到38S取值范围-84到127	存储定点数，值的绝对值范围为1.0 x 10 -130至1.0 x 10 126。值大于等于1.0 x 10 126时报错。p为有意义的10进制位数，正值s为小数位数，负值s表示四舍五入到小数点左部多少位。
BINARY_FLOAT	5字节，其中有一长度字节。	32位单精度浮点数类型。符号位1位，指数位8位，尾数位23位。
BINARY_DOUBLE	9字节，其中有一长度字节。	64位双精度浮点数类型。

3）时间、时间间隔类型

*时间字段可取值范围：*

时间字段	时间类型有效值	时间间隔类型有效值
YEAR	-4712至9999，包括0	任何整数
MONTH	01至12	0至11
DAY	01至31	任何整数
HOUR	00 至 23	0 至 23
MINUTE	00 至 59	0至 59
SECOND	00 to 59.9(n)，9(n)不适用与DATE类型	0 to 59.9(n)
TIMEZONE_HOUR	-1至14，不适用与DATE和TIMESTAMP类型	不可用
TIMEZONE_MINUTE	00至59，不适用与DATE和TIMESTAMP类型	不可用
TIMEZONE_REGION		不可用
TIMEZONE_ABBR		不可用

*时间、时间间隔类型：*

数据类型	长度	说明
DATE	7字节	默认值为SYSDATE的年、月，日为01。包含一个时间字段，若插入值没有时间字段，则默认值为：00:00:00 or 12:00:00 for 24-hour and 12-hour clock time。没有分秒和时间区。
TIMESTAMP [(fractional_seconds_precision)]	7至11字节	fractional_seconds_precision为Oracle存储秒值小数部分位数，默认为6，可选值为0到9。没有时间区。
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	13字节	使用UTC，包含字段YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	7至11字节	存时使用数据库时区，取时使用回话的时区。
INTERVAL YEAR [(year_precision)] TO MONTH	5字节	包含年、月的时间间隔类型。year_precision是年字段的数字位数，默认为2，可取0至9。
INTERVAL DAY [(day_precision)]TO SECOND [(fractional_seconds_precision)]	11字节	day_precision是月份字段的数字位数，默认为2，可取0至9。

4）大对象类型

数据类型	长度	说明
BLOB	最大为(4GB-1)*数据库块大小	存储非结构化二进制文件。支持事务处理。
CLOB	最大为(4GB-1)*数据库块大小	存储单字节或者多字节字符数据。支持事务处理。
NCLOB	最大为(4GB-1)*数据库块大小	存储Unicode数据。支持事务处理。
BFILE	最大为2 32-1字节	LOB地址指向文件系统上的一个二进制文件，维护目录和文件名。不参与事务处理。只支持只读操作。

5）其他类型

数据类型	长度	说明
LONG	最大为2GB	变长类型，存储字符串。创建表时不要使用该类型。
RAW(n)	最大2000字节，n为字节数，必须指定n	变长类型，字符集发生变化时不会改变值。
LONG RAW	最大为2GB	变长类型，不建议使用，建议转化为BLOB类型，字符集发生变化时不会改变值。
ROWID	10字节	代表记录的地址。显示为18位的字符串。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。
UROWID(n)

DQL语言（数据查询语言）

1.语法

select [TOP|DISTINCT] [选择列表]|[*]
from 数据源
[where 查询条件]
[group by 分组条件]
[having 过滤条件]
[order by 排序条件 asc|desc nulls first|last];

1.1执行顺序

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚合函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。

2.简单查询

--查询所有员工的信息
select * from emp;

3.别名查询

--查询所有员工的姓名
select e.ename from emp e;

4.去重查询

--查询所有部门的编号
select distinct e.deptno from emp e;

5.条件查询

一、运算符

1. 条件运算符：>、>=、<、<=、=、<=>、!=、<>
2. 逻辑运算符：and、or、not
3. 模糊运算符：
   • like：%任意多个字符、_任意单个字符、如果有特殊字符，需要使用escape转义
   • between and
   • not between and
   • in
   • is null
   • is not null

二、演示

--查询工资>3000的员工信息
select * from emp where sal > 3000;

6.分组查询

--统计每个部门有多少个人
select deptno as "部门",count(*) as "人数" from emp group by deptno;

7.分组过滤

--统计部门人数>5人的部门的编号
select deptno as "部门", count(*) as "人数"
from emp
group by deptno
having count(*) > 5;

8.排序查询

--按照员工主管编号由高到低进行排序，NULL值放到最后边
select * from emp order by mgr desc nulls last;

9.分页查询

目的：控制输出结果集大小，将结果尽快的返回

通用格式:

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21  

SELECT * FROMSELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
)  
WHERE RN BETWEEN 21 AND 40   ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

10.多表查询

• 内连接

  • 隐式内连接：select * from emp e1, dept d1 where e1.deptno = d1.deptno;
  • 显示内连接：select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

• 外连接

  • 左外连接

    • 隐式左外连接：select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
    • 显示左外连接：select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;

  • 右外连接

    • 隐式右外连接：select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
    • 显示右外连接：select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;

  • 全外连接：select * from emp e1 full outer join dept d1 on e1.deptno = d1.deptno;

    (+)代表连接方向

    主表的数据可以完全查出来

    左连接：左边的表是主表，右连接：右边的表是主表

    如果(+)在右边，是左连接（左边的表是主表）

    如果(+)在左边，是右连接（右边的表是主表）

• 交叉连接

  • 隐式交叉连接：select * from emp, dept;
  • 显示交叉连接：select * from emp e1 cross join dept d1;

11.联合查询

• 并集运算：将两个查询结果进行合并

/*
	union 		: 它会去除重复的，并且排序
	union all 	: 不会去除重复的，不会排序
*/

--工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union
select * from emp where deptno = 20;

--工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;

• 交集运算：找两个查询结果的交集

--工资大于1500并且20号部门下的员工
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;

• 差集运算：找两个查询结果的差集

--1981年入职员工(不包括总裁和经理)
select * from emp where to_char(hiredate,'yyyy') = '1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';

• 注意事项：

1. 列的类型要一致
2. 列的顺序要一致
3. 列的数量要一致，如果不够，可以使用null填充

12.子查询

• 单行子查询：>、>=、<、<=、!=、<>、=、<=>
• 多行子查询：in、not in、any、some、all、exits

1、in的使用

--查询所有经理的信息
select * from emp where empno in (select mgr from emp where mgr is not null);

2、not in的使用

--查询不是经理的信息
select * from emp where empno not in (select mgr from emp where mgr is not null);

3、any的使用

--查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > any (select sal from emp where deptno = 10);

4、some的使用

--查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > some (select sal from emp where deptno = 10);

5、all的使用

--查询出比20号部门所有员工薪资高的员工信息
select * from emp where sal > all (select sal from emp where deptno = 20);

6、exits的使用

--查询有员工的部门的信息
select * from dept d1 where exists (select * from emp e1 where e1.deptno = d1.deptno);

DCL语言（数据控制语言）

1、创建表空间

一、语法

create tablespace 表空间的名称
datafile '文件的路径'
size 初始化大小
autoextend on
next 每次扩展的大小;

二、演示

create tablespace mytest
datafile 'd:/mytest.dbf'
size 100m
autoextend on
next 10m;

2、删除表空间

一、语法

drop tablespace 表空间的名称;

二、演示

drop tablespace mytest;

3、创建用户

一、语法

create user 用户名
identified by 密码
default tablespace 表空间的名称;

二、演示

create user zhangsan
identified by 123456
default tablespace mytest;

4、删除用户

DROP USER '用户名'@'主机名';

DROP USER 'Summerday'@'localhost';

5、授权用户

一、语法

grant 系统权限列表 to 用户名;
或者
grant 实体权限列表 on 表名称 to 用户名;

二、权限列表

系统权限分类：（系统权限只能由DBA用户授出）

• DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
• RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
• CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

实体权限分类：select、update、insert、alter、index、delete、all

三、演示

grant CONNECT to zhangsan;
或者
grant CONNECT,RESOURCE to zhangsan;
或者
grant CONNECT,RESOURCE,DBA to zhangsan;
或者
grant DBA to zhangsan;
或者
grant all on emp to zhangsan;

6、取消授权

一、语法

revoke 系统权限列表 from 用户名;
或者
revoke 实体权限列表 on 表名称 from 用户名;

二、注意事项

系统权限只能由DBA用户回收

二、演示

revoke CONNECT from zhangsan;
或者
revoke CONNECT,RESOURCE from zhangsan;
或者
revoke CONNECT,RESOURCE,DBA from zhangsan;
或者
revoke DBA from zhangsan;
或者
revoke all on emp from zhangsan;
123456789

7、修改密码

一、语法

alter user 用户名 identified by "密码";

二、演示

alter user zhangsan identified by "123456";

8、查询权限

SHOW GRANTS FOR '用户名'@'主机名';

SHOW GRANTS FOR 'Summerday'@'localhost';

DDL语言

1、创建表

一、语法

create table 表名(
     列名 列的类型 [列的约束],
     列名 列的类型 [列的约束]  
);

二、演示

create table users(
     id number,
     username varchar2(20),
     password varchar2(20)
);

2、复制表

一、语法

create table 表名 as 查询语句;

二、演示

create table emp_copy as 
     select * from emp;

3、删除表

一、语法

方式一：drop table 表名;
方式二：truncate table 表名;

二、演示

方式一：drop table emp_copy;
方式二：truncate table emp_copy;

delete、truncate、drop的区别

delete

1、删除整张表的数据：

delete from table_name;
1

2、删除部分数据，添加where子句：

delete  from table_name where...;
1

3、说明
　　１）、属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项。产生rollback，事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发，如果删除大数据量的表速度会很慢。
　　２）、删除表中数据而不删除表的结构(定义)，同时也不释放空间。

truncate

1、只能操作表，将表中数据全部删除，在功能上和不带where子句的delete语句相同：

truncate table table_name;
1

2、说明
　　１)、默认情况下，truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少，可以使用reuse storage; truncate会将高水线复位(回到最开始).
　　2 )、 truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
　　3 )、删除内容、释放空间但不删除表的结构(定义)。

drop

1、drop语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);

drop table table_name;
1

2、说明
　　1）、删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
　　2）、drop也属于DDL语言，立即执行，执行速度最快
　　3）、删除内容和定义，释放空间。

区别

1、表和索引所占空间：
　　当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小；
　　DELETE操作不会减少表或索引所占用的空间；
　　DROP语句将表所占用的空间全释放掉。
　　
2、应用范围：
　　TRUNCATE 只能对table；
　　DELETE可以是table和view。

3、执行速度：
　　drop > truncate > delete

4、delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。

5、DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
　　TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

6、当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。

7、如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变；
　使用truncate删除之后，种子会恢复到初始值。

总结

1、delete 语句可以使用where子句实现部分删除，而truncate不可以，会将表中的整个数据全部删除，使用时，可以按需求选择；
2、如果想从表中删除所有的数据，不要使用delete，可以使用truncate语句，因为这样执行速度更快。truncate语句实际是删除原来的表然后重新建立一个新表；
3、在没有备份情况下，谨慎使用 drop 与 truncate。要删除表结构使用drop;
4、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

4、修改表

1、添加一列

格式：alter table 表名 add 列名 列的类型;
演示：alter table users add phone varchar2(11);

2、修改列名

格式：alter table 表名 rename column 旧列名 to 新列名;
演示：alter table users rename column phone to mobile;

3、修改类型

格式：alter table 表名 modify 列名 列的类型;
演示：alter table users modify mobile char(11);

4、删除一列

格式：alter table 表名 drop column 列名;
演示：alter table users drop column mobile;

5、修改表名

格式：rename 旧表名 to 新表名;
演示：rename users to myusers;

5、表约束

一、语法

CREATE TABLE 表名(
        列名 列的类型 primary key,--主键约束
        列名 列的类型 not null,--非空约束
        列名 列的类型 unique,--唯一约束
    	列名 列的类型 check(列名 in (检查列表)),--检查约束
        constraint 约束名 foreign key(字段名) references 主表(被引用列)--外键约束
) ;

二、演示

--商品分类表
create table category(
       cid number primary key,
       cname varchar2(20) 
);

--商品详情表
create table product(
       pid number primary key,--主键约束
       pname varchar2(50) not null,--非空约束
       pimg varchar2(50) unique,--唯一约束
       pflag varchar2(10) check(pflag in ('上架','下架')),--检查约束
       cid number,
       constraint FK_CATEGORY_ID foreign key(cid) references category(cid)--外键约束
);

三、修改

1、主键约束

添加
alter table product add constraint PK_PRODUCT_PID primary key(pid);
删除
alter table product drop constraint PK_PRODUCT_PID;
或者
alter table product drop primary key;

2、非空约束

添加
alter table product modify pname not null;
删除
alter table product modify pname null;

3、唯一约束

添加
alter table product add constraint UK_PRODUCT_PIMG unique(pimg);
删除
alter table product drop constraint UK_PRODUCT_PIMG;
或者
alter table product drop unique(pimg);

4、检查约束

添加
alter table product add constraint CK_PRODUCT_PFLAG check(pflag in ('上架','下架'));
删除
alter table product drop constraint CK_PRODUCT_PFLAG;

5、外键约束

添加
alter table product add constraint FK_PRODUCT_ID foreign key(cid) references category(cid);
删除
alter table product drop constraint FK_PRODUCT_ID;

DML语言

1、插入语句

格式：insert into 表名(列名1,列名2,...) values(值1,值2,...);
演示：insert into category(cid,cname) values(1,'电视');
注意：commit;

2、修改语句

格式：update 表名 set 列名1=值1,列名2=值2,... where 查询条件;
演示：update category set cname='汽车' where cid = 1;
注意：commit;

3、删除语句

格式：delete from 表名 where 查询条件;
演示：delete from category where cid = 1;
注意：commit;

TCL语言

1、事务

一、含义

一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行

二、特点（ACID）

1. 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
2. 一致性：一个事务的执行不能破坏数据库数据的完整性和一致性
3. 隔离性：一个事务不受其它事务的干扰，多个事务是互相隔离的
4. 持久性：一个事务一旦提交了，则永久的持久化到本地

三、分类

1、开启事务
Oracle 11g中事务是隐式自动开始的，它不需要用户显示的执行开始事务语句

2、编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete

【设置回滚点】
savepoint 回滚点名;

3、结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方： rollback to 回滚点名;

2、事务并发（读问题）

一、事物的并发问题如何发生？

多个事务同时操作同一个数据库的相同数据时

二、事务的并发问题都有哪些？

1. 脏读：一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样（Oracle中不会产生）
2. 不可重复读：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致
3. 幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样

三、事物的并发问题如何解决？

通过设置隔离级别来解决并发问题

四、隔离级别

Oracle中只支持READ COMMITTED、SERIALIZABLE、READ ONLY、READ WRITE，这些语句是互斥的，不能同时设置两个或两个以上的选项，一般默认即可。

3、丢失更新（写问题）

一、定义

在事务的隔离级别内容中，能够了解到两个不同的事务在并发的时候可能会发生数据的影响。细心的话可以发现事务隔离级别章节中，脏读、不可重复读、幻读三个问题都是由事务A对数据进行修改、增加，事务B总是在做读操作。如果两事务都在对数据进行修改则会导致另外的问题：丢失更新。

二、解决

1. 悲观锁：认为两个事务更新操作一定会发生丢失更新
   • 解决：通过在语句后边添加for update来实现行级上锁，所以又称为“行级锁”，例如：select * from t_account t wheret.id=‘1’ for update;
2. 乐观锁：认为事务不一定会产生丢失更新，让事务进行并发修改，不对事务进行锁定
   • 解决：由程序员自己解决，可以通过给数据表添加自增的version字段或时间戳timestamp，进行数据修改时，数据库会检测version字段或者时间戳是否与原来的一致，若不一致，抛出异常或者重新查询

三、注意

对于账户交易建议直接使用悲观锁，数据库的性能很高，并发度不是很高的场景两者性能没有太大差别。如果是交易减库存的操作可以考虑乐观锁，保证并发度。

函数

Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。 Oracle 数据库中主要使用两种类型的函数：

\1. 单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，

比如：MOD(x,y)返回 x 除以 y 的余数（x 和 y 可以是两个整数，也可以是表中的整数列）。

常用的单行函数有：

字符函数：对字符串操作。

数字函数：对数字进行计算，返回一个数字。

转换函数：可以将一种数据类型转换为另外一种数据类型。

日期函数：对日期和时间进行处理。

\2. 聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。

比如 SUM(x)返回结果集中 x 列的总合。

1、数值型常用函数

ceil(n) 大于或等于数值n的最小整数　　select ceil(10.6) from dual; 11

floor(n) 小于等于数值n的最大整数　 select ceil(10.6) from dual; 10

mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2

power(m,n) m的n次方　　　　　　　　 select power(3,2) from dual; 9

round(n,m) 将n四舍五入,保留小数点后m位　　select round(1234.5678,2) from dual; 1234.57

sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1

sqrt(n) n的平方根　　　　　　　　　select sqrt(25) from dual ; 5

trunc（number[,decimals]）其中： number 待做截取处理的数值；decimals 指明需保留小数点后面的位数，可选 项，忽略它则截去所有的小数部分。 注意：截取时并不对数据进行四舍五入。

​ select trunc(123.567,2) from dual;–123.56,将小数点右边指定位数后面的 截去;
​ select trunc(123.567,-2) from dual;–100,第二个参数可以为负数，表示将 小数点左边指定位数后面的部分截去，即均以0记;
​ select trunc(123.567) from dual;–123,默认截去小数点后面的部分;

2、常用字符函数

initcap(char) 把每个字符串的第一个字符换成大写　　select initicap(‘mr.ecop’) from dual; Mr.Ecop

lower(char) 整个字符串换成小写　　　　　　　　　select lower(‘MR.ecop’) from dual; mr.ecop

replace(char,str1,str2) 字符串中所有str1换成str2 select replace(‘Scott’,‘s’,‘Boy’) from dual; Boycott

substr(char,m,n) 取出从m字符开始的n个字符的子串　　select substr(‘ABCDEF’,2,2) from dual; CD

length(char) 求字符串的长度　　　　select length(‘ACD’) from dual; 3

|| 并置运算符　　　 select ‘ABCD’||‘EFGH’ from dual; ABCDEFGH

3、日期型函数

sysdate 当前日期和时间 select sysdate from dual;

last_day 　本月最后一天 select last_day(sysdate) from dual;

add_months(d,n)　当前日期d后推n个月 select add_months(sysdate,2) from dual;

months_between(d,n) 日期d和n相差月数 select months_between(sysdate,to_date(‘20020812’,‘YYYYMMDD’)) from dual;

next_day(d,day) d后第一周指定day的日期 select next_day(sysdate,‘Monday’) from dual;

day 格式　　有　　‘Monday’ 星期一　　‘Tuesday’ 星期二

‘wednesday’ 　星期三 ‘Thursday’ 星期四 ‘Friday’ 星期五

‘Saturday’ 星期六 ‘Sunday’ 星期日

​ – TRUNC（date）表示截断日期
​ – select trunc(sysdate) from dual 结果是 截止到当日不设置，默认是截止到”日“
​ – select trunc(sysdate,‘year’) from dual; --获取到本年 第一天
​ – select trunc(sysdate,‘month’) from dual; --获取到 本月 第一天
​ – select trunc(sysdate,‘q’) from dual; --获取到本季度 第一天
​ – select to_char(trunc(sysdate),‘yyyy-mm-dd hh24:mi:ss’) from dual; --默认获取到日（当日的零点零分零秒）
​ – select trunc(sysdate,‘iw’) from dual; --本周一
​ – select trunc(sysdate,‘ww’) from dual; – 获取离当前最近的周四，若当天为周四则返回当天，否则返回上周四
​ – select trunc(sysdate,‘day’) from dual; --获取到周（本周第一天，即上周日）
​ – select trunc(sysdate,‘hh24’) from dual; --截取到小时（当前小时，零分零秒）
​ – select trunc(sysdate,‘mi’) from dual; --截取到分（当前分，零秒）

4、特殊格式的日期型函数

Y或YY或YYY 年的最后一位，两位，三位 select to_char(sysdate,‘YYY’) from dual;

Q 季度,1-3月为第一季度　　　　select to_char(sysdate,‘Q’) from dual;

MM 　月份数　　　　　　　　　　　select to_char(sysdate,‘MM’) from dual;

RM 月份的罗马表示 select to_char(sysdate,‘RM’) from dual; IV

month 用9个字符表示的月份名 select to_char(sysdate,‘month’) from dual;

ww 当年第几周　　　　　　　　　select to_char(sysdate,‘ww’) from dual;

w 本月第几周　　　　　　　　　select to_char(sysdate,‘w’) from dual;

DDD 当年第几天,一月一日为001 ,二月一日032 select to_char(sysdate,‘DDD’) from dual;

DD 当月第几天 select to_char(sysdate,‘DD’) from dual;

D 周内第几天 select to_char(sysdate,‘D’) from dual; 如　sunday

DY 周内第几天缩写　　　　　　　select to_char(sysdate,‘DY’) from dual; 如　sun

hh12 12小时制小时数　　　　　　　select to_char(sysdate,‘hh12’) from dual;

hh24 24小时制小时数　　　　　　　select to_char(sysdate,‘hh24’) from dual;

Mi 分钟数　　　　　　　　　　　 select to_char(sysdate,‘Mi’) from dual;

ss 秒数　　　　　　　　　　　　 select to_char(sysdate,‘ss’) from dual;

select to_char(sysdate,‘YYYY-MM-DD HH:24:mi:ss’) from dua;

to_number() 将合法的数字字符串 select to_number(‘88877’) from dual; 88877

to_char() 将数字转换为字符串　 select to_char(88877) from dual; ‘88877’

set serveroupt on;

dbms_output.put_line(‘hello world’)

set heading off 由于正在创建数据文件，不需要表头

set pagesize 0 不需要分页

set linesize 80 设置行的最大尺寸

set echo off 告诉sql plus 在执行语句时，不要回显语句

set feedback off 禁止sql plus 显示有多少满足查询的行被检索到

col sales format 999,999,999

append 添加文本到当前行尾

change/old/new/ 在当前行用新的文本代替旧的文本

change/text 从当前行删除ｗｅｎｂ

del 删除当前行

input text 　在当前行之后添加一行

list 显示缓冲区中的所有行

list n 显示缓冲区中的第n行

list m n 显示m到n

5、字符函数

字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。

\1. CONCAT (char1, char2)

返回连接“char2”的“char1”。

示例 SELECT CONCAT( CONCAT(ename, ’ is a '), job) FROM emp;

\2. INITCAP(string)

将“string”的字符转成大写。

示例 Select INITCAP(ename) from emp;

\3. LOWER (string)

将“string”转成小写。

示例 Select LOWER(ENAME) from emp;

\4. LPAD(char1,n [,char2])

返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。

示例 SELECT LPAD(ename,15,’*’) FROM emp;

\5. LTRIM(string,trim_set)

从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。

示例 SELECT LTRIM(‘abcdab’,‘a’) FROM DUAL;

\6. REPLACE(string, if, then)

用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。

示例 SELECT REPLACE(‘JACK and JUE’,‘J’,‘BL’) FROM DUAL;

\7. RPAD(char1, n [,char2])

返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。

示例 SELECT RPAD(ename,15,’*’) FROM emp;

\8. RTRIM(string,trim_set)

从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。

示例 SELECT RTRIM(‘abcdef’, ‘f’) FROM DUAL;

\9. SOUNDEX(char)

返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。

示例 SELECT ename FROM emp

WHERE SOUNDEX(ename) = SoUNDEX(‘SMYTHE’);

\10. SUBSTR(string, start [,count])

返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，则从“start”开始截取到“string”的尾部。

示例 SELECT SUBSTR(‘ABCDEFGIJKLM’,3,4) FROM DUAL;

\11. TRANSLATE(string, if, then)

“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。

示例 SELECT TRANSLATE(ename,‘AEIOU’, ‘XXXXX’) FROM emp;

\12. UPPER(string)

返回大写的“string”。

示例 SELECT UPPER(‘aptech computer education’) FROM dual;

\13. ASCII(string)

该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。该函数返回 “string”中第一个（最左边）字符的 ASCII 值。

示例 SELECT ASCII(‘APTECH’) from dual;

\14. INSTR (string, set[, start[, occurrence] ] )

该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。

示例 SELECT INSTR(‘aptech is aptech’,‘ap’,1,2) FROM DUAL;

\15. LENGTH(string)

返回“string”的长度值。

示例 SELECT ename, LENGTH(ename) FROM emp

WHERE empno = 7698;

6、聚合函数

–1: AVG(DISTINCT|ALL)

ALL表示对所有的值求平均值,DISTINCT只对不同的值求平均值

SELECT AVG(SAL) FROM SCOTT.EMP;

SELECT AVG(DISTINCT SAL) FROM SCOTT.EMP;

–2: MAX(DISTINCT|ALL)

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

(加不加查询结果一致，不知DISTINCT有什么用途，不同于AVG等聚合函数)

SELECT MAX(DISTINCT SAL) FROM SCOTT.EMP;

SELECT MAX(SAL) FROM SCOTT.EMP

–3: MIN(DISTINCT|ALL)

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

SELECT MIN(SAL) FROM SCOTT.EMP;

SELECT MIN(DISTINCT SAL) FROM SCOTT.EMP;

–4: STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

SELECT STDDEV(SAL) FROM SCOTT.EMP;

SELECT STDDEV(DISTINCT SAL) FROM SCOTT.EMP;

–5: VARIANCE(DISTINCT|ALL)

求协方差 ALL表示对所有的值求协方差,DISTINCT表示只对不同的值求协方差

SELECT VARIANCE(SAL) FROM SCOTT.EMP;

SELECT VARIANCE(DISTINCT SAL) FROM SCOTT.EMP;

–6: SUM(DISTINCT|ALL)

求和 ALL表示对所有值求和，DISTINCT表示只对不同值求和(相同值只取一次)

SELECT SUM(SAL) FROM SCOTT.EMP;

SELECT SUM(DISTINCT SAL) FROM SCOTT.EMP;

–7：COUNT(DISTINCT|ALL)

求记录、数据个数。 ALL对所有记录，数组做统计， DISTINCT只对不同值统计(相同值只取一次)

SELECT COUNT(SAL) FROM SCOTT.EMP;

SELECT COUNT(DISTINCT SAL) FROM SCOTT.EMP;

----8： MEDIAN

求中位数

SELECT MEDIAN(SAL) FROM SCOTT.EMP;

SELECT MEDIAN(DISTINCT SAL) FROM SCOTT.EMP; --错误：DISTINCT 选项在此函数中禁用

15道SQL练习（答案在下期,建议先做一遍）

/*表架构
Student(S#,Sname,Sage,Ssex) 学生表 
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score) 成绩表 
Teacher(T#,Tname) 教师表 
*/

--建表语句 
CREATE TABLE student(s# number, sname varchar(32),sage  number,ssex  varchar(8));
CREATE TABLE course(c# number, cname varchar(32), t# number);
CREATE TABLE sc(s# number,c# number,score number);
CREATE TABLE teacher(t# number,tname varchar(16));

--插入测试数据语句 
insert into Student select 1,N'刘一',18,N'男' from dual union
 select 2,N'钱二',19,N'女' from dual union select 3,N'张三',17,N'男' from dual union 
  select 4,N'李四',18,N'女' from dual union  select 5,N'王五',17,N'男' from dual union  
  select 6,N'赵六',19,N'女' from dual;

insert into Teacher select 1,N'叶平' from dual union all select 2,N'贺高' from dual union 
all select 3,N'杨艳' from dual union all select 4,N'周磊' from dual ;

insert into Course select 1,N'语文',1 from dual union all select 2,N'数学',2 from dual union
 all select 3,N'英语',3 from dual union all select 4,N'物理',4 from dual; 

insert into SC  select 1,1,56 from dual union all  select 1,2,78 from dual union
		 all  select 1,3,67 from dual union all  select 1,4,58 from dual union
 		 all  select 2,1,79 from dual union all  select 2,2,81 from dual union
  		 all  select 2,3,92 from dual union all  select 2,4,68 from dual union
  		 all  select 3,1,91 from dual union all  select 3,2,47 from dual union
   		 all  select 3,3,88 from dual union all  select 3,4,56 from dual union
    	 all  select 4,2,88 from dual union all  select 4,3,90 from dual union
    	 all  select 4,4,93 from dual union all  select 5,1,46 from dual union
    	 all  select 5,3,78 from dual union all  select 5,4,53 from dual union
      	 all  select 6,1,35 from dual union all  select 6,2,68 from dual union
     	 all  select 6,4,71 from dual;

–1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；

–2.查询平均分大于60的学生的学号和平均分

–3.查询所有学生的学号，姓名，选课数，总分

–4.查询姓叶的老师的个数

–5.查询没学过“叶平”老师课的同学的学号、姓名；

–6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；

–7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；

–8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

–9.查询所有课程成绩小于60分的同学的学号、姓名；

–10.查询没有学全所有课的同学的学号、姓名；

–11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

–12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名；

–13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；

–14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

–15.按各科平均成绩从低到高和及格率的百分数从高到低顺序