本篇文章较长,可根据目录跳转至相应位置进行查看
一、指定查询字段
-- 查询全部的学生
SELECT 字段 FROM 表SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数
Concat(a,b)SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
语法:
SELECT 字段, ...... FROM 表
有的时候,列名字不是那么的见名知意。我们起别名 AS
字段名 as 别名/表明 as 别名。
去重:distinct
作用:去除SELECT 查询出来的结果中重复的数据,只显示一条
-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据,去重
SELECT DISTINCT `StudentNo` FROM result
数据库的列(表达式):
-- 查询系统版本(函数)
SELECT VERSION()
-- 用来计算(表达式)
SELECT 100*3-1 AS 计算结果
-- 查询自增的步长(变量)
SELECT @@auto_increment_increment
-- 学员考试成绩+1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
数据库中的表达式:文本值,列。Null,函数,计算表达式,系统变量…
select 表达式 from 表
二、where条件查询
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成!结果 布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a !a | 逻辑非,真为假,假为真! |
尽量使用英文字母
SELECT studentNo,`studentresult` FROM result
-- 查询考试成绩在95~100分之间的
SELECT studentNo,`studentresult` FROM resultWHERE studentresult>=95 AND studentresult<-100
-- and &&
SELECT studentNo,`studentresult` FROM resultWHERE studentresult>=95 && studentresult<-100
-- 模糊查询(区间)
SELECT studentNo,`studentresult` FROM resultWHERE studentresult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`studentresult` FROM resultWHERE studentNo != 1000
-- != not
SELECT studentNo,`studentresult` FROM resultWHERE NOT studentNo = 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为null,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为null,结果为真 |
BETWEEN | a between b and c | 若a在b和c之间,则结果为真 |
like | a like b | SQL匹配,若果a匹配b,则结果为真 |
in | a in (a1, a2,…) | 假设a在a1,或者a2,…其中的某一个值中,结果为真 |
-- ======================模糊查询=========================
-- 查询姓刘的同学-- like 结合 %(代表0到任意个字符) _(一个字符)
SELECT `studentno`,`studentname` FROM `student`WHERE studentname LIKE '刘%'
-- 查询姓刘的同学,名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`WHERE studentname LIKE '刘_'
-- 查询姓刘的同学,名字后面只有两个字的
SELECT `studentno`,`studentname` FROM `student`WHERE studentname LIKE '刘__'
-- 查询名字中间有加字的同学 %加%
SELECT `studentno`,`studentname` FROM `student`WHERE studentname LIKE '%加%'
-- ===============in(具体的一个或多个值)=======================
-- 查询 1001,1002,1003号学员
SELECT `studentno`,`studentname` FROM `student`WHERE studentno IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`WHERE `address` IN ('唐山','吉林长春')
-- ======================null not null==================
-- 查询地址为空的学生 null ''
SELECT `studentno`,`studentname` FROM `student`WHERE address='' OR address IS NULL
-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`WHERE `borndate` IS NOT NULL
-- 查询没有出生日期的同学 为空
SELECT `studentno`,`studentname` FROM `student`WHERE borndate IS NULL
三、联表查询
join 对比
-- =======================联表查询 join=============================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*
思路:
1、分析需求,分析查询的字段来自哪些表,(连接查询)
2、确定使用哪种连接查询? 7种
3、确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表 studentNo
*/
SELECT s.studentNo,studentName,subjectNo,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.`studentno`=r.`studentno`
-- right join
SELECT s.studentNo,studentName,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
-- left join
SELECT s.studentNo,studentName,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
操作 | 描述 |
---|---|
inner join | 若果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,及时右表中没有匹配 |
right join | 会从右表中返回所有的值,即是左表中没有匹配 |
-- =======================联表查询 join=============================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*
思路:
1、分析需求,分析查询的字段来自哪些表,(连接查询)
2、确定使用哪种连接查询? 7种
3、确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表 studentNo
*/
-- join (连接的表) on (判断的条件) 连接查询
-- where 等值查询
SELECT s.studentNo,studentName,subjectNo,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.`studentno`=r.`studentno`
SELECT s.studentNo,studentName,subjectNo,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.`studentno`=r.`studentno`
-- right join
SELECT s.studentNo,studentName,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
-- left join
SELECT s.studentNo,studentName,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
-- 查询缺考的同学
SELECT s.`studentno`,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
WHERE studentresult = NULL
-- 思考题:查询了参加考试的 学生信息:学号,学生姓名,科目名,分数
/*
思路:
1、分析需求,分析查询的字段来自哪些表,student,result,subject(连接查询)
2、确定使用哪种连接查询? 7种
3、确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表 studentNo
*/
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.subject
-- 我要查询哪些数据 select .....
-- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
-- from a left join b
-- from a right join b
自连接(了解)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术技术 |
子类
pid | categoryId | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息:把一张表看成一模一样的两张表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
练习:
-- 查询学员所属的年级(学号,学生姓名,年级名称)
SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid
-- 查询科目所属的年级(科目名称,年级名称)
SELECT subjectname,gradename
FROM `subject` sub
INNER JOIN `grade` g
ON sub.gradeid = g.gradeid
-- 思考题(查询了参加 数据库结构-1 考试的同学信息:学号,学生姓名,科目名,分数)
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` subON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectname='高等数学-1'
四、分页和排序
排序
-- 排序: 升序 asc ,降序 desc
-- order by 通过哪个字段排序 怎么排
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC
分页
-- 100万
-- 为什么要分页?
-- 缓解数据库压力,给人的体验更好, 瀑布流
-- 分页,每页只显示五条数据
-- 语法: limit 当前,总的页数,页面的大小
-- limit 0,5 1~5
-- limit 1,5 2~6
-- limit 6,5
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
-- WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC
LIMIT 1,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第n页 limit 0,5 (n-1)*pagesize,pagesize
-- 【pagesize:页面大小】
-- 【(n-1)*pagesize:起始值】
-- 【n:当前页】
-- 【数据总数/页面大小 = 总页数】
语法:limit(查询起始下标,pagesize)
思考:
-- 查询 java 第一学年 课程成绩排名前十的学生,并且分数要大于80 的学生信息(学号,姓名,课程名称,分数)
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
WHERE subjectname='Java程序设计-1' AND r.studentresult > 80
ORDER BY studentresult
LIMIT 0,10
五、子查询
where (这个值是计算出来的)
本质:在where语句中嵌套一个查询语句
where (select * from )
-- ========================================================where=====================================
-- 1、查询 Java程序设计-1 的所有考试结果(学号,科目编号,成绩),降序排列
-- 方式一:使用连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
WHERE subjectname='Java程序设计-1'
ORDER BY studentresult DESC
-- 方式二:使用子查询(由里及外)
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno=(
SELECT subjectno
FROM `subject`
WHERE subjectname='Java程序设计-1'
)
ORDER BY studentresult DESC
-- 查询所有Java程序设计-1 的学生学号
SELECT subjectno FROM `subject` WHERE subjectname='Java程序设计-1'
-- 查询课程为 高等数学-2 且分数不小于 80 的学生的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE studentresult >=80 AND subjectname='高等数学-2'
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE studentresult >=80
-- 在这个基础上增加一个科目,高等数学-2
-- 查询 高等数学-2 的编号
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE studentresult >=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-2'
)
-- 再改造(由里及外)
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
– 练习:查询 C语言-1 前5名同学的成绩的信息(学号,姓名,分数)
使用子查询
SELECT studentno 学号,(SELECT studentname FROM student WHERE studentno = result.`studentno`) '姓名',studentresult 分数
FROM result
WHERE studentno IN (SELECT studentno FROM student)
AND subjectno IN (SELECT subjectno FROM `subject` WHERE subjectname='C语言-1')
ORDER BY studentresult DESC
LIMIT 0,5
六、分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>=80
七、select小结
select 语法:
select 语法:
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,......]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] --联合查询
[WHERE ...] --指定结果需满足的条件
[GROUP BY ...] --指定结果按照哪几个字段来分组
[HAVING] --过滤分组的记录必须满足的次要条件
[ORDER BY ...] --指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; --指定查询的记录从哪条至哪条
注意:[ ]括号代表可选的,{ }括号代表必选的
顺序很重要
select 去重 要查询的字段 from 表 (注意:表和字段可以取别名)
xxx join 要连接的表 on 等值判断
where (具体的值,子查询语句)
group by (通过哪个字段来分组)
having (过滤分组后的信息,条件和 where 是一样的,位置不同)
order by ... (通过哪个字段排序) [升序|降序]
limit startindex, pagesize
查询:跨表、跨数据库...