MySQL数据管理,DQL数据查询,函数,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时都必须考虑外键约束,会导致开发的时候很痛苦,测试数据极为不便
== 最佳实践:==
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现,用方式二)
3.2 DML语言(全部记住)
数据库意义: 数据存储,数据管理
- insert
- update
- 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:数据查询语言
- 所有查询操作都用它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}];
-- 指定查询的记录从哪条至哪条
注意 : [ ] 括号代表可选的 , { }括号代表必选得
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 联表查询
先看思路:
- 分析需求:确定查询的列来自哪些表,student,result,连接查询
- 确定使用哪种连接查询? 7种
- 确定交叉点(这两个表中的哪个数据是相同的)
- 判断条件:学生表中的 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 REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname 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() )
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 的记录 。
/*
很多人认为count(1)执行的效率会比count(*)高,原因是count(*)会存在全表扫描,而count(1)可以针对一个字段进行查询。其实不然,count(1)和count(*)都会对全表进行扫描,统计所有记录的条数,包括那些为null的记录,因此,它们的效率可以说是相差无几。而count(字段)则与前两者不同,它会统计该字段不为null的记录条数。
下面它们之间的一些对比:
1)在表没有主键时,count(1)比count(*)快
2)有主键时,主键作为计算条件,count(主键)效率最高;
3)若表格只有一个字段,则count(*)效率较高。
*/
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')