mysql常用查询,怎么用SQL语句实现排名次
分类:高并发

前几天去了两个比较牛的互联网公司面试,在sql这块都遇到问题了,哎,可惜呀,先把简单的梳理一下

处理mysql使用in关键字子查询1317错误

昨晚在CSDN论坛上看到有某个人问了类似这样的一个问题,现有三个数据表,分别是学生表,课程表,成绩表。它们的结构与样例数据如下:

 

成绩表 score

 

学生表:
学生Id    姓名
1             张三
2             李四
3             王五

SELECT kh,zf,(SELECT count(*)+1 FROM 成绩表 WHERE a.zf<zf) as jmc FROM 成绩表 as a into curs lsb
scan
upda 表 set jmc=lsb.jmc where allt(kh)=allt(lsb.kh)
ends

图片 1

    Error 1317 mysql query execution interrupted  消息内容:查询执行被中断(数据库直接挂起)

课程表:
课程Id    课程名
1             语文
2             化学
3             外语
4             物理

 

1、group by 使用

按某一个维度进行分组

例如:

求每个同学的总分

SELECT student,SUM(score) FROM score GROUP BY student

求每个同学的平均分

SELECT student,AVG(score) FROM score GROUP BY student

也可以按照 班级,课程 来求

  1. 现象: 

成绩表
学生Id     课程Id        成绩
1               1                 60
1               2                 70
1               3                 65
1               4                 90
2               1                 80
2               2                 65
2               3                 85
2               4                 80
3               1                 50
3               2                 75
3               3                 85
3               4                 60

2、having 与 where的区别

having与where类似,可以筛选数据,where后的表达式怎么写,having后就怎么写 where针对表中的列发挥作用,查询数据having对查询结果中的列发挥作用,筛选数据 例如:

查出挂了两门及以上的学生

SELECT student,SUM(score<60)as gk FROM score GROUP BY student HAVING gk>1

(1)在PHP程序中使用子查询语句,导致Mysql自动“挂起”,即数据库“卡死”,程序不能正常运行

现要求在一行中输出每个学生的所有课程的成绩单,显示样例格式如下:
姓名        语文        化学       外语        物理
张三        60            70           65            90
李四        80            65           85            80
王五        50            75           85            60

3、子查询

(1)where子查询

(把内层查询结果当作外层查询的比较条件)

求比每门课程平均分低的学生

SELECT student ,course, score
FROM score ,(SELECT course AS a_course,AVG( score)AS a_score FROM score GROUP BY course) AS avg_score
WHERE course = a_course AND score<a_score

先写到这吧 

成绩表 score 1、group by 使用 按某一个...

(2)在mysql命令行执行子查询语句,Mysql需要等待较长时间,提示 “  Error 1317 mysql query execution interrupted”  

当大家看到这样的题目时会优先考虑到的是怎样的一条SQL语句呢?嵌套Select?对,在将行转换为列时,也许这种方法是最优先考虑到(或者你是高手,所以不是优先考虑到这个而是其它图片 2),所以我最开始也写出了下面这条语句:

  1. 处理办法有两种 :

图片 3SELECT B.姓名,
图片 4(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='语文' ) AS 语文,
图片 5(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='化学' ) AS 化学,
图片 6(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='外语' ) AS 外语,
图片 7(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='物理' ) AS 物理
图片 8FROM 学生 B
图片 9

           006_kh表记录数目共计为 24256 条     uzone_2701_kh 表中记录数目共计为 52327条

这样我们的目的是达到了,但后来我又想了一下,因为我们要的数据其实都在成绩表里,只不过现有的是用行来存放,那我们怎么将它转换为列显示呢?嗯,这也许就要搬出聚合函数加Case条件来处理了!最终的SQL语句如下:

原始SQL语句(子查询):

图片 10SELECT 姓名,
图片 11MAX(CASE 课程名 WHEN '语文' THEN 成绩 ELSE 0 END) AS 语文,
图片 12MAX(CASE 课程名 WHEN '化学' THEN 成绩 ELSE 0 END) AS 化学,
图片 13MAX(CASE 课程名 WHEN '外语' THEN 成绩 ELSE 0 END) AS 外语,
图片 14MAX(CASE 课程名 WHEN '物理' THEN 成绩 ELSE 0 END) AS 物理
图片 15FROM (SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D 
图片 16INNER JOIN 学生 B ON B.学生ID=D.学生ID 
图片 17INNER JOIN 课程 C ON C.课程ID=D.课程ID) AS TMP GROUP BY 姓名
图片 18

[html] 

运行后,也是可以得到正确的数据,下面给出测试代码,大家可以直接在SQL查询分析器里运行

SELECT count(kh_id) FROM `006_kh` WHERE  kh_id  in (select khbh from  uzone_2701_kh where uzbh ='180' and  jgm='27010899')  

图片 19CREATE TABLE 学生 (学生ID INT, 姓名 VARCHAR(20))
图片 20CREATE TABLE 课程 (课程ID INT, 课程名 VARCHAR(20))
图片 21CREATE TABLE 成绩表 (学生ID INT, 课程ID INT, 成绩 INT)
图片 22
图片 23INSERT INTO 学生
图片 24SELECT 1,'张三' UNION ALL
图片 25SELECT 2,'李四' UNION ALL
图片 26SELECT 3,'王五'
图片 27
图片 28INSERT INTO 课程
图片 29SELECT 1,'语文' UNION ALL
图片 30SELECT 2,'化学' UNION ALL
图片 31SELECT 3,'外语' UNION ALL
图片 32SELECT 4,'物理'
图片 33
图片 34INSERT INTO 成绩表
图片 35SELECT 1,1,60 UNION ALL
图片 36SELECT 1,2,70 UNION ALL
图片 37SELECT 1,3,65 UNION ALL
图片 38SELECT 1,4,90 UNION ALL
图片 39SELECT 2,1,80 UNION ALL
图片 40SELECT 2,2,65 UNION ALL
图片 41SELECT 2,3,85 UNION ALL
图片 42SELECT 2,4,80 UNION ALL
图片 43SELECT 3,1,50 UNION ALL
图片 44SELECT 3,2,75 UNION ALL
图片 45SELECT 3,3,85 UNION ALL
图片 46SELECT 3,4,60
图片 47
图片 48--方法一
图片 49SELECT B.姓名,
图片 50(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='语文' ) AS 语文,
图片 51(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='化学' ) AS 化学,
图片 52(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='外语' ) AS 外语,
图片 53(SELECT 成绩 FROM 成绩表 INNER JOIN 课程 ON 成绩表.课程ID=课程.课程ID WHERE 成绩表.学生ID=B.学生ID AND 课程.课程名='物理' ) AS 物理
图片 54FROM 学生 B
图片 55
图片 56--方法二
图片 57SELECT 姓名,
图片 58MAX(CASE 课程名 WHEN '语文' THEN 成绩 ELSE 0 END) AS 语文,
图片 59MAX(CASE 课程名 WHEN '化学' THEN 成绩 ELSE 0 END) AS 化学,
图片 60MAX(CASE 课程名 WHEN '外语' THEN 成绩 ELSE 0 END) AS 外语,
图片 61MAX(CASE 课程名 WHEN '物理' THEN 成绩 ELSE 0 END) AS 物理
图片 62FROM (SELECT B.姓名,C.课程名,D.成绩 FROM 成绩表 D 
图片 63INNER JOIN 学生 B ON B.学生ID=D.学生ID 
图片 64INNER JOIN 课程 C ON C.课程ID=D.课程ID) AS TMP GROUP BY 姓名
图片 65
图片 66
图片 67DROP TABLE 学生
图片 68DROP TABLE 课程
图片 69DROP TABLE 成绩表

 

PS:用嵌套SELECT与用聚合函数加Case两者的效率如何,我没有测试,各位有兴趣的可测试一下图片 70

使用 desc 命令分析,结果如下:

[html] 

mysql>   

mysql> desc SELECT count(kh_id) FROM `006_kh` WHERE  kh_id  in (select khbh from  uzone_2701_kh where uzbh ='180' and  jgm='27010899') ;  

+----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+  

| id | select_type        | table         | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |  

+----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+  

|  1 | PRIMARY            | 006_kh        | index | NULL          | PRIMARY | 4       | NULL | 89394 | Using where; Using index |  

|  2 | DEPENDENT SUBQUERY | uzone_2701_kh | ALL   | NULL          | NULL    | NULL    | NULL | 24256 | Using where              |  

+----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+  

2 rows in set (0.00 sec)  

(1) 第一种方式:

sql脚本

[html] 

select count(kh_id) FROM `006_kh` where kh_id in(select khbh from (select khbh from uzone_2701_kh where uzbh ='180' and  jgm='27010899') as khid_array)  

 

使用 desc 命令分析,结果如下:

[html] 

mysql> desc select count(kh_id) FROM `006_kh` where kh_id in(select khbh from (select khbh from uzone_2701_kh where uzbh ='180' and  jgm='27010899') as khid_array) ;  

+----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+  

| id | select_type        | table         | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |  

+----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+  

|  1 | PRIMARY            | 006_kh        | index | NULL          | PRIMARY | 4       | NULL | 96767 | Using where; Using index |  

|  2 | DEPENDENT SUBQUERY | <derived3>    | ALL   | NULL          | NULL    | NULL    | NULL | 24099 | Using where              |  

|  3 | DERIVED            | uzone_2701_kh | ALL   | NULL          | NULL    | NULL    | NULL | 24256 | Using where              |  

+----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+  

3 rows in set (0.02 sec)  

 

 

(2)第二种方式 :

sql脚本 :

[html] 

select count(a.kh_id)  from 011_kh a inner join uzone_2701_kh b on a.kh_id = b.khbh where b.uzbh ='180' and  b.jgm='27010899'   

 

使用 desc 命令分析,结果如下: 

[html] 

mysql>   

mysql> desc select count(a.kh_id)  from 011_kh a inner join uzone_2701_kh b on a.kh_id = b.khbh where b.uzbh ='180' and  b.jgm='27010899'  ;  

+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+  

| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows  | Extra       |  

+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+  

|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL               | 24256 | Using where |  

|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | dxzs_v2_new.b.khbh |     1 | Using index |  

+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+  

2 rows in set (0.00 sec)  

 

  个人试验结论:使用JOIN语句的查询不一定总比使用子查询的语句快,根据我自己的试验结果和DESC分析结果 来说,还是JOIN语句比较快,效率比较高;因此,当使用in关键字进行子查询,效率低下时,强烈推荐第二种!

Error 1317 mysql query execution interrupted 消息内容:查询执行被中断(数据库直接挂起) 1. 现象: (1)在...

本文由10bet手机官网发布于高并发,转载请注明出处:mysql常用查询,怎么用SQL语句实现排名次

上一篇:循环结构程序设计,可供参考 下一篇:多薄多表合并,excel外接程序插件
猜你喜欢
热门排行
精彩图文