mysql 给表增加表名_5天学完《MySQL必知必会》学习笔记之第四天

本篇知识点

更新删除数据、创建操纵表、视图、存储过程

---

更新和删除数据

• 使用UPDATE语句更新（修改）表中的数据：
  • 更新表中特定行（使用WHERE语句
  • 更新表中所有行
• UPDATE语句以要更新的表名开始，SET命令用来赋值

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

更新多个列

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

使用IGNORE关键字，即使在更新多个行时发生错误，也能继续进行更新

UPDATE IGNORE customers

• 删除某个列的值，可使用UPDATE设置它为NULL（假如表定义允许NULL值）

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

其中NULL用来去除cust_mail列中的值

• 使用DELETE语句从一个表中删除数据：
  • 从表中删除特定行
  • 从表中删除所有行
• DELETE删除整行，删除列（值）用UPDATE

DELETE FROM customers WHERE cust_id = 10006;

DELETE不删除表本身；删除所有行用TRUNCATE TABLE^[1]更快。

创建和操纵表

• 使用CREATE TABLE创建表时，须给出下列信息：
  • 新表的名字，在关键字CREATE TABLE之后给出；
  • 表列的名字和定义，用逗号分隔
• 创建练习使用的customers表

CREATE TABLE customers IF NOT EXISTS
(
   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，仅在表名不存在时创建它。

• NULL值表示没有值或缺值，允许NULL值的列允许在插入行时不给出该列的值，否之必须给值

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

NULL值和NOT NULL可以在创建时混用

• 主键值必须唯一；创建由多个列组成的主键，应该以逗号分隔的列表给出各列名：

CREATE TABLE orderitems
(
  order_num int NOT NULL ,
  order_item int NOT NULL ,
  prod_id char(10) NOT NULL ,
  quantity int NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE = InnoDB;

订单号(order_num)和订单物品(order_item)的组合是唯一的，从而适合作为主键。

主键：其值唯一标识表中每个行的列；主键中只能使用定义为NOT NULL的列。

• 使用AUTO_INCREMENT，当一列增加一行时自动增量，给该列赋予一个可用的值，一般用在id列，可用作主键值；每个表只允许一个AUTO_INCREMENT列，且它必须被索引。
• 使用last_insert_id( )函数可获得使用AUTO_INCREMENT列时获得的值

SELECT last_insert_id( )

返回一个AUTO_INCREMENT值

• 指定默认值：如果在插入行时没有给值，可指定使用默认值

quantity int NOT NULL DEFAULT 1,

MySQL只支持常量作为默认值

• 引擎类型
  • InnoDB是一个可靠的事务处理引擎，不支持全文本搜索
  • MEMORY功能等同与MyISAM，数据存储在内存中，速度很快(适合于临时表)
  • MyISAM性能很高，支持全文本搜索，不支持事务处理

外键不能跨引擎

• 使用ALTER TABLE语句更新表定义，须给出：
  • ALTER TABLE之后给出要更改的表名
  • 更改的列表
• 给表添加一个列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

• 删除刚添加的列

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

对单个表进行多个更改，可使用单挑ALTER TABLE语句，每个更改用逗号分隔

• 删除表

DROP TABLE customers2;

• 重命名表

RENAME TABLE customers2 TO customers;

可以对多个表重命名，用逗号分开。

使用视图

• 视图是虚拟的表；视图只包含使用时动态检索数据的查询。
• 为什么使用视图：
  • 重用SQL语句
  • 简化复杂的SQL操作。编写查询后，可以方便地重用它而不必知道基本细节
  • 使用表的组成部分而非整个表
  • 保护表数据。用户仅被授予表的特定部分的访问权限。
  • 更改数据格式和表使。视图可返回与底层表的表示和格式不同的数据
• 视图是用来查看存储在别处的数据的一种设施，它本身不包含数据。
• 视图的创建：
  • 用CREATE VIEW语句创建
  • 使用SHOW CREATE VIEW viewname；来查看创建视图的语句
  • 用DROP删除视图，语法为DROP VIEW viewname
  • 更新视图时，可先DROP再CREATE，也可直接CREATE OR REPLACE VIEW.
• 使用视图隐藏复杂的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 ;

这条语句创建一个名为productcustomers的视图，它联结三个表。

为检索订购了产品TNT2的客户，可如下进行：

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

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

CREATE VIEW vendorlocations AS
SELECT Concat(Rtrim(vend_name), '(', Rtrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

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

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

定义customeremaillist视图，过滤没有电子邮件地址的客户。

• 使用视图与计算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
 prod_id,
 quantity,
 item_price,
 quantity*item_price AS expanded_price
FROM orderitems ;

• 视图是可更新的（对它们使用INSERT, UPDATE和DELETE），但是如果视图定义中有以下操作（使得MySQL不能正确地确定被更新的基数据），则不能进行视图更新：
  • 分组（使用GROUP BY和HAVING）
  • 联结
  • 子查询
  • 并
  • 聚集函数（Min( ), Count( ), Sum( )等）
  • DISTINCT
  • 导出（计算）列

使用存储过程

• 存储过程，就是为了以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。
• 执行存储过程 称为调用，使用语句CALL

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

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

• 创建存储过程 例子：一个返回产品平均价格的存储过程

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

此存储过程名为productpricing，用CREATE PROCEDURE productpricing( )语句定义，若存储过程接受参数，将在( )中列举出来。BEGIN和END语句用来限定存储过程体，存储过程体本身仅是一个简单的SELECT语句。

• mysql命令行客户机的分隔符 （我懒得打字总结了）

摘自《MySQL必知必会》

• 使用存储过程

CALL productpricing( ) ;

• 删除存储过程

DROP PROCEDURE 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;

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键词OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT(从存储过程传出)和INOUT（对存储过程传入和传出）类型的参数。

• 调用此存储过程须指定3个变量名

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

变量以@开始。调用时上条语句不显示数据，它返回可以显示的变量。

• 显示数据

SELECT @priceaverage;

• 显示3个数据

SELECT @pricehigh, @pricelow, @priceaverage;

• 使用IN和OUT参数

CREATE PROCEDURE ordertotal (
      IN onumer INT,
      OUT ototal DECIMAL(8, 2)
)
BEGIN
      SELECT Sum(item_price*quantity)
      FROM orderitens
      WHERE order_num = onumer
      INTO ototal;
END;

onumer定义为IN, 因为订单号被传入存储过程。ototal定义为OUT， 因为要从存储过程返回合计，INTO使用ototal存储计算出来的合计。

调用这个存储过程

CALL ordertotal (20005, @total);

• 建立智能存储过程

智能存储过程中包含了业务规则和智能处理，不仅限于封装简单的SELECT语句

案例 需要对订单合计增加营业税，不过只针对某些顾客

-- Name: ordertotal
-- Parameters: onumber = order numer
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable
 
CREATE PRECEDURE ordertotal(
     IN onumer INT,
     IN taxable BBOLEAN,
     OUT ototal DECIMAL(8, 2)
)    COMMENT 'Obtian order total, optionally adding tax'
BEGIN
 
     -- Declare variable for total
     DECLARE total DECIMAL(8,2);
     -- Decalre tax percentage
     DECLARE taxrate INT DEFAULT 6;
 
     -- Get the order total
     SELECT Sum(item_price*quantity)
     FROM orderitems
     WHERE order_num = onumber
     INTO total;
 
     -- Is this taxable?
     IF taxable THEN
          -- Yes, so add taxrate to the total
          SELECT total+(total/100 *taxrate) INTO total;
     END IF;
     -- And finally, save to out variable
     SELECT total INTO ototal;

END;

这个存储过程增加了注释（前面放置--），用DECALRE语句定义了两个局部变量。COMMENT关键字非必须，如果给出，将在SHOW PROCEDURE STATUS结果中显示。

• 试验存储过程

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

• 检查存储过程

SHOW CREATE PRECEDURE ordertotal;

参考

1. ^TRUNCATE实际是删除原来的表并重新创建一个表，而不 是逐行删除表中的数据.