1、CRUD

*注释：在SQL中可以使用"--空格+描述"来表示注释说明

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

2、新增(Create)

语法：

insert [into] table_name

             [(column[，column] ...)]

values(value_list) [， (value_list)] …

注：

*[]：选填

*value_list：value，[，value] ...

示例：

-- 创建一张学生表

create table student01( 

                 id int,

                 sn int comment '学号',

                 name varchar(20) comment '姓名',

                 qq_mail varchar(20) comment'QQ邮箱'

);

 2.1、单行数据+全列插入

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

 insert into student01 values(100,10000,'鸡你实在是太美',null);

insert into student01 values(101,10001,'只因坤坤爱坤坤',1234);

insert into student01 values(102,10002,'唉哟,你干嘛',12345);

 2.2、多行数据+指定列插入

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

 insert into student01(id,sn,name) values

                        (103,10003,'中分头'),

                        (104,10004,'背带裤'),

                       (105,10005,'我叫坤坤你记住');

 3、查询(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,74,90),

                     (2,'李四',10,28,10),

                     (3,'王五',99,98,99),

                     (4,'赵六',12,23,44),

                     (5,'坤坤',66,66,66),

                     (6,'他真的很努力',9,8,7);

 3.1、全列查询

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

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

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

select * from exam_result;

3.2、指定列查询

-- 指定列的顺序不需要按照自定义的表的顺序来

select id,name,math from exam_result;

 3.3、查询字段位表达式

-- 表达式不包括字段

 select id,name,5 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,math+chinese+english as 总分 from exam_result;

 3.5、去重：DISTINCT

使用关键字distinct对数据去重

-- 插入一条重复数据记录

 insert into exam_result values(7,'鸽鸽',40,66,99);

--66分重复了 

select math from exam_result;

--去重结果

 select distinct math from exam_result;

 3.6、排序：ORDER BY

语法：

-- asc为升序排列

-- desc为降序排列

-- 系统默认为asc

select ... from table_name [where ...]

        order by column [asc | desc],[...];

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

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

-- 查询同学姓名和qq_mail,按 qq_mail排序显示

 select name,qq_mail from student01 order by qq_mail;

 select name,qq_mail from student01 order by qq_mail desc;

 *使用表达式和别名排序

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

 select name,chinese+math+english as 总分 from exam_result order by 总分 desc;

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

-- 查询同学们的各门成绩，一次按照数学降序，英语升序，语文升序的方式显示

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

 3.7、条件查询

比较运算符：

逻辑运算符：

注：

*where条件可以使用表达式，但不能使用别名

*and的优先级别高于or，在同时使用时，需要使用小括号()包裹优先执行部分

示例：

*基本查询

-- 查询英语不及格的同学及英语成绩 ( < 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 总分 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、BETWEND ... AND ...

-- 查询语文成绩在 [50, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 50 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 '坤_';-- 匹配到坤坤

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