DML基本操作(MySQL)

1. INSERT 语句

# 插入-指定字段
INSERT INTO test2 (`name`, `price`) VALUES ('one',22), ('two', 33);
# 插入-不指定，values顺序需要和表字段一致
INSERT INTO test2 VALUES (NULL, 'three', 33), (NULL, 'four', 44);

2. UPDATE

UPDATE test2 SET price = 4411 WHERE `name` = 'four'

3. 删除

# 返回删除影响条数【触发trigger，可回滚】
DELETE FROM test2 ORDER BY id DESC LIMIT 2
# DDL的删除【相当于初始化表】
TRUNCATE TABLE test2

4. 查询

# 限制+排序查询
SELECT * FROM test2 ORDER BY id DESC LIMIT 3;
# 应用聚合函数
SELECT COUNT(id),SUM(price),MIN(price),MAX(price),AVG(price) FROM test2 ORDER BY id DESC;
# 去重查询
SELECT DISTINCT(`name`) FROM test2 LIMIT 10