MySQL学习笔记

MySQL

1.连接数据库

命令行连接！

mysql -uroot -p123456 ---连接数据库

sc delete mysql; 清空服务
update mysql.user set anthentication_string=password('123456')where user='root' and Host='localhost';--修改用户密码
flush privileges; --刷新权限
--所有的语句都是用;结尾

use school --切换数据库： use 数据库名
Database changed
show tables; --查看数据库中所有的表
describe student（表名）; --显示数据库中所有的表的信息
create database westos; --创建一个数据库
exit; --退出连接

--单行注释（SQL的本来的注释）
/*    SQL多行注释
hello 
*/

数据库xxx语言 CRUD 增删改查！

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.操作数据库

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

mysql中的关键字不区分大小写

2.1操作数据库(了解)

1.创建数据库

CREATE DATABASE [if not EXISTS] person; --创建表，语句没有中括号，中括号中的内容为如果没有这个表的情况下创建表

2.删除数据库

DROP DATABASE [if exists] person;
--如果存在就删除

3.使用数据库

--table键的上面，如果你的表名或者字段名是一个特殊字符，就需要``括起来
use `school`

4.查看数据库

show databases --查看所有的数据库

2.2数据库的列类型

数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小数据 3个字节
int 标准的整数 4个字节 常用
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节（精度有问题！）
decimal 字符串形式的浮点数，用于金融计算的时候，一般用decimal

字符串

char 字符串固定大小的 0-255
varchar 可变字符串 0-65535 常用的 String
tingtest 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

时间日期

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

null

没有值，未知
==注意，不要使用NUll进行运算，结果为NUll

2.3数据库的字段属性（重点）

Unsigned 无符号：

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

zerofill 0填充：

0填充的
不足的位数，使用0来填充，比如int（3），5 --005

auto_increment 自增：

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

非空 NUll not null

假设设置为 not null，如果不给它赋值，就会报错！
NULL，如果不填写值，默认就是null！

默认：

设置默认的值！
sex，默认值为男，如果不指定该列的值，则会有默认的值！

拓展：

id          主键
`vestion`   乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间

2.4创建数据库表（重点）

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL 创建
-- 学号 int 登陆密码 varchar（20）姓名，性别varchar（2），出生日期（datetime），家庭住址email
-- 注意点，使用英文（），表的名称和字段 尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来！
-- 所有的语句后面加，（英文的），最后一个不用加
--  PRIMARY KEY(`id`) 主键是唯一的

create table if not exists `student`(
    `id` int(4) not null auto_increment comment '学号',
    `name` varchar(10) not null default '匿名' comment '姓名',
    `password` varchar(20) not null default '1234546' comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `datetime` datetime default null comment '出生日期',
    `address` varchar(20) default null comment '住址',
    `email` varchar(40) default null comment '邮箱',
    primary key(`id`)
)engine=innodb default charset=utf8

/*
格式：
create table [if not exists] `表名`(
`字段名` 列类型 [属性 not null default(默认) ''] [索引] [注释 comment],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释]
.....
)engine=innodb default charset=utf8 --[设置表类型默认 ENGINE=INNODB DEFAULT][设置字符集charset=utf8]
*/

常用命令

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

2.5数据表的类型

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

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	支持（全英文）
表空间大小	较小	较大约MYISAM2倍

常规使用操作:

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

在物理空间存在的位置

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

本质还是文件的存储！

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

innoDB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1文件
MYISAM对应文件

*.frm -表结构的定义文件
*.MYD 数据文件（data）
*.MYI 索引文件（index）

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

charset=utf8

不设置的话，会是mysql默认的字符集集编码~不支持中文

可以在my.ini中配置默认的编码 character-set-server=utf8

小技巧：通过 show create table student语句

显示表信息后右键复制，粘贴到查询面板上，就自动生成表的sql代码。

2.6修改删除表

-- 修改表名: ALTER TABLE 旧表名 rename AS 新表名
ALTER TABLE student RENAME AS student1

-- 增加表的字段: ALTER TABLE 表名 ADD 字段名 列属性
alter table student1 add age int(10)

-- 修改表的字段（重命名，修改约束！）:
ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(10) --修改约束
ALTER TABLE 表名 CHANGE `旧字段名` `新字段名` 列属性
ALTER TABLE student1 CHANGE age age1 INT(12) --重命名

-- 删除表的字段
ALTER TABLE student1 DROP age1

--删除表(如果表存在再删除)
DROP TABLE IF EXISTS student1

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

if not exists 如果不存在就创建

if exists 如果存在就删除

注意点：

``字段名，使用这个包裹！
注释： -- /**/
sql关键字大小写不敏感，建议写小写
所有的符号全部用英文

3.MySQL数据管理

3.1外键（了解即可）

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

CREATE TABLE IF NOT EXISTS `school`.`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(4) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `gradeid` INT(10) NOT NULL auto_increment COMMENT '年级编号',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(60) 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

方式二：创建表之后再创建外键关系

alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`);
-- alter table `表名`
-- add constraint `约束名` foreign key(作为外键的列) references 哪个表（哪个字段）;

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），在删除自己的表（）

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

最佳实践

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

3.2DML语言（全部记住）

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

DML语言：数据操作语言

insert
update
delete

3.3添加 insert

-- 插入语句（添加）
-- insert into 表名（[字段1,字段2,字段3])VALUES(`值1`),(`2`),(`3`)
insert into `student`(`name`,`sex`,`address`) values('张三','男','371312')-- 由于主键自增我们可以省略（如果不写字段，它就会一一匹配）
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多个字段
INSERT INTO `student`(`name`) VALUES('11'),('22'),('33')

注意事项：

字段和字段之间使用英文逗号隔开
字段是可以省略的，但是后面的值必须一一对应
可以同时插入多条数据，values后面的值，需要使用,隔开即可values（）,（）,....

3.4修改 update

update

update 修改谁（条件）set原来的值=新值

-- 修改学员名字，带了简介
UPDATE `student` SET `name`='赵武' WHERE id !=2;
-- 不指定条件的情况下，会改动所有表！
UPDATE `student` SET `name`='赵东'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='大哥',`age`=18,`adress`='三屯' WHERE id=1；

条件：where 运算符 id等语某个值，大于某个值，在某个区间内修改...

操作符	含义	范围	结果
=	等语	5=6	false
<>或!=	不等于	5<>6	true
>
<
<=
>=
between...and...	在某个范围内	[2,5]
and	我和你&&	5>1&&1>2	false
or	我或你\|\|	5>1&&1>2	true

-- 通过多个条件定位数据
update `student` set `name`='狂神' where name='大哥' and sex='男'

语法：update 表名 set colnum_name=value,[colnum_name=value,...] where[条件]

注意：

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

3.5删除 delete

delete

语法：delete from 表名 [where] 条件

-- 删除数据（避免这样写，会全部删除）
delete from `student`

--删除指定数据
delete from `student` where id=1;

truncate 命令

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

-- 清空数据库
truncate `student`

delete 和truncate区别

相同点：都能删除数据，都不会删除表结构
不同点：
- TRUNCATE 重新设置自增列计数器会归零
- TRUNCATE 不会影响事务

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

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

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

4.1DQL

(Data Query LANGUAGE:数据查询语言)

所有的查询操作都用它 select
简单的查询，复杂的查询它都能作~
数据库中最核心的语言，最重要的语言
使用频率最高的语句

4.2指定查询字段

用到的数据库

DROP DATABASE IF EXISTS `school`;
-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
-- 使用school数据库
USE `school`;
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
    `student_no` INT(4) NOT NULL COMMENT '学号',
    `login_pwd` VARCHAR(20) DEFAULT NULL,
    `student_name` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `grade_id` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话',
    `address` VARCHAR(255) NOT NULL COMMENT '地址',
    `born_date` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号',
    `identity_card` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`student_no`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
  `grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
   PRIMARY KEY (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
  `subject_no`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `subject_name` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
  `class_hour` INT(4) DEFAULT NULL COMMENT '学时',
  `grade_id` INT(4) DEFAULT NULL COMMENT '年级编号',
   PRIMARY KEY (`subject_no`)
)ENGINE = INNODB  DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
  `student_no` INT(4) NOT NULL COMMENT '学号',
  `subject_no` INT(4) NOT NULL COMMENT '课程编号',
  `exam_date` DATETIME NOT NULL COMMENT '考试日期',
  `student_result` INT (4) NOT NULL COMMENT '考试成绩'
  )ENGINE = INNODB DEFAULT CHARSET = utf8;
  
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`student_no`,`login_pwd`,`student_name`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`)
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 `grade` (`grade_id`,`grade_name`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
INSERT INTO `subject`(`subject_no`,`subject_name`,`class_hour`,`grade_id`)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);

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`student_no`,`subject_no`,`exam_date`,`student_result`)
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),
(1001,1,'2013-11-11 16:00:00',83),
(1001,2,'2013-11-12 16:00:00',100),
(1001,3,'2013-11-11 09:00:00',52),
(1001,4,'2013-11-13 16:00:00',91),
(1001,5,'2013-11-14 16:00:00',68);

查询

-- 查询全部的学生 select 字段 from 表
select * from student

-- 查询指定字段
SELECT `studentNo`,`studentName` from student

-- 别名，给结果起一个名字，AS 给可以给字段起别名，也可以给表起
select `studentNo` AS 学号,`studentName` AS 学生姓名 FROM student AS 学生

--函数 concat（a,b）
SELECT CONCAT('姓名：',studentName) AS 新名字 FROM student

语法：select 字段名 ... from 表名

有的时候，列名字不是那么的见名知意。我们起别名 As 字段名 as 别名表名 as 别名

去重 distinct

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

-- 查询一下有哪些同学参加了考试，成绩
select * from result -- 查询全部的考试成绩
select `studentNo` from result --查询有哪些同学参加了考试
select distinct `studentNo` from result --去除重复数据

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

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

select 表达式 from 表

4.3where条件子句

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

逻辑运算符

运算符	语法	描述
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

 SELECT `studentno`,`studentresult` FROM `result` WHERE NOT `studentno`=1000

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，则结果为真
is not null	a is not null	如果操作符为 not null，则结果为真
between	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 `studentno`,`studentname` from result
where studentname like '刘%' ;
-- 查询姓刘后面一个字符的
select `studentno`,`studentname` from result
where studentname like '刘_' ;
-- 查询姓刘后面两个字符的
select `studentno`,`studentname` from result
where studentname like '刘__'-- 查询名字中有加字的同学
select `studentno`,`studentname` from result
where studentname like '%刘%'---------------------------------------------------------------
-- in 具体的一个或多个值 查询1001 1002 1003学员的成绩
select `studentno`,`studentname` from result
where studentno IN(1001,1002,1003)

select `studentno`,`studentname` from result
where address in('北京')


---------------null----not null-------------''---
-- 查询地址为空的学生
select `studentno`,`studentname` from result
where address is NULL or adress=''-- 查询没有生日日期的学生
select `studentno`,`studentname` from result
where birthday is  null

4.4联表查询

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）

/* 思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？ 7种
判断交叉点（这两个表中那个数据是相同的）
判断的条件：学生表中的 studentno =成绩表 studentno
*/
SELECT s.`student_no`,`student_name`,`subject_no`,`student_result` FROM `student` AS s 
INNER JOIN `result` AS r WHERE s.`student_no`=r.`student_no`

-- right join
SELECT s.`student_no`,`student_name`,`subject_no`,`student_result` FROM `student` AS s
RIGHT JOIN `result` AS r ON s.`student_no`=r.`student_no`

-- left join
SELECT s.`student_no`, `student_name`,`subject_no`,`student_result` FROM `student` AS s
LEFT JOIN `result` AS r ON s.`student_no`=r.`student_no`

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- 查询缺考的同学
SELECT s.`student_no`,`student_name`,`subject_no`,`student_result` FROM `student`AS s LEFT JOIN `result` AS r 
ON s.`student_no`=r.`student_no` WHERE `student_result` IS NULL

-- 我们要 查询哪些数据 SELECT
-- 需要从哪几个表中查 FROM 表  xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表增加然后再慢慢增加

-- 查询 学号，姓名，科目名，成绩，
SELECT s.`student_no`,`student_name`,`subject_name`,`student_result`,r.`subject_no` FROM `student` AS s 
RIGHT JOIN `result` AS r ON s.`student_no`=r.`student_no`
RIGHT JOIN `subject` AS sub ON r.`subject_no`=sub.`subject_no`

自连接（了解）

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

在上面用到的数据库中插入表：

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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');

父类表：

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

子类表

categoryid	pid	categoryName
4	3	数据库
6	3	web开发
7	5	ps技术
8	2	办公信息

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

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

-- 查询父子信息
select a.`categoryname` as father,b.`categoryname` as son 
from `category` as a,`category` as b 
where a.`categoryid`=b.`pid`

4.5 分页和排序

排序：升序 ASC 降序 DESC

ORDER BY：通过哪个字段排序，怎么排

-- 查询的结果 根据成绩降序排序
SELECT s.`student_no`,`student_name`,`subject_name`,`student_result`,r.`subject_no` FROM `student` AS s 
RIGHT JOIN `result` AS r ON s.`student_no`=r.`student_no`
RIGHT JOIN `subject` AS sub ON r.`subject_no`=sub.`subject_no`
WHERE `subject_name`='高等数学-1'
ORDER BY `student_result` DESC

分页

语法：limit 起始位置页面的大小

为什么分页：

　　缓解数据库压力，给人的体验更好瀑布流

语法： limit (查询起始下标,页面大小)

-- 查询科目高等数学-2，课程成绩排名前十的学生，并且分数要大于60的学生信息（学号，姓名，课程名称，分数）
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `subject` sub
ON stu.`grade_id`=sub.`grade_id`
INNER JOIN `result` res
ON sub.`subject_no`=res.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result`>60
ORDER BY res.`student_result`
LIMIT 0,10;

4.6子查询

where（这个值是计算出来的）

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

-- 1.查询数据库结构-1的所有考试结果（学号，科目名，成绩），降序排列
-- 方式1：使用连接查询
SELECT res.`student_no`,res.`subject_no`,res.`student_result`
FROM `result` res
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
ORDER BY res.`student_result` DESC;


-- 使用子查询(由里及外)
SELECT res.`student_no`,res.`subject_no`,res.`student_result`
FROM `result` res
WHERE res.`subject_no` = (
  SELECT sub.`subject_no`
  FROM `subject` sub
  WHERE sub.`subject_name`='高等数学-2'
)
ORDER BY res.`student_result` DESC;

-- 分数不小于80分的学生的学号和姓名

SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
WHERE res.`student_result` > 80;


-- 在这个基础上增加一个科目，查询课程为高等数学-2，且分数不小于80分的学生的学号和姓名
SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
WHERE res.`student_result` > 80
AND res.`subject_no`=(
SELECT sub.`subject_no` FROM `subject` sub
WHERE sub.`subject_name`='高等数学-2'
);


SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result` > 80;

--  再次改造（由里及外）
SELECT DISTINCT `student_no`,`student_name` FROM student WHERE student_no IN (
  SELECT student_no FROM result WHERE `student_result` > 80 AND subject_no = (
    SELECT subject_no FROM `subject` WHERE `subject_name`='高等数学-2'
  )
);

4.7分组和过滤

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

5 MySQL函数

5.1常用函数

----------常用函数------------------------------
-- 数学运算
select ABS(-8)  -- 绝对值 ABS(X)
select CEILING(9.4)  -- 向上取整  ceiling(x)
SELECT FLOOR(9.4)  -- 向下取整 FLOOR(X)
SELECT RAND()   -- 返回一个0-1之间的随机数
SELECT SIGN(10)   -- 判断一个数的符号 0-0 负数返回-1，整数返回1

-- 字符串函数
select char_length('即使再小的帆，也能远航') -- 查字符串长度 CHAR_LENGTH(str)
select CONCAT('','','')  -- 拼接字符串
select `INSERT`('我爱变成hello',1,4,'超级热爱编程') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('KuangShen') -- 小写字母
select UPPER('KuangShen') -- 大写字母
select INSTR('KuangShen',n)  -- 但会第一次出现的子串的索引
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 一段字符串，替换指定字符串
select SUBSTR('狂神说坚持就能成功',4.6)  -- 一段字符串，从第几个字符开始，截取多少个字符
select reverse('清晨我上马')  -- 反转字符串

-- 查询 姓 周的同学， 改为 邹
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() -- 版本

5.2 聚合函数（常用）

函数名称	描述
count()	计数
sum（）	求和
avg（）	平均值
max（）	最大值
min（）	最小值

-- ========聚合函数=======
-- 都能够统计表中的数据
SELECT COUNT(`student_name`) FROM `student` -- count(指定列)，会忽略所有的null值
SELECT COUNT(*) FROM `student`  -- count(*)，不会忽略null值 本质：计算行数
SELECT COUNT(1) FROM `student`  -- count(1)，不会忽略null值  本质：计算行数

SELECT SUM(`student_result`) AS 总和 FROM `result`
SELECT AVG(`student_result`) AS 平均成绩 FROM `result`
SELECT MIN(`student_result`) AS 最低成绩 FROM `result`
SELECT MAX(`student_result`) AS 最高成绩 FROM `result`

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

什么是MD5？

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

MD5不可逆，具体的值的MD5是一样的。

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

CREATE TABLE `testMD5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(40) NOT NULL,
    `pwd` VARCHAR(40) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三',12345),(2,'李四',54321),(3,'王六',415263)

-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1
UPDATE `testmd5` SET pwd=MD5(pwd) -- 加密全部

-- 插入时加密
INSERT INTO `testmd5` VALUES (4,'给我',MD5(124578))

-- 如何效验，将用户传递进来的密码进行md5加密，用值来对比
SELECT * FROM testmd5 WHERE NAME ='小明' AND pwd= MD5('123')

select完整语法

select [ALL | distinct]
{*|table.* |[table.field1[as alias1][table.field1[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}]; --指定查询的记录从哪条至哪条

6 事务

6.1 什么是事务

==要么都成功，要么都失败==

将一组sql放到一个批次中取执行

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

原子性（Atomicity）

针对同一个事务

要么都成功，要么都失败

这个过程包含两个步骤：

A：800-200=600

B：200+200=400

原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作

一致性（Consistency）

事务前后的数据完整性要保持一致

持久性（Durability）--- 事务提交

事务一旦提交则不可逆，被持久化到数据库中！

隔离性（Isolation）

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

　　隔离所导致的一些问题：

　　脏读

　　　　指一事务读取到另一个事务未提交的数据

不可重复读

　　　　在同一个事务中，前后两次读取的数据不一致

虚读（幻读）

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

-- mysql 是默认开启事务自动提交的
set autocommit =0 -- 关闭自动提交事务
set autocommit =1 -- 开启自动提交事务（默认）

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

-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的 sql 都在同一事务内
insert xxx
insert xxxx

-- 提交：持久化（成功！）
COMMIT
-- 回滚：回道原来的样子（失败！）
ROLLBACK
-- 事务结束
set atuommit=1 -- 开启自动提交

-- 了解
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存

流程：

模拟场景：

-- 转账

-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 使用shop数据库
USER `shop`;


-- 建表
CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) 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; -- 恢复自动提交

7 索引

Msql官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1索引的分类

主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（key/index）
- 默认的，index，key关键字来设置
全文索引（FUllText）

在特定的数据库引擎下才有，MyISMA
快速定位数据

基础语法：

-- 索引的使用

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

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

-- 新增一个索引 (索引名) 列名

ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);

ALTER TABLE `student`  ADD FULLTEXT INDEX `FI_PHONE` (`phone`);

-- explain 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引

7.2 测试索引

-- 创建一个新表
CREATE TABLE app_user (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `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 COMMENT '创建时间',
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

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

添加索引

-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.440 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

-- 创建索引
-- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);
 -- 加索引后
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

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

7.3 索引原则

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

索引的数据结构

Hash 类型的索引

Btree:innodb 的默认数据结构

8.权限管理和备份

8.1用户管理

sql yog 可视化管理

sql 命令操作

======================创建用户===========================
-- 创建用户 create user 用户名 identified by ‘密码’

create user kuangshen identified by '123456'

-- 修改密码（修改当前用户名密码）set password= password('新密码')
SET PASSWORD= PASSWORD('123456')

-- 修改密码（修改制定用户名密码）
set password for root = password('123456')

-- 重命名 rename user 原用户名 to 新用户名
rename user root to rootdouble

-- 用户授权 grant all privileges 全部的权限 库.表
-- 除了给别人授权，其他都能够干
grant all privileges on *.* to kuangshen
-- 查询权限
show grants FOR kuangshen -- 茶看制定用户的权限
show grants for root@localhost
-- root 用户的权限

-- 撤销权限 revoke 那些权限， 在哪个库撤销 给谁撤销
revoke all privileges on *.* from kuangshen

-- 删除用户
drop user kuangshen

8.2mysql备份

为什么要备份？

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

mysql数据库备份的方式

直接拷贝物理文件
在sqlyog这种可视化工具中手动导出
- 在想要导出的表或者库中，右键

使用命令行导出 mysqldump 命令行使用

--  一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

-- 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql

--  数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql

--  导入
--  登录的情况下，切换到指定的数据库
--  source 备份文件
--  也可以这样
mysql -u用户名 -p密码 库名<备份文件

假设你要备份数据库，防止数据丢失。

把数据库给别人，直接给sql即可。

9.规范数据库设计

9.1为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

设计数据库的步骤：

收集信息，分析需求

用户表（用户登陆注销，用户的个人信息，写博客，创建分类）

分类表（文章分类，谁创建的）
文章表（文章的信息）
友链接（友链信息）

自定义表（系统信息，某个关键的子，或者一些主字段）

标识实体（把需求落地到每个字段）
标识实体之间的关系

写博客user-->blog
创建分类user -->category
关注：user-->User

9.2三大范式

为什么需要数据规范化

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

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

墙体：满足第一范式和第二范式

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

（规范数据库的设计）

规范性和性能的问题

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

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

10.JDBC（重点）

10.1数据库驱动

驱动：声卡、显卡、数据库

我们的程序会通过数据库驱动，和数据库打交道！

10.2JDBC

JDBC全称为：Java Data Base Connectivity（java数据库连接），它主要由接口组成。
组成JDBC的２个包：java.sql、javax.sql
开发JDBC应用需要以上2个包的支持外，还需要导入相应JDBC的数据库实现(即数据库驱动包—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,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

新建一个Java工程，并导入数据驱动。

jar包下载地址：maven仓库。

编写程序从user表中读取数据，并打印在命令行窗口中：

package com.king.lesson01;

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=true";
        String username="root";
        String password="x5";

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

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

        //5.执行sql的对象去执行sql，可能存在结果，查看返回结果
        String sql="SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们查询出来的全部结果

        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"));
            System.out.println("==============================");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

加载驱动；
连接数据库 DriverManager；
获得执行SQL的对象 Statement；
获得返回的结果集；
释放连接。

10.4 使用jdbc对数据库增删改查

新建一个 lesson02 的包；

在src目录下创建一个db.properties文件，写入如下内容：

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

在lesson02 下新建一个 utils 包，新建一个类 Utils：

package com.github.lesson02.utils;

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

public class Utils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static{
        try{
            // 读取db.properties文件中的数据库连接信息
            InputStream in = Utils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            // 获取数据库连接驱动
            driver = properties.getProperty("driver");
            // 获取数据库连接URL地址
            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 conn, Statement st, ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

执行增删改查数据

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

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

/**
 * 插入数据
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            // 获取一个数据库连接
            conn = Utils.getConnection();

            // 通过conn对象获取负责执行SQL命令的Statement对象
            st = conn.createStatement();

            // 要执行的SQL
            String sql = "";

            // 执行操作
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            Utils.release(conn,st,rs);
        }
    }
}

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

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

/**
 * 删除数据
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            String sql = "delete from users where id=4";
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("删除数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

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

/**
 * 修改数据
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            String sql = "update users set name='王伟',email='wangwei@163.com' where id=3";
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("更新数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

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

/**
 * 查询数据
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
//            String sql = "select * from users";
            String sql = "select * from users where id=2";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("查询数据成功！！！");
//                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

10.5 PreparedStatement对象

PreperedStatement是Statement的子类，它的实例对象可以通过调用 Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避免SQL注入的问题。

Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。

PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于 sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

插入数据

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            // 为SQL参数赋值，索引是从1开始的
            st.setInt(1,5); // id是int类型
            st.setString(2,"subei"); // name是字符串类型
            st.setString(3,"123456"); // password是字符串类型
            st.setString(4,"24635862@qq.com"); // email是字符串类型
            st.setDate(5,new java.sql.Date(System.currentTimeMillis())); // birthday是date类型

            /*
            * 这里有个小问题:
            *   在使用  new Date().getTime() 时，会报错：请使用System.currentTimeMillis()代替new Date().getTime()
            * 对于这个问题，百度一下：
            *   new Date()所做的事情其实就是调用了System.currentTimeMillis()。
            *   如果仅仅是需要或者毫秒数，那么完全可以使用System.currentTimeMillis()去代替new Date()，
            *   效率上会高一点。况且很多人喜欢在同一个方法里面多次使用new Date()，
            *   通常性能就是这样一点一点地消耗掉，这里其实可以声明一个引用。
             * */

            // 执行插入数据操作
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL释放资源
            Utils.release(conn,st,rs);
        }

    }
}

删除数据

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

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

/**
 * 删除数据
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "delete * from users where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,4);
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("删除数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

更新数据

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

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

/**
 * 修改数据
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "update users set name=?,email=? where id=?";
            st = conn.prepareStatement(sql);
            st.setString(1,"叶凡");
            st.setString(2,"632579682@163.com");
            st.setInt(3,1);
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("更新数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

查询数据

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

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

/**
 * 查询数据
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "select * from users where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,1);
            rs = st.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println("查询数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

10.6 使用IDEA连接数据库

打开IDEA2020.2的如下图：

打开如下界面，开始进行相关设置：

连接成功后，进入下图操作：

然后打开如下图：

一定要点击那个绿色的箭头，否则更新失败，数据未保存！更新成功如下图：

编写数据库，编写后点击下图左上角绿色执行，具体如下图：

10.7 JDBC操作事务

事务：逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。
ACID原则：
- 原子性(Atomic)：要么全部完成，要么都不完成。
- 一致性(Consist)：总数不变。
- 隔离性(Isolated)：多个进程互不干扰。
- 持久性(Durable)：一旦提交不可逆，持久化到数据库了。
隔离导致的一些问题：
- 脏读：一个事务读取了另外一个事务未提交的数据。
- 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）。
- 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列的 JDBC控制事务语句。

Connection.setAutoCommit(false);//开启事务(start transaction)
Connection.rollback();//回滚事务(rollback)
Connection.commit();//提交事务(commit)

模拟转账成功时的业务

package com.github.lesson04;

import com.github.lesson02.utils.Utils;

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

/**
 * 模拟转账成功时的业务场景
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // 关闭数据库的自动提交，自动开启事务
            conn = Utils.getConnection();
            // 开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money=money+100 where name='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("转账成功！！！");
        } catch (Exception e) {
            try {
                conn.rollback(); // 失败，则事务回滚
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }

    }
}

模拟转账过程中出现异常，导致部分SQL执行失败后，让数据库自动回滚事务

package com.github.lesson04;

import com.github.lesson02.utils.Utils;

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

/**
 * 模拟转账失败时的业务场景
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // 关闭数据库的自动提交，自动开启事务
            conn = Utils.getConnection();
            // 开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            int x=1/0;  // 报错

            String sql2 = "update account set money=money+100 where name='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("转账成功！！！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }

    }
}

10.7 数据库连接池

用户每次请求都需要向数据库获得链接，而数据库创建连接通常需要消耗相对较大的资源，创建时间也较长。假设网站一天10万访问量，数据库服务器就需要创建10万次连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、拓机。

数据库连接池的基本概念：

数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现的尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正式针对这个问题提出来的。数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。

数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

数据库连接池的最小连接数和最大连接数的设置要考虑到以下几个因素:

最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。
最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作。
如果最小连接数与最大连接数相差很大：那么最先连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，他将被放到连接池中等待重复使用或是空间超时后被释放。

编写连接池，需实现java.sql.DataSource接口。

DBCP：

要使用DBCP数据源，需要导入如下三个 jar 文件：

　　Commons-dbcp-1.4.jar

　　Commons-pool-1.6.jar

　　commons-logging-1.2.jar

在src目录下加入dbcp的配置文件：dbcpconfig.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED,REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

编写工具类 Utils_DBCP

package com.github.lesson05.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 数据库连接工具类
 */
public class Utils_DBCP {
    private static DataSource ds = null;

    static{
        try {
            InputStream in = Utils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties prpo = new Properties();
            prpo.load(in);
            // 创建数据源
            ds = BasicDataSourceFactory.createDataSource(prpo);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

测试

package com.github.lesson05;

import com.github.lesson05.utils.Utils_DBCP;
import java.sql.*;

/**
 * 数据库连接工具类测试
 */
public class TestDbcp {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils_DBCP.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,6);
            st.setString(2,"apple");
            st.setString(3,"232323");
            st.setString(4,"327338203@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
            // 执行插入数据操作
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL释放资源
            Utils_DBCP.release(conn,st,rs);
        }
    }
}

C3P0：

C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。
c3p0与dbcp区别：
- dbcp没有自动回收空闲连接的功能；
- c3p0有自动回收空闲连接功能。
要使用C3P0数据源，需要导入如下两个 jar 文件：
- c3p0-0.9.5.5.jar
- mchange-commons-java-0.2.19.jar

在src目录下加入C3P0的配置文件：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="acquireIncrement">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");”这样写就表示使用的是name是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="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

编写工具类 Utils_C3P0.java

package com.github.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**
 * C3P0工具类
 */
public class Utils_C3P0 {
    private static ComboPooledDataSource ds = null;

    static{
        try {
            // 使用C3P0的默认配置来创建数据源
            ds = new ComboPooledDataSource();
            // 使用C3P0的命名配置来创建数据源
//            ds = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

package com.github.lesson05;

import com.github.lesson05.utils.Utils_C3P0;

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

/**
 * C3P0测试类
 */
public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils_C3P0.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,7);
            st.setString(2,"pink");
            st.setString(3,"263223");
            st.setString(4,"3276128203@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
            // 执行插入数据操作
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL释放资源
            Utils_C3P0.release(conn,st,rs);
        }
    }
}

posted @ 2021-12-07 20:21 King_X 阅读(94) 评论(0) 收藏举报

刷新页面返回顶部

许华杰