Java（MySQL02）

Java（MySQL02）

参考视频：16. 基本的select语句和别名使用（狂神）
参考文档：https://gitee.com/zhayuyao/java-notes/blob/master/md%E6%96%87%E6%A1%A3/%E7%8B%82%E7%A5%9E%E8%AF%B4java/03.MySql/MySQL%E5%9F%BA%E7%A1%80.md#22%E5%88%86%E9%A1%B5%E5%92%8C%E6%8E%92%E5%BA%8F

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后面的，其他的也可以

4.2 指定查询字段

1. 基础语法： 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;

2. 去重：distinct

-- 查询一下有哪些同学参加了考试，有成绩。
SELECT * FROM result;	-- 查询全部的考试成绩
SELECT `student_no` FROM result;	-- 查询有哪些同学参加了考试
SELECT DISTINCT `student_no` FROM result;	-- 如果有重复数据，可去重

3. 数据库的列（表达式）

SELECT VERSION();	-- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果;	-- 用来计算（表达式）
SELECT @@auto_increment_increment;	-- 查询自增的步长（变量）

-- 学员考试成绩 +1分后 查看
SELECT `student_no`,`student_result`+1 AS '提分后' FROM result;

• 数据库中的表达式：文本值，列，null，函数，计算表达式，计算表达式，系统变量… …
• select 表达式 from 表;

4.3 where条件子句

• 作用：检索数据中 符合条件 的值。
• 搜索的条件由一个或者多个表达式组成！结果是布尔值。

1. 逻辑运算符

运算符	语法	描述
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;

2. 模糊查询：比较运算符

运算符	语法	描述
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
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路：
1. 分析需求，分析查询的字段来自哪些表，（连接查询）
2. 确定使用那种连接查询，（共7种）
3. 确定交叉点，（两个表中那个数据是相同的）
4. 判断的条件：学生表中的student_no = 成绩表中的student_no;
*/
-- （只有内连接，既可以用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
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路：
1. 分析需求，分析查询的字段来自哪些表，（连接查询）
2. 确定使用那种连接查询，（共7种）
3. 确定交叉点，（两个表中那个数据是相同的）
4. 判断的条件：学生表中的student_no = 成绩表中的student_no;
*/
-- 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 分页和排序

1. 排序

--
-- 分页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;

2. 分页

-- 为什么分页？
-- 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的某个/某些字段

5.3 数据库级别的MD5加密（扩展）

• 什么是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');

 
 

—————— THE END ——————