- 五、DQL(查询语言)`重点`
- 5.1 准备
- 5.2 指定查询字段
- 5.3 去重 distinct
- 5.4 数据库的列(表达式)
- 5.5 where条件语句
- 5.5.1 逻辑运算符
- 5.5.2 练习
- 5.6 模糊查询:比较运算符
- 5.6.1 练习
- 5.7 联表查询(掌握)
- 5.7.1 join on 连接查询
- 5.7.2 join on where
- 5.7.3 自联结查询(了解)
- 5.8 分页和排序
- 5.9 子查询和嵌套查询
- 5.10 分组和过滤
学习视频来自于:秦疆(遇见狂神说)Bilibili地址
他的自学网站:kuangstudy
我们缺乏的不是知识,而是学而不厌的态度
五、DQL(查询语言)重点
重点掌握,MySQL主要就是学习这个。
(Data Query Language:数据库查询语言)。
查询语句结构
SELECt
[ ALL | DISTINCT | DISTINCTROW ] -- 去重
[ HIGH_PRIORITY ]
[ STRAIGHT_JOIN ]
[ SQL_SMALL_RESULT ][ SQL_BIG_RESULT ][ SQL_BUFFER_RESULT ]
[ SQL_CACHE | SQL_NO_CACHE ][ SQL_CALC_FOUND_ROWS ]
select_expr [, select_expr...]
[ FROM table_references
[ PARTITION partition_list ]
[ WHERe where_condition ] -- 指定结果需要满足的条件
[ GROUP BY { col_name | expr | position } -- 指定结果按照那几个字段来分组
[ ASC | DESC ] ,...[ WITH ROLLUP ]]
[ HAVINg where_condition ] -- 过滤分组的记录必须满足的次要条件
[ ORDER BY { col_name | expr | position } -- 指定查询记录一个或者多个条件排序
[ ASC | DESC ],...]
[ LIMIT {[ OFFSET ,] row_count | row_count OFFSET offset }] -- 指定查询的记录从那条至那跳
[ PROCEDURE procedure_name (argument_list) ]
[ INTO OUTFILe 'file_name'
[ CHARACTER SET charset_name ]
export_options
| INTO DUMPFILe 'file_name'
| INTO var_name [, var_name]]
[ FOR UPDATe | LOCK IN SHARE MODE]]
表结构
CREATE TABLE `grade` ( `gradeid` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号', `gradename` varchar(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`gradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `result` ( `studentno` int(4) NOT NULL COMMENT '学号', `subjectno` int(4) NOT NULL COMMENT '课程编号', `examdate` datetime NOT NULL COMMENT '考试日期', `studentresult` int(4) NOT NULL COMMENT '考试成绩', KEY `subjectno` (`subjectno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `student` ( `studentno` int(4) NOT NULL COMMENT '学号', `loginpwd` varchar(20) DEFAULT NULL, `studentname` varchar(20) DEFAULT NULL COMMENT '学生姓名', `sex` tinyint(1) DEFAULT NULL COMMENT '性别,0或1', `gradeid` int(11) DEFAULT NULL COMMENT '年级编号', `phone` varchar(50) NOT NULL COMMENT '联系电话,允许为空', `address` varchar(255) NOT NULL COMMENT '地址,允许为空', `borndate` datetime DEFAULT NULL COMMENT '出生时间', `email` varchar(50) NOT NULL COMMENT '邮箱账号允许为空邮箱账号允许为空', `identitycard` varchar(18) DEFAULT NULL COMMENT '身份证号', PRIMARY KEY (`studentno`), UNIQUE KEY `identitycard` (`identitycard`), KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `subject` ( `subjectno` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号', `subjectname` varchar(50) DEFAULT NULL COMMENT '课程名称', `classhour` int(4) DEFAULT NULL COMMENT '学时', `gradeid` int(4) DEFAULT NULL COMMENT '年级编号', PRIMARY KEY (`subjectno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入数据
-- 插入学生数据 其余自行添加 这里只添加了2行 INSERT INTO `student` ( `studentno`, `loginpwd`, `studentname`, `sex`, `gradeid`, `phone`, `address`, `borndate`, `email`, `identitycard` ) VALUES (1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'), (1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'); -- 插入成绩数据 这里仅插入了一组,其余自行添加 INSERT INTO `result` ( `studentno`, `subjectno`, `examdate`, `studentresult` ) VALUES (1000,1,'2013-11-11 16:00:00',85), (1000,2,'2013-11-12 16:00:00',70), (1000,3,'2013-11-11 09:00:00',68), (1000,4,'2013-11-13 16:00:00',98), (1000,5,'2013-11-14 16:00:00',58); -- 插入年级数据 INSERT INTO `grade` (`gradeid`, `gradename`) VALUES (1, '大一'), (2, '大二'), (3, '大三'), (4, '大四'), (5, '预科班'); -- 插入科目数据 INSERT INTO `subject` ( `subjectno`, `subjectname`, `classhour`, `gradeid` ) VALUES (1, '高等数学-1', 110, 1), (2, '高等数学-2', 110, 2), (3, '高等数学-3', 100, 3), (4, '高等数学-4', 130, 4), (5, 'C语言-1', 110, 1), (6, 'C语言-2', 110, 2), (7, 'C语言-3', 100, 3), (8, 'C语言-4', 130, 4), (9,'Java程序设计-1',110,1), (10,'Java程序设计-2',110,2), (11,'Java程序设计-3',100,3), (12,'Java程序设计-4',130,4), (13,'数据库结构-1',110,1), (14,'数据库结构-2',110,2), (15,'数据库结构-3',100,3), (16,'数据库结构-4',130,4), (17, 'C#基础', 130, 1);5.2 指定查询字段
-- 查询全部的学生 格式:SELECT * FROM 表
SELECt * FROM `student`;
-- 查询指定字段
SELECt `name` AS '姓名',`sex` AS '性别' FROM `student`;
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECt `name` AS '姓名',`sex` AS '性别' FROM `student` AS 's';
-- 函数Concat(a,b)
SELECt CONCAt('姓名:',`name`) AS '新名字' FROM student;
5.3 去重 distinct
作用:去除SELECt 查询出来的结果中重复的数据,重复的数据只能显示一条。
-- 查询所有 SELECT * FROM student -- 查询学生名字 SELECt `name` FROM `student`; -- 将名字相同的数据只显示一条 SELECt DISTINCT `name` FROM `student`;5.4 数据库的列(表达式)
-- 查询系统版本 SELECt VERSION(); -- 用来计算(表达式) SELECT 100*3-50 AS '计算结果'; -- 查询自增的步长 SELECT @@auto_increment_increment; -- 所有学生年龄+1 SELECT `name`,`age`+1 AS '年龄+1' FROM `student`;
数据库中的表达式:文本值、列、null、函数、计算表达式、系统变量。
5.5 where条件语句**作用:**检索数据中符合条件的值。
搜索的条件有一个或者多个表达式组成,结果布尔值。
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
| or || | a or b a||b | 逻辑或,其中一个为真,结果为真 |
| not ! | not a !a | 逻辑非,真伪假,假为真 |
-- 查询考试成绩在95~100之间的 select `studentno`,`subjectno`,`studentresult` from `result` where `studentresult` >=95 and `studentresult` <=100; -- 除了1000号学生之外的学生成绩 select `studentno`,`subjectno`,`studentresult` from result where `studentno`!=1000;5.6 模糊查询:比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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,a3) | 假设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 '%强%'
-- 查询 1001 1000 号学员
SELECt `studentno`,`studentname` FROM `student`
WHERe `studentno` in (1000,1001);
-- 查询地址在北京的学员
SELECt `studentno`,`studentname` FROM `student`
WHERe `address` in ('北京朝阳');
-- 查询出生日期为null的学员
SELECt `studentno`,`studentname` FROM `student`
WHERe address='' OR address IS NULL;
-- 查询出生日期不为空的学员
SELECt `studentno`,`studentname` FROM `student`
WHERe`address` IS NOT NULL;
5.7 联表查询(掌握)
5.7.1 join on 连接查询
join(连接的表)on(判断条件)连接查询
-- student表、result表联合查询:姓名、学科编号、分数 -- INNER JOIN:取两张表公共部分 SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数' from student AS s INNER JOIN result AS r ON s.studentno = r.studentno; -- LEFT JOIN:以左表为主表查询 SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数' from student AS s LEFT JOIN result AS r ON s.studentno = r.studentno; -- RIGHT JOIN:以右表左主表查询 SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数' from student AS s RIGHT JOIN result AS r ON s.studentno = r.studentno; -- student表、result表、subject表联合查询:姓名、学科、分数 -- Left Join SELECt s.studentname AS '姓名',su.subjectname AS '学科',studentresult AS '分数' from `student` AS s LEFT JOIN `result` AS r ON s.studentno = r.studentno LEFT JOIN `subject` AS su ON r.subjectno = su.subjectno;
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配,就返回行 |
| left join | 会从左表中返回所有的值,即使右表中没有匹配 |
| rigth join | 会从右表中返回所有的值,即使左表中没有匹配 |
where等值查询
-- LEFT JOIN + WHERe SELECt s.studentname AS '姓名',subjectno AS '学科',studentresult AS '分数' from student AS s LEFT JOIN result AS r ON s.studentno = r.studentno WHERe studentresult IS NULL;5.7.3 自联结查询(了解)
自己的表和自己的表连接,核心:一张表拆成两张一样的表。
表结构
-- 自连接数据库:
CREATE TABLE `school`.`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 CHARSET = utf8
-- 数据插入
INSERT INTO `school`.`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; -- 查询学号、姓名、年级 SELECt s.studentno AS '学号',s.studentname AS '姓名',g.gradename AS '年级' FROM student AS s INNER JOIN grade AS g ON s.gradeid = g.gradeid; -- 查询学科所属的年级(学科名称、年级名称) SELECt su.subjectname,g.gradename FROM `subject` AS su INNER JOIN grade AS g ON su.gradeid = g.gradeid; -- 查询参加了考试同学的信息:学号、姓名、学科、分数 SELECt s.studentno,s.studentname,su.subjectname,r.studentresult FROM student AS s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` AS su ON r.subjectno = su.subjectno WHERe su.subjectname = '数据库结构-1';5.8 分页和排序
排序:
order by
分页:
缓解数据库的压力,给人的体验更好。
limit
-- 排序:升序ASC、降序DESC 按成绩升序排列 SELECt s.studentno,s.studentname,su.subjectname,r.studentresult FROM student AS s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` AS su ON r.subjectno = su.subjectno ORDER BY r.studentresult ASC; -- 分页:limit 起始值,每页显示数量 SELECt s.studentno,s.studentname,su.subjectname,r.studentresult FROM student AS s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` AS su ON r.subjectno = su.subjectno ORDER BY r.studentresult ASC LIMIT 0,5; -- 第一页 limit 0,5 -- 第二页 limit 5,5 -- 第n也 limit (n - 1) * pageSize,pageSize -- pageSize:页面大小 -- n:代表当前页数 -- 数据总数/页面大小 = 总页数 -- 查询大一高等数学考试排名前10且分数大于80的同学信息:学号、姓名、学科、分数 SELECt s.studentno AS '学号',s.studentname AS '姓名',g.gradename as '年级',su.subjectname as '科目',r.studentresult as '分数' FROM student AS s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` AS su ON r.subjectno = su.subjectno INNER JOIN grade AS g ON su.gradeid = g.gradeid WHERe gradename = '大一' AND su.subjectname = '高等数学-1' AND r.studentresult >80 ORDER BY r.studentresult DESC LIMIT 0,10;5.9 子查询和嵌套查询
在查询条件中嵌套查询。
-- 查询'数据库结构-1'的学生学号 SELECt studentno ,`subjectno`,studentresult FROM result WHERe subjectno = (SELECt subjectno FROM `subject` WHERe subjectname = '数据库结构-1');5.10 分组和过滤
GROUP BY
分组
**HAVINg **
过滤
-- 查询不同课程的平均分、最高分、最低分 -- 核心:(根据不同的课程分组) SELECt `subjectname`,AVG(studentResult) AS 平均分,MAX(studentResult) AS 最大,MIN(studentResult) AS 最小 FROM result AS r INNER JOIN `subject` su ON r.subjectno = su.subjectno GROUP BY r.subjectno -- 通过subjectno来分组 HAVINg 平均分 >= 70 -- 过滤平均分大于70



