1.模糊查询
-- ====================== 模糊查询============
-- 查询性赵的同学
-- like结合 %(代表0到任意个字符) _(代表一个字符)
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE ('赵%')
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE ('赵_')
-- =========== in(具体的一个或多个值) ====
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN(1001,1000)
SELECT `studentno`,`studentname` FROM student
WHERE `address`IN('北京朝阳')
-- 查询有出生日期的同学 不为空; 查询为空的 把not去掉即可
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NOT NULL
2.联表查询
/*思路:
1. 分析需求,分析查询的字段来自哪些表
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件: 学生表中 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
RIGHT JOIN result AS r
ON s.`studentno`= r.`studentno`
-- 左查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN result AS r
ON s.`studentno`= r.`studentno`
-- 查询了参加考试 高等数学-1 同学的信息,学号、学生姓名、科目名、分数
-- 三表查询
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
RIGHT JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno` WHERE subjectname='高等数学-1'
3.自连接查询
-- ===================== 自连接 =================
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `category` (`categoryid`, `pid`, `categoryname`)
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
-- 查询父子信息,把一张表看为俩个一摸一的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE b.`categoryid`=a.`pid`
4.分页和排序
-- 语法: limit 起始位置,页面的大小
-- limit 0,5 :从0开始,显示五条记录,即 1-5
-- limit 5,5 :从5开始,显示五条记录,即 6-10
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
ORDER BY StudentResult ASC
LIMIT 2,2
-- 查询 高等数学-1,课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname='高等数学-1' AND studentresult>80
ORDER BY studentresult DESC
LIMIT 0,10
5.子查询
-- ================== where 子查询 =============
-- 查询 高等数学-1的所有考试结果(学号,姓名,课程名,分数)
-- 方式一:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
-- 方式二:使用子查询(由里及外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-1'
)
ORDER BY studentresult DESC
-- 再改造
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo FROM result WHERE StudentResult > 80 AND SubjectNo = (
SELECT SubjectNo FROM `subject` WHERE `SubjectName`='高等数学-2'
)
)
6.
-- ============常用函数 =======================
-- 时间和日期函数(记住)
SELECT NOW() -- 获取当前的时间
SELECT YEAR(NOW())-- 获取当前年份
7 .group by
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段分组
HAVING AVG(`studentresult`)>80