经常不用,忘得一干二净啊….
插入一行或多行
1 指定列名插入记录,可以多行插入
INSERT [INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
2 设置某列的值为一个特定值
INSERT [INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
3 根据从其他表选择出的视图插入,注意指定列名,不指定时表示全部列名
INSERT [INTO] tbl_name [(col_name,...)]
SELECT ...
更新一行
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
例子:
如果您在一个表达式中通过tbl_name访问一列,则UPDATE使用列中的当前值。例如,以下语句把年龄列设置为比当前值多一:
mysql> UPDATE persondata SET age=age+1;
UPDATE赋值被从左到右评估。例如,以下语句对年龄列加倍,然后再进行增加:
mysql> UPDATE persondata SET age=age*2, age=age+1;
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
强大的选择语句
where –> group by –> having –> order by –> limit
SELECT
[ALL | DISTINCT | DISTINCTROW ] # 选择约束,是否是不重复的
select_expr, ... # 选择的列名以及表达式等
[FROM table_references
[WHERE where_definition] # where子句
[GROUP BY {col_name | expr | position} # group by 子句
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition] # having 子句
[ORDER BY {col_name | expr | position} # order by 子句
[ASC | DESC] , ...] # 升序(ascending),降序descending)
[LIMIT {[offset,] row_count | row_count OFFSET offset}] # limit子句
删除一行或多行
DELETE FROM tbl_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]