MySQL高级查询

作者: 数据库信息  发布:2019-08-15

 

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( O路虎极光DE本田UR-V BY )  (二〇〇五自此支持)
select * from
(
select *,row_number() over(order by 主键列) as myid from 表
) as temp
 where myid between 开始号码 and 每页数据量

--mysql :
SELECT <字段名列表>
FROM <表名或视图>
[WHERE <查询条件>]
[GROUP BY <分组的字段名>]
[O本田UR-VDEPAJERO BY <排序的列名> [ASC 或DESC]]
[LIMIT [地方偏移量,]行数];

--临时表
有的时候表首要用以对时局据量的表上作三个子集,进步查询功用。加快数据访问速度
暂且表存在于系统数据库
SQL Sever :
   存在于系统数据库tempdb
 #表名:局地不经常表:
      只对这段日子对话有效
 ##表名:全局有的时候表
      全部会话分享

MySQL :
 在对话断费用毁
 全体一时表都以劳务于前段时间连连
   不经常表只在此时此刻连年可见,当关闭连接时,Mysql会活动删除表并释放具有空中。因而在不一致的连小刑得以成立同名的有时表,而且操作属于本连接的有时表。
  创立有的时候表的语法与创制表语法类似,不一致之处是充实珍视字TEMPORA普拉多Y,如:
  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

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

-- 检查“高端数学-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

 

 

内链接中的 结果集 :
 笛Carl积 :八个表记录的乘积!
 笛Carl积又叫笛Carl乘积,是贰个叫笛Carl的人建议来的。
  一言以蔽之便是七个聚众相乘的结果。
 笛Carl乘积是指在数学中,多个会集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的 学生姓名 以及年级名称 和课程名称以及成就

  1. 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
  1. 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

高端查询 关键字书写顺序关键字实施顺序 select:投影结果 15 from:定位到表 21 where:分组前先是道过滤 32 group by:分组 4...

from:定位到表             2    1

where:分组前率先道过滤       3    2

group by:分组                4    3

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

本文由金沙澳门官网发布于数据库信息,转载请注明出处:MySQL高级查询

关键词: 金沙澳门官网

上一篇:总结网站Mysql优化
下一篇:没有了