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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏向治洪

Kotlin之基本语法

在今年Google IO大会上Google已经明确kotlin作为为Android第一官方语言的地位。我相信Google的决意,就像当初毫不犹豫的抛弃eclip...

2287
来自专栏小白客

常用SQL语句和语法汇总

近几年数据库发挥了越来越重要的作用,这其中和大数据、数据科学的兴起有不可分割的联系。学习数据库,可以说是每个从事IT行业的必修课。你学或不学,它就在那里;你想或...

3428
来自专栏Android群英传

Kotlin Primer·第四章·Kotlin 的类特性(下)

672
来自专栏码农笔录

特殊回文数-Java

1103
来自专栏算法与数据结构

PTA 7-1 有序链表的插入(20 分)

已知一个递增有序链表L(带头结点,元素为整数),编写程序将一个新整数插入到L中,并保持L的有序性。 其中单链表的类型定义参考如下: typedef int el...

2618
来自专栏一个会写诗的程序员的博客

第3章 Kotlin 可空类型与类型系统第3章 Kotlin 可空类型与类型系统

我们在编程语言中使用类型的目的是为了让编译器能够确定类型所关联的对象需要分配多少空间。

702
来自专栏苦逼的码农

Unicode与UTF-8的区别

要弄清Unicode与UTF-8的关系,我们还得从他们的来源说起,下来我们从刚开始的编码说起,直到Unicode的出现,我们就会感觉到他们之间的关系

1022
来自专栏项勇

笔记10 | 学习整理静态static 和 终态final

1165
来自专栏专注 Java 基础分享

虚拟机字节码执行引擎

所谓的「虚拟机字节码执行引擎」其实就是 JVM 根据 Class 文件中给出的字节码指令,基于栈解释器的一种执行机制。通俗点来说,也就是 JVM 解析字节码指令...

2454
来自专栏屈定‘s Blog

Java--Enum的思考

枚举类是Java5引进的特性,其目的是替换int枚举模式或者String枚举模式,使得语义更加清晰,另外也解决了行为和枚举绑定的问题.

974

扫码关注云+社区