目录
- 面试题:
SQL查询中各个关键字的执行先后顺序
from > on> join > where > group by > with > having > select > distinct > order by > limit
表的增删查改
CRUD:create,retrieve,update,delete
1.1Create
- 语法:
insert [into] table_name
[(column [,column] ...)]
values(value_list) [,(value_list)]...
value_list:value,[,value] ...
示例:
- 创建一张学生表
create table if not exists student(
id int primary key auto_increment comment '编号',
sn int not null unique comment '学号',
name varchar(32) not null comment '姓名',
qq varchar(12) unique key comment 'qq号'
);
1.1.1单行数据+全部插入(全列插入)
-- 插入两条记录,value_list 数量必须和定义表的列的数量及顺序一致
示例:
insert into student values(1,100,'唐三藏',null);
insert into student values(2,101,'孙悟空',500400);
- 查看输入结果
select * from student;
1.1.2多行数据+指定列插入(批量插入)
--插入两条记录,value_list 数量必须和指定列数量及顺序一致
insert into student (id,sn,name)values(3,102,'曹孟德'),(4,103,'孙仲谋');
- 查看输入结果
select * from student;
- 补充:
--单行数据+指定列
insert into student(id,sn,name) values(5,104,'刘备');
--多行数据+全列插入
insert into student values(6,105,'诸葛亮','6666'),(7,106,'关羽','34325');
1.1.3插入否则更新
由于主键或者唯一键对应的值已经存在而导致插入失败
--主键或者唯一键冲突
insert into student (id,sn,name) values (7,'107','张飞');
insert into student (id,sn,name) values (8,'106','张飞');
解决:可选择性进行同步更新操作
- 语法:
insert ... on duplicate key update column = value[, column = value] ...
1.1.4冲突更新
insert into student (id,sn,name) values (7,'107','张飞')
on duplicate key update sn = '107',name = '张飞';
-- 0 row affected: 表中有冲突数据,但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据,数据被插入
-- 2 row affected: 表中有冲突数据,并且数据已经被更新
- 通过MySQL函数获取受到影响的数据行数
select row_count();
1.1.5冲突替换
--主键 或者 唯一键 没有冲突,则直接插入
--主键 或者 为一间 如果冲突,则删除后在插入
先删除原有的数据,再插入新数据
replace into student (id,sn,name) values(7,'107','关羽');
replace into student (id,sn,name) values(8,'108','鲁肃');
-- 1 row affected: 表中没有冲突数据,数据被插入
-- 2 row affected: 表中有冲突数据,删除后重新插入
1.2Retrieve
- 语法:
select [distinct]{column [,column] ...} [from table_name] [where ...]
[order by column] [ASC|DESC], ...] limit ...
示例:
- 创建表结构
create table exam_result(
id int unsigned primary key auto_increment,
name varchar(20) not null comment'同学成绩',
chinese float default 0.0 comment'语文成绩',
math float default 0.0 comment'数学成绩',
english float default 0.0 comment'英语成绩'
);
- 插入测试数据
insert into exam_result(name,chinese,math,english) values
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30 );
1.2.1select列
- 全列查询
--通常情况下不建议使用* 进行全列查询
--1.查询的列越多,需要传输的数据量越大
--2.可能会影响到索引的使用。
select id,name,chinese,math,english from exam_result;
1.2.2 指定列查询
--制定列的顺序不需要按定义表的顺序来
示例:
select id, name, english from exam_result;
1.2.3查询字段为表达式
--表达式包含多个字段
select id,name, chinese + math + english from exam_result;
1.2.4结果去重
- 98 分重复
select math from exam_result;
- 去重结果
select math from exam_result;
1.3Where条件
1.3.1比较运算符:
1.3.2逻辑运算符:
示例:
- 英语不及格的同学及英语成绩(<60)
--基本比较
select name,english from exam_result where english < 60;
- 语文成绩在[80,90]分的同学及语文成绩
--使用and进行条件连接
select name,chinese from exam_result where chinese >= 80 and chinese <=90;
--使用between ... and ... 条件
select name,chinese from exam_result where chinese between 80 and 90;
- 数学成绩是58或者59或者98或者99分的同学及数学成绩
--使用or进行条件连接
select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
- 姓孙的同学及孙某同学
--%匹配任意多个(包括 0 个)任意字符
select name from exam_result where name like '孙%';
-- _匹配严格的一个任意字符
select name from exam_result where name like '孙_';
- 语文成绩好于英语成绩的同学
--where 条件中比较运算符两侧都是字段
select name,chinese,english from exam_result where chinese > english;
- 总分在200分以下的同学
--where 条件中使用表达式
--别名不能用在Where条件中
select name,chinese + math + english from exam_result
where chinese + math + english < 200;
- 语文成绩> 80并且不姓孙的同学
--and 与 not的使用
select name,chinese from exam_result where chinese > 80 and name not like '孙%';
1.3.3Null查询
- Null he Null 的比较, = 和 <=>的区别
select null = null, null = 1,null = 0;
select null <=> null,null <=> 1,null <=> 0;
1.4结果排序:
语法:
--ASC为升序(从小到大)
--DESC为降序(从大到小)
--默认为ASC(升序)
select ... from table_name [where ...] order by column [ASC|DESC],[...];
注意:
没有order by 子句的查询,返回的顺序是未定义的,永远不要依赖此顺序
示例:
- 查询同学各门成绩,依次按数学降序,英语升序,语文升序的方式显示
--多字段排序,排序优先级随书写顺序
select name,math,english,chinese from exam_result order by math desc,english,chinese;
1.5筛选分页结果
- 语法:
-- 起始下标为 0
-- 从 0 开始,筛选 n 条结果
select ... from table_name[where ...] [order by ...] limit n;
-- 从 s 开始,筛选 n 条结果
select ... from table_name [where ...] [order by ...] limit n offset n;
注意:
对未知表进行查询时,加一条limit 1,避免因为表中数据过大,查询全表数据导致数据库卡死
- 按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;
1.6Update
- 语法:
update table_name set cliumn = expr[,clumn = expr ...]
[where ...] [order by ...] [limit...]
- 对查询到的结果进行列值更新
示例:
- 将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分
--一次更新多个列
--查看原数据
select name, math, chinese from exam_result where name = '曹孟德';
-- 数据更新
update exam_result set math = 60, yuwen = 70 where name = '曹孟德';
--查看更新后数据
select name,math,chinese from exam_result where name = '曹孟德';
- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
-- 更新值为原值基础上变更
-- 查看原数据
select name, math, chinese + math + chinese 总分 from exam_result order by 总分 limit 3;
--数据更新
update exam_result set math = math + 30
order by chinese + math + english limit 3;
- 将所有同学的语文成绩更新为原来的 2 倍
注:更新全表的语句慎用
--没有where 子句,则更新全表
--查看原数据
select * from exam_result;
--数据更新
update exam_result set chinese = chinese *2;
--查看更新后的数据
select * from exam_result;
1.7Delete
- 删除数据
语法:
delete from table_name [where ...] [order by ...] [limit ...]
示例:
- 删除孙悟空同学的考试成绩
-- 查看原数据
delete from table_name [where ...] [order by ...] [limit ...]
-- 删除数据
delete from exam_result where name = '孙悟空';
-- 查看删除结果
select * from exam_result where name = '孙悟空';
1.8截断表
truncate [table] table_name
Truncate与Delete的区别:
1.9插入查询结果
- 语法:
insert into table_name [(column [,column ... ])] select ...
示例:分析
- 删除表中的重复记录,重复的数据只能有一份
--创建原始数据
create table duplicate_table (id int, name varchar(20));
--插入数据
insert into duplicate_table values
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
--结果
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(300, 'ccc');
- 思路:
实现步骤:
查询表结构:
desc duplicate_table;
1.创建一个和原表一摸一样的表
create table duplicate_table2 like duplicate_table;
2.查询原表的数据并且去重
select distinct id,name from duplicate_table;
3.查询原表去重的数据插入至新表
duplicate_table2 insert into duplicate_table2(id,name)
select distinct id,name from duplicate_table;
4.删除原表
drop table duplicate_table;
5.将新表duplicate_table2 名字改为duplicate_table
alter table duplicate_table2 rename to duplicate_table;
查看最终结果
select * from duplicate_table;