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

十七、插入数据

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

• 数据插入
  

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

• 插入完整的行
  

1. INSERT INTO Customers
2. VALUES(NULL,
3.    'Pep E. LaPew',
4.    '100 Main Street',
5.    'Los Angeles',
6.    'CA',
7.    '90046',
8.    'USA',
9.    NULL,
10.    NULL);

复制代码

上述sql语句将高度依赖于表中列的定义次序，某个列没有值，应该设置为null

1. INSERT INTO customers(cust_name,
2.    cust_address,
3.    cust_city,
4.    cust_state,
5.    cust_zip,
6.    cust_country,
7.    cust_contact,
8.    cust_email)
9. VALUES('Pep E. LaPew',
10.    '100 Main Street',
11.    'Los Angeles',
12.    'CA',
13.    '90046',
14.    'USA',
15.    NULL,
16.    NULL);

复制代码

此时该sql指定了插入具体列名，那么values的顺序就将与指定的插入列一一对应
注意：
一般建议使用这种指定插入列的INSERT语句
不论使用上述哪种INSERT语句，values必须为每个列提供值
使用指定列的INSERT语句并不用将所有列都指定出来，可以省略列，指定列的顺序也不用与数据库表中列的顺序一致，但是省略的列必须允许为NULL值或者表定义中给出默认值

• 插入多个行
  

当前可以多次执行INSERT语句实现插入多个行，但是也可以使用一条INSERT语句

1. INSERT INTO customers(cust_name,
2.    cust_address,
3.    cust_city,
4.    cust_state,
5.    cust_zip,
6.    cust_country)
7. VALUES(
8.         'Pep E. LaPew',
9.         '100 Main Street',
10.         'Los Angeles',
11.         'CA',
12.         '90046',
13.         'USA'
14.      ),
15.       (
16.         'M. Martian',
17.         '42 Galaxy Way',
18.         'New York',
19.         'NY',
20.         '11213',
21.         'USA'
22.    );

复制代码

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

• 插入检索出的数据
  

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

1. INSERT INTO customers(cust_id,
2.     cust_contact,
3.     cust_email,
4.     cust_name,
5.     cust_address,
6.     cust_city,
7.     cust_state,
8.     cust_zip,
9.     cust_country)
10. SELECT cust_id,
11.     cust_contact,
12.     cust_email,
13.     cust_name,
14.     cust_address,
15.     cust_city,
16.     cust_state,
17.     cust_zip,
18.     cust_country
19. FROM custnew;

复制代码

注意：
custnew表与customers表具有相同的表结构
上述sql中，不需要select返回的列名与customers的列名一致，它使用的是列的位置，select的第一列（不管其列名）将填充表列中指定的第一列
当然，select语句还可以使用where子句进行过滤数据！
十八、更新和删除数据

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

• 更新数据
  

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

• 删除数据
  

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

1. delete from customers
2. where cust_id = 10006;

复制代码

注意：
如果忽略了where子句，将删除所有行
更快的删除
如果想从表中删除所有行，可以使用TRUNCATE TABLE,速度更快，实际上底层是删除表后再新建一个表

• 更新和删除的指导原则
  

一定要使用带where的update和delete语句
使用where子句前先用select进行查看测试，防止where过滤的数据不正确
MySQL没有撤销命令，一定要小心使用！
十九、创建和操纵表

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

• 表的基本创建
  

1. CREATE TABLE customers
2. (
3.   cust_id      int       NOT NULL AUTO_INCREMENT,
4.   cust_name    char(50)  NOT NULL ,
5.   cust_address char(50)  NULL ,
6.   cust_city    char(50)  NULL ,
7.   cust_state   char(5)   NULL ,
8.   cust_zip     char(10)  NULL ,
9.   cust_country char(50)  NULL ,
10.   cust_contact char(50)  NULL ,
11.   cust_email   char(255) NULL ,
12.   PRIMARY KEY (cust_id)
13. ) ENGINE=InnoDB;

复制代码

需要给出以下信息：
新表的名字，再create table后给出
表列的名字和定义，用逗号隔开
注意：
在创建新的表时，指定的表名必须不存在，可以在表名后使用IF NOT EXISTS做判断

• 使用NULL值
  

null值就是没有值或者缺少值，上述创建表的sql中，cust_id、cust_name就不能为空，其他可以为空
不要将null值与空串相混淆，空串是一个有效的值，它不是无值

• 主键再介绍
  

1. PRIMARY KEY (vend_id)

复制代码

或者

1. PRIMARY KEY (order_num,order_id)

复制代码

可以使用单个列作为主键，也可以使用组合列作为主键
主键不允许使用null值

• 使用AUTO_INCREMENT
  

1. cust_id  int  NOT NULL AUTO_INCREMENT

复制代码

这样MySQL在插入数据时，自动分配一个递增的int值
使用SELECT last_insert_id()函数将能查看最后一个自增的值

• 指定默认值
  

1. quantity  int  NOT NULL DEFAULT 1

复制代码

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

• 引擎类型
  

1. ENGINE=InnoDB

复制代码

还可以使用其他引擎如MyISAM

• 更新表
  

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

1. ALTER TABLE vendors
2. ADD vend_phone CHAR(20);

复制代码

1. ALTER TABLE vendors
2. DROP COLUME vend_phone;

复制代码

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

• 删除表
  

1. DROP TABLE customers2;

复制代码

• 重命名表
  

1. RENAME TABLE customers2 TO customers;

复制代码

二十、使用视图

本章将介绍何为视图，怎样工作的，何时使用，利用视图简化sql
需求：从三个表中检索数据

1. SELECT cust_name, cust_contact
2. FROM customers, orders, orderitems
3. WHERE customers.cust_id = orders.cust_id
4.   AND orderitems.order_num = orders.order_num
5.   AND prod_id = 'TNT2';

复制代码

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

1. SELECT cust_name,cust_contact
2. FROM productcustomers
3. WHERE prod_id ='TNT2';

复制代码

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

• 为什么使用视图
  

重用SQL语句，简化复杂的SQL操作，使用表的组成部分、保护数据、更改数据格式和表示
视图本身不包含数据，返回的数据是从其他表中检索出来的
性能问题
每次使用视图之前都要处理查询后返回给视图，处理复杂多表联查会降低性能
注意：
视图不能有索引、必须有足够的访问权限

• 使用视图
  

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

• 使用视图简化复杂联结
  

1. CREATE VIEW productcustomers AS
2. SELECT cust_name, cust_contact, prod_id
3. FROM customers, orders, orderitems
4. WHERE customers.cust_id = orders.cust_id
5.   AND orderitems.order_num = orders.order_num;

复制代码

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

1. SELECT cust_name,cust_contact
2. FROM productcustomers
3. WHERE prod_id ='TNT2';

复制代码

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

• 使用视图重新格式话检索的数据
  

1. CREATE VIEW vendorlocations AS
2. SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
3.        AS vend_title
4. FROM vendors
5. ORDER BY vend_name;

复制代码

• 用视图过滤不想要的数据
  

1. CREATE VIEW customeremaillist AS
2. SELECT cust_id, cust_name, cust_email
3. FROM customers
4. WHERE cust_email IS NOT NULL;

复制代码

• 使用视图与计算字段
  

1. CREATE VIEW orderitemsexpanded AS
2. SELECT order_num,
3.        prod_id,
4.        quantity,
5.        item_price,
6.        quantity*item_price AS expanded_price
7. FROM orderitems;

复制代码

• 更新视图
  

其实，对视图更新也就是对视图表示的基表进行更新，通过视图增加和删除一行，也是对基表进行增加和删除
但是如果视图涉及到分组、联结、子查询、并、聚集函数、DISTINCT、计算列那将不能进行更新
一般将视图用于检索而不用于更新

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！