MySQL表的增删改查（基础）

目录

1. CRUD

2. 新增（Create） 

2.1 单行数据 + 全列插入 

2.2 多行数据 + 指定列插入  

3. 查询（Retrieve） 

3.1 全列查询 

3.2 指定列查询 

3.3 查询字段为表达式  

3.4 别名

3.5 去重：DISTINCT  

3.6 排序：ORDER BY 

3.7 条件查询：WHERE 

3.8 分页查询：LIMIT  

4. 修改（Update）

 5. 删除（Delete）

1. CRUD

• 注释：在SQL中可以使用“–空格+描述”来表示注释说明
• CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写

2. 新增（Create） 

这里的新增是向表中插入数据

语法：

INSERT  INTO  表名  [(列名，列名,……)]  VALUES(属性值，属性值，……);

[ ]中内容是为了指定列进行插入，是可省略的，如果省略，则value_list（指VALUES( )）中要写出表中全部列的属性值，顺序也要一样。

举例：

—
创建一张学生表

DROP TABLE IF EXISTS
student;

CREATE TABLE
student (

  id
INT
,

  sn
INT
comment
‘
学号
‘
,

  name
VARCHAR
(
20
)
comment
‘
姓名
‘
,

  qq_mail
VARCHAR
(
20
)
comment
‘QQ
邮箱
‘

); 

使用
comment
增加字段说明

2.1 单行数据 + 全列插入 

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

INSERT INTO
student
VALUES
(
100
,
10000
,
‘
唐三藏
‘
,
NULL
);

INSERT INTO
student
VALUES
(
101
,
10001
,
‘
孙悟空
‘
,
‘11111’
);

2.2 多行数据 + 指定列插入  

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

INSERT INTO
student (id, sn, name)
VALUES

(
102
,
20001
,
‘
曹孟德
‘
),

(
103
,
20002
,
‘
孙仲谋
‘
);

3. 查询（Retrieve） 

语法：

SELECT

        [
DISTINCT
] {* | column [,
column
] …}

        [
FROM table_name
]

        [
WHERE
…]

        [
ORDER BY column
[
ASC
|
DESC
], …]

        LIMIT
…

举例：

—
创建考试成绩表

DROP TABLE IF EXISTS
exam_result;

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
);

3.1 全列查询 

— 通常情况下不建议使用 * 进行全列查询

— 1.
查询的列越多，意味着需要传输的数据量越大；

— 2.
可能会影响到索引的使用。

       SELECT * FROM 表名;

3.2 指定列查询 

— 指定列的顺序不需要按定义表的列的顺序

SELECT id, name, english FROM exam_result; ​​​​​​​ 

3.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;

3.4 别名

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

SELECT 列名 [AS] 别名 […] FROM 表名;

—
结果集中，表头的列名
=
别名

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

3.5 去重：DISTINCT  

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

— id为3，重复了

 

— 去重后

 

3.6 排序：ORDER BY 

语法：

— ASC
为升序（从小到大）

— DESC
为降序（从大到小）

—
默认为
ASC

SELECT
…
FROM table_name
[
WHERE
…]

        ORDER BY column
[
ASC
|
DESC
], […];​​​​​​​

1.
没有
ORDER BY
子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2. NULL
数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

—
查询同学姓名和
qq_mail
，按
qq_mail
排序显示

SELECT
name, qq_mail
FROM
student
ORDER BY
qq_mail;

SELECT
name, qq_mail
FROM
student  
ORDER BY
qq_mail
DESC
;

 3. 使用表达式及别名排序

—
查询同学及总分，由高到低

SELECT
name, chinese + english + math
FROM
exam_result

        ORDER BY
chinese + english + math
DESC
;

SELECT
name, chinese + english + math as total
FROM
exam_result

        ORDER BY
total
DESC
;

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

—
查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

SELECT
name, math, english, chinese
FROM
exam_result

        ORDER BY
math
DESC
, english, chinese;

这个查询的意思为，按数学降序排列，在数学成绩相同情况下，再按英语升序排列，在英语成绩相同情况下，再按语文升序排列。

3.7 条件查询：WHERE 

比较运算符： 

逻辑运算符：

注
：

1. WHERE
条件可以使用表达式，
但不能使用别名。

2.
AND的优先级高于OR
，在同时使用时，需要使用小括号
()
包裹优先执行的部分

举例：

• 基本查询 

—
查询英语不及格的同学及英语成绩
( < 60 )

SELECT
name, english
FROM
exam_result
WHERE
english english;

—
查询总分在
200
分以下的同学

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

WHERE
chinese + math + english <
200
;

• AND 与 OR 

—
查询语文成绩大于
80
分，且英语成绩大于
80
分的同学

SELECT
*
FROM
exam_result
WHERE
chinese >
80
and
english >
80
;

—
查询语文成绩大于
80
分，或英语成绩大于
80
分的同学

SELECT
*
FROM
exam_result
WHERE
chinese >
80
or
english >
80
;

—
观察
AND
和
OR
的优先级：

SELECT
*
FROM
exam_result
WHERE
chinese >
80
or
math>
70
and
english >
70
;

SELECT
*
FROM
exam_result
WHERE
(chinese >
80
or
math>
70
)
and
english >
70
;

• 范围查询 

1、BETWEEN … AND …

— 查询语文成绩在
[80, 90]
分的同学及语文成绩

SELECT
name, chinese
FROM
exam_result
WHERE
chinese
BETWEEN
80
AND
90
;

—
使用
AND
也可以实现

SELECT
name, chinese
FROM
exam_result
WHERE
chinese >=
80
AND
chinese <=
90
;

2、IN

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

SELECT
name, math
FROM
exam_result
WHERE
math
IN
(
58
,
59
,
98
,
99
);

—
使用
OR
也可以实现

SELECT
name, math
FROM
exam_result
WHERE
math =
58
OR
math =
59
OR
math

=
98
OR
math =
99
; 

• 模糊查询：LIKE 

— %
匹配任意多个（包括
0
个）字符

SELECT
name
FROM
exam_result
WHERE
name
LIKE
‘
孙
%’
;
—
匹配到孙悟空、孙权

— _
匹配严格的一个任意字符

SELECT
name
FROM
exam_result
WHERE
name
LIKE
‘
孙
_’
;
—
匹配到孙权

• NULL的查询：IS [NOT] NULL 

—
查询
qq_mail
已知的同学姓名

SELECT
name, qq_mail
FROM
student
WHERE
qq_mail
IS NOT
NULL
;

—
查询
qq_mail
未知的同学姓名

SELECT
name, qq_mail
FROM
student
WHERE
qq_mail
IS
NULL
;

3.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进行分页，每页3条记录，分别显示第1、2、3页 

—
第
1
页

SELECT
id, name, math, english, chinese
FROM
exam_result
ORDER BY
id
LIMIT
3

OFFSET
0
;

—
第
2
页

SELECT
id, name, math, english, chinese
FROM
exam_result
ORDER BY
id
LIMIT
3

OFFSET
3
;

—
第
3
页，如果结果不足
3
个，不会有影响

SELECT
id, name, math, english, chinese
FROM
exam_result
ORDER BY
id
LIMIT
3

OFFSET
6
;

4. 修改（Update）

语法：

UPDATE table_name SET column
= expr [,
column
= expr …]

        [
WHERE
…] [
ORDER BY
…] [
LIMIT
…]

举例：

—
将孙悟空同学的数学成绩变更为
80
分

UPDATE
exam_result
SET
math =
80
WHERE
name =
‘
孙悟空
‘
;

—
将曹孟德同学的数学成绩变更为
60
分，语文成绩变更为
70
分

UPDATE
exam_result
SET
math =
60
, chinese =
70
WHERE
name =
‘
曹孟德
‘
;

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

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

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

UPDATE
exam_result
SET
chinese = chinese *
2
;

 5. 删除（Delete）

语法：

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

举例：

—
删除孙悟空同学的考试成绩

DELETE FROM
exam_result
WHERE
name =
‘
孙悟空
‘
;

—
删除整张表数据

—
准备测试表

DROP TABLE IF EXISTS
for_delete;

CREATE TABLE
for_delete (

id
INT
,

name
VARCHAR
(
20
)

);

—
插入测试数据

INSERT INTO
for_delete (name)
VALUES
(
‘A’
), (
‘B’
), (
‘C’
);

—
删除整表数据

DELETE FROM
for_delete;

这里删除的是表中的数据，而不是表。