【MySQL】增删查改（基础）

Wik_西瓜

已于 2022-04-18 10:55:31 修改

阅读量307

点赞数

分类专栏： MySQL 文章标签： java

于 2022-04-18 10:43:09 首次发布

本文链接：https://blog.csdn.net/Wisk1019/article/details/124243843

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL表的增删查改(基础)

文章目录

MySQL表的增删查改(基础)

1. 新增(Create)

语法

insert into table_name
	[(column [,column])...]
	values (valus_list) [, (value_list)]...
value_list: value,[,value]...

案例:

-- 创建一张学生表
create table student(
    id int,
    sn int comment '学号'，
    name varchar(20) comment '姓名',
    qq_mail varchar(20) comment 'QQ邮箱'
)；

1.1 单行数据 + 全列插入

-- 插入两条记录，value_list数量必须和定义表的列的数量及顺序一致
insert into student values(100,10000,'唐三藏',null);
insert into student values(101,10001,'孙悟空','11111');

1.2 多行数据 + 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
insert into student (id,sn,name) values(102,20001,'曹孟德')，(103,20002,'孙仲谋');

2. 查询(Retrieve)

语法：

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

案例：

-- 创建考试成绩表
create table exam_result(
    id int,
    name varchar(20),
    chinese decimal(3,1),
    math decimal(3,1),
    english decimal(3,1)
);
-- 插入测试数据
insert into exam_result (id,name,chinese,math,english) values 
 (1,'唐三藏', 67, 98, 56),
 (2,'孙悟空', 87.5, 78, 77),
 (3,'猪悟能', 88, 98.5, 90),
 (4,'曹孟德', 82, 84, 67),
 (5,'刘玄德', 55.5, 85, 45),
 (6,'孙权', 70, 73, 78.5),
 (7,'宋公明', 75, 65, 30);

2.1 全列查询

select * from exam_result;

2.2 指定列查询

select id , name,english from exam_result;

2.3 查询字段为表达式

-- 表达式不含字段
select id,name,10 from exam_result;
-- 表达式包含一个字段
select id,name,english+10 from exam_result;
-- 表达式包含多个字段
select id,name,chinese + math + english from exam_result;

2.4 别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称，语法：

select column [as] alias_name [...] from table_name;

-- 结果集中，表头的列名=别名
select id,name,chinese + math + english 总分 from exam_result;

2.5 去重：DISTINCT

使用DISTINCT关键字对某列数据进行去重

-- 98 分重复了
SELECT math FROM exam_result;
+--------+
| math |
+--------+
|     98 |
|     78 |
|     98 |
|     84 |
|     85 |
|     73 |
|     65 |
+--------+
-- 去重结果
select distinct math from exam_result;
+--------+
| math |
+--------+
|     98 |
|     78 |
|     84 |
|     85 |
|     73 |
|     65 |
+--------+

2.6 排序：ORDER BY

语法

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

没有 ORDER BY 子句的查询，返回的顺序是未定义的，
NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

select name,qq_mail from student order by qq_mail;
select name,qq_mail from student order by qq_mail desc;

使用表达式及别名排序

-- 查询同学及总分，由高到低
select name,chinese + math + english from exam_result 
order by chinese + math + english desc;

select name,chinese + math + english total from exam_result
order by total desc;

可以对多个字段进行排序，排序优先级随书写顺序

-- 查询同学各门成绩，依次按数学降序，英语，语文升序的方式显示
select name,math,english,chinese from exam_result
order by math desc,english,chinese;

2.7 条件查询：WHERE

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全例如：NULL=NULL的结果为NULL
<=>	等于，NULL安全例如：NULL<=>NULL的结果为TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0，a1]，如果a0<=value<=a1，返回TRUE(1)
IN(option,…)	如果是option中的任意一个，返回TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配，%表示任意多个(包括0个)任意字符；_表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为TRUE(1),结果才是TRUE(1)
OR	任意一个条件为TRUE(1)，结果为TRUE(1)
NOT	条件为TRUE(1)，结果为FALSE(0)

注意：

WHERE条件可以使用表达式，但不能使用别名
AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

示例：

-- 成绩表的信息
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    32.1 | 65.4 |    98.7 |
|    2 | 孙悟空    |    96.3 | 80.0 |    74.1 |
|    3 | 猪八戒    |    73.5 | 45.7 |    15.9 |
|    4 | 沙和尚    |    25.8 | 46.9 |    14.7 |
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    6 | 刘玄德    |    38.5 | 65.0 |    98.0 |
|    7 | 孙仲谋    |    33.0 | 92.0 |    58.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
|    9 | 刑道荣    |    32.1 | 65.4 |    98.7 |
|   10 | 花和尚    |    96.3 | 55.5 |    33.3 |
|   11 | 黑旋风    |    NULL | NULL |    NULL |
+------+-----------+---------+------+---------+
-- 查询英语不及格的同学及英语成绩(<60)
mysql> select name,english from exam_result where english<60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 猪八戒    |    15.9 |
| 沙和尚    |    14.7 |
| 孙仲谋    |    58.0 |
| 花和尚    |    33.3 |
+-----------+---------+
-- 查询语文成绩高于英语成绩的同学
mysql> select name,chinese,english from exam_result where chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 孙悟空    |    96.3 |    74.1 |
| 猪八戒    |    73.5 |    15.9 |
| 沙和尚    |    25.8 |    14.7 |
| 曹孟德    |    95.0 |    90.0 |
| 花和尚    |    96.3 |    33.3 |
+-----------+---------+---------+
-- 查询总分在200分一下的同学
mysql> select name,chinese+math+english 总分 from exam_result where chinese+math+english < 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 唐三藏    |  196.2 |
| 猪八戒    |  135.1 |
| 沙和尚    |   87.4 |
| 孙仲谋    |  183.0 |
| 刑道荣    |  196.2 |
| 花和尚    |  185.1 |
+-----------+--------+
-- 查询语文成绩大于80分，且英语成绩大于80分的同学
mysql> select * from exam_result where chinese > 80 and english > 80;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
+------+-----------+---------+------+---------+
-- 查询语文成绩大于80分，或者英语成绩大于80分的同学
mysql> select * from exam_result where chinese > 80 or english > 80;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    32.1 | 65.4 |    98.7 |
|    2 | 孙悟空    |    96.3 | 80.0 |    74.1 |
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    6 | 刘玄德    |    38.5 | 65.0 |    98.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
|    9 | 刑道荣    |    32.1 | 65.4 |    98.7 |
|   10 | 花和尚    |    96.3 | 55.5 |    33.3 |
+------+-----------+---------+------+---------+
-- 观察AND 和 OR 的优先级：
mysql> select * from exam_result where chinese > 80 or math >70 and english > 70;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    2 | 孙悟空    |    96.3 | 80.0 |    74.1 |
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
|   10 | 花和尚    |    96.3 | 55.5 |    33.3 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)

mysql> select * from exam_result where (chinese > 80 or math > 70) and english >70;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    2 | 孙悟空    |    96.3 | 80.0 |    74.1 |
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
+------+-----------+---------+------+---------+
范围查询：
1.BETWEEN...AND...
-- 查询语文成绩在[60，90]分的同学及语文成绩
mysql> select name,chinese from exam_result where chinese between 60 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪八戒    |    73.5 |
+-----------+---------+
-- 使用 AND 也可以实现
mysql> select name,chinese from exam_result where chinese >=60 and chinese<=90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪八戒    |    73.5 |
+-----------+---------+
2.IN
-- 查询数学成绩是50，或者65，或者80分的同学及数学成绩
mysql> select name,math from exam_result where math in (50,65,80);
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    | 80.0 |
| 曹孟德    | 50.0 |
| 刘玄德    | 65.0 |
+-----------+------+
模糊查询：LIKE
-- %匹配任意多个（包括0个字符）
mysql> select name from exam_result where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙仲谋    |
+-----------+
-- _匹配严格的一个任意字符 
mysql> select name from exam_result where name like '孙__';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙仲谋    |
+-----------+
NULL的查询：IS[NOT]NULL
-- 查询qq_mail已知的同学姓名
mysql> select name,qq_mail from student where qq_mail is not null;
+-----------------+------------------+
| name            | qq_mail          |
+-----------------+------------------+
| 黑旋风李逵      | xuanfeng@qq.com  |
| 许仙            | xuxian@qq.com    |
| 好好说话        | say@qq.com       |
| 老外学中文      | foreigner@qq.com |
+-----------------+------------------+
-- 查询qq_mail未知的同学姓名
mysql> select name,qq_mail from student where qq_mail is null;
+--------------+---------+
| name         | qq_mail |
+--------------+---------+
| 菩提老祖     | NULL    |
| 白素贞       | NULL    |
| 不想毕业     | NULL    |
| tellme       | NULL    |
+--------------+---------+

2.8 分页查询：LIMIT

语法：

-- 起始下标为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;

示例：按id进行分页，每页三条记录，分别显示第1、2、3页

mysql> select * from exam_result order by id limit 3 offset 0;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    32.1 | 65.4 |    98.7 |
|    2 | 孙悟空    |    96.3 | 80.0 |    74.1 |
|    3 | 猪八戒    |    73.5 | 45.7 |    15.9 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam_result order by id limit 3 offset 3;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    4 | 沙和尚    |    25.8 | 46.9 |    14.7 |
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    6 | 刘玄德    |    38.5 | 65.0 |    98.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam_result order by id limit 3 offset 6;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    7 | 孙仲谋    |    33.0 | 92.0 |    58.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
|    9 | 刑道荣    |    32.1 | 65.4 |    98.7 |
+------+-----------+---------+------+---------+

3. 修改(Update)

语法

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

示例：

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    32.1 | 65.4 |    98.7 |
|    2 | 孙悟空    |    96.3 | 80.0 |    74.1 |
|    3 | 猪八戒    |    73.5 | 45.7 |    15.9 |
|    4 | 沙和尚    |    25.8 | 46.9 |    14.7 |
|    5 | 曹孟德    |    95.0 | 50.0 |    90.0 |
|    6 | 刘玄德    |    38.5 | 65.0 |    98.0 |
|    7 | 孙仲谋    |    33.0 | 92.0 |    58.0 |
|    8 | 诸葛亮    |    96.3 | 85.2 |    98.7 |
|    9 | 刑道荣    |    32.1 | 65.4 |    98.7 |
|   10 | 花和尚    |    96.3 | 55.5 |    33.3 |
|   11 | 黑旋风    |    NULL | NULL |    NULL |
+------+-----------+---------+------+---------+
-- 将孙悟空同学的数学成绩变更为60分
mysql> update exam_result set math = 60 where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    | 60.0 |
+-----------+------+
-- 将曹孟德同学的数学成绩变更为60，语文成绩变更为70
mysql> update exam_result set math = 60 , chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name,chinese,math from exam_result where name = '曹孟德';
+-----------+---------+------+
| name      | chinese | math |
+-----------+---------+------+
| 曹孟德    |    70.0 | 60.0 |
+-----------+---------+------+

4. 删除(Delete)

语法：

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

示例：

-- 删除孙悟空的考试成绩
mysql> delete from exam_result where name = '孙悟空';
Query OK, 1 row affected (0.00 sec)

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    32.2 | 65.4 |    98.7 |
|    3 | 猪八戒    |    73.6 | 45.7 |    15.9 |
|    4 | 沙和尚    |    25.8 | 46.9 |    14.7 |
|    5 | 曹孟德    |    70.0 | 60.0 |    90.0 |
|    6 | 刘玄德    |    38.6 | 65.0 |    98.0 |
|    7 | 孙仲谋    |    33.0 | 92.0 |    58.0 |
|    8 | 诸葛亮    |    96.4 | 85.2 |    98.7 |
|    9 | 刑道荣    |    32.2 | 65.4 |    98.7 |
|   10 | 花和尚    |    96.4 | 55.5 |    33.3 |
|   11 | 黑旋风    |    NULL | NULL |    NULL |
+------+-----------+---------+------+---------+
-- 删除整张表数据
mysql> delete from exam_result;