行转列
对于示例数据,完整情况如下
select * from test;
多行转一行多列
select name,
max(case course
when 'java' then score
end) as Java,
max(case course
when 'MySQL' then score
end) as MySQL from test group by name;
多行转一行一列
相关函数:
- concat(值,‘拼接符’,值):拼接,多行数据只会拼接一行
- group_cancat(值,‘拼接符’,值):拼接,多行数据压扁到一行
# 首先使用concat查看效果
select name,
concat(course,'=',score)
as scores from test group by name;
----------------------------------------------------------------------------------------------------------------------
# 然后使用group_concat查看效果,其中separator是分隔符,默认是 ,
select name,
group_concat(course,'=',score separator ' | ')
as scores from test group by name;
DQL-连接查询
示例数据
# teacher表
select * from teacher;
----------------------------------------------------------------------------------------------------------------------
# student表
select * from student;
笛卡尔积
多表查询中,链接的where限定条件,不能少于 表的个数-1 , 否则就会发生笛卡尔乘积,这个限定条件并不是随便一个限定条件,而是用于维护映射两个表的条件,比如 外键
发生
# 直接写select * from teacher,student;结果的条数是两个表的乘积
select * from teacher,student;
所以判断条件最少也要是1个,也就是表的个数-1
# 增加where条件
select * from teacher as t,student as s where t.id=s.teacher_id;
虽然条数对了,但是也会先发生一个完全的笛卡尔乘积,然后在新视图中找匹配的数据,再展示匹配的数据,会消耗内存一些,所以不推荐使用,推荐使用链接查询
优化的几种方法
优化一:使用等值连接条件,比如上面的where s.teahcer_id = t.id。
优化二:能使用inner join的就不要使用left join。
优化三:使用记录数更少的表当左表。
# 在表中各自额外添加一条数据
insert into teacher(id,name)values(3,'jlk');
insert into student(id,name)values(7,'nnm');
select * from teacher;
select * from student;
inner join
select * from teacher as t
inner join student as s on t.id=s.teacher_id;
----------------------------------------------------------------------------------------------------------------------
select * from student as s
inner join teacher as t on s.teacher_id=t.id;
使用内连接的话,会以左边表为基准(student),生成新视图的时候,先生成左边表中的数据,然后再去匹配右边表中是否有符合条件的,没有的话,就不生成这一行,同时左表中有的,右表中没有的数据,都不会生成,右表中有的,左表中没有的也一样不会生成,所以 左表和右表就算换了位置,数据行数不会变多
但是会丢失数据,不符合条件的数据不会查询出来
left join 和 right join
select * from student as s
left join teacher as t on s.teacher_id=t.id;
----------------------------------------------------------------------------------------------------------------------
select * from teacher as t
left join student as s on t.id=s.teacher_id;
以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替
生成视图的时候,也是先生成左表的数据
right join 就是以右边的表为主表
数据库导入和导出
导出:可以导出数据库或者某张表
导入:可以导入数据库或表,但是要注意的是导入数据库的时候,需要该数据库存在,也就是说导入库并不会创建新的库