MySQL中的增删改查1

注：此博文为本人学习过程中的笔记

1.CRUD

CRUD即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

注：SQL的注释方法--后加一个空格即可注释

2.新增(Create)

语法

INSERT [INTO] table_name(colum...) values (value_list...);

案例

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

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...}
    [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(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 exam_result;

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 column [AS] alias_name [...] FROM table_name;

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

3.5.去重：DISTINCT

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

select distinct math from exam_result;

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 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.条件查询 

比较运算符：

>,>=.<,<=        大于，大于等于，小于，小于等于

=        等于，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        模糊匹配。%表示任意多个任意字符；_表示任意一个字符         

逻辑运算符：

and        多个条件必须都为TRUE(1)，结果才是TRUE(1)

or        任意一个条件为TRUE(1)，结果为TRUE(1)

not        条件为TRUE(1), 结果为FALSE(0)

注：

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

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

案例：

基本查询：

-- 查询英语不及格的同学及英语成绩
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 总分 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 > 80 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_resutl where name like '孙_';

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;
-- 从 5 开始，筛选 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 页
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_resutl set math = 60,  chinese = 70 where name = '曹孟德';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
update exam_result set math = math + 30 order by chinese + math + englisih 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 = '孙悟空';
-- 删除整表数据
delete from exam_result;