MySQL高级查询

 

 高级查询

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

from：定位到表             2    1

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

group by：分组                4    3

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

order by：排序                      6    6

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