MySQL高级查询

 

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( OENCOREDEHaval BY )  (二〇〇七后头辅助)
select * from
(
select *,row_number() over(order by 主键列) as myid from 表
) as temp
 where myid between 起首号码 and 每页数据量

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

–临时表
一时表首要用来对天意据量的表上作一个子集,进步查询功用。加速数据访谈速度
权且表存在于系统数据库
SQL Sever :
   存在于系统数据库tempdb
 #表名:局地偶尔表:
      只对前段时间对话有效
 ##表名:全局有时表
      全体会话分享

MySQL :
 在对话断开销毁
 全部不时表都以劳动于当下连连
  
临时表只在现阶段总是可见,当关闭连接时,Mysql会自行删除表并释放具有空中。因而在不一致的连年中可以创制同名的不时表,而且操作属于本连接的不时表。
 
创制偶尔表的语法与创设表语法类似,不相同之处是增添种点字TEMPORA奥迪Q7Y,如:
  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乘积是指在数学中,五个会集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

order by:排序                      6    6

limit:        最后

—分页 *
目标:为了加紧网址对数码的查询(检索)速度

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

)


-2.row_nubmer() over( OEnclaveDECRUISER BY )  (二〇〇六事后帮衬)
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会活动删除表并释放具备空中。由此在分裂的连日中得以创制同名的偶然表,何况操作属于本连接的有的时候表。
 
成立一时表的语法与创设表语法类似,分裂之处是充实重视字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

— 检查“高档数学-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的 学生姓名 以及年级名称 和科目名称以及成绩
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

 

Post Author: admin

发表评论

电子邮件地址不会被公开。 必填项已用*标注