MySQL必知必会19-21-数据和表的操作

MySQL PV:

数据插入

INSET可以用来插入数据，其可以用以下几种方式使用：

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

插入及系统安全机制：可针对每个表或 每个用户，利用MySQL的安全机制禁止使用INSET语句。

插入完整的行

可以直接按照表中的字段顺序来使用INSERT插入数据。

例如表Customers的结构为：

id name location birthday hobby

则可以通过下面的方式来插入数据：

1
INSERT INTO Customers VALUES(NULL, 'mw', 'China', '1999-1-1', 'hike');

但这种方式并不安全，因为其依赖于表中字段的顺序。还可以按照给出的字段顺序来插入数据：

1
INSERT INTO Customers(id, name, location, birthday, hobby) VALUES(NULL, 'mw', 'China', '1999-1-1', 'hike');

此例子完成与前一个INSERT语句完全相同的工作，但在表名后的括号里明确地给出了列名。则其后VALUES中的数据就是与前面给出的顺序相同。

因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不 一定按各个列出现在实际表中的次序。其优点是，即使表的结构改变， 此INSERT语句仍然能正确工作。

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

省略列：如果表的定义允许，则可以在INSERT操作中省略某 些列。省略的列必须满足以下某个条件。

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

如果数据检索是最重要的（通常是这样），则你可以通过在 INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL 降低INSERT语句的优先级，如下所示：
顺便说一下，这也适用于下一章介绍的UPDATE和DELETE语句。

插入多行

INSERT也支持一条语句插入多条数据。

例如：

1
2
3
4
INSERT INTO Customers(id, name, location, birthday, hobby) VALUES
(NULL, 'mw', 'China', '1999-1-1', 'hike'),
(NULL, 'tom', 'America', '1999-1-1', 'basketball'),
(NULL, 'jimmy', 'France', '1999-1-1', 'drink');

其中单条INSERT语句有多组值，每组值用一对圆括号括起来， 用逗号分隔。

提高INSERT的性能此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。

插入检索的数据

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

假如你想从另一表中合并客户列表到你的customers表。不需要每次读取一行，然后再将它用INSERT插入，可以如下进行：

例如，将Customers中的数据插入到新的CustomersNew表中。

1
2
3
INSERT INTO CustomersNew(id, name, location, birthday, hobby)
SELECT id, name, location, birthday, hobby
FROM customers;

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

这条语句将插入多少行有赖于customers表中有多少行。

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

INSERT SELECT中的列名：为简单起见，这个例子在INSERT和 SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。 事实上，MySQL甚至不关心SELECT返回的列名。它使用的是 列的位置，因此SELECT中的第一列（不管其列名）将用来填充 表列中指定的第一个列，第二列将用来填充表列中指定的第二 个列，如此等等。这对于从使用不同列名的表中导入数据是非
常有用的。

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

更新和删除数据

为了更新数据，需要使用UPDATE语句，可以采用两种方式使用UPDATE：

  • 更新表中特定行。
  • 更新表中的所有的行。
  • 确定要更新行的过滤条件。

更新单列

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

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

UPDATE语句总是以要更新的表的名字开始。在此例子中，要更新的 表的名字为customers。SET命令用来将新值赋给被更新的列。如这里所示，SET子句设置cust_email列为指定的值。

UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有 WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有行，这不是我们所希望的。

更新多列

更新多列时，需要在SET放置多个更新项，用逗号隔开。

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

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

删除某个值

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

如：

1
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

其中NULL用来去除cust_email列中的值。

删除数据

可以通过DELETE关键值来删除数据。其有两种方式：

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

例如：

1：删除特定行

1
DELETE FROM customers WHERE cust_id = 10006;

DELETE FROM要求指定从中删除数据的表名。 WHERE子句过滤要删除的行。在这个例子中，只删除客户10006。

2：删除所有的行

1
DELETE FROM customers;

即去掉WHERE限制条件。就会删除表中的所有数据。

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

更新和删除指导

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE 子句的UPDATE或DELETE语句。
  • 保证每个表都有主键，尽可能 像WHERE子句那样使用它。
  • 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进 行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库。

创建和操纵表

这里仅讨论通过MySQL命令来创建和操作表，如果使用GUI工具会更加简单。

表的创建

为利用CREATE TABLE创建表，必须给出下列信息：

  • 新表的名字，在关键字CREATE TABLE之后给出；
  • 表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。

表创建基础

例如，创建customers表：

1
2
3
4
5
6
7
8
9
10
11
12
13
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_emai1 char(255) NULL,
    PRIMARY KEY (cust_id)
) ENGINE-InnoDB;

从上面的例子中可以看到，表名紧跟在CREATE TABLE关键字后 面。实际的表定义（所有列）括在圆括号之中。各列之间用逗号分隔。这个表由9列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型。表的主键可以在创建表时用 PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。整条语句由右圆括号后的分号结束。

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

使用NULL值

允许NULL的字段，则允许在插入或更新的时候空缺其值或者赋予NULL值。

MySQL可以在指定字段时指定NULL或NOT NULL来确定允许空缺或不允许空缺。

使用AUTO_INCREMENT

被AUTO_INCREMENT标记得的字段会在数据增加的时候自动增加（一般将其赋NULL，让其自增）。但是其有以下规范：

  • AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。

  • 设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。

  • AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复(即是主键或者主键的一部分)。

  • AUTO_INCREMENT数据列必须具备NOT NULL属性。

  • AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。

  • 当进行全表删除时，MySQL AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：

    delete from table_name;或者truncate table table_name。

指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。

其通过DEFAULT关键字来指定。

例如：

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
    order_numint NOT NULL,
    order_item int NOT NULL ,
    prod_idchar(10) NOT NULL,
    quantity intNOT NULL DEFAULT 1,
    item _price decima1(8,2) NOT NULL,
    PRIMARY KEY (order_num，order_item)
)ENGINE=InnoDB;

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

引擎类型

MySQL有一个具体管理和处理数据的内部引擎。 在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT 语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。

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

当然，你完全可以忽略这些数据库引擎。如果省略ENGINE=语句，则 使用默认引擎。多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

下面是几个引擎：

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

更新表

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

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

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

例如:

1：给表添加一个字段：

1
ALTER TABLE vendors ADD vend_phone CHAR(20);

2：删除表的一个字段：

1
ALTER TABLE DROP COLUMN vend_phone;

3：定义外键：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products 
FOREIGN KEY (prod_id)REFERENCES products (prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers 
FOREIGN KEY (cust_id)REFERENCES customers (cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id)REFERENCES vendors (vend_id);

这里，由于要更改4个不同的表，使用了4条ALTER TABLE语句。为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

删除表

使用DROP关键字来删除整个表。

例如：

1
DROP TABLE customers;

这条语句删除customers表（假设它存在）。删除表没有确认， 也不能撤销，执行这条语句将永久删除该表。

重命名表

使用RENAME TABLE关键字来对表进行重命名。

例如：

1
RENAME TABLE customers TO customersNew;

还可以对表进行批量重命名：

1
2
3
RENAME TABLE backup_customers To customers,
			backup_vendors To vendors,
			backup_products TO products;

Powered by Hexo and Hexo-theme-hiker

Copyright © 2019 - 2024 My Wonderland All Rights Reserved.

UV : | PV :