博客

《MySQL必知必会》知识汇总四

  • Post category:MySQL

本篇讲解插入、更新、删除表中数据，以及表结构、视图的相关操作

十七、插入数据

本章将介绍如何利用sql的INSERT语句将数据插入表中

  • 数据插入

插入分为以下几种方式：插入完整的行、插入行的一部分、插入多行、插入某些查询结果

  • 插入完整的行
INSERT INTO Customers
VALUES(NULL,
   'Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);

上述sql语句将高度依赖于表中列的定义次序，某个列没有值，应该设置为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);

此时该sql指定了插入具体列名，那么values的顺序就将与指定的插入列一一对应

注意：

一般建议使用这种指定插入列的INSERT语句

不论使用上述哪种INSERT语句，values必须为每个列提供值

使用指定列的INSERT语句并不用将所有列都指定出来，可以省略列，指定列的顺序也不用与数据库表中列的顺序一致，但是省略的列必须允许为NULL值或者表定义中给出默认值

  • 插入多个行

当前可以多次执行INSERT语句实现插入多个行，但是也可以使用一条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'
   );

建议使用这种单条INSERT语句来插入多行数据，会提高性能

  • 插入检索出的数据

需求：想从另一表中合并客户列表到你的customers表。

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表具有相同的表结构

上述sql中，不需要select返回的列名与customers的列名一致，它使用的是列的位置，select的第一列（不管其列名）将填充表列中指定的第一列

当然，select语句还可以使用where子句进行过滤数据！

十八、更新和删除数据

本章将介绍如何利用UPDATE和DELETE语句进一步操纵表数据

  • 更新数据

可采用两种方式使用update：更新表中特定行、更新表中所有行

update customers
set cust_emal = 'elemer@fudd.com'
where cust_id = 10005;

注意：

不要省略where语句，否则将会更新所有行

update customers
set cust_emal = 'elemer@fudd.com',
    cust_name = 'The Fudds'
where cust_id = 10005;

通过单个set命令，可以更新多个列

UPDATE IGNORE customers 
set cust_emal = 'elemer@fudd.com',
    cust_name = 'The Fudds'
where cust_id = 10005;

如果在更新行时，出现一行或者多行错误，则整个UPDATE操作被取消，当然可以是使用IGNORE关键字进行继续更新

  • 删除数据

同样可以采用两种方式使用DELETE：从表中删除特定行、从表中删除所有行

delete from customers
where cust_id = 10006;

注意：

如果忽略了where子句，将删除所有行

更快的删除

如果想从表中删除所有行，可以使用TRUNCATE TABLE,速度更快，实际上底层是删除表后再新建一个表

  • 更新和删除的指导原则

一定要使用带where的update和delete语句

使用where子句前先用select进行查看测试，防止where过滤的数据不正确

MySQL没有撤销命令，一定要小心使用！

十九、创建和操纵表

本章讲述表的创建、更改和删除的基本知识

  • 表的基本创建
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;

需要给出以下信息：

新表的名字，再create table后给出

表列的名字和定义，用逗号隔开

注意：

在创建新的表时，指定的表名必须不存在，可以在表名后使用IF NOT EXISTS做判断

  • 使用NULL值

null值就是没有值或者缺少值，上述创建表的sql中，cust_id、cust_name就不能为空，其他可以为空

不要将null值与空串相混淆，空串是一个有效的值，它不是无值

  • 主键再介绍
PRIMARY KEY (vend_id)

或者

PRIMARY KEY (order_num,order_id)

可以使用单个列作为主键，也可以使用组合列作为主键

主键不允许使用null值

  • 使用AUTO_INCREMENT
cust_id  int  NOT NULL AUTO_INCREMENT

这样MySQL在插入数据时，自动分配一个递增的int值

使用SELECT last_insert_id()函数将能查看最后一个自增的值

  • 指定默认值
quantity  int  NOT NULL DEFAULT 1

表示在未给出数量的情况下使用默认数量1

  • 引擎类型
ENGINE=InnoDB

还可以使用其他引擎如MyISAM

  • 更新表

通过alter table来改变表结构，必须给出要更改的表名，所作更改的列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

ALTER TABLE vendors
DROP COLUME vend_phone;

注意：

一定要小心使用alter table，删除了不该删除的列，会造成数据丢失

  • 删除表
DROP TABLE customers2;
  • 重命名表
RENAME TABLE customers2 TO customers;

二十、使用视图

本章将介绍何为视图，怎样工作的，何时使用，利用视图简化sql

需求：从三个表中检索数据

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

如果三个表的数据都在一张表中，那么sql将变为

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

这就是视图的作用，productcustomers可以看作是一个视图，它不包含表中应该有的数据，仅包含的是一个SQL查询

  • 为什么使用视图

重用SQL语句，简化复杂的SQL操作，使用表的组成部分、保护数据、更改数据格式和表示

视图本身不包含数据，返回的数据是从其他表中检索出来的

性能问题

每次使用视图之前都要处理查询后返回给视图，处理复杂多表联查会降低性能

注意：

视图不能有索引、必须有足够的访问权限

  • 使用视图

使用SHOW CREATE VIEW viewname;来查看创建视图的语句。

用DROP删除视图，其语法为DROP VIEW viewname;

更新视图时，可以先用DROP 再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图

  • 使用视图简化复杂联结
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;

该sql创建了productcustomers视图，联结了三个表，返回订购了任意商品的客户信息

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

因此，检索购买了TNT的客户这条sql即可满足

  • 使用视图重新格式话检索的数据
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;
  • 用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
  • 使用视图与计算字段
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;
  • 更新视图

其实，对视图更新也就是对视图表示的基表进行更新，通过视图增加和删除一行，也是对基表进行增加和删除

但是如果视图涉及到分组、联结、子查询、并、聚集函数、DISTINCT、计算列那将不能进行更新

一般将视图用于检索而不用于更新

Tags: