当前位置: 澳门新濠3559 > 数据库 > 正文

但对于性能问题,分组前第一道过滤      

时间:2019-12-27 19:58来源:数据库
  一,sql性能优化基础方法论 对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为: cpu消耗 内存使用 对磁

 

一,sql性能优化基础方法论

对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为:

cpu消耗

内存使用

对磁盘,网络或其他I/O设备的输入/输出(I/O)操作。

 

但我们遇到性能问题时,要判断的第一点就是“在这三种资源中,是否有哪一种资源达到了有问题的程度”,因为这一点能指导我们搞清楚“需要优化重构什么”和“如何优化重构它”

澳门新濠3559 1

 高级查询

二,sql调优领域

应用程序级调优

sql语句调优

管理变化调优

示例级调优

内存

数据结构

实例配置

操作系统交互

I/O

swap

Parameters

    关键字书写顺序  关键字执行顺序
select:投影结果       1    5

三,sql优化方法

优化业务数据

优化数据设计

优化流程设计

优化sql语句

优化物理结构

优化内存分配

优化I/O

优化内存竞争

优化操作系统

from:定位到表             2    1

四,sql优化过程

定位有问题的语句

检查执行计划

检查执行计划中优化器的统计信息

分析相关表的记录数、索引情况

改写sql语句、使用HINT、调整索引、表分析

有些sql语句不具备优化的可能,需要优化处理方式

达到最佳执行计划

where:分组前第一道过滤       3    2

五,什么是好的sql语句

尽量简单,模块化

易读,易维护

节省资源

内存

cpu

扫描的数据块要少

但对于性能问题,分组前第一道过滤       3    2。少排序

不造成死锁

group by:分组                4    3

六,sql语句的处理过程

sql语句的四个处理阶段:

 

澳门新濠3559 2

解析(PARSE):

检查语法

检查语义和相关的权限

在共享池中查找sql语句

合并(MERGE)视图定义和子查询

确定执行计划

绑定(BIND)

在语句中查找绑定变量

赋值(或重新赋值)

执行(EXECUTE)

应用执行计划

执行必要的I/O和排序操作

提取(FETCH)

从查询结果中返回记录

必要时进行排序

使用ARRAY FETCH机制

having:分组后第二道过滤             5    4

七,sql表的基本连接方式

表连接有几种?

sql表连接分成外连接、内连接和交叉连接。

新建两张表:

表1:student  截图如下:

 

澳门新濠3559 3

表2:course  截图如下:

 

澳门新濠3559 4

(此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键。)

一、外连接

外连接可分为:左连接、右连接、完全外连接。

1、左连接  left join 或 left outer join

SQL语句:select * from student left join course on student.ID=course.ID

执行结果:

 

澳门新濠3559 5

左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).

注:此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。

2、右连接  right join 或 right outer join

SQL语句:select * from student right join course on student.ID=course.ID

执行结果:

 

澳门新濠3559 6

右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。

注:同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。

3、完全外连接  full join 或 full outer join

SQL语句:select * from student full join course on student.ID=course.ID

执行结果:

 

澳门新濠3559 7

完全外连接包含full join左右两表中所有的行,如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL),如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。

二、内连接  join 或 inner join

SQL语句:select * from student inner join course on student.ID=course.ID

执行结果:

 

澳门新濠3559 8

inner join 是比较运算符,只返回符合条件的行。

此时相当于:select * from student,course where student.ID=course.ID

三、交叉连接 cross join

1.概念:没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

SQL语句:select * from student cross join course

执行结果:

 

澳门新濠3559 9

如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID

此时将返回符合条件的结果集,结果和inner join所示执行结果一样。

order by:排序                      6    6

八,sql优化最佳实践

1,选择最有效率的表连接顺序

首先要明白一点就是SQL 的语法顺序和执行顺序是不一致的

SQL的语法顺序:

    select   【distinct】 ....from ....【xxx  join】【on】....where....group by ....having....【union】....order by......

SQL的执行顺序:

   from ....【xxx  join】【on】....where....group by ....avg()、sum()....having....select   【distinct】....order by......

from 子句--执行顺序为从后往前、从右到左

表名(最后面的那个表名为驱动表,执行顺序为从后往前, 所以数据量较少的表尽量放后)

where子句--执行顺序为自下而上、从右到左

将可以过滤掉大量数据的条件写在where的子句的末尾性能最优

group by 和order by 子句执行顺序都为从左到右

select子句--少用*号,尽量取字段名称。 使用列名意味着将减少消耗时间。

2,避免产生笛卡尔积

含有多表的sql语句,必须指明各表的连接条件,以避免产生笛卡尔积。N个表连接需要N-1个连接条件。

3,避免使用*

当你想在select子句中列出所有的列时,使用动态sql列引用“*”是一个方便的方法,不幸的是,是一种非常低效的方法。sql解析过程中,还需要把“*”依次转换为所有的列名,这个工作需要查询数据字典完成!

4,用where子句替换having子句

澳门新濠3559,where子句搜索条件在进行分组操作之前应用;而having自己条件在进行分组操作之后应用。避免使用having子句,having子句只会在检索出所有纪录之后才对结果集进行过滤,这个处理需要排序,总计等操作。如果能通过where子句限制记录的数目,那就能减少这方面的开销。

5,用exists、not exists和in、not in相互替代

原则是哪个的子查询产生的结果集小,就选哪个

select * from t1 where x in (select y from t2)

select * from t1 where exists (select null from t2 where y =x)

IN适合于外表大而内表小的情况;exists适合于外表小而内表大的情况

6,使用exists替代distinct

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在select子句中使用distinct,一般可以考虑使用exists代替,exists使查询更为迅速,因为子查询的条件一旦满足,立马返回结果。

低效写法:

select distinct dept_no,dept_name from dept d,emp e where d.dept_no=e.dept_no

高效写法:

select dept_no,dept_name from dept d where  exists (select 'x' from emp e where e.dept_no=d.dept_no)

备注:其中x的意思是:因为exists只是看子查询是否有结果返回,而不关心返回的什么内容,因此建议写一个常量,性能较高!

用exists的确可以替代distinct,不过以上方案仅适用dept_no为唯一主键的情况,如果要去掉重复记录,需要参照以下写法:

select * from emp  where dept_no exists (select Max(dept_no)) from dept d, emp e where e.dept_no=d.dept_no group by d.dept_no)

7,避免隐式数据类型转换

隐式数据类型转换不能适用索引,导致全表扫描!t_tablename表的phonenumber字段为varchar类型

以下代码不符合规范:

select column1 into i_l_variable1 from t_tablename where phonenumber=18519722169;

应编写如下:

select column1 into i_lvariable1 from t_tablename where phonenumber='18519722169';

8,使用索引来避免排序操作

在执行频度高,又含有排序操作的sql语句,建议适用索引来避免排序。排序是一种昂贵的操作,在一秒钟执行成千上万次的sql语句中,如果带有排序操作,往往会消耗大量的系统资源,性能低下。索引是一种有序结果,如果order by后面的字段上建有索引,将会大大提升效率!

9,尽量使用前端匹配的模糊查询

例如,column1 like 'ABC%'方式,可以对column1字段进行索引范围扫描;而column1 kike '%ABC%'方式,即使column1字段上存在索引,也无法使用该索引,只能走全表扫描。

10,不要在选择性较低的字段建立索引

在选择性较低的字段使用索引,不但不会降低逻辑I/O,相反,往往会增加大量逻辑I/O降低性能。比如,性别列,男和女!

11,避免对列的操作

不要在where条件中对字段进行数学表达式运算,任何对列的操作都可能导致全表扫描,这里所谓的操作,包括数据库函数,计算表达式等等,查询时要尽可能将操作移到等式的右边,甚至去掉函数。

例如:下列sql条件语句中的列都建有恰当的索引,但几十万条数据下已经执行非常慢了:

select * from record where amount/30<1000 (执行时间11s)

由于where子句中对列的任何操作结果都是在sql运行时逐行计算得到,因此它不得不进行全表扫描,而没有使用上面的索引;如果这些结果在查询编译时就能得到,那么就可以被sql优化器优化,使用索引,避免全表扫描,因此sql重写如下:

select * from record where amount<1000*30 (执行时间不到1秒)

12,尽量去掉"IN","OR"

含有"IN"、"OR"的where子句常会使用工作表,使索引失效,如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引;

select count(*) from stuff where id_no in('0','1')

可以拆开为:

select count(*) from stuff where id_no='0'

select count(*) from stuff where id_no='1'

然后在做一个简单的加法

13,尽量去掉"<>"

尽量去掉"<>",避免全表扫描,如果数据是枚举值,且取值范围固定,可以使用"or"方式

update serviceinfo set state=0 where state<>0;

以上语句由于其中包含了"<>",执行计划中用了全表扫描(Table access full),没有用到state字段上的索引,实际应用中,由于业务逻辑的限制,字段state智能是枚举值,例如0,1或2,因此可以去掉"<>" 利用索引来提高效率。

update serviceinfo set state=0 where state =1 or state =2

14,避免在索引列上使用IS NULL或者NOT

避免在索引中使用任何可以为空的列,导致无法使用索引

15,批量提交sql

如果你需要在一个在线的网站上去执行一个大的DELETE或INSERT查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

Apache会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程或线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。所以,如果你有一个大的处理,你一定把其拆分。

 

 

 

 

 

 

 

limit:        最后

---分页 *
目的:为了加快网站对数据的查询(检索)速度

--sql server :
-1.跳过前几条,取剩下的几条数据
 双top 双order by
select top 每页数据量 * from 表 where 列 not in
(
 select top 要跳过的数据量 列 from 表

)


-2.row_nubmer() over( ORDER BY )  (2005以后支持)
select * from
(
select *,row_number() over(order by 主键列) as myid from 表
) as temp
 where myid between 起始号码 and 每页数据量

--mysql :

SELECT <字段名列表>
FROM <表名或视图>
[WHERE <查询条件>]
[GROUP BY <分组的字段名>]
[ORDER BY <排序的列名> [ASC 或DESC]]
[LIMIT [位置偏移量,]行数];

 

--临时表
临时表主要用于对大数据量的表上作一个子集,提高查询效率。加快数据访问速度
临时表存在于系统数据库
SQL Sever :
   存在于系统数据库tempdb
 #表名:局部临时表:
      只对当前会话有效
 ##表名:全局临时表
      所有会话共享

MySQL :
 在会话断开销毁
 所有临时表都是服务于当前连接
   临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。
  创建临时表的语法与创建表语法类似,不同之处是增加关键字TEMPORARY,如:
  CREATE TEMPORARY TABLE 表名 (…. )
   show create table可以查看临时表;

--伪表
dual  我们称之为 伪表!

在mysql中是一个摆设

select *;
select * from dual;

select * from dual;  报错

oracle中 必须使用 from dual;
select * from dual;  正确的
select * ;  错误

dual是一个只有一行一列的表!
只能查询!  不能对 dual进行增删改!

 

--和并列
DROP TABLE IF EXISTS `testa`;
CREATE TABLE `testa` (
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert  into `testa`(`name`,`subject`,`score`) values ('张三','语文',80),('李四','语文',90),('王五','语文',70),('张三','数学',60),('李四','数学',98),('王五','数学',100);

--  需要成绩和科目 在一列 显示  根据name分组
SELECT  
`name` AS  姓名,
GROUP_CONCAT(`subject`,':',score) AS  成绩
FROM  testa
GROUP BY  `name`;

 
  

 -- 查询所有年级编号为1的学员信息,按学号升序排序
SELECT * FROM student
WHERE GradeID=1
ORDER BY Studentno ASC; 
-- 显示前4条记录
SELECT * FROM student
WHERE GradeID=1
ORDER BY Studentno ASC
LIMIT 0,4;
-- 每页4条,显示第2页,即从第5条记录开始显示4条数据
SELECT * FROM student
WHERE GradeID=1
ORDER BY Studentno ASC
LIMIT 4,4

 

SQL99标准:
(1)是操作所有关系型数据库的规则
(2)是第四代语言
(3)是一种结构化查询语言 s
(4)只需发出合法合理的命令,就有对应的结果显示

<>:不等于(SQL99标准)   

-- 子查询
 相关和嵌套
 相关子查询: 执行机制
 内层查询不能单独执行,需要和外部的查询进行结合。外层和内层并行执行。|
 嵌套子查询: 内层查询可以单独执行。内层的结果作为外层的条件
注意:并不是所有子查询都是先执行内层查询

 子查询可以应用到任何位置

所有表连接都可以使用子查询替换,但是能用子查询的地方,未必都能够使用表连接
 举例: 限制:不能使用表连接场景:::查询条件为<>时

(结论:子查询应用范围更广)
 一个查询中又包含了另一个查询,一般来说,子查询会使用()扩起来,并且小括号内的检索结果会作为外层查询的条件存在

 比较运算符 只能投影一列    in,not in,not exists和exists可投影多列

-- 把一个查询的结果 当成另一个查询的 字段,条件或者表(子查询可以应用到任何位置)!
SELECT  studentName FROM  student
--  只能通过student 表 查询出 学生对应的 年级名称
--  01. 先查询出 学生 武松 对应的  年级编号
SELECT    GradeID  FROM student WHERE  studentName='武松'
--  02.根据年级编号  取  年级名称
SELECT gradeName FROM grade WHERE GradeID=???
SELECT gradeName FROM grade WHERE GradeID
=(SELECT    GradeID  FROM student WHERE  studentName='武松')

 -- 查询年级编号是1或者2 的  所有学生列表
 SELECT * FROM student WHERE gradeId IN(1,2)
-- 查询 年级名称是  大一或者大二的所有学生信息
 -- 学生表 中没有  年级名称  但是有年级编号
 -- 01.根据  年级名称 查询出 编号
 
SELECT gradeID FROM grade WHERE gradeName IN('大一','大二');
 -- 02.再根据id查询学生信息
SELECT  * FROM student WHERE 
gradeID 
IN (SELECT gradeID FROM grade WHERE gradeName IN('大一','大二')) 
 
 -- 查询参加 最近一次 高等数学-1 考试成绩的学生的最高分和最低分
-- 01. 发现成绩表中 没有 科目名称 只有编号!根据名称取编号
SELECT  SubjectNo  FROM `subject`  WHERE
subjectName='高等数学-1'
-- 02.查询最近一次 高等数学-1 考试的时间
SELECT  MAX(ExamDate)  FROM result
WHERE 
SubjectNo=(SELECT  SubjectNo  FROM `subject`  WHERE
subjectName='高等数学-1')
--  所有最近考试的成绩
SELECT *  FROM result
WHERE ExamDate='2013-11-11 16:00:00'
-- 03.开始获取最高分和 最低分
SELECT  MAX(studentResult) AS 最高分,
        MIN(studentResult) AS 最低分
FROM  result
WHERE SubjectNo=(SELECT  SubjectNo  FROM `subject`  WHERE
subjectName='高等数学-1')
AND ExamDate=
(SELECT  MAX(ExamDate)  FROM result
WHERE 
SubjectNo=(SELECT  SubjectNo  FROM `subject`  WHERE
subjectName='高等数学-1'))
 

 

-- 查询 高等数学-1 考试成绩是 60 分的 学生信息
-- 01.根据  科目名称 获取 科目编号
SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1'
-- 02.根据编号 查询 所有的学生编号
SELECT studentNo FROM result 
WHERE SubjectNo=(SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60;  -- 成绩=60
-- 03.查询学生信息
SELECT * FROM  student
WHERE  studentNo IN
(SELECT studentNo FROM result 
WHERE SubjectNo=(SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60)

-- 使用in替换 等于(=)的子查询语句!
-- in后面的子查询可以返回多条记录!

--  not in :不在某个范围之内
-- 查询未参加 “高等数学-1” 课程最近一次考试的在读学生名单
-- 01.根据  科目名称 获取 科目编号
SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1'
-- 02.获取最近一次考试时间
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=
(SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1')
-- 03.查询没参加的学生编号
SELECT studentNo,StudentName FROM student
WHERE studentNo NOT IN
(
SELECT  StudentNo  FROM  result
WHERE SubjectNo=
(SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1')
AND ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=
(SELECT SubjectNo  FROM  `subject`
WHERE SubjectName='高等数学-1'))
)
 
 
 

 

-  exists(检查子查询)的使用
--  01. 用于检测表,数据库等等 是否存在
--  02. 检查子查询中是否会返回数据!检查子查询并不返回任何数据!
       值返回 true或者false!
1.Exists使用场景?
    判定数据库对象是否存在
    1.1
    if exists XXXX
    1.2
    where exists(子查询)

SELECT * FROM Student WHERE EXISTS(SELECT NULL)
SELECT * FROM Student WHERE EXISTS(SELECT 9*9)
SELECT * FROM Student WHERE EXISTS(SELECT StudentName FROM student) 

SELECT * FROM Student 
WHERE EXISTS(SELECT studentName FROM Student WHERE studentName='张三')
SELECT * FROM Student WHERE studentName IN(SELECT studentName FROM Student)
 -- in  效果等同于 =any
SELECT * FROM Student WHERE 
studentName =ANY(SELECT studentName FROM Student)

--  all 大于子查询语句中的 最大值   >(1,2,3)    >3
SELECT * FROM student
WHERE studentNo>ALL
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))
--  any 大于子查询语句中的 最小值   >(1,2,3)   >1
SELECT * FROM student
WHERE studentNo>ANY
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))
--  some 和any功能一样
SELECT * FROM student
WHERE studentNo>SOME
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- 检查“高等数学-1” 课程最近一次考试成绩
-- 如果有 80分以上的成绩,显示分数排在前5名的学员学号和分数
--  不使用exists

-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
-- 03. 在02的基础上 加条件 成绩大于80
SELECT * FROM result 
WHERE ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
AND StudentResult>80
-- 04.优化
SELECT studentNo,StudentResult FROM result 
WHERE ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
AND StudentResult>80
ORDER BY StudentResult DESC
LIMIT 0,5
 

 

--  使用exists
-- 检查“高等数学-1” 课程最近一次考试成绩
-- 如果有 80分以上的成绩,显示分数排在前5名的学员学号和分数

-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')

-- 03.查询学号和成绩
SELECT StudentNo,StudentResult FROM result
WHERE  EXISTS
(
SELECT * FROM result
WHERE subjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
AND StudentResult>80
)
AND subjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
ORDER BY StudentResult DESC
LIMIT 0,5
 

 

 -- not  exists

-- 检查“高等数学-1”课程最近一次考试成绩

如果全部未通过考试(60分及格),认为本次考试偏难,计算的该次考试平均分加5分

-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
 
-- 03.查询成绩大于60的  反着来
SELECT StudentResult FROM result
WHERE StudentResult>60
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
 

-- 04. 如果全部未通过考试,考试平均分加5分
SELECT AVG(StudentResult)+5  FROM result
WHERE NOT EXISTS
(
SELECT StudentResult FROM result
WHERE StudentResult>60
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
)
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
 
-- 如果有 年级名称是大二 的学生,就 查询出 年级名称是大一的 所有学生信息
--  01.先查询出 对应的年级编号
SELECT GradeId FROM grade  WHERE GradeName='大一'
SELECT GradeId FROM grade  WHERE GradeName='大二'
--  02.在学生表中是否存在  年级名称是大二 的学生
SELECT * FROM  student  WHERE  gradeID=(
SELECT GradeId FROM grade  WHERE GradeName='大二'
)
-- 03.如果有查询出 年级名称是大一的 所有学生信息
SELECT * FROM student
WHERE  EXISTS
(
SELECT * FROM  student  WHERE  gradeID=(
SELECT GradeId FROM grade  WHERE GradeName='大二'
)
)
AND GradeId=(
SELECT GradeId FROM grade  WHERE GradeName='大一'
)

 

 

--  使用子查询的注意事项
--  01.任何允许使用表达式的地方都可以使用子查询
--  02.只出现在子查询中但是没有在父查询中出现的列,结果集中的列不能包含!  

  sql优化

  使用exists 代替 in
  使用not exists 代替not  in

exists 只返回true或者false.不返回结果集
in    返回结果集

-- 查询姓李的学生信息   % 代表0或者多个字符  _代表一个字符
SELECT * FROM student WHERE StudentName LIKE '李%'
SELECT * FROM student WHERE StudentName LIKE '李_'
-- 使用in完成上述代码
SELECT * FROM student WHERE StudentName IN(
SELECT studentName FROM student WHERE StudentName LIKE '李%')
--  in(多条数据--》返回结果集)
-- 使用exists替换
SELECT * FROM student WHERE EXISTS(
SELECT studentName FROM student)
AND StudentName LIKE '李%'
-- exists(有没有数据)
 

-- 统计每门课程平均分各是多少 GROUP BY  列名 分组
SELECT subjectno,AVG(studentresult)  FROM result
GROUP BY  subjectno
-- 查询出课程平均分大于60的课程编号 和 平均分
SELECT subjectno,AVG(studentresult)  FROM result
GROUP BY  subjectno
HAVING AVG(studentresult)>60   -- 分组之后的条件
-- 统计每门课程平均分各是多少 降序排列
SELECT subjectno,AVG(studentresult)  FROM result
GROUP BY  subjectno
ORDER BY AVG(studentresult) DESC
-- 如果成绩相同  再按照 课程编号 升序排序
SELECT subjectno,AVG(studentresult)  FROM result
GROUP BY  subjectno
ORDER BY AVG(studentresult) DESC,subjectno 
-- 分组统计每个年级的 男女人数
SELECT  gradeid 年级编号,sex 性别,COUNT(sex) 人数
FROM student
GROUP BY gradeid,sex

 

-- 创建表
CREATE TABLE IF NOT  EXISTS   examTest(
 id  INT(2) NOT NULL,
 sex VARCHAR(20) 
)
-- 同时新增多条数据
INSERT INTO examTest  VALUES(1,'男'),(2,'男'),(3,'女'),(4,NULL);
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
WHERE sex IS NOT NULL
GROUP BY sex
ORDER BY COUNT(sex) DESC 

SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
GROUP BY sex
HAVING sex IS NOT NULL
ORDER BY COUNT(sex) DESC 
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
WHERE  sex IN('男','女')
GROUP BY sex
ORDER BY COUNT(sex) DESC 
 

-- 创建表 
CREATE  TABLE IF NOT EXISTS mytable(
`name` VARCHAR(10) NOT NULL,
class  INT(4) NOT NULL,
sorce  DOUBLE NOT NULL
)
-- 插入数据
INSERT INTO mytable 
VALUES
('小黑1',1,88),('小黑2',1,80),
('小黑3',1,68),('小黑4',1,70),
('小黑5',1,98),('小黑6',1,90),
('小白1',2,88),('小白2',2,80),
('小白3',2,68),('小白4',2,70),
('小白5',2,98),('小白6',2,90)
--  找出表中分数的前三名
SELECT * FROM mytable
ORDER BY sorce DESC
LIMIT 0,3
--  找出每个班级的前三名   
SELECT * FROM mytable t1
WHERE
(
SELECT COUNT(1) FROM mytable t2
WHERE   t1.`sorce`<t2.`sorce`
AND t1.class=t2.`class`
)<3
ORDER BY class,sorce DESC
 

 

 

内链接中的 结果集 :
 笛卡尔积 :两个表记录的乘积!
 笛卡尔积又叫笛卡尔乘积,是一个叫笛卡尔的人提出来的。
  简单的说就是两个集合相乘的结果。
 笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尓积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员[1]  。

表连接中
on  两个表通过哪一列建立关联关系
(所有表连接同理)

 内连接 :通过匹配两个表中公共列,找到 公共的行!

 左外连接: 以左表为准,右表中没有数据返回null

 右外连接: 以右表为准,左表中没有数据返回null

 隐式内连接: 通过匹配两个表中公共列,找到 公共的行!
 
 自连接    把一个表当成多个表来使用   关键是 使用别名

-- 输出学生姓名以及对应的年级名称   内连接
SELECT StudentName,GradeName  FROM  student INNER JOIN grade
ON student.`GradeId`=grade.`GradeID`
--  隐式内连接
SELECT StudentName,GradeName  FROM  student,grade
WHERE student.`GradeId`=grade.`GradeID`
-- 查询 考试 课程编号是1的 学生姓名 以及年级名称 和科目名称以及成绩
01.
SELECT s.StudentName,GradeName,SubjectName ,studentResult FROM student s
INNER JOIN grade g  ON (s.gradeID=g.gradeID)
INNER JOIN `subject` b ON(g.gradeID=b.gradeID)
INNER JOIN result  r ON (b.subjectNo=r.subjectNo)
AND s.studentNo=r.studentNo
AND b.subjectNo=1

 

02.

SELECT StudentName,GradeName,SubjectName ,studentResult FROM
student s,grade g,`subject` b,result  r
WHERE s.gradeID=g.gradeID
AND g.gradeID=b.gradeID
AND s.studentNo=r.studentNo
AND b.subjectNo=r.subjectNo
AND b.subjectNo=1
--  查询的列 不在同一个表中!  必须使用连接查询!建立关联关系!
 --  临时表只有当前连接可见 随连接的关闭 自动删除
  --  临时表的增删改 不会影响到 真表
  CREATE TEMPORARY TABLE myStudent
  (SELECT * FROM student)
  
  SELECT * FROM myStudent
  DELETE FROM mystudent  -- 临时表的数据删除
  SELECT * FROM student -- 不会影响到真表

 

 自连接

 

--  自连接    把一个表当成多个表来使用   关键是 使用别名
 SELECT * FROM teacher
 --  查询 老师3 的姓名和  对应的 导师的姓名
 --  t1  老师   t2  导师    老师的导师编号===  导师的编号
 SELECT t1.`name` AS a,t2.`name` AS 导师姓名  FROM teacher t1,teacher t2
 WHERE  t1.`name`='老师3'
 AND t2.id=t1.tid

 

编辑:数据库 本文来源:但对于性能问题,分组前第一道过滤      

关键词: