MySQL基础学习

MySQL

1. 初识MySQL

1.1 什么是MySQL

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。

MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Data Base Management System，关系数据库管理系统) 应用软件之一。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

DBMS（Data Base Management System） 数据库管理系统

就是数据库的管理软件、用来管理和操作数据库中的数据。

1.2 什么是数据库

前端：页面、展示数据

后台：连接数据库、连接前端、控制视图跳转和给前端传递数据

数据库(DB，Data Base)：其实就是一种电子的仓库，是专门储存数据和管理管理数据的一种处所，用户可以对数据库中的数据进行新增和更新或者删除等操作。

数据库是所有软件体系中最核心的存在

1.3 数据库分类

关系型数据库

• 关系型数据库是一个结构化的数据库，创建在关系模型（二维表格模型）基础上，一般面向于记录。

• 通过表和表之间，行和列之间的关系进行数据存储。

• SQL 语句（标准数据查询语言）就是一种基于关系型数据库的语言，用于执行对关系型数据库中数据的检索和操作。

• 主流的关系型数据库包括 Oracle、MySQL、SQL Server、Microsoft Access、DB2 等。

非关系型数据库

• NoSQL(NoSQL = Not Only SQL )，意思是“不仅仅是 SQL”，是非关系型数据库的总称。

• 除了主流的关系型数据库外的数据库，都认为是非关系型。

• 主流的 NoSQL 数据库有 Redis、MongDB、Hbase、CouhDB 等。

1.4 连接数据库

安装好MySQL

命令行连接

mysql -uroot -p123456  --连接数据库 用户名root 密码123456

--命令行中所有的sql语句都要用;结尾
--单行注释
/*
多行注释
*/

--一些命令
show databases; --查看所有的数据库
use 数据库名; --使用数据库
show tables; --查看数据库中所有的表
describe 表名; --查看表中的信息
exit --退出连接

---

2. 操作数据库

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

MySQL关键字不区分大小写

[ ]中的命令是可选命令

2.1 操作数据库

创建数据库

CREATE DATABASE [IF NOT EXISTS] school; -- 创建一个school数据库[如果它不存在]

使用数据库

-- 如果表名或者字段名是特殊字符，就需要带``
USE `school`; --使用school数据库

删除数据库

DROP DATABASE [IF EXISTS] school; --删除school数据库[如果它存在]

2.2 数据库的列类型

数值

• tinyint 1个字节
• smallint 2个字节
• mediumint 3个字节
• int 标准的整数 4个字节 （常用）
• bigint 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 一般用于金融计算

字符串

• char 固定大小字符串 0~255字符 不足255字符的部分用空格补齐，数据库检索出char类型的数据表数据时会自动截断后面的空格，也就是说字符串后面自带的空格也会被删掉，使用要小心。定长的好处是存取更高效。

  PS: MySQL中的char不同于Java。Java中的char类型是一个单一的 16 位(两个字节) Unicode 字符，而MySQL中则是0~255字符。

• varchar 可变长度字符串 0~65535字符 例如：当varchar(10)字段类型中插入"abc"时，实际存储大小也只有3个字符，此外，varchar还需要使用1个额外字符来记录字符串的长度。 (常用)

• tinytext 可变长度文本 0 ~ 2^8-1(255)字符

• text 可变长度文本 0 ~ 2^16-1(65535)字符

• mediumtext 可变长度文本 0 ~ 2^24-1字符

• longtext 可变长度文本 0 ~ 2^32-1个字符

时间

java.util.Date

• date YYYY-MM-DD 日期
• time HH:MM:SS 时间
• datetime YYYY-MM-DD HH:MM:SS
• timestrap 时间戳 1970.1.1到现在的毫秒数
• year 年份

null

null 没有值

注意不要使用null进行运算，运算结果都为null。

2.3 数据库的字段属性

Field Name：列名或字段名

Datatype：数据类型

Len：长度

Default：默认值

PK？：是否为主键，主键即表中每一行的唯一标识，不可重复。

Not Null？：是否非空，勾选后该列的值就不能为空(null)。

Unsigned：无符号整数，勾选后该列的值不能为负数。

Auto Incr？：自增，自动在上一条记录的基础上+1，可以自定义自增的起始值和步长。

Zerofill？：0填充，不足的位数使用0填充，例如int类型长度为3，输入5就变成005。

Comment：注释

拓展

/*在未来做项目时，每一个表都必须有这5个字段

id			主键
`version`	乐观锁
is_delete	伪删除
gmt_creat	创建时间
gmt_update	修改时间
*/

2.4 创建数据库表

使用sql语句创建数据库表

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

-- 字段尽量使用``括起来
-- 所有语句后加逗号 最后一句不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
    `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
    `pwd` VARCHAR(10) NOT NULL DEFAULT'123456' COMMENT'密码',
    `sex` VARCHAR(2) NOT NULL COMMENT'性别',
    `birthday` DATE DEFAULT NULL COMMENT'生日',
    `address` VARCHAR(100) DEFAULT NULL COMMENT'地址',
    `e-mail` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
    PRIMARY KEY(`id`)	
)ENGINE=INNODB DEFAULT CHARSET=utf8

几个常用命令

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

2.5 修改和删除数据表

修改

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

-- 修改表的字段(修改约束)：ALTER TABLE 表名 MODIFY 字段名 列属性 [];
ALTER TABLE teacher MODIFY age VARCHAR(10) DEFAULT'你猜呀';
-- 修改表的的字段(修改字段名)ALTER TABLE 表名 CHANGE 旧列名 新列名 列属性 [];
ALTER TABLE teacher CHANGE age hobby VARCHAR(15);

-- 删除表的字段：ALTER TABLE 表名 DROP 字段名;
ALTER TABLE teacher DROP hobby;

删除

-- 删除表(如果存在)
DROP TABLE IF EXISTS teacher;

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

---

3. MySQL数据管理

3.1 外键

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

使用sql语句创建外键

PS：删除有外键关系的表时，必须先删除引用的表(student)，再删除被引用的表(grade)。

方式一：在创建表时声明外键

CREATE TABLE IF NOT EXISTS `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'',
    `gradename` VARCHAR(10) NOT NULL COMMENT'',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。
-- student表的gradeid字段 引用grade表的gradeid
-- gradeid即为student表的外键
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT'学号',
    `name` VARCHAR(10) NOT NULL DEFAULT'匿名' COMMENT'姓名',
    `sex` VARCHAR(3) NOT NULL COMMENT'性别',
    `birthday` DATE NOT NULL COMMENT'出生日期',
    `gradeid` INT(10) NOT NULL COMMENT'年级id',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`),  -- 将gradeid字段定义为名为FK_gradeid的键
    CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)  -- 将FK_gradeid键定义为外键
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：在外部声明

CREATE TABLE IF NOT EXISTS `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'',
    `gradename` VARCHAR(10) NOT NULL COMMENT'',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT'学号',
    `name` VARCHAR(10) NOT NULL DEFAULT'匿名' COMMENT'姓名',
    `sex` VARCHAR(3) NOT NULL COMMENT'性别',
    `birthday` DATE NOT NULL COMMENT'出生日期',
    `gradeid` INT(10) NOT NULL COMMENT'年级id',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- ALTER TABLE `表名`
-- ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `引用的表`(引用的字段);
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(gradeid);

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

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段），使用外键用程序层面去实现

3.2 DML语言(重点)

DML(Data Manipulation Language)：数据操作语言

主要有三个操作：

• insert
• update
• delete

3.2.1 insert

-- student表
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT'学号',
    `name` VARCHAR(10) NOT NULL DEFAULT'匿名' COMMENT'姓名',
    `sex` VARCHAR(3) COMMENT'性别',
    `birthday` DATE COMMENT'出生日期',
    `grade` VARCHAR(10) NOT NULL COMMENT'年级',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入数据
-- insert into 表名[(字段1,字段2,字段3...)] values('字段1值','字段2值','字段3值'...)
INSERT INTO `student`(`name`,`sex`,`birthday`,`grade`) VALUES('青城','男','2000-03-13','研一');

-- 字段可以省略 但是后面的值必须和字段一一对应
INSERT INTO `student` VALUES(2,'青山','男','1999-06-18','大四');

-- 可以同时插入多条数据，多条数据用括号括起来 用逗号隔开
INSERT INTO `student`(`name`,`grade`) VALUES('青衿','研二'),('青衣','研一');

3.2.2 update

-- 修改数据
-- update 表名 set 列名=值 [where 条件]
-- 如果不指定条件，会改动所有的表
UPDATE `student` SET `name`='程序猿'; 
-- 指定条件 id为1
UPDATE `student` SET `name`='掉头发' WHERE `id`=1;
-- 修改多个属性 用逗号隔开
UPDATE `student` SET `sex`='女',`grade`='研二' WHERE `name`='掉头发';

条件：where子句

部分运算符：

​ 等于= 大于> 小于< 大于等于>= 小于等于<= 不等于<>或!=

​ 在某个范围between…and… 和and 或or 非not

例如：
WHERE `sex`<>'男' -- 性别不为男
WHERE `id` BETWEEN 2 AND 5 -- id在2和5之间（包括2和5）
WHERE `grade`='研一' OR `grade`='研二' -- 年级为研一或者年级为研二
WHERE `name`='程序猿' AND `sex`='男' -- 名字为程序猿并且性别为男

3.2.3 delete

-- 删除数据
-- DELETE FROM 表名 [WHERE 条件];
-- 如果不写条件，则会删除表中所有数据
DELETE FROM `student` WHERE `id`=1;

一般删除表中所有数据时，不使用delete命令，而是使用truncate命令

-- 删除表中所有数据
TRUNCATE `student`;
-- truncate在清空表时，会将自增列重新归零，而delete则不会

---

4. DQL查询数据（最重点）

DQL(Data Query Language )：数据查询语言

建4张表 并插入数据

-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `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;

-- 插入学生数据 
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
-- 一年级
(1001,'123456','高瞻',1,1,'11100001111','陕西咸阳','2001-06-05','text111@qq.com','123456200106051234'),
(1003,'123456','刘明月',0,1,'11100003333','广东深圳','2001-08-28','text111@qq.com','123456200108281234'),
-- 二年级
(2001,'123456','金枝',0,2,'11100001111','陕西西安','2000-09-23','text111@qq.com','123456200009231234'),
(2002,'123456','张云腾',1,2,'11100002222','北京朝阳','2000-03-07','text111@qq.com','123456200003071234'),
-- 三年级
(3001,'123456','于泽坤',1,3,'12200001111','新疆伊犁','1999-11-11','text111@qq.com','123456199911111234'),
(3002,'123456','朱淼淼',0,3,'12200002222','陕西西安','1999-07-03','text111@qq.com','123456199907031234'),
(3003,'123456','赵威',1,3,'12200003333','江苏南京','1999-08-09','text111@qq.com','123456199908091234'),
(3004,'123456','相欣雨',0,3,'14400001111','陕西西安','1999-04-01','text111@qq.com','123456199904011234'),
(3005,'123456','高明',1,3,'14400002222','陕西西安','1999-12-12','text111@qq.com','123456199912121234'),
(3006,'123456','安澳瑞',1,3,'13300001111','安徽淮南','1999-03-13','text123@qq.com','123456200003131234'),
(3007,'123456','张雷',1,3,'13300002222','河北保定','1999-02-22','text111@qq.com','123456199902221234'),
(3008,'123456','赵晴',0,3,'13300002222','上海外滩','1999-06-18','text111@qq.com','123456199906181234');

-- 插入成绩数据
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
-- 二年级
(2001,4,'2017-11-11 09:00:00',91),
(2002,4,'2017-11-11 09:00:00',78),
(2001,5,'2017-11-12 09:00:00',95),
(2002,5,'2017-11-12 09:00:00',72),
-- 三年级
(3001,6,'2017-11-11 09:00:00',63),
(3002,6,'2017-11-11 09:00:00',92),
(3003,6,'2017-11-11 09:00:00',81),
(3004,6,'2017-11-11 09:00:00',98),
(3005,6,'2017-11-11 09:00:00',88),
(3006,6,'2017-11-11 09:00:00',72),
(3007,6,'2017-11-11 09:00:00',91),
(3008,6,'2017-11-11 09:00:00',96),

(3001,7,'2017-11-12 09:00:00',68),
(3002,7,'2017-11-12 09:00:00',91),
(3003,7,'2017-11-12 09:00:00',88),
(3004,7,'2017-11-12 09:00:00',99),
(3005,7,'2017-11-12 09:00:00',93),
(3006,7,'2017-11-12 09:00:00',76),
(3007,7,'2017-11-12 09:00:00',92),
(3008,7,'2017-11-12 09:00:00',94);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'一年级'),(2,'二年级'),(3,'三年级');

-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学',36,1),
(2,'大学英语',36,1),
(3,'C语言',36,1),
(4,'Java程序设计',36,2),
(5,'数据结构',36,2),
(6,'软件工程',36,3),
(7,'数据库',36,3);

4.1 查询字段

select完整语法

PS：[ ]为可选，{ }为必选。 where、group by、having、order by、limit这些子句可省略，但顺序不能变，是按固定顺序执行的。

-- 查询表中所有字段 *表示所有字段
SELECT * FROM `student`;
-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`;

别名

-- 别名 给字段起一个别名 也可以给表起别名
-- 有的时候 列名不是那么见明知意 就可以起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS s;

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

concat函数

-- concat函数 concat(a,b...) 拼接查询结果
SELECT CONCAT('学生：',`studentname`,`sex`) AS 学生 FROM `student`;

去重

-- 去重 distinct
SELECT `studentno` FROM `result`; -- 查询哪些学生有成绩
-- 出现重复数据 可使用distance去除重复数据
SELECT DISTINCT `studentno` FROM `result`;

4.2 where条件子句

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

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

基本运算符和逻辑运算符

运算符	语法	描述
=	a > b	等于
>	a > b	大于
<	a < b	小于
>=	a >= b	大于等于
<=	a <= b	小于等于
<> 或 !=	a <> b 或 a != b	不等于
and 或 &&	a and b 或 a && b	逻辑与，两个都为真，结果为真
or 或 ||	a or b 或 a || b	逻辑或，其中一个为真，结果为真
not 或 !	not a 或 ! a	逻辑非，真为假，假为真

-- 查询考试成绩在90分以上100分以下的
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=90 AND `studentresult`<=100;

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`BETWEEN 90 AND 100;

-- 查询除了3001和3002号以外的学生成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=3001 AND `studentno`!=3002;

SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno`=3001 AND NOT `studentno`=3002;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为空，结果为真
is not null	a is not null	如果操作符不为空，结果为真
between…and…	a between b and c	如果a在b和c之间，结果为真
like	a like b	如果a匹配b，则结果为真
in	a in (a1,a2,a3…)	如果a为a1,a2,a3…中某一个值，结果为真

-- like  %代表0到任意个字符  _代表一个字符
-- 查询姓张的同学名字
SELECT `studentname` FROM `student`
WHERE `studentname` LIKE '张%';
-- 查询叫 张X 的同学名字
SELECT `studentname` FROM `student`
WHERE `studentname` LIKE '张_';
-- 查询叫 张XX 的同学名字
SELECT `studentname` FROM `student`
WHERE `studentname` LIKE '张__';
-- 查询名字中有 欣 字的同学名字
SELECT `studentname` FROM `student`
WHERE `studentname` LIKE '%欣%';
-- 查询1年级的学生
SELECT `studentname` FROM `student`
WHERE `studentno` LIKE '1%';

-- in(具体的一个或多个值)  in中不能使用%和_
-- 查询陕西西安和陕西咸阳的同学
SELECT `studentname`,`address` FROM `student`
WHERE `address` IN('陕西西安','陕西咸阳');
-- 查询学号为3001,3002,3003的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(3001,3002,3003);

4.3 联表查询

操作	描述
inner join	两边表同时有对应的数据，才会返回值。
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- 用student表联立result表，查询 学号 学生姓名 科目号 成绩
-- 别名的 AS 可以省略
-- 表名.列名 不能使用``括起来，否则会报错
-- 由于s表r表都有studentno，故查询时学号要写成s.studentno或r.studentno,否则程序不知道查询哪个学号
-- join ...on...为固定语法，也可将on改为where 结果不变

-- 内联 通过两个表中的学号连接起来，
-- 因为成绩表中没有一年级的信息，只返回两个表的交集，即二、三、四年级的成绩
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno;

-- 左联 以左表s表为基准，即使右表r表没有一年级的信息，但左表s表有，查询结果也会返回一年级信息
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.studentno = r.studentno;

-- 右联 以右表r表为基准，即使左表s表有一年级的信息，但右表r表没有，故查询结果不会返回一年级信息
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.studentno = r.studentno;

-- 多表联立
-- 联立student、result、subject三表，查询学号 姓名 科目名称 成绩
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sb
ON r.subjectno = sb.subjectno;

-- 查询考数据库的同学的 学号 姓名 年级 科目名称 考试时间 成绩
SELECT s.`studentno`,`studentname`,`gradename`,`subjectname`,`examdate`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `grade` g
ON s.gradeid = g.gradeid
INNER JOIN `subject` sb
-- 这里不能使用g表的gradeid联立sb表
-- gradeid不是sb表的主键，不具有唯一性，联立会造成数据错乱
ON r.subjectno = sb.subjectno 
WHERE subjectname = '数据库';

4.4 分页和排序

排序

-- 查询所有同学的成绩 并排序
-- 语法：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` sb
ON r.subjectno = sb.subjectno
ORDER BY `studentresult` DESC;

分页

-- 分页 让查询结果每页显示5条数据
-- 语法：LIMIT 起始值(第一个值为0),页面大小
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sb
ON r.subjectno = sb.subjectno
ORDER BY `studentresult` DESC
LIMIT 0,5;
-- 第一页  LIMIT 0,5
-- 第二页  LIMIT 5,5
-- 第三页  LIMIT 10,5
-- 第n页   LIMIT (n-1)*页面大小,页面大小

4.5 子查询

一般来讲连接查询比子查询效率更高，因为子查询会多次遍历数据，而连接查询只遍历一次。

-- 查询数据库的考试结果 （学号 科目号 成绩）降序排列

-- 方式一：连表查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sb
ON r.subjectno = sb.subjectno
WHERE subjectname = '数据库'
ORDER BY `studentresult` DESC;

-- 方式二：使用子查询（由里到外查询）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
    SELECT `subjectno` FROM `subject`
    WHERE `subjectname` = '数据库'
)
ORDER BY `studentresult` DESC;

-- 查询考数据库且分数大于90分的同学的 学号 姓名

-- 方式一：联立student result subject 三表查询
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sb
ON r.subjectno = sb.subjectno
WHERE `subjectname` = '数据库' AND `studentresult`>=90;

-- 方式二：联立两张表并使用一个子查询
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
WHERE `studentresult`>=90 AND `subjectno`=(
    SELECT `subjectno` FROM `subject`
    WHERE `subjectname` = '数据库'
);

-- 方式三：使用两个子查询嵌套
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN ( -- 由于成绩大于90分的学号是一个范围，不是固定值，所以使用IN，不用=
    SELECT `studentno` FROM `result`
    WHERE `studentresult`>=90 AND `subjectno`=(
        SELECT `subjectno` FROM `subject`
        WHERE `subjectname` = '数据库'    
    )
);

4.6 分组和过滤

-- 查询不同课程的平均分 最高分 最低分
-- 通过GROUP BY使查询结果按照不同的科目进行分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sb
ON r.subjectno = sb.subjectno
GROUP BY r.subjectno;

-- 在上述查询的基础上只显示平均分85分以上的科目
-- 由于where写在GROUP BY之上，先执行，且where后不能跟聚合函数，
-- 所以使用having来过滤 having写在GROUP BY下面
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sb
ON r.subjectno = sb.subjectno
GROUP BY r.subjectno
HAVING 平均分 >=85;

---

5. MySQL函数

5.1 基本函数

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

-- 字符串函数
SELECT CHAR_LENGTH('人间忽晚，山河已秋'); -- 字符串长度
SELECT CONCAT('我','爱','你'); -- 拼接字符串
SELECT INSERT('小陈学编程',1,2,'狗都不'); -- 替换（'原字符串',起始位置,长度,'替换的字符串'） 小陈 -> 狗都不
SELECT LOWER('QingCheng'); -- 变小写字母
SELECT UPPER('QingCheng'); -- 变大写字母
SELECT INSTR('QingCheng','g'); -- 返回子串第一次出现的位置
SELECT REPLACE('我爱学习','学习','玩游戏'); -- 替换出现的指定字符串
SELECT SUBSTR('面对疾风吧',3,2); -- 返回指定字符串 （'原字符串',截取位置,截取长度） 第3个开始长度为2 -> 疾风
SELECT REVERSE('八级大狂风'); -- 反转字符串

-- 查询姓张的同学 并改为 赵
SELECT REPLACE(studentname,'张','赵') FROM student
WHERE studentname LIKE '张%';

-- 时间和日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间

SELECT YEAR(NOW()); -- 年
SELECT MONTH(NOW()); -- 月
SELECT DAY(NOW()); -- 日
SELECT HOUR(NOW()); -- 时
SELECT MINUTE(NOW()); -- 分
SELECT SECOND(NOW()); -- 秒

-- 系统
SELECT SYSTEM_USER();
SELECT USER();  -- 系统用户
SELECT VERSION() -- 版本

5.2 聚合函数

-- 计数
SELECT COUNT(`studentno`) FROM student; -- COUNT(字段名) 统计该字段名不为null的数据总数。
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;

COUNT(*)和COUNT(1)的区别：

这俩种方式都可以用来统计表中总数据量，但是他们的实现方式略有不同。

• COUNT(*)是统计表中的记录总数，即使某条记录的某几个字段为空，只要不是所有字段都为空，都会被统计。

• COUNT(1)是通过在该表的表结构中添加一列，该列的数据都为1，最终是通过统计该列中有多少个数据为1的数量，即为数据总数。

5.3 数据库级别的MD5加密

MD5是一种被广泛使用的密码散列函数

-- 创建测试表
CREATE TABLE `testmd5`(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(10) NOT NULL,
    `pwd` VARCHAR(10) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入信息
INSERT INTO `testmd5` VALUES
(1,'张三','123456'),(2,'李四','123456'),(3,'王麻子','123456');

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

-- 也可在插入数据时加密
INSERT INTO testmd5 VALUES(4,'青城',MD5('123456'));

---

6. 事务

参考博客：https://blog.csdn.net/qq_41116027/article/details/124135831

什么是事务

事务是逻辑上的一组数据库操作，要么都执行，要么都不执行。

**例子：**假如张三要给李四转账200元，这个转账会涉及到两个关键操作就是：将张三的余额减少200元，将李四的余额增加200元。如果两个操作之间突然出现错误，例如银行系统崩溃导致张三余额减少，而李四的余额没有增加，这样的系统是有问题的。事务就是保证这两个关键操作要么都成功，要么都要失败。

事务的特性：ACID

• 原子性(atomicity)：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；例如转账的这两个关键操作（将张三的余额减少200元，将李四的余额增加200元）要么全部完成，要么全部失败。

• 一致性(consistency)： 确保从一个正确的状态转换到另外一个正确的状态，这就是一致性。例如转账业务中，将张三的余额减少200元，中间发生断电情况，李四的余额没有增加200元，这个就是不正确的状态，违反一致性。又比如表更新事务，一部分数据更新了，但一部分数据没有更新，这也是违反一致性的；

• 隔离性(isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

• 持久性(durability)：一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务之间的相互影响

事务之间的相互影响分为几种，分别为：脏读，不可重复读，幻读等。

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

不可重复读：不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。

不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这回导致锁竞争加剧，影响性能。

幻读：指在一个事务中读取到了别的事务插入的数据，导致前后读取不一致。

例如：程序员某天消费了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉。

手动处理事务的流程

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

-- 手动处理事务流程

-- 关闭自动提交
SET autocommit = 0;

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

-- 执行sql
INSERT xx
INSERT xx

-- 回滚：执行失败时进行回滚，回到原来的数据
ROLLBACK;

-- 提交：执行成功后提交，事务一旦提交就会持久化，无法回滚到之前的数据
COMMIT;

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

模拟事务：以转账为例

-- 创建数据库
-- 字符集和校对规则可以不用写，默认为utf8和utf8_general_ci
CREATE DATABASE bank CHARACTER SET utf8 COLLATE utf8_general_ci;
USE bank;

-- 创建账户表
CREATE TABLE `account`(
    `id` INT(3) AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(10) NOT NULL,
    `money` DECIMAL(10,2) NOT NULL, -- DECIMAL(10,2)表示最多10位，小数点后2位
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `account`(`name`,`money`)
VALUES ('张三',2000.00),('李四',10000.00);


SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一组事务

UPDATE `account` SET money=money+500 WHERE `name`='张三';
UPDATE `account` SET money=money-500 WHERE `name`='李四';

ROLLBACK -- 回滚
COMMIT; -- 执行成功后提交事务

SET autocommit = 1; -- 开启自动提交，恢复默认

---

7. 索引

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

索引的分类

• 主键索引（PRIMARY KEY）：唯一标识，不能为空，被设为主键的字段中的数据不可重复，且一张表只能有一个字段为主键。
• 唯一索引（UNIQUE KEY）：被设为唯一索引的字段中的数据不可重复，但可以为空，且一张表可以有多个字段为唯一索引。
• 常规索引（KEY）：最基本的索引，没有任何限制。
• 全文索引（FULLTEXT）：快速定位数据。

-- 显示所有的索引信息
SHOW INDEX FEOM `表名`;

-- 创建索引
CREATE INDEX 索引名 ON 表名(`字段名`);

索引原则：

• 索引不是越多越好。
• 索引在小数据量时用处不大，在数据特别多的时候。使用索引可以增加查询效率，缩短查询所需时间。
• 不要对经常变动的数据加索引。
• 索引一般加在经常查询的字段上。

---

8. 权限管理和备份

8.1 用户管理

使用SQL命令对用户进行操作，本质是操作mysql数据库下的user表。

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER qingcheng IDENTIFIED BY '123456';

-- 修改用户密码 修改当前登录用户的密码时可以省略 for 用户名
SET PASSWORD FOR qingcheng = PASSWORD('111111');

-- 重命名用户  RENAME USER 原用户名 TO 新用户名;
RENAME USER qingcheng TO saturday;

-- 用户授权  GRANT 权限 ON 数据库名.表名 TO 用户名;
GRANT ALL PRIVILEGES ON *.* TO saturday; -- ALL PRIVILEGES 所有权限   *.* 所有库的所有表

-- 查询权限
SHOW GRANTS FOR saturday;
SHOW GRANTS FOR root@localhost; -- 查询根用户权限

-- 撤销权限  REVOKE 权限 ON 数据库名.表名 FROM 用户名;
REVOKE ALL PRIVILEGES ON *.* FROM saturday;

-- 删除用户
DROP USER saturday;

8.2 MySql备份

MySQL数据库备份方式

• 直接拷贝物理文件(data文件)。
• 在Sqlyog这种可视化工具中手动导出
• 使用命令行导出 mysqldump

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

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

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

#导入
#登录的情况下，切换到指定数据库 source备份文件   
mysql -uroot -p123456  登录数据库
use school  切换到指定数据库
source D:/student.sql  导入备份文件

---

9. 三大范式

参考：https://blog.csdn.net/djklsajdklsajdlk/article/details/107954633

范式的作用是为了规范数据库的设计

当数据表越来越规范，就会产生更多的数据表，查询起来就需要联立更多的表，就会降低性能，所以在实际设计中，需要注意规范和性能的平衡。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第五范式（5NF）。

通常我们用到最多的就是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF），即三大范式。

第一范式（1NF）

要求数据表的每一列都是不可分割的原子项数据。

第二范式（2NF）

满足第一范式的前提下

第二范式需确保数据表中的每一列都和主键相关，而不能只与主键的某一部分相关，即每张表只描述一件事情。

第三范式（3NF）

满足第一范式和第二范式的前提下

第三范式需确保表中每一列数据都和主键直接相关，而不是间接相关。

---

10. JDBC

JDBC是Java操作数据库的一种规范。

10.1 第一个JDBC程序

1.在Sqlyog中建立数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

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

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04');

2.导入数据库驱动。

建立一个idea项目，在项目下建立一个lib目录，下载 mysql-connector-java jar包放入lib目录，并导入项目库。

3. 编写测试代码

package com.chen.lesson1;

import java.sql.*;

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

        //2. 用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";

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

        //4. 获取执行sql对象  statement即执行sql的对象
        Statement statement = connection.createStatement();

        //5. 使用 执行sql的对象 去 执行sql
        String sql = "SELECT * FROM `users`";  //定义查询sql
        ResultSet resultSet = statement.executeQuery(sql);//执行该查询 接收返回的查询结果集，结果集中封装了全部的查询结果
        //通过while循环输出查询结果
        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();
    }
}

运行结果：

10.2 JDBC对象

DriverManager

Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动

Connection connection = DriverManager.getConnection(url, username,password);

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

URL

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

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

Statement 执行SQL的对象 PrepareStatement 执行SQL的对象**

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

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

ResultSet 结果集对象：封装了所有查询结果

//获取数据类型
resultSet.getObject(); //不知道列类型情况下 使用Object

resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
...

//遍历，指针
resultSet.beforeFirst(); //最前面的数据
resultSet.afterLast(); //最后面的数据
resultSet.next(); //下一个数据
resultSet.previous(); //前一行数据
resultSet.absolute(row); //指定行的数据

释放资源

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

10.3 statement对象

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

CURD操作-create

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

CURD操作-delete

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

CURD操作-update

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

CURD操作-retrieve

Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
	//根据获取的数据类型，调用resultSet相应的方法映射到java对象中。
}

10.4 通过封装工具类实现CURD

通过封装工具类来操作数据库可以避免每次操作时都要编写重复代码，方便快捷。

10.4.1 建立配置文件

在src文件下创建一个File文件，命名为 db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

10.4.2 封装工具类

封装一个工具类 JdbcUtils

package com.chen.lesson2.utils;

import java.io.IOException;
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 = JdbcUtils.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");

            //1.驱动只用加载一次
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException 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 e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

10.4.3 进行CURD操作

插入数据

package com.chen.lesson2;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection 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,'QingCheng','123456','154438396@qq.com','2000-03-13')";
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

删除数据

package com.chen.lesson2;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); //获取数据库连接
            statement = connection.createStatement(); //获取SQL的执行对象
            String sql = "DELETE FROM `users` 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);
        }
    }
}

更新数据

package com.chen.lesson2;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); //获取数据库连接
            statement = connection.createStatement(); //获取SQL的执行对象
            String sql = "UPDATE `users` SET `NAME`='QingCheng',`birthday`='2000-03-13' WHERE `id`=1";
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

查找数据

package com.chen.lesson2;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection(); //获取数据库连接
            statement = connection.createStatement(); //获取SQL的执行对象
            String sql = "select * from users where id=1";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

10.4 SQL注入问题

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

package com.chen.lesson2;

import com.chen.lesson2.utils.JdbcUtils;

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

public class SQL注入 {
    public static void main(String[] args) {
        login(" ' or '1=1"," ' or '1=1"); //拼接不合法的字符串让数据库接收
        //拼接后的sql语句为：
        //select * from users where `NAME`=' ' or '1=1' and `PASSWORD`=' ' or '1=1'
    }

    public static void login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection(); //通过自己建立的工具类获取数据库连接
            statement = connection.createStatement(); //获取SQL的执行对象
            //select * from users where `NAME`='QingCheng' and `PASSWORD`='123456'
            String sql = "select * from users where `NAME`='"+username+"' and `PASSWORD`='"+password+"'"; 
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

10.5 PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更高！

10.5.1 实现CURD

插入数据

package com.chen.lesson3;

import com.chen.lesson2.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";//使用占位符?代替参数
            preparedStatement = connection.prepareStatement(sql); //预编译sql，先写sql 不执行

            //手动给参数赋值
            preparedStatement.setInt(1,4); //第1个参数 id
            preparedStatement.setString(2,"Tuesday"); //第2个参数 name
            preparedStatement.setString(3,"123456"); //第3个参数 password
            preparedStatement.setString(4,"154438396@qq.com"); //第4个参数 email
            //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 (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

删除数据

package com.chen.lesson3;

import com.chen.lesson2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "delete from `users` where `id`=?";//使用占位符?代替参数
            preparedStatement = connection.prepareStatement(sql); //预编译sql，先写sql 不执行

            //手动给参数赋值
            preparedStatement.setInt(1,4); //第1个参数 id

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

更新数据

package com.chen.lesson3;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "update `users` set `NAME` = ? where `id`=?;";//使用占位符?代替参数
            preparedStatement = connection.prepareStatement(sql); //预编译sql，先写sql 不执行

            //手动给参数赋值
            preparedStatement.setString(1,"青城"); //第1个参数 NAME
            preparedStatement.setInt(2,1); //第2个参数 id

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

查询数据

package com.chen.lesson3;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        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，先写sql 不执行

            //手动给参数赋值
            preparedStatement.setInt(1,1); //第1个参数 id

            //执行
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

10.5.2 防止SQL注入

PreparedStatement防止SQL注入的本质是把传进来的参数当做字符，若其中有转义字符，比如 ’ 会被直接忽略。

package com.chen.lesson3;

import com.chen.lesson2.utils.JdbcUtils;

import java.sql.*;

public class NoSQL注入 {
    public static void main(String[] args) {
        //login("lisi","123456");
        login("'' or '1=1'","'' or '1=1'");
    }
    public static void login(String username,String password){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection(); //通过自己建立的工具类获取数据库连接
            //PreparedStatement防止SQL注入的本质是把传进来的参数当做字符
            //若其中有转义字符，比如 ' 会被直接忽略
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?"; //sql 使用占位符?代替参数
            preparedStatement = connection.prepareStatement(sql); //预编译sql
            //给参数赋值
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            //执行
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

10.6 使用IDEA连接数据库

点击IDEA右侧的Database，选择MySQL，输入用户名和密码后，连接数据库。

连接成功后，点击设置按钮，勾选想要的数据库。

连接成功后，可以直接在IDEA中打开数据表，修改数据(修改完后需提交)，并且可以在控制台中编写SQL。

10.7 事务

先建立一个测试表

-- 创建账户表
create table `account`(
    `id` int(4) not null auto_increment primary key,
    `name` varchar(10) not null,
    `money` float
);

-- 插入测试数据
insert into `account`(`name`,`money`)
values ('zhangsan',1500),('lisi',5500);

代码实现：

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

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

3.可以在catch语句中显示定义回滚语句，即使没定义，失败也会默认回滚

package com.chen.lesson4;

import com.chen.lesson2.utils.JdbcUtils;

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

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            //关闭数据库的自动提交，就会自动开启事务
            connection.setAutoCommit(false); //开启事务

            String sql1 = "update `account` set `money`=`money`+500 where `id`=1";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            String sql2 = "update `account` set `money`=`money`-500 where `id`=2";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //提交事务
            connection.commit();
            System.out.println("转账成功！");

        } catch (SQLException e) {
            try {
                connection.rollback(); //如果失败 回滚事务 (不写也会默认回滚)
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

10.8 数据库连接池

数据库连接池就是是程序启动时建立多个数据库连接对象，并将这些对象组成一个连接池，统一进行管理，由程序动态地对池中的链接进行申请，使用，释放。

这样就可以避免每次执行SQL时都进行 数据库连接–资源释放 这一步骤，节省资源。

通过实现接口 DataSource 来编写连接池。

实现 DataSource 的开源数据源有 DBCP、C3P0、Druid 等。 使用了这些数据库连接池后，在项目开发时就不需要编写连接数据库的代码了。

DBCP数据源

需要用到两个jar包：commons-dbcp.jar、commons-pool.jar

C3P0数据源

需要用到的两个jar包：c3p0-0.9.5.5、mchange-commons-java-0.2.19