文章目录
- 系列文章目录
- 前言
- 一、Create
- 1.首先创建一张学生表
- 1.单行数据 + 全列插入
- 2. 多行数据 + 指定列插入
- 3.插入否则更新
- 1.主键冲突
- 2.唯一键冲突
- 4.替换
- 二、Retrieve
- 1.创建一张exam_result的表
- 2.向exam_result表中插入数据
- 3.select列
- 1.全列查询
- 2.指定列查询
- 3.查询字段为表达式
- 4.为查询结果指定别名
- 5.查询结果去重
- 4.where约束条件
- 1.英语不及格的同学及英语成绩 ( < 60 )
- 2.语文成绩在 [80, 90] 分的同学及语文成绩
- 3.数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
- 4.姓孙的同学 及 孙某同学
- 5.语文成绩好于英语成绩的同学
- 6.总分在 200 分以下的同学
- 7.语文成绩 > 80 并且不姓孙的同学
- 8.孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
- 9.NULL 的查询
- 5.结果排序
- 1.同学及数学成绩，按数学成绩升序显示
- 2.同学及 qq 号，按 qq 号排序显示
- 3.查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
- 4.查询同学及总分，由高到低
- 5.查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
- 6.筛选分页结果
- 三、Update
- 1.将孙悟空同学的数学成绩变更为 80 分
- 2.将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
- 3.将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
- 4. 将所有同学的语文成绩更新为原来的 2 倍
- 四、Delete
- 1.删除数据
- 2.删除孙悟空同学的考试成绩
- 3. 删除整张表数据（慎用）
- 4.截断表
- 1.首先建立一张for_truncate表
- 2.插入一些数据到for_truncate表
- 3.截断表数据
- 五、插入查询结果
- 六、聚合函数
- 1.统计班级共有多少同学
- 2.统计班级收集的 qq 号有多少
- 3.统计本次考试的数学成绩分数个数
- 4.统计数学成绩总分
- 5.统计平均总分
- 6.返回英语最高分
- 7.返回 > 70 分以上的数学最低分
- 七、group by子句的使用
- 1.先备份一张scott_data.sql数据库
- 2.准备工作，创建一个雇员信息表
- 3.如何显示每个部门的平均工资和最高工资
- 4.显示每个部门的每种岗位的平均工资和最低工资
- 5.显示平均工资低于2000的部门和它的平均工资
- 1.统计各个部门的平均工资
- 2.having和group by配合使用，对group by结果进行过滤
- 总结
前言
一、Create 1.首先创建一张学生表
1.单行数据 + 全列插入create table students(id int unsigned primary key auto_increment,sn int not null unique comment ‘学号’,name varchar(20) not null,qq varchar(20));
插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致:
2. 多行数据 + 指定列插入insert into students value(100,1000,‘唐僧’ ,null);
insert into students value(101,10001,‘孙猴子’,‘11111’);
插入两条记录，value_list 数量必须和指定列数量及顺序一致。
3.插入否则更新insert into students (id,sn,name) values(102,2001,‘曹孟德’),(103,2002,‘孙仲谋’),(104,2003,‘刘备’),(105,2004,‘诸葛亮’);
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败：
1.主键冲突2.唯一键冲突INSERT INTO students (id, sn, name) VALUES (100, 10010, ‘唐大师’);
4.替换INSERT INTO students (sn, name) VALUES (2001, ‘曹阿瞒’);
主键 或者 唯一键 没有冲突，则直接插入；主键 或者 唯一键 如果冲突，则删除后再插入。
主键 或者 唯一键 没有冲突，则直接插入。
二、Retrieve 1.创建一张exam_result的表replace into students(sn,name) values(10001,‘赵云’);
主键 或者 唯一键 如果冲突，则删除后再插入。
replace into students(sn,name) values(2001,‘曹孟德’);
2.向exam_result表中插入数据-> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAr(20) NOT NULL COMMENT ‘同学姓名’,
-> yuwen float DEFAULT 0.0 COMMENT ‘语文成绩’,
-> shuxue float DEFAULT 0.0 COMMENT ‘数学成绩’,
-> yingyu float DEFAULT 0.0 COMMENT ‘英语成绩’
-> );
3.select列 1.全列查询MariaDB [user2]> INSERT INTO exam_result (name, yuwen, shuxue, yingyu) VALUES
-> (‘唐三藏’, 67, 98, 56),
-> (‘孙悟空’, 87, 78, 77),
-> (‘猪悟能’, 88, 98, 90),
-> (‘曹孟德’, 82, 84, 67),
-> (‘刘玄德’, 55, 85, 45),
-> (‘孙权’, 70, 73, 78),
-> (‘宋公明’, 75, 65, 30);
通常情况下不建议使用 * 进行全列查询：
– 1. 查询的列越多，意味着需要传输的数据量越大；
– 2. 可能会影响到索引的使用。（索引待后面博客讲解）
2.指定列查询select * from exam_result;
3.查询字段为表达式select name ,shuxue from exam_result;
①表达式不包含字段
select id,name,10 from exam_result;
②表达式包含一个字段
select id,name ,yingyu+10 from exam_result;
③表达式包含多个字段:
4.为查询结果指定别名select id, name,yingyu+shuxue+yuwen from exam_result;
语法:
SELECt column [AS] alias_name […] FROM table_name;
5.查询结果去重select id,name,yingyu+shuxue+yuwen 总分 from exam_result;
4.where约束条件select distinct shuxue from exam_result;
比较运算符：
逻辑运算符：
2.语文成绩在 [80, 90] 分的同学及语文成绩select name ,yingyu from exam_result where yingyu<60;
select name,yuwen from exam_result where yuwen>=80 and yuwen<=90;
3.数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩select name,yuwen from exam_result where yuwen between 80 and 90;
4.姓孙的同学 及 孙某同学select name,shuxue from exam_result where shuxue=58 or shuxue=59 or shuxue=98 or shuxue=99;
% 匹配任意多个（包括 0 个）任意字符:
select name from exam_result where name like ‘孙%’;
_ 匹配严格的一个任意字符：
5.语文成绩好于英语成绩的同学select name from exam_result where name like ‘孙_’;
6.总分在 200 分以下的同学select name ,yuwen,yingyu from exam_result where yuwen>yingyu;
WHERe 条件中使用表达式-- 别名不能用在 WHERe 条件中
7.语文成绩 > 80 并且不姓孙的同学select name ,yuwen+shuxue+yingyu as 总分 from exam_result where yuwen+shuxue+yingyu<200;
8.孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80select name,yuwen from exam_result where yuwen>80 and name not like ‘孙%’;
9.NULL 的查询select name,yingyu,shuxue,yuwen,yingyu+shuxue+yuwen from exam_result where name like ‘孙%’ or(yingyu+shuxue+yuwen>200 and yuwen80);
5.结果排序select name,qq from students where qq is not null;
语法：
– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
SELECt … FROM table_name [WHERe …]
ORDER BY column [ASC|DESC], […];
注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
1.同学及数学成绩，按数学成绩升序显示2.同学及 qq 号，按 qq 号排序显示select name,shuxue from exam_result order by shuxue;
NULL 视为比任何值都小，升序出现在最上面:
select name ,qq from students order by qq;
NULL 视为比任何值都小，降序出现在最下面
3.查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示select name ,qq from students order by qq desc;
4.查询同学及总分，由高到低select name , shuxue,yingyu ,yuwen from exam_result order by shuxue desc,yingyu,yuwen;
select name ,shuxue,yingyu,yuwen ,shuxue+yingyu+yuwen from exam_result order by shuxue+yingyu+yuwen desc;
ORDER BY 子句中可以使用列别名:
5.查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示select name ,shuxue,yingyu,yuwen ,shuxue+yingyu+yuwen as 总分 from exam_result order by 总分 desc;
6.筛选分页结果select name,shuxue from exam_result where name like ‘孙%’ or name like ‘曹%’ order by shuxue desc;
语法：
– 起始下标为 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 n;
建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页.。
select * from exam_result limit 2;
select * from exam_result limit 1,2;
三、Updateselect * from exam_result limit 3 offset 0;
语法：
UPDATE table_name SET column = expr [, column = expr …]
[WHERe …] [ORDER BY …] [LIMIT …]
对查询到的结果进行列值更新:
1.将孙悟空同学的数学成绩变更为 80 分2.将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分select name,shuxue from exam_result where name =‘孙悟空’;
select name,shuxue from exam_result where name =‘孙悟空’;
3.将总成绩倒数前三的 3 位同学的数学成绩加上 30 分update exam_result set shuxue=60 , yuwen=70 where name=‘曹孟德’;
select name,shuxue+yingyu+yuwen from exam_result order by shuxue+yingyu+yuwen limit 3;
4. 将所有同学的语文成绩更新为原来的 2 倍
update exam_result set shuxue=shuxue+30 order by shuxue+yingyu+yuwen limit 3;
四、Delete 1.删除数据update exam_result set yuwen=yuwen*2;
语法：
2.删除孙悟空同学的考试成绩DELETE FROM table_name [WHERe …] [ORDER BY …] [LIMIT …]
3. 删除整张表数据（慎用） 4.截断表delete from exam_result where name=‘孙悟空’;
语法：
TRUNCATE [TABLE] table_name
- 注意：这个操作慎用
- 只能对整表操作，不能像 DELETe 一样针对部分数据操作。
- 实际上 MySQL 不对数据操作，所以比 DELETE 更快。
- 会重置 AUTO_INCREMENT 项。
2.插入一些数据到for_truncate表create table for_truncate(id int primary key auto_increment,name varchar(20));
3.截断表数据insert into for_truncate(name) values(‘A’),(‘B’),(‘C’);
截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作。
truncate for_truncate;
再次插入数据时自增系列重置。
INSERT INTO table_name [(column [, column …])] SELECT …
删除表中的的重复复记录，重复的数据只能有一份。
六、聚合函数 1.统计班级共有多少同学2.统计班级收集的 qq 号有多少select count(*) from students;
3.统计本次考试的数学成绩分数个数select count(qq) from students;
COUNT(shuxue) 统计的是全部成绩。
select count(shuxue) from exam_result;
COUNT(DISTINCT shuxue) 统计的是去重成绩数量
4.统计数学成绩总分select count(distinct shuxue) from exam_result;
5.统计平均总分select sum(shuxue) from exam_result;
6.返回英语最高分select avg(shuxue+yuwen+yingyu) from exam_result;
7.返回 > 70 分以上的数学最低分select max(yingyu) from exam_result;
七、group by子句的使用select min(shuxue) from exam_result where shuxue>70;
在select中使用group by 子句可以对指定列进行分组查询。
1.先备份一张scott_data.sql数据库 2.准备工作，创建一个雇员信息表select column1, column2, … from table group by column;
- EMP员工表。
- DEPT部门表。
- SALGRADE工资等级表。
4.显示每个部门的每种岗位的平均工资和最低工资select deptno,avg(sal),max(sal) from emp group by deptno;
5.显示平均工资低于2000的部门和它的平均工资 1.统计各个部门的平均工资select avg(sal),min(sal),deptno from emp group by deptno,job;
2.having和group by配合使用，对group by结果进行过滤select deptno,avg(sal) from emp group by deptno;
select deptno,avg(sal) as myavg from emp group by deptno having myavg<2000;
总结
以上就是今天要讲的内容，本文详细介绍了数据库表的查询，像表的增、删、查、改，数据库提供了大量的方法供我们使用，非常的便捷，我们务必掌握。希望大家多多支持！另外如果上述有任何问题，请懂哥指教，不过没关系，主要是自己能坚持，更希望有一起学习的同学可以帮我指正，但是如果可以请温柔一点跟我讲，爱与和平是永远的主题，爱各位了。加油啊！
