数据库圣经--Alter & 视图

ALTER：修改表结构

• 用途：调整表的结构（表的 “框架”），比如添加列、删除列、修改列类型、重命名表等。
• 语法示例：

-- 给表添加列（之前的例子）
ALTER TABLE `java113`.`student2` ADD COLUMN `age` int NULL AFTER `sn`;

-- 修改列的数据类型
ALTER TABLE `student2` MODIFY COLUMN `age` tinyint;

-- 删除列
ALTER TABLE `student2` DROP COLUMN `age`;

ALTER 是 SQL 中用来修改数据库表结构的命令，它允许你在已存在的表中添加、删除或修改列、约束等结构。主要用法如下：

1. 添加列 (ADD)

你可以用 ALTER TABLE 来添加新的列到已有的表。

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name ADD column_name datatype;

例如，在 employees 表中添加一个 age 列：

ALTER TABLE employees ADD age INT;

ALTER TABLE employees ADD age INT;

2. 删除列 (DROP)

DROP 子句允许你从表中删除某个列。删除列时，所有数据也会被删除。

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name DROP COLUMN column_name;

例如，删除 employees 表中的 age 列：

ALTER TABLE employees DROP COLUMN age;

ALTER TABLE employees DROP COLUMN age;

3. 修改列 (MODIFY 或 CHANGE)

使用 ALTER TABLE 可以修改现有列的数据类型或名称。

修改列的数据类型： 使用 MODIFY 来更改列的数据类型。例如，将 age 列的数据类型从 INT 修改为 BIGINT：

ALTER TABLE employees MODIFY age BIGINT;

ALTER TABLE employees MODIFY age BIGINT;

更改列名： 使用 CHANGE 来更改列名，同时可以指定新的数据类型。例如，将 age 列的名称改为 years，并改变数据类型：

ALTER TABLE employees CHANGE age years INT;

ALTER TABLE employees CHANGE age years INT;

需要注意，CHANGE 需要指定新的数据类型，即使你只打算改列名。

4. 添加约束 (ADD CONSTRAINT)

ALTER TABLE 也允许你添加各种约束，如 PRIMARY KEY、FOREIGN KEY、UNIQUE 等。

添加主键 (PRIMARY KEY)：

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

添加外键 (FOREIGN KEY)：

ALTER TABLE table_name ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);

ALTER TABLE table_name ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);

5. 删除约束 (DROP CONSTRAINT)

你可以用 ALTER TABLE 删除已存在的约束。需要指定约束的名称。

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

例如，删除外键约束：

ALTER TABLE orders DROP CONSTRAINT fk_customer;

ALTER TABLE orders DROP CONSTRAINT fk_customer;

6. 重命名表 (RENAME TO)

ALTER TABLE 还可以用于重命名整个表。

ALTER TABLE old_table_name RENAME TO new_table_name;

ALTER TABLE old_table_name RENAME TO new_table_name;

例如，将 employees 表改名为 staff：

ALTER TABLE employees RENAME TO staff;

ALTER TABLE employees RENAME TO staff;

7. 修改表的存储引擎

某些数据库（如 MySQL）允许你修改表的存储引擎。

ALTER TABLE table_name ENGINE = new_engine;

ALTER TABLE table_name ENGINE = new_engine;

例如，将 employees 表的存储引擎改为 InnoDB：

ALTER TABLE employees ENGINE = InnoDB;

ALTER TABLE employees ENGINE = InnoDB;

8. 修改表的字符集

你也可以通过 ALTER 修改表的字符集和排序规则。

ALTER TABLE table_name CHARACTER SET charset_name COLLATE collation_name;

ALTER TABLE table_name CHARACTER SET charset_name COLLATE collation_name;

例如，将 employees 表的字符集改为 utf8mb4，并设置排序规则为 utf8mb4_unicode_ci：

ALTER TABLE employees CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE employees CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SQL执行语句执行顺序：

视图

1.概念

视图是⼀个虚拟的表，它是基于⼀个或多个基本表或其他视图的查询结果集。

视图本⾝不存储数 据，⽽是通过执⾏查询来动态⽣成数据。用户可以像操作普通表⼀样使用视图进⾏查询、更新和管理。视图本⾝并不占⽤物理存储空间，它仅仅是⼀个查询的逻辑表⽰，物理上它依赖于基础表中的数据。

CREATE VIEW view_name [(column_list)] AS select_statement

1. 语法部分：CREATE VIEW作用：这是创建视图的关键字，用于标识当前执行的是 “视图创建” 操作。
2. 语法部分：view_name作用：指自定义的视图名称，后续可以像操作普通数据库表一样，直接调用这个名称来使用视图。
3. 语法部分：(column_list)作用：这是可选的语法部分，用于指定视图包含的列名；如果省略这部分，视图的列名会默认使用后续select_statement（查询语句）中的列名称。
4. 语法部分：AS select_statement作用：用于定义视图的数据来源 —— 视图展示的内容，是该 SELECT 查询语句执行后得到的结果集。

2.查看视图：

show tables;
//查看这个创建视图的语句
show create view view—name;
//查看创建好的这个视图
select * from view_name;

示例：

CREATE VIEW v_student_score_v1 (
    id, name, class_id, class_name, course_id, course_name, score
) AS (
    SELECT 
        s.student_id,
        s.`name`,
        cls.class_id,
        cls.`name`,
        c.course_id,
        c.`name`,
        sc.score
    FROM student s, class cls, course c, score sc
    WHERE s.class_id = cls.class_id
      AND sc.student_id = s.student_id
      AND sc.course_id = c.course_id
);

当然我们创建好了，可以自定义排序：

3. 修改数据

• 通过真实表修改数据，会影响视图

mysql> update score set score = 99 where student_id = 1 and course_id = 1;
 Query OK, 2 rows affected (0.01 sec)
 Rows matched: 2  Changed: 2  Warnings: 0
 # 查询视图，发现唐三藏这条记录已被修改
mysql> select * from v_student_socre;

• 通过视图修改数据会影响基表

##修改唐三藏的计算机⽹络成绩为99分
mysql> update v_student_socre set score = 99 where score_id = 3;
 ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
 # 发现更新失败，因为创建视图时使⽤了
order by 语句
# 更新视图
mysql> update v_student_socre_v1 set score = 99 where score_id = 3;
 Query OK, 1 row affected (0.01 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 # 是看真实表数据已被修改
select * from score where student_id = 1 and course_id = 5;

4. 注意事项

• 修改真实表会影响视图，修改视图同样也会影响真实表
• 以下视图不可更新：
  • 创建视图时使用聚合函数的视图
  • 创建视图时使用 DISTINCT
  • 创建视图时使用 GROUP BY 以及 HAVING 子句
  • 创建视图时使用 UNION 或 UNION ALL
  • 查询列表中使用子查询
  • 在 FROM 子句中引用不可更新视图

解释：如果你创建视图的时候，在 FROM 后面又用了一个已经属于“不可更新视图”的视图，那么你新创建的这个视图，也一定不可更新。

5.删除视图

 # 语法
drop view view_name；

6. 视图的优点

1. 简单性：视图可以将复杂的查询封装成⼀个简单的查询。例如，针对⼀个复杂的多表连接查询，可 以创建⼀个视图，用户只需查询视图而无需了解底层的复杂逻辑。

2. 安全性：通过视图，可以隐藏表中的敏感数据。例如，⼀个系统的用户表中，可以创建⼀个不包含 密码列视图，普通用户只能访问这个视图，而不能访问原始表。

3. 逻辑数据独立性：视图提供了⼀种逻辑数据独立性，即使底层表结构发生变化，只需修改视图定 义，而无需修改依赖视图的应用程序。使用到应⽤程序与数据库的解耦

• 物理独立性（底层存储变，比如从硬盘换 SSD）：视图不直接管这个，数据库自动处理。
• 逻辑独立性（表结构变，比如加列、拆表）：视图超级擅长！它让你的程序只看到“逻辑视图”，不管底层逻辑怎么重组。

4. 重命名列：视图允许用户重命名列名，以增强数据可读性。