mysql(三)

模糊查询 like
一个下划线就是一个字符
%百分号代表可以代表任意字符
select * from student where name like '%小%';

select * from student where name like '%小%'; /*显示符号条件的数据的所有内容*/
select name from student where name like '%小%'; /*只显示符合条件的name字段*/
select name from student where name like '小%'; /*开头必须是小后面字符任意*/
select name from student where name like '%小'; /*结尾必须是小前面字符任意*/

select name from student where name like '小_'; /*能输出小明的名字*/
select name from student where name like '小__';/*两个下划线不可以搜出,因为小明只有两个字符串*/

-- 查询不重复的数据 去重 distinct order by 排序 默认是降序,后面加desc是升序
-- select distinct sex from student;
-- select * from score order by score; /*升序*/
-- select * from score order by score desc; /*降序*/

-- max（）最大值 min（）最小值 avg()平均值 sum()综合 count(*)统计行数 总共多少条数据 不用写具体哪列 所以使用*星号
select max(score) from score; /*98*/
select min(score) from score; /*82*/
select avg(score) from score; /*90.8333*/
select sum(score) from score; /*545*/
select count(*) from score; /*6行*/

-- group by 6、分组查询 表示对所取得的数据，以所给定的字段来进行分组,最后的结果就是将数据分成了若干组，每组作为一个“整体”成为一行数据。
select stage from score group by stage; /*把score这个表的stage这列 分组*/

-- 分组后筛选使用 having 和where含义一样,只是having是用于group by 分组后的筛选
select stage,count(*) from score group by stage having count(*)>2;

-- limit 起始行(第一行是0),显示行数 分页查询
select * from score limit 0,5;
select * from score limit 5,5;

-- 多表联合查询 join on left join左表数据全部查出来 right join是右表全部查出来
select * from teacher as a join score as b on a.name = b.tid where a.name='苍老师';
SELECT * from teacher JOIN score on teacher.name = score.tid where teacher.name='苍老师';

select a.* from teacher as a join score as b on a.name = b.tid where a.name='苍老师'; /*只显示苍老师*/

-- 子查询
select * from grade where tid=(SELECT name from teacher where name='王老师');