Content-MySQL数据查询
- 条件查询(重点,难点)
- 聚合函数
- 分组与分页(重点)
- 连接查询(重点)
- 子查询(重点,难点)
- 保存查询结果
条件查询
修改与删除的时候提到过where,使用where限定语句,查询集只返回条件为True内容
如:select * from students where id>13; #这个查询语句将会返回id大于13的数据
区间:where id between 5 and 9
比较运算符
比较运算符 | 描述 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
!=或者<> | 不等于 |
逻辑运算符
逻辑运算符 | 描述 |
---|---|
and | 与 |
or | 或 |
not | 非 |
模糊查询
- like 表示模糊查询
- % 表示任意多个字符
- _ 表示一个字符
- rlike 可以匹配一个正则
- in 用法匹配括号里面的,符合就返回结果集中
注意:如果需要匹配%本身。那么则需要使用%%
例:select name,hometown from students where hometown like '%州';
匹配到所有以州为结尾的地点
例:select name,hometown from students where hometown like '_州';
匹配到所有2个字符以州结尾的地点
例:select name,hometown from students where hometown rlike '.*州';
正则匹配,任意字符只要包含州字的地点
例:select name,hometown from students where id in(3,6,13);
匹配到id是3,6,13的
between查询
- between and 表示一段区间
例:查询id 13-16:select * from students where id between 13 and 16;
null值判断
例:
先插入几条数据hometown为null值的
直接用hometown=null并不能得到结果,在mysql中null表示空。如果要查询为null值的应该是使用is:select * from students where hometown is null;
排序
- order by 字段 [desc/asc]
- desc 表示降序(从大到小排序)
- asc 默认排序规则,表示升序(从小到大排序)
例:select * from students order by id; #按照id升序排
select * from students order by id desc; #按照id降序排
select * from students order by id desc, hometown asc; 先按照id降序,再按照hometown升序
聚合函数 - 统计使用
主要是为了快速得到结果,经常使用的几个聚合函数
常见函数列表
- count 统计个数
- max 计算最大值
- min 计算最小值
- 数学函数
- sum 求和
- avg 求平均数
- round 函数
- 时间函数
- substr 字符串处理函数
例:
select count(*) from students;
select max(id) from students;
select min(id) from students;
数学函数
- MOD(N,M)% 取模
select mod(234,10); #值4
- FLOOR(X) 向下取整
select floor(1.23); #值1
- CELLING(X) 向上取整
select celling(-1.02); #值-1
- ROUND(X,D) 四舍五入到最近的整数 #X是小数,D保留位数
select round(-1.55); #值-2
select round(1.58); #值2
select round(1.298,1); #值1.3
select round(1.298,0); #值1
avg求平均数,计算数值型
select avg(id) from students; #保留小数点后四位
select round(avg(id),1) from students; #先求id平均数,再四舍五入小数点后保留一位
时间函数
- select now(); 显示当前日期及时间
- curdate(); 当前日期
- curtime(); 当前时间
字符串函数
- mysql substr() 函数
用法:substr(string,start,length)
string字符串,start起始位置,length长度
注意:mysql中的start是从1开始的
例:select substr('abcdefg',2,5); #输出bcdef
- left(str,len) 函数
返回字符串str的最左面len个字符
例:select left('abcdefg',5); #输出abcde
- right(str,len) 函数
返回字符串str的最右面len个字符
select right('abcdefg',4); #输出defg
求一个字符的长度
select length('abcdefg'); #输出7
select length('吴老师'); #输出9 mysql中一个汉字占3个字节
小写变大写
select upper('abcdefg'); #输出ABCDEFG
大写变小写
select lower('ABCDEFG'); #输出abcdefg
拼接
select concat('wu','_','laoshi 很帅气'); #输出wu_laoshi 很帅气
分组和分页
- group by字段 #以xx字段作为分组依据分组
注意:分组后分组依据会显示在结果集,其他列不会出现
如:统计男生,女生有多少人
select gender,count(*) from students group by gender;
- as取别名
在统计人数的时候,结果集中显示的是count(*)这个有时候我们并不知道它代表的是什么,如何改成有语义的命名
select gender,count(*) as '人数' from students group by gender;
select gender as '性别',count(*) as '人数' from students group by gender;
- 分组后条件筛选
分组后不能使用where做条件过滤,需要一个使用新的having函数
*where和having的区别
- where用户from之后的条件过滤
- having用在分组之后的条件过滤,两个功能是一样的,只是作用的位置不一样
例:select gender as '性别',count(*) as '人数' from students group by gender having gender=0;
- limit 分页
分页原因:如果数据量很大的话,一次性将所有数据查询出来,不仅不方便查看而且消耗传输带宽。那么就使用到了分页功能,一次只查询一页的数据,如:
select * from students limit start,count; #start从第几条数据开始,count表示获取几条信息
select * from students limit 0,3; #查询前3名同学信息
连接查询
新需求:给students表中的学生分下班,新加一个class_id字段,保存班级id,之前已经有一个class表已经保存了班级信息了,如:
alter table students add class_id int default null;
id小于15分到1班,大于等于分到2班,如:
update students set class_id=1 where id<15;
update students set class_id=2 where id>=15;
mysql三种连接查询
- 内连接查询:查询的结果为两个表匹配到的数据,两个表都能匹配上的数据将返回给结果表
select * from 表1 inner join 表2 on 表1.列=表2.列;
注意:这里如果多次使用到的表名/表名太长的情况下,也可以使用as给表取别名
select b1.name,b1.hometown,b2.name from students as b1 inner join class as b2 on b1.class_id=b2.id;
select b1.name as 学生姓名,b1.hometown as 籍贯,b2.name as 班级名称 from students as b1 inner join class as b2 on b1.class_id=b2.id;
- 右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
select * from 表1 right join 表2 on 表1.列=表2.列
select * from students as b1 right join class as b2 on b1.class_id=b2.id;
- 左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
select * from 表1 left join 表2 on 表1.列=表2.列
select * from students as b1 left join class as b2 on b1.class_id=b2.id;
子查询
- 概念:在一个select语句中嵌入另外一个select语句,嵌入的这个select语句就是子查询语句。子查询是辅助主查询的,充当数据源,或者充当条件。子查询是一条独立的语句。即使单独拿出子查询也是可以正常执行
- 四种类型:标量子查询;列级子查询;行级子查询;表级子查询
- 标量子查询:子查询返回一行一列的数据
例:查询学生年龄小于平均年龄的学生信息
子查询语句先查出平均年龄:select avg(age) from students;
select * from students where age < (select avg(age) from students);
select * from students where age < (select round(avg(age),0) from students);
- 列级子查询:子查询返回一列多行的数据
例:查询class表中已经安排学生的班级信息
select * from class where id in (select class_id from students);
备注:可用于子查询的关键字有4个(in/all/any/some)
in: 符合列子查询里面的一个
any/some(有满足的结果才为true): 功能相同,表示任意一个如:where 列 = any(列子查询)
all(全部满足才为true): 等于里面所有,如:where 列 = all(列子查询)
例:查询出在学生表当中,2班的学生年龄 都大于1班的所有同学年龄
select * from students where class_id=2 and age>all(select age from students where class_id=1)
select * from students whe
re class_id=2 and age>(select max(age) from students where class_id=1)
例:查询出在学生表当中,2班的学生年龄 任意大于1班所有同学年龄
select * from students where class_id=2 and age>any(select age from students where class_id=1)
select * from students where class_id=2 and age>(select min(age) from students where class_id=1)
- 行级子查询:子查询返回一行多列的数据
例:查询一班同学中年龄最大的同学信息,单独使用子查询这条语句查的结果可以看出结果集是一行多列。嵌套到主查询后将查出一班同学中年龄最大的同学信息
select * from students where (age,class_id)=(select max(age),class_id from students where class_id=1);
- 表级子查询:子查询返回多行多列的数据
例:查询学生信息对应班级名称,子查询返回的数据充当数据源,再进行过滤
select t1.name,t1.class_name from (select s.*,c.name as class_name from students as s inner join class as c on s.class_id=c.id) as t1;
保存查询结果
语句格式:insert into 表名(例1,例2) select... #这个方法可以将查询的结果直接保存到表里
例:新建一个表用来保存查询结果,学生id,名字,班级,年龄
全列插入,将查询结果插入到info表
insert into temp_table select 0, b1.id,b1,name,b2.name,b1.age from students as b1 inner join class b2 on b1.class_id=b2.id;
合并查询
- union all 将两次查询的结果集合并到一起显示
- union 将两个查询的结果集先去重后合并到一起显示