MySQL数据库基础(必知必会19-22)-增删改，创建和操纵表，视图

插入数据

数据插入

INSERT是用来插入(或添加)行到数据库表的，可以实现

• 插入完整的行
• 插入行的一部分
• 插入多行
• 插入某些查询的结果

插入完整的行

-- 19.2插入完整的行
INSERT customers VALUES(NULL,'Pep E.LaPew','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL); -- 将一个新客户的信息插入到表中，每个列必须有对应的值，如果没有，指定为NULL
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES ('Pep E.LaPew','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL); -- 与上一行效果相同

• 优点：即使改变表的结构，INSERT语句仍然能够正常工作，一般不使用没有明确给出列的列表的INSERT语句
  • 省略列，满足的条件
    • 列定义为允许NULL值
    • 在表定义中给出默认值。如果不给出值就使用默认值
    • 表中不允许NULL值且没有默认值的列不给出值，会产生错误
  • 提高整体性能
  • INSERT操作很耗时（特别是很多索引需要更新时），可以通过INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低优先级

插入多个行

可以通过多条INSERT语句，一次进行提交；只要每条INSERT语句中的列名(和次序)相同，可以采用每组值通过圆括号括起来，用逗号分隔的方式

-- 19.3插入多个行
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES ('Pep E.LaPew','100 Main Street','Los Angeles','CA','90046','USA'),('M. Martin','42 Galaxy Way','New York','NY','11213','USA'); -- 与上一行效果相同

插入检索的数据

由INSERT和SELECT组成， MySQL不管SELECT返回的列名，它使用的是列的位置

-- 19.4插入检索出的数据
INSERT INTO customers(cust_id, cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) SELECT cust_id, cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country FROM custnew; -- 将从custnew表中检出的值插入到customers，插入多少行取决于SELECT的语句

更新和删除数据

更新数据

使用UPDATE语句

• 更新表中特定行
• 更新表中所有行

UPDATE语句由3部分组成

• 要更新的表
• 列名和它们的新值
• 确定要更新行的过滤条件

更新多个列的时候，'列名=值’对之间采用逗号分隔
UPDATE语句可以使用子查询
IGNORE关键字，在更新多行时，如果其中一行或多行出现错误，导致UPDATE操作被取消。为了发生错误的情况仍然能够继续更新，可以使用IGNORE关键字

-- 20.更新和删除数据
-- 20.1 更新数据
UPDATE customers SET cust_email='elmer@fudd.com' WHERE cust_id=10005;-- 更新客户10005的邮箱为'elmer@fudd.com'
UPDATE customers SET cust_name='The Fudds',cust_email='elmer@fudd.com' WHERE cust_id=10005;-- 更新客户10005的邮箱为'elmer@fudd.com'
UPDATE customers SET cust_email=NULL WHERE cust_id=10005;	-- 删除某行的值可以设置其为null值

删除数据

使用 DELETE语句

• 从表中删除特定的行
• 从表中删除所有行
  DELETE不需要列名或通配符
  DELETE从表中删除行，但不删除表本身
  更快的删除 从表中删除所有行，可以使用 TRUNCATE TABLE语句，它实际上是删除原来的表并重新创建一个表

-- 20.2删除数据
DELETE FROM customers WHERE cust_id=10006;	-- 删除客户id为10006

更新和删除的原则

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE和DELETE语句

• 保证每个表都有主键，尽可能像WHERE子句那样使用它
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先试用SELECT语句进行测试，保证它过滤的是正确的记录
• 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关的数据的行

创建和操纵表

创建表

创建表的方法

• 使用具有交互式创建和管理表的工具
• 直接用MySQL语句进行操纵

表创建的基础

利用CREATE TABLE创建表，新表名称在CREATE TABLE后给出，表列的名字和定义用逗号分隔
如果只想在一个表不存在时创建它，应该在表名后给出 IF NOT EXISTS

使用NULL值

每个表列或是NULL列，或是NOT NULL列，可以在创建时指定
NULL值是没有值，它不是空串

主键再介绍

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

• PRIMARY KEY() 单个列作为主键值
• PRIMARY KEY(a,b,c) 多个列作为主键值

使用AUTO_INCREMENT

AUTO_INCREMENT，本列每当增加一行时自动增量，保证主键值的唯一性，每个表只允许有一个AUTO_INCREMENT列，而且必须被索引

指定默认值

使用DEFAULT关键字指定
只支持常量

引擎类型

TABLE语句以ENGINE=XXX语句结束
引擎类型

• INNODB是一个可靠的事务处理引擎，不支持全文本搜索
• MEMORY在功能上等同于MYISAM，但由于数据存储内存中，速度快（特别适合临时表）
• MYISAM是一个性能极高的引擎，支持全文本搜索，但不支持事务处理
• 引擎类型可以混用

更新表

更新表的定义，使用ALTER TABLE语句
必须给出以下信息

• 在ALTER TABLE 之后给出要更改的表名
• 所做更改的列名

-- 21.2 更新表
ALTER TABLE vendors ADD  vend_phone CHAR(20);	-- 在vendors表中添加一个名为vend_phone的列
ALTER TABLE vendors DROP COLUMN vend_phone;	-- 删除刚刚增加的列
-- ALTER TABLE的常见用途是定义外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY(order_num) REFERENCES orders(order_num);

复杂的表结构更改一般需要手动删除过程

• 用新的列布局创建一个新表；
• 使用INSERT SELECT语句
• 检验包含所需数据的新表
• 重命名旧表
• 用旧表原来的名字重命名新表
• 根据需要，重新创建触发器、存储过程、索引和外键

删除表

-- 21.3删除表
-- 通过DROP TABLE语句实现
DROP TABLE customers2; -- 表示永久删除

重命名表

-- 21.4重命名表
-- 使用RENAME TABLE实现
RENAME TABLE customers2 TO customers;

使用视图

视图

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

为什么使用视图

视图常见应用

• 重用SQL语句
• 简化复杂的SQL操作
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

视图的规则和限制

与表一样，视图必须唯一命名

• 对于可以创建的视图数目没有限制
• 为了创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予
• 视图可以嵌套，可以利用其它视图中检索数据的查询来构造一个视图
• ORDER BY可以用在视图，若从该视图检索数据SELECT中也含有ORDER BY ,该视图的ORDER BY将被覆盖
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一块使用

使用视图

使用用 CREATE VIEW语句进行创建
使用SHOW CREATE VIEW viewname;来查看创建视图的语句
用DROP删除视图，其语法为DROP VIEW viewname;
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW.

利用视图简化复杂的联结

-- 22.2.1利用视图简化复杂的联结
CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id FROM customers,orders,orderitems WHERE customers.`cust_id`=orders.`cust_id` AND orderitems.`order_num`=orders.`order_num`;
-- 检索订购产品TNT2的用户
SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='TNT2';

用视图重新格式化检索出的数据

使用视图重新格式化检索的数据

SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
-- 不必在每次需要时进行联结执行，可以创建一个视图
CREATE VIEW vendorlocations AS SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
SELECT * FROM vendorlocations;

用视图过滤不想要的数据

-- 22.2.3用视图过滤不想要的数据
CREATE VIEW customeremaillist AS SELECT cust_id,cust_name,cust_email FROM customers WHERE cust_email IS NOT NULL;	-- 定义customeremaillist视图，过滤没有电子邮件地址的客户
SELECT * FROM customeremaillist;

使用视图与计算字段

-- 22.2.4使用视图与计算字段
CREATE VIEW orderitemsexpanded AS SELECT order_num,prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems;	-- 检索订单为20005的详细内容
SELECT * FROM orderitemsexpanded WHERE order_num=20005;

更新视图

通常情况下视图是可更新的，更新一个视图将更新其基表。
并非所有视图都是可更新的，如果视图定义有以下操作，则不能更新

• 分组（使用GROUP BY和HAVING）
• 联结
• 子查询
• 并
• 聚集函数（Min(),Count(),Sum()等）
• DISTNCT
• 导出列

本文相关代码可到我的码云下载

结束语

数据库基础到此处就告一段落，后续相关的数据库事务，锁以及引擎索引等方面的知识不再参照《MySQL必知必会》进行学习，同时，我为数据库基础绘制了一张脑图，加深记忆。