目录
视图(View)是数据库中的虚拟表,是由一个或多个基本表(或其他视图)的行或列组成的。视图并不在数据库中占用存储空间,并且可以提供一种逻辑上的数据组织方式,将数据按照某种条件过滤、划分或组合,以方便用户查询和使用数据。
含义:
虚拟表,和普通表一样使用
数据安全性:视图可以限制用户对数据的访问权限。通过只向用户开放特定的视图,可以隐藏实际存储数据的表和列,保护敏感信息的安全性。视图还可以根据用户的角色和权限,提供不同级别的数据访问控制。
数据的逻辑组织和定制:视图可以对基本表进行逻辑上的组织和定制,以满足用户的特定需求。通过视图,可以对数据进行过滤、排序、聚合和连接等操作,将数据以一种用户友好的方式展示出来,减少了用户对底层数据结构的了解和操作。
数据的简化和抽象:视图可以简化复杂的查询操作,将一系列的数据操作组合在一起提供给用户,使用户能够更方便地进行数据的查询和分析。通过视图,用户可以从多个表中获取所需的信息,而无需编写复杂的JOIN和WHERE子句。
逻辑数据独立性:视图可以提供逻辑数据独立性,使应用程序和数据库之间的关联变得松耦合。如果底层表结构发生变化,只需调整视图的定义即可,而不需要修改应用程序中使用视图的相关代码。
性能提升:视图可以提供对常用查询的优化,通过将查询的结果存储在视图中,可以减少复杂的计算和数据访问操作,提高查询的性能。
创建视图语法:
carete view 视图名
as
查询语句
方式一:
create or replace view 视图名
as
查询语句
方式二:
alter view 视图名
as
查询语句
删除视图的语法:
drop view 视图名,视图名........
查看视图相关字段
DESC 视图名
查看视图相关语句
SHOW CREATE VIEW 视图名?
索引是数据库中用于加快数据检索速度的一种数据结构。它类似于书籍的目录,通过在关键字和对应数据位置之间建立的映射关系,可以帮助数据库系统更快地定位和访问特定的数据记录。索引通常是在数据库表的一列或多列上创建的,以提高对这些列的查询速度。
索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录。
- 提高查询速度:索引可以大大加快数据的检索速度,特别是在大型数据表中。
- 加速排序:使用索引可以加速ORDER BY子句的查询,使数据按照特定的列进行快速排序。
- 加速连接:在进行表的连接操作时,如果连接的列上有索引,可以提高连接的速度。
- 提高唯一性约束:可以创建唯一索引来强制单个列或多列的唯一性约束。
- 占用存储空间:索引需要额外的存储空间,这会增加数据库的存储成本。
- 维护成本:在数据插入、更新和删除时,索引的维护也会增加相应的开销,可能会影响性能。
- 复杂查询的代价:在执行查询时,如果使用了多个索引,可能需要对多个索引进行查找和合并,这可能会导致查询性能下降。
- 索引失效:当数据库中的数据量较小时,使用索引可能会导致优化器不选择索引,反而影响查询性能。
- 索引选择错误:选择不当的索引可能导致查询性能降低,需要权衡查询需求与索引选择。
单列索引(Single-Column Index):针对单个列创建的索引。最为常见的索引类型,可用于加快单列查询速度。
复合索引(Composite Index):针对多个列组合创建的索引。通过在多个列上创建索引可以提高复合条件的查询速度,同时也提供了对这些列的单列查询的优化。
唯一索引(Unique Index):确保索引列的值全部唯一的索引。用于实施唯一性约束,防止表中出现重复的值。
主键索引(Primary Key Index):主键在数据库表中起到唯一标识记录的作用,数据库系统通常会对主键列自动创建主键索引,以确保主键的快速检索。
外键索引(Foreign Key Index):在包含外键的列上创建的索引,用于加快外键关联表之间的连接和查询操作。
全文索引(Full-Text Index):针对文本类型的列进行的特殊索引,用于支持全文搜索和匹配。
聚集索引(Clustered Index):数据行的物理顺序与索引的逻辑顺序一致的索引。在聚集索引中,索引本身就是数据的排列顺序,因此表中的数据行实际上就是按照这个顺序进行存储的。
非聚集索引(Non-Clustered Index):索引中的顺序与数据行的物理存储不同。在非聚集索引中,索引和数据行是分开存储的,索引的叶子节点中存储的是指向数据行的指针或引用。
01)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
?? ?*?
FROM
?? ?t_mysql_student s,
?? ?( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
?? ?( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2?
WHERE
?? ?s.sid = t1.sid?
?? ?AND t1.sid = t2.sid?
?? ?AND t1.score > t2.score
?查询结果
02)查询同时存在" 01 "课程和" 02 "课程的情况
SELECT
?? ?s.*,
?? ?( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
?? ?( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学?
FROM
?? ?t_mysql_student s,
?? ?( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
?? ?( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2?
WHERE
?? ?s.sid = t1.sid?
?? ?AND t1.sid = t2.sid
?查询结果
03)查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT
?? ?*?
FROM
?? ?( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
?? ?LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid;
SELECT
?? ?s.*,
?? ?( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
?? ?( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学?
FROM
?? ?t_mysql_student s
?? ?INNER JOIN ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1 ON s.sid = t1.sid
?? ?LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid;?
?查询结果
04)查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT
?? ?s.*,
?? ?( CASE WHEN sc.cid = '01' THEN sc.score END ) 语文,
?? ?( CASE WHEN sc.cid = '02' THEN sc.score END ) 数学?
FROM
?? ?t_mysql_student s,
?? ?t_mysql_score sc?
WHERE
?? ?s.sid = sc.sid?
?? ?AND s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' )?
?? ?AND sc.cid = '02'
查询结果
05)查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT
?? ?s.sid,
?? ?s.sname,
?? ?ROUND( avg( sc.score ), 2 ) n?
FROM
?? ?t_mysql_student s
?? ?LEFT JOIN t_mysql_score sc ON s.sid = sc.sid?
GROUP BY
?? ?s.sid,
?? ?s.sname?
HAVING
?? ?n >= 60
查询结果
06)查询在t_mysql_score表存在成绩的学生信息
SELECT
?? ?s.sid,
?? ?s.sname?
FROM
?? ?t_mysql_student s
?? ?LEFT JOIN t_mysql_score sc ON s.sid = sc.sid?
GROUP BY
?? ?s.sid,
?? ?s.sname
查询结果
07)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT
?? ?s.sid,
?? ?s.sname,
?? ?count( sc.score ) 选课总数,
?? ?sum( sc.score ) 总成绩?
FROM
?? ?t_mysql_student s
?? ?LEFT JOIN t_mysql_score sc ON s.sid = sc.sid?
GROUP BY
?? ?s.sid,
?? ?s.sname
查询结果