MySQL-笔记-07.试图及索引的应用

2024-04-27 04:48:03
开发
33

7.1 索引

7.1.1 索引的概念

7.1.2 索引的类型

7.1 索引

7.1.1 索引的概念

在关系数据库中，索引是一种单独的、物理的、对数据库表中一列或多列的值进行排序的存储结构。

索引是一种数据结构，是对照表、指针表。

索引是为了加速对表中元组的检索而创建的一种分散存储结构。

索引是对表而建立的，由除存放表的数据页面以外的索引页面组成。

索引加快查询速度，减慢更新的速度。

7.1.2 索引的类型

MySQL中的索引主要有以下几种类型：

普通索引：是MySQL中的基本索引类型，无任何限制条件，通常用关键字INDEX或KEY来定义。

唯一索引：与普通索引基本相同，所不同的是，唯一索引所在列的值不能重复，即必须是唯一的，通常使用关键字UNIQUE来定义。

主键：也是一种唯一索引，一个表只能有一个主键。主键一般是在创建表时指定，也可以通过修改表来设置主键，使用关键字PRIMARY KEY来定义。

全文索引：只能在CHAR、VARCHAR或TEXT类型的列上创建全文索引，并且现在只有MyISAM和InnoDB存储引擎支持全文索引。使用关键字FULLTEXT来定义。

空间索引：只能创建在空间数据类型的列上，目前只有MyISAM存储引擎支持空间索引，使用关键字SPATIAL来定义。对于初学者来说，空间索引很少会用到。

7.1.3 索引的管理

7.1.3.1 创建索引

可以在创建数据表的同时创建索引，也可以在已有的数据表上添加索引。使用SQL语句创建索引主要有以下几种方法：

（1）使用create index语句创建索引（该语句不能创建主键。）

基本语法格式如下：

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name 
ON table_name ( column_name[ ( length ) ] [ ASC | DESC ] , … )

【例3.40】在专业表major中mname列创建名为idx_mname的唯一索引。

CREATE TABLE major
(
  mno CHAR(4) primary KEY,
  mname varchar(20)，
  dno char(2),
  constraint fk_dno foreign key (dno) references dept(dno), -- 定义外键dno为dept的主键
  unique index idx_mname(mname)
);

（2）使用CREATE TABLE语句创建索引

基本语法格式如下：

CREATE TABLE table_name
(
column_name data_type [ column_constraint ] ,
… 
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[ index_name ] ( column_name [ ( length ) ] [ ASC | DESC ] , … )
)

【例3.41】在学生表student中的sname列创建名为idx_sname的普通索引，降序排列。

CREATE INDEX idx_sname 
ON student(sname DESC);

【例3.42】在教师表teacher中的tname列和tprof列创建名为idx_tname_tprof的组合索引，按tname升序，tprof降序排列。

CREATE INDEX idx_tname_tprof 
ON teacher (sname , tprof DESC);

（3）使用ALTER TABLE语句创建索引

基本语法格式如下：

ALTER TABLE table_name
ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
index_name ( column_name [ ( length ) ] [ ASC | DESC ] , … )

【例3.43】在课程表course中的cname列上创建普通索引，降序排列。

ALTER TABLE course
ADD INDEX idx_cname(cname DESC);

7.1.3.2 查看索引

可以使用SHOW INDEX语句查看表中创建的索引。基本语法格式如下：

SHOW  (INDEX | INDEXES | KEYS) FROM table_name [ FROM database_name ]

【例3.44】查看数据库school所有索引。

SHOW INDEXS;

【例3.45】查看数据库school中course表上的所有索引。

SHOW INDEX FROM school.course;

7.1.3.3 删除索引

删除索引的方式有两种：

（1）使用DROP INDEX语句删除索引，基本语法格式如下：

DROP INDEX index_name ON table_name

【例3.46】删除教师表teacher的索引idx_tname_tprof。

DROP INDEX idx_tname_tprof ON teacher;

（2）使用ALTER TABLE语句删除索引，基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name

【例3.47】删除课程表course的索引idx_cname。

ALTER TABLE course DROP INDEX idx_cname;

7.2 试图

7.2.1 试图的概念

视图是从一个或多个表（或视图）导出的表。

视图是一个虚拟表，并不包含任何的物理数据，即视图所对应的数据不进行实际存储。

数据库中只存放视图的定义，这些数据仍存放在定义视图的基本表（数据库中永久存储的表）中。

与直接操作基本表相比，使用视图具有以下优点：

1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户进行数据查询和处理。

2）屏蔽数据库的复杂性。用户不必了解复杂数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。

3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，增加了安全性。

4）便于数据共享。各用户可共享数据库的数据，而不必都定义和存储自己所需的数据，这样，同样的数据只需存储一次。

5）可以重新组织数据。以便输出到其他应用程序中。

在创建或使用视图时，应遵守以下规定：

（1）只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名。

（2）不能把规则、默认值或触发器与视图相关联。

（3）允许嵌套视图。

（4）不能基于临时表建立视图。

7.2.2 试图的创建

CREATE VIEW语句用于建立视图，其基本语法格式如下：

CREATE VIEW view_name [ ( column_list ) ]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

【例3.125】在student数据库中创建v_student_1视图，该视图选择学生信息表student中的所有女学生。

CREATE VIEW v_student_1
AS
SELECT * FROM student WHERE ssex='女';

【例3.127】创建学生的平均成绩视图v_student_avg，该视图包括sno（在视图中列名为学号）和平均成绩。

CREATE VIEW v_student_avg
AS
   SELECT sno AS 学号, AVG(grade) AS 平均成绩
   FROM score
   GROUP BY sno;

使用视图不仅可以方便地查询所需要的数据信息，还可以更新基本表的数据，其方式与使用UPDATE、INSERT和DELETE语句在数据表中更新数据一样。

并非所有的视图都可以进行更新操作，使用视图更新数据时，需要注意以下几点：

⑴从多个基本表通过连接操作导出的视图，不允许更新；

⑵对使用了分组、集函数操作的视图，则不允许进行更新操作；

⑶如果视图是从单个基本表通过投影、选择操作导出的，则允许进行更新操作，且语法同基本表。

【例3.128】将v_student_1中学号为202302020110的学生的班级修改为“2023020301”。

UPDATE v_student_1
SET clsno=' 2023020301'
WHERE sno=' 202302020110'

7.2.3 试图的查询

定义视图后，对视图的查询如同基本表的查询操作，不同的是FROM子句中是视图名。

【例3.129】使用视图v_student_1查找student表中的女生。

SELECT * 
FROM v_student_ 1
Where ssex= '女'

【例3.130】查找平均成绩在80分以上学生的学号和平均成绩。

SELECT *
FROM v_student_avg
WHERE 平均成绩>80

7.2.4 试图的修改

(1) 使用create or replace view 语句修改视图

使用CREATEOR REPLACE VIEW语句修改视图的语法和创建视图的一致，执行时先删除数据库中已存在的同名视图，再用新的视图定义语句创建视图。

(2) 使用alterview 语句修改视图，其基本语法格式如下：

ALTER VIEW view_name [ ( column_list ) ]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

说明：ALTER VIEW语句的各项参数与CREATE VIEW语句中的含义相同。

【例3.131】修改v_student_ 1视图。将视图中选择学生信息表student中的所有女学生修改为选择所有男学生。

CREATE OR REPLACE VIEW v_student_1
AS
SELECT * FROM student WHERE ssex='男';

ALTER VIEW v_student_1
AS
SELECT * FROM student WHERE ssex='男'

7.2.5 试图的删除

当不再需要某个存在的视图时，可以删除它。删除视图后，表和视图所基于的数据并不受影响。可以使用DROP VIEW语句删除视图，其基本语法格式如下：

DROP VIEW [ IF EXISTS ] view_name1 [ , view_name2 …]

【例3.132】删除v_student_2视图。

DROP VIEW IF EXISTS v_student_2

原文地址:https://blog.csdn.net/weixin_44867329/article/details/138073021 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1783961256768704512.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部