操作数据表中的记录
- 增
- 删
- 改
查
插入 INSERT
INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…)…
- 省略col_name则表示要插入所有字段,所有字段都要赋值否则报错
- 为自增的字段赋值可用NULL/DEFAULT,他仍遵守递增的规则,此外为有默认值的项赋值DEFAULT代表默认值
- 所赋值可以为表达式
插入id,name,password,score
INSERT users_table VALUES(NULL,’wm’,123456,DEFAULT)>
INSERT users_table VALUES(NULL,’wm’,123456,DEFAULT),VALUES(3*7,’wm’,md(‘123’),DEFAULT)
- INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…(此方法与第一种方法的区别在于可以使用子查询SubQuery,且只能插入一条数据)
INSERT users SET username=’wm’,password=’123’ INSERT [INTO] tbl_name [(col_name,…)] SELECT …(将查询结果写入到指定数据表中)
将 user_table 中age大于30的数据中的username写入到test_table表(只有一列)
INSERT test SELECT username WHERE age>=30INSERT test(username) SELECT username WHERE age>=30
单张表更新 UPDATE
- UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr1 | DEFAULT}] … [WHERE where_condition] (单张表更新)
UPDATE user_table SET age = age + 5 WHERE id % 2 = 0 (偶数位id age加5)- UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr1 | DEFAULT}] … [WHERE where_condition] (单张表更新)
单张表删除 DELETE
- DELETE FROM tbl_name [WHERE where_condition] (单张表删除)
DELETE FROM user_table WHERE id = 6- DELETE FROM tbl_name [WHERE where_condition] (单张表删除)
查找 SELECT
- SELECT select_expr [,select_expr…]
- [FROM tab_references
- [WHERE where_condition]
- [GROUP BY {col_name | position }[ASC| DESC],…]
- [HAVING where_condition]
- [ORDER BY {col_name | expr | position} [ASC | DESC],…]
- [LIMIT {[offset,] row_count | row_count FOOSET offset}]
] - select_expr: 查询表达式
- 每一个表达式表示想要的一列,必须有至少一个
- 多个列用英文逗号隔开
- 型号( * )表示所有列,tbl_name.* 可以表示命名表的所有列
- 查询表达式可以使用[AS] alias_name 为其赋予别名
- 别名可用于 GROUP BY ,ORDER BY 或HAVING子句
- select字段顺序可影响到查找结果出现的顺序,别名也一样会影响
SELECT id AD user_id, username AS u_name FROM user_table
WHERE
[WHERE where_condition]
对记录过滤,如果没有指定WHERE子句,则显示所有记录
在WHERE表达式中,可以使用MySQL支持的函数或表达式
GROUP BY
查询结果分组
[GROUP BY {col_name | position }[ASC| DESC],…]SELECT sex FROM user_table GROUP BY sex (对性别进行分组)
HAVING
[HAVING where_condition] 分组条件 对某一部分进行分组
SELECT sex FROM GROUP BY sex HAVING age > 35SELECT sex,age FROM user_table GROUP BY sex HAVING age > 35 (*用时必须在select查找字段内中出现*) SELECT sex FROM FROM user_table GROUP BY sex HAVING count(id) > 2 (*或者聚合函数最大值,最小值等,因为只会出现一个值*)
ORDER BY
[ORDER BY {col_name | expr | position} [ASC | DESC],…]
SELECT * FROM user_table ORDERBY id DESC (降序)
SELECT * FROM user_table ORDERBY age id DESC (按着age降序排列,当age相同时这些相同的数据再按着id降序排列)LIMIT
[LIMIT {[offset,] row_count | row_count FOOSET offset}] 限制查询数量
SELECT * FROM user_table LIMIT 10 (从第一条数据开始返回,一共10条)
SELECT * FROM user_table LIMIT 2,2 (返回第3,4条,数据从第0开始,从第2+1条开始,返回两条)