DQL查询数据
指定查询字段
-- 查询全部学生
SELECT * FROM student
-- 查询指定字段
SELECT `name`,`pwd` FROM student
-- 别名
SELECT `id` AS 学号,`name` AS 学生姓名 FROM student AS a
-- 函数
SELECT CONCAT('姓名:',name) AS 新名字 FROM student
去重
SELECT * FROM result
SELECT `StudentNo` FROM result
-- 发现重复数据,去重
SELECT DISTINCT `StudentNo` FROM result
数据库的列(表达式)
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增步长(变量)
SELECT `StudentNo`, `StudentResult`+1 AS '提分后' FROM result
WHERE条件子句
SELECT `StudentNo`, `StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
-- 模糊查询(区间)
SELECT `StudentNo`, `StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 模糊查询(非真)
SELECT `StudentNo`, `StudentResult` FROM result
WHERE NOT StudentNo=1000
模糊查询
-- 查询学号以0121开头的同学
-- like结合 %(代表0到任意个字符) _(代表一个字符)
SELECT `StudentNo`, `StudentResult` FROM result
WHERE StudentNo LIKE '0121%'
-- in (具体的一个或者多个值)
SELECT `StudentNo`, `StudentResult` FROM result
WHERE StudentNo IN (1001,1002,1003);
-- 查询地址为空的学生
SELECT `StudentNo`, `StudentResult` FROM result
WHERE address='' OR address IS NULL
联表查询
SELECT * FROM student
SELECT * FROM reslut
/*联表查询
1、分析查询字段来自哪些表
2、确定使用哪种联接查询?7种
3、确定交叉点
*/
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN reslut AS r
ON s.studentNO=r.studentNOsql
查询方法:
Inner join 如果表中至少有一个匹配,就返回行(并集)
left join 会从左表中返回所有值,即 使右表中没有匹配
right join 会从右表中返回所有值,即 使左表中没有匹配
自连接
核心:一张表拆为两张一样的表
CREATE TABLE IF NOT EXISTS `category` (
`categoryid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '子类id',
`pid` VARCHAR(50) NOT NULL COMMENT '父类id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '课程名称',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
分页和排序
排序:ODER BY
-- 排序:升序 ASC,降序 DESC
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`
WHERE subjectName='数据库结构-1'
ORDER BY StudentResult ASC
分页:
-- 分页:limit 起始值 页面大小
-- 网页应用:当前页面,总页数,页面大小
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`
WHERE subjectName='数据库结构-1'
ORDER BY StudentResult ASC
LIMIT 6,5
第一页 limit 0,5
第二页 limit 5,5
子查询
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`
)
)