[大数据]MySQL的增删查改(第一话)

CRUD:Create,Retrieve,Update,Delete

文章目录

• 新增(create)
• • 单行数据+全列插入
  • 多行数据+指定列插入
• 查询(Retrieve)
• • 全列查询
  • 指定列查询
  • 查询字段为表达式
  • 别名
  • 去重:DISTINCT
  • 排序:ORDER BY
  • 条件查询:WHERE
  • 分页查询:LIMIT
• 修改(Update)
• 删除(Delete)

新增(create)

语法:

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

案例:

-- 创建一张学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT,
  sn INT comment '学号',
  name VARCHAR(20) comment '姓名',
  qq_mail VARCHAR(20) comment 'QQ邮箱'
);

单行数据+全列插入

-- 插入两条记录,value_list 数量必须和定义表的列的数量以及顺序一致
INSERT INTO student VALUES (100, 10000, '金蝉子', NULL);
INSERT INTO student VALUES (101, 10001, '孙行者', '2455380921');

多行数据+指定列插入

-- 插入两条记录,value_list 数量必须和指定列数量及顺序一致
INSERT INTO student (id, sn, name) VALUES
(102, 20001, '曹操'),
(103, 20002, '孙权');

查询(Retrieve)

语法:

SELECT
  [DISTINCT] {...}
  [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,91),
       (4,'曹孟德',82,84,67),
       (5,'刘玄德',55.5,85,45),
       (6,'孙权',70,73,78.5),
       (7,'宋公明',75,65,30);

全列查询

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

– 1.查询的列越多,意味着需要传输的数据量越大;
– 2.可能会影响到索引的使用
SELECT * FROM exam_result;

指定列查询

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english FROM exam_result;

查询字段为表达式

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;

-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;

-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

别名

为了查询结果中的列再去指定别名,表示返回的结果集合,以别名作为该集合列的名称

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

-- 结果集中,表头的列名=别名
SELECT id, name,chinese + math + english 总分 FROM exam_result;
SELECT id, name,chinese + math + english AS 总分 FROM exam_result;

去重:DISTINCT

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

SELECT math FROM exam_result;

👆这里98分重复了,去掉了一个98分

SELECT DISTINCT math FROM exam_result;

排序: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;

条件查询:WHERE

比较运算符:

1. SQL在进行条件查询的时候,需要遍历数据,带入条件,遍历操作在数据集合非常大的时候,是比较低效的.数据库内部会做出一些优化手段,来尽可能得避免遍历.在进行优化的时候,MySQL自身实现的一些行为,相比于直接使用and是更优化的,比如between and.
2. 我们通过like来完成模糊查找,意思就是不一定完全相等,只要有一部分匹配即可

%葛:匹配以葛结尾的数据

%葛%:匹配包含葛的数据源
%:匹配任意数据
_葛:匹配一个以葛为结尾的数据
葛:匹配包含葛的一个数据(不包含首尾是葛的)
_:匹配空集

示例如下:👇

其他用法示例:👇

注:

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

代码示例:👇

1. 基本查询

-- 查询英语不及格的同学以及英语成绩(<60)
SELECT name, english FROM exam_result WHERE english < 60;

--查询语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result WHERE chinese > english;

-- 查询总分在200分以下的同学
SELECT name, chinese + math + english AS 总分 FROM exam_result
  WHERE chinese + math + english < 200;

2. AND与OR

-- 查询语文成绩大于80,且英语成绩大于80的同学
SELECT name FROM exam_result WHERE chinese>80 AND english>80;

-- 查询语文成绩大于80分,或英语成绩大于80的同学
SELECT name 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;

3. 范围查询

• BETWEEN…AND…

-- 查询语文成绩在[80,90]分的同学及语文成绩
SELECT name FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 使用AND也可以实现
SELECT name FROM exam_result WHERE chinese>=80 AND chinese<=90;

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

• NULL 的查询:IS [NOT] NULL

-- 查询qq_mail已经明确的同学的姓名
SELECT name,qq_mail FROM student WHERE qq_mail IS NOT NULL;

分页查询: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;
-- LIMIT和OFFSET对应的对象要注意不要搞反,OFFSET可以理解为本页第一条记录从哪里开始.

案例:按照id来进行分页,每页有3条记录,分别显示1\2\3页

-- No.1
SELECT id,name,english,chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;
-- No.2
SELECT id,name,english,chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;
-- No.3
SELECT id,name,english,chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6;

修改(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;

这里报了一个这样的错误,错误原因是表字段数据类型设计时超出范围,我们的分数设为了decimal(3,1),也就是三位有效数字,保留一位小数.

那我们再看原来它的数学成绩

这里的数据加上30之后有一些就是四位有效数字了,不符合decimal(3,1)的要求,所以命令会报错.
那我们看到这倒数前三分别是60.0/65.0/73.0.我们将73.0的数据进行修改


👆好吧还是报错了.为什么呢?仔细审题,这里的命令行语句是针对总分排名,而并不是数学单独的排名,所以我们吧ORDER BY 后面的总分改为数学即可.

成功了.

删除(Delete)

语法:

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

案例:

-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name='孙悟空';

-- 删除语文成绩为67的同学的成绩(即唐三藏)
DELETE FROM exam_result WHERE chinese = 67;

-- 删除整张表的数据
-- 准备测试表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (
  id INT;
  name VARCHAR(20)
);
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('Gremmie') ('Bonnie') ('honey');
-- 删除整表的数据
DELETE FROM for_delete;

希望能帮到你~