插入、更新与删除
插入insert
*插入单行
NSERT INTO T
VALUES (
DEFAULT,
X2,
NULL/DEFAULT,
…) (所有列都必须一一对应)
INSERT INTO T (
C2_NAME,
C1_NAME,
C5_NAME
)
VALUES(
X2,
X1,
X5
)(不需要一一对应,也不需要写NULL和DEFAULT)
*插入多行
INSERT INTO T (C1_NAME)
VALUES (X11),
(X12),
(X13)
*多张关联表的插入,父表记T1,子表记T2,…
LAST_INSERT_ID() (上一个插入的id)
INSERT INTO T1
VALUES
(X1,X2,…);
INSERT INTO T2
VALUES
(LAST_INSERT_ID(),X21,X31,X41),
(LAST_INSERT_ID(),X22,X32,X42),
建表(create)
*复制整张表
CREATE TABLE T AS
SELECT * FROM T1(复制的表不会像原表那样有主键,自增等)
*复制部分表
INSERT INTO T
SELECT *
FROM T1
WHERE C1 < ‘2019-01-01’
更新(update)
*更新单行
UPDATE T
SET C2 = X2,C3 = X3
WHERE C1 = X1
*更新多行
UPDATE T
SET C2 = X2,C3 = X3
WHERE C4 IN (X4,X5,X1)
(需要关闭sql设置里的safe update)
*UPDATE中的子查询
UPDATE T
SET C1 = X1,C2 = X2
WHERE C3 =
(SELECT C4
FROM T1
WHERE C5 = X5)
–WHERE C3 IN
(SELECT C4
FROM T1
WHERE C5 IN (X5,X6))
删除(delete)
*删除行
DELETE FROM T
WHERE C1 = X1
– WHERE C1 IN (
SELECT语句
)
*删除整张表的所有记录
DELETE FROM T