MySQL - 表的增删查改

1.新增

1.1语法

INSERT [INTO] table_name [(column1, column2, ...)]
VALUES (value1, value2, ...), (value1, value2, ...), ...

1. INSERT [INTO]: INTO 是可选的，但通常会被包括在内，以明确指出这是一个插入操作。
2. table_name: 要插入数据的表的名称。
3. (column1, column2, …): 可选的列列表。如果提供，VALUES 子句中的每个值列表必须与列列表中的列一一对应。如果省略，则必须为每个表列提供一个值，并且值的顺序必须与表中列的顺序相同。
4. VALUES: 跟随一个或多个值列表，每个值列表用括号括起来，并用逗号分隔（如果有多个值列表）。每个值列表中的值数量必须与列列表中的列数量（如果提供了列列表）或表中的列数量（如果省略了列列表）相匹配。

1.2单行插入

学生表

create table stu(
    id int PRIMARY KEY AUTO_INCREMENT,	
    name varchar(20) not null,
     qq varchar(20) not null
    );

单行插入：指定姓名和qq列插入。

insert into stu(name,qq) value('张三','123456');

1.3多行插入

指定姓名和qq列插入。

insert into stu(name,qq) value('李四','1234567'),('王五','12345678');

全列多行插入。

insert into stu  value(4,'老六','123456897'),(5,'田七','132345678');

注意：省略列列表默认是全列插入。

上述插入后的表数据：

1.4插入后更新

1.4.1语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
ON DUPLICATE KEY UPDATE
column1 = value1,
column2 = value2,
...

1. INSERT INTO table_name (column1, column2, column3, …)：指定要插入数据的表名以及要插入数据的列。
2. VALUES (value1, value2, value3, …)：指定对应列的值。
3. ON DUPLICATE KEY UPDATE：这是一个条件子句，当尝试插入的数据会导致主键或唯一索引冲突时触发。
4. column1 = value1, column2 = value2, …：在键冲突时，更新指定的列到新的值。…;

1.4.2使用
将id =1的学生更换名字和qq。

insert into stu(id,name,qq) value(1,'zhangsan','123') ON DUPLICATE KEY UPDATE  name = 'zhangsan' ,qq = '123';

注意：

– 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，并且数据已经被更新

更新后：

1.5替换

当主键或者唯一键冲突时进行替换。

1.5.1语法

REPLACE INTO	table_name (column1, column2, column3, ...)  
VALUES (value1, value2, value3, ...)

1.5.2使用
对id=2的学生替换。

replace  into stu(id,name,qq) value(2,'lisi','1234');

注意：

– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，删除后重新插入

替换后：

2.查找

2.1语法

SELECT 
    [DISTINCT] { * | column1 [, column2, ...] }
FROM 
    table_name
[WHERE condition]
[ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC], ...]]
[LIMIT number [OFFSET offset]];

1. SELECT：这是 SQL 语句的开始，用于指定要从表中检索哪些列的数据。
2. DISTINCT：可选的关键字，用于确保查询结果中的记录是唯一的（即去除重复的记录）。
3. { * | column1 [, column2, …] }：可以选择检索所有列（使用 *）或指定一个或多个列名。
4. FROM：指定要从哪个表中检索数据。
5. table_name：要查询的表的名称。
6. WHERE：可选的部分，用于指定筛选条件，只有满足条件的记录才会被包含在查询结果中。
7. condition：一个或多个条件，用于过滤记录。
8. ORDER BY：可选的部分，用于指定如何对查询结果进行排序。
9. column1 [ASC| DESC] [, column2 [ASC | DESC], …]：可以指定一个或多个列进行排序，每个列后面可以跟着 ASC（升序，默认）或 DESC（降序）。
10. LIMIT：可选的部分，用于限制查询结果的数量。
11. number：要返回的记录数量。
12. [OFFSET offset]：可选的部分，用于指定从哪条记录开始返回结果（通常用于分页）。

2.2使用

2.2.1全表查询

select * from stu;

2.2.2指定列查询

select id, name from stu;

2.2.3查询字段为表达式
补充：在SELECT表达式后可以跟表达式。

select id, name,id+10 from stu;

2.2.4为查询结果指定别名
2.2.4.1语法

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

1. column：更改前的名字。
2. [AS]：AS 关键字用于为列或表达式指定一个别名，可选可不选。
3. alias_name：需要更改的名字。

2.2.4.2使用

select id as 编号 , name as 姓名,id+10 from stu;

2.2.5对查询后的结果去重

select DISTINCT  qq from stu;

2.2.6WHERE 条件
2.2.6.1比较运算符
注意：NULL不参与计算的，所以对于NULL来说有特定的方式判断。

2.2.6.2逻辑运算符

2.2.6.3使用
查询 id 在3到6的学生。

select * from stu where id  BETWEEN  3 AND 6;	//1
select * from stu where id >= 3 AND id <= 6; 	//2

2.2.7排序
2.2.7.1语法

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

2.2.7.2使用
更换测试表

create table stu1
( id int PRIMARY KEY AUTO_INCREMENT, 
name varchar(20) not null, 
sum int);

insert into stu1(name,sum) value('李四',100),('王五',102),('老六',99);

对sum进行降序排序

select *  from stu1 order by sum desc;

2.2.7.3注意

//能够正确运行
select  sum as 成绩  from stu1 order by 成绩 desc;

//会报错
select  sum as 成绩 from stu1 where 成绩 >= 100;

为什么导致上面的问题呢？
顺序问题：

2.2.8筛选分页结果
2.2.8.1语法

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

2.2.8.1使用
筛选前2条记录

 select *  from stu1  limit 2;

筛选下标1到2的记录

select *  from stu1  limit 1 ,2;

实现分页效果，每一页一行。

select *  from stu1  limit 1 OFFSET 0;
select *  from stu1  limit 1 OFFSET 1;
select *  from stu1  limit 1 OFFSET 2;

3.修改

3.1语法

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

1. UPDATE table_name：此部分指明了要更新数据的表名。
2. SET column = expr [, column = expr …]：在 SET 子句中，您需要列出希望更新的列及其对应的新值（或表达式）。您可以同时更新多列，各更新项之间用逗号分隔。
3. [WHERE …]：WHERE 子句是可选的，但强烈建议使用，以避免无意中更新表中的所有记录。通过 WHERE 子句，您可以指定更新操作应针对哪些记录执行。
4. [ORDER BY …]：尽管一些 DBMS（例如 MySQL）支持在 UPDATE语句中使用 ORDER BY 来定义更新操作的顺序，但并非所有系统都支持此功能。
5. [LIMIT …]：LIMIT 子句同样并非在所有 DBMS 中都受支持。它用于限制更新操作所影响的记录数量。例如，在 MySQL 中，您可以使用LIMIT 来确保只更新前 N 条匹配的记录。

3.2使用

把id=1的同学的sum改为200。

UPDATE stu1 SET sum = 200 where id = 1;

注意：不是用where子句进行筛选时，修改的目标是全表。

4.删除

4.1语法

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

1. DELETE FROM table_name：此部分指明了要从哪个表中删除数据。
2. [WHERE …]：WHERE子句是可选的，但强烈建议使用。通过指定条件，WHERE子句能够确保仅删除满足特定条件的记录，从而避免误删数据。若省略此子句，表中的所有记录都将被删除。
3. [ORDER BY …]：虽然某些 DBMS（例如 MySQL）支持在 DELETE 语句中使用 ORDER BY
   来定义删除操作的顺序，但并非所有系统都具备此功能。
4. [LIMIT …]：LIMIT 子句同样并非在所有 DBMS 中都受支持。它用于限制删除操作所影响的记录数量。例如，在 MySQL 中，LIMIT 可以确保只删除前 N 条匹配的记录。

4.2使用

删除id=1的学生。

delete from stu1 where id = 1;

删除整张表的操作（慎用）：

DELETE FROM delete_name;

4.3截断表

4.3.1语法

TRUNCATE [TABLE] table_name

table_name：表名

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作。
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚。
3. 会重置 AUTO_INCREMENT 项（普通删除不会重置）。

4.3.2使用

 TRUNCATE TABLE stu;

删除前 AUTO_INCREMENT = 7

删除后会进行重置

截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作。

5.插入查询结果

将查询到的结果进行插入。

5.1语法

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

SELECT …：查询结果。

5.2使用

删除表a中的的重复复记录。
操作：先创建与表a结构一样的表b，再将表a去重后的查询结果插入表b，最后删除表a，对表b进行重命名。

create table a(id int);
insert into a value(1),(1),(2),(2),(3);

create table b(id int);
insert into b select DISTINCT* from a;

drop table a;
RENAME TABLE b TO a;