MYSQL笔记:curd，视图

最新推荐文章于 2024-08-05 13:18:23 发布

kiwi5691

最新推荐文章于 2024-08-05 13:18:23 发布

阅读量279

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_37571795/article/details/100865721

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

插入数据

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

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

INSERT INTO prod VALUES(NULL,'dog');

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此，编写依赖于特定列次序的SQL语句是很不安全的。如果这样做，有时难免会出问题。
编写INSERT语句的更安全（不过更烦琐）

INSERT INTO prod(id,name) VALUES(NULL,'dog');

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

该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。
如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功

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

INSERT LOW_PRIORITY INTO

这也适用于UPDATE和DELETE语句。

插入多个行

可以使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束。

INSERT INTO prod(id,name) VALUES(1,'dog');
INSERT INTO prod(id,name) VALUES(2,'who');

或者，只要每条INSERT语句中的列名（和次序）相同，可以如下组合各语句：

INSERT INTO prod(id,name) VALUES(1,'dog'), (2,'who');

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

插入检索出的数据

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

INSERT INTO prod(id,name) VALUES(1,'dog')
SELET id,name FROM prodnew;

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

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

更新数据

用UPDATE语句。可采用两种方式使用UPDATE：

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

不要省略WHERE子句：在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行。
UPDATE语句总是以要更新的表的名字开始
UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句MySQL将会更新表中所有
行。

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

UPDATE IGNORE customers…

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

UPDATE customers
SET cust_email =null
where cust_id = 1;

删除数据

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

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

同UPDATE ，不要省略WHERE子句。因为稍不注意，就会错误地删除表中所有行

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

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

创建表

MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。
一般有两种创建表的方法：
 使用具有交互式创建和管理表的工具
 表也可以直接用MySQL语句操纵。

表创建基础

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

新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔。
处理现有的表：在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
如果你仅想在一个表不存在时创建它，应该在表名后给出IF
NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

order_id int NOT NULL AUOT_INCREMENT

有关键字NOT NULL。将会阻止插入没有值的列。如果试图插入没有值的列，将返回错误，且插入失败。
NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。

主键

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

PRIMARY KEY (order_id)

为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名.

PRIMARY KEY (order_id,order_num)

主键可以在创建表时定义（如这里所示），或者在创建表之后定义

AUTO_INCREMENT

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

确定AUTO_INCREMENT值: 让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。
考虑这个场景：你正在增加一个新订单。这要求在orders表中创建一行，然后在orderitms表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。
那么，如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值。

SELECT last_insert_id()

此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

指定默认值

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

不允许函数 :与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
使用默认值而不是NULL值: 许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

引擎类型

ENGINE=InnoDB

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。
多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。
为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。但并不是所有语句都默认使用它。

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

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

更新表

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

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

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

ALTER TABLE prod
ADD prod_ff CHAR(20)

删除列

ALTER TABLE prod
DROP COLUMN prod_ff

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

ALTER TABLE prod
ADD CONSTRAINT fk_ordetimes
FOREIGN KEY (order_num) PEFERENCES order(order_num);

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

用新的列布局创建一个新表；
使用INSERT SELECT语句
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键。
使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

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

DROP TABLE prod

重命名表

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

RENAME TABLE prod

使用视图

MySQL 5添加了对视图的支持。
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确联结表的相同的查询）。
下面是视图的一些常见应用。

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

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
性能问题 :因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

使用视图

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

利用视图简化复杂的联结

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

CREATE VIEW productcustomers AS 
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = order.cust_id
AND orderitems.order_num = order.order_num;

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

如上所述，视图的另一常见用途是重新格式化检索出的数据

select Concat(name,'(',sortb_order,')')  AS title  from tb_category order by name;

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

CREATE VIEW sorbname AS SELECT Concat(name,'(',sortb_order,')') AS title from tb_category order by name;

使用视图与计算字段

CREATE VIEW productcustomers AS 
SELECT cust_name,cust_contact,prod_id,
quanti*price AS expand_price
FROM customers,orders,orderitems

更新视图

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。
但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：