参考视频：16. 基本的select语句和别名使用（狂神）
4. DQL查询数据（最重点） 4.1 DQL- （Data Query Language：数据查询语言）
- 所有的查询操作都用它 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}];
-- 指定查询的记录从哪条至哪条
- [] 中括号代表可选
- {} 大括号代表必选
- group by
- 一般选择需要生成的表的主键为分组字段
- 上文中select后的字段都要在groupby后面，除了聚合函数
- groupby后面可以跟多个字段，除了select后面的，其他的也可以
- 基础语法： SELECT 字段,... FROM 表;
- 有时，列名不是很见名知意。可以起别名，使用关键字as
-- 查询全部的学生 SELECt 字段 FROM 表;
SELECt * FROM student;
-- 查询全部的成绩
SELECt * FROM result;
-- 查询全部学生的成绩
SELECt
`student`.`student_no`,`student`.`student_name`,`result`.`student_result`
FROM
`student`,`result`
WHERe
`student`.`student_no` = `result`.`student_no`;
-- 查询指定字段
SELECt `student_no`,`student_name` FROM student;
-- 别名，可以给结果起名，用as，可以给字段或者表起名
SELECt `student_no` AS 学号,`student_name` AS 学生姓名 FROM student AS s;
-- 函数 Concat(a,b)
SELECt CONCAt('姓名：',student_name) AS 新名字 FROM student;
- 去重：distinct
-- 查询一下有哪些同学参加了考试，有成绩。 SELECt * FROM result; -- 查询全部的考试成绩 SELECt `student_no` FROM result; -- 查询有哪些同学参加了考试 SELECt DISTINCT `student_no` FROM result; -- 如果有重复数据，可去重
- 数据库的列（表达式）
SELECt VERSION(); -- 查询系统版本（函数） SELECT 100*3-1 AS 计算结果; -- 用来计算（表达式） SELECT @@auto_increment_increment; -- 查询自增的步长（变量） -- 学员考试成绩 +1分后 查看 SELECT `student_no`,`student_result`+1 AS '提分后' FROM result;
- 数据库中的表达式：文本值，列，null，函数，计算表达式，计算表达式，系统变量… …
- select 表达式 from 表;
- 作用：检索数据中 符合条件 的值。
- 搜索的条件由一个或者多个表达式组成！结果是布尔值。
- 逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and 即 && | a and b 即 a&&b | 逻辑与，两个都为真，结果才为真 |
| or 即 || | a or b 即 a||b | 逻辑或，其中一个为真，则结果为真 |
| not 即 ! | not a 即 !a | 逻辑非，真为假，假为真 |
- 尽量使用英文字母
-- where SELECt student_no,student_result FROM result; -- 查询考试成绩在 95-100 分之间 SELECt student_no,student_result FROM result WHERe student_result>=95 AND student_result<=100; -- and 改 && SELECt student_no,student_result FROM result WHERe student_result>=95 && student_result<=100; -- 模糊查询（区间）（照理说模糊查询的关键字是like） SELECt student_no,`student_result` FROM result WHERe student_result BETWEEN 95 AND 100; -- 除了1000号学生之外的同学的成绩 SELECt student_no,`student_result` FROM result WHERe student_no!=1000; -- != 改 not SELECt student_no,`student_result` FROM result WHERe NOT student_no=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,a3…) | 如果a在a1，或者a2…其中的某一个值中，结果为真 |
--
-- 模糊查询
-- 查询姓羽的同学
-- like结合%（代表0~任意 个字符）或者_（一个字符）
SELECt student_no,student_name FROM student
WHERe student_name LIKE '羽%';
-- 查询姓羽的同学，但是单名
SELECt student_no,student_name FROM student
WHERe student_name LIKE '羽_';
-- 查询姓周的同学，但是名是两个字
SELECt student_no,student_name FROM student
WHERe student_name LIKE '周__';
-- 查询名字中间有 伟 字的同学
SELECt student_no,student_name FROM student
WHERe student_name LIKE '%伟%';
--
-- in（具体的一个或者多个值，不能用%进行模糊查询）
-- 查询1001，1002号学员
SELECt student_no,student_name FROM student
WHERe student_no IN (1001,1002);
-- 查询在北京的学生
SELECt student_no,student_name FROM student
WHERe address IN ('北京','台湾台北');
--
-- null 和 not null
-- 查询地址为空的学生 null或者''
SELECt student_no,student_name FROM student
WHERe address='' OR address IS NULL;
-- 查询有出生日期的同学 不为空
SELECt student_no,student_name FROM student
WHERe born_date IS NOT NULL;
-- 查询没有出生日期的同学 为空
SELECt student_no,student_name FROM student
WHERe born_date IS NULL;
4.4 连表查询（联表查询？）
- 什么是联表查询？
- 7种模型
- 示例：（未扩充，以下一个为准）
-- -- 联表查询 关键字join -- 查询参加了考试的同学（学号，姓名，科目编号，分数） -- （只有内连接，既可以用where，也可以用on） SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s INNER JOIN result AS r ON s.student_no=r.student_no; -- 内连接相当于下面这个（可以用where，不可以用on） SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s,result AS r WHERe s.student_no=r.student_no; -- right join （不能用where，只能用on） SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s RIGHT JOIN result AS r ON s.student_no=r.student_no; -- left join SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s LEFT JOIN result AS r ON s.student_no=r.student_no;
- 如何确定使用左连接还是右连接？
- 左表完整，右表残缺：
- 以左表为准，将右表的null显示，所以用left join
- 不显示null，用right join
- 左表残缺，右表完整：
- 以右表为准，将左表的null显示，所以用right join
- 不显示null，用left join
| 操作 | 描述 |
|---|---|
| inner join | 内连接：返回值，只要两个表中有一个表能匹配到 |
| left join | 左连接：返回左表中所有的值，可能会因为右表不够匹配而显示一些null |
| right join | 右连接：返回右表中所有的值，可能会因为左表不够匹配而显示一些null |
- 完整示例： （已扩充）
-- -- 联表查询 关键字join -- 查询参加了考试的同学（学号，姓名，科目编号，分数） -- join（连接的表） on（判断的条件） 连接查询 -- where 等值查询 -- 内连接 -- 只有内连接，既可以用where，也可以用on SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s INNER JOIN result AS r ON s.student_no=r.student_no; -- 内连接相当于下面这个，等值查询（可以用where，不可以用on） SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s,result AS r WHERe s.student_no=r.student_no; -- right join （不能用where，只能用on） SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s RIGHT JOIN result AS r ON s.student_no=r.student_no; -- left join SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s LEFT JOIN result AS r ON s.student_no=r.student_no; -- 查询缺考者（加个where...is null来搜索null值） SELECt s.student_no,s.student_name,r.subject_no,r.student_result FROM student AS s LEFT JOIN result AS r ON s.student_no=r.student_no WHERe student_result IS NULL; -- 思考题（查询参加了考试的同学的信息：学号，学生姓名，科目名，分数） -- 来自哪些表？student、result、subject -- right+inner=参加了考试的；left+left=所有的（包括没参加考试的） SELECt student.student_no,student.student_name, subject.subject_name,result.student_result FROM student LEFT JOIN result ON student.student_no=result.student_no LEFT JOIN `subject` ON result.subject_no=subject.subject_no;
- 自连接： （了解）
- 自己的表和自己的表连接，核心：一张表拆成两张一样的表
表格一：（父类）
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
表格二：（子类）
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | 美术设计 |
表格三：（操作：查询父类对应的子类关系）
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息：把一张表看作两张一模一样的表 SELECt a.category_name AS '父栏目', b.category_name AS '子栏目' FROM category AS a,category AS b WHERe a.`category_id`=b.`pid`;
-- 查询学员所属的年级（学号，学生的姓名，年级名称） SELECt student_no,student_name,grade_name FROM student s INNER JOIN grade g ON s.grade_id=g.grade_id; -- 查询科目所属的年级（科目名称，年级名称） SELECt subject_name,grade_name FROM `subject` sub INNER JOIN grade g ON sub.grade_id=g.grade_id; -- 查询了参加 数据库结构-1 考试的学生信息（学号，学生姓名，科目名，分数） SELECt s.student_no,s.student_name,sub.subject_name,r.student_result FROM student s INNER JOIN result r ON s.student_no=r.student_no INNER JOIN `subject` sub ON r.subject_no=sub.subject_no WHERe sub.subject_name='C语言-1';4.5 分页和排序
- 排序
-- -- 分页limit 和 排序order by -- 排序：升序asc 降序desc -- order by 通过哪个字段排序，怎么排序 -- 查询的结果根据 成绩降序 排序 SELECt s.student_no,s.student_name,sub.subject_name,r.student_result FROM student s INNER JOIN result r ON s.student_no=r.student_no INNER JOIN `subject` sub ON r.subject_no=sub.subject_no WHERe sub.subject_name='高等数学-1' ORDER BY student_result ASC;
- 分页
-- 为什么分页？ -- 100万条数据时... -- 缓解数据库压力，给人的体验更好，瀑布流（往下拉，拉不到底）... -- 分页，每页只显示五条数据（此处3条） -- 语法：limit 起始数据的位置，每页数据量（页面大小） -- 网页应用：当前页，总页数，每页数据量。 -- limit 0,5 指的是第1~第5个数据 -- limit 1,5 2~6 -- limit 6,5 SELECt s.student_no,s.student_name,sub.subject_name,r.student_result FROM student s INNER JOIN result r ON s.student_no=r.student_no INNER JOIN `subject` sub ON r.subject_no=sub.subject_no WHERe sub.subject_name='高等数学-1' ORDER BY student_result ASC LIMIT 1,3; -- 第一页 limit 0,5 起始页：(1-1)*5 -- 第二页 limit 5,5 起始页：(2-1)*5 -- 第三页 limit 10,5 起始页：(3-1)*5 -- 第 N页 limit ?,5 起始页：(n-1)*pagesize -- 【页面大小：pagesize】 -- 【起始值 ：(n-1)*pagesize】 -- 【当前页 ：n】 -- 【总页数 ：数据总额/页面大小】
- 语法：
- limit(查询起始下标,pagesize);
- 练习：
-- 查询 Java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生， -- （学号，姓名，课程名称，分数） SELECt s.`student_no`,`student_name`,`subject_name`,`student_result` FROM `student` s INNER JOIN `result` r ON s.`student_no`=r.`student_no` INNER JOIN `subject` sub ON r.`subject_no`=sub.`subject_no` WHERe subject_name = '高等数学-1' AND student_result>=80 ORDER BY student_result DESC LIMIT 0,2; -- 高等数学-1排名前二4.6 子查询
- where：值是固定的，这个值是计算出来的
- 子查询本质：在where语句中嵌套一个子查询语句
- where (select * from)
-- -- where -- 1.查询数据库结构-1 的所有考试结果（学生学号，学科编号，学生成绩），降序排列 -- 方式一：使用连表查询 SELECt student_no,r.subject_no,student_result FROM result r INNER JOIN `subject` sub ON r.subject_no=sub.subject_no WHERe subject_name='高等数学-1' ORDER BY student_result DESC; -- 方式二：使用查询（由内而外） -- 查询所有数据库结构-1的学生学号 SELECt student_no,subject_no,student_result FROM result WHERe subject_no = ( SELECt subject_no FROM `subject` WHERe subject_name='高等数学-1' )ORDER BY student_result DESC; -- 查询课程为 高等数学-2 并且分数>=80分 的同学的学号和姓名 -- 连表方式 SELECt s.student_no,student_name FROM student s INNER JOIN result r ON s.student_no = r.student_no INNER JOIN `subject` sub ON r.subject_no=sub.subject_no WHERe subject_name='高等数学-2' AND student_result>=80; -- 子查询方式 -- 分数不小于80分的学生的学号和姓名 distinct去重 SELECt DISTINCT s.student_no,student_name FROM student s LEFT JOIN result r ON s.`student_no`=r.`student_no` WHERe student_result >= 80; -- 在此基础上增加：课程 高等数学-2 不小于80分 -- 相当于要 查询 高等数学-2 的编号 SELECt DISTINCT s.student_no,student_name FROM student s LEFT JOIN result r ON s.`student_no`=r.`student_no` WHERe student_result >= 80 AND subject_no=( SELECt subject_no FROM `subject` WHERe subject_name = '高等数学-2' ); -- 再改造（由内而外） SELECt student_no,student_name FROM student WHERe student_no IN (-- 要用in 除非确定只有一个 SELECt student_no FROM result WHERe student_result>=80 AND subject_no =( SELECt subject_no FROM `subject` WHERe subject_name='高等数学-1' ) ); -- 练习，查询 c语言-1 前5名同学的成绩的而信息（学号，姓名，分数） -- 使用子查询4.7 分组和过滤
-- 查询不同课程的平均分，最高分，最低分 -- 核心：根据不同的课程分组 SELECt subject_name,AVG(student_result) AS 平均分, MAX(student_result) AS 最高分, MIN(student_result) AS 最低分 FROM result r INNER JOIN `subject` AS sub ON r.subject_no = sub.subject_no GROUP BY r.subject_no -- 通过什么字段来分组 HAVINg 平均分>=60;4.8 select小结 5. MySQL函数
官网：https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
5.1 常用函数（不常用）--
-- 常用函数
-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- 返回一个0-1之间的随机数
SELECT SIGN(10); -- 判断一个数的符号，整数返回1，负数返回-1，0返回0
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航'); -- 字符串长度
SELECT CONCAt('天','地','大','同'); -- 拼接字符串
SELECT INSERT('人生若只如初见，',1,8,'何事秋风悲画扇。'); -- 查询，从某个位置替换多少长度的内容
SELECT LOWER('Zach'); -- 转成小写
SELECT UPPER('Zach'); -- 转成大写
SELECT INSTR('ZachZach','z'); -- 返回第一次出现的子串的索引
SELECT REPLACE('狂神说：坚持就能成功','成功','失败'); -- 替换出现的指定字符串
SELECT SUBSTr('狂神说：坚持就能成功',5,6); -- 返回指定的子字符串（源字符串）
SELECT REVERSe('猪是的念来过倒'); -- 返回反转字符串
-- 查询姓大的同学，改成小
SELECT REPLACE(student_name,'大','小') FROM student
WHERe student_name LIKE '大%';
-- 时间和日期函数（记住）
SELECt CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 获取本地时间
SELECT SYSDATE(); -- 获取系统时间
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();
5.2 聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| … | … |
-- -- 聚合函数 -- 都能够统计 表中的数据（想查询一个表中有多少条记录时使用count()） SELECT COUNT(born_date) FROM student; -- count(字段)，会忽略所有的null值 SELECt COUNT(*) FROM student; -- count(*)，不会忽略null值，本质：计算行数 SELECt COUNT(1) FROM result; -- count(1)，不会忽略所有的null值，本质：计算行数（把字段名改成1，计算有多少个1） -- 效率：count(1) ≈ count(*) > count(主键id) > count(字段) SELECt SUM(`student_result`) AS 总分 FROM result; SELECt AVG(`student_result`) AS 平均分 FROM result; SELECt MAX(`student_result`) AS 最高分 FROM result; SELECt MIN(`student_result`) AS 最低分 FROM result; -- 查询不同课程的平均分，最高分，最低分 -- 核心：根据不同的课程分组 SELECt subject_name,AVG(student_result) AS 平均分, MAX(student_result) AS 最高分, MIN(student_result) AS 最低分 FROM result r INNER JOIN `subject` AS sub ON r.subject_no = sub.subject_no GROUP BY r.subject_no -- 通过什么字段来分组 HAVINg 平均分>=60;
- where + 数据表中存在的字段
- having + 上文select的某个/某些字段
- 什么是MD5？
- 主要增强算法复杂度和不可逆性
- MD5是不可逆的，但同一个具体的值的md5是一样的
- MD5破解网站的破解原理：字典。MD5加密后的值→加密前的值
--
-- 测试MD5加密
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAr(20) NOT NULL,
`pwd` VARCHAr(50) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 明文密码
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');
SELECT * FROM `testmd5`;
-- 加密
UPDATe testmd5 SET pwd=MD5(pwd) WHERe id=2;
-- 插入的时候加密
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(4,'小明',MD5('123456'));
-- 如何校验，将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERe `name`='小明' AND pwd = MD5('123456');
