一、插入数据
在最开始,我们先创建了一个表,在创建表的时候,create 后面跟了表头也就是列名的相关信息,包括列名,该列数据的数据类型,该列是否是 null 的情形,那么在创建表之后,肯定是需要往表中插入数据的,也就是很多行数据,我们使用 insert into 命令来插入,首先我们先创建一个空白表:
CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
这个表我们在(一)中就已经创建过了,下面需要向其中插入数据:
INSERT INTO ProductIns
(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
可以看出其格式为:insert into 表名(列1,列2,…)values(值1,值2,…),把括号里的参数称为清单。
括号里面的参数用逗号隔开,并且注意到列名和值需要按顺序对应起来,并且插入字符串时需要加单引号。每执行一条 insert into 语句,就会向表中插入一行数据,如果遇到插入多行数据的,可以连续使用 insert into 语句,但是需要用逗号隔开,其实这已经是多条 sql 语句了 。
INSERT INTO ProductIns VALUES
('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
在这里可以看到,我们把列名省略了。在insert into 过程中,可以省略列的清单,但注意后面的 values 的清单需要按照列的顺序来插入。
- 当我们插入的数据某一行为 null 时,直接在清单中写 null 就可以了,但是需要注意的点是,在我们用 create 创建表时,其实是指定了某些列是不能为 null 的,那么在这些列下,就不能插入一行数据,但是对应该列的值为 null 。
我们在创建表的时候,除了 null 和 not null 以外还可以设置默认值,也就是 deafault 值, 比如上述代码中的 deafault 0,这表示这一列的默认值为0,那么后面再进行插入数据的时候,就可以对该列不进行赋值,比如:
INSERT INTO
ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
但其实能不能直接省略呢,答案也是可以的。
INSERT INTO
ProductIns (product_id, product_name, product_type, purchase_price, regist_date)
VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');
可以看到,有默认值的那一列直接就被省略了。values 清单会自动跳过有默认值的列。
那么如果没有默认值,并且 values 的清单里面没有该值会怎么样呢?答案是会把该单元格设置为 null,这其实也符合 null 的定义,其实这也是 sql 有自动识别的功能,因为说比如有7个列,它可以识别出来缺失的是哪个列,可能是根据数据类型来判断的吧。
但是如果这个列创建的时候就设置了 not null ,然后插入数据时还空缺了,那就不能设置为 null 了, sql 只能报错了。
- 对于插入数据,除了使用insert into 之外,还可以使用 insert into select 来复制别的表的信息。
当然还是得先创建一张表:
CREATE TABLE ProductCopy
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
然后将 product 表中的数据复制过来:
INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;
也就是说这个新表会将 product 表中选择地一些列完整地复制过来。
但是有时候我们希望复制的时候并不复制完全的原表,在前面已经学习了使用 where,group by ,having 来过滤数据,那么这里的 select 语句中一样可以使用,但是需要注意的是,在前面我们会使用 order by 来进行 select 后的排序,所以当我们在 select 子句中使用 order by 事,复制的表能不能也是排序之后的呢?答案是不能的。因为无法保证表内部记录的排列顺序。但比较有意思的是我自己实验的时候竟然成功了:
INSERT INTO ProductType (product_type, sum_sale_price,sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price) FROM Product
GROUP BY product_type;
这是不加order by 的复制语句,然后我在最后加上 order by 的排序命令:
INSERT INTO ProductType (product_type, sum_sale_price,sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price) FROM Product
GROUP BY product_type
order by sum(sale_price);
最后得到的结果却是:
实际实验中是先执行的加了 order by 子句的插入,可以发现,前三行插入数据确实是按照 order by 的顺序进行插入的,我也不知道为什么。
二、删除数据
在之前已经学过对整张表的删除,使用 drop table 表名 就可以了,和 create 创建表的格式相同,但是这是删除整张表,若是想删除单元格中的数据呢,则需要使用 delete 命令,格式为:delete from 表名,然后会将所有的行都删除,只留下表头。需要注意的是 delete 的对象是行,并不能说删除某一列,或者 delete * 这种表达也是不可取的。
但是有的时候我们不想删除所有的行,此时想到我们之前学过的用 where 子句来过滤行,比如:
DELETE FROM Product
WHERE sale_price >= 4000;
这样就会把商品价格小于4000的数据保留下来。
与 select 语句不同的是 delete 语句并不能使用 group by ,having,order by ,原因也很简单,group by 和 having 是从表中选取数据时用来改变抽取数据形式的,而 order by 是用来指定取得结果显示顺序的。因此,在删除表中数据时它们都起不到什么作用。
三、更新数据
除了对一张表插入数据和删除数据,更多情况下,我们希望是针对其中的数据进行修改,也就是 update 。这和之前对表头的修改不同,表头的修改使用的是 alter 和 rename ,在前面都已经介绍过了,而在单元格数据的修改中使用的是 set :
UPDATE Product
SET regist_date = '2009-10-10';
这表示把 regist_date 这一列都赋值成 ‘2009-10-10’,这显然是不太好的,所以,在用 set 修改单元格的时候,也可以使用 where 来进行过滤
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
在上述代码中,不仅是在后面添加了 where 子句来进行筛选,其实在 set 中甚至使用了运算,反正运算的本质还是赋值。
- null 清空,也就是将某一列所有的值全部设为 null。或者是在一定的 where条件下使用 null 清空也是可以的
除了上述的一列进行赋值,还可以实现多列赋值:
UPDATE Product
SET sale_price = sale_price * 10, purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
可以看到只要把多个更新的命令放在一个 set 中然后用逗号隔开就可以了。
四、事务
在这小节,简单介绍一下事务的概念。简单来讲,事务就是需要在同一个处理单元中执行的一系列更新处理的集合。在对表进行更新的时候有 insert into,delete,update,但是这些一般都不是单独进行的,也就是一般不会单独的插入或者赋值,而是比如先赋值,然后再删除啥的。一个事务中包含很多个更新处理。
使用事务开始语句和事务结束语句,然后将一系列 DML 语句(INSERT/UPDATE/DELETE 语句)括起来,就实现了一个事务处理。
事务开始语句在 MySQL 中是 start transaction,事务结束语句是 commit.比如以下:
START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
COMMIT;
- commit 是提交事务包含的全部更新处理的结束指令,相当于文件处理中的覆盖保存。一旦提交,就无法恢复到事务开始前的状态了。因此,在提交之前一定要确认是否真的需要进行这些更新。
- rollback 是取消事务包含的全部更新处理的结束指令,相当于文件处理中的放弃保存。一旦回滚,数据库就会恢复到事务开始之前的状态。通常回滚并不会像提交那样造成大规模的数据损失。
总结
本节讲述了有关表内数据的更新,包括 insert into,delete 以及update 的相关格式 ,着重需要注意的是这些都是针对行的处理方法,更多的是使用 where 过滤来进行局部的删除或者赋值。