企业网站建设实战教程,海淀区,wordpress页面设置栏目,做网站申请什么商标MySQL 只会写代码 基本码农 要学好数据库#xff0c;操作系统#xff0c;数据结构与算法 不错的程序员 离散数学、数字电路、体系结构、编译原理。实战经验#xff0c; 高级程序员 去IOE#xff1a;去掉IBM的小型机、Oracle数据库、EMC存储设备#xff0c;代之以自己在开源…MySQL 只会写代码 基本码农 要学好数据库操作系统数据结构与算法 不错的程序员 离散数学、数字电路、体系结构、编译原理。实战经验 高级程序员 去IOE去掉IBM的小型机、Oracle数据库、EMC存储设备代之以自己在开源软件基础上开发的系统。 一 初识MYSQL
1.1 数据库
数据库DBDataBase是按照数据结构来组织、存储和管理数据的仓库安装在操作系统之上 DBMS(数据库管理系统)数据库的管理软件科学有效的管理我们的数据维护和获取数据
1.2 数据库分类
关系型数据库SQL
MYSQL、Oracle、Sql Server、DB2、SQLlite通过表和表之间行和列之间的关系进行数据的存储 学员信息库考勤表
非关系型数据库NoSQL Not Only SQL:
RedisMongDB非关系型数据库对象存储通过对象自身的属性来决定
1.3 MySQL简介
MySQL是一个关系型数据库管理系统DBMS (Relational Database Management System关系数据库管理系统)属于Oracle旗下产品使用的SQL语言。 SQL语言Structure Query Language(结构化查询语言)是用于访问数据库的最常用标准化语言由于其体积小、速度快、开放源码等特点一般中小型网站的开发都选择MySQL作为网站数据库。
1.4 SQL的分类
DDL(Data Definition Language) 数据定义语言用来操作数据库、表、列等 常用语句CREATE、 ALTER、DROPDML(Data Manipulation Language) 数据操作语言用来操作数据库中表里的数据常用语句INSERT、 UPDATE、 DELETEDCL(Data Control Language) 数据控制语言用来操作访问权限和安全级别 常用语句GRANT、DENYDQL(Data Query Language) 数据查询语言用来查询数据 常用语句SELECT
1.5 安装MySQL8.0.34win11
1.5.1 下载压缩包
MySQL官网下载 安装建议使用压缩包不使用.exe安装会产生注册表卸载麻烦 下载并解压在环境目录下
1.5.2 配置环境变量
右键此电脑——属性——高级系统设置——环境变量——path系统变量中的——编辑 点击新建将解压后的bin目录地址
1.5.3 编写MySQL配置文件
新建mysql配置文件my.ini 注意basedir、datadir是需要改的
[mysqld]
# 设置3306端口
port3306
# 设置mysql的安装目录 ----------是你的文件路径-------------
basedirD:\Environment\Mysql-8.0.34\
# 设置mysql数据库的数据的存放目录 ---------是你的文件路径data文件夹自行创建不要自己创建
#datadirD:\DataBase\MySQL\Data\
# 允许最大连接数
max_connections200
# 允许连接失败的次数。
max_connect_errors10
# 服务端使用的字符集默认为utf8mb4
character-set-serverutf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engineINNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_pluginmysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-setutf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port3306
default-character-setutf8mb4启动管理员模式下的CMD进入mysql的bin目录我的是cd/d D:\Environment\Mysql-8.0.34\bin6. 输入mysqld -install安装mysql服务 输入mysqld --initialize-insecure --usermysql初始化数据库文件会出现data文件夹 输入net start mysql启动mysql 通过命令行mysql -u root -p两个回车进入mysql输入alter user rootlocalhostidentified by 123456;修改密码SQL语句后面一定要加分号 最后输入flush privileges;刷新权限 输入quit或者exit都可以退出 重启mysql。net start mysql连接测试若连接成功即可 mysql -uroot -p登陆 输入密码 ok 然后右键此电脑——管理——服务与应用程序——服务 找到mysql将启动类型改为手动防止电脑启动过慢
1.5.4 如果出现问题重装
清空服务 sc delete mysql 1.6 安装SQLyog
SQLyog 是一个易于使用的、快速而简洁的图形化管理MYSQL数据库的工具 安装地址 注意下载一个社区的的版本下载包里含Community
1.7 SQLyog基本使用
每一个SQLyog的执行操作本质就是对应了一个sql语句可以在软件的历史记录中查看 新建数据库前提连接mysql 名称随意输入密码 IP地址本地访问本地安装的数据库使用localhost 用户名使用root用户 密码安装mysql时设置的密码 端口号安装mysql时设置的端口号一般使用3306 输入信息点击连接时报错错误代码为2058。说明mysql没链接重连即可1 新建一个数据库school 新建一张表student 字段 id,name,age 查看表 自己尝试添加多条记录 记得刷新保存
如何查看实际执行的sql语言
1.8 基本命令行操作
命令行里的连接
-- 所有语句都用分号结尾
cd/d D:\Environment\Mysql-8.0.34\bin
mysql -uroot -p --链接数据库update mysql.user set authentication_stringpassword(123456) where userroot and Host localhost; --修改密码flush privileges; --修改权限show databases; -- 查看所有的数据库use school -- 切换数据库use 数据库名show tables; -- 查看数据库中所有的表desc student; -- 显示表结构desc 表名create database hello; -- 创建一个数据库helloexit; -- 退出连接-- 单行注释
/*
多行注释
*/ 二 数据库、表的基本操作
操作数据库——操作数据库中的表——操作数据库中表的数据
mysql数据库关键字不区分大小写
2.1 操作数据库
2.1.1 创建数据库
([]表示可选{}表示必选)
CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET utf8 COLLATE utf8_general_ci]2.1.2 删除数据库
DROP DATABASE [IF EXISTS] 数据库名2.1.3 使用数据库· -- tab键的上面如果你的表名或者字段名是一个特殊字符就需要带上USE school2.1.4 查看数据库
SHOW DATABASES -- 查看所有数据库2.2 操作数据库表 自定义名称和字段 尽量使用 括起来 -- 目标创建一个school数据库
-- 创建学生表列字段 使用sql创建
-- 学号int 登录密码varchar(20) 姓名性别varchar(2) 出生日期(datetime)家庭住址email--
-- AUTO_INCREMENT 自增
-- COMMENT 备注注释
-- 字符创使用 单引号括起来
-- 所有的语句后面加英文的逗号, 最后一个字段加英文的分号;
-- primary key 主键一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS student(
id INT(4) NOT NULL AUTO_INCREMENT COMMENT 学号,
password VARCHAR(20) NOT NULL DEFAULT 123456 COMMENT 密码,
name VARCHAR(30) NOT NULL DEFAULT 匿名 COMMENT 姓名,
general 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)
UNIQUE KEY general (general),
KEY email (email)
)ENGINEINNODB DEFAULT CHARSETutf8唯一索引和一般索引语法UNIQUE KEY 给唯一键取的名字( 原名 ) / KEY/INDEX 给唯一键取的名字( 原名 )
格式
CREATE TABLE [IF NOT EXISTS] 表名(字段名 列类型 [属性] [索引] [注释],……字段名 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构三 数据库的数据类型
3.1 数值
简称的类型名适用长度字节tinyint十分小的数据1smallint较小的数据2个字节mediumint中等大小的数据3int标准的数据4big较大的数据8float浮点数4double浮点数8decima字符串形式的浮点数16 int(3) 意思不是最大保存3位数而是指最大显示宽度即输入2、20、200显示分别为002、020、200 decimal(5,3) 指的是总长度为5(总长度5)但小数后有3位如存数据5----5.000总长度4存数据123.7由于小数点后会补零至3位—123.700总长度 超过5将报错或者写成999.99} decimal不存在精度损失金融计算的时候一般使用decimal 2.2.2 字符串
简称的类型名适用长度字节char固定字符串0~255varchar可变字符串0~65535tinytext微型文本0~255ext文本串0~65535 在使用中char(5)指固定长度为5即只能输入5位数据否则报错一般知道文本长度就用char 在使用中varchar(20)指的可变长度只要长度20即可这里存中文和英文存储的个数都可以使20个以内中文不占3个字节但在5.0之前就会 2.2.3 时间日期
简称的类型名格式适用dateYYYY-MM-dd日期格式timeHH:mm:ss时间格式datetimeYYYY-MM-DD HH:mm:ss最常用的时间格式timestamp时间戳 1970.1.1到现在的毫秒数year年份表示
2.2.4 null
使用null进行计算结果肯定为null
2.3、数据库的字段属性 Unsigned无符号即不能为负数的整数zerofill0填充不足的位数使用0来填充自增auto incr通常理解为自动上一条记录的基础上1默认
通常用来设计唯一的主键index必须是整数类型可以在高级处自定义设计主键自增的起始值和步长 非空Null、not null设置为not null如果不赋值会报错 默认设置默认的值
/*每一个表都必须存在以下五个字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/2.5、数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/MYISAMINNDB事物支持不支持支持数据行锁定不支持支持外键约束不支持支持全文索引支持不支持表空间的大小较小较大约为MYISAM的两倍
常规使用操作
MYISAM 节约空间速度较快INNODB 安全性高事务的处理多表多用户操作 在物理空间存在的位置 所有的数据库文件都存在data目录下一个文件夹就对应一个数据库
本质上还是文件的存储
MySQL引擎在物理文件上的区别 innoDB在数据库表中只有一个*.frm文件以及上级目录下的ibdata1文件在data目录下 创建表的引擎为MYISAM时生成的文件 *.frm 表结构的定义文件*.MYD 数据文件data*.MYI 索引文件index 设置数据库表的字符集编码 CHARSETutf8不设置话会是mysql默认的字符集编码 Latin1不支持中文
在my.ini中配置默认的编码
character-set-serverutf82.6、修改删除表 修改 修改表名语法 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1增加表的字段语法ALTER TABLE 表名 ADD 新增字段名 数据类型
ALTER TABLE teacher1 ADD age INT(11) 用modify修改表字段语法ALTER TABLE 表名 MODIFY 字段名 数据类型[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- modify不能重命名只能修改的是约束和数据类型用change修改表字段语法ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型][]
ALTER TABLE teacher1 CHANGE age agel INT(11) -- change可以重命名、约束和数据类型但必须要写上旧名和新名即使名字都一样删除表的字段语法ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP agel删除表语法DROP TABLE 表名
DROP TABLE IF EXISTS teacher1所有的创建和删除操作尽量加上判断以免报错
注意点 反引号注释 --(空格) /**/所有符号用英文
3、MySQL数据管理
3.1、外键了解 方式一、在创建表的时候增加约束比较复杂 CREATE TABLE IF NOT EXISTS grade(gradeid INT(10) NOT NULL AUTO_INCREMENT COMMENT 年级id,gradename VARCHAR(10) NOT NULL COMMENT 年级名称,PRIMARY KEY (gradeid)
)ENGINEINNODB DEFAULT CHARSETutf8-- 学生表的grad字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束执行引用 references 引用
CREATE TABLE IF NOT EXISTS student(id INT(20) NOT NULL AUTO_INCREMENT COMMENT 学号,name VARCHAR(20) DEFAULT 匿名 COMMENT 名字,gender VARCHAR(2) NOT NULL DEFAULT 男 COMMENT 性别,birthday DATETIME DEFAULT 2020-11-13 COMMENT 生日,gradeid INT(10) NOT NULL COMMENT 年级,PRIMARY KEY(id),KEY FK_gradeid (gradeid),CONSTRAINT FK_gradeid FOREIGN KEY (gradeid) REFERENCES grade (gradeid)
)ENGINEINNODB DEFAULT CHARSETutf8删除有外键关系的表的时候必须要先删除引用别人的表从表再删除被引用的表主表 方式二创建没有约束的表成功后再创建约束 -- 学生表的grad字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束执行引用 references 引用
CREATE TABLE IF NOT EXISTS student(id INT(20) NOT NULL AUTO_INCREMENT COMMENT 学号,name VARCHAR(20) DEFAULT 匿名 COMMENT 名字,gender VARCHAR(2) NOT NULL DEFAULT 男 COMMENT 性别,birthday DATETIME DEFAULT 2020-11-13 COMMENT 生日,gradeid INT(10) NOT NULL COMMENT 年级,PRIMARY KEY(id),KEY FK_gradeid (gradeid)
)ENGINEINNODB DEFAULT CHARSETutf8-- 创建表时没有外键关系创建后添加外键
ALTER TABLE student ADD CONSTRAINT FK_gradeid FOREIGN KEY(gradeid) REFERENCES grade(gradeid);以上的操作都是物理外键数据库级别的外键我们不建议使用仅了解即可
最佳实践
数据库就是单纯的表只用来存数据只有行数据和列字段我们想使用多张表的数据想使用外键就用程序去实现
3.2、DML语言背下来
**数据库意义**数据存储数据管理
DML语言数据操作语言
Insertupdatedelete
3.2.1、添加insert
-- 插入语句添加
-- insert into 表名[字段名1,2,3] values值1)(值2)(值3
INSERT INTO grade(gradename) VALUES(大四)-- 由于grade表中的主键gradeid设置了自增所以我们可以省略不写
-- 但如果不写后面的gradename由于是顺序存储系统默认为存入的数据是gradeid的就会报错
INSERT INTO grade(gradename) VALUES(大五) -- 省略写法
INSERT INTO grade(gradeid,gradename) VALUES(NULL,大五) -- 实际运行
-- 所以一般写插入语句我们一定要数据和字段一一对应-- 插入多个字段
INSERT INTO grade(gradename) VALUES(大一),(大二)INSERT INTO student(name,gradeid) VALUES(张三,大一),(万物,大二),(玩具呀,大三)总结
语法insert into 表名([字段名1,2,3]) value(s)(值1,值2,值3)【添加一条数据时可以使用value】字段和字段之间使用英文逗号 隔开字段时可以省略的但是后面的值必须要一一对应。可以同时插入多条数据values后面的值需要使用隔开即可values(),(),()·····
3.2.2、修改update
-- 修改学员的名字
-- 指定条件的情况下
UPDATE student SET name时代 WHERE id1
-- 不指定条件的情况下会改动所有表中对应的数据
UPDATE student SET name积分-- column_name即col常说的列的常用写法
-- 语法UPDATE 表名 set column_namevalue,[column_namevalue],…… where [条件]-- 修改多个属性用逗号隔开
UPDATE student SET name开心,gradeid难过 WHERE id1
Where 子句
搜索 empno 等于 7900 的数据
Select * from emp where empno7900;1、Where 条件筛选行
条件列比较运算符值
比较运算符包涵 ,, !, 表示不等于
Select * from emp where enameSMITH;例子中的 SMITH 用单引号引起来表示是字符串字符串要区分大小写。
2、逻辑运算
And(或者): 与 同时满足两个条件的值。
Select * from emp where sal 2000 and sal 3000;查询 EMP 表中 SAL 列中大于 2000 小于 3000 的值。
Or(或者||): 或 满足其中一个条件的值
Select * from emp where sal 2000 or comm 500;查询 emp 表中 SAL 大于 2000 或 COMM 大于500的值。
Not(或者 ! ) : 非 满足不包含该条件的值。
select * from emp where not sal 1500;查询EMP表中 sal 小于等于 1500 的值。
逻辑运算的优先级
() not and or3、特殊条件
1.空值判断 is null
Select * from emp where comm is null;查询 emp 表中 comm 列中的空值。
2.between and (在 之间的值)
Select * from emp where sal between 1500 and 3000;查询 emp 表中 SAL 列中大于 1500 的小于 3000 的值。
注意大于等于 1500 且小于等于 3000 1500 为下限3000 为上限下限在前上限在后查询的范围包涵有上下限的值。
3.In
Select * from emp where sal in (5000,3000,1500);查询 EMP 表 SAL 列中等于 500030001500 的值。
4.like
Like模糊查询
Select * from emp where ename like M%;查询 EMP 表中 Ename 列中有 M 的值M 为要查询内容中的模糊信息。
% 表示多个字值_ 下划线表示一个字符M% : 为能配符正则表达式表示的意思为模糊查询信息为 M 开头的。%M% : 表示查询包含M的所有内容。%M_ : 表示查询以M在倒数 第二位的所有内容。
语法UPDATE 表名 set column_namevalue,[column_namevalue],…… where [条件]
注意 column_name即col常说的列 是数据库的列尽量带上 条件筛选的条件如果没有指定则会修改所有的列 value是一个具体的值也可以是一个变量。举例 update studentset birthdaycurrent_time where name开心多个设置的属性之间使用英文逗号隔开
3.2.3、删除delete delete命令 语法delete from 表名 [where 条件]
-- 删除数据避免这样写会全部删除)
DELETE FROM student -- 删除指定数据
DELETE FROM student WHERE id 1truncate命令截短缩短删节 作用完全清除一个数据库表表的结构和索引约束不会变
语法TRUNCATE table 表名
--
TRUNCATE table studenttruncate和delete的区别
相同点都能删除数据且不会删除表结构不同点 truncate 会重新设置自增列计数器会归零。而delete则不会归零即使删除了表数据当再次插入数据时仍会继续之前计数器的大小开始计数truncate速度更快且不留痕迹。而delete是逐行删除速度慢且会发送报告留下痕迹
拓展了解即可delete的删除问题当重启数据库时发生的现象
引擎为InnoDB 自增列会从1开始InnoDB是存在内存当中的所以断电即失引擎为MYISAM 继续从上一个自增量开始存在文件中不会丢失
4、DQL查询数据究极重要
本章的所有建表数据均在目录D:\笔记\java笔记\MySQL.assets\4、DQL查询数据建表数据.sql
4.1、DQL
Data Query Language数据查询语言
所有的查询操作都是它 Select数据库中最核心的语言使用频率最高的语句
4.2、查询指定字段
-- 查询全部学生信息 SELECT 字段 FROM 表名
SELECT * FROM student-- 查询指定字段
SELECT studentno,studentname FROM student
-- 给查询的结果起别名 可以给字段起别名也可以给表起别名 AS可省略
SELECT studentno AS 学号,studentname AS 别名 FROM student AS s-- 函数 Concat (str1,str2,……) 令查询的语句结果的呈现发生变化
SELECT CONCAT(姓名:,studentno,studentname) AS 别字 FROM student a查询结果为 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QqtTZhqf-1690606873590)(D:\笔记\java笔记\MySQL.assets\image-20201114183234694.png)]
语法select 需要查询的字段名 [AS] [给查询的字段名取的别名] from 表名 [AS] [给表取的别名]
有的时候列的名字不是那么见名知意我们就需要起别名起名时的AS可以省略但为了代码浏览建议保留 去重 distinct 作用去除SELECT查询出来的结果中重复的数据每条数据只显示一次例张三参加运动会中的20个项目而要查询的事都有谁参加过运动会那么查询结果张三只显示一次
-- 第一步查询有哪些同学参加了考试
SELECT studentno FROM result
-- 第二步发现有重复数据去重
SELECT DISTINCT studentno FROM student数据库的列表达式 通过version()函数查询mysql系统版本
SELECT VERSION() --查询系统版本[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EXvBFxTj-1690606873591)(D:\笔记\java笔记\MySQL.assets\image-20201114201909937.png)]
通过计算表达式用来计算普通计算
SELECT 11 AS 计算结果[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SWbDvQxK-1690606873591)(D:\笔记\java笔记\MySQL.assets\image-20201114201934993.png)]
通过变量查询自增的步长(自增)
SELECT auto_increment_increment AS 步长结果[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4CvgfMJJ-1690606873591)(D:\笔记\java笔记\MySQL.assets\image-20201114202130136.png)]
学院考试成绩1分查看
SELECT studentno,studentresult1 FROM result[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fe0uWpzF-1690606873592)(D:\笔记\java笔记\MySQL.assets\image-20201114211043487.png)]
数据库中的表达式文本值列null函数计算表达式系统变量……
语法select 表达式 from 表名
4.3、where条件子句详细请看3.3.2的where子句
4.4、联表查询
4.4.1、Join
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wzaULdx1-1690606873592)(D:\笔记\java笔记\MySQL.assets\image-20201115163224209.png)]
七种Join概念[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8zFVIitd-1690606873592)(D:\笔记\java笔记\MySQL.assets\20181103160140252.png)]
注意A left join B 表示的是A为主表A里要查找的内容都显示B为从表B里的内容去匹配A显示的内容如果B表里没有则自动不上null再显示
4.4.2、where和on的区别 在左(右)外连接时left/right Joinon是在生成临时表时使用的条件它不管on中的条件是否为真都会先返回左右表中的记录所以即使右(左)表的记录不满足on里的条件依旧会显示出来只是不满足的就补null 如需要查询左表中的studentno和studentname并 左 连接 右 表中的subjectno、examdate和studentresult SELECT s.studentno ,studentname,subjectno,examdate,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno r.studentno[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mvlEyXt5-1690606873593)(D:\笔记\java笔记\MySQL.assets\image-20201115173456021.png)] 而在外连接中where是在临时表生成后再对临时表进行过滤的条件。所以只要left/right Join在生成的左右表不满足where条件则都不显示 如需要查询左表中的studentno和studentname并 左 连接 右 表中的subjectno、examdate和studentresult SELECT s.studentno ,studentname,subjectno,examdate,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno r.studentno -- 这里是为了显示列属性若不加会报错
WHERE s.studentno r.studentno[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jAp5niad-1690606873593)(D:\笔记\java笔记\MySQL.assets\image-20201115174038206.png)]
总结on是建立连接where是对结果的筛选
联表查询思路
分析需求分析查询的字段来自哪些表连接查询确定使用哪种连接查询7种 确定交叉点这两个表中哪个数据是相同的 判断条件如学生表 studentno 成绩表 studentno
举个例子多表联表查询
-- 思考题查询参加考试的学生信息学号学生姓名科目名分数
SELECT st.studentno,studentname,studentresult,sb.subjectno
FROM student st LEFT JOIN result r -- 先从student表左连接result表为了student为主表主表的所有内容显示
ON st.studentnor.studentno
INNER JOIN subject sb --再从以上得到的两表连接结果和subject表做内连接取两者的交集
ON r.subjectno sb.subjectno语法from 表名[别名] left|right|inner join 表名[别名] on 查询条件
4.4.3、自连接
自己的表和自己的表连接核心一张表拆为两张一样的表即可
先拆第一个表父类表
categoryidcategoryName2信息技术3软件开发5美术设计
子类
pidcategoryidcategoryName34数据库28办公信息36web开发57ps技术
操作查询父类对应的子类关系
父类子类信息技术categoryid2办公信息pid2软件开发categoryid3数据库pid3软件开发categoryid3web开发pid3美术设计categoryid5ps技术pid5
例如
-- 自查询
SELECT a.categoryName AS 父栏目,b.categoryName AS 子栏目
FROM category AS a,category AS b
WHERE a.categoryidb.pid即自查询:把一张表看成两张一模一样的表
4.5、分页和排序
4.5.1、排序
-- 分页 limit 和排序 order by
-- ORDER BY 通过哪个字段排序就怎么排
-- 排序升序 ASCAscending order 降序 DESCdescending order
SELECT r.studentresult,st.studentno,st.studentname,sb.subjectno
FROM student st
LEFT JOIN result r
ON st.studentnor.studentno
INNER JOIN subject sb
ON sb.subjectnor.subjectno
ORDER BY studentno DESC语法ORDER BY 需要排序的字段 DESC|ASC
4.5.2、分页
当数据太多时可以使用分页缓解数据库压力
但也可以使用瀑布流例如淘宝商品永远到不了底比较暴力
SELECT st.studentno,st.studentname,sb.subjectno,r.studentresult
FROM student st
LEFT JOIN result r
ON st.studentnor.studentno
INNER JOIN subject sb
ON sb.subjectnor.subjectno
WHERE r.studentresult80
ORDER BY r.studentresult DESC
LIMIT 1,3 -- 跳过的的行数每页只显示的行数没加limit前总共有8条数据[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uJpZQ1bq-1690606873593)(D:\笔记\java笔记\MySQL.assets\image-20201117203356342.png)]
加了limit后[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iFFBbFYY-1690606873594)(D:\笔记\java笔记\MySQL.assets\image-20201117203447751.png)]
语法limit 跳过的数据条数(如原本数字‘28’数据在第7行那要想直接显示28则跳过6行即可在第一行显示数据28)页面显示多少条数据
一般要像页面这样显示[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0zM7dQkP-1690606873594)(D:\笔记\java笔记\MySQL.assets\image-20201117203719290.png)]
-- 第一页 limit 0,5 跳过0条数据每个页面容纳5条数据
-- 第二页 limit 5,5 第二页会从第六条数据开始显示所以跳过前5每个页面容纳5条数据
-- 第三页 limit 10,5 ………………
-- 第n页 limit 5(n-1),5 limit (n-1)*pageSize,pageSize
-- 【pageSize页面容量大小】
-- 【n当前页码】
-- 【数据总数/页面容量大小 总页数向上取整)】4.6、子查询由里及外先由最里面那层开始想
where这个值是计算出来的
本质在where语句中嵌套一个子查询语句
例子一
-- 1、查询 高等数学-3 的所有考试结果学号,科目编号成绩降序排列
-- 方式一使用联表查询
SELECT r.studentno,subjectname,r.subjectno,studentresult
FROM result r
INNER JOIN subject sb
ON r.subjectnosb.subjectno
WHERE subjectname高等数学-3-- 方式二使用子查询由里及外先由最里面那层开始想
/*思路先看条件需要 高等数学-3 出自subject表
而条件 学号,科目编号成绩 都出自result表
那么subject表和result表关联的就是 subjectno
则在子查询中就以subjectno为索引在subject表中查找
返回的结果作为result表筛选的依据
*/
SELECT r.studentno,r.subjectno,studentresult
FROM result r
WHERE subjectno(
SELECT subjectno FROM subject WHERE subjectname高等数学-3
)例子二
-- 查询课程为 高等数学-1 且分数不小于80的同学的学号和姓名
-- 方法一使用联表查询
SELECT st.studentno,studentname
FROM student st
INNER JOIN result r
ON st.studentnor.studentno
INNER JOIN subject sb
ON sb.subjectnor.subjectno
WHERE studentresult80 AND subjectname高等数学-1-- 方法二使用子查询由里及外先由最里面那层开始想
SELECT st.studentno,studentname
FROM student st
INNER JOIN result r
ON st.studentnor.studentno
WHERE studentresult80 AND
subjectno(
SELECT subjectno FROM subject WHERE subjectname高等数学-1
)-- 方法三双重自查询由里及外先由最里面那层开始想
/*首先看条件 高等数学-1且分数不小于80 一个出自result表一个出自subject表
那么谁做最里面那层呢
因为subject表只能返回和题目相关的subjectname而result可以返回题目相关的studentresult和studentno
所以只能由subject作为最里面的条件换得subjectno作为result表的筛选条件再和result表的studentresult作为student表的筛选条件最终返回结果
*/
【③SELECT studentno,studentname FROM student WHERE studentnoIN (【②SELECT studentno FROM result WHERE studentresult80 AND subjectno (【①SELECT subjectno FROM subject WHERE subjectname高等数学-1】)】
)】4.7、分组和过滤
-- 查询不同课程的平均分最高分最低分
-- 核心根据不同的课程分组
SELECT r.subjectno, AVG(studentresult) as 平均分,MAX(studentresult) as 最高分,MIN(studentresult) as 最低分
FROM result r
INNER JOIN subject sb
ON r.subjectnosb.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分80由于上面的avg等函数式聚合函数where用不了所以需要用having
4.8、select小结
Select 完整的语法
selet[ALL | DISTINCT] -- 查询所有/去重
{* | table.* |[table.field[as alias1][,table.field2[as alias2]][……]]}
FROM table_name [as table_alias][left |right |inner join table_name2 on ……] -- 联合查询[WHERE……] -- 指定结果需满足的条件不能跟聚合函数[GROUP BY……] -- 指定结果按照哪几个字段来分组[HAVING ……] -- 过滤分组的记录必须满足的次要条件可以跟聚合函数[ORDER BY ……] -- 指定查询记录按一个或多个条件排序 desc降序 asc升序[LIMIT {[offest,Jrow_count | row_countOFFSET offset]};-- 指定查询的记录从哪条至哪条注意前后顺序不能换[]代表可选,{}代表必选
5、MySQL函数
网址https://dev.mysql.com/doc/refman/8.0/en/functions.html
5.1、常用函数
-- 常用函数
-- 数学运算
SELECT ABS(-1) -- 绝对值
SELECT CEILING(9.2) -- 向上取整
SELECT FLOOR(9.6) -- 向下取整
SELECT RAND() -- 返回0~1之间的随机数
SELECT SIGN(0) -- 判断一个数的符号 0返回0 负数返回-1 整数返回1
-- 字符串
SELECT CHAR_LENGTH(这行字不用数了就十个) -- 字符串长度
SELECT CONCAT(这,里,是,b,站)
SELECT INSERT(一二三四五六七,1,2,520) -- 查询从某个位置开始把新字符串替换原字符串
SELECT UPPER(hello) -- 大写字母
SELECT LOWER(Owner) -- 小写字母
SELECT INSTR(大力接待费,接) -- 返回第一次出现的子字符串
SELECT REPLACE(兼职就能成功,兼职,屁) -- 替换出现的指定字符串
SELECT SUBSTR(骄傲ID设计费呢,5,2) -- 返回指定的子字符串源字符串截取的位置长度
SELECT REVERSE(猪是的念来过倒) -- 反转-- 查询姓 周 的同学名字 邹
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 -- 18 count字段 返回指定列名的个数会忽略所有null值
SELECT COUNT(*) FROM student -- count(*),不会忽略null值本质 计算行数
SELECT COUNT(1) FROM result -- 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 result5.3、数据库级别的MD5加密扩展
MD5信息摘要算法
主要增强算法复杂度和不可逆性。
MD5不可逆具体的值的md5是一样的
MD5破解网站的原理背后有一个字典MD5加密后的值加密的前值 sql
-- 测试MD5 加密
CREATE TABLE testmd5(id INT(4) NOT NULL,name VARCHAR(20) NOT NULL,pwd VARCHAR(50) NOT NULL,PRIMARY KEY(id)
)ENGINEINNODB DEFAULT CHARSETutf8-- 明文密码
INSERT INTO testmd5 VALUES(1,zhangsan,123456),(2,lisi,234567),(3,wangwu,345678)-- 加密
UPDATE testmd5 SET pwdMD5(pwd) WHERE id1
UPDATE testmd5 SET pwdMD5(pwd) -- 加密全部的密码
-- 插入数据时直接加密INSERT INTO testmd5 VALUES(4,小明,MD5(456798))-- 如何校验123456将用户传递进来的密码进行md5加密再比对加密后的值
SELECT * FROM testmd5 WHERE name小明 AND pwdMD5(456798)6、事务重点 要么都成功要么都失败 1、SQL执行 A给B转账 A 1000 ----转200 B 0
2、SQL执行 B收到A的钱 A 800 —B 200
必须1、2都成功交易才完成任意一个失败都导致失败 核心将一组SQL放在一个批次中执行 事务原则ACID原则 A(Atomicity)原子性 指事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生。 C(Consistency)一致性 指事务前后数据的完整性必须保持一致总数不变。 I(Isolation)隔离性 指多个用户并发访问数据库时数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离。 **脏读**一个事务读取了另外一个事务未提交的数据。**不可重复读**在一个事务内读取表中的某一行数据多次读取结果不同。这个不一定是错误只是某些场合不对**虚(幻)读**是指在一个事务内读取到了别的事务插入的数据导致前后读取不一致。一般是行影响多了一行 D(Durability)持久性 表示事务结束后的数据不随着外界原因例如服务器宕机或断电导致数据丢失 事务还没提交外界原因发生数据状态应复原事务已经提交外界原因发生数据状态应变为提交后的数据 执行事务 -- 事务
-- mysql是默认开启事务自动提交的
SET autocommit0 -- 自动提交关闭开启手动处理事务
SET autocommit1 -- 地洞提交开启默认-- 事务开启
START TRANSACTION -- 标记一个事务的开始从这个之后的sql都在同一个事务内-- 提交持久化成功
COMMIT
-- 回滚回到原来的样子失败
ROLLBACK-- 事务结束
SET autocommit 1 -- 开启自动提交SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销指定的保留点[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4PPgtFYJ-1690606873594)(D:\笔记\java笔记\MySQL.assets\image-20201119211503196.png)] 模拟场景 -- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shopCREATE TABLE account(id INT(3) NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,money DECIMAL(9,2) NOT NULL,PRIMARY KEY(id)
)ENGINEINNODB DEFAULT CHARSETutf8INSERT INTO account(name,money)
VALUES (A,2000.00),(B,10000.00)-- 模拟转账事务
SET autocommit 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务一组事务
UPDATE account SET moneymoney-500 WHERE nameA -- A减500
UPDATE account SET moneymoney500 WHERE nameB -- B减500
-- 如果成功
COMMIT; -- 提交事务
-- 如果失败
ROLLBACK; -- 回滚SET autocommit1 -- 恢复默认值在java中事务的实现
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VRLnRW0x-1690606873595)(D:\笔记\java笔记\MySQL.assets\image-20201120215020533.png)]
7、索引 MySQLT官方对索引的定义为索引index是帮助 MYSQLR高效获取数据的数据结构。提取句子主干就可以得到索引的本质索引是数据结构 7.1、索引的分类
一、单列索引一个索引只包含单个列但一个表中可以有多个单列索引。 这里不要搞混淆了 **普通索引(key / Index)**MySQL中基本索引类型没有什么限制允许在定义索引的列中插入重复值和空值纯粹为了查询数据更快一 点。 唯一索引(UNIQUE KEY)索引列中的值必须是唯一的但是允许为空值。 主键索引(PRIMARY KEY)是一种特殊的唯一索引不允许有空值。主键约束就是一个主键索引。
主键索引与唯一索引的区别 主键是一种约束唯一索引是一种索引两者在本质上是不同的。 主键创建后一定包含一个唯一性索引唯一性索引并不一定就是主键。 唯一性索引列允许空值而主键列不允许为空值。 主键索引在创建时已经默认为非空值 唯一索引了。 一个表最多只能创建一个主键索引但可以创建多个唯一索引。 主键更适合那些不容易更改的唯一标识如自动递增列、身份证号等。 主键可以被其他表引用为外键而唯一索引不能。
二、组合索引在表中的多个字段组合上创建的索引只有在查询条件中使用了这些字段的左边字段时索引才会被使用使用组合索引时遵循最左前缀集合。例如这里由id、name和age3个字段构成的索引索引行中就按id/name/age的顺序存放索引可以索引下面字段组合(idnameage)、(idname)或者(id)。如果要查询的字段不构成索引最左面的前缀那么就不会是用索引比如age或者nameage组合就不会使用索引查询。
三、全文索引(FullText)全文索引只有在MyISAM引擎上才能使用只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引就类似像平时的CtrlF的使用涉及了很多细节我们只需要知道这个大概意思。一般开发中不会用到全文索引因为其占用很大的物理空间和降低了记录修改性故较为少用。
创建索引的语句
1、直接创建索引
CREATE INDEX index_name ON table(column(length)); -- 创建普通索引
CREATE UNIQUE INDEX indexName ON table(column(length)); -- 创建唯一索引
CREATE FULLTEXT INDEX index_content ON article(content); -- 全文索引2、修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length)); -- 创建普通索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)); -- 创建唯一索引
ALTER TABLE table ADD INDEX name_city_age (name,city,age); -- 组合索引
ALTER TABLE article ADD FULLTEXT index_content(content); -- 全文索引3、创建表的时候创建索引
CREATE TABLE table (id int(11) NOT NULL AUTO_INCREMENT ,title char(255) CHARACTER NOT NULL ,content text CHARACTER NULL ,time int(10) NULL DEFAULT NULL ,PRIMARY KEY (id),INDEX index_name (title(length))
); -- 普通索引
CREATE TABLE table (id int(11) NOT NULL AUTO_INCREMENT ,title char(255) CHARACTER NOT NULL ,content text CHARACTER NULL ,time int(10) NULL DEFAULT NULL ,UNIQUE indexName (title(length))
); -- 唯一索引
CREATE TABLE table (id int(11) NOT NULL AUTO_INCREMENT ,title char(255) NOT NULL ,PRIMARY KEY (id)
); -- 主键索引
CREATE TABLE table (id int(11) NOT NULL AUTO_INCREMENT ,title char(255) CHARACTER NOT NULL ,content text CHARACTER NULL ,time int(10) NULL DEFAULT NULL ,PRIMARY KEY (id),FULLTEXT (content)
); -- 全文索引4、删除索引
DROP INDEX index_name ON table;5、显示所有索引
show index from table_name;6、基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后增加索引-- 显示所有的索引信息
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(张)索引的优缺点
1、优点创建索引可以大大提高系统的性能。 第一、通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。 第二、可以大大加快 数据的检索速度这也是创建索引的最主要的原因。 第三、可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。 第四、在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。 第五、通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。
2、缺点
增加索引有如此多的优点为什么不对表中的每一个列创建一个索引呢这是因为增加索引也有许多不利的一个方面:
第一、创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加。
第二、索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间。如果要建立聚簇索引那么 需要的空间就会更大。
第三、当对表中的数据进行增加、删除和修改的时候索引也要动态的维护这样就降低了数据的维护速度。
7.2、测试索引
目的创建一个表并插入百万条数据比较没有索引和有索引时在这一百万的数据中查找数据的耗时
创建测试表app_user
CREATE TABLE app_user (id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(50) DEFAULT COMMENT用户昵称,email VARCHAR(50) NOT NULL COMMENT用户邮箱,phone VARCHAR(20) DEFAULT COMMENT手机号,gender TINYINT(4) UNSIGNED DEFAULT 0COMMENT 性别0男;1:女,password VARCHAR(100) NOT NULL COMMENT 密码,age TINYINT(4) DEFAULT0 COMMENT 年龄,create_time DATETIME DEFAULT CURRENT_TIMESTAMP,update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEINNODB DEFAULT CHARSETutf8 COMMENT app用户表写一个自动插入一百万条数据的函数并执行。
-- 自写函数插入100万条数据
DELIMITER $$ -- 写函数之前必须要写$$
CREATE FUNCTION mock_data()
RETURNS INT
BEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE inum DO-- 插入语句INSERT INTO app_user(name,email,phone,gender,password,age) VALUES(CONCAT(用户,i),122025658qq.com,CONCAT(18,FLOOR(RAND()*((999999999-100000000)100000000))),FLOOR(RAND()2),UUID(),FLOOR(RAND()*100));SET ii1;END WHILE;RETURN i;
END;生成数据后运行函数 SELECT mock_data() -- 执行此函数 生成一百万条数据为每条数据添加索引
-- CREATE INDEX 索引名 on 表(字段)
-- 常用的索引命名方式id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(name);对比 SELECT * FROM app_user WHERE name用户500000 -- 查找数据EXPLAIN SELECT * FROM app_user WHERE name用户500000; -- 分析SQL执行的状况添加索引前查找数据
总耗时0.663sec总共执行条数993037[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bnRL4jbU-1690606873595)(D:\笔记\java笔记\MySQL.assets\image-20201120213934841.png)]
添加索引后查找数据
总耗时0sec总共执行条数1[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CIXwhwpo-1690606873595)(D:\笔记\java笔记\MySQL.assets\image-20201120214136629.png)]
如何删除索引
ALTER TABLE school.app_user DROP INDEX id_app_user_name; 或者直接右击表–改变表–索引–删除–保存
索引在小数据的时候用处不大处理大数据是区别十分明显
7.3、索引原则
索引不是越多越好不要对进程变动数据加索引小数据量的表不需要加索引索引一般加在常用来查询的字段上 索引的数据结构 Hash类型的索引
BtreeInnDB的默认数据结构
详细索引的博客https://blog.codinglabs.org/articles/theory-of-mysql-index.html
8、数据库备份
8.1、用户管理 SQLyog可视化管理 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gNIZbE6U-1690606873596)(D:\笔记\java笔记\MySQL.assets\image-20201120220201937.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xKRXsy2D-1690606873596)(D:\笔记\java笔记\MySQL.assets\image-20201120220251374.png)] SQL命令操作 用户表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tU8mrIJl-1690606873596)(D:\笔记\java笔记\MySQL.assets\image-20201120220432177.png)]
**本质**对这张表进行增删改查
创建用户语法CREATE USER
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER LONG IDENTIFIED BY 123456
CREATE USER jidao IDENTIFIED BY 1234567-- 修改密码修改当前用户密码
SET PASSWORDPASSWORD(123456)-- 修改密码修改制定用户密码
SET PASSWORD FOR jidao PASSWORD(121212)-- 重命名 RENAME USER 原名 TO 新名
RENAME USER wenlong TO wenlong2-- 用户授权 ALL PRIVILEGES 全部权限(或库表)
-- ALL PRIVILEGES除了给别人授权这个权利外其他都可以
GRANT ALL PRIVILEGES ON *.* TO wenlong2-- 查询制定用户的权限
SHOW GRANTS FOR wenlong2-- Root用户权限GRANT ALL PRIVILEGES ON *.* To rootlocalhost WITH GRANT OPTION-- 撤销权限 REVOKE 那些权限 在哪个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM wenlong2-- 删除用户
DROP USER wenlong28.2、MySQL备份
为什么要备份
保证重要的数据不丢失数据转移
数据库备份方式 直接拷贝物理文件 在SQLyog这种可视化工具中手动导出 在想要导出的表或者库中-------右键-------选择备份或导出 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IOBMUYox-1690606873597)(D:\笔记\java笔记\MySQL.assets\image-20201121161736727.png)] 在DOS界面使用命令行导出 mysqldump 命令行使用 导出 # 在DOS中导出单/多个表
# mysqldump -h 目标ip -u 用户名 -p 密码 数据库 表1 表2 表3…… 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student D:/a.sql#导出数据库
# mysqldump -h 目标ip -u 用户名 -p 密码 数据库 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school D:/a.sql导入 # 在DOS界面登录mysql用户的情况下
# 先登录
mysql -uroot -p123456
# 若导入的是表则切换到指定的数据库若导入的是数据库则直接导入即可
use school;
# 导入文件 source 文件路径source d:/a.sql# 没有登录的情况下
mysql -u用户名 -p密码 库名 备份文件路径9、权限管理
9.1、为什么需要设计
当数据库比较复杂的时候我们就需要设计
糟糕的数据库设计
数据冗余浪费空间数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】程序的性能差
良好的数据库设计
节省内存空间保证数据的完整性方便我们开发系统
软件开发中关于数据库的设计
分析需求分析业务和需要处理的数据库的需求概要设计设计关系图E-R图
设计数据库的步骤个人博客 收集信息分析需求 用户表用户登录注销、用户的个人信息写博客、创建分类分类表文章分类谁创建的文章表文章的信息评论表友链表友情信息自定义表系统信息某个关键的字或者一些主字段 keyvalue说说表发心情id···content··· 标识实体把需求落地到每个字段 标识实体之间的关系 写博客要用到的表user—blog创建分类要用到的表user -category关注用到的表user - user友链用到的表links评论用到的表user-user-blog
9.2、三大范式
为什么需要数据规范化
信息会重复更新会导致异常插入异常 无法正常显示信息 删除异常 丢失一下有效的信息 三大范式 第一范式1NF
原子性保证每一列不可再分
第二范式2NF
前提是要满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关而不能只与主键的某一部分相关主要针对联合主键而言
每张表只描述一件事情
第三范式3NF
前提是要满足第一范式 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关而不能间接相关
目的规范数据库的设计
规范性 和 性能的问题‘
关联查询的表不得超过三张表
考虑商业化的需求和目标成本用户体验!数据库的性能更加重要在规范性能的问题的时候需要适当的考虑一下 规范性故意给某些表增加一些冗余的手段。从多表查询中变为单表查询故意增加一些计算列从大数据量降低为小数据量的查询索引
10、JDBC
10.1、数据库驱动
驱动声卡、显卡、数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DvzNkeUq-1690606873597)(D:\笔记\java笔记\MySQL.assets\image-20201121190257749.png)]
我们的程序会通过 数据库 驱动和数据库打交道
10.2、JDBC
SUN公司为了简化开发人员对数据库的统一的操作提供了一个Java操作数据库规范俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说我们只需要掌握JDBC接口的操作即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nr6PRFTp-1690606873597)(D:\笔记\java笔记\MySQL.assets\image-20201121190723179.png)]
要学会JDBC要先知道两个包java.sql、javax.sql
还需要导入一个数据库驱动mysql-connector-java-5.1.47.jar到maven线上仓库搜索MySQL Connector/J即可找到
10.3、第一个JDBC程序 创建测试数据库 -- 创建第一个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,zssina.com,1980-12-04),
(2,lisi,123456,lisisina.com,1981-12-04),
(3,wangwu,123456,wangwusina.com,1979-12-04)1、在IDEA中创建一个普通项目
2、导入数据库驱动
创建文件夹lib并往里粘贴上要导入的mysql-connector-java-5.1.47.jar jar包 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E7UcciK5-1690606873598)(D:\笔记\java笔记\MySQL.assets\image-20201121194906238.png)]
3、新建java文件编写测试代码
package com.Long.lesson01;
import java.sql.*;public class JdbcFirstDemo {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1.加载驱动Class.forName(com.mysql.jdbc.Driver); //固定写法加载的驱动是先前导入好的驱动目录//2.添加用户信息和url(数据库的路径)//useUnicodetruecharacterEncodingutf8useSSLtrue//jdbc:mysql(协议)://主机地址:端口号/数据库名?参数1参数2参数3 String url jdbc:mysql://localhost:3306/jdbcstudy?useUnicodetruecharacterEncodingutf8useSSLfalse; //useUnicode使用Unicode字符集//useSSL使用安全协议用于兼容版本String username root; //你连接数据库时的用户名String password 123456; //连接数据库时的密码//3.通过用户信息和url(数据库的路径)连接到数据库。 Connection 代表连接数据库Connection connection DriverManager.getConnection(url, username, password);//4.连接数据库后创建Statement(陈述报告说明)对象返回statement对象 用于执行SQLStatement statement connection.createStatement();//5.通过执行查询来执行SQL对象并通过返回的结果对象集输出结果String sqlSELECT * FROM users;//execut执行 Query询问 执行查询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(pwd resultSet.getObject(PASSWORD));System.out.println(email resultSet.getObject(email));System.out.println(birth resultSet.getObject(birthday));System.out.println();}//6.释放资源resultSet.close();statement.close();connection.close();}
}注意useSSL就是使用JDBC跟你的数据库连接的时候你的JDBC版本与MySQL版本不兼容MySQL的版本更高一些在连接语句后加上“useSSL‘true’” 就可以连接到数据库了。更高版本。
步骤总结
1、加载驱动
2、连接数据库DriverManager驱动管理
3、获取执行sql的对象 Statement
4、执行完后获得返回的结果集
5、释放连接
解释 DriverManager // DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 不建议这样写因为Driver的源码已经注册了Driver这么写相当于注册了两次Class.forName(com.mysql.jdbc.Driver); //固定写法加载驱动这个类在添加的驱动对应的路径里面Connection connection DriverManager.getConnection(url, username, password);
//connection 代表数据库
//就可以设置数据库自动提交、事务提交、事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();URL String url jdbc:mysql://localhost:3306/jdbcstudy?useUnicodetruecharacterEncodingutf8useSSLfalse;
//mysql默认端口---3306
//jdbc:mysql(协议)://主机地址:端口号/数据库名?参数1参数2参数3
//Oracle默认端口-----1521
//jdbc:oracle:thin:localhost:1521:sidStatement执行SQL的对象还有PrepareStatement也是执行SQL的对象 String sqlSELECT * FROM users;//编写SQLstatement.executeQuery();//查询操作返回resultset结果集
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除都是用这个返回一个受影响的行数
statement.executeBatch();//放多个SQL进去同时执行ResultSet查询的结果集封装了所有的查询结果 获得指定的数据类型
resultSet.getObject();//在不知道列的类型的情况下用Object
resultSet.getString();//如果知道列类型就用指定的类型
resultSet.getInt();
resultSet.getDouble();
resultSet.getFloat();
resultSet.getDate();
·········遍历指针
resultSet.beforeFirst(); //遍历时指针移动到最前面
resultSet.afterLast(); //指针移动到最后面
resultSet.next(); //指针移动到下一个
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行释放资源 //6.释放资源
resultSet.close();
statement.close();
connection.close(); //好资源要关闭掉10.4、statement对象(重要)
Jdbc中的 statement对象用于向数据库发送SQL语句想完成对数据库的增删改查只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的 executeupdate方法用于向数据库发送增、删、改的sq语句 executeupdate执行完后将会返回一个整数即增刪改语句导致后的数据库有多少行数据发生了变化
Statement.executeQueryexecute执行 Query询问 执行查询方法用于向数据库发送査询语句executeQuery方法返回代表查询结果的Resultset对象。 CRUD操作–create 增 使用executeUpdate(String sql)方法完成数据添加操作示例操作
Statement stconn.createStatement();
String sql insert into user(……) values(……);
int num st.executeUpdate(sql);
if(num0){//如果受影响的行数0说明插入成功System.out.println(插入成功!);
}CRUD操作–delete 删 使用executeUpdate(String sql)方法完成数据删除操作示例操作
Statement stconn.createStatement();
String sql delete from user where id1;
int num st.executeUpdate(sql);
if(num0){//如果受影响的行数0说明删除成功System.out.println(删除成功!);
}CRUD操作–update 改 使用executeUpdate(String sql)方法完成数据修改操作示例操作
Statement stconn.createStatement();
String sql update user set name where name;
int num st.executeUpdate(sql);
if(num0){//如果受影响的行数0说明修改成功System.out.println(修改成功!);
}CRUD操作–read 查 使用executeUpdate(String sql)方法完成数据查操作
Statement stconn.createStatement();
String sql select * from user where id1;
ResultSet rs st.executeQuery(sql); //executeQuery返回的是结果集
while(rs.next()){//根据获取列的数据类型分别调用rs的相应的方法映射到java对象中
}代码实现 1、创建一个properties配置类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tXbvgY5S-1690606873598)(D:\笔记\java笔记\MySQL.assets\image-20201122170236435.png)]
drivercom.mysql.jdbc.Driver
urljdbc:mysql://localhost:3306/jdbcstudy?useUnicodetruecharacterEncodingutf8useSLLtrue
usernameroot
password1234562、提取工具类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fW2VZd7J-1690606873598)(D:\笔记\java笔记\MySQL.assets\image-20201122170337366.png)]
package com.Long.lesson02.utils;import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//用于简化连接JDBC除了查询操作外都可以从这个类中直接获取
public class JdbcUtils {private static String drivernull;private static String urlnull;private static String usernamenull;private static String passwordnull;static {try {InputStream in JdbcUtils.class.getClassLoader().getResourceAsStream(db.properties);Properties properties new Properties();properties.load(in);driverproperties.getProperty(driver);urlproperties.getProperty(url);usernameproperties.getProperty(username);passwordproperties.getProperty(password);//1.驱动只用加载一次Class.forName(driver);} catch (Exception e) {e.printStackTrace();}}//2.通过驱动管理获取连接对象public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, username, password);}//3.释放连接资源public static void release(Connection conn, Statement st, ResultSet rs) {if (rs ! null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st ! null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn ! null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}public static void release(Connection conn, Statement st){if (st!null){try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn!null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}
}3.1、测试代码————插入数据
package com.Long.lesson02;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {public static void main(String[] args) {Connection connnull;Statement stnull;ResultSet rsnull;try {conn JdbcUtils.getConnection();//获取连接对象stconn.createStatement();//创建statement对象用于执行sql语句String sqlINSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(5,liuliu,123456,254615qq.com,1995-11-11);int i st.executeUpdate(sql);if (i0){System.out.println(插入成功);}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,st,rs);}}
}3.2、测试代码————删除数据
package com.Long.lesson02;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {public static void main(String []args){Connection connnull;Statement stnull;ResultSet rsnull;try {conn JdbcUtils.getConnection();//获取连接对象stconn.createStatement();//创建statement对象用于执行sql语句String sqlDELETE FROM users WHERE id1;int i st.executeUpdate(sql);if (i0){System.out.println(删除成功);}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,st,rs);}}
}3.3、测试代码————修改数据
package com.Long.lesson02;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {public static void main(String[] args) {Connection connnull;Statement stnull;ResultSet rsnull;try {Connection connection JdbcUtils.getConnection();//获取链接对象stconn.createStatement();String sqlUPDATE users SET PASSWORD2231546 WHERE id2;int ist.executeUpdate(sql);if (i0){System.out.println(修改成功);}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,st,rs);}}
}3.4、测试代码————查询数据
package com.Long.lesson02;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {public static void main(String[] args) {Connection connnull;Statement stnull;ResultSet rsnull;try {conn JdbcUtils.getConnection();//获取连接对象stconn.createStatement();//创建statement对象用于执行sql语句String sql1select * from users;rs st.executeQuery(sql1);while (rs.next()){System.out.println(id:rs.getObject(id));System.out.println(NAME:rs.getObject(NAME));System.out.println(PASSWORD:rs.getObject(PASSWORD));System.out.println(email:rs.getObject(email));System.out.println(birthday:rs.getObject(birthday));System.out.println();}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,st,rs);}}
}10.5、SQL注入问题
package com.Long.lesson02;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** sql存在漏洞当程序实现为用户输入用户名和密码登录时* 由于后台是使用sql语句所以用户可以用拼接条件“or”来实现*/
public class SQL注入 {public static void main(String[] args) throws SQLException {//Sql注入时由于使用的方法实际上是拼接字符串所以可以修改条件login( or 11, or 11);//用户这样输入后后台实际上输入的是//SELECT * FROM users WHERE NAME or 11 AND PASSWORD or 11//即无论如何都为真就可以查看数据库中所有用户的所有信息}public static void login(String username,String password) throws SQLException {Connection connnull;Statement stnull;ResultSet rsnull;try {connJdbcUtils.getConnection();stconn.createStatement();//原意为用户输入用户名和密码来查看对应用户的所有信息// SELECT * FROM users WHERE NAME用户自己的用户名 AND PASSWORD用户自己的密码String sqlSELECT * FROM users WHERE NAMEusername AND PASSWORDpassword;rs st.executeQuery(sql);while (rs.next()){System.out.println(id:rs.getObject(id));System.out.println(NAME:rs.getObject(NAME));System.out.println(PASSWORD:rs.getObject(PASSWORD));System.out.println(email:rs.getObject(email));System.out.println(birthday:rs.getObject(birthday));System.out.println();}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils.release(conn,st,rs);}}
}10.6、PreparedStatement对象
PreparedStatement可以防止SQL注入而且效率更高
1、新增
package com.Long.lesson03;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题所以使用PreparedStatement来解决这个问题*/
public class TestInsert {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;try {connJdbcUtils.getConnection();//主要区别//使用‘?’占位符代替参数String sql INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?);//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setInt(1,6); //给第1个问号也就是id赋值为5pst.setString(2,行到1人); //给第2个问号 (也就是name)赋值为 行到1人pst.setString(3,123456); //给第3个问号 (也就是password)赋值为 123456pst.setString(4,123465qq.com); //给第4个问号 (也就是email)赋值为 123465qq.com//注意点sql.Date 应用于数据库 java.sql.Date()// util.Date 应用于java new Date().getTime获取时间戳pst.setDate(5,new java.sql.Date(new Date().getTime())); //给给第5个问号 (也就是birthday)赋值为 行到1人//执行int i pst.executeUpdate(); //不用传参数因为前面已经预编译了可以直接执行if (i0){System.out.println(插入数据成功);}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils.release(conn,pst);}}
}2、删除
package com.Long.lesson03;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题所以使用PreparedStatement来解决这个问题*/
public class TestDelete {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;try {connJdbcUtils.getConnection();//主要区别//使用‘?’占位符代替参数String sqlDELETE FROM users WHERE id?;//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setInt(1,5);//执行不用传参数因为前面已经预编译了可以直接执行int i pst.executeUpdate();if (i0){System.out.println(删除成功);}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils.release(conn,pst);}}
}3、修改
package com.Long.lesson03;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题所以使用PreparedStatement来解决这个问题*/
public class TestUpdate {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;try {conn JdbcUtils.getConnection();//主要区别//使用‘?’占位符代替参数String sqlUPDATE users SET NAME? WHERE id?;//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setObject(1,张三);pst.setInt(2,2);//执行不用传参数因为前面已经预编译了可以直接执行int i pst.executeUpdate();if (i0){System.out.println(更新成功);}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils.release(conn,pst);}}
}4、查询
package com.Long.lesson03;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题所以使用PreparedStatement来解决这个问题*/
public class TestSelect {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;ResultSet rsnull;try {conn JdbcUtils.getConnection();//主要区别//使用‘?’占位符代替参数String sqlSELECT * FROM users WHERE NAME?;//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setObject(1,张三);//执行不用传参数因为前面已经预编译了可以直接执行rs pst.executeQuery();if (rs.next()){System.out.println(name:rs.getObject(NAME));}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils.release(conn,pst,rs);}}
}5、防止SQL注入
package com.Long.lesson03;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class 防止SQL注入 {public static void main(String[] args) {login(张三,123456);}public static void login(String username,String password){Connection connnull;PreparedStatement pstnull;ResultSet rsnull;try {conn JdbcUtils.getConnection();//主要区别//使用‘?’占位符代替参数String sqlSELECT * FROM users WHERE NAME? and PASSWORD?;//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setObject(1,username);pst.setObject(2,password);//执行不用传参数因为前面已经预编译了可以直接执行rs pst.executeQuery();if (rs.next()){System.out.println(id:rs.getObject(id));System.out.println(NAME:rs.getObject(NAME));System.out.println(PASSWORD:rs.getObject(PASSWORD));System.out.println(email:rs.getObject(email));System.out.println(birthday:rs.getObject(birthday));System.out.println();}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils.release(conn,pst,rs);}}
}10.7、使用IDEA连接数据库 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uXwtkJg5-1690606873599)(D:\笔记\java笔记\MySQL.assets\image-20201122202514346.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tgFG64rQ-1690606873599)(D:\笔记\java笔记\MySQL.assets\image-20201122203252944.png)]
连接成功后可以选择数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DmMmthyK-1690606873599)(D:\笔记\java笔记\MySQL.assets\image-20201122203532456.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-89jpI8nU-1690606873600)(D:\笔记\java笔记\MySQL.assets\image-20201122203637091.png)]
双击数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j04sYn85-1690606873600)(D:\笔记\java笔记\MySQL.assets\image-20201122203903820.png)]
4.数据库编写区
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JRRFdGSv-1690606873600)(D:\笔记\java笔记\MySQL.assets\image-20201122211045244.png)]
连接失败查看原因 有可能没有导入数据库驱动 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uoqkE8aW-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122211228947.png)] 版本不对或删除了某些默认设置 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2FjufHuz-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122211438515.png)]
10.8、事务
要么都成功要么都失败 用实现 开启事务conn.setAutoCommit(false);一组业务执行完毕提交事务可以在catch语句中显示的定义回滚语句但默认失败就会回滚
完整代码
package com.Long.lesson04;import com.Long.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;/*** author Eric* date 2020-11-22-21:23*/
public class TestTransaction01 {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;try {connJdbcUtils.getConnection();//关闭数据库的自动提交,自动回开启事务conn.setAutoCommit(false);String sql1update account set moneymoney-100 where nameA;pstconn.prepareStatement(sql1);int i pst.executeUpdate();if (i0){System.out.println(A的数据修改了);}//int a1/0;String sql2update account set moneymoney100 where nameB;pstconn.prepareStatement(sql2);int j pst.executeUpdate();if (j0){System.out.println(B的数据修改了);}//事务成功则提交数据conn.commit();System.out.println(事务成功);} catch (SQLException throwables) {//若事务失败默认回滚try {conn.rollback();} catch (SQLException e) {e.printStackTrace();}throwables.printStackTrace();}finally {JdbcUtils.release(conn,pst);}}
}10.9、数据库连接池
每次连接时会发生数据库连接----执行完毕-----释放 十分浪费资源 池化技术准备一些预先的资源过来就连接预先准备好的 作用预先准备好的连接池就好比银行的服务员只要有需求连接需要服务就可以直接用进行服务否则等待
最小连接数 一般赋值与 常用连接数 一致
最大连接数最高承载上限超过的话连接就只能等待
编写连接池实现一个接口 DataSource 开源数据源实现 DBCP
C3P0
Druid阿里巴巴
使用了这些数据库连接池之后我们在项目开发中就不需要编写连接数据库的代码了 DBCP 需要先导入到IDEA中的jar包
commons-dbcp-1.4.jarcommons-pool-1.6.jar [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F7ZLDbDb-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122235416201.png)]
1、配置properties文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fdMtEThb-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122235503723.png)]
#连接设置
driverClassNamecom.mysql.jdbc.Driver
urljdbc:mysql://localhost:3306/jdbcstudy?useUnicodetruecharacterEncodingutf8useSSLtrue
usernameroot
password123456#!-- 初始化连接 --
initialSize10#最大连接数量
maxActive50#!-- 最大空闲连接 --
maxIdle20#!-- 最小空闲连接 --
minIdle5#!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --
maxWait60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样【属性名property;】
#注意user 与 password 两个属性会被明确地传递因此这里不需要包含他们。
connectionPropertiesuseUnicodetrue;characterEncodingUTF8#指定由连接池所创建的连接的自动提交auto-commit状态。
defaultAutoCommittrue#driver default 指定由连接池所创建的连接的只读read-only状态。
#如果没有设置该值则“setReadOnly”方法将不被调用。某些驱动并不支持只读模式如Informix
defaultReadOnly#driver default 指定由连接池所创建的连接的事务级别TransactionIsolation。
#可用值为下列之一详情可见javadoc。NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolationREAD_UNCOMMITTED2、提取工具类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aaTdkxyn-1690606873602)(D:\笔记\java笔记\MySQL.assets\image-20201122235853850.png)]
package com.Long.lesson05.utils;import org.apache.commons.dbcp.BasicDataSourceFactory;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//用于简化连接JDBC除了查询操作外都可以从这个类中直接获取
public class JdbcUtils_DBCP {private static DataSource dataSourcenull;static {try {InputStream in JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream(dbcpconfig.properties);Properties properties new Properties();properties.load(in);//创建数据源 工厂模式---创建dataSource BasicDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}//2.通过驱动管理获取连接对象public static Connection getConnection() throws SQLException {return dataSource.getConnection(); //从数据源中获取连接}//3.释放连接资源public static void release(Connection conn, Statement st, ResultSet rs) {if (rs ! null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st ! null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn ! null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}public static void release(Connection conn, Statement st){if (st!null){try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn!null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}
}3、测试查询数据库
package com.Long.lesson05;import com.Long.lesson05.utils.JdbcUtils_DBCP;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题所以使用PreparedStatement来解决这个问题*/
public class TestDBCP {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;ResultSet rsnull;try {conn JdbcUtils_DBCP.getConnection();//主要区别//使用‘?’占位符代替参数String sqlSELECT * FROM users WHERE NAME?;//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setObject(1,张三);//执行不用传参数因为前面已经预编译了可以直接执行rs pst.executeQuery();if (rs.next()){System.out.println(id:rs.getObject(id));System.out.println(NAME:rs.getObject(NAME));System.out.println(PASSWORD:rs.getObject(PASSWORD));System.out.println(email:rs.getObject(email));System.out.println(birthday:rs.getObject(birthday));System.out.println();}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils_DBCP.release(conn,pst,rs);}}
}C3P0 需要用到的jar包
c3p0-0.9.5.5.jarmchange-commons-java-0.2.19.jar [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bGzAu8zB-1690606873602)(D:\笔记\java笔记\MySQL.assets\image-20201123000356788.png)]
1、配置c3p0-config.xml文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-99hwNgae-1690606873603)(D:\笔记\java笔记\MySQL.assets\image-20201123000429788.png)]
?xml version1.0 encodingUTF-8?
c3p0-config!--c3p0的缺省默认配置如果在代码中ComboPooledDataSource dsnew ComboPooledDataSource();这样写就表示使用的是c3p0的缺省默认--default-configproperty namedriverClasscom.mysql.cj.jdbc.Driver/propertyproperty namejdbcUrljdbc:mysql://localhost:3306/jdbcstudy?userUnicodetrueamp;characterEncodingutf8amp;uesSSLtrueamp;serverTimezoneUTC/propertyproperty nameuserroot/propertyproperty namepassword123456/propertyproperty nameacquiredIncrement5/propertyproperty nameinitialPoolSize10/propertyproperty nameminPoolSize5/propertyproperty namemaxPoolSize20/property/default-config!--c3p0的缺省默认配置如果在代码中ComboPooledDataSource dsnew ComboPooledDataSource();这样写就表示使用的是c3p0的缺省默认--name-config nameMySQLproperty namedriverClasscom.mysql.cj.jdbc.Driver/propertyproperty namejdbcUrljdbc:mysql://localhost:3306/jdbcstudy?userUnicodetrueamp;characterEncodingutf8amp;uesSSLtrueamp;serverTimezoneUTC/propertyproperty nameuserroot/propertyproperty namepassword123456/propertyproperty nameacquiredIncrement5/propertyproperty nameinitialPoolSize10/propertyproperty nameminPoolSize5/propertyproperty namemaxPoolSize20/property/name-config/c3p0-config2、提取工具类配置JdbcUtils_C3P0
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YZlOH59n-1690606873603)(D:\笔记\java笔记\MySQL.assets\image-20201123000531274.png)]
package com.Long.lesson05.utils;import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;//用于简化连接JDBC除了查询操作外都可以从这个类中直接获取
public class JdbcUtils_C3P0 {private static DataSource dataSourcenull;static {try {//这些都可以用配置好的c3p0-config.xml文件直接获取别人的
// dataSourcenew ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();//创建数据源 工厂模式-- 创建dataSourcenew ComboPooledDataSource(MySQL);} catch (Exception e) {e.printStackTrace();}}//2.通过驱动管理获取连接对象public static Connection getConnection() throws SQLException {return dataSource.getConnection(); //从数据源中获取连接}//3.释放连接资源public static void release(Connection conn, Statement st, ResultSet rs) {if (rs ! null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st ! null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn ! null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}public static void release(Connection conn, Statement st){if (st!null){try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (conn!null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}
}3、测试
package com.Long.lesson05;import com.Long.lesson05.utils.JdbcUtils_C3P0;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题所以使用PreparedStatement来解决这个问题*/
public class TestC3P0 {public static void main(String[] args) {Connection connnull;PreparedStatement pstnull;ResultSet rsnull;try {conn JdbcUtils_C3P0.getConnection();//主要区别//使用‘?’占位符代替参数String sqlSELECT * FROM users WHERE NAME?;//预编译sql先写sql然后不执行pstconn.prepareStatement(sql);//手动给参数赋值pst.setObject(1,张三);//执行不用传参数因为前面已经预编译了可以直接执行rs pst.executeQuery();if (rs.next()){System.out.println(id:rs.getObject(id));System.out.println(NAME:rs.getObject(NAME));System.out.println(PASSWORD:rs.getObject(PASSWORD));System.out.println(email:rs.getObject(email));System.out.println(birthday:rs.getObject(birthday));System.out.println();}} catch (SQLException throwables) {throwables.printStackTrace();}finally {JdbcUtils_C3P0.release(conn,pst,rs);}}
}结论 无论使用什么数据源本质还是一样的DataSource接口不会变方法就不会变
补充
1、修改引擎
ALTER TABLE 表名 ENGINE引擎名
2、创建/删除复合主键multiple key
如果项设置复合主键复合主键的特点是同时创建、同时删除所以需要把主键删除但是这里设置了自增需要先把自增删除才可以删除主键如果不先删除自增而直接删除主键的话会报错
先删除自增即直接更改字段的属性没有自增请跳过 ALTER TABLE 表名 MODIFY 字段名 字段属性····
这里我们创建复合主键的话把name字段上的普通索引先删除掉再给字段名1字段名2字段同时创建复合主键 DROP INDEX 字段名 ON 表名 ALTER TABLE STU ADD PRIMARY KEY(字段名1字段名2)
