LeaCoder

《MySQL必知必会》学习笔记（19-23）

2019-06-11
leacoder
MySQL

《MySQL必知必会》学习笔记 目录

《MySQL必知必会》学习笔记 目录

第 十 九 章 插 入 数 据

如何利用SQL的INSERT语句将数据插入表中。

19.1 数据插入

INSERT是用来插入（或添加）行到数据库表的。插入可以用几种方式使用：

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

19.2 插入完整的行

把数据插入表中的最简单的方法是使用基本的INSERT语法，它要求指定表名和被插入到新行中的值。

INSERT INTO customers 
 VALUES ( NULL, 'Pep E. Lapew' , '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

19.2INSERT 1.png

没有输出 INSERT语句一般不会产生输出。

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。

编写INSERT语句的更安全（不过更烦琐）的方法如下：

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);

19.2INSERT 2.png

此例子完成与前一个INSERT语句完全相同的工作，但在表名后的括号里明确地给出了列名。在插入行时， MySQL将用VALUES列表中的相应值填入列表中的对应项。 VALUES中的第一个值对应于第一个指定的列名。第二个值对应于第二个列名，如此等等。

下面的INSERT语句填充所有列（与前面的一样），但以一种不同的次序填充。因为给出了列名，所以插入结果仍然正确：

INSERT INTO customers(cust_name,cust_contact, cust_email ,cust_address , cust_city , cust_state, cust_zip, cust_country  )
 VALUES (  'Pep E. Lapew' , NULL, NULL, '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');

19.2INSERT 3.png

总是使用列的列表 一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化。

19.3 插入多个行

可以使用多条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');
INSERT INTO customers(cust_name, cust_address , cust_city , cust_state, cust_zip, cust_country)
 VALUES (  'M. Martian' , '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

或者，只要每条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');

19.4 插入检索出的数据

INSERT一般用来给表插入一个指定列值的行。但是， INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT，顾名思义，它是由一条INSERT语句和一条SELECT语句组成的

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;

这个例子使用INSERT SELECT从custnew中将所有数据导入customers。

这个例子导入了cust_id（假设你能够确保cust_id的值不重复）。也可以简单地省略这列（从INSERT和SELECT中），这样MySQL就会生成新值。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

第 二 十 章 更新和删除数据

如何利用UPDATE和DELETE语句进一步操纵表数据。

20.1 更新数据

为了更新（修改）表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE：

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

不要省略WHERE子句 在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行。

UPDATE语句由3部分组成，分别是：

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

客户10005现在有了电子邮件地址，因此他的记录需要更新，语句如下：

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

20.1UPDATE1.png

更新多个列的语法稍有不同：

UPDATE customers SET cust_name = 'The Fudds' , cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

20.1UPDATE2.png

为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

20.1UPDATE3.png

20.2 删除数据

为了从一个表中删除（去掉）数据，使用DELETE语句。可以两种方式使用DELETE

  • 从表中删除特定的行；
  • 从表中删除所有行。

下面的语句从customers表中删除一行：

DELETE FROM customers WHERE cust_id = 10006;

20.2DELETE1.png

在这个例子中，只删除客户10006。如果省略WHERE子句，它将删除表中每个客户。

DELETE不需要列名或通配符。 DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。换句话说，如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。

第 二 十 一 章 更新和删除数据

表的创建、更改和删除的基本知识。

21.1 创建表

为了用程序创建表，可使用SQL的CREATE TABLE语句。

21.1.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(50)       NULL,
  cust_zip              char(50)       NULL,
  cust_country       char(50)       NULL,
  cust_contact        char(50)       NULL,
  cust_email           char(255)     NULL,
  PRIMARY KEY (cust_id)
) ENGINE = InnoDB;

21.1.1CREATE.png

表的主键可以在创建表时用PRIMARY KEY关键字指定。

处理现有的表 在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表， SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。

21.1.2 使用NULL值

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

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

CREATE TABLE orders
(
  order_num  int NOT NULL AUTO_INCREMENT,
  order_date  datetime  NOT NULL ,
  cust_id    int    NOT NULL,
  PRIMARY KEY (order_num)
) ENGINE = InnoDB;

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

21.1.3 主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。

主键和NULL值 主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

21.1.4 使用AUTO_INCREMENT

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

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

21.1.5 指定默认值

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

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;

quantity列包含订单中每项物品的数量。在此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1。

21.1.6 引擎类型

MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

以下是几个需要知道的引擎：

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

外键不能跨引擎 混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

21.2 更新表

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

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

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

下面的例子给表添加一个列：

ALTER TABLE vendors ADD vend_phone CHAR(20);

删除刚刚添加的列，可以这样做：

ALTER TABLE vendors DROP COLUMN vend_phone;

21.2ALTER .png

ALTER TABLE的一种常见用途是定义外键。

21.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可：

DROP TABLE customers3;

21.4 重命名表

使用RENAME TABLE语句可以重命名一个表：

RENAME TABLE customers2 TO customers3;

image.png

第 二 十 二 章 使 用 视 图

介绍视图究竟是什么，它们怎样工作，何时使用它们。我们还将看到如何利用视图简化前面章节中执行的某些SQL操作。

22.1 视图

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

理解视图的最好方法是看一个例子。下面的SELECT语句从3个表中检索数据：

SELECT cust_name, cust_contact FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';

此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。 现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据： SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = ‘TNT2’; productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确联结表的相同的查询）。

22.1.1 为什么使用视图

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

重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

22.1.2 视图的规则和限制

关于视图创建和使用的一些最常见的规则和限制。

  • 与表一样，视图必须唯一命名
  • 对于可以创建的视图数目没有限制。
  • 为了创建视图，必须具有足够的访问权限。
  • 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
  • ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
  • 视图不能索引，也不能有关联的触发器或默认值
  • 视图可以和表一起使用。

22.2 使用视图

视图的创建

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

22.2.1 利用视图简化复杂的联结

视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。

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;

22.2.1VIEW1

这条语句创建一个名为productcustomers的视图， 它联结三个表，以返回已订购了任意产品的所有客户的列表。 为检索订购了产品TNT2的客户，可如下进行

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

22.2.1VIEW2.png

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

视图的另一常见用途是重新格式化检索出的数据。

SELECT Concat( RTrim(vend_name), '(', RTrim(vend_country), ')' ) AS vend_title 
FROM vendors ORDER BY vend_name;

22.2.2VIEW1.png

假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。

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.2VIEW2.png

22.2.3 用视图过滤不想要的数据

视 图 对 于 应 用 普 通 的 WHERE 子 句 也 很 有 用 。

CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email 
FROM customers WHERE cust_email IS NOT NULL;

这里的WHERE子句过滤了cust_email列中具有NULL值的那些行，使他们不被检索出来。

SELECT * FROM customeremaillist;

22.2.3 VIEW.png

22.2.4 使用视图与计算字段

视图对于简化计算字段的使用特别有用。

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems WHERE order_num = 20005;

22.2.4VIEW.png

将其转换为一个视图

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;

22.2.5 更新视图

迄今为止的所有视图都是和SELECT语句使用的。然而，视图的数据能否更新？答案视情况而定。 基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

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

第 二 十 三 章 使用存储过程

介绍什么是存储过程，为什么要使用存储过程以及如何使用存储过程，并且介绍创建和使用存储过程的基本语法。

23.1 存储过程

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

23.2 为什么要使用存储过程

23.3 使用存储过程

23.3.1 执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。 CALL接受存储过程的名字以及需要传递给它的任意参数。

CALL productpricing ( @pricelow, @pricehigh, @priceaverage);

执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

23.3.2 创建存储过程

一个例子——一个返回产品平均价格的存储过程

CREATE PROCEDURE productpricing() 
BEGIN
    SELECT Avg(prod_price) AS priceaverage  FROM products;
END;

直接这样会报错 此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句.

mysql命令行客户机的分隔符 默认的MySQL语句分隔符为;mysql命令行实用程序也使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。 解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

DELIMITER //
CREATE PROCEDURE productpricing() 
BEGIN
    SELECT Avg(prod_price) AS priceaverage  FROM products;
END //
DELIMITER ;

image.png

其中， DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END//而不是END;。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ;

使用这个存储过程

CALL productpricing() ;

image.png

23.3.3 删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。

为删除刚创建的存储过程，可使用以下语句：

DROP PROCEDURE productpricing;

23.3.4 使用参数

以下是productpricing的修改版本

CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),  
  OUT ph DECIMAL(8,2),  
  OUT pa DECIMAL(8,2)
) 
BEGIN
    SELECT Min(prod_price) 
    INTO pl
    FROM products;
     SELECT MAX(prod_price) 
    INTO ph
    FROM products;
     SELECT Avg(prod_price) 
    INTO pa
    FROM products;
END;

同样可能需要DELIMITER 改变为如下

DELIMITER //
CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),  
  OUT ph DECIMAL(8,2),  
  OUT pa DECIMAL(8,2)
) 
BEGIN
    SELECT Min(prod_price) 
    INTO pl
    FROM products;
     SELECT MAX(prod_price) 
    INTO ph
    FROM products;
     SELECT Avg(prod_price) 
    INTO pa
    FROM products;
END//

DELIMITER ;

image.png

CALL productpricing ( @pricelow, @pricehigh, @priceaverage);

image.png

这条CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。

变量名 所有MySQL变量都必须以@开始。

SELECT @priceaverage;

image.png

SELECT @pricelow, @pricehigh, @priceaverage;

image.png

23.3.5 建立智能存储过程

CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  DECLARE total DECIMAL(8,2);
  DECLARE taxrate INT  DEFAULT 6;
  SELECT Sum(item_price*quantity)
  FROM orderitems WHERE order_num = onumber 
  INTO total;
  IF taxable THEN
      SELECT total+(total/100*taxrate) INTO total;
  END IF;
  SELECT total INTO ototal;
END;

注意使用 DELIMITER // 转

DELIMITER //
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  DECLARE total DECIMAL(8,2);
  DECLARE taxrate INT  DEFAULT 6;
  SELECT Sum(item_price*quantity)
  FROM orderitems WHERE order_num = onumber 
  INTO total;
  IF taxable THEN
      SELECT total+(total/100*taxrate) INTO total;
  END IF;
  SELECT total INTO ototal;
END//
DELIMITER ;

注意 DELIMITER 与 ;之间的空格

image.png

添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用DECLARE语句定义了两个局部变量。 DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。 SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。 IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

CALL ordertotal(20005, 0 , @total);
SELECT @total;

image.png

CALL ordertotal(20005, 1 , @total);
SELECT @total;

image.png

IF语句 这个例子给出了MySQL的IF语句的基本用法。 IF语句还支持ELSEIF和ELSE子句

23.3.6 检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATEPROCEDURE语句：

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表， 使用SHOW PROCEDURE STATUS。

限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

SHOW PROCEDURE STATUS LIKE 'ordertotal';

image.png

GitHub链接： https://github.com/lichangke/LeetCode

知乎个人首页： https://www.zhihu.com/people/lichangke/

简书个人首页： https://www.jianshu.com/u/3e95c7555dc7

个人Blog: https://lichangke.github.io/

欢迎大家来一起交流学习

Similar Posts

上一篇 《MySQL必知必会》学习笔记（15-18）

下一篇 《MySQL必知必会》学习笔记（24-29）

Comments

Content