表和视图

视图和表的创建、修改和删除是DDL 还是 DML 呢 ？

表

在 03 创建数据库和表 中，我们就已经创建了一张表了。现在我们再好好学习一次。

一般有两种创建表的方法

• 使用具有交互式创建和管理表的工具 （比如图形客户端）

• 表也可以直接用MySQL语句操纵

利用CREATE TABLE创建表，必须给出下列信息：

• 新表的名字，在关键字CREATE TABLE之后给出

• 表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节

示例，创建 的customers 表

CREATE TABLE customers (
  cust_id int(11) NOT NULL AUTO_INCREMENT,
  cust_name char(50)  NOT NULL,
  cust_address char(50)  ,
  cust_city char(50)   ,
  cust_state char(5)   ,
  cust_zip char(10)   ,
  cust_country char(50)   ,
  cust_contact char(50)   ,
  cust_email char(255)   ,
  PRIMARY KEY (cust_id) 
) ENGINE = InnoDB

从上面的例子中可以看到，表名紧跟在CREATE TABLE关键字后面。实际的表定义（所有列）括在圆括号之中。各列之间用逗号分隔

表由 9 列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型

表的主键可以在创建表时用 PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列

使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定

cust_name char(50)  NOT NULL

NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL

不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定''（两个单引号，其间没有字符） ，这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定

主键

主键值必须唯一，可以在创建表的时候定义，也可以在表创建后再创建

主键的定义语义，参考上面创建表的SQL

primary key( 列名 )

主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识

AUTO_INCREMENT

主键是作为唯一值，可以迅速查找到对应的数据。那么这个值就不可重复。一般我们使用的都是数字来填充。

我们可以使用SELECT语句得出最大的数 ，然后对它加1。但这样做并不可靠（你需要找出一种办法来保证，在你执行SELECT和INSERT两条语句之间没有其他人插入行，对于多用户应用，这种情况是很有可能出现的），而且效率也不高。

这个时候就是 AUTO_INCREMENT 发挥作用的时候了，来看上面创建表的部分sql

cust_id int(11) NOT NULL AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

指定默认值

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

user_age int(2) NOT NULL default 18,

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

引擎类型

上面创建表的SQL中 CREATE TABLE 语句以 ENGINE=InnoDB 语句结束，这个就是MYSQL 的引擎。

修改表（表结构的更新）

更新表定义，可使用ALTER TABLE语句

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

为了使用ALTER TABLE更改表结构，必须给出下面的信息：

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

• 所做更改的列表

修改表名

alter table test_a rename to sys_app;

修改表注释

alter table sys_application comment '系统信息表';

修改字段信息

1.修改字段类型和注释

alter table sys_application  modify column app_name varchar(20) COMMENT '应用的名称';

2.修改字段类型

alter table sys_application  modify column app_name text;

3.单独修改字段注释

alter table sys_application column field_name int comment ‘修改后的字段注释’;
-- 注意：字段名和字段类型照写就行

4.设置字段允许为空

alter table sys_application  modify column description varchar(255) null COMMENT '应用描述';

5.增加一个字段，设好数据类型，且不为空，添加注释

alert table sys_application add `url` varchar(255) not null comment '应用访问地址';

6.增加主键

alter table t_app add aid int(5) not null ,add primary key (aid);

7.增加自增主键

alter table t_app add aid int(5) not null auto_increment ,add primary key (aid);

8.修改为自增主键

alter table t_app  modify column aid int(5) auto_increment ;

9.修改字段名字(要重新指定该字段的类型)

alter table t_app change name app_name varchar(20) not null;

10.删除字段

alter table t_app drop aid;

11.在某个字段后增加字段

alter table `t_app` add column gateway_id int  not null default 0 AFTER `aid`;
--(在哪个字段后面添加)  

12.调整字段顺序

alter table t_app  change gateway_id gateway_id int not null after aid;
--(注意gateway_id出现了2次)

清空表数据

删除表信息的方式有两种 :

truncate table 表名;
delete * from 表名;

注 : truncate操作中的table可以省略，delete操作中的*可以省略

truncate、delete 清空表数据的区别 :

1. truncate 是整体删除 (速度较快)，delete是逐条删除 (速度较慢)

2. truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete高的原因

3. truncate 不激活trigger (触发器)，但是会重置Identity (标识列、自增字段)，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的 ID数。而 delete 删除以后，identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录，只能使用 DELETE语句配合 where条件。

删除表

drop table 表名;

drop 是直接将表格删除，无法找回。

视图

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

SELECT
customers.cust_name,
customers.cust_contact
FROM
customers
INNER JOIN orders ON orders.cust_id = customers.cust_id
INNER JOIN orderitems ON orderitems.order_num = orders.order_num
WHERE prod_id = 'TNT2'

以上是一个多表查询的SQL ，我们查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。

现在，假如可以把整个查询包装成一个名为 productcustomers 的虚拟表，则可以如下轻松地检索出相同的数据。

SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2'

productcustomers 是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询

创建视图

SQL 创建

语法：

CREATE [OR REPLACE] VIEW 视图名(列1，列2...)
​
AS SELECT (列1，列2...)
​
FROM ...;
​
[WITH [CASCADED|LOCAL] CHECK OPTION]

示例：

CREATE VIEW `MyView1` AS 
SELECT customers.cust_name, customers.cust_contact FROM customers
INNER JOIN orders ON orders.cust_id = customers.cust_id
INNER JOIN orderitems ON orderitems.order_num = orders.order_num
;

视图创建非常简单，就是在 CREATE VIEW 视图名 AS 后面跟上查询语句即可。

图形工具创建

点击视图按钮，在新弹出的界面中找到定义选项卡，将查询的SQL粘贴过来。然后点击保存按钮，为视图起名。

修改视图

语法：

CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
[WITH [CASCADED|LOCAL] CHECK OPTION]

删除视图

语法：

drop view 视图名;

查看视图

show tables;
desc 视图名;

视图的作用

• 重用SQL语句。

• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。

• 使用表的组成部分而不是整个表。

• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

注意事项

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

视图的规则和限制

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字） 。

• 对于可以创建的视图数目没有限制。

• 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。

• ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

• 视图不能索引，也不能有关联的触发器或默认值。

• 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。