devbox
Guff_9hys
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

狂神mysql笔记完整版

作者：Guff_9hys | 2024-07-17 16:10:10

赞

踩

狂神mysql笔记

2、 操作数据库

2.1、数据库的字段属性

UnSigned

  • 无符号的
  • 声明该数据列不允许负数 .

ZEROFILL

  • 0填充的
  • 不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

  • 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)

  • 通常用于设置主键 , 且为整数类型

  • 可定义起始值和步长

    • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
    • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

  • 默认为NULL , 即没有插入该列的数值
  • 如果设置为NOT NULL , 则该列必须有值

DEFAULT

  • 默认的
  • 用于设置默认值
  • 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

2.2、创建数据库表(重点)

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

-- 注意点，使用英文()，表的名称和字段尽量使用``括起来
-- AUTO INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加，（英文的），最后一个不用加

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '姓别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)	
)ENGINE=INNODB DEFAULT CHARSET=utf8

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

格式

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

常用命令

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

  • 1
  • 2
  • 3

2.3、 数据库表的类型

-- 关于数据库引擎
/*
INNODB	默认使用
MYISAM 	早些年使用的
*/

  • 1
  • 2
  • 3
  • 4
  • 5
MYISAM（早些年使用的）INNODB（默认使用）
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大，约为两倍

常规使用操作：

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

在物理空间存在的位置

所有数据库文件都在data目录下

本质还是文件的存储！

MYSQL引擎是在物理文件上的区别

  • innoDB在数据库中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
  • MYISAM对应文件
    • *.frm – 表结构定义文件
    • *.MYD – 数据文件（data）
    • *.MYI – 索引文件（index）

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

CHARSET=UTF8

  • 1

2.4、修改删除表

修改

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

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[];
ALTER TABLE teacher1 MODIFY age VARCHAR(11);	-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[];
ALTER TABLE teacher1 CHANGE age ageq INT(1);	-- 字段重命名

-- 删除表的字段	ALTER TABLE 表名 DROP 字段名;
ALTER TABLE teacher1 DROP ageq;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

删除

-- 删除表	如果存在再删除
DROP TABLE IF EXISTS teacher1;

  • 1
  • 2

注意：

  • `` 字段名，使用反引号包裹
  • 注释 – /**/
  • sql关键字大小写不敏感，建议写小写

3、数据管理

3.1、外键（了解）

主键PK_，唯一键UK_，外键FK_

方式一、在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade`(
	`gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 学生表的 gradeid 字段 要去引用年纪表的 gradeid
-- 定义外键key
-- 给这个外键添加约束 （执行引用）references 引用
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '姓别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）

创建表成功后添加外键约束

CREATE TABLE `grade`(
	`gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 学生表的 gradeid 字段 要去引用年纪表的 gradeid
-- 定义外键key
-- 给这个外键添加约束 （执行引用）references 引用
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '姓别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`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 那个表 (那个字段)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27

以上操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多造成困扰）

最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
  • 我们想使用多张表的数据，要使用外键（程序实现）

3.2、DML 语言（重点）

**数据库的意义：**数据存储数据管理

DML语言：数据库操作语言

  • insert
  • update
  • delete

3.3、添加

INSTER

-- 插入语句（添加）
-- INSERT INTO 表名(字段一,字段二,字段三) VALUES('值1','值2','值3');
INSERT INTO `grade`(`gradename`) VALUES('大四');

-- 由于主键自增我们可可以省略（如果不写表的字段他就会一一匹配）
INSERT INTO `grade`VALUES('大三');

-- 一般写插入语句，我们需要数据和字段一一对应！

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');

INSERT INTO `student`(`name`)VALUES('张三');
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('张三','aaaaa','男');
INSERT INTO `student`(`name`,`pwd`,`sex`)
	VALUES('李四','aaaaa','男'),('王五','aaaaa','男');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

语法：INSERT INTO 表名(字段一,字段二,字段三) VALUES('值1','值2','值3');

注意事项：

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

3.4、修改

UPDATE

-- 修改学员名字,带条件
UPDATE `student` SET `name`='hwt' WHERE `id`=1;

-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='老六';

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='嘎子',`email`='gazi@163.com' WHERE id = 1;

-- 语法
-- UPDATE 表名 SET 列名 = '修改的值',列名 = '修改的值',.... WHERE 条件;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

条件：where字句 运算符 id等于某个值，大于某个值，属于某个区间内修改

操作符含义范围结果
=等于5=6flase
<>或!=
>
<
<=
>=
BETWEN…AND…在某个范围内[2,5]
AND我和你&&5>x and x>2false
OR我或你||5>x or x>2true
-- 通过多个条件定位数据
UPDATE `student` SET `name`='老八' WHERE `name`='老六'AND `sex`='男';

  • 1
  • 2

语法：UPDATE 表名 SET 列名 = '修改的值',列名 = '修改的值',.... WHERE 条件;

注意;

  • colnum_name 是数据库的列，尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有的列
  • value，是一个具体的值，也可以是一个变量
  • 多个设置属性之间使用英文逗号隔开
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='老八'AND `id`=2;

  • 1

3.5、删除

DELETE

语法：DELETE FROM 表名 WHERE 条件;

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`;

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和约束不会变！

-- 清空 student 表
TRUNCATE `student`;

  • 1
  • 2

DELETE和TRUNCATE 区别

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

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

DELETE FROM `test`; 	-- 不会影响自增

TRUNCATE TABLE `test`	-- 自增会归零

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

了解：DELETE删除问题，重启数据库之后

  • InnoDB 自增列会从1开始（存在内存中的，断电即失）
  • MyISAM 继续上一个自增量开始（存在文件中的，不会丢失）

4、 DQL查询数据（重点）

4.1、DQL

（Data Query Language:数据库查询语言）

  • 所有的查询操作都用Select
  • 数据库中最核心的语句
  • 使用频率最高的语句
-- 测试用表
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;
-- 创建年级表
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;


-- 插入学生数据 其余自行添加 这里只添加了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,'预科班');
-- 插入科目数据
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);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85

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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

语法：SELECT 字段,... FROM 表

有的时候，列名字不是那么见名知意，就需要起别名 AS（字段名 AS 别名） （字段名 AS 别名）

去重 distinct

作用：去除SELECT查询出来的结果中的重复数据，重复数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result;   -- 查询全部的考试成绩

SELECT `studentno` FROM result; -- 查询有哪些同学参加了考试
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM result;


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

数据库的列（表达式）

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

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量。。。。

select 表达式 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;
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;
SELECT `studentno`,`studentresult`FROM result WHERE NOT `studentno`=1000;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

模糊查询：比较运算符

运算符语法描述
IS NULLa is null如果操作符为NULL，结果为真
IS NOT NULLa is not null如果操作符不为NULL，结果为真
BETWEENa between b and c若a在b和c之间，则结果为真
LIKEa like bSQL匹配，如果a匹配b，则结果为真
INa in(a1,a2,a3…)假设a在a1，或者a2…其中一个值中，结果为真
-- 查寻姓赵的同学
-- 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 '%嘉%';

-- =========in=========
-- 查询1001，1002，1003号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN(1001,1002,1003);

-- 查询在北京的学生
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 `address` IS NOT NULL;

-- 查询没有出生日期的同学  不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IS NULL;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

4.4、联表查询

JOIN

请添加图片描述
在这里插入图片描述

-- 查询参加了考试的同学
SELECT * FROM student
SELECT * FROM result;
/*
思路：
1. 分析需求，分析查询的字段来自那些表（连接查询）
2. 确定那种连接查询？  7种
确定交叉点（这两个表中那个数据是相同的）
判断条件：学生表中 studentno =成绩表studentno
*/
SELECT s.studentno,studentname,subjectno,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;


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
操作描述
Inner join如果表中至少有一个匹配，就返回行
Left join会从左表中返回所有的值，即使右表中没有匹配
Right join会从右表中返回所有的值，即使左表中没有匹配
-- join on(判断的条件） 连接查询
-- where      等值查询

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

-- 我要查询哪些数据 select 。。。
-- 从哪几个表查from 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表表查询，慢慢来，先查询两张表然后慢慢增加

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

自联接

自己和自己的表建立连接， 一张表侪分为两张一样的表即可

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','办公信息');


-- 查询父子信息
SELECT a.`categoryname`AS'父栏目',b.`categoryname`AS'子栏目'
FROM `category` AS a, `category`AS b
WHERE a.`categoryid`=b.`pid`;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

父类

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

子类

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

查询：查询父类对应的子类关系

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术技术ps技术

4.5、分页和排序

排序

-- ==========分页limit和排序order by==============
-- 排序 ： 升序 ASC ，降序DESC
-- 查询的结果根据成绩降序 排序
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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

分页

-- 为什么分页
-- 缓解数据库的压力，更好的体验，瀑布流
-- 语法： Limit 起始值，页面的大小
-- 网页应用 ： 当前，总的页数，页面的大小
-- LIMIT 0,5	1-5
-- LIMIT 1,5	2-6
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 0,5;

-- 第一页  limit  0，5	   （1-1）*5
-- 第二页  limit  5，5     （2-1）*5
-- 第三页  limit  10，5    （3-1）*5
-- 第N页   limit  0，5    （n-1）*pageSize，pageSize
-- 【pageSize：页面大小】
-- 【(n-1)*pageSize:起始值】
-- 【n：当前页】
-- 【数据总大小 / 页面大小 = 总页数】

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

语法：limit（查询开始下标，pageSize）

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'
);

-- 查询所有数据库结构-1 的学生学号
SELECT subjectno FROM `subject`WHERE subjectname='数据库结构-1';

-- 分数不小于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'
);

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心： （根据不同的课程分组）
SELECT subjectname,AVG(`studentresult`) 平均分,MAX(`studentresult`) 最高分,MIN(`studentresult`) 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=r.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均分>80;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

4.8、Select小结

顺序
select 去重 要查询的字段 from 表 （表和字段可以取别名）
xxx join 要连接的表 on 等值判断
where （具体的值，子查询语句）
group by（通过那个字段来分组）
having （过滤分组后的信息，条件和where是一样的，位置不同）
order by ..（通过那个字段排序）[升序/降序]
limit startindex，pagesize

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

5、Mysql函数

官网：https://dev.mysql.com/doc/refman/8.0/en/

5.1、常用函数

-- ===============常用函数==================
-- 数学运算
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('s','t','u');		-- 拼接字符串
SELECT INSERT('aalloword',1,2,'he');	-- 查询，从某个位置开始替换某个长度
SELECT LOWER('HELLOWORLD');		-- 小写字母
SELECT UPPER('helloword');		-- 大写字母
SELECT INSTR('helloword','o');		-- 返回第一次出现的字符串索引
SELECT REPLACE('aalloword','aa','he');	-- 替换出现的指定字符串
SELECT SUBSTR('helloword','6','9');	-- 返回指定的字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('drowlleh');		-- 反转


-- 查询姓赵的同学
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();	-- 版本


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

5.2、聚合函数（常用）

函数名称描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
。。。。。。
-- =============聚合函数==========
-- 都能够统计表中的数据（想查询一个表中有多少个记录，就使用这个count()
SELECT COUNT(studentname) FROM student;	     -- 指定列，会忽略null值
SELECT COUNT(*)FROM student;   		     -- 不会忽略null值，包括了所有的列，本质计算行数
SELECT COUNT(1)FROM result;		     -- 不会忽略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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

5.3、数据库级别的MD5加密（扩展）

什么是MD5？

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

特点：MD5不可逆，具体的值的md5是一样的

MD5破解网站的原理是一个字典，MD5加密后的值，加密前的值

-- ===============测试MD5=============
CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20)NOT NULL,
	`pwd` VARCHAR(50)NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'zhangsan','123456'),(3,'zhangsan','123456');

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1;

UPDATE testmd5 SET pwd=MD5(pwd);   -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'));

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

6、事务

6.1、什么是事务

要么都成功，或者都失败

将一组SQL放在一个批次中去执行

事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读…）

原子性（Atomicity）
事务中的操作要么都成功，要么都失败。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
持久性（Durability）
事务一旦被提交，它对数据库中数据的改变就是永久性的，即使发生故障也不应该对其有任何影响（不可逆）

隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。（互不影响）

https://blog.csdn.net/dengjili/article/details/82468576

隔离导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

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

虚读（幻读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

执行事务

-- ===================事务==================
-- mysql 是默认开启事务自动提交的
SET autocommit = 0;	-- 关闭
SET autocommit = 0;	-- 开启

-- 手动处理事务
SET autocommit = 0;	-- 关闭自动提交

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

-- 提交：持久化（成功！）
ROLLBACK

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

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

模拟场景

-- 转账
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB 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'; -- A-500
UPDATE account SET money=money+500 WHERE `name`='B'; -- A+500

COMMIT;		-- 提交事务，就被持久化了！
ROLLBACK;	-- 回滚

SET autocommit = 1;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

7、索引

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;

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('赵');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

7.2、测试索引

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'性别',
`password` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '密码',
`age` TINYINT(4) DEFAULT NULL 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_data()
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 1;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'123e456@qq.com',FLOOR(RAND()*((999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));		
		SET i=i+1;
	END WHILE;
	RETURN i;
END;
SELECT mock_data();



DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 999;
WHILE i<num DO
INSERT INTO app_user(NAME,email,phone,gender,PASSWORD,age)VALUES(CONCAT('用户',i),'123e456@qq.com',FLOOR(RAND()*((999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END$$
SELECT mock_data();

SELECT * FROM app_user WHERE `name` ='用户999';   -- 4.022sec
SELECT * FROM app_user WHERE `name` ='用户999';   -- 4.082sec
SELECT * FROM app_user WHERE `name` ='用户999';   -- 3.504sec

EXPLAIN SELECT * FROM app_user WHERE `name` ='用户999';  -- 1986771行

SELECT * FROM student;

-- id_表名_字段名_
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`); 
SELECT * FROM app_user WHERE `name` ='用户999';  -- 0.123sec
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户999';  -- 1

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61

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

7.3、索引原则

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

索引的数据结构

https://blog.csdn.net/weixin_40462767/article/details/105958138

Hash类型的索引

Btree：InnoDB的默认数据结构

8、权限管理和数据库备份

8.1、用户管理

SQLyog可视化管理

在这里插入图片描述

SQL命令操作

用户表：mysql。user

使用命令创建用户就是对user表的数据进行修改

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER hwt IDENTIFIED BY '20230201';

-- 修改密码
SET PASSWORD = PASSWORD('123456');		-- 5.0修改当前用户密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; -- 8.0之后使用


-- 重命名 RENAME USER 原名 TO 新名;
RENAME USER hwt TO hwt2;

-- 用户授权 GRANT ALL PRIVILEGES ON *.* TO hwt2
-- ALL PRIVILEGES ON 可以授权除了 给用户授权权限 之外的所有权限，
GRANT ALL PRIVILEGES ON *.* TO hwt2

-- 查询权限
SHOW GRANTS FOR 'hwt2'@'localhost';-- 查看指定用户权限

SHOW GRANTS FOR 'root'@'localhost';-- 查看root用户权限

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 'hwt2'@'localhost';

-- 删除用户
DROP USER 'hwt2'@'localhost';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

8.2、Mysql备份

MySql数据库备份的方式

  • 直接拷贝物理文件

  • sqlyog之类可视化工具导出

请添加图片描述

  • 使用命令行导出 mysqldump 命令行使用
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1，表2，表3 > 物理磁盘位置/文件名

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘位置/文件名

# 导入
# 登陆的情况下，切换到指定的数据库
# scoure 备份文件
source d:/a.sql

mysql -u用户名 -p密码 库名<备份文件

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

9、数据库的三大范式

9.1、设计的原因

不进行数据库设计的后果：

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

良好的数据库设计：

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

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

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

设计数据库的步骤：（个人博客）

  • 收集信息，分析需求
    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的基本信息）
    • 评论表
    • 友链表（友情链接信息）
    • 自定义表（（系统信息，某个关键字，或者一些主字段）key：value
    • 说说表（发表心情… id… content… create_create_time
  • 标识实体（把需求落地到每个字段）
    • 评论： user --> blog
    • 创建分类： user --> category
    • 关注： user --> user
    • 友链： links
    • 评论：user-user-blog
USE `hwt`;

DROP TABLE IF EXISTS `blog`;

CREATE TABLE `blog` (
  `id` int NOT NULL COMMENT '文章的唯一标识',
  `title` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章标题',
  `author_id` int NOT NULL COMMENT '编写文章的用户',
  `category_id` int NOT NULL COMMENT '文章分类',
  `content` text COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章内容',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  `love` int DEFAULT NULL COMMENT '喜欢',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `category_name` varchar(30) NOT NULL COMMENT '分类标题',
  `create_user_id` int NOT NULL COMMENT '创建用户的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `comment`;

CREATE TABLE `comment` (
  `id` int NOT NULL COMMENT '评论id',
  `blog_id` int NOT NULL COMMENT '所属文章',
  `user_id` int NOT NULL COMMENT '评论人',
  `content` varchar(2000) NOT NULL COMMENT '评论的内容',
  `creat_time` datetime NOT NULL COMMENT '评论时间',
  `user_id_parent` int NOT NULL COMMENT '回复的人的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `links`;

CREATE TABLE `links` (
  `id` int NOT NULL COMMENT '友链id',
  `links` varchar(50) NOT NULL COMMENT '网站名称',
  `href` varchar(2000) NOT NULL COMMENT '网站链接',
  `sort` int NOT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户的唯一id',
  `username` varchar(60) NOT NULL COMMENT '用户名',
  `password` varchar(60) NOT NULL COMMENT '密码',
  `sex` varchar(2) NOT NULL COMMENT '性别',
  `age` int DEFAULT NULL COMMENT '年龄',
  `sign` varchar(200) DEFAULT NULL COMMENT '签名',
  `open_id` varchar(10000) DEFAULT NULL COMMENT '微信id',
  `avatar` varchar(1000) DEFAULT NULL COMMENT '头像链接地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


DROP TABLE IF EXISTS `user_follow`;

CREATE TABLE `user_follow` (
  `id` int NOT NULL COMMENT '唯一标识',
  `user_id` int NOT NULL COMMENT '被关注的id',
  `follow_id` int NOT NULL COMMENT '关注人的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70

9.2、三大范式

为什么要数据规范化？

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

三大范式 作用：规范数据库的设计

第一范式（1NF）

​ 要求数据库表的每一列都是不可分割的原子数据项（原子性）

第二范式（2NF）

	满足第一范式的基础上，每一张表只能描述一件事情（唯一性）

  • 1

第三范式（3NF）

​ 确保数据表中的每一列数据都和主键直接相关，而不能间接相关（冗余性）

https://blog.csdn.net/GB__LaoWang/article/details/117082522

（规范数据库的设计）

规范性 和 性能的问题

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

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

10、jdbc

10.1、数据库驱动

img

程序只能通过数据库驱动和数据库打交道！

10.2、JDBC

SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。

这套接口由数据库厂商去实现，

开发人员只需要学习JDBC接口，并通过JDBC加载具体的驱动，就可以操作数据库。

img

java.sql

javax.sql

数据库驱动包：mysql-connector-java-5.1.47.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','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

1、创建一个普通项目

2、导入数据库驱动

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2. 用户信息和url
        // userUnicode=true 支持中文编码&
        // characterEncoding=utf8
        // 使用utf-8
        // useSSL=true 使用安全连接
        // userUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";
        //3. 连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, 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("pwd = " + resultSet.getObject("PASSWORD"));
            System.out.println("email " + resultSet.getObject("email"));
            System.out.println("birth = " + resultSet.getObject("birthday"));
        }

        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39

步骤总结：

  1. 加载驱动
  2. 连接数据库 DriveManager
  3. 获得执行sql的对象 Statement
  4. 获得返回的结果集
  5. 释放连接

DriveManager

//DriverManager.getConnection(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

URL

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

//mysql -- 3306
//协议：//主机地址：端口号/数据库名？参数？参数1&参数二&参数3

//oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sid

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

Statement 执行sql的对象 PreparedStatement：执行sql的对象

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

statement.executeQuery();//查询操作返回 ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//更新，插入，删除。  返回一个受影响的行数

  • 1
  • 2
  • 3
  • 4
  • 5

ResultSet : 查询的结果集：封装了所有的查询结果

resultSet.getObject();//在不知道类的类型的情况下使用
//知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

遍历,指针

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

  • 1
  • 2
  • 3
  • 4
  • 5

释放资源

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

  • 1
  • 2
  • 3
  • 4

10.4、statement对象

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

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的Resultset对象。

CRUD操作-inster

使用executeUpdate（String sql）方法完成数据的添加

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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("插入成功！")
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作-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("插入成功！")
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作-select

使用executeQuery（String sql）方法完成数据的修改

Statement statement = connection.createStatement();
String sql = "update user set name='' where name = ''";
ResultSet rs = statement.executeQuery(sql);
while(rs.next()){
    //根据获取列的数据类型，分别调用rs的相应方法映射到Java对象中
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

代码实现

编写工具类

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static{
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.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 connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
        }if (statement!=null){
            statement.close();
        }if (connection!=null){
            connection.close();
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39

增删改方法executeUpdate()

public class TestInster {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            //替换指定的sql语句即可
            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(4,'hwt','root','717287937@qq.com','2023-02-02');";//增
            
            String sql = "DELETE FROM users WHERE id = 4;";//删
            String sql = "UPDATE users SET `NAME` = 'hwt',`PASSWORD`='123456' WHERE id = 4;";//修改

            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

查询executeQuery()

public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT * FROM users;";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("NAME="+resultSet.getString("NAME"));
                System.out.println("PASSWORD="+resultSet.getString("PASSWORD"));
                System.out.println("email="+resultSet.getString("email"));
                System.out.println("birthday="+resultSet.getDate("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

10.5、SQL注入

sql存在漏洞会被攻击导致数据泄露

**原理：**sql被拼接

//sql注入
public class SqlImmit {
    public static void main(String[] args) throws SQLException {
//        login("hwt","123456");
//        login("'or '1=1","123456");
        login("'or '1=1","'or '1=1");//拼接字符串

    }
    //登录业务
    public static void login(String username,String password) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            //SELECT * FROM users WHERE `NAME`='"+username+"'AND`PASSWORD`='"+password+"';
//            SELECT * FROM users WHERE `NAME`='"+'' or '1=1'+"'AND`PASSWORD`='"+'' or '1=1'+"';
            String sql = "SELECT * FROM users WHERE `NAME`='"+username+"'AND`PASSWORD`='"+password+"';";

            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println("NAME="+resultSet.getString("NAME"));
                System.out.println("PASSWORD="+resultSet.getString("PASSWORD"));
                System.out.println("************");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

10.6、PreparedStatement对象

PreparedStatement可以防止sql注入

增

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            //区别
            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";

            preparedStatement = connection.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"hwt");
            preparedStatement.setString(3,"root");
            preparedStatement.setString(4,"717287937@qq.com");
            //注意点：sql.Date      数据库     java.sql.Date()
            //       util.Date     Java       new Date().getTime()获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

删

public class TestDelete {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            //区别
            String sql = "DELETE FROM users WHERE id = ?;";

            preparedStatement = connection.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值
            preparedStatement.setInt(1,4);

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

改

public class TestUpdate {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            //区别
            String sql = "UPDATE users SET `NAME` = ?,`PASSWORD`=? WHERE id = ?;";

            preparedStatement = connection.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值
            preparedStatement.setString(1,"hwt1");
            preparedStatement.setString(2,"root");
            preparedStatement.setInt(3,4);

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

查

public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //区别
            String sql = "SELECT * FROM users WHERE id = ?;";
            preparedStatement = connection.prepareStatement(sql);//预编译，先写sql，然后不执行
            //手动赋值
            preparedStatement.setInt(1,4);
            //执行
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("NAME="+resultSet.getString("NAME"));
                System.out.println("PASSWORD="+resultSet.getString("PASSWORD"));
                System.out.println("email="+resultSet.getString("email"));
                System.out.println("birthday="+resultSet.getDate("birthday"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

防止sql注入

public class SqlImmit {
    public static void main(String[] args) throws SQLException {
//        login("hwt","root");
//        login("'or '1=1","root");
        login("'or '1=1","'or '1=1");//拼接字符串
    }
    //登录业务
    public static void login(String username,String password) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            connection = JdbcUtils.getConnection();
            //PreparedStatement 防止SQL注入的原理是，把传递进来的参数当做是字符
            //假设其中存在转义字符，会被直接转义
            String sql = "SELECT * FROM users WHERE `NAME`= ? AND `PASSWORD`= ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);

            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                System.out.println("NAME="+resultSet.getString("NAME"));
                System.out.println("PASSWORD="+resultSet.getString("PASSWORD"));
                System.out.println("************");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

10.7、事务

-- 测试用表
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);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

测试

  1. 开启事务connection.setAutoCommit(false);
  2. 一组执行完毕，提交事务
  3. 可以在catch语句中显式定义回滚语句，但是默认会回滚
public class TestTransaction1 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            //关闭数据库自动提交
            connection.setAutoCommit(false);//开启事务

            String sql1 = "UPDATE account SET money=money-100 WHERE `name` = 'A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

//            int x = 1/0;//长须停止执行

            String sql2 = "UPDATE account SET money=money+100 WHERE `name` = 'B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //业务完成，提交事务
            connection.commit();
            System.out.println("操作成功");

        }catch (Exception e){
            System.out.println("操作失败");
            connection.rollback();//操作失败，回滚
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

10.8、数据库连接池

数据库连接 --> 执行完毕 --> 释放

连接到释放也会浪费资源

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

  • 最小连接数（一般就是常用连接数）
  • 最大连接数：业务最高连接上限
  • 等待超时

编写数据连接池，实现一个接口DateSource

开源数据实现DBCP

DBCP

C3P0

Druid：阿里

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

commons-pool-1.6.jar下载地址

https://mvnrepository.com/artifact/commons-pool/commons-pool/1.6
commons-dbcp-1.4.jar下载地址
https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp/1.4

c3p0-0.9.5.5.jar

https://mvnrepository.com/artifact/com.mchange/c3p0/0.9.5.5

mchange-commons-java-0.2.19.jar https://mvnrepository.com/artifact/com.mchange/mchange-commons-java/0.2.19

在这里插入图片描述

DBCP

commons-pool-1.6.jar
commons-dbcp-1.4.jar

dbcpconfig.properties 文件

#DBCP数据源中定义好的名字
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSl=true
username=root
password=root
#初始化连接数
initialSize=10
#最大连接数
maxActive=50
#最大空闲连接
maxIdle=20
#最小空闲连接
minIdle=5
#最长等待超时时间 以毫秒为单位
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=UTF8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

工具类

public class JdbcUtils_DBCP {
    private static DataSource dataSource;
    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 connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
        }if (statement!=null){
            statement.close();
        }if (connection!=null){
            connection.close();
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

测试类

public class DBCP {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils_DBCP.getConnection();

            //区别
            String sql = "UPDATE users SET `NAME` = ?,`PASSWORD`=? WHERE id = ?;";

            preparedStatement = connection.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值
            preparedStatement.setString(1,"hwt1");
            preparedStatement.setString(2,"root");
            preparedStatement.setInt(3,4);

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

C3P0

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
    c3p0的命名配置
    如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省（默认）
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

工具类

public class JdbcUtils_C3P0 {

    private static ComboPooledDataSource dataSource = null;
    static{
        try {
            //代码配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();
            
            //创建数据源     工厂模式 -->  创建
              dataSource = new ComboPooledDataSource("MySQL");//配置文件写法

        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //2.获取链接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
    //3.释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
        }if (statement!=null){
            statement.close();
        }if (connection!=null){
            connection.close();
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

测试类

public class C3P0 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils_C3P0.getConnection();

            String sql = "UPDATE users SET `NAME` = ?,`PASSWORD`=? WHERE id = ?;";

            preparedStatement = connection.prepareStatement(sql);//预编译，先写sql，然后不执行

            //手动赋值
            preparedStatement.setString(1,"hwt1");
            preparedStatement.setString(2,"root");
            preparedStatement.setInt(3,4);

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(connection,preparedStatement,resultSet);
        }
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

结论

无论使用什么数据源，本质必须是一样的，DataSource接口不会变，方法就不会变

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Guff_9hys/article/detail/840823?site
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号