Oracle约束 SQL语句分类 集合运算 索引 相关知识点讲解

ORACLE 约束：

数据库用来确保数据满足业务的手段，强制数据符合某些特定条件或者规范才可以插入

1. 主键约束： primary key PK
2. 唯一约束：unique
3. 非空约束：not null
4. 外键约束：foreign key FK
5. 检查约束： check
6. 默认约束： default

主键约束：

主键是唯一表示某一行的数据方法。
特点：唯一、非空、一张表只有一个主键、主键列必须有索引，如果没有会自动创建索引。
语法：

-- 创建表时添加 ：
CREATE TABLE 表名(
列名  类型 pirimary key，
...
)

CREATE TABLE 表2(
列名1 类型，
列名2 类型，
... ,
CONSTRAINT 约束名 primary key(列名)
)
-- 创建表之后添加：
-- 表创建之后添加约束可以借助于图形界面
-- 也可以通过ALTER TABLE 去添加、修改约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名)

-- 为了统一规范，一般通过  表名_关键词来作为约束名)

唯一约束：

唯一约束可以作用在一列或者多列上，用于确保数据的唯一性。
特点：唯一、会默认创建索引、唯一约束允许为null（多个null）

外键约束：

外键约束定义在具有父子关系的子表中，外键约束使子表中的列必须对应父表中的主键列。用于确保数据的完整性。
特点： 外键约束的子表中的列和附表中的主键类型一致， 列名可以不同
对应父类中的列必须是主键或者唯一键
外键约束的列允许为null
语法：

 CREATE TABLE 表名(
列   类型  REFERENCES 父表(父表对应的主键的列)
)
-- 创建表之后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 关键词(子表列) REFERENCES 父表(父表的类)

---

案例：

--先建父表，后建子表

CREATE TABLE TYPE(
ID NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(200) UNIQUE
)

CREATE TABLE GOODZ(
ID NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(200),
PRICE NUMBER(9, 2),
--外键
TYPEID NUMBER(5) REFERENCES TYPE(ID)
)

ALTER TABLE GOODZ ADD CONSTRAINT
GOODZ_FK FOREIGN KEY(T_ID)
REFERENCES TYPE(ID)

注：
创建主外键约束时，先创建父表后创建子表
删除表： 先删除子表后删除父表
删除表中的数据时： 先删除子表数据再删除父表中的数据

检查约束：

检查约束可以用于给指定的列进行一些简单的校验。 比如：性别 男\女
设定某一些值的特定范围
例子：

CREATE TABLE 表(
SEX VARCHAR2(10) CHECK(SEX IN ('男', '女'))，
AGE NUMBER(4) CHECK(AGE > 0 AND AGE <= 100)
)

约束目的：保证存入数据库的数据是正确的、有意义的数据

---

SQL语句分类

DDL（Data Defintion Language） 数据定义语言： 用来定义和修改数据库对象（表结构）
包括：CREATE DROP ALTER TRUNCATE(截断表)
DDL操作是隐式提交事务， 不需要手动提交，也不能使用rollback
DML(Data Manipulation Language) 数据操作语言：
包括：INSERT DELETE UPDATE
每一次操作都需要做事务 commit / rollback
DCL(Data Control Language) 数据控制语言：：用于控制数据库权限
包括：GRANT 授权 REVOKE 撤销
GRANT CONNECT, RESOURCE TO ADMIN
REVOKE CONNECT TO ADMIN
TCL(Transaction Control Language) 事务控制语言： commit / rollback
DQL(Data Query Language) 数据查询语言: SELECT

删除表

DELETE 和 DROP 和 TRUNCATE 区别？

delete：只删除表数据，不能删除表定义，不释放空间，可以提交或回滚事务，还会触发触发器。
truncate：使用语法类似于drop， 也是删除表中的数据，不需要提交事务, 不能删除表的定义，
可以释放空间（相当于把表删掉，又创建了一张新的表）。
drop：删除表，会删除表中的定义，并且会释放空间，也不需要提交事务。

速度：DROP > TRUNCATE > DELETE

---

集合运算：

并集 UNION 和 UNION ALL

UNION：将两个结果集合并， 去重， 会按照默认排序规则排序
UNION ALL : 将两个结果集合并， 不去重， 不排序

注：合并的两个结果集， 列的类型和个数相同， UNION 也可以作为行转列来使用

行转列：

涉及的函数：

DECODE(列名，值1，结果1，… [，缺省值])；
CASE 列 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 [ELSE 缺省值] END

SELECT NAME, SUM(CASE COURSE WHEN '语文'
THEN SCORE ELSE 0 END) 语文,
  SUM(CASE COURSE WHEN '数学'
THEN SCORE ELSE 0 END) 数学,
  SUM(CASE COURSE WHEN '英语'
THEN SCORE ELSE 0 END) 英语
FROM TESTTABLE
GROUP BY NAME

列转行：

SELECT NAME, '语文' COURSE, CH_SCORE 
FROM TESTTABLE2

UNION ALL

SELECT NAME, '数学' COURSE, MA_SCORE 
FROM TESTTABLE2

UNION ALL

SELECT NAME, '英语' COURSE, EN_SCORE 
FROM TESTTABLE2

交集 INTERSECT :

获取两个结果集相同的部分 语法类似于并集

差集 MINUS:

两个结果集不同的部分

---

索引：

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。
语法：CREATE INDEX 索引名 ON 表名(列[，列2，列3]) [tablespace 表空间名]
注： 主键和唯一键会默认创建索引
使用：1.如果查询时使用了添加索引的列作为查询条件，则数据库会默认走索引
          2.如果对多列建立索引，代表联合索引，查询时条件顺序是创建索引的顺序来走索引。

EXPLAIN PLAN F5 如果走了索引，就看到了索引的执行计划。

常见不走索引的情况：

1 . like 使用%进行模糊查询 不走索引
（如：列 like ‘%xxxx’ 不走索引     列 like ‘xxx%’ 走索引）
2.使用 is null 或 is not null 不走索引
3.where子句中使用了函数 不走索引
4.使用<>, not 不走索引

什么样子的列适合加索引？

非空、唯一 且不经常修改的数据还有经常作为查询条件的数据。
总结： 索引并不是越多越好， 索引可以增加查询的效率，但是会降低增删改的效率。