1、常见的数据库对象
TABLE
数据字典：即系统表？
约束CONSTRAINT：执行数据校验的规则，用于保护数据完整性的规则
视图VIEW:一个或多个数据表里数据的逻辑显示
索引INDEX:用于提高查询性能，相当于书的目录
存储过程PROCEDURE:用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数FUNCTION:用于完成一次特定的计算，具有一个返回值
触发器TRIGGER:相当于事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理
2、视图概况
使用场景：
1.使用表的一部分而不是所有表，查询效率
2.针对不同用户制定不同的查询视图
概念：
1. 虚拟表，本身不具有数据
2. 建立在已有表的基础上，视图赖以建立的这些表称为基表
3. 视图的创建&删除只影响视图本身，不影响视图；视图中数据的增删改则会同步基表
4. 向用户提供基表数据的另一形式；
优点：
1.操作简单：经常将查询操作定义为视图，开发人员可以不关注表与表关联关系、表结构
2.减少数据冗余：本身不存储数据
3.数据安全：用户权限
4.适应多变的需求：视图减少改动的工作量
5.分解复杂查询逻辑
缺点：
基表结构变更，需要及时维护视图，增加维护成本
3、创建视图
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名称[(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
精简版
CREATE VIEW 视图名称 AS 查询语句;
在创建视图时，若没有指定字段列表，则默认与查询语句相同
4、查看视图
mysql> USE test;
Database changed
mysql> SHOW TABLES; # 查看数据库表对象、视图对象
+---------------------------+
| Tables_in_test |
+---------------------------+
| aaa |
| course |
| score |
| student |
| student_course_score_view |
| teacher |
+---------------------------+
6 rows in set (0.00 sec)
mysql> DESC student_course_score_view; # 查看视图结构，全称写DESCRIBE
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | varchar(10) | NO | | NULL | |
| studentName | varchar(20) | YES | | NULL | |
| scoreName | varchar(10) | NO | | NULL | |
| score | int(11) | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> DESCRIBE student_course_score_view; # 查看视图结构，简写DESC
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | varchar(10) | NO | | NULL | |
| studentName | varchar(20) | YES | | NULL | |
| scoreName | varchar(10) | NO | | NULL | |
| score | int(11) | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE student_course_score_view;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'student_course_score_view' at line 1
mysql> SHOW TABLE STATUS LIKE 'student_course_score_view'; # 查看视图的属性信息，注意视图名称前后的引号
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| student_course_score_view | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql> SHOW CREATE VIEW student_course_score_view; # 查看视图的详细定义信息
+---------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+---------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| student_course_score_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_course_score_view` AS (select `s`.`id` AS `id`,`s`.`name` AS `studentName`,`c`.`name` AS `scoreName`,`sc`.`score` AS `score` from ((`student` `s` join `score` `sc` on((`sc`.`student_id` = `s`.`id`))) join `course` `c` on((`c`.`id` = `sc`.`course_id`))) order by `s`.`id`,`sc`.`score`) | utf8mb4 | utf8mb4_general_ci |
+---------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql>
5、更新视图数据
一般支持INSERT、UPDATE、DELETE
不可更新的视图：
1.ALGORITHM = TEMPTABLE=>insert、delete均不支持
2.查询语句中使用了JOIN=>insert、delete均不支持
3. 查询语句中，存在数学表达式或DISTINCT、聚合函数、GROUP BY 、HAVING、UNION等=>insert update delete均不支持
4. 查询语句中包括子查询，且子查询引用了FROM后的表=>insert update delete均不支持
5. 基于一个不可更新视图，如常量视图
6. 视图中不包含基表中所有被定义非空又未指定默认值的列=>insert update delete均不支持
=>不建议针对视图数据修改
mysql> UPDATE student_course_score_view SET score=70 WHERE id='1003'and sorceName='UML';
ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
6、修改/删除视图结构
-
CREATE OR REPLACE VIEW ……;可以修改视图
2.ALTER VIEW 视图名称 AS 查询语句; -
DROP VIEW [IF EXISTS] 视图名称;# 删除视图，但不会删除基表的数据
基于视图a,创建的新视图b,在视图a删除后，视图b需要手动删除或修改，否则无法使用
网友评论