从0开始学Java——MySQL(五)
从0开始学Java——大目录
整体目录全部移至单独文章,毕竟是一套长期更新
要不然后期目录都该比文章内容长了
一、查询语句的基本语法结构
select <[*/字段名]> from 表名
[where 查询条件]
[group by 列名 [having 分组条件]]
[order by 列名]
[limit x,y];
-- ---------------------------------------------------
# 查询语句
select * from `stu`;
select `name`,`sex`,`score`,`classid` from `stu`;
# where 子句的用法 条件
select `name`,`sex`,`score`,`classid` from `stu`
where `sex` = '0';
# group by 子句分组
select `sex`,count(`sex`) from `stu`
group by `sex` having `sex`!='2';
# order by 子句
select * from `stu` order by `age` -- 默认是升序排序
select * from `stu` order by `age` asc -- 默认是升序排序,asc 默认可以省略
select * from `stu` order by `age` desc -- desc 表示降序排序
# limit 子句
select * from `stu` limit 4; -- 显示前四条
select * from `stu` limit 2,4; -- 跳过前两条显示4条
二、为查询输出的结果字段名改名
select 字段名 as 新字段名 from 表名 ...;
-- -------------------------------------------
# 为查询出来的字段改名
select `sex` as '性别', count(`sex`) as '个数' from `stu` group by `sex`;
select `sex` '性别', count(`sex`) '个数' from `stu` group by `sex`; -- as 默认可以不写
三、查询语句中的运算符
- 逻辑运算符
- AND 或 && 逻辑与
- OR 或 || 逻辑或
- NOT 或 !逻辑非
- XOR 或 ^ 逻辑异或
# 逻辑运算符
-- 逻辑与
select * from `stu` where true and true;
select * from `stu` where true && true;
select * from `stu` where true and false;
select * from `stu` where false and true;
select * from `stu` where false and false;
select * from `stu` where `age` < 18 and `sex` = '1';
select * from `stu` where `age` < 18 && `sex` = '1';
-- 逻辑或
select * from `stu` where true or true;
select * from `stu` where true or false;
select * from `stu` where false or true;
select * from `stu` where false or false;
select * from `stu` where `sex` = '1' or `sex` = '0';
select * from `stu` where `sex` = '1' || `sex` = '0';
-- 逻辑非
select * from `stu` where not true;
select * from `stu` where not false;
select * from `stu` where !false;
select * from `stu` where not `score`='100';
select * from `stu` where !(`score`='100');
select * from `stu` where `score`!='100';
-- 逻辑异或
select * from `stu` where true xor false;
select * from `stu` where false xor true;
select * from `stu` where true xor true;
select * from `stu` where false xor false;
select * from `stu` where true ^ false;
- 关系运算符
- = 是否相等,相等为真
- <=> 是否相等,相等为真
- != 或 <> 不等于值为真
- <、<=、>、>= 基本关系运算符
- is null 空为真
- is not null 不空为真
- between and 在从哪到哪之间
- not between and 不在哪到哪之间
- like 匹配字符串,有匹配为真
- not like 匹配字符串,没有匹配为真
- in 在集合里匹配,有结果为真
- not in 不在集合里为真
-- 关系运算符
select * from `stu` where `id` = 6;
select * from `stu` where `id` <=> 6;
select * from `stu` where `id` != 6;
select * from `stu` where `id` <> 6;
select * from `stu` where `id` <> 6;
select * from `stu` where `score` is not null;
select * from `stu` where `score` is null;
select * from `stu` where `score` >= '90' and `score` <= '100';
select * from `stu` where not (`score` >= '90' and `score` <= '100');
select * from `stu` where `score` between '90' and '100';
select * from `stu` where `score` not between '90' and '100';
# like 表示字符串匹配 %表示不定长度字符的通配符
select * from `stu` where `name` like 'L%'; -- 查询L开头的
select * from `stu` where `name` like '%l%'; -- 查询名字里面有l的
# like 使用的时候 _(下划线)表示单个字符的通配符
select * from `stu` where `name` like '__L%'; -- 查询第三个字母是L的
select * from `stu` where `sex` = '1' or `sex` = '0';
select * from `stu` where `sex` in('1', '0');
select * from `stu` where `sex` not in('1', '0');
- 算术运算符
# 算术运算符
select * from `stu` where `id` % 2 = 1;
select * from `stu` where `score` % 2 = 1;
select `name`,`age` from `stu`;
select `name`,`age` + 10 from `stu`; -- 十年之后大家的年龄
- 常用函数
- count() 统计计数
- sum() 求和
- avg() 求平均值
- max() 求最大值
- min() 求最小值
- concat() 字符串连接
-- 查看一共有多少条数据
select count(*) from `stu`;
-- 统计班级的总分
select sum(`score`) from `stu`;
-- 统计班级的平均分
select avg(`score`) from `stu`;
-- 统计所有女生的平均分
select avg(`score`) from `stu` where `sex` = '0';
-- 通过男生的最高分
select max(`score`) from `stu` where `sex` = '1';
-- 查询最高分的人是谁?
select `name`, `score` from `stu`
where `score` = (select max(`score`) from `stu`);
-- 查询女生的最低分
select min(`score`) from `stu` where `sex` = '0';
-- 字符串连接
select concat("Hello ","MySQL ","world!~");
select concat(
(select `name` from `stu` where `id` = 3),' <==> ',
(select `name` from `stu` where `id` = 4),' <==> ',
(select `name` from `stu` where `id` = 5),' <==> ',
(select `name` from `stu` where `id` = 6)
);