MySQL 6 插入数据(INSERT INTO\VALUES\SELECT FROM)、更新和删除数据(UPDATE SET WHERE\DELETE)、创建和操纵表、视图
1. 插入数据
使用INSERT
INSERT可以用几种方式使用:
插入完整的行;插入行的一部分;插入多行;插入某些查询的结果;
插入完整的行:
指定表名和被插入到新行中的值。
INSERT INTO Customers
VALUES( NULL,'Pep E. LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL);
INSERT语句一般不会产生输出。
INSERT INTO指定插入的表,VALUES指定插入行的每列数据。
编写INSERT语句的更安全(不过更繁琐)的方法:在INSERT INTO要插入的表中指出列的次序(可以任意次序),之后VALUES中的数据按这个次序插入到表中成为一行记录。而且指出的列可以不是表的所有列。
INSERT INTO Student( name, age ,sex)
VALUES( 'cjj', 10, 'male' );
一般使用给出列的列表的INSERT语句更好。
连续插入多行数据:
INSERT INTO Student( name, sex ,age)
VALUES( 'cjj', 'male', 10), ('xyy', 'male', 10);
可以在VALUES后用逗号分隔出多个要插入的数据。
插入检索出的数据:
INSERT还可以将一条SELECT语句的结果插入表中。这就是INSERT SELECT。
INSERT INTO Student( name, sex, age)
SELECT name,sex,age
FROM sheep_school;
INSERT SELECT中SELECT可包含WHERE过滤行记录。
2. 更新和删除数据
UPDATE和DELETE
UPDATE可以以两种方式更新表中的数据:更新表中的特定行;更新表中的所有行。
UPDATE指明要更新的表、列名和它们的新值、确定要等新的过滤条件。
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
SET命令用来将新值赋给更新的列。
UPDATE语句以WHERE子句结束,它告诉MySQL更新哪一行。没有WHERE子句,MySQL将会用这个值更新表中所有的行。
更新多个列:
UPDATE customers
SET cust_name = 'The Fudds',
cust_eamil = 'elmer@fudd.com'
WHERE cust_id = 10005;
更新多个值时,在SET的多个值之间用逗号分隔开。
IGNORE关键字:如果用UPDATE语句更新多行,并且在更新这些行中的一行或多行时出现一个错误,则整个UPDATE操作被取消。为了即使是在发生错误,也继续进行更新,可使用IGNORE关键字。
UPDATE IGNORE customers。。。
删除数据:
为了删除某个列的值,可设置它为NULL(假如表定义允许NULL值)。
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
其中NULL用来去除cust_email列中的值。
DELETE语句删除数据:删除特定的行、删除所有行。
不要省略WHERE子句。
DELETE FROM customers
WHERE cust_id = 10006;
删除了WHERE条件指定的行。
DELETE不需要列名或通配符,DELETE删除整行而不是整列。
删除整列可以用UPDATE更新SET所有行的某列为NULL。
如果想从表中删除所有行,不要使用DELETE,可使用TRUNCATE TABLE语句。他删除原来的表并重新创建一个表。
更新和删除的指导原则:
UPDATE和DELETE语句全都具有WHERE子句。如果省略了WHERE子句,则UPDATE或DELETE将被应用到表中的所有行。
MySQL没有undo撤销按钮,应该非常小心地使用UPDATE和DELETE。
3. 创建表和操纵表
创建表:
表的创建方法一般有两种:使用具有交互式创建和管理表地工具、表也可以直接用MySQL语句操纵。
CREATE TABLE关键字。
表创建应该指明:新表的名字,在关键字CREATE TABLE之后给出;表列的名字和定义,用逗号分隔。
CREATE TABLE sheep(
id int NOT NULL AUTO_INCREMENT,
name char(10) NOT NULL,
sex char(10) NOT NULL,
age char(10) NOT NULL,
food char(10) NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;
创建新表时,指定的表名必须不存在,否则将出错。
如果仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS。
使用NULL值:
NULL值就是没有值或缺值。有些列允许NULL值,有些不允许NOT NULL。
NULL值是否允许在创建时由表的定义规定。
主键:
主键必须唯一。即表中的每个行必须具有唯一的主键值。如果主键使用单列,则它的值必须唯一。如果使用多列,则这些列的组合值必须唯一。
PRIMARY KEY(id) //指明了主键列
使用AUTO_INCREMENT:
AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量。每次执行一个INSERT操作时,MySQL自动对该列增量,给该列赋予一个可用的值。这样每个行分配一个唯一的值得列,可作为主键。
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引。
覆盖AUTO_INCREMENT,当用INSERT插入行时,可以为设置AUTO_INCREMENT赋值一个自定义的值,只要他是唯一的。
指定默认值:
如果在插入行时没有给出值,MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。
CREATE TABLE sheep(
name char(10) NOT NULL,
id int AUTO_INCREMENT,
food char(10) NOT NULL DEFAULT 'grass'
)ENGINE=InnoDB;
DEFAULT指定了默认值,当插入行时没有指定该列的值就赋予这个默认值。
引擎类型:
在CREATE TABLE的表列结构的括号后用ENGINE=指定引擎类型。
InnoDB是一个可靠的事务处理引擎,它不支持文本搜索。
MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快,特别适合于临时表。
MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
引擎类型可以混用。
更新表:
ALTER TABLE语句。
使用ALTER TABLE时需要提供的信息:在ALTER TABLE之后给出要更改的表名,该表必须存在,否则将出错;所做更改的列表。
ALTER TABLE vendors
ADD vend_phone CHAR(20);
这条语句给vendors表增加了一个名为vend_phone的列,必须明确其数据类型。
ADD在更新表时加入列,DROP在更新表时删除列。
ALTER TABLE vendors
DROP COLUMN vend_phone;
ALTER TABLE的一种常见用途是定义外键。
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREGIN KEY (order_num) REFERENCES orders(order_num);
为了对单个表进行多个更改,可以使用单条ALTER TABLE语句,每个更改用逗号分隔。
删除表:
DROP TABLE关键字。删除整个表而不是其内容。
DROP TABLE sheep;
重命名表:
RENAME TABLE语句重命名一个表。
RENAME TABLE sheep TO sheeps;
对多个表进行重命名时,使用逗号分隔。
4. 使用视图
视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
视图重用SQL语句,简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节;使用表的组成部分而不是整个表;保护数据,可以给用户授予表的特定部分的访问权限而不是整个表的访问权限;更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据。
视图创建之后,可以用与表基本相同的方式使用它们。
视图本身不含数据,只是一种用来查看存储在别处的数据的一种设施。每次使用视图时都必须处理查询执行时所需的任一个检索。
视图有唯一命名,对于可创建的视图数目没有限制,为了创建视图必须具有足够的访问权限,视图可嵌套,ORDER BY可用在视图中,但如果从该视图检索数据的SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖。视图不能索引,也不能有关联的触发器或默认值。视图可和表一起使用。
使用视图:
CREATE VIEW来创建,SHOW CREATE VIEW viewname来查看创建视图的语句。
DROP删除视图,DROP VIEW viewname。更新视图时先DROP再CREATE,也可直接CREATE OR REPLACE VIEW,如果更新的视图不存在就创建一个。
查看视图结构:describe viewname; describe可简写为desc,describe也可用于描述表格结构。
查看所有的视图:information_schema数据库中的views表中。
利用视图简化复杂的联结:
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;
这条语句创建一个名为productcustomer的视图,它联结三个表。创建的视图可用于类型表一样的操作。
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'CJJ';
视图简化了复杂SQL语句的使用。
利用视图重新格式化检索出的数据:
CREATE VIEW vendoelocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ') ') AS vend_title
FROM vendors
ORDER BY vend_name;
这条语句使用与以前的SELECT语句相同的查询创建视图。之后可对视图操作获得格式化后的数据。
用视图过滤不想要的数据:
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
WHERE子句可在创建视图时使用也可在创建后对视图的操作时使用,最终的条件是两者结合。
使用视图与计算字段:
更新视图:
通常视图是可更新的。更新一个视图将更新其基表(它所代表的表)。如果对视图增加或删除行,实际上是对其基表增加或删除行。
但是并不是所有视图都可更新。如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除)。
通常有视图定义中出现以下操作时,不能进行视图更新:
分组、联结、子查询、并、聚集函数、DISTINCT、导出计算列。
一般将视图用于检索而不是更新。
视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装,可用来简化数据处理以及重新格式化基础数据或保护基础数据。