MySQL学习笔记二（MySQL数据管理、DQL查询数据（重点重点）、函数、MD5加密）

MySQL数据管理，DQL数据查询，函数，MD5加密

• MySQL数据管理
• • 3.1 外键（了解即可）
  • 3.2 DML语言（全部记住）
  • 3.3 添加数据
  • 3.4 修改
  • 3.5 删除
• 4. DQL查询数据
• • 4.1 DQL
  • 4.2 指定查询字段
  • 4.3 where条件子句
  • 4.4 联表查询
  • 4.5 分页和排序
  • 4.6 子查询
  • 4.7 分组过滤
  • 4.8 select小结
• 5. MySQL函数
• • 5.1 常用函数
  • 5.2 聚合函数
  • 5.3 数据库级别的MD5加密

MySQL数据管理 3.1 外键（了解即可）

方式一： 在创建表的时候，增加约束（麻烦，比较复杂）

注意： 删除有外键关系的表的时候，一定必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方式二： 创建表成功以后，添加外键约束

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`)REFERENCES `grade`(`gradeid`);

拆解：
ALTER TABL 表名  ADD CONSTRAINT  约束名  FOREIGN KEY（作为外键的列）REFERENCES  那个表（哪个字段）

~~ 以上操作都是物理外键，数据库级别的外键，我们不建议使用！ ~~
（避免数据库过多造成困扰，了解即可）
阿里java规范中： 不得使用外键与级联，一切外键概念必须在应用层解决，因为每次在做delete或者uodate时都必须考虑外键约束，会导致开发的时候很痛苦，测试数据极为不便

== 最佳实践：==

1. 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
2. 我们想使用多张表的数据，想使用外键（程序去实现，用方式二）

3.2 DML语言（全部记住）

数据库意义： 数据存储，数据管理

1. insert
2. update
3. delete

3.3 添加数据

-- 插入语句（添加）
-- insert into  表名 ([字段名1，字段2，字段3])values('值1'),('值2'),('值3'...)，...
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略(如果不写表的字段，自己会一一匹配)
INSERT INTO `grade`(`gradename`) VALUES('大三')

一般写插入语句，我们一定要数据和字段一一对应！

-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大二'),('大一')

INSERT INTO `student`(`pwd`) VALUES('225584')
INSERT INTO `student`(`name`) VALUES('张三')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','aaaaaaa','女')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('王五','969548','男'),('老六','6666','女')

INSERT INTO `student`
VALUES (7,'雯雯','1010','女','2002-03-21','西安','email')


语法：insert into  表名 ([字段名1，字段2，字段3])values('值1'),('值2'),('值3'...)，...

== 注意事项 ==
1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，VALUES后面的值，需要使用英文逗号隔开
	Eg：VALUES(),(),...

3.4 修改

在这里插入代码Update    修改谁  （条件）     set原来的值=新值
--修改学员名字，带了条件
UPDATE  `student`  SET  `name`=’张三封’  WHERe id = 1;

-- 不指定条件的情况下，会改动所有的表！
UPDATE  `student`  SET  `name`='张三封'

--修改多个属性，逗号隔开
UPDATE  `student`  SET  `name`=’张三封’ ,`email`=’56941494@qq.com’ WHERe id = 1;
片

条件：where 子句 运算符 id 等于某个值 or 大于某个值 or 在某个区间修改…

操作符会返回 布尔值
操作符：
=,  >,  <,  >=,  <=,  <> or != (不等于),  BETWEEN..and..(闭合区间),  
&&（AND）,  ||（OR）,

--通过多个条件定位数据   **trim标签保证把多余的逗号删掉
UPDATE  `student`  SET  `name`=’老三’  WHERe name = ‘张66’ AND sex = ‘女’

语法：UPDATE 表名 set = value,[conlum_name = value,…] where [条件]

注意事项：
1.	clnum_name 是数据库的列，尽量带上``,
2.	SET后面是要修改为什么，WHERe后跟的是表里现有的条件
3.	条件，筛选的条件，如果没有指定，则修改所有的列
4.	value，是一个具体的值，也可以是一个变量
5.	多个设置的属性之间，使用英文逗号隔开
eg: UPDATE `student` SET `birthday`= CURRENT_TIME WHERe `name`='张66' AND sex='男'

3.5 删除

语法：
Delete from 表名 [where 条件]

-- 删除数据（一定加条件，避免数据库全部删除）
DELETe   FROM   `student`   WHERe   id  =  5;

--清空 student 表  truncate(截取 截断)
TRUNCATE  `student`

--delete 与 truncate 区别
相同点：都能删除数据，都不会删除表结构
不同点：truncate  重新设置 自增列 计数器会归零
		Truncate  不会影响事务

测试：
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAr(20)NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8


INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')

DELETE FROM `test` -- 不影响自增

TRUNCATE TABLE `test` -- 自增会归零

了解即可： delete删除问题，重启数据库 现象
1. InnoDB 自增列会从1开始（存在内存当中，断电即失）
2. MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）

4. DQL查询数据 4.1 DQL

data Query Language：数据查询语言

1. 所有查询操作都用它Select
2. 简单的查询，复杂的查询它都能做
3. 数据库中最核心的语言，最重要的语句
4. 使用频率最高的语句

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}];

-- 指定查询的记录从哪条至哪条
注意 : [ ] 括号代表可选的 , { }括号代表必选得

4.2 指定查询字段

-- 查询所有学生信息
SELECt * FROM student;

-- 查询指定列(学号 , 姓名)
SELECt studentno,studentname FROM student;

-- 别名，给结果起一个名字  AS  可以给字段起别名，也可以给表起别名
SELECt studentno AS 学号,studentname AS 姓名 FROM student AS s;  

-- CONCAt()函数拼接字符串
SELECt CONCAt('姓名:',studentname) AS 新姓名 FROM student;

语法： SELECt 字段,…FROM表

*有时候，列名字不是那么见名知意，起别名 AS 字段名 as 别名 表名 as 别名

去重（distinct）
作用： 去除SELECt查询出来的结果中重复的数据，只显示一条

-- 查看哪些同学参加了考试，去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECt studentno FROM result; -- 查看哪些同学参加了考试
SELECt DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

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

-- 学员考试成绩  +1分查看
SELECT `studentNo`,`studentResult` +1 AS ‘提分后’ FROM result
数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量.....
select 表达式 from 表

4.3 where条件子句

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

逻辑运算符：

== 尽量使用英文字母 ==

-- 满足条件的查询(where)
SELECt Studentno,StudentResult FROM result;

-- 查询考试成绩在95-100之间的
SELECt Studentno,StudentResult FROM result
WHERe StudentResult>=95 AND StudentResult<=100;

-- AND    &&
SELECt Studentno,StudentResult FROM result
WHERe StudentResult>=95 && StudentResult<=100;

-- 模糊查询(对应的词:精确查询)
SELECt Studentno,StudentResult FROM result
WHERe StudentResult BETWEEN 95 AND 100;

-- 除了1000号同学,要其他同学的成绩
SELECt studentno,studentresult FROM result
WHERe studentno!=1000;

-- 使用NOT   !=
SELECt studentno,studentresult FROM result
WHERe NOT studentno=1000; 

模糊查询：比较运算符

-- 查询姓刘的同学
-- 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 '%嘉%';


--  IN
-- 查询学号为1000,1001,1002的学生姓名
SELECt studentno,studentname FROM student
WHERe studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECt studentno,studentname,address FROM student
WHERe address IN ('北京','南京','河南洛阳');


--   null    not null
-- 查询地址为空的学生 null     ‘  ’
SELECt `StudentNo`，`StudentName` FROM `student`
WHERe address=’ ’  OR  address IS NULL
-- 查询出生日期没有填写的同学，不能直接写=NULL , 这是代表错误的 , 用 is null
SELECt studentname FROM student  WHERe BornDate IS NULL;
-- 查询出生日期填写的同学
SELECt studentname FROM student
WHERe BornDate IS NOT NULL;

4.4 联表查询

先看思路：

1. 分析需求：确定查询的列来自哪些表，student，result，连接查询
2. 确定使用哪种连接查询？ 7种
3. 确定交叉点（这两个表中的哪个数据是相同的）
4. 判断条件：学生表中的 studenNo = 成绩表 studenNo

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECt * FROM student;
SELECt * FROM result;

----inner join（七种里的第三种）
SELECt s.studentno,studentname,subjectno,StudentResult
FROM student  AS  s
INNER JOIN result  AS  r          
WHERe  s.studentno = r.studentno

----Right Join
SELECt s.studentno,studentname,subjectno,StudentResult
FROM student  s  RIGHT JOIN  result r  (有左右关系)
ON s.studentNO  =  r.studentNO

----Left Join
SELECt s.studentno,studentname,subjectno,StudentResult
FROM student  s  RIGHT JOIN  result r
ON s.studentNO  =  r.studentNO

---- 查看缺考的同学
SELECt s.studentno,studentname,subjectno,StudentResult
FROM student  s  RIGHT JOIN  result r
ON s.studentNO  =  r.studentNO
WHERe  StudentResult  IS  NULL

---------------------------
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
INNER JOIN result  AS  r          
ON s.studentno = r.studentno

我要查询哪些数据 select.....
从那几个表中查 FROM 表 XXX  Join 连接的表  on  交叉条件
假设存在一种多张表查询，一定要慢慢来，先查询两张表然后再慢慢增加

自连接（了解）
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 查询父子信息：把一张表看成两个一模一样的表
SELECt a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERe a.`categoryid` = b.`pid`

-- 练习：
-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECt studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.`gradeid` = g.`gradeid`

-- 查询科目所属的年级（科目名称，年级名称）查的是交集 inner join
SELECt `subjectname`,`gradename`
FROM `subject` s
INNER JOIN`grade` g
ON s.`gradeid` = g.`gradeid`

-- 查询了参加 数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数
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'  

4.5 分页和排序

===========分页 limit 和 排序 order by ===========

-- 排序： 升序 ASC ， 降序 DESC
-- order by 通过拿个字段排序，怎么排
-- 查询的结果根据 成绩 降序 排序

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` DESC

– 分页：最起码100万数据
– 为什么要分页
– 缓解数据压力，给人的体验更好，避免瀑布流

-- 分页，每一页显示五条数据
-- 语法：limit 起始值，页面大小
-- 网页应用： 当前，总的页数，页面的大小
-- limit 0,5       1~5
-- limit 1,5       2~6
-- limit 6.5
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` DESC
LIMIT 5,5

语法： limit（查询起始下标，pagesize）

-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECt s.`studentno`,`studentname`,`subjectname`,`studentresult` ¬¬--  注意符号和加别名
FROM `student` s
INNER JOIN `result` r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERe subjectname = 'Java程序设计-1' AND studentresult >= 80
ORDER BY studentresult DESC
LIMIT 0,5

4.6 子查询

Where （这个值是计算出来的）
本质：在where语句汇总嵌套一个子查询语句
Where（select * from）

-- 1. 查询数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
 -- 方式一：使用连接查询
 SELECt `studentno`,r.`subjectno`,`studentresult`
 FROM `result`r
 INNER JOIN `subject` sub
 ON r.subjectno = sub.subjectno
 WHERe subjectname = '数据库结构-1'
 ORDER BY studentresult DESC
 
 -- 2.方式二：使用子查询 （由内到外）
SELECt `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERe subjectno = (
	SELECt subjectno FROM `subject`
	WHERe subjectname = '数据库结构-1'	
)
 ORDER BY studentresult DESC


-- 分数不小于80分的学生的学号和姓名
SELECt DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERe `studentresult`>=80
-- 在以上基础增加一个科目，高等数学-2
-- 查询 高等数学-2 的编号
SELECt DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERe `studentresult`>=80 AND `subjectno` = (
	SELECt subjectno FROM `subject`
	WHERe `subjectname` = '高等数学-2'
)

-- 再改造
SELECt studentno,studentname FROM student WHERe studentno IN(
	SELECt studentno FROM result WHERe studentresult>80 AND subjectno = (
		SELECt subjectno FROM `subject`WHERe `subjectname` = '高等数学-2'
   )
)

4.7 分组过滤

SELECt subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 		HAVINg 平均分>80;

4.8 select小结

5. MySQL函数 5.1 常用函数

数学运算
SELECT ABS(-8)    -- 绝对值
SELECT CEILING(9.2)  -- 向上取整
SELECT FLOOR(9.2)   -- 向下取整
SELECT RAND()       -- 返回一个0-1之间的随机数
SELECT SIGN(-1)      -- 判断一个数的符号  负数返回-1 正数返回1

字符串函数
 SELECT CHAR_LENGTH('狂神说坚持就能成功'); 
 SELECT CONCAt('我','爱','程序');  
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  
 SELECT LOWER('KuangShen'); 
 SELECT UPPER('KuangShen'); 
 SELECT LEFt('hello,world',5);   
 SELECT RIGHt('hello,world',5);  
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  
 SELECT SUBSTr('狂神说坚持就能成功',4,6); 
 SELECT REVERSe('狂神说坚持就能成功'); 
 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() ）
 SELECT COUNT(studentname) FROM student; -- count(字段)，会忽略所有的null值
 SELECt COUNT(*) FROM student; -- count(*),不会忽略null值，本质是计算行数
 SELECt COUNT(1) FROM student; -- count(1),不会忽略null值，本质是计算行数

 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 

 SELECt SUM(StudentResult) AS 总和 FROM result;
 SELECt AVG(StudentResult) AS 平均分 FROM result;
 SELECt MAX(StudentResult) AS 最高分 FROM result;
 SELECt MIN(StudentResult) AS 最低分 FROM result;

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同课程分组）
SELECt subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,
MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVINg 平均分>80;

5.3 数据库级别的MD5加密

什么是MD5？
主要增强算法复杂度和不可逆性。
MD5不可逆，具体值的MD5是一样的
MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

测试MD5 加密：

CREATE TABLE `testMd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAr(20) NOT NULL,
	`pwd` VARCHAr(70) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 明文密码
INSERT INTO testMd5 VALUES(1,'wudi','123232'),(2,'zhangdi','123232'),(3,'wangdi','123232')

-- 加密
UPDATe testMd5 SET pwd=MD5(pwd) WHERe id = 1
UPDATE testMd5 SET pwd=MD5(pwd)-- 加密全部

-- 插入的时候加密
INSERT INTO testMd5 VALUES(4,'小迪',MD5('258099'))
-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT *FROM testmd5 WHERe `name` = '小迪' AND pwd=MD5('258099')