MySQL笔记
@Auther：纸箱里的猫
@Description：MySQL自用学习笔记
@Last Updated Date：2021.7.31
@Learning Completion Date：?
@Writing The Software：Typora
待补充
目录
文章目录
1. 初识MySQL
JavaEE：企业级Java开发 web
前端（页面：展示，数据！）
后台（连接点：连接数据库JDBC，链接前端（控制，控制视图跳转，给前端传递数据））
数据库（存数据，txt）
1.1 为什么学习数据库
1.岗位需求
2.现在的世界，是大数据时代，得数据库者得天下
3.被迫需求：存数据
4.数据库是所有软件体系中最核心的存在 DBA
1.2 什么是数据库
数据库（DB，DataBase）
概念：数据仓库，存储数据，软件，安装在操作系统之上。可以存储大量的数据。500万
作用：存数据，管理数据
1.3 数据库分类
关系型数据库：
（SQL） 行、列
- MySQL，Oracle，Sql Server，DB2，SQLite
- 通过表和表之间，行和列之间的关系进行数据的存储， 学员信息表，考勤表…
- 关系型数据库通过外键关联来建立表与表之间的关系
非关系型数据库：
（No SQL）Not Only SQL {key：value}
-
Redis，MongDB
-
非关系型数据库，对象存储，通过对象自身的属性来决定。
-
非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
什么是DBMS
数据库管理系统（DataBase Management System）
- 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据
- MySQL，本质是一个数据库管理系统
1.4 MySQL简介
MySQL是一个关系型数据库管理系统
前世：瑞典MySQL AB公司开发
今生：属于 Oracle 旗下产品
MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一。
开源的数据库软件
体积小、速度快、总体拥有成本低，招人成本比较低，所有人必须会
中小型网站，或者大型网站，集群
官网：https://www.mysql.com/
安装建议：
1.尽量不要使用exe，会产生注册表，不好卸载
2.尽可能使用压缩包安装
1.5 安装MySQL
1.6 安装SQLyog
创建库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nZHuMuwn-1628643646423)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210721095640293.png)]
每一个SQLyog的执行操作1，本质就是对应了一个SQL，可以在软件的历史记录中查看
创建表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NhUoATFa-1628643646425)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210721100422551.png)]
查看表
1.7 命令行连接数据库
命令行连接：
在DOS命令行窗口进入 安装目录\mysql\bin
C:\Users\10512>cd /d F:\MySQL\MySQL Server 8.0\bin
也可设置环境变量，设置了环境变量，可以在任意目录打开！
连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !
F:\MySQL\MySQL Server 8.0\bin>mysql -uroot -p12345
1.8 几个基本的数据库操作命令
mysql -uroot -p12345 -- 连接数据库
--所有的语句都要使用;结尾
update user set password=password('12345')where user='root'; -- 修改密码
flush privileges; -- 刷新数据库
show databases; -- 显示所有数据库
use dbname；-- 打开某个数据库
show tables; -- 显示数据库mysql中所有的表
describe user; -- 显示表mysql数据库中user表的列信息
create database name; -- 创建数据库
use databasename; -- 选择数据库
exit; -- 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GSw95i6d-1628643646426)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210721110050625.png)]
数据库xxx语言 CRUD增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
2. 操作数据库
操作数据库->操作数据库中的表->操作数据库中表的数据
MySQL关键字不区分大小写
2.1 操作数据库（了解即可）
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] westos
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iOzYSbyr-1628643646427)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210727163925587.png)]
2.删除数据库
DROP DATABASE [IF EXISTS[] westos
3.使用数据库
-- 如果你的表名或者字段名是一个特殊字符，就要带` `
USE `school`
4.查看数据库
SHOW DATABASES -- 查看所有的数据库
对比：SQLyog的可视化操作
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z5M1QjrA-1628643646428)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210721113150428.png)]
2.2 数据库的列类型
数值
| 数据类型 | 描述 | 大小 |
|---|---|---|
| tinyint | 十分小的数据 | 1个字节 |
| smallint | 较小的数据 | 2个字节 |
| mediumint | 中等大小的数据 | 3个字节 |
| int(常用) | 标准的整数 | 4个字节 |
| bigint | 较大的数据 | 8个字节 |
| 数据类型 | 描述 | 大小 |
|---|---|---|
| float | 浮点数 | 4个字节 |
| double | 浮点数 | 8个字节 |
| decimal | 字符串形式的浮点数，金融计算的时候一般使用decimal，decimal(9,2)，代表一个9位数，其中小数位是2位 |
字符串
| 数据类型 | 描述 | 大小 |
|---|---|---|
| char | 字符串固定大小的 | 0-255 |
varchar(常用) string | 可变字符串 | 0-65535 |
| tinytext | 微型文本 | 2^8-1 |
| text(保存大文本) | 文本串 | 2^16-1 |
时间日期
java.util.Date
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime 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，默认值为男，如果不指定该列的值，则会有默认的值
每一个表，都必须存在以下五个字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
2.4 创建数据库表（重点）
-- NOT NULL 不为空
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加,(英文的)最后一个字段不用加
-- DEFAULT 默认值
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(2) 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] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
#...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];
常用命令
SHOW CREATE DATABASE `school`
-- 查看创建数据库的语句
SHOW CREATE TABLE student
-- 查看student数据表的定义语句
DESC student
-- 显示表的结构
2.5 数据表的类型
关于数据库引擎
INNODB：默认使用
MYISAM：早些年使用
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为MYISAM 2倍 |
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件的存储
位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
MySQL引擎在物理文件上的区别
- INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
CHARSET = utf8
不设置的话，会是mysql默认的字符集编码Latin1，（不支持中文）
可以在my.ini中配置默认的编码：
character-set-server=utf8
2.6 修改删除表
2.6.1 修改
-- 修改表名ALTER TABLE teacher RENAME AS teacher1-- 增加表的字段ALTER TABLE teacher1 ADD age INT(10)-- 修改表的字段（重命名，修改约束！）ALTER TABLE teacher1 MODIFY age VARCHAR(10) -- 修改约束ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名 -- 删除表的字段ALTER TABLE teacher1 DROP age1
修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
2.6.2 删除
-- 删除表DROP TABLE IF EXISTS teacher1
语法：DROP TABLE [IF EXISTS] 表名
- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
所有的创建和删除操作尽量加上判断，以免保存
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mCxrCbMq-1628643646429)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210722170422673.png)]
3.MySQL的数据管理
3.1 外键（了解）
什么是外键
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键的作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
创建外键
创建外键的方式一 ：创建子表同时创建外键（麻烦）
-- 创建外键的方式一 : 创建子表同时创建外键 -- 年级表 (id\年级名称)CREATE TABLE `grade` ( `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 -- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)CREATE TABLE `student` ( `studentno` INT(4) NOT NULL COMMENT '学号', `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `sex` TINYINT(1) DEFAULT '1' COMMENT '性别', `gradeid` INT(10) DEFAULT NULL COMMENT '年级', `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机', `address` VARCHAR(255) DEFAULT NULL COMMENT '地址', `birthday` DATETIME DEFAULT NULL COMMENT '生日', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', PRIMARY KEY (`studentno`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8
注意：删除有外键关系的表的时候，必须要先删除引用别人的表（），再删除被引用的表
方法二：创建表成功后，添加外键约束
-- 年级表 (id\年级名称)CREATE TABLE `grade` ( `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建表的时候没有外键关系CREATE TABLE `student` ( `id` INT(4) NOT NULL COMMENT '学号', `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `sex` TINYINT(1) DEFAULT '1' COMMENT '性别', `gradeid` INT(10) DEFAULT NULL COMMENT '年级', `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机', `address` VARCHAR(255) DEFAULT NULL COMMENT '地址', `birthday` DATETIME DEFAULT NULL COMMENT '生日', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键ALTER TABLE `student`ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`id`)
以上的操作都是物理外键，数据库级别的外键，我们不建议使用。（避免数据库过多，相关联，删都删不掉）这里了解即可
最佳实践
-
数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
-
如果想使用多张表的数据，想使用外键，后面可以用程序去实现
-- 删除外键ALTER TABLE student DROP FOREIGN KEY FK_gradeid;-- 发现执行完上面的,索引还在,所以还要删除索引-- 注:这个索引是建立外键的时候默认生成的ALTER TABLE student DROP INDEX FK_gradeid;
3.2 DML语言（重点，全部记住）
数据库意义：存储数据，管理数据
DML语言：数据操作语言
-
insert
-
update
-
delete
3.3 添加（insert）
语法：INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
测试代码：
INSERT INTO `student` (`id`,`studentname`,`sex`,`gradeid`,`phoneNum`,`address`,`birthday`,`email`,`idCard`)VALUE ('001','老农民','1','1','110','火星','2000-1-1','123456','123456')INSERT INTO `student` (`studentname`) VALUE ('张三'),('李四'),('王五'),('赵六')INSERT INTO `student` (`studentname`,`sex`,`gradeid`,`phoneNum`,`address`,`birthday`,`email`,`idCard`)VALUE ('张三','1','1','110','火星','2000-1-1','123456','123456'),('李四','1','1','110','火星','2000-1-1','123456','123456')
注意：
- 字段或值之间用英文逗号隔开 .
- ’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
- 可同时插入多条数据 , values 后用英文逗号隔开 。
values(),(),...
3.4 修改（update）
语法：UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
测试代码：
UPDATE `school`.`student` SET `gradeid` = '234' , `address` = '234' WHERE `id` = '1';
注意：
- column_name 为要更改的数据列
- value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
- condition 为筛选条件 , 如不指定则修改该表的所有列数据
where条件子句
可以简单的理解为 : 有条件地从表中筛选数据
操作符会返回布尔值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5 = 6 | false |
| <>或!= | 不等于 | 5 <> 6 | true |
| > | 大于 | ||
| < | 小于 | ||
| >= | 大于等于 | ||
| <= | 小于等于 | ||
| BETWEEN…AND… | 在2到5之间，包含2和5。[2,5] | ||
| AND | && | ||
| OR | || |
3.5 删除
delete命令
语法：DELETE FROM 表名 [WHERE condition];
测试代码：
DELETE FROM grade WHERE gradeid = 5
TRUNCATE
作用：完全清空一个数据库表，表的结构和索引约束不会变
注意：区别于DELETE命令
- 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
- 不同 :
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）
测试：
-- 创建一个测试表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('row1'),('row2'),('row3');-- 删除表数据(不带where条件的delete)DELETE FROM test;-- 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.-- 删除表数据(truncate)TRUNCATE TABLE test;-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)
了解即可：DELETE删除的问题，重启数据库，现象
- InnoDB 重启数据库自增列会从1开始（存在内测中的，断电即失）
- MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
4.DQL查询数据（最重点）
4.1 什么是DQL
DQL( Data Query Language 数据查询语言 )
- 所有的查询操作都用它 Select
- 简单或复杂的查询它都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
select语法
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NNXdk1Mo-1628643646430)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210726162717012.png)]
4.2 指定查询字段
查询指定字段
-- 查询所有学生信息SELECT * FROM student; -- 查询指定列(学号 , 姓名)SELECT studentno,studentname FROM student;
AS
-- AS：给查询的结果起一个别名SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS 学生表-- 函数concat(a,b) 拼接字符串SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM `student`
如果列的名字不是那么见名知意，就可以as起别名
distinct 去重
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1gAh3cTQ-1628643646430)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210723162109714.png)]
数据的列（表达式）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0KZ2Teom-1628643646431)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210723162136293.png)]
数据库中的表达式：文本，列，NULL，函数，计算表达式，系统变量等
4.3 where条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果为布尔值
逻辑运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UaM9LW2i-1628643646432)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210726092326434.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2rNdmrtd-1628643646433)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210723165534518.png)]
模糊查询：（比较运算符）
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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，查询姓刘的同学-- %代表任意字符，_代表一个字符SELECT `studentname` FROM `student` WHERE `studentname` LIKE '张%'-- 查询姓刘的同学，名字后面只有一个字的SELECT `studentname` FROM `student` WHERE `studentname` LIKE '张_'-- 查询姓刘的同学，名字后面两个字的SELECT `studentname` FROM `student` WHERE `studentname` LIKE '张__'-- 查询名字中带有小字的SELECT `studentname` FROM `student` WHERE `studentname` LIKE '%小%'-- 查询1000，1001，1002，1003号学员SELECT CONCAT('学号:',`studentno`) AS 新学号, CONCAT('姓名:',`studentname`) AS 新名字 FROM `student` WHERE `studentno` IN(1000,1001,1002,1003)SELECT `studentname` FROM `student` WHERE `studentname` IN ('张小龙')-- 查询年纪为空的学生SELECT `studentno`,`studentname` FROM `student`WHERE `gradeid` IS NULL
4.4 联表查询
JOIN 对比
– 联表查询 JOIN
– join(连接的表) on(判断的条件) 连接查询
– where 等值查询
– 查询参加了考试的同学
SELECT s.`studentno`,s.`studentname`,r.`subjectno`,r.`studentresult`FROM `student` AS sRIGHT JOIN `result` AS rON s.`studentno` = r.`studentno`
测试代码
/*连接查询 如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询内连接 inner join 查询两个表中的结果集中的交集外连接 outer join 左外连接 left join (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充) 右外连接 right join (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充) 等值连接和非等值连接自连接*/ -- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)SELECT * FROM student;SELECT * FROM result; /*思路:(1):分析需求,确定查询的列来源于两个类,student result,连接查询(2):确定使用哪种连接查询?(内连接)*/SELECT s.studentno,studentname,subjectno,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentno -- 右连接(也可实现)SELECT s.studentno,studentname,subjectno,StudentResultFROM student sRIGHT JOIN result rON r.studentno = s.studentno -- 等值连接SELECT s.studentno,studentname,subjectno,StudentResultFROM student s , result rWHERE r.studentno = s.studentno -- 左连接 (查询了所有同学,不考试的也会查出来)SELECT s.studentno,studentname,subjectno,StudentResultFROM student sLEFT JOIN result rON r.studentno = s.studentno -- 查一下缺考的同学(左连接应用场景)SELECT s.studentno,studentname,subjectno,StudentResultFROM student sLEFT JOIN result rON r.studentno = s.studentnoWHERE StudentResult IS NULL -- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)SELECT s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON sub.subjectno = r.subjectno
自连接
数据表与自身相连接，核心：一张表拆为两张一样的表即可
测试代码
/*自连接 数据表与自身进行连接需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中 查询父栏目名称和其他子栏目名称*/ -- 创建一个表CREATE TABLE `category` ( `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', `pid` INT(10) NOT NULL COMMENT '父id', `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryid`)) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 -- 插入数据INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)VALUES('2','1','信息技术'),('3','1','软件开发'),('4','3','数据库'),('5','1','美术设计'),('6','3','web开发'),('7','5','ps技术'),('8','2','办公信息'); -- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'FROM category AS a,category AS bWHERE a.`categoryid`=b.`pid`
练习
-- 练习-- 查询学员及所属的年级(学号,学生姓名,年级名)SELECT `studentno`,`studentname`,`gradename`FROM `student` AS aRIGHT JOIN `grade` AS bON a.`gradeid` = b.`gradeid`WHERE a.`studentname` IS NOT NULL-- 查询科目及所属的年级(科目名称,年级名称)SELECT `subjectname`,b.`gradename`FROM `subject` AS aINNER JOIN `grade` AS bON a.`gradeid` = b.`gradeid`-- 查询 高等数学-1 的所有考试结果(学号 学生姓名 科目名称 成绩)SELECT s.gradeid,s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON r.subjectno = sub.subjectnoWHERE subjectname='高等数学-1'
4.5 分页和排序
分页
推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
…
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]
-- 分页limitSELECT s.gradeid,s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON r.subjectno = sub.subjectnoORDER BY StudentResult DESCLIMIT 0,2
排序
-- order by 升序：ASC 降序：DESCSELECT s.gradeid,s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON r.subjectno = sub.subjectnoORDER BY StudentResult DESC
4.6 子查询
where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句
where(select * from)
测试代码：
/*============== 子查询 ================什么是子查询? 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句 嵌套查询可由多个子查询组成,求解的方式是由里及外; 子查询返回的结果一般都是集合,故而建议使用IN关键字;*/ -- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列-- 方法一:使用连接查询SELECT studentno,r.subjectno,StudentResultFROM result rINNER JOIN `subject` subON r.`SubjectNo`=sub.`SubjectNo`WHERE subjectname = '数据库结构-1'ORDER BY studentresult DESC; -- 方法二:使用子查询(执行顺序:由里及外)SELECT studentno,subjectno,StudentResultFROM resultWHERE subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1')ORDER BY studentresult DESC; -- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名-- 方法一:使用连接查询SELECT s.studentno,studentnameFROM student sINNER JOIN result rON s.`StudentNo` = r.`StudentNo`INNER JOIN `subject` subON sub.`SubjectNo` = r.`SubjectNo`WHERE subjectname = '高等数学-2' AND StudentResult>=80 -- 方法二:使用连接查询+子查询-- 分数不小于80分的学生的学号和姓名SELECT r.studentno,studentname FROM student sINNER JOIN result r ON s.`StudentNo`=r.`StudentNo`WHERE StudentResult>=80 -- 在上面SQL基础上,添加需求:课程为 高等数学-2SELECT r.studentno,studentname FROM student sINNER JOIN result r ON s.`StudentNo`=r.`StudentNo`WHERE StudentResult>=80 AND subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2') -- 方法三:使用子查询-- 分步写简单sql语句,然后将其嵌套起来SELECT studentno,studentname FROM student WHERE studentno IN( SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2' )) /*练习题目: 查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数) 使用子查询,查询郭靖同学所在的年级名称*
4.7 分组和过滤
GROUP BY 和 HAVING
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分FROM `result` rINNER JOIN `subject` subON r.`subjectno` = sub.`subjectno`GROUP BY r.SubjectNoHAVING 平均分 > 60
4.8 Select小结
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-125y3SBA-1628643646433)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210727153542845.png)]
5.MySQL函数
5.1常用函数
5.1.1 数据函数
SELECT ABS(-8); /*绝对值*/ SELECT CEILING(9.4); /*向上取整*/ SELECT FLOOR(9.4); /*向下取整*/ SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/ SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
5.1.2 字符串函数
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 '周%';
5.1.3 日期和时间函数
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());
5.1.4 系统信息函数
SELECT VERSION(); /*版本*/ SELECT USER(); /*用户*/
5.2 聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】 |
| SUM() | 返回数字字段或表达式列作统计，返回一列的总和。 |
| AVG() | 通常为数值字段或表达列作统计，返回一列的平均值 |
| MAX() | 可以为数值字段，字符字段或表达式列作统计，返回最大的值。 |
| MIN() | 可以为数值字段，字符字段或表达式列作统计，返回最小的值。 |
where条件中不能包含聚合函数，要在group by 后的having中使用
测试代码：
-- 聚合函数 /*COUNT:非空的*/ SELECT COUNT(studentname) FROM student; SELECT COUNT(*) FROM student; SELECT COUNT(1) FROM student; /*推荐*/ -- 从含义上讲，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;
GROUP BY 和 HAVING
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分FROM `result` rINNER JOIN `subject` subON r.`subjectno` = sub.`subjectno`GROUP BY r.SubjectNoHAVING 平均分 > 60
5.3 数据库级别的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 = utf8INSERT INTO `testMD5` VALUES('1','张三','123456')UPDATE testMD5 SET pwd = MD5(pwd) WHERE id = 1INSERT INTO `testMD5` VALUES('2','李四','wxy20020417+')UPDATE testMD5 SET pwd = MD5(pwd) WHERE id = 2INSERT INTO `testMD5` VALUES('3','王五','192837465')UPDATE testMD5 SET pwd = MD5(pwd) WHERE id = 3UPDATE testMD5 SET pwd = '192837465789632145' WHERE id = 3
6.事务
6.1 什么是事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
存储过程
6.2 事务原则：ACID原则
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据要保持一致
隔离性（Isolation）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
持久性（Durability）—事务提交
事务一旦提交则不可逆，被持久化到数据库中
隔离所导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据
不可重复读
虚读（幻读）
6.3 执行事务
基本语法
-- 使用set语句来改变自动提交模式SET autocommit = 0; /*关闭*/SET autocommit = 1; /*开启*/ -- 注意:--- 1.MySQL中默认是自动提交--- 2.使用事务时应先关闭自动提交 -- 开始一个事务,标记事务的起始点START TRANSACTION -- 提交一个事务给数据库COMMIT -- 将事务回滚,数据回到本次事务的初始状态ROLLBACK -- 还原MySQL数据库的自动提交SET autocommit =1; -- 保存点SAVEPOINT 保存点名称 -- 设置一个事务保存点ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点RELEASE SAVEPOINT 保存点名称 -- 删除保存点
测试代码
/*课堂测试题目A在线买一款价格为500元商品,网上银行转账.A的银行卡余额为2000,然后给商家B支付500.商家B一开始的银行卡余额为10000创建数据库shop和创建表account并插入2条数据*/ CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;USE `shop`; CREATE TABLE `account` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL, `cash` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO account (`name`,`cash`)VALUES('A',2000.00),('B',10000.00) -- 转账实现SET autocommit = 0; -- 关闭自动提交START TRANSACTION; -- 开始一个事务,标记事务的起始点UPDATE account SET cash=cash-500 WHERE `name`='A';UPDATE account SET cash=cash+500 WHERE `name`='B';COMMIT; -- 提交事务# rollback;SET autocommit = 1; -- 恢复自动提交
7.索引
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YiyRxiJW-1628643646434)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210728110705116.png)]
7.1索引的分类
- 主键索引 PRIMARY KEY
- 唯一的标识，主键不可重复，一张表只能有一个主键
- 唯一索引 UNIQUE KEY
- 避免重复的列出现，可以重复，多个列都可以标识为 唯一索引
- 常规索引 KEY/INDEX
- 默认的，index、key关键字来设置
- 全文索引 FULLTEXT
- 在特定的数据库引擎下才有，快速定位数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OJ5UPfUt-1628643646434)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210728160458989.png)]
7.2 测试索引
CREATE TABLE `app_user` (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) DEFAULT '',`email` VARCHAR(50) NOT NULL,`phone` VARCHAR(20) DEFAULT '',`gender` TINYINT(4) UNSIGNED DEFAULT 0,`password` VARCHAR(100) NOT NULL DEFAULT '',`age` TINYINT(4) DEFAULT NULL,`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8DELIMITER $$CREATE FUNCTION mock_data()RETURNS INTDETERMINISTICBEGIN 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),'24736743@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` = '用户99999'-- CREATE INDEX 索引名 on 表(字段)CREATE INDEX name_app_user_name ON app_user(`name`)SELECT * FROM `app_user` WHERE `name` = '用户99999'
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显
7.3 索引原则
索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上
索引的数据类型
Hash类型的索引
Btree：innoDB的默认数据结构
Extra1
视图
面向视图更新，原表数据也会被更新
我们把特别长的sql语句当作视图对象创建出来
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个SQL语句的时候都需要重新编写，就可以把这条复杂的SQL语句以视图对象的形形式创建出来。
视图对象是存储在硬盘上的，重启不会消失
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SvQ3mEUr-1628643646434)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729153122697.png)]
as后面只能是DQL（查询语句）
增删改查，又叫做CRUD
笛卡尔积现象
当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表的乘积，这种现在被称为：笛卡尔积现象。
如何避免笛卡尔积现象？
连接时加条件，满足这个条件的记录被筛选出来
如：
`SELECT `student`.`studentno`,`address`,`studentresult` FROM `student`,`result` ------ SELECT `student`.`studentno`,`address`,`studentresult` FROM `student`,`result`WHERE `student`.`studentno` = `result`.`studentno` ------- SELECT a.`studentno`,`address`,`studentresult`FROM `student` AS aINNER JOIN `result` AS bON a.`studentno` = b.`studentno`
8.权限管理和备份（DBA命令）
8.1 用户管理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-628wZYaq-1628643646435)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729103659340.png)]
基本命令
/* 用户和权限管理 */ ------------------用户信息表：mysql.user -- 刷新权限FLUSH PRIVILEGES -- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串) - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。 - 只能创建用户，不能赋予权限。 - 用户名，注意引号：如 'user_name'@'192.168.1.1' - 密码也需引号，纯数字密码也要加引号 - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD -- 重命名用户 RENAME USER kuangshen TO kuangshen2RENAME USER old_user TO new_user -- 设置密码SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码 -- 删除用户 DROP USER kuangshen2DROP USER 用户名 -- 分配权限/添加用户GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password'] - all privileges 表示所有权限 - *.* 表示所有库的所有表 - 库名.表名 表示某库下面的某表 -- 查看权限 SHOW GRANTS FOR root@localhost;SHOW GRANTS FOR 用户名 -- 查看当前用户权限 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER(); -- 撤消权限REVOKE 权限列表 ON 表名 FROM 用户名REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
权限解释
-- 权限列表ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限ALTER -- 允许使用ALTER TABLEALTER ROUTINE -- 更改或取消已存储的子程序CREATE -- 允许使用CREATE TABLECREATE ROUTINE -- 创建已存储的子程序CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLECREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。CREATE VIEW -- 允许使用CREATE VIEWDELETE -- 允许使用DELETEDROP -- 允许使用DROP TABLEEXECUTE -- 允许用户运行已存储的子程序FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILEINDEX -- 允许使用CREATE INDEX和DROP INDEXINSERT -- 允许使用INSERTLOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLESPROCESS -- 允许使用SHOW FULL PROCESSLISTREFERENCES -- 未被实施RELOAD -- 允许使用FLUSHREPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）SELECT -- 允许使用SELECTSHOW DATABASES -- 显示所有数据库SHOW VIEW -- 允许使用SHOW CREATE VIEWSHUTDOWN -- 允许使用mysqladmin shutdownSUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。UPDATE -- 允许使用UPDATEUSAGE -- “无权限”的同义词GRANT OPTION -- 允许授予权限 /* 表维护 */ -- 分析和存储表的关键字分布ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...-- 检查一个或多个表是否有错误CHECK TABLE tbl_name [, tbl_name] ... [option] ...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}-- 整理数据文件的碎片OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
8.2 MySQL备份
为什么要备份
保证重要的数据不丢失
数据转移
方法：
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件和相关配置文件
-- 导出1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql) 可以-w携带备份条件 -- 导入1. 在登录mysql的情况下：-- source D:/a.sql source 备份文件2. 在不登录的情况下 mysql -u用户名 -p密码 库名 < 备份文件
9. 规范数据库设计
9.1为什么需要设计
当数据库比较复杂的时候，我们就需要设计了
分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图E-R图
Element UI
Ant Design Pro
Lay UI
https://max.book118.com/html/2019/1112/8126134011002063.shtm
9.2 三大范式
1.为什么需要数据库规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
2.什么是数据库设计范式
数据库表的设计依据。教你怎么进行数据库表的设计
3.三大范式是什么
第一范式（1NF）
要求任何一张表必须有主键，每一个字段原子性不可再分
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
要求所有非主键字段完全依赖主键，不要产生部分依赖
第三范式（3NF）
前提：满足第一范式和第二范式
要求所有非主键字段直接依赖主键，不要产生传递依赖
三范式是面试官经常问的，所以一定要熟记在心
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费
第一范式
最核心，做重要的范式，所以表的设计都需要满足。
必须要有主键，并且每一个字段都是原子性不可再分。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z6jeGxDL-1628643646435)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729155148097.png)]
第二范式
建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5jGxYGJ7-1628643646436)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729163526280.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vGsXsWj1-1628643646436)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729164212944.png)]
第三范式
建立在第二范式的基础之上，要求所有非主键字段必须直接依赖主键，不要产生传递依赖
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s4zs6m99-1628643646436)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729164728679.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dXccFW08-1628643646437)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729165009134.png)]
规范数据库的设计
数据库设计三范式是理论上的
实践和理论有的时候有偏差
最终的目的都是为了满足客户的需求，有点时候会拿冗余换执行速度
在sql当中，表和表直接连接次数越多，效率越低（笛卡尔积）
有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低
面试的时候把这句话说上，塔就不会认为你是初级程序员了
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zezgbOoP-1628643646437)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210729170123902.png)]
10.JDBC（重点）
10.1 数据库驱动
我们的程序会通过 数据库驱动 和数据库打交道
10.2 JDBC
SUN公司为了简化 开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC接口的操作即可。
java.sql
javax.sql
还需要导入一个数据库驱动包，如 mysql-connector-java-8.0.16.java
10.3 第一个JDBC程序
创建测试数据库
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;USE `jdbcStudy`;CREATE TABLE `users`( `id` INT PRIMARY KEY, `NAME` VARCHAR(40), `PASSWORD` VARCHAR(40), `email` VARCHAR(60), birthday DATE);INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),('2','lisi','123456','lisi@sina.com','1981-12-04'),('3','wangwu','123456','wangwu@sina.com','1979-12-04');
1.创建一个普通项目
2.导入数据库驱动
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IZUrhLDG-1628643646438)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210730090637457.png)]
3.编写测试代码
package com.lesson01;import java.sql.*;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class JdbcDemo01 { public static void main(String[] args) throws SQLException { //1.加载驱动 try { Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动 } catch (ClassNotFoundException e) { e.printStackTrace(); } //2.用户信息和url String url = "jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSl=true"; String userName = "root"; String password = "12345"; //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")); System.out.println("email = "+resultSet.getObject("email")); System.out.println("birthday = "+resultSet.getObject("birthday")); } //6.释放连接 resultSet.close(); statement.cancel(); connection.close(); }}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zXhwqeaE-1628643646438)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210730093938270.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GU1mqS1P-1628643646438)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210730170706106.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ptxdrnVm-1628643646439)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210730170451568.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2h1hJDIT-1628643646439)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731090923011.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YGOnsQCq-1628643646439)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731091102308.png)]
10.4 statement对象
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K339kAFk-1628643646440)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731091610298.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iKK1nYcX-1628643646440)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731091622627.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MvRavzlb-1628643646441)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731091654474.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s3psI2wJ-1628643646441)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731091710650.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RmpEIJFQ-1628643646441)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20210731093451412.png)]
代码实现
1.提取工具类
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
