【SQL】表的相关操作-2增删改查

一、 增（create）

1. 语法

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邮箱'
);

2. 单行数据全列插入
插入时，value_list 数量必须和定义表的列的数量、顺序一致

insert into student values(100,10000,'唐三藏',NULL);
insert into student values(101,10001,'孙悟空',NULL);

3. 多行数据指定列插入
value_list 数量必须和指定列数量、顺序一致

insert into student(id,sn,name) values
(102,20001,'曹孟德'),
(103,20002,'孙仲谋');

二、 删

语法

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;

三、 改

语法

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;

四、查

1.语法

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(name,chinese,math,english) values
('唐三藏',67,98,56),
('孙悟空',87.5,78,77),
('猪悟能',88,98,90),
('曹孟德',82,84,67),
('刘玄德',55.5,85,45),
('孙权',70,73,78.5),
('宋公明',75,65,30);

2.全列查询

select * from exam_result;

【注】通常情况下不建议使用*进行全列查询
因为：
查询的列越多，意味着需要传输的数据量越大；
可能会影响到索引的使用；

3.指定列查询

select id,name,english from exam_result;

指定列查询时，查询列表的顺序不需要按定义表的顺序来

4.查询字段为表达式

-- 表达式不包含字段
select id,name,10 from exam_result;
-- 表达式包含一个字段
select id,name,english+10 from exam_result;
-- 表达式包含多个字段
select id,name,chinese+math+english from exam_result;

5.别名
语法：
SELECT column [AS] alias_name [...] FROM table_name;

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

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

6.去重（distinct）

使用 distinct 关键字可对某列数据进行去重
例：对于之前创建的表 exam_result 中，math列里98分重复了

select distinct math from exam_result;

7.排序（order by）
语法：
SELECT ... FROM table_name [WHERE...] ORDER BY column [ASC | DESC],[...];
升序 ---- ASC（小 -> 大）
降序 ---- DESC（大 -> 小）
一般默认为ASC
【注】
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;

8.条件查询（where）

• 比较运算符：

运算符	说明
>、>=、<、<=	大于、大于等于、小于、小于等于
=	等于，NULL不安全，例如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	模糊匹配。%表示任意多个（包括0个）任意字符；_表示任意一个字 符

• 逻辑运算符：

运算符	说明
and	多个条件必须都为TRUE(1)，结果才是TRUE(1)
or	任意一个条件为TRUE(1),结果为TRUE(1)
not	条件为TRUE(1)，结果为FALSE(0)

注：
1.where 条件可以使用表达式，但不能使用别名

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

范围查询：
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;

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;

9.分页查询（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;