sql查询总结

常用SQL写法的学习总结

目录

常用SQL写法的学习总结

Sql的执行顺序

#查询01课程比02课程成绩高的学生信息以及课程分数

自连接(分成两张表进行连接)

长型数据转为宽型数据

#查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

使用having时注意sql的执行顺序

表连接查询

子查询

#查询平均成绩小于等于60分的同学的学生编号和学生姓名和平均成绩

NULL值的处理

#查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩

Sql的执行顺序

1、from

先确定从哪个表中取数据，所以最先执行from tab。存在多表连接，from tab1，tab2。可以对表加别名，方便后面的引用。

2、where

where语句是对条件加以限定，如果没有需要限定的，那就写成where 1=1，表示总为true，无附加条件。

3、group by…… having

分组语句，比如按照员工姓名分组，要就行分组的字段，必须出现在select中，否则就会报错。having是和group by配合使用的，用来作条件限定，下面写个例子。

4、聚合函数

常用的聚合函数有max，min， count，sum，聚合函数的执行在group by之后，having之前。如果在where中写聚合函数，就会出错。

5、select语句

选出要查找的字段，如果全选可以select *。这里选出员工姓名，所有月份的总工资数。

6、order by

排序语句，默认为升序排列。如果要降序排列，就写成order by [XX] desc。order by语句在最后执行，只有select选出要查找的字段，才能进行排序。

#查询01课程比02课程成绩高的学生信息以及课程分数

思路：

1. 需要用到的表 student，score
2. 01课程比02课程成绩高其实就是说01课程分数要比02课程分数高，所以把成绩表根据课程类型分成两张表，再进行关联查询
3. 最后关联学生信息表
4. 两种方式

自连接(分成两张表进行连接)

SELECT

Stu.*,

S1.s_score AS sco1,

S2.s_score AS sco2

FROM

Score s1,score s2,student stu

WHERE

s1.c_id='1'

AND s2.c_id='2'

AND s1.s_id=s2.s_id

AND s1.`s_score`>s2.`s_score`

AND s1.s_id=stu.s_id

长型数据转为宽型数据

1.这种方法主要就是把同一张表中的多条数据合并成一条数据，中间用到了函数max

2.因为分数表中同一个学生的学生id是相同的，所以可以把同一个学生的多条数据合并成一条

3.使用CASE when 和max 分别对三个学科进行表示

SELECT

STU.*,

T.S01,

T.S02

FROM

(SELECT

S.S_ID,

MAX(CASE WHEN S.C_ID='1' THEN S.S_SCORE ELSE NULL END) S01,

MAX(CASE WHEN S.C_ID='2' THEN S.S_SCORE ELSE NULL END) S02

FROM

SCORE S

GROUP BY S.S_ID

)T,STUDENT STU

WHERE T.S_ID=STU.S_ID

AND T.S01>T.S02

#查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

使用having时注意sql的执行顺序

注意：sql的执行顺序是先执行聚合函数再执行 SELECT ,而且聚合函数执行再group之后，在having之前，所以having这里不能写成HAVING AVSCO>='60' ，having执行完了才会执行select,它才会有这个 AVSCO 别名

表连接查询

SELECT

STU.`s_id`,

STU.`s_name`,

IFNULL(AVG(S.S_SCORE),0) AVSCO

FROM

SCORE S,STUDENT STU

WHERE S.S_ID=STU.`s_id`

GROUP BY S.S_ID

IFNULL(AVG(S.S_SCORE),0)>='60'

子查询

SELECT

S.`s_id`,

(SELECT S_NAME FROM STUDENT STU WHERE STU.S_ID=S.S_ID ),

AVG(S.S_SCORE) AVSCO

FROM

SCORE S

GROUP BY S.S_ID

HAVING AVG(S.S_SCORE)>='60'

#查询平均成绩小于等于60分的同学的学生编号和学生姓名和平均成绩

NULL值的处理

这里用到了一个IFNULL函数，并且因为存在学生表中有数据，而因为缺考等因数导致成绩表中没有相关学生的成绩数据，所以这种展示就需要用到外连接，(分组也要依靠主表)选择学生表为主表，再用IFNULL函数对空进行处理

SELECT

STU.`s_id`,

STU.`s_name`,

IFNULL(AVG(S.S_SCORE),0) AVSCO

FROM

SCORE S RIGHT JOIN STUDENT STU

ON S.S_ID=STU.`s_id`

GROUP BY STU.S_ID

HAVING IFNULL(AVG(S.S_SCORE),0)<='60'

#查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩

用到了STUDENT ,COUNT,SUM,GROUP BY

SELECT

STU.S_ID,

STU.S_NAME,

COUNT(S.C_ID) AS NUM,

IFNULL(SUM(S.S_SCORE),0) AS SUMMER

FROM

STUDENT STU LEFT JOIN SCORE S

ON STU.S_ID=S.S_ID

GROUP BY STU.S_ID

MYSQL连接查询效率问题

连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：
1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。（MYSQL没有全外连接一般用union来合并左连接和右连接）
5、 求差操作的时候用联合查询。
多个表查询的时候，这些不同的连接类型可以写到一块。例如：
SELECT T1.C1,T2.CX,T3.CY
FROM TAB1 T1
       INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
       INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
       LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
WHERE T1.X >T3.Y;

总结：

1. 可以用explain 来解析sql,row表示大概扫描的数据量，他们是乘积关系，也就是说他们相乘的值越大扫描到的数据量也就越大，应该尽量避免，filter表示扫描的数据量进行筛选和过滤的百分比；
2. 主要是看连接条件和我们要的数据是否是需要，如果我们是只需要两张表相等的数据，就用内连接，可以理解成集合A和集合B，我们只需要A∪B，也就是只要是相等的数据相关的表我们就用内连接
3. 如果我们需要的数据必须是一张表的全部，而另外一张表不一定由对应的数据，此时我们就要用外连接，使用外连接的时候其实MYSQL很多时候是转换成了内连接，所以能用内连接就不用外连接，外连接需要注意的是把条件当成连接条件和WHERE后面的效果不一样，而且查询效率也不一样，WHERE会降低查询效率，但是它也可以达到删除一些不必要的条件，其实就是对以左表为一张主表，对它里面的数据进行删选，比如我们进行左外连接的时候，有AB两张表，A中的数据可能在B中不存在，如果是连接条件中写A.a=B.a,那么查询的结果中B中没有的数据就会显示为空，如果是写在WHERE里面，则会只显示他们相等的数据；总之，关联条件中写的条件，基本的原则就是以左表为了主表，where则可以看成连接后的结果再进行条件删选

#查询学过张三老师授课同学的学生信息

 EXPLAIN SELECT

*

FROM

  TEACHER T

  LEFT JOIN COURSE C

    ON T.T_ID = C.T_ID

  LEFT JOIN SCORE SC

    ON SC.C_ID = C.C_ID

  LEFT JOIN STUDENT STU

    ON STU.S_ID = SC.S_ID

    WHERE T.T_NAME = '张三'

    

     EXPLAIN SELECT

*

FROM

  TEACHER T

  LEFT JOIN COURSE C

    ON T.T_ID = C.T_ID

     AND T.T_NAME = '张三'

  LEFT JOIN SCORE SC

    ON SC.C_ID = C.C_ID

  LEFT JOIN STUDENT STU

    ON STU.S_ID = SC.S_ID

    

    

   EXPLAIN SELECT * FROM

    TEACHER T ,SCORE SC ,STUDENT STU,COURSE C

    WHERE

    T.T_ID = C.T_ID

    AND SC.C_ID = C.C_ID

    AND T.T_NAME = '张三'

AND STU.S_ID = SC.S_ID

#8查询没有学过“张三”的授课的学生信息

Not in 的使用

select * from student where s_id not in(

select

 d.s_id

 from

teacher b,course c,score d

 where

 c.t_id=b.t_id

 and d.c_id=c.c_id

 and b.t_name='张三');

Not exits 的使用

select * from student where not exits(

select

 student.s_id

 from

(select

 d.s_id

 from

teacher b,course c,score d

 where

 c.t_id=b.t_id

 and d.c_id=c.c_id

 and b.t_name='张三')t,student

 where t.s_id=student.s_id);

#查询学过编号为“01”并且也学过编号为“02”的课程的同学信息

内连接

SELECT stu.*  FROM score s1, score s2,student stu

WHERE  s1.c_id ='01'

AND s2.c_id ='02'

AND s2.s_id=s1.s_id

AND s1.s_id=stu.s_id

长型数据转宽型数据

SELECT s.*,t.* FROM

Student s,(SELECT

Sc.s_id,

MAX(CASE WHEN sc.c_id='1' THEN  sc.c_id END ) 'c01',

MAX(CASE WHEN sc.c_id='2' THEN  sc.c_id END) 'c02'

FROM  score sc

GROUP BY sc.s_id)t

WHERE  t.s_id=s.s_id

AND t.c01 IS NOT NULL

AND t.c02 IS NOT NULL

#对空值进行了处理会更快

EXPLAIN SELECT s.*,t.* FROM

Student s,(SELECT

Sc.s_id,

IFNULL(MAX(CASE WHEN sc.c_id='1' THEN  sc.c_id END ),0) 'c01',

IFNULL(MAX(CASE WHEN sc.c_id='2' THEN  sc.c_id END) ,0)'c02'

FROM  score sc

GROUP BY sc.s_id)t

WHERE  t.s_id=s.s_id

AND t.c01 >0

AND t.c02 >0

注意：内连接对相连接的几张表的数据的null数据进行了删选，所以上面的方法不用处理为null的情况，而下面是先将score表进行了合并，故而存在为null的情况，所以要单独处理

#查询学过编号‘01’，但是没有学过编号‘02’课程的同学信息

EXPLAIN SELECT s.*,t.* FROM

Student s,(SELECT

Sc.s_id,

IFNULL(MAX(CASE WHEN sc.c_id='1' THEN  sc.c_id END ),0) 'c01',

IFNULL(MAX(CASE WHEN sc.c_id='2' THEN  sc.c_id END) ,0)'c02'

FROM  score sc

GROUP BY sc.s_id)t

WHERE  t.s_id=s.s_id

AND t.c01 >0

AND t.c02 =0