《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、计算列那将不能进行更新

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

作者:ChangesWorlds原文地址:https://www.cnblogs.com/Changes404/p/16960428.html

%s 个评论

要回复文章请先登录注册