MySQL

1. 初始MySql

JavaEE : 企业级Java开发 Web

前端：页面：展示、数据

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

数据库：存数据

1.1 为什么学习数据库

  1. 岗位需求

  2. 大数据时代：得数据者得天下

  3. 需求：存数据

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

1.2 什么是数据库

数据库（DB DataBase）

概念：数据仓库 软件(安装在操作系统之上) SQL可以存储大量的数据

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

1.3 数据库分类

关系型数据库： （SQL）

  • MySQL、Oracle、SQL server......

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

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

  • Redis、MongDB

  • 对象存储，通过对象自身的属性来决定

 

DBMS(数据库管理系统)

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

  • MySQL，数据库管理系统

1.4 MySQL简介

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

  • 由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品

  • 在 WEB应用方面，MySQL是最好的 RDBMS 应用软件之一

  • 体积小、速度快、总体拥有成本低

  • 开放源码的数据库软件

1.5 安装MySQL、SQLyog并使用

安装MySQL、SQLyog 略

操作SQLyog：

  1. 新建一个数据库：school

     

    1. 新建一张表：student

      字段：id name age

       

    2. 查看表

       

 

1.6 连接数据库

基本的命令行操作：

 mysql -uroot -proot  --连接数据库
 ​
 update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost';   --修改用户密码
 ​
 flush privileges;  --刷新权限
 ​
 ---------------------------------------
 -- 所有的sql语句都要使用;结尾
 ​
 show databases;  --查看所有的数据库
 ​
 use school;  --切换数据库   use 数据库名
 ​
 show tables;  --查看数据库中所有的表
 ​
 describe student;  --显示数据库中所有的表得信息
 ​
 create database teacher;  --创建一个teacher数据库
 ​
 exit;  --退出连接

 

DxL ：数据库xxx语言

DDL 数据库定义语言 DML 操作 DQL 查询 DCL 控制

2. 操作数据库

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

mysql关键字不区分大小写

2.1 操作数据库（了解）

  1. 创建数据库

     CREATE DATABASE teacher;

  2. 删除数据库

     DROP DATABASE teacher;

  3. 使用数据库

     -- `` tab键的上面  如果表名或者字段名是一个特殊字符，就需要带上
     USE `school`;

  4. 查看数据库

     SHOW DATABASES;   --查看所有的数据库

学习思路：

  • 对照sqlyog的历史记录可以查看sql语句

  • 固定的语法或关键字必须强行记住

 

2.2 数据库的列类型

数值

常用的：

  • int 标准的整数 4个字节

  • float 浮点数 4个字节

  • double 浮点数 8个字节 (存在精度问题)

  • decimal 字符串形式的浮点数 金融计算的时候 用decimal

字符串

  • varchar 可变字符串 0-65535

  • tinytext 微型文本 2^8-1

  • text 文本串 2^16-1 保存大文本

时间日期

java.util.Date

  • date YYYY-MM-DD 日期格式

  • time HH:mm:ss 时间格式

  • datetime YYYY-MM-DD HH:mm:ss

  • timestamp 时间戳 1970.1.1到现在的毫秒数

  • year 年份表示

null

  • 没有值 未知

  • 注意：不要使用NULL进行运算，结果为NULL

 

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

Unsigned

  • 无符号的整数

  • 声明了该列不能为负数

Zerofill

  • 0填充

  • 不足的位数。使用0来填充 例：int(2) 1-->01

AUTO_INCREMENT

  • 自增 自动在上一条记录的基础上 +1 (默认是0，可以更改)

  • 通常用来设计唯一的主键 必须是整数类型

Not Null / NULL

  • 假设设置为 not null 如果不给它赋值，就会报错

  • NULL 如果不填写值，默认就是NULL

Default

  • 设置默认的值

  • 例：sex 如果设置默认值为男，如果不指定该列的值，则默认值就是男

补充：阿里的建表规范

必须有的：

 /*
 每个表，都必须存在以下五个字段，表示每个记录都存在意义
 */
 ​
 id  主键
 `version`   乐观锁
 is_delete   伪删除
 gmt_create  创建时间
 gmt_update  修改时间

 

2.4 创建数据库（重点）

小案例：

 -- 目标：创建一个school数据库
 -- 创建学生表(列、字段) student
 -- 学号 姓名 性别 密码 出生日期 家庭住址 email
 ​
 CREATE TABLE `student`(
     `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
     `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
     `sex` VARCHAR(3) NOT NULL DEFAULT '男' COMMENT '性别',
     `psw` VARCHAR(6) NOT NULL DEFAULT '123456' COMMENT '密码',
     `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
     `address` VARCHAR(120) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱地址',
     PRIMARY KEY (`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8

注意：

  • 括号使用英文的 表的名称、字段最好使用``括起来（tab键上边那个）

  • 字符串使用，单引号括起来

  • 所有的语句后面加 , （英文的），最后一个不用加

  • PRIMARY KEY (主键) 主键用括号括起来

 格式：
 ​
 CREATE TABLE `表名`(
     `字段名` 列类型 [属性] [索引] [注释],
     `字段名` 列类型 [属性] [索引] [注释],
     ......
     `字段名` 列类型 [属性] [索引] [注释]
 )

 

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

 

2.5 数据表的类型

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

 

常规使用操作：

  • MYISAM 节省空间 速度较快

  • INNODB 安全性高 事务的处理 多表多用户操作

 

所有的数据库文件都存储在data目录下，一个文件夹对应一个数据库，本质上还是文件的存储

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

CHAESET=utf8

MySQL的默认编码是Latin1，不支持中文

 

2.6 修改、删除表

修改

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

-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE students ADD age INT(3)

-- 修改表的字段（重用名、约束）
-- MODIFY  修改约束  ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE students MODIFY age VARCHAR(3)
-- CHANGE  字段重用名 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性
ALTER TABLE students CHANGE age age1 INT(3)

 

删除

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

-- 删除表：DROP TABLE 表名
DROP TABLE students

 

3. MySQL数据管理

3.1 外键（了解即可）

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

-- 创建一个年级表 与学生表通过外键关联起来
CREATE TABLE `grade`(
	`gradeid` INT(3) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段  去引用年级表的gradeid
-- 定义外键key
-- 给这个外键加约束（执行引用） references 引用   foreign key 外键

CREATE TABLE `students` (
  `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(3) NOT NULL DEFAULT '男' COMMENT '性别',
  `psw` VARCHAR(6) NOT NULL DEFAULT '123456' COMMENT '密码',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(120) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱地址',
  `gradeid` INT(3) NOT NULL COMMENT '年级id',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须先删除引用的表，再删除被引用的表

 

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

-- 创建一个年纪表 与学生表通过外键关联起来
CREATE TABLE `grade`(
	`gradeid` INT(3) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表的gradeid字段  去引用年级表的gradeid

CREATE TABLE `students` (
  `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(3) NOT NULL DEFAULT '男' COMMENT '性别',
  `psw` VARCHAR(6) NOT NULL DEFAULT '123456' COMMENT '密码',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(120) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱地址',
  `gradeid` INT(3) NOT NULL COMMENT '年级id',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 定义外键key
-- 给这个外键加约束（执行引用） references 引用   foreign key 外键

-- ALTER TABLE 表名 ADD CONSTRAINT `FK_字段名` FOREIGN KEY(`字段名`) REFERENCES `被引用的表名`(`字段名`);
ALTER TABLE `students`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

 

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

最佳实践：

  • 数据库就是单纯的表，只用来存数据，只有行（数据），列（字段）

  • 我们想使用多张表的数据，想使用外键，可以用程序去实现

3.2 DML语言(重点)

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

DML语言：数据操作语言

  • insert

  • update

  • delete

添加 insert

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

-- 插入语句
-- 语法：INSERT INTO 表名(字段名1,字段名2,字段名3,......) VALUES('值1','值2','值3',......)
-- 如果设置主键自增，可以省略
INSERT INTO `grade`(`gradename`) VALUES('大三')

-- 字段名与值按写的顺序一一匹配，写的时候要一一对应

INSERT INTO `students`(`name`,`psw`,`address`) VALUES('张三','13142','河北省石家庄市')

INSERT INTO `students`(`name`,`sex`,`psw`,`birthday`,`address`,`email`,`gradeid`)
VALUES('胖丫','女','666666','2000-1-6','河北省衡水市','6666666@qq.com','1')

-- 全字段添加  可以省略字段名，数值会根据数据表所有字段按顺序一一对应
INSERT INTO `students`
VALUES('3','菲菲','女','888888','2002-6-18','河北省衡水市','888888@qq.com','3')

INSERT INTO `grade`(`gradename`)
VALUES('大一')

INSERT INTO `grade`(`gradename`)
VALUES('大二')

INSERT INTO `grade`(`gradename`)
VALUES('大四')

INSERT INTO `students`(`name`,`sex`,`email`,`psw`)
VALUES('牛牛','男','66986@qq.com','669652')

 

修改 update

语法：UPDATE 表名 SET 字段名1=值1,字段名2=值2,字段名3=值3 WHERE 条件;

-- update 修改   set 字段=值 
UPDATE `grade` SET `gradename`='大一' WHERE `gradeid`='1';

-- 不指定条件的情况下，会改动所有的表
UPDATE `grade` SET `gradename`='大一';

-- 修改多个属性，英文,隔开
UPDATE `students` SET `name`='超超',`psw`='211314',`address`='河北省衡水市',`email`='zcc2671185030@qq.com',`gradeid`='3' WHERE `id`='1';

-- 语法：UPDATE 表名 SET 字段名1=值1,字段名2=值2,字段名3=值3 WHERE 条件;

条件：where语句

操作符含义范围结果
= 等于 5=3 false
<> 或 != 不等于 5=3 true
> 大于 5>3 true
< 小于 5<3 false
>= 大于等于 5>=3 true
<= 小于等于 5<=3 false
BETWEEN...AND... 双闭合区间 [2,5]  
AND && 我和你 5>3 and 1>2 false
OR || 我或你 5>3 or 1>2 true 
-- 可以通过多个条件锁定数据  CURRENT_DATE:当前系统日期
UPDATE `students` SET `birthday`=CURRENT_DATE WHERE `sex`='女' OR `gradeid`='3';

 

删除 delete

语法：DELETE FROM 表名 WHERE 条件

-- 删除指定数据
DELETE FROM `students` WHERE `id`='4'

-- 删除数据表  （避免这样写  会全部删除）
DELETE FROM `students`

-- 而且清除数据表数据的话  推荐用truncate命令

TRUNCATE TABLE `students`

-- 这两个命令 DELETE、TRUNCATE都能清除整个数据表的命令 但是有区别
-- DELETE删除后，自增的字段不会清空，但是重新启动数据库后会清空，因为是存在缓存里的
-- TRUNCATE删除后，自增的字段会清空，再从1开始

 

4. DQL 查询语句(最重点)

4.1 DQL

（DATE QUERY LANGUAGE ：数据查询语言）

  • 所有的查询操作都用它 select

  • 简单的查询 复杂的查询它都能做

  • 数据库中最核心的语言 最重要的语句

  • 使用频率最高的语句

select语法：

 

 

4.2 查询指定字段

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

还可以通过as起别名

-- 查询全部的学生  
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`

 

去重：distinct

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

-- 任务：查看有哪些同学参加了考试
SELECT * FROM `result`  -- 查询全部的考试成绩
SELECT `studentno` FROM `result`  -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result`   -- 去重

数据库的列（表达式）

SELECT VERSION()  -- 查看系统版本
SELECT 100/3 AS `结果`  -- 用来计算
SELECT @@auto_increment_increment  -- 查询自增的步长（变量）

-- 设置所有学生的考试成绩+1  并查看
SELECT `studentno`,`studentresult`+1 AS '新成绩' FROM `result`

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

select 表达式 from 表

4.3 where条件子句

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

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

逻辑运算符

运算符语法描述
and && a and b a && b 逻辑与，两个都为真，结果为真
or || a or b a || b 逻辑或，两个都为假，结果为假
not ! not a !a 逻辑非 真为假 ，假为真

尽量使用英文字母

-- 查询考试成绩在95到100之间的学生
SELECT `studentno` FROM `result` WHERE `studentresult`>=95 AND `studentresult`<=100

-- and 和 && 作用一样
SELECT `studentno` FROM `result` WHERE `studentresult`>=95 && `studentresult`<=100

-- 模糊查询 （区间） between and
SELECT `studentno` FROM `result` 
WHERE `studentresult` BETWEEN 95 AND 100

-- !=  not
SELECT DISTINCT `studentno` FROM `result`
WHERE `studentno` !=1000

模糊查询：比较运算符

运算符语法描述
is null a is null 如果操作符为null，结果为真
is not null a is not null 如果操作符不为null，结果为真
between...and... a between b and c 若a在b和c之间，结果为u真
like a like b SQL匹配 如果a匹配b，则结果为真
in a in(a1,a2,a3.....) 假设a在a1，或者a2......其中的一个值中，结果为真

注意：判断为null时候，如果是varchar类型，注意加上空字符串''

% _ 是在like上用的

  • %：0到任意个字符

  • _：一个字符

-- 查询姓张的同学
-- like 结合 % _
SELECT studentname FROM student
WHERE studentname LIKE '张%';

-- 查询姓张的、两个字的同学
SELECT studentname FROM student
WHERE studentname LIKE '张_';

-- 查询名字中带有 强 字的同学
SELECT studentname FROM student
WHERE studentname LIKE '%强%';

-- in 具体的一个或者多个值
-- 查询 赵强、张三、王五 这三个学生的信息
SELECT * FROM student
WHERE studentname IN('赵强','张三','王五');

-- 查询在河北衡水的同学
SELECT studentname FROM student
WHERE address IN('河北衡水');

-- 查询地址为空的同学
SELECT studentname FROM student
WHERE address IS NULL OR address='';

4.4 联表查询

JOIN 对比

 

 

7种JOIN理论

 

-- 联表查询 join

-- 查询参加了考试的同学(学号、姓名、科目、分数)
SELECT * FROM `student`
SELECT * FROM `result`

/*思路：
1. 分析需求，分析查询的字段来自哪些表(联结查询)
2. 确定使用哪种连接查询  共7种
3. 确定交叉点(这两个表哪些数据是相同的)
4. 判断的条件：学生表中的studentno=成绩表中的studentno
*/

-- INNER JOIN  找共同有的
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno=r.studentno;

-- LEFT JOIN  所有左表全部数据，找右边有的  但是没有成绩的也被显示出来了
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno=r.studentno


-- RIGHT JOIN  所有右表全部数据，找左边有的
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno=r.studentno

-- 查询缺考的同学
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno=r.studentno
WHERE studentresult IS NULL
操作描述
INNER JOIN 如果表中至少有一个匹配，就返回行
LEFT JOIN 会从左表中返回所有的值，即使右表没有匹配
RIGHT JOIN 会从右表中返回所有的值，即使左表没有匹配

JOIN ON 连接查询

JOIN WHERE 等值查询

-- 思考题：查询参加考试的学生信息：学号、姓名、科目名、分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno

/* 小结
我要查询哪些数据  select....
从哪几个表中查  from 表 xxx join 连接的表  on  交叉条件
假设存在多张表查询，慢慢来，先查询两张表然后慢慢增加
*/

 

自连接

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

原表：

 

拆分：

父类表：

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

子类表：

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

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

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

sql语句：

-- 查询父子信息

SELECT f.categoryname AS '父',z.categoryname AS '子'
FROM category f,category z
WHERE f.categoryid=z.pid

 

练习：

-- 查询学生所属的年级:学号、姓名、年级名称
SELECT studentno,studentname,gradename
FROM student s
LEFT JOIN grade g
ON s.gradeid=g.gradeid


-- 查询科目所属的年级（科目名称、年级名称）
SELECT subjectname,gradename
FROM `subject` s
INNER JOIN grade g
ON s.gradeid=g.gradeid


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

 

4.5 分页和排序

排序

-- 需求：找到所有参加‘高等数学-1’考试的学生，并把成绩排序

-- 排序 order by 通过哪个字段  怎么排  (不写的话默认是升序)
-- 查询的结果：  升序 ASC    降序 DESC
SELECT studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC

 

 

分页

语法： LIMIT (n-1)*pageSize,n

-- 为什么分页？  1. 缓解数据库压力 2. 给用户更佳的体验
-- 需求：分页  每页显示2条数据
-- 语法 limit 开始的下标 每页显示的几条数据
SELECT studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,2


-- 第一页   LIMIT 0,2     (1-1)*2
-- 第二页   LIMIT 2,2     (2-1)*2 
-- 第三页   LIMIT 4,2     (3-1)*2

-- 第N页    LIMIT (n-1)*2,2     (n-1)*2
-- pageSize: 页面大小
-- (n-1)*pageSize: 起始值
-- n: 当前页

-- 语法： LIMIT (n-1)*pageSize,n

 

练习

-- 需求：'高等数学-1' 课程排名前3的学生，并且分数大于80的学生信息 （学号、姓名、课程名称、分数）


SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,3

 

4.6 子查询

子查询

-- 需求 ：查询 高等数学-1 的所有考试结果(学号、科目编号、成绩) 降序

-- 方法一：连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE sub.subjectname='高等数学-1'
ORDER BY studentresult DESC 

-- 方法二：子查询

-- 找到subjectname='高等数学-1'的subjectno
SELECT subjectno
FROM `subject` sub
WHERE sub.subjectname='高等数学-1'

-- 开始 子查询
SELECT studentno,subjectno,studentresult
FROM result r
WHERE subjectno=(
	SELECT subjectno
	FROM `subject` sub
	WHERE sub.subjectname='高等数学-1'
)

练习：

-- 练习：分数不小于80分的学生的学号和姓名,课程是'高等数学-1'

-- 方式1：
SELECT s.studentno,studentname
FROM student s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE r.studentresult>=80 AND sub.subjectname='高等数学-1'
ORDER BY r.studentresult 

-- 方式2：
SELECT s.studentno,studentname
FROM student s
INNER JOIN `result` r
ON s.studentno=r.studentno
WHERE r.studentresult>=80 AND r.subjectno=(
	SELECT subjectno
	FROM `subject` sub
	WHERE sub.subjectname='高等数学-1'
)

5. MySQL函数

5.1 常用函数

-- 常用函数

-- 数学运算
SELECT ABS(-1) 绝对值
SELECT CEILING(5.2) 向上取整
SELECT FLOOR(5.2) 向下取整
SELECT RAND() `随机数(0~1)`
SELECT SIGN(2) -- 判断一个数的符号，负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('我爱学习')  -- 字符串长度
SELECT CONCAT('我','爱','编程')  -- 拼接字符串
SELECT LOWER('I Love You')  -- 转小写字母
SELECT UPPER('I Love You')  -- 转大写字母
SELECT INSTR('I Love You','Y')  -- 返回第一次出现的字串的索引
SELECT REPLACE('I Love You','I','He')  -- 替换出现的指定字符串
SELECT SUBSTR('I Love You',3,4)  -- 返回指定的子字符串(开始索引下标,子字符串长度)
SELECT REVERSE('I Love You')  -- 反转

-- 时间和日期函数
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()

4.7 分组和过滤

-- 需求：查询不同课程的平均分，最高分，最低分  平均分>80
-- 核心：根据不同的课程分组

SELECT subjectname,AVG(studentresult) 平均分,MAX(studentresult) 最高分,MIN(studentresult) 最低分
FROM `subject` sub
INNER JOIN result r
ON sub.subjectno=r.subjectno
GROUP BY sub.subjectno  -- 通过哪个字段分组
HAVING 平均分>80

分组了 过滤分组的记录 需要用having 不能用where了 还要注意语句的顺序

5.2 聚合函数(常用)

函数名称描述
count() 计数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
...... ...... 
-- 聚合函数

SELECT COUNT(`studentname`) FROM student  -- 会忽略所有的null值
SELECT COUNT(*) FROM student  -- 不会忽略null值 本质是计算行数
SELECT COUNT(1) FROM student  -- 不会忽略null值 本质是计算行数

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

练习：

-- 需求：查询不同课程的平均分，最高分，最低分  平均分>80
-- 核心：根据不同的课程分组

SELECT subjectname,AVG(studentresult) 平均分,MAX(studentresult) 最高分,MIN(studentresult) 最低分
FROM `subject` sub
INNER JOIN result r
ON sub.subjectno=r.subjectno
GROUP BY sub.subjectno
HAVING 平均分>80

分组了 过滤分组的记录 需要用having 不能用where了 还要注意语句的顺序

5.3 数据库级别的md5加密

什么是md5？

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。

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

 

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

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

演示：数据库级别的md5加密

-- 测试md5加密

-- 建表
CREATE TABLE `md5Test`(
	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`username` VARCHAR(20) NOT NULL COMMENT 'username',
	`psw` VARCHAR(20) NOT NULL COMMENT 'psw',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入数据  明文密码
INSERT INTO md5Test(username,psw) VALUE('zhangsan','123456'),('lisi','666666'),('wangwu','888888')

-- 加密
UPDATE md5Test SET psw=MD5(psw) WHERE id=1

UPDATE md5Test SET psw=MD5(psw)  -- 加密所有

-- 但是应该在插入的时候进行加密
INSERT INTO md5Test(username,psw) VALUE('haha',MD5('123456'))

-- 如何校验：将用户传递进来的密码  进行MD5加密，然后比对加密后的值
SELECT * FROM md5test WHERE username='haha' AND psw=MD5('123456')

 

6. 事务

6.1 什么是事务

  1. SQL执行 A给B转账 A 1000 --->200 B 0

  2. SQL执行 B收到A的钱 A 800 B 200

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

事务原则：ACID原则

原子性（Atomicity）

要么都成功，要么都失败

也就是说，事务中的操作要么都发生，要么都不发生

一致性（Consistency）

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

比如说转账，A原来有1000，B有0，无论怎么转，两个的合都得是1000

隔离性（Isolation）

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

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

 

隔离所导致的一些问题

脏读：

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

不可重复读：

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

虚读(幻读)：

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

6.2 执行事务

执行流程：

-- 事务

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

-- 手动处理事务 流程

SET autocommit=0  -- 1.关闭自动提交

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

INSERT xxx  -- 3. 写SQL语句
INSERT xxx

COMMIT  -- 4. 提交  持久化(成功)  (一旦提交就固定了，回滚不管用了)

ROLLBACK -- 5. 回滚：回到原来的样子(失败)

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

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

转账实例：

-- 转账

-- 创建数据表
USE school
CREATE TABLE account(
	id INT(3) NOT NULL AUTO_INCREMENT,
	username VARCHAR(20) NOT NULL,
	money DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO account(username,money) VALUES('张三','10000'),('李四','20000')

-- 事务：模拟转帐
SET autocommit=0

START TRANSACTION

UPDATE account SET money=money+5000 WHERE username='张三'
UPDATE account SET money=money-5000 WHERE username='李四'

COMMIT

ROLLBACK

SET autocommit=1

 

7. 索引

MySQL官方对索引的定义：索引(index)是帮助MySQL高效获取数据的数据结构。

索引本质：索引是数据结构

7.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

  • 主键索引(PRIMARY KEY)

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

  • 唯一索引(UNIQUE KEY)

    • 避免重复的列出现，唯一索引可以重复，多个列可以标识为唯一索引

  • 常规索引(KEY/INDEX)

    • 默认 的，index或key关键字来设置

  • 全文索引(FULLTEXT)

    • 在特定的数据库引擎中才有，MyISAM

    • 快速定位数据

基础语法：

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

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

-- 增加一个全文索引 索引名(列名)
ALTER TABLE student ADD FULLTEXT INDEX studentname(studentname)

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student  -- 非全文索引

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

 

7.2 测试索引

先创建100万条数据

-- 建表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT NULL,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


-- 插入100万数据
-- 写函数之前必须要写，标志  
DELIMITER $$

CREATE FUNCTION mock_data2()
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`)
VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data2() -- 执行此函数 生成一百万条数据

-- 建表后，先执行18~31行的  然后好了，执行32行的

测试：

-- 测试有索引和无索引的区别

SELECT * FROM app_user WHERE `name`='用户99999'  -- 时间 00:937  接近1秒

EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999'  -- rows:998577

-- 添加索引
-- id_表名_字段名
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`)

-- 再次查询
EXPLAIN SELECT * FROM app_user WHERE `name`='用户99969'  -- 时间 :00:000 几乎无  rows：1

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

7.3 索引原则

  • 索引不是越多越好

  • 不要对进程变动数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree：InnoDB的默认数据结构

 

8. 权限管理和备份

8.1 用户管理

sql yog 可视化

 

 

sql语句操作

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER zcc IDENTIFIED BY 'root'

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

-- 修改密码 > 修改指定用户密码
SET PASSWORD FOR zcc=PASSWORD('123456')

-- 重用名
RENAME USER zcc TO zcc2

-- 用户授权
-- ALL PRIVILEGES 除了不能给别人授权，其他的都能干
GRANT ALL PRIVILEGES ON *.* TO zcc2
-- 授权还可以对特定用户、特定表授权

-- 查询权限
SHOW GRANTS FOR zcc2 -- 查询指定用户的权限

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

-- 删除用户
DROP USER zcc2

 

8.2 MySQL备份

为什么要备份？

  • 保证重要的数据不丢失

  • 数据转移

MySQL数据库备份的方式

  1. 直接拷贝物理文件data

  2. 在SQLyog 这种可视化工具手动导出

     

     

    导表和导库操作都一样

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

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

-- 导出库
mysqldump -hlocalhost -uroot -proot school >D:/b.sql

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

     

9. 规范数据库设计

9.1 为什么要设计

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

糟糕的数据库设计：

  • 数据冗余，浪费空间

  • 数据库插入和删除都会麻烦、异常【屏蔽使用武力外键】

  • 程序的性能差

良好的数据库设计：

  • 节省内存空间

  • 保证数据库的完整性

 

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

  • 分析需求：分析业务和需要处理的数据库的需求

  • 概要设计：设计关系图 -->E-R图

 

设计数据库的步骤：(个人博客为例)

  • 收集信息，分析需求

    • 用户表(用户登录注销，用户的个人信息，写博客，创建分类)

    • 分类表(文章分类，谁创建的)

    • 文章表(文章的信息)

    • 友链表(友联信息)

    • 自定义表(系统信息，某个关键字。主字段 key:value)

    • ..........

  • 标识实体(把需求落地到每个字段)

  • 标识实体之间的关系

    • 写博客：user-->blog

    • 创建分裂：user-->category

    • ..........

9.2 三大范式

为什么需要数据规范化？

  • 信息重复

  • 更新异常

  • 插入异常

    • 无法正常显示信息

  • 删除异常

    • 丢失有效的信息

三大范式

第一范式(1NF)

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

第二范式(2NF)

前提：满足第一范式

每张表只描述一件事情

第三范式(3NF)

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

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

规范性和性能的问题

鱼和熊掌不可兼得

阿里规范要求：关联查询的表不得超过三张表

  • 也要考虑商业化的需求和目标(成本和用户体验) 数据库的性能更加重要

  • 在规范性能的时候，需要适当的考虑以下规范性

  • 有时候需要故意给某些表增加一些冗余的字段(从多表查询变为单表查询)

  • 有时候需要故意增加一些计算列(从大数据量降低为小数据量的查询：索引)

 

10. JDBC(重点)

10.1 数据库驱动

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

 

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

10.2 JDBC

sun公司为了简化开发人员对数据库的操作，提供了一个(java 操作数据库的)规范，俗称JDBC

这些规范的实现是由具体的厂商去做

对于开发人员，我们只需要掌握jdbc接口的操作即可

 

 

10.3 第一个JDBC程序

  1. 在idea中创建一个普通项目

  2. 在sqlyog建数据库表,插入数据

    CREATE DATABASE `jdbcstudy`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcstudy`;

CREATE TABLE `user` (
  `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` VARCHAR(20) NOT NULL COMMENT '用户姓名',
  `password` VARCHAR(100) NOT NULL COMMENT '用户密码',
  `email` VARCHAR(60) NOT NULL COMMENT '用户邮箱',
  `birthday` DATE NOT NULL COMMENT '用户生日',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

INSERT INTO `jdbcstudy`.`user`(`id`,`username`,`password`,`email`,`birthday`) VALUES ( NULL,'张三','123456','123@qq.com','2016-05-06');
INSERT INTO `jdbcstudy`.`user`(`id`,`username`,`password`,`email`,`birthday`) VALUES ( NULL,'李四','965896','696@qq.com','1980-11-23');
INSERT INTO `jdbcstudy`.`user`(`id`,`username`,`password`,`email`,`birthday`) VALUES ( NULL,'胖丫','896657','568@qq.com','2001-06-16');

  3. 导入数据库驱动

     

  4. 编写代码：

    package com.zcc.lesson01;

import java.sql.*;

//我的第一个JDBC程序
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="root";
        //3. 连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        //4. 执行SQL的对象
        Statement statement = connection.createStatement();
        //5. 执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果
        String sql="SELECT * FROM user";

        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("username="+resultSet.getObject("username"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}
/*
id=1
username=张三
password=123456
email=123@qq.com
birthday=2016-05-06
id=2
username=李四
password=965896
email=696@qq.com
birthday=1980-11-23
id=3
username=胖丫
password=896657
email=568@qq.com
birthday=2001-06-16
 */

    步骤总结：

    1. 加载驱动

    2. 连接数据库 DriverManager

    3. 获得执行SQL得对象 Statement

    4. 获得返回的结果

    5. 释放连接

10.4 第一个JDBC程序升级版

提取工具类

先写配置类文件db.properties

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

提取工具类

package com.zcc.lession02.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 resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1. 驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if (rs!=null){
            rs.close();
        }
    }
}

编写代码:

package com.zcc.lession02;

import com.zcc.lession02.utils.JdbcUtils;

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

public class Test01 {
    public static void main(String[] args) throws SQLException {

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

        try {
            conn= JdbcUtils.getConnection();  //获取数据库连接
            st=conn.createStatement();  //获取SQL的执行对象
            String sql="INSERT INTO `user`(`username`,`password`,`email`,`birthday`) VALUES('菲儿','687652','967@qq.com','2003-06-23')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入数据成功 ");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

注意：插入数据、更新数据、删除数据都是用st.executeUpdate()方法

如果是查找，用st.executeQuery()方法

st.execute()方法对于增删改查都可以，但是效率比另外两个方法慢，而且这个方法返回的是布尔值，对于结果不好判断

 

sql注入问题

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

因为SQL会被拼接 or

比如：

SELECT * FROM `user` WHERE `username`='胖丫' AND `password`='896657'

被or拼接，这样可以查询出全部的用户名密码
SELECT * FROM `user` WHERE `username`='丫' OR '1=1' AND `password`='657' OR '1=1'

"丫 和 657"那里乱输都可以

package com.zcc.lession02;

import com.zcc.lession02.utils.JdbcUtils;

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

public class Test02 {
    public static void main(String[] args) throws SQLException {
        login("2 'OR' 1=1","1 'OR' 1=1");
    }

    public static void login(String username,String password) throws SQLException {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();  //获取数据库连接
            st=conn.createStatement();  //获取SQL的执行对象
            // String sql="SELECT * FROM `user` WHERE `username`='胖丫' AND `password`='896657'";
            String sql="SELECT * FROM `user` WHERE `username`='"+username+"' AND `password`='"+password+"'";
            ResultSet resultSet = st.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getString("username"));
                System.out.println(resultSet.getString("password"));
                System.out.println("------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
/* 输出结果
张三
123456
------------
李四
965896
------------
胖丫
896657
------------
菲菲
687652
------------
 */

 

解决sql注入的不安全

10.5 PreparedStatement对象（解决sql注入问题）

package com.zcc.lession02;

import com.zcc.lession02.utils.JdbcUtils;

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

public class Test04 {
    public static void main(String[] args) throws SQLException {
        login("2 'OR' 1=1","1 'OR' 1=1");
    }

    public static void login(String username,String password) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn=JdbcUtils.getConnection();

            String sql="SELECT * FROM `user` WHERE `username`=? AND `password`=?";

            st=conn.prepareStatement(sql);

            st.setString(1,username);
            st.setString(2,password);

            ResultSet resultSet = st.executeQuery();
            if (resultSet.next()){
                System.out.println(username);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

 

 

10.6 数据库连接池

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

最小连接数 最大连接数 等待超时

 

编写连接池，实现一个接口 DataSource

开源数据源实现

DBCP

C3P0

Druild ：阿里巴巴的

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

 

DBCP

需要用到的jar包

commons-dbcp-1.4

commons-pool-1.6

package com.zcc.lession03.utils;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_dbcp {

    private  static DataSource dataSource=null;
    static {
        try {
            InputStream resourceAsStream = JdbcUtils_dbcp.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            //创建数据源  工厂模式-->创建
            dataSource= BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();  //从数据源中获取连接
    }
    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if (rs!=null){
            rs.close();
        }
    }
}


package com.zcc.lession03;

import com.zcc.lession02.utils.JdbcUtils;
import com.zcc.lession03.utils.JdbcUtils_dbcp;

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

public class Test {
    public static void main(String[] args) throws SQLException {
        login("张三","123456");
    }

    public static void login(String username,String password) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn= JdbcUtils_dbcp.getConnection();

            String sql="SELECT * FROM `user` WHERE `username`=? AND `password`=?";

            st=conn.prepareStatement(sql);

            st.setString(1,username);
            st.setString(2,password);

            ResultSet resultSet = st.executeQuery();
            if (resultSet.next()){
                System.out.println(username);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_dbcp.release(conn,st,null);
        }
    }
}

 

 

 

 

posted @   超、自律即自由  阅读(58)  评论(0)  编辑  收藏  举报
相关博文：
· MyBatis_狂神
· Javaweb
· MySQL数据库
· MySql 总结
· 数据库笔记
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛