MySQL详细学习笔记

于 2022-04-09 19:39:18 发布
阅读量750 收藏
点赞数
分类专栏： MySQL 文章标签： mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_49133806/article/details/124065979
版权
本文详细介绍了MySQL数据库的基础知识，包括数据库的分类、安装与管理，以及操作数据库的步骤。讲解了数据类型、字段属性、创建表、存储引擎、DML语句（INSERT、UPDATE、DELETE）、DQL查询数据、事务处理、索引和权限管理。内容涵盖从数据库安装到实际操作的全过程，适合初学者入门。
摘要由CSDN通过智能技术生成

文章目录

1、MySQL

前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和给前端传递数据））

数据库（存数据，Txt，Excel，word）

操作系统，数据结构与算法！当一个不错的程序员！

离散数学、数字电路、体系结构、编译原理。+实战经验，高级程序员～一个优秀的程序员

1.1、为什么学习数据库

  • 现在的世界，大数据时代，得数据库者得天下。
  • 被迫需求：存数据
  • 数据库是所有软件体系中最核心的存在 DBA

1.2、什么是数据库

数据库（DB，DataBase）

概念：数据仓库、软件、安装在操作系统（Windows、Linux、mac，…）之上！

作用：存储数据，管理数据

1.3、数据库分类

关系型数据库：（SQL）

  • MySQL，Oracle，SqlServer，DB2，SQLlite
  • 通过表和表之间，行和列之间的关系进行数据的存储。

非关系型数据库：（NoSQL） Not Only

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

DBMS(数据库管理系统)

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

1.4、MySQL简历

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

  • 前世：瑞典MySQL AB公司

  • 今生：属于Oracle旗下产品

  • MySQL是最好的 RDBMS (Relational Database Management System关系数据库管理系统) 应用软件之一，MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

  • 开软的数据库软件～

  • 体积小、速度快、总体拥有成本低，尤其是开放源码这一特点、中小网站，或者大型网站。

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

安装MySql

官网安装地址：https://dev.mysql.com/downloads/mysql/

RDBMS（Relational Database Management System）：关系型数据库管理系统

安装建议：尽量不要用.exe进行安装，用压缩包安装，对日后的卸载更为方便

1、下载得到zip压缩包

在这里插入图片描述

2、添加环境变量

  • 我的电脑–>属性–>高级–>环境变量
  • 选择path添加：mysql安装目录下的bin文件夹地址,

在这里插入图片描述

3、新建mysql 配置文件

  • 在你的mysql安装目录下，新建my.ini文件，编辑 my.ini文件，注意替换路径位置
[mysqld]
basedir=G:\Program Files\MySql\mysql-8.0.28\
datadir=G:\Program Files\MySql\mysql-8.0.28\data\
port=3306
#免密登录
skip-grant-tables

G:\Program Files\MySql\mysql-8.0.28\要换成自己的目录

4、安装mysql服务

  • 启动管理员模式下的cmd，并将路径切换至mysql安装目录下的bin目录，输入以下命令

cd \d G:\Program Files\MySql\mysql-8.0.28\bin
mysqld -install

5、初始化数据文件

  • 输入以下命令

mysqld --initialize-insecure --user=mysql

启动mysql

net start mysql

6、进入mysql管理界面修改密码（第一次输入密码回车即可）

  • 输入以下第一条命令进入管理界面（p后面没有空格！！）

  • 在输入第二条命令修改密码（最后一定要有分号）

    mysql -u root -p
update mysql.user set authentication_string=password('你的密码') where user='root' and Host = 'localhost';

刷新权限

flush privileges;

9、重启mysql即可正常使用

my.ini文件删除最后一句skip-grant-tables

重启mysql即可正常使用

#退出mysql管理界面
exit
#停止mysql服务
net stop mysql
#开始mysql服务
net start mysql

基本命令

所有的语句都要以分号结尾

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库,-如果表名或者字段名是特殊字符，则需要带``
show tables;	--查看所有的表
describe 表名;	--显示表的信息
CREATE DATABASE [IF NOT EXISTS] 数据库名; --创建一个数据库
DROP DATABASE [if EXISTS] 数据库名;  删除数据库
exit	--退出连接

--		--单行注释
#		--单行注释
/*...*/		--多行注释

2、操作数据库

2.1、数据库的列类型

数值

数据类型描述大小
tinyint十分小的数据1个字节
smallint较小的数据2个字节
mediumint中等大小的数据3个字节
int标准的整数4个字节
bigint较大的数据8个字节
float浮点数4个字节
double浮点数8个字节
decimal字符串形式的浮点数，一般用于金融计算

字符串

数据类型描述大小
char字符串固定大小0~255
varchar可变字符串0~65535
tinytext微型文本2^8-1
text文本串2^16-1

时间日期

数据类型描述格式
date日期格式YYYY-MM-DD
time时间格式HH：mm：ss
datetime最常用的时间格式YYYY-MM-DD HH：mm：ss
timestamp时间戳，1970.1.1到现在的毫秒数
year年份表示

null

  • 没有值，未知
  • 不要使用NULL值进行计算

2.2、数据库的字段属性

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

  • 默认的
  • 用于设置默认值
  • 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

拓展：每一个表，都必须存在以下五个字段：

名称描述
id主键
version乐观锁
is_delete伪删除
gmt_create创建时间
gmt_update修改时间

2.3、创建数据库表

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 ( 20 ) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8

注意点：

  • 表名和字段尽量使用``括起来
  • AUTO_INCREMENT 代表自增
  • 所有的语句后面加逗号，最后一个不加
  • 字符串使用单引号括起来
  • 主键的声明一般放在最后，便于查看
  • 不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

格式

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

常用命令：

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

2.4、数据库存储引擎

INNODB

  • 默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

  • 早些年使用，节约空间，速度较快
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为2倍

数据库文件存在的物理空间位置：

MySQL数据表以文件方式存放在磁盘中

  • 包括表文件 , 数据文件 , 以及数据库的选项文件
  • 位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

MySQL在文件引擎上区别：

  • INNODB数据库文件类型就包括**.frm**、.ibd以及在上一级目录的ibdata1文件
  • MYISAM存储引擎，数据库文件类型就包括
    • .frm：表结构定义文件
    • .MYD：数据文件
    • .MYI：索引文件

2.5、修改数据库

修改

修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改

  • 修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名

  • 添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

删除字段 : ALTER TABLE 表名 DROP 字段名

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student;

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student ADD age INT(11);

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

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE student DROP age;

删除

语法：DROP TABLE [IF EXISTS] 表名

  • IF EXISTS为可选 , 判断是否存在该数据表
  • 如删除不存在的数据表会抛出错误
-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS student;

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

3、MySQL数据管理

3.1、外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

目标：学生表（student）的gradeid字段 要去引用年级表（grade）的 gradeid字段

创建外键

方式一：在创建表的时候增加约束

-- 创建年级表
CREATE TABLE `grade` (
	`gradeid` INT ( 10 ) NOT NULL auto_increment COMMENT '班级id',
	`gradename` VARCHAR ( 50 ) NOT NULL COMMENT '班级名称',
	PRIMARY KEY ( `gradeid` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8

/*
	1. 定义外键key
	2. 给外键添加约束（执行引用）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 '出生日期',
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR ( 20 ) DEFAULT NULL COMMENT '邮箱',
		`gradeid` INT ( 10 ) NOT NULL COMMENT '学生的年级',
		PRIMARY KEY ( `id` ),
		KEY `FK_gradeid` ( `gradeid` ),
	CONSTRAINT `FK_gradeid` FOREIGN KEY ( `gradeid` ) REFERENCES `grade` ( `gradeid` ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8

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

方法二：创建表成功后，添加外键约束

-- 创建年级表
CREATE TABLE `grade` (
	`gradeid` INT ( 10 ) NOT NULL auto_increment COMMENT '班级id',
	`gradename` VARCHAR ( 50 ) NOT NULL COMMENT '班级名称',
	PRIMARY KEY ( `gradeid` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8

/*
	1. 定义外键key
	2. 给外键添加约束（执行引用）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 '出生日期',
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR ( 20 ) DEFAULT NULL COMMENT '邮箱',
		`gradeid` INT ( 10 ) NOT NULL COMMENT '学生的年级',
		PRIMARY KEY ( `id` )
	) ENGINE = INNODB DEFAULT CHARSET = utf8	
-- 创建表的时候没有外键关系
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grande` (`gradeid`);

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

最佳实践

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

3.2、DML语言

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

Data Manipulation Luaguge：数据操作语言

1. 添加 insert
-- 普通用法
INSERT INTO `student`(`name`) VALUES ('jasper');

-- 插入多条数据
INSERT INTO `student` (`name`, `pwd`, `sex`) VALUES( 'jasper', '191127', '男' )
 
-- 省略字段
INSERT INTO `student` VALUES (5,'jasper','123456','男','2000-02-04','新疆','2271427740@qq.com',1);

语法：

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

注意：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的，但是值必须完整且一一对应
  3. 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开
2. 修改 update
-- 修改学员名字,指定条件
UPDATE `student` SET `name`='jasper' WHERE id=9;

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

-- 修改多个属性
UPDATE `student` SET `name`='jasper',`address`='新疆' WHERE id=5;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='jasper' WHERE `name`='aize' AND `pwd`='191127';

语法：

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：

操作符含义
操作符含义
=等于
<>或!=不等于
>大于
<小于
<=小于等于
>=大于等于
BETWEEN…AND…闭合区间
AND和
OR或
3. 删除 delete
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;

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

语法：

DELETE FROM 表名 [WHERE 条件]

关于DELETE删除的问题，重启数据库现象：

  • INNODB 自增列会从1开始（存在内存当中，断电即失）

  • MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）

TRUNCATE

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

DELETE和TRUNCATE 的区别：

  • DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表

  • TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增

  • DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

在这里插入图片描述

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

-- 会影响自增
TRUNCATE TABLE `student`;

4、DQL查询数据

Data QueryLanguage 数据查询语言

  • 查询数据库数据 , 如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}]; -- 指定查询的记录从哪条至哪条

前提配置：

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;

-- 用school数据库
USE `school`;

-- 创建年级表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;

-- 给grade表插入数据
INSERT INTO `grade`(`GradeID`,`GradeName`) 
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');

-- 创建成绩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;

-- 给result表插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);

-- 创建学生表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 '联系电话,允许为空,即可选输入',
	`Adress` 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;

-- 给学生表插入数据
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');

-- 创建科目表
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=18 DEFAULT CHARSET=utf8;

-- 给科目表subject插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);

SELECT 字段 FROM 表;

4.1、基础查询

语法：

SELECT 查询列表 FROM 表名;
  • 查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
  • 查询结果是一个虚拟的表格
-- 查询全部学生
SELECT * FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;

-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;

-- 查询系统版本(函数)
SELECT VERSION();

-- 用来计算(计算表达式)
SELECT	100 * 5+20 AS 计算结果 

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;

-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

-- 考试成绩+1分查兰
SELECT StudentNo,StudentResult+1 FROM result

4.2、条件查询

where 条件字句：检索数据中符合条件的值

语法：

select 查询列表 from 表名 where 筛选条件;

逻辑运算符

运算符语法描述
and &&a and b a&&b逻辑与，两个都为真，结果为真
or ||a or b a || b逻辑或，其中一个为真，则结果为
Not !not a ! a逻辑非，真为假! ，假为真!
-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
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;

-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';

-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

-- 查询地址为空的学生 null ''
SELECT * FROM student
WHERE Adress='' OR Adress IS NULL

4.3、分组查询

语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

区别：

使用关键字筛选的表位置
分组前筛选where原始表group by的前面
分组后筛选having分组后的结果group by 的后面
-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

4.4、连接查询

在这里插入图片描述

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;

-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;

-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
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='高数';

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 创建一个表
CREATE TABLE `course` (
`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
`pid` INT(10) NOT NULL COMMENT '父课程id',
`courseName` VARCHAR(50) NOT NULL COMMENT '课程名',
PRIMARY KEY (`courseid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `course` (`courseid`, `pid`, `courseName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

将该表进行拆分：

pid（父课程id）courseid（课程id）courseName（课程名）
12信息技术
13软件开发
15美术设计
pid（父课程id）courseid（课程id）courseName（课程名）
28办公信息
34数据库
36web开发
57ps技术

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

父类子类
信息技术 2办公信息 4
软件开发 3数据库 4、web开发 6
美术设计 5ps技术 7
SELECT a.`courseid` AS '父课程',b.`courseid` AS '子课程'
FROM course AS a,course AS b
WHERE a.`courseid`=b.`pid`;

4.5、排序和分页

排序

语法：

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc
  • order by的位置一般放在查询语句的最后（除limit语句之外）
asc ：升序，如果不写默认升序
desc：降序
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;

分页

语法：

select 查询列表
from 表
limit offset,pagesize;
  • offset代表的是起始的条目索引，默认从0开始
  • size代表的是显示的条目数
  • offset=(n-1)*pagesize
-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面

4.6、子查询

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

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
WHERE SubjectName='高数'
ORDER BY StudentResult DESC;

-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='高数'
)

4.7、MySQL函数

1. 常用函数
-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('azmat'); -- 转大写
SELECT LOWER('Aize'); -- 转小写
SELECT INSTR('aize','z'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('aize'); -- 反转字符串

-- 时间日期函数
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();
2. 聚合函数
函数描述
max最大值
min最小值
sum和
avg平均值
count计算个数
SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;

SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;

小结:

在这里插入图片描述

5、数据库级别的MD5加密

MD5信息摘要算法（MD5 Message-Digest Algorithm）

  • MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性
  • 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,'azmat','200024'),
(2,'aize','000421'),(3,'hero','123456');

-- 加密
UPDATE `testMD5` SET `pwd`=MD5(pwd) WHERE id=1;
UPDATE `testMD5` SET `pwd`=MD5(pwd); -- 加密全部的密码


-- 插入的时候加密
INSERT INTO `testMD5` VALUES(5,'baby',MD5('456789'));

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

6、事务

要么都成功，要么都失败

SQL执行：A转账给B
SQL执行：B收到A的钱

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

  • 例如银行转账：只有A转账成功且B成功到账，该事件才算结束，如果一方不成功，则该事务不成功

6.1、事务原则：ACID

参考链接：https://blog.csdn.net/dengjili/article/details/82468576

名称描述
原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务前后数据的完整性必须保持一致。
隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）事务一旦被提交则不可逆，被持久化到数据库中，接下来即使数据库发生故障也不应该对其有任何影响

6.2、事务并发导致的问题

隔离所导致的一些问题：

名称描述
脏读指一个事务读取了另外一个事务未提交的数据。
不可重复读在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读(幻读)是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

6.3、隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别

  • 读未提交：一个事务读取到其他事务未提交的数据；这种隔离级别下，查询不会加锁，一致性最差，会产生脏读、不可重复读、幻读的问题

  • 读已提交：一个事务只能读取到其他事务已经提交的数据；该隔离级别避免了脏读问题的产生，但是不可重复读和幻读的问题仍然存在；

    读提交事务隔离级别是大多数流行数据库的默认事务隔离级别，比如 Oracle，但是不是 MySQL 的默认隔离界别

  • 可重复读：事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同；该个里级别避免了脏读、不可重复度的问题，但是仍然无法避免幻读的问题

    可重复读是MySQL默认的隔离级别

  • 串行化：事务串行化执行，事务只能一个接着一个地执行,、，并且在执行过程中完全看不到其他事务对数据所做的更新；缺点是并发能力差，最严格的事务隔离，完全符合ACID原则，但是对性能影响比较大

事务隔离级别脏读不可重复读幻读
读未提交（read-uncommitted）是是是
读已提交（read-committed）否是是
可重复读（repeatable-read）否否是
串行化（serializable）否否否

6.4、执行事务的过程

MySql 是默认开启事务自动提交

1️⃣ 关闭自动提交

SET autocommit=0;

2️⃣ 事务开启

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

3️⃣ 成功则提交，失败则回滚

-- 提交：持久化（成功）
COMMIT

-- 回滚：回到原来的样子（失败）
ROLLBACK

4️⃣ 事务结束

SET autocommit=1; -- 开启自动提交

5️⃣ 其他操作

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

模拟转账：事务测试

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

use shop

-- 创建表
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';  -- B加500

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

SET autocommit = 1;  -- 恢复默认值 

SELECT * FROM account;

7、索引

推荐阅读：MySQL索引背后的数据结构及算法原理

索引（Index）是帮助MySQL高效获取数据的数据结构。

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化

7.1、索引的分类

-- 创建学生表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 '联系电话,允许为空,即可选输入',
	`Adress` 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;
主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只有一个列作为主键

  • 最常见的索引类型，不允许为空值
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置
-- 创建表的时候指定主键索引
CREATE TABLE tableName(
  ......
  PRIMARY INDEX (columeName)
)

-- 修改表结构添加主键索引
ALTER TABLE tableName ADD PRIMARY INDEX (columnName)
普通索引（KEY / INDEX）

默认的，快速定位特定数据

  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作
-- 直接创建普通索引
CREATE INDEX indexName ON tableName (columnName)

-- 创建表的时候指定普通索引
CREATE TABLE tableName(
  ......
  INDEX [indexName] (columeName)
)

-- 修改表结构添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)
唯一索引（UNIQUE KEY）

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

与主键索引的区别：主键索引只能有一个、唯一索引可以有多个

-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)

-- 创建表的时候指定唯一索引
CREATE TABLE tableName(  
	......
	UNIQUE INDEX [indexName] (columeName)  
);  

-- 修改表结构添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)
全文索引（FULLText）

快速定位特定数据（百度搜索就是全文索引）

  • 在特定的数据库引擎下才有：MyISAM
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集
-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

7.2、索引的使用

  1. 索引的创建
  • 在创建表的时候给字段增加索引
CREATE TABLE 表名 (
    字段名1 数据类型 [完整性约束条件…],
    字段名2 数据类型 [完整性约束条件…],
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(长度)] [ASC |DESC])
);
  • 创建完毕后，增加索引
-- 方法一：CREATE在已存在的表上创建索引
       CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
       ON 表名 (字段名[(长度)] [ASC |DESC]) ;

-- 方法二：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] 
       INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
  1. 索引的删除
-- 删除索引
	DROP INDEX 索引名 ON 表名;
-- 删除主键索引
	ALTER TABLE 表名 DROP PRIMARY KEY;
  1. 显示索引信息
SHOW INDEX FROM 表名;
  1. explain分析sql执行的情况
-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

7.3、测试索引

建表app_user：

CREATE TABLE `app_user` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT '' COMMENT '用户昵称',
    `email` varchar(50) NOT NULL COMMENT '用户邮箱',
    `phone` varchar(20) DEFAULT '' COMMENT '手机号',
    `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
    `password` varchar(100) NOT NULL COMMENT '密码',
    `age` tinyint(4) DEFAULT '0' COMMENT '年龄',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

-- 1418错解决方案(创建函数前执行此语句)
set global log_bin_trust_function_creators=true;

-- 插入100万条数据
DELIMITER $$	-- 写函数之前要写的标志
CREATE FUNCTION mock_data()	-- 创建mock_data()函数
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i < num DO
  		INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   		VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  		SET i = i + 1;
	END WHILE;
	RETURN i;
END;

-- 执行函数
SELECT mock_data();

在这里插入图片描述

测试查询速度

-- 查询用户名为'用户9999'性能分析
EXPLAIN SELECT * FROM app_user where name='用户99999'

在这里插入图片描述

增加索引后测试

在这里插入图片描述

对比两次结果，速度有了很大的提升

7.4、索引原则

  • 索引不是越多越好，小数据量的表不需要加索引
  • 不要对经常变动的数据增加索引
  • 索引一般加在经常要查询的列上

8、explain关键字

建议阅读：

9、权限管理和备份

9.1、用户管理

方式一：可视化管理

在这里插入图片描述

方式二：SQL命令操作

-- 创建用户
CREATE USER azmat IDENTIFIED BY '123456'

-- 删除用户
DROP USER azmat

-- 修改当前用户密码
SET PASSWORD = PASSWORD('200024')

-- 修改指定用户密码
SET PASSWORD FOR azmat = PASSWORD('200024')

-- 重命名
RENAME USER azmat to azmat2

-- 用户授权(授予全部权限,除了给其他用户授权)
GRANT ALL	PRIVILEGES on *.* TO azmat2

-- 查询权限
SHOW GRANTS FOR zsr
-- 查看root用户权限
SHOW GRANTS FOR root@localhost

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM azmat

9.2、数据库备份

保证重要的数据不丢失、数据转义

方式一：直接拷贝物理文件，MySQL数据表以文件方式存放在磁盘中

  • 包括表文件 , 数据文件 , 以及数据库的选项文件
  • 位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

方式二：可视化管理

Navicat打开要备份的数据库，然后点击新建备份

在这里插入图片描述

点击对象选择，这里可以自定义选择备份的表

在这里插入图片描述

选择完毕后，点击备份即可开始备份

在这里插入图片描述

等待备份完成，关闭，然后便可看到备份的文件

方式三：可视化管理

选中要导出的表，右键转储SQL文件

然就就可以得到.sql文件

方式四：命令mysqldump导出

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

# 导出school数据库的cource grade student表到D:/school.sql
mysqldump -hlocalhost -uroot -p1127 school course grade student >G:/school.sql

在这里插入图片描述

然后便可看到导出的sql文件

在这里插入图片描述

然后可以命令行登录mysql，切换到指定的数据库，用source命令导入 source G:/school.sql

10、三大范式

规范化理论：改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余的问题。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定规范化理论。在关系型数据库中这种规则就称为范式

三大范式的通俗理解

  • 如果一个关系模式R的所有属性都是不可分的数据项，则R属于第一范式
  • 如果关系模式R属于第一范式，且每一个非主属性完全函数依赖于码，则R属于第二范式
  • 若关系模式R属于第二范式，且R中所有的非主属性都直接依赖于码，则R属于第三范式

规范性问题：

数据库的范式是为了规范数据库的设计，但是实际中相比规范性，往往更需要看中性能、成本、用户体验等问题；

因此有时会故意给某些表增加一个冗余的字段，使多表查询变为单表查询。有时还会增加一些计算列，从大数据量变为小数据量（数据量大时，count(*)很耗时，可以直接添加一列，每增加一行+1，查该列即可）；阿里也曾提出关联查询的表最多不超过三张表。

这些就是为了性能、成本而舍弃一定规范性的例子

11、数据库驱动和JDBC

我们编写的程序会通过数据库驱动来和数据库进行交互

然后不同的数据库有不同的驱动，这不便于我们程序对各种数据库进行操作；因此为了简化对不同数据库的操作，SUN公司提供了一个Java操作数据库的规范JDBC；不同数据库的规范由对应的数据库厂商完成，对于开发人员，只需要掌握JDBC接口的操作即可

1. 第一个JDBC程序

1️⃣ 新建空项目

2️⃣ 导入mysql-connector-java

在项目目录下新建lib目录，放入jar包

下载地址：https://downloads.mysql.com/archives/c-j/

在这里插入图片描述

在这里插入图片描述

3️⃣ 编写代码&测试

在src目录下新建JDBCDemo用来操作数据库

import java.sql.*;

public class JDBCDemo {
   public static void main(String[] args) throws Exception {
      //1.加载驱动
      Class.forName("com.mysql.cj.jdbc.Driver");
      //2.连接信息url,用户信息
      String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
      String username = "root";
      String password = "";
      //3.连接,获得数据库对象connection
      Connection connection = DriverManager.getConnection(url, username, password);
      //4.获取执行sql的对象
      Statement statement = connection.createStatement();
      //5.执行sql
      String    sql       = "select * from app_user where id<10";
      ResultSet resultSet = statement.executeQuery(sql);
      while (resultSet.next()) {
         System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
      }
      //6.释放连接
      resultSet.close();
      statement.close();
      connection.close();
   }
}

在这里插入图片描述

2. JDBC对象

DriverManager

DriverManager：驱动管理

//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

本质上执行DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

//3.连接,获得数据库对象connection
Connection connection = DriverManager.getConnection(url, username, password);

connection代表数据库，因此可以设置事务自动提交，事务回滚等

在这里插入图片描述

Statement

Statement：执行sql的对象，用于向数据库发送SQL语句，想完成对数据库的增删改査，只需要通过这个对象向数据库发送增删改查语句即可

statement.executeQuery();//查询操作,返回结果
statement.execute();//执行sql
statement.executeUpdate();//用于增删改,返回受影响的行数

在这里插入图片描述

在这里插入图片描述

ResultSet

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

在这里插入图片描述

3. 封装jdbc工具类

1. 编写数据库配置文件

在src目录下新建db.properties，用于存放数据库配置信息

在这里插入图片描述

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=root
password=aize1127
2. 编写工具类

然后再src目录下新建JDBCUtils.java作为工具类

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

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 inputStream = JDBCDemo.class.getClassLoader().getResourceAsStream("db.properties");
         Properties properties = new Properties();
         properties.load(inputStream);
         driver = properties.getProperty("driver");
         url = properties.getProperty("url");
         username = properties.getProperty("username");
         password = properties.getProperty("password");
         //加载驱动
         Class.forName(driver);
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
   
   //获取连接
   public static Connection getConnection() throws SQLException {
      return DriverManager.getConnection(url, username, password);
   }
   
   //释放连接资源
   public static void release(Connection connection, Statement statement, ResultSet resultSet) {
      if (resultSet != null) {
         try {
            resultSet.close();
         } catch (SQLException throwables) {
            throwables.printStackTrace();
         }
      }
      if (statement != null) {
         try {
            statement.close();
         } catch (SQLException throwables) {
            throwables.printStackTrace();
         }
      }
      if (connection != null) {
         try {
            connection.close();
         } catch (SQLException throwables) {
            throwables.printStackTrace();
         }
      }
   }
}
3. 测试

修改JDBCDemo

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) throws SQLException {
        //获得数据库对象connection
        Connection connection = JDBCUtils.getConnection();
        //获取sql执行对象statement
        Statement statement = connection.createStatement();
        //执行sql
        String sql = "select * from app_user where id<10";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
        }
        //释放连接
        JDBCUtils.release(connection, statement, resultSet);
    }
}

4. SQL注入问题

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

sql注入案例：主函数中传入用户名，查找指定名字用户信息

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

public class SQLInjection {
    public static void main(String[] args) throws SQLException {
        searchName("' or '1=1");
    }

    //查找指定名字用户信息
    public static void searchName(String username) throws SQLException {
        //获得数据库对象connection
        Connection connection = JDBCUtils.getConnection();
        //获取sql执行对象statement
        Statement statement = connection.createStatement();
        //执行sql
        String sql = "select * from app_user where name='" + username + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
        }
        //释放连接
        JDBCUtils.release(connection, statement, resultSet);
    }
}

结果：查询到了数据库中所有的数据

这里传入一个不是用户名，而是一个不合法字符串，却获取到了全部的数据，为什么呢？

拼接整条sql语句是select * from app_user where name=' ' or '1==1'，其中1==1永远是真的，所以该sql语句相当于查询表中所有的数据；这就是sql注入，主要是字符串拼接引起的问题，十分危险！！

5. PreparedStatement对象

PreparedStatement是Statement的子类，与其相比，可以防止SQL注入，并且效率更高

同样测试sql注入案例

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) throws SQLException {
        searchName("' 'or '1=1'");
    }

    //登录
    public static void searchName(String username) throws SQLException {
        //获得数据库对象connection
        Connection connection = JDBCUtils.getConnection();
        //获取sql执行对象preparedStatement(预编译sql,先写不执行,参数用?表示)
        PreparedStatement preparedStatement = connection.prepareStatement("select * from app_user where name=?");
        //手动传参
        preparedStatement.setString(1, username);
        //执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
        }
        //释放连接
        JDBCUtils.release(connection, preparedStatement, resultSet);
    }
}

根据结果，PreparedStatement对象完美避免了sql注入问题

6. 事务案例

在这里插入图片描述

首先创建account表

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

然后编写Java代码

public class TestTransaction {
	public static void main(String[] args) throws Exception {
		//获得数据库对象connection
		//1.加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");
		//2.连接信息url,用户信息
		String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
		String username = "root";
		String password = "";
		//3.连接,获得数据库对象connection
		Connection connection = DriverManager.getConnection(url, username, password);
		
		//关闭数据库自动提交,即开启事务
		connection.setAutoCommit(false);
		String sql1 = "update account set money = money+100 where name = 'A' ";
		String sql2 = "update account set money = money-100 where name = 'B' ";
		//获取sql执行对象preparedStatement
		PreparedStatement preparedStatement = connection.prepareStatement(sql1);
		preparedStatement.executeUpdate();
		preparedStatement = connection.prepareStatement(sql2);
		preparedStatement.executeUpdate();
		//业务完毕,提交事务
		connection.commit();
	}
}

运行结果：

在这里插入图片描述

如果两次更新之间加int x = 1 / 0;

则会报错，且事务执行失败，两条语句都不会执行成功

在这里插入图片描述

Azмat
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql学习
lei19950206的博客
10-29 389
http://www.runoob.com/mysql/mysql-install.html 书籍：https://pan.baidu.com/s/1fIVAXZnZH7u_DDRIyjLTRQ mysql登陆： mysql -uroot -p 输入密码 增加用户： mysql &gt; create user 'dog''@''localhost'identified by '123'; 查看...
MySql教程
It's my life . . .
07-17 248
MySQL简介 什么是MySQL？ MySQL是一个多用户、多线程的SQL数据库，是一个客户机/服务器结构的应用，它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。SQL (Structur...
MYSQL入门学习
xiaoyangger的专栏
01-07 4028
MYSQL入门学习之一：基本操作  1、登录数据库    www.2cto.com       命令：mysql -u username –p （mysql -h主机地址 -u用户名 －p用户密码）     描述：输入命令回车，即可输入密码了。密码验证通过即可进入数据库了。 2、查看数据状态     命令：show database;     描述：该命令会显示现存的所有数据库。
MySQL详细学习教程（建议收藏）
热门推荐
欢迎来到 Baret~H 的博客
04-14 23万+
目录1、初识数据库1.1、什么是数据库1.2、数据库分类1.3、相关概念1.4、MySQL及其安装1.5、基本命令2、操作数据库2.1、操作数据库2.2、数据库的列类型2.3、数据库的字段属性2.4、创建数据库表2.5、数据库存储引擎2.6、修改数据库3、MySQL数据管理3.1、外键3.2、DML语言1. 添加 insert2. 修改 update3. 删除 delete4、DQL查询数据4.1、基础查询4.2、条件查询4.3、分组查询4.4、连接查询4.5、排序和分页4.6、子查询4.7、MySQL函
mysql详细学习笔记
01-05
### MySQL详细学习笔记 #### 数据库基础 **数据库的相关概念：** - **DB（Database）**: 数据库，即数据的仓库，是用来存储一系列有组织的数据的集合。 - **DBMS (Database Management System)**: 数据库管理系统...
Mysql详细学习笔记.rar
06-06
这份“Mysql详细学习笔记”显然是一个深入解析MySQL的资源，旨在帮助初学者和有经验的开发者更好地理解和运用SQL语句。以下是笔记中可能包含的一些核心知识点： 1. **SQL基础**：SQL（Structured Query Language）...
MySql学习笔记（详细）
最新发布
05-18
### MySQL学习笔记（详细） #### 一、数据库操作 MySQL是一种关系型数据库管理系统，通过一系列命令来进行数据库的管理和数据的处理。以下是一些常见的数据库操作命令： - **连接MySQL服务器**： - `mysql -u ...
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySQL进阶篇学习笔记
05-14
黑马MySQL课程总结的学习笔记
Mysql 全部教程
10-17
数据库中是通过数据库表来存储数据的，数据库表是二维结构，每行对应一条记录，每一列对应具体的内容。 数据库表的设计时，每一列都有具体的列类型，列名 Java是面向对象的编程语言，类中有对象的特征（属性），对于对象的存储，可以直接存储到数据库表中的一行记录上。要求类的属性和表的字段列一一对应的。
MySql学习
等待唐僧的悟空
03-17 165
MySql学习笔记 MySQL数据库存储引擎 MyISM：MyISAM存储引擎：不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表 InnoDB：支持事务 Memory：Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访...
MySQL学习
ppbgi的博客
02-08 232
一、基础语法 1、从cmd进入mysql数据库，show database显示数据库的库名，use table显示表名。 2、数据库连接：mysql -h127.0.0.1 -P3306 -uroot -proot 【-h (host) -P—端口 -p（密码）】 或 ：mysql -uroot -p 3、在数据库中进行 查询当前数据库版本：select version(); 查询当前数据库用户：select user(): 查询当前路径：select @@basedir; 查询所有数据库
MySQL 学习
qq_44213041的博客
02-21 334
DB（database） 关系型数据库：MySQL,Orcale,Sql server,DB2,SQLlite 非关系型数据库:Redis,MongDB DBMS(数据库管理系统)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值