数据库问题解析

1、表连接

1. 表连接（JOIN）是在多个表中间通过⼀定的连接条件，使表之间发⽣关联进⽽能从多个表之间获取数据。

2、

3、表联合

union：对两个结果集进⾏并集操作，不包括重复⾏

union all：对两个结果集进⾏并集操作，包括重复⾏

注意事项：

①每条SELECT 语句必须拥有相同数量的列；

②每条 SELECT 语句中列的顺序必须相同 。

4、

-- 左上
select * from t01;

-- 右上
select * from t02;

-- 中间
select t01.*
from t01 
inner join t02 on t01.id=t02.id;

-- 左下
select t01.*
from t01
left join t02 on t01.id=t02.id
where t02.id is null;

-- 右下
select t02.*
from t01 
right join t02 on t01.id=t02.id
where t01.id is null;

-- 表联合
select * from t01
union
select * from t02;

select * from t01
union all
select * from t02;

-- 左下
select t01.*
from t01
left join t02 on t01.id=t02.id
where t02.id is null
union
-- 右上
select * from t02;

-- 左上
select * from t01
union
-- 右下
select t02.*
from t01 
right join t02 on t01.id=t02.id
where t01.id is null;

-- 左下
select t01.*
from t01
left join t02 on t01.id=t02.id
where t02.id is null
union
-- 中间
select t01.*
from t01 
inner join t02 on t01.id=t02.id;
union
-- 右下
select t02.*
from t01 
right join t02 on t01.id=t02.id
where t01.id is null;

-- 中空
-- 左下
select t01.*
from t01
left join t02 on t01.id=t02.id
where t02.id is null
union
-- 右下
select t02.*
from t01 
right join t02 on t01.id=t02.id
where t01.id is null; 

5、if函数

1. 

2.  

   -- if函数
   select if(true,1,2),if(false,1,2);
   select if(true,if(false,1,3),2);
   
   -- 按照学生成绩分为几个等级：
   -- 优秀[>=90]，良好[>=80]，中等[>=70]，及格[>=60]，不及格[<60]
   select studentNo,subjectNo,StudentResult
    if(StudentResult>=90,'优秀',
       if(StudentResult>=80,'良好',
             if(StudentResult>=70,'中等',
                  if(StudentResult>=60,'及格','不及格')))) as '等级'
   from result;
   
   select studentNo,subjectNo,StudentResult
    if(StudentResult<60,'不及格',
       if(StudentResult<70,'及格',
             if(StudentResult<80,'中等',
                  if(StudentResult<90,'良好','优秀')))) as '等级'
   from result;

6、case 函数

1. 

2.  

   -- case函数
   select studentNo,subjectNo,StudentResult,
    case
    when StudentResult between 90 and 100 then '优秀'
    when StudentResult between 80 and 89 then '良好'
    when StudentResult between 70 and 79 then '中等'
    when StudentResult between 60 and 69 then '及格'
    else '不及格'
    end as '等级'
   from result;

    

3. 

4.  

   select studentName,
   case
   when sex=1 then "男"
   when sex=2 then "女"
   else ''
   end 性别
   from student;
   
   select studentName,
    case
    when sex=1 then "男"
    else "女"
    end 性别
   from student;
   
   select studentName,
    case sex
    when 1 then "男"
    else "女"
    end 性别
   from student;
   
   select StudentName 姓名,if(sex=1,"男","女") 性别
   from student;

7、行转列

1. 

2.  

   -- 行转列
   CREATE TABLE students_score (
     cname VARCHAR (10),
     cource VARCHAR (10),
     score INT
   ) ENGINE = INNODB ;
   INSERT INTO students_score VALUES('张三','语文',74);
   INSERT INTO students_score VALUES('张三','数学',83);
   INSERT INTO students_score VALUES('张三','物理',93);
   INSERT INTO students_score VALUES('李四','语文',74);
   INSERT INTO students_score VALUES('李四','数学',84);
   INSERT INTO students_score VALUES('李四','物理',94);
   
   -- 行转列
   select cname 姓名,sum(if(cource="语文",score,0)) 语文,
   sum(if(cource="数学",score,0)) 数学,
   sum(if(cource="物理",score,0)) 物理
   from students_score
   group by cname;
   
   select cname 姓名,max(if(cource="语文",score,0)) 语文,
   max(if(cource="数学",score,0)) 数学,
   max(if(cource="物理",score,0)) 物理
   from students_score
   group by cname;
   
   select cname 姓名,min(if(cource="语文",score,100)) 语文,
   min(if(cource="数学",score,100)) 数学,
   min(if(cource="物理",score,100)) 物理
   from students_score
   group by cname;