文章目录
数据库笔记
mysql安装后出现的一些问题
mysql启动地点：
cd /usr/local/mysql/bin
重置密码：
Mysql8.0版本以后Mysql语句有更新废弃了password字段和password()函数,使用下面的语句设置:
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassword';
5.7版本以前:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
在终端输入:FLUSH PRIVILEGES;
exit退出mysql;
安装全流程：
https://www.jianshu.com/p/07a9826898c0
1. 初识MySql
show databases; -- 查看所有数据库
use school; --切换数据库
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有表的信息
create database westos; --创建一个数据库
exit; --退出连接
DDL(数据库定义语言)、DML(数据库操作语言)、DQL(数据库查询语言)、DCL(数据库控制语言)
2. 操作数据库
2.1、操作数据库(了解)
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
2.删除数据库
DROP DATABASE [IF EXISTS] westos;
3.使用数据库
-- tab 键的上面这个，叫飘~
-- 如果你的表名或者字段名是一个特殊字符，就需要带``
UES `school`;
SELECT `user` FROM student; --因为user是系统自带的一个字段，但如果我的表名里有一个字段也叫user，就需要用飘来括起来，否则有高亮影响观感。
4.查看数据库
SHOW DATABASES;
2.2、数据库的列类型
数值
Tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的数据 4个字节
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal
字符串
char 字符串固定大小的 0~255
varchar 可变字符串 0~65535 对应Java常用的String
tinytext 微型文本 127
text 文本串 2^16-1
char与varchar的区别
可变字符串指的是，物理空间的可变，比如都设置为大小为5，那么char类型一定开辟5个空间，而varchar根据实际使用的空间大小来开辟，但是不论char还是varchar，都不能超过设定的大小，意味着5个空间大小为最大值。
char速度快，但是可能浪费空间，并产生碎片空间
varchar速度慢，但不会浪费空间，不会产生碎片空间
时间日期
java.util.Date
·date YYYY-MM-DD 日期格式
·time HH: mm: ss 时间格式
·datatime YYYY-MM-DD HH: mm: ss 最常用的时间格式
·timestamp 时间戳 1970.1.1到现在的毫秒数！也较为常用
·year 年份表示
null
·没有值，未知
·注意，不要使用NULL进行运算，结果为NULL
2.3、数据库的字段属性
Unsigned
·无符号的整数
·声明了该列不能声明为负数
zerofill
·0填充的
·不足的位数，使用0来填充，int（3）， 5 ----> 005
自增
·通常理解为自增，自动在上一条记录的基础上+1（默认）
·通常用来设计唯一的主键 ~index，必须是整数类型
·可以自定义设计主键的起始值和步长
非空 NULL ，not null
·假设设置为 not null ，如果不给它赋值，就会报错！
·NULL，如果不填写值，默认就是null！
默认
·设置默认的值！
·sex，默认为男，如果不指定该列的值，则会使用默认的值
2.4、新建数据库表（重点）
-- 字符串使用单引号括起来！
-- AUTO_INCREMENT 自增
-- 所有的语句后面加 , （英文的逗号） ，最后一个不用加
-- comment 注释
-- 一个表有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) Not NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名' ,
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(20) not null default '女' comment '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
-- 设置主键
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE `School`; -- 查看创建数据库的语句
SHOW CREATE TABLE student; -- 查看student数据表的定义语句
DESC student; -- 显示表的结构
2.5、数据表的类型
关于数据库引擎：
INNODB 默认使用
MYISAM 早些年使用
| MYISAM | INNODB | |
|---|---|---|
| 事物支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 （表锁） | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为2倍 |
常规使用操作：
·MYISAM 节约空间，速度较快
·INNODB 安全性高，事物的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在与 /usr/local/mysql/data目录下，一个文件夹对应一个数据库
本质还是文件的存储！
MySql引擎在物理文件上的区别
·INNOBD 在数据库表中只有一个 *.frm文件，以及上级目录下的ibdata1文件
·MYISAM 对应的文件：
*.frm - 表结构的定义文件
*.MYD - 数据文件（data）
*.MYI - 索引文件（index）
设置数据库表的字符集编码
CHARSET=utf8
不设置的话，会是mysql默认的字符集编码 [Latin1]（ 不支持中文！）
在my.ini中可以配置默认的代码，但不建议这么做，因为换电脑就不支持了
character-set-server=utf8
2.6、修改删除表
修改
-- 修改表
ALTER TABLE 旧表名 RENAME AS 新表名
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE 表名 ADD age INT(11)
-- 修改表的字段（重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher CHANGE age age1 INT(1) -- 字段重命名
最终结论：
change用来字段重命名，不能修改字段类型和约束
modify不用来字段重命名，只能修改字段类型和约束
删除
-- 删除表的字段
ALTER TABLE teacher DROP age
-- 删除表
DROP TABLE [IF EXISTS] teacher
所有的创建和删除操作尽量加上判断，以免报错
注意点：
· 字段名，使用飘来包裹
· 注释 – 或者/**/
· sql关键字大小写不敏感，建议写小写
· 所有的符号全部用英文！
3. Mysql数据管理
3.1、外键（了解）
方式一：在创建表的时候，增加约束
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) Not NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名' ,
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(20) not null default '女' comment '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
-- 设置主键
PRIMARY KEY (`id`),
-- 设置外键 , 分两步
-- 第一步，定义外键key
-- 第二步，给这个外键添加约束（执行引用）
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）
方式二：创建表的时候不添加约束
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)
以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）
最佳实践
·数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）·
·我们想使用多张表的数据，想使用外键（程序去实现）
3.2、DML语言（全部记住）
DML:数据库管理操作语言
数据库的意义：数据存储，数据管理
·insert
·update
·delete
3.3、添加
-- 插入语句（添加）
-- insert into 表名([字段1,字段2,字段3])values('值1','值2','值3'),('值1','值2','值3'),... ;
-- 插入多个字段
insert into grade(gradename) VALUES('大一'),('大二');
注意事项：
1.写插入语言，一定要数据和字段一一对应！
2.字段是可以省略的
3.可以同时插入多条数据，values后面的值，需要使用英文逗号来一一隔开
3.4、修改
-- 修改学员的名字
update `student` set `name`='沈航冉' where id = 1;
-- 语法：
-- update 表名 set 列名 = 值 where 条件
-- 修改多个属性，逗号隔开
update `student` set `name`='沈航冉',`email`='420310767@qq.com' where id = 1;
-- 语法：
-- update 表名 set 列名 = 值[,列名 = 值,...] where 条件
条件：where字句 运算符
操作符会返回布尔值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 2<>3 | True |
| > | 大于 | ||
| < | 小于 | ||
| >= | 大于等于 | ||
| <= | 小于等于 | ||
| BETWEEN…AND… | 在某个范围内的闭合区间 | [2,5] | |
| AND | 我和你 && | 5>1 AND 1>2 | false |
| OR | 我或你 || | 5>1 OR 1>2 | True |
注意事项：
·条件，是筛选条件，如果没有指定，则会修改所有的列
·值，可以是一个具体的值，也可以是一个变量（在时间变量中常见，如CURRENT_TIME）
·多个设置的属性之间，用英文逗号隔开
3.5、删除
delete命令
-- 语法：
-- delete from 表名 [where 条件]
TRUNCATE命令
作用：完全清空一个数据表，表的结构和索引约束不会变！
-- 语法：
-- truncate TABLE 表名
delete和truncate的区别
·相同点：都可以删除数据，都不会删除表结构
·不同点：
–TRUNCATE 重新设置自增列，计数器会归零
–TRUNCATE 不会影响事物
– DELETE删除的问题：当重启数据库的时候，如果数据库的类型是
----> InnoDB 自增量会从1开始（存在内存当中，断电即失）
----> MyISAM 继续从上一个自增量开始 （存在文件中，不会丢失）
4. DQL查询数据(最重点)
4.1、DQL
Data Query Language ： 数据查询语言
- 所有的查询操作都用它 Select
- 简单的查询，复杂的查询它都能做~
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
4.2、指定查询字段
-- 查询全部
SELECT * FROM student;
-- 查询指定字段
SELECT `StudentNo` , `StudentName` From student;
-- 使用别名，让查询结果的列名看起来好看
SELECT `StudentNo` AS 学号, `StudentName` AS 学生姓名 From student; -- 注意，这里的中文不需要加引号
-- AS用来起别名，AS可以给字段起别名，也可以给表起别名。
SELECT * FROM student AS stu;
-- 函数 Concat(a,b) 拼接a和b
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student -- 结果样式见下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-48nfwIIj-1618425022128)(/Users/shenhangran/Desktop/学习笔记/我的/数据库.assets/image-20210410154822461.png)]
CONCAT函数使得结果的每一项前面都被拼接了 “姓名：”字样。
有的时候列的名字或者表的名字不是那么的见名知意，我们可以使用AS起别名。
去重 distinct
-- 查询有哪些同学参加了考试
SELECT * FROM result; --查询全部的考试成绩
SELECT StudentNo FROM result; -- 查询有哪些学生参与了考试
-- 发现重复数据，因此要去重
SELECT DISTINCT StudentNo From result; -- 成功查询有哪些学生参与了考试
distinct作用： 将查询出的重复的数据去除，只显示一条。
-- 查询系统版本（函数）
SELECT VERSION();
-- SELECT可以用来计算（表达式）
SELECT 100*3-1 AS 计算结果;
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment
-- 学院考试成绩全部+1分以后再查看（真实数据不变）
SELECT StudentNo , `StudentResult`+1 AS '提分后' From result;
数据库中的表达式： 文本值，列，Null，函数，计算表达式，系统变量…
select 表达式 from 表
4.3、where条件字句
作用：检索数据中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or b a||b | 逻辑或 |
| Not ! | Not a !a | 逻辑非 |
尽量使用英文字母
搜索的条件由一个或多个表达式组成，结果是布尔值
模糊查询：比较运算符（重点）
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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，则结果为真。 |
| – like结合 % 代表0到任意个字符 _(一个字符) | ||
| IN | a in (a1,a2,a3,…) | 假设a在a1，或者在a2…其中的某一个值中，结果为真 |
| – IN括号里面，必须是具体的值，不能使用%和_ |
-- 查询姓刘的同学
SELECT StudentName ,StudentNo FROM student where StudentName Like '刘%';
-- 查询姓刘的同学，且名字后面只有一个字的
SELECT StudentName ,StudentNo FROM student where StudentName Like '刘_';
-- 查询姓刘的同学，且名字后面只有两个字的
SELECT StudentName ,StudentNo FROM student where StudentName Like '刘__';
-- 查询名字中间有嘉字的同学
SELECT StudentName ,StudentNo FROM student where StudentName Like '%嘉%';
-- 查询1001，1002，1003号学员
SELECT STUDENTNAME,STUDENTNO FROM STUDENT WHERE STUDENTNO IN(1001,1002,1003);
-- 查询地址为空的学生
SELECT StudenName from student where address='' OR address IS NULL;
4.4、联表查询
JOIN 对比
-- 联表查询 join
/*
思路：
1.分析需求，要查的东西分别来自哪张表
2.确定使用哪种连接查询？ 7种(本质就3种)
-确定交叉点（两个表中哪个数据是相同的）
-判断的条件： 学生表中的StudentNo = 成绩表中的StudentNo
*/
-- InnerJoin
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s -- AS可以省略不写
INNER JOIN result AS r
ON s.studentNo=r.StudentNo -- ON可以换成Where
/*
join（连接的表） on（判断的条件） 连接查询
where 等值查询
*/
-- RightJoin 将右边的表作为主表
-- LeftJoin 将左边的表作为主表
/*
理解： 如果一个学号在主表里有，在成绩表里没有，那么查询到的结果是可以查到这个学生，但是成绩列全部为null
反之，如果在从表里有，在主表里没有这个学号，则压根搜索不到这个学生，但是它在成绩表里是有成绩的。
*/
| 操作 | 描述 |
|---|---|
| InnerJoin | 如果表中至少有一个匹配，就返回行 |
| LeftJoin | 会从左表中返回所有的值，即使右表中没有匹配 |
| RightJoin | 会从右表中返回所有的值，即使左表中没有匹配 |
-- 查询缺考的同学
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNo=r.studentNo
WHERE StudentResult is NULL;
-- FROM 表 join 连接的表 on 交叉条件
-- 假设存在一种多表查询，慢慢来，先查询两张表然后再慢慢增加 ↓
多个表也可以join，即先两个表join之后，再加一个join
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可（想象用一个数组来存储一棵树）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BSQTnryD-1618425022131)(/Users/shenhangran/Desktop/学习笔记/我的/数据库.assets/image-20210411010127126.png)]
父类
| categoryId | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryId | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | 美术设计 |
操作：查询父类对应的子类关系
结果为：
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
sql语句如下：
-- 查询父子信息:把一张表看为两张一模一样的表
SELECT categoryName as '父栏目', categoryName as '子栏目'
FROM category as A , category as B
where a.categoryId = b.pid
4.5、分页[limit]和排序[order by]
排序：升序 ASC 降序 DESC
-- 通过哪个字段排序，怎么排
ORDER BY StudentResult ASC
分页
-- 为什么要分页？
-- 缓解数据库压力，给用户更好体验。 （图片、视频网站一般用 瀑布流 ）
-- 分页 每页只显示五条数据
-- 语法：limit 起始值,页面的大小
LIMIT 0,5 -- 从第一个值开始显示，每页显示5个
LIMIT是唯一一个下标从0开始计算的，其他都是从1开始计算的
4.6、子查询
where （这个值是计算出来的，一个具体的值）
子查询本质：在where语句中嵌套一个子查询语句
大概长这样： where(select … from …)
-- 查询数据结构的所有考试结果（学生姓名，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT StudentName,SubjectName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN subject sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubjectName = '数据结构'
ORDER BY StudentResult DESC
-- 方式二：使用子查询(由里及外)
SELECT StudentName,SubjectNo,StudentResult
FROM result r
INNER JOIN student s
ON s.StudentNo = r.StudentNo
WHERE SubjectNo = (
SELECT SubjectNo FROM subject WHERE SubjectName='数据结构'
)
ORDER BY StudentResult DESC
-- 查询"高等数学"分数不小于80分的学生的学号和姓名
SELECT 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 = '高等数学'
) -- 因为不能直接取到SubjectName，但是可以取到SubjectNo
4.7、分组和过滤
GROUP BY 用来分组，分组之后的条件不用WHERE，而用HAVING，详见5.2聚合函数
5. MySql函数
5.1、常用函数
-- 常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(8.2) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机整数
SELECT SIGN(-10) -- 判断一个数的符号 0返回0 负数返回-1，正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 查询 替换 从某个位置开始替换某个长度
-- 上面这句话中，把“我爱” 替换为了“超级热爱”
SELECT LOWER('KUangshen') -- 全部转换为小写
SELECT UPPER('kuangshen') -- 全部转换为大写
SELECT INSTR('kuangshen','he') -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('我说过我很爱你',4,2) -- 返回指定的子字符串 从a位置开始，截取b长度，如果不写b，则返回剩下的全部
SELECT SUBSTRING() -- 同上
SELECT REVERSE('abcdefg') -- 反转
-- 查询姓周的同学，把周改为邹
SELECT REPLACE(studentname,'周','邹')
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() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
-- 聚合函数
-- COUNT 能够统计表中的数据（想查询一个表中有多少个记录，就会使用count()）
SELECT COUNT(studentname) FROM student; -- COUNT(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- COUNT(*)，不会忽略null值，本质 计算行数 -- 要走一遍所有的列
SELECT COUNT(1) FROM student; -- COUNT(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
-- 查询不同课程的平均分，最高分，最低分，且平均分大于80分
-- 核心：（根据不同的课程分组）
-- GROUP BY 用来分组
-- HAVING 分组以后的记录必须满足的次要条件，比如这里平均分大于80不能使用WHERE
-- 因为GROUP BY 在WHERE后面，如果使用where则会报错，因为平均值还没分组计算得到呢！
SELECT SubjectName,AVG(StudentResult) as 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组，如果不分组，会把所有不同的课程的成绩加载一起求平均，毫无意义
HAVING 平均分 > 80
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 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候就加密，而不是像上面一样插入以后再加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('234212'))
-- 如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
-- 下面这段代码是在java里写的sql
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd=MD5('234212');
6. 事务
6.1、什么是事务？
要么都成功，要么都失败
将一组SQL放在一个批次中去执行（典型案例：A给B转账，A扣了钱，B收到钱，两个动作必须都完成，不能只完成一个动作，这就是原子性；不管怎么转账，钱的总量不变，这就是一致性）
出现突发情况时，事务没有提交，则会复原到原状；事务已经提交，则会持久化到数据库。即事务一旦提交，则不可逆，这就是持久性
A给B转钱的同时，不影响B给C转钱，这就是隔离性
事物原则： ACID原则 原子性，一致性，隔离性，持久性
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性保证一直
持久性（Durability）
事务一旦提交则不可逆，被持久化到数据库中！
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事物，不能被其他事物的操作数据所干扰，多个事物之间要相互隔离
隔离所导致的一些问题
脏读
指一个事务读取了另外一个事务未提交的数据。
不可重复读
在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误，只是某些场合不对）
虚读（幻读）
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
幻读与不可重复读类似，但是幻读强调的是【集合】，而不可重复读是【单独一条数据】
执行事务
-- mysql默认开启事务自动提交
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启，默认的*/
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交： 持久化（成功！）
COMMIT
-- 回滚： 回到原来的样子！（失败！）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
转账案例
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;
Create TABLE `account`(
`id` int(3) not null auto_increment,
`name` varchar(30) not null,
`money` decimal(9,2) not null,
primary key(`id`)
)engine = INNODB DEFAULT CHARSET=utf8;
INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',1000.00);
--模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务
UPDATE account SET money=money-500 WHERE `name` = 'A'; -- A减少500块钱
UPDATE account SET money=money+500 WHERE `name` = 'B';
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
7. 索引
Mysql官方对索引的定义为：
索引（index）是帮助MySql高效获取数据的数据结构。即.索引是数据结构。
7.1、索引的分类
主键索引唯一，唯一索引不唯一
- 主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复（多个列都可以标识为唯一索引）
- 常规索引（KEY或KEYINDEX）
- 默认的，index，key关键字来设置
- 全文索引（FULLTEXT）
- 在特定的数据库引擎下才有，MyISAM（最新版本的INNODB也支持全文索引了）
- 快速定位数据
-- 索引的使用
-- 1、在创建表的时候增加索引
-- 2、创建完毕后使用ALTER增加索引
-- 3、CREATE INDEX 索引名 on 表(字段)
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引
ALTER TABLE student ADD FULLTEXT INDEX `studentName`(`studentname`); -- 第一个名字是索引的名字，括号里的名字是这个索引要对应哪一列的列名
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张'); -- 全文索引
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vjmBQgAL-1618425022132)(/Users/shenhangran/Desktop/学习笔记/我的/数据库.assets/image-20210412170613783.png)]
全文检索语法如上，注意MATCH括号里，必须包含所有已定义的（已建立的）全文索引的字段。
7.2、测试索引
-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,password`,age`)
VALUES(CONCAT('用户',i),'aaa@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
-- 使用函数
SELECT mock_data();
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 执行这一条语句就耗时超过1秒了！
-- 尝试分析原因
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; --通过分析可知，实际查找了991749行
创建索引：
-- 起名规范：id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`);
创建索引需要时间（创建了一棵树）
创建索引以后再执行下面的语句：
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 仅耗时0.001秒
-- 尝试分析原因
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; --通过分析可知，实际查找了1行
实际查找1行，意味着直接定位
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上，提高查询速率！
索引的数据结构
Hash类型的索引
Btree：INNODB的默认数据结构
阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8.权限管理和数据库备份
8.1、用户管理
SQL命令操作
用户表：mysql.user
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('1111111')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen = PASSWORD('123456')
-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2
-- 用户授权 ALL PRIVILEGES 全部的权限， 库.表 *.* 代表该用户对所有库的所有表都有最高权限
-- ALL PRIVILEGES唯一没有的权限就是 GRANT 即它不能给别人授权，只有root可以给别人授权
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看管理员权限，管理员后面必须跟【@主机名】
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* TO kuangshen2
--删除用户
DROP USER kuangshen2
8.2、MySql备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式：
-
直接拷贝物理文件
-
在可视化工具中手动导出
-
使用命令行导出 mysqldump 命令行中使用
# mysqldump -h 主机名 -u 用户名 -p 密码 数据库 [表名1,表名2,...] >物理磁盘位置/文件名 >mysqldump -h localhost -u root -p jiawensili.1029 school student >D:/a.sql # 命令行导入数据库 >mysql mysql> use shcool; mysql> source d:/a.sql; # 登录的情况下，切换到指定的数据库后，指定source 备份文件 # 没有登录的情况下，语句为： >mysql -u 用户名 -p 密码 库名< 备份文件
9.规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候，就需要设计了
糟糕的数据库设计：
-
数据冗余，浪费空间
-
数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
-
程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们系统开发
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
-
收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段）key：value
-
标识实体类（把需求落地到每个字段）
-
标识实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注： user --> user
- 友链：links
- 评论：user – user – blog
9.2、三大范式
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
·无法正常显示信息
-
删除异常
·丢失有效的信息
三大范式
第一范式 1NF （表述清楚列）
原子性：保证每一列不可再分
第二范式 2NF （表述清楚表）
前提：满足第一范式
第二范式需要确保数据库表中的每一列都和主键有关，而不能只和主键的某一部分相关。
即：每张表只描述一件事情
第三范式 3NF （消除依赖）
前提：满足第一范式和第二范式
在2NF基础上，任何非主属性不依赖于其他非主属性。（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
（规范数据库的设计）
规范化和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 有时候会故意给某些表增加一些冗余的字段，从而使得多表查询变为单表查询（空间换时间）
- 故意增加一些计算列（从大数据量降低为小数据量的查询）
10. JDBC(重点)
10.1、数据库驱动
驱动：声卡、显卡、数据库 —> 数据库也是有驱动的
10.2、JDBC
SUN 公司为了简化开发人员的统一操作，提供了一个Java操作数据库的规范，俗称JDBC
这些规范的实现由具体的厂商去做~对于开发人员来说，我们只需要掌握JDBC即可
本质：没有什么是加一层不能解决的。
java.sql
javax.sql
还需要导入一个数据库驱动包（一般通过Maven去找）
10.3、第一个JDBC程序
import java.sql.*;
public class jdbcFirstTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动
//2.用户信息和url
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "jiawensili1029";
//3.连接成功，数据库对象,Connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象 Statement
Statement statement = connection.createStatement();
//5.执行SQL的对象 去 执行SQL 可能存在结果，查看返回结果
String sql = "SELECT * FROM student";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("studentno"));
System.out.println("loginpwd="+resultSet.getObject("loginpwd"));
System.out.println("studentname="+resultSet.getObject("studentname"));
System.out.println("sex="+resultSet.getObject("sex"));
System.out.println("gradeid="+resultSet.getObject("gradeid"));
System.out.println("phone="+resultSet.getObject("phone"));
System.out.println("address="+resultSet.getObject("address"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
Statement执行SQL的对象 PrepareStatement执行SQL的对象
statement.execute();//执行任何SQL
statement.executeQuery();//执行以后，返回ResultSet
statement.executeUpdate();//更新、插入、删除。返回一个受影响的行数。
ResultSet 遍历，指针，定位
resultSet.beforeFirst();// 移动到最前面
resultSet.afterLast();// 移动到最后面
resultSet.next(); // 移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row)；// 移动到指定行
可以把加载驱动，获得链接什么的，放到一个utils类里，避免代码冗余
10.4、PreparedStatement对象
PreparedStatement可以防止 SQL注入，效率更好！
1、新增
2、删除
3、更新
4、查找
例：
//区别：使用？占位符代替参数
String SQL = "INSERT INTO subject(`subjectno`,`subjectname`,`classhour`,`gradeid`) values(?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(SQL); //预编译SQL，先写SQL， 但不执行
//手动给参数赋值
preparedStatement.setInt(1,20); // 1代表上面第一个问号
preparedStatement.setString(2,"数据结构");
preparedStatement.setInt(3,120);
preparedStatement.setInt(4,2);
//执行预编译好的sql
preparedStatement.executeUpdate();
PrepareStatement防止SQL注入的本质：
把传递进来的参数当做字符。
假设其中存在转义字符，就直接忽略。比如说 引号’ ’ 会被直接转义
10.5、Java里实现事务
try{
conn.setAutoCommit(false); // 关闭自动提交意味着开启事务
//....使用多条preparedStatement的sql语句
conn.commit();
}catch{
//因为任何原因导致多条语句未能全部执行完，比如中间出现了1/0
//下面可以不写，默认失败回滚
try{
conn.rollback(); //如果失败则回滚事务
}catch(SQLException e1){
e1.printStackTrace();
}
e.printStackTrace();
}finally{
jdbcUtils.release(conn,statement,result); //调用utils公共包里的释放资源的函数
}
10.6、数据库连接池
数据库连接 — 执行完毕 — 释放
连接 – 释放 十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
编写连接池，只需要实现一个接口 DataSource（相当于公共类变成实现接口的类了）
开源数据源实现
DBCP
C3P0
Druid：阿里巴巴
使用了这些数据库连接池后，我们在项目开发中就不需要编写连接数据库的代码了！效率大幅提升了！
DBCP
需要用到的jar包
Commons-dbcp-1.4 、 commons-pool-1.6
C3P0
C3P0可以采用xml进行配置，也可以在代码里配。xml配置简单。
需要用到的jar包
C3p0-0.9.5.5 、 mchange-
结论
无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变。
228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
