高性能 SQL 计划 Day 4

今天计划继续，主要内容是《MySQL必知必会》19-22章的内容。

Ch 19.插入数据

1.插入一行

#第一种方式
INSERT INTO Customers
VALUES(NULL,
   'Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);
#VALUES中值的顺序必须与表中列的顺序一致
   
#第二种方式
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);
#VALUES中值的顺序与前面列的顺序一致即可，无需与表中列定义的顺序一致。

使用第二种方式时，如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下条件之一：

  • 该列定义为允许NULL值（无值或空值）。
  • 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功。

关于主键的处理：表中 cust_id 为自增主键，第一种方式中需要给这一列赋值为NULL，第二种可以直接省略该列。

注意：不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对每个列出的列给出一个值。如果不这样，将产生一条错误消息，相应的行插入不成功。

提高整体性能： 数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：

 INSERT LOW_PRIORITY INTO

这个操作同样适用于UPDATE和DELETE语句。

2.插入多行

如果想向表中插入多行数据，可以依次执行多个 INSERT 语句，同样也可以用以下方式插入：

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. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );

MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快，因此此方法可以提高性能。

3.插入检索出的数据

可以将INSERT语句和SELECT语句结合使用，将检索出的数据插入新表中。

INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT id,
    contact,
    email,
    name,
    address,
    city,
    state,
    zip,
    country
FROM custnew;
#注意：本例中要求 custnew 表中的 id 与 customers 表中的 cust_id 不重复，否则会造成主键重复的错误。如果 customers 中定义的是自增主键，则可以直接省略。

注意：插入检索出的数据要求对应列的数据类型一致。

Ch 20.更新和删除数据

1.更新数据

更新数据使用的是 UPDATE 语句，主要包含三部分内容：

  • 要更新的表
  • 要更新的列名及其更新后的值
  • 要更新行的过滤条件
#更新某行的一列
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

#更新某行的多列
UPDATE customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

注意：使用UPDATE语句时一定要注意使用WHERE进行过滤，否则会更新全部行的内容。

IGNORE关键字：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：

UPDATE IGNORE customers ...

删除某列的值：为了删除某列的值，如果该列允许值为NULL，可以直接将其设为NULL。

2.删除数据

删除数据使用的是DELETE关键字，主要包含两部分：

  • 使用的表
  • 删除的过滤条件
DELETE FROM customers
WHERE cust_id = 10006;

注意：使用DELETE语句时一定要注意使用WHERE进行过滤，否则会删除全部的行。

更快的删除：如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

3.更新和删除的原则

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
  • 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

Ch 21.创建和操纵表

1.创建表

使用CREATE TABLE语句创建表，主要包含以下两部分：

  • 新表的名字，在关键字CREATE TABLE之后给出；
  • 表列的名字和定义，用逗号分隔。
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

  1. 注意以下几点：

    • 主键值必须唯一
    • 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如成为主键）
    • 如果对AUTO_INCREMENT列强行指定一个未使用过的值，则会覆盖自增的值，并且接下来插入的记录该列将会从指定值开始自增

  2. 获得最后一个AUTO_INCREMENT值：

SELECT last_insert_id();
  1. 指定默认值：
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL  DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

  1. 数据库引擎

    MySQL数据库主要有以下几种引擎：

    • InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
    • MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
    • MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

    注意：

    • 同一数据库中不同的表可以使用不同的引擎；
    • 外健不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

2.更新表

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

ALTER TABLE语句主要包含以下几个部分：

  • 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
  • 所做更改的列表。
#添加列
ALTER TABLE vendors
ADD vend_phone CHAR(20);

#删除列
ALTER TABLE vendors
DROP COLUMN vend_phone;

#修改列属性
ALTER TABLE vendors MODIFY COLUMN vend_phone VARCHAR(50);

#修改列
ALTER TABLE vendors CHANGE vend_phone phone VARCHAR(30);  

#定义外键
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)
REFERENCES products (prod_id);

如果要更改结构复杂的表，一般涉及到以下过程：

  • 用新的列布局创建新表；
  • 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
  • 检验包含所需数据的新表；
  • 重命名旧表（如果确定，可以删除它）；
  • 用旧表原来的名字重命名新表；
  • 根据需要，重新创建触发器、存储过程、索引和外键。

3.删除和重命名表

删除表：

DROP TABLE customers2;

重命名表：

RENAME TABLE customers2 TO customers;

Ch 22.使用视图

视图是一个虚拟的表，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询结果。

使用视图可以带来以下优点：

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

1.使用视图

视图的基本使用：

  • 视图用CREATE VIEW语句来创建。
  • 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
  • 用DROP删除视图，其语法为DROP VIEW viewname。
  • 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW，如果要更新的视图不存在，则会创建一个视图；如果要更新的视图存在，则会替换原有视图。

  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;
  
#使用视图查询
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

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

    #创建视图
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

#使用视图
SELECT *
FROM vendorlocations;

  3. 用视图过滤数据

    #创建视图
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

#使用视图
SELECT *
FROM customeremaillist;

  4. 使用视图与计算字段

    #创建视图
CREATE VIEW orderitemsexpanded AS
SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

#使用视图
SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

2.更新视图

通常而言，视图是可以进行更新操作的（使用INSERT、UPDATE和DELETE），更新视图将更新基表。因此，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。

如果视图的定义中存在以下要素，则不允许更新视图：

  • 分组（使用GROUP BY和HAVING）；
  • 联结；
  • 子查询；
  • 并；
  • 聚集函数（Min()、Count()、Sum()等）；
  • DISTINCT；
  • 导出（计算）列。

一般而言，应该将视图用于检索而非更新。

未完待续···

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 196,583评论 5赞 462
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,669评论 2赞 374
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 143,684评论 0赞 325
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,682评论 1赞 267
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,533评论 5赞 358
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,396评论 1赞 275
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,814评论 3赞 387
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,458评论 0赞 255
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,745评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,789评论 2赞 314
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,565评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,410评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,828评论 3赞 300
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,050评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,342评论 1赞 253
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,793评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,010评论 2赞 337