MySQL

1、初始MySQL

1.1、什么是数据库

DB(datebase)数据库,存储数据!

1.2、数据库分类

关系型数据库:(SQL)

  • MySQL,Oracle,SqlServer,DB2…
  • 通过表和表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表…

非关系型数据库:(NoSQL)Not Only

  • Redis,MongDB
  • 非关系型数据库,对象存储,通过对象的自身的属性来决定!

DBMS(数据库管理系统)

  • 数据库的管理软件,科学有效的管理我们的数据。维护和获取数据;
  • MySQL,数据库管理系统

1.4、MySQL简介

MySQL是一个关系型数据库管理系统

前世:瑞典MySQLAB公司

今生:属于Oracle旗下产品

开源的数据库软件

体积小,速度快,总体拥有成本低,招人成本比较低,所有人必须会

中小型网站,或者大型网站,集群!

官网:https://www.mysql.com

安装:尽量不要使用exe,注册表;尽可能使用压缩包

1.5、安装MySQL

1、解压到合适位置

2、配置环境变量(高级系统设置->环境变量->系统变量->path->单击编辑->新建->把bin路径粘贴过来)

3、在解压到的文件夹中添加my.ini配置文件

[mysqld]
basedir=D:\MySQLHJ\mysql-8.0.23-winx64\
datadir=D:\MySQLHJ\mysql-8.0.23-winx64\data\
port=3306
skip-grant-tables

…(狂神视频)

1.6、安装SQLyog

…(狂神视频)

1、创建一个数据库school

基字符集:utf8

数据库排序规则:utf8_general_ci

2、创建一张student表

高级表属性:表类型-InnoDB 字符集:utf8 校队规则:utf8_general_ci

3、查看表

4、尝试添加数据,之后刷新保存

1.7、连接数据库

命令行连接

mysql -uroot -proot--连接数据库
update mysql.user set anthentication_string=password('root') where user='root' and Host = 'localhost';--修改用户密码
flush privileges;--刷新权限

----------------------------------------------------------
--所有的语句都使用;结尾
show databases;--查看所有的数据库
use librarydb  --切换数据库
Database changed
show tables; --查看数据库中所有的表
describe admin; --显示数据库中所有的信息
create database westos; --创建一个数据库
exit; --退出连接
-- 单行注释(SQL本来的注释)
/*
(sql的多行注释)
*/

数据库 xxx 语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

操作数据库>操作数据库中的表>操作数据库中的数据

mysql关键字不区分大小写

2.1 操作数据库(了解)

CREATE DATABASE [ IF NOT EXISTS] westos;

[ ]表示可选

2.删除数据库

DROP DATABASE [IF EXISTS] westos

3.使用数据库

如果你的表名或者字段名是一个特殊字符,就需要带 ``(此符号在esc键下面tab键上面)

USE school

SELECT `user` FROM westos

4.查看数据库

SHOW DATABASE --查看所有的数据库

对比,sqlyog的可视化操作

  • 对照可视化历史记录查看sql
  • 固定的语法或关键字必选要强行记住

2.2 数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中度大小的数据 3个字节
  • int 标准的数据 4个字节 INT(M) ,M表示最大显示宽度,与存储大小或类型包含的值的范围无关
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
  • decimal 字符串形式的浮点数 (金融计算的时候,一般使用decimal)

字符串

  • char 字符串固定大小 0~255
  • varchar 可变字符串 0~65535
  • tinytext 微信文本 2^8 - 1
  • text 文本串 2^16 - 1

java.unti.Data

  • data YYYY-MM-DD ,日期格式
  • time HH:mm :ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳, 1970.1.1到现在的毫秒数!
  • year 年份表示

时间日期

java.unti.Data

  • data YYYY-MM-DD ,日期格式
  • time HH:mm :ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳, 1970.1.1到现在的毫秒数!
  • year 年份表示

null

  • 没有值,未知
  • 注意,不要使用NULL进行数值运算,结果为NULL

2.3、数据库的字段属性(重点)

Unsigned:

  • 无符号的整数
  • 声明了该列不能声明为负数

zerofill:

  • 0填充
  • 不足的位数,用0填充

自增

  • 通常理解为自增,自动在上一条记录的基础上+1(默认)
  • 通常用来设计唯一的主键~index,必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

非空 NUT NULL

  • 假设设置为not null ,如果不给他赋值,就会报错!
  • NULL, 如果不填写值,默认就是NULL

默认

  • 设置默认的值
  • sex, 默认值为男, 如果不指定该列的值,就会有默认的值

阿里巴巴规范

/* 每一个表,都必须存在以下五个字段!未来做项目用的,表示一个记录存在的意义
id 主键
`version`  乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/

2.4、创建数据库

-- 目标:创建一个school数据库
-- 创建学生表(列,字段) 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email

-- 注意点,使用英文(), 表的名称 和 字段尽量使用 `` 括起来
-- 字符串使用 单引号括起来
-- AUTO_INCREMENT 自增
-- 所有的语句后面加, (英文的),最后一个不用加
-- DELETE 默认值 COMMENT 注释
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键
CREATE TABLE if not EXISTS `student`(
	`id` INT(4) NOT NUll AUto_increment COMMENT '学号',
	`name` VARCHAR(30) NOT NUll DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULl COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表明`(
	`字段名` 列类型[属性] [索引] [注释],
    `字段名` 列类型[属性] [索引] [注释],
    ......
    `字段名` 列类型[属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school -- 查看创建数据库的语句 
SHOW CREATE TABLE student -- 查看数据库表的定义语句
DESC student -- 显示表的结构

2.5、数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大,约为2倍

常规使用操作

  • MYISAM 节约空间,速度快
  • INNODB 安全性高,事务的处理,多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库!

本质话说文件的存储!

MySQl引擎在物理文件上的区别

  • InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
  • MYISAM 对应文件
    • *.frm -表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件 (index)

设置数据库表的字符集编码

CHARSET=utf8

不设置的话,会是MySQL默认的字符集编码~(不支持中文)

mysql的默认编码是Latin1,不支持中文

在my.ini中配置默认的编码(加上下面的语句)

character-set-server=utf8

2.6、删改数据库

修改表

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE sys_student RENAME As sys_student_info
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE sys_student_info ADD age INT(11)

-- 修改表的字段(重命名,修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[] modify
ALTER TABLE sys_student_info MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE sys_student_info CHANGE age age1 INT(1) -- 修改字段名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名 
ALTER TABLE sys_student_info DROP age1

删除表

-- 删除表(如果存在)
-- DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS sys_student_info

所有的创建和删除操作尽量加上判断,以免报错

注意点:

  • `` 字段名,使用这个这个包裹!
  • 注释 – /**/
  • sql关键字大小写不敏感
  • 所有的符号用英语

3.MySQL数据管理

3.1、外键(了解即可)

方式一:在创建表的时候,增加约束(麻烦,比较复杂)

CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradeName` VARCHAR(10) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 学生表的gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用) reference 应用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN key (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)(即先删除school表,再删除grade表)

方式二:创建表成功后,添加外键约束

CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradeName` VARCHAR(10) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

SHOW CREATE TABLE student

-- 学生表的gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用) reference 应用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `Fk_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)
12345678910111213141516171819202122232425262728

以上操作都是物理外键,数据库级别的外键,不建议使用!(避免数据库最多造成困扰)

最佳实践

  • 数据库就是单纯的表,只用来存数据,只有行(数据)列(字段)
  • 我们想使用多张表的数据,想使用外键(程序去实现)

3.2、DML语言(全部记住)

**数据库意义:**数据存储,数据管理

DML语言:数据操作语言

  • insert
  • update
  • delete

3.3 、添加

-- 插入语句(添加)
-- INSERT INTO 表名({字端名1,字段2,字段3}) VALUES('值1','值2','值3',...)
INSERT INTO `grade`(`gradeName`) VALUES('大四')

-- 由于主键自增我们可以省略(如果不写表的字段,他就会一一匹配)
INSERT INTO `grade` VALUES ('大三') -- 报错,字段不匹配,相当于下面
INSERT INTO `grade`(`gradeid`,`gradeName`)  VALUES ('大三',null)

-- 一般写插入语句,数据一定和字段一一对应

-- 插入多个字段,值值用()隔开
INSERT INTO `grade`(`gradeName`) VALUES('大二'),('大一')


INSERT INTO `student`(`name`) VALUES ('张三')

INSERT INTO `student`(`name`, `password`) VALUES ('张三', '2434')

INSERT INTO `student`(`name`, `password`, `sex`) VALUES ('张三', '2434','女')
12345678910111213141516171819

语法:INSERT INTO 表名 ([字段1],[字段2],…) VALUES (‘值1’,‘值2’,…)

注意事项:

  1. 字段和字段之间使用 英文逗号 隔开
  2. 字段是可以省略,但是后面的值必须要一一对应,不能少
  3. 可以同时插入多条语句,VALUES后面的值,需要用**,**隔开即可 valuse(),()

3.4、修改

update 修改谁(条件) set 原来的值 = 新值

-- 修改学员名字
UPDATE `student` SET `name` = '漠殇' WHERE id = 1;

-- 不指定条件的情况下,会改动所有表!
UPDATE `student` SET `name` = '1001001'

-- 修改多个属性,逗号隔开
UPDATE `student` SET `name` = '漠殇', email = '1243242@qq.com' WHERE id = 1

-- 语法:
-- UPDATE 表名 SET colnum_name = value,[colnum_name = value, ....] where[条件]

条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改…

操作符含义范围结果
=等于5=6false
<>或!=不等于5<>6true
>
<
<=
>=
BETWEEN … AND,闭合区间内[2,5][2,5]
AND&&5>1 and 1>2false
OR||5>1 or 1>2true

语法:UPDATE 表名 set colnum_name = value,[colnum_name = value,…] where[条件]

注意:

  1. colnum_name 是数据库的列,尽量带上``
  2. 条件:筛选的条件,如果没有指定,则会修改所有的列
  3. value,是一个具体的值,也可以是一个变量
  4. 多个设置的属性之间,使用英文逗号隔开

3.5、删除

delete命令

语法 delete from 表名 [where 条件]

-- 删除数据 (避免这样写)
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE `id` = 1
12345

TRUNCATE命令

作用:完全清空一个数据库表,表的结构和索引约束不会变

-- 清空表 
TRUNCATE `grade`
12

DELETE 和 TRUNCATE 区别

  • 相同点:都能删除数据,都不会删除表结构
  • 不同
    • TRUNCATE重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务
-- 测试delete 和TRUNCATE区别
CREATE TABLE `test` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')

DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零
1234567891011

了解即可:DELETE删除的问题,重启数据库,现象

  • innoDB 自增列会重1开始(存在内存当中,不会丢失)
  • MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)

4.DQL查询数据(最重点)

4.1、DQL

(data query LANGUAGE:数据查询语言)

  • 所有的查询操作都用他 select
  • 简单的查询,复杂的查询都能做
  • 数据库中最核心的语言,最重要的语言
  • 使用频率最高的语言

在这里插入图片描述

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

4.2、指定查询字段

-- 查询所有学生 SELECT 字段 FROM 表
SELECT * From `student`

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`

-- 别名,给结果起一个名字 AS, 可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM `student` AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名:', `studentname`) AS 新名字 FROM `student`

语法SELECT 字段... FROM 表

有的时候,列名字不是那么的见名知意。我们就可以起别名 AS 字段名 as 别名 表名 as 别名

去重:distant

作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条

-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM `result` -- 查询全部的考试成绩
SELECT `studentno` FROM `result` -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result` -- 发现重复数据,去重

数据库的列(表达式)

SELECT VERSION() -- 查询系统版本 (函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算 (表达式)
SELECT @@auto_increment_increment -- 查询自增的步长 (变量)

-- 学员考试成绩 +1 分查看
SELECT `studentno`,`studentresult`+ 1 AS '提分后' FROM `result`

数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量

selsect 表达式from 表

4.3、Where 条件子句

作用:检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成 。 结果都是布尔值

运算符语法描述
and &&a and b a&&b逻辑与
or ||a or b a||b逻辑或
not !not a !a逻辑非
SELECT `Studentno`, `studentresult` FROM result -- 列也不区分大小写

-- 查询考试成绩在95~100分之间
SELECT `studentno`, `studentresult` FROM result
WHERE studentresult >= 95 And studentresult <= 100

-- and &&
SELECT `studentno`, `studentresult` FROM result
WHERE studentresult >= 95 && studentresult <= 100

-- 模糊查询(区间)
SELECT `studentno`, `studentresult` FROM result
WHERE studentresult BETWEEN 95 And 100

-- 除了1000 号学生之外的成绩
SELECT `studentno`, `studentresult` FROM result
WHERE studentno != 1000;

-- != NOT
SELECT `studentno`, `studentresult` FROM result
WHERE not studentno = 1000;

模糊查询:比较运算符

运算符语法描述
IS NULLa is null
is not nulla is not null
BETWEEN ANDa between b and ca在b和c之间
Likea like bsql匹配,如果a匹配b,结果为真
Ina in(a1,a2,a3…)假设a在a1,a2,a3…其中一个,结果为真
-- =======================模糊查询======================
-- 查询姓刘的同学
-- like 结合 %(代表0到任意个字符) _(一个字符)
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '张%'

-- 查询修张的同学,名字后面只有一个字的
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '张_'

-- 查询修张的同学,名字后面只有两个个字的
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '张__'

-- 查询名字中间带有伟的 %伟%
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '%伟%'

-- =====in 具体的一个或多个值=====
-- 查询1001,1002,1003号学员
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentno` IN(1001, 1002, 1003)

-- 查询在北京的学员
SELECT `studentno`, `studentname` FROM `student`
WHERE  `address` IN('北京朝阳');

SELECT `studentno`, `studentname` FROM `student`
WHERE  `address` IN('北京'); -- 查不出来,因为没有

-- ====== null  not null ====

-- 查询地址为空的学生 null ''
SELECT `studentno`, `studentname` FROM `student`
WHERE  `address` = '' OR `address` IS NULL


-- 查询有出生日期 不为空
SELECT `studentno`, `studentname` FROM `student`
WHERE  `borndate` IS NOT NULL

4.4、联表查询

在这里插入图片描述

七种jion理论
在这里插入图片描述

- -(查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
/*思路
1.分析需求,分析查询的字段来自哪些表,student,result, subject(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中的哪个数据是相同的)
判断条件: 学生表中的studentno = 成绩表 studentno
*/
SELECT s.studentno, studentname, subjectname, studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- Right Join
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
RIGHT JOIN result 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
操作描述
lnner join如果表中至少有一个匹配,就返回行
left jion会从左表中返回所有的值,即使右表中没有匹配
rigth jion会从右表中返回所有的值,即使左表中没有匹配
-- 查询缺考的同学
-- Left Join
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

- 思考题(查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
/*思路
1.分析需求,分析查询的字段来自哪些表,student,result, subject(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中的哪个数据是相同的)
判断条件: 学生表中的studentno = 成绩表 studentno
*/
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 我要查询那些数据 select 。。。
-- 从那几个表中查FROM表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,从两张慢慢增加

自连接(了解)

自己的表和自己的表连接。核心:一张表拆分为两张一样的表即可

父类

categoryidcategeoryName
2信息技术
3软件开发
5美术设计

子类

pidcategoryidcategeryName
34数据库
28办公信息
36web开发
57ps技术

操作:查询父类对应的子类关系

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
CREATE TABLE `school`.`category`(
	`categoryid` INT(3) NOT NULL COMMENT 'id', 
	`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
	`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
	 PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

-- 查询父子信息 : 把一张表看为两个一模一样的的表
SELECT a.`categoryname` AS '父栏目' , b.`categoryname` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`


-- 查询科目所属的年级(科目名称,年级名称)
SELECT `subjectname`, gradename
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询了参加 高等数据-1 考试的同学信息: 学号,学生姓名,科目名称,分数
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-1'

4.5、分页和排序

排序

-- 排序: 升序 ASC, 降序 DESC

-- order BY 通过哪个字段排序,怎么排
-- 根据查询结果根据 成绩降序	排序
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-1'
ORDER BY studentresult ASC

分页

-- 为什么要分页?
-- 缓解数据库压力,给人的体验更好

-- 分页,每页只显示五条数据
-- 语法:limit 起始值,页面的大小
-- 网页应用: 当前页,总的页数,页面的大小
-- LIMIT 0,5 1~5
-- LIMIT 1,5 1~5
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-1'
ORDER BY studentresult ASC
LIMIT 5,5

-- 第一页 LIMIT 0,5  (1 - 1)* 5
-- 第二页 LIMIT 5,5 (2 - 1)* 5
-- 第三页 LIMIT 10,5 (3 - 1)* 5
-- 第四页 LIMIT 15,5 (4 - 1)* 5  (n - 1)*pagesize, pagesize
-- [pagesize:页面大小]
-- [(n - 1)*pagesize 起始值]
-- [n: 当前页]
-- [数据总数/页面大小 + 1= 总页数]


-- 查询 JAVA第一学年 课程成绩排名前十的学生,并且分数要大于80的学生(学号,姓名,课程名称,分数)
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'Java程序设计-1' AND studentresult > 80
ORDER BY studentresult DESC
LIMIT 0,10

4.6、子查询

where(这个值是计算出来的)

本质在where语句中嵌套一个子查询语句

where(select * from)

-- ==================== WHERE ==================
-- 1.查询数据库结构-1的所有考试结果(学号,科目编号,成绩),降序排列
-- 方式一:使用连接查询
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 student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >= 80

-- 在这个基础上增加一个科目,高等数学-2
-- 查询 高等数学-2 的编号
SELECT DISTINCT s.studentno, studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >= 80 AND subjectno = (
	SELECT `subjectno` FROM `subject`
	WHERE subjectname = '高等数学-2'
)

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
SELECT  s.studentno, studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-2' AND studentresult >= 80

-- 再改造
SELECT  studentno, studentname FROM student WHERE studentno in (
	SELECT studentno FROM result WHERE studentresult>80 AND `subjectno` = (
		SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
	)
)

4.7、分组和过滤

-- 查询不同课程的平均分,最高分,最低分,平均分大于60分
-- 核心:(根据不同的课程分组)
SELECT `subjectname`, AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段分组
HAVING 平均分 > 60

4.8、select小结

在这里插入图片描述

5、MySQl函数

官网:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

5.1、常用函数(并不常用)

-- ======================= 常用函数===========================

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(-10) -- 判断参数符号 负数返回-1,正数返回0,0返回0

-- 字符串函数
SELECT CHAR_LENGTH('黑') -- 字符串长度
SELECT CONCAT('wo','se') -- 拼接字符串
SELECT INSERT('hellloword',1,2,'hei') -- 查询,从某个位置开始替换某个长度
SELECT LOWER('lcP') -- 小写字母
SELECT UPPER('Lcp') -- 大写字母
SELECT INSTR('lcp','c') -- 返回第一次出现的字符串索引
SELECT REPLACE('dsdkdsj', 'ds', 'fuj') -- 替换出现的指定字符串
SELECT SUBSTR('dskfn',4 , 6) -- 返回指定的字符串(同Java)
SELECT REVERSE('dskksdfsegs') -- 反转

-- 查询姓 张 的同学 名字 变为 周
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 VERSION() -- 版本

5.2、聚合函数

函数名称描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
-- ================ 聚合函数==============
-- 都能统计表中的数据(查询表有多少个记录)
SELECT COUNT(studentname) FROM student; -- COUNT(指定列),会忽略所有的null值

SELECT COUNT(*) FROM student; -- COUNT(*),不会忽略null , 本质,计算行数
SELECT COUNT(1) FROM result; -- COUNT(1),不会忽略所有的null 本质 计算行数

/*
无主键:count(1)比count(x)快

有主键:count(主键)最快

count(*)与count(1)都包括null统计,而count(column)不包括null统计*/


SELECT SUM(studentresult) AS 总和 FROM result
SELECT AVG(studentresult) AS 平均分	FROM result
SELECT MAX(studentresult) AS 最高分 FROM result
SELECT MIN(studentresult) AS 最低分	FROM result

5.3、数据库级别的MD5加密(扩展)

什么是MD5?

MD5信息摘要算法(英语:MD5 Message-Digest Algorithm),一种被广泛使用的[密码散列函数],可以产生出一个128位(16[字节]的散列值(hash value)

主要增强算法复杂度和不可逆性

MD5不可逆,具体的值的MD5是一样的

MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前值

-- =============== 测试MD5 加密=================
CREATE TABLE `testmd5` (
	`id` INT(3) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES(1, '张三', '123456'),(2, '李四', '123456'),(3, '王五 ', '123456')

-- 加密
UPDATE `testmd5` SET pwd = MD5(`pwd`) -- 加密所有密码

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4, '小明', MD5('123456'))

-- 如何校验:将用户传递进来的密码,进行md5加密,然后对比加密后的值
SELECT *FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')

6、事务

6.1、什么是事务

要么都成功,要么都失败

参考博客:https://blog.csdn.net/dengjili/article/details/82468576

ACID

原子性(Atomicity):要么都成功,要么都失败

在这里插入图片描述

一致性(Consistency):事务前后数据完整性要保证一致

在这里插入图片描述

**隔离性(Isolation):**隔离性是多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间要相互隔离

持久性(Durability) – 事务提交

  • 事务没有提交,恢复原状
  • 事务提交,持久化到数据库

在这里插入图片描述

隔离所导致的一些问题

脏读:

指一个事务读取了另外一个事务未提交的数据。
在这里插入图片描述

不可重复读:

在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)

执行事务

-- mysql是默认开始事务自动提交的 
SET autocommit = 0 -- 关闭
SET autocommit = 1 --开启(默认)
-- 手动处理事务
SET autocommit = 0 -- 关闭自动条件

-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内

-- 提交:持久化(成功)
COMMIT 
-- 回滚:回到原来的样子(失败)
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名  --  设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE  SAVEPOINT 保存点		-- 撤销保存点

在这里插入图片描述

模拟场景

-- 转账

CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8 */

CREATE TABLE `account` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `money` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

INSERT INTO account('name','money')
VALUES('A',2000.00),('B',10000.00)
-- 模拟转账 : 事务
SET autocommit = 0; -- 关闭自动事务
START TRANSACTION -- 开启一个事务

UPDATE account SET money = money - 500 WHERE `name` = 'A'
UPDATE account SET money = money + 500 WHERE `name` = 'B' 

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET autocommit = 1;

SHOW CREATE DATABASE shop
SHOW CREATE TABLE account

7、索引

参考博客:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

7.1、索引的分类

  • 主键索引 (PRIMARY KEY)
    • 唯一的标识,主键不可重复 ,只能有一个列作为主键
  • 唯一索引 (UNIQUE KEY)
    • 避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
  • 常规索引(KEY / INDEX)
    • 默认的,index,key 关键词来设置
  • 全文索引 (FullText)
    • 在特定的数据库引擎下才有,MyISAM
    • 快速定位数据

基础语法

-- 索引的使用
-- 1.在创建表的时候给字段添加索引
-- 2.创建完毕后,增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 添加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 常规索引, 非全文索引

SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')

7.2、测试索引

-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

select mock_data()

SELECT * FROM app_user WHERE 'name' = '用户9999'; --0.993sec
SELECT * FROM app_user WHERE 'name' = '用户9999'; --1.098sec
SELECT * FROM app_user WHERE 'name' = '用户9999'; --0.778sec
EXPLAIN SELECT * FROM app_user WHERE 'name' = '用户9999'SELECT * FROM student
--id_表名_字段名
--CREAT INDEX 索引名 on 表(字段)
CREAT INDEX id_app_user_name ON app_user('name');
SELECT *FROM app_user WHERE 'name' = '用户9999'--0.001esc
SELECT *FROM app_user WHERE 'name' = '用户9999'EXPLAIN SELECT *FROM app_user WHERE 'name' = '用户9999'

创建索引后查询数据消耗时间显著降低

索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显

7.3、索引原则

  • 索引不是越多越好
  • 不要对进程变动数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用查询的字段上

索引的数据结构

Hash类型的索引

Btree:InnoDB的默认数据结构

8、权限管理和备份

8.1、用户管理

SQLyog 可视化管理

SQL命令操作

用户表:mysql.user

用户的本质:对mysql.user这张表进行增删改查

--创建用户 CREAT USER 用户名 IDENTIFIDE BY '密码'
CREAT USER kuangshen IDNETIFIED BY '123456'
--修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
--修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('123456')
--重命名RENAME USER 原来名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2
--用户授权 ALL PRIVILEGES 全部的权限,库.表
--ALL PRIVILEGES  除了给别人授权,其他都能够干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
--查询权限
SHOW GRNTS FOR kuangshen2 --查看指定用户的权限
SHOW GRANTS FOR root@localhost
--ROOT用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root' @'localhost' WITH GRANT OPTION
--撤销权限 REVOKE 那些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
--删除用户
DROP USER kuangshen

8.2、MySql备份

为什么要备份:

  • 保证重要的数据不丢失
  • 数据转移

MySQl数据库备份的方式

  • 直接拷贝物理文件
  • 在可视化软件直接导出
  • 使用命令行导出 mysqldump 命令行使用
#mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 表名3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

#导入
#登录
mysql -u用户名 -p密码
# 切换到指定数据库
use 数据库名或表名
# source 备份文件
source d:/a.sql

#导入,法二
mysql -u用户名 -p密码 库名 < 备份文件

9、规范数据库设计

9.1、为什么需要设计

当数据比较大的时候,就需要设计

糟糕的数据库设计

  • 数据冗余,浪费空间
  • 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
  • 程序的性能差

良好的数据库设计

  • 节省内存空间
  • 保证数据库的完整型
  • 方便我们开发系统

软件开发中,关于数据库的设计

  • 分析需求:分析业务和需要处理的数据库的需求
  • 概要设计:设计关系E—R图

设计数据库的步骤:(个人博客)

  • 收集信息,分析需求
    • 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
    • 分类表(文章分类,谁创建的)
    • 文章表(文章的信息)
    • 友链表(友情链接信息)
    • 评论表
    • 自定义表(系统消息,某个关键的字,或者一些主字段) key :value
  • 标示实体(把需求落地到某个字段)

用户表:

在这里插入图片描述

分类表:
在这里插入图片描述

文章表:

在这里插入图片描述

评论表:

在这里插入图片描述

友链表:

在这里插入图片描述

标识实体之间的关系

  • 写博客user–> blog
  • 创建分类:user–>category

9.2、三大范式

为什么需要数据规范化

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息
  • 删除异常
    • 丢失有效的信息

三大范式

博客链接:https://www.cnblogs.com/wsg25/p/9615100.html

第一范式(1NF)

原子性:保证每一列不可再分

第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提:满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关

规范性和性能的问题

关联查询的表不得超过三张表

  • 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
  • 在规范性能的问题的时候,需要适当的考虑一下 规范性
  • 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询 )
  • 故意增加一些计算列(从大数据量降为小数据量的查询:索引)

10、JDBC(重点)

10.1、数据库驱动

程序要通过 数据库驱动,和数据库打交道

10.2、JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库)规范,俗称JDBC。这些规范的实现有具体的厂商去做。对于开发人员,我们只需要掌握JDBC接口的操作即可!

在这里插入图片描述

java.sql

javax.sql

还需要导入一个数据库驱动包mysql-connector-java-版本号jar

10.3、第一个JDBC程序

创建数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','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')

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		
		//2.用户信息
		//useUnicode = true 支持中文编码
		//characterEncoding = utf8 设定字符集
		//useSSL = true 安全连接
		String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 && useSSL = true";
		String user = "root";
		String password = "lcp5211314..";
		
		//3.连接成功,数据库对象 Connection 代表数据库
		Connection connection = DriverManager.getConnection(url, user, password);
		
		//4.执行SQL的对象
		Statement statement = connection.createStatement();
		
		//5.执行SQL的对象,去执行SQL,可能存在结果,查看返回结果
		String sql = "SELECT * FROM users";
		
		ResultSet resultSet = statement.executeQuery(sql); //返回的结果集
		
		while(resultSet.next()) {
			System.out.println("id = " + resultSet.getObject("id"));
			System.out.println("name = " + resultSet.getObject("NAME"));
			System.out.println("password = " + resultSet.getObject("PASSWORD"));
			System.out.println("email = " + resultSet.getObject("email"));
			System.out.println("birthday = " + resultSet.getObject("birthday"));
		}
		
		//6.释放连接
		resultSet.close();
		statement.close();
		connection.close();
	}

步骤总结:

1、加载驱动

2、连接数据库DriverManager

3、获得执行sql的对象Statement

4、获得返回的结果集

5、释放连接

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法
Connection connection= DriverManager.getConnection(url,name,password);

//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
1234567891011

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";

//mysql 默认3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3

//Oracle   1521
//jdbc:oralce:thin:@localhost:1521:sid

statement 执行SQL的对象 PrepareStatement 执行SQL的对象

String sql="SELECT * FROM users";//编写Sql

statement.executeQuery();
statement.execute();
statement.executeUpdate();//更新,插入,删除,返回一个受影响的行数

ResultSet 查询的结果集,封装了所以的查询结果

获得指定的数据类型

ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
resultSet.getObject();//在不知道列类型下使用
resultSet.getString();//如果知道则指定使用
resultSet.getInt();

遍历,指针

resultSet.next(); //移动到下一个
resultSet.afterLast();//移动到最后
resultSet.beforeFirst();//移动到最前面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

释放内存

//6. 释放连接
resultSet.close();
statement.close();
connection.close();//耗资源

10.4statement对象

Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sq|语句, executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发生查询语句,executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作,示例操作:

 Statement statement = connection.createStatement();
        String sql = "insert into user(...) values(...)";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("插入成功");
        }

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "delete from user where id =1";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("删除成功");
        }

CURD操作-update

使用executeUpdate(String sql)方法完成数据修改操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "update user set name ='' where name = ''";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("修改成功");
        }

CURD操作-read

使用executeUpdate(String sql)方法完成数据查询操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "select * from  user where id =1";
        ResultSet rs= statement.executeQuery(sql);
        if(rs.next()){
            System.out.println("");
        }

代码实现

1.提取工具类

package com.kuang.lesson02.utils;

import java.sql.*;

public class JdbcUtils {
    static {
        try{
            //1.驱动只用加载一次
            Class.forName("com.mysql.jdbc.Driver");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    static String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode = true & characterEncoding = utf8 && useSSL = true";
    static String username = "root";
    static String password = "root";
    //2.获取连接
    public static Connection getConnection() throws Exception{
        return DriverManager.getConnection(url, username, password);
    }
    //3.释放资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if(rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }

    }
}

2.编写增删改的方法,exectueUpdate

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.*;

public class TestInnsert {
    public static void main(String[] args){
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;
        try {
             conn = getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'sanjin','123456','233223@qq.com','2020-01-01')";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

增:

package com.kuang.lesson02.utils;

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 = JdbcUtils.getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(3,'sanjin','123456','233223@qq.com','2020-01-01')";

            int i = st.executeUpdate(sql);
            if(i>0) {
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }

}

删:

package com.kuang.lesson02.utils;

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 conn =null;
        Statement st = null;
        ResultSet rs =null;
        try {
            conn = JdbcUtils.getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "DELETE FROM users where id = 5";
            int i = st.executeUpdate(sql);
            if(i>0) {
                System.out.println("删除成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }
}

改:

package com.kuang.lesson02.utils;

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 conn =null;
        Statement st = null;
        ResultSet rs =null;
        try {
            conn = JdbcUtils.getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "UPDATE users SET email='236782@qq.com' WHERE id=1 ";
            int i = st.executeUpdate(sql);
            if(i>0) {
                System.out.println("修改成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }

}



3.查询 executeQuery

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.*;

public class TestInnsert {
    public static void main(String[] args) throws SQLException {
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;

        try {
             conn = getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "select * from users";
            rs=st.executeQuery(sql);//查询完毕返回结果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

SQL注入问题

sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.getConnection;

public class SQL注入 {
    public static void main(String[] args) {
        //SQL注入
        login("' or '1=1","123456");
    }
    public static void login(String name,String password){

        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;

        try {
            conn = getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "select * from users where `NAME`='"+ name +"'  AND `PASSWORD`='"+ password +"'" ;
            rs=st.executeQuery(sql);//查询完毕返回结果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
            JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}


10.5 PreparedStatement对象

PreparedStatement 可以防止SQL注入 ,效率更高。

  1. 新增
  2. 删除
  3. 查询
  4. 查询
//新增,其他的同理
package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        Connection connection= null;
        PreparedStatement pstm=null;
        try {

            connection = JdbcUtils.getConnection();
            //区别
            //使用?占位符代替参数
            String sql = "insert into users(id,`NAME`) values(?,?)";
            pstm = connection.prepareStatement(sql);//预编译sql,先写sql然后不执行
            //手动给参数赋值
            pstm.setInt(1,8);//1表示第一个参数,插入为8
            pstm.setString(2,"SANJIN");//2表示第二个参数,插入SANJIN

            //执行
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(connection,pstm,null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

防止SQL注入本质,传递字符 带有“ ”,转义字符会被转义

10.6 使用IDEA连接数据库

在这里插入图片描述

10.7 JDBC事务

要么都成功,要么都失败

ACID原则

原子性:要么全部完成,要么都不完成

一致性:结果总数不变

隔离性:多个进程互不干扰

持久性:一旦提交不可逆,持久化到数据库了

隔离性的问题:

脏读: 一个事务读取了另一个没有提交的事务

不可重复读:在同一个事务内,重复读取表中的数据,表发生了改变

虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致

代码实现

  1. 开启事务conn.setAutoCommit(false);

  2. 一组业务执行完毕,提交事务

  3. 可以在catch语句中显示的定义回滚,但是默认失败会回滚

    package com.kuang.lesson04;
    
    import com.kuang.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class Action {
        public static void main(String[] args) {
    
            Connection conn =null;
            PreparedStatement ps = null;
            ResultSet rs = null;
    
            try {
                conn = JdbcUtils.getConnection();
                //关闭数据库的自动提交功能, 开启事务
                conn.setAutoCommit(false);
                //自动开启事务
                String sql = "update account set money = money-500 where id = 1";
                ps =conn.prepareStatement(sql);
                ps.executeUpdate();
                String sql2 = "update account set money = money-500 where id = 2";
                ps=conn.prepareStatement(sql2);
                ps.executeUpdate();
    
                //业务完毕,提交事务
                conn.commit();
                System.out.println("操作成功");
            } catch (Exception e) {
                try {
                    //如果失败,则默认回滚
                    conn.rollback();//如果失败,回滚
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtils.release(conn,ps,rs);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
    

10.8数据库连接池

数据库连接–执行完毕–释放

连接–释放 十分浪费资源

池化技术: 准备一些预先的资源,过来就连接预先准备好的

常用连接数 100

最少连接数:100

最大连接数 : 120 业务最高承载上限

排队等待,

等待超时:100ms

编写连接池,实现一个接口 DateSource

开源数据源实现(拿来即用)

DBCP

C3P0

Druid: 阿里巴巴

使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了

DBCP

需要用到的jar包

commons-dbcp-1.4.jar

commons-pool-1.6.jar

C3P0

结论

无论使用什么数据源,本质是不变的,DateSource接口不会变,方法就不会变

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页