第三十五讲——MySQL数据库（DataBase）
第三十五讲——MySQL数据库（DataBase）
目录
前端： 页面展示，数据
后台： 连接点、链接数据库 JDBC ；链接前端（控制视图跳转，给前端传递数据）
数据库： （存数据、txt、excel、word） 500万条数据大于要优化
数据库分类
关系型数据库： (SQL)
- MySQL、 Oracle、Sql Server、DB2、SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表。.....
非关系型数据库： （NoSQL） not only
- Redis、MongDB
- 非关系型数据库，对象存储
DBMS(数据库管理系统）
- 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据
- MySQL，数据库管理系统
MySQL——简介
MySQL 是一个关系型数据库管理系统
前世： 瑞典 MySQL AB 公司
今生： 属于 Oracle 旗下产品
MySQL 是最好的 RDBMS （关系型数据库管理系统）应用软件之一，开源的数据库软件；
体积小、速度快、总体拥有成本低、招人成本比较低
安装建议：
- 尽量不用 exe 安装，会走注册表，存在卸载遗留问题
- 尽可能用 压缩包下载
1——安装 MySQL 5.7.19
- 官网下载 zip 点击下载
- 把这个包放到自己的电脑环境目录下 我的(D:\MySQL_5.7.19\mysql-5.7.19)
- 新建配置环境变量 在 path 中 到 MyQSL bin目录
- 新建 mysql 配置文件 ini （ zip 是没有ini 的要自己创建一个）
配置
ini
[mysq1d]
basedir=D:\MySQL_5.7.19\mysql-5.7.19\
datadir=D:\MySQL_5.7.19\mysql-5.7.19\data
port=3306
skip-grant-tab1es
- 启动管理员模式下的CMD,并将路径切换至 mysql 下的bin目录,然后输入mysqld -install (安装mysql)
地址不管，反正 成功是这个样子
-
再输入
mysqld --initialize-insecure --user=mysql初始化数据文件 (这时候会自动生成一些文件，data 、、、)报错的话
![]()
-
启动 MySQL
net start mysql
报错的话
就能看见 MySQL 在任务管理器中 正在运行
- 然后再次启动 mysql 然后用命令
mysql -u root -p(u : user; p : password)进入mysql 管理界面(密码可为空 注意！ p 后面不能添加任意字符,会产生密码) - 进入页面修改 root 密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';(最后输入flush privileges;刷新权限) - 如果你重新卸载安装过什么的 建议去 sqlyog 重新修改密码后再来试试
- 注释掉 myini.ini 最后一句内容
#skip-grant-tables - 重启 MySQL 即可正常使用
net stop mysqlnet start mysql
启动 自己账号的 mySQL
mysql -u root -p
mysql -u root -p123456启动后就变为 mysql 前缀 要用
exit退出 （要先退出自己 mysql 的账号 才能关闭 mysql 同样 要先启动 mysql 才能登入自己账号）
出现大段文字 说明成功 success！
假设没有使用管理员权限打开，可能会引起报错
如果安装出错 可以选择重新安装 ：
sc delete mysql清空服务 （没事别瞎点）
2——安装 SQLyog
为了方便操作
MySQL数据库而开发的一种图形化管理工具
- 安装 —— 百度云下载 SQLyog-12.0.9-0.x64
- 打开并链接数据库
- 新建一个数据库 school
每一次 sqlyog 的执行操作，本质就是对应了一个 sql ，可以在软件的历史记录中查看
- 新建一张表 student
字段 ： id、 name 、 age、
- 查看以及储存表 （选择要查看的表，右键打开表）
卸载 sqlyog
-
卸载 sqlyog
![image-20221204152329057]()
-
windows+R运行“regedit”文件，打开注册表。 删除 Mysql 文件夹
计算机\HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL
3—— 基本的命令行操作
命令行链接：
ctrl + C 强行停止
mysql -u root -p; -- 链接数据库
flush privileges; --刷新权限
-----------------------------------
-- 所有的语句都应该用 ; 结尾
-- 以下都是 sql 语句
show databases; -- 查看所有数据库
use school; -- 切换数据库 use 数据库名
--
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中的某个表的信息
create database westos; -- 创建一个数据库
exit; -- 退出链接
-- 单行注释
/*
多行注释
*/
4——操作数据库
操作数据库—>操作数据库中的表—> 操作数据库中表的数据
MySQL 不区分大小写
操作数据库
中括号可选操作、大括号必选操作
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
- 删除数据库
DROP DATABASE [IF EXISTS] westos;
- 使用数据库
-- 如果表或字段名是一个特殊字符，就需要添加: tab 键上面的 `
use school; -- 切换数据库
- 查看数据库
SHOW DATABASES; -- 查看所有数据库
对照 sqlyog 可视化历史记录查看 sql
固定的语法或关键字必须要强行记住
数据库的列类型
数值
| tinyint | 小数据 | 1 个字节 |
|---|---|---|
| smallint | 较小数据 | 2 个字节 |
| mediumint | 中等数据 | 3 个字节 |
| int | 标准数据 | 4 个字节 |
| bigint | 较大数据 | 8 个字节 |
| float | 浮点数 | 4 个字节 |
| double | 浮点数 | 8 个字节 |
| decimal | 字符串形式的浮点数 | 一般用于金融计算 |
字符串
| char | 字符串固定大小 | 0~255 |
|---|---|---|
| varchar | 可变字符串 | 0~65535 |
| tinytext | 微型文本 | 2^8-1 |
| text | 文本串(大型文本） | 2^16-1 |
时间日期
| date | YYYY-MM-DD | 日期格式 |
|---|---|---|
| time | HH：MM：SS | 时间格式 |
| datetime | YYYY-MM-DD HH：MM：SS | |
| year | 表示年份 |
null
没有值
属性
| Unsigned | 无符号的整数 | 声明该列不能为负数 |
|---|---|---|
| zerofill | 0填充 | 位数3 | 3 = 003 |
| 自增 | 自动在上一次记录中自增 | 通常用来设置唯一主键 index 必须整数 自定义自增初始和步长 |
| 非空 | 不赋值会报错 | 但是本身会给一个空字符串 默认为null |
| 默认 | 默认值 |
拓展
/* 每个表都必须存在以下五个字段， 做项目用，表示一个记录存在意义
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
创建数据库表
SQL create table
-- 目标： 创建一个 school 数据库
-- 创建学生表（列，字段） 使用 SQL 创建
-- 学号 int 登入密码 varchar(20) 姓名 ，性别varchar（2），出生日期（datatime），家庭住址，email
-- 字表名称和字段 尽量使用 `` 飘 括起来 （字符串用 单引号！！！ 会变红色就对）
-- AUTO INCREMENT 自增
-- 所有的语句后面加，一般一个表只有一个唯一的主键
-- 有语法错误 注意标点问题 以，结尾 最后一句不用
CREATE TABLE IF NOT EXISTS `teacher` (
-- column name | 数据类型 | 不为空 | 默认值 | 注释
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(10) NOT NULL DEFAULT'123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME NULL COMMENT '出生日期',
`email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
FLUSH PRIVILEGES; -- 刷新权限
主要格式；
create table if not exists `teacher`( -- 列名 | 数据类型 | 不为空 | 注释 | , `id` int(4) not null comment '学号', -- ..... primary key （`id`） -- 特殊属性 -- 自增 auto_increment 默认值 default 主键 primary key （`id`） )
常用命令：
SHOW CREATE DATABASE school; -- 查看创建数据库的语句 SHOW CREATE TABLE student; -- 查看 student 数据表的定义语句 DESC student; -- 显示表的结构
关于数据引擎
/*
innodb 默认使用
myisam 早些年使用
*/
| InnoDB | MYISAM | |
|---|---|---|
| 事务支持 | 支持 | 不支持 |
| 数据行锁定 | 支持 （表锁） | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表空间的大小 | 较大，约为两倍 | 较小 |
区别：
- MYISAM 节约空间, 速度较快
- innoDB 安全性高，事务处理好，多表多用户操作
在物理空间存在的位置 ： 在 Data 文件下 本质还是文件
修改删除表
修改
-- 修改表名
ALTER TABLE `teacher02` RENAME AS teacher01; -- 注意 ` 有时候可以用 有时不能用
-- 增加表的字段
ALTER TABLE teacher01 ADD age INT(3) DEFAULT 123;
-- 修改表的字段 (重命名，修改约束) 面试可能会有 modify 与change 的区别
ALTER TABLE teacher01 MODIFY age VARCHAR(3); -- modify 用于修改约束，
ALTER TABLE teacher01 CHANGE age age01 INT(3); -- change 用于重命名 也可以修改约束
-- 删除表的字段
ALTER TABLE teacher01 DROP age01; -- 注意标点！！！
删除表
-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher01;
- 字段 用 `` 包裹
5.——MySQL 数据管理
外键（了解即可）
方式一
KEY `FK_gradeid`(`classid`), -- 1. 声明一个外键 (绑定到clssid) CONSTRAINT `FK_gradeid` FOREIGN KEY (`classid`) REFERENCES `grade`(`gradeid`) -- 2. 添加约束 (外键名) 外键 （要绑定的当前键） 引用 `外键表` (`链接列`)
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '班级id',
`gradeName` INT(10) NOT NULL COMMENT '班级名称',
PRIMARY KEY(`gradeid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8
-- 学生表的 gradeid 字段要去引用年级表的 gradeid
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
`classid` INT(10) NOT NULL COMMENT '班级',
PRIMARY KEY(`id`),
KEY `FK_gradeid`(`classid`), -- 1. 声明一个外键 (绑定到clssid)
CONSTRAINT `FK_gradeid` FOREIGN KEY (`classid`) REFERENCES `grade`(`gradeid`)
-- 2. 添加约束 (外键名) 外键 （要绑定的当前键） 引用 `外键表` (`链接列`)
) ENGINE= INNODB DEFAULT CHARSET = utf
删除的话要先删除 从表 才能删除 主表（被引用的表）
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '班级id',
`gradeName` INT(10) NOT NULL COMMENT '班级名称',
PRIMARY KEY(`gradeid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8
-- 学生表的 gradeid 字段要去引用年级表的 gradeid
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
`classid` INT(10) NOT NULL COMMENT '班级',
PRIMARY KEY(`id`)
) ENGINE= INNODB DEFAULT CHARSET = utf8
-- 第二种方式 分开写
ALTER TABLE `student` -- 修改某个表
-- ADD CONSTRAINT [外键约束名] FOREIGN KEY[作为外键的列] REFERENCES [被链接的表](被链接的字段)
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`classid`) REFERENCES `grade`(`gradeid`);
物理外键，数据库级别操作，不建议使用，不便于修改 （一般只用程序来实现外键）
- 最好的数据库 只有行（数据）和列（字段）
DML 语言 （全部记住）
数据库意义： 数据存储 、数据管理
DML 语言 ：数据操作语言
- insert
- update
- delete
- query
增
-- 插入语句（添加）
-- insert into 表名 ([字段名1,字段名2,字段名3]) values ('值1')，('值2')，('值3');
INSERT INTO `grade`(`gradeName`) VALUES(12); -- 字符串用 ''
-- 由于主键自增 我们可以省略
INSERT INTO `grade` VALUES(12); -- （如果不写表的字段，就会一一匹配）但是必须全部填写
-- 插入语句 ，一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradeName`) VALUES ('66'),('88');
INSERT INTO `teacher`(`name`,`sex`) VALUES ('李四','男'),('王五','男'),('赵六','男');
注意中英文标点！！！
删
detele 命令
-- 语法: delete from 表名 where 条件
-- delete 删除数据
DELETE FROM `teacher`; -- 这样写会删除所有数据！！！
-- 删除指定数据
DELETE FROM `teacher` WHERE id=19;
truncate 命令
truncate `student`;
truncate FROM `teacher` WHERE id=19;
delete 与 truncate 区别
- 相同点 : 都能删除数据，不会删除表结构
- 不同：
- truncate
自增归零不会影响事务
- truncate
delete 删除问题 重启数据库 现象
- innodb 自增会从 1 开始 （存在内存，断电及失）
- myisam 继续从上一个自增量开始 （存在文件中，不会丢失）
改
-- 修改 teacher 名字
-- update 表名 set 键 where 条件
UPDATE `teacher` SET NAME = '李四22' WHERE id = 1;
-- 不指定条件的情况下，会改动所有表！
UPDATE `teacher` SET NAME = '12';
-- 修改多个属性 逗号隔开
UPDATE `teacher` SET `name` = 'xxz',`sex` = 'boy' WHERE id = 12;
-- where 后面跟条件 < > <= >= !=
-- between 在什么之间
UPDATE `teacher` SET NAME = 'yj',`sex` ='girl' WHERE id BETWEEN 2 AND 5;
-- and 与运算
UPDATE `teacher` SET NAME = 'hz' , `sex` = 'girl' WHERE `name` = '12' AND id<15;
-- or 或运算
UPDATE `teacher` SET NAME = '小灰机', `sex` = '女' WHERE `name` = '12' OR id=1;
-- value 可以是具体的值 也可以是一个变量
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '长江七号';
6——DQL 查询数据（重重点）
Data Query LANGUAGE : 数据查询语言
- 所有的查询操作都用它 select
- 简单的查询，复杂的查询它都能做
数据库中最核心，最重要的语句- 使用频率最高的语句
测试用sql数据库
-- test 1111111111111111111111111111111111
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- test 2
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- test03
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 插入成绩数据 这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- test04
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
简单查询语句
-- 查询全部的学生 select 字段 from 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,StudentName FROM student -- 只查看学号名字
-- 别名， 给结果起一个名字 AS 可以给字段起别名， 也可以给表起别名
SELECT `StudentNo` AS '学号',`StudentName` AS '学生姓名' FROM student AS 学生
-- 合并函数
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
去重数据库 及表达式
-- 查询那些学生参加了考试 成绩
SELECT * FROM result
SELECT `StudentNo` FROM result -- 查询有那些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- DISTINCT 发现重复数据，去重
SELECT VERSION() -- 查询版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算 (表达式)
SELECT @@auto_increment_increment -- 查询自增步长 (变量)
-- 学生考试成绩 +1查看
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM `result`
where 之逻辑运算符
-- ======================== where =======
SELECT studentno ,studentresult FROM result
-- 查询成绩在 70 ~ 80 的人
-- and &&
SELECT studentno,studentresult FROM result WHERE studentresult >=60 AND studentresult <=80
-- 区间查询 （between）
SELECT studentno, studentresult FROM result WHERE studentresult BETWEEN 60 AND 80
-- not !=
SELECT studentno ,studentresult FROM result WHERE NOT studentresult=70
-- or ||
SELECT subjectno AS 学科 ,studentresult AS 成绩 FROM result WHERE studentresult=80 OR studentresult=68
模糊查询
比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | |
| is not null | a is not null | |
| between | a between a and b | |
| like | a like b | 相似匹配 |
| in | a in (.....) | 在什么中 |
-- ========== 模糊查询 ==========
-- 查询姓刘的同学
-- like 结合 %（表示0到任意个字符） _(一个字符) 如果是两个字就两个_
SELECT studentno, studentname FROM student WHERE studentname LIKE '张%'
-- 查询姓张的同学，后面只有一个字的
SELECT studentno , studentname FROM student WHERE studentname LIKE '赵_' -- rg
-- 查询名字包含伟字的同学 %伟% 开头伟 伟% 结尾伟 %伟
SELECT studentno , studentname FROM student WHERE studentname LIKE '%伟%'
-- ========== in (具体的一个或者多个值)======
-- 查询 classhour 为110和100 的 subjectname
SELECT subjectname ,subjectno ,classhour FROM SUBJECT WHERE classhour IN(110,100);
-- ============ null not null ===============
-- if not null 不为空
-- not null 不为空
SELECT `gradeid` , `gradename` FROM grade WHERE gradeid IS NOT NULL
链表查询 join查询
-- ========= 链表查询 jion =============
-- inner join
-- 查询参加考试的同学 （学号、姓名、科目编号、分数）
-- 用到两个表 student result 共同键 studentNo
SELECT * FROM student
SELECT * FROM result
/*思路
1. 分析查询的字段来自那些表，链接查询
2. 确定使用哪种连接查询？
确定交叉点(这两个表中，那个数据是相同的)
判断的条件： 学生表中 studentNo = 成绩表 studentNo
*/
-- 这里的 studentno 必须声明 为s的 要不然会报错 error ambiguous
-- 重点是 s.xxx 两个表都有结果的情况下 返回哪个表的数据
SELECT s.studentNo , studentname,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentNo -- where 换 on 也成立
-- right Join
SELECT s.studentNo ,studentName,Subjectno,studentResult
FROM student AS s -- As 可以忽略
RIGHT JOIN result AS r
ON s.studentno = r.studentNo
-- left join
SELECT s.studentNo, studentName ,subjectNo,studentResult
FROM student s
LEFT JOIN result r
ON s.studentNo = r.studentNo
-- 如下图所示： left join 相当于 在两个表中查找 id 以左表为准，
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right | 会从右表中返回所有的值，即使左表中没有匹配值 |
-- 查看缺考的同学
SELECT s.studentNo, studentName ,subjectNo,studentResult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentResult IS NULL
练习题 链表查询
-- 查询学员所属的年级 (学号、学生的姓名、年级名称) student 和grade
SELECT s.studentNo,studentName,gradename
FROM student AS s
LEFT JOIN grade AS g
ON s.gradeid = g.gradeid
-- 查询科目所属的年级 (科目名称, 年级名称) subject 和 grade
SELECT subjectname, gradename
FROM `subject` s
LEFT JOIN `grade` g
ON s.gradeid = g.gradeid
-- 查询了参与 高等数学-1 考试同学的信息: 学号、学生姓名、科目名、分数
SELECT s.studentno,studentname,subjectName,studentresult
FROM `student` AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
LEFT JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = "高等数学-1"
思考题 三表联查
-- 思考题 (查询参加考试同学信息: 学号、学生姓名、科目名、分数)
-- 三表查询
-- 多表查询思路 就是从两个表开始查
SELECT s.studentNo,studentName,subjectName,studentResult
FROM result AS r
LEFT JOIN student AS s
ON r.studentNo = s.studentNo
-- 在前一个表查询的基础上查询
INNER JOIN `subject` AS sub
ON sub.subjectNo = r.subjectno
result
自连接（理解）
自连接教程所用(数据库)
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','办公信息');
原表
pid 父类id 1 应该是最高级
自己的表和自己的表连接，核心： 一张表拆为两张一样的表即可
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 3 | 6 | wed开发 |
| 5 | 7 | ps技术 |
| 2 | 8 | 办公信息 |
操作 ： 查询父类对应子类的关系
比如 category 为 3 的信息技术 下有 数据库和wed开发
| 父类 | 子类 |
|---|---|
| 软件开发 | 数据库 |
| 软件开发 | wed |
| 美术设计 | ps技术 |
| 信息技术 | 办公信息 |
-- 查询父子信息 : 把一张表看为两个一模一样的表
SELECT a.categoryname AS '父',b.categoryname AS '子'
FROM category AS a,category AS b
WHERE a.categoryid = b.pid
result
分页和排序
排序
-- 排序 ： 升序 asc 降序 desc
-- order by 通过哪个字段排序，怎么排
-- 查询的结果根据，成绩降序，排序
SELECT * FROM result
ORDER BY studentresult ASC
-- order by 字段 asc | desc
-- (排序可以用前端排)
分页
- 分页可以缓解数据库压力
- 有更好的用户体验
-- 分页 : limit 2,5 | 起始值 ，页面大小 页面大小是指一页多少数据
-- limit 0,5 1~5
-- limit 1,2 2~2
-- current_time
SELECT * FROM result
LIMIT 2,5
-- 第一页 limit 0,5 （1-1）*5
-- 第二页 limit 0,5 （2-1）*5
-- 第三页 limit 0,5 （3-1）*5
-- 第 N 页 limit 0,5 (n-1)*pagesize [(n(当前页)-1)起始值 * 页面大小]
-- [pagesize 页面大小]
练习题
-- 练习题 -- 查询'高等数学-1' 课程成绩第一名的学生，且分数大于80的学生信息 (学号、姓名、课程名称、分数) -- 分析 -- 一、连表查询 提取信息 -- 二、使用 where 过滤信息 '高等数学-1' and result >= 80 -- 三、设置order by 降序 -- 四、分页 limit 为0,1 SELECT s.studentno,studentname,subjectname,studentresult -- s.'studentno' FROM student s RIGHT JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectno WHERE subjectname = '高等数学-1' AND studentresult >=80 ORDER BY studentresult DESC LIMIT 0,1
子查询
子查询： 从其他表中返回，原表所代表的字符就可以避免连表查询 效率高
WHERE subjectno = ( -- 相当于查询 该科目的编号 返回编号 SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1' )
-- 查询:
-- 高等数学-1的所有考试结果 (学号，科目编号，成绩)[在 result 都有]，降序排列
-- 方式一 ： 使用连接查询
SELECT studentno, r.subjectno ,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
-- 方式二 : 使用子查询 （由里及外）
-- 假设需要用两个表的时候 就不需要连表查询了
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno = (
-- 相当于查询 该科目的编号 返回编号
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC
-- 查询训练 分数>=80 的学号和姓名
-- 连表查询
SELECT DISTINCT s.studentno,studentname
FROM result r
INNER JOIN student s
ON r.studentno = s.studentno
WHERE studentresult >= 80
-- 科目 高等数学-4 分数>=80 的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >=80 AND subjectno = (
-- 子查询 这里需要查询 高等数学-4 对应的编号 就不需要连表查询了
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-4'
)
-- 子查询 效率高
练习
-- 练习: 查询 C语言-1 第一名同学的成绩信息 (学号、姓名、分数) -- 使用子查询 SELECT s.studentno,studentName,studentresult FROM student s INNER JOIN result r ON s.`studentno` = r.`studentno` WHERE subjectno = ( SELECT `subjectno` FROM `subject` WHERE subjectname = 'C语言-1' ) ORDER BY studentresult DESC LIMIT 0,1
7——MySQL 函数
常用函数（了解）
-- ============== 常用函数 ===============
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 0 ~ 1 之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回 -1 正数返回 1
-- 字符串函数
SELECT CHAR_LENGTH('坚持就是胜利') -- 字符串长度
SELECT CONCAT('i','love','you') -- 拼接字符串
SELECT INSERT('坚持就是胜利',1,2,'温柔') -- 查询从某个位置开始替换某个长度
SELECT LOWER('Xxz') -- 转小写
SELECT UPPER('Xxz') -- 转大写
SELECT INSTR('xxz','z') -- 返回第一次出现的字符索引
SELECT REPLACE('坚持就是胜利','坚持'，'努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',4,6) -- 返回指定的子字符串 (源字符串，截取的位置，截取长度)
SELECT REVERSE('赵兄托你帮我办点事') -- 字符反转
-- 查询 新张的同学，替换为李
SELECT REPLACE(studentname,'张','李') FROM student
WHERE studentname LIKE '张%' -- 不知道 啥语法 反正有这个操作
-- 时间和日期函数
SELECT CURRENT_TIME() -- 获取当前日期
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()
聚合函数
-- =============聚合函数 ===============
-- count 查询一个表中有多少行的记录
SELECT COUNT('sex') AS '总科目数' FROM `student`
-- count(字段) 会忽略所有的 null 值(好像没有忽略 留意下) 计算有多少行数据
SELECT COUNT(*) FROM `subject` -- count(*) 不会忽略 null 本质计算行数 * 初步理解代表的是所有的列
SELECT COUNT(1) FROM `subject` -- count(1) 不会忽略所有的null 本质在计算行数 (1) 应该是指第一列 有多少数据
SELECT SUM(`studentresult`) AS 总分 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MAX(`studentresult`) AS 最高分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min | 最小值 |
| ···· | 查文档 |
-- 查询不同课程的平均分，最高分，最低分, 平均分大于 80
-- 核心： (根据不同的课程分组)
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING AVG(studentresult) > 70 -- 过滤分组后的次要条件 这里就不能用 where
初步认识 数据库 md5 加密
`id` INT(4) NOT NULL,
`name` VARCHAR(10) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 指定加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
-- 这里要注意 加密后的字符很长，所以在设置数据类型的时候长度要够
-- 不选定后 就全部加密
UPDATE testmd5 SET pwd = MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES (4,'小明',MD5('123456'))
-- 如何效验 ： 将用户传递进来的密码，进行md5 加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name` = '小明' -- and pwd = md5('123456')
8——事务
事务： 是指访问或者修改数据库的操作
-
原子性（Atomicity） 要么都成功，要么都失败
-
一致性（Consistency） 事务前后的数据完整性保证一致 1000 不变
-
持久性（Durability）事务一旦提交，不可逆，持久化到数据库中
-
隔离性（Isolation） 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
隔离导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）
-- =====================事务 ===================
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启 (默认开启)
-- 手动处理事务
SET autocommit = 0 -- 关闭
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx
-- 提交： 持久化(成功)
COMMIT
-- 回滚: 回到原来的样子 (失败！)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 事务结束后 开启自动提交
-- 了解
SAVEPOINT -- 设置一个事务的保存点（了解）
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 为了安全 加字符集 和核对码
USE shop
CREATE TABLE `accout`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL, -- (9,2) 长度 9 保留两位
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO accout(`name`,`money`) VALUES ('A',2000.00),('B',10000.00)
-- 模拟转账 : 事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务 这就是一组事务
UPDATE accout SET money = money - 500 WHERE `name` = 'A' -- A - 500
UPDATE accout SET money = money + 500 WHERE `name` = 'B' -- B + 500
COMMIT -- 提交事务 一旦提交就持久化了
ROLLBACK -- 回滚
SET autocommit = 1 -- 恢复默认值
9——索引
索引： 帮助mySQl 高效的获取数据的数据结构
提取句子主干就可以得到索引的本质，索引是数据结构
-
主键索引 （primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
-
唯一索引（UNIQUE key）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识位，唯一索引
-
常规索引 （key 、index）
- 默认的 index key 关键字来设置
-
全文索引 （fulltext）
- 特定的数据库才有 mylsam
- 快速定位数据
索引
-- 索引的使用
-- 1. 在创建表的时候给字段增加索引
-- 2. 创建完毕后，增加索引
-- 显示索引的索引信息
SHOW INDEX FROM accout
-- 增加一个全文索引 （索引名）列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`) -- 数据太少可能不能做全文索引
-- explain 分析 sql 执行的状况
EXPLAIN SELECT * FROM accout
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张') -- 前面设置了索引 就可以用索引去搜索数据
生成 一百万条数据 测试 （这个了解即可）
-- 创建表 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 '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`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=utf8 COMMENT = 'app用户表'
-- 插入100万数据.
DELIMITER $$ -- 从这里
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data01 ()
-- 执行失败 可以把函数名换一下 表重新建试试
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*((99999999-100000000)+1000000000))),CONCAT(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END; -- 到这里 执行
SELECT mock_data01() -- 执行此函数 生成一百万条数据
测试
-- 添加索引前 查询
SELECT * FROM app_user WHERE `name` = '用户99999'; -- 耗时 1.261 sec -- 要转圈圈
SELECT * FROM app_user WHERE `name` = '用户99999'; -- 耗时 1.467 sec
-- 添加索引
-- create index(索引名) ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 添加索引后 查询
SELECT * FROM app_user WHERE `name` = '用户99999'; -- 耗时 0.002 sec
-- 在改变表 可以删除索引 要保存
-- 初步理解 索引就是将每一个数据 都挂上索引 通过索引直接查找 1 对 1 假设没有索引呢 就会 一个一个的查找 比如 100 个数据找 99 可能就要遍历99次
索引原则
- 索引不是越多越好
- 不要对经常变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用查询字段上
索引的数据结构 Hash 类型的索引
btree ： innodb 的默认数据结构
10—— 用户管理与备份
用户管理
sqlyog 可视化管理
sql 语句的 用户管理
-- 创建用户 create user '用户名' identified by '密码'
CREATE USER xxz IDENTIFIED BY '123456'
-- 修改密码 当前用户
UPDATE PASSWORD = PASSWORD('111111')
-- 修改密码 指定用户
SET PASSWORD FOR xxz = PASSWORD('111111')
-- 重命名 rename user 原来名字 to 新的名字
RENAME USER xxz TO xxz01
-- 用户授权 all privileges 全部的权限 库.表 *.* 全库全表
-- all privileges 最高权限 除了给其他人授权(grant) 拉满
GRANT ALL PRIVILEGES ON *.* TO xxz01
-- 查询权限
SHOW GRANTS FOR xxz01 -- 查看指定用户的权限 GRANT USAGE ON *.* TO 'xxz01'@'%'
SHOW GRANTS FOR root@localhost -- 查看当前系统管理员的权限 必须加localhost
-- 撤销权限 revoke 哪些权限 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM xxz01
-- 删除用户
DROP USER xxz01
数据备份
备份的作用
- 保证重要的数据不丢失
- 数据转移
MySQL 数据库备份的方式
-
直接拷贝物理文件
-
在 sqlyog 这种 可视化的数据库管理系统 就比较简单
- 直接选择相应的表或库，直接导出，导入直接拉到 sqlyog 中就可以
-
使用命令行提示符(
CMD)导出 mysqlDump 命令行使用导出
mysqldump# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名 # 在同数据库导出多个表 用 空格 隔开就行 # 不选择任意表导出 就是导出数据库 mysqldump -h localhost -u root -p123456 school student >D:/1.sql # 登入的情况下 mysql导入
source注意 导入 路径没有>
# 一、先登入 mysql 选择对应库
use school;
# 二、进行导入操作
source D:/1.sql;
C:\Users\夏天的风>mysql -u root -p123456 school <D:/1.sql
# 提示信息 反正插入成功 : mysql: [Warning] Using a password on the command line interface can be insecure(不安全的).
11——设计一个项目的数据库
糟糕的数据库
- 数据冗([ rǒng ])余,浪费空间
- 数据插入和删除都会麻烦，异常（尽量疲敝使用物理外键）
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求： 分析业务和需要处理的数据库的需求
- 概要设计： 设计关系图 E-R (流程图)图
设计数据库的步骤：(个人博客)
-
收集信息，分析需求
-
用户表(用户登入注销，个人的信息，写博客，创建分类)
![]()
-
分类表 （文章信息）
![]()
-
文章表 （文章的信息）
![]()
-
评论表
![]()
评论表： 查有没有父 id 有就是回复id ....
-
友链表 (友链信息)
![]()
-
关注表
![]()
-
自定义表 (系统信息，某个关键字，或者一些主字段) (狂神自加)略. ..
-
-
标识实体 (把需求落地到每个字段)
我理解 就是把前端的操作，和数据库的数据存储进行一个链接，操作字段达到储存的功能。
-
写博客： user --> blog
-
创建分类 ：user -- > category
-
关注： user -- > user
-
友链： links
-
评论： user-user-blog
-
数据库的三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式 （1nf）
原子性 ： 保证每一列不可再分
第二范式 (2nf)
前提：满足第一范式
每张表只描述一件事情
第三范式 （3nf）
前提： 满足第一范式 和 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
（规范数据库的设计）
规范性 和 性能的问题
关联查询的表不得超过三张表
- 在考虑商业化的需求和目标，（成本，用户体验） 数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下，规范性
- 故意给某些表增加一些冗余的字段，（从多表查询中变为单表查询）
- 故意增加一些计算列 (从大数据量降低为小数据量的查询 索引计算列：就是提前做好计算 直接查结果就行)
12——JDBC(JAVA)
数据库驱动 基本概念:
我们的程序会通过数据库驱动，和数据库打交道
Sun 公司伪类简化开发人员的 (对数据库的统一)操作，提供了一个，(java操作数据库)的规范， 俗称 JDBC 这些规范的实现 由具体的厂商去做对，于开发人员来说，我们只需要掌握 JDBC 接口的操作即可
驱动下载地址： 要和数据库版本相对应 数据库版本5.7.19 驱动版本：用5.1 都行 我们用 5.1.47
第一个 JDBC 程序
- 使用 sqlyog 创建测试数据库
数据内容
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')
- 导入数据库驱动
这样才算完成！
Description
package com.xxz.lesson01;
import java.sql.*;
// 我的第一个 JDBC 程序
public class jdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. 加载驱动
Class.forName("com.mysql.jdbc.Driver"); // 固定写法， 加载驱动
// 2. 用户信息和 url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// 字符编码 字符集 useSSL安全
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); // 执行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"));
}
// 6. 释放连接
resultSet.close();
}
}
Result
E:\study\jdbc\out\production\jdbc;E:\study\jdbc\lib\mysql-connector-java-5.1.47.jar com.xxz.lesson01.jdbcFirstDemo
id=1
name=zhangsan
pwd=123456
email=zs@sina.com
birth=1980-12-04
id=2
name=lisi
pwd=123456
email=lisi@sina.com
birth=1981-12-04
id=3
name=wangwu
pwd=123456
email=wangwu@sina.com
birth=1979-12-04
Process finished with exit code 0
步骤总结：
- 加载驱动
- 连接到数据库 DriverManager
- 获得sql 的对象 Statement
- 或得返回的结果集
- 释放连接
Driver Manager
// 3. 连接成功，数据库对象 connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
// contaition 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql -- 3306
// 协议 :// 主机地址：端口号/数据库名？参数1&参数2&参数3
// oralce -- 1521 了解
// jdbc : oracle : thin:@localhost : 1521 :sid
Statement 执行sql 的对象， prepareStatement 执行 sql 的对象
String sql = "select * FROM users"; // 编写 SQL
statement.executeQuery();// 查询操作返回 ResultSet
statement.execute(); // 执行任何sql
statement.executeUpdate();// 更新、插入、删除、都是用这个，返回一个受影响的行数
ResultSet 查询的结果集 ： 封装了所有的查询结果
获得指定的数据类型
ResultSet resultSet = statement.executeQuery(sql); // 执行sql
resultSet.getObject(); // 在不知道列类型的情况下使用
// 如果知道类型就指定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getLong();
// ...
遍历 结果集 指针
resultSet.beforeFirst();// 移动到最前面
resultSet.afterLast();// 移动到最后面
resultSet.next();// 移动到下一个数据
resultSet.previous();// 移动到前一行
resultSet.absolute(1);// 移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close(); // 耗资源 用完关掉
Statement 对象
jdbc 中的statement 对象用于向数据库发送sql 语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可
Statement 对象的 executeUpdate方法，用于向数据库发送增删改查的sql 语句，executeUpdate 执行后，将会返回一个整数 （即增删改查语句导致了数据库几行数据发生了变化）
Statement.executeQuery 方法用于向数据库发送查询语句，executeQuery 方法返回代表查询结果的 resultSet 对象
在 用 java 操作数据库的时候 最好 在 sqlyog 上进行测试 成功再移
CRUD 操作-create
使用 executeUpdate（String sql）方法完成数据库添加操作，实例操作；
Statement statement = connection.createStatement();
String update = "INSERT INTO `users` VALUES(6,'xxz','123456','xxz@com',CURRENT_TIME )";
int num = statement.executeUpdate(update);
if (num>0){
System.out.println("insert success！！");
}
CRUD操作-delete
使用 executeUpdate（string sql）方法完成数据删除操作，示例操作；
Statement statement = connection.createStatement();
String update = "DELETE FROM `users` WHERE id = 7;"
int num = statement.executeUpdate(update);
if (num>0){
System.out.println("delete success！！");
}
CRUD 操作 -update
使用excuteUpdate（string sql）方法完成数据修改操作，示例操作；
Statement statement = connection.createStatement();
String update = "UPDATE users SET `name`='xxz' WHERE `name`='123';";
int num = statement.executeUpdate(update);
if (num>0){
System.out.println("update success！！");
}
CRUD操作-
Query
使用 excuteQuery（string sql）方法完成数据查询操作，示例操作；
Statement statement = connection.createStatement();
String update = "SELECT `name` FROM users WHERE id=4";
ResultSet set = statement.executeQuery(update);
while(set.next()){
System.out.println(set.getObject("name"));
// 根据获取列的数据类型，分别调用 相应类型的方法 映射到 java 对象中
}
Statement 提取工具类后 CRUD
增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)
-
提取工具类
-
第一步 在
SRC目录下创建 配置文件db.properties![]()
一定要在 src 目录下创建 要不然导入需要添加相应目录 内容如下
driver = com.mysql.jdbc.Driver url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true username = root password = 123456 -
第二步 创建 util 包后 创建
jdbcUtil文件
package com.xxz.lesson02.util; import javax.naming.LimitExceededException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class jdbcUtil { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try { InputStream in = jdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"); // 反射 获取资源 文件一定要在 src 文件夹下 要不然 要写目录 Properties properties = new Properties(); /* * Properties类表示一组持久的属性。 Properties可以保存到流中或从流中加载。 * 属性列表中的每个键及其对应的值都是一个字符串。 * */ properties.load(in); //load() 以简单的线性格式从输入字符流读取属性列表（关键字和元素对）。 properties.getProperty("driver"); // 使用此属性列表中指定的键搜索属性。 返回String value url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); // 驱动只用加载一次 Class.forName(driver); // Class.forName("com.mysql.jdbc.Driver"); // 固定写法， 加载驱动 } catch (Exception e) { e.printStackTrace(); } } // 获取连接 public static Connection getConnection() throws SQLException{ return DriverManager.getConnection(url,username,password); } // 释放连接资源 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(); } } } } -
Create (增) 主方法后面不写了
package com.xxz.lesson02;
import com.xxz.lesson02.util.jdbcUtil;
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 conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection(); // 获取数据库连接
st = conn.createStatement(); // 获得 SQL 的执行对象
String sql = "INSERT INTO users " +
"VALUES(7,'LQ','123456','1234616@qq.com',CURRENT_TIME)";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("insert success!!!");
}else{
System.out.println("defeated!"); // 失败 不过一般直接报错
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
jdbcUtil.release(conn,st,null);
// 这个方法里面有非空判断，所以用没用到都可以传 jdbcUtil.release(conn,st,null); null 也可以
}
}
}
Delete
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection(); // 获取数据库连接
st = conn.createStatement(); // 获得 SQL 的执行对象
String sql = "delete from users where id = 5";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("delete success!!!");
}else{
System.out.println("defeated!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
jdbcUtil.release(conn,st,null);
// 这个方法里面有非空判断，所以用没用到都可以传 jdbcUtil.release(conn,st,null); null 也可以
}
Update
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection(); // 获取数据库连接
st = conn.createStatement(); // 获得 SQL 的执行对象
String sql = "UPDATE users SET `name` = '张飞' WHERE id = 4";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("update success!!!");
}else{
System.out.println("defeated!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
jdbcUtil.release(conn,st,null);
// 这个方法里面有非空判断，所以用没用到都可以传 jdbcUtil.release(conn,st,null); null 也可以
}
Query
就是 搜索比较特殊 是用 executeQuery 去执行搜索
并且返回值是一个 结果集 需要读取
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection(); // 获取数据库连接
st = conn.createStatement(); // 获得 SQL 的执行对象
String sql = "SELECT * FROM users WHERE id = 1; ";
ResultSet set = st.executeQuery(sql); // 返回一个结果集合
while(set.next()){
System.out.println(
set.getObject("id")
+"——"+set.getObject("name")
+"——"+set.getObject("password")
+"——"+set.getObject("email")
+"——"+set.getObject("birthday")
);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
jdbcUtil.release(conn,st,null);
// 这个方法里面有非空判断，所以用没用到都可以传 jdbcUtil.release(conn,st,null); null 也可以
}
SQL 注入问题
package com.xxz.lesson02;
import com.xxz.lesson02.util.jdbcUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
// login("张飞","123456");
login("'or' 1=1","'or' 1=1");
// 账号和密码 都为 true 则返回所有的 表 为什么要单引号 不知道
}
public static void login(String username,String userPassword){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection(); // 获取数据库连接
st = conn.createStatement(); // 获得 SQL 的执行对象
String sql = "SELECT * FROM users WHERE `name` ='"+username+"' AND `password` = '"+userPassword+"'";
//SELECT * FROM users WHERE `name` = '张飞' AND `password` = '123456'; - 注意 单引号 记得加 因为是字符
// SQL 注入 本质就是拼接字符 1=1 肯定是 true 所以 能查到所有用户
// SELECT * FROM users WHERE `name` = 'HZ' OR 1=1 AND `password` = '123456' OR 1=1;
ResultSet set = st.executeQuery(sql);
while(set.next()){
System.out.println(
set.getObject("id")
+"——"+set.getObject("name")
+"——"+set.getObject("password")
+"——"+set.getObject("email")
+"——"+set.getObject("birthday")
);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcUtil.release(conn,st,null);
// 这个方法里面有非空判断，所以用没用到都可以传 jdbcUtil.release(conn,st,null); null 也可以
}
}
}
Result
E:\study\jdbc\out\production\jdbc;E:\study\jdbc\lib\mysql-connector-java-5.1.47.jar com.xxz.lesson02.SQL注入
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
4——张飞——123456——xxz@com——2022-02-05
Process finished with exit code 0
SQL 注入的本质就是拼接字符，会被攻击导致数据泄漏
PreparedStatement 对象
与 Statement 注入 本质的区别就是
可以防止SQL 注入，效率更高
package com.xxz.Lesson03;
import com.xxz.lesson02.util.jdbcUtil;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = jdbcUtil.getConnection();
// 区别
// 使用 ？ 占位符 代替参数
// like 用mysql内置的字符串连接函数concat()
// String sql = "select * from smbms_address where postCode like concat('%',?,'%') ;";
String sql = "INSERT INTO users(id,`name`,`password`,email,birthday) VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql); // 预编译 SQL 先写 SQL 然后不执行
// 手动给参数赋值 (key,value)
st.setInt(1,5); // 第一个 占位符 ？ 值为 5
st.setString(2,"BJ"); // 第二个 占位符 ？ 值为 "BJ"
st.setString(3,"123456"); // ...
st.setString(4,"123456@qq.com");
st.setDate(5,new java.sql.Date(new Date().getTime()));
// 这里要填写 sql 的date 所以 要转化 导入 import java.util.Date; 时间戳 :new Date().getTime())
/*
* sql.date 数据库 java.sql.date
* util.date java new Date().getTime() 获得时间戳
* */
// 执行
int i = st.executeUpdate();
if (i>0){
System.out.println("insert success!!!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcUtil.release(conn,st,null);
}
}
}
Delete 修改sql 赋值就行
String sql = "delete from users where id = ? ";
// 手动给参数赋值 (key,value)
st.setInt(1,6); // 第一个 ？ 值为 6
Update
String sql = "update users set `name`=? where id = ? ";
// 手动给参数赋值 (key,value)
st.setString(1,"hangzhou");
st.setint(2,3);
Query
package com.xxz.Lesson03;
import com.xxz.lesson02.util.jdbcUtil;
import java.sql.*;
public class TestQuery {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs =null;
try {
conn = jdbcUtil.getConnection();
String sql = "select * from users where id = ? "; // 编写SQL
st = conn.prepareStatement(sql); // 预编译
st.setInt(1,2); // 传递参数
rs = st.executeQuery(); // 执行
if (rs.next()){
// 查找整行内容 查找行中 字段 name 的内容
System.out.println(rs.getString("name")); // lisi
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.release(conn,st,rs);
}
}
}
package com.xxz.Lesson03;
import com.xxz.lesson02.util.jdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.Period;
public class 防注入 {
public static void main(String[] args) {
login("张飞","123456");
//4——张飞——123456——xxz@com——2022-02-05
// login("or 1=1","or 1=1");
// 无响应
// 账号和密码 都为 true 则返回所有的 表
}
public static void login(String username,String userPassword){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection(); // 获取数据库连接
String sql = "SELECT * FROM users WHERE `name` = ? AND `password` = ?";
st = conn.prepareStatement(sql); // 预编译 SQL 先写 SQL 然后不执行
st.setString(1,username); // 传递参数
st.setString(2,userPassword);
rs = st.executeQuery(); // 执行
while(rs.next()){
System.out.println(
rs.getObject("id")
+"——"+rs.getObject("name")
+"——"+rs.getObject("password")
+"——"+rs.getObject("email")
+"——"+rs.getObject("birthday")
);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcUtil.release(conn,st,null);
// 这个方法里面有非空判断，所以用没用到都可以传 jdbcUtil.release(conn,st,null); null 也可以
}
}
}
< a href="IDEA连接数据库" >IDEA连接数据库
-
选择数据源 MySQL
![]()
-
连接数据库
![]()
Success
Error Server returns invalid timezone
jdbc:mysql://localhost:3306?serverTimezone=GMT // 要设置时区
-
加载要用的数据库
![]()
-
添加库 添加表测试
CREATE TABLE account( id Int PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(40), money FLOAT ); /*插入测试数据*/ insert into account(name,money) values("A",1000); insert into account(name,money) values("B",1000); insert into account(name,money) values("C",1000);
数据库 在 schemas 文件下
事务
要么都成功，要么都失败
ACID 原则
原子性： 要么全部完成，要么都不完成
一致性： 总数不变
隔离性： 多个进程互不干扰
持久性： 一旦提交不可逆，持久化到数据库
隔离性的问题：
- 脏读： 一个事务读取了另一个没有提交的事务
- 不可重复读： 在同一个事务内，重复读取表中的数据，表数据发生了改变
- 虚度 (幻读) ：在一个事务内，读取到了别人插入的数据，导致前后读出结果不一致
Description
package com.xxz.Transaction;
import com.xxz.lesson02.util.jdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
// 前提是用了 工具类 自己做得 .jdbcUtil
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = jdbcUtil.getConnection();
// 关闭数据库的自动提交，自动会开启事务
conn.setAutoCommit(false); // 开启事务
String sql1 = "UPDATE account SET `money` = `money`-100 WHERE `name` = 'A'";
// copy SQL 语句的字符 注意要加单引号 'A'
st = conn.prepareStatement(sql1); // 预编译 不执行
st.executeUpdate(); // 执行
//int i = 1/0;
// 这里在前段语句执行完毕后 添加语句 分母不为零 一定报错 测试事务表现的原子性
String sql2 = "UPDATE account SET `money` = `money`+100 WHERE `name` = 'B'";
st = conn.prepareStatement(sql2); // 预编译 不执行
st.executeUpdate(); // 执行
// 业务完成，提交事务
conn.commit(); // 这里会开启自动提交
System.out.println("success!!!");
} catch (SQLException e) {
try {
conn.rollback();
// 执行失败 则回滚
// 开启事务后 失败会自动rollback 这是手动显示定义 可不写
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
jdbcUtil.release(conn,st,null);
}
}
}
13——DBCP—C3P0数据连接池
数据库连接——执行完毕——释放 每次进行连接和释放 十分浪费系统资源
池化技术: 准备一些预先留好的资源，过来就连接预先准备好的
银行 ： ---来服务开门 -- 服务 -- 服务完关门 （如果一个人过来也开门关门，十分麻烦）
池化技术 ： ---来服务开门 --业务员：等待 --- 直到关门 （相当于服务器一直开着，预留业务员服务）
这就涉及到，需要预留多少 业务员 既是：
常用连接数：10
最小连接数(一般设置为常用数)：10
最大连接数: 100
还有设置等待超时 ： 100ms 就劝退
编写连接池子： 实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid: 阿里巴巴
使用数据库连接池后，在项目开发中就不需要编写连接数据库的代码了
DBCP
- 下载两个 jar 包 直接在 search 栏中搜索 dbcp 和 pool
-
添加数据库
![]()
-
添加配置文件 db.dbcpconfig
配置文件
DBCP配置文件 #连接设置 这些名字 是 DBCP 数据源中定义好的 不能修改 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true username=root password=123456 # initialSize=10 #最大连接数量 maxActive=50 # maxIdle=20 # minIdle=5 # maxWait=60000#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】 #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=utf8 #指定由连接池所创建的连接的自动提交（auto-commit）状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读（read-only）状态。 #如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix） defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。 #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_COMMITTED
对比 Oracle
--Mysql
delete from users where id = (select t.* from (select MAX(id) max_id from users) t) ;
--Oracle
delete from users where id =(select MAX(id) from users);
如果你有梦想 就努力去实现 就这样
浙公网安备 33010602011771号