【MySQL】表的增删改查_mysql添加删除代码-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_72532428/article/details/144222743

表的增删改查

一、向表添加数据

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

创建一个表

语法：

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

使用示例：

-- 创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

1.1单行数据，全列插入

#插入数据
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO students VALUES (101, 10001, '孙悟空', '11111');
#查询结果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏     | NULL  |
| 101 | 10001 | 孙悟空     | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)

1.2多行数据，指定列插入

插入两条记录，value_list 数量必须和指定列数量及顺序一致

#插入数据
INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');

#查询结果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL      |
| 101 | 10001 | 孙悟空 | 11111     |
| 102 | 20001 | 曹孟德 | NULL      |
| 103 | 20002 | 孙仲谋 | NULL      |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

1.3插入或则更新

由于主键或者 唯一键 对应的值已经存在而导致插入失败

#主键冲突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师');
#唯一键冲突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞒');

此时就可以选择性的进行同步更新操作

语法：

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 = '唐大师';

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

1.4替换

主键或者唯一键没有冲突，则直接插入
主键或者唯一键如果冲突，则删除后再插入

语法：

REPLACE INTO [(column [, column] ...)] VALUES (value_list) [, (value_list)] ...;

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

二、Retrieve（查询）

2.1 select

用于表的查询

语法：

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

使用示例：

创建一个学生表，包含学号，姓名，年龄

create table stu(sn int primary key comment '学号',
	name varchar(32) not null comment '姓名',
	age tinyint not null comment '年龄');

插入三条数据：

insert into stu values(22011, '张三', 20);
insert into stu values(22012, '李四', 21);
insert into stu values(22013, '王五', 22);

全列查询

一般不建议使用，如果数据量多的话，会让数据库出现卡顿甚至宕机的情况

select * from stu;

结果：

mysql> select * from stu;
+-------+--------+-----+
| sn    | name   | age |
+-------+--------+-----+
| 22011 | 张三   |  20 |
| 22012 | 李四   |  21 |
| 22013 | 王五   |  22 |
+-------+--------+-----+
3 rows in set (0.00 sec)

指定列查询

指定查询的时候，顺序可以不按表中列的顺序进行查询

select name, age from stu;

结果：

mysql> select name, age from stu;
+--------+-----+
| name   | age |
+--------+-----+
| 张三   |  20 |
| 李四   |  21 |
| 王五   |  22 |
+--------+-----+
3 rows in set (0.00 sec)

查询字段也可以为表达式

select name, 10 + 10 from stu;

结果：

mysql> select name, 10 + 10 from stu;
+--------+---------+
| name   | 10 + 10 |
+--------+---------+
| 张三   |      20 |
| 李四   |      20 |
| 王五   |      20 |
+--------+---------+
3 rows in set (0.00 sec)

当然查询的时候，表达式中可以包含一个或多个字段：

向该学生表中添加三个字段：语文成绩，数学成绩，英语成绩：

alter table stu add chinese int not null comment '语文成绩' after age;
alter table stu add math int not null comment '数学成绩' after age;
alter table stu add english int not null comment '英语成绩' after age;

然后将表中的三组数据替换掉：

replace into stu values(22011, '张三', 20, 90, 98, 80);
replace into stu values(22012, '李四', 21, 90, 98, 80);
replace into stu values(22013, '王五', 22, 90, 98, 80);

表达式中包含一个字段

select sn, name, 10 + chinese from stu;

结果：

mysql> select sn, name, 10 + chinese from stu;
+-------+--------+--------------+
| sn    | name   | 10 + chinese |
+-------+--------+--------------+
| 22011 | 张三   |           90 |
| 22012 | 李四   |           90 |
| 22013 | 王五   |           90 |
+-------+--------+--------------+
3 rows in set (0.00 sec)

表达式中包含多个字段

select sn, name, chinese + math + english from stu;

结果：

mysql> select sn, name, chinese + math + english from stu;
+-------+--------+--------------------------+
| sn    | name   | chinese + math + english |
+-------+--------+--------------------------+
| 22011 | 张三   |                      268 |
| 22012 | 李四   |                      268 |
| 22013 | 王五   |                      268 |
+-------+--------+--------------------------+
3 rows in set (0.00 sec)

为查询结果字段，指定别名

select sn, name, chinese + math + english as '总分' from stu;

结果：

mysql> select sn, name, chinese + math + english as '总分' from stu;
+-------+--------+--------+
| sn    | name   | 总分   |
+-------+--------+--------+
| 22011 | 张三   |    268 |
| 22012 | 李四   |    268 |
| 22013 | 王五   |    268 |
+-------+--------+--------+
3 rows in set (0.00 sec)

将搜索的结果去重

#未去重
select math from stu;
#去重
select distinct math from stu;

结果：

mysql> select math from stu;
+------+
| math |
+------+
|   98 |
|   98 |
|   98 |
+------+
3 rows in set (0.00 sec)

mysql> select distinct math from stu;
+------+
| math |
+------+
|   98 |
+------+
1 row in set (0.00 sec)

2.2 where条件

查询的时候，让查询根据对应的条件去查：

查询语文成绩大于 60 分的同学姓名

select name from stu where chinese > 60;

结果：

mysql> select name from stu where chinese > 60;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
+--------+
3 rows in set (0.00 sec)

查询数学成绩和英语成绩都高于 80 分的同学学号

select sn from stu where math > 80 and english > 80;

结果：

mysql> select sn from stu where math > 80 and english > 80;
+-------+
| sn    |
+-------+
| 22011 |
| 22012 |
| 22013 |
+-------+
3 rows in set (0.00 sec)

查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

#使用or进行条件连接
select sn, name, age, math from stu where math = 58 or math = 59 or math = 98 or math = 99;

#或使用in条件
select sn, name, age, math from stu where math in(58, 59, 98, 99);

结果：

mysql> select sn, name, age, math from stu where math = 58 or math = 59 or math = 98 or math = 99;
+-------+--------+-----+------+
| sn    | name   | age | math |
+-------+--------+-----+------+
| 22011 | 张三   |  20 |   98 |
| 22012 | 李四   |  21 |   98 |
| 22013 | 王五   |  22 |   98 |
+-------+--------+-----+------+
3 rows in set (0.00 sec)

查询姓张的同学或某王同学的学号姓名

select sn, name from stu where name like '张%' or name like '%王';

结果：

mysql> select sn, name from stu where name like '张%' or name like '%王';
+-------+--------+
| sn    | name   |
+-------+--------+
| 22011 | 张三   |
+-------+--------+
1 row in set (0.00 sec)

查询数学成绩高于80分，且不姓张的同学学号姓名

select name from stu where math > 80 and name like '张%';

结果：

mysql> select name from stu where math > 80 and name like '张%';
+--------+
| name   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

2.3对查询到的结果排序

语法：

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

ASC 为升序（从小到大）
DESC 为降序（从大到小）
默认为 ASC

注意：没有 ORDER BY 语句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

查询学生的学号姓名总分，并且按总分升序排序

select sn, name, chinese + math + english as '总分' from stu order by '总分' asc;

结果：

mysql> select sn, name, chinese + math + english as '总分' from stu order by '总分' asc;
+-------+--------+--------+
| sn    | name   | 总分   |
+-------+--------+--------+
| 22011 | 张三   |    268 |
| 22012 | 李四   |    268 |
| 22013 | 王五   |    268 |
+-------+--------+--------+
3 rows in set (0.00 sec)

2.4 筛选分页结果

默认下标为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;

从 0 开始，筛选 n 条结果

select sn, name from stu where math > 80 limit 2;

结果：

mysql> select sn, name from stu where math > 80 limit 2;
+-------+--------+
| sn    | name   |
+-------+--------+
| 22011 | 张三   |
| 22012 | 李四   |
+-------+--------+
2 rows in set (0.00 sec)

从 s 开始，筛选 n 条结果

select sn, name from stu limit 1, 2;
select sn, name from stu limit 2 offset 1;

结果：

mysql> select sn, name from stu limit 1, 2;
+-------+--------+
| sn    | name   |
+-------+--------+
| 22012 | 李四   |
| 22013 | 王五   |
+-------+--------+
2 rows in set (0.00 sec)

mysql> select sn, name from stu limit 2 offset 1;
+-------+--------+
| sn    | name   |
+-------+--------+
| 22012 | 李四   |
| 22013 | 王五   |
+-------+--------+
2 rows in set (0.00 sec)

三、update（更新）

对查询到的结果进行列值更新

语法：

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

将张三同学的数学成绩变更为 80 分

update stu set math = 80 where name = '张三';

结果：

mysql> update stu set math = 80 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu where name = '张三';
+-------+--------+-----+---------+------+---------+
| sn    | name   | age | english | math | chinese |
+-------+--------+-----+---------+------+---------+
| 22011 | 张三   |  20 |      90 |   80 |      80 |
+-------+--------+-----+---------+------+---------+
1 row in set (0.00 sec)

将李四同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update stu set math = 60, chinese = 70 where name = '李四';

结果：

#改之前
mysql> select * from stu where name = '李四';
+-------+--------+-----+---------+------+---------+
| sn    | name   | age | english | math | chinese |
+-------+--------+-----+---------+------+---------+
| 22012 | 李四   |  21 |      90 |   98 |      80 |
+-------+--------+-----+---------+------+---------+
1 row in set (0.00 sec)

#修改
mysql> update stu set math = 60, chinese = 70 where name = '李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#改之后
mysql> select * from stu where name = '李四';
+-------+--------+-----+---------+------+---------+
| sn    | name   | age | english | math | chinese |
+-------+--------+-----+---------+------+---------+
| 22012 | 李四   |  21 |      90 |   60 |      70 |
+-------+--------+-----+---------+------+---------+
1 row in set (0.00 sec)

将所有同学的语文成绩更新为原来的 2 倍

update stu set chinese = chinese * 2;

结果：

#改之前
mysql> select chinese from stu;
+---------+
| chinese |
+---------+
|      80 |
|      70 |
|      80 |
+---------+
3 rows in set (0.00 sec)

#修改
mysql> update stu set chinese = chinese * 2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

#修改后
mysql> select chinese from stu;
+---------+
| chinese |
+---------+
|     160 |
|     140 |
|     160 |
+---------+
3 rows in set (0.00 sec)

四、delete(删除数据 )

语法：

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

删除张三同学的考试成绩

delete from stu where name = '张三';

结果：

mysql> delete from stu where name = '张三';
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+-------+--------+-----+---------+------+---------+
| sn    | name   | age | english | math | chinese |
+-------+--------+-----+---------+------+---------+
| 22012 | 李四   |  21 |      90 |   60 |     140 |
| 22013 | 王五   |  22 |      90 |   98 |     160 |
+-------+--------+-----+---------+------+---------+
2 rows in set (0.00 sec)

删除整张表数据

delete from stu;

注意：删除整表操作要慎用！

五、聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

统计班级共有多少同学

select count(*) from stu;

结果：

mysql> select count(*) from stu;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

统计数学成绩总分

select sum(math) from stu;

结果：

mysql> select sum(math) from stu;
+-----------+
| sum(math) |
+-----------+
|       158 |
+-----------+
1 row in set (0.00 sec)

统计平均总分

select avg(math + chinese + english) from stu;

结果：

mysql> select avg(math + chinese + english) from stu;
+-------------------------------+
| avg(math + chinese + english) |
+-------------------------------+
|                      319.0000 |
+-------------------------------+
1 row in set (0.00 sec)

返回英语最高分

select max(english) from stu;

结果：

mysql> select max(english) from stu;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

返回 > 70 分以上的数学最低分

select min(math) from stu where math > 70;

结果：

mysql> select min(math) from stu where math > 70;
+-----------+
| min(math) |
+-----------+
|        98 |
+-----------+
1 row in set (0.00 sec)

六、group by的使用

在select中使用group by 子句可以对指定列进行分组查询

语法:

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

使用示例：

假设现在有三个表：EMP员工表、DEPT部门表、SALGRADE工资等级表

显示每个部门的平均工资和最高工资：

select deptno,avg(sal),max(sal) from EMP group by deptno;

显示每个部门的每种岗位的平均工资和最低工资：

select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

显示平均工资低于2000的部门和它的平均工资

统计各个部门的平均工资:

select avg(sal) from EMP group by deptno

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

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

having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where

补充：MySQL语句的执行顺序为：

FROM 子句：选择数据源。
ON 子句：连接条件（如果有的话），例如在 JOIN 操作中。
JOIN 子句：如果有的话，则执行连接。
WHERE 子句：执行过滤条件。
GROUP BY 子句：分组统计。
HAVING 子句：分组过滤条件。
SELECT 子句：选择特定的列。
DISTINCT 子句：去除重复数据。
ORDER BY 子句：结果集的排序。
LIMIT 子句：结果集的限制。

表的操作知识就分享到这了，如有错误还望指出，886！！！