视图、序列、索引

  • Z时代
  • 2024-01-10
  • 分类：综合

database

使用视图的两种情况：

  1. 使用的查询基于非常复杂的查询之上。

  2. 查询的时候希望屏蔽数据库中真实的字段名和数据

CREATE [OR REPLAC] VIEW view_name[(alias[, alias...])] AS subquery [WITH CHECK OPTION];

视图创建后，主要是查询Subquery是SELECT查询语句，对应的表称为基表。
根据所对应的子查询种类分为：简单视图，复杂视图，连接视图。

先根据我们需要查询的视图所对应的SQL将数据检索出来，然后再根据我们对视图查询的需求，检索视图查询出来的数据，所以使用视图是两次查询。

极其不推荐对视图进行DML操作

视图中没有出现的字段，插入的时候都为NULL，所以有非空约束的时候，插入失败。
WITH CHECK OPTION具有检查约束的功能。

还可以创建只读视图。

CREATE [OR REPLAC] VIEW view_name[(alias[, alias...])] AS subquery [WITH CHECK OPTION];

创建复杂视图必须为子查询的表达式或函数定义别名。

删除视图：DROP VIEW view_name;

通过查询user_views获取相关信息

和视图相关的数据字典：

  • USER_OBJECTS 保存用户的所有数据库对象，表，视图，索引，序列等

  • USER_VIEWS 保存了用户所创建的视图

  • USER_UPDATE_COLUMNS

MySQL的字典不同。

序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象；通常我们用来生成主键。
序列是独立的数据库对象，和表是独立的对象，序列并不依附于表；
通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值。尽可能不要共用，避免主键断裂。

CREATE SEQUENCE [schema.]sequence_name
[START WITH i] [INCREMENT BY j]
[MAXVALUE m|NOMAXVALUE]
[MIN VALUE n|NOMIN VALUE]
[CYCLE|NOCYCLE][CACHE p|NOCACHE]

CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10;

sequence_name是序列名，将创建在schema方案下；
序列的第一个序列值是i ,步进是j；
如果j是正数，表示递增，如果是负数，表示递减。
START WITH默认值是1，INCREMENT BY默认值是1，NOCYCLE默认，CACHE默认开启，默认值是20；

**MySQL自增长与Oracle序列的区别:
自增长只能用于表中的其中一个字段
自增长只能被分配给固定表的固定的某一字段，不能被多个表共用.
自增长会把一个未指定或NULL值的字段自动填上.
mysql的AUTO_INCREMENT可以设置起始值，但是不能设置步长，其步长默认就是1. **

序列有两种伪列：

  • NEXTVAL：获取序列的下一个值；

  • CURRVAL：获取序列的当前值；

注意，序列创建以后，必须先执行一次NEXTVAL才能使用CURRVAL。

删除序列：
DROP SEQUENCE sequence_name;

索引

索引是用来加快对某一张表的查询效率的。

CREATE [UNIQUE] INDEX index.name ON table_name(column[, column...]);
index_name表示索引名称
table_name表示表名
column表示列名，可以建立单列索引或复合索引，也可以是函数。
UNIQUES表示唯一索引

CREATE INDEX idx_emp_ename ON emp(ename);
CREATE INDEX idx_emp_job_sal ON emp(job,sal);
CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename));

查询的时候自动使用索引。

修改重建索引：
ALTER INDEX index_name REBUILD；

删除索引：
DROP INDEX index_name;

合理使用索引提升查询效率：

  • 为经常出现在WHERE子句中的列创建索引

  • 为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致

  • 为经常作为表的连接条件的列上创建索引

  • 不要在经常做DML操作的表上建立索引

  • 不要在小表上建立索引

  • 限制表上的索引数目，索引并不是越多越好

  • 删除很少被使用的、不合理的索引

  • 数据访问很频繁，检索所占的比重占2%~4%，宜加索引

  • 检索的字段有大量的null时

约束

约束（CONSTRAINT)的全称是约束条件，也称作完整性约束条件。

约束条件包括：

  • 非空约束(Not Null),简称NN

  • 唯一性约束(Unique),简称UK

  • 主键约束(Primary Key),简称PK

  • 外键约束(Foreign Key),简称FK

  • 检查约束(Check),简称CK

非空约束

建表是添加NOT NULL，NOT NULL只能在声明和修改时设置非空约束。
可以如下方法添加：

CREATE TABLE employees(
eid INT(6),
name VARCHAR(30) NOT NULL,
salary FLOAT(7,2),
hiredate DATE 
CONSTRAINT employees_hiredate_nn NOT NULL
);

NOT NULL是列级约束，在声明列的同时加的约束。

表级约束：单独定义的约束（定义列之后）。

修改表是添加非空约束：
可以通过修改表的定义，添加非空约束：

ALTER TABLE employees MODIFY eid INT(6) NOT NULL;

取消非空约束：

ALTER TABLE employees MODIFY eid INT(6)NULL;

唯一性约束

唯一性（Unique）保证字段不出现重复值。
允许有NULL，唯一性可以是列级，也可以是表级。null不是任何值，可以有多个null。

CREATE TABLE employees(
--以下是列级约束
eid INT(6) UNIQUE,
name VARCHAR(30),
email VARCHAR(50),
salary FLOAT(7,2),
hiredate DATE,
--以下是表级约束
CONSTRAINT employees_email_uk UNIQUE(email)
);

NOT NULL是列级约束，列级约束只能设置一个，所以列级的时候设置NOT NULL，再将UNIQUE设置为表级约束

建表后添加唯一性约束条件：

ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name);

主键约束

主键(Primary Key)约束条件非空 (NUT NULL)且唯一（UNIQUE)，一个表只允许一个主键约束。

主键应是对系统无意义的数据；

主键不应该包含动态变化的数据，如时间戳；

主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义；

常见的主键生成的形式：序列，UUID，

ORACLE生成UUID
SELECTsys_guid()FROM DUAL;
INSERTINTO emp VALUES(sys_guid(),"xxxx"....);

添加主键约束：

CREATE TABLE employees2(
eid INT(6) PRIMARY KEY,
name VARCHAR(30));
也可以采用表级约束：
CONSTRAINT employees2_eid_pk PRIMARY KEY(eid);

添加主键约束：

ALTER TABLE employees3 ADD CONSTRAINT
employees_eid_pk PRIMARY KEY(eid);

外键约束

外键保存的是另一张表的主键。
有外键的是子表或从表，对应的表就是父表或者成为主表。

CREATE TABLE employees4(
eid INT(6),
name VARCHAR(30),
deptno INT(4),
CONSTRAINT employees4_deptno_fk 
FOREIGN KEY(deptno) #本表的字段
 REFERENCES dept(deptno) #主表的主键
);

关联不一定需要外键约束：

  • 保证数据完整性可以由程序或触发器控制

  • 简化开发，维护数据时不用考虑外键约束

  • 大量数据DML操作时不需考虑外键耗费时间

检查约束

检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。
例如：

ALTER TABLE employees4
ADD CONSTRAINT employees4_salary_check
CHECK(salary >2000);

CHECK括号中表达是必须是一个Boolean表达式

多对多

两张表要想形成多对多关系，要引入一张关联关系表。关联关系表不需要主键。只需要两个字段，分别记录两张表的主键。


链接：https://www.jianshu.com/p/6abcd7979424

以上是 视图、序列、索引 的全部内容， 来源链接： utcz.com/z/532285.html

回到顶部