MySQL基础-04表和视图

MySQL基础-04表和视图

表

创建表

通过 create table 创建表，必须给出下列信息

• 新表的名字
• 表列的名字和定义

creat table tablename
(
	列名1 数据类型 列定义/约束，
	列名2 数据类型 列定义/约束，
	...
)

MySQL中的数据类型

varchar

约束

NULL：

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

注意：NULL值和空串不是一个概念，空串是一个有效的值

主键：

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

primary key(列名)
or
primary key(列名1，列名2,...)

上面这条语句加在列定义完之后

• 主键可以在创建表时定义，也可以在创建表之后定义
• 主键只能使用非NULL值的列

auto_increment:

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的值，从而可以用作主键值。每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。

覆盖auto_increment:你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

确定auto_increment的值:下面的语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句

select last_insert_id()

default

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

不允许使用函数作为默认值，只支持常量

最好是使用默认值而不是NULL值

处理现有的表

• 创建新表时，表名必须不存在，否则会出错
• 如果要覆盖已有的表，SQL要求先删除该表，然后再重建它
• 如果仅想在一个表不存在的时候创建它，可以在表名后加上 if not exists

更新表

使用 ALTER TABLE 语句

理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

更改表的结构，必须给出下列信息：

• 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）
• 所做更改的列表

添加一个列

alter table tablename
add 列名 数据类型 # 必须注明数据类型

删除刚刚添加的列

alter table tablename
drop column 列名 

小心使用ALTER TABLE

使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

drop table tablename

重命名表

rename table old_name to new_name

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

为什么使用视图

• 重用SQL语句。
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
• 使用表的组成部分而不是整个表。
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据（添加和更新数据存在某些限制）

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能 下降得很厉害

视图的规则和限制

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
• 对于可以创建的视图数目没有限制
• 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
• ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句

使用视图

• 视图用 create view 来创建
• 使用 show create view viewname 来查看创建视图的语句
• 删除视图 drop view
• 更新视图可以用先 drop 后 create ，也可以直接用 create or replace view

create view viewname as
select ...
from ...
where ...

更新视图

视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

• 分组
• 连接
• 子查询
• 并
• 聚集函数
• distinct
• 计算列

一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。