mysql day4：表操作，数据的增删改，使用视图

数据表存放位置：SHOW variables LIKE '%datadir%';

 

 

 

创建表（使用MySQL语句）

CREATE TABLE test
(
　　id 　　 　　int 　　 　　 NOT NULL AUTO_INCREMENT,
　　Name 　　 char(50)　　NOT NULL ,
　　City 　　　 char(20)　　NULL ,
　　Phone 　　char(20)　　NULL,
　　PRIMARY KEY (id)
) ENGINE=InnoDB;

• test：表名
• 括号中的每一行：列名　　数据格式　　设置（是否允许为空，是否自动编号AUTO_INCREMENT，是否有默认值DEFAULT）
• PRIMARY KEY：定义主键为某一列
• InnoDB：引擎名

更新表

ALTER TABLE test

• ADD 列名 列数据格式;
• DROP COLUMN 列名;
• ADD CONSTRAINT 外键名_{（通常为fk_列名_表名1_表名2的类似结构）} FOREIGN KEY （外键列名） REFERENCES 外键原表名 （外键原列名）_{（即原表主键列名）;}

删除表：DROP TABLE 表名;

重命名表：RENAME TABLE 原表名 TO 新表名;

%——————————————————————————————————————————————————————————————%

插入数据：

INSERT INTO 表名(列的列表)

VALUES(数据列表);

• 数据列表的数据顺序和给出的列的列表对应
• 虽然列的列表可以省略，但是基于固定列次序的SQL语句是不安全的
• 对允许NULL值的列或者有默认值的列，可以在INSERT中省略
• 可以在一个VALUES子句后跟随以逗号分隔的多个数据列表，以达成一次插入多行的目的。

插入检索出的数据：

INSERT INTO 表名(列的列表)

SELECT 列的列表 FROM 另一表名;

• 列的列表中的列名不一定要相同，只需内容对应即可。
• 两表的结构应当相同，以保证导入顺利。
• 主键值的冲突会导致导入失败，应该在导入前加以区分或者利用AUTO_INCREMENT自动生成
• INSERT只需要SELECT的返回结果，因此在SELECT中使用之前学过的绝大多数子句都是合法的，例如WHERE和ORDER BY等

更新数据：

UPDATE  表名

SET 列名 = 新的数据值

WHERE 需要更新的行条件;

• 更新多个列时可以在SET后增加更新内容，以逗号分隔
• UPDATE子句如不以WHERE结束将更新整个表
• 在UPDATE中使用子查询是常用的筛选方式
• 若更新中出现问题，该次完成的所有其他更新也将失效，可以通过在UPDATE后加关键字IGNORE来实现。

删除数据：

若要删除某个列的某些或者全部数据，请使用UPDATE将要删除的数据置为NULL。

若要删除某些行或者所有行的全部数据：

DELETE FROM 表名

WHERE 要删除的行条件;

同样的，若不使用WHERE，将删除全部行，但不会删除表本身。若要更快的完成这一操作，可以使用TRUNCATE TABLE语句。

%————————————————————————————————————————————————————————%

使用视图

视图的层级和表相同，而区别是其中并不包含数据，而是包含了一个复杂联结的查询，可以认为是一个虚拟表。它起到了如下作用：

1. 重用SQL代码
2. 简化复杂操作，封装细节
3. 使用表的某些组成部分而非整个表
4. 保护数据，分离某些功能的访问权限
5. 可以用于更改数据格式及表示

简而言之，视图的地位就像是数据库语言中的函数。

例如：

SELECT (列1),(列2),(列3) FROM (表1),(表2) WHERE 表1.主键=表2.外键;

这是联结章节常用的联结查询语句的一部分，经过视图的封装，它可以变成：

SELECT (列1),(列2),(列3) FROM 视图名;

而原始的两个表名，以及他们之间的联结关系都被隐藏。原语句中出现的其他子句将照常出现。

• 视图可以用CREATE VIEW 视图名 AS 原语句;的方式来创建
• SHOW CREATE VIEW 视图名可以查看对应的原语句
• DROP VIEW 视图名可以用于删除视图
• 更新视图时，可以先DROP再CREATE，也可以直接用CREATE OR REPLACE VIEW 来达到创建或替换的效果

视图可以用于格式化检索出的数据，只要将SELECT Concat的结果定义为一个视图；

视图可以用于过滤数据，只需要封装一个特定的WHERE子句即可；

视图还可以用于简化计算字段的使用，而隐藏计算字段的具体原理，同样只需要封装一个包含计算字段的SELECT语句。

通常，视图仅用于查询，而不进行更新。更新一个视图意味着更新其基表，而如果视图的原语句中包含信息的丢失（如分组，聚集函数，联结等），将导致无法通过更新的视图信息确定基表如何更新，这样的视图更新是非法的。故而通常不对视图使用UPDATE等语句，也不用其进行此类封装。