简单插入、更新、删除、查找

插入INSERT(3种方式)

1.INSERT [INTO] tb_name [(col_name, ...)] {VALUES | VALUE} ({expre | DEFAULT}, ...), (...), ...

$$:INSERT info_tb VALUES (DEFAULT, '111', '小涛', DEFAULT, DEFAULT);

2.INSERT [INTO] tb_name SET col_name={expr | DEFAULT}, ...

$$:INSERT info_tb SET nick_name='小李', age=DEFAULT;

3.INSERT [INTO] tb_name [(col_name, ...)] SELECT...

$$:INSERT cp_info_tb (name) SELECT (nick_name) FROM info_tb WHERE id = 2;

更新记录（update）

1.单表更新: UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1= {expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}]... [WHERE where_condition]

$$: UPDATE info_tb SET token='222', sex=5 WHERE id = 2;

删除记录

1.单表删除: DELETE FROM tb_name [WHERE where_condition]

$$:DELETE FROM info_tb where token='222';

查询记录(SELECT)：

SELECT select_expr [, select_expr...]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | postion} [ASC | DESC], ...]

[HAVING where_condition]

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

[LIMIT {[offset, ] row_count | row_count OFFSET offset}]

]

查询表达式

每一个表达式表示想要的一列，必须有至少一个；多个列之间以英文逗号分隔；星号（*）表示所有列，tb_name.*可以表示命名表的所有列；查询表达式可以使用[AS] alias_name为其赋予别名；别名可用于GROUP BY, ORDER BY 或HAVING子句。

条件表达式(where)

对记录进行过滤，如果没有指定WHERE子句，则显示所有记录；在WHERE表达式中，可以使用MySQL支持的函数或运算符。

$$: SELECT nick_name, id FROM info_tb WHERE id%2=0;

查询结果分组（GROUP BY）

[GROUP BY {col_name | position} [ASC | DESC], ..]

$$:SELECT id, nick_name FROM info_tb GROUP BY id DESC;

分组条件(HAVING)

[HAVING where_condition]

$$:SELECT age, SUM(salary) FROM tb1 GROUP BY age HAVING SUM(salary)<5000;

对查询结果进行排序（ORDER BY）

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

$$:SELECT salary, username, age FROM tb1 ORDER BY SALARY DESC;

限制查询结果返回的数量(LIMIT)

[LIMIT {[offset, ] row_count | row_count OFFSET offset}]

$$: SELECT salary, username, age FROM tb1 limit 1, 2;