注:此博文为本人学习过程中的笔记
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;