1.子查询:
1.1 select 后面
select 字段名,(查询语句) from 表名;
select *,(
select name from teacher where id=teacher_id
) as teacher_name from student ;
eg:使用子查询时,最好养成使用别名的习惯
1.2 from 后面
from (
select *,
case
when score < 60 then 'C'
when score >=60 and score <80 then 'B'
when score >=80 then 'A'
end as rank
from student
)
eg:可以把子查询当成一张表,必须有别名
1.3 where 后面
where teacher_id in (
select id from teacher where name='张老师'
);
eg:多条数据用 in ,不要用=
2.行转列
2.1多行转一行多列
select name,max(
case course
when 'java' then score
end) Java, max(
case course
when 'MySQL' then score
end) MySQL
from test_9
group by name;
2.2多行转一行一列
concat(值,’拼接符’,值 ) : 拼接,多行数据只会拼接一行
group_concat(值,’拼接符’,值 ) : 拼接,多行压扁到一行
第一步:拆分问题,先按分组的思路
select name,1 as '各科成绩' from test_9 group by name;
第二步:将课程名与成绩拼接成一列
select name,
concat(course,'=',score) as '各科成绩'
from test_9 group by name;
第三步:利用group_concat函数将多行压扁到一行
select name,
group_concat(course,'=',score) as '各科成绩'
from test_9 group by name;
第四步:修改分隔符(默认是逗号)
select name,
group_concat(course,'=',score separator ' | ') as '各科成绩'
from test_9 group by name;
第五步:按课程名称排序
select name,
group_concat(course,'=',score order by course asc separator ' | ') as '各科成绩'
from test_9 group by name;
3.DQL-连接查询
3.1 笛卡尔积
排列组合相乘
3.2 inner join
只取两边共有的
select *
from teacher tea
inner join student stu on tea.id = stu.teacher_id;
3.3 left join
左边全列出来
3.4 right join
右边全列出来