大一课余时间所学的MySQL，笔记为总结与摘抄
内容创建收集于2023年4月11日，21:18:41
1.初始MySQL
1.1 启动/关闭数据库
net start mysql80
net stop mysql80
1.2 连接数据库
命令行连接
mysql -u root -p -- 回车
Enter password: 123456
--------------------------------------------------------------------------------------------------------
show databases; -- 查看所有数据库
use 'databasesName' -- 切换并使用数据库
show tables; -- 查看数据库中所有的表
describe ‘tables’; -- 显示表里的所有信息
create database 'databasename'; -- 创建一个数据库
exit -- 退出连接
1.3 SQL通用语法
1. SQL语句可以单行或多行书写，以分号结尾
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
4.注释:
单行注释:-- 注释内容或#注释内容(MySQL特有)
多行注释: /*注释内容*/
1.4 SQL的分类
1. DDL
Data Definition Language
- 数据定义语言，用来定义数据库对象(数据库，表，字段)
2. DML
Data Manipulation Language
- 数据操作语言，用来对数据库表中的数据进行增删改
3. DQL
Data Query Language
- 数据查询语言，用来查询数据库中表的记录
4. DCL
Data Control Language
- 数据控制语言，用来创建数据库用户、控制数据库的访问权限
2.操作数据库
操作数据库——>操作数据库中的表——>操作表中数据
mysql不区分大小写
2.1 操作数据库
1. 创建数据库
create database [if not exists] name -- []可加可不加
2. 删除数据库
drop DATABASE [IF EXISTS] name
3. 使用数据库
use 'name' -- 如果表名或者字段名是一个特殊字符，就需要带符号'name' eg.'USE'
4. 查看数据库
show databases
2.2 数据库的列类型
1. 数值
| 类型 | 说明 | 字节数 |
|---|---|---|
| tinyint | 十分小的数据 | 1 |
| smallint | 较小的数据 | 2 |
| mediumint | 中等大小的数据 | 3 |
| int | 标准整型 | 4 |
| bigint | 较大的数据 | 8 |
| float | 浮点型 | 4 |
| double | 浮点型 | 8 |
| decimal | 字符串形的浮点数（金融计算常用） |
2. 字符串
| 类型 | 说明 | 范围 |
|---|---|---|
| char | 字符串固定大小 | 0~255 |
| varchar | 可变字符串（常用）’String‘ | 0~65535 |
| tinytext | 微型文本 | 2**8-1 |
| txet | 文本串（保存大文本） | 2**16-1 |
3. 时间日期
| 类型 | 格式 | eg |
|---|---|---|
| data | YYYY-MM-DD | 2023-4-12 |
| time | HH:mm:ss | 10：43：01 |
| datetime(常用) | YYYY-MM-DD HH:mm:ss | 2023-4-12 10：43：01 |
| timestamp(时间戳) | 1970.1.1到现在的毫秒数 | |
| year | 年份表示 |
4. null
- 没有值，未知
- 注意不要使用NULL运算，结果会为NULL
2.3 数据库的字段属性
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充
- 不足位数，使用0补充 eg: int(3) 输入：5 5—–>005
自增: (aoto_increment）
- 自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键~index
- 可以自定义设计主键自增的起始值和步长
非空：NULL、not null
- 设置not null ，不进行赋值报错
- 设置NULL，不赋值无影响，默认值为NULL
默认：
- 设置默认值
- 不指定某列的值时，就为默认值
2.4 创建数据库表
CREATE TABLE `demo001` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(255) DEFAULT NULL COMMENT '名字',
`password` varchar(255) NOT NULL COMMENT '密码',
`birthday` datetime DEFAULT NULL COMMENT '生日日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 引擎 字符集 整理
-- 格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释]，
`字段名` 列类型 [属性] [索引] [注释]，
……………………………………………………………………………………………
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集类型][注释]
常用命令
show create databases `basename` -- 查看创建数据库的语句
show create table `tablename` -- 查看创建数据表的定义语句
desc `tablename` -- 显示表结构
2.5 修改删除表
修改表
-- 修改表名 -- alter:改变
alter table old_name rename new_name
-- 增加表的字段
alter table 列名字 add 字段名 列属性[]
-- 修改表字段（重命名，修改约束）
alter table 列名字 modify 字段名 列属性[] -- 修改约束（列属性）
alter table 列名字 change old字段名 new字段名 列属性[] -- 修改字段名、也可以修改约束
-- 删除表字段
alter table 列名字 drof 字段名
删除表
drop table if exists tablename
所有创建删除操作尽量加上判断，以免报错
3.MySQL数据管理
3.1 外键 foreign key
方法一：13~14
CREATE TABLE `grade` (
`gradeidfather` int NOT NULL,
`gradename` varchar(255) DEFAULT NULL,
PRIMARY KEY (`gradeidfather`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `student` (
`id` int NOT NULL COMMENT '学生id',
`name` varchar(255) DEFAULT NULL COMMENT '学生姓名',
`gradeidson` int DEFAULT NULL COMMENT '所属年级',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeidson`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeidson`) REFERENCES `grade` (`gradeidfather`)
-- 约束 外键 引用
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
方法二：创建表成功后，添加外键约束
alter tables student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeidson`) REFERENCES `grade` (`gradeidfather`)
ADD CONSTRAINT 约束名 FOREIGN KEY 作为外键的列 REFERENCES 被引用的表名 被引用表的字段名
3.2 插入 insert ！
-- 插入单条数据
insert into `tablename`(字段名1,字段名2,.....) values(data1);
-- 插入多条数据
insert into `tablename`(字段名1,字段名2,.....) values(data1),(data2)................;
insert into `tablename` values(data1),(data2)................; -- 要求一一对应
3.3 修改 update ！
-- 修改单个
update `tablename` set 字段名 = `values` where [条件]
-- 修改多个
update `tablename` set 字段名 = `values`,[字段名 = 赋值],...... where [条件]
条件where： 操作符会返回布尔值
values内容可以是具体的值也可以是变量
| 操作符 | 含义 | 例子 | 结果 |
|---|---|---|---|
| = | 等于 | 4=5 | false |
| <>或！= | 不等于 | 4<>5 | true |
| > | |||
| < | |||
| >= | |||
| <= | |||
| BETWEEN…and… | 闭合区间（包含） | ||
| and | && | ||
| or | || |
3.4 删除 delete truncate ！
delete (不会重置自增)
delete from `tablename` (会全部删除)
delete from `tablename` where [] (删除指定数据)
truncate
truncate `tablename`
共同点：都能删除数据，都不会删除表结构
不同点：truncate 自增会归零 ，delete不会
4. DQL查询数据（最重点）
4.1 指定查询字段
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}];
-- 指定查询的记录从哪条至哪条
[]代表可选
{}代表必选
格式：select [字段名] from
TableName
select * from `TableName` -- 查询表中全部信息
select [字段名1],[字段名2]…… from `TableName` -- 查询指定信息
[字段名] as `OtherName“
[TableName] as `OtherName“
as 和飘号可以省略
select * from `TableName` as `别名`
函数 Concat（a,b）拼接字符串
select concat(`姓名 = `, StudentName) as `新名字` from student
-- 输出结果eg:
+---------+
| 新名字 |
+---------+
+ 姓名=张三+
去重distinct
发现重复数据就去重,只显示一条
select distinct [字段名] from `TableName`
数据库的列（表达式）
select 表达式 from tablename
select version() -- 查询MySQL版本 8.032
select 5-3 as 计算结果 -- 用来计算（表达式）
select @@aoto_increment_increment -- 查看自增步数（变量）
select `StudentNo`,`StudentResult` + 1 as '加一分后的成绩' from `TableName`
4.2 where条件字句
作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成，结果为布尔值
select [字段名],...... from `tablename` where [判断语句]
select [字段名],...... from `tablename` between [] and [] -- 模糊查询（效果同上）
模糊查询：比较运算符
| 运算符 | eg | 描述 |
|---|---|---|
| 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 | 如果a匹配b，结果为真 |
| IN | A IN (A1,A2,A3……) | 假设a在a1或a2….其中的某一个值，结果为真 |
| % | 只能在like里面用 | 代表0到任意一个字符 |
| _ | 只能在like里面用 | 代表一个字符 |
like结合
-- 查询姓D的同学 like结合
select `studentname` from `tablename` where `studentname` LIKE 'D%'
-- 查询名字里面有D的同学
select `studentname` from `tablename` where `studentname` LIKE '%D%'
-- 查询姓D的同学并且姓后面只有一个字
select `studentname` from `tablename` where `studentname` LIKE 'D_'
-- 查询查询姓D的同学并且姓后面只有两个字
select `studentname` from `tablename` where `studentname` LIKE 'D__'
in结合
-- 查询 1001，1002成员
select `studentname` from `tablename` where `studentno` in (1001,1002)
4.3 联表查询
join
思路：
1.分析需要，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两表中哪个数据是相同的）
判断条件：eg. s(student表).studentNo = r(result表).studenNo
左连接Left
select s.studentno,`studentname`,`subjectno`,`studentresult`
from student as s
left join result as r
Where/on s.studentno = r.studentno; -- on是连表前的条件，where是连表后的过滤
--输出结果
studentno studentname subjectno studentresult
--------- ----------- --------- ---------------
1000 张伟 5 58
1000 张伟 4 98
1000 张伟 3 68
1000 张伟 2 70
1000 张伟 1 85
1001 赵强 1 100
右连接right
select s.studentno,`studentname`,`subjectno`,`studentresult`
from student as s
right join result as r -- 连接外表
Where/on s.studentno = r.studentno; -- 判断条件
交集inner
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
嵌套查询
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno;
-- 输出结果
studentno studentname subjectname studentresult
--------- ----------- -------------- ---------------
1001 赵强 高等数学-1 100
1000 张伟 高等数学-4 98
1000 张伟 C语言-1 58
1000 张伟 高等数学-1 85
1000 张伟 高等数学-3 68
1000 张伟 高等数学-2 70
自连接
自己的表和自己的表连接，核心：一张表拆成两张一样的表
4.4 分页和排序
order by 必须在where语句下面，limit必须在order by 和 where语句下面
排序：升序ASC 降序DESC
order by [字段名] ASC/DESC
分页
格式：limit （n-1）*pageSize ,pageSize
pageSize：页面存储数据的个数
n:当前页面号
(n-1)*pageSize：起始值（相对于一个数组中元素对应的索引）
数据总数/pageSize=总页数
eg: limit 0,5 — 起始值数据为索引为0的值，单页面中数据总数为5
eg: limit 6,5 — 起始值数据为索引为6的值，单页面中数据总数为5
4.5 子查询
本质：where语句中嵌套select查询
select ...... from .........
inner join ........
on ........=........
where ....... and xxx=(
select.........from........where.......
)
5. MySQL函数
5.1 常用函数
-- 数学函数
SELECT ABS(-8) -- 绝对值 8
SELECT CEILING(9.4) -- 向上取整 10
SELECT FLOOR(9.4) -- 向下取整 9
SELECT RAND() -- 返回一个0~1的随机数
SELECT SIGN(0) -- 判断一个数的符号 0->0 -3->-1 2->1
-- 字符串函数
SELECT CHAR_LENGTH() -- 字符串长度
SELECT CONCAT('h','0') -- 拼接字符串 'h0'
SELECT INSERT('今天星期一',1,2,'明天') -- 查询，从第（1）处开始替换（2）个字符 ‘明天星期一’
SELECT LOWER('A') -- 小写字母 'a'
SELECT UPPER('a') -- 大写字母 'A'
SELECT INSTR('abc','c') -- 返回第一次出现字串的位置 3（从1开始）
SELECT replace('我是xxx','xxx','abc') -- 替换指定字符串‘我是abc’
SELECT substr(str,起始位置，截取数量[为空默认到底]) -- 查找指定闭区间子串
SELECT reverse() -- 反转字符串
-- 时间和日期函数
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()/user() -- 查询系统用户
SELECT version() -- 查询MySQL版本
5.2 聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
count()
-- *、1会记录NULL值，本质计数行数
SELECT COUNT(*) FROM [表名]
SELECT COUNT(1) FROM [表名]
SELECT COUNT([字段名]) FROM [表名] -- 不会记录NULL值
5.3 数据库级别的MD5加密
MD5不可逆，具体的值md5值一样
加密
update `tablename` set [字段名] = MD5(字段名)
-- 插入时加密
insert into `tablename` values(`value1`,.....,MD5(......))
如何校验
将用户传递进来的值进行MD5加密，然后对比加密后的值
select * from `tablename` where [字段名] = MD5(......)
6. 事务
6.1 事务的四大主要特性(ACID)
a. 原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。
比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。
b. 一致性（Consistency）
事务按照预期生效，数据的状态是预期的状态。
举例说明：张三向李四转100元，转账前和转账后的数据是正确的状态，这就叫一致性，如果出现张三转出100元，李四账号没有增加100元这就出现了数据错误，就没有达到一致性。
c. 隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
d. 持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
隔离导致出现的一些问题
(6条消息) 事务的四大特性、不考虑隔离性会产生的三个问题、如何解决事务的四个特性在项目中怎么解决X先生__的博客-CSDN博客
脏读：
指一个事务读取另一个事务未提交的数据
例子
小明的银行卡余额里有100元。现在他打算用手机点一个外卖饮料，需要付款10元。但是这个时候，他的女朋友看中了一件衣服95元，她正在使用小明的银行卡付款。于是小明在付款的时候，程序后台读取到他的余额只有5块钱了，根本不够10元，所以系统拒绝了他的交易，告诉余额不足。但是小明的女朋友最后因为密码错误，无法进行交易。小明非常郁闷，明明银行卡里还有100元，怎么会余额不足呢？（他女朋友更郁闷。。。）
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
例子
事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
虚读（幻读）:
是指一个事务内读取到了别的事务插入的数据，导致前后读取不一致
例子
事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
-- mysql是默认开启事务、自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启
-- 手动处理事务
set autocommit = 0 -- 关闭自动提交
-- 事务开始
start transaction -- 标记一个事务的开始，从这个之后的sql语句都在同一事务中
insert [语句]
insert [语句]
-- 提交：持久化（成功）
commit
-- 回滚：回到原来的样子（失败）
rollback
-- 事务结束
set autocommit = 1 -- 开启自动提交
-- 了解
savepoint [保存点名] -- 设置一个事务的保存点
rollback to savepoint [保存点名] -- 回滚保存点
release savepoint [保存点名] -- 撤销保存点
7. 索引
索引是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构
7.1 索引的分类
主键索引 (primary key)
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引 (unique key)
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 索引列的值必须唯一，但允许有空值
常规索引 (key/index)
- 默认的，用index、key关键字来设置
全文索引 (fulltext)
- 快速定位数据
-- 显示所有的索引信息
show inedx from `tablename`
-- 增加一个全文索引 【`索引名`,(列名)】
alter table `tablename` add fulltext index `索引名`(列名)
-- 给表字段添加索引
create index `indexname` on `tablename`([列名])
-- 删除索引
drop index [索引名] on `tablename`
-- 删除主键索引
alter table `tablename` drop primary key
-- explain[解释] 分析SQL语句执行性能
explain select * from `tablename`; -- 非全文索引
explain select * from `tablename` where match(`列名`) against('')
7.2 索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
8. 权限管理和备份
8.1 用户管理
a. 创建用户——赋予权限——登录密码
-- 创建用户
create user [用户名] identified by '密码'
-- 修改用户密码
ALTER USER "root"@"localhost" IDENTIFIED BY "新密码";
-- 重命名
rename user `用户` to `新用户名`
-- 用户授权
-- all privileges除了授权给其他人的特权（grant），其他都有
grant all privileges on *.* to `用户名` -- 全部权限 库.表
-- 查看权限
show grants for `用户名` -- 查看指定用户权限
show grants for "root"@"localhost" -- 查看root用户权限
-- 撤销权限 哪些权限，在哪个库，给谁撤销
revoke all privileges on *.* from `用户名`
-- 删除用户
drop user `用户名`
8.2 MySQL 备份
为什么要备份数据
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方式
- 使用命令行导出 mysqldump （cmd中)
- 数据库管理工具手动导出
- 直接拷贝数据库文件和相关配置文件
使用命令行导出 mysqldump （cmd中)
-- mysqldump -h[主机] -u[用户名] -p[密码] 数据库 表名1 表名2 表名3...... > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D:/name.sql
-- 导入
source d:/name.sql -- 登录的情况下，并且切换到指定数据库
mysql -u用户名 -p密码 库名 < 备份文件地址 -- 无登录情况下
9. 规范数据库设计
9.1 为什么需要数据库设计
当数据库比较复杂时我们需要设计数据库
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
9.2 三大范式
不合规范的表设计会导致的问题：
信息重复
更新异常
插入异常
- 无法正确表示信息
删除异常
- 丢失有效信息
Mysql – 什么是三大范式（通俗详解） – 知乎 (zhihu.com)
- 第一范式（1 NF）：字段不可再拆分。
- 第二范式（2 NF）：表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。
- 第三范式（3 NF）：在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取 某非主键字段 B。
第一范式 – 1NF
遵循原子性。即，表中字段的数据，不可以再拆分。
先看一个不符合第一范式的表结构，如下：
| 员工编码 | 姓名 | 年龄 |
|---|---|---|
| 001 | 销售部小张 | 28 |
| 002 | 运营部小黄 | 25 |
| 003 | 技术部小高 | 22 |
在这一个表中的，姓名 字段下的数据是可以再进行拆分的，因此它不符合第一范式，那怎么样才符合第一范式呢？如下：
| 员工编码 | 部门 | 姓名 | 年龄 |
|---|---|---|---|
| 001 | 销售部 | 小张 | 28 |
| 002 | 运营部 | 小黄 | 25 |
| 003 | 技术部 | 小高 | 22 |
那是否遵循第一范式就一定是好的呢？如下：
| 员工编码 | 姓名 | 地址 |
|---|---|---|
| 001 | 小张 | 江西省南昌市东湖区 |
| 002 | 小黄 | 广东省佛山市禅城区 |
| 003 | 小高 | 湖北省武汉市新洲区 |
通过观察上述表结构，我们发现，地址是可以再进一步拆分的，比如：
| 员工编码 | 姓名 | 省 | 市 | 区 |
|---|---|---|---|---|
| 001 | 小张 | 江西省 | 南昌市 | 东湖区 |
| 002 | 小黄 | 广东省 | 佛山市 | 禅城区 |
| 003 | 小高 | 湖北省 | 武汉市 | 新洲区 |
虽然拆分后，看上去更符合第一范式了，但是如果项目就只需要我们输出一个完整地址呢？那明显是表在没拆分的时候会更好用。
所以范式只是给了我们一个参考，我们更多的是要根据项目实际情况设计表结构。
第二范式 – 2NF
在满足第一范式的情况下，遵循唯一性，消除部分依赖。即，表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。
再通俗点讲就是，一个表只能描述一件事情。
我们用一个经典案例进行解析。
| 学号 | 姓名 | 年龄 | 课程名称 | 成绩 | 学分 |
|---|---|---|---|---|---|
| 001 | 小张 | 28 | 语文 | 90 | 3 |
| 001 | 小张 | 28 | 数学 | 90 | 2 |
| 002 | 小黄 | 25 | 语文 | 90 | 3 |
| 002 | 小黄 | 25 | 语文 | 90 | 3 |
| 003 | 小高 | 22 | 数学 | 90 | 2 |
我们先分析一下表结构。
- 假设学号是表中的唯一主键，那由学号就可以确定姓名和年龄了，但是却不能确定课程名称和成绩。
- 假设课程名称是表中的唯一主键，那由课程名称就可以确定学分了，但是却不能确定姓名、年龄和成绩。
- 虽然通过学号和课程名称的联合主键，可以确定除联合主键外的所有的非主键值，但是基于上述两个假设，也不符合第二范式的要求。
那我们应该如何调整表结构，让它能复合第二范式的要求呢？
我们可以基于上述的三种主键的可能，拆分成 3 张表，保证一张表只描述一件事情。
- 学生表 – 学号做主键
| 学号 | 姓名 | 年龄 |
|---|---|---|
| 001 | 小张 | 28 |
| 002 | 小黄 | 25 |
| 003 | 小高 | 22 |
- 课程表 – 课程名称做主键
| 课程名称 | 学分 |
|---|---|
| 语文 | 3 |
| 数学 | 2 |
- 成绩表 – 学号和课程名称做联合主键
| 学号 | 课程名称 | 成绩 |
|---|---|---|
| 001 | 语文 | 90 |
| 001 | 数学 | 90 |
| 002 | 语文 | 90 |
| 002 | 语文 | 90 |
| 003 | 数学 | 90 |
这时候我们可能会想，为什么我们就要遵循第二范式呢？不遵循第二范式会造成什么样的后果呢？
- 造成整表的数据冗余。
如，学生表，可能我就只有2个学生，每个学生都有许多的信息，比如，年龄、性别、身高、住址……如果与课程信息放到同一张表中，可能每个学生有3门课程，那数据总条数就会变成6条了。但是通过拆分，学生表我们只需要存储 2 条学生信息，课程表只需要存储 3 条课程信息，成绩表就只需保留学号、课程名称和成绩字段。
- 更新数据不方便。
假设，课程的学分发生了变更，那我们就需要把整表关于该课程的学分都要更新一次，但如果我们拆分出课程表，那我们就只需要把课程表中的课程信息更新就行。
- 插入数据不方便或产生异常。
① 假设主键是学号或课程名称，我们新增了某个课程，需要把数据插入到表中，这时，可能只有部分人有选修这门课程，那我们插入数据的时候还要规定给哪些人插入对应的课程信息，同时可能由于成绩还没有，我们需要对成绩置空，后续有成绩后还得重新更新一遍。
② 假设主键是学号和课程名称的联合主键。同样也是新增了某课程，但是暂时没有人选修这门课，缺少了学号主键字段数据，会导致课程信息无法插入。
第三范式 – 3NF
在满足第二范式的情况下，消除传递依赖。即，在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取 某非主键字段 B。
仍然用一个经典例子来解析
| 学号 | 姓名 | 班级 | 班主任 |
|---|---|---|---|
| 001 | 小黄 | 一年级（1）班 | 高老师 |
这个表中，学号是主键，它可以唯一确定姓名、班级、班主任，符合了第二范式，但是在非主键字段中，我们也可以通过班级推导出该班级的班主任，所以它是不符合第三范式的。
那怎么设计表结构，才是符合第三范式的呢？
- 学生表
| 学号 | 姓名 | 班级 |
|---|---|---|
| 001 | 小黄 | 一年级（1）班 |
- 班级表
| 班级 | 班主任 |
|---|---|
| 一年级（1）班 | 高老师 |
通过把班级与班主任的映射关系另外做成一张映射表，我们就成功地消除了表中的传递依赖了。
9.3 规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
10. JDBC（重点）
10.1 数据库驱动
10.2 JDBC
简化开发人员对数据库的统一操作，提供一个Java操作数据库的规范，俗称——JDBC（规范的实现由具体的厂商去做）
对于开发人员来说，我们只需要掌握JDBC接口的操作即可
java.sql
javax.ssql
还需要导入一个数据库驱动包
10.3 实操Java与MySQL
1. 将MySQL驱动添加至IDEA
IDEA导入MySQL的jdbc驱动，并操作数据库 – 打点 – 博客园 (cnblogs.com)
- 在IDEA里面创建一个java项目
- 选择创建Java项目，JDK这里选择1.8，直接Next
- 勾选按模板创建，Next
- 输入项目名称和项目地址，Finish
- 项目创建完成的界面如下
- 在项目中建一个名为lib（名称建议就用lib）的文件夹
- 对之前下载来的zip文件进行解压（mysql-connector-java-8.0.18.jar ），并进入解压出来的文件夹
- 复制其中的mysql-connector-java-8.0.18.jar文件，在lib文件夹上右键，粘贴到IDEA中，刚刚新建的lib文件夹里
让导入的驱动生效
- 在IDEA中点击 File——Project Structure
- 其中的 Module 模块，Dependencies 选项卡
- 点击最右边的加号（+），选择 JARS or directories
- 在弹出的窗口中选择刚刚导入 lib 文件夹的驱动，点击Ok
- 可以看到Module模块中，多出了一个mysql驱动，最后点击Apply，然后Ok
2. 实现JDBC程序
package com.aliyun;
import java.sql.*;
public class demo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动 通常不需要手动加载驱动程序类
// 2.用户信息和url（网址） 库名
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
//使用统一码 字符编码 MySQL8以上SSL协议为false 时区
String username = "root";
String password = "123456";
// 3.连接成功，数据库对象 Connection（连接）代表的是数据库
Connection connection = DriverManager.getConnection(url,username,password);
// 驱动程序管理器
// 4.执行sql的对象 Statement（声明） 执行的sql对象
Statement statement = connection.createStatement();
// 5. 执行sql的对象 去 执行sql,可能存在结果，查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集 中 封装了我们全部查询出来的结果
// 执行查询（这里的方法不唯一）
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("password="+resultSet.getObject("PASSWORD"));
}
//6.释放连接
resultSet.close();
connection.close();
statement.close();
}
}
步骤：
- 加载驱动
- 连接数据库
- 获取执行sql的对象
- 获得返回的结果集
- 释放连接
3. 分解知识点
URL
"jdbc:mysql://localhost:3306/jdbcstudy[useUnicode]=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
// 协议 ://主机地址：端口号/数据库名？参数1&参数2&参数3
Connection
代表数据库
// 事务的提交、回滚，数据库设置自动提交
connection.commit();
connection.rollback();
connection.setAutoCommit();
Statement 执行SQL的对象
String sql = "SELECT * FROM users"; // 编写SQL
statement.executeQuery(sql); //查询操作返回ResultSet
statement.execute(sql); //执行任何SQL
statement.executeUpdate(sql); //更新、插入、删除。都是用这个，返回一个受影响的行树
ResultSet 查询的结果集：封装了所有的查询结果
获得指定的数据类型
//在不知道列类型的时候
resultset.getObject();
//在知道列类型的时候
resultset.getInt();
resultset.getString();
resultset.getFloat();
resultset.getDouble();
resultset.get........
//遍历
resultSet.next(); // 移动到下一个数据
resultSet.beforeFirst(); // 移动到最前面
resultSet.afterLast(); // 移动到最后面
4. 通过配置文件，封装成方法调用
C:\Users\86188\IdeaProjects\jdbc 实例代码
【【狂神说Java】MySQL最新教程通俗易懂】https://www.bilibili.com/video/BV1NJ411J79W?p=40&vd_source=4bf637fc1e8a26c5ca8d7829888d0d52
封装方法
package com.aliyun.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class jdbcutils {
private static String dirver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try{
InputStream in = jdbcutils.class.getClassLoader().getResourceAsStream("db.properites");
Properties properties = new Properties();
properties.load(in);
dirver = properties.getProperty("dirver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1. 驱动只用加载一次
Class.forName(dirver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
调用方法
package com.aliyun.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class test {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = jdbcutils.getConnection();
statement = connection.createStatement();
String sql = ""; //sql语句填充
//根据增删改查，填充不同方法
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcutils.release(connection,statement,resultSet);
}
}
}
10.4 SQL注入
(6条消息) JDBC-Statement_小袁拒绝摆烂的博客-CSDN博客
实际工作一般用PreparedStatement来进行sql语句的执行，因为sql注入的风险
10.5 PreparedStatement
防止SQL注入（推荐使用PreparedStatement）
比较于Statement
PreparedStatement是先写SQL语句，并且将参数用问号？替代
将SQL语句进行预编译（不执行）
预编译后通过setObiect(Int、String……)等方法对 问好？进行赋值操作
注意：setObject()等方法的参数为 （index，obj） index 的数值由1开始依次往后递增
C:\Users\86188\IdeaProjects\jdbc\TEST02
package com.aliyun.utils;
import java.sql.*;
import java.util.Date;
public class TEST02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet re = null;
try {
conn = jdbcutils.getConnection();
String sql = "insert into users(id,`NAME`,`PASSWORD`,email,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);
st.setInt(1,4);
st.setString(2,"zzh");
st.setString(3,"123456");
st.setString(4,"1422833206@qq.com");
st.setDate(5,new java.sql.Date(new Date().getTime()));
// 这里将系统的时间戳转换为SQL语句中的时间戳（同时注意Date的导包由来）
int i = st.executeUpdate();
if(i>0){
System.out.println("t");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcutils.release(conn,st,re);
}
}
}
10.6 idea连接数据库
【【狂神说Java】MySQL最新教程通俗易懂】https://www.bilibili.com/video/BV1NJ411J79W?p=43&vd_source=4bf637fc1e8a26c5ca8d7829888d0d52
在idea中，我们可以：
- 编写sql语句
- 修改数据 （修改之后要记得点击DB键）
- 查看数据库
- ……….
10.7 事务
通过java完成事务
C:\Users\86188\IdeaProjects\jdbc\test03事务
步骤：
- 开启事务 conn.setAutoCommit(false);
- 一组业务之下完毕，提交事务 conn.commit();
- 可以在catch语句中 编写回滚语句conn.rollback();，默认失败回滚
package com.aliyun.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class test03事务 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet re = null;
try {
conn = jdbcutils.getConnection();
//关闭数据库的自动提交，这里会自动开启事务
conn.setAutoCommit(false);
String sql1 = "UPDATE account set money = money-500 where NAME = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
int s = 1/0; //失败
String sql2 = "UPDATE account set money = money+500 where NAME = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕，提交事务
conn.commit();
System.out.println("成功");
} catch (SQLException throwables) {
//如果失败，默认回滚（下列注释代码可写可不写）
/*try {
conn.rollback(); // 如果失败回滚
} catch (SQLException e) {
e.printStackTrace();
}*/
throwables.printStackTrace();
}finally {
jdbcutils.release(conn,st,re);
}
}
}
