1.插入数据
INSERT INTO orders(o_num,o_date,c_id)
VALUES(30001,'2008-09-01',10001),
(30002,'2008-09-12',10003),
(30003,'2008-09-30',10004),
(30004,'2008-10-03',10005),
(30005,'2008-10-08',10001);
2.将查询结果插入到表
INSERT INTO table_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)
3.更新数据
UPDATE TABLE_name
SET column_name1 = value1,column_name2 = value2,column_name3 = value3
WHERE (condition);
4.删除数据
DELETE FROM table_name [WHERE <condition>];
5.删除所有记录
TRUNCATE将直接删除原来的表,并重新创建一个表,因此执行速度比DELETE快
TRUNCATE TABLE table_name
6.增加计算列
在CREAE TABLE和ALTER TABLE中都支持增加计算列,以CREAE TABLE为例:
CREATE TABLE tb1(
id INT(9) NOT NULL auto_increment,
a INT(9) DEFAULT NULL,
b INT(9) DEFAULT NULL,
c INT(9) GENERATED ALWAYS AS ((a + b)) VIRTUAL,
PRIMARY KEY (id)
);
插入数据a和b,c自动计算。
insert into tb1(a,b) values (100,200);
修改a后,c自动修改。