【MySQL】详解MySQL中表的基本插入、删除、查询、修改语句

一、表的插入

假设我们已经创建了一张表：

CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败的时候，如果我们想说我们要插入的这条记录无论主键或者是唯一键冲突都帮我完成插入，可以选择性的进行同步更新操作语法。

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

Query OK, 2 rows affected ( 0.47 sec) -- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等 -- 1 row affected: 表中没有冲突数据，数据被插入 -- 2 row affected: 表中有冲突数据，并且数据已经被更新

        上述情况也可以采用replace（替换）的方法来解决。采用替换语句时如果主键或者唯一键没有冲突，则直接插入； 如果主键或者唯一键如果冲突，则删除后再插入。

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');

Query OK, 2 rows affected ( 0.00 sec) -- 1 row affected: 表中没有冲突数据，数据被插入 -- 2 row affected: 表中有冲突数据，删除后重新插入

二、表的查询

语法：

SELECT [DISTINCT] {* | {column [, column] ...} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...

select语句的执行顺序为from，where，select，order by，limit。重命名伴随着select。通常情况下不建议使用 * 进行全列查询，查询的列越多，意味着需要传输的数据量越大，可能会影响到索引的使用。

可以为查询结果指定别名：

SELECT column [AS] alias_name [...] FROM table_name;

SELECT id, name, chinese + math + english 总分 FROM exam_result;

 可以为查询结果去重：

SELECT DISTINCT math FROM exam_result;

 select可以和insert、delete等联合使用：

INSERT INTO table_name [(column [, column ...])] SELECT ...

2.1、where条件 

比较条件：

例：选出表中总分在 200 分以下的同学

SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english < 200;

举这个例子只想说明这里需要注意的是，别名不能出现在where子句中。

2.2、结果排序

语法：

-- ASC 为升序（从小到大） -- DESC 为降序（从大到小） -- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。NULL 视为比任何值都小，升序出现在最上面。ORDER BY 子句中可以使用列别名。

2.3、分页筛选结果

-- MySQL数据库起始下标为 0 -- 从 0 开始，筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; -- 从 s 开始，筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n; -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

三、表中数据的更新

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

例：将总成绩倒数前三的 3 位同学的数学成绩加上 30 分。

UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;

四、表中数据的删除

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

如果不带where子句，下面这条sql语句则是删除表中所有的数据，要慎用！！！ 

DELETE FROM for_delete;

 五、聚合函数

六、group by子句

在select中使用group by 子句可以对指定列进行分组查询。 可以用来分组的条件在组内一定是相同的。也可以这么理解，分组其实就是分表，分组其实就是按照条件在逻辑上拆成多个子表，然后对分别的子表进行聚合统计。

select column1, column2, .. from table group by column;

having和group by配合使用，可以对group by结果进行过滤。

select avg(sal) as myavg from EMP group by deptno having myavg<2000; 

在MySQL中，其实我们可以认为一切皆表。